思途SQL学习 0728

一、数据类型与完整性约束

1.1 结构化 vs 非结构化数据

• 结构化数据：可存储于表格中，如关系型数据库中的用户信息、订单记录等。
• 非结构化数据：如音频、视频、图片、文档等，通常不适合直接存入传统关系表，常用 BLOB 或外部存储+路径引用方式处理。

1.2 常见数据类型

⚠️ TIMESTAMP 与 DATETIME 区别：

• TIMESTAMP 受时区影响，范围较小（1970~2038）；
• DATETIME 不受时区影响，范围更大（1000~9999）。

1.3 完整性与一致性：通过约束实现

📝 建议：

• 外键可在应用层或逻辑层维护，避免数据库级外键带来的锁竞争和迁移复杂性。
• 使用 CHECK 时注意不同数据库支持程度（MySQL 8.0+ 支持较好）。

二、SQL 核心语言分类

🔤 SQL 不区分大小写（但建议关键字大写，字段/表名小写以提高可读性）。

三、DQL：数据查询语言详解

3.1 基础查询

sql

深色版本

-- 查询所有列
SELECT * FROM t_student;-- 指定列查询
SELECT id, stu_id, name, pinyin FROM t_student;-- 别名（AS 可省略）
SELECT id AS 编号, name 姓名 FROM t_student;-- 列运算
SELECT id, height + 10 AS 修正身高, weight + 10 AS 修正体重 FROM t_student;

3.2 限制结果集

-- 限制前10条
SELECT * FROM t_student LIMIT 10;-- 分页：跳过10条，取10条（常用于分页）
SELECT * FROM t_student LIMIT 10 OFFSET 10;
-- 或写法：LIMIT 10,10

3.3 条件查询（WHERE）

-- 基本比较
SELECT * FROM t_student WHERE id = 5;
SELECT * FROM t_student WHERE id <> 5; -- 或 !=-- 日期比较（MySQL）
SELECT * FROM t_student WHERE birthday > '2000-01-01';-- 逻辑运算符
SELECT * FROM t_student WHERE height > 174 AND sex = '女';
SELECT * FROM t_student WHERE NOT sex = '女'; -- 或 sex != '女'-- 空值判断
SELECT * FROM t_student WHERE class_id IS NULL;
SELECT * FROM t_student WHERE class_id IS NOT NULL;

3.4 模糊查询与正则表达式

-- LIKE（%任意多字符，_单个字符）
SELECT * FROM t_student WHERE name LIKE '张%';     -- 姓张
SELECT * FROM t_student WHERE name LIKE '%小%';   -- 名含“小”
SELECT * FROM t_student WHERE name LIKE '张__';   -- 姓张且名字两字-- 正则表达式（MySQL）
SELECT * FROM t_student WHERE name REGEXP '^张.{2}$'; -- 张+两个字符
SELECT * FROM t_student WHERE wechat NOT RLIKE '\\d{11}'; -- 微信不是11位数字

3.5 去重与排序

-- 去重（慎用，影响性能）
SELECT DISTINCT sex FROM t_student;-- 排序（ASC 升序，默认；DESC 降序）
SELECT * FROM t_student ORDER BY height ASC, weight DESC;

四、分组查询（GROUP BY）

4.1 聚合函数

-- 按性别分组统计
SELECT sex, AVG(height), MIN(weight), COUNT(*) 
FROM t_student 
GROUP BY sex;-- 多字段分组
SELECT class_id, sex, AVG(height), COUNT(*) 
FROM t_student 
GROUP BY class_id, sex 
ORDER BY class_id, sex;

4.2 HAVING：分组后筛选

-- 先 WHERE 过滤原始数据，再 GROUP BY，最后 HAVING 过滤分组结果
SELECT class_id, sex, MAX(height) mh, MAX(weight) mw
FROM t_student
WHERE height > 180
GROUP BY class_id, sex
HAVING mh > 183
ORDER BY class_id, sex;

✅ WHERE 作用于行，HAVING 作用于组。

五、子查询（嵌套查询）

5.1 列子查询

SELECT id, name,(SELECT class_name FROM t_class WHERE id = class_id) AS class_name
FROM t_student;

5.2 表子查询（派生表）

SELECT * FROM (SELECT * FROM t_student WHERE sex = '女'
) AS female_students;

5.3 比较子查询（=, >, <）

-- 子查询必须返回单行单列
SELECT * FROM t_student 
WHERE class_id = (SELECT id FROM t_class WHERE class_name LIKE '0805%');

5.4 IN / NOT IN 子查询

SELECT * FROM t_student 
WHERE class_id IN (SELECT id FROM t_class WHERE class_name LIKE '%JAVA%');

⚠️ NOT IN 若子查询包含 NULL，整个结果为 NULL，应避免。

5.5 EXISTS / NOT EXISTS（断言查询）

-- 只判断是否存在，不关心具体值
SELECT * FROM t_student 
WHERE EXISTS (SELECT 1 FROM t_class WHERE class_name LIKE '%JAVA%');

5.6 相关子查询

-- 查询比所在班级平均身高高的学生
SELECT id, name, height, class_id
FROM t_student t1
WHERE height > (SELECT AVG(height) FROM t_student t2 WHERE t2.class_id = t1.class_id
);

六、表连接（JOIN）

-- 内连接
SELECT t1.id, t1.name, t2.class_name
FROM t_student t1
INNER JOIN t_class t2 ON t1.class_id = t2.id;-- 左连接（推荐用于“主表+可选信息”场景）
SELECT t1.name, t2.class_name
FROM t_student t1
LEFT JOIN t_class t2 ON t1.class_id = t2.id;

七、联合查询（UNION）

-- 合并两个查询结果（列数、类型需一致）
SELECT id, name, sex FROM t_student WHERE sex = '男'
UNION ALL  -- ALL 不去重，性能更好
SELECT id, name, sex FROM t_student WHERE sex = '男';-- 模拟全外连接（MySQL）
(SELECT * FROM t_student LEFT JOIN t_class ON ...)
UNION
(SELECT * FROM t_student RIGHT JOIN t_class ON ...);

✅ UNION 自动去重；UNION ALL 保留重复，效率更高。

八、高级对象概览

九、最佳实践与注意事项

1. 命名规范：

   • 表名小写加下划线（t_student）
   • 字段名清晰（stu_id, birthday）
   • 避免保留字（如 name 加反引号 `name`）

2. 性能建议：

   • 合理使用索引（避免过度索引）
   • DISTINCT、ORDER BY、GROUP BY 易影响性能
   • 分页使用 LIMIT OFFSET，避免全表扫描

3. 外键与检查约束：

   • 建议在应用层维护一致性
   • 外键影响扩展性和迁移灵活性

4. NULL 值处理：

   • 使用 IS NULL / IS NOT NULL
   • 注意 COUNT(column) 不统计 NULL

5. SQL 注入防范：

   • 使用预编译语句（PreparedStatement）
   • 避免拼接 SQL 字符串

十、总结

本笔记系统梳理了 SQL 的核心知识点，涵盖：

• 数据类型与约束机制
• DQL 查询语法（基础、条件、分组、子查询、连接）
• 数据完整性保障策略
• 性能与设计最佳实践

掌握这些内容，可胜任大多数数据库开发与管理工作。建议结合实际项目不断练习，深入理解执行计划、索引优化等进阶技能。

📌 附录：常用聚合函数速查表