SQL 核心技术详解：从入门到实战

m0_73627883

340人浏览 · 2026-05-06 22:36:13

m0_73627883 · 2026-05-06 22:36:13 发布

前言：SQL（Structured Query Language）是用于管理关系型数据库的标准编程语言。无论是Web开发、数据分析还是后端服务，SQL都是程序员必备的核心技能之一。本文将系统讲解SQL的核心语法，配合大量实战示例，帮助读者快速掌握数据库查询与操作。

目录导航

一、数据库基础操作
二、数据表操作
三、数据查询基础
四、数据操纵语言（DML）
五、WHERE条件查询
六、排序与分页
七、聚合函数详解
八、分组查询GROUP BY
九、多表查询
十、子查询
十一、约束详解
十二、索引原理与优化
十三、视图
十四、事务控制
十五、常用函数速查
十六、实战技巧与最佳实践

一、数据库基础操作

1.1 什么是数据库

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。常见的关系型数据库有：

MySQL - 开源免费，广泛应用于Web开发
PostgreSQL - 功能强大的开源数据库
Oracle - 企业级商业数据库
SQL Server - 微软出品的企业级数据库
SQLite - 轻量级嵌入式数据库

1.2 创建数据库

-- 最简单的创建方式
CREATE DATABASE mydb;

-- 生产环境推荐：指定字符集和排序规则
-- utf8mb4 支持完整的Unicode字符（包括emoji）
-- utf8mb4_unicode_ci 排序规则，兼容性和准确性更好
CREATE DATABASE mydb
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

为什么要指定字符集？ 如果不指定，默认字符集可能不支持中文或emoji，导致乱码。推荐始终使用 utf8mb4。

1.3 查看和选择数据库

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

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

-- 选择（切换）数据库
USE mydb;

-- 查看当前使用的数据库
SELECT DATABASE();

1.4 删除数据库

-- 删除数据库（危险操作！数据无法恢复）
DROP DATABASE mydb;

-- 安全写法：如果存在才删除
DROP DATABASE IF EXISTS mydb;

警告：删除数据库是不可逆的操作，所有数据将永久丢失，生产环境中务必谨慎！

二、数据表操作

2.1 表的概念

表（Table）是数据库中最基本的数据存储单位，由**行（Row）和列（Column）**组成：

行代表一条记录，如一个用户信息
列代表一个字段，如用户的姓名、年龄

2.2 MySQL 数据类型详解

在创建表之前，需要了解常用的数据类型：

数值类型

类型	占用空间	取值范围	说明
TINYINT	1字节	-128 ~ 127	小整数，如状态码
SMALLINT	2字节	-32768 ~ 32767	中等整数
INT	4字节	-21亿 ~ 21亿	常规整数（常用）
BIGINT	8字节	很大	大整数，如ID
FLOAT	4字节	-	单精度浮点
DOUBLE	8字节	-	双精度浮点
DECIMAL(10,2)	变长	-	精确小数，用于金额

建议：金额类数据务必使用 DECIMAL，浮点数计算会有精度丢失！

字符串类型

类型	最大长度	说明
CHAR(n)	255	固定长度，不足补空格
VARCHAR(n)	65535	可变长度（常用）
TEXT	65535字节	长文本
MEDIUMTEXT	16MB	较长文本
LONGTEXT	4GB	极大文本

VARCHAR vs CHAR：VARCHAR 更节省空间，但 CHAR 查询速度略快。短固定长度字段（如手机号、邮编）用 CHAR 更合适。

日期时间类型

类型	格式	说明
DATE	2024-01-15	仅日期
TIME	12:30:45	仅时间
DATETIME	2024-01-15 12:30:45	日期+时间，范围大
TIMESTAMP	2024-01-15 12:30:45	时间戳，自动更新

DATETIME vs TIMESTAMP：

DATETIME 占用8字节，范围 1000-9999年

TIMESTAMP 占用4字节，范围 1970-2038年，会自动更新

2.3 创建表

-- 创建用户表
CREATE TABLE users (
    -- 主键：唯一标识每条记录
    id          BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',

    -- 用户名：非空、唯一
    username    VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',

    -- 密码：加密存储
    password    VARCHAR(255) NOT NULL COMMENT '密码',

    -- 邮箱：可为空
    email       VARCHAR(100) COMMENT '邮箱',

    -- 年龄：TINYINT 足够
    age         TINYINT UNSIGNED COMMENT '年龄',

    -- 性别：枚举值
    gender      TINYINT DEFAULT 1 COMMENT '1男2女0未知',

    -- 状态：1启用0禁用
    status      TINYINT DEFAULT 1 COMMENT '状态',

    -- 创建时间：自动记录
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

    -- 更新时间：记录最后修改时间
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表';

关键参数说明：

PRIMARY KEY：主键，唯一且非空
AUTO_INCREMENT：自增，主键使用
UNSIGNED：无符号，如年龄不能为负数
DEFAULT：默认值
COMMENT：字段注释
ENGINE=InnoDB：存储引擎，支持事务

2.4 查看表结构

-- 查看所有表
SHOW TABLES;

-- 查看表结构（两种写法效果相同）
DESC users;
DESCRIBE users;

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

2.5 修改表结构

-- 添加新字段
ALTER TABLE users ADD phone VARCHAR(20) COMMENT '手机号';
ALTER TABLE users ADD address VARCHAR(200) AFTER email;  -- 插入到email之后

-- 修改字段类型或约束
ALTER TABLE users MODIFY phone VARCHAR(11) NOT NULL;

-- 重命名字段（同时可修改类型）
ALTER TABLE users CHANGE phone mobile VARCHAR(11) COMMENT '手机号';

-- 删除字段
ALTER TABLE users DROP mobile;

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

-- 修改表名
ALTER TABLE users RENAME TO user_info;

2.6 删除表和清空表

-- 删除表（结构和数据都删除）
DROP TABLE users;
DROP TABLE IF EXISTS users;  -- 安全写法

-- 清空表（删除所有数据，保留结构，自增重新开始）
TRUNCATE TABLE users;

DROP vs TRUNCATE vs DELETE：

DROP：删除表结构 + 数据 + 索引

TRUNCATE：删除所有数据，保留结构，自增重置，速度快

DELETE：可带WHERE条件删除，速度慢，可回滚

三、数据查询基础

3.1 最简单的查询

-- 查询表中所有数据（生产环境慎用！）
SELECT * FROM users;

-- 查询指定字段
SELECT username, email, age FROM users;

-- 给字段起别名（AS可省略）
SELECT username AS '用户名', email AS '邮箱', age AS '年龄' FROM users;

-- 带表达式的查询
SELECT username, age, age + 10 AS '10年后年龄' FROM users;
SELECT username, price, price * quantity AS '总价' FROM orders;

3.2 去重查询

-- 查询所有不同的年龄
SELECT DISTINCT age FROM users;

-- 多个字段组合去重
SELECT DISTINCT status, gender FROM users;

3.3 拼接字符串

-- MySQL拼接
SELECT CONCAT(username, ' (', email, ')') AS user_info FROM users;

-- 添加分隔符拼接
SELECT CONCAT_WS(' - ', username, email, age) AS info FROM users;

四、数据操纵语言（DML）

4.1 插入数据 INSERT

-- 插入单条数据（指定字段）
INSERT INTO users (username, password, email, age)
VALUES ('张三', '123456', 'zhangsan@example.com', 25);

-- 插入单条数据（所有字段，可省略字段名）
INSERT INTO users VALUES (NULL, '李四', 'abcdef', 'lisi@example.com', 30, 1, 1, NOW(), NOW());

-- 批量插入（推荐，性能更好）
INSERT INTO users (username, password, email, age) VALUES
('王五', 'pass123', 'wangwu@example.com', 28),
('赵六', 'pass456', 'zhaoliu@example.com', 35),
('孙七', 'pass789', 'sunqi@example.com', 22);

4.2 更新数据 UPDATE

-- 基本语法
UPDATE users SET email = 'new@example.com' WHERE id = 1;

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

-- 按条件批量更新
UPDATE users SET status = 0 WHERE age > 80;

重要提醒：UPDATE 语句必须带 WHERE 条件！否则会更新表中所有数据！

4.3 删除数据 DELETE

-- 删除单条数据
DELETE FROM users WHERE id = 1;

-- 删除多条数据
DELETE FROM users WHERE id IN (3, 5, 7);

-- 删除满足条件的数据
DELETE FROM users WHERE age < 18 AND status = 0;

重要提醒：DELETE 语句必须带 WHERE 条件！否则会删除表中所有数据！

4.4 插入或更新（ON DUPLICATE KEY UPDATE）

-- 如果不存在则插入，存在则更新
INSERT INTO user_stats (user_id, login_count)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1;

五、WHERE条件查询

WHERE 子句用于筛选满足条件的记录，是SQL中最常用的子句之一。

5.1 比较运算符

-- 等于（数值）
SELECT * FROM users WHERE age = 25;

-- 等于（字符串，需要加引号）
SELECT * FROM users WHERE username = '张三';

-- 不等于
SELECT * FROM users WHERE age <> 30;
SELECT * FROM users WHERE age != 30;

-- 大于、小于、大于等于、小于等于
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age < 60;
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE age <= 100;

5.2 逻辑运算符

-- AND：所有条件都满足
SELECT * FROM users WHERE age >= 18 AND status = 1;

-- OR：满足任一条件
SELECT * FROM users WHERE status = 1 OR status = 2;

-- NOT：取反
SELECT * FROM users WHERE NOT status = 0;

-- 组合使用
SELECT * FROM users
WHERE (age >= 18 AND age <= 60) OR (status = 1 AND gender = 1);

5.3 范围查询

-- BETWEEN...AND：闭区间 [min, max]
SELECT * FROM users WHERE age BETWEEN 18 AND 60;

-- IN：在指定列表中
SELECT * FROM users WHERE status IN (1, 2, 3);
SELECT * FROM users WHERE username IN ('张三', '李四', '王五');

-- NOT IN：不在列表中
SELECT * FROM users WHERE status NOT IN (0, -1);

5.4 空值判断

-- IS NULL：为空
SELECT * FROM users WHERE email IS NULL;

-- IS NOT NULL：不为空
SELECT * FROM users WHERE email IS NOT NULL;

-- 常见错误写法（不会返回预期结果）
SELECT * FROM users WHERE email = '';  -- 空字符串不等于NULL
SELECT * FROM users WHERE email = NULL;  -- 永远为假！

5.5 模糊查询 LIKE

模糊查询用于匹配字符串模式：

通配符	说明	示例
%	匹配任意字符（0个或多个）	‘张%’ 匹配张三、张三丰
_	匹配单个字符	‘张_’ 匹配张三

-- 以某字符开头
SELECT * FROM users WHERE username LIKE '张%';

-- 以某字符结尾
SELECT * FROM users WHERE email LIKE '%@qq.com';

-- 包含某字符
SELECT * FROM users WHERE username LIKE '%三%';

-- 固定长度匹配（查询姓张且名字两个字的用户）
SELECT * FROM users WHERE username LIKE '张_';

-- 某个位置是特定字符
SELECT * FROM users WHERE phone LIKE '138%';

-- ESCAPE 转义特殊字符（查询包含%的内容）
SELECT * FROM users WHERE description LIKE '%\%%' ESCAPE '\';

5.6 综合示例

-- 查询条件组合示例
SELECT id, username, email, age, status
FROM users
WHERE status = 1                       -- 状态正常
  AND age BETWEEN 18 AND 60           -- 年龄在18-60之间
  AND email IS NOT NULL                -- 邮箱不为空
  AND username LIKE '张%'              -- 姓张
ORDER BY age DESC                      -- 按年龄降序
LIMIT 10;                              -- 取前10条

六、排序与分页

6.1 排序 ORDER BY

-- 升序排序（ASC，可省略，默认就是升序）
SELECT * FROM users ORDER BY age ASC;

-- 降序排序（DESC）
SELECT * FROM users ORDER BY age DESC;

-- 按多个字段排序（先按第一个字段排，相同时按第二个字段排）
SELECT * FROM users ORDER BY status ASC, age DESC;

-- 按计算结果排序
SELECT username, price, quantity, price * quantity AS total
FROM orders
ORDER BY total DESC;

-- 按字段位置排序（不推荐，含义不明确）
SELECT * FROM users ORDER BY 3;  -- 按第3个字段排序

6.2 分页查询 LIMIT

-- 查询前N条记录
SELECT * FROM users LIMIT 10;

-- 跳过前M条，查询N条（OFFSET M 可省略）
SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT * FROM users LIMIT 20, 10;  -- 同样效果，查询第21-30条

-- 经典分页：第page页，每页pageSize条
-- 第1页
SELECT * FROM users LIMIT 0, 10;
-- 第2页
SELECT * FROM users LIMIT 10, 10;
-- 第3页
SELECT * FROM users LIMIT 20, 10;

-- 计算公式：LIMIT (page-1) * pageSize, pageSize

6.3 排序分页组合

-- 查询年龄最大的10个用户
SELECT * FROM users
ORDER BY age DESC
LIMIT 10;

-- 查询第2页的用户（每页5条）
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 5, 5;

七、聚合函数详解

聚合函数用于对一组值进行计算并返回单个值。

7.1 常用聚合函数

函数	说明	示例
COUNT()	统计数量	COUNT(*) 统计行数，COUNT(column) 统计非空值
SUM()	求和	SUM(score)
AVG()	平均值	AVG(price)
MAX()	最大值	MAX(age)
MIN()	最小值	MIN(created_at)

-- 统计总用户数
SELECT COUNT(*) FROM users;

-- 统计有邮箱的用户数（忽略NULL）
SELECT COUNT(email) FROM users;

-- 统计不同状态的用户数
SELECT COUNT(DISTINCT status) FROM users;

-- 统计年龄总和
SELECT SUM(age) FROM users;

-- 统计平均年龄
SELECT AVG(age) FROM users;

-- 保留2位小数
SELECT ROUND(AVG(age), 2) FROM users;

-- 统计年龄最大值和最小值
SELECT MAX(age), MIN(age) FROM users;

7.2 聚合函数与WHERE结合

-- 统计启用状态的用户数
SELECT COUNT(*) FROM users WHERE status = 1;

-- 统计启用用户的平均年龄
SELECT AVG(age) FROM users WHERE status = 1;

八、分组查询GROUP BY

GROUP BY 用于将数据按照一个或多个字段进行分组。

8.1 基本用法

-- 按性别分组统计人数
SELECT gender, COUNT(*) as count
FROM users
GROUP BY gender;

-- 按状态分组统计
SELECT status, COUNT(*) as count
FROM users
GROUP BY status;

8.2 分组后筛选 HAVING

WHERE vs HAVING 的区别：

WHERE：在分组前筛选，不能使用聚合函数

HAVING：在分组后筛选，可以使用聚合函数

-- 错误写法：WHERE 不能使用 COUNT()
-- SELECT gender, COUNT(*) FROM users WHERE COUNT(*) > 10 GROUP BY gender;

-- 正确写法：使用 HAVING
SELECT gender, COUNT(*) as count
FROM users
GROUP BY gender
HAVING count > 5;

-- 统计各状态用户数，只显示超过10人的状态
SELECT status, COUNT(*) as user_count
FROM users
GROUP BY status
HAVING user_count > 10;

-- 统计各部门平均工资，只显示平均工资>5000的部门
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id
HAVING avg_salary > 5000;

8.3 完整示例

-- 统计各状态用户数、年龄总和、平均年龄
-- 只显示用户数>=3的状态
SELECT
    status,
    COUNT(*) as user_count,
    SUM(age) as total_age,
    ROUND(AVG(age), 1) as avg_age
FROM users
WHERE status IN (1, 2)       -- 先筛选
GROUP BY status              -- 再分组
HAVING user_count >= 3       -- 最后筛选分组结果
ORDER BY user_count DESC;    -- 按人数降序

九、多表查询

9.1 连接的类型概述

多表查询是SQL中最重要也最复杂的部分，主要包括：

连接类型	说明
INNER JOIN	只返回两表匹配的记录
LEFT JOIN	返回左表全部记录，右表无匹配则为NULL
RIGHT JOIN	返回右表全部记录，左表无匹配则为NULL
FULL OUTER JOIN	返回两表全部记录

9.2 内连接 INNER JOIN

内连接只返回两个表中都存在的记录。

-- 标准语法
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- 隐式内连接（不推荐，条件分散）
SELECT e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

-- 查询结果示例：
-- 员工表：id=1, name='张三', dept_id=1
-- 部门表：id=1, dept_name='技术部', id=2, dept_name='市场部'
-- 结果：张三 - 技术部（只有匹配上的才显示）

9.3 左连接 LEFT JOIN

左连接以左表为主，返回左表全部记录，右表无匹配则填NULL。

-- 查询所有员工及其部门（没有部门的员工也显示）
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- 典型应用：查询没有部门的员工
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;

9.4 右连接 RIGHT JOIN

右连接以右表为主，返回右表全部记录。

-- 查询所有部门及其员工（没有员工的部门也显示）
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

9.5 多表连接

-- 连接3个表
SELECT
    o.order_no,
    u.username,
    p.product_name,
    o.quantity,
    p.price * o.quantity as total_price
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON o.product_id = p.id
WHERE o.status = 1;

9.6 自连接

自连接是表与自身进行连接，常用于处理层级数据（如组织架构、分类树）。

-- 查询每个员工及其上司姓名
SELECT
    e.name as employee_name,
    m.name as manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 查询所有上级及其下属数量
SELECT
    m.name as manager,
    COUNT(e.id) as subordinate_count
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
GROUP BY m.id, m.name;

9.7 连接与聚合

-- 统计各部门人数
SELECT
    d.dept_name,
    COUNT(e.id) as employee_count,
    IFNULL(AVG(e.salary), 0) as avg_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.id, d.dept_name
ORDER BY employee_count DESC;

十、子查询

子查询是嵌套在另一个查询中的查询，可以出现在 SELECT、FROM、WHERE 等子句中。

10.1 WHERE子句中的子查询

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

-- 查询销售部的所有员工
SELECT * FROM employees
WHERE dept_id = (
    SELECT id FROM departments
    WHERE dept_name = '销售部'
);

-- 查询在多个部门工作的员工（子查询返回多行，用IN）
SELECT * FROM employees
WHERE dept_id IN (
    SELECT id FROM departments
    WHERE name LIKE '%部'
);

-- 查询不存在任何部门的员工（NOT IN）
SELECT * FROM employees
WHERE dept_id NOT IN (
    SELECT id FROM departments WHERE id IS NOT NULL
);

10.2 FROM子句中的子查询

-- 查询各部门人数超过5人的部门
SELECT * FROM (
    SELECT dept_id, COUNT(*) as cnt
    FROM employees
    GROUP BY dept_id
) as dept_counts
WHERE cnt > 5;

-- 综合示例：查询各部门人数及其与平均人数的差值
SELECT
    d.dept_name,
    t.emp_count,
    t.emp_count - (SELECT COUNT(*) FROM employees) / (SELECT COUNT(*) FROM departments) as diff
FROM departments d
LEFT JOIN (
    SELECT dept_id, COUNT(*) as emp_count
    FROM employees
    GROUP BY dept_id
) t ON d.id = t.dept_id;

10.3 SELECT子句中的子查询

-- 查询员工及其部门名称
SELECT
    name,
    (SELECT dept_name FROM departments WHERE id = employees.dept_id) as dept_name
FROM employees;

-- 查询员工及其所属城市
SELECT
    e.name,
    (SELECT city FROM departments WHERE id = e.dept_id) as city
FROM employees e;

10.4 EXISTS 子查询

EXISTS 用于检查子查询是否返回任何行。

-- 查询有员工的部门
SELECT * FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

-- 查询没有员工的部门
SELECT * FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

10.5 子查询优化建议

-- 避免：子查询中引用外层表字段
-- SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 1);

-- 推荐：使用JOIN代替IN子查询（MySQL优化更好）
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id AND u.status = 1;

十一、约束详解

约束用于限制表中数据的规则，保证数据的完整性和一致性。

11.1 主键约束 PRIMARY KEY

主键是唯一标识表中每条记录的字段，主键值不能重复且不能为空。

-- 单字段主键
CREATE TABLE users (
    id BIGINT PRIMARY KEY
);

-- 表级定义（适用于复合主键）
CREATE TABLE order_items (
    order_id BIGINT,
    product_id BIGINT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)  -- 复合主键
);

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

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

11.2 自增约束 AUTO_INCREMENT

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    ...
);

-- 指定自增起始值
ALTER TABLE users AUTO_INCREMENT = 1000;

-- LAST_INSERT_ID() 获取最后插入的ID
SELECT LAST_INSERT_ID();

11.3 唯一约束 UNIQUE

唯一约束确保字段值不重复，但可以为空（只能有一个NULL）。

-- 单字段唯一
CREATE TABLE users (
    email VARCHAR(100) UNIQUE
);

-- 表级定义
CREATE TABLE users (
    email VARCHAR(100),
    phone VARCHAR(20),
    UNIQUE(email),
    UNIQUE(phone)
);

-- 复合唯一约束
CREATE TABLE user_scores (
    user_id BIGINT,
    exam_id BIGINT,
    score INT,
    UNIQUE(user_id, exam_id)  -- 同一用户同一考试只能有一条记录
);

11.4 非空约束 NOT NULL

CREATE TABLE users (
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100)  -- 可为空
);

11.5 默认值约束 DEFAULT

CREATE TABLE users (
    status INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

11.6 外键约束 FOREIGN KEY

外键用于建立两个表之间的关联关系。

-- 创建外键
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 级联操作：ON DELETE / ON UPDATE
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE      -- 删除用户时，级联删除其订单
        ON UPDATE CASCADE     -- 更新用户ID时，级联更新订单
);

-- 常见的级联动作
-- CASCADE：级联删除/更新
-- SET NULL：设为NULL
-- RESTRICT：阻止删除/更新
-- NO ACTION：同RESTRICT

11.7 检查约束 CHECK

检查约束限制字段取值范围（MySQL 8.0.16+支持）。

CREATE TABLE users (
    age INT CHECK (age >= 0 AND age <= 150),
    gender CHAR(1) CHECK (gender IN ('M', 'F'))
);

十二、索引原理与优化

12.1 索引的概念

索引是一种特殊的数据结构，用于加速数据检索。就像书的目录，索引让数据库可以快速定位到目标数据，而不需要扫描全表。

优点：大幅提升查询速度
缺点：占用磁盘空间，增加写操作时间

12.2 索引类型

类型	说明	使用场景
普通索引	无限制，可重复	加速一般查询
唯一索引	值唯一	保证数据唯一性
主键索引	值唯一且非空	主键字段
全文索引	文本内容搜索	文章搜索（MyISAM）
复合索引	多个字段组合	多条件查询

12.3 创建索引

-- 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age, status);

-- 创建表时添加索引
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    INDEX idx_username (username),
    UNIQUE idx_email (email)
);

-- 查看索引
SHOW INDEX FROM users;

12.4 删除索引

-- 按名称删除
DROP INDEX idx_username ON users;

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

12.5 最左前缀原则

复合索引遵循最左前缀原则：查询条件必须包含索引的最左边的字段，才能使用索引。

-- 创建复合索引
CREATE INDEX idx_name_age_status ON users(name, age, status);

-- 以下查询可以命中索引：
SELECT * FROM users WHERE name = '张三';           -- ✅ 使用name
SELECT * FROM users WHERE name = '张三' AND age = 25;  -- ✅ 使用name, age
SELECT * FROM users WHERE name = '张三' AND age = 25 AND status = 1;  -- ✅ 全部使用

-- 以下查询无法命中索引：
SELECT * FROM users WHERE age = 25;               -- ❌ 没有name
SELECT * FROM users WHERE status = 1;            -- ❌ 没有name
SELECT * FROM users WHERE age = 25 AND status = 1;  -- ❌ 缺少name

12.6 何时创建索引

适合创建索引：

WHERE 子句中经常使用的字段
JOIN 连接的字段
ORDER BY 排序的字段
高区分度字段（值重复少）

不适合创建索引：

区分度低的字段（如性别、状态）
表数据量小
频繁更新的字段

12.7 EXPLAIN 分析查询

-- 分析查询计划
EXPLAIN SELECT * FROM users WHERE username = '张三';

-- 分析结果关键字段：
-- type: 查询类型（const > eq_ref > ref > range > index > all）
-- key: 实际使用的索引
-- rows: 预估扫描的行数
-- Extra: 额外信息（Using index, Using filesort等）

十三、视图

13.1 视图的概念

视图（View）是一个虚拟表，其内容由查询定义。视图不存储数据，而是存储查询语句，每次访问视图时动态执行查询。

13.2 创建视图

-- 创建简单视图
CREATE VIEW v_active_users AS
SELECT id, username, email
FROM users
WHERE status = 1;

-- 创建复杂视图（多表连接）
CREATE VIEW v_user_orders AS
SELECT
    u.id,
    u.username,
    COUNT(o.id) as order_count,
    IFNULL(SUM(o.total_price), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

13.3 使用视图

-- 查询视图（和表一样使用）
SELECT * FROM v_active_users;

-- 带条件查询
SELECT * FROM v_active_users WHERE username LIKE '张%';

-- 关联其他表查询
SELECT v.*, o.order_count
FROM v_active_users v
INNER JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
) o ON v.id = o.user_id;

13.4 修改和删除视图

-- 修改视图（替换）
CREATE OR REPLACE VIEW v_active_users AS
SELECT id, username, email, age
FROM users
WHERE status = 1;

-- 删除视图
DROP VIEW v_active_users;
DROP VIEW IF EXISTS v_active_users;

13.5 视图的作用

简化复杂查询：将多表连接封装为视图
提高安全性：只暴露需要的字段
数据独立：修改表结构不影响使用方

十四、事务控制

14.1 事务的概念

事务（Transaction）是数据库操作的最小工作单元，一个事务包含一组SQL语句，这些语句要么全部成功，要么全部失败。

14.2 事务的特性（ACID）

特性	说明
Atomicity（原子性）	事务是最小执行单元，不可分割
Consistency（一致性）	事务前后数据保持一致
Isolation（隔离性）	并发事务相互隔离，互不影响
Durability（持久性）	事务提交后，修改永久保存

14.3 事务控制语句

-- 开启事务（方式1）
START TRANSACTION;

-- 开启事务（方式2）
BEGIN;

-- 执行SQL语句
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- 提交事务（成功）
COMMIT;

-- 回滚事务（失败）
ROLLBACK;

14.4 保存点 SAVEPOINT

BEGIN;

UPDATE users SET status = 0 WHERE id = 1;
SAVEPOINT sp1;  -- 创建保存点

UPDATE users SET status = 0 WHERE id = 2;
SAVEPOINT sp2;

-- 回滚到保存点（保留sp1之前的操作）
ROLLBACK TO SAVEPOINT sp1;

COMMIT;

14.5 事务隔离级别

隔离级别	说明	脏读	不可重复读	幻读
READ UNCOMMITTED	读取未提交	✓	✓	✓
READ COMMITTED	读取已提交	✗	✓	✓
REPEATABLE READ	可重复读（MySQL默认）	✗	✗	✓
SERIALIZABLE	串行化	✗	✗	✗

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

-- 设置隔离级别（会话级别）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置隔离级别（全局）
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

14.6 事务使用场景

-- 转账场景
START TRANSACTION;

UPDATE accounts SET balance = balance - 1000
WHERE id = 1 AND balance >= 1000;

IF ROW_COUNT() = 0 THEN
    ROLLBACK;
ELSE
    UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
    COMMIT;
END IF;

十五、常用函数速查

15.1 字符串函数

-- 拼接字符串
SELECT CONCAT('Hello', ' ', 'World');           -- Hello World

-- 带分隔符拼接
SELECT CONCAT_WS('-', '2024', '01', '15');       -- 2024-01-15

-- 字符串长度
SELECT LENGTH('Hello');                           -- 5（字节）
SELECT CHAR_LENGTH('你好');                       -- 2（字符）

-- 大小写转换
SELECT UPPER('hello');                            -- HELLO
SELECT LOWER('HELLO');                            -- hello

-- 去空格
SELECT TRIM('  hello  ');                        -- hello
SELECT LTRIM('  hello');                         -- hello
SELECT RTRIM('hello  ');                         -- hello

-- 截取字符串
SELECT SUBSTRING('Hello World', 1, 5);           -- Hello
SELECT SUBSTRING('Hello World', -5);             -- World

-- 替换
SELECT REPLACE('Hello World', 'World', 'MySQL'); -- Hello MySQL

-- 重复
SELECT REPEAT('Ha', 3);                          -- HaHaHa

-- 反转
SELECT REVERSE('abc');                            -- cba

15.2 日期时间函数

-- 获取当前日期时间
SELECT NOW();           -- 2024-01-15 12:30:45
SELECT CURDATE();       -- 2024-01-15
SELECT CURTIME();       -- 12:30:45

-- 获取日期部分
SELECT YEAR(NOW());     -- 2024
SELECT MONTH(NOW());    -- 1
SELECT DAY(NOW());      -- 15
SELECT HOUR(NOW());     -- 12
SELECT MINUTE(NOW());   -- 30
SELECT SECOND(NOW());   -- 45

-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);          -- 7天后
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);         -- 1月前
SELECT DATE_ADD(NOW(), INTERVAL -3 HOUR);         -- 3小时前

-- 日期差
SELECT DATEDIFF('2024-01-20', '2024-01-15');     -- 5

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

15.3 数值函数

-- 绝对值
SELECT ABS(-10);                                  -- 10

-- 四舍五入
SELECT ROUND(3.14159, 2);                        -- 3.14
SELECT ROUND(3.5);                               -- 4

-- 向上/向下取整
SELECT CEIL(3.1);                                -- 4
SELECT FLOOR(3.9);                               -- 3

-- 平方根
SELECT SQRT(16);                                 -- 4

-- 幂运算
SELECT POW(2, 3);                               -- 8
SELECT POWER(2, 3);                             -- 8

-- 随机数
SELECT RAND();                                   -- 0-1之间的随机数
SELECT FLOOR(RAND() * 100);                      -- 0-99随机整数

-- 取模
SELECT MOD(10, 3);                              -- 1
SELECT 10 % 3;                                  -- 1

15.4 条件判断函数

-- IF函数
SELECT IF(age >= 18, '成年', '未成年') FROM users;

-- IFNULL函数
SELECT IFNULL(email, '未填写') FROM users;

-- NULLIF函数（相等返回NULL，不等返回第一个值）
SELECT NULLIF(a, b);

-- CASE表达式（搜索CASE）
SELECT
    username,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 60 THEN 'C'
        ELSE 'D'
    END as grade
FROM students;

-- CASE表达式（简单CASE）
SELECT
    status,
    CASE status
        WHEN 1 THEN '启用'
        WHEN 0 THEN '禁用'
        ELSE '未知'
    END as status_text
FROM users;

15.5 分组聚合函数

-- COUNT
SELECT COUNT(*) FROM users;                      -- 总行数
SELECT COUNT(DISTINCT status) FROM users;        -- 不同状态的数量

-- SUM/AVG
SELECT SUM(score) FROM students WHERE exam_id = 1;
SELECT ROUND(AVG(score), 1) FROM students WHERE exam_id = 1;

-- MAX/MIN
SELECT MAX(price) FROM products;
SELECT MIN(created_at) FROM orders;

15.6 其他常用函数

-- COALESCE：返回第一个非NULL值
SELECT COALESCE(NULL, NULL, 'default', 'other');  -- default

-- GREATEST：返回最大值
SELECT GREATEST(3, 1, 5, 2);                     -- 5

-- LEAST：返回最小值
SELECT LEAST(3, 1, 5, 2);                        -- 1

-- CAST：类型转换
SELECT CAST('123' AS UNSIGNED) + 1;             -- 124
SELECT CAST('2024-01-15' AS DATE);

十六、实战技巧与最佳实践

16.1 SQL编写规范

-- ✅ 推荐：关键字大写，表名和字段名用反引号
SELECT username, email
FROM `users`
WHERE status = 1
ORDER BY created_at DESC;

-- ❌ 避免：全小写，难以区分关键字
select username, email from users where status = 1;

16.2 性能优化技巧

-- ✅ 推荐：明确指定字段，避免 SELECT *
SELECT id, username, email FROM users WHERE id = 1;

-- ✅ 推荐：使用 LIMIT 限制结果集
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

-- ✅ 推荐：批量插入
INSERT INTO users (username, email) VALUES
('user1', 'user1@example.com'),
('user2', 'user2@example.com');

-- ❌ 避免：在 WHERE 条件中对字段使用函数
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- 优化：改为范围查询
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- ❌ 避免：大量使用 OR，用 UNION 替代
SELECT * FROM users WHERE status = 1 OR age > 30;
-- 优化：
SELECT * FROM users WHERE status = 1
UNION
SELECT * FROM users WHERE age > 30;

16.3 常用实战SQL

-- 分页查询（标准版）
SELECT * FROM users
ORDER BY created_at DESC
LIMIT #{offset}, #{pageSize};

-- 排名查询
SELECT
    username,
    score,
    RANK() OVER (ORDER BY score DESC) as ranking
FROM students;

-- 累计求和
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as cumulative_amount
FROM orders;

-- 查找重复数据
SELECT username, COUNT(*) as cnt
FROM users
GROUP BY username
HAVING cnt > 1;

-- 删除重复数据（保留ID最小的）
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id) FROM users GROUP BY username
);

-- 统计每日数据（补零）
SELECT
    DATE(created_at) as day,
    COUNT(*) as cnt
FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-08'
GROUP BY DATE(created_at);

-- 两表差异对比
SELECT * FROM table1
WHERE id NOT IN (SELECT id FROM table2)
UNION
SELECT * FROM table2
WHERE id NOT IN (SELECT id FROM table1);

16.4 SQL注入防护

-- ❌ 危险：字符串拼接（SQL注入漏洞）
-- "SELECT * FROM users WHERE username = '" + username + "'"
SELECT * FROM users WHERE username = '1' OR '1'='1';

-- ✅ 安全：使用参数化查询
-- SELECT * FROM users WHERE username = ?
-- 参数化查询由数据库驱动处理，恶意代码会被转义

16.5 数据库设计原则

命名规范：表名用单数，字段用下划线分隔
主键设计：每张表都有主键，建议使用自增BIGINT
适当冗余：为减少JOIN，可适当保留冗余字段
统一时间戳：创建时间、更新时间字段
逻辑删除：敏感数据使用status标记而非物理删除

16.6 常见错误处理

-- 查询报错：Unknown column
-- 检查字段名是否正确拼写

-- 查询报错：Table 'xxx' doesn't exist
-- 检查是否已 USE 数据库，或表名拼写

-- 插入报错：Duplicate entry
-- 检查是否违反唯一约束

-- 更新/删除报错：Affected 0 rows
-- 检查WHERE条件是否正确

-- 连接报错：Can't connect to MySQL server
-- 检查服务是否启动，端口是否正确

总结

本文系统介绍了SQL的核心语法，涵盖：

DDL（数据定义语言）：数据库、表、索引、视图的创建与管理
DML（数据操作语言）：数据的增删改查
DQL（数据查询语言）：条件查询、分组聚合、多表连接、子查询
DCL（数据控制语言）：事务控制

掌握这些内容后，你已经具备了SQL开发的核心能力。建议：

多练习：在本地数据库多写SQL
理解原理：理解索引、事务的底层原理
性能意识：关注SQL性能，避免全表扫描
安全意识：防止SQL注入，使用参数化查询

附录：MySQL数据类型速查表

分类	类型	说明
整数	TINYINT/SMALLINT/INT/BIGINT	不同长度的整数
浮点	FLOAT/DOUBLE/DECIMAL	浮点数/精确小数
字符串	CHAR/VARCHAR/TEXT	定长/变长/长文本
日期	DATE/DATETIME/TIMESTAMP	日期/时间戳

AtomGit开源社区

AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。

更多推荐

【负荷预测】基于LSTM-KAN的负荷预测研究（Python代码实现）

LSTM-KAN模型结合了LSTM和注意力机制的特点。LSTM是一种特殊的循环神经网络（RNN），通过引入记忆单元和门控机制（遗忘门、输入门、输出门），有效解决了传统RNN在处理长序列数据时存在的梯度消失或梯度爆炸问题，能够捕捉时间序列数据中的长期依赖关系。而注意力机制则能够模拟人类在处理信息时的注意力分配过程，通过为不同时间步的输入数据分配不同的权重，突出重要信息对预测结果的影响。