My Knowledge Base

外观

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 语句根据其功能，主要分为四类：

分类全称说明
DDLData Definition Language数据定义语言，用来定义数据库对象(数据库，表，字段)
DMLData Manipulation Language数据操作语言，用来对数据库表中的数据进行增删改
DQLData Query Language数据查询语言，用来查询数据库中表的记录
DCLData Control Language数据控制语言，用来创建数据库用户、控制数据库的访问权限

1.4 SQL 通用语法

  1. SQL 语句可以单行或多行书写，以分号结尾
  2. SQL 语句可以使用空格/缩进来增强语句的可读性
  3. SQL 语句不区分大小写（MySQL 数据库），关键字建议使用大写
  4. 注释：
    • 单行注释：-- 注释内容 或 # 注释内容
    • 多行注释：/* 注释内容 */

二、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%'; -- 包含son

4.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); -- 8

5.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 indexJSONB、数组、全文搜索
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 查询优化技巧

  1. **避免 SELECT ***

    -- 不推荐
SELECT * FROM users;

-- 推荐
SELECT id, name, email FROM users;

  2. 合理使用 LIMIT

    -- 分页查询优化
SELECT * FROM large_table ORDER BY id LIMIT 10000, 20;
-- 优化为
SELECT * FROM large_table WHERE id > 10000 ORDER BY id LIMIT 20;

  3. 避免在 WHERE 子句中对字段进行表达式操作

    -- 不推荐（无法使用索引）
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 推荐
SELECT * FROM users 
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

  4. 使用 EXPLAIN 分析查询

    EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john';

  5. 利用覆盖索引

    -- 创建覆盖索引
CREATE INDEX idx_covering ON users(username, email);

-- 查询只使用索引中的字段
SELECT username, email FROM users WHERE username = 'john';

八、最佳实践

8.1 表设计规范

  1. 命名规范

    • 数据库名、表名、字段名使用小写字母
    • 使用下划线分隔单词（如 user_info）
    • 避免使用 SQL 保留字

  2. 字段选择

    • 选择合适的数据类型，优先选择更小的数据类型
    • 为字段设置合适的长度
    • 为经常查询的字段建立索引
    • 为外键字段建立索引

  3. 主键设计

    • 每个表都应该有一个主键
    • 优先考虑使用自增整数作为主键
    • 避免使用业务相关的字段作为主键

8.2 查询优化技巧

  1. 索引使用原则

    • 为 WHERE 子句中的条件字段创建索引
    • 为 ORDER BY 和 GROUP BY 字段创建索引
    • 避免在索引列上使用函数或表达式
    • 选择性高的列更适合创建索引

  2. 避免全表扫描

    • 确保查询条件能有效利用索引
    • 限制返回的字段数量
    • 合理使用 LIMIT

  3. 优化 JOIN 操作

    • 确保 JOIN 条件字段有索引
    • 小表驱动大表
    • 避免多表 JOIN

8.3 安全注意事项

  1. 防止 SQL 注入

    • 使用参数化查询
    • 避免拼接 SQL 语句
    • 对用户输入进行验证和过滤

  2. 权限管理

    • 为不同应用创建不同的数据库用户
    • 遵循最小权限原则
    • 定期审查用户权限

九、实用技巧

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;