MySQL-多表查询练习题3

多表查询-数据准备

-- 创建学生表
CREATE TABLE student (
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(20)
);

-- 创建课程表
CREATE TABLE course (

    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(20)
)

-- 创建学生表和课程表的三方外键关联表
CREATE TABLE stu_course(
    sid INT,
    cid INT,
    FOREIGN KEY (sid) REFERENCES student(sid),
    FOREIGN KEY (cid) REFERENCES course(cid)
)

-- 向学生表中插入数据
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李思思'),(NULL,'李虎');

-- 向课程表中插入数据
INSERT INTO course (cname) VALUES ('JAVA'),('ANDROID'),('IOS'),('PHP'),('C++');

-- 向三方外键关联表中插入数据
INSERT INTO stu_course VALUES (1,1),(1,3),(1,5),(2,1),(2,2),(3,2),(3,3),(3,5);

题目如下：

1.查询每个学生所选择的课程
2.统计每个学生所选课程的数量
3.统计每门课程被多少学生选择
4.选择了两门课的学生
5.一门课都没有选的学生
6.选择了所有课程的学生
7.选了至少一门课的人

提示数据可能不全请自行添加相关数据

参考答案：
（1）查询每个学生所选择的课程

select s.sname, c.cname 
from student s, stu_course sc, course c 
where s.sid = sc.sid and sc.cid = c.cid;

(2)统计每个学生所选课程的数量

select s.sname, count(1) 
from student s, stu_course sc, course c
 where s.sid = sc.sid and sc.cid = c.cid group by s.sname;

(3)统计每门课程被多少学生选择

select c.cname, count(1) 
from student s, stu_course sc, course c 
where s.sid = sc.sid and sc.cid = c.cid group by c.cname;

(4)选择了两门课的学生

select s.sname, count(1) couresNum 
from student s, stu_course sc, course c 
where s.sid = sc.sid and sc.cid = c.cid group by s.sname having couresNum = 2;

(5)一门课都没有选的学生

select s.sname, count(c.cname) courseNum 
from student s left join stu_course sc on s.sid = sc.sid 
left join course c on sc.cid = c.cid group by s.sname having courseNum = 0;

(6)选择了所有课程的学生

select s.sname, count(c.cname) courseNum 
from student s left join stu_course sc on s.sid = sc.sid 
left join course c on sc.cid = c.cid group by s.sname 
having courseNum = (select count(1) from course);

(7)选了至少一门课的人

select s.sname, count(c.cname) courseNum 
from student s left join stu_course sc on s.sid = sc.sid 
left join course c on sc.cid = c.cid group by s.sname 
having courseNum >= 1;