四章SQL.ppt

《四章SQL.ppt》由会员分享，可在线阅读，更多相关《四章SQL.ppt（85页珍藏版）》请在三一文库上搜索。

1、第四章 SQL SQL概述 SQL数据定义功能 SQL数据查询功能 SQL数据修改功能 SQL数据控制功能 嵌入式SQL SQL概述（） 历史 l1974年，由Boyce和Chamber提出。 l1975-1979年，在System R上实现，由IBM的San Jose研究室研制，称为Sequel，现在称为SQL (Struceured Query Languang)。 标准化 l有关组织 ANSI(American Natural Standard Institute) ISO(International Organization for Standardization) l有关标准 SQL

2、-86：“数据库语言SQL” SQL概述（） SQL-89：“具有完整性增强的数据库语言SQL”，增加了 对完整性约束的支持。 SQL-92：“数据库语言SQL”，是SQL-89的超集，增加 了许多新特性，如新的数据类型，更丰富的数据操作， 更强的完整性、安全性支持等。 SQL-3：正在讨论中的新的标准，将增加对面向对象模 型的支持。 特点 l一体化 集DDL，DML，DCL于一体。 单一的结构-关系，带来了数据操作符的统一。 l面向集合的操作方式 一次一集合。 SQL概述（） l高度非过程化 用户只需提出“做什么”，无须告诉“怎么做”，不必了解 存取路径。 l两种使用方式，统一的语法结构 S

3、QL既是自含式语言（用户使用），又是嵌入式语言（ 程序员使用）。 l语言简洁，易学易用 SQL功能操作符 数据查询SELECT 数据定义CREATE，DROP 数据操纵INSERT，UPDATE，DELETE 数据控制GRANT，REVOKE 示例关系 DEPT(D# , DNAME , DEAN) S(S# , SNAME , SEX , AGE , D#) COURSE(C# , CN , PC# , CREDIT) SC(S# , C# , SCORE) PROF(P# , PNAME, AGE, D# , SAL) PC(P# , C#) SQL数据定义功能 域定义 基本表的定义 索引

4、的定义 数据库的建立与撤消 SQL数据定义特点 域定义（） 域类型（SQL-92） lchar（n）：固定长度的字符串。 lvarchar（n）：可变长字符串。 lint：整数。 lsmallint：小整数类型。 lnumeric（p，d）：定点数，小数点左边p位，右边q位 。 lreal：浮点数。 ldouble precision：双精度浮点数。 ldate：日期（年、月、日）。 ltime：时间（小时、分、秒）。 linterval：两个date或time类型数据之间的差。 域定义（） 域定义 l格式 create domain 域名 数据类型 l示例 create domain per

5、son-name char（20） 类似C语言中： typedef ADDRESS_LIST char name10; char telephone20; char location20 char email20; ADDRESS_LIST tom; 基本表的定义（） 基本表的定义（CREATE） l格式 create table 表名（ 列名 数据类型 default 缺省值 not null ，列名 数据类型 default 缺省值 not null ，primary key（列名 ，列名 ） ，foreign key （列名 ，列名 ） references 表名 （列名 ，列名 ） ，

6、check（条件） 基本表的定义（） l示例 create domain person_name char(20) create table PROF ( PNO char10, person_name PNAME not null, SAL int, AGE int, DNO char10, primary key (PNO), foreign key (DNO) references DEPT(DNO), check (SAL 0) 基本表的定义（） 修改基本表定义（ALTER） l格式： alter table 表名 add 子句增加新列 drop 子句删除列 modify 子句修改列定

7、义 l示例 alter table PROF add LOCATION char30 基本表的定义（） 撤消基本表定义（drop） l格式 drop table 表名 l示例 drop table DEPT l l 危险危险 撤消基本表后，基本表的定义、表中数据、索引、 以及由此表导出的视图的定义都被删除。 索引的定义（） 索引的定义 l格式 create unique/distinct cluster index 索引名 on 表名 (列名 asc/desc , 列名asc/desc) unique（distinct）：唯一性索引，不允许表中不同的 行在索引列上取相同值。若已有相同值存在，则

8、系统 给出相关信息，不建此索引。系统并拒绝违背唯一性 的插入、更新。 cluster：聚集索引，表中元组按索引项的值排序并物 理地聚集在一起。一个基本表上只能建一个聚集索引 。 asc/desc：索引表中索引值的排序次序，缺省为asc。 l示例： create cluster index s-index on S（S#） 索引的定义（） 索引的删除 l格式： drop index 索引名 索引的有关说明 l可以动态地定义索引，即可以随时建立和删除索引 。 l不允许用户在数据操作中引用索引。索引如何使用 完全由系统决定，这支持了数据的物理独立性。 l应该在使用频率高的、经常用于连接的列上建索引

9、。 l一个表上可建多个索引。索引可以提高查询效率， 但索引过多耗费空间，且降低了插入、删除、更新 的效率。 数据库的建立与撤消 有的数据库系统支持多库。 建立一个新数据库 create database 数据库名 撤消一个数据库 drop database 数据库名 指定当前数据库 database 数据库名 指定当前数据库 close database 数据库名 SQL数据定义特点 SQL中，任何时候都可以执行一个数据定义语句，随 时修改数据库结构。而在非关系型的数据库系统中， 必须在数据库的装入和使用前全部完成数据库的定义 。若要修改已投入运行的数据库，则需停下一切数据 库活动，把数据库卸

10、出，修改数据库定义并重新编译 ，再按修改过的数据库结构重新装入数据。 数据库定义不断增长（不必一开始就定义完整）。 数据库定义随时修改（不必一开始就完全合理）。 可进行增加索引、撤消索引的实验，检验其对效率的 影响。 SQL数据查询功能 SQL数据查询基本结构 select子句 重复元组的处理 from子句 where子句 更名运算 字符串操作 元组显示顺序 集合操作 SQL数据查询功能 分组和聚集函数 空值 嵌套子查询 派生关系 视图 关系的连接 SQL数据查询基本结构 基本结构 select A1 , A2 , , An from r1 , r2 , , rm where P A1 , A

11、2 , , An(p(r1 r2 rm) 示例 给出所有老师的姓名。 select PNAME from PROF select子句 目标列形式 可以为列名，* ，算术表达式，聚集函数。 l“*”：表示“所有的属性”。 给出所有老师的信息。 select * from PROF l带， ， 的算术表达式 给出所有老师的姓名及税后工资额。 select PNAME，SAL 0.95 from PROF 重复元组的处理 语法约束 缺省为保留重复元组，也可用关键字all显式指明。 若要去掉重复元组，可用关键字distinct或unique指 明。 示例 找出所有选修课程的学生。 select dis

12、tinct SNO from SC from子句（） 说明 from子句列出查询的对象表。当目标列取自多个表 时，在不混淆的情况下可以不用显式指明来自哪个 关系。 示例 l例：找出工资低于500的职工的姓名、工资、系别 。 select PNAME , SAL , DNAME from PROF , DEPT where SAL P2.SAL 注：as可选。 字符串操作（） 命令格式 llike：找出满足给定匹配条件的字符串。 格式：列名 not like “字符串” l匹配规则： “%” ：匹配零个或多个字符。 “”：匹配任意单个字符。 escape ：定义转义字符，以去掉特殊字符的特定含

13、义，使其被作为普通字符看待。如escape “”，是 定义了 作为转义字符，则可用%去匹配%，用 去匹配，用 去匹配 。 字符串操作（） 示例 l列出姓名以“张”打头的教师的所有信息。 select * from PROF where PNAME like “张%” l列出名称中含有4个字符以上，且倒数第3个字符是 d，倒数第2个字符是_的系的所有信息。 select * from PROF where PNAME like “% d ” 元组显示顺序 命令 order by 列名 asc | desc 示例 l按系名升序列出老师姓名，所在系名，同一系中老 师按姓名降序排列。 select D

14、NAME，PNAME from PROF，DEPT where PROF.DNO = DEPT.DNO order by DNAME asc，PNAME desc 集合操作（） 命令 集合并：union 集合交：intersect 集合差： except 示例 l求选修了001或002号课程的学生号。 （select SNO from SC where CNO = 001） union all （select SNO from SC where CNO = 002） 集合操作（） l求选修了001和002号而没有选003号课程的学生号 。 （select SNO from SC where C

15、NO = 001 or CNO = 002 ） except （select SNO from SC where CNO = 003） 提示 集合操作自动去除重复元组，如果要保留重复元组 的话，必须用all关键词指明。 分组和聚集函数（） 分组命令 group by 列名 having 条件表达式 group by将表中的元组按指定列上的值相等的原则分组， 然后在每一分组上使用聚集函数，得到单一值。having则 对分组进行选择，只将聚集函数作用到满足条件的分组上 。 聚集函数 l平均值：avg l最小值：min l最大值：max l总和：sum l记数：count 分组和聚集函数（） 示例

16、l列出各系的老师的最高、最低、平均工资。 select DNO，max(SAL)，min(SAL)，avg(SAL) from PROF group by DNO l列出及格的学生的平均成绩。 select SNO，avg(SCORE) from SC group by SNO having min(SCORE) = 60 分组和聚集函数（） l? 求选修了课程的学生人数。 select count (SNO ) from SC select PNAME，max(SAL) from PROF select DNO，avg(SAL) from PROF group by DNO where AG

17、E 60 空值（） 空值测试 is not null 测试指定列的值是否为空值。 示例 找出年龄值为空的老师姓名。 select PNAME from PROF where AGE is null 不可写为where AGE = null 空值（） 注意事项 l除is not null之外，空值不满足任何查找条件。 l如果null参与算术运算，则该算术表达式的值为 null。 l如果null参与比较运算，则结果可视为false。在 SQL-92中可看成unknown。 l如果null参与聚集运算，则除count(*)之外其它聚集 函数都忽略null。 例：select sum(SAL) fro

18、m PROF 例：select count(*) from PROF 嵌套子查询 集合成员资格 集合之间的比较 集合基数的测试 l测试集合是否为空 l测试集合是否存在重复元组 集合成员资格（） in 子查询 表达式 not in （子查询） 判断表达式的值是否在子查询的结果中。 示例 l选修了001号课程的学生的学号及姓名。 select SNO，SNAME from S where SNO in (select SNO from SC where CNO = 001） 集合成员资格（） l列出选修了001号和002号课程的学生的学号。 select SNO from SC where SC.

19、CNO = 001 and SNO in （select SNO from SC where CNO = 002） l列出张军和王红同学的所有信息。 select * from S where SNAME in （“张军”，“王红”） 集合之间的比较（） some/all子查询 l表达式 比较运算符 some （子查询） 表达式的值至少与子查询结果中的一个值相比满足 比较运算符 。 l表达式 比较运算符 all （子查询） 表达式的值与子查询结果中的所有的值相比都满足 比较运算符。 集合之间的比较（） 示例 l找出平均成绩最高的学生号。 select SNO from SC group by

20、SNO having avg(SCORE) = all （select avg(SCORE) from SC group by SNO） 集合基数的测试（） 测试集合是否为空 not exists （子查询） 判断子查询的结果集合中是否有任何元组存在。 l列出选修了01号课程的学生的学号及姓名。 select SNO，SNAME from S where exists （select * from SC where CNO = 01 and SNO = S.SNO） 集合基数的测试（） l列出选修了001号和002号课程的学生的学号。 select SNO from SC SC1 where

21、SC1.CNO = 001 and exists （select SNO from SC SC2 where SC2. CNO = 002 and SC2.SNO = SC1.SNO） l注：in后的子查询与外层查询无关，每个子查询执 行一次，而exists后的子查询与外层查询有关，需 要执行多次，称之为相关子查询。 l列出至少选修了001号学生选修的所有课程的学生名。 select SNAME from S where not exists （select CNO from COURSE where exists （select * from SC where SC.CNO = COURSE

22、.CNO and SC.SNO = 001） and not exists （select * from SC where SC.CNO = COURSE.CNO and SC.SNO = S.SNO） 任意课程，001号学生 选之，所求学生选之 。 不存在任何一门课程 ，001号学生选之，所 求学生没有选之。 集合基数的测试（） 测试集合是否存在重复元组 unique (子查询） 如果子查询结果中没有重复元组，则返回true。 示例 l找出所有只教授一门课程的老师姓名。 select PNAME from PROF where unique （select PNO from PC where

23、 PC.PNO = PROF.PNO) 集合基数的测试（） l找出至少选修了两门课程的学生姓名。 select SNAME from S where not unique （select SNO from SC where SC.SNO = S.SNO) 思考 to TRUE or not to TRUE , that is the question. unique (a , b , null) , (a , b , null) ? 派生关系（） 命令 （子查询） as 关系名（列名，列名，） SQL-92中，允许在from子句中使用子查询表达式，这 时可将该子查询的结果命名为一个临时关系加以

24、引用 。 示例 l找出平均成绩及格的学生。 先求出每个学生的平均成绩，再从中找出及格的学生 select SNAME ， avg（SCORE） from S，SC where SC.SNO = S.SNO group by SC .SNO 派生关系（） select SNAME ， AVG_SCORE from (select SNAME ， avg（SCORE） from S，SC where SC.SNO = S.SNO group by SC .SNO) as result(SNAME ， AVG_SCORE ) where AVG_SCORE = 60 派生关系 Vs 视图？ 视图（）

25、 定义视图 create view view_name(列名，列名 ) as （查询表达式） with check option 视图的属性名缺省为子查询结果中的属性名，也可以 显式指明。 with check option指明当对视图进行insert，update时， 要检查是否满足视图定义中的条件。 撤消视图 drop view view_name 视图（） 示例 create view COMPUTER_PROF as (select PNO , PNAME , SAL from PROF，DEPT where PROF.PNO = DEPT.PNO and DEPT.DNAME = “

26、计算机系”） create view DEPTSAL( DNO, LOW, HIGH, AVERAGE, TOTAL ) as ( select DNO, min(SAL), max(SAL), avg(SAL), sum(SAL) from PROF group by DNO ) 视图（） l给出计算机系工资超过800的老师姓名。 select PNAME from COMPUTER_PROF where SAL 800 l给出计算机系老师的最低、最高、平均工资以及工 资总额。 select LOW , HIGH , AVERAGE , TOTAL from DEPTSAL , DEPT w

27、here DEPTSAL.DNO = DEPT.DNO and DEPT.DNAME =“计算机系” 关系的连接（） 基本分类 连接成分包括两个输入关系、连接条件、连接类型 。 l连接条件：决定两个关系中哪些元组相互匹配，以 及连接结果中出现哪些属性。 l连接类型：决定如何处理与连接条件不匹配的元组 。 连接类型连接条件 inner join left outer join right outer join full outer join nature on using (A1, A2 , An) 关系的连接（） l自然连接：出现在结果关系中的两个连接关系的元 组在公共属性上取值相等，且公共属

28、性只出现一次 。 lon ：出现在结果关系中的两个连接关系的 元组在公共属性上取值满足谓词条件P，且公共属 性出现两次。 lusing (A1, A2 , An)： (A1, A2 , An)是两个连接 关系的公共属性的子集，元组在(A1, A2 , An)上 取值相等，且(A1, A2 , An)只出现一次。 l内连接：舍弃不匹配的元组。 l左外连接：内连接 + 左边关系中失配的元组（缺少 的右边关系属性值用null表示）。 关系的连接（） l右外连接：内连接 + 右边关系中失配的元组（缺少 的左边关系属性值用null表示）。 l全外连接：内连接 + 左边关系中失配的元组（缺少 的右边关系属

29、性值用null表示）+ 右边关系中失配 的元组（缺少的左边关系属性值用null表示）。 lcross join：两个关系的笛卡儿积。 lunion join：左边关系中失配的元组+ 右边关系中失 配的元组。 l对于外连接，连接条件是必须的； 对于内连接，连接条件是可选的，没有连接条件等 价于两个关系的笛卡儿积。 关系的连接（） l列出老师的教工号、姓名、工资、所教课程号。 select PNO，PNAME，SAL，CNO from （PROF nature left outer join PC） lR inner join S on R.C = S.C RS 关系的连接（） lR left o

30、uter join S on R.C = S.C lR nature right outer join S 关系的连接（） lR full outer join S on R.C = S.C SQL的数据修改功能 插入 删除 修改 视图更新 插入操作（） 命令 insert into 表名 （列名，列名 values （值 ，值） 插入一条指定好值的元组 insert into 表名 （列名，列名 （子查询） 插入子查询结果中的若干条元组 示例 linsert into PROF values ( P123, “王明”, 35, D08, 498 ) linsert into PROF (PN

31、O, PNAME, DNO) values ( P123, “王明”, D08 ) 思考：SAL取何值？如何防止插入带有空值的元组？ 插入操作（） l将平均成绩大于90的学生加入到EXCELLENT中。 insert into EXCELLENT ( SNO, GRADE) select SNO , avg(SCORE) from SC group by (SNO) having avg(SCORE) 90 l l FORBIDDEN(FORBIDDEN( INFORMIXINFORMIX) ) insert into PROF select * from PROF 若支持，则完成查询后，再执

32、行修改操作 不支持修改在子查询中出现的表 删除操作（） 命令 delete from 表名 where 条件表达式 从表中删除符合条件的元组，如果没有where语句， 则删除所有元组。 示例 l清除所有选课记录 delete from SC l删除王明老师所有的任课记录。 delete from PC where PNO in (select PNO from PROF where PNAME = “王明”) 删除操作（） l删除低于平均工资的老师记录。 delete from PROF where SAL 2000 update PROF set SAL = SAL * 0.95 where

33、 SAL 0：取到主变量的值发生了截断，指示变 量的值是截断前的字符串的实际长度 。 需要解决的几个问题（） 指示变量的用法：声明与宿主变量的声明方式一样 ，在数据操纵语句中，在宿主变量和指示变量之间 加( : )或关键字indicator。 EXEC SQL BEGIN DECLARE SECTION int prof_no; char prof_name30; int salary; short name_id; shortsal_id; EXEC SQL END DECLARE SECTION EXEC SQL select PNAME , SAL into :prof_name : n

34、ame_id , :salary: sal_id from PROF where PNO = prof_no ; 需要解决的几个问题（） SQL与主语言之间操作方式的协调 SQL：一次一集合。 C语言：一次一记录。 l游标：在查询结果的记录集合中移动的指针。 若一个SQL语句返回单个元组，则不用游标。 若一个SQL语句返回多个元组，则使用游标。 l不需要游标的数据操作 结果是一个元组的select语句 EXEC SQL select PNAME , SAL into :prof_name : name_id , :salary: sal_id from PROF where PNO = pro

35、f_no ; 需要解决的几个问题（） insert语句 EXEC SQL insert into PROF values (:prof_no, :prof_name , :salary , :dept_no , : salary) ; delete语句 EXEC SQL delete from PROF values PNO :prof_no ; update语句 EXEC SQL update PROF set SAL = :salary where PNO = : prof_no ; 需要解决的几个问题（） l需要游标的数据操作 当select语句的结果中包含多个元组时，使用游标 可以逐个

36、存取这些元组。 活动集：selecT语句返回的元组的集合。 当前行：活动集中当前处理的那一行。游标即是指 向当前行的指针。 游标分类： 滚动游标：游标的位置可以来回移动，可在活动集中取 任意元组。 非滚动游标：只能在活动集中顺序地取下一个元组。 更新游标：数据库对游标指向的当前行加锁，当程序读 下一行数据时，本行数据解锁，下一行数据加锁。 需要解决的几个问题（） 定义与使用游标的语句 declare：定义一个游标，使之对应一个select语 句。 declare 游标名 scroll cursor for select语句 for update of列表名 for update任选项，表示该游

37、标可用于对当前行 的修改与删除。 open：打开一个游标，执行游标对应的查询，结 果集合为该游标的活动集。 open 游标名 需要解决的几个问题（） fetch ：在活动集中将游标移到特定的行，并取 出该行数据放到相应的宿主变量中。 fetch next | prior | first | last | current | relative n | absolute m 游标名 into 宿主变量表 close ：关闭游标，释放活动集及其所占资源。 需要再使用该游标时，执行open语句。 close 游标名 free：删除游标，以后便不能再对该游标执行 open语句了 free 游标名 需要解决的几个问题（） SQL语句执行信息反馈 l良好的应用程序必须提供对错误的处理,应用程序 需要知道SQL语句是否正确执行了，发生错误时的 错误代码，执行时遇到特殊情况时的警告信息。 lSQL通讯域SQLCA是一结构,每一嵌入SQL语句的执行 情况在其执行完成后写入USERCA结构中的各变量中 , 根据SQLCA中的内容可以获得每一嵌入SQL语句执 行后的信息,应用程序就可以做相应的处理。 l为了说明 (USERCA),必须在应用程序中包括: EXEC SQL INCLUDE SQLCA; 作业 4.2 c，g， h，j