MYSQL数据库—多表查询

目录

第1关：连接查询——多表联合查询 

第2关：连接查询——内连接查询

第3关：连接查询——左外连接查询

第4关：连接查询——右外连接查询

第5关：连接查询——交叉连接查询

第6关：普通子查询

第7关：相关子查询

第8关：UNION联合查询

---

2023.6.3更新了第2关的第一题。

第1关：连接查询——多表联合查询 

编程要求

现已建立STUDENT数据库和如下数据表，请根据提示，在右侧编辑窗口补充代码，用Select语句完成下列查询操作。

测试说明

平台会对你编写的代码进行测试：
查询选修了编号为'c01'课程的学生学号、姓名和成绩

查询与'刘伟'在同一个系学习的学生学号、姓名及所在院系

查询选修了'数据结构'课程且成绩大于等于90的学号、姓名和成绩

查询选修编号为'c01'或'c02'课程的学生姓名

USE student;

#请在此处添加实现代码

########## 查询选修了编号为'c01'课程的学生学号、姓名和成绩 ##########
SELECT s.sno, s.sname, sc.score
 from s, sc
  WHERE s.sno=sc.sno
   AND sc.cno='c01';


########## 查询与'刘伟'在同一个系学习的学生学号、姓名及所在院系 ##########
SELECT s1.sno, s1.sname, s1.dept
 from s s1, s s2
  WHERE s1.dept = s2.dept AND s2.sname='刘伟' AND s1.sname !='刘伟';



########## 查询选修了'数据结构'课程且成绩大于等于90的学号、姓名和成绩 ##########

SELECT s.sno, s.sname, sc.score
 from s, c,sc
 WHERE s.sno=sc.sno AND c.cno=sc.cno AND c.cname='数据结构' AND sc.score>=90;


########## 查询选修编号为'c01'或'c02'课程的学生姓名 ##########
SELECT DISTINCT sname FROM s
JOIN sc ON s.sno=sc.sno
JOIN c ON sc.cno=c.cno
WHERE c.cno IN ('c01','c02');

第2关：连接查询——内连接查询

任务描述

本关任务：使用内连接查询实现对多数据表的查询操作。

相关知识

为了完成本关任务，你需要掌握：
1.什么是内连接查询；
2.如何使用内连接查询。

内连接查询

• 通过关键字[inner] join将多个表中仅满足连接条件的行组合起来作为结果集称为内连接，功能等同多表联合查询。语法格式如下：

  SELECT 字段列表 FROM 表1 [别名1] [INNER] JOIN 表2 [别名2]
  [ON 表1.字段=表2.字段]
  [ WHERE 条件表达式 ];

提示：
1.语法解释：‘ON’表示连接条件，从表1中取出每一条记录，与表2中所有的记录进行匹配，匹配必须是满足ON条件的记录，最终才会保留结果，否则不保留。INNER 关键字可省略不写；
2.两张表在进行连接时，连接列字段的名称可以不同，但要求必须具有相同数据类型，长度和精度，且表达同一范畴的意义，通常连接列字段一般是数据表的主键和外键。
3.当连接条件由两张表相同名称且类型系统的字段相连接时，可以使用USING(字段名)来连接。

编程要求

现已建立STUDENT数据库和如下数据表，请根据提示，在右侧编辑窗口补充代码，用Select语句完成下列查询操作。

查询与'张乐乐'年龄相同的学生学号、姓名、所在院系和年龄（别名age）

查询每个学生的学号、姓名、选修课程号、课程名及成绩 

查询选修课程数大于等于2门的学生学号、姓名及平均成绩（别名avg），查询结果按平均成绩降序输出

USE student;

#请在此处添加实现代码

########## 查询与'张乐乐'出生年份相同的学生学号、姓名、所在院系和年龄（别名age，使用TimeStampDiff()函数计算到2023年4月1日的年龄） ##########
SELECT sno, sname, dept, TIMESTAMPDIFF(YEAR, dob, '2023-04-01') AS age
FROM s
WHERE YEAR(dob) = (
    SELECT YEAR(dob)
    FROM s
    WHERE sname = '张乐乐'
)
AND sname <> '张乐乐';


########## 查询每个学生的学号、姓名、选修课程号、课程名及成绩 ##########
SELECT s.sno, s.sname,c.cno, c.cname, sc.score 
FROM s 
INNER JOIN sc ON s.sno = sc.sno 
INNER JOIN c ON sc.cno = c.cno;
 
########## 查询选修课程数大于等于2门的学生学号、姓名及平均成绩（别名avg），查询结果按平均成绩降序输出 ##########
SELECT s.sno, s.sname, AVG(sc.score) AS avg 
FROM s INNER JOIN sc ON s.sno = sc.sno
INNER JOIN c ON sc.cno = c.cno
WHERE s.sno IN (
   SELECT sno FROM sc GROUP BY sno HAVING COUNT(*) >= 2
)
GROUP BY s.sno, s.sname
HAVING COUNT(*) >= 2
ORDER BY AVG(sc.score) DESC;

第3关：连接查询——左外连接查询

查询所有学生的选课情况（包括选课和未选课的学生），要求显示学号、姓名、课程号和成绩

USE student;

#请在此处添加实现代码

########## 查询所有学生的选课情况（包括选课和未选课的学生），要求显示学号、姓名、课程号和成绩 ##########
SELECT s.sno, s.sname, c.cno, sc.score 
FROM s
LEFT JOIN sc ON s.sno = sc.sno
LEFT JOIN c ON sc.cno = c.cno ;

第4关：连接查询——右外连接查询

查询所有课程的被选情况（包括从未被选修的课程），要求显示选课的学生学号、课程编号、课程名和成绩

USE student;

#请在此处添加实现代码

########## 查询所有课程的被选情况（包括从未被选修的课程），要求显示选课的学生学号、课程编号、课程名和成绩 ##########
SELECT sc.sno, c.cno, c.cname, COALESCE(sc.score, NULL) AS score
FROM c LEFT JOIN sc ON c.cno = sc.cno
ORDER BY c.cno ASC;

第5关：连接查询——交叉连接查询

查询所有学生的可能选课组合,结果集包括学号、姓名、课程号及课程名

USE student;

#请在此处添加实现代码

########## 查询所有学生的可能选课组合,结果集包括学号、姓名、课程号及课程名 ##########

SELECT s.sno, s.sname, c.cno, c.cname
FROM s CROSS JOIN c
LEFT JOIN sc ON s.sno = sc.sno AND c.cno = sc.cno

第6关：普通子查询

查询学号为's003'学生的选修课程号和课程名

查询选修编号为'c01'但没选修'c04'课程的学生学号

查询同时选修编号为'c01'和'c02'课程的学生学号

查询没有选修任何课程的学生记录，输出结果包括学号、姓名和所在院系

USE student;

#请在此处添加实现代码

########## 查询学号为's003'学生的选修课程号和课程名 ##########

SELECT sc.cno, c.cname 
FROM sc 
INNER JOIN c ON sc.cno = c.cno 
WHERE sc.sno = 's003';


########## 查询选修编号为'c01'但没选修'c04'课程的学生学号 ##########
SELECT s.sno 
FROM s 
WHERE s.sno IN (
    SELECT sc.sno 
    FROM sc 
    WHERE sc.cno = 'c01'
) AND s.sno NOT IN (
    SELECT sc.sno 
    FROM sc 
    WHERE sc.cno = 'c04'
);



########## 查询同时选修编号为'c01'和'c02'课程的学生学号 ##########
SELECT s.sno 
FROM s 
WHERE s.sno IN (
  SELECT sc.sno 
  FROM sc 
  WHERE sc.cno = 'c01' 
  AND sc.sno IN (
    SELECT sc.sno 
    FROM sc 
    WHERE sc.cno = 'c02'
  )
);



########## 查询没有选修任何课程的学生记录，输出结果包括学号、姓名和所在院系 ##########
SELECT s.sno, s.sname, s.dept
FROM s LEFT JOIN sc ON s.sno = sc.sno
WHERE sc.sno IS NULL;

第7关：相关子查询

查询选修了至少包含学号为's002'选修了的全部课程的学生学号和姓名

USE student;

#请在此处添加实现代码

########## 查询选修了至少包含学号为's002'选修了的全部课程的学生学号和姓名 ##########

SELECT sno, sname 
FROM s 
WHERE s.sno <> 's002' AND EXISTS (
    SELECT * 
    FROM sc 
    WHERE sc.sno = s.sno AND sc.cno IN (
        SELECT cno 
        FROM sc 
        WHERE sno = 's002'
    )
    GROUP BY sc.sno
    HAVING COUNT(DISTINCT sc.cno) = (
        SELECT COUNT(DISTINCT cno)
        FROM sc
        WHERE sno = 's002'
    )
);

第8关：UNION联合查询

查询来自'北京'或'上海'的学生学号、姓名、所在院系和家庭地址

查询同时选修了编号为'c01'或'c02'课程的学生学号和姓名

USE student;

#请在此处添加实现代码

########## 查询来自'北京'或'上海'的学生学号、姓名、所在院系和家庭地址 ##########
SELECT sno, sname, dept, addr
FROM s
WHERE addr = '北京'
UNION
SELECT sno, sname, dept, addr
FROM s
WHERE addr = '上海';



########## 查询同时选修了编号为'c01'或'c02'课程的学生学号和姓名 ##########
SELECT sno, sname FROM s WHERE sno IN (
    SELECT sno FROM sc WHERE cno = 'c01'
    UNION
    SELECT sno FROM sc WHERE cno = 'c02'
);