To add a new MySQL table column and index, you can use ALTER TABLE command.The syntax is as followsALTER TABLE yourTableName ADD COLUMN yourColumnName dataType, ADD INDEX(yourColumnName );To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table AddColumnAndIndexDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(100),    -> Address varchar(200)    -> ); Query OK, 0 rows affected (0.81 sec)Now you can check the description of table. The query is as follows −mysql> desc AddColumnAndIndexDemo;The following is the output+---------+--------------+------+-----+---------+----------------+ | Field ... Read More

To get the difference between two number in MySQL, let us first create a demo tablemysql> create table findDifferenceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> FirstNumber float, -> SecondNumber float -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into findDifferenceDemo(FirstNumber, SecondNumber) values(4.2, 2.3); Query OK, 1 row affected (0.20 sec) mysql> insert into findDifferenceDemo(FirstNumber, SecondNumber) values(23.4, 5.6); Query OK, 1 row affected (0.14 sec) ... Read More

You can select unique value with the help of DISTINCT keyword.The syntax is as followsselect distinct yourColumnName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table selectUniqueValue -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20), -> Age int -> ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into selectUniqueValue(Name, Age) values('John', 21); ... Read More

You can enforce compound uniqueness in MySQL with the help of UNIQUE keyword. Here is the syntax to add UNIQUE keyword to your table column.The syntax is as followsCREATE TABLE yourTableName (    yourColumnName1 datatype,    yourColumnName2 datatype,    yourColumnName3 datatype,    .    .    N    UNIQUE yourConstarintName(yourColumnName2, yourColumnName3) );To understand the above concept, let us create a table with some columns and add a unique constraint to a table. The query to create a table is as followsmysql> create table UniqueDemo    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(100), ... Read More

The following is the syntax to merge two tables using MySQL unioncreate table yourTableName ( select *from yourTableName1 ) UNION ( select *from yourTableName2 );To understand the above syntax, let us create a table. The query to create first table is as followsmysql> create table Old_TableDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(20) -> ); Query OK, 0 rows affected (0.63 sec)The query to create second table is as followsmysql> create table Old_TableDemo2 ... Read More

The following is the syntax to order by date field which is not in date formatselect *from yourTableName order by STR_TO_DATE(yourColumnName, '%d/%m/%Y') DESC;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table orderByDateFormatDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ArrivalDatetime varchar(100)    -> ); Query OK, 0 rows affected (0.73 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('01/10/2012'); Query OK, 1 row affected (0.20 sec) mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('03/11/2010'); ... Read More

You can use UPDATE command for this.The syntax is as followsupdate yourTableName set yourColumnName =yourColumnName +yourIntegerValue where ;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table addANumberToCurrentValueDemo    -> (    -> Game_Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Game_Score int    -> ); Query OK, 0 rows affected (0.67 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into addANumberToCurrentValueDemo(Game_Score) values(1090); Query OK, 1 row affected (0.30 sec) mysql> insert into addANumberToCurrentValueDemo(Game_Score) values(204); Query OK, ... Read More

To insert pipe(|) character in string on INSERT INTO, let us first see an example and create a table. The query to create a table is as followsmysql> create table PipeInsertDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserPassword varchar(100) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into PipeInsertDemo(UserPassword) values('John123|'); Query OK, 1 row affected (0.15 sec) mysql> insert into PipeInsertDemo(UserPassword) values('|123456CarolTaylor'); Query OK, 1 row affected ... Read More

In order to view the auto_increment value for a table, you can use SHOW TABLE command.The syntax is as followsSHOW TABLE STATUS LIKE 'yourTableName'\GThe syntax is as followsSELECT `AUTO_INCREMENT`    FROM `information_schema`.`TABLES`    WHERE `TABLE_SCHEMA` = ‘yourDatabaseName’    AND `TABLE_NAME` =’yourTableName';To understand the above syntaxes, let us create a table. The query to create a table is as followsmysql> create table viewAutoIncrementDemo    -> (    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserName varchar(20)    -> ); Query OK, 0 rows affected (0.59 sec)Now you can insert some records in the table using insert command. The ... Read More

If a table is crashed that means your ENGINE is NULL or empty. The syntax is as follows to check for crashed table.SHOW TABLE STATUS FROM yourDatabaseName;Let us implement the above syntax to check for crashed table Here, our database name is ‘test3’ with some tablesmysql> show table status from test3;The following is the output+------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | bestdateformatdemo           ... Read More