SQL语法基础
一，SQL通用语法
一、基本结构
操作类型 [字段/列]
FROM [表名]
[子句]
[运算符/函数];
- 操作类型：如
SELECT,INSERT,UPDATE等。 - 数据来源：通过
FROM指定表或视图。 - 条件修饰：通过子句（如
WHERE,ORDER BY）细化操作逻辑。
二、核心语句类型
1. 数据查询 (DQL)
- SELECT: 检索数据
2. 数据操作 (DML)
- INSERT: 插入数据
- UPDATE: 更新数据
- DELETE: 删除数据
3. 数据定义 (DDL)
- CREATE: 创建表/数据库
- ALTER: 修改表结构
- DROP: 删除表/数据库
4. 数据控制 (DCL)
- GRANT: 授权
- REVOKE: 撤销权限
三、常见子句
- WHERE: 过滤条件
- ORDER BY: 排序
- GROUP BY: 分组聚合
- HAVING: 过滤分组结果
- LIMIT (或 TOP / FETCH): 限制返回行数
四、运算符与函数
- 比较运算符:
=,<>,>,<,>=,<= - 逻辑运算符:
AND,OR,NOT - 通配符:
%(匹配任意字符，如LIKE 'A%')_(匹配单个字符，如LIKE 'User_1')
- 聚合函数:
COUNT(),SUM(),AVG(),MIN(),MAX()
- 字符串函数:
CONCAT(),SUBSTRING(),UPPER(),LOWER()
- 日期函数:
NOW(),DATE(),YEAR(),DATEDIFF()
五、注释
-
单行注释:
-- 这是单行注释 SELECT * FROM customers; -
多行注释:
/* 这是多行注释 可跨越多行 */ SELECT * FROM orders;
六、注意事项
- 大小写不敏感: SQL 关键字和表名/列名通常不区分大小写（但数据内容可能区分）。
- 分号结尾: 多条语句需用分号分隔（某些数据库如 SQL Server 可省略）。
- 兼容性差异:
- 字符串连接符：
||（Oracle/PostgreSQL）或+（SQL Server） - 分页查询：
LIMIT（MySQL） vsOFFSET-FETCH（SQL Server）
- 字符串连接符：
二，SQL语句的分类
一、数据查询语言 (DQL - Data Query Language)
用途：从数据库中检索数据。
核心语句：
-
SELECT：查询数据，支持过滤、排序、分组等。SELECT column1, column2 FROM table_name WHERE condition ORDER BY column1;
特点：
- 常与
JOIN、UNION等结合使用，支持复杂查询。 - 聚合函数（如
SUM、AVG）常与GROUP BY搭配。
二、数据操作语言 (DML - Data Manipulation Language)
用途：对数据库中的数据进行增删改操作。
核心语句：
-
INSERT：插入新数据。INSERT INTO table_name (column1, column2) VALUES (value1, value2); -
UPDATE：修改已有数据。UPDATE table_name SET column1 = value1 WHERE condition; -
DELETE：删除数据。DELETE FROM table_name WHERE condition; -
MERGE（部分数据库支持）：合并数据（插入或更新）。
特点：
- 需谨慎使用
WHERE子句，避免误操作。
三、数据定义语言 (DDL - Data Definition Language)
用途：定义或修改数据库结构（如表、索引）。
核心语句：
-
CREATE：创建数据库对象（表、视图、索引等）。CREATE TABLE table_name ( id INT PRIMARY KEY, name VARCHAR(50) ); -
ALTER：修改数据库对象结构。ALTER TABLE table_name ADD COLUMN new_column INT; -
DROP：删除数据库对象。DROP TABLE table_name; -
TRUNCATE：快速清空表数据（不可回滚）。TRUNCATE TABLE table_name;
特点：
- 执行后通常自动提交（不可回滚）。
- 影响数据库架构，需高权限。
四、数据控制语言 (DCL - Data Control Language)
用途：控制数据库访问权限。
核心语句：
-
GRANT：授予用户权限。GRANT SELECT, INSERT ON table_name TO user1; -
REVOKE：撤销用户权限。REVOKE DELETE ON table_name FROM user1;
特点：
- 权限粒度可细化到表或列级别。
- 需管理员权限执行。
五、事务控制语言 (TCL - Transaction Control Language)
用途：管理数据库事务（保证数据一致性）。
核心语句：
-
COMMIT：提交事务，确认更改。COMMIT; -
ROLLBACK：回滚事务，撤销未提交的更改。ROLLBACK; -
SAVEPOINT：设置事务保存点。SAVEPOINT savepoint_name;
示例：
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user = 'A';
UPDATE accounts SET balance = balance + 100 WHERE user = 'B';
COMMIT; -- 提交事务（若失败则回滚）
六、其他辅助语句
-
USE（切换数据库）：USE database_name; -
EXPLAIN（分析查询执行计划）：EXPLAIN SELECT * FROM table_name; -
SHOW（显示元信息，如 MySQL 中的表结构）：SHOW TABLES;
七、分类总结表
| 类别 | 核心功能 | 主要语句 | 示例场景 |
|---|---|---|---|
| DQL | 数据查询 | SELECT | 生成报表、数据分析 |
| DML | 数据增删改 | INSERT, UPDATE, DELETE | 用户注册、订单修改 |
| DDL | 定义/修改数据库结构 | CREATE, ALTER, DROP | 创建新表、添加字段 |
| DCL | 权限管理 | GRANT, REVOKE | 分配用户访问权限 |
| TCL | 事务控制 | COMMIT, ROLLBACK | 转账操作（保证原子性） |
八、注意事项
- 权限依赖：
- DDL 和 DCL 通常需要管理员权限，DML 和 DQL 可由普通用户执行。
- 事务兼容性：
- 部分数据库（如 MySQL 的 MyISAM 引擎）不支持事务。
- 方言差异：
- 分页语法：
LIMIT（MySQL）、OFFSET-FETCH（SQL Server）、ROWNUM（Oracle）。 - 字符串处理函数：不同数据库的函数名可能不同（如
SUBSTRINGvsSUBSTR）。
- 分页语法：
三，SQL图形化界面工具
主流 SQL 图形化界面工具推荐
1. 跨平台工具（支持 Windows/macOS/Linux）
- DBeaver
- 特点：开源免费，支持几乎所有数据库（MySQL、PostgreSQL、Oracle、SQLite 等），提供直观的界面、数据编辑、ER 图生成和 SQL 调试功能。
- 适用场景：开发者、数据分析师、多数据库用户。
- DataGrip（JetBrains）
- 特点：商业软件（付费），智能代码补全、跨数据库兼容性强、版本控制集成（Git）、强大的数据分析功能。
- 适用场景：专业开发者、团队协作。
- MySQL Workbench
- 特点：官方免费工具，专为 MySQL 设计，支持数据库建模、SQL 开发、服务器配置及性能监控。
- 适用场景：MySQL 开发者、DBA。
- pgAdmin（PostgreSQL 官方工具）
- 特点：开源免费，专注于 PostgreSQL 管理，提供查询工具、监控仪表板和备份功能。
- 适用场景：PostgreSQL 管理员及开发者。
2. Windows 平台推荐
- SQL Server Management Studio (SSMS)
- 特点：微软官方免费工具，专用于 SQL Server，支持 T-SQL 调试、性能分析和自动化任务。
- 适用场景：SQL Server 开发及运维。
- HeidiSQL
- 特点：轻量级开源工具，支持 MySQL、MariaDB、SQL Server 和 PostgreSQL，界面简洁，适合快速查询。
- 适用场景：简单数据库操作和小型项目管理。
3. macOS 平台推荐
- TablePlus
- 特点：付费工具（有免费试用），支持多种数据库，界面美观，注重安全（支持本地加密）。
- 适用场景：设计感要求高的用户或 macOS 深度用户。
- Sequel Pro（仅 MySQL）
- 特点：免费开源，轻量级 MySQL 管理工具，适合基本操作和快速查询。
- 适用场景：MySQL 简单管理及开发。
4. 商业/高级工具
- Navicat Premium
- 特点：付费工具，支持 20+ 种数据库（MySQL、Oracle、MongoDB 等），提供数据同步、备份、团队协作功能。
- 适用场景：企业级多数据库管理。
- Toad for SQL
- 特点：功能强大的商业工具（付费），支持 Oracle、SQL Server 等，提供自动化测试、性能优化和团队开发支持。
- 适用场景：企业 DBA 和复杂项目管理。
核心功能
- 可视化操作：通过拖拽创建表、设计关系模型（ER 图）。
- SQL 编辑器：语法高亮、自动补全、错误提示。
- 数据管理：导入/导出 Excel/CSV、批量编辑数据。
- 性能优化：执行计划分析、索引建议。
- 安全与权限：用户权限管理、连接加密（SSH/SSL）。
如何选择工具？
- 数据库类型：专用于某数据库（如 SSMS）还是多数据库支持（如 DBeaver）。
- 预算：优先开源免费工具（DBeaver、HeidiSQL）或选择商业工具高级功能（Navicat）。
- 操作系统：确认工具是否支持您的系统（如 Sequel Pro 仅限 macOS）。
- 功能需求：是否需要数据建模、团队协作或云数据库支持（如 AWS RDS）。
常见问题
- 免费替代品：DBeaver 社区版功能已足够应对大多数场景。
- 学习成本：TablePlus 和 HeidiSQL 界面简单，适合新手；DataGrip 和 Toad 更适合进阶用户。
- 云数据库支持：大多数工具（如 Navicat、DBeaver）支持连接 AWS RDS、Azure SQL 等云服务。
四，DDL详解
DDL全称是Data Definition Language，即数据定义语言，定义语言就是定义关系模式、删除关系、修改关系模式以及创建数据库中的各种对象，比如表、聚簇、索引、视图、函数、存储过程和触发器等等。
一、DDL 核心功能
DDL 用于 定义或修改数据库结构，包括：
- 创建/删除 数据库、表、索引、视图、存储过程 等对象
- 修改表结构（如增删列、修改约束）
二、MySQL DDL 核心语句
1. 数据库操作
-
创建数据库：
CREATE DATABASE [IF NOT EXISTS] database_name [CHARACTER SET charset_name] [COLLATE collation_name]; -- 示例：创建使用 utf8mb4 字符集的数据库 CREATE DATABASE IF NOT EXISTS my_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -
删除数据库：
DROP DATABASE [IF EXISTS] database_name; -- 示例：安全删除数据库（避免报错） DROP DATABASE IF EXISTS old_db; -
切换数据库：
USE database_name;
2. 表操作
-
创建表：
CREATE TABLE [IF NOT EXISTS] table_name ( column1 datatype [约束] [COMMENT '注释'], column2 datatype [约束], ... [PRIMARY KEY (column)], -- 主键 [FOREIGN KEY (column) REFERENCES other_table(column)], -- 外键 [INDEX index_name (column)] -- 索引 ) [ENGINE=engine_name] [CHARSET=charset_name] [COMMENT '表注释']; -- 示例：创建用户表 CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID', username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_email (email) ) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='用户信息表'; -
删除表：
DROP TABLE [IF EXISTS] table_name [, table_name2...]; -- 示例：删除表（安全写法） DROP TABLE IF EXISTS temp_table; -
清空表数据（保留结构，删除指定表，然后重新创建该表）：
TRUNCATE TABLE table_name; -- TRUNCATE 与 DELETE 的区别： -- 1. TRUNCATE 删除所有数据，不可回滚，速度快 -- 2. DELETE 可加 WHERE 条件逐行删除，可回滚
3. 修改表结构
-
添加列：
ALTER TABLE table_name ADD COLUMN column_name datatype [约束] [AFTER existing_column]; -- 示例：添加手机号列到 username 列之后 ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL AFTER username; -
修改列定义：
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype [约束]; -- 示例：修改 email 列长度为 150 ALTER TABLE users MODIFY COLUMN email VARCHAR(150) NOT NULL; -
重命名列：
ALTER TABLE table_name CHANGE COLUMN old_name new_name datatype [约束]; -- 示例：将 phone 重命名为 mobile ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20); -
删除列：
ALTER TABLE table_name DROP COLUMN column_name; -- 示例：删除 mobile 列 ALTER TABLE users DROP COLUMN mobile; -
重命名表：
ALTER TABLE old_table_name RENAME TO new_table_name; -- 或使用 RENAME TABLE（支持多表重命名） RENAME TABLE old_table1 TO new_table1, old_table2 TO new_table2;
4. 索引操作
-
创建索引：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column1, column2...); -- 示例：为 users 表的 username 创建唯一索引 CREATE UNIQUE INDEX idx_username ON users (username); -
删除索引：
DROP INDEX index_name ON table_name; -- 示例：删除索引 DROP INDEX idx_email ON users;
5. 约束操作
-
添加主键：
ALTER TABLE table_name ADD PRIMARY KEY (column); -
添加外键：
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column) REFERENCES other_table(column) [ON DELETE CASCADE|SET NULL|...]; -- 示例：订单表关联用户表 ALTER TABLE orders ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; -
删除约束：
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
三、MySQL 特有语法注意
- 存储引擎：
- 通过
ENGINE=InnoDB|MyISAM|...指定，InnoDB 支持事务和行级锁，推荐使用。
- 通过
- 字符集与排序规则：
- 推荐使用
utf8mb4（支持 Emoji 和更多 Unicode 字符）。
- 推荐使用
- 自增主键：
- 使用
AUTO_INCREMENT定义自增列，通常搭配主键使用。
- 使用
- 注释：
- 支持为表和列添加注释 (
COMMENT)，方便维护。
- 支持为表和列添加注释 (
四、注意事项
- 数据备份：
- 执行 DDL（尤其是
DROP或ALTER）前备份数据，避免误操作。
- 执行 DDL（尤其是
- 锁表风险：
- 大表执行
ALTER TABLE可能锁表，影响业务。可使用在线 DDL 工具（如pt-online-schema-change）。
- 大表执行
- 兼容性：
- 不同 MySQL 版本对 DDL 的支持可能有差异（如
VARCHAR长度限制）。
- 不同 MySQL 版本对 DDL 的支持可能有差异（如
- 外键约束：
- 外键操作需确保关联表的存在性和一致性。
五，DML详解
MySQL 的 DML（Data Manipulation Language，数据操作语言） 用于对数据库中的数据进行增删改查操作。
一、INSERT（插入数据）
向表中插入新记录。
1. 基本语法
INSERT INTO 表名 (列1, 列2, ...)
VALUES (值1, 值2, ...);
2. 示例
-
插入单行数据：
INSERT INTO users (name, age, email) VALUES ('Alice', 25, 'alice@example.com'); -
插入多行数据：
INSERT INTO users (name, age, email) VALUES ('Bob', 30, 'bob@example.com'), ('Charlie', 28, 'charlie@example.com'); -
从其他表插入数据：
INSERT INTO user_backup (name, age) SELECT name, age FROM users WHERE age > 20;
3. 注意事项
- 如果插入的值与表的列顺序一致，可省略列名。
- 主键或唯一约束冲突时会报错，可使用
INSERT IGNORE忽略错误，或ON DUPLICATE KEY UPDATE处理冲突。
二、SELECT（查询数据）
从表中检索数据。
1. 基本语法
SELECT 列1, 列2, ... FROM 表名 [WHERE 条件] [ORDER BY 排序] [LIMIT 限制];
2. 示例
-
简单查询：
SELECT * FROM users; -- 查询所有列 SELECT name, age FROM users; -- 查询指定列 -
使用别名：
SELECT name AS 用户名, age AS 年龄 FROM users; -
去重（DISTINCT）：
SELECT DISTINCT age FROM users; -
条件过滤（WHERE）：
SELECT * FROM users WHERE age > 25; SELECT * FROM users WHERE name LIKE 'A%'; -- 以A开头 SELECT * FROM users WHERE age BETWEEN 20 AND 30; -
多表连接（JOIN）：
-- 内连接 SELECT u.name, o.order_id FROM users u INNER JOIN orders o ON u.id = o.user_id; -- 左连接 SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id; -
聚合函数与分组（GROUP BY）：
SELECT age, COUNT(*) AS count FROM users GROUP BY age HAVING count > 1; -- HAVING 过滤分组结果 -
排序与分页（ORDER BY + LIMIT）：
SELECT * FROM users ORDER BY age DESC LIMIT 10 OFFSET 0; -- 第一页，每页10条 -
子查询：
SELECT name FROM users WHERE age = (SELECT MAX(age) FROM users);
三、UPDATE（更新数据）
修改表中的现有记录。
1. 基本语法
UPDATE 表名
SET 列1=值1, 列2=值2, ...
[WHERE 条件];
2. 示例
-
更新单列：
UPDATE users SET email = 'new@example.com' WHERE id = 1; -
更新多列：
UPDATE users SET age = 26, email = 'alice@newexample.com' WHERE name = 'Alice'; -
使用表达式：
UPDATE products SET price = price * 1.1; -- 价格上调10% -
结合JOIN更新：
UPDATE users u JOIN orders o ON u.id = o.user_id SET u.status = 'active' WHERE o.total > 1000;
3. 注意事项
- 无
WHERE条件会更新整个表！ - 更新前建议先备份或使用事务。
四、DELETE（删除数据）
从表中删除记录。
1. 基本语法
DELETE FROM 表名 [WHERE 条件];
2. 示例
-
删除特定行：
DELETE FROM users WHERE id = 1; -
清空表：
DELETE FROM users; -- 逐行删除，可回滚 TRUNCATE TABLE users; -- DDL语句，快速清空（不可回滚） -
使用JOIN删除：
DELETE u, o FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age < 18;
3. 注意事项
- 无
WHERE条件会删除所有数据！ DELETE可回滚，TRUNCATE不可回滚且重置自增值。
五、其他高级 DML 操作
-
INSERT INTO … ON DUPLICATE KEY UPDATE
INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25) ON DUPLICATE KEY UPDATE age = 25; -- 主键冲突时更新 -
REPLACE INTO
REPLACE INTO users (id, name) VALUES (1, 'Bob'); -- 存在则删除后插入 -
SELECT … INTO OUTFILE
SELECT * FROM users INTO OUTFILE '/tmp/users.csv' FIELDS TERMINATED BY ',';
六、注意事项
-
事务支持：
START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE id = 1; UPDATE account SET balance = balance + 100 WHERE id = 2; COMMIT; -- 或 ROLLBACK; -
WHERE 子句的重要性：避免误删或误更新。
-
性能优化：批量操作时使用事务，频繁查询的列建立索引。
六，DQL详解
以下是关于 MySQL DQL（Data Query Language，数据查询语言） 的详细解析，涵盖语法、常用操作及高级技巧：
一、DQL 概述
DQL 是 SQL 中专门用于查询数据的子语言，核心语句是 SELECT。DQL 不修改数据，仅用于从数据库中检索数据，支持复杂条件过滤、多表关联、分组统计、排序分页等操作。
二、基本语法
SELECT [DISTINCT] 列1, 列2, 函数或表达式...
FROM 表名
[WHERE 条件]
[GROUP BY 分组列]
[HAVING 分组后过滤条件]
[ORDER BY 排序列 [ASC|DESC]]
[LIMIT 偏移量, 行数];
数据库引擎并非按书写顺序执行，而是按以下优先级处理：
| 执行顺序 | 子句 | 作用 |
|---|---|---|
| 1 | FROM | 确定数据来源，加载表或子查询的数据（包括JOIN操作） |
| 2 | WHERE | 对原始数据逐行过滤，排除不符合条件的行 |
| 3 | GROUP BY | 按指定列分组，合并相同分组的数据（生成分组后的中间结果集） |
| 4 | HAVING | 对分组后的结果集进行过滤，排除不符合条件的分组 |
| 5 | SELECT | 选择最终输出的列，计算表达式或聚合函数（此时才处理别名） |
| 6 | ORDER BY | 对结果集排序（可使用SELECT中定义的别名） |
| 7 | LIMIT | 限制返回的行数 |
三、核心子句详解
1. SELECT 子句
-
查询所有列：
SELECT * FROM employees; -
查询指定列：
SELECT first_name, salary FROM employees; -
使用别名（AS）：
SELECT first_name AS 姓名, salary * 12 AS 年薪 FROM employees; -
去重（DISTINCT）：
SELECT DISTINCT department_id FROM employees; -
使用表达式或函数：
SELECT CONCAT(first_name, ' ', last_name) AS 全名, UPPER(email) AS 邮箱, salary + IFNULL(bonus, 0) AS 总收入 FROM employees;
2. WHERE 子句
用于过滤行数据，支持多种运算符和逻辑条件。
-
比较运算符：
SELECT * FROM employees WHERE salary > 10000; SELECT * FROM employees WHERE hire_date < '2020-01-01'; -
逻辑运算符（AND/OR/NOT）：
SELECT * FROM employees WHERE salary > 5000 AND department_id = 10; -
模糊查询（LIKE）：
SELECT * FROM employees WHERE first_name LIKE 'J%'; -- 以 J 开头 -
范围查询（BETWEEN, IN）：
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000; SELECT * FROM employees WHERE department_id IN (10, 20, 30); -
空值判断（IS NULL）：
SELECT * FROM employees WHERE bonus IS NULL;
3. 分组查询（GROUP BY）
将数据按指定列分组，并对每组进行聚合计算。
- 基本语法
SELECT 分组列, 聚合函数
FROM 表名
[WHERE 过滤条件]
GROUP BY 分组列
[HAVING 分组后过滤条件];
- 示例
-- 按部门分组，统计每个部门的平均工资和人数
SELECT
department_id,
AVG(salary) AS avg_salary,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
-- 结合 WHERE 和 HAVING
SELECT
department_id,
AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01' -- 分组前过滤
GROUP BY department_id
HAVING avg_salary > 8000; -- 分组后过滤
3. 注意事项
SELECT后的非聚合列必须出现在GROUP BY中。WHERE在分组前过滤，HAVING在分组后过滤。
4. HAVING 子句
过滤分组后的结果（类似于 WHERE，但用于分组后）。
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING avg_salary > 8000; -- 过滤平均工资大于8000的部门
5. 排序查询（ORDER BY）
对查询结果按指定列排序，默认升序（ASC），可指定降序（DESC）。
1. 基本语法
SELECT 列1, 列2
FROM 表名
ORDER BY 排序列1 [ASC|DESC], 排序列2 [ASC|DESC];
- 示例
-- 按工资降序排序
SELECT * FROM employees
ORDER BY salary DESC;
-- 多列排序：先按部门升序，再按工资降序
SELECT * FROM employees
ORDER BY department_id ASC, salary DESC;
-- 使用别名排序
SELECT name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;
3. 注意事项
ORDER BY总是写在查询的最后（除了LIMIT）。- 可以按列名、别名、列位置（如
ORDER BY 1）排序。
6. 分页查询（LIMIT）
限制返回的行数，常用于分页显示数据。
1. 基本语法
SELECT 列1, 列2
FROM 表名
LIMIT 行数; -- 取前 N 行
LIMIT 偏移量, 行数; -- 跳过 M 行，取 N 行
-- 或
LIMIT 行数 OFFSET 偏移量; -- 同上（更清晰的写法）
2. 示例
-- 取前 10 条数据
SELECT * FROM employees
LIMIT 10;
-- 分页查询（每页 10 条，第 3 页）
SELECT * FROM employees
LIMIT 10 OFFSET 20; -- 偏移量 = (页码 - 1) * 每页行数
-- 等价于
LIMIT 20, 10;
3. 注意事项
- 偏移量从
0开始计算。 - 分页性能问题：偏移量较大时，避免使用
LIMIT N OFFSET M，可改用基于游标的分页（如WHERE id > last_id）。
7.聚合函数（Aggregate Functions）
聚合函数对一组值执行计算，返回单个值，常用于统计或汇总数据。
- 常用聚合函数
| 函数 | 作用 | 示例 |
|---|---|---|
COUNT() | 统计行数（含 NULL 需注意） | SELECT COUNT(*) FROM employees; |
SUM() | 求和 | SELECT SUM(salary) FROM employees; |
AVG() | 求平均值 | SELECT AVG(age) FROM users; |
MAX() | 最大值 | SELECT MAX(price) FROM products; |
MIN() | 最小值 | SELECT MIN(score) FROM exams; |
- 使用示例
-- 统计员工总数（包括 NULL）
SELECT COUNT(*) AS total_employees FROM employees;
-- 统计有奖金的员工数量（忽略 NULL）
SELECT COUNT(bonus) AS has_bonus_count FROM employees;
-- 计算平均工资（忽略 NULL）
SELECT AVG(salary) AS avg_salary FROM employees;
-- 同时使用多个聚合函数
SELECT
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM employees;
- 注意事项
COUNT(*)统计所有行，COUNT(列名)忽略该列的 NULL 值。- 聚合函数通常与
GROUP BY结合使用，但也可以单独使用。
8.综合示例
结合聚合、分组、排序和分页：
-- 统计每个部门工资最高的前 3 名员工
SELECT
department_id,
name,
salary
FROM employees
WHERE department_id IN (10, 20, 30)
ORDER BY department_id ASC, salary DESC
LIMIT 3;
-- 分页显示各部门的平均工资（每页 5 条）
SELECT
department_id,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC
LIMIT 5 OFFSET 0; -- 第一页
四、高级查询技巧
1. 多表连接（JOIN）
-
内连接（INNER JOIN）：
SELECT e.first_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id; -
左连接（LEFT JOIN）：
SELECT e.first_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id; -- 保留左表所有行 -
右连接（RIGHT JOIN）：
SELECT e.first_name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id; -- 保留右表所有行
2. 子查询（Subquery）
-
标量子查询（返回单个值）：
SELECT first_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); -
行子查询（返回一行）：
SELECT * FROM employees WHERE (department_id, salary) IN (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id); -
关联子查询（引用外部查询）：
SELECT e.first_name, e.salary, e.department_id FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department_id = e.department_id );
3. 联合查询（UNION）
合并多个查询结果，要求列数相同且数据类型兼容。
SELECT first_name FROM employees
UNION
SELECT department_name FROM departments; -- 自动去重
UNION ALL -- 保留重复行
4. 窗口函数（Window Functions）
MySQL 8.0+ 支持，用于在结果集的“窗口”内计算（如排名、累计值）。
SELECT
first_name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank,
SUM(salary) OVER (PARTITION BY department_id) AS dept_total
FROM employees;
五、性能优化建议
-
索引优化：
- 为
WHERE、JOIN、ORDER BY涉及的列创建索引。 - 避免在索引列上使用函数或表达式（如
WHERE YEAR(date) = 2023）。
- 为
-
避免全表扫描：
-
使用
EXPLAIN分析查询计划：EXPLAIN SELECT * FROM employees WHERE salary > 5000;
-
-
减少返回数据量：
- 明确指定需要的列，避免
SELECT *。 - 合理使用
LIMIT分页。
- 明确指定需要的列，避免
-
避免复杂子查询：
- 将子查询改写为
JOIN操作以提高效率。
- 将子查询改写为
六、示例场景
场景 1：统计各部门人数及平均工资
SELECT
d.department_name,
COUNT(e.employee_id) AS 人数,
AVG(e.salary) AS 平均工资
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id
HAVING 人数 > 5
ORDER BY 平均工资 DESC;
场景 2：查询每个员工的工资排名
SELECT
first_name,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS 排名
FROM employees;
七，DQL详解
MySQL中的DCL（Data Control Language，数据控制语言）主要用于数据库的权限管理和安全控制，核心语句包括GRANT、REVOKE，以及角色管理相关的操作。
1. GRANT（授予权限）
功能：向用户或角色授予特定权限。
语法：
GRANT 权限列表 ON 对象 TO 用户或角色 [WITH GRANT OPTION];
示例：
-- 授予用户对所有数据库的SELECT权限
GRANT SELECT ON *.* TO 'user1'@'localhost';
-- 授予用户对特定数据库的全部权限
GRANT ALL PRIVILEGES ON mydb.* TO 'user2'@'%';
-- 允许用户将自己的权限授予他人
GRANT INSERT ON mydb.table1 TO 'user3'@'192.168.1.%' WITH GRANT OPTION;
权限层级：
- 全局权限：
*.*（所有数据库和表）。 - 数据库级：
mydb.*。 - 表级：
mydb.table1。 - 列级：可细化到具体列（需明确指定列名）。
2. REVOKE（撤销权限）
功能：从用户或角色收回已授予的权限。
语法：
REVOKE 权限列表 ON 对象 FROM 用户或角色;
示例：
-- 撤销用户对某表的UPDATE权限
REVOKE UPDATE ON mydb.table1 FROM 'user1'@'localhost';
-- 撤销所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'%';
3. 角色管理（MySQL 8.0+）
功能：通过角色简化权限分配。
相关操作：
-
创建角色：
CREATE ROLE 'role_read', 'role_write'; -
为角色授权：
GRANT SELECT ON mydb.* TO 'role_read'; GRANT INSERT, UPDATE ON mydb.* TO 'role_write'; -
将角色授予用户：
GRANT 'role_read', 'role_write' TO 'user1'@'localhost'; -
激活角色：
SET DEFAULT ROLE 'role_read' TO 'user1'@'localhost'; -
撤销角色：
REVOKE 'role_write' FROM 'user1'@'localhost';
4. 用户管理（关联操作）
虽然用户管理通常属于DDL，但与DCL密切相关：
-
创建用户：
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; -
修改用户密码：
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'new_password'; -
删除用户：
DROP USER 'olduser'@'%';
5. 其他关键命令
-
查看权限：
SHOW GRANTS FOR 'user1'@'localhost'; -
刷新权限（手动生效）：
FLUSH PRIVILEGES; -- 修改系统表后需执行
6. 注意事项
- 权限生效时机：已登录用户需重新连接才能生效。
- 通配符使用：
%表示任意主机，*表示所有数据库或表。 - WITH GRANT OPTION：谨慎使用，避免权限扩散。
- 角色激活：MySQL 8.0需显式激活角色或设置默认角色。
示例场景
-- 1. 创建用户并授予角色
CREATE USER 'dev_user'@'%' IDENTIFIED BY 'dev_pass';
CREATE ROLE 'developer';
GRANT SELECT, INSERT, UPDATE ON app_db.* TO 'developer';
GRANT 'developer' TO 'dev_user'@'%';
-- 2. 撤销权限并删除角色
REVOKE INSERT ON app_db.* FROM 'developer';
REVOKE 'developer' FROM 'dev_user'@'%';
DROP ROLE 'developer';
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
