常用的 SQL 命令

基本思路：

JOIN 做 关联查询，WHERE 做条件限制，函数做计算。

查询每个部门的平均年龄

使用 AVG 函数来求均值，JOIN 表加条件，GROUP BY 来按部门展示

1
2
3
4
5
6
7
8
SELECT 部门信息.部门编号,部门信息.部门名称,
    AVG(员工信息.年龄) AS 平均年龄
FROM 
    员工信息
JOIN
    部门信息 ON 员工信息.所在部门 = 部门信息.部门编号
GROUP BY
    部门信息.部门编号, 部门信息.部门名称;

mitmproxy

查询和李文萌不在一个部门的员工

展示除了特定值以外数据，使用 ！= 来限制，先在部门表将和李文萌不在一个部门的员工给选择出来，再通过关联查询

1
2
3
4
5
6
7
8
SELECT 员工姓名
FROM 员工信息
WHERE 所在部门 != (
    SELECT 所在部门
    FROM 员工信息
    WHERE 员工姓名 = '李文萌'
)
AND 员工姓名 != '李文萌';

查询每个员工的工号，姓名，所在部门名称，以及其部门领导姓名

用自连接查询，像加入其他表一样加入自己的也是可以的

1
2
3
4
5
6
7
8
SELECT
    e.员工工号,
    e.员工姓名,
    d.部门名称,
    l.员工姓名 AS 部门领导姓名
FROM 员工信息 AS e
JOIN 部门信息 AS d ON e.所在部门 = d.部门编号
JOIN 员工信息 AS l ON d.部门领导工号 = l.员工工号;

找出哪个部门没有员工？

用 IS NULL

1
2
3
4
SELECT d.部门编号, d.部门名称
FROM 部门信息 AS d
LEFT JOIN 员工信息 AS e ON d.部门编号 = e.所在部门
WHERE e.员工工号 IS NULL;

查询在第5学期选修了“数据库系统原理”和“软件工程导论”这两门课程的学生学号、学生姓名、所在专业名称。

这里用 AND 链接做条件限制

1
2
3
4
5
6
7
8
9
SELECT s.student_id, s.student_name, m.major_name
FROM students_info s
JOIN grades_info g1 ON s.student_id = g1.student_id
JOIN grades_info g2 ON s.student_id = g2.student_id
JOIN courses_info c1 ON g1.course_id = c1.course_id
JOIN courses_info c2 ON g2.course_id = c2.course_id
JOIN major_info m ON s.major_id = m.major_id
WHERE c1.course_name = '数据库原理' AND c1.course_term = 5
AND c2.course_name = '软件工程' AND c2.course_term = 5;

查询在选修了“数据库系统原理”和“软件工程导论”这两门课程的学生学号、学生姓名、以及各科课程的成绩。

如何拆解，首先我们先考虑条件：“选修了“数据库系统原理”和“软件工程导论”这两门课程的学生”，先用 WHERE 语句按照条件查询出来，然后再去 JOIN 其他表展示剩下的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT 
    s.student_id, 
    s.student_name,
    c.course_name,
    g.grades
FROM students_info s
JOIN grades_info g ON s.student_id = g.student_id
JOIN courses_info c ON g.course_id = c.course_id
WHERE s.student_id IN (
    SELECT student_id
    FROM grades_info
    JOIN courses_info ON grades_info.course_id = courses_info.course_id
    WHERE course_name = '数据库原理'
) AND s.student_id IN (
    SELECT student_id
    FROM grades_info
    JOIN courses_info ON grades_info.course_id = courses_info.course_id
    WHERE course_name = '软件工程'
);

求出每门课的课程号、选课人数。

用 COUNT 去统计，这里使用左链接显示没有人选的科目

1
2
3
4
5
6
7
SELECT 
    c.course_id,
    c.course_name,
    COUNT(g.student_id) AS number_of_students
FROM courses_info c
LEFT JOIN grades_info g ON c.course_id = g.course_id
GROUP BY c.course_id, c.course_name;

查询所有学生的选修课程情况和成绩，若学生没有选修任何课程，也要包含在其中。

也是左链接，同理。

1
2
3
4
5
6
7
8
9
SELECT 
    c.course_id,
    c.course_name,
    s.student_id,
    s.student_name
FROM grades_info g
RIGHT JOIN courses_info c ON g.course_id = c.course_id
LEFT JOIN students_info s ON g.student_id = s.student_id

查询被选修了的课程的选修情况和所有开设课程名，包括没有被选修的课程

1
2
3
4
5
6
7
8
SELECT 
    c.course_name,
    GROUP_CONCAT(s.student_name) AS students_enrolled
FROM courses_info c
LEFT JOIN grades_info g ON c.course_id = g.course_id
LEFT JOIN students_info s ON g.student_id = s.student_id
GROUP BY c.course_name
ORDER BY c.course_name;

在学生信息表中，查询年龄比“王林”大的学生的学号，姓名，年龄

用 YEAR()函数转换为 int 再相减

1
2
3
4
5
6
7
SELECT
    s1.student_id,
    s1.student_name,
    YEAR(CURRENT_DATE) - YEAR(s1.birthday) AS age
FROM students_info s1
LEFT JOIN students_info s2 ON s1.student_id != s2.student_id
WHERE s2.student_name = '王林' AND s1.birthday > s2.birthday

将“student_view3”视图的内容修改为：“数据库系统原理”课程所选修学生的专业名称和各个专业的选课学生人数

这里用 CREATE OR REPLACE 修改视图，然后 FROM 这边可以从一个查询结果里的表选取，用 AS t 重新命名为 t，这里就可以体现出数据库查询思路了，由简单再到复杂，将复杂的语句拆开成几个部分，实现了各个部分再合并。

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE VIEW student_view3 AS
SELECT t.major_name,COUNT(t.student_id) AS numbers
FROM 
(SELECT m.major_name, s.student_id
FROM students_info AS s
JOIN major_info AS m ON s.major_id = m.major_id) AS t
JOIN grades_info AS g ON t.student_id = g.student_id
JOIN courses_info AS c ON c.course_id = g.course_id
WHERE c.course_name = "数据库原理"
GROUP BY t.major_name;