mysql语句：DML、DDL、DQL、TCL、DCL

分类

类别	核心功能	典型命令	事务支持	操作对象
DDL	定义或修改数据库结构	CREATE、ALTER、DROP	自动提交	表、索引、视图
DML	操作数据（增删改）	INSERT、UPDATE、DELETE	需显式提交	表中的数据
DQL	查询数据	SELECT	无影响	数据查询结果
TCL	控制事务边界	COMMIT、ROLLBACK、SAVEPOINT	事务相关	事务状态
DCL	管理访问权限	GRANT、REVOKE	自动提交	用户/角色权限

注意

• DDL 与事务的冲突：执行 DDL 语句（如 ALTER TABLE）会隐式提交当前未提交的事务。
• DQL 的归属争议：严格来说 SELECT 属于 DQL，但有时也被归类到 DML。
• TCL 的依赖条件：需使用支持事务的引擎（如 InnoDB），否则 ROLLBACK 无效。

数据定义语言（DDL）

DDL （Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。

数据定义语言（Data Definition Language，DDL）是 SQL 语言集中负责数据结构定义与数据库对象定义的语言。

·DDL 的主要功能是定义数据库对象。

典型命令：CREATE（创建表）、ALTER（修改表结构）、DROP（删除表）、TRUNCATE（清空表）、RENAME（重命名表）

数据操纵语言（DML）

DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询，是开发人员日常使用最频繁的操作

数据操纵语言（Data Manipulation Language, DML）是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句。

DML 的主要功能是 访问数据，因此其语法都是以读写数据库为主。

DML 的核心指令是 INSERT、UPDATE、DELETE、SELECT。这四个指令合称 CRUD(Create, Read, Update, Delete)，即增删改查

典型命令：INSERT（插入数据）、UPDATE（更新数据）、DELETE（删除数据）

数据查询语言 （DQL）

DQL（Data Query Language）：由于select不会对表进行破坏，所以有的地方也会把select单独区分开叫做数据库查询语言 DQL（Data Query Language）

特点：

• 不修改数据，但可能消耗大量 I/O 资源（尤其未命中索引时）。
• 支持复杂逻辑（如 JOIN、GROUP BY、子查询）。

典型命令：SELECT（查询数据）

事务控制语言（TCL）

事务控制语言 (Transaction Control Language, TCL) 用于管理数据库中的事务。

这些用于管理由 DML 语句所做的更改。它还允许将语句分组为逻辑事务。

特点：

• 确保数据的 ACID 特性（原子性、一致性、隔离性、持久性）。
• InnoDB 引擎支持，MyISAM 引擎不支持。

典型命令：COMMIT（提交事务）、ROLLBACK（回滚事务）、SAVEPOINT（设置保存点）

数据控制语言（DCL）

数据控制语言 (Data Control Language, DCL) 是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。

典型命令：GRANT（授权）、REVOKE（撤销权限）

特点：

• 控制用户或角色对库、表、字段的访问权限。
• 权限信息存储在 mysql.user、mysql.db 等系统表中

DCL 以控制用户的访问权限为主，因此其指令作法并不复杂，可利用 DCL 控制的权限有：CONNECT、SELECT、INSERT、UPDATE、DELETE、EXECUTE、USAGE、REFERENCES。

根据不同的 DBMS 以及不同的安全性实体，其支持的权限控制也有所不同。

数据定义 DDL

数据库操作（DATABASE）

创建数据库

CREATE DATABASE test;

删除

DROP DATABASE test;

选择

USE test;

数据表操作（TABLE）

创建数据表

普通创建

CREATE TABLE user (
  id int(10) unsigned NOT NULL COMMENT 'Id',
  username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用户名',
  password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密码',
  email varchar(64) NOT NULL DEFAULT 'default' COMMENT '邮箱'
) COMMENT='用户表';

根据已有的表创建新表

CREATE TABLE vip_user AS
SELECT * FROM user;

删除数据表

DROP TABLE user;

修改数据表

列操作

添加列

ALTER TABLE user
ADD age int(3);

删除列

ALTER TABLE user
DROP COLUMN age;

修改列

ALTER TABLE `user`
MODIFY COLUMN age tinyint;

主键操作

添加主键

ALTER TABLE user
ADD PRIMARY KEY (id);

删除主键

ALTER TABLE user
DROP PRIMARY KEY;

增删改 DML

插入数据

INSERT INTO 语句用于向表中插入新记录。

插入完整的行

# 插入一行
INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com');
# 插入多行
INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com'), (12, 'user1', 'user1', 'xxxx@163.com'), (18, 'user2', 'user2', 'xxxx@163.com');

插入行的一部分

INSERT INTO user(username, password, email)
VALUES ('admin', 'admin', 'xxxx@163.com');

插入查询出来的数据

INSERT INTO user(username)
SELECT name
FROM account;

更新数据

UPDATE 语句用于更新表中的记录

UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';

删除数据

DELETE 语句用于删除表中的记录。

TRUNCATE TABLE 可以清空表，也就是删除所有行

说明：TRUNCATE 语句不属于 DML 语法而是 DDL 语法。

删除表中的指定数据

DELETE FROM user
WHERE username = 'robot';

清空表中的数据

TRUNCATE TABLE user;

数据查询 DQL

DQL（Data Query Language）：由于select不会对表进行破坏，所以有的地方也会把select单独区分开叫做数据库查询语言 DQL（Data Query Language）

查询数据

基本查询命令select、limit

SELECT 语句用于从数据库中查询数据。

DISTINCT 用于返回唯一不同的值。它作用于所有列，也就是说所有列的值都相同才算相同。

LIMIT 限制返回的行数。可以有两个参数，

• 第一个参数为起始行，从 0 开始；
• 第二个参数为返回的总行数。

ASC：升序（默认）

DESC：降序

查询单列

SELECT prod_name
FROM products;

查询多列

SELECT prod_id, prod_name, prod_price
FROM products;

查询所有列

SELECT *
FROM products;

查询不同的值

SELECT DISTINCT
vend_id FROM products;

限制查询结果

-- 返回前 5 行
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM mytable LIMIT 2, 3;

排序

order by 用于对结果集按照一个列或者多个列进行排序。

默认按照升序对记录进行排序，如果需要按照降序对记录进行排序，可以使用 desc 关键字。

order by 对多列排序的时候，先排序的列放前面，后排序的列放后面。并且，不同的列可以有不同的排序规则。

SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

分组

group by

group by：group by 子句将记录分组到汇总行中。

group by 为每个组返回一个记录。

group by 通常还涉及聚合count，max，sum，avg 等。

group by 可以按一列或多列进行分组。

group by 按分组字段进行排序后，order by 可以以汇总字段来进行排序。

分组

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name;

分组后排序

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;

having

having 用于对汇总的 group by 结果进行过滤。

having 一般都是和 group by 连用。

where 和 having 可以在相同的查询中。

使用 WHERE 和 HAVING 过滤数据

SELECT cust_name, COUNT(*) AS NumberOfOrders
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) > 1;

having vs where

where：过滤过滤指定的行，后面不能加聚合函数（分组函数）。where 在group by 前。

having：过滤分组，一般都是和 group by 连用，不能单独使用。having 在 group by 之后。

子查询

基本概念

查询就是指将一个 select 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件

子查询是嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句中的完整 SELECT 语句

‌结构‌：

• 外层查询称为主查询
• 内层嵌套的查询称为子查询

用在 WHERE 子句和 FROM 子句后边：

• 当用于 WHERE 子句时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为 WHERE 子句查询条件的值。
• 当用于 FROM 子句时，一般返回多行多列数据，相当于返回一张临时表，这样才符合 FROM 后面是表的规则。这种做法能够实现多表联合查询

执行顺序‌：通常先执行子查询，然后将结果传递给主查询使用

基本语法

WHERE 子句的子查询的基本语法如下：

• 子查询需要放在括号( )内。
• operator 表示用于 where 子句的运算符。

select column_name [, column_name ]
from   table1 [, table2 ]
where  column_name operator
    (select column_name [, column_name ]
    from table1 [, table2 ]
    [where])

FROM 子句的子查询的基本语法：用于 FROM 的子查询返回的结果相当于一张临时表，所以需要使用 AS 关键字为该临时表起一个名字。

select column_name [, column_name ]
from (select column_name [, column_name ]
      from table1 [, table2 ]
      [where]) as temp_table_name
where  condition

示例

示例如下：

• 作为过滤条件

  SELECT * FROM products WHERE 
  	price > 
  		(SELECT AVG(price) FROM products);
  
  

• ‌FROM 子句中的子查询（派生表）

  SELECT * FROM 
  	(SELECT * FROM students WHERE score > 90) 
  	AS top_students;
  
  

• ‌SELECT 子句中的子查询‌

  SELECT 
  	name, 
  	(SELECT COUNT(*) FROM orders WHERE cust_id = c.id) AS order_count 
  FROM customers c;
  
  

• ‌HAVING 子句中的子查询

  SELECT deptno, AVG(sal) FROM emp 
  GROUP BY deptno 
  HAVING AVG(sal) > (SELECT AVG(sal) FROM emp);
  
  

连接JOIN

基本概念

JOIN 子句用于将两个或者多个表联合起来进行查询

连接表的本质就是将不同表的记录合并起来，形成一张新表，这张新表只是临时的，它仅存在于本次查询期间。

基本语法如下： table1.common_column1 = table2.common_column2 是连接条件，只有满足此条件的记录才会合并为一行

其他的也可以，例如 =、>、<、<>、<=、>=、!=、between、like 或者 not，但是最常见的是使用 =

select table1.column1, table2.column2...
from table1
join table2
on table1.common_column1 = table2.common_column2;

如果两张表的关联字段名相同，也可以使用 USING子句来代替 ON，举例如下

# join....on
select c.cust_name, o.order_num
from Customers c
inner join Orders o
on c.cust_id = o.cust_id
order by c.cust_name;

# 如果两张表的关联字段名相同，也可以使用USING子句：join....using()
select c.cust_name, o.order_num
from Customers c
inner join Orders o
using(cust_id)
order by c.cust_name;

连接中ON 和 WHERE 的区别联系

连接表时，SQL 会根据连接条件生成一张新的临时表。ON 就是连接条件，它决定临时表的生成

WHERE 是在临时表生成以后，再对临时表中的数据进行过滤，生成最终的结果集，这个时候已经没有 JOIN-ON 了。

SQL 先根据 ON 生成一张临时表，然后再根据 WHERE 对临时表进行筛选

内连接（交集）

查询的是相关的数据表之间有交集的那部分的数据

-- 内连接
/*
	显示内连接
		SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;
*/
-- 查询用户信息和对应的订单信息
SELECT * FROM USER INNER JOIN orderlist ON orderlist.uid = user.id;

/*
	隐式内连接
	标准语法:
		SELECT 列名 FROM 表名1,表名2 WHERE 关联条件;
*/
-- 查询用户姓名，年龄。和订单编号
SELECT 
	u.name,
	u.age,
	o.number
FROM
	USER u,
	orderlist o
WHERE
	o.uid = u.id;

隐式内连接

“隐式内连接”，也就是没有 INNER JOIN 关键字，使用 WHERE 语句实现内连接的功能

# 隐式内连接
select c.cust_name, o.order_num
from Customers c, Orders o
where c.cust_id = o.cust_id
order by c.cust_name;

显式内连接

# 显式内连接
select c.cust_name, o.order_num
from Customers c inner join Orders o
using(cust_id)
order by c.cust_name;

外连接

左外连接

左外连接：查询左表所有数据，并将两表有交集的数据，补充到查询的所有左表的数据中

可以理解为左表要补充 数据

右外连接：查询右表所有数据，并将两表有交集的数据，补充到查询的所有左表的数据中

可以理解为右表要补充 数据

左右表判断：table_a 插入 table_b，

• 左连接：LEFT JOIN 左侧的表是主表（左表保留全量数据）
  右连接：RIGHT JOIN 右侧的表是主表（右表保留全量数据）

-- 左表：table_a | 右表：table_b
SELECT * 
FROM table_a 
LEFT JOIN table_b ON ... ;

-- 左表：table_b | 右表：table_a
SELECT * 
FROM table_b 
RIGHT JOIN table_a ON ... ;

全外连接（并集）

同时保留两个表中所有的记录，即使其中一方没有匹配项。
对等性：FULL OUTER JOIN 包含了 LEFT JOIN 和 RIGHT JOIN 的结果。

未匹配的记录填充 NULL：如果某条记录在一张表中没有匹配项，则其对应的字段以 NULL 表示。

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

对比

连接类型	保留数据范围	典型用途
INNER JOIN	仅匹配成功的记录	精确关联查询
LEFT JOIN	左表全量 + 右表匹配项	主表数据完整性保障
RIGHT JOIN	右表全量 + 左表匹配项	较少使用

组合UNION

基本概念

UNION 用于将多个 SELECT 语句的结果集合并为单个结果集，自动去重重复行

UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。

UNION 基本规则：

• 所有查询的列数和列顺序必须相同。
• 每个查询中涉及表的列的数据类型必须相同或兼容。
• 通常返回的列名取自第一个查询 。

默认地，UNION 操作符选取不同的值，如果允许重复的值，请使用 UNION ALL

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

注意：

• 列数与数据类型必须兼容：
  • 所有 SELECT 的列数必须相同。
  • 对应列的数据类型需兼容（如 VARCHAR 与 TEXT 兼容，INT 与 DECIMAL 需隐式转换）。
• 列名与别名：最终结果集的列名以 第一个 SELECT 的列名或别名 为准。如下示例

  UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名

  SELECT id AS user_id, name FROM employees
  UNION
  SELECT contractor_id, contractor_name FROM contractors;
  -- 结果列名显示为 user_id、name
  
  

• 去重与保留重复：
  • UNION：自动去重（性能较低）。
  • UNION ALL：保留所有行（高效，优先使用）

使用示例

合并两个部门的员工名单：

-- 查询正式员工和外包员工的姓名、部门
SELECT name, department FROM employees  -- 表1
UNION
SELECT name, department FROM contractors; -- 表2

统计所有用户的登录总次数：

SELECT 'Web' AS source, COUNT(*) AS total_logins FROM web_logins
UNION
SELECT 'App', COUNT(*) FROM app_logins;

JOIN vs UNION

JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。

UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。

函数

不同数据库的函数往往各不相同，因此不可移植，如AVG()等函数

性能优先：避免在 WHERE 条件中对字段使用函数（如 WHERE YEAR(date) = 2025），否则索引可能失效。可改用范围查询：

WHERE date >= '2025-01-01' AND date < '2026-01-01'

字符串函数

函数	功能说明	示例	注意事项
CONCAT(str1, str2)	拼接字符串	SELECT CONCAT(first_name, ’ ', last_name) FROM employees;	若任一参数为 NULL，返回 NULL
SUBSTRING(str, start, length)	截取子字符串	SELECT SUBSTRING(‘2023Q4’, 5, 1) AS quarter; → ‘Q’	索引从 1 开始
LENGTH(str)	返回字节数（中文通常占3字节）	SELECT LENGTH(‘数据’) → 6	需与 CHAR_LENGTH()（字符数）区分
REPLACE(str, old, new)	替换指定子串	SELECT REPLACE(‘Hello SQL’, ‘SQL’, ‘MySQL’) → ‘Hello MySQL’	严格区分大小写
TRIM([BOTH/LEADING/TRAILING] ‘x’ FROM str)	去除首尾指定字符	SELECT TRIM(LEADING ‘0’ FROM ‘000123’) → ‘123’	默认去除空格

数值函数

函数	功能说明	示例	注意事项
ROUND(num, decimals)	四舍五入保留小数位数	SELECT ROUND(3.1415, 2) → 3.14	第二参数为负数时向整数位舍入
CEIL(num)	向上取整	SELECT CEIL(2.3) → 3	常用于分页计算（总页数）
ABS(num)	绝对值	SELECT ABS(-15) → 15	支持整数和浮点数
MOD(num1, num2)	取余运算	SELECT MOD(10, 3) → 1	等价于 % 运算符
RAND()	生成 0~1 的随机数	SELECT RAND() → 0.7632…	可配合 ORDER BY RAND() 随机排序

日期时间函数

函数	功能说明	示例	注意事项
NOW()	当前日期和时间	SELECT NOW() → 2025-07-15 15:48:00	返回结果带时区（会话时区）
DATE_FORMAT(date, format)	格式化日期	SELECT DATE_FORMAT(NOW(), ‘%Y年%m月%d日’) → ‘2025年07月15日’	%H 为 24 小时制小时
DATEDIFF(date1, date2)	计算日期差（天数）	SELECT DATEDIFF(‘2025-12-31’, NOW()) → 169	仅比较日期部分
DATE_ADD(date, INTERVAL expr unit)	日期加减运算	SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) → 2025-07-22 15:48:00	支持 YEAR、MONTH、MINUTE 等单位
TIMESTAMPDIFF(unit, start, end)	计算时间差（灵活单位）	SELECT TIMESTAMPDIFF(MONTH, ‘2025-01-01’, NOW()) → 6	支持秒、小时、周等单位

聚合函数

函数	功能说明	示例	注意事项
COUNT(expr)	统计行数（排除 NULL）	SELECT COUNT(DISTINCT user_id) FROM orders; COUNT(*)	统计所有行
SUM(expr)	求和	SELECT SUM(price * quantity) AS total FROM order_items;	自动跳过 NULL 值
AVG(expr)	平均值	SELECT AVG(salary) FROM employees WHERE dept = ‘IT’;	结果为浮点数（即使整数列）
GROUP_CONCAT(expr [SEPARATOR sep])	合并分组后的字符串	SELECT dept, \GROUP_CONCAT(name SEPARATOR ', ') FROM employees GROUP BY dept;	默认长度限制 1024 字符（可通过 SET 调整）
MAX(expr)/MIN(expr)	最大/最小值	SELECT MAX(temperature), MIN(temperature) FROM sensors;	支持日期、字符串比较

事务处理TCL

MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交

当出现 START TRANSACTION 语句时，会关闭隐式提交；
当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交；

autocommit 标记是针对每个连接而不是针对服务器的

指令：

• START TRANSACTION - 指令用于标记事务的起始点。
• SAVEPOINT - 指令用于创建保留点。
• ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START TRANSACTION 语句处。
• COMMIT - 提交事务

-- 开始事务
START TRANSACTION;

-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');

-- 创建保留点 updateA
SAVEPOINT updateA;

-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');

-- 回滚到保留点 updateA
ROLLBACK TO updateA;

-- 提交事务，只有操作 A 生效
COMMIT;

权限控制DCL

要授予用户帐户权限，可以用GRANT命令。要撤销用户的权限，可以用REVOKE命令

这个平时使用比较少，就不说嘛详细了，后续使用了再补充…