To delete under safe mode, you can use the below query −SET SQL_SAFE_UPDATES = 0;To understand the above query, let us create a table. The following is the query to create a table −mysql> create table SafeDeleteDemo    −> (    −> Price int    −> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into SafeDeleteDemo values(100); Query OK, 1 row affected (0.11 sec) mysql> insert into SafeDeleteDemo values(200); Query OK, 1 row affected (0.19 sec) mysql> insert into SafeDeleteDemo ... Read More

You can group month and year with the help of function DATE_FORMAT() in MySQL. The GROUP BY clause is also used.The syntax is as follows −SELECT DATE_FORMAT(yourColumnName, '%m-%Y') from yourTableName GROUP BY MONTH(yourColumnName), YEAR(yourColumnName)DESC;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table GroupMonthAndYearDemo −> ( −> DueDate datetime −> ); Query OK, 0 rows affected (1.49 sec)Insert records in the table using the following query −mysql> insert into GroupMonthAndYearDemo values(now()); Query OK, 1 row affected (0.11 sec) ... Read More

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