mysql下的”not exists ( b except A)”解決辦法

朋友在使用mysql時提示”not exists ( b except A)”錯誤了，下文章小編整理了一篇此錯誤問題的解決辦法。

數據庫系統概論第六版中文版中的51頁，有個"not exists ( b except A)" 的例子，要求查詢“找出選修了 Biology 系開設的所有課程的學生”。實驗平台搭建去我博客搜索
書上的sql 命令如下：
select S.ID , S.name
from student as S
where not exists (( select course_id
from course
where dept_name = 'Biology')
except
( select T.course_id
from takes as T
where S.ID = T.ID ));
這個在sql server上運行是沒有問題的，但是如果在myql下運行就是如下報錯：
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'excep
t
( select T.course_id
from takes as T
where S.ID = T.ID ))' at line 6
mysql>
因為mysql下不支持 except的命令，所以，我們要換個方式來查詢“找出選修了 Biology 系開設的所有課程的學生”。
其實， not exists ( B except A) 和 not in 差不多的 。 所以，我們可以使用下面的sql命令達到查詢要求：
先看下student表中的記錄：
mysql> select * from student;
+-------+----------+------------+----------+
| ID | name | dept_name | tot_cred |
+-------+----------+------------+----------+
| 00128 | Zhang | Comp. Sci. | 102 |
| 12345 | Shankar | Comp. Sci. | 32 |
| 19991 | Brandt | History | 80 |
| 23121 | Chavez | Finance | 110 |
| 44553 | Peltier | Physics | 56 |
| 45678 | Levy | Physics | 46 |
| 54321 | Williams | Comp. Sci. | 54 |
| 55739 | Sanchez | Music | 38 |
| 70557 | Snow | Physics | 0 |
| 76543 | Brown | Comp. Sci. | 58 |
| 76653 | Aoi | Elec. Eng. | 60 |
| 98765 | Bourikas | Elec. Eng. | 98 |
| 98988 | Tanaka | Biology | 120 |
+-------+----------+------------+----------+
13 rows in set (0.00 sec)
takes表中的記錄：
mysql> select * from takes;
+-------+-----------+--------+----------+------+-------+
| ID | course_id | sec_id | semester | year | grade |
+-------+-----------+--------+----------+------+-------+
| 00128 | CS-101 | 1 | Fall | 2009 | A |
| 00128 | CS-347 | 1 | Fall | 2009 | A- |
| 12345 | CS-101 | 1 | Fall | 2009 | C |
| 12345 | CS-190 | 2 | Spring | 2009 | A |
| 12345 | CS-315 | 1 | Spring | 2010 | A |
| 12345 | CS-347 | 1 | Fall | 2009 | A |
| 19991 | HIS-351 | 1 | Spring | 2010 | B |
| 23121 | FIN-201 | 1 | Spring | 2010 | C+ |
| 44553 | PHY-101 | 1 | Fall | 2009 | B- |
| 45678 | CS-101 | 1 | Fall | 2009 | F |
| 45678 | CS-101 | 1 | Spring | 2010 | B+ |
| 45678 | CS-319 | 1 | Spring | 2010 | B |
| 54321 | CS-101 | 1 | Fall | 2009 | A- |
| 54321 | CS-190 | 2 | Spring | 2009 | B+ |
| 55739 | MU-199 | 1 | Spring | 2010 | A- |
| 76543 | CS-101 | 1 | Fall | 2009 | A |
| 76543 | CS-319 | 2 | Spring | 2010 | A |
| 76653 | EE-181 | 1 | Spring | 2009 | C |
| 98765 | CS-101 | 1 | Fall | 2009 | C- |
| 98765 | CS-315 | 1 | Spring | 2010 | B |
| 98988 | BIO-101 | 1 | Summer | 2009 | A |
| 98988 | BIO-301 | 1 | Summer | 2010 | NULL |
+-------+-----------+--------+----------+------+-------+
22 rows in set (0.00 sec)
course表中的記錄：
mysql> select * from course;
+-----------+----------------------------+------------+---------+
| course_id | title | dept_name | credits |
+-----------+----------------------------+------------+---------+
| BIO-101 | Intro. to Biology | Biology | 4 |
| BIO-301 | Genetics | Biology | 4 |
| BIO-399 | Computational Biology | Biology | 3 |
| CS-101 | Intro. to Computer Science | Comp. Sci. | 4 |
| CS-190 | Game Design | Comp. Sci. | 4 |
| CS-315 | Robotics | Comp. Sci. | 3 |
| CS-319 | Image Processing | Comp. Sci. | 3 |
| CS-347 | Database System Concepts | Comp. Sci. | 3 |
| EE-181 | Intro. to Digital Systems | Elec. Eng. | 3 |
| FIN-201 | Investment Banking | Finance | 3 |
| HIS-351 | World History | History | 3 |
| MU-199 | Music Video Production | Music | 3 |
| PHY-101 | Physical Principles | Physics | 4 |
+-----------+----------------------------+------------+---------+
13 rows in set (0.00 sec)
接著看一下'Biology'系總共開了哪些課程：
mysql> select course_id
-> from course
-> where dept_name = 'Biology';
+-----------+
| course_id |
+-----------+
| BIO-101 |
| BIO-301 |
| BIO-399 |
+-----------+
3 rows in set (0.00 sec)
通過觀察，我們的都能輕易看出，“找出選修了 Biology 系開設的所有課程的學生”的結果是，就只有一個叫Tanaka 上了Biology系開的課程。
所以，我們可以將書上的改成except命令改成：
select distinct S.ID , S.name
from student as S ,takes as T
where S.ID = T.ID and course_id in (
select course_id
from course
where dept_name = 'Biology');
查詢結果：
+-------+--------+
| ID | name |
+-------+--------+
| 98988 | Tanaka |
+-------+--------+
1 row in set (0.03 sec)
我們將問題改成“找出選修了 Comp. Sci. 系開設的所有課程的學生” ，
執行：
select distinct S.ID , S.name
from student as S ,takes as T
where S.ID = T.ID and course_id in (
select course_id
from course
where dept_name = 'Comp. Sci.');
查詢結果：
+-------+----------+
| ID | name |
+-------+----------+
| 00128 | Zhang |
| 12345 | Shankar |
| 45678 | Levy |
| 54321 | Williams |
| 76543 | Brown |
| 98765 | Bourikas |
+-------+----------+
6 rows in set (0.00 sec)
教程完！