Important Queries In MySQL

Introduction

In this tutorial, I shall explain to you some important queries in MySQL. And, I assume each developer and DBA should have an understanding of these queries. These queries are very helpful for users of MySQL. And, learning those queries can solve certain complicated tasks and can be used in several situations. So, I wanted to write an article about these queries in MySQL.

Query 1) Get the version name of MySQL

SELECT VERSION();

Query 2) Get Data Directory of MySQL

SELECT @@datadir AS DataDirectory;  

Query 3) Get the Default Storage Engine in MySQL

SELECT @@default_storage_engine AS Default_Storage_Engine; 

Query 4) Get Hostname of MySQL

SELECT @@hostname AS HOSTNAME; 

Query 5) Get Current Language of MySQL

SELECT @@lc_time_names AS Current_Language;  

Query 6) Retrieve the list of All Databases

SHOW DATABASES;  

Query 7) Get the first date of the Previous Month

SELECT DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH,'%Y-%m-01') AS 'FIRST DATE OF PREVOIUS MONTH';  

Query 8) Get the last date of the Previous Month

SELECT LAST_DAY(NOW() - INTERVAL 1 MONTH) AS 'LAST DATE OF PREVIOUS MONTH';  

Query 9) Get the first date of the Current Month

SELECT DATE_SUB(LAST_DAY(NOW()),INTERVAL DAY(LAST_DAY(NOW()))-1 DAY) AS 'FIRST DATE OF CURRENT MONTH';    

OR

SELECT DATE_FORMAT(CURDATE(),'%Y-%m-01') AS 'FIRST DATE OF CURRENT MONTH';  

Query 10) Get the last date of the Current Month

SELECT LAST_DAY(NOW()) AS 'LAST DATE OF CURRENT MONTH';  

Query 11) Get the first date of the Next Month

SELECT DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH,'%Y-%m-01') AS 'FIRST DATE OF NEXT MONTH';  

Query 12) Get the last date of the Next Month

SELECT LAST_DAY(NOW() + INTERVAL 1 MONTH) AS 'LAST DATE OF NEXT MONTH';  

Query 13) Display the Text of Stored Procedure, Trigger, and View

Syntax

SHOW CREATE [<PROCEDURE> | <TRIGGER> | <VIEW>] [<PROC_NAME> | <TRIGGER_NAME> | <VIEW_NAME>];

A) Procedure Example

SHOW CREATE PROCEDURE VATSACALLING; 

B) Trigger Example

SHOW CREATE TRIGGER student_audits;   

C) View Example

SHOW CREATE VIEW myview;  

Query 14) Get the list of Stored Procedures related to Database

Syntax

SHOW PROCEDURE STATUS WHERE Db = <database_name>;

Example

SHOW PROCEDURE STATUS WHERE Db = 'vatsa';  

Query 15) Display Text of Stored Procedure

Syntax

SELECT ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_Name = <Procedure_name>;

Example

SELECT ROUTINE_DEFINITION   
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_Name = 'USP_GET_vatsadetails';  

Query 16) Find the Byte Size of all tables in the database

Syntax

SELECT table_name, round(((data_length + index_length) / 1024 / 1024), 4) as SIZE_In_MB

FROM information_schema.TABLES

WHERE table_schema = 'database_name';

Example

SELECT table_name, round(((data_length + index_length) / 1024 / 1024), 4) as SIZE_In_MB   
FROM information_schema.TABLES 
WHERE table_schema = 'order_by';  

Query 17) List of Primary Key for the particular Database

Syntax

SELECt sta.index_name as Primarykey_name, tab.table_schema as database_name, tab.table_name,

group_concat(distinct sta.column_name order by sta.column_name) as 'Column_List'

FROM INFORMATION_SCHEMA.TABLES as tab

INNER JOIN INFORMATION_SCHEMA.STATISTICS as sta

ON sta.table_schema = tab.table_schema

AND sta.table_name = tab.table_name

AND sta.index_name = 'primary'

WHERE tab.table_schema = <database_name>

AND tab.table_type = 'BASE TABLE'

GROUP BY table_name;

Example

SELECt sta.index_name as Primarykey_name, tab.table_schema as database_name, tab.table_name, 
group_concat(distinct sta.column_name order by sta.column_name) as 'Column_List'  
FROM INFORMATION_SCHEMA.TABLES as tab  
INNER JOIN INFORMATION_SCHEMA.STATISTICS as sta  
ON sta.table_schema = tab.table_schema 
AND sta.table_name = tab.table_name  
AND sta.index_name = 'primary'   
WHERE tab.table_schema = 'order_by'  
AND tab.table_type = 'BASE TABLE'   
GROUP BY table_name;  

Query 18) List of tables with a number of records

Syntax

SELECT table_name, TABLE_ROWS

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'database_name';

Example

SELECT table_name, TABLE_ROWS  
FROM INFORMATION_SCHEMA.TABLES   
WHERE TABLE_SCHEMA = 'order_by';  

Query 19) List of Stored Procedures created in last N days

SHOW PROCEDURE STATUS 
WHERE datediff(curdate(), Created) < 25;  

Query 20) List of Stored Procedures modified in last N days

SHOW PROCEDURE STATUS  
WHERE datediff(curdate(), Modified) < 25;  

Query 21) Get all View list inside the database

Syntax

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

WHERE TABLE_SCHEMA = <database_name>;

Example

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE  
WHERE TABLE_SCHEMA = 'vatsa'  

Query 22) Get the information on tables’ columns

Syntax

DESCRIBE <table_name>;

Example

DESCRIBE studentdetails;  

Working with a database in MySQL

Query 23) Create a database

CREATE DATABASE <database_name>;

Query 24) Use the database name or change it to another database name

USE <database_name>;

Query 25) Drop a database

DROP DATABASE <database_name>;

Working with tables in MySQL

Query 26) Create a new table

CREATE TABLE <table_name> (
  column_name1 Datatype,
  column_name2 Datatype,...
  column_nameN Datatype,
  PRIMARY KEY (column_list) 
);

Query 27) Drop a table

DROP TABLE <Table_name>;

Query 28) Insert a new row into a table

INSERT INTO <table_name> (column_1, column_2, column_3, ...) 
VALUES (value_1, value_2, value_3, ...);

Query 29) Insert multiple rows into a table

INSERT INTO <table_name> (column_list) 
VALUES (value_list1), (value_list2), (value_list), ...;

Query Data from Table(s) in MySQL

Query 30) Fetch all records from a table

SELECT * FROM <table_name>;

Query 31) While retrieving the records, remove duplicate data from a table

SELECT DISTINCT (column_name) 
FROM <table_name>;

Modifying Data In Tables In MySQL

Query 32) Update a record in a table

UPDATE <table_name> 
SET <column_name> = <updated_value> 
WHERE <condition>;

Query 33) Update multiple columns in a row in the table

UPDATE <table_name> 
SET <column_name1> = <updated_value>,         
    <column_name2> = <updated_value>,        
    <column_nameN> = <updated_value>
WHERE <condition>;

Query 34) Delete a record in a table

DELETE FROM <table_name>
WHERE <condition>;

Query 35) Delete all rows

DELETE FROM <table_name>;

Working with triggers in MySQL

Query 36) Create a new trigger

CREATE TRIGGER <trigger_name>
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON <table_name>
FOR EACH ROW
trigger_body;

Query 37) Drop a trigger

DROP TRIGGER <trigger_name>;

Query 38) Show triggers in a database

SHOW TRIGGERS
FROM <database_name>; 

Working with Stored Procedures in MySQL

Query 39) Create a new Stored Procedure

DELIMITER $$
CREATE PROCEDURE <Proc_name> (Parameters)
BEGIN
Stored Procedure Body
END $$ 
DELIMITER;

Query 40) rop a Stored Procedure

DROP PROCEDURE <stored_procedure_name>;

Modifying and Updating Tables In MySQL

Query 41) Add a new column into an existing table

ALTER TABLE <table_name>
ADD COLUMN <column_name> <Datatype> [CONSTRAINTS];

Query 42) Modify a column(s) datatype into a table

ALTER TABLE <table_name>
MODIFY COLUMN <column_name> <Datatype>;

Query 43) Change Column name

ALTER TABLE <table_name>
CHANGE <old_column_name> <new_column_name> Datatype;

Query 44) Drop a column from an existing table

ALTER TABLE <table_name>
DROP COLUMN <column_name>;

Alter Key Constraints in MySQL

Query 45) PRIMARY KEY CONSTRAINT Using ALTER TABLE

ALTER TABLE <table_name>
ADD PRIMARY KEY (column_name);

Query 46) DROP PRIMARY KEY Constraint

ALTER TABLE Persons
DROP PRIMARY KEY;

Query 47) UNIQUE KEY CONSTRAINT Using ALTER TABLE

ALTER TABLE <table_name>
ADD UNIQUE (column_name);

Query 48) DROP UNIQUE KEY Constraint

ALTER TABLE <table_name>
DROP INDEX <constraint_name>;

Query 49) FOREIGN KEY CONSTRAINT Using ALTER TABLE

ALTER TABLE <child_table_name>
ADD FOREIGN KEY (column_name)
REFERENCES <Parent_table_name>(column_name);

Query 50) DROP FOREIGN KEY Constraint

ALTER TABLE <table_name>
DROP FOREIGN KEY <constraint_name>;

You can connect with me @

• LinkedIn
• Twitter

Conclusion

In this article, I have explained various MySQL Important Queries with examples.

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.

Thanks for reading this article!