SQL语句中练习
- 格式：doc
- 大小：58.00 KB
- 文档页数：9
使用子查询进行存在性测试时，通常使用Exists谓词，其形式为：Where 【Not】Exists（子查询）
Exists或者Not Exists是把内层子查询的结果集为空否（注意：不是数据）传到外层查询中作为条件，返回值是逻辑真值True或者逻辑假值False。
若内层子查询的结果集为空，则施加Exists作用的结果为False，而施加Not Exists作用的结果为True；若内层子查询的结果集为非空，则施加Exists作用的结果为True，而施加Not Exists作用的结果为False。
当为True时，那么就是外层查询条件成立，一般结果会显示出来。
当为False时，外层查询条件不成立。
例1 查询至少有一门选修课课程不及格的学生名单。
方法一
SELECT 姓名FROM 学生
where exists( select * from 选课
where 学生.学号=学号and 成绩<60) 注意成绩若为空值Null被忽略。
方法二
SELECT 姓名FROM 学生
where 学号in (select 学号from 选课
where 成绩<60)
方法三
SELECT distinct 姓名FROM 学生,选课
where 学生.学号=选课.学号and 成绩<60
例2 查询至少有两门选修课课程不及格的学生名单。
方法一
SELECT 姓名FROM 学生
where exists ( select * from 选课
where 学生.学号=选课.学号and 成绩<60
group by 学号having count(*)>=2)
方法二
SELECT 姓名FROM 学生
where 学号in
(select 学号from 选课
where 成绩<60
group by 学号having count(*)>=2)
方法三
SELECT 姓名FROM 学生,选课
where 学生.学号=选课.学号and 成绩<60
group by 学生.学号,姓名having count(*)>=2
例3 查询选修了全部课程的学生的姓名。
方法一
SELECT 姓名from 学生,选课
where 学生.学号=选课.学号
group by 学生.学号,姓名/*分列组*/
having count(*)=(select count(*) from 课程)/*组选择条件*/
方法二
Select 姓名From 学生
Where Not Exists (Select * From 课程
Where Not Exists (Select * From 选课
Where 学生.学号=学号And 课程.课程号=课程号))
解读:由于没有全称量词，因此可以将题目的意思转换成等价的适合应用存在量词的形式：查询这样的学生姓名，没有一门课程是他不选修的。
第一个Not Exists表示不存在这样的课程元组，第二个Not Exists表示没有选修的选课元组。
写命令的思路：
首先写出查询被查询学生（设学号为x）没有选修的课
程记录
Select * From 课程
Where Not Exists (Select * From 选课
Where 学号= x And 课程.课程号=课程号)
或
Select * From 课程
where 课程号not in
(select 课程号from 选课where 学号=x)
如果上述查询结果为空，则说明该学生(学号为x)选修了全部选修课，所以查询选修了全部课程的学生姓名的查询语句为：
Select 姓名From 学生
Where Not Exists (Select * From 课程
Where Not Exists (Select * From 选课
Where 学生.学号=学号And 课程.课程号=课程号))
或
Select 姓名From 学生
Where Not Exists ( Select * From 课程
where 课程号not in
(select 课程号from 选课where 学号=学生.学号))
系统执行过程猜测:
假设有m个学生,编号为S1,S2,…,Sm，有n门课程,编号为C1,C2,…,Cn。
step 0：i=1
step 1：若i≤m，则选择Si，否则结束。
step 2：逐项检查<Si,C1>,<Si,C2>,…,<Si,Cn>是否在选课
表中，将未出现在选课表中的课程记录送入结果集。
若此结果集为空，则显示Si所对应的姓名，否则不显示。
step 3：i=i+1,转向step 1。
另：试着将Not Exists 改成Exists，查询何意呢？
Select 姓名From 学生
Where Not Exists (Select * From 课程
Where Exists (Select * From 选课
Where 学生.学号=学号And 课程.课程号=课程号))
意为：查询没有选课记录的学生的姓名。
（选课门数为0）等价于：Select 姓名From 学生
Where 学号Not in (Select 学号From 选课)
Select 姓名From 学生
Where Exists (Select * From 课程
Where not Exists (Select * From 选课
Where 学生.学号=学号And 课程.课程号=课程号))
意为：查询有缺选课程的学生的姓名。
（0≤选课门数≤n-1）查询未全部选修所有课程的学生的姓名。
问是否等价于如下命令？若不是，该如何修改为等价形式！
SELECT 姓名from 学生,选课
where 学生.学号=选课.学号
group by 学生.学号,姓名
having count(*)<(select count(*) from 课程) Select 姓名From 学生
Where Exists (Select * From 课程
Where Exists (Select * From 选课
Where 学生.学号=学号And 课程.课程号=课程号))
意为：查询有选课记录的学生的姓名。
（1≤选课门数≤n）等价于：Select 姓名From 学生
Where 学号in (Select 学号From 选课)
例4 查询至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。
(含s2 学生在内)
首先写出学号为“S2”学生选修的所有课程:
select 课程号from 选课
where 学号='s2'
上述课程中没有被某学生(学号为x)选修的课程：select * from 课程
where 课程号in
(select 课程号from 选课where 学号='s2') and 课程号not in
(select 课程号from 选课where 学号=x) 若上述查询结果为空，表示某学生(学号为x)选修了“S2”
学生选修的所有课程，所以最终的查询语句为：
select 学号,姓名from 学生
where not exists(
select * from 课程
where 课程号in
(select 课程号from 选课where 学号='s2') and 课程号not in
(select 课程号from 选课where 学号=学生.学号) )
若将上述红色字体部分可改写为:
SELECT 课程号FROM 选课选课1
WHERE 选课1.学号='S2' AND
NOT EXISTS
(SELECT * FROM 选课选课2
WHERE 学生.学号=选课2.学号AND
选课2.课程号=选课1.课程号)
则有下列等价命令:
select 学号,姓名from 学生
where not exists(
SELECT 课程号FROM 选课选课1
WHERE 选课1.学号='S2' AND
NOT EXISTS
(SELECT * FROM 选课选课2
WHERE 学生.学号=选课2.学号AND
选课2.课程号=选课1.课程号)
)
练习
1、设在teacher数据库内有如下3个基本表：
教师(教师编号、姓名、性别、年龄、职称、所在专业) 课程(课程号、课程名、课程性质、学分)
教师授课(教师编号、课程号、教龄)，这里的教龄是指教授这门课的教龄。
试用SQL语句完成下列操作：
（1）查询担任c2课程的教帅姓名。
（2）查询所有未担任C2课程的教师姓名。
（3）查询担任全部课程的教师姓名。
2、设在处方管理数据库内有如下3个基本表：
药品信息表Drug（药品编号、药品名称、药品类型、药品剂型、药品单位、药品数量、药品价格、药品生产日期、药品过期日期）
医生处方表Prescription（处方编号、医生编号）
处方用药表PD（处方编号、药品编号、药品用量）
试用SQL语句完成下列操作：
（1）查询所有已经使用过药品的编号和名称。
（2）查询用到了所有药品的医生编号。
