User-Defined Variables

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('1','李强',23,'黑');  
INSERT INTO student VALUES('2','刘丽',22,'白');  
INSERT INTO student VALUES('5','张友',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('1','K1',83);  
INSERT INTO sc VALUES('2','K1',85);  
INSERT INTO sc VALUES('5','K1',92);  
INSERT INTO sc VALUES('2','K5',90);  
INSERT INTO sc VALUES('5','K5',84);  
INSERT INTO sc VALUES('5','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的时间？