mysql下的”not exists ( b except A)”解决办法

朋友在使用mysql时提示”not exists(b except A)”错误了,下文章小编整理了一篇此错误问题的解决办法,数据库系统概论第六版中文版中的51页,有个"not exists(b except A)" 的例子,要求查询“找出选修了 Biology 系开设的所有课程的学生”,实验平台搭建去我博客搜索,书上的sql 命令如下:

1. selectS.ID,S.name
2. fromstudentasS
3. wherenotexists((selectcourse_id
4. fromcourse
5. wheredept_name='Biology')
6. except
7. (selectT.course_id
8. fromtakesasT
9. whereS.ID=T.ID));

这个在sql server上运行是没有问题的,但是如果在myql下运行就是如下报错:

1. ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthat
2. correspondstoyourMySQLserverversionfortherightsyntaxtousenear'excep
3. t
4. (selectT.course_id
5. fromtakesasT
6. whereS.ID=T.ID))'atline6
7. mysql>

因为mysql下不支持 except的命令,所以,我们要换个方式来查询“找出选修了 Biology 系开设的所有课程的学生”.

其实,not exists(B except A)和 not in 差不多的,所以,我们可以使用下面的sql命令达到查询要求,先看下student表中的记录:

1. mysql>select*fromstudent;
2. +-------+----------+------------+----------+
3. |ID|name|dept_name|tot_cred|
4. +-------+----------+------------+----------+
5. |00128|Zhang|Comp.Sci.|102|
6. |12345|Shankar|Comp.Sci.|32|
7. |19991|Brandt|History|80|--phpfensi.com
8. |23121|Chavez|Finance|110|
9. |44553|Peltier|Physics|56|
10. |45678|Levy|Physics|46|
11. |54321|Williams|Comp.Sci.|54|
12. |55739|Sanchez|Music|38|
13. |70557|Snow|Physics|0|
14. |76543|Brown|Comp.Sci.|58|
15. |76653|Aoi|Elec.Eng.|60|
16. |98765|Bourikas|Elec.Eng.|98|
17. |98988|Tanaka|Biology|120|
18. +-------+----------+------------+----------+
19. 13rowsinset(0.00sec)

takes表中的记录:

1. mysql>select*fromtakes;
2. +-------+-----------+--------+----------+------+-------+
3. |ID|course_id|sec_id|semester|year|grade|
4. +-------+-----------+--------+----------+------+-------+
5. |00128|CS-101|1|Fall|2009|A|
6. |00128|CS-347|1|Fall|2009|A-|
7. |12345|CS-101|1|Fall|2009|C|
8. |12345|CS-190|2|Spring|2009|A|
9. |12345|CS-315|1|Spring|2010|A|
10. |12345|CS-347|1|Fall|2009|A|
11. |19991|HIS-351|1|Spring|2010|B|
12. |23121|FIN-201|1|Spring|2010|C+|
13. |44553|PHY-101|1|Fall|2009|B-|
14. |45678|CS-101|1|Fall|2009|F|
15. |45678|CS-101|1|Spring|2010|B+|
16. |45678|CS-319|1|Spring|2010|B|
17. |54321|CS-101|1|Fall|2009|A-|
18. |54321|CS-190|2|Spring|2009|B+|
19. |55739|MU-199|1|Spring|2010|A-|
20. |76543|CS-101|1|Fall|2009|A|
21. |76543|CS-319|2|Spring|2010|A|
22. |76653|EE-181|1|Spring|2009|C|
23. |98765|CS-101|1|Fall|2009|C-|
24. |98765|CS-315|1|Spring|2010|B|
25. |98988|BIO-101|1|Summer|2009|A|
26. |98988|BIO-301|1|Summer|2010|NULL|
27. +-------+-----------+--------+----------+------+-------+
28. 22rowsinset(0.00sec)

course表中的记录:

1. mysql>select*fromcourse;
2. +-----------+----------------------------+------------+---------+
3. |course_id|title|dept_name|credits|
4. +-----------+----------------------------+------------+---------+
5. |BIO-101|Intro.toBiology|Biology|4|
6. |BIO-301|Genetics|Biology|4|
7. |BIO-399|ComputationalBiology|Biology|3|
8. |CS-101|Intro.toComputerScience|Comp.Sci.|4|
9. |CS-190|GameDesign|Comp.Sci.|4|
10. |CS-315|Robotics|Comp.Sci.|3|
11. |CS-319|ImageProcessing|Comp.Sci.|3|
12. |CS-347|DatabaseSystemConcepts|Comp.Sci.|3|
13. |EE-181|Intro.toDigitalSystems|Elec.Eng.|3|
14. |FIN-201|InvestmentBanking|Finance|3|
15. |HIS-351|WorldHistory|History|3|
16. |MU-199|MusicVideoProduction|Music|3|
17. |PHY-101|PhysicalPrinciples|Physics|4|
18. +-----------+----------------------------+------------+---------+
19. 13rowsinset(0.00sec)

接着看一下'Biology'系总共开了哪些课程:

1. mysql>selectcourse_id
2. ->fromcourse
3. ->wheredept_name='Biology';
4. +-----------+
5. |course_id|
6. +-----------+
7. |BIO-101|
8. |BIO-301|
9. |BIO-399|
10. +-----------+
11. 3rowsinset(0.00sec)

通过观察,我们的都能轻易看出,“找出选修了 Biology 系开设的所有课程的学生”的结果是,就只有一个叫Tanaka 上了Biology系开的课程.

所以,我们可以将书上的改成except命令改成:

1. selectdistinctS.ID,S.name
2. fromstudentasS,takesasT
3. whereS.ID=T.IDandcourse_idin(
4. selectcourse_id
5. fromcourse
6. wheredept_name='Biology');
7. --查询结果：
8. +-------+--------+
9. |ID|name|
10. +-------+--------+
11. |98988|Tanaka|
12. +-------+--------+
13. 1rowinset(0.03sec)

我们将问题改成“找出选修了 Comp. Sci,系开设的所有课程的学生” ,执行:

1. selectdistinctS.ID,S.name
2. fromstudentasS,takesasT
3. whereS.ID=T.IDandcourse_idin(
4. selectcourse_id
5. fromcourse
6. wheredept_name='Comp.Sci.');
7. --查询结果：
8. +-------+----------+
9. |ID|name|
10. +-------+----------+
11. |00128|Zhang|
12. |12345|Shankar|
13. |45678|Levy|
14. |54321|Williams|
15. |76543|Brown|
16. |98765|Bourikas|
17. +-------+----------+
18. 6rowsinset(0.00sec)