知识花园

SQL完整语法与操作大全

19分钟阅读

SQL 是管理关系型数据库的标准化语言，核心是面向集合的声明式设计——只需描述”要什么数据”，无需关心”如何获取”。

语法体系概览

分类说明核心语句
DQL（Data Query Language）数据查询SELECT
DML（Data Manipulation Language）数据操纵INSERT/UPDATE/DELETE
DDL（Data Definition Language）数据定义CREATE/ALTER/DROP
DCL（Data Control Language）数据控制GRANT/REVOKE

DQL：数据查询

DQL（Data Query Language）用于从数据库中检索数据，是 SQL 中使用最频繁的操作。

基础结构与执行顺序

关键字说明：

  • SELECT：指定要返回的列（投影）
  • FROM：指定数据来源表
  • JOIN：连接多个表
  • WHERE：行级过滤条件
  • GROUP BY：按列分组
  • HAVING：组级过滤条件
  • ORDER BY：结果排序
  • LIMIT：限制返回行数
SELECT 列名
FROM 表名
[JOIN 关联表 ON 关联条件]
WHERE 过滤条件
GROUP BY 分组列
HAVING 分组过滤
ORDER BY 排序列
LIMIT 限制条数;

执行顺序：FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

理解执行顺序至关重要——WHERE 在分组前筛选，HAVING 在分组后筛选；SELECT 别名只能在 ORDER BY 中使用。

条件查询

运算符分类：

  • 比较运算符：=, >, <, >=, <=, !=, <>（!= 和 <> 均表示不等于）
  • 逻辑运算符：AND（且）、OR（或）、NOT（非）
  • 范围运算符：BETWEEN...AND（闭区间）、IN（枚举值）
  • NULL 判断：IS NULL / IS NOT NULL（NULL 不能用 = 比较）
  • 模式匹配：LIKE 配合通配符

通配符：

  • %：匹配任意 0 个或多个字符
  • _：匹配任意单个字符
-- 比较运算
WHERE age = 18 / > 18 / >= 18 / < 30 / <= 30 / != 18 / <> 18
 
-- 逻辑运算
WHERE age >= 18 AND age <= 30
WHERE gender = 1 OR gender = 2
WHERE NOT (user_status = 0)
 
-- 范围查询
WHERE age BETWEEN 18 AND 30
WHERE user_id IN (1, 3, 5)
 
-- NULL 查询
WHERE email IS NULL / IS NOT NULL
 
-- 模糊匹配（% 任意字符，_ 单字符）
WHERE username LIKE '张%'   -- 以张开头
WHERE username LIKE '%三'   -- 以三结尾
WHERE username LIKE '张_'   -- 张+一个字符

聚合与分组

聚合函数：对一组值进行计算，返回单个结果。常见聚合函数：

  • COUNT()：计数，COUNT(*) 计所有行，COUNT(字段) 忽略 NULL
  • SUM()：求和
  • AVG()：平均值
  • MAX() / MIN()：最大/最小值

分组：GROUP BY 将数据按指定列值分组，每组返回一行聚合结果。

WHERE vs HAVING：WHERE 在分组前筛选行，HAVING 在分组后筛选组。HAVING 可使用聚合函数，WHERE 不可以。

-- 聚合函数
SELECT COUNT(*) FROM 表名;
SELECT COUNT(DISTINCT 字段) FROM 表名;
SELECT SUM(price), AVG(age), MAX(age), MIN(create_time) FROM 表名;
 
-- 分组统计
SELECT class_id, COUNT(id) AS cnt FROM student GROUP BY class_id HAVING cnt >= 20;
SELECT category_id, AVG(price) AS avg_price FROM 商品表
GROUP BY category_id HAVING AVG(price) > 100;

多表关联

JOIN（连接）：将多个表按关联条件合并查询。

连接类型说明
INNER JOIN内连接，只返回两表匹配的记录
LEFT JOIN左连接，返回左表全部记录，右表无匹配时为 NULL
RIGHT JOIN右连接，返回右表全部记录，左表无匹配时为 NULL
CROSS JOIN交叉连接，返回两表所有组合（笛卡尔积）

自连接：同一表与自己连接，常用于层级关系查询（如员工-上级）。

-- 内连接：只返回匹配记录
SELECT u.username, o.order_id FROM 用户表 u
INNER JOIN 订单表 o ON u.user_id = o.user_id;
 
-- 左连接：左表全部，右表无匹配则为 NULL
SELECT u.username, o.order_id FROM 用户表 u
LEFT JOIN 订单表 o ON u.user_id = o.user_id;
 
-- 自连接：查询员工及上级
SELECT e.employee_name AS '员工', m.employee_name AS '上级'
FROM 员工表 e LEFT JOIN 员工表 m ON e.manager_id = m.employee_id;

子查询

子查询（Subquery）：嵌套在主查询中的查询语句，先执行子查询，结果作为主查询的条件或数据源。

分类：

  • 标量子查询：返回单个值，可用在 WHERE 条件中比较
  • 列子查询：返回一列多值，配合 IN、ANY、ALL 使用
  • 行子查询：返回一行多列
  • 表子查询：返回多行多列，可作为临时表放在 FROM 中（派生表）

相关子查询：子查询引用主查询的字段，每行都要重新执行子查询。

EXISTS vs IN：EXISTS 只判断是否存在匹配行，不返回具体值，适合大表查询；IN 需要子查询返回所有值。

-- WHERE 子查询
SELECT * FROM 商品表 WHERE price > (SELECT AVG(price) FROM 商品表);
 
-- IN 子查询
SELECT * FROM 用户表 WHERE user_id IN (
  SELECT DISTINCT user_id FROM 订单表 WHERE amount > 1000
);
 
-- EXISTS 子查询（性能更优）
SELECT * FROM 用户表 u WHERE EXISTS (
  SELECT 1 FROM 订单表 o WHERE o.user_id = u.user_id AND o.amount > 1000
);
 
-- 相关子查询：查询比班级平均年龄大的学生
SELECT name, age, class_id FROM student s1
WHERE age > (SELECT AVG(age) FROM student s2 WHERE s2.class_id = s1.class_id);

窗口函数

窗口函数：在不减少行数的情况下，对一组相关行（窗口）进行计算。与 GROUP BY 不同，窗口函数不合并行，每行都有独立结果。

核心语法：函数名 OVER (PARTITION BY 分组列 ORDER BY 排序列)

  • PARTITION BY：定义窗口（分组），类似 GROUP BY 但不合并
  • ORDER BY：窗口内排序
  • ROWS/RANGE：可进一步限定窗口范围

常用窗口函数：

函数说明
ROW_NUMBER()窗口内行号（唯一，无间隙）
RANK()窗口内排名（相同值并列，有间隙）
DENSE_RANK()窗口内排名（相同值并列，无间隙）
SUM()/AVG() OVER窗口内累计/平均

分组内排序，无需聚合：

SELECT name, class_id, age,
  ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY age DESC) AS rn
FROM student;

排序与分页

排序：ORDER BY 按指定列排列结果，默认升序（ASC），可多字段排序（按优先级依次排列）。

分页：LIMIT 限制返回行数，常配合偏移量实现分页。公式：LIMIT (页码-1)*每页条数, 每页条数。

-- 排序
ORDER BY age ASC   -- 升序（默认）
ORDER BY age DESC  -- 降序
ORDER BY user_status DESC, create_time ASC  -- 多字段
 
-- 分页
LIMIT 10              -- 前 10 条
LIMIT 0, 10           -- 第 1 页（跳过 0 条，取 10 条）
LIMIT 10, 10          -- 第 2 页

DML：数据操纵

DML（Data Manipulation Language）用于操作表中的数据，即 CRUD 操作：Create（插入）、Read（查询）、Update（更新）、Delete（删除）。

-- 插入
INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2);
INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2), (值3, 值4);  -- 批量
INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 倵2)
ON DUPLICATE KEY UPDATE 字段2 = VALUES(字段2);  -- 插入或更新
 
-- 更新（务必加 WHERE）
UPDATE 表名 SET 字段1 = 值1 WHERE 条件;
UPDATE 商品表 SET stock = stock - 1 WHERE product_id = 100;
 
-- 删除（务必加 WHERE）
DELETE FROM 表名 WHERE 条件;

DDL：数据定义

数据库操作

数据库（Database）：存储数据的容器，一个 MySQL 服务可管理多个数据库。

字符集与排序规则：

  • CHARACTER SET：字符编码，utf8mb4 支持中文和 emoji
  • COLLATE：排序规则，utf8mb4_unicode_ci 不区分大小写排序
CREATE DATABASE IF NOT EXISTS 库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE 库名;
ALTER DATABASE 库名 CHARACTER SET utf8mb4;
DROP DATABASE IF EXISTS 库名;

表操作

表（Table）：数据库中存储数据的基本单位，由行（记录）和列（字段）组成。

关键字概念：

  • PRIMARY KEY：主键，唯一标识每行记录，不能为 NULL，一个表只能有一个主键
  • FOREIGN KEY：外键，引用另一表的主键，建立表间关联关系
  • AUTO_INCREMENT：自增，数值自动递增，常用于主键
  • NOT NULL：非空约束，字段必须有值
  • UNIQUE：唯一约束，字段值不能重复
  • CHECK：检查约束，字段值必须满足条件
  • DEFAULT：默认值，未指定时自动填充
  • COMMENT：注释，说明字段或表的用途

存储引擎：

  • InnoDB：默认引擎，支持事务、外键、行级锁
  • MyISAM：不支持事务，读性能好，适合只读场景
-- 创建表（完整模板）
CREATE TABLE IF NOT EXISTS 用户表 (
    user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    phone CHAR(11) COMMENT '手机号',
    gender TINYINT DEFAULT 0 COMMENT '性别：0-未知 1-男 2-女',
    user_status TINYINT DEFAULT 1 COMMENT '状态：0-禁用 1-正常',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
 
-- 查看表
SHOW TABLES;
DESC 表名;
SHOW CREATE TABLE 表名;
 
-- 修改表
ALTER TABLE 表名 ADD COLUMN 新字段 类型 AFTER 已有字段;
ALTER TABLE 表名 MODIFY COLUMN 字段名 新类型;
ALTER TABLE 表名 CHANGE COLUMN 旧字段 新字段 类型;
ALTER TABLE 表名 DROP COLUMN 字段名;
ALTER TABLE 表名 ADD INDEX 索引名 (字段名);
RENAME TABLE 旧表名 TO 新表名;
 
-- 删除表
DROP TABLE IF EXISTS 表名;
TRUNCATE TABLE 表名;  -- 清空数据，保留结构

数据类型

数据类型：定义字段可存储的数据种类和范围，影响存储效率、查询性能和数据完整性。

选择原则：

  • 够用即可，不必过大（TINYINT 存状态比 INT 省空间）
  • 精确计算用 DECIMAL，金额避免 FLOAT（有精度误差）
  • 固定长度用 CHAR（如手机号），可变长度用 VARCHAR

数值类型：

类型大小用途
TINYINT1字节状态、性别、年龄
INT4字节主键、ID、数量
BIGINT8字节超大ID、金额（分）
DECIMAL(M,D)可变金额、价格（精确）

字符串类型：

类型大小用途
CHAR(M)0-255字符固定长度：手机号、身份证
VARCHAR(M)0-65535字符可变长度：用户名、地址
TEXT0-65535字节长文本
LONGTEXT0-4GB超长文本

时间类型：

类型格式用途
DATEYYYY-MM-DD日期
DATETIMEYYYY-MM-DD HH:MM:SS日期+时间
TIMESTAMP时间戳自动更新时间

删除操作对比

三种删除方式的本质区别：

操作类型作用对象是否可回滚速度自增重置
DROPDDL删除表结构+数据否最快是
TRUNCATEDDL清空数据，保留结构否快是
DELETEDML删除符合条件的行是较慢否
DROP TABLE 表名;        -- 删除整表，结构和数据一并删除
TRUNCATE TABLE 表名;    -- 清空数据，保留表结构、索引、约束
DELETE FROM 表名 WHERE 条件;  -- 按条件删除行，可回滚

为什么 DROP/TRUNCATE 是 DDL，而 DELETE 是 DML？

DDL 操作数据库对象（结构层面）：

  • DROP：删除表定义本身，是结构操作
  • TRUNCATE：释放数据页、重置表元数据（如自增计数器），属于结构重组

DML 操作数据内容（记录层面）：

  • DELETE：逐行删除记录，是数据操作，每行都记录到事务日志

关键差异：DDL 隐式提交，无法回滚；DML 可纳入事务，支持回滚。

DCL：权限控制

DCL（Data Control Language）用于管理数据库用户和权限。

权限层级：

  • 全局权限：*.*（所有数据库）
  • 数据库权限：库名.*
  • 表权限：库名.表名

常用权限：SELECT（查询）、INSERT（插入）、UPDATE（更新）、DELETE（删除）、ALL PRIVILEGES（全部权限）。

CREATE USER 'dev'@'localhost' IDENTIFIED BY '123456';
GRANT SELECT, INSERT ON school.* TO 'dev'@'localhost';
REVOKE INSERT ON school.* FROM 'dev'@'localhost';
FLUSH PRIVILEGES;
SHOW GRANTS FOR '用户名'@'主机';

索引优化

索引（Index）：类似书籍目录，加速数据查找。本质是 B+ 树 等有序数据结构，将查找从 O(n) 降至 O(log n)。

索引类型：

类型说明
普通索引最基本索引，无约束
唯一索引值不能重复，允许 NULL
主键索引特殊的唯一索引，不允许 NULL
复合索引多列组合索引，遵循最左匹配原则
全文索引用于文本搜索，支持分词

最左匹配原则：复合索引 (a, b, c)，查询条件必须从左连续使用才能命中索引——WHERE a=1、WHERE a=1 AND b=2 命中，WHERE b=2 不命中。

索引失效场景：

  • 在索引列使用函数：WHERE YEAR(create_time) = 2024
  • 类型转换：字符串列用数字比较
  • 使用 OR 连接非索引列
  • LIKE 以 % 开头：WHERE name LIKE '%张'
-- 创建索引
CREATE INDEX idx_username ON 用户表(username);
CREATE UNIQUE INDEX idx_email ON 用户表(email);
CREATE INDEX idx_status_time ON 用户表(user_status, create_time);  -- 复合索引
CREATE FULLTEXT INDEX idx_content ON 文章表(content);  -- 全文索引
 
-- 查看与删除
SHOW INDEX FROM 表名;
DROP INDEX 索引名 ON 表名;

适用场景：WHERE、JOIN、ORDER BY 涉及的列

优化原则：

  • 避免在索引列使用函数（导致索引失效）
  • 用 EXPLAIN 分析执行计划，type=ALL 表示全表扫描

视图操作

视图（View）：虚拟表，不存储数据，而是存储查询语句。每次访问视图时，动态执行底层查询。

优势：

  • 简化复杂查询，封装为可复用的表
  • 安全隔离，可隐藏敏感列
  • 逻辑独立，修改底层表不影响视图调用

限制：视图通常不能直接 UPDATE/INSERT/DELETE（除非满足特定条件）。

CREATE VIEW 用户订单视图 AS
SELECT u.user_id, u.username, o.order_id, o.amount
FROM 用户表 u INNER JOIN 订单表 o ON u.user_id = o.user_id;
 
SELECT * FROM 用户订单视图 WHERE amount > 100;
DROP VIEW IF EXISTS 视图名;

事务处理

事务（Transaction）：一组操作的逻辑单元，要么全部成功，要么全部失败回滚。用于保证数据一致性，典型场景：转账、订单支付。

ACID 特性：

  • 原子性（Atomicity）：事务是不可分割的整体，全部成功或全部失败
  • 一致性（Consistency）：事务前后数据状态合法，满足约束规则
  • 隔离性（Isolation）：并发事务互不干扰，有四种隔离级别（读未提交、读已提交、可重复读、串行化）
  • 持久性（Durability）：事务提交后，数据永久保存，即使系统崩溃也不丢失
START TRANSACTION;
UPDATE 账户表 SET balance = balance - 100 WHERE user_id = 1;
UPDATE 账户表 SET balance = balance + 100 WHERE user_id = 2;
COMMIT;   -- 成功提交
ROLLBACK; -- 失败回滚

最佳实践

命名规范

  • 库名、表名、字段名：小写 + 下划线
  • 主键：id 或 表名_id
  • 时间字段：create_time, update_time

设计规范

  • 每表必有主键、create_time、update_time
  • 字符集统一 utf8mb4
  • 金额用 DECIMAL 或 BIGINT（存分）

查询优化

  • 避免 SELECT *
  • WHERE 条件用索引字段
  • 大偏移量分页用 WHERE id > last_id
  • 复杂查询先 EXPLAIN

安全规范

  • 生产环境禁用 root
  • UPDATE/DELETE 必须加 WHERE
  • 定期备份

方言差异

数据库分页语法特色功能
MySQLLIMIT nJSON 函数
PostgreSQLLIMIT nJSONB、数组类型
OracleROWNUM分区表、PL/SQL
SQL ServerTOP nT-SQL

常用速查

操作语句
当前时间SELECT NOW();
当前用户SELECT USER();
当前数据库SELECT DATABASE();
版本SELECT VERSION();
连接数SHOW PROCESSLIST;

学习资源

  • 文档：MySQL、PostgreSQL
  • 书籍：《SQL 必知必会》《高性能 MySQL》
  • 实战：LeetCode 数据库题库、HackerRank SQL

关系图谱