大家好，对mysql下的”not exists ( b except A)”解决办法感兴趣的小伙伴，下面一起跟随三零脚本的小编来看看mysql下的”not exists ( b except A)”解决办法的例子吧。
朋友在使用mysql时提示”not exists(b except A)”错误了,下文章小编整理了一篇此错误问题的解决办法,数据库系统概论第六版中文版中的51页,有个"not exists(b except A)" 的例子,要求查询“找出选修了 Biology 系开设的所有课程的学生”,实验平台搭建去我博客搜索,书上的sql 命令如下:
selectS.ID,S.name fromstudentasS
wherenotexists((selectcourse_id fromcourse
wheredept_name='Biology') except
(selectT.course_id fromtakesasT
whereS.ID=T.ID));
这个在sql server上运行是没有问题的,但是如果在myql下运行就是如下报错:
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthat correspondstoyourMySQLserverversionfortherightsyntaxtousenear'excep
t (selectT.course_id
fromtakesasT whereS.ID=T.ID))'atline6
mysql>
因为mysql下不支持 except的命令,所以,我们要换个方式来查询“找出选修了 Biology 系开设的所有课程的学生”.
其实,not exists(B except A)和 not in 差不多的,所以,我们可以使用下面的sql命令达到查询要求,先看下student表中的记录:
mysql>select*fromstudent; +-------+----------+------------+----------+
|ID|name|dept_name|tot_cred| +-------+----------+------------+----------+
|00128|Zhang|Comp.Sci.|102| |12345|Shankar|Comp.Sci.|32|
|19991|Brandt|History|80|--q3060.com |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| +-------+----------+------------+----------+
13rowsinset(0.00sec)
takes表中的记录:
mysql>select*fromtakes; +-------+-----------+--------+----------+------+-------+
|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|
+-------+-----------+--------+----------+------+-------+ 22rowsinset(0.00sec)
course表中的记录:
mysql>select*fromcourse; +-----------+----------------------------+------------+---------+
|course_id|title|dept_name|credits| +-----------+----------------------------+------------+---------+
|BIO-101|Intro.toBiology|Biology|4| |BIO-301|Genetics|Biology|4|
|BIO-399|ComputationalBiology|Biology|3| |CS-101|Intro.toComputerScience|Comp.Sci.|4|
|CS-190|GameDesign|Comp.Sci.|4| |CS-315|Robotics|Comp.Sci.|3|
|CS-319|ImageProcessing|Comp.Sci.|3| |CS-347|DatabaseSystemConcepts|Comp.Sci.|3|
|EE-181|Intro.toDigitalSystems|Elec.Eng.|3| |FIN-201|InvestmentBanking|Finance|3|
|HIS-351|WorldHistory|History|3| |MU-199|MusicVideoProduction|Music|3|
|PHY-101|PhysicalPrinciples|Physics|4| +-----------+----------------------------+------------+---------+
13rowsinset(0.00sec)
接着看一下'Biology'系总共开了哪些课程:
mysql>selectcourse_id ->fromcourse
->wheredept_name='Biology'; +-----------+
|course_id| +-----------+
|BIO-101| |BIO-301|
|BIO-399| +-----------+
3rowsinset(0.00sec)
通过观察,我们的都能轻易看出,“找出选修了 Biology 系开设的所有课程的学生”的结果是,就只有一个叫Tanaka 上了Biology系开的课程.
所以,我们可以将书上的改成except命令改成:
selectdistinctS.ID,S.name fromstudentasS,takesasT
whereS.ID=T.IDandcourse_idin( selectcourse_id
fromcourse wheredept_name='Biology');
--查询结果： +-------+--------+
|ID|name| +-------+--------+
|98988|Tanaka| +-------+--------+
1rowinset(0.03sec)
我们将问题改成“找出选修了 Comp. Sci,系开设的所有课程的学生” ,执行:
selectdistinctS.ID,S.name fromstudentasS,takesasT
whereS.ID=T.IDandcourse_idin( selectcourse_id
fromcourse wheredept_name='Comp.Sci.');
--查询结果： +-------+----------+
|ID|name| +-------+----------+
|00128|Zhang| |12345|Shankar|
|45678|Levy| |54321|Williams|
|76543|Brown| |98765|Bourikas|
+-------+----------+ 6rowsinset(0.00sec)
