外观
SQL 笔记
约 2854 字大约 10 分钟
2025-08-16
一、SQL 简介
1.1 什么是 SQL?
SQL(Structured Query Language,结构化查询语言)是专门用来访问和处理数据库的编程语言。它定义了一套操作关系型数据库的统一标准,能够让我们以编程的形式操作数据库中的数据。
三个关键点:
- SQL 是一门数据库编程语言
- 使用 SQL 语言编写出来的代码,叫做 SQL 语句
- SQL 语言只能在关系型数据库中使用(例如 MySQL、Oracle、SQL Server),非关系型数据库(例如 MongoDB)不支持 SQL 语言
1.2 SQL 能做什么?
- 从数据库中查询数据
- 向数据库中插入新的数据
- 更新数据库中的数据
- 从数据库删除数据
- 创建新数据库
- 在数据库中创建新表
- 创建存储过程、视图
1.3 SQL 分类
SQL 语句根据其功能,主要分为四类:
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
| DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
1.4 SQL 通用语法
- SQL 语句可以单行或多行书写,以分号结尾
- SQL 语句可以使用空格/缩进来增强语句的可读性
- SQL 语句不区分大小写(MySQL 数据库),关键字建议使用大写
- 注释:
- 单行注释:
-- 注释内容或# 注释内容 - 多行注释:
/* 注释内容 */
- 单行注释:
二、DDL:数据定义语言
2.1 数据库操作
-- 创建数据库
CREATE DATABASE mydb;
-- 查看所有数据库
SHOW DATABASES;
-- 使用/切换数据库
USE mydb;
-- 查看当前使用的数据库
SELECT DATABASE();
-- 删除数据库
DROP DATABASE mydb;2.2 表操作
-- 创建表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age TINYINT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 查看数据库中所有表
SHOW TABLES;
-- 查看表结构
DESC users;
-- 或
DESCRIBE users;
-- 修改表(添加列)
ALTER TABLE users ADD COLUMN address VARCHAR(200);
-- 修改表(修改列)
ALTER TABLE users MODIFY COLUMN address TEXT;
-- 修改表(删除列)
ALTER TABLE users DROP COLUMN address;
-- 删除表
DROP TABLE users;2.3 数据类型
常用数据类型:
| 类别 | 类型 | 描述 |
|---|---|---|
| 整数类型 | ||
TINYINT | 小整数 | -128~127 |
INT | 标准整数 | -2147483648~2147483647 |
BIGINT | 大整数 | -9223372036854775808~9223372036854775807 |
| 浮点类型 | ||
FLOAT | 单精度浮点数 | |
DOUBLE | 双精度浮点数 | |
DECIMAL(M,D) | 精确小数 | DECIMAL(10,2) |
| 字符串类型 | ||
CHAR(N) | 定长字符串 | CHAR(50) |
VARCHAR(N) | 变长字符串 | VARCHAR(100) |
TEXT | 长文本 | |
| 日期时间类型 | ||
DATE | 日期 | 2023-08-15 |
TIME | 时间 | 14:30:00 |
DATETIME | 日期时间 | 2023-08-15 14:30:00 |
TIMESTAMP | 时间戳 | 2023-08-15 14:30:00 |
三、DML:数据操作语言
3.1 插入数据
-- 插入单条记录
INSERT INTO users (name, email, age)
VALUES ('John', 'john@example.com', 30);
-- 插入多条记录
INSERT INTO users (name, email, age)
VALUES
('Alice', 'alice@example.com', 25),
('Bob', 'bob@example.com', 35);
-- 插入查询结果
INSERT INTO active_users (user_id, name)
SELECT id, name FROM users WHERE status = 'active';3.2 更新数据
-- 更新单条记录
UPDATE users SET email = 'new@example.com' WHERE id = 1;
-- 更新多条记录
UPDATE users SET status = 'inactive' WHERE last_login < '2022-01-01';
-- 更新时使用表达式
UPDATE products SET price = price * 0.9 WHERE category = 'sale';3.3 删除数据
-- 删除特定记录
DELETE FROM users WHERE id = 1;
-- 删除多条记录
DELETE FROM users WHERE last_login < '2022-01-01';四、DQL:数据查询语言
4.1 基本查询
-- 查询所有列
SELECT * FROM users;
-- 查询指定列
SELECT id, name, email FROM users;
-- 去重查询
SELECT DISTINCT department FROM employees;
-- 限制结果数量
SELECT * FROM users LIMIT 10; -- 前10条
SELECT * FROM users LIMIT 5, 10; -- 从第6条开始,取10条(分页常用)4.2 条件查询
-- 基本条件
SELECT * FROM users WHERE age > 18;
-- 逻辑运算符
SELECT * FROM users WHERE age > 18 AND status = 'active';
SELECT * FROM users WHERE age > 30 OR city = 'New York';
-- 范围查询
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
-- IN 查询
SELECT * FROM users WHERE department IN ('IT', 'HR', 'Finance');
-- 模糊查询
SELECT * FROM users WHERE name LIKE 'J%'; -- 以J开头
SELECT * FROM users WHERE name LIKE '%son%'; -- 包含son4.3 排序
-- 单列排序
SELECT * FROM users ORDER BY created_at DESC;
-- 多列排序
SELECT * FROM users ORDER BY age ASC, name DESC;4.4 聚合函数
-- 计数
SELECT COUNT(*) AS total_users FROM users;
-- 求和
SELECT SUM(amount) AS total_sales FROM orders;
-- 平均值
SELECT AVG(age) AS average_age FROM users;
-- 最大值/最小值
SELECT MAX(created_at) AS latest_user, MIN(created_at) AS earliest_user
FROM users;4.5 分组查询
-- 基本分组
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
-- 分组过滤(HAVING)
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
-- 多列分组
SELECT department, position, COUNT(*) AS count
FROM employees
GROUP BY department, position;4.6 多表连接查询
-- 内连接(INNER JOIN)
SELECT users.name, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;
-- 左连接(LEFT JOIN)
SELECT users.name, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- 右连接(RIGHT JOIN)
SELECT users.name, orders.order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
-- 多表连接
SELECT users.name, orders.order_id, products.name
FROM users
JOIN orders ON users.id = orders.user_id
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id;4.7 子查询
-- WHERE 子句中的子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- FROM 子句中的子查询
SELECT u.name, o.total_orders
FROM users u
JOIN (
SELECT user_id, COUNT(*) AS total_orders
FROM orders
GROUP BY user_id
) o ON u.id = o.user_id;
-- SELECT 子句中的子查询
SELECT
name,
(SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;4.8 窗口函数
-- 排名函数
SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
-- 分区排名
SELECT
department,
name,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;
-- 累计求和
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS cumulative_sales
FROM orders;五、常用函数
5.1 字符串函数
-- 字符串拼接
SELECT CONCAT('Hello', ' ', 'SQL'); -- 'Hello SQL'
-- 大小写转换
SELECT UPPER('hello'); -- 'HELLO'
SELECT LOWER('HELLO'); -- 'hello'
-- 字符串长度
SELECT LENGTH('SQL'); -- 3
SELECT CHAR_LENGTH('你好'); -- 2
-- 子字符串
SELECT SUBSTRING('Hello SQL' FROM 1 FOR 5); -- 'Hello'
-- 填充
SELECT LPAD('1', 3, '0'); -- '001'
SELECT RPAD('1', 3, '0'); -- '100'
-- 去除空格
SELECT TRIM(' SQL '); -- 'SQL'
-- 正则表达式
SELECT REGEXP_REPLACE('123-456-7890', '[^0-9]', '', 'g'); -- '1234567890'5.2 数值函数
-- 向上取整
SELECT CEIL(1.1); -- 2
-- 向下取整
SELECT FLOOR(1.9); -- 1
-- 四舍五入
SELECT ROUND(2.344, 2); -- 2.34
-- 随机数
SELECT RANDOM(); -- 0到1之间的随机数
-- 绝对值
SELECT ABS(-10); -- 10
-- 幂运算
SELECT POWER(2, 3); -- 85.3 日期函数
-- 当前日期时间
SELECT NOW(); -- '2023-08-15 14:30:00'
-- 当前日期
SELECT CURRENT_DATE; -- '2023-08-15'
-- 当前时间
SELECT CURRENT_TIME; -- '14:30:00'
-- 日期格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS'); -- '2023-08-15 14:30:00'
-- 日期加减
SELECT NOW() + INTERVAL '1 day'; -- 明天
SELECT NOW() - INTERVAL '1 month'; -- 上个月
-- 日期差
SELECT AGE('2023-12-31', '2023-01-01'); -- '11 mons 30 days'5.4 流程函数
-- IF 函数
SELECT IF(1 > 0, 'Yes', 'No'); -- 'Yes'
-- IFNULL 函数
SELECT IFNULL(NULL, 'Default'); -- 'Default'
SELECT IFNULL('', 'Default'); -- ''
-- CASE 语句
SELECT
name,
CASE
WHEN score >= 90 THEN '优秀'
WHEN score >= 80 THEN '良好'
WHEN score >= 70 THEN '中等'
ELSE '及格'
END AS grade
FROM students;六、事务与锁
6.1 事务基础
事务是什么:事务是一系列操作的集合,这些操作要么全部成功,要么全部失败。
ACID 特性:
- Atomicity(原子性):事务是一个不可分割的工作单位
- Consistency(一致性):事务必须使数据库从一个一致性状态变换到另一个一致性状态
- Isolation(隔离性):并发事务之间的操作互不干扰
- Durability(持久性):事务一旦提交,它对数据库的改变是永久的
6.2 事务控制语句
-- 开始事务
BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 保存点
SAVEPOINT point1;
-- 回滚到保存点
ROLLBACK TO point1;6.3 事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 说明 |
|---|---|---|---|---|
| READ UNCOMMITTED | ✓ | ✓ | ✓ | 最低隔离级别,性能最好 |
| READ COMMITTED | ✗ | ✓ | ✓ | 大多数数据库默认级别 |
| REPEATABLE READ | ✗ | ✗ | ✓ | MySQL 默认级别 |
| SERIALIZABLE | ✗ | ✗ | ✗ | 最高隔离级别,性能最差 |
设置隔离级别:
-- 查看当前隔离级别
SHOW transaction_isolation;
-- 设置会话级别隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;七、索引与性能优化
7.1 常见索引类型
| 类型 | 描述 | 适用场景 |
|---|---|---|
| B-tree | 默认索引类型 | 等值查询、范围查询、排序 |
| Hash | 哈希索引 | 等值查询(不支持范围查询) |
| GIN | 通用 inverted index | JSONB、数组、全文搜索 |
| GiST | 通用搜索树 | 地理空间数据、全文搜索 |
| BRIN | 块范围索引 | 大型表,数据有序 |
7.2 索引操作
-- 创建索引
CREATE INDEX idx_username ON users(username);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
-- 创建组合索引
CREATE INDEX idx_name_status ON users(username, status);
-- 创建部分索引
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';
-- 删除索引
DROP INDEX idx_username;7.3 查询优化技巧
**避免 SELECT ***
-- 不推荐 SELECT * FROM users; -- 推荐 SELECT id, name, email FROM users;合理使用 LIMIT
-- 分页查询优化 SELECT * FROM large_table ORDER BY id LIMIT 10000, 20; -- 优化为 SELECT * FROM large_table WHERE id > 10000 ORDER BY id LIMIT 20;避免在 WHERE 子句中对字段进行表达式操作
-- 不推荐(无法使用索引) SELECT * FROM users WHERE YEAR(created_at) = 2023; -- 推荐 SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';使用 EXPLAIN 分析查询
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john';利用覆盖索引
-- 创建覆盖索引 CREATE INDEX idx_covering ON users(username, email); -- 查询只使用索引中的字段 SELECT username, email FROM users WHERE username = 'john';
八、最佳实践
8.1 表设计规范
命名规范
- 数据库名、表名、字段名使用小写字母
- 使用下划线分隔单词(如
user_info) - 避免使用 SQL 保留字
字段选择
- 选择合适的数据类型,优先选择更小的数据类型
- 为字段设置合适的长度
- 为经常查询的字段建立索引
- 为外键字段建立索引
主键设计
- 每个表都应该有一个主键
- 优先考虑使用自增整数作为主键
- 避免使用业务相关的字段作为主键
8.2 查询优化技巧
索引使用原则
- 为 WHERE 子句中的条件字段创建索引
- 为 ORDER BY 和 GROUP BY 字段创建索引
- 避免在索引列上使用函数或表达式
- 选择性高的列更适合创建索引
避免全表扫描
- 确保查询条件能有效利用索引
- 限制返回的字段数量
- 合理使用 LIMIT
优化 JOIN 操作
- 确保 JOIN 条件字段有索引
- 小表驱动大表
- 避免多表 JOIN
8.3 安全注意事项
防止 SQL 注入
- 使用参数化查询
- 避免拼接 SQL 语句
- 对用户输入进行验证和过滤
权限管理
- 为不同应用创建不同的数据库用户
- 遵循最小权限原则
- 定期审查用户权限
九、实用技巧
9.1 批量操作
-- 批量插入
INSERT INTO users (name, email) VALUES
('User1', 'user1@example.com'),
('User2', 'user2@example.com'),
('User3', 'user3@example.com');
-- 批量更新(使用CASE语句)
UPDATE users
SET status = CASE id
WHEN 1 THEN 'active'
WHEN 2 THEN 'inactive'
WHEN 3 THEN 'suspended'
ELSE status
END
WHERE id IN (1, 2, 3);9.2 日期处理
-- 获取当前日期的开始和结束
SELECT
DATE_TRUNC('day', CURRENT_TIMESTAMP) AS start_of_day,
DATE_TRUNC('day', CURRENT_TIMESTAMP) + INTERVAL '1 day' - INTERVAL '1 second' AS end_of_day;
-- 获取本月的第一天和最后一天
SELECT
DATE_TRUNC('month', CURRENT_TIMESTAMP) AS first_day_of_month,
(DATE_TRUNC('month', CURRENT_TIMESTAMP) + INTERVAL '1 month' - INTERVAL '1 day')::DATE AS last_day_of_month;9.3 窗口函数应用
-- 计算移动平均
SELECT
order_date,
amount,
AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7_days
FROM orders;
-- 计算累计和
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS cumulative_sales
FROM orders;