数据库系统概论第三章.ppt

1、第三章关系数据库标准语言SQL,SQL语言,SQL概述 SQL数据查询功能 SQL数据定义功能 SQL数据更新功能 嵌入式SQL,SQL概述,历史 1974年，由Boyce和Chamberlin提出，称为SEQUEL（Structured English Query Language）。 1975-1979年，在IBM的San Jose研究室研制的System R上实现。 1981年， IBM在推出SQL/DS关系数据库时，将其命名为SQL (Structured Query Language)。 现在，不仅关系型数据库系统，甚至非关系型数据库系统都将SQL作为其数据库语言。SQL语言已成为当

2、今最为成功和应用最广的数据库语言。,SQL概述（I）,有关标准 随着SQL语言应用的日益广泛，ANSI和ISO先后制定了多个SQL标准： SQL-86：较为简单，主要包括数据定义语言、数据操纵语言、嵌入式语法等几个部分。 SQL-89：增加了对完整性约束的支持。 SQL-92：也称SQL2，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。 SQL-3：正在讨论中的新的标准，将增加对面向对象模型的支持。,SQL概述（II）,特点 综合统一 集数据定义，数据操纵，数据查询和数据控制于一体。 单一的结构-关系，带来了数据操作符的统一。 高度非过程

3、化 用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径。 面向集合的操作方式 操作的对象和操作的结果均为集合。 以同一种语法结构提供两种使用方式 SQL既是自含式语言，又是嵌入式语言,SQL概述（III）,语言简捷，易学易用 类似于英语自然语言,SQL对关系数据库模式的支持,SQL对关系数据库模式的支持,基本表 是本身独立存在的表，一个（或多个）基本表对应一个存储文件 存储文件 由数据块构成的存储空间，用于存储基本表、索引等。其逻辑结构组成了关系数据库的内模式 视图 是从一个或几个基本表中导出的表，其本身不独立存储于数据库中,例子表,STUDENT,SC,COURSE,SQL数据查询

4、功能,查询语句SELECT的语法： Select ALL|DISTINCT, From , Where Group By ,HAVING Order By , ,SQL数据查询功能,其语义是：从FROM子句给定的表中，检索出满足WHERE子句给定检索条件的记录，并根据SELECT子句从这些记录中选出指定的列（属性）所构成的结果集合。SELECT和FROM两个子句定义了投影操作。WHERE子句给出了选取操作的定义（当涉及多表操作时，WHERE子句要同时给出连接操作条件）。可见，查询语句作为整体代表着关系代数中的投影、选取、连接等操作的组合。但是，由于在查询语句中并不规定投影、选取、连接等操作的执

5、行顺序。因此，它比关系代数更一般，更少过程化。,SQL数据查询功能,单表查询 连接查询 嵌套查询 集合查询,单表查询,查询全体学生的姓名与学号 Select Sname, Sno From Student; 查询全体学生的全部属性 Select * From Student; 查看全体学生的姓名及其出生年份 Select Sname, 2001-Sage From Student;,单表查询（续I）,查询全体学生所在学校、姓名、出生年份和所在系（用小写字母显示系名）。 Select “北航”, Sname, 2001-Sage, ISLOWER(Sdept) From Student; Sel

6、ect “北航” School, Sname, 2001-Sage Birth, ISLOWER(Sdept) From Student;,单表查询（续II）,单表查询（续III）,查询选修了课程的学生学号 Select Distinct Sno From STUDENT; 查询计算机系全体学生的姓名 Select Sname From Student where Sdept=CS; 查询所有年龄在20岁以下的学生姓名及其年龄 Select Sname From Student where Sage20;,单表查询（续IV）,查询年龄在2023（含20和23岁）之间的学生姓名、性别和年龄。 S

7、elect Sname, Ssex, Sage From Student Where Sage BETWEEN 20 AND 23; 查询数学系和计算机系的学生的姓名和性别。 Select Sname, Ssex From Student Where Sdept IN (MA, CS);,单表查询（续V）,查询所有姓刘的学生的学号、姓名 Select Sno, Sname From Student Where Sname Like 刘% 查询所有姓刘，并且名字为两个汉字的学生的学号、姓名 Select Sno, Sname From Student Where Sname Like 刘_,单表

8、查询（续VI）,查询所选课程没有成绩的学生的学号，所选课程号。 Select Sno, Cno From SC Where Grade IS NULL,单表查询（续VII）,ORDER BY子句可以将查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排序。 查询全体学生的情况，并按所在系的升序和年龄的降序排列 Select * From Student Order By Sdept Asc, Sage Desc,单表查询（续VIII）,聚集函数 Count() 求元组个数 Sum() 对数值列求总和 Avg() 求数值列的平均值 Max() 求最大值 Min() 求最小值,单表查询

9、（续IX）,查询学生总数 Select Count(*) From Student; 查询选修了课程的学生人数 Select Count(Distinct Sno) From SC; 计算1号课程的学生平均成绩 Select AVG(Grade) From SC Where Cno=1; 查询选修1号课程的学生最高分数 Select MAX(Grade) From SC Where Cno=1;,单表查询（续X）,查询各门课及其相应选课人数。 Select Cno, Count(Sno) From SC Group By Cno; 查询选课至少为三门的学生的学号及其选课门数。 Select S

10、no, Count(Sno) From SC Group By Sno Having Count(Sno)=3;,连接查询,连接条件 连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为： . . 其中的称为连接字段， 连接条件的各个连接字段类型必须是可比的，但不必是相同的。主要有：=，=，=，!=。当连接运算符为等号时，称为等值连接，否则称非等值连接。,连接查询（续I）,查询每个学生及其所选课程的情况 Select Student.*, SC.* From Student, SC Where Student.Sno = SC.Sno; 查询每一门课的间接先修课（即先修课的先修课

11、） Select FIRST.Cno, SECOND.Cpno From Course FIRST, Course SECOND Where FIRST. Cpno = SECOND. Cno 查询所有学生的学号、姓名、所选修课程的课程号和成绩 Select Student.Sno, Sname, Cno, Grade From Student, SC Where Student.Sno=SC.Sno(*),嵌套查询,在SQL语言中，一个SELECT-FROM-WHERE 称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。上层的查询块称

12、为外层查询或父查询，下层查询称为内层查询或子查询。外层查询利用子查询来获取检索条件的条件值，并根据这个条件值确定外层查询的结果集合。通过子查询，可以利用一系列简单查询来构造一个复杂查询。 子查询中的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。,嵌套查询（续I）,不相关子查询 不相关子查询的执行顺序是由内向外，即每个子查询在上一级查询处理前求解，子查询的结果集合作为其外部查询的检索条件的条件值。 相关子查询 相关子查询的查询条件引用了其外部查询的某个属性值，从而其执行依赖于其外部查询。其执行顺序是：首先取外部查询选取一行记录（称为候选行），然后子查

13、询利用候选行中相关列的值查询结果数据。接着外部查询利用子查询返回的结果集合判断候选行是否满足检索条件，若满足，则把它放入外部查询的结果集合。重复上述步骤直到处理完外部查询的每一行候选行。,嵌套查询（续II）,查询与刘晨在同一个系学习的学生。 Select Sno, Sname, Sdept From Student Where Sdept = (Select Sdept From Student Where Sname = 刘晨);,嵌套查询（续III）,查询选修了课程名为信息系统的学生的学号和姓名。 Select Sno, Sname From Student Where Sno IN (S

14、elect Sno From SC Where Cno IN (Select Cno From Course Where Cname = 信息系统);,嵌套查询（续IV）,查询其他系中比信息系中某一学生年龄小的学生姓名和年龄。 Select Sname, Sage From Student Where Sdept IS And Sage Any(Select Sage From Student Where Sdept = IS),嵌套查询（续V）,查询其他系中比信息系中所有学生年龄都小的学生姓名和年龄。 Select Sname, Sage From Student Where Sdept I

15、S And Sage All(Select Sage From Student Where Sdept = IS),嵌套查询（续VI）,查询选择了1号课程的学生姓名。 Select Sname From Student Where Exists (Select * from SC Where Sno=Student.Sno And Cno=1) 查询没有选择1号课程的学生姓名。 Select Sname From Student Where Not Exists (Select * from SC Where Sno=Student.Sno And Cno=1),嵌套查询（续VII）,查询选修

16、了全部课程的学生姓名 Select Sname From Student Where Not Exists (Select * from Course Where Not Exists (Select * from SC Where Sno=Student.Sno And Cno=Course.Cno);,集合查询,查询选修了1号或2号课程的学生 Select Sno From SC Where Cno=1 Union Select Sno From SC Where Cno=2,集合查询（续I）,查询选修了1号课程的学生和选修了2号课程的学生的交集 Select Sno From SC Wh

17、ere Cno=1 And Sno In (Select Sno From SC Where Cno=2),集合查询（续II）,查询计算机系的学生与年龄不大于19岁的学生的差集 Select * from Student Where Sdept = CS And Sage 19;,SQL数据更新功能,Insert语句 Update语句 Delete语句,Insert语句,插入数据 Insert Into (,) Values(,) 插入子查询结果 Insert Into (,) ,Insert语句（续I）,Insert Into Student Values (95020, 陈冬, IS, 1

18、8); Insert Into SC (Sno, Cno) Values(95020, 1); Insert Into Dept_Age (Sdept, Avgage)Select Sdept, AVG(Sage) From Student Group By Sdept;,Update语句,修改数据 Update Set =, = Where ,Update语句（续I）,将学生95001的年龄改为22岁 Update Student Set Sage = 22 Where Sno=95001 将所有学生的年龄增加1岁 Update Student Set Sage=Sage+1,Update语

19、句（续II）,将计算机系全体学生的成绩置零。 Update SC Set Grade = 0 Where CS = (Select Sdept From Student Where Student.Sno = SC.Sno),Delete语句,删除数据 Delete From Where ,Delete语句（续I）,删除学号为95019的学生的记录 Delete From Student Where Sno=95019 删除所有学生的选课记录 Delete From SC,Delete语句（续II）,删除计算机系所有学生的选课记录 Delete From SC Where CS = (Sele

20、ct Sdept From Student Where Student.Sno = SC.Sno),SQL数据定义功能,定义、删除、修改基本表 定义、删除索引 定义、删除视图,SQL数据定义功能（续I）,定义、删除、修改基本表,定义基本表 Create Table ( , , ,定义、删除、修改基本表（续I）,SQL92的数据类型 char（n）：固定长度的字符串。 varchar（n）：可变长字符串。 int：整数。 smallint：小整数类型。 numeric（p，d）：定点数共p位，小数点右边q位。 Real, double precision ：浮点数与双精度浮点数,精度与机器有关。

21、 Float(n)：n位的精度浮点数。 date：日期（年、月、日）。 time：时间（小时、分、秒）。 interval：两个date或time类型数据之间的差,定义、删除、修改基本表（续II）,完整性约束 NULL/NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK,定义、删除、修改基本表（续III）,Create table student (Sno char(5) not null, Sname char(20) not null unique, Ssex char(2), Sage Int, Sdept char(15), Primary ke

22、y(Sno), Check (sage =18 and sage =45);,定义、删除、修改基本表（续IV）,Create table SC (Sno char(5) not null, Cno char(5) not null, Grade number(4,2), Primary key (Sno, Cno), Foreign key (Sno) references Student(Sno) Foreign key (Cno) references Course(Cno);,定义、删除、修改基本表（续V）,Alter Table Add() Drop Modify(),定义、删除、修改

23、基本表（续VI）,向Student表增加“入学时间”列 Alter Table Student Add (Scome Date); 将Sage的数据类型改为半字长整数 Alter Table Student Modify (Sage Smallint) 删除Student表的主键 Alter Table Student Drop Primary Key,定义、删除、修改基本表（续VII）,删除基本表 语法: Drop Table 例: Drop Table Student;,定义、删除索引,建立索引 Create UniqueCluster Index On (次序, 次序 ); 删除索引 D

24、rop Index ,定义、删除索引（续I）,Create Unique Index Stusno On Student (Sno); Create Unique Index Scno On Sc (Sno ASC, Cno DESC) Drop Index Stusno;,定义、删除视图,视图的定义 视图是由一个或几个基本表（或视图）导出的表，数据库不存储视图所对应的 数据，而仅在 数据字典中存储其定义，因此，也称视图为虚表。视图一经定义，就可以像基本表一样被查询和删除。 视图的作用 能够简化用户操作 使用户能够以多种角度看待同一数据 对重构数据提供了一定程度的逻辑独立性 能够对机密数据提供

25、安全保护,定义、删除视图（续I）,创建视图 Create View (, ) As With Check Option 删除视图 Drop View ,定义、删除视图（续II）,建立信息系的学生视图，并要求进行修改和插入操作时只能针对信息系的学生。 Create View IS_Student As Select Sno, Sname, Sage From Student Where Sdept = IS With Check Option;,定义、删除视图（续III）,建立信息系选修了1号课程的学生的视图 Create View IS_S1 (Sno, Sname, Grade) As Se

26、lect Student.Sno, Sname, Grade From Student, SC Where Sdept = IS And Student.Sno= Sc.Sno And Sc.Cno = 1;,定义、删除视图（续IV）,建立信息系选修了1号课程且成绩在90分以上的学生的视图 Create View IS_S2 As Select Sno, Sname, Grade From IS_S1 Where Grade = 90; 将学生的学号及平均成绩建立为一个视图 Create View S_G(Sno, Gavg) As Select Sno, AVG(Grade) From SC

27、 Group By Sno;,定义、删除视图（续V）,删除视图IS_S1 Drop View IS_S1; 视图消解（View Resolution） DBMS执行对视图的查询时，从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正的查询。这一转换过程称为视图消解。,定义、删除视图（续VI）,在信息系学生的视图中找出年龄小于20岁的学生。 Select Sno, Sage From IS_Student Where Sage 20; 转换后为： Select Sno, Sage From Student Where Sdept=IS An

28、d Sage 20;,嵌入式SQL,为什么使用嵌入式SQL？ 许多事务处理都是过程性的，需要根据不同的条件执行不同的任务，单纯使用SQL语言较难实现。 实际的应用系统是非常复杂的，数据库访问只是其中一个部件。有些动作如与用户交互、图形化显示数据等只能用高级语言实现。 嵌入式SQL分为：预编译方式和函数方式。,嵌入式SQL （续I）,区分SQL语句与C语言语句 嵌入的SQL语句以EXEC SQL开始，以分号(;) 结束。嵌入的SQL语句根据其内容不同又分为可执行语句和说明性语句。可执行语句又分为数据定义，数据操纵，数据控制三种。 EXEC SQL delete from Student wher

29、e Sno = 95001；,嵌入式SQL （续II）,嵌入SQL语句与C语言之间的数据传递 主变量 用来在C语句和SQL语句之间传递数据，宿主变量的声明与C变量的声明类似，但必须放在下列标识语句之间。 EXEC SQL BEGIN DECLARE SECTION int Sno; char Sname30; EXEC SQL END DECLARE SECTION,嵌入式SQL （续III）,主变量可以出现于SQL语句任意可以出现表达式的地方，但前面要加（:）以与数据库对象名（如表名、列名）相互区别。在C语言中使用时则不需要。主变量可以分为输入主变量（由应用程序赋值，SQL语句引用）和输出主变量（由SQL语句赋值，应用程序引用）。 示例：EXEC SQL select Sname, Sdept into :Sname, :Sdept :Sdept_id from Student where Sno = :Sno ;,嵌入式SQL （续IV）,指示变量 用来指示返回相关主变量的状态，指示变量的返回值包括： = 0：取到主变量的值不空，没有发生截断。 = 1：取到主变量的值为空值。 0：取到主变量的值发生了截断，指示变量的值是截断前的字符串的实际长度。 指示变量必须紧跟在SQL语句中指定的主变量