This tutorial describes various MySQL SQL commands classified into four subgroups: DDL for database schema, DML for data manipulation, DCL for access control, and TCL for transaction management.
DDL is an abbreviation for Data Definition Language. It is concerned with database schemas and descriptions of how data should be stored in the database. DDL statements are auto-committed, meaning the changes are immediately made to the database and cannot be rolled back. These commands enable database administrators and developers to manage and optimize MySQL databases effectively.
|Creates a new database.|
|Deletes a database.|
|Creates a new table in a database.|
|Alters the structure of an existing table.|
|Removes a table from a database.|
|Creates an index on a table to improve a specific query performance.|
|Creates a view, a virtual table based on one or more existing tables.|
|Creates a stored procedure, a precompiled SQL statement that can be run multiple times with different parameters.|
|Creates a custom user-defined function that can be utilized in SQL statements.|
|Creates a trigger, a type of stored procedure that is automatically executed when certain events occur, such as inserting, updating, or deleting data in a table.|
DML stands for Data Manipulation Language. It deals with data manipulation and includes the most common SQL statements such as
DELETE, etc. DML statements are not auto-committed, meaning the changes can be rolled back if necessary. By mastering these DML commands, you can efficiently manipulate data in MySQL databases.
|Retrieves data from a table.|
|Inserts new data into a table.|
|Updates existing data in a table.|
|Deletes data from a table.|
|Updates or inserts a record into a table.|
|Performs a UPSERT operation (insert or update) on a table.|
|Calls a stored procedure or Java subprogram.|
|Displays the execution plan for a given query.|
|Locks a table to prevent other users from modifying it while a transaction progresses.|
DCL stands for Data Control Language. It includes commands such as
GRANT and is primarily concerned with rights, permissions, and other controls of the database system. DCL statements are also auto-committed.
|Grants permissions to a user or group of users.|
|Revokes permissions from a user or group of users.|
TCL stands for Transaction Control Language. It deals with transactions within a database, which are groups of related DML statements treated as a single unit.
|Commits a transaction, making the changes permanent.|
|Rolls back a transaction, undoing all the changes made.|
|Creates a savepoint within a transaction so that the transaction can be rolled back to that point if necessary.|
|Specifies the characteristics of a transaction, such as its isolation level.|