第四部分SQL教学课件

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

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

3、L功能操作符数据查询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数据定义功能域定义基本表的定义索引的定义数据库的建立与撤消SQL数据定义特点域定义（）域类型（SQL-92）char（n）：固定长度的字符串。varchar

4、（n）：可变长字符串。int：整数。smallint：小整数类型。numeric（p，d）：定点数，小数点左边p位，右边q位。real：浮点数。double precision：双精度浮点数。date：日期（年、月、日）。time：时间（小时、分、秒）。interval：两个date或time类型数据之间的差。域定义（）域定义格式 create domain 域名 数据类型示例create domain person-name char（20） 类似C语言中：typedef ADDRESS_LISTchar name10;char telephone20;char location20char

5、 email20;ADDRESS_LIST tom;基本表的定义（）基本表的定义（CREATE）格式 create table 表名（ 列名 数据类型 default 缺省值 not null ，列名 数据类型 default 缺省值 not null ，primary key（列名 ，列名 ） ，foreign key （列名 ，列名 ） references 表名 （列名 ，列名 ） ，check（条件）基本表的定义（）示例 create domain person_name char(20)create table PROF( PNO char10, person_name PNAME

6、not null, SAL int, AGE int, DNO char10, primary key (PNO), foreign key (DNO) references DEPT(DNO), check (SAL 0)基本表的定义（）修改基本表定义（ALTER）格式：alter table 表名add 子句增加新列drop 子句删除列modify 子句修改列定义示例alter table PROFadd LOCATION char30基本表的定义（）撤消基本表定义（drop）格式drop table 表名示例 drop table DEPT危险 撤消基本表后，基本表的定义、表中数据、索引

7、、以及由此表导出的视图的定义都被删除。索引的定义（）索引的定义格式 create unique/distinct cluster index 索引名 on 表名 (列名 asc/desc , 列名asc/desc) unique（distinct）：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背唯一性的插入、更新。 cluster：聚集索引，表中元组按索引项的值排序并物理地聚集在一起。一个基本表上只能建一个聚集索引。 asc/desc：索引表中索引值的排序次序，缺省为asc。 示例： create cluster index

8、s-index on S（S#） 索引的定义（）索引的删除格式： drop index 索引名索引的有关说明可以动态地定义索引，即可以随时建立和删除索引。不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持了数据的物理独立性。应该在使用频率高的、经常用于连接的列上建索引。一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率。数据库的建立与撤消 有的数据库系统支持多库。建立一个新数据库create database 数据库名撤消一个数据库drop database 数据库名指定当前数据库database 数据库名指定当前数据库close d

9、atabase 数据库名SQL数据定义特点 SQL中，任何时候都可以执行一个数据定义语句，随时修改数据库结构。而在非关系型的数据库系统中，必须在数据库的装入和使用前全部完成数据库的定义。若要修改已投入运行的数据库，则需停下一切数据库活动，把数据库卸出，修改数据库定义并重新编译，再按修改过的数据库结构重新装入数据。数据库定义不断增长（不必一开始就定义完整）。数据库定义随时修改（不必一开始就完全合理）。可进行增加索引、撤消索引的实验，检验其对效率的影响。SQL数据查询功能SQL数据查询基本结构select子句重复元组的处理from子句where子句更名运算字符串操作元组显示顺序集合操作SQL数据查

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

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

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

13、desc示例按系名升序列出老师姓名，所在系名，同一系中老师按姓名降序排列。 select DNAME，PNAME from PROF，DEPT where PROF.DNO = DEPT.DNO order by DNAME asc，PNAME desc集合操作（）命令集合并：union集合交：intersect集合差： except示例求选修了001或002号课程的学生号。（select SNO from SC where CNO = 001）union all（select SNO from SC where CNO = 002）集合操作（）求选修了001和002号而没有选003号课程的学

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

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

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

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

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

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

20、O = 002 and SC2.SNO = SC1.SNO）注：in后的子查询与外层查询无关，每个子查询执行一次，而exists后的子查询与外层查询有关，需要执行多次，称之为相关子查询。列出至少选修了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 =

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

22、elect 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子句中使用子查询表达式，这时可将该子查询的结果命名为一个临时关系加以引用。示例找出平均成绩及格的学生。 先求出每个学生的平均成绩，再从中找出及格的学生 select SNAME ， avg（SCORE） from S，SC where SC.SNO =

23、 S.SNO group by SC .SNO派生关系（）select SNAME ， AVG_SCOREfrom (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 o

24、ption指明当对视图进行insert，update时，要检查是否满足视图定义中的条件。撤消视图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)

25、 from PROF group by DNO )视图（）给出计算机系工资超过800的老师姓名。select PNAMEfrom COMPUTER_PROFwhere SAL 800给出计算机系老师的最低、最高、平均工资以及工资总额。select LOW , HIGH , AVERAGE , TOTALfrom DEPTSAL , DEPTwhere DEPTSAL.DNO = DEPT.DNO and DEPT.DNAME =“计算机系”关系的连接（）基本分类 连接成分包括两个输入关系、连接条件、连接类型。连接条件：决定两个关系中哪些元组相互匹配，以及连接结果中出现哪些属性。连接类型：决定如

26、何处理与连接条件不匹配的元组。连接类型连接条件inner joinleft outer joinright outer joinfull outer joinnatureon using (A1, A2 , An)关系的连接（）自然连接：出现在结果关系中的两个连接关系的元组在公共属性上取值相等，且公共属性只出现一次。on ：出现在结果关系中的两个连接关系的元组在公共属性上取值满足谓词条件P，且公共属性出现两次。using (A1, A2 , An)： (A1, A2 , An)是两个连接关系的公共属性的子集，元组在(A1, A2 , An)上取值相等，且(A1, A2 , An)只出现一次。内

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

28、教课程号。select PNO，PNAME，SAL，CNOfrom （PROF nature left outer join PC）R inner join S on R.C = S.CRS关系的连接（）R left outer join S on R.C = S.CR nature right outer join S关系的连接（）R full outer join S on R.C = S.CSQL的数据修改功能插入删除修改视图更新插入操作（）命令insert into 表名 （列名，列名values （值 ，值）插入一条指定好值的元组insert into 表名 （列名，列名（子查询）插

29、入子查询结果中的若干条元组示例insert into PROF values ( P123, “王明”, 35, D08, 498 )insert into PROF (PNO, PNAME, DNO) values ( P123, “王明”, D08 ) 思考：SAL取何值？如何防止插入带有空值的元组？插入操作（）将平均成绩大于90的学生加入到EXCELLENT中。insert into EXCELLENT ( SNO, GRADE)select SNO , avg(SCORE)from SCgroup by (SNO)having avg(SCORE) 90FORBIDDEN( INFOR

30、MIX) insert into PROFselect *from PROF若支持，则完成查询后，再执行修改操作不支持修改在子查询中出现的表删除操作（）命令delete from 表名 where 条件表达式 从表中删除符合条件的元组，如果没有where语句，则删除所有元组。示例清除所有选课记录delete from SC删除王明老师所有的任课记录。delete from PCwhere PNO in (select PNO from PROF where PNAME = “王明”) 删除操作（）删除低于平均工资的老师记录。delete from PROFwhere SAL 2000 upda

31、te PROF set SAL = SAL * 0.95 where SAL 0：取到主变量的值发生了截断，指示变 量的值是截断前的字符串的实际长度。需要解决的几个问题（）指示变量的用法：声明与宿主变量的声明方式一样，在数据操纵语句中，在宿主变量和指示变量之间加( : )或关键字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 , SA

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