数据库概论第三章SQL.ppt

1、1,结构化查询语言SQL(Structured Query Language),第三章 关系数据库标准语言,功能包括： 查询、操纵、定义和控制 通用的、功能极强的关系数据库语言 关系数据库的标准语言。,2,SQL的历史 SQL语言最初是由IBM公司在七十年代中期在原型的关系数据库管理系统SYSTEM R上开发的语言。 1979年，ORACLE公司首家推出商业上可执行的SQL。 如今，SQL成为工业上标准的数据库存取语言。,3,SQL的标准 美国国家标准学会（ANSI )将SQL作为关系数据库管理系统的标准语言，并且定义在ANSI x3.125-1989”具有完整性增强特征的数据库语言SQL”文

2、档中，即ANSI SQL89 1987.6-1989.4 ISO SQL89 1992年 ISO SQL92 SQL2 1999年 ISO SQL99 SQL3 2003年 ISO SQL2003,4,3.1 SQL概述及其特点 1. SQL概述 按其功能分为四大类,其作用是建立和使用数据库。,5,SQL 的特点, 综合统一的一体化的特点 高度非过程化 面向集合的操作方式，语言功能强 统一的语法结构，两种使用方式 语言简洁，易学易用,6,2. SQL数据库的体系结构 按支持SQL的数据库管理系统可称其SQL数据库，其结构基本上采用三级结构，但所用术语与传统关系模型的术语有些不同。,7,用户1,

3、用户2,用户3,用户4,视图3,视图1,视图2,基本表1,基本表2,基本表3,基本表4,存储文件1,存储文件1,SQL用户,视图,基本表,存储文件,外模式,模式,内模式,8,学生-课程关系数据库,S,C,SC,9,10,SQL的数据定义基本对象为: 定义基本表 定义视图 定义索引表 TABLE TABLE CREATE VIEW DROP VIEW ALTER TABLE INDEX INDEX,3.3 SQL数据定义 - DDL,11,1. 基本表的定义和修改 定义基本表 定义表名 定义表中各列的特征： 列名、数据类型、 长度等,12,一般格式： CREATE TABLE ( 数据类型 列级

4、完整性约束条件 , 数据类型 列级完整性约束条件 ) ；,SQLcreat table S(Sno char(9) primary key, Sname char(20) unique, Ssex char(20), Sage smallint, Sdept char(20), );,13,14,例6 建立课程表 C,SQLcreate table C(Cno char(4) primary key, Cname char(40), Cpno char(4), Ccredit smallint, foreign key Cpno references C(Cno) );,15,例7 建立学生选

5、课表 SC,SQLcreate table SC(Sno char(7), Cno char(4), Grade smallint, primary key(Sno,Cno), foreign key Sno references S(Sno), foreign key Cno references C(Cno), );,16, 修改基本表结构 修改已经定义的表的结构 修改已经定义的表的完整性约束： 一般格式： ALTER TABLE ADD 完整性约束 DROP ALTER COLUMN ;,17,例9：为表S添加一个新的列“入学时间”。 alter table S add S_entran

6、ce DATE； 例10：将年龄的数据类型由字符型改为整型。 alter table S alter column Sage int; 例11：增加课程名取唯一值的约束条件。 alter table C add UNIQUE(Cname); alter table C add constraint U_C UNIQUE(Cname),例12：删除表R中的列A 。 alter table R drop column A; 例13：删除表R中的两列A和B 。 alter table R drop column A,B;,18,* 建表后声明主关键字： alter table table_name

7、add primary key ( column_name); 注意： table_name 是表的名字 column_name 是要被定义为主键的字段名。,alter table S add primary key ( SNO);,19,* 建表后声明外关键字： alter table table_name1 add foreign key ( column_name) references table_name2(column_name) ; 注意： table_name1 是要增加外部键的表的名字， table_name2 是相对table_name1的主表。 column_name 是

8、要被定义为外键的字段名。,alter table SC add foreign key ( SNO) references S (SNO) ;,20, 删除基本表定义 一般格式： DROP TABLE restrict | cascade;,例13：删除学生表S。,Drop table S;,21,3. 索引的建立和删除 索引的概念,索引表,学生表,22,（2） 建立索引 一般格式： CREATE UNIQUE CLUSTER INDEX ON ( ASC|DESC , ASC|DESC );,为学生表S建立按学号升序的唯一索引,CREATE UNIQUE INDEX Stusno ON S(

9、Sno);,ORA-01452: 无法 CREATE UNIQUE INDEX；找到重复的关键字,23,聚簇索引,CREATE CLUSTER INDEX Stusnme ON S(Sname);,例：对选课表建立Sno ，Cno索引,CREATE UNIQUE INDEX Scno ON SC(Sno ASC，Cno DESC);,24, 删除索引 一般格式： DROP INDEX ;,DROP INDEX Stusname；,例：删除索引Stusname,25,练习,3 为关系R建立按照属性A升序的唯一值索引,4 为关系R建立按照属性B降序的聚簇索引,5 为关系R建立按照属性A升序，按照属

10、性B降序的唯一值索引,1 创建一个关系表R,包括两个属性 A和B，类型分别为INT 和CHAR(6) 。,2 为关系R添加一个属性C,类型为CHAR长度为8,26,练习,8 为关系T建立按照属性TNAME升序的聚簇索引,9 为关系T建立按照TNAME升序，TAGE降序的唯一值索引,6 创建一个教师表T,包括教师号（TNO），教师名（TNAME），年龄（TAGE），数据类型和长度自定 ，教师号为主键。,7 为关系T添加一个属性职称（TTITLE）,类型为CHAR长度为8,27,作业,图书馆有很多书，为了提高查询效率需要建立多种索引，要求： 1. 创建一个关系BOOK，属性包括书号、书名、作者、出

11、版社、出版日期、定价。属性名要求用字母表示，数据类型和长度自定，书号为主键。 2. 创建3个你认为最需要的索引以提高查询效率。,28,格式： SELECT ALL|DISTINCT， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC； 描述： 根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP BY子句则将结果按的值进行分组，该属性列相等的元组为一个组。通常会在每组中作用集函数。如果GROUP子句带有HAVING短语，则只有满足指定

12、条件的组才予输出。如果有ORDER BY子句，则结果表还要按的值升序或降序排列。,3.2 SQL查询,29,Select * from R; Select A from R; Select A,B from R; Select * from R where A10; Select B,A from R order by A desc;,30,1. 简单查询 仅涉及一个表的查询 选择表中的若干列 选择表中的若干列； 选择表中的所有列； 结果列的显示顺序； 使用列表达式； SELECT中的可以是表中的列，也可以是表达式，包括算术表达式、字符串常数、函数等。（字符串用单引号定界）。,31,例1：求全

13、体学生的学号、姓名。 Select SNO,SNAME from S; 例2：求全体学生的详细信息。 Select * from S; 例3：求学生学号和学生出生的年份 （经过计算的值）。 Select Sno,2007-Sage from S; 例4：求学生的学号和出生年份，显示时 使用别名 Student_No 和 Birth_Day。,Select Sno Student_No,2007-Sage Birth_Day from S;,32, 选择表中的若干元组 消除重复行； 查找相异的行：在SELECT语句中使用关键字 DISTINCT 原本不完全相同的元组，经过向某些列投影操作后，可能

14、变成相同的行了。如果想去掉结果表中的重复行，必须指定 DISTINCT 短语，没有指定，则使用用缺省值 ALL，意为保留结果表中所有的行。 例5：求选修了课程的学生学号。,Select distinct Sno from SC;,33,Select * from R; Select * from R where A 0; Select A,5 from R where Ba;,34,查询指定条件的元组可以通过WHERE子句来实现。 WHERE常用的查询条件如下表所示。,35,大小比较,Select Sname,Sage from S where Sage=20;,例：求年龄大于等于20岁的学生

15、姓名和年龄。,条件表达式的形式如下： 列名 比较运算符 列名|常量|表达式 其中：字符串常量和日期常量要用一对单引号括起来。,Select * from S where Sname = 张三;,36,例9 查询考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade 60,37,BETWEEN 确定范围 NOT BETWEEN AND 其中： 为范围的下限（低值）， 为范围的上限（高值）。,例：求年龄在20岁与23岁之间（包括20岁和 23岁）的学生学号和年龄。 Select sno,sage from S where sage betwee

16、n 20 and 23;,例：求年龄不在20岁与23岁之间的学生学号和年龄。,Select sno,sage from S where sage not between 20 and 23;,38,IN 确定集合 谓词IN可以用来查找属性值属于指定集合的元组。 NOT IN 表示与IN完全相反的含义。,例： 求在下列各系的学生：数学系（MA）、 计算机科学系（CS）。 Select * from S where sdept in (MA, CS); 例：求不是数学系、计算机科学系的学生的 信息。,39,LIKE 匹配查询或模糊查询 谓词LIKE的一般格式为： NOT LIKE 查询指定的属性列

17、与相匹配的元组。 注释：可以是一个完整的字符串，也可以含有通配符的字符串。通配符包括： %（百分号）： 代表任意长（长度为0）字符串。 _（下划线）： 代表任意单个字符 所有其它字符：只代表自己,40,例：求姓名是以字母D打头的学生信息。 Select * from S where,例：求姓名中含有字母AB的学生信息。 Select * from S where Sname like %AB%; 例：求姓名长度至少是三个字符且倒数第 三个字符必须是字母P的学生信息。,Sname like D%;,Select * from S where Sname like, P ;,_ _,%,41,例1

18、8 查询所有不姓刘的学生姓名 SELECT SNAME FROM S WHERE SNAME NOT LIKE 刘%,42,例20 查询课程名以“DB_”开头，且倒数第三个字符为i的课程的详细情况 SELECT * FROM C WHERE CNAME LIKE DB_%i_ _ ESCAPE ,43,注意：如果LIKE语句中没有通配符“”或者“%”，则LIKE相当于“=”。 例如：查询学号为200215121的学生的详细情况。 SELECT * FROM S WHERE SNO LIKE 200215121； 等价于： SELECT * FROM S WHERE SNO = 20021512

19、1；,44, NULL 涉及空值查询 NULL表示空值。空值是一种不存在的或者不知道、不可用的数据。 列名 IS NOT NULL 这里的IS不能用=替代。 数据库表的行中，未被赋值的字段自动被认为是空值。0长度的字符串自动解释为空值。 空值的赋值： 把连续两个单引号赋值给它； 把空值常量NULL赋值给它。,45,例21：求缺少学习成绩的学生的学号和课程号。 （查询含有空值的行）。 SELECT SNO, CNO FROM SC WHERE GRADE IS NULL; GRADE = NULL;,46,例22：求所有有成绩的学生的学号和课程号。 （查询含有空值的行）。 SELECT SNO,

20、 CNO FROM SC WHERE GRADE IS NOT NULL;,47, 多重条件 当查询条件涉及到多个时，可将若干条件通过逻辑运算符构成一个更复杂的条件进行查询。 可以使用三种逻辑运算符： NOT 逻辑非； AND 逻辑与； OR 逻辑或,48,运算符的优先顺序如下： = != = = NOT BETWEENAND NOT IN NOT LIKE IS NOT NULL AND OR 用户可以用括号( )改变优先级。,49,例：求计算机系(CS)或数学系(MA)，年龄 大于20岁的学生姓名、系和年龄。 Select Sname,Sdept,Sage from S where Sde

21、pt=CS or Sdept=MA and Sage20； 例：求选修课程(001)或课程(002)，成绩在85和95之间，学号为96xxx的学生的学号、课程号与成绩。,Select * from SC where CNO=001 or CNO=002 and GRADE=85 and GRAED=95；,(,),(,),CNO in (001,002),and GRAED between 85 and 95,50, 控制行的显示顺序 ORDER BY 子句中，列名表2指出在显示查询结果时，数据按指定的列排序。 ASC 升序排序（默认值）；DESC 降序排序,Select * from S o

22、rder by Sdept,Sage DESC;,Select * from S order by 2006-Sage;,51,1 从R中选出A不等于3的纪录。,2 从R中选出B小于D的纪录。,练习,6 求选修课程(001)或课程(002)学生的 学号、课程号与成绩，结果按课程号升序、 成绩降序排序。,3 查询年龄大于20的学生信息。,4 查询数学系和计算机系的学生信息。,5 求姓名第三个字符是字母A的学生信息,52,2. 组函数 SQL提供的五种组函数 COUNT( ) 计算所选数据（记录）的个数 SUM( ) 计算某一数值列的和 AVG( ) 计算某一数值列的平均值 MAX( ) 求(字符

23、、日期、数值列)的最大值 MIN( ) 求(字符、日期、数值列)的最小值,53,R,1：求总纪录数。 2：对A求和。 3：对A求平均。 4：求A的最大值。,Select count(*) from R Select sum(A) from R Select avg(A) from R Select max(A) from R,54,3. 分组查询 实现行的分组和分组统计。 在对表格进行操作时，要求将记录按某个或某几个字段上相同的值分成组，然后再对组进行相应的操作，称作分组查询。,55,GROUP BY 子句,R,1：select count(A),A from R group by A; CO

24、UNT(A) A - - 3 3 1 4 1 2,2：select sum(A),A from R group by A; SUM(A) A - - 2 2 4 4 9 3,56,求每个班的平均年龄 Select class,avg(Sage) from S group by class,在包含GROUP BY子句的查询语句中，SELECT子句后面的所有字段列表（除组函数外），均应该包含在GROUP BY 子句中，即所选项与分组的一致性。,57,HAVING 子句 如果分组后还要按一定的条件对这些分组进行筛选，只输出满足条件的组，则应该使用HAVING短语指定筛选条件。 HAVING 子句用来

25、选择满足条件的分组。,例32：查询平均年龄在20岁以上的班级。,Select class,avg(Sage) from S group by class Having avg(Sage)20;,58,WHERE与HAVING的区别： 作用对象不同 WHERE 作用于基本表或视图，从中选择满足条件的元组； HAVING短语作用于组，从中选择满足条件的组。,59,1 求选修“001”课程的学生人数。,练习,2 求“001”课程的总成绩。,3 求“001”课程的平均成绩。,4 求“001”课程的最高成绩。,5 求每门课程的平均成绩。,6 求每个学生的平均成绩。,7 求平均分大于90分的学生学号和平均

26、成绩,60,4. 连接查询 涉及两个以上的表。 连接查询是通过各表中相应列的公共数据把一个表中的某些行与另一个表中的某些行连接起来。,61,回顾,R,S,等值连接,自然连接,62,一. 等值连接与非等值连接 连接条件的一般格式是： . . 其中：比较运算符主要有：=、=、.：指明是哪个表的哪个列,限定符,63,例33：查询每个学生以及其选修课程的情况。 SELECT S.*, SC.* FROM S, SC WHERE S.SNO=SC.SNO;,连接条件,SNO：连接字段,自然连接： SELECT S.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM S, S

27、C WHERE S.SNO=SC.SNO;,64,S,C,SC,65,连接结果,66,例：查询李勇选修课程的情况。 SELECT Sname, SC.* FROM S, SC WHERE S.SNO=SC.SNO AND S.Sname=李勇;,例：查询李勇选修数据库课程的成绩。 SELECT Sname, Cname,Grade FROM S, C,SC WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND S.Sname=李勇 AND C.Cname=数据库;,67,三、外连接,例36：查询所有学生的选课情况 select s.*,sc.* from s left

28、 out join sc on(s.sno=sc.sno),68,二 自身连接 连接查询不仅可以作用在两个不同的表上，而且同一个表可进行自身连接。 执行过程： 可以把一个看成两个副本，即两个相同的表，然后再对这两个表在连接字段上进行连接。表与自身的连接必须使用表的别名，若在FROM子句中用两个不同的别名对应于同一个表是时，就可以象连接两个分开的表一样来把表自身连接起来。,69,例：查询年龄小于李勇的学生学号,姓名和年龄 Select S1.SNO, S1.SNAME, S1.SAGE From S S1, S S2 Where S1.SAGE S2.SAGE AND S2.NAME=李勇,S1

29、,S2,70,例35：查询每一门课的间接先修课 (即先修课的先修课) Select FIRST.CNO, SECOND.CPNO From C FIRST, C SECOND Where FIRST.CPNO=SECOND.CNO,FIRST,SECOND,AND CNAME=数据库,71,四、复合条件连接,例37：求选修2号课程且成绩90分以上的 学生学号和姓名 select S.sno,sname from S,SC Where S.sno=SC.sno And o=2 and sc.grade90;,72,例：求选修课程001且成绩在70分以下或 成绩在90分以上的学生的姓名，课程 名称

30、和成绩。 select sname,cname,grade from S,C,SC Where s.sno=sc.sno and o=o And o=001 and sc.grade90;,(,),73,5. 嵌套查询 概念 查询块：一个select-from-where语句称为一个查询块。 嵌套查询：将一个查询块嵌套在另一个查询块的where子句或having子句中的查询。子查询的结果用于建立主查询的查找条件。 处理步骤 一般由里向外进行处理。,74,嵌套查询实例 Select sname from S Where sno in (select sno from SC where Cno=2

31、) 查询选修了2号课程的学生姓名,Select sname from S,SC Where S.sno=SC.sno And o=2;,75,注意 子查询必须用括号括住。 子查询可以有多层，所存取的表可以是父查询没有存取的表。 子查询中不能有ORDER BY子句。 子查询选出的记录不显示。,76,嵌套查询分类 带有IN谓词的子查询 带有比较运算符的子查询 带有ANY（SOME）或ALL的子查询 带有EXIST谓词的子查询,77,一、带有IN谓词的子查询 例39，查询与刘晨在同一个系的学习的学生 Select sno,sname,sdept from S Where sdept in (sele

32、ct sdept from S where sname=刘晨),78,例40，查询选修了课程名为“信息系统”的学生学号和姓名 Select sno,sname from S Where sno in (select sno from SC where cno in (select cno from C where cname=信息系统)；,select sno，sname from S,C,SC Where s.sno=sc.sno and o=o And C.cname=信息系统；,还可以用连接查询来实现,79,二、带有比较运算符的子查询 例39，查询与刘晨在同一个系的学习的学生 Selec

33、t sno,sname,sdept from S Where sdept in (select sdept from S where sname=刘晨),Select sno,sname,sdept from S Where sdept = (select sdept from S where sname=刘晨),80,例 找出年龄大于平均年龄的的学生姓名和年龄 Select Sname,Sage from S Where Sage (select AVG(Sage) from S);,81,练习：求比王平年龄大的学生姓名与年龄。 Selcet Sname,Sage from S Where

34、Sage (select Sage from S where sname=王平);,82,三、带有ANY（SOME）或ALL的子查询,例42：求其他系比计算机系中某一学生年龄小的学生姓名和年龄。 Select sname,sage from S Where sage CS; 可以用聚集函数实现。 Select sname,sage from S Where sage CS;,83,例43：求其他系比计算机系中所有学生年龄小的学生姓名和年龄。 Select sname,sage from S Where sage CS; 可以用聚集函数实现。 Select sname,sage from S W

35、here sage CS;,84,四、带有EXIST谓词的子查询 EXISTS 为存在量词，用来测试子查询是否返回结果； 由EXIST 引出的子查询，其目标列表达式通常都用 *，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。,例44：求选修了1号课程学生姓名。 select sname from S where exists (select * from SC where sno=S.sno And cno=1);,求没有选修了1号课程学生姓名,85, 嵌套查询可以是多层的 查询涉及多个关系时，利用嵌套查询逐次求解层次分明，容易理解也容易书写，具有结构化程序设计的特点。 嵌套

36、查询的执行效率比连接查询效率高。,86,1：求选修了课程名为数据结构的学生的学号和姓名。 Select sno,sname from S Where sno in (select sno from SC where cno in (select cno from C where cname=数据结构)；,练习,87,2：求与王二同系，并且年龄大于刘五的学生学号和姓名。 Select sno,sname from S where Sdept=(select sdept from S where sname=王二) and Sage(select sage from S where sname=刘

37、五),88,6. 集合查询 并 UNION 交 INTERSECT 差 EXCEPT,89,作业： P. 127 第五题 （1）-（8）,90,复习上次课内容,1 创建一个学生表S(SNO,SNAME,SAGE)，SNO为主健; 2 给表S添加一个字符型属性SDEPT。 3 给SNAME添加唯一值约束条件。 4 为S表创建按姓名升序的唯一值索引 I_SNAME。 5 查询年龄大于20岁的学生。 6 查询各系学生的平均年龄 7 查询姓张的学生 8 查询王刚的数据库成绩,91,相关子查询,例41 找出每个学生超过他选修课程平均成绩的课程号. SELECT SNO,CNO FROM SC X WHE

38、RE GRADE = (SELECT FROM SC Y WHERE Y.SNO=X.SNO),例 求比刘晨年龄大的学生信息 SELECT * FROM S WHERE SAGE = (SELECT SAGE FROM S WHER SNAME=刘晨),92,例44：求选修了1号课程学生姓名。 select sname from S where exists (select * from SC where sno=S.sno And cno=1);,93,例46：求选修了全部课程的学生姓名。 select sname from S where not exists (select * from

39、 C where not exists (select * from SC where Sno=S.Sno And Cno=C.Cno);,94,数据操纵命令将导致数据库中数据 的改变。SQL语言的数据操纵功能包括三部分: INSERT 向表中插入数据 UPDATE 修改表中已存在行中的数据 DELETE 删除表中的数据,3.5 SQL数据更新（操纵） - DML,95,1. 插入数据 要求表必须是已存在的。 单行插入 一般格式： INSERT INTO (,) VALUES (,);,insert into s values(96001,李刚,CS,NULL,男,20),96,注意： 在表定

40、义时说明了NOT NULL 的属性列不能取空值，否则会出错。 字符型和日期型数据在插入时要加单引号。 对于在INSERT语句中未出现的列，那么这些列则为NULL；也可以显式地在VALUES子句中用NULL来代表空值进行插入数据。,insert into s (sno) values(96001),97, 多行插入 多行插入也称为表间拷贝，即从一个表中抽取若干行数据插入到另一个表中。 一般格式： INSERT INTO (,) SELECT 列表达式1 , 列表达式2 FROM WHERE ;,子查询,98,例50：把学生表S中在计算机系(CS)中的学生插入 到表S1中。 Create tabl

41、e S1 (sno char(10),snamechar(10),sage int,ssex char(2); Insert into S1 Select sno,sname,sage,ssex from S Where sdept = CS;,99, 利用查询实现表的定义与数据插入 产生一个表，但这个表的列是从子查询的结果中全部复制或部分复制，且子查询结果作为表的行插入到表中。 一般格式： CREATE TABLE (,) AS 子查询;,100,例：求每一个学生的平均成绩，把结果存入表S_GR中。 Create table S_GR (Sno char(8),Avg_grade int)

42、As Select sno,AVG(grade) form Sc Group by sno;,101,2. 修改数据 可以修改表中某些指定列的数据。 一般格式： UPDATE 别名 SET = ,= WHERE ; 其中：表达式=表达式|常量|列名,102,例：把所有学生的年龄改为22。 Update S set sage = 22; 例：把所有学生的年龄加上2。 Update S set sage = sage + 2; 例：把学号为“0401”学生的年龄改为22 。 Update S set sage 22 where sno=0401; 例：将计算机科学系所有学生的成绩置零。 Updat

43、e SC set Grade = 0 Where CS=(select sdept from S where S.sno=SC.sno);,103,注意： 如果不选WHERE子句，则表中所有的行全被更新； 如果选择了WHERE子句，则使WHERE 中条件表达式为真的行被更新；,104,3. 删除数据 删除表中某些行的数据。 一般格式： DELETE FROM WHERE ; 其中：表达式=表达式|常量|列名; 注意： 删除表中的全部记录后，该表的定义仍在数据字典中。,105,例：删除学号为“0401”学生的记录。 Delete from S where sno=0401; 例：删除所有学生的选

44、课记录。 Delete from SC;,例：删除计算机系所有学生的选课记录。 Delete from SC Where CS= (select sdept from S where S.sno=SC.sno);,106,R,1：把2改为5。 2：给第一列加2。 3：删除A=3的行。 4：删除所有行。,107,3.5 视图(VIEW),查询选修了课程名为“信息系统”的学生学号和姓名 Select sno,sname from S Where sno in (select sno from SC where cno in (select cno from C where cname=信息系统)；

45、,CREATE VIEW,S_XINXI,(SNO,SNAME),AS,Select sno,sname from S Where sno in (select sno from SC where cno in (select cno from C where cname=信息系统)；,创建一个视图,SELECT * FROM S_XINXI;,108,视图可以由一个表中选取的某些列或某些行组成，也可由若干表中满足一定条件的数据组成。简单地说，视图可以看成是一个窗口，它所反映的是一个表或若干表的局部数据。视图一经定义，用户就可以把它当作表一样来查询数据。 视图和基本表不同，视图是一个虚表，即视

46、图所对应的数据不实际存在数据库中，数据库中只存储视图的定义（存在数据字典中），因此不占用存储空间。,109,1. 视图的定义与删除 视图的定义 一般格式： CREATE VIEW (,) AS 子查询 WITH CHECK OPTION;,110,说明： WITH CHECK OPTION表示对视图进行UPDATE, INSERT和DELETE操作时要保证更新、插入和删除的行满足视图定义中的谓词条件。 属性列名或者全部省略或者全部指定，没有第3种选择；如果子查询中包含有计算列，则必须指定列名（别名）。,111,例1：建立数学系的学生视图。 Create view Ma_S as select

47、sno,sname,sage From S where Sdept=MA;,例2：建立数学系的学生视图,并要求修改操作仍需保证视图只有数学系的学生 Create view Ma_S as select sno,sname,sage From S where Sdept=MA With check option;,112,例3：多个表上的视图-建立数学系选修了1号课程的学生视图。 Create view Ma_S1(sno,sname,grade) as select S.sno,sname,grade From S,SC where Sdept=MA and s.sno=sc.sno And

48、o=1;,例4：视图上的视图-建立数学系选修了1号课程的且成绩在90分以上的学生视图。 Create view Ma_S2 as select * From Ma_S1 where grade90;,113,例6：把学生的学号及它的平均成绩定义为另一个视图。 Create view S_G(sno,gavg) as select sno,AVG(grade) From SC group by sno;,114,视图2,视图4,视图1,视图3,基本表1,基本表2,基本表3,基本表4,视图是定义在基本表上的，也可以定义在视图上；一个视图可在几个表或视图上建立，一个表或视图也可建立多个视图。,115

49、,视图的特点： 视图如同基本表，但不象基本表那样真实存在； 视图的数据来源于基本表的数据； 不能进行数据的拷贝。,116, 视图的删除 一般格式： DROP VIEW ; 说明： 视图不能修改； 视图删除后，与该视图相关的基本表的数据不受任何影响。 若导出视图的基本表删除，则由该基本表导出的所有视图为不可用的视图；,117,2. 视图的查询 视图的查询通常是通过转换为基本表的查询来实现的。即首先把查询转换成等价的对基本表的查询，然后执行修改了的查询（系统从数据字典中取出视图定义，然后把定义中的子查询和视图查询语句结合起来，形成一个修正的查询语句）。,用户查询,视图,基表,DB,用户,内部,11

50、8,例：求平均成绩为90分以上的学生的学号和 平均成绩（用例6的视图）。 Select * from S_G where Gavg=90;,Select sno,AVG(grade) from SC group by sno having AVG(grade)=90,119,3. 视图的更新 INSERT, DELETE, UPDATE 对视图的更新最终要转换成对基本表的更新，但实际上，在RDBMS中，并非所有的视图都是可以更新的，有些视图的更新不能唯一地有意义地转换成对基本表的更新。,120,例：将96001学生的平均成绩修改为90分。,UPDATE S_G SET Gavg=90 WHER

51、E SNO=96001;,例13：向数学系视图MA_S添加一条记录 Insert into MA_S values(2002018,赵刚,20); Insert into S values (2002018,赵刚,NULL,20,MA);,121,视图的更新必须遵守以下约束： 视图的定义是从一个基本表导出的； 定义视图的SELECT语句中不含有GROUP BY、DISTINCT和组函数； 视图的任何要更新的列不是通过列表达式定义的； 表中的所有NOT NULL列都在视图的定义中。,122,视图的删除操作：同时满足和约束； 视图的修改操作：同时满足、和约束； 视图的插入操作：同时满足以上全部约束

52、； 定义在多个表上的视图是不能进行更新的； 一个不允许更新的视图上定义的视图也不允许更新。,123,4. 视图的优点 简化了用户操作； 提供逻辑数据独立性； 例如：把学生关系 S(SNO, SNAME, SDEPT, SAGE)分为 SX(SNO, SANEM, SAGE) 和 SY(SNO, SDEPT)。 这时，原表S是SX，SY的自然连接的结果。因此可建立视图S CREATE VIEW S(SNO, SNAME, SDEPT, SAGE) AS SELECT SX.SNO, SX.SNAME, SY.SDEPT, SX.SAGE FROM SX, SY WHERE SX.SNO = SY

53、.SNO; 对机密数据提供了安全保护功能。,124,练习,1 建立计算机系（CS）的学生视图 2 建立包括系号和每个系平均年龄的视图,125,3.6 SQL数据控制 SQL数据控制功能是指控制用户对数据的存取权利。 在数据库中，所有数据是按用户划分的，各个用户一般不能存取别的用户的数据。如果要使各用户之间相互共享数据，就必须相互授予适当的权力。,126,创建用户,CREATE USER U001 IDENTIFIED BY K001 DEFAULT TABLESPACE DXPDATASPACE ;,GRANT CONNECT,RESOURCE TO U001;,给用户授权,127,RDBMS

54、的SQL数据控制功能必须要能够做到： 把授权的决定告之系统，这是由GRANT和REVOKE语句来完成的； 把授权的结果存入数据字典； 当用户提出操作请求时，根据授权情况进行检查，以决定是执行操作请求还是拒绝之。,128,1. 授权 - GRANT 授权就是给予用户一定的访问特权。 有两种授权： 授予某类数据库用户的特权，只能由DBA授予； 授予对某些数据对象进行某些操作的特权，可以由DBA授予，也可由数据对象的创建者授予。,129,不同对象允许的操作权限：,130,一般格式： GRANT 操作表|ALL ON TO WITH GRANT OPTION;,grant select on tabl

55、e SC to U007,WITH GRANT OPTION,131,例1：给用户USER01授予在SC表上的SELECT、INSERT和UPDATE的权力。 grant select,insert,update on table SC to user01; 例2：给用户USER01和USER02授予在S表上的SELECT和INSERT。 grant select,insert on table S to user01,user02; 例3：给用户USER01授予在C表上的所有权力。 grant all privileges on table C to user01; 例4：给所有用户授予SC

56、表上的SELECT权力。 grant select on table SC to public;,132,例5：给LNX用户授予对表S的SELECT权力，并使该用户具有给其它用户授予相同权力的用户。 grant select on table S to lnx with grant option; 例6：把查询SC表和修改GRADE的权限授予用户USER01。 grant select,update(grade) on table SC to user01;,133,2. 收回授权 - REVOKE 收回一些已授予用户的权限。 一般格式： REVOKE 操作表|ALL ON FROM , |P

57、UBLIC CASCADE/RESTRICT;,134,说明： 授予链：A - B， B - C，C - D，。 一旦A从B那里收回权限，则B授予C的，C授予D的使用权也一起收回； 收回属性列的操作权限要通过收回整个表的操作权来进行； 当删除表时，SQL自动地删除对该表授予的各种权限。,135,例：从用户LNX收回对S表的查询权限。 Revoke select on table S from lnx;,例：从用户LNX收回对S表的所有权限。 Revoke all on table S from lnx;,136,练习,1 把对表S的查询和修改权授予USER01 2 给USER01用户授予对课程

58、表C的查询权力，并使该用户具有给其它用户授予相同权力的用户 3 把查询S表和修改SNAME的权限授予所有用户。 4 收回USER01 对S表的所有权限。,137,模式 (schema),模式的定义 CREATE SCHEMA AUTHORIZATION ,CREATE SCHEMA AUTHORIZATION wang,138,CREATE SCHEMA AUTHORIZATION ross CREATE VIEW v1(c1) AS SELECT c1 from t1 CREATE TABLE t1(c1 int) GRANT SELECT on v1 TO public,139,删除模式,DROP SCHEMA ,140,3.7 SQL的事务处理 1. 事务处理的概述 所谓事务（Transaction）是指一系列动作的组合，这些动作被当作一个整体或一个逻辑单元来处理。 在数据库中，一个动作是指一个SQL语句。事务是一组SQL语句组成的一个逻辑单位。 SQL以逻辑工作单元为单位保证数据一致性。,141,Begin transaction sql语句 Commint (或者rollback),142,事务是一个完整的活动序列，包含一组操作，这些操作或者全部成功地