《chapter03chinese》由会员分享，可在线阅读，更多相关《chapter03chinese（47页珍藏版）》请在金锄头文库上搜索。
1、颜启华颜启华 华南师范大学华南师范大学3.7 嵌套子查询l嵌套子查询嵌套子查询l出现在父查询的出现在父查询的From子句中子句中l出现在父查询的出现在父查询的Where子句中子句中颜启华颜启华 华南师范大学华南师范大学在From子句中用子查询构造派生关系l在在From子句中，允许用子查询构造新的关系，称为派子句中，允许用子查询构造新的关系，称为派生关系生关系l新关系必须命名。其属性也可以重命名新关系必须命名。其属性也可以重命名l格式格式From , (SQL 子查询子查询) (as) 关系名关系名 , From , (SQL子查询子查询) (as) 关系名关系名(属性属性1, 属性属性2) ,
2、 颜启华颜启华 华南师范大学华南师范大学在From子句中用子查询构造派生关系l例例lR: 选修关系选修关系l问问: 每门课程的最高成绩和相应学生姓名每门课程的最高成绩和相应学生姓名?姓名 课程 成绩王红 物理70王红 数学80张军 数学90张军 物理 100刘朝 物理80R课程课程 最高成绩最高成绩物理100数学90Select R.课程课程, 姓名姓名, 最高成绩最高成绩 From R, () as SWhere R.成绩成绩=S.最高成绩最高成绩and R.课程课程=S.课程课程Select 课程课程, Max(成绩成绩)as 最高成绩最高成绩 From RGroup By 课程课程 课程
3、课程 姓名姓名 最高成绩最高成绩物理 张军100数学 张军90颜启华颜启华 华南师范大学华南师范大学在From子句中用子查询构造派生关系l正确理解派生关系的作用正确理解派生关系的作用l把一个复杂的查询分解成很多步，把上一步的查询结果变把一个复杂的查询分解成很多步，把上一步的查询结果变成一个派生关系，然后在下一步查询中使用。从而成一个派生关系，然后在下一步查询中使用。从而一步一步步步地完成复杂的查询。地完成复杂的查询。颜启华颜启华 华南师范大学华南师范大学在Where子句中 用子查询构造条件l条件条件1: 判定一个值判定一个值(例如属性值例如属性值)是否在查询结果中是否在查询结果中lin运算符运
4、算符l格式格式A not in (子查询子查询)l要求要求lA往往是一个值，此时子查询要相当于一个值的集合。往往是一个值，此时子查询要相当于一个值的集合。即子查询可以有多个元组，但只能有一个属性即子查询可以有多个元组，但只能有一个属性l元组只有一个属性时，相当于一个单值，子查询的元组只有一个属性时，相当于一个单值，子查询的结果是一个这样的元组集合，即多个单值的集合结果是一个这样的元组集合，即多个单值的集合l结果结果l当当A是子查询结果中的一个值时，结果为真。否则为假是子查询结果中的一个值时，结果为真。否则为假颜启华颜启华 华南师范大学华南师范大学在Where子句中 用子查询构造条件l例例lR:
5、 修选关系修选关系l问问: 没有选修数学的学生姓名没有选修数学的学生姓名?姓名姓名 课程课程 成绩成绩王红 物理70王红 数学80张军 数学90张军 物理 100刘朝 物理80RSelect 姓名姓名From RWhere 姓名姓名 not in (Select 姓名姓名 From RWhere 课程课程=数学数学 ) 姓名姓名刘朝颜启华颜启华 华南师范大学华南师范大学在Where子句中 用子查询构造条件l条件条件2: 判定查询结果是否为空判定查询结果是否为空lexist运算符运算符l格式格式not exists (子查询子查询)l结果结果lexist的含义为存在（至少一条）记录的判定。所以当
6、的含义为存在（至少一条）记录的判定。所以当子查询结果为空（不包含任何记录）时，子查询结果为空（不包含任何记录）时，exists的结果的结果为假，否则为真。为假，否则为真。l要点要点l子查询可包含一个或多个属性（子查询可包含一个或多个属性（exist是对记录的判断，是对记录的判断，和属性无关）和属性无关）l子查询可引用父查询关系的属性子查询可引用父查询关系的属性(此时最好标明关系前此时最好标明关系前缀缀)，作为子查询的，作为子查询的参数参数。这一点对于其它类型（。这一点对于其它类型（in、some、all）的子查询也是适用的）的子查询也是适用的颜启华颜启华 华南师范大学华南师范大学在Where子
7、句中 用子查询构造条件l例例lR: 选修关系选修关系; S: 学生关系学生关系l问问: 哪个学生没有选修任何课程哪个学生没有选修任何课程? 列出姓名和年龄列出姓名和年龄Select 姓名姓名, 年龄年龄From SWhere not exists (Select * From R Where R.姓名姓名=S.姓名姓名) 姓名姓名 年龄年龄李丽24姓名姓名 性别性别 年龄年龄王红女23张军男22刘朝男22李丽女24S姓名姓名 课程课程 成绩成绩王红 物理70王红 数学80张军 数学90张军 物理 100刘朝 数学80R颜启华颜启华 华南师范大学华南师范大学在Where子句中 用子查询构造条件l
8、条件条件3: 将一个值将一个值(例如属性值例如属性值)与查询结果做比较与查询结果做比较lsome|all运算符运算符l格式格式A (比较运算比较运算, , = all (Select 成绩成绩 From RWhere 课程课程= 物理物理) 姓名姓名张军颜启华颜启华 华南师范大学华南师范大学3.11 连接关系颜启华颜启华 华南师范大学华南师范大学连接关系l连接关系连接关系l在在From子句中的两个相邻关系之间，可以是一个逗号子句中的两个相邻关系之间，可以是一个逗号 表示做笛卡尔积表示做笛卡尔积; 也可以是一个也可以是一个连接操作连接操作 表示将表示将它们连接成一个新的关系它们连接成一个新的关系
9、l每个连接操作由一个每个连接操作由一个连接类型连接类型和一个和一个连接条件连接条件组成组成l连接条件连接条件lnatural : 连接两边元组条件为同名属性相等（自然连接）连接两边元组条件为同名属性相等（自然连接）lusing (属性属性1, 属性属性2, ) : 类似自然连接，但是只限于列类似自然连接，但是只限于列出的的属性相等出的的属性相等lon 条件条件 : 按照指定的条件连接两边元组按照指定的条件连接两边元组颜启华颜启华 华南师范大学华南师范大学连接关系l连接类型连接类型l(inner) join : 内连接。结果不包含失配元组内连接。结果不包含失配元组*l* 这里失配元组指的是因不满
10、足这里失配元组指的是因不满足连接条件连接条件，无法和其，无法和其它元组相连接的元组它元组相连接的元组lleft (outer) join : 左外连接。结果包含左边关系的失配左外连接。结果包含左边关系的失配元组元组lright (outer) join : 左外连接。结果包含右边关系的失左外连接。结果包含右边关系的失配元组配元组lfull (outer) join : 全外连接。结果包含两边关系的失配全外连接。结果包含两边关系的失配元组元组lcross join : 等价于连接条件为等价于连接条件为True的内连接，或笛卡儿的内连接，或笛卡儿积积lunion join : 等价于连接条件为等价
11、于连接条件为False的全外连接的全外连接颜启华颜启华 华南师范大学华南师范大学连接关系l连接关系的例子连接关系的例子员工号 姓名 部门号A01张军D01A02何敏D02A03肖华D02B04吴天D03B05余东nullEmp部门号 名称 经理工号D01 市场部A03D02 生产部A02D03 财务部B04D04 人事部nullDeptSelect Emp.姓名姓名, Dept.名称名称 as 部门部门From Emp inner join Dept on Emp.部门号部门号Dept.部门号部门号姓名部门张军 市场部何敏 生产部肖华 生产部吴天 财务部颜启华颜启华 华南师范大学华南师范大学连
12、接关系l连接关系的例子连接关系的例子员工号 姓名 部门号A01张军D01A02何敏D02A03肖华D02B04吴天D03B05余东nullEmp部门号 名称 经理工号D01 市场部A03D02 生产部A02D03 财务部B04D04 人事部nullDeptSelect Emp.姓名姓名, Dept.名称名称 as 部门部门From Emp natural inner join Dept颜启华颜启华 华南师范大学华南师范大学连接关系l连接关系的例子连接关系的例子员工号员工号 姓名姓名 部门号部门号A01张军D01A02何敏D02A03肖华D02B04吴天D03B05余东nullEmp部门号部门号
13、 名称名称 经理工号经理工号D01 市场部A03D02 生产部A02D03 财务部B04D04 人事部nullDeptSelect Emp.姓名姓名, Dept.名称名称 as 部门部门From Emp inner join Dept using (部门号部门号)颜启华颜启华 华南师范大学华南师范大学连接关系l连接关系的例子连接关系的例子员工号员工号 姓名姓名 部门号部门号A01张军D01A02何敏D02A03肖华D02B04吴天D03B05余东nullEmp部门号部门号 名称名称 经理工号经理工号D01 市场部A03D02 生产部A02D03 财务部B04D04 人事部nullDeptSel
14、ect Emp.姓名姓名, Dept.名称名称 as 部门部门From Emp left outer join Dept on Emp.部门号部门号=Dept.部门号部门号Emp姓名部门张军 市场部何敏 生产部肖华 生产部吴天 财务部余东null颜启华颜启华 华南师范大学华南师范大学连接关系l连接关系的例子连接关系的例子员工号员工号 姓名姓名 部门号部门号A01张军D01A02何敏D02A03肖华D02B04吴天D03B05余东nullEmp部门号部门号 名称名称 经理工号经理工号D01 市场部A03D02 生产部A02D03 财务部B04D04 人事部nullDeptSelect Emp.姓
15、名姓名, Dept.名称名称 as 部门部门From Emp natural right outer join DeptEmp姓名部门张军 市场部何敏 生产部肖华 生产部吴天 财务部null人事部颜启华颜启华 华南师范大学华南师范大学连接关系l连接关系的例子连接关系的例子员工号员工号 姓名姓名 部门号部门号A01张军D01A02何敏D02A03肖华D02B04吴天D03B05余东nullEmp部门号部门号 名称名称 经理工号经理工号D01 市场部A03D02 生产部A02D03 财务部B04D04 人事部nullDeptSelect Emp.姓名姓名, Dept.名称名称 as 部门部门Fro
16、m Emp right outer join Dept using (部门号部门号)Emp姓名部门张军 市场部何敏 生产部肖华 生产部吴天 财务部余东nullnull人事部颜启华颜启华 华南师范大学华南师范大学连接关系l连接关系的应用连接关系的应用l假定假定lStu: 学生关系；学生关系；Class：班级关系：班级关系l有些学生还未分配到相应班级，有些班级也未包含任有些学生还未分配到相应班级，有些班级也未包含任何学生何学生学号学号姓名姓名班号班号01王红王红C0102张军张军C0203刘朝刘朝null04陈平陈平C01Stu班号班号班名班名班主任班主任C01财会财会老王老王C02 计算机计算机
17、老陈老陈C03电子电子老刘老刘Class颜启华颜启华 华南师范大学华南师范大学连接关系l问学号学号姓名姓名班号班号01王红王红C0102张军张军C0203刘朝刘朝null04陈平陈平C01Stu班号班号班名班名班主任班主任C01财会财会老王老王C02 计算机计算机老陈老陈C03电子电子老刘老刘Class每个学生的姓名和班级每个学生的姓名和班级?列出学生和班主任之间的全部联系列出学生和班主任之间的全部联系?每个班级的学生人数每个班级的学生人数?Stu right join Class on Stu left join Class on Stu full join Class on 颜启华颜启华
18、华南师范大学华南师范大学连接关系l连接关系的应用连接关系的应用1l每个学生的姓名和班级每个学生的姓名和班级学号学号姓名姓名班号班号01王红王红C0102张军张军C0203刘朝刘朝null04陈平陈平C01Stu班号班号班名班名班主任班主任C01财会财会老王老王C02 计算机计算机老陈老陈C03电子电子老刘老刘ClassSelect 姓名姓名, 班名班名From Stu left outer join Class on Stu.班号班号=Class.班号班号颜启华颜启华 华南师范大学华南师范大学连接关系l连接关系的应用连接关系的应用2l每个班级的学生人数每个班级的学生人数?学号学号姓名姓名班号班
19、号01王红王红C0102张军张军C0203刘朝刘朝null04陈平陈平C01Stu班号班号班名班名班主任班主任C01财会财会老王老王C02 计算机计算机老陈老陈C03电子电子老刘老刘ClassSelect 班名班名, count(学号学号)From Stu right outer join Class on Stu.班号班号=Class.班号班号Group By 班号班号, 班名班名颜启华颜启华 华南师范大学华南师范大学连接关系l连接关系的应用连接关系的应用3l列出学生和班主任之间的全部联系列出学生和班主任之间的全部联系?学号学号姓名姓名班号班号01王红王红C0102张军张军C0203刘朝刘朝
20、null04陈平陈平C01Stu班号班号班名班名班主任班主任C01财会财会老王老王C02 计算机计算机老陈老陈C03电子电子老刘老刘ClassSelect 姓名姓名, 班主任班主任From Stu full outer join Class on Stu.班号班号=Class.班号班号颜启华颜启华 华南师范大学华南师范大学3.6 空值颜启华颜启华 华南师范大学华南师范大学空值的处理l空值判定空值判定A is not nulll判断指定判断指定A（属性，表达式等）的值是否为空值（属性，表达式等）的值是否为空值l例：找出年龄值未知的员工姓名例：找出年龄值未知的员工姓名Select姓名姓名From
21、employeeWhere年龄年龄 is nullemployee姓名姓名老孙老孙工号工号 姓名姓名 年龄年龄 部门部门1老张老张 35 销售销售2老李老李 40 研发研发3老孙老孙 null 销售销售4老陈老陈 43 生产生产颜启华颜启华 华南师范大学华南师范大学空值的处理l思考，能用思考，能用= null来判断空值么来判断空值么?姓名姓名employee工号工号 姓名姓名 年龄年龄 部门部门1老张老张 35 销售销售2老李老李 40 研发研发3老孙老孙 null 销售销售4老陈老陈 43 生产生产Select姓名姓名From employeeWhere年龄年龄= null颜启华颜启华 华南
22、师范大学华南师范大学空值的处理l空值参与算术运算空值参与算术运算l结果一律为结果一律为nulll例：每个员工的出生年份例：每个员工的出生年份?Select姓名姓名, 2010 年龄年龄 as 出生年份出生年份From employeeemployee工号工号 姓名姓名 年龄年龄 部门部门1老张老张 35 销售销售2老李老李 40 研发研发3老孙老孙 null 销售销售4老陈老陈 43 生产生产姓名姓名 出生年份出生年份老张老张1975老李老李1970老孙老孙null老陈老陈1967颜启华颜启华 华南师范大学华南师范大学空值的处理l空值参与比较运算空值参与比较运算l结果一律为结果一律为unkno
23、wn。最终结果为。最终结果为unknown时，转化为时，转化为Falsel例例: 年龄年龄35周岁以上的员工姓名周岁以上的员工姓名Select姓名姓名From employeeWhere年龄年龄35employee工号工号 姓名姓名 年龄年龄 部门部门1老张老张 35 销售销售2老李老李 40 研发研发3老孙老孙 null 销售销售4老陈老陈 43 生产生产姓名姓名老李老李老陈老陈颜启华颜启华 华南师范大学华南师范大学空值的处理l空值参与布尔运算空值参与布尔运算l此时空值被当作此时空值被当作unknown来处理来处理l例例: 销售部年龄销售部年龄35周岁以上的员工姓名周岁以上的员工姓名Sele
24、ct姓名姓名From employeeWhere年龄年龄35 and (部门部门=销售销售)employee工号工号 姓名姓名 年龄年龄 部门部门1老张老张 35 销售销售2老李老李 40 研发研发3老孙老孙 null 销售销售4老陈老陈 43 生产生产姓名姓名颜启华颜启华 华南师范大学华南师范大学空值的处理l空值参与聚集运算空值参与聚集运算l参与聚集运算时，属性中的空值将被忽略。唯一的例外是参与聚集运算时，属性中的空值将被忽略。唯一的例外是count(*)lcount(*)相当于统计记录的总数相当于统计记录的总数Select avg(年龄年龄) as 平均已知年龄平均已知年龄 count (
25、年龄年龄) as 已知年龄人数已知年龄人数 count () as 总人数总人数From employeeemployee工号工号 姓名姓名 年龄年龄 部门部门1老张老张 35 销售销售2老李老李 40 研发研发3老孙老孙 null 销售销售4老陈老陈 43 生产生产平均平均已知已知年龄年龄 已知年龄人数已知年龄人数总人数总人数39.334颜启华颜启华 华南师范大学华南师范大学3.10 数据库的修改颜启华颜启华 华南师范大学华南师范大学SQL的功能lSQL的功能和相应的语句的功能和相应的语句SQL功能功能语句语句数据定义数据定义CREATE，ALTER，DROP数据查询数据查询SELECT数据
26、修改数据修改INSERT，UPDATE，DELETE数据控制数据控制GRANT，REVOKE，颜启华颜启华 华南师范大学华南师范大学SQL的数据修改功能lSQL提供的数据修改操作包括提供的数据修改操作包括l插入：插入：Insert语句语句l删除：删除：Delete语句语句l更新：更新：Update语句语句颜启华颜启华 华南师范大学华南师范大学Insert 语句l格式格式1: 插入一个新元组插入一个新元组Insert Into 关系关系 (属性属性1, 属性属性2, ) Values (值值1, 值值2 , )l注意注意l所列值的个数必须和属性的个数相等，且一一对应所列值的个数必须和属性的个数相
27、等，且一一对应l在插入的新元组中，没有指定的属性填入缺省值在插入的新元组中，没有指定的属性填入缺省值（Create Table时定义），没有缺省值则填入空值时定义），没有缺省值则填入空值为为新新元元组组的的哪哪些些属属性性赋赋值值。缺缺省省为全部属性为全部属性为为前前面面的的属属性性赋赋予什么值予什么值颜启华颜启华 华南师范大学华南师范大学Insert 语句l例例1：lInsertIntoRValues(刘朝刘朝, 物理物理, 80)姓名姓名 课程课程 成绩成绩王红王红 物理物理70王红王红 数学数学80张军张军 数学数学90姓名姓名 课程课程 成绩成绩王红王红 物理物理70王红王红 数学数学
28、80张军张军 数学数学90刘朝刘朝 物理物理80RR插入一个元组插入一个元组颜启华颜启华 华南师范大学华南师范大学Insert 语句l例例2：l新建表新建表S：Create Table Stu ( 学号学号 VARCHAR (4), 姓名姓名 VARCHAR(10) not null, 年龄年龄 SMALLINT default 22, 班级班级 VARCHAR(20), )l在在S中插入元组中插入元组 Insert Into Stu (学号学号, 姓名姓名) Values (1, 小刘小刘) 学号学号 姓名姓名 年龄年龄 班级班级学号学号 姓名姓名 年龄年龄 班级班级1小刘小刘22NullS
29、tuStu颜启华颜启华 华南师范大学华南师范大学Insert 语句l格式格式2: 插入多个元组（一个插入多个元组（一个SQL查询的结果）查询的结果） Insert Into 关系关系 (属性属性1, 属性属性2, ) Select 属性属性A, 属性属性B l注意注意l同样地，对其它的属性填入缺省值或空值同样地，对其它的属性填入缺省值或空值为为新新元元组组的的哪哪些些属属性性赋赋值值。缺缺省省为全部属性为全部属性查查询询结结果果的的各各个个属属性性赋赋值值给给前前面面列列出出的的属属性性，所所以以要要一一一一对对应应：类类型型相相容容，属属性性名名可可不同不同颜启华颜启华 华南师范大学华南师范
30、大学Insert 语句l例：例：l添加所有学生选修化学课程的信息添加所有学生选修化学课程的信息 Insert Into R(姓名姓名, 课程课程)Select 姓名姓名, 课程课程 From S, C Where 课程课程=化学化学姓名姓名 课程课程 成绩成绩王红王红 物理物理70王红王红 数学数学80张军张军 数学数学90刘朝刘朝 物理物理80姓名姓名 课程课程 成绩成绩王红王红 物理物理70王红王红 数学数学80张军张军 数学数学90刘朝刘朝 物理物理80王红王红 化学化学Null张军张军 化学化学NullRR姓名姓名 年龄年龄王红王红 18张军张军 19C课程课程 学分学分物理物理2数学
31、数学3化学化学1S颜启华颜启华 华南师范大学华南师范大学Insert 语句l例：例：l统计每门课程的平均成绩，然后保存到一个表里统计每门课程的平均成绩，然后保存到一个表里l1. Create table C (课程课程 varchar(20), 平均成绩平均成绩 float ) l2. InsertIntoC Select 课程课程, Avg(成绩成绩) From R Group By 课程课程姓名姓名 课程课程 成绩成绩王红王红 物理物理70王红王红 数学数学80张军张军 数学数学90刘朝刘朝 物理物理80王红王红 化学化学 Null张军张军 化学化学 Null课程课程 平均成绩平均成绩物理
32、物理70数学数学85化学化学NullRC颜启华颜启华 华南师范大学华南师范大学Delete 语句l格式格式Delete From 关系关系 Where 条件条件l说明：说明：l在关系中找到满足条件的元组，并删除之在关系中找到满足条件的元组，并删除之l如果没有如果没有Where子句，表示删除关系的全部元组子句，表示删除关系的全部元组(保留保留结构结构)l一次只能删除一个关系中的元组一次只能删除一个关系中的元组颜启华颜启华 华南师范大学华南师范大学Delete 语句l例例1：l删除物理课的选修信息删除物理课的选修信息Delete From R Where 课程课程=物理物理RR姓名姓名 课程课程
33、成绩成绩王红王红 物理物理50王红王红 数学数学60张军张军 数学数学90张军张军 化学化学 100刘朝刘朝 物理物理80姓名姓名 课程课程 成绩成绩王红王红 数学数学80张军张军 数学数学90张军张军 化学化学100颜启华颜启华 华南师范大学华南师范大学Delete 语句l例例2：l删除全部选修信息删除全部选修信息Delete From RRR姓名姓名 课程课程 成绩成绩王红王红 物理物理50王红王红 数学数学60张军张军 数学数学90张军张军 化学化学100刘朝刘朝 物理物理80姓名姓名 课程课程 成绩成绩颜启华颜启华 华南师范大学华南师范大学Delete 语句l例例3：l删除平均分不及格
34、的学生的选修信息删除平均分不及格的学生的选修信息 Delete From R Where 姓名姓名 in (select 姓名姓名 from R group by 姓名姓名 having avg(成绩成绩) 60 )R姓名姓名 课程课程 成绩成绩王红王红 物理物理50王红王红 数学数学60张军张军 数学数学90张军张军 化学化学100刘朝刘朝 物理物理80R姓名姓名 课程课程 成绩成绩张军张军 数学数学90张军张军 化学化学100刘朝刘朝 物理物理80颜启华颜启华 华南师范大学华南师范大学Update 语句l格式格式Update 关系关系 Set 属性属性1 = 表达式表达式1 ,属性属性2
35、= 表达式表达式2 Where 条件条件l说明：说明：l在关系中找到满足条件的元组，然后更新：表达式在关系中找到满足条件的元组，然后更新：表达式1的的值赋予属性值赋予属性1；表达式；表达式2的值赋予属性的值赋予属性2l没有没有Where子句时，则对关系的全部元组都要更新子句时，则对关系的全部元组都要更新颜启华颜启华 华南师范大学华南师范大学Update 语句l例：例：l给销售部门的职工增加给销售部门的职工增加10%的工资的工资 Update Emp Set 工资工资=工资工资*1.1 Where 部门部门=销售销售Emp工号工号 姓名姓名 部门部门 工资工资1老张老张 销售销售 32002老李
36、老李 研发研发 28003老孙老孙 销售销售 40004老陈老陈 生产生产 2000工号工号 姓名姓名 部门部门 工资工资1老张老张 销售销售 35202老李老李 研发研发 30803老孙老孙 销售销售 44004老陈老陈 生产生产 2200Emp颜启华颜启华 华南师范大学华南师范大学Update 语句l例：例：l在原有的学生关系在原有的学生关系S里面增加一个新属性：选修课程数。里面增加一个新属性：选修课程数。然后填充正确的数值。然后填充正确的数值。lalter table S .lUpdate S set 选修课程数选修课程数 = ( select count(课程课程) from R where R.姓名姓名=S.姓名姓名 )姓名姓名 课程课程 成绩成绩王红王红 物理物理70王红王红 数学数学80张军张军 数学数学90姓名姓名年龄年龄选修课程数选修课程数王红王红18?张军张军19?RS
