第4章SQL语言.ppt

1、第4章 SQL语言,SQL语言（结构化查询语言）是当前关系数据库的标准操作语言。 大部分的RDBMS都支持SQL。 SQL有86，89，92，99版本。 本章主要讲述SQL92语言的用法。 要求能熟练使用SQL语句在不同的RDBMS中完成数据库的基本操作。,第4章 SQL语言,4.1 SQL概述 4.2 数据定义 4.3 数据查询 4.4 数据更新 4.5 视图 4.6 数据控制 4.7 嵌入式SQL 4.小结 4.9 练习,4.1 SQL概述,SQL经历了一个逐步发展过程。 SQL具有不同于其他语言的特点。 SQL对关系数据库模式提供支持。 SQL语言语句简单，只用几条语句就能完成数据库的基

2、本操作。 本节主要介绍SQL的基本知识，要求对SQL有一个全局性的基本了解。,4.1 SQL概述,4.1.1 SQL的发展过程 4.1.2 SQL的特点 4.1.3 SQL对关系数据库模式的支持 4.1.4 SQL语言的基本知识,4.1.1 SQL的发展过程,1974年由Boyce 和Chamberlin提出； 1975-1979年 IBM的San Jose Research Labortatory研制的RDBMS原型系统ystem R中初次实现； 1986年 ANSI公布第一个SQL标准； 1987、1989、1992.不断扩充； 目前有三个标准：SQL86、SQL92、SQL99。,4.1

3、.2 SQL的特点,1. 综合统一。 2. 高度非过程化。 3. 面向集合操作。 4. 以同一种语法结构提供两种使用方式（自含式和嵌入式）。 5. 简洁易学易用。,4.1.3 SQL对RDBS模式的支持,SQL,视图2,视图1,基本表1,基本表2,基本表3,基本表4,外模式,模式,内模式,存储文件1,存储文件2,4.1.4 SQL语言的基本知识,SQL语句的动词只有九条。,4.2 数据定义,SQL的数据定义语句（DDL） 可定义表结构、索引、视图等，也可进行修改和删除。 定义表结构时要注意完整性约束。 定义索引时要注意查询的要求和速度。 定义视图时要注意用户和应用开发的需要。,4.2 数据定义

4、,4.2.1 DDL概述 4.2.2 基本表的定义、删除和修改 4.2.3 索引操作(建立和删除),4.2.1 DDL概述,SQL的数据定义语句（DDL）包括以下语句：,注意：视图和索引无修改语句！,4.2.2 基本表操作一：定义,语句格式： CREATE TABLE （ 列完整性约束条件 列完整性约束条件），表级完整性约束条件； 例：建立学生表 student1。CREATE TABLE student1(sno char(5) not null unique，sname char(20)，ssex char(2) )；,语句格式：ALTER TABLE ADD 列完整性约束条件DROP M

5、ODIFY ； 例：修改course表结构增加一个jc 属性alter table course add jc char(20) not null； 例：删除course表的jc 属性alter table course drop jc；,4.2.2 基本表操作二：修改,4.2.2 基本表操作三：删除,语句格式：DROP TABLE ; 删除表结构时，表中的数据也一并删除。删除表要慎重！例：删除课程表。drop table course;,4.2.3 索引：索引作用,建立索引可有效提高查询的速度。如果把一个基本库表比作一本书，索引就好像书的目录，通过查询目录，可找到相关章节的页号，从而可迅速地

6、找到那一节内容。不同的是，基本表可建立不止一个索引，它可按不同的属性或表达式建立多个索引。,4.2.3 索引之二：建立,语句格式:CREATE UNIQUE CLUSTER INDEX ON （ ，); 说明： （1）ASC升序，DESC降序，缺省为ASC。 （2）CLUSTER为聚族索引（指索引项的顺序与表中记录的物理顺序一致的索引组织）。一个表只有一个。 （3） UNIQUE表示唯一索引。 例：按课程表的课程名建立索引。create unique index course_name on course(cname);,4.2.3 索引之三：删除,删除索引语句格式：DROP INDEX ；

7、例：DROP INDEX course_name ；,4.3 数据查询语句,数据查询是DBS最常用的一项操作。 DBS必须提供强大而完善的数据查询功能。 对于关系数据库，查询有时可能需要从多个表中取得数据。 SQL只用SELECT就能完成各种查询。 SELECT用法很灵活。,4.3 数据查询,4.3.1 SELECT一般格式 4.3.2 SELECT查询方式 4.3.3 单表查询 4.3.4 多表查询 4.3.5 连接查询,4.3.1 SELECT一般格式,SELECT ALL | DISTINCT 别名 ， 别名 FROM ， WHERE GROUP BY HAVING ORDER BY A

8、SC | DESC；,4.3.1 SELECT一般格式说明,1. 目标列表达式可以有以下格式： （1）* （2）.* （3）COUNT(ALL | DISTINCT * ) （4）. 别名 ， . 别名 2. WHERE条件表达式非常灵活 3. GROUP BY 表示按列名1的值分组，每个组产生结果表中一记录。HAVING 表示符合条件的组才输出。 4. ORDER BY ASC | DESC：表示排序。,4.3.1 SQL语言简易格式,SELECT ：投影 FROM ：连接 WHERE ：选取 GROUP BY ：分组 HAVING ：去组 ORDER BY ：排序,4.3.1 SQL结果的

9、转向,SELECT FROM WHERE 缺省：输出到临时窗口 TO SCREEN：输出到屏幕 TO ：输出到TXT文件 INTO TABLE ：输出到表,4.3.2 SELECT查询方式,1.单表查询 （1）选择若干列：指定列；全部列；经过计算的列 （2）选择若干行：消除重复的行；满足条件的行（比较大小，确定范围，确定集合，字符匹配，空值，多条件）； （3）对查询结果排序。（4）使用集函数。（5）分组 2. 连接查询（等值与非等值连接；自身连接；外连接；复合条件连接） 3. 嵌套查询（用IN子查询；用=；用ANY和ALL；用EXISTS）,4.3.3 查询：成绩管理数据库,在学生成绩管理数据

10、库中，包括基本的三个关系：student，course，sc 。 （1）student（sno，sname，ssex，sage，sdept），表示学号，姓名，性别，年龄，示所在系。主码为sno。 （2）Course (cno，cname，cpno，ccredit)，表示课程号，课程名，先行课程号，学分。主码为cno。 （3）SC (sno，cno，grade)，表示学号，课程号，成绩。主码为(sno，cno)。,4.3. 单表查询,例.查询全体学生详细记录 select * from student; 例.查询信息系所有男生的学号、姓名、出生年份Select sno， sname，2002-s

11、age from student where ssex=男 and sdept=IS; 例.查询选修过课的学生的学号Select distinct sno from sc;,4.3. 查询满足条件的元组,4.3. 查询满足条件的元组例子,例. 查询年龄在25-30之间的学生姓名及性别。Select sname，ssex from studentwhere sage between 25 and 30; 例. 查询姓“欧阳”的学生 。Select * from student where sname like 欧阳%; 例. 查询信息系IS，数学系MA和计算机系CS的学生。Select * fr

12、om studentwhere sdept in (IS，MA，CS);,4.3.4 使用集函数查询,集函数包括： COUNT（DISTINCT | ALL *）统计元组个数 COUNT（DISTINCT | ALL ）统计一列中值的个数 SUM（DISTINCT | ALL ）计算一列值的总和 AVG（DISTINCT | ALL ）计算一列的平均值 MAX（DISTINCT | ALL ）计算一列的最大值 MAX（DISTINCT | ALL ）计算一列的最小值 例. 统计学生总人数。select count(*) from student; 例. 查询选修了课程的学生人数。select

13、count(distinct sno) from sc;,4.3.5 分组查询,例. 查询各个课程号与相应的选课人数据。SELECT Cno COUNT(Sno) FROM sc GROUP BY Cno; 例1. 查询选修了4门课以上的学生的学号。SELECT Sno FROM SCGROUP BY SnoHAVING COUNT(*)4,4.3. 连接查询,连接查询包括：等值与非等值连接；自身连接；外连接；复合条件连接 例1. 查询每一门课的间接先修课Select o，second.pcno From course first，course secondWhere first.pcno=o

14、; 例1. 查询每个学生及其选课信息(右外连接)Select student.sno，sname，ssex，sage，sdept，cno，gardeFrom student，scWhere student.sno=sc.sno(*)；,4.3. 嵌套查询,可以完成很复杂的查询功能。 子查询的结果作为父查询的条件件。 包括（用IN子查询；用=；用ANY和ALL；用EXISTS）。 不同方法实现同一种查询效率相差很大，即需要进行查询优化。any 表示大于子查询中的某个值; all 表示大于子查询中的所有值,4.3.6 嵌套查询-EXISTS,带有EXISTS的子查询不返回实际数据，只产生逻辑值，子

15、查询非空，返回T，否则，返回F。 例1.查询选修了全部课程的学生姓名（即没有一门课程他不选修）Select sname from studentwhere not exists(select * from course where not exists(select * from sc where sno=student.sno and sno=o)；,4.3. 嵌套查询-EXISTS,例1. 查询至少选修了学生“2001002选修的全部课程的学号。 该查询转换为：不存在这样的课程Y，学生2001002选了Y，但学生X没有选Y。Select distinct sno from sc scx w

16、here not exists(select * from sc scywhere scy.sno=2001002 and not exists (select * from sc scz where scz.sno=scx.sno and o=o);,4.3. 集合查询并交差,并UNION、交INTERSECT、差MINUS。交差运算标准SQL未提供，通过其它方法实现。 例. 查询数学系学生及年龄等于20岁的学生。Select * from student where sdept=math UnionSelect * from student where sage=20 例1. 查询数学系学

17、生与年龄等于20岁学生的交集。Select * from studentwhere sdept=math and sage=20 例. 查询数学系学生与年龄等于20岁学生的差集。Select * from student where sdept=math and sage20,4.3.8 SELECT练习一,对STUDENT、COURSE、SC三个表，用SQL完成以下操作。 1.创建关系stu1(sno，sname，ssex，sage，sdept)。 2.对STUDENT按姓名建立索引。 3.查询信息系所有年龄不大于21岁的女生。 4.查询1982年出生的男生的姓名。 5.查询信息系、金融系所

18、有姓“王”的同学的姓名和年龄。 6.查询姓“王”的男同学的人数。 7.查询2号课程的最低分。 8.查询总分最高的学生的学号。 9.查询每个同学的平均分。 10.查询每个同学所选修的课程门数。,4.3.8 SELECT练习二,对STUDENT、COURSE、SC三个表完成以下操作： 1. 查询“计算机网络“分数大于90分的同学的姓名。 2. 查询每门课程的课程名及选修人数。 3. 查询选修了全部课程的学生姓名。 4. 查询总学分已超过40学分的学生学号、姓名、总学分。 5. 查询至少选修了学生”020001“选修的全部课程的姓名。 6. 查询其他系中比CS系任一学生年龄都小的学生名单。 7. 查

19、询每一个同学的学号、姓名、选修的课程名及分数。 8. 查询信息系学生及年龄小于20岁的学生。 9. 查询信息系学生与年龄小于20岁学生的交集。 10. 查询信息系学生与年龄小于20岁学生的差集。,4.4 数据更新,数据更新是对关系中的数据进行插入、修改、删除。 在进行更新时，应注意维护数据库中数据的一致性。 SQL提供三条语句来完成，即INSERT、DELETE、UPDATE。,4.4 数据更新,4.4.1 插入记录 4.4.2 修改数据 4.4.3 删除记录,4.4.1 插入：插入单个元组,语句格式： INSERT INTO （，）VALUES ( ，)； 说明:（）如某属性列在INTO子句

20、中没有，则新记录中在该属性列上取空值。（）为NOT NULL的属性列不能为空。（）如INTO子句中没有指明任何属性列，则新记录须在每个属性列上均有值。 例:向S表插入（“99035”，“陈红”，“女”，“CS”，20） INSERT INTO SVALUES（“99035”，“陈红”，“女”，“CS”，20）,4.4.1 插入：插入子查询结果,格式： INSERT INTO （,）子查询； 例：先建一个与S同结构的关系S2，然后将S的所有男生数据插入S2中INSERT INTO S2SELECT * FROM SWHERE ssex=男；,4.4.2 修改数据,语句格式：UPDATE SET

21、=， = WHERE ； 例：将信息系全体学生成绩置0。UPDATE SC SET Grade=0 WHERE IS=(SELECT Sdept FROM StudentWHERE Student.Sno=Sc.Sno);,4.4.3 删除数据,语句格式：DLEETE FROM WHERE ； 例，删除“李红”的学生记录。DELETE FROM Student WHERE Sname=李红； 例，删除信息系全体学生的选课记录。DELETE FROM SC WHERE IS=(SELECT Sdept FROM StudentWHERE Student.Sno=Sc.Sno)；,4.5 视图,什

22、么是视图？ 视图是从不同角度观察库中数据所得的一个数据集合。如student表中所有的男生，所有数学系学生。 视图是数据库一种重要的数据保护机制，可增强数据的独立性。 开发数据库应用系统时应进行规划设计，确定需要建立哪些视图。,4.5 视图,4.5.1 视图基本知识 4.5.视图的功能 4.5.3 建立视图 4.5.4 查询视图 4.5.5 删除视图 4.5.6 更新视图 4.5.7 视图的类型,4.5.1 视图基本知识,视图与基本表的区别： （1）视图是多用户从不同角度观察库中数据的重要机制。 （2）视图是从一个或几个基本表（或视图）导出的表，是个虚表，本身不保存数据，数据仍保存在基本表中。

23、 （3）在视图上可再定义新视图。视图一经定义，就可和基本表一样被查询和删除，但对视图的增、删、改操作有限制。,4.5. 视图的功能,视图的功能包括： （1）能够简化用户的操作； （2）视图能使用户从多种角度看待同一数据； （3）对重构数据库提供了一定程度的逻辑独立性； （4）能对机密数据提供安全保护。,4.5.3 建立视图,CREATE VIEW （， ）AS WITH CHECK OPTION; 说明： 1. 子查询一般不允许含有ORDER BY 和DISTINCT短语。 2. with check option 表示对视图进行update、 insert、 delete操作时要保证所操作的

24、行满足视图定义的谓词条件。 3. 在以下情况必须在视图中的确定列名：A. 其中某个列不是单纯的属性名而是集函数或表达式；B. 多表连接时选出了几个同名列作为视图的字段；C. 需要在视图中为某个列启用更合适的名字。,例. 创建数学系学生的视图。CREATE VIEW MATH_S ASSELECT * FROM S WHERE SDEPT=MATH； 例2. 建立选修了“数据库原理”的学生视图（从多表中取数）CREATE VIEW DB_S(SNO，SNAME，GRADE)AS SELECT SSNO，SNAME，GRADE FROM S，C，SCWHERE S.SNO=SC.SNO AND S

25、C.CNO=C.CNO AND CNAME=数据库原理；,4.5.3 建立视图示例,4.5.4 删除视图,格式 ：DROP VIEW ； 说明：1. 删除基本表后，基本表的视图失效。2. 视图被删后，该视图的导出视图也将失效。 例：将学生的学号及平均成绩创建一个视图。CREATE VIEW S_G(sno，gavg)AS SELECT sno，AVG(grade) FROM SCGROUP BY sno; 例：删除视图S_G。DROP VIEW S_G;,4.5.5 查询视图,对视图的查询最终将转变为对基本表的查询。 例：查询数学系选修了3号课程的学生。SELECT sno，sname FRO

26、M math_student，scWHERE math_student.sno=sc.sno AND o=3 例：查询平均成绩大于95分的学生学号。SELECT sno，AVG(grade) FROM SC GROUP BY snoHAVING AVG(grade)95; 例：这个查询不能用视图：SELECT * FROM S_G WHERE gavg95,4.5.6 更新视图,更新包括插入（INSERT）、删除（DELETE）、修改（UPDATE）。 对视图的更新最终要转化为对基本表的更新。但不是所有的视图都能转化为对基本表的更新，有些视图是不能更新的。 例：数学系学号为200133的学生改

27、名为张强。UPDATE math_student SET sname= 张强 WHERE sno=200133,4.5.6 更新视图例子,例：向数学系学生视图插入一新生记录（200158，李强，男，22，数学系）。INSERT INTO math_studentVALUES （200158，李强，男，22） 例：删除数学系学号为200168的学生。DELETE FROM math_student WHERE sno= 200168 例：将学号为200138的学生的平均成绩改为85分。（对该视图的更新不能实现！）,4.5.7 视图的类型,4.6 数据控制,提供了数据控制功能，能在一定程度上保证数

28、据的安全性、完整性、并提供了一定的并发控制和恢复能力。 1. 完整性：定义库结构 2. 安全性：存取控制，规定不同用户对于不同数据对象允许执行的操作，并控制各用户它有权存取的数据。 3. 并发控制和恢复：SQL支持事务、提交、回滚等概念。,4.6 数据控制,4.6.1 权限 4.6.2 授权 4.6.3 收权,4.6.1 权限,不同类型的操作对象的操作权限。,4.6.2 授权,语句格式： GRANT ， ON TO ，WITH GRANT OPTION;说明： （1）不同类型的操作对象的操作权限。 （2）接受权限的用户可以是一个或多个，也可是PUBLIC。 （3）WITH GRANT OPTI

29、ON ：用户可将权限授予别的用户。,4.6.2 授权例题,例: 把查询student表和修改学生学号的权限授给用户user2GRANT UPDATE(sno)，SELECTON student TO user2; 例: 把对SC表的删除权限授给user5，并允许他此权限授予给别的用户。GRANT DELETE ON sc TO user5 WITH GRANT OPTION;,4.6.3 收回权限,语句格式：REVOKE ，ON FROM ，； 例： 把user2对学生学号的修改权限收回。REVOKE UPDATE（sno）ON TABLE student FROM user2; 例： 收回所

30、有用户对表的查询权限。REVOKE SELECT ON scFROM PUBLIC；,4.6.4 SQL练习,对STUDENT、COURSE、SC三个表完成以下操作： 1. 向student中增一新同学(“020089”，“王飞”，“男”，19，“IS”) 2. 删除一“李军”同学的所有信息。 3. 为信息系所有男生建一视图S_ISM。 4. 利用视图查询信息系“王强”同学。 5. 授给user2用户更新SC关系的权限。 6. 从user2用户收回更新SC关系的权限。,4.7 嵌入式SQL,SQL是非过程性语言，无过程性结构，大多数语句独立执行，不能根据不同的条件执行不同的任务，所以单纯用SQ

31、L语句很难完成实际的应用，往往需要将SQL语言同其它高级语言结合起来使用。 SQL有两种用法：自含式SQL（交互式）和嵌入式SQL 。 自含式SQL就是在DBMS环境中使用SQL来对关系进行交互式操作，本节主要讲述嵌入式SQL 的用法。,4.7 嵌入式SQL,4.7.1 嵌入式SQL一般用法 4.7.2 高级语言与SQL语句通信 4.7.3 不用游标的SQL语句 4.7.4 游标的用法 4.7.5使用游标的SQL语句 4.7.6 静态SQL和动态SQL,4.7.1 嵌入式SQL一般用法,1. 一般形式为： EXEC SQL ；C语言中：例 EXEC SQL DROP TABLE student

32、;COBLE语言中： EXEC SQL DROP TABLE student END-EXEC;2. 处理方式： （1）预编译； （2）修改扩充宿主语言使之能处理SQL语句。,4.7.2 高级语言与SQL语句通信,（1）通过使主变量或指示变量，可以向SQL语句输入或输出值。一般变量前加“：”号。 （2）SQL语句每次执行后通过SQL通信区返回状态信息，SQLCODE。 （3） 游标SQL面向集合，主语言面向记录，一组主变量一次只能存放一条记录，为此，嵌入式SQL常使用游标来协调两种不同的处理方式。游标是系统为用户开设的一个数据缓冲区，存放SQL执行的结果。每个游标区有一个名字，用户可利用SQL

33、语句从游标中一条条取记录，并赋给主变量，交由主语言作进一步处理。,4.7.3 不用游标的SQL语句之一,1. 说明性语句EXEC SQL BEGIN DECLARE SECTION；EXEC SQL END DECLARE SECTION 2. 数据定义语句EXEC SQL CREATE TABLE student1(sno char(5) NOT NULL UNIQUE，sname char(20); 3. 数据控制语句EXEC SQL GRANT INSERT ON TABLE student TO U1; 4. 查询结果为单记录的SELECT语句EXEC SQL SELECT INTO

34、， ,4.7.3 不用游标的SQL语句之二,5. 非CURRENT形式的UPDATE语句EXEC SQL UPDATE SC SET Grade=:newgrade WHERE sno=:givensno; 6. 非CURRENT形式的DELECT语句EXEC SQL DELETE FROM SC WHERE sno=(SELECT sno FROM student WHERE sname=:stdname); 7. INSERT语句Gardeid=-1EXEC SQL INSERT INTO SC(sno，cno，Grade) VALUES(:stdno，:couno，:gr:gradeid

35、);,4.7.4 使用游标的步骤,1. 说明游标EXEC SQL DECLARE CURSOR FOR ； 2. 打开游标EXEC SQL OPEN ; 3. 推进游标指针并取当前记录EXEC SQL FETCH INTO ， 4. 关闭游标EXEC SQL ;,4.7.5 使用游标的SQL语句,1. 查询结果为多条记录的SELECT语句 2. CURRENT形式的UPDATE语句和DELECT语句（p122）,4.7.6 静态SQL和动态SQL,静态SQL：语句中主变量的个数和数据类型在预编译时是确定的，只有主变量的值是程序运行过程中动态输入的。编程灵活性不足。 动态SQL：允许程序在运行过

36、程中临时“组装”SQL语句。如果在编译时下列信息不确定则要用动态SQL语句：SQL语句正文、主变量个数、主变量的数据类型、SQL语句中引用的数据库对象（列、索引、基本表、视图等）。SQL提供了相应语句实现动态SQL：EXECUTE IMMEDATE，PREPARE， EXECUTE，DESCRIBE等。,4.8 小结,本章节主要讲述了SQL的用法。 SQL86可操纵的元素主要的表、索引、视图等 数据定义是建立表、索引、视图。 SQL查询用SELECT，这是重点和难点。 数据更新包括插入、修改、删除。 视图是一种很好的机制，可保护数据安全 权限是对数据库操作时要考虑的因素，如何授权和收回权限，在

37、数据库管理中很重要。 在高级语言中使用SQL很方便，但要注意一些问题。,4.9 练习1,.设有三个数据表，写出下列操作的 SQL语句。R(BH,XM,XB,DWH)表示：编号、姓名、性别、单位号S(DWH,DWM)表示：单位号、单位名T(BH,XM,XB,DWH)表示：编号、姓名、性别、单位号 (1)实现RT。 (2)实现DWH=100(R) 。 (3)实现XM,XB(R) 。 (4)实现XM,DWH(XB=女(R) 。 (5)实现R*S 。 (6)实现XM,XB,DWM(XB=男(R*S) 。,4.9 练习2,.设有如下关系表 R(NO,NAME,SEX,AGE,CLASS)主关键字是NO，

38、其中NO为学号，NAME为姓名，SEX为性别，AGE为年龄，CLASS为班号。 写出实现下列功能的SQL语句： (1) 插入一个记录(25,李明,男,21,95031)。 (2) 插入95031班学号为30、姓名为郑和的学生记录。 (3) 将学号为10的学生姓名改龙王华。 (4) 将所在95101班号改为95091。 (5) 删除学号为20的学生记录。 (6) 删除姓王的学生记录。,4.9 练习3,.设有如下三个基本表，表结构如下： BORROWER（借书证号，姓名，系名，班级） LOANS（借书证号，图书登记号，借书日期） BOOKS（索书号，书名，作者，图书登记号，出版社，价格） 试用SQL语言进行查询： (1) 检索借了5本书以上的学生的借书证号、姓名、系名和借书数量。 (2) 检索借书和欧阳同学所借图书中任意一本相同的学生的姓名、系名、书名和借书日期。 (3) 建立信息系学生借书的视图SB，该视图的属性列由借书证号、姓名、班级、图书登记号、书名、出版社和借书日期组成。,