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

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