经典SQL查询

hogwarts · 2022 年9 月 5 日 01:26

本文引用自霍格沃兹测试开发学社录播课

经典 SQL 查询问题

霍格沃兹测试开发学社
http://ceshiren.com

经典面试题

已知有如下 3 张表：
- 学⽣表：student (学号, 学⽣姓名, 出⽣年⽉, 性别)
- 成绩表：score (学号, 课程号, 成绩)
- 课程表：course (课程号, 课程名称, 教师姓名)
根据以上信息按照下⾯要求写出对应的 SQL 语句

考察点

分析数据表的关联关系
SQL 的编写能⼒

技术点

SQL 查询语法

创建学生表

学⽣表：student
- 学号：studentId，主键
- 学⽣姓名：studentName
- 出⽣年⽉：birthDate
- 性别：sex

CREATE TABLE `shcool`.`student` (
  `studentId` VARCHAR(10) NOT NULL,
  `studentName` VARCHAR(45) NULL,
  `birthDate` DATE NULL,
  `sex` VARCHAR(10) NULL,
  PRIMARY KEY (`studentId`));
insert into student(studentId, studentName, birthDate, sex) 
values("0001", "张三", "1989-01-01", "男");
insert into student(studentId, studentName, birthDate, sex) 
values("0002", "李四", "1990-12-21", "⼥");
insert into student(studentId, studentName, birthDate, sex) 
values("0003", "王五", "1991-12-21", "男");
insert into student(studentId, studentName, birthDate, sex) 
values("0004", "赵六", "1990-05-20", "男");
insert into student(studentId, studentName, birthDate, sex) 
values("0005", "冯七", "1991-08-11", "女");

创建成绩表

成绩表：score
- 学号：studentId，主键
- 课程号：courseId，主键
- 成绩：score

CREATE TABLE `shcool`.`score` (
  `studentId` INT NOT NULL,
  `courseId` INT NOT NULL,
  `score` FLOAT NULL,
  PRIMARY KEY (`studentId`, `courseId`));
insert into score(studentId, courseId, score) 
values("0001" , "0001" , 80);
insert into score(studentId, courseId, score)
values("0001" , "0002" , 90);
insert into score(studentId, courseId, score)
values("0001" , "0003" , 99);
insert into score(studentId, courseId, score)
values("0002" , "0001" , 58);
insert into score(studentId, courseId, score)
values("0002" , "0002" , 55);
insert into score(studentId, courseId, score)
values("0002" , "0003" , 80);
insert into score(studentId, courseId, score)
values("0003" , "0001" , 90);
insert into score(studentId, courseId, score)
values("0003" , "0002" , 80);
insert into score(studentId, courseId, score)
values("0003" , "0003" , 59);
insert into score(studentId, courseId, score)
values("0004" , "0002" , 49);
insert into score(studentId, courseId, score)
values("0005" , "0003" , 70);

创建课程表

课程表：course
- 课程号：courseId，主键
- 课程名称：courseName
- 教师姓名：teacher

CREATE TABLE `shcool`.`course` (
  `courseId` INT NOT NULL,
  `courseName` VARCHAR(255) NULL,
  `teacher` VARCHAR(255) NULL,
  PRIMARY KEY (`courseId`));
insert into course(courseId, courseName, teacher) 
values("0001" , "语⽂" , "王老师");
insert into course(courseId, courseName, teacher) 
values("0002" , "数学" , "李老师");
insert into course(courseId, courseName, teacher) 
values("0003" , "英语" , "赵老师");

汇总分析

查询课程编号为 “0002” 的总成绩
查询选了课程编号为 “0002” 的学生人数

-- 查询课程编号为 "0002" 的总成绩
SELECT 
    SUM(score)
FROM
    score
WHERE
    courseId = "0002";
-- 查询选了课程编号为 "0002" 的学生人数
SELECT 
    COUNT(DISTINCT studentId) AS 学⽣⼈数
FROM
    score
WHERE
    courseId = "0002";

分组

查询各科成绩最高和最低分
查询每门课程被选修的学生数

-- 查询各科成绩最高和最低分
SELECT 
    courseId, MAX(score) AS 最⾼分, MIN(score) AS 最低分
FROM
    score
GROUP BY courseId;
-- 查询每门课程被选修的学生数
SELECT 
    courseId, COUNT(studentId)
FROM
    score
GROUP BY courseId;

分组结果的条件

查询平均成绩大于 60 分学生的学号
查询⾄少选修两门课程的学⽣学号

-- 查询平均成绩大于 60 分学生的学号
SELECT 
    studentId, AVG(score)
FROM
    score
GROUP BY studentId
HAVING AVG(score) > 60;
-- 查询⾄少选修两门课程的学⽣学号
SELECT 
    studentId, COUNT(courseId) AS 选修课程数⽬
FROM
    score
GROUP BY studentId
HAVING COUNT(courseId) >= 2;

复杂查询

查询所有有过不及格（低于 60 分）成绩的学生姓名（不重复）
查询 2 门以上（含 2 门）不及格（低于 60 分）的学生姓名
查询没有选过王老师的课的所有学生姓名
查询选过王老师的课并且及格的所有学生姓名
查询所有学⽣的学号、姓名、选课数、总成绩

-- 查询所有有过不及格（低于 60 分）成绩的学生姓名（不重复）
-- 表连接
SELECT DISTINCT
    studentName 不及格学生姓名
FROM
    student st
        LEFT JOIN
    score sc ON st.studentId = sc.studentId
WHERE
    score < 60;
-- 子查询
SELECT DISTINCT
    studentName 不及格学生姓名
FROM
    student
WHERE
    studentId IN (SELECT 
            studentId
        FROM
            score
        WHERE
            score < 60)
-- 查询 2 门以上（含 2 门）不及格（低于 60 分）的学生姓名
SELECT 
    studentName 姓名, COUNT(studentName) 不及格课程数
FROM
    student st
        LEFT JOIN
    score sc ON st.studentId = sc.studentId
WHERE
    score < 60
GROUP BY studentName
HAVING COUNT(studentName) >= 2;
-- 查询没有选过王老师的课的所有学生姓名
SELECT 
    studentId, studentName
FROM
    student
WHERE
    studentId NOT IN (SELECT 
            st.studentId
        FROM
            student st
                INNER JOIN
            score sc ON st.studentId = sc.studentId
                INNER JOIN
            course co ON sc.courseId = co.courseId
        WHERE
            teacher = "王老师");
-- 查询选过王老师的课并且及格的所有学生姓名
SELECT 
    *
FROM
    student st
        INNER JOIN
    score sc ON st.studentId = sc.studentId
        INNER JOIN
    course co ON sc.courseId = co.courseId
WHERE
    teacher = "王老师" and score >= 60;
-- 查询所有学⽣的学号、姓名、选课数、总成绩
SELECT 
    sc.studentId,
    studentName,
    COUNT(sc.courseId) AS 选课数,
    SUM(score) AS 总成绩
FROM
    student AS st
        LEFT JOIN
    score AS sc ON st.studentId = sc.studentId
GROUP BY st.studentId;

答案总结

SQL 查询问题分析思路

查询的内容是什么

查询的内容从哪张表中查找

如果查询的内容，或者查询的条件分别在不同的表中，需要使用表连接或者子查询

确定查询的条件是什么

如果使用了聚合函数，则要对某个字段进行分组

是否需要对分组后的结果进行条件过滤

是否需要排序

是否需要限定输出数据的数量

SELECT <列名>
FROM  
  <表名1>  [别名1]
INNER/LEFT/RIGHT JOIN 
  <表名2>  [别名2]
ON 
  别名1.外键列 = 别名2.主键列
WHERE <查询条件表达式>
GROUP BY <分组的列名>
HAVING <分组后的查询条件表达式>
ORDER BY <排序的列名> [ASC或者DESC]
LIMIT [开始的行数], <查询记录的条数>
```: