1 mysql> SET @w := SELECT COUNT(*) FROM course WHERE cteacher='程军';
2 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
3 the right syntax to use near 'SELECT COUNT(*) FROM course WHERE cteacher='程军'' at line 1
4 mysql> SELECT @w;
5 +------+
6 | @w |
7 +------+
8 | NULL |
9 +------+
10 1 row in set (0.00 sec)
11
12 mysql> SET @w := (SELECT COUNT(*) FROM course WHERE cteacher='程军');
13 Query OK, 0 rows affected (0.00 sec)
14
15 mysql> SELECT @w;
16 +------+
17 | @w |
18 +------+
19 | 2 |
20 +------+
21 1 row in set (0.00 sec)
22
23 mysql>
1 SHOW TABLES;
2 DROP TABLE IF EXISTS student;
3 SHOW TABLES;
4 CREATE TABLE IF NOT EXISTS student
5 (
6 sno int primary key,
7 sname VARCHAR(20) CHARACTER SET gbk,
8 sage TINYINT,
9 shair ENUM('黑','白') CHARACTER SET gbk
10 );
11 INSERT INTO student VALUES('1','李强',23,'黑');
12 INSERT INTO student VALUES('2','刘丽',22,'白');
13 INSERT INTO student VALUES('5','张友',22,'黑');
14 SELECT * FROM student;
15
16 SHOW TABLES;
17 DROP TABLE IF EXISTS course;
18 SHOW TABLES;
19 CREATE TABLE IF NOT EXISTS course
20 (
21 cno VARCHAR(10) primary key,
22 cname VARCHAR(20) CHARACTER SET gbk,
23 cteacher VARCHAR(20) CHARACTER SET gbk
24 );
25 INSERT INTO course VALUES('k1','c语言','王华');
26 INSERT INTO course VALUES('k5','数据库原理','程军');
27 INSERT INTO course VALUES('k8','编译原理','程军');
28 SELECT * FROM course;
29
30 SHOW TABLES;
31 DROP TABLE IF EXISTS sc;
32 SHOW TABLES;
33 CREATE TABLE IF NOT EXISTS sc
34 (
35 fk_sno INT NOT NULL,
36 fk_cno VARCHAR(10) NOT NULL,
37 score INT NOT NULL,
38 PRIMARY KEY(fk_sno,fk_cno),
39 FOREIGN KEY(fk_sno) REFERENCES student(sno),
40 FOREIGN KEY(fk_cno) REFERENCES course(cno)
41 );
42 INSERT INTO sc VALUES('1','K1',83);
43 INSERT INTO sc VALUES('2','K1',85);
44 INSERT INTO sc VALUES('5','K1',92);
45 INSERT INTO sc VALUES('2','K5',90);
46 INSERT INTO sc VALUES('5','K5',84);
47 INSERT INTO sc VALUES('5','K8',80);
48 SELECT * FROM sc;
1-检索至少选修＂程军＂老师所授全部课程的学生姓名（SNAME）
检索选修了“程军”老师所授全部课程的学生姓名
0-检索选修了“程军”老师所授课程的学生
1 SELECT * FROM sc RIGHT JOIN course ON sc.fk_cno=course.cno;
2 SELECT * FROM (SELECT * FROM sc RIGHT JOIN course ON sc.fk_cno=course.cno) w RIGHT JOIN student ON w.fk_sno=student.sno;
3 SELECT * FROM (SELECT * FROM sc RIGHT JOIN course ON sc.fk_cno=course.cno) w RIGHT JOIN student ON w.fk_sno=student.sno WHERE cteacher='程军';
集合子集，逐步精确，缩小范围 1-是学生-2-选程老师的课-3-选了程老师所有的课
/*
假定
“ 某学生选程老师的课程门数等于程老师所开门数”等价于“该学生至少选了程老师的所有的课程”。
*/
1 SELECT sname FROM student
2 WHERE
3 (
4 SELECT COUNT(*) FROM
5 (
6 SELECT * FROM (SELECT * FROM sc RIGHT JOIN course ON sc.fk_cno=course.cno) w RIGHT JOIN student ON w.fk_sno=student.sno
7 WHERE cteacher = '程军'
8 ) wb
9 WHERE wb.sname = student.sname
10 )
11 =
12 (SELECT COUNT(*) FROM course WHERE cteacher='程军');
/* 0-澄清问题：找出至少选了程老师全部课程的学生：学生-没有选程老师的课+学生选了程老师的部分的课+学生选了程老师全部的课
等价转化：找出选了程老师部分的课或者没有选程老师的课的学生
数据关系：学生和老师没有数据关系，是两个完全独立的数据对象，分数是桥梁
等价转化：
选了部分程老师的课 ，NOT EXISTS (SELECT * FROM sc WHERE sc.sno = student AND sc.cno=course.cno)->FALSE (内层SELECT不空，TRUE,退出)
没有选程老师的课 teacher='程军'->FALSE ,后边的不用看了(中层SELECT空，FALSE，退出)
外中内-三层SELECT，外层返回的是最终的结果
最外层选得的最终量result，学生姓名，它的措施是将各个学生名称带入，逐个检测
1-程序表达： */
1 SELECT sname FROM student
2 WHERE NOT EXISTS
3 (
4 SELECT * FROM course
5 WHERE cteacher='程军' AND NOT EXISTS
6 (
7 SELECT * FROM sc
8 WHERE sc.fk_sno = student.sno AND sc.fk_cno = course.cno
9 )
10 ) ;
2- 检索选修全部课程的学生姓名(SNAME)。
1 SELECT sname FROM student
2 WHERE
3 (
4 SELECT COUNT(*) FROM
5 (
6 SELECT * FROM (SELECT * FROM sc RIGHT JOIN course ON sc.fk_cno=course.cno) w RIGHT JOIN student ON w.fk_sno=student.sno
7 ) wb
8 WHERE wb.sname = student.sname
9 )
10 =
11 (SELECT COUNT(*) FROM course );
12
13 SELECT sname FROM student
14 WHERE NOT EXISTS
15 (
16 SELECT * FROM course
17 WHERE NOT EXISTS
18 (
19 SELECT * FROM sc
20 WHERE sc.fk_sno = student.sno AND sc.fk_cno = course.cno
21 )
22 );
23
24 SELECT * FROM course
25 WHERE NOT EXISTS
26 (
27 SELECT 3>5
28 );
29
30 SELECT * FROM course
31 WHERE EXISTS
32 (
33 SELECT 3>5
34 );
explain
1 mysql> explain
2 -> SELECT sname FROM student
3 -> WHERE
4 -> (
5 -> SELECT COUNT(*) FROM
6 -> (
7 -> SELECT * FROM (SELECT * FROM sc RIGHT JOIN course ON sc.fk_cno=course.cno) w RIGHT JOIN student ON w.fk_sno=student.sno
8 -> ) wb
9 -> WHERE wb.sname = student.sname
10 -> )
11 -> =
12 -> (SELECT COUNT(*) FROM course );
13 +----+--------------------+---------+------------+--------+----------------+---------+---------+-------------------+------+----------+------------------------------+
14 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
15 +----+--------------------+---------+------------+--------+----------------+---------+---------+-------------------+------+----------+------------------------------+
16 | 1 | PRIMARY | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
17 | 5 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
18 | 2 | DEPENDENT SUBQUERY | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
19 | 2 | DEPENDENT SUBQUERY | sc | NULL | ref | PRIMARY,fk_cno | PRIMARY | 4 | w0811.student.sno | 2 | 100.00 | Using index |
20 | 2 | DEPENDENT SUBQUERY | course | NULL | eq_ref | PRIMARY | PRIMARY | 12 | w0811.sc.fk_cno | 1 | 100.00 | Using index |
21 +----+--------------------+---------+------------+--------+----------------+---------+---------+-------------------+------+----------+------------------------------+
22 5 rows in set, 2 warnings (0.00 sec)
23
24 mysql>
25 mysql> explain
26 -> SELECT sname FROM student
27 -> WHERE NOT EXISTS
28 -> (
29 -> SELECT * FROM course
30 -> WHERE NOT EXISTS
31 -> (
32 -> SELECT * FROM sc
33 -> WHERE sc.fk_sno = student.sno AND sc.fk_cno = course.cno
34 -> )
35 -> );
36 +----+--------------------+---------+------------+--------+----------------+---------+---------+------------------------------------+------+----------+--------------------------+
37 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
38 +----+--------------------+---------+------------+--------+----------------+---------+---------+------------------------------------+------+----------+--------------------------+
39 | 1 | PRIMARY | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
40 | 2 | DEPENDENT SUBQUERY | course | NULL | index | NULL | PRIMARY | 12 | NULL | 3 | 100.00 | Using where; Using index |
41 | 3 | DEPENDENT SUBQUERY | sc | NULL | eq_ref | PRIMARY,fk_cno | PRIMARY | 16 | w0811.student.sno,w0811.course.cno | 1 | 100.00 | Using index |
42 +----+--------------------+---------+------------+--------+----------------+---------+---------+------------------------------------+------+----------+--------------------------+
43 3 rows in set, 3 warnings (0.00 sec)
44
45 mysql>
“带入遍历”，“重复循环”，这是上边代码原貌，而两种思路的区别仅仅是让计算机计算时怎样“重复次数”少一点。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
