关系数据库标准语言SQL(续).ppt

本章内容,SQL概述 数据定义 数据查询 数据更新 视图 数据控制,3.3 数据查询,3.3.1 单表查询 3.3.2 连接查询 3.3.3 嵌套查询 3.3.4 集合查询,3.3.2 连接查询,等值与非等值连接查询 自身连接 外连接 复合条件连接,查询涉及两个或以上的表，必须将多个表进行连接。,等值与非等值连接查询,按一定条件把多个表连接起来，条件的形式为： . 比较运算符有：=、=、=、!= 如果属性名在参见连接的各表中是唯一的，则可以省略表名前缀。 自然连接：最常用的等值连接。,多表查询等值连接,例：列出每个学生及其选修课程的情况 学生情况student 选修课程情况SC Select student.*,SC.* From student,SC Where student.sno = SC.sno,多表查询自然连接,例：自然连接Student和SC表。 Select Student.sno,sname,ssex,sage,sdept,cno,grade From Student,SC Where Student.sno=SC.sno; 例：列出选修C01课程的学生的名册 select sno,sname from student, SC where student.sno = SC.sno and cno = C01;,多表查询自身连接,例：查询每门课的间接先修课（先修课的先修课）。 把Course表想象成完全相同的两张表，取别名为：FIRST、SECOND。,连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为自身连接。,FIRST表(Course表) SEOND表(Course表),连接条件为：FIRST.cpno=SECOND.cno,Select o,second.cpno From course first,course second Where first.cpno = o,连接条件为：FIRST.cpno=SECOND.cno,为course 表命别名，first、second,多表查询自身连接,例：列出年龄比95001大的学生的学号、姓名,Select s1.sno, s1.sname From student s1, student s2 Where s2.sno=95001 and s1.sages2.sage; （自身连接查询）,select sno, sname from student where sage (select sage from student where sno=95001) （嵌套查询）,多表查询外连接,不匹配连接，含有NULL信息的处理 例：列出学生及其选课情况,主表，完全,从表，不完全,所有的学生情况及他们的选课情况（含未选课）,主要用于主表-从表之间信息短缺的处理 不同的数据库产品有不同的方言,甚至不支持,多表查询外连接,例：列出学生及其选课情况 所有的学生情况及他们的选课情况（含未选课）,Select student.*, cno, grade From student left outer join SC On student.sno=sc.sno,Select student.*,cno,grade From student,sc Where student.sno=sc.sno(*),有些数据库用代替*。 Oracle等数据库支持这种形式外连接。,outer可以省略。 SQL Sever、access等数据库支持这种形式外连接。,多表查询复合条件查询,查询选修了数据库课程且成绩在80分以上的学生学号和姓名。 Select student.sno,sname From student,sc,course Where student.sno=sc.sno and o=o and ame like%数据库% and sc.grade=80;,嵌套查询,查询块 一个Select-From-Where语句。 嵌套查询(SubQuery) 将一个查询块嵌套在另一个查询块的Where子句或Having子句的条件中。,嵌套查询一个简单例子,找出与95001同岁的学生学号及姓名 95001的年龄？ Select sage From student Where sno = 95001 与上述年龄相等的学生？ Select sno,sname From student Where sage = (Select sage From student where sno = 95001),嵌套查询,带有IN谓词的子查询 带有比较运算符的子查询 带有ANY或ALL谓词的子查询 带有EXISTS谓词的子查询,嵌套查询带IN的子查询,父查询与子查询之间由IN连接，判断某个属性列值是否在子查询的结果中。 适用于子查询的结果是一个集合。,嵌套查询带IN的子查询,select sno from SC Where cno IN (Select cno From course Where cname Like %数据库%),Select sno from sc,course Where o=o And cname like %数据库%；,例：找出选修数据库课程的学生的学号,嵌套查询带IN的子查询,Select sno,sname From student Where sno IN ( Select sno From SC Where cno IN ( Select cno From course Where cname Like %数据库%),例：找出选修数据库课程的学生的学号、姓名,嵌套查询带IN的子查询,表达查询最自然的方式 可以用多个简单查询来构成复杂查询，以增强SQL的查询能力 将复杂的查询切分成若干块，逐个解决 子查询中不能使用 Order By 子句，Order By子句只能对最终查询结果进行排序,嵌套查询带比较的子查询,子查询与主查询之间由比较运算符相连接 子查询返回单值 直接使用比较运算符 、=、=、!=等 子查询返回多值 比较运算符与ANY/ALL连用,嵌套查询带比较的子查询,找出年龄比学生95001大的所有学生的学号、姓名 95001的年龄 年龄比他大的学生 Select sno,sname From student Where sage (Select sage From student Where sno = 95001),嵌套查询带ANY/ALL的子查询,当子查询返回多值结果集（多行单列）时，比较操作须由比较运算符 + ANY/ ALL ANY子查询结果集中的某一个 ALL子查询结果集中的所有 all、=all、 all(!=all) = any 等价于 in、 any 不等价于 not in all 等价于 not in,嵌套查询带ANY/ALL的子查询,例：找出不是最小年龄的学生 学生年龄Select sage From student 不是最小年龄any 学生年龄 Select * From student Where sage any (Select sage From student),嵌套查询带ANY/ALL的子查询,例：找出年龄最小的学生 学生年龄Select sage From student 最小年龄=All 学生年龄 Select * From student Where sage =All (Select sage From student),Select * From Student where sage = (Select min(sage) From Student ),嵌套查询带ANY/ALL的子查询,找出具有最高平均成绩的学号及平均成绩 Select sno ,avg(grade) From SC Group By sno Having avg(grade) = all (Select avg(grade) From SC Group By sno),嵌套查询带Exists的子查询,Exists表示存在， Exists + 子查询可用以测试该子查询的结果是否有元组,带有Exists的子查询不返回任何数据,只产生True/False 当子查询的结果集含有元组时Exists为True 当子查询的结果集不含有任何元组时Exists为False 由于不关心子查询的具体内容，因此用 Select *,嵌套查询带Exists的子查询,例：列出至少选修一门课程的学生的学号、姓名 Select sno,sname From student Where Exists ( Select * From SC Where student.sno = SC.sno ),嵌套查询带Exists的子查询,例：列出选修了1号课程的学生的学号、姓名 Select sno,sname From Student Where Exists ( Select * From SC Where Student.sno = SC.sno And cno=1),相关子查询,内层子查询的条件引用外层主查询的某些属性 在Exists运算中，很多涉及到相关子查询的使用,嵌套查询相关子查询,查询没有选修1号课程的学生姓名 Select sname From student Where Not Exists ( Select * From SC Where student.sno = SC.sno and SC.cno = 1),嵌套查询相关子查询,找出与95001在同一个系的学生学号,Select sno From student Where sdept= (select sdept From student Where sno=95001),Select st1.sno From student st1 Where Exists (Select * From student st2 Where st2.sdept = st1.sdept and st2.sno = 95001),嵌套查询相关子查询,查询选修了所有课程的学生姓名 不存在这样的一门课程，这个学生没有选修。 Select sname From student Where Not Exists ( Select * From Course Where Not Exists ( Select * From SC Where SC.sno = student.sno and SC.cno = o),这样的课是不存在的,查询至少选修了学生95002选修的全部课程的学生学号,Select distinct sno From sc scx Where (select cno from sc scy where sc.sno=95002 and (select * from sc scz where scz.sno=scx.sno and o=o),not exists,not exists,查询至少选修了学生95002选修的全部课程的学生学号,Select distinct sno From sc scx Where not exists (select cno from sc scy where sc.sno=95002 and not exists (select * from sc scz where scz.sno=scx.sno and o=o),集合查询,并、交、差集合操作。 标准SQL只提供并操作，其他操作根据具体情况由基本操作组合完成。 对多个查询的结果集实施集合操作 属性必须相容 ORDER BY只能施加在整个结果集中 并不是所有的DBMS均支持三个操作,集合查询,查询计算机系以及数学系的学生 Select * From student Where sdept = CS Union Select * From student Where sdept = IS;,集合查询,查询计算机系的学生及年龄不大于19岁的学生 Select sno,sname as Name ,sage From Student Where sdept = CS Union Select sno,sname,sage From Student Where sage =19 Order By sage,交：查询即选修了1号课程又选修了2号课程的学生学号,Select sno From sc Where cno=1 and Sno in (select sno from sc where cno=2),标准SQL中不直接提供集合交、差的操作，可用其他方法实现。,差：查询选修了1号课程但没选修2号课程的学生学号,Select sno From sc Where cno=1 and Sno not in (select sno from sc where cno=2),3.4 数据更新,SQL中数据更新包括插入数据、修改数据和删除数据。 3.4.1 插入数据（Insert） 3.4.2 删除数据（Delete） 3.4.3 修改数据（Update）,数据更新增Insert,插入单个元组 Insert Into 属性列表 Values(,); 按关系模式的属性顺序 Insert Into Student Values ( 95001,张三,M,27,CS ) 按指定的属性顺序，也可以只添加部分属性（非 Null属性为必需）,其余属性值为NULL Insert Into Student ( sno, sname, sage) Values ( 95002,李四,26 ),数据更新增Insert,插入多行一个集合 将子查询的结果插入数据库表中 Insert Into 属性列表 子查询; Insert into stu_cs Select * From student Where sdept = CS,数据更新删除Delete,Delete From Where; 删除学生表中年龄小于15岁的学生记录。 Delete From Student Where sage 15； 删除学生表中与学生95001同岁的学生记录。 Delete From Student Where sage = (Select sage From student Where sno = 95001) 删除学生表中所有学生的记录。 Delete From Student;,数据更新修改Update,针对符合条件的元组,修改其属性值 格式 Update Set =, Where ,数据更新修改Update,修改符合条件的元组 例：将所有学号前缀为95的学生转至IS系 Update student set sdept = IS Where sno Like 95%,3.5 视图（View）,用户模式、外模式 是从基本表(或其他视图)中导出的虚表(结果集) 数据库中存放它的定义(在数据字典中) 是RDBMS提供给用户以多种角度观察数据库中数据的重要机制,视图（View）,视图层,创建视图、删除视图 Create View v(列名1，列名2) as with check option; Drop View v 例：生成计算机系的花名册视图。 Create View CS_Stu as Select sno,sname,ssex From Student Where sdept = CS,视图(View),在视图上增、删、改数据时，DBMS会进行检查,视图可以象基本表那样使用。可以出现在关系名可以出现的地方。 DBMS将视图操作转换成对基本表的操作。,视图(View)视图查询,例：列出计算机系的男生 Select sno,sname From CS_Stu Where ssex = M,实现方法： Select sno,sname From student Where ssex=M and sdept=CS;,视图更新,包括插入(insert)、删除(delete)、修改(update) 。 将视图CS_Stu中学号为95002的学生姓名改为“刘晨” update CS_Stu set sname=刘晨 where sno=95002 向视图cs_stu中，增加一新学生的记录(95009,王刚,男)。 insert into cs_stu values(95009,王刚,男) 删除视图CS_Stu中，学号为95002的学生记录。 delete from cs_stu where sno=95002,视图作用,安全 简便 数据独立性,用户授权,数据库用户权限的管理 实施用户对特定数据的访问权限 用户的授权信息被存入数据字典之中 用户权限的校验 DCL(Data Co