Detailed explanation of the four SQL languages: DDL DML DCL TCL_MySQL

I have seen many people discussing that SQL is divided into four types. Let’s popularize the knowledge here and summarize their differences.

1. DDL – Data Definition Language

Database definition language: defines the structure of the database.

The main commands are CREATE, ALTER, DROP, etc., which are explained in detail below with examples. This language does not require commit, so be cautious.

CREATE – to create objects in the database Create objects in the database

Example:

CREATE DATABASE test; // 创建一个名为test的数据库

ALTER – alters the structure of the database Modify the database structure

Example:

ALTER TABLE test ADD birthday date; // 修改test表，新增date类型的birthday列

DROP – delete objects from the database Delete objects from the database

Example:

DROP DATABASE test;// 删除test数据库

And others:

TRUNCATE – truncate table contents (very common during development period)

COMMENT – Add comments to the data dictionary

2. DML – Data Manipulation Language

Database operation language: Processing data in the database in SQL

The main commands include INSERT, UPDATE, DELETE, etc. These examples are commonly used by everyone and I will not introduce them one by one. This language requires commit. There is also the commonly used LOCK TABLE.

There are other unfamiliar ones:

CALL – Call a PL/SQL or Java subroutine

EXPLAIN PLAN – Analyze and analyze data access path

3. DCL – Data Control Language

Database control language: authorization, role control, etc.

GRANT – Grant access to users

REVOKE – revoke authorization permission

4. TCL – Transaction Control Language

Transaction Control Language

COMMIT – Save completed work

SAVEPOINT – Sets a savepoint in a transaction and can roll back to it

ROLLBACK –Rollback

SET TRANSACTION – Change transaction options

Example: JDBC in Java encapsulates support for transactions. For example, we first create a new table: test

test.sql

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for `city`
-- ----------------------------
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
 `id` int(11) NOT NULL DEFAULT '0' COMMENT '城市ID',
 `name` varchar(20) DEFAULT NULL COMMENT '名称',
 `state` varchar(20) DEFAULT NULL COMMENT '状态',
 `country` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
SET FOREIGN_KEY_CHECKS = 1;

The first example of JDBC transaction rollback - JDBC database transaction rollback:

/**
 * 描述:JDBC数据库事务回滚
 *
 * Created by bysocket on 16/6/6.
 */
public class TransactionRollBack extends BaseJDBC {
 
  public static void main(String[] args) throws SQLException {
    Connection conn = null;
    try {
      // 加载数据库驱动
      Class.forName(DRIVER);
      // 数据库连接
      conn = DriverManager.getConnection(URL,USER,PWD);
 
      // 关闭自动提交的事务机制
      conn.setAutoCommit(false);
      // 设置事务隔离级别 SERIALIZABLE
      conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
 
      Statement stmt = conn.createStatement();
      int rows = stmt.executeUpdate("INSERT INTO city VALUES (3,'china',1,'cc')");
      rows = stmt.executeUpdate("UPDATE city set country = 'TAIWAN' WHERE id = 4");
 
      // 提交事务
      conn.commit();
    } catch (Exception e) {
      e.printStackTrace();
      // 回滚事务
      if (conn != null) {
        conn.rollback();
      }
    } finally {
      /** 关闭数据库连接 */
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
  }
}

Line 19: The transaction isolation level is set to SERIALIZABLE. The underlying call is SET TRANSACTION of TCL language

Line 22: Execution passes, inserting data

Line 23: The execution fails, there is no record with primary key 4, and an exception is thrown directly

Line 31: Transaction rollback, encapsulated is the ROLLBACK of the TCL statement

The second example of JDBC transaction rollback - JDBC database transaction rollback, rollback to a specific save point:

/**
 * 描述:JDBC数据库事务回滚,回滚到特定的保存点
 *
 * Created by bysocket on 16/6/6.
 */
public class TransactionRollBack2 extends BaseJDBC {
  public static void main(String[] args) throws SQLException {
    Connection conn = null;
    Savepoint svpt = null;
    try {
      // 加载数据库驱动
      Class.forName(DRIVER);
      // 数据库连接
      conn = DriverManager.getConnection(URL,USER,PWD);
 
      // 关闭自动提交的事务机制
      conn.setAutoCommit(false);
      // 设置事务隔离级别 SERIALIZABLE
      conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
 
      Statement stmt = conn.createStatement();
      int rows = stmt.executeUpdate("INSERT INTO city VALUES (3,'china',1,'cc')");
      // 设置事务保存点
      svpt = conn.setSavepoint();
      rows = stmt.executeUpdate("UPDATE city set country = 'TAIWAN' WHERE id = 4");
 
      // 提交事务
      conn.commit();
    } catch (Exception e) {
      e.printStackTrace();
      // 回滚事务
      if (conn != null) {
        conn.rollback(svpt);
      }
    } finally {
      /** 关闭数据库连接 */
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
  }
}

I won’t mention the duplicates of the first example.

Line 9: Declare a savepoint

Line 24: Savepoint set

Line 33: Roll back transaction to this savepoint

The above code involves SAVEPOINT in TCL language

Finally, here is a picture to summarize: (SELECT belongs to DQL.)

I hope this article will be helpful to everyone learning sql.