《chapter03-chinese.ppt》由会员分享,可在线阅读,更多相关《chapter03-chinese.ppt(47页珍藏版)》请在三一文库上搜索。
1、3.7 嵌套子查询,嵌套子查询 出现在父查询的From子句中 出现在父查询的Where子句中,在From子句中用子查询构造派生关系,在From子句中,允许用子查询构造新的关系,称为派生关系 新关系必须命名。其属性也可以重命名 格式 From , (SQL 子查询) (as) 关系名 , From , (SQL子查询) (as) 关系名(属性1, 属性2) ,在From子句中用子查询构造派生关系,例 R: 选修关系 问: 每门课程的最高成绩和相应学生姓名?,R,Select R.课程, 姓名, 最高成绩 From R, () as S Where R.成绩=S.最高成绩 and R.课程=S.课
2、程,Select 课程, Max(成绩) as 最高成绩 From R Group By 课程,在From子句中用子查询构造派生关系,正确理解派生关系的作用 把一个复杂的查询分解成很多步,把上一步的查询结果变成一个派生关系,然后在下一步查询中使用。从而一步步地完成复杂的查询。,在Where子句中 用子查询构造条件,条件1: 判定一个值(例如属性值)是否在查询结果中 in运算符 格式 A not in (子查询) 要求 A往往是一个值,此时子查询要相当于一个值的集合。即子查询可以有多个元组,但只能有一个属性 元组只有一个属性时,相当于一个单值,子查询的结果是一个这样的元组集合,即多个单值的集合
3、结果 当A是子查询结果中的一个值时,结果为真。否则为假,在Where子句中 用子查询构造条件,例 R: 修选关系 问: 没有选修数学的学生姓名?,R,Select 姓名 From R Where 姓名 not in (Select 姓名 From R Where 课程=数学 ),在Where子句中 用子查询构造条件,条件2: 判定查询结果是否为空 exist运算符 格式 not exists (子查询) 结果 exist的含义为存在(至少一条)记录的判定。所以当子查询结果为空(不包含任何记录)时,exists的结果为假,否则为真。 要点 子查询可包含一个或多个属性(exist是对记录的判断,和
4、属性无关) 子查询可引用父查询关系的属性(此时最好标明关系前缀),作为子查询的参数。这一点对于其它类型(in、some、all)的子查询也是适用的,在Where子句中 用子查询构造条件,例 R: 选修关系; S: 学生关系 问: 哪个学生没有选修任何课程? 列出姓名和年龄,Select 姓名, 年龄 From S Where not exists (Select * From R Where R.姓名=S.姓名),S,R,在Where子句中 用子查询构造条件,条件3: 将一个值(例如属性值)与查询结果做比较 some|all运算符 格式 A (比较运算, , , =,) some|all (子
5、查询) 要求 A是一个值,而后面的子查询也要相当于一个值的集合。即子查询可以有多个元组,但只能有一个属性 结果 some表示: A与一个集合(子查询结果)中的多个值比较,有一个比较成功时结果为真;全部都比较失败时,结果为假 all表示: A与子查询中的值比较,全部比较成功时结果为真;有一个比较失败时,结果为假,在Where子句中 用子查询构造条件,例 R: 修选关系 问: 物理课哪个学生的成绩最高?,R,Select 姓名 From R Where 课程物理 and 成绩= all (Select 成绩 From R Where 课程= 物理),3.11 连接关系,连接关系,连接关系 在Fro
6、m子句中的两个相邻关系之间,可以是一个逗号 表示做笛卡尔积; 也可以是一个连接操作 表示将它们连接成一个新的关系 每个连接操作由一个连接类型和一个连接条件组成 连接条件 natural : 连接两边元组条件为同名属性相等(自然连接) using (属性1, 属性2, ) : 类似自然连接,但是只限于列出的的属性相等 on 条件 : 按照指定的条件连接两边元组,连接关系,连接类型 (inner) join : 内连接。结果不包含失配元组* * 这里失配元组指的是因不满足连接条件,无法和其它元组相连接的元组 left (outer) join : 左外连接。结果包含左边关系的失配元组 right
7、(outer) join : 左外连接。结果包含右边关系的失配元组 full (outer) join : 全外连接。结果包含两边关系的失配元组 cross join : 等价于连接条件为True的内连接,或笛卡儿积 union join : 等价于连接条件为False的全外连接,连接关系,连接关系的例子,Emp,Dept,Select Emp.姓名, Dept.名称 as 部门 From Emp inner join Dept on Emp.部门号Dept.部门号,连接关系,连接关系的例子,Emp,Dept,Select Emp.姓名, Dept.名称 as 部门 From Emp natu
8、ral inner join Dept,连接关系,连接关系的例子,Emp,Dept,Select Emp.姓名, Dept.名称 as 部门 From Emp inner join Dept using (部门号),连接关系,连接关系的例子,Emp,Dept,Select Emp.姓名, Dept.名称 as 部门 From Emp left outer join Dept on Emp.部门号=Dept.部门号,Emp,连接关系,连接关系的例子,Emp,Dept,Select Emp.姓名, Dept.名称 as 部门 From Emp natural right outer join De
9、pt,Emp,连接关系,连接关系的例子,Emp,Dept,Select Emp.姓名, Dept.名称 as 部门 From Emp right outer join Dept using (部门号),Emp,连接关系,连接关系的应用 假定 Stu: 学生关系;Class:班级关系 有些学生还未分配到相应班级,有些班级也未包含任何学生,Stu,Class,连接关系,问,Stu,Class,每个学生的姓名和班级?,列出学生和班主任之间的全部联系?,每个班级的学生人数?,Stu right join Class on ,Stu left join Class on ,Stu full join C
10、lass on ,连接关系,连接关系的应用1 每个学生的姓名和班级,Stu,Class,Select 姓名, 班名 From Stu left outer join Class on Stu.班号=Class.班号,连接关系,连接关系的应用2 每个班级的学生人数?,Stu,Class,Select 班名, count(学号) From Stu right outer join Class on Stu.班号=Class.班号 Group By 班号, 班名,连接关系,连接关系的应用3 列出学生和班主任之间的全部联系?,Stu,Class,Select 姓名, 班主任 From Stu full
11、 outer join Class on Stu.班号=Class.班号,3.6 空值,空值的处理,空值判定 A is not null 判断指定A(属性,表达式等)的值是否为空值 例:找出年龄值未知的员工姓名,Select 姓名 From employee Where 年龄 is null,employee,空值的处理,思考,能用= null来判断空值么?,employee,Select 姓名 From employee Where 年龄= null,空值的处理,空值参与算术运算 结果一律为null 例:每个员工的出生年份?,Select 姓名, 2010 年龄 as 出生年份 From e
12、mployee,employee,空值的处理,空值参与比较运算 结果一律为unknown。最终结果为unknown时,转化为False 例: 年龄35周岁以上的员工姓名,Select 姓名 From employee Where 年龄35,employee,空值的处理,空值参与布尔运算 此时空值被当作unknown来处理 例: 销售部年龄35周岁以上的员工姓名,Select 姓名 From employee Where 年龄35 and (部门=销售),employee,空值的处理,空值参与聚集运算 参与聚集运算时,属性中的空值将被忽略。唯一的例外是count(*) count(*)相当于统计
13、记录的总数,Select avg(年龄) as 平均已知年龄 count (年龄) as 已知年龄人数 count () as 总人数 From employee,employee,3.10 数据库的修改,SQL的功能,SQL的功能和相应的语句,SQL的数据修改功能,SQL提供的数据修改操作包括 插入:Insert语句 删除:Delete语句 更新:Update语句,Insert 语句,格式1: 插入一个新元组 Insert Into 关系 (属性1, 属性2, ) Values (值1, 值2 , ) 注意 所列值的个数必须和属性的个数相等,且一一对应 在插入的新元组中,没有指定的属性填入缺
14、省值(Create Table时定义),没有缺省值则填入空值,为新元组的哪些属性赋值。缺省为全部属性,为前面的属性赋予什么值,Insert 语句,例1: Insert Into R Values (刘朝, 物理, 80),R,R,插入一个元组,Insert 语句,例2: 新建表S: Create Table Stu ( 学号 VARCHAR (4), 姓名 VARCHAR(10) not null, 年龄 SMALLINT default 22, 班级 VARCHAR(20), ) 在S中插入元组 Insert Into Stu (学号, 姓名) Values (1, 小刘),Stu,Stu,
15、Insert 语句,格式2: 插入多个元组(一个SQL查询的结果) Insert Into 关系 (属性1, 属性2, ) Select 属性A, 属性B 注意 同样地,对其它的属性填入缺省值或空值,为新元组的哪些属性赋值。缺省为全部属性,查询结果的各个属性赋值给前面列出的属性,所以要一一对应:类型相容,属性名可不同,Insert 语句,例: 添加所有学生选修化学课程的信息 Insert Into R(姓名, 课程) Select 姓名, 课程 From S, C Where 课程=化学,R,R,C,S,Insert 语句,例: 统计每门课程的平均成绩,然后保存到一个表里 1. Create
16、table C (课程 varchar(20), 平均成绩 float ) 2. Insert Into C Select 课程, Avg(成绩) From R Group By 课程,R,C,Delete 语句,格式 Delete From 关系 Where 条件 说明: 在关系中找到满足条件的元组,并删除之 如果没有Where子句,表示删除关系的全部元组(保留结构) 一次只能删除一个关系中的元组,Delete 语句,例1: 删除物理课的选修信息 Delete From R Where 课程=物理,R,R,Delete 语句,例2: 删除全部选修信息 Delete From R,R,R,De
17、lete 语句,例3: 删除平均分不及格的学生的选修信息 Delete From R Where 姓名 in (select 姓名 from R group by 姓名 having avg(成绩) 60 ),R,R,Update 语句,格式 Update 关系 Set 属性1 = 表达式1 ,属性2 = 表达式2 Where 条件 说明: 在关系中找到满足条件的元组,然后更新:表达式1的值赋予属性1;表达式2的值赋予属性2 没有Where子句时,则对关系的全部元组都要更新,Update 语句,例: 给销售部门的职工增加10%的工资 Update Emp Set 工资=工资*1.1 Where 部门=销售,Emp,Emp,Update 语句,例: 在原有的学生关系S里面增加一个新属性:选修课程数。然后填充正确的数值。 alter table S . Update S set 选修课程数 = ( select count(课程) from R where R.姓名=S.姓名 ),R,S,
链接地址:https://www.31doc.com/p-2141289.html