MySQL Basics: DDL/DML/DCL/TCL Commands, Normal Forms, and Table Pivoting

In daily development, basic and infrequently used MySQL knowledge can be easily forgotten; this article records essential MySQL concepts for quick reference.

SQL Commands

SQL commands are divided into four groups: DDL, DML, DCL, and TCL.

DDL (Data Definition Language)

DDL handles database schemas and defines how data resides in the database. Common commands include:

CREATE : create databases and objects such as tables, indexes, views, stored procedures, functions, and triggers.

ALTER : modify existing database structures.

DROP : delete objects from the database.

TRUNCATE : remove all records from a table, freeing the allocated space.

COMMENT : add comments.

RENAME : rename objects.

Common DDL examples:

# 建表
CREATE TABLE sicimike (
  id int(4) primary key auto_increment COMMENT '主键ID',
  name varchar(10) unique,
  age int(3) default 0,
  identity_card varchar(18)
) ENGINE = InnoDB;

# 设置主键
alter table sicimike add primary key(id);

# 删除主键
alter table sicimike drop primary key;

# 设置唯一键
alter table sicimike add unique key(column_name);

# 删除唯一键
alter table sicimike drop index column_name;

# 创建索引
alter table sicimike add [unique/fulltext/spatial] index/key index_name (identity_card(len) [asc/desc]) [using btree/hash];

# 删除索引
alter table sicimike drop key/index identity_card;

# 查看索引
show index from sicimike;

# 查看列
DESC sicimike;

# 新增列
alter table sicimike add column column_name varchar(30);

# 删除列
alter table sicimike drop column column_name;

# 修改列名
alter table sicimike change column_name new_name varchar(30);

# 修改列属性
alter table sicimike modify column_name varchar(22);

# 查看建表信息
SHOW CREATE TABLE sicimike;

# 添加表注释
alter table sicimike comment '表注释';

# 添加字段注释
alter table sicimike modify column column_name varchar(10) comment '姓名';

DML (Data Manipulation Language)

DML includes the most common SQL statements such as SELECT , INSERT , UPDATE , and DELETE , used to store, modify, retrieve, and delete data.

Pagination example:

-- 查询从第11条数据开始的连续5条数据
SELECT * FROM sicimike LIMIT 10, 5;

Group by behavior and handling of non‑aggregated columns are explained, with examples showing how MySQL returns the first row of a group when non‑grouped columns are selected.

mysql> SELECT * FROM c;
+-----+-------+----------+
| CNO | CNAME | CTEACHER |
+-----+-------+----------+
| 1   | 数学  | liming   |
| 2   | 语文  | liming   |
| 3   | 历史  | xueyou   |
| 4   | 物理  | guorong  |
| 5   | 化学  | liming   |
+-----+-------+----------+
5 rows in set (0.00 sec)

mysql> SELECT cteacher, COUNT(cteacher), cname FROM c GROUP BY cteacher;
+----------+-----------------+-------+
| cteacher | count(cteacher) | cname |
+----------+-----------------+-------+
| guorong  | 1               | 物理  |
| liming   | 3               | 数学  |
| xueyou   | 1               | 历史  |
+----------+-----------------+-------+
3 rows in set (0.00 sec)

The HAVING clause is used to filter grouped data, similar to WHERE but applied after aggregation.

Multi‑Table Update and Delete

-- Multi‑table update
UPDATE tableA a INNER JOIN tableB b ON a.xxx = b.xxx SET a.col1 = xxx, b.col1 = xxx WHERE ...;

-- Multi‑table delete
DELETE a, b FROM tableA a INNER JOIN tableB b ON a.xxx = b.xxx WHERE a.col1 = xxx AND b.col1 = xxx;

DCL (Data Control Language)

DCL deals with permissions. Common commands:

GRANT : grant access rights to users.

REVOKE : revoke previously granted rights.

TCL (Transaction Control Language)

TCL manages transactions:

COMMIT : commit a transaction.

ROLLBACK : roll back a transaction on error.

Normalization (范式)

Database normalization reduces redundancy and improves consistency. The first three normal forms are described:

First Normal Form (1NF) : each column holds atomic values; no repeating groups.

Second Normal Form (2NF) : eliminates partial dependencies on a composite primary key.

Third Normal Form (3NF) : eliminates transitive dependencies; non‑key attributes depend only on candidate keys.

Violations lead to insertion, update, and deletion anomalies, illustrated with example tables.

Horizontal and Vertical Tables (横表纵表)

SQL scripts for creating a horizontal table ( table_h2z ) and a vertical table ( table_z2h ) are provided, followed by examples of converting between them.

Horizontal to Vertical

SELECT NAME, 'chinese' AS `subject`, chinese AS `score` FROM table_h2z
UNION ALL
SELECT NAME, 'math' AS `subject`, math AS `score` FROM table_h2z
UNION ALL
SELECT NAME, 'english' AS `subject`, english AS `score` FROM table_h2z;

Resulting rows list each student with subject and score.

Vertical to Horizontal

SELECT NAME,
  SUM(CASE `subject` WHEN 'chinese' THEN score ELSE 0 END) AS chinese,
  SUM(CASE `subject` WHEN 'math' THEN score ELSE 0 END) AS math,
  SUM(CASE `subject` WHEN 'english' THEN score ELSE 0 END) AS english
FROM table_z2h
GROUP BY NAME;

The query pivots the vertical data back to a horizontal layout.

References

https://www.w3schools.in/mysql/ddl-dml-dcl/