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

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,本章内容,SQL,概述,数据定义,数据查询,数据更新,视图,数据控制,3.3 数据查询,3.3.1 单表查询,3.3.2,连接查询,3.3.3,嵌套查询,3.3.4,集合查询,3.3.2 连接查询,等值与非等值连接查询,自身连接,外连接,复合条件连接,查询涉及两个或以上的表，必须将多个表进行,连接。,等值与非等值连接查询,按一定条件把多个表连接起来，条件的形式为：,.,.,比较运算符有：,=、=、s2.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子句只能对最终查询结果进行排序,嵌套查询,带比较的子查询,子查询与主查询之间由比较运算符相连接,子查询返回单值,直接使用比较运算符,、=、,(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 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 Control Language),Grant,Revoke,用户授权,给特定的用户授予相应的权限,例:向用户jiang授予表student的,查询,权限,Grant,Select,On Table student,To jiang;,用户授权,格式,Grant ,On ,To ,With Grant Option;,具有传播,权限的权利,Table,|,Database,用户授权,权限,用户授权,例：向用户jiang授予student表的查询以及对sdept属性修改的权限，并允许其传播该权限,Grant,select,update(sdept),on Table,student,to,jiang,with grant option,用户授权,例：向用户jiang以及用户zhang授予student表上的所有权限,Grant,All privileges,on Table,student,to,jiang，zhang,权限回收,Revoke,回收由Grant授予的权限,Revoke ,On ,From,;,权限回收,回收用户jiang在student,表的sdept属性上的修改权限,Revoke Update(sdept),On Table student,From jiang;,级联回收,某用户（具有权限传播能力）的权限被回收后，由其授权的用户权限也被自动回收。,练习,查找只选修,2,号课程的学生学号。,显示所有学生的基本信息及选课信息。,查找大于,20,岁的女生的学号及其各课程总成绩。,检索选修课程名为“数学”的学生学号和姓名。,检索选修课程号为,2,或,4,的学生学号。,检索至少选修了,2,号和,4,号课程的学生学号。,在基本表,SC,中，检索至少不学,2,号和,4,号课程的学生学号。,检索不学,2,号课程的学生姓名和年龄。,检索所学课程包含学生,95003,所学全部课程的学生学号。,