Let’s say we have a database “business” with number of tables. If you want to show only foreign key constraints, then use the following query −mysql> select *    −> from information_schema.referential_constraints    −> where constraint_schema = 'business';The following is the output displaying only foreign key constraints −+--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-------------------+-----------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME          | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME       | REFERENCED_TABLE_NAME | +--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-------------------+-----------------------+ | def                | business          | ConstChild         ... Read More

You can use MONTHNAME() function from MySQL to display Month name from number. The syntax is as follows.SELECT MONTHNAME(STR_TO_DATE(yourColumnName, ’%m’)) as anyVariableName from yourTableName;To understand the above concept, let us first create a table. The query to create a table is as follows.mysql> create table MonthDemo -> ( -> MonthNum int -> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into MonthDemo values(1); Query OK, 1 row affected (0.14 sec) mysql> insert into MonthDemo values(2); Query OK, 1 row affected (0.15 sec) mysql> ... Read More

You can insert NULL value into an int column with a condition i.e. the column must not have NOT NULL constraints. The syntax is as follows.INSERT INTO yourTableName(yourColumnName) values(NULL);To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table InsertNullDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> ); Query OK, 0 rows affected (0.53 sec)Here is the query to insert NULL whenever you do not pass any value for column. Here this column is StudentAge. MySQL inserts null value by default. The query to ... Read More

You can use BETWEEN clause from MySQL to select records with a particular date and time. The syntax is as follows.select *from AllRecordsFromadate where AdmissionDate between 'yourDateTimeValue1 ' and ''yourDateTimeValue2';To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table AllRecordsFromadate -> ( -> Id int, -> Name varchar(100), -> Age int, -> AdmissionDate datetime -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command. The query to insert records is as follows.mysql> insert into AllRecordsFromadate values(101, 'John', 23, '2018-10-13'); Query OK, ... Read More

You can use DATE() from MySQL to select records with a particular date. The syntax is as follows.SELECT *from yourTableName WHERE DATE(yourDateColumnName)=’anyDate’;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table AllRecordsFromadate -> ( -> Id int, -> Name varchar(100), -> Age int, -> AdmissionDate datetime -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command. The query to insert records is as follows.mysql> insert into AllRecordsFromadate values(101, 'John', 23, '2018-10-13'); Query OK, 1 row affected (0.18 sec) mysql> insert ... Read More

To sort string number, use the CAST() function from MySQL. The syntax is as follows −SELECT *FROM yourTableName ORDER BY (yourColumnName as Decimal(integerValue, integerValueAfterDecimalPoint)) desc;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table SortingStringDemo -> ( -> Amount varchar(10) -> ); Query OK, 0 rows affected (0.91 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into SortingStringDemo values('12.34'); Query OK, 1 row affected (0.21 sec) mysql> insert into SortingStringDemo values('124.50'); Query OK, 1 row affected (0.56 sec) ... Read More

You can use CAST() function from MySQL to achieve this. The syntax is as follows −SELECT CAST(yourColumnName as Date) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table ConvertDateTimeToDate -> ( -> ArrivalDatetime datetime -> ); Query OK, 0 rows affected (0.37 sec)Insert the datetime in the table using insert command. The query is as follows −mysql> insert into ConvertDateTimeToDate values(date_add(now(), interval -1 year)); Query OK, 1 row affected (0.19 sec) mysql> insert into ConvertDateTimeToDate values('2017-11-21 13:10:20'); Query OK, 1 row affected ... Read More

To add columns at a specific position in existing table, use after command. The syntax is as follows −ALTER TABLE yourTableName ADD COLUMN yourColumnName data type AFTER yourExistingColumnName;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table changeColumnPosition -> ( -> Id_Position1 int, -> Name_Position2 varchar(100), -> Address_Position4 varchar(200) -> ); Query OK, 0 rows affected (0.53 sec)Now you can check the description of existing table using desc command. The syntax is as follows −desc yourTableName;The following is the query to check the description.mysql> desc changeColumnPosition;The following is ... Read More

In MySQL, auto increment counter starts from 0 by default, but if you want the auto increment to start from another number, use the below syntax.ALTER TABLE yourTable auto_increment=yourIntegerNumber;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table startAutoIncrement -> ( -> Counter int auto_increment , -> primary key(Counter) -> ); Query OK, 0 rows affected (0.90 sec)Implement the above syntax to begin auto increment from 20. The query is as follows.mysql> alter table startAutoIncrement auto_increment=20; Query OK, 0 rows affected (0.30 sec) Records: 0 Duplicates: 0 Warnings: ... Read More

The symbol in MySQL is same as not equal to operator (!=). Both gives the result in boolean or tinyint(1). If the condition becomes true, then the result will be 1 otherwise 0.Case 1 − Using != operator.The query is as follows −mysql> select 3!=5;The following is the output.+------+ | 3!=5 | +------+ | 1    | +------+ 1 row in set (0.00 sec)Case 2 − Using operator.The query is as follows −mysql> select 3 5;The following is the output.+--------+ | 3 5 | +--------+ | 1      | +--------+ 1 row in set (0.00 ... Read More