学习记录375@查询学过“谌燕”老师所教的所有课的同学的学号、姓名-MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
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 2005','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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT DISTINCT student.sno,student.sname
FROM student
WHERE student.sno IN(
-- in中查询满足条件的学号
	SELECT sc.sno
	FROM sc 
	JOIN course
	ON course.cno=sc.cno
	JOIN teacher
	ON course.tno=teacher.tno
	WHERE teacher.tname='谌燕'
	GROUP BY sc.sno
-- 	HAVING判断分组下学生学的谌燕老师得课程数量是否和谌燕老师教的课程总数相同
	HAVING COUNT(DISTINCT sc.cno)=(
-- 	子查询查谌燕老师教得课程总数
		SELECT COUNT(course.cno)
		FROM course
		LEFT JOIN teacher
		ON course.tno=teacher.tno
		WHERE teacher.tname='谌燕'
	)
)