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

You need to use INFORMATION_SCHEMA.SCHEMATA for current default database collation.The syntax is as followsSELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'yourDatabaseName' LIMIT 1;Let us implement the above syntax to discover current default database collation (via command line client). Our database here is ‘sample’.The query is as follows −mysql> SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'sample' LIMIT 1;The following is the output+------------------------+ | DEFAULT_COLLATION_NAME | +------------------------+ | utf8_general_ci | +------------------------+ 1 row in set (0.00 sec)

To understand the concept, let us first create a demo table.mysql> create table addToExistingValueDemo    -> (    -> Instructor_Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Instructor_Name varchar(30),    -> Instructor_TechnicalSubject text    -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into addToExistingValueDemo(Instructor_Name, Instructor_TechnicalSubject) values('John', 'C, C++'); Query OK, 1 row affected (0.15 sec) mysql> insert into addToExistingValueDemo(Instructor_Name, Instructor_TechnicalSubject) values('Carol', 'Java, Python'); Query OK, 1 row affected (0.18 sec) mysql> insert into addToExistingValueDemo(Instructor_Name, Instructor_TechnicalSubject) values('Bob', 'MySQL, SQL Server'); Query OK, 1 row ... Read More

To count boolean field values within a single query, you can use CASE statement. Let us create a demo table for our example −mysql> create table countBooleanFieldDemo    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentFirstName varchar(20),    -> isPassed tinyint(1)    -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into countBooleanFieldDemo(StudentFirstName, isPassed) values('Larry', 0); Query OK, 1 row affected (0.12 sec) mysql> insert into countBooleanFieldDemo(StudentFirstName, isPassed) values('Mike', 1); Query OK, 1 row affected (0.17 sec) mysql> insert into ... Read More

To avoid inserting duplicate rows in MySQL, you can use UNIQUE(). The syntax is as follows −ALTER TABLE yourTableName ADD UNIQUE(yourColumnName1, yourColumnName2, ...N);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table avoidInsertingDuplicateRows    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> FirstValue int,    -> SecondValue int    -> ); Query OK, 0 rows affected (0.53 sec)Now check the description of table using desc command. The query is as follows −mysql> desc avoidInsertingDuplicateRows;Sample The following is The output −+-------------+---------+------+-----+---------+----------------+ | Field       ... Read More