多表关联

-- 创建学生表，课程表，成绩表
-- 1. 学生表： 学号，姓名，性别，住在等...
-- 2. 课程表： 课程编号，课程名称，授课教师等...
-- 3. 学生课程-成绩表： 成绩表编号，学号，课程编号，成绩
CREATE TABLE stu1(
sid INT PRIMARY KEY auto_increment,
sname VARCHAR(50) NOT NULL,
gender INT(1),
address VARCHAR(255)
);

CREATE TABLE course(
cid INT PRIMARY KEY auto_increment,
cname VARCHAR(50) NOT NULL,
teacher VARCHAR(50)
);

CREATE TABLE sc(
sc_id INT PRIMARY KEY auto_increment,
s_id INT,
c_id INT,
score INT,
CONSTRAINT FK_SC_STU_S_ID FOREIGN KEY(s_id) REFERENCES stu1(sid),
CONSTRAINT FK_SC_COURSE_C_ID FOREIGN KEY(c_id) REFERENCES course(cid)
);

-- 子查询
-- 在where语句中可以进行另外的一个查询。
-- 例如：查询选择了'编程'这门课的学生
-- 先查询编程课程的编号
SELECT cid FROM course WHERE cname = '编程';
-- 根据 cid 可以去 sc 表查询出学生的 id
SELECT s_id FROM sc WHERE c_id = 1;
-- 根据学生 id 可以去 stu1 查询出学生的信息
SELECT * FROM stu1 WHERE sid in (1, 2, 3, 4);
-- 把上面的sql串起来
SELECT * FROM stu1 WHERE sid IN (
SELECT s_id FROM sc WHERE c_id IN (
SELECT cid FROM course WHERE cname = '编程'
));

-- 查询课程名称为'编程'，且分数低于60的学生姓名和分数
SELECT stu1.sname, sc.score FROM stu1, sc WHERE stu1.sid = sc.s_id AND sc.score < 60 AND sc.c_id IN(
SELECT cid FROM course WHERE cname = '编程'
);

-- 关联查询
-- 关联查询就是把多个表格通过 join 的方式合并在一起，然后进行条件检索.
-- 语法规则：
-- 1. SELECT...FROM  A XXX JOIN B ON A.字段1 = b.字段2
-- 表示： A 表和 B 表连接，通过 A 表的字段1和 B 表的字段2 进行连接. 通常 ON 后面的都是主外键关系

-- INNER JOIN
-- 查询每门课程被选修的学生数
-- COUNT(*)
-- GROUP BY cid
SELECT c.cid,c.cname, COUNT(*) FROM sc INNER JOIN course c ON sc.c_id = c.cid GROUP BY c.cid, c.cname;

-- LEFT JOIN
-- 查询所有学生的选课情况
SELECT s.sname, c.cname FROM stu1 s LEFT JOIN sc ON s.sid = sc.s_id LEFT JOIN course c on sc.c_id = c.cid

-- 查询任何一门课程成绩在70分以上的姓名，课程名称和分数
-- score > 70 sc
-- sname student
-- cname course
SELECT s.sname, c.cname, sc.score FROM stu1 s INNER JOIN sc ON s.sid = sc.s_id INNER JOIN course c ON sc.c_id = c.cid WHERE sc.score > 70;