5月22日数据库学习笔记

一、学习概述
今天主要学习了数据库的 DQL（数据查询语言），深入掌握了 SQL 查询的多种技巧和方法，包括基本查询、条件查询、排序、聚合查询、分组查询、连接查询、子查询等，能够高效地从数据库中检索所需数据。
二、DQL（数据查询语言）

1. 基本查询
   查询所有列
   使用 SELECT * 查询表中的所有列。
   示例：查询学生表的所有数据。
   sql

SELECT * FROM student;
查询指定列
使用 SELECT 列名1, 列名2, ... 查询指定的列。
示例：查询学生表的姓名和年龄列。
sql

SELECT name, age FROM student;
2. 条件查询
使用 WHERE 子句
指定查询条件，过滤结果集。
示例：查询年龄大于 18 的学生。
sql

SELECT * FROM student WHERE age > 18;
查询性别为 'M' 且年龄等于 19 的学生。
sql

SELECT * FROM student WHERE gender = 'M' AND age = 19;
查询姓名为 'Alice' 或 'Charlie' 的学生。
sql

SELECT * FROM student WHERE name IN ('Alice', 'Charlie');
查询年龄在 18 到 20 之间的学生。
sql

SELECT * FROM student WHERE age BETWEEN 18 AND 20;
3. 排序
使用 ORDER BY 子句
对查询结果进行排序，ASC 表示升序（默认），DESC 表示降序。
示例：按年龄升序查询学生。
sql

SELECT * FROM student ORDER BY age ASC;
按姓名降序查询学生。
sql

SELECT * FROM student ORDER BY name DESC;
4. 聚合查询与分组查询
聚合函数
常见聚合函数：COUNT()（计数）、SUM()（求和）、AVG()（平均值）、MAX()（最大值）、MIN()（最小值）。
示例：查询学生表中的学生总数。
sql

SELECT COUNT(*) AS student_count FROM student;
查询所有学生的年龄总和。
sql

SELECT SUM(age) AS total_age FROM student;
分组查询
使用 GROUP BY 子句对结果集进行分组，通常与聚合函数一起使用。
示例：查询每个班级的学生数量。
sql

SELECT class_id, COUNT(*) AS student_count
FROM student
GROUP BY class_id;
查询每个班级的平均年龄和最大年龄。
sql

SELECT class_id, AVG(age) AS avg_age, MAX(age) AS max_age
FROM student
GROUP BY class_id;
分组过滤
使用 HAVING 子句对分组后的结果进行过滤，类似于 WHERE 子句，但作用于分组后的结果。
示例：查询学生数量大于 2 的班级。

SELECT class_id, COUNT() AS student_count
FROM student
GROUP BY class_id
HAVING COUNT() > 2;
5. 连接查询
内连接（INNER JOIN）
查询两个表中满足连接条件的记录。
示例：查询学生及其对应的班级信息。
sql

SELECT student.id, student.name, student.age, class.class_name, class.location
FROM student
INNER JOIN class ON student.class_id = class.id;
左连接（LEFT JOIN）
查询左表的所有记录及右表中满足连接条件的记录，右表中不满足条件的记录显示 NULL。
示例：查询所有学生及其对应的班级信息（包括未分配班级的学生）。
sql

SELECT student.id, student.name, student.age, class.class_name, class.location
FROM student
LEFT JOIN class ON student.class_id = class.id;
右连接（RIGHT JOIN）
查询右表的所有记录及左表中满足连接条件的记录，左表中不满足条件的记录显示 NULL。
示例：查询所有班级及其对应的学生信息（包括没有学生的班级）。
sql

SELECT student.id, student.name, student.age, class.class_name, class.location
FROM student
RIGHT JOIN class ON student.class_id = class.id;
全连接（FULL JOIN）
查询两个表中所有记录，满足连接条件的显示对应记录，不满足的显示 NULL。并非所有数据库都支持 FULL JOIN，MySQL 不支持，可以使用 UNION ALL 模拟。
示例：（使用 MySQL 模拟 FULL JOIN）
sql

SELECT student.id, student.name, student.age, class.class_name, class.location
FROM student
LEFT JOIN class ON student.class_id = class.id
UNION ALL
SELECT student.id, student.name, student.age, class.class_name, class.location
FROM student
RIGHT JOIN class ON student.class_id = class.id
WHERE student.id IS NULL;
6. 子查询
子查询（Subquery）
在一个查询语句内部嵌套另一个查询语句，子查询通常用于提供条件值。
示例：查询年龄大于平均年龄的学生。
sql

SELECT * FROM student
WHERE age > (SELECT AVG(age) FROM student);
查询与 'Alice' 在同一班级的学生。
sql

SELECT * FROM student
WHERE class_id = (SELECT class_id FROM student WHERE name = 'Alice') AND name != 'Alice';
存在性子查询（EXISTS）
检查子查询是否返回至少一行，返回布尔值。
示例：查询存在学生的班级。
sql

SELECT * FROM class
WHERE EXISTS (SELECT 1 FROM student WHERE student.class_id = class.id);
7. 限制查询结果
使用 LIMIT 子句
限制返回的行数，常用于分页查询。
示例：查询前 3 条记录。
sql

SELECT * FROM student LIMIT 3;
查询第 4 到第 6 条记录（偏移 3 行，取 3 行）。
sql

SELECT * FROM student LIMIT 3 OFFSET 3;
三、案例实践

1. 学生管理数据库查询案例
   创建数据库和表
   创建学生管理数据库并插入数据（参考前面 DDL 和 DML 部分的案例）。
   查询案例
   查询所有学生的姓名、年龄和班级名称，并按年龄降序排列。
   sql

SELECT student.name, student.age, class.class_name
FROM student
INNER JOIN class ON student.class_id = class.id
ORDER BY student.age DESC;
查询每个班级的学生数量和平均年龄，只显示学生数量大于 2 的班级。
sql

SELECT class.class_name, COUNT(student.id) AS student_count, AVG(student.age) AS avg_age
FROM student
RIGHT JOIN class ON student.class_id = class.id
GROUP BY class.id, class.class_name
HAVING COUNT(student.id) > 2;
查询年龄大于 18 且所在班级位置为 'Building A' 的学生。
sql

SELECT student.name, student.age, class.location
FROM student
INNER JOIN class ON student.class_id = class.id
WHERE student.age > 18 AND class.location = 'Building A';
查询没有学生的班级。
sql

SELECT * FROM class
WHERE NOT EXISTS (SELECT 1 FROM student WHERE student.class_id = class.id);
分页查询学生信息，每页显示 2 条记录，查询第 3 页的数据。
sql

SELECT * FROM student
LIMIT 2 OFFSET 4; -- 偏移 4 行（前两页共 4 条记录），取 2 条记录