MySQL Advanced Query Example

In This section of tutorial, We will learn about advanced MySQL Queries.These Queries Frequently asked by interviewer.

List the MySQL Users.

select user,host from mysql.user;

Rename the 'root' user to 'tutsway'

rename user 'root'@'localhost' to 'tutsway'@'localhost'; FLUSH PRIVILEGES;// flush the privileges command to make these changes happen.

Command to check indexes for a table.

SHOW INDEX FROM table_name;

Query to find Last AUTO_INCREMENT value for a Table

SHOW TABLE STATUS FROM 'DatabaseName' WHERE `name` LIKE 'TableName' ;

Query to find Source and Destination of All Foreign Key Constraint

SELECT referenced_table_name AS ParentTable ,table_name AS ChildTable ,constraint_name AS ConstraintName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE referenced_table_name IS NOT NULL ORDER BY referenced_table_name;

Create an Index on JSON Data Type Column

Let suppose we have following sample table.

CREATE TABLE tbl_SampleJSON (ID INTEGER PRIMARY KEY ,DepartName VARCHAR(250) ,EmployeeDetails JSON );

Insert some sample JSON formatted record in table.

INSERT INTO tbl_TestJSON VALUES ( 1 ,'CEO' ,'{"firstName": "Manish", "lastName": "Mishra"}' ) ,( 2 ,'Production' ,'{"firstName": "Pawan", "lastName": "Kumar"}' );

Create VirtualColumn from JSON Field EmployeeDetails.

ALTER TABLE tbl_SampleJSON ADD VirtualColumn VARCHAR(50) GENERATED ALWAYS AS (JSON_EXTRACT(EmployeeDetails, '$.firstName')) VIRTUAL;

Create Index on VirtualColumn column which indirectly work as Index.

CREATE INDEX idx_tbl_SampleJSON_VirtualColumn ON tbl_SampleJSON(VirtualColumn);

Use MAX() function with group by

SELECT id, MAX(price),category FROM books GROUP BY category;

How to Enable and Disable Foreign Key

Disable Foreign Key

SET FOREIGN_KEY_CHECKS=0;

Enable Foreign Key

SET FOREIGN_KEY_CHECKS=1;

Note:In MySQL InnoDB storage engine, you can use foreign keys constraints to set referential constraints between parent and child tables. FOREIGN_KEY_CHECKS option is set to 1 by default.InnoDB does not allow you to insert a row that violates a foreign key constraint.

To insert a row that violates FOREIGN KEY constraint you need to disable referential integrity.

How to use SQL Query Profiler finds total execution time

Before executing any query you can enable SQL Profiler to check the performance of query.

SET profiling=1; SELECT * FROM table_name;

Check the execution time for each query by running below command

SHOW PROFILES; /*Result*/ Query_ID Duration Query -------------------------------------------------- 1 0.00355325 SELECT * FROM table_name;

Check the CPU information

SHOW PROFILE CPU FOR QUERY 1;

SELECT UTC_TIMESTAMP and CONVERT local date time to UTC time zone.

SELECT local time.

SELECT NOW() AS Local_DateTime;

SELECT UTC time.

SELECT UTC_TIMESTAMP() AS UTC_DateTime;

Convert local time to UTC time

SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00') AS UTC_DateTime;

MySQL query to find last Updated time of the any table.

Using information_schema.tables.

SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME = 'tablename';

We can also use Status Command.

SHOW TABLE STATUS FROM Database_Name LIKE 'tablename';

How to set default value for a Datetime Column

Use "DATETIME DEFAULT CURRENT_TIMESTAMP"

CREATE TABLE TestDefault ( Name VARCHAR(50) ,MyDate DATETIME DEFAULT CURRENT_TIMESTAMP );

Explain Flush Command.

Below are some example of Flush Command.

FLUSH HOSTS; // FLUSH cache of Hosts. FLUSH LOGS; // FLUSH LOGS uses to close and reopen all log files. FLUSH PRIVILEGES; // When you add new user to table. FLUSH QUERY CACHE; //It is used for better performance . FLUSH STATUS; // This command used to FLUSH all kinds of counters. FLUSH TABLES; // This commond remove all query results.

Insert Record in MySQL if not exists in MySQL.

INSERT IGNORE INTO tbl_second SELECT * FROM tbl_first;

Get all student details from the student table order by student_Name descending

Select * from student order by student_Name desc

Get all student details from the student table order by student_Name Ascending and age descending

Select * from student order by student_Name asc,age desc

Get student details from student table whose student name are "Manish" and "Max"

Select * from student where student_Name in ('Manish','Max')

Get student details from student table whose student name are not "Manish" and "Max"

Select * from student where student_Name not in ('Manish','Max')

Get student details from student table whose student_Name starts with 'M'

Select * from student where student_Name like 'M%'

Get student details from student table whose student_Name contains 'M'

Select * from student where student_Name like '%M%'

Get student details from student table whose student_Name ends with 'M'

Select * from student where student_Name like '%M'

Get student details from student table whose student_Name ends with 'M'

Select * from student where student_Name like '%M'

Get student details from student table whose student_Name ends with 'm' and student_Name contains 3 letters

Select * from student where student_Name like '__m'

Get student details from student table whose student_Name starts with 'm' and student_Name contains 3 letters

Select * from student where student_Name like 'm__'

Get student details from student table whose age greater than 18

Select * from student where age > 18

Get student details from student table whose age less than 18

Select * from student where age < 18

Get student details from student table whose age between 18 and 20

Select * from student where age between 18 and 20

Get student details from student table whose joining year is "2015"

Select * from student where year(joining_Date)='2015'

Get database date

select now()