第四部分SQL教学课件.ppt

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

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

3、取路径。l两种使用方式，统一的语法结构 SQL既是自含式语言（用户使用），又是嵌入式语言（程序员使用）。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示例creat

5、e domain person-name char（20）类似C语言中：typedef ADDRESS_LISTchar name10;char telephone20;char location20char 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 PROFadd LOCATION char30基本表的定义（基本表的定义（）撤消基本表定义（撤消基本表定义（drop）l格式drop table 表名l示例 drop table DEPT 撤消基本表后，基本表的定义、表中数据、索引、以及由此表导出的视图的定义都被删除。索引的定义（索引的定义（）索引的定义索引的定义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数据查询功能数据查询功能 分组和聚集函数分组和聚集函数 空值空值 嵌套子

11、查询嵌套子查询 派生关系派生关系 视图视图 关系的连接关系的连接SQL数据查询基本结构数据查询基本结构 基本结构基本结构select A1,A2,Anfrom r1,r2,rmwhere P A1,A2,An(p(r1 r2 rm)示例示例给出所有老师的姓名。select PNAMEfrom PROFselect子句子句 目标列形式目标列形式 可以为列名，*，算术表达式，聚集函数。l“*”：表示“所有的属性”。给出所有老师的信息。select *from PROFl带，的算术表达式 给出所有老师的姓名及税后工资额。select PNAME，SAL 0.95from PROF重复元组的处理重复元

12、组的处理 语法约束语法约束 缺省为保留重复元组，也可用关键字all显式指明。若要去掉重复元组，可用关键字distinct或unique指明。示例示例 找出所有选修课程的学生。select distinct SNO from SCfrom子句（子句（）说明说明 from子句列出查询的对象表。当目标列取自多个表时，在不混淆的情况下可以不用显式指明来自哪个关系。示例示例l例：找出工资低于500的职工的姓名、工资、系别。select PNAME,SAL,DNAME from PROF,DEPT where SAL P2.SAL 注：as可选。字符串操作（字符串操作（）命令格式命令格式llike：找出满

13、足给定匹配条件的字符串。格式：列名 not like “字符串”l匹配规则：“%”：匹配零个或多个字符。“”：匹配任意单个字符。escape：定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待。如escape“”，是定义了 作为转义字符，则可用%去匹配%，用去匹配，用 去匹配 。字符串操作（字符串操作（）示例示例l列出姓名以“张”打头的教师的所有信息。select *from PROF where PNAME like “张%”l列出名称中含有4个字符以上，且倒数第3个字符是d，倒数第2个字符是_的系的所有信息。select *from PROF where PNAME like

14、“%d”元组显示顺序元组显示顺序 命令命令order by 列名列名 asc|desc 示例示例l按系名升序列出老师姓名，所在系名，同一系中老师按姓名降序排列。select DNAME，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（selec

15、t SNO from SC where CNO=002）集合操作（集合操作（）l求选修了001和002号而没有选003号课程的学生号。（select SNO from SC where CNO=001 or CNO=002）except（select SNO from SC where CNO=003）提示提示 集合操作自动去除重复元组，如果要保留重复元组的话，必须用all关键词指明。分组和聚集函数（分组和聚集函数（）分组命令分组命令group by 列名列名 having 条件表达式条件表达式 group by将表中的元组按指定列上的值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值

16、。having则对分组进行选择，只将聚集函数作用到满足条件的分组上。聚集函数聚集函数l平均值：avgl最小值：minl最大值：maxl总和：suml记数：count分组和聚集函数（分组和聚集函数（）示例示例l列出各系的老师的最高、最低、平均工资。select DNO，max(SAL)，min(SAL)，avg(SAL)from PROF group by DNOl列出及格的学生的平均成绩。select SNO，avg(SCORE)from SC group by SNO having min(SCORE)=60分组和聚集函数（分组和聚集函数（）l?求选修了课程的学生人数。select coun

17、t(SNO)from SC select PNAME，max(SAL)from PROF select DNO，avg(SAL)from PROF group by DNO where AGE 60空值（空值（）空值测试空值测试is not null测试指定列的值是否为空值。示例示例找出年龄值为空的老师姓名。select PNAME from PROF where AGE is null不可写为where AGE=null空值（空值（）注意事项注意事项l除is not null之外，空值不满足任何查找条件。l如果null参与算术运算，则该算术表达式的值为null。l如果null参与比较运算，则

18、结果可视为false。在SQL-92中可看成unknown。l如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null。例：select sum(SAL)from PROF 例：select count(*)from PROF嵌套子查询嵌套子查询 集合成员资格集合成员资格 集合之间的比较集合之间的比较 集合基数的测试集合基数的测试l测试集合是否为空l测试集合是否存在重复元组集合成员资格（集合成员资格（）in 子查询子查询表达式表达式 not in （子查询）（子查询）判断表达式的值是否在子查询的结果中。示例示例l选修了001号课程的学生的学号及姓名。select SNO，S

19、NAME from S where SNO in (select SNO from SC where CNO=001）集合成员资格（集合成员资格（）l列出选修了001号和002号课程的学生的学号。select SNO from SC where SC.CNO=001 and SNO in（select SNO from SC where CNO=002）l列出张军和王红同学的所有信息。select *from S where SNAME in（“张军”，“王红”）集合之间的比较（集合之间的比较（）some/all子查询子查询l表达式表达式 比较运算符比较运算符 some（子查询）（子查询）表达

20、式的值至少与子查询结果中的一个值相比满足比较运算符。l表达式表达式 比较运算符比较运算符 all（子查询）（子查询）表达式的值与子查询结果中的所有的值相比都满足比较运算符。集合之间的比较（集合之间的比较（）示例示例l找出平均成绩最高的学生号。select SNO from SC group by SNO having avg(SCORE)=all（select avg(SCORE)from SC group by SNO）集合基数的测试（集合基数的测试（）测试集合是否为空测试集合是否为空not exists （子查询）（子查询）判断子查询的结果集合中是否有任何元组存在。l列出选修了01号课程的

21、学生的学号及姓名。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 SC1.CNO=001 and exists（select SNO from SC SC2 where SC2.CNO=002 and SC2.SNO=SC1.SNO）l注：in后的子查询与外层查询无关，每个子查询执行一次，而exists后的子查询与外层查询有关，需要执行多次，称之

22、为相关子查询。l列出至少选修了001号学生选修的所有课程的学生名。select SNAME from S where not exists（select CNO from COURSE where exists（select *from SC where SC.CNO=COURSE.CNO and SC.SNO=001）and not exists（select *from SC where SC.CNO=COURSE.CNO and SC.SNO=S.SNO）任意课程，001号学生选之，所求学生选之。不存在任何一门课程，001号学生选之，所求学生没有选之。集合基数的测试（集合基数的测试（）测

23、试集合是否存在重复元组测试集合是否存在重复元组unique (子查询）子查询）如果子查询结果中没有重复元组，则返回true。示例示例l找出所有只教授一门课程的老师姓名。select PNAME from PROF where unique（select PNO from PC where PC.PNO=PROF.PNO)集合基数的测试（集合基数的测试（）l找出至少选修了两门课程的学生姓名。select SNAME from S where not unique（select SNO from SC where SC.SNO=S.SNO)思考思考 to TRUETRUE or not to TR

24、UETRUE,that is the question.unique (a,b,null),(a,b,null)?派生关系（派生关系（）命令命令（子查询）（子查询）as 关系名（列名，列名，关系名（列名，列名，）SQL-92中，允许在from子句中使用子查询表达式，这时可将该子查询的结果命名为一个临时关系临时关系加以引用。示例示例l找出平均成绩及格的学生。先求出每个学生的平均成绩，再从中找出及格的学生 select SNAME，avg（SCORE）from S，SC where SC.SNO=S.SNO group by SC.SNO派生关系（派生关系（）select SNAME，AVG_SC

25、OREfrom (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 视图？视图（视图（）定义视图定义视图create view view_name(列名列名，列名，列名)as （查询表达式）（查询表达式）with check option 视图的属性名缺省为子查询结果中的属性名，也可以显式指明。with check option指明当对视图进行insert，update时，要检查是否满足视图定义中的条件。

26、撤消视图撤消视图drop view view_name视图（视图（）示例示例create view COMPUTER_PROFas (select PNO,PNAME,SAL from PROF，DEPT where PROF.PNO=DEPT.PNO and DEPT.DNAME=“计算机系”）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的老师姓名。sele

27、ct PNAMEfrom COMPUTER_PROFwhere SAL 800l给出计算机系老师的最低、最高、平均工资以及工资总额。select LOW,HIGH,AVERAGE,TOTALfrom DEPTSAL,DEPTwhere DEPTSAL.DNO=DEPT.DNO and DEPT.DNAME=“计算机系”关系的连接（关系的连接（）基本分类基本分类 连接成分包括两个输入关系、连接条件、连接类型。l连接条件连接条件：决定两个关系中哪些元组相互匹配，以及连接结果中出现哪些属性。l连接类型连接类型：决定如何处理与连接条件不匹配的元组。连接类型连接类型连接条件连接条件inner joinl

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

29、的元组（缺少的右边关系属性值用null表示）。关系的连接（关系的连接（）l右外连接：右外连接：内连接+右边关系中失配的元组（缺少的左边关系属性值用null表示）。l全外连接：全外连接：内连接+左边关系中失配的元组（缺少的右边关系属性值用null表示）+右边关系中失配的元组（缺少的左边关系属性值用null表示）。lcross join：两个关系的笛卡儿积。lunion join：左边关系中失配的元组+右边关系中失配的元组。l对于外连接，连接条件是必须的；对于内连接，连接条件是可选的，没有连接条件等价于两个关系的笛卡儿积。关系的连接（关系的连接（）l列出老师的教工号、姓名、工资、所教课程号。sel

30、ect PNO，PNAME，SAL，CNOfrom （PROF nature left outer join PC）ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3lR inner join S on R.C=S.CABCCDa1b1c1c1d1a2b2c2c2d2RS关系的连接（关系的连接（）lR left outer join S on R.C=S.CABCCDa1b1c1c1d1a2b2c2c2d2a3b3c3nullnulllR nature right outer join SABCDa1b1c1d1a2b2c2d2nullnullc4d3关系的连接（关系的连接

31、（）lR full outer join S on R.C=S.CABR.CS.CDa1b1c1c1d1a2b2c2c2d2a3b3c3nullnullnullnullnullc4d3SQL的数据修改功能的数据修改功能 插入插入 删除删除 修改修改 视图更新视图更新插入操作（插入操作（）命令命令insert into 表名表名 （列名（列名，列名，列名values （值（值，值，值）插入一条指定好值的元组insert into 表名表名 （列名（列名，列名，列名（子查询）（子查询）插入子查询结果中的若干条元组 示例示例linsert into PROF values(P123,“王明”,35,

32、D08,498)linsert into PROF(PNO,PNAME,DNO)values(P123,“王明”,D08)思考：SAL取何值？如何防止插入带有空值的元组？插入操作（插入操作（）l将平均成绩大于90的学生加入到EXCELLENT中。insert into EXCELLENT(SNO,GRADE)select SNO,avg(SCORE)from SCgroup by(SNO)having avg(SCORE)90 insert into PROFselect *from PROF若支持，则完成查询后，再执行修改操作不支持修改在子查询中出现的表删除操作（删除操作（）命令命令dele

33、te from 表名 where 条件表达式 从表中删除符合条件的元组，如果没有where语句，则删除所有元组。示例示例l清除所有选课记录delete from SCl删除王明老师所有的任课记录。delete from PCwhere PNO in (select PNO from PROF where PNAME=“王明”)删除操作（删除操作（）l删除低于平均工资的老师记录。delete from PROFwhere SAL 2000 update PROF set SAL=SAL*0.95 where SAL 0：取到主变量的值发生了截断，指示变 量的值是截断前的字符串的实际长度。需要解决

34、的几个问题（需要解决的几个问题（）指示变量的用法：声明与宿主变量的声明方式一样，在数据操纵语句中，在宿主变量和指示变量之间加(:)或关键字indicator。EXEC SQL BEGIN DECLARE SECTIONint prof_no;char prof_name30;int salary;short name_id;shortsal_id;EXEC SQL END DECLARE SECTIONEXEC SQL select PNAME,SAL into :prof_name:name_id,:salary:sal_id from PROF where PNO=prof_no;需要解决

35、的几个问题（需要解决的几个问题（）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=prof_no;需要解决的几个问题（需要解决的几个问题（）insert语句 EXEC SQL insert int

36、o 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语句的结果中包含多个元组时，使用游标可以逐个存取这些元组。活动集：活动集：selecT语句返回的元组的集合。当前行：当前行：活动集中当前处理的那一行。游标即是指向当

37、前行的指针。游标分类：滚动游标：游标的位置可以来回移动，可在活动集中取任意元组。非滚动游标：只能在活动集中顺序地取下一个元组。更新游标：数据库对游标指向的当前行加锁，当程序读下一行数据时，本行数据解锁，下一行数据加锁。需要解决的几个问题（需要解决的几个问题（）定义与使用游标的语句 declare：定义一个游标，使之对应一个select语句。declare 游标名 scroll cursor for select语句for update of列表名for update任选项，表示该游标可用于对当前行的修改与删除。open：打开一个游标，执行游标对应的查询，结果集合为该游标的活动集。open 游标

38、名需要解决的几个问题（需要解决的几个问题（）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;EXEC SQL INCLUDE SQLCA;作业作业 4.2 c，g，h，j