MySQL 的语言体系

MySQL的语言体系可分为四大类，分别用于数据库的不同层面操作。以下是分类及核心功能介绍，结合示例帮助理解：

一、数据定义语言（DDL）

作用：定义和管理数据库对象（表、视图、索引等）的结构。
核心语句：CREATE、ALTER、DROP、TRUNCATE、RENAME。
示例：

-- 创建表
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL
);-- 修改表结构
ALTER TABLE users ADD COLUMN email VARCHAR(100);-- 删除表
DROP TABLE IF EXISTS users;

二、数据操纵语言（DML）

作用：操作数据库中的数据（增、删、改、查）。
核心语句：INSERT、UPDATE、DELETE、SELECT。
示例：

-- 插入数据
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');-- 更新数据
UPDATE users SET email = 'new@example.com' WHERE id = 1;-- 删除数据
DELETE FROM users WHERE id = 1;-- 查询数据
SELECT * FROM users WHERE name LIKE 'A%';

三、数据控制语言（DCL）

作用：管理用户权限和数据库安全。
核心语句：GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT。
示例：

-- 授予权限
GRANT SELECT, INSERT ON users TO 'user'@'localhost';-- 撤销权限
REVOKE DELETE ON users FROM 'user'@'localhost';-- 事务提交
COMMIT;-- 事务回滚
ROLLBACK;

四、数据查询语言（DQL）

说明：严格来说，DQL 属于 DML 的一部分，但因其重要性常被单独列出。
核心语句：SELECT（最复杂、功能最丰富的 SQL 语句）。
示例：

-- 基础查询
SELECT name, email FROM users;-- 聚合查询
SELECT COUNT(*) FROM users WHERE age > 18;-- 多表连接
SELECT u.name, o.order_id 
FROM users u 
JOIN orders o ON u.id = o.user_id;

其他辅助语言

1. 事务控制语言（TCL）：
   管理事务，如 START TRANSACTION、COMMIT、ROLLBACK。
   示例：

   START TRANSACTION;
   UPDATE accounts SET balance = balance - 100 WHERE id = 1;
   UPDATE accounts SET balance = balance + 100 WHERE id = 2;
   COMMIT;
   

2. 系统控制语言（SCL）：
   管理数据库系统配置，如 SET、SHOW。
   示例：

   -- 查看当前用户
   SELECT USER();-- 设置 SQL 模式
   SET sql_mode = 'STRICT_ALL_TABLES';
   

总结对比

语言类型	核心功能	典型语句
DDL	定义数据库结构	CREATE、ALTER、DROP
DML	操作数据库中的数据	INSERT、UPDATE、DELETE
DCL	控制用户权限和事务	GRANT、REVOKE、COMMIT
DQL	查询数据（DML 的子集）	SELECT

理解这些语言分类，能帮助你更清晰地规划数据库操作：

• DDL 用于设计表结构；
• DML 用于日常数据处理；
• DCL 用于权限管理；
• DQL 用于数据分析。

DDL（Data Definition Language）

在 MySQL 中，DDL（Data Definition Language） 用于定义和管理数据库中的表结构。以下是核心的 DDL 操作语法、使用方法及典型场景：

一、创建表（CREATE TABLE）

语法：

CREATE TABLE 表名 (字段1 数据类型 [约束条件],字段2 数据类型 [约束条件],...[PRIMARY KEY (主键字段)],[FOREIGN KEY (外键字段) REFERENCES 关联表(关联字段)]
);

示例：

CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age TINYINT CHECK (age > 0),dept_id INT,FOREIGN KEY (dept_id) REFERENCES departments(id)
);

使用背景：设计新业务表时，需定义字段类型、约束（如主键、外键）。

二、修改表结构（ALTER TABLE）

1. 添加字段

ALTER TABLE 表名 ADD 字段名 数据类型 [约束条件] [FIRST|AFTER 已有字段];

示例：

ALTER TABLE students ADD email VARCHAR(100) UNIQUE AFTER name;

场景：业务需求增加新字段（如用户表新增“手机号”）。

2. 删除字段

ALTER TABLE 表名 DROP COLUMN 字段名;

示例：

ALTER TABLE students DROP COLUMN phone;

场景：移除不再需要的字段（如废弃的“备注”字段）。

3. 修改字段类型/约束

ALTER TABLE 表名 MODIFY 字段名 新数据类型 [约束条件];

示例：

ALTER TABLE students MODIFY age SMALLINT UNSIGNED; -- 扩大年龄范围

场景：调整字段存储能力（如从 TINYINT 改为 INT）。

4. 重命名字段

ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [约束条件];

示例：

ALTER TABLE students CHANGE stu_id student_id INT;

场景：统一字段命名规范（如将 user_name 改为 username）。

5. 添加/删除约束

-- 添加主键
ALTER TABLE 表名 ADD PRIMARY KEY (字段);
-- 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
-- 添加外键
ALTER TABLE 表名 ADD FOREIGN KEY (字段) REFERENCES 关联表(关联字段);

场景：建立表间关联（如订单表关联用户表）。

三、删除表（DROP TABLE）

语法：

DROP TABLE [IF EXISTS] 表名;

示例：

DROP TABLE IF EXISTS temp_users;

场景：清理临时表或废弃业务表。

四、复制表结构（CREATE TABLE LIKE）

语法：

CREATE TABLE 新表 LIKE 原表;

示例：

CREATE TABLE students_backup LIKE students;

场景：快速创建备份表或测试表。

五、重命名表（RENAME TABLE）

语法：

RENAME TABLE 原表名 TO 新表名;

示例：

RENAME TABLE users TO customers;

场景：业务变更导致表名调整。

六、使用背景与注意事项

1. 何时使用 DDL？

• 业务迭代：新增功能需添加字段或表；
• 性能优化：调整字段类型（如将 VARCHAR(255) 改为 VARCHAR(50)）；
• 数据结构重构：拆分或合并表。

2. 风险提示

• 数据丢失：修改字段类型可能导致数据截断（如 VARCHAR(10) 改 VARCHAR(5)）；
• 锁表问题：大表的 DDL 操作可能长时间锁表，建议低峰期执行；
• 兼容性：不同 MySQL 版本对 DDL 的支持略有差异（如 5.7 与 8.0 的 ALTER 语法）。

3. 最佳实践

• 备份数据：执行 DDL 前先备份表（如 CREATE TABLE backup LIKE users）；
• 使用事务：部分 DDL 支持事务回滚（如 ALTER TABLE 在 8.0 中支持原子化操作）；
• 渐进式变更：复杂结构调整分多次执行（如先添加字段，再迁移数据）。

七、示例：综合应用场景

需求：为用户表添加“注册时间”字段，并设置默认值。

-- 1. 添加字段
ALTER TABLE users ADD register_time DATETIME;-- 2. 更新已有数据的注册时间
UPDATE users SET register_time = NOW() WHERE register_time IS NULL;-- 3. 添加默认值约束
ALTER TABLE users ALTER COLUMN register_time SET DEFAULT CURRENT_TIMESTAMP;

通过 DDL 操作，可灵活调整表结构以适应业务变化，但需谨慎评估风险，避免影响线上服务。

DML（Data Manipulation Language）

DML（Data Manipulation Language，数据操纵语言） 是用于对数据库中的数据进行操作的语言，主要包括对表中数据的增、删、改、查。在不同的数据库系统（如MySQL、Oracle、SQL Server等）中，DML语言的核心语法和功能相似，但在一些细节和函数的使用上会存在差异。以下以MySQL为例，为你介绍DML语言的相关内容：

1. 插入数据（INSERT）

语法：

• 插入单条记录：

INSERT INTO 表名 (列1, 列2, ...) 
VALUES (值1, 值2, ...);

如果要插入表中所有列的数据，并且插入值的顺序和表中列的顺序一致，还可以省略列名，语法如下：

INSERT INTO 表名 
VALUES (值1, 值2, ...);

• 插入多条记录：

INSERT INTO 表名 (列1, 列2, ...) 
VALUES 
(值1_1, 值1_2, ...),
(值2_1, 值2_2, ...),
...
(值n_1, 值n_2, ...);

使用规则：

• 插入的值必须与列的数据类型匹配，例如，数值列不能插入字符串（除非能隐式转换）。
• 对于有约束的列，如主键、唯一键、非空约束等，插入的值要满足相应的约束条件，否则会插入失败。

使用场景：

• 向数据库中添加新的业务数据，例如在用户表中插入新注册用户的信息，在订单表中插入新的订单记录等。

示例：
假设有一个students表，包含id、name、age三列，以下是插入数据的示例：

-- 插入单条记录
INSERT INTO students (id, name, age) 
VALUES (1, 'Alice', 20);-- 省略列名插入单条记录（前提是值的顺序和表中列顺序一致）
INSERT INTO students 
VALUES (2, 'Bob', 22);-- 插入多条记录
INSERT INTO students (id, name, age) 
VALUES 
(3, 'Charlie', 21),
(4, 'David', 23);

2. 更新数据（UPDATE）

语法：

UPDATE 表名
SET 列1 = 值1, 列2 = 值2, ...
[WHERE 条件];

WHERE子句用于筛选出需要更新的行，如果省略WHERE子句，则会更新表中的所有行。

使用规则：

• 确保更新的值与列的数据类型兼容。
• 合理使用WHERE条件，避免误更新大量数据。

使用场景：

• 修改已有数据，比如更新用户的联系方式、修改商品的价格、调整员工的工资等。

示例：
将students表中id为1的学生的年龄更新为21：

UPDATE students
SET age = 21
WHERE id = 1;

3. 删除数据（DELETE）

语法：

DELETE FROM 表名
[WHERE 条件];

WHERE子句用于指定删除的条件，如果省略WHERE子句，则会删除表中的所有数据，但表结构仍然保留。

使用规则：

• 执行删除操作时要格外谨慎，因为删除的数据一般很难恢复（除非有备份）。
• 注意外键约束，如果删除的数据在其他表中有相关联的外键数据，可能会违反外键约束，导致删除失败（可以通过设置外键的级联操作来避免这种情况）。

使用场景：

• 删除不再需要的数据，例如删除已注销用户的记录、删除过期的订单数据等。

示例：
删除students表中id为4的学生记录：

DELETE FROM students
WHERE id = 4;

4. 查询数据（SELECT）

语法：

SELECT 列1, 列2, ...
FROM 表名
[WHERE 条件]
[GROUP BY 列]
[HAVING 条件]
[ORDER BY 列 [ASC|DESC]];

• SELECT子句：指定要查询的列，可以使用*表示查询所有列。
• FROM子句：指定要查询的表。
• WHERE子句：用于筛选行。
• GROUP BY子句：用于对结果集进行分组。
• HAVING子句：用于对分组后的结果进行筛选。
• ORDER BY子句：用于对结果集进行排序，ASC表示升序（默认），DESC表示降序。

使用规则：

• GROUP BY、HAVING、ORDER BY子句是可选的，根据具体的查询需求来使用。
• HAVING子句通常与GROUP BY子句一起使用，用于筛选分组后的结果。

使用场景：

• 从数据库中获取所需的数据，比如查询用户的订单列表、统计每个部门的员工人数、查询成绩排名等。

示例：
查询students表中年龄大于20的学生信息，并按照年龄降序排列：

SELECT *
FROM students
WHERE age > 20
ORDER BY age DESC;

不同数据库的差异

• Oracle：在插入数据时，支持INSERT ALL语法，可以同时插入到多个表中；更新和删除操作的语法与MySQL基本一致，但在一些函数和数据类型的处理上存在差异。
• SQL Server：支持MERGE语句，它可以在一个语句中同时实现插入、更新和删除操作，在处理数据同步等场景时非常方便。

DML语言是数据库操作的核心部分，熟练掌握这些操作的语法、规则和使用场景，有助于高效地管理和使用数据库中的数据。

DCL（Data Control Language）

DCL（Data Control Language，数据控制语言）在 MySQL 中主要用于管理用户权限和控制事务，确保数据的安全性和完整性。以下是 DCL 的核心功能、使用方法及相关知识点：

一、权限管理

1. 用户管理

• 创建用户：

  CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
  

  • 主机：指定用户登录的 IP 或域名，例如：
    • 'localhost'：仅允许本地登录；
    • '%'：允许任意 IP 登录；
    • '192.168.1.%'：允许该网段的 IP 登录。

  示例：创建一个只能从本地登录的用户：

  CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'password123';
  

• 删除用户：

  DROP USER '用户名'@'主机';
  

2. 权限授予（GRANT）

• 语法：

  GRANT 权限列表 ON 数据库.表 TO '用户名'@'主机';
  

  • 权限列表：如 SELECT、INSERT、UPDATE、DELETE、ALL PRIVILEGES（所有权限）。
  • 数据库.表：指定权限作用范围，例如：
    • mydb.*：数据库 mydb 的所有表；
    • mydb.users：数据库 mydb 中的 users 表；
    • *.*：所有数据库的所有表。

  示例：

  -- 授予用户对 mydb 数据库的所有表的查询和插入权限
  GRANT SELECT, INSERT ON mydb.* TO 'dev_user'@'localhost';-- 授予用户对所有数据库的所有权限（超级用户）
  GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
  

  • WITH GRANT OPTION：允许用户将自己的权限授予他人。

3. 权限撤销（REVOKE）

• 语法：

  REVOKE 权限列表 ON 数据库.表 FROM '用户名'@'主机';
  

  示例：

  -- 撤销用户的插入权限
  REVOKE INSERT ON mydb.* FROM 'dev_user'@'localhost';
  

4. 查看权限

SHOW GRANTS FOR '用户名'@'主机';

二、事务控制

1. 事务开启与提交

• 显式事务：

  START TRANSACTION;  -- 或 BEGIN;
  -- 执行 SQL 语句（如多个 UPDATE、INSERT 等）
  COMMIT;  -- 提交事务，永久保存更改
  

• 自动提交（默认）：
  MySQL 默认每条 SQL 语句都是一个独立事务，自动提交。可通过以下命令关闭：

  SET autocommit = 0;  -- 0 表示关闭，1 表示开启
  

2. 回滚（ROLLBACK）

• 撤销事务中的所有更改：

  START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  ROLLBACK;  -- 撤销所有 UPDATE
  

3. 保存点（SAVEPOINT）

• 在事务中设置中间点，可部分回滚：

  START TRANSACTION;
  UPDATE table1 SET col = 1;
  SAVEPOINT sp1;  -- 设置保存点
  UPDATE table2 SET col = 2;
  ROLLBACK TO sp1;  -- 回滚到保存点，仅撤销第二个 UPDATE
  COMMIT;
  

三、关键知识点详解

1. 主机（Host）

• 作用：限制用户登录的 IP 或域名，增强安全性。
• 常见值：
  • localhost：本地登录（通过 Unix socket 或 Windows 命名管道）；
  • %：任意 IP（需配合防火墙控制）；
  • 192.168.1.100：指定 IP；
  • %.example.com：指定域名（如 db.example.com）。

2. 端口（Port）

• MySQL 默认端口为 3306，用户连接时需指定（如 mysql -h 127.0.0.1 -P 3306 -u root -p）。
• 端口与权限管理无直接关联，但需确保防火墙开放对应端口。

3. 权限分类

• 全局权限：作用于整个服务器（如 GRANT ALL ON *.*）；
• 数据库权限：作用于指定数据库（如 GRANT SELECT ON mydb.*）；
• 表权限：作用于指定表（如 GRANT UPDATE ON mydb.users）；
• 列权限：作用于指定列（如 GRANT SELECT(id, name) ON mydb.users）。

4. 权限表

MySQL 的权限信息存储在 mysql 数据库的系统表中：

• user：用户账户和全局权限；
• db：数据库级权限；
• tables_priv：表级权限；
• columns_priv：列级权限。

修改这些表后需执行 FLUSH PRIVILEGES; 使变更生效。

四、示例场景

1. 创建只读用户

-- 创建只能从内网访问的只读用户
CREATE USER 'read_only'@'192.168.%' IDENTIFIED BY 'readonly123';
GRANT SELECT ON mydb.* TO 'read_only'@'192.168.%';

2. 安全的事务操作

START TRANSACTION;
SAVEPOINT before_update;-- 扣款操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;-- 检查余额是否足够
IF (SELECT balance FROM accounts WHERE id = 1) < 0 THENROLLBACK TO before_update;
ELSE-- 转账到目标账户UPDATE accounts SET balance = balance + 100 WHERE id = 2;COMMIT;
END IF;

五、注意事项

1. 最小权限原则：只授予用户完成任务所需的最低权限，避免 ALL PRIVILEGES。
2. 密码安全：使用强密码，定期更换，避免明文存储。
3. 事务隔离级别：通过 SET TRANSACTION ISOLATION LEVEL 设置隔离级别（如 READ COMMITTED），影响并发事务的行为。
4. 权限刷新：修改权限后执行 FLUSH PRIVILEGES; 确保立即生效。

掌握 DCL 是数据库安全管理的基础，合理配置权限和事务控制可有效保护数据免受未授权访问和保证数据一致性。

DQL（Data Query Language）

DQL（Data Query Language，数据查询语言）是 SQL 的核心组成部分，主要用于从数据库中检索数据。其核心语句是 SELECT，功能强大且灵活。以下是 DQL 的基本用法和典型场景：

一、基础语法

SELECT [DISTINCT] 列1, 列2, ...
FROM 表名
[WHERE 条件]
[GROUP BY 列]
[HAVING 条件]
[ORDER BY 列 [ASC|DESC]]
[LIMIT 行数 OFFSET 偏移量];

• 关键子句作用：
  1. SELECT：指定要返回的列，支持表达式（如 price*0.9）。
  2. FROM：指定数据源表。
  3. WHERE：过滤行，支持逻辑运算符（AND、OR、NOT）。
  4. GROUP BY：分组统计（如按部门统计人数）。
  5. HAVING：过滤分组结果（类似 WHERE，但作用于分组后）。
  6. ORDER BY：排序结果（ASC 升序，DESC 降序）。
  7. LIMIT：限制返回行数，用于分页。

二、核心用法示例

1. 基础查询

-- 查询所有列（不推荐，可能影响性能）
SELECT * FROM users;-- 查询指定列
SELECT id, name FROM users;-- 去重查询（仅返回不同值）
SELECT DISTINCT department FROM employees;

2. 条件过滤（WHERE）

-- 数值比较
SELECT * FROM products WHERE price > 100;-- 字符串匹配
SELECT * FROM users WHERE name LIKE '张%';  -- 姓张的用户-- 范围查询
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';-- 空值判断
SELECT * FROM employees WHERE manager_id IS NULL;

3. 聚合函数（统计）

-- 统计总行数
SELECT COUNT(*) FROM users;-- 计算平均值、总和、最大值、最小值
SELECT AVG(score) AS 平均分, SUM(salary) AS 总工资 FROM students;-- 分组统计（结合 GROUP BY）
SELECT department, COUNT(*) AS 人数 FROM employees GROUP BY department;

4. 多表连接（JOIN）

-- 内连接（取交集）
SELECT u.name, o.order_id 
FROM users u 
JOIN orders o ON u.id = o.user_id;-- 左连接（保留左表所有记录）
SELECT p.name, c.category_name 
FROM products p 
LEFT JOIN categories c ON p.category_id = c.id;

5. 排序与分页

-- 按价格降序排列
SELECT * FROM products ORDER BY price DESC;-- 分页查询（第 2 页，每页 10 条）
SELECT * FROM users LIMIT 10 OFFSET 10;  -- 或 LIMIT 10, 10

三、高级用法

1. 子查询

-- 查询价格高于平均价的商品
SELECT name, price 
FROM products 
WHERE price > (SELECT AVG(price) FROM products);

2. 组合查询（UNION）

-- 合并两个查询结果（自动去重）
SELECT name FROM users WHERE age < 18
UNION
SELECT name FROM users WHERE age > 60;

3. 窗口函数（MySQL 8.0+）

-- 计算每个部门的工资排名
SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS 排名
FROM employees;

四、使用场景

1. 数据检索：

   • 查询用户列表、订单详情等基础数据。

   SELECT * FROM orders WHERE status = '已支付' AND amount > 1000;
   

2. 数据分析：

   • 统计每月销售额、用户活跃度等。

   SELECT MONTH(order_date) AS 月份, SUM(amount) AS 总销售额 
   FROM orders 
   GROUP BY MONTH(order_date);
   

3. 报表生成：

   • 生成员工工资单、库存报表等。

   SELECT e.name, d.department_name, e.salary 
   FROM employees e 
   JOIN departments d ON e.department_id = d.id;
   

4. 关联查询：

   • 从多个表中组合数据（如用户与其订单）。

   SELECT u.name, o.order_date, p.product_name 
   FROM users u 
   JOIN orders o ON u.id = o.user_id 
   JOIN order_items oi ON o.id = oi.order_id 
   JOIN products p ON oi.product_id = p.id;
   

五、注意事项

1. 性能优化：

   • 避免 SELECT *，按需指定列；
   • 在 WHERE 和 JOIN 条件的列上创建索引。

2. 子查询 vs 连接：

   • 优先使用 JOIN 替代子查询，通常性能更优。

3. SQL 注入风险：

   • 使用预编译语句（Prepared Statements）防止恶意输入。

4. 兼容性：

   • 部分高级功能（如窗口函数）需 MySQL 8.0+ 支持。

总结

DQL 的核心是 SELECT 语句，通过灵活组合子句（WHERE、GROUP BY、JOIN 等），可以实现从简单数据查询到复杂数据分析的各种需求。熟练掌握 DQL 是数据库开发和数据分析的基础。