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
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.
| DDL Command | Description |
|---|---|
CREATE DATABASE
| Creates a new database. |
DROP DATABASE
| Deletes a database. |
CREATE TABLE
| Creates a new table in a database. |
ALTER TABLE
| Alters the structure of an existing table. |
DROP TABLE
| Removes a table from a database. |
CREATE INDEX
| Creates an index on a table to improve a specific query performance. |
CREATE VIEW
| Creates a view, a virtual table based on one or more existing tables. |
CREATE PROCEDURE
| Creates a stored procedure, a precompiled SQL statement that can be run multiple times with different parameters. |
CREATE FUNCTION
| Creates a custom user-defined function that can be utilized in SQL statements. |
CREATE TRIGGER
| 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
DML stands for Data Manipulation Language. It deals with data manipulation and includes the most common SQL statements such as SELECT, INSERT, UPDATE, 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.
| DML Command | Description |
|---|---|
SELECT
| Retrieves data from a table. |
INSERT
| Inserts new data into a table. |
UPDATE
| Updates existing data in a table. |
DELETE
| Deletes data from a table. |
REPLACE
| Updates or inserts a record into a table. |
MERGE
| Performs a UPSERT operation (insert or update) on a table. |
CALL
| Calls a stored procedure or Java subprogram. |
EXPLAIN
| Displays the execution plan for a given query. |
LOCK TABLE
| Locks a table to prevent other users from modifying it while a transaction progresses. |
DCL
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.
| DCL Command | Description |
|---|---|
GRANT
| Grants permissions to a user or group of users. |
REVOKE
| Revokes permissions from a user or group of users. |
TCL
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.
| TCL Command | Description |
|---|---|
COMMIT
| Commits a transaction, making the changes permanent. |
ROLLBACK
| Rolls back a transaction, undoing all the changes made. |
SAVEPOINT
| Creates a savepoint within a transaction so that the transaction can be rolled back to that point if necessary. |
SET TRANSACTION
| Specifies the characteristics of a transaction, such as its isolation level. |