跳到主要内容

SQL 语言详解

第3章 数据定义语言（DDL）​

3.1 数据库与表的创建、修改、删除​

数据库操作​

-- 创建数据库
CREATE DATABASE blog_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS blog_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 查看数据库创建信息
SHOW CREATE DATABASE blog_db;

-- 切换当前数据库
USE blog_db;

-- 修改数据库字符集
ALTER DATABASE blog_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 删除数据库
DROP DATABASE blog_db;

-- 如果存在则删除
DROP DATABASE IF EXISTS blog_db;

表的创建​

-- 基础语法
CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type [column_constraint],
    ...
    [table_constraints]
) [table_options];

-- 示例：创建用户表
CREATE TABLE users (
    -- 列定义：列名 数据类型 约束
    id INT UNSIGNED AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
    gender ENUM('male', 'female', 'other') DEFAULT 'other' COMMENT '性别',
    balance DECIMAL(10, 2) DEFAULT 0.00 COMMENT '余额',
    profile TEXT COMMENT '个人简介',
    is_active BOOLEAN DEFAULT TRUE COMMENT '是否激活',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    -- 表级约束
    PRIMARY KEY (id),
    UNIQUE KEY uk_email (email),
    KEY idx_username (username),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- 查看表结构
DESC users;
DESCRIBE users;
SHOW COLUMNS FROM users;

-- 查看建表语句
SHOW CREATE TABLE users;

-- 查看所有表
SHOW TABLES;
SHOW TABLES FROM blog_db;

表的修改​

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users ADD COLUMN nickname VARCHAR(50) COMMENT '昵称' FIRST;

-- 修改列类型
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);

-- 修改列名和类型
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30);

-- 删除列
ALTER TABLE users DROP COLUMN nickname;

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE KEY uk_username (username);

-- 添加普通索引
ALTER TABLE users ADD INDEX idx_age (age);

-- 添加复合索引
ALTER TABLE users ADD INDEX idx_age_gender (age, gender);

-- 删除索引
ALTER TABLE users DROP INDEX uk_username;

-- 修改表引擎
ALTER TABLE users ENGINE=MyISAM;

-- 修改表字符集
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改表注释
ALTER TABLE users COMMENT='用户信息表';

-- 重命名表
RENAME TABLE users TO user_info;
ALTER TABLE users RENAME TO user_info;

表的删除​

-- 删除表
DROP TABLE users;

-- 如果存在则删除
DROP TABLE IF EXISTS users;

-- 清空表数据（保留表结构）
TRUNCATE TABLE users;

-- 删除表并重建（等同于 TRUNCATE）
DROP TABLE users;
CREATE TABLE users (...);

DELETE vs TRUNCATE 对比：

特性DELETETRUNCATE
删除方式逐行删除删除并重建表
执行速度较慢快
自增ID继续原有值重置为1
事务支持支持（可回滚）不支持（不可回滚）
触发器激活触发器不激活触发器
返回值返回删除行数返回0
空间回收不回收（除非 OPTIMIZE）立即回收

3.2 数据类型​

数值类型​

整数类型：

类型字节数有符号范围无符号范围说明
TINYINT1-128 ~ 1270 ~ 255小整数
SMALLINT2-32768 ~ 327670 ~ 65535中等整数
MEDIUMINT3-8388608 ~ 83886070 ~ 16777215较大整数
INT / INTEGER4-2147483648 ~ 21474836470 ~ 4294967295标准整数
BIGINT8-2^63 ~ 2^63-10 ~ 2^64-1大整数
-- 整数类型示例
CREATE TABLE numbers (
    tiny_num TINYINT,
    small_num SMALLINT,
    medium_num MEDIUMINT,
    int_num INT,
    big_num BIGINT,

    -- 无符号类型
    age TINYINT UNSIGNED,

    -- 显示宽度（不影响存储范围，仅用于 ZEROFILL）
    code INT(4) ZEROFILL  -- 0012, 0123, 1234
);

-- 显示宽度示例
INSERT INTO numbers (code) VALUES (12);   -- 显示为 0012
INSERT INTO numbers (code) VALUES (1234); -- 显示为 1234

浮点类型：

类型字节数精度说明
FLOAT4单精度精度约7位小数
DOUBLE8双精度精度约15位小数
DECIMAL(M, D)变长精确数值M=总位数，D=小数位数
-- 浮点类型示例
CREATE TABLE prices (
    price1 FLOAT(7, 4),      -- 总共7位，4位小数：123.4567
    price2 DOUBLE(10, 2),    -- 总共10位，2位小数：12345678.12
    price3 DECIMAL(10, 2)    -- 精确数值，适合金额
);

-- 重要：浮点数精度问题
CREATE TABLE test_float (
    f FLOAT,
    d DOUBLE,
    dc DECIMAL(10, 4)
);

INSERT INTO test_float VALUES (0.1, 0.1, 0.1);
SELECT f + 0.2 FROM test_float;  -- 可能不是 0.3（浮点精度问题）
SELECT dc + 0.2 FROM test_float; -- 精确等于 0.3000

-- 最佳实践：金额字段使用 DECIMAL
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    amount DECIMAL(12, 2)  -- 最大 999999999999.99
);

位类型：

-- BIT 类型：位字段类型
CREATE TABLE bit_test (
    flags BIT(8)  -- 8个位，可以存储 8 个布尔值
);

-- 插入数据（使用二进制或十进制）
INSERT INTO bit_test VALUES (b'10101010');  -- 二进制
INSERT INTO bit_test VALUES (170);          -- 十进制：10101010 = 170

-- 查询时需要转换
SELECT BIN(flags) FROM bit_test;  -- 输出：10101010

字符串类型​

字符类型对比：

类型最大长度存储需求特点适用场景
CHAR(M)255 字符M 字符（固定）固定长度，补空格固定长度数据（MD5、手机号）
VARCHAR(M)65535 字节L+1 或 L+2 字节变长长度，不补空格变长字符串（姓名、地址）
TEXT65535 字节L+2 字节长文本文章内容、评论
MEDIUMTEXT16MBL+3 字节中等文本较长文本
LONGTEXT4GBL+4 字节超长文本超长文档
-- CHAR vs VARCHAR 示例
CREATE TABLE string_test (
    char_col CHAR(10),     -- 固定 10 字符
    varchar_col VARCHAR(10) -- 最多 10 字符
);

INSERT INTO string_test VALUES ('abc', 'abc');

-- 实际存储：
-- char_col: 'abc       ' (后面补7个空格)
-- varchar_col: 'abc' (不补空格)

-- CHAR 适合：
CREATE TABLE users (
    phone CHAR(11),        -- 手机号固定11位
    id_card CHAR(18),      -- 身份证固定18位
    md5_hash CHAR(32)      -- MD5固定32位
);

-- VARCHAR 适合：
CREATE TABLE articles (
    title VARCHAR(200),     -- 标题长度不定
    author VARCHAR(50),     -- 作者名长度不定
    summary VARCHAR(500)    -- 摘要长度不定
);

-- TEXT 适合：
CREATE TABLE posts (
    content TEXT,           -- 文章内容
    comments LONGTEXT       -- 超长评论
);

CHAR vs VARCHAR 性能对比：

特性CHARVARCHAR
存储空间可能浪费（固定长度）节省空间（变长）
查询性能稍快（固定长度）稍慢（需要读取长度信息）
更新性能快（不产生碎片）可能产生碎片
适用场景短且固定长度变长数据

二进制类型：

类型最大长度说明
BINARY(M)255 字节固定长度二进制字符串
VARBINARY(M)65535 字节变长二进制字符串
BLOB65535 字节二进制大对象
MEDIUMBLOB16MB中等二进制数据
LONGBLOB4GB超大二进制数据
-- BINARY vs CHAR
CREATE TABLE binary_test (
    binary_col BINARY(10),    -- 二进制存储，区分大小写
    char_col CHAR(10)         -- 字符存储，不区分大小写（取决于排序规则）
);

-- BLOB 适合存储图片、文件等
CREATE TABLE attachments (
    id INT PRIMARY KEY,
    file_name VARCHAR(255),
    file_data LONGBLOB,       -- 文件二进制数据
    file_size INT
);

-- 最佳实践：通常建议存储文件路径，而非文件本身
CREATE TABLE attachments (
    id INT PRIMARY KEY,
    file_name VARCHAR(255),
    file_path VARCHAR(500),   -- 存储 /uploads/files/xxx.pdf
    file_size INT
);

日期时间类型​

日期时间类型对比：

类型字节数格式范围时区用途
DATE3YYYY-MM-DD1000-01-01 ~ 9999-12-31无日期
TIME3HH:MM:SS-838:59:59 ~ 838:59:59无时间
DATETIME8YYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:59无日期时间
TIMESTAMP4YYYY-MM-DD HH:MM:SS1970-01-01 00:00:01 ~ 2038-01-19 03:14:07有时间戳
YEAR1YYYY1901 ~ 2155无年份
-- 日期时间示例
CREATE TABLE events (
    event_date DATE,
    event_time TIME,
    event_datetime DATETIME,
    event_timestamp TIMESTAMP,
    event_year YEAR
);

-- 插入当前时间
INSERT INTO events VALUES (
    CURDATE(),
    CURTIME(),
    NOW(),
    CURRENT_TIMESTAMP,
    YEAR(NOW())
);

-- DATETIME vs TIMESTAMP
CREATE TABLE test_time (
    dt DATETIME,
    ts TIMESTAMP
);

-- TIMESTAMP 特点：
-- 1. 自动时区转换
-- 2. 默认值为 CURRENT_TIMESTAMP
-- 3. 自动更新 ON UPDATE CURRENT_TIMESTAMP

CREATE TABLE users (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- DATETIME 特点：
-- 1. 不受时区影响
-- 2. 需要显式设置默认值
-- 3. 不会自动更新

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_time DATETIME DEFAULT CURRENT_TIMESTAMP,  -- MySQL 5.6.5+ 支持
    update_time DATETIME ON UPDATE CURRENT_TIMESTAMP
);

DATETIME vs TIMESTAMP 选择：

特性DATETIMETIMESTAMP
时区无有（自动转换）
范围1000-9999 年1970-2038 年
存储空间8 字节4 字节
自动初始化MySQL 5.6.5+支持
自动更新MySQL 5.6.5+支持
推荐场景业务时间（不受时区影响）记录时间、日志时间
-- 日期时间函数
SELECT NOW();              -- 当前日期时间：2024-01-15 10:30:45
SELECT CURDATE();          -- 当前日期：2024-01-15
SELECT CURTIME();          -- 当前时间：10:30:45
SELECT CURRENT_TIMESTAMP;  -- 当前时间戳

-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);      -- 明天
SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK);     -- 一周前
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);    -- 下个月
SELECT DATE_ADD(NOW(), INTERVAL '1-2' YEAR_MONTH); -- 1年2个月后

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');  -- 2024-01-15 10:30:45
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');       -- 2024年01月15日

-- 常用格式化符号
-- %Y：4位年份  %y：2位年份
-- %m：月份    %d：日期
-- %H：小时24  %h：小时12  %i：分钟  %s：秒
-- %W：星期名称  %w：星期数字(0=周日)

JSON 类型（MySQL 5.7.8+）​

-- JSON 类型示例
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON,      -- JSON 类型
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入 JSON 数据
INSERT INTO products (id, name, attributes) VALUES (
    1,
    'iPhone 15',
    JSON_OBJECT(
        'color', 'Space Gray',
        'storage', 256,
        'price', 7999,
        'features', JSON_ARRAY('Face ID', '5G', 'Wireless Charging')
    )
);

-- 查询 JSON 数据
SELECT
    id,
    name,
    attributes->>'$.color' AS color,          -- 提取标量值
    attributes->>'$.storage' AS storage,
    attributes->>'$.price' AS price,
    attributes->'$.features' AS features       -- 提取数组
FROM products;

-- 结果：
-- id | name      | color      | storage | price | features
-- 1  | iPhone 15 | Space Gray | 256     | 7999  | ["Face ID", "5G", "Wireless Charging"]

-- JSON 函数
SELECT JSON_EXTRACT(attributes, '$.color') FROM products WHERE id = 1;
SELECT JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color')) FROM products WHERE id = 1;

-- 更新 JSON 数据
UPDATE products
SET attributes = JSON_SET(attributes, '$.price', 8999)
WHERE id = 1;

UPDATE products
SET attributes = JSON_INSERT(attributes, '$.discount', 0.1)
WHERE id = 1;

UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.discount')
WHERE id = 1;

-- JSON 数组操作
SELECT JSON_ARRAY_LENGTH(attributes->'$.features') FROM products WHERE id = 1;
SELECT JSON_CONTAINS(attributes->'$.features', '"5G"') FROM products WHERE id = 1;

-- 在 JSON 字段上创建虚拟索引（MySQL 8.0.17+）
CREATE TABLE products_with_index (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON,
    color VARCHAR(20) AS (JSON_UNQUOTE(attributes->>'$.color')) STORED,
    INDEX idx_color (color)
);

-- JSON 类型优势：
-- 1. 灵活存储半结构化数据
-- 2. 无需预定义字段结构
-- 3. 支持索引和高效查询
-- 4. 适合存储配置、属性、日志等

3.3 约束​

主键约束（PRIMARY KEY）​

-- 主键：唯一标识表中的每一行记录
-- 特点：非空、唯一、一张表只能有一个主键

-- 列级主键
CREATE TABLE users1 (
    id INT PRIMARY KEY,
    username VARCHAR(50)
);

-- 表级主键（推荐，可以定义复合主键）
CREATE TABLE users2 (
    id INT,
    username VARCHAR(50),
    PRIMARY KEY (id)
);

-- 复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)  -- 联合主键
);

-- 自增主键（推荐）
CREATE TABLE users3 (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;

-- 主键设计最佳实践：
-- 1. 使用无业务含义的代理键（如自增ID）
-- 2. 避免使用有业务含义的字段作为主键（如手机号、身份证）
-- 3. 复合主键要考虑字段的顺序（查询最频繁的字段放前面）
-- 4. 主键字段最好设置为 NOT NULL

外键约束（FOREIGN KEY）​

-- 外键：建立表与表之间的关联关系，保证引用完整性

-- 创建主表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL
);

-- 创建从表，添加外键
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 外键命名（推荐）
CREATE TABLE employees1 (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    CONSTRAINT fk_emp_dept
        FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 外键级联操作
CREATE TABLE employees2 (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE CASCADE      -- 删除部门时级联删除员工
        -- ON DELETE SET NULL  -- 删除部门时将员工的dept_id设为NULL
        -- ON DELETE RESTRICT  -- 删除部门时如果有关联员工则阻止删除（默认）
        -- ON DELETE NO ACTION -- 同 RESTRICT
        ON UPDATE CASCADE      -- 更新部门ID时级联更新员工
);

-- 添加外键
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

-- 删除外键
ALTER TABLE employees DROP FOREIGN KEY fk_emp_dept;

-- 外键设计注意事项：
-- 1. 外键字段类型必须与引用字段类型完全一致
-- 2. 外键字段最好是索引（自动创建）
-- 3. 外键会影响插入、删除、更新的性能
-- 4. 高并发场景可能选择在应用层维护外键关系

唯一约束（UNIQUE）​

-- 唯一约束：保证字段值唯一，但允许NULL

-- 列级唯一约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

-- 表级唯一约束（可以定义复合唯一约束）
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email)
);

-- 复合唯一约束
CREATE TABLE user_follows (
    follower_id INT,
    followee_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_follow (follower_id, followee_id)  -- 防止重复关注
);

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE KEY uk_username (username);

-- 删除唯一约束
ALTER TABLE users DROP INDEX uk_username;

-- 唯一约束特点：
-- 1. 允许有多个 NULL（取决于 NULL 的数量）
-- 2. 自动创建唯一索引
-- 3. 与主键区别：主键只能有一个，唯一约束可以有多个

非空约束（NOT NULL）​

-- 非空约束：字段值不能为 NULL

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT,              -- 允许 NULL
    phone VARCHAR(20)      -- 允许 NULL
);

-- 修改字段为非空
ALTER TABLE users MODIFY COLUMN age INT NOT NULL;

-- 修改字段为允许 NULL
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NULL;

-- 非空约束最佳实践：
-- 1. 重要字段设置为 NOT NULL（如用户名、邮箱）
-- 2. 数值类型字段设置 DEFAULT 值，同时设为 NOT NULL
-- 3. 避免 NULL 以提高查询性能（NULL 需要额外空间）

检查约束（CHECK）​

-- 检查约束：自定义数据验证规则（MySQL 8.0.16+ 完整支持）

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18 AND age <= 65),
    salary DECIMAL(10, 2) CHECK (salary >= 0),
    gender ENUM('male', 'female') CHECK (gender IN ('male', 'female'))
);

-- 命名检查约束（MySQL 8.0.16+）
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    quantity INT,
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_quantity CHECK (quantity >= 0)
);

-- 添加检查约束
ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age >= 18);

-- 删除检查约束
ALTER TABLE employees DROP CONSTRAINT chk_age;

-- 检查约束应用场景：
-- 1. 年龄范围限制
-- 2. 价格、数量非负
-- 3. 枚举值验证
-- 4. 自定义业务规则

默认值约束（DEFAULT）​

-- 默认值：插入数据时如果不指定值，使用默认值

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 数值类型默认值
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2) DEFAULT 0.00,
    stock INT DEFAULT 0,
    sales INT DEFAULT 0
);

-- 添加默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'inactive';

-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- 默认值设计建议：
-- 1. 为常用字段设置合理的默认值
-- 2. 数值类型默认值为 0，字符串为空字符串 ''，布尔值为 FALSE
-- 3. 状态类字段默认值为初始状态（如 'pending'、'active'）

3.4 索引创建与管理​

索引基础​

-- 索引：提高查询性能的数据结构（B+树）

-- 主键索引（自动创建）
CREATE TABLE users (
    id INT PRIMARY KEY,  -- 自动创建主键索引
    username VARCHAR(50)
);

-- 唯一索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE  -- 自动创建唯一索引
);

-- 普通索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    INDEX idx_username (username)  -- 创建普通索引
);

-- 复合索引
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    status VARCHAR(20),
    INDEX idx_user_date (user_id, order_date)  -- 复合索引
);

-- 全文索引（仅适用于 MyISAM，MySQL 5.6+ InnoDB 也支持）
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_title_content (title, content)
);

-- 空间索引（用于空间数据类型）
CREATE TABLE locations (
    id INT PRIMARY KEY,
    location GEOMETRY,
    SPATIAL INDEX idx_location (location)
);

索引管理​

-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX uk_email ON users(email);

-- 添加索引
ALTER TABLE users ADD INDEX idx_username (username);
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);

-- 查看索引
SHOW INDEX FROM users;
SHOW INDEXES FROM users;

-- 删除索引
DROP INDEX idx_username ON users;
ALTER TABLE users DROP INDEX idx_username;

-- 分析索引使用情况
EXPLAIN SELECT * FROM users WHERE username = 'zhangsan';

索引设计原则​

适合创建索引的场景：

  1. 频繁作为 WHERE 条件的字段
  2. 经常用于 JOIN 连接的字段
  3. 经常用于 ORDER BY 排序的字段
  4. 经常用于 DISTINCT 去重的字段
  5. 外键字段

不适合创建索引的场景：

  1. 频繁更新的字段
  2. 数据重复度高的字段（如性别、状态）
  3. 表数据量小
  4. 很少查询的字段

复合索引设计原则（最左前缀原则）：

-- 创建复合索引 (a, b, c)
CREATE TABLE test (
    id INT PRIMARY KEY,
    a INT,
    b INT,
    c INT,
    INDEX idx_abc (a, b, c)
);

-- 可以使用索引的查询：
SELECT * FROM test WHERE a = 1;
SELECT * FROM test WHERE a = 1 AND b = 2;
SELECT * FROM test WHERE a = 1 AND b = 2 AND c = 3;
SELECT * FROM test WHERE a = 1 AND c = 3;  -- b 用了范围扫描或跳过

-- 不能使用索引的查询：
SELECT * FROM test WHERE b = 2;          -- 违反最左前缀
SELECT * FROM test WHERE c = 3;          -- 违反最左前缀
SELECT * FROM test WHERE b = 2 AND c = 3; -- 违反最左前缀

-- 排序可以使用索引
SELECT * FROM test WHERE a = 1 ORDER BY b, c;
SELECT * FROM test WHERE a = 1 ORDER BY b;  -- 部分使用

-- 最左前缀原则总结：
-- 1. 查询必须包含复合索引的第一列
-- 2. 跳过中间列会导致后面列无法使用索引
-- 3. 索引列的顺序要与查询条件的顺序匹配

聚簇索引 vs 非聚簇索引​

特性聚簇索引（主键索引）非聚簇索引（辅助索引）
数量一个（主键）多个
存储内容完整数据行索引列 + 主键值
查询速度快（一次IO）需要回表（两次IO）
排序数据按主键排序按索引列排序
-- InnoDB 引擎的索引结构：
-- 聚簇索引：主键索引的叶子节点存储完整数据行
-- 辅助索引：叶子节点存储索引列 + 主键值

-- 示例：
CREATE TABLE users (
    id INT PRIMARY KEY,           -- 聚簇索引
    username VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_username (username), -- 辅助索引
    INDEX idx_email (email)        -- 辅助索引
);

-- 查询过程：
-- 1. SELECT * FROM users WHERE id = 1;  -- 直接用聚簇索引，一次IO
-- 2. SELECT * FROM users WHERE username = 'zs'; -- 先查辅助索引，再回表查聚簇索引
-- 3. SELECT id FROM users WHERE username = 'zs';  -- 覆盖索引，无需回表

覆盖索引​

-- 覆盖索引：查询的列都在索引中，无需回表

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    age INT,
    email VARCHAR(100),
    INDEX idx_username_age (username, age)
);

-- 覆盖索引查询（推荐）
SELECT id, username, age FROM users WHERE username = 'zs';
-- 所有查询列都在索引中（id在聚簇索引，username和age在辅助索引）

-- 非覆盖索引查询
SELECT * FROM users WHERE username = 'zs';
-- email 不在索引中，需要回表查询

-- 优化：创建覆盖索引
CREATE INDEX idx_username_age_email ON users(username, age, email);

第4章 数据操作语言（DML）​

4.1 INSERT：插入数据​

单行插入​

-- 基础语法
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

-- 完整插入
INSERT INTO users (id, username, email, age) VALUES (1, 'zhangsan', 'zs@example.com', 25);

-- 省略列名（按表定义顺序插入，不推荐）
INSERT INTO users VALUES (2, 'lisi', 'li@example.com', 26, 'male');

-- 部分列插入（其他列使用默认值）
INSERT INTO users (username, email) VALUES ('wangwu', 'ww@example.com');

-- 使用 DEFAULT 关键字
INSERT INTO users (username, age, status) VALUES ('zhaoliu', 30, DEFAULT);

-- 使用函数
INSERT INTO users (username, created_at) VALUES ('sunqi', NOW());

批量插入​

-- 多值插入（推荐，性能高）
INSERT INTO users (username, email, age) VALUES
    ('user1', 'user1@example.com', 20),
    ('user2', 'user2@example.com', 21),
    ('user3', 'user3@example.com', 22);

-- 批量插入性能优化：
-- 1. 单次插入 100-1000 条记录
-- 2. 使用事务包裹批量插入
-- 3. 关闭索引：ALTER TABLE users DISABLE KEYS; 插入后启用
-- 4. 调整 bulk_insert_buffer_size

START TRANSACTION;
INSERT INTO users (username, age) VALUES
    ('user1', 20),
    ('user2', 21),
    -- ... 大量数据
    ('user1000', 30);
COMMIT;

INSERT ... SELECT​

-- 从查询结果插入数据
INSERT INTO users_backup (username, email, age)
SELECT username, email, age FROM users WHERE age > 18;

-- 复制表
CREATE TABLE users_copy LIKE users;
INSERT INTO users_copy SELECT * FROM users;

-- 跨表插入
INSERT INTO user_logs (user_id, action, created_at)
SELECT id, 'login', NOW() FROM users WHERE is_active = TRUE;

INSERT ... ON DUPLICATE KEY UPDATE​

-- 如果主键或唯一键冲突则更新，否则插入

CREATE TABLE counters (
    id INT PRIMARY KEY,
    name VARCHAR(50) UNIQUE,
    count INT DEFAULT 0
);

-- 插入或更新
INSERT INTO counters (id, name, count) VALUES (1, 'page_views', 1)
ON DUPLICATE KEY UPDATE count = count + 1;

-- 使用 VALUES 函数引用新值
INSERT INTO counters (id, name, count) VALUES (1, 'page_views', 100)
ON DUPLICATE KEY UPDATE count = VALUES(count) + 1;

-- 应用场景：统计计数、配置更新

REPLACE INTO​

-- 如果主键或唯一键冲突则删除旧记录再插入新记录

REPLACE INTO counters (id, name, count) VALUES (1, 'page_views', 1);

-- INSERT ON DUPLICATE KEY UPDATE vs REPLACE INTO：
-- INSERT ... ON DUPLICATE KEY UPDATE：更新现有记录
-- REPLACE INTO：删除旧记录，插入新记录（会重新生成自增ID）

4.2 UPDATE：更新数据​

单表更新​

-- 基础语法
UPDATE table_name SET column1 = value1, column2 = value2, ... [WHERE condition];

-- 更新单个字段
UPDATE users SET age = 26 WHERE id = 1;

-- 更新多个字段
UPDATE users SET age = 27, email = 'new@example.com' WHERE id = 1;

-- 使用表达式
UPDATE users SET age = age + 1 WHERE id = 1;
UPDATE products SET price = price * 0.9 WHERE category_id = 1;

-- 使用函数
UPDATE users SET updated_at = NOW() WHERE id = 1;

-- 使用 DEFAULT
UPDATE users SET status = DEFAULT WHERE id = 1;

多表关联更新​

-- 关联更新
UPDATE users u
JOIN departments d ON u.dept_id = d.dept_id
SET u.bonus = d.base_bonus * u.salary
WHERE d.dept_name = 'Sales';

-- 子查询更新
UPDATE users SET dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'IT') WHERE id = 1;

-- 多表更新示例
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'priority'
WHERE c.vip_level = 'gold';

批量更新​

-- CASE WHEN 批量更新
UPDATE products
SET price = CASE id
    WHEN 1 THEN 100
    WHEN 2 THEN 200
    WHEN 3 THEN 300
    ELSE price
END
WHERE id IN (1, 2, 3);

-- 批量更新优化建议：
-- 1. 使用事务
-- 2. 分批更新（每次 1000-5000 条）
-- 3. 避免在高峰期大批量更新
-- 4. 更新前备份

START TRANSACTION;
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';
COMMIT;

4.3 DELETE：删除数据​

删除数据​

-- 基础语法
DELETE FROM table_name [WHERE condition];

-- 删除指定记录
DELETE FROM users WHERE id = 1;

-- 条件删除
DELETE FROM users WHERE age < 18;
DELETE FROM users WHERE status = 'deleted' AND created_at < '2023-01-01';

-- 使用子查询
DELETE FROM users WHERE dept_id IN (SELECT dept_id FROM departments WHERE status = 'closed');

-- 限制删除数量
DELETE FROM users WHERE age < 18 LIMIT 10;

-- 排序删除
DELETE FROM users ORDER BY created_at ASC LIMIT 100;

TRUNCATE TABLE​

-- 清空表（保留表结构）
TRUNCATE TABLE users;

-- TRUNCATE vs DELETE：
-- TRUNCATE：快速，重置自增ID，不可回滚
-- DELETE FROM table：较慢，保留自增ID，可回滚

-- 等价于
DROP TABLE users;
CREATE TABLE users (...);

多表删除​

-- 关联删除
DELETE users
FROM users
JOIN departments ON users.dept_id = departments.dept_id
WHERE departments.status = 'closed';

-- 删除多个表的数据
DELETE users, orders
FROM users
JOIN orders ON users.id = orders.user_id
WHERE users.status = 'deleted';

-- 使用别名
DELETE u FROM users u
JOIN departments d ON u.dept_id = d.dept_id
WHERE d.status = 'closed';

4.4 事务性 DML 操作​

事务基础​

-- 事务：一组 SQL 操作，要么全部成功，要么全部失败

START TRANSACTION;  -- 或 BEGIN

UPDATE account SET balance = balance - 1000 WHERE id = 1;
UPDATE account SET balance = balance + 1000 WHERE id = 2;

-- 如果操作成功
COMMIT;

-- 如果出现错误
ROLLBACK;

-- 示例：银行转账
START TRANSACTION;

-- 检查余额
SELECT balance FROM account WHERE id = 1 FOR UPDATE;

-- 扣款
UPDATE account SET balance = balance - 1000 WHERE id = 1;
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
END IF;

-- 入账
UPDATE account SET balance = balance + 1000 WHERE id = 2;

COMMIT;

事务隔离级别​

-- 查看当前隔离级别
SELECT @@transaction_isolation;
SELECT @@tx_isolation;  -- MySQL 5.7-

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 全局设置
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 四种隔离级别：
-- 1. READ UNCOMMITTED（读未提交）：可能脏读
-- 2. READ COMMITTED（读已提交）：避免脏读，可能不可重复读
-- 3. REPEATABLE READ（可重复读）：避免脏读、不可重复读（MySQL默认）
-- 4. SERIALIZABLE（串行化）：最高隔离级别，性能差

保存点​

-- 保存点：在事务中设置标记，可以回滚到指定位置

START TRANSACTION;

INSERT INTO users VALUES (1, 'user1');
SAVEPOINT sp1;

INSERT INTO users VALUES (2, 'user2');
SAVEPOINT sp2;

INSERT INTO users VALUES (3, 'user3');

-- 回滚到 sp2（删除 user3）
ROLLBACK TO sp2;

-- 继续执行
INSERT INTO users VALUES (4, 'user4');

COMMIT;  -- 最终提交：user1, user2, user4

-- 释放保存点
RELEASE SAVEPOINT sp1;

隐式提交​

-- 某些语句会自动提交之前的事务（隐式提交）

START TRANSACTION;
INSERT INTO users VALUES (1, 'user1');
DDL LANGUAGE;  -- CREATE, ALTER, DROP 等语句会自动提交
-- 此时前一个事务已提交

-- 会隐式提交的语句：
-- DDL: CREATE, ALTER, DROP, TRUNCATE, RENAME
-- 权限: GRANT, REVOKE
-- 事务控制: BEGIN, START TRANSACTION
-- 锁: LOCK TABLES, UNLOCK TABLES
-- 其他: SET AUTOCOMMIT = 1

第5章 数据查询语言（DQL）​

5.1 SELECT 语句完整语法结构​

基础查询​

-- SELECT 完整语法
SELECT [DISTINCT]
    select_expr [, select_expr ...]
FROM table_name
[WHERE where_condition]
[GROUP BY {col_name | expr | position} [ASC | DESC], ...]
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

-- 基础查询
SELECT * FROM users;
SELECT id, username, email FROM users;

-- 去重查询
SELECT DISTINCT age FROM users;
SELECT DISTINCT gender, age FROM users;

WHERE 条件​

-- 比较运算符
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age <> 25;  -- 与 != 相同

-- 逻辑运算符
SELECT * FROM users WHERE age > 18 AND age < 65;
SELECT * FROM users WHERE age < 18 OR age > 65;
SELECT * FROM users WHERE NOT is_active;

-- 范围查询
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE age NOT BETWEEN 18 AND 65;

-- 集合查询
SELECT * FROM users WHERE age IN (18, 25, 30);
SELECT * FROM users WHERE age NOT IN (18, 25, 30);

-- 空值查询
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- 模糊查询
SELECT * FROM users WHERE username LIKE 'zhang%';   -- 以 'zhang' 开头
SELECT * FROM users WHERE username LIKE '%san';     -- 以 'san' 结尾
SELECT * FROM users WHERE username LIKE '%zhang%';  -- 包含 'zhang'
SELECT * FROM users WHERE username LIKE 'zhang_';   -- 'zhang' 后跟一个字符
SELECT * FROM users WHERE username LIKE 'zhang_san' ESCAPE '\';  -- 转义字符

-- 正则表达式（REGEXP）
SELECT * FROM users WHERE username REGEXP '^zhang';  -- 以 'zhang' 开头
SELECT * FROM users WHERE email REGEXP '@.*\\.com$';  -- 邮箱以 .com 结尾

ORDER BY 排序​

-- 单列排序
SELECT * FROM users ORDER BY age ASC;   -- 升序（默认）
SELECT * FROM users ORDER BY age DESC;  -- 降序

-- 多列排序
SELECT * FROM users ORDER BY age DESC, id ASC;

-- 表达式排序
SELECT * FROM users ORDER BY age - 10 DESC;

-- CASE WHEN 排序
SELECT * FROM users
ORDER BY
    CASE status
        WHEN 'active' THEN 1
        WHEN 'pending' THEN 2
        WHEN 'inactive' THEN 3
        ELSE 4
    END;

-- NULL 排序（NULL 最早）
SELECT * FROM users ORDER BY email ASC;  -- NULL 在最前
SELECT * FROM users ORDER BY email IS NULL, email ASC;  -- NULL 在最后

LIMIT 分页​

-- 限制结果数量
SELECT * FROM users LIMIT 10;

-- 分页查询（从第 0 条开始，取 10 条）
SELECT * FROM users LIMIT 0, 10;
SELECT * FROM users LIMIT 10 OFFSET 0;

-- 分页公式
-- 页码：page，每页数量：pageSize
-- LIMIT (page - 1) * pageSize, pageSize

-- 第1页（每页10条）
SELECT * FROM users LIMIT 0, 10;

-- 第2页
SELECT * FROM users LIMIT 10, 10;

-- 第3页
SELECT * FROM users LIMIT 20, 10;

-- 深分页优化（使用子查询或延迟关联）
SELECT * FROM users
WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 10000, 1)
LIMIT 10;

5.2 聚合函数与分组查询​

聚合函数​

-- COUNT：计数
SELECT COUNT(*) FROM users;              -- 所有行数
SELECT COUNT(id) FROM users;             -- 非NULL的行数
SELECT COUNT(DISTINCT age) FROM users;   -- 去重计数

-- SUM：求和
SELECT SUM(age) FROM users;
SELECT SUM(price * quantity) FROM order_items;

-- AVG：平均
SELECT AVG(age) FROM users;
SELECT ROUND(AVG(score), 2) FROM exams;

-- MIN/MAX：最小值/最大值
SELECT MIN(age) FROM users;
SELECT MAX(salary) FROM employees;

-- GROUP_CONCAT：分组连接（MySQL特有）
SELECT dept_id, GROUP_CONCAT(username) FROM users GROUP BY dept_id;
SELECT dept_id, GROUP_CONCAT(username ORDER BY id DESC SEPARATOR '|') FROM users GROUP BY dept_id;

GROUP BY 分组​

-- 单列分组
SELECT gender, COUNT(*) FROM users GROUP BY gender;

-- 多列分组
SELECT dept_id, gender, AVG(salary) FROM employees GROUP BY dept_id, gender;

-- 分组后的条件（HAVING）
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING avg_salary > 5000;

-- WHERE vs HAVING
-- WHERE：分组前过滤（不能使用聚合函数）
-- HAVING：分组后过滤（可以使用聚合函数）

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 3000  -- 先过滤
GROUP BY dept_id
HAVING avg_salary > 5000;  -- 后过滤

-- GROUP BY 的特殊用法（MySQL特有）
-- SELECT 中的非聚合列可以不在 GROUP BY 中（不推荐）
SELECT id, username, dept_id FROM users GROUP BY dept_id;
-- 这会返回每个分组的第一条记录（不确定）

ROLLUP、CUBE（MySQL 8.0+）​

-- GROUPING SETS
SELECT dept_id, gender, COUNT(*) AS cnt
FROM employees
GROUP BY GROUPING SETS (
    (dept_id, gender),  -- 按部门和性别分组
    (dept_id),          -- 按部门分组
    ()                  -- 总计
);

-- ROLLUP：层级分组
SELECT year, quarter, SUM(revenue) AS total
FROM sales
GROUP BY year, quarter WITH ROLLUP;
-- 等价于 GROUPING SETS ((year, quarter), (year), ())

-- CUBE：全维度分组
SELECT year, quarter, region, SUM(revenue) AS total
FROM sales
GROUP BY year, quarter, region WITH CUBE;
-- 等价于 GROUPING SETS (
--     (year, quarter, region), (year, quarter), (year, region),
--     (quarter, region), (year), (quarter), (region), ()
-- )

5.3 多表连接​

INNER JOIN：内连接​

-- 内连接：只返回匹配的行

SELECT u.username, o.order_id, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 等价于
SELECT u.username, o.order_id, o.order_date
FROM users u, orders o
WHERE u.id = o.user_id;

-- 多表内连接
SELECT u.username, o.order_id, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

LEFT JOIN：左外连接​

-- 左外连接：返回左表所有行，右表匹配不到的显示NULL

SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 应用场景：查询没有订单的用户
SELECT u.username
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NULL;

-- 多表左连接
SELECT u.username, o.order_id, oi.product_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id;

RIGHT JOIN：右外连接​

-- 右外连接：返回右表所有行，左表匹配不到的显示NULL

SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 应用场景：查询没有用户的订单（异常数据）
SELECT o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL;

CROSS JOIN：交叉连接​

-- 交叉连接：返回笛卡尔积（左表每一行 × 右表每一行）

SELECT u.username, p.product_name
FROM users u
CROSS JOIN products p;

-- 等价于
SELECT u.username, p.product_name
FROM users u, products p;

-- 应用场景：生成所有组合（如用户 × 商品）

SELF JOIN：自连接​

-- 自连接：表与自身连接

-- 查询员工及其领导
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 查询同部门的所有员工
SELECT
    e1.name AS employee1,
    e2.name AS employee2
FROM employees e1
JOIN employees e2 ON e1.dept_id = e2.dept_id
WHERE e1.id < e2.id;

多表连接示例​

-- 综合示例：查询用户订单详情
SELECT
    u.username,
    o.order_id,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
ORDER BY o.order_date DESC
LIMIT 10;

-- 复杂连接：查询部门平均工资高于公司平均工资的部门
SELECT
    d.dept_name,
    AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING AVG(e.salary) > (
    SELECT AVG(salary) FROM employees
);

5.4 子查询​

标量子查询（返回单行单列）​

-- 标量子查询：返回单个值

-- 查询工资高于平均工资的员工
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 查询年龄最大的用户
SELECT * FROM users
WHERE age = (SELECT MAX(age) FROM users);

-- WHERE 中的子查询
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- HAVING 中的子查询
SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (
    SELECT AVG(salary) FROM employees
);

-- SELECT 中的子查询
SELECT
    username,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

列子查询（返回单列多行）​

-- 列子查询：返回一列多个值

-- 使用 IN
SELECT * FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'Beijing');

-- 使用 NOT IN
SELECT * FROM employees
WHERE dept_id NOT IN (SELECT dept_id FROM departments WHERE status = 'active');

-- 使用 ANY / SOME
SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 1);
-- 等价于 salary > (SELECT MIN(salary) FROM employees WHERE dept_id = 1)

-- 使用 ALL
SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 1);
-- 等价于 salary > (SELECT MAX(salary) FROM employees WHERE dept_id = 1)

行子查询（返回单行多列）​

-- 行子查询：返回一行多列

-- 查询与指定用户部门和性别都相同的用户
SELECT * FROM users
WHERE (dept_id, gender) = (SELECT dept_id, gender FROM users WHERE id = 1);

-- 使用行构造器
SELECT * FROM users
WHERE (dept_id, gender) = (1, 'male');

-- 查询满足多个条件的记录
SELECT * FROM products
WHERE (category_id, price) = (
    SELECT category_id, MAX(price)
    FROM products
    GROUP BY category_id
    LIMIT 1
);

表子查询（返回多行多列）​

-- 表子查询：返回多行多列（临时表）

-- FROM 中的子查询
SELECT * FROM (
    SELECT username, email, age FROM users WHERE age > 18
) AS adult_users
WHERE age < 65;

-- 必须使用别名
SELECT u.* FROM (
    SELECT * FROM users ORDER BY created_at DESC
) AS u
LIMIT 10;

-- JOIN 中的子查询
SELECT u.username, o.order_count
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;

-- 子查询与临时表
-- 对于复杂的子查询，可以使用临时表提高可读性
CREATE TEMPORARY TABLE temp_orders AS
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

SELECT u.username, o.order_count
FROM users u
JOIN temp_orders o ON u.id = o.user_id;

DROP TEMPORARY TABLE temp_orders;

相关子查询 vs 非相关子查询​

-- 非相关子查询：子查询不依赖外层查询（执行一次）
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 相关子查询：子查询依赖外层查询（每行执行一次）
SELECT * FROM employees e
WHERE salary > (
    SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id
);

-- 相关子查询优化：改用 JOIN
SELECT e.*
FROM employees e
JOIN (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) avg_salaries ON e.dept_id = avg_salaries.dept_id
WHERE e.salary > avg_salaries.avg_salary;

EXISTS 和 NOT EXISTS​

-- EXISTS：判断子查询是否返回数据
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 等价于 INNER JOIN
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id;

-- NOT EXISTS：判断子查询是否不返回数据
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 应用场景：查询没有订单的用户
-- NOT EXISTS vs LEFT JOIN ... IS NULL
-- NOT EXISTS 语义更清晰，性能可能更好

5.5 窗口函数（MySQL 8.0+）​

窗口函数基础​

-- 窗口函数：在不聚合数据的情况下进行计算

-- 语法
function_name OVER ([PARTITION BY ...] [ORDER BY ...] [WINDOW ...])

-- 示例数据
CREATE TABLE sales (
    id INT,
    department VARCHAR(50),
    employee VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10, 2)
);

-- ROW_NUMBER：行号（相同值会不同编号）
SELECT
    department,
    employee,
    amount,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY amount DESC) AS row_num
FROM sales;

-- RANK：排名（相同值相同编号，跳过后续编号）
SELECT
    department,
    employee,
    amount,
    RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS rank_num
FROM sales;

-- DENSE_RANK：排名（相同值相同编号，不跳过后续编号）
SELECT
    department,
    employee,
    amount,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS dense_rank
FROM sales;

排名函数对比​

-- 示例数据：amount = 1000, 1000, 800, 800, 600
SELECT
    employee,
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,    -- 1, 2, 3, 4, 5
    RANK() OVER (ORDER BY amount DESC) AS rank_num,        -- 1, 1, 3, 3, 5
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank -- 1, 1, 2, 2, 3
FROM sales;

聚合窗口函数​

-- SUM 窗口函数：计算累计总和
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total,
    SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total2
FROM sales;

-- AVG 窗口函数：计算移动平均
SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 3天移动平均
    AS moving_avg
FROM sales;

-- 其他聚合窗口函数
SELECT
    employee,
    amount,
    SUM(amount) OVER (PARTITION BY department) AS dept_total,
    AVG(amount) OVER (PARTITION BY department) AS dept_avg,
    COUNT(*) OVER (PARTITION BY department) AS dept_count,
    MAX(amount) OVER (PARTITION BY department) AS dept_max,
    MIN(amount) OVER (PARTITION BY department) AS dept_min
FROM sales;

取值函数​

-- LAG：获取前N行的值
SELECT
    sale_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_amount,
    LAG(amount, 2) OVER (ORDER BY sale_date) AS prev_amount_2,
    amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS diff
FROM sales;

-- LEAD：获取后N行的值
SELECT
    sale_date,
    amount,
    LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_amount,
    LEAD(amount, 2) OVER (ORDER BY sale_date) AS next_amount_2
FROM sales;

-- FIRST_VALUE：分组第一个值
SELECT
    department,
    employee,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY department
        ORDER BY amount DESC
    AS max_amount
FROM sales;

-- LAST_VALUE：分组最后一个值
SELECT
    department,
    employee,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY department
        ORDER BY amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    AS min_amount
FROM sales;

-- NTH_VALUE：分组第N个值
SELECT
    employee,
    amount,
    NTH_VALUE(amount, 2) OVER (
        ORDER BY amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    AS second_highest
FROM sales;

窗口框架（WINDOW FRAME）​

-- ROWS BETWEEN：定义窗口范围

-- 从开始到当前行
SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

-- 从前1行到后1行（共3行）
AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

-- 从前3行到当前行
AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
)

-- 从当前行到最后一行
SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)

-- 所有行
COUNT(*) OVER (
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

-- 窗口框架语法：
-- UNBOUNDED PRECEDING：分组第一行
-- UNBOUNDED FOLLOWING：分组最后一行
-- CURRENT ROW：当前行
-- N PRECEDING：前N行
-- N FOLLOWING：后N行

窗口函数命名​

-- 使用 WINDOW 子句简化代码
SELECT
    employee,
    amount,
    SUM(amount) OVER w AS running_total,
    AVG(amount) OVER w AS running_avg,
    ROW_NUMBER() OVER w AS row_num
FROM sales
WINDOW w AS (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

窗口函数应用场景​

-- 1. 计算累计销售额
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS cumulative_amount
FROM sales;

-- 2. 计算同比增长
SELECT
    MONTH(sale_date) AS month,
    SUM(amount) AS monthly_sales,
    LAG(SUM(amount), 12) OVER (ORDER BY MONTH(sale_date)) AS last_year_sales,
    SUM(amount) - LAG(SUM(amount), 12) OVER (ORDER BY MONTH(sale_date)) AS yoy_growth
FROM sales
GROUP BY MONTH(sale_date);

-- 3. 部门内排名
SELECT
    department,
    employee,
    amount,
    RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS dept_rank
FROM sales;

-- 4. 找出前20%的客户
SELECT * FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_amount,
        NTILE(5) OVER (ORDER BY SUM(amount) DESC) AS customer_tier
    FROM sales
    GROUP BY customer_id
) ranked
WHERE customer_tier = 1;

-- 5. 计算环比变化
SELECT
    sale_date,
    amount,
    LAG(amount) OVER (ORDER BY sale_date) AS prev_amount,
    amount - LAG(amount) OVER (ORDER BY sale_date) AS change,
    (amount - LAG(amount) OVER (ORDER BY sale_date)) / LAG(amount) OVER (ORDER BY sale_date) * 100 AS change_percent
FROM sales;

小结​

本章深入讲解了 SQL 语言的 DDL、DML、DQL 三大类操作：

第3章 - DDL 数据定义语言重点：

  • 掌握数据库、表的创建、修改、删除操作
  • 理解各种数据类型的特点和适用场景（数值、字符串、日期时间、JSON）
  • 熟练使用各类约束（主键、外键、唯一、非空、检查、默认值）
  • 理解索引原理和设计原则（聚簇索引、辅助索引、复合索引、覆盖索引）

第4章 - DML 数据操作语言重点：

  • 掌握 INSERT 的各种插入方式（单行、批量、INSERT SELECT）
  • 理解 UPDATE 的单表和多表更新
  • 掌握 DELETE 和 TRUNCATE 的区别
  • 熟练使用事务处理数据一致性

第5章 - DQL 数据查询语言重点：

  • 掌握 SELECT 完整语法结构和执行顺序
  • 熟练使用聚合函数和 GROUP BY、HAVING
  • 掌握各种连接方式（INNER JOIN、LEFT JOIN、RIGHT JOIN、CROSS JOIN）
  • 理解子查询的分类和应用场景
  • 掌握窗口函数的使用（MySQL 8.0+）

下章预告： 下一章将学习 MySQL 高级查询技巧，包括视图、存储过程、触发器、自定义函数等内容。

最后由 Embrace 于 更新