这玩意折磨了我一个小时，必须记录一下！解题思路和答案在最后。
相关表
CREATE DATABASE stumsc;CREATE TABLE student(sno VARCHAR(10) PRIMARY KEY,sname VARCHAR(20),sage INT,ssex VARCHAR(5));CREATE TABLE teacher(tno VARCHAR(10) PRIMARY KEY,tname VARCHAR(20));CREATE TABLE course(cno VARCHAR(10),cname VARCHAR(20),tno VARCHAR(20),PRIMARY KEY (cno,tno));CREATE TABLE sc(sno VARCHAR(10),cno VARCHAR(10),score DOUBLE(4,2),PRIMARY KEY (sno,cno));/*******初始化学生表的数据******/INSERT INTO student VALUES ('s001','张三',23,'男');INSERT INTO student VALUES ('s002','李四',23,'男');INSERT INTO student VALUES ('s003','吴鹏',25,'男');INSERT INTO student VALUES ('s004','琴沁',20,'女');INSERT INTO student VALUES ('s005','王丽',20,'女');INSERT INTO student VALUES ('s006','李波',21,'男');INSERT INTO student VALUES ('s007','刘玉',21,'男');INSERT INTO student VALUES ('s008','萧蓉',21,'女');INSERT INTO student VALUES ('s009','陈萧晓',23,'女');INSERT INTO student VALUES ('s010','陈美',22,'女');COMMIT;/******************初始化教师表***********************/INSERT INTO teacher VALUES ('t001', '刘阳');INSERT INTO teacher VALUES ('t002', '谌燕');INSERT INTO teacher VALUES ('t003', '胡明星');COMMIT;/***************初始化课程表****************************/INSERT INTO course VALUES ('c001','J2SE','t002');INSERT INTO course VALUES ('c002','Java Web','t002');INSERT INTO course VALUES ('c003','SSH','t001');INSERT INTO course VALUES ('c004','Oracle','t001');INSERT INTO course VALUES ('c005','SQL SERVER ','t003');INSERT INTO course VALUES ('c006','C#','t003');INSERT INTO course VALUES ('c007','JavaScript','t002');INSERT INTO course VALUES ('c008','DIV+CSS','t001');INSERT INTO course VALUES ('c009','PHP','t003');INSERT INTO course VALUES ('c010','EJB3.0','t002');COMMIT;/***************初始化成绩表***********************/INSERT INTO sc VALUES ('s001','c001',78.9);INSERT INTO sc VALUES ('s002','c001',80.9);INSERT INTO sc VALUES ('s003','c001',81.9);INSERT INTO sc VALUES ('s004','c001',60.9);INSERT INTO sc VALUES ('s001','c002',82.9);INSERT INTO sc VALUES ('s002','c002',72.9);INSERT INTO sc VALUES ('s003','c002',81.9);INSERT INTO sc VALUES ('s001','c003',59.0);COMMIT;
关联关系
查询学过“谌燕”老师所教的所有课的同学的学号、姓名
主要是解决学过XXX的所有课程的问题，怎么判断学过所有的课程呢？IN 肯定是不行的，得转换思路：可以判断course表中谌燕老师得所有课程得数量与sc表中学生分组下学的谌燕老师得课程得总数是否相同，如果相同，则说明学了所有课程！
SELECT DISTINCT student.sno,student.snameFROM studentWHERE student.sno IN(-- in中查询满足条件的学号SELECT sc.snoFROM sc JOIN courseON o=oJOIN teacherON course.tno=teacher.tnoWHERE teacher.tname='谌燕'GROUP BY sc.sno-- HAVING判断分组下学生学的谌燕老师得课程数量是否和谌燕老师教的课程总数相同HAVING COUNT(DISTINCT o)=(-- 子查询查谌燕老师教得课程总数SELECT COUNT(o)FROM courseLEFT JOIN teacherON course.tno=teacher.tnoWHERE teacher.tname='谌燕'))
