On the basis of Operation MySQL queries provide the following five different types of queries:-
- DDL (Data definition language)
- DML (Data manipulation language)
- DQL (Data query language)
- DCL (Data control language)
- TCL (Transaction control language)
Now I am going to discuss one by one with examples:-
- DDL (Data definition language):- DDL consists of an SQL command which defines database schema. It is used for adding, editing, or deleting the logical structure of the database. It is actually metadata. And it allows how to data reside in the database.
For using DDL commands CREATE and DROP privileges are required in the database schema.
There are following commands are used for DDL.
CREATE:- It is used to create a database and its objects like table, index, view, functions, trigger, store, and procedure.
Example:-
CREATE DATABASE database_name;
CREATE TABLE table_name (col1 datatyple, col2 datatype, col3 datatype, …..);
ALTER:- It is used for modifying or altering parent databases and their structure.
Example:-
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name MODIFY column_name datatype;
DROP:- It is used to delete or remove database objects.
Example:-
DROP TABLE table_name;
TRUNCATE:- It is used to remove all records and all allocated space from the table.
Example:-
TRUNCATE TABLE table_name;
COMMENT:- It is used for adding comments to the data dictionary.
MySQL uses single line comment, which starts with a double dash (–) and end with :
and multi-line comments which start with /* and end with */
Example:-
— Displaying all records from the student table :
SELECT * FROM student;
/* Multi-line comment
Display all records of employee*/
SELECT * FROM employee;
RENAME:- It is used for renaming database objects.
Example:-
ALTER TABLE old_name RENAME TO new_name;
- DML (Data manipulation language):- Data Manipulation language mainly consists of structure query which manipulates data of the table.
The following statements are used for DML:-
- CALL statement:- A CALL statement is used for calling procedures.
- DELETE Statement:- DELETE statement is used for deleting specific rows or complete records of a table.
- DO Statement:- DO execute the expressions but does not return any results. In most respects, DO is shorthand for SELECT
DO is useful primarily with stored functions or triggers.
- EXCEPT Clause:- EXCEPT Clause is used for retrieving unique records between two or more tables. It is normally used with ALL or DISTINCT.
- HANDLER Statement:- The HANDLER statement provides direct access to table storage engine interfaces. When HANDLER is used with OPEN then it will open the table. HANDLER is used with READ then making it accessible using subsequent.
- IMPORT TABLE Statement:- It is used for importing data into the table.
- INSERT Statement:- INSERT Statement is used for inserting data into the table. It inserts data row by row.
- INTERSECT Clause:- INTERSECT limits the result from multiple query blocks to those rows which are common to all. By default it is DISTINCT.
- LOAD DATA Statement:-The LOAD DATA statement reads rows from a text file into a table at a very high speed.
- LOAD XML Statement:- The LOAD XML statement reads data from an XML file into a table.
- Parenthesized Query Expressions:- This expression comes from MySQL 8.0.22 .parenthesized query expression contains a single SELECT or other statement returning a result set and no following optional clauses.
A parenthesized query expression can also contain queries linked by one or more set operations.
- REPLACE Statement:-It is similar to INSERT Statement. the basic difference is if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
- SELECT Statement:- It is used to retrieve rows of the table.
- Set Operations with UNION, INTERSECT, and EXCEPT-SQL set operations combine the results of multiple query blocks into a single result.
- Subqueries:- subquery is a SELECT statement within another statement.
- TABLE Statement:- TABLE Statement returns rows and columns of the named table. It comes from MySQL 8.0.19
- UPDATE Statement:- UPDATE Statement is used to modify specific rows.
- UNION Clause:-UNION combines the result from multiple query blocks into a single result set.
- VALUES Statement:- It is used for returning a set of one or more rows as a table. So, we can say that “it is a table value constructor which also functions as a standalone SQL statement”.
It comes from MySQL 8.0.19.
- DQL (Data query language):- DQL is used to get organized data from the database. It uses the SELECT command for getting data from database tables.
- DCL (Data control language):- DCL is used for the right permission and other controls of the database system. DCL mainly use two command:-
GRANT:- This command provides all relevant permission of the database user.
REVOKE:-This command is used for withdrawing all permission of users.
- TCL (Transaction control language):- TCL command is used for all transactions within a database. TCL command also maintains the consistency of the database.
The TCL commands are: –
COMMIT:- It saves the data permanently
ROLLBACK:- It restores the data from the last save point or commit.
SAVEPOINT:- It saves the data temporarily at a particular point.
Read Also:-
MySQL Functions
MySQL String Functions
MySQL Numeric or Mathematical Function
MySQL Date Or Time Function
MySQL Advanced Function
MySQL Encryption or Compression Function
Please read more detail about MySQL Queries Functions in the MySQL manual