mysql> SET @w := SELECT COUNT(*) FROM course WHERE cteacher='程军';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
the right syntax to use near 'SELECT COUNT(*) FROM course WHERE cteacher='程军'' at line 1
mysql> SELECT @w;
+------+
| @w |
+------+
| NULL |
+------+
1 row in set (0.00 sec) mysql> SET @w := (SELECT COUNT(*) FROM course WHERE cteacher='程军');
Query OK, 0 rows affected (0.00 sec) mysql> SELECT @w;
+------+
| @w |
+------+
| 2 |
+------+
1 row in set (0.00 sec) mysql>
SHOW TABLES;
DROP TABLE IF EXISTS student;
SHOW TABLES;
CREATE TABLE IF NOT EXISTS student
(
sno int primary key,
sname VARCHAR(20) CHARACTER SET gbk,
sage TINYINT,
shair ENUM('黑','白') CHARACTER SET gbk
);
INSERT INTO student VALUES('','李强',23,'黑');
INSERT INTO student VALUES('','刘丽',22,'白');
INSERT INTO student VALUES('','张友',22,'黑');
SELECT * FROM student; SHOW TABLES;
DROP TABLE IF EXISTS course;
SHOW TABLES;
CREATE TABLE IF NOT EXISTS course
(
cno VARCHAR(10) primary key,
cname VARCHAR(20) CHARACTER SET gbk,
cteacher VARCHAR(20) CHARACTER SET gbk
);
INSERT INTO course VALUES('k1','c语言','王华');
INSERT INTO course VALUES('k5','数据库原理','程军');
INSERT INTO course VALUES('k8','编译原理','程军');
SELECT * FROM course; SHOW TABLES;
DROP TABLE IF EXISTS sc;
SHOW TABLES;
CREATE TABLE IF NOT EXISTS sc
(
fk_sno INT NOT NULL,
fk_cno VARCHAR(10) NOT NULL,
score INT NOT NULL,
PRIMARY KEY(fk_sno,fk_cno),
FOREIGN KEY(fk_sno) REFERENCES student(sno),
FOREIGN KEY(fk_cno) REFERENCES course(cno)
);
INSERT INTO sc VALUES('','K1',83);
INSERT INTO sc VALUES('','K1',85);
INSERT INTO sc VALUES('','K1',92);
INSERT INTO sc VALUES('','K5',90);
INSERT INTO sc VALUES('','K5',84);
INSERT INTO sc VALUES('','K8',80);
SELECT * FROM sc;
1-检索至少选修＂程军＂老师所授全部课程的学生姓名（SNAME）
检索选修了“程军”老师所授全部课程的学生姓名
0-检索选修了“程军”老师所授课程的学生
SELECT * FROM sc RIGHT JOIN course ON sc.fk_cno=course.cno;
SELECT * FROM (SELECT * FROM sc RIGHT JOIN course ON sc.fk_cno=course.cno) w RIGHT JOIN student ON w.fk_sno=student.sno;
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-选了程老师所有的课
/*
假定
“ 某学生选程老师的课程门数等于程老师所开门数”等价于“该学生至少选了程老师的所有的课程”。
*/
SELECT sname FROM student
WHERE
(
SELECT COUNT(*) FROM
(
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 = '程军'
) wb
WHERE wb.sname = student.sname
)
=
(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-程序表达： */
SELECT sname FROM student
WHERE NOT EXISTS
(
SELECT * FROM course
WHERE cteacher='程军' AND NOT EXISTS
(
SELECT * FROM sc
WHERE sc.fk_sno = student.sno AND sc.fk_cno = course.cno
)
) ;
2- 检索选修全部课程的学生姓名(SNAME)。
SELECT sname FROM student
WHERE
(
SELECT COUNT(*) FROM
(
SELECT * FROM (SELECT * FROM sc RIGHT JOIN course ON sc.fk_cno=course.cno) w RIGHT JOIN student ON w.fk_sno=student.sno
) wb
WHERE wb.sname = student.sname
)
=
(SELECT COUNT(*) FROM course ); SELECT sname FROM student
WHERE NOT EXISTS
(
SELECT * FROM course
WHERE NOT EXISTS
(
SELECT * FROM sc
WHERE sc.fk_sno = student.sno AND sc.fk_cno = course.cno
)
); SELECT * FROM course
WHERE NOT EXISTS
(
SELECT 3>5
); SELECT * FROM course
WHERE EXISTS
(
SELECT 3>5
);
explain
mysql> explain
-> SELECT sname FROM student
-> WHERE
-> (
-> SELECT COUNT(*) FROM
-> (
-> SELECT * FROM (SELECT * FROM sc RIGHT JOIN course ON sc.fk_cno=course.cno) w RIGHT JOIN student ON w.fk_sno=student.sno
-> ) wb
-> WHERE wb.sname = student.sname
-> )
-> =
-> (SELECT COUNT(*) FROM course );
+----+--------------------+---------+------------+--------+----------------+---------+---------+-------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------+------------+--------+----------------+---------+---------+-------------------+------+----------+------------------------------+
| 1 | PRIMARY | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 5 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DEPENDENT SUBQUERY | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 2 | DEPENDENT SUBQUERY | sc | NULL | ref | PRIMARY,fk_cno | PRIMARY | 4 | w0811.student.sno | 2 | 100.00 | Using index |
| 2 | DEPENDENT SUBQUERY | course | NULL | eq_ref | PRIMARY | PRIMARY | 12 | w0811.sc.fk_cno | 1 | 100.00 | Using index |
+----+--------------------+---------+------------+--------+----------------+---------+---------+-------------------+------+----------+------------------------------+
5 rows in set, 2 warnings (0.00 sec) mysql>
mysql> explain
-> SELECT sname FROM student
-> WHERE NOT EXISTS
-> (
-> SELECT * FROM course
-> WHERE NOT EXISTS
-> (
-> SELECT * FROM sc
-> WHERE sc.fk_sno = student.sno AND sc.fk_cno = course.cno
-> )
-> );
+----+--------------------+---------+------------+--------+----------------+---------+---------+------------------------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------+------------+--------+----------------+---------+---------+------------------------------------+------+----------+--------------------------+
| 1 | PRIMARY | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | course | NULL | index | NULL | PRIMARY | 12 | NULL | 3 | 100.00 | Using where; Using index |
| 3 | DEPENDENT SUBQUERY | sc | NULL | eq_ref | PRIMARY,fk_cno | PRIMARY | 16 | w0811.student.sno,w0811.course.cno | 1 | 100.00 | Using index |
+----+--------------------+---------+------------+--------+----------------+---------+---------+------------------------------------+------+----------+--------------------------+
3 rows in set, 3 warnings (0.00 sec) mysql>
“带入遍历”，“重复循环”，这是上边代码原貌，而两种思路的区别仅仅是让计算机计算时怎样“重复次数”少一点。
http://blog.csdn.net/wolfofsiberian/article/details/39346781
发问:
0-语言只是工具而已，不是解决问题方法的源泉，怎么减少对接语法API的时间？