第三章

关系数据库标准语言 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 语言已成为当今最为成功和应用最广的数据库语言。

SQL 概述（ I ） 有关标准 随着 SQL 语言应用的日益广泛， ANSI 和 ISO 先

后制定了多个 SQL 标准：– SQL-86 ：较为简单，主要包括数据定义语言、数据操纵语言、嵌入式语法等几个部分。

– SQL-89 ：增加了对完整性约束的支持。– SQL-92 ：也称 SQL2 ，是 SQL-89 的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。

– SQL-3 ：正在讨论中的新的标准，将增加对面向对象模型的支持。

SQL 概述（ II ） 特点

– 综合统一集数据定义，数据操纵，数据查询和数据控制于一体。单一的结构 ---- 关系，带来了数据操作符的统一。

– 高度非过程化 用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径。

– 面向集合的操作方式操作的对象和操作的结果均为集合。

– 以同一种语法结构提供两种使用方式 SQL 既是自含式语言，又是嵌入式语言

SQL 概述（ III ）– 语言简捷，易学易用

类似于英语自然语言SQL功能 操作符数据查询 SELECT

数据定义 CREATE， DROP

数据操纵 INSERT， UPDATE， DELETE

数据控制 GRANT， REVOKE

SQL 对关系数据库模式的支持

用户

视图 1 视图 2

SQL

基本表 1 基本表 2 基本表 3 基本表 4

存储文件 1 存储文件 2

外模式

内模式

概念模式

SQL 对关系数据库模式的支持 基本表

– 是本身独立存在的表，一个（或多个）基本表对应一个存储文件

存储文件– 由数据块构成的存储空间，用于存储基本表、索引等。其逻辑结构组成了关系数据库的内模式

视图– 是从一个或几个基本表中导出的表，其本身不独立存储于数据库中

例子表

Sno Sname Sex Sage Sdept

95001 李勇 男 20 CS

95002 刘晨 女 19 IS

95003 王敏 女 18 MA

95004 张立 男 19 IS

STUDENT

Sno Cno Grade

95001 1 92

95001 2 85

95001 3 88

95002 2 90

95002 3 80

SC

Cno Cname Cpno Ccredit

1 数据库 5 4

2 数学 2

3 信息系统 1 4

4 操作系统 6 3

5 数据结构 7 4

6 数据处理 2

7 Pascal 语言 6 4

COURSE

SQL 数据查询功能 查询语句 SELECT 的语法：Select [ALL|DISTINCT]< 目标表达式 >[{,< 目标表达式 >}]

From < 表名或视图名 >[{,< 表名或视图名 >}]

[Where < 条件表达式 >]

[Group By < 分组列 > [{,< 分组列 >}][HAVING < 条件表达式>]]

[Order By < 排序列 > < 排序方式 >[{,< 排序列 > < 排序方式>}]

SQL 数据查询功能 其语义是：从 FROM 子句给定的表中，检索出满

足 WHERE 子句给定检索条件的记录，并根据 SELECT 子句从这些记录中选出指定的列（属性）所构成的结果集合。 SELECT 和 FROM 两个子句定义了投影操作。 WHERE 子句给出了选取操作的定义（当涉及多表操作时， WHERE 子句要同时给出连接操作条件）。可见，查询语句作为整体代表着关系代数中的投影、选取、连接等操作的组合。但是，由于在查询语句中并不规定投影、选取、连接等操作的执行顺序。因此，它比关系代数更一般，更少过程化。

SQL 数据查询功能 单表查询 连接查询 嵌套查询 集合查询

单表查询 查询全体学生的姓名与学号

– Select Sname, Sno From Student;

查询全体学生的全部属性– Select * From Student;

查看全体学生的姓名及其出生年份– Select Sname, 2001-Sage From Student;

单表查询（续 I ） 查询全体学生所在学校、姓名、出生年份和所在系（用小写字母显示系名）。– Select “北航” , Sname, 2001-Sage, ISLOW

ER(Sdept)

From Student;– Select “北航” School, Sname, 2

001-Sage Birth, ISLOWER(Sdept)

From Student;

单表查询（续 II ）查询条件 谓词

比 较 >, >=, <, <=, =, !=, <>, !>, !<;

NOT + 上述比较运算符确定范围 BETWEEN AND,

NOT BETWEEN AND

确定集合 IN, NOT IN

字符匹配 LIKE, NOT LIKE

空 值 IS NULL, IS NOT NULL

多重条件 AND, OR

单表查询（续 III ） 查询选修了课程的学生学号

– Select Distinct Sno From STUDENT; 查询计算机系全体学生的姓名

– Select Sname From Student where Sdept=‘CS’;

查询所有年龄在 20岁以下的学生姓名及其年龄– Select Sname From Student

where Sage<20;

单表查询（续 IV ） 查询年龄在 20~23 （含 20 和 23岁）之间的学生姓名、性别和年龄。– Select Sname, Ssex, Sage From Student W

here Sage BETWEEN 20 AND 23;

查询数学系和计算机系的学生的姓名和性别。– Select Sname, Ssex From Student Wher

e Sdept IN (‘MA’, ‘CS’);

单表查询（续 V ） 查询所有姓刘的学生的学号、姓名

– Select Sno, Sname From Student Where Sname Like ‘ 刘 %’

查询所有姓刘，并且名字为两个汉字的学生的学号、姓名– Select Sno, Sname From Student Wher

e Sname Like ‘ 刘 __’

单表查询（续 VI ） 查询所选课程没有成绩的学生的学号，所选课程号。– Select Sno, Cno From SC Wher

e Grade IS NULL

单表查询（续 VII ） ORDER BY 子句可以将查询结果按照一个或多个属性列的升序（ ASC ）或降序（ DESC ）排序。

查询全体学生的情况，并按所在系的升序和年龄的降序排列– Select * From Student Orde

r By Sdept Asc, Sage Desc

单表查询（续 VIII ）聚集函数

– Count() 求元组个数– Sum() 对数值列求总和– Avg() 求数值列的平均值– Max() 求最大值– Min() 求最小值

单表查询（续 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 Sno, Count(Sno) From SC

Group By Sno Having Count(Sno)>=3;

连接查询 连接条件

– 连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：

[< 表名 > .] < 列名 > <比较运算符 > [< 表名 > .] < 列名>

– 其中的 < 列名 > 称为连接字段， 连接条件的各个连接字段类型必须是可比的，但不必是相同的。 <比较运算符 > 主要有： = ， > ， < ， >= ，<= ， != 。当连接运算符为等号时，称为等值连接，否则称非等值连接。

连接查询（续 I ） 查询每个学生及其所选课程的情况

– Select Student.*, SC.* From Student, SC Where Student.Sno = SC.Sno;

查询每一门课的间接先修课（即先修课的先修课）– Select FIRST.Cno, SECOND.Cpno

From Course FIRST, Course SECOND Where FIRST. Cpno = SECOND. Cno

查询所有学生的学号、姓名、所选修课程的课程号和成绩– Select Student.Sno, Sname, Cno, Grade Fro

m Student, SC Where Student.Sno=SC.Sno(*)

嵌套查询 在 SQL 语言中，一个 SELECT-FROM-WHERE 称为

一个查询块，将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING短语的条件中的查询称为嵌套查询。上层的查询块称为外层查询或父查询，下层查询称为内层查询或子查询。外层查询利用子查询来获取检索条件的条件值，并根据这个条件值确定外层查询的结果集合。通过子查询，可以利用一系列简单查询来构造一个复杂查询。

子查询中的 SELECT 语句中不能使用 ORDER BY 子句， ORDER BY 子句只能对最终查询结果排序。

嵌套查询（续 I ） 不相关子查询

– 不相关子查询的执行顺序是由内向外，即每个子查询在上一级查询处理前求解，子查询的结果集合作为其外部查询的检索条件的条件值。

相关子查询– 相关子查询的查询条件引用了其外部查询的某个属性值，从而其执行依赖于其外部查询。其执行顺序是：首先取外部查询选取一行记录（称为候选行），然后子查询利用候选行中相关列的值查询结果数据。接着外部查询利用子查询返回的结果集合判断候选行是否满足检索条件，若满足，则把它放入外部查询的结果集合。重复上述步骤直到处理完外部查询的每一行候选行。

嵌套查询（续 II ） 查询与刘晨在同一个系学习的学生。

– Select Sno, Sname, Sdept

From Student

Where Sdept =

(Select Sdept

From Student

Where Sname = ‘ 刘晨’ );

嵌套查询（续 III ） 查询选修了课程名为‘信息系统’的学生的学号和姓名。Select Sno, Sname From Student Where Sno IN (Select 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 <> ‘IS’ And

Sage < All(Select Sage From Student

Where Sdept = ‘IS’)

嵌套查询（续 VI ） 查询选择了 1号课程的学生姓名。

Select Sname From StudentWhere Exists (Select * from SC Where Sno=Student.Sno And Cno=‘1’)

查询没有选择 1号课程的学生姓名。Select Sname From StudentWhere Not Exists (Select * from SC Where Sno=Student.Sno And Cno=‘1’)

嵌套查询（续 VII ） 查询选修了全部课程的学生姓名

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

Where 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’, 18);

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 语句（续 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’ = (Select Sdept From Student

Where Student.Sno = SC.Sno)

SQL 数据定义功能 定义、删除、修改基本表 定义、删除索引 定义、删除视图

SQL 数据定义功能（续 I ）

操作对象操作方式

创建 删除 修改

表 Create Table Drop Table Alter Table视图 Create View Drop View索引 Create Index Drop Index

定义、删除、修改基本表 定义基本表

Create Table < 表名 >

(< 列名 >< 数据类型 >[< 列级完整性约束 >]

[{,< 列名 >< 数据类型 >[< 列级完整性约束>]}]

[{, [< 表级完整性约束 >]}]

定义、删除、修改基本表（续 I ） SQL92 的数据类型

– char （ n ）：固定长度的字符串。– varchar （ n ）：可变长字符串。– int ：整数。– smallint ：小整数类型。– numeric （ p ， d ）：定点数共 p位，小数点右边 q位。– Real, double precision ：浮点数与双精度浮点数 ,精度与机器

有关。– 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 key(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(< 列名 >< 数据类型 >)

定义、删除、修改基本表（续 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 [Unique][Cluster] Index < 索引名 >

On < 表名 >

(< 列名 >[次序 ][, < 列名 >[次序 ]] …);

删除索引Drop Index < 索引名 >

定义、删除索引（续 I ）Create Unique Index Stusno

On Student (Sno);

Create Unique Index Scno

On Sc (Sno ASC, Cno DESC)

Drop Index Stusno;

定义、删除视图 视图的定义

– 视图是由一个或几个基本表（或视图）导出的表，数据库不存储视图所对应的 数据，而仅在 数据字典中存储其定义，因此，也称视图为虚表。视图一经定义，就可以像基本表一样被查询和删除。

视图的作用– 能够简化用户操作– 使用户能够以多种角度看待同一数据– 对重构数据提供了一定程度的逻辑独立性– 能够对机密数据提供安全保护

定义、删除视图（续 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

Select 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

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’ And Sage <20;

嵌入式 SQL

为什么使用嵌入式 SQL？– 许多事务处理都是过程性的，需要根据不同的条件执行不同的任务，单纯使用 SQL 语言较难实现。

– 实际的应用系统是非常复杂的，数据库访问只是其中一个部件。有些动作如与用户交互、图形化显示数据等只能用高级语言实现。

嵌入式 SQL 分为：预编译方式和函数方式。

嵌入式 SQL （续 I ） 区分 SQL 语句与 C 语言语句

嵌入的 SQL 语句以 EXEC SQL开始，以分号 (;) 结束。嵌入的 SQL 语句根据其内容不同又分为可执行语句和说明性语句。可执行语句又分为数据定义，数据操纵，数据控制三种。EXEC SQL delete from Student

where Sno = ‘95001’；

嵌入式 SQL （续 II ） 嵌入 SQL 语句与 C 语言之间的数据传递

– 主变量用来在 C 语句和 SQL 语句之间传递数据，宿主变量的声明与 C变量的声明类似，但必须放在下列标识语句之间。EXEC SQL BEGIN DECLARE SECTION

int Sno;

char Sname[30];

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 语句中指定的主变量之后，前面也要加（ : ）以与数据库对象名（如表名、列名）相互区别。在 C 语言中使用时则不需要。

嵌入式 SQL （续 V ） SQL与主语言之间操作方式的协调

– SQL ：一次一集合。 C 语言：一次一记录。

–游标：是系统为用户查询开辟的一个缓冲区，存放 SQL 语句的执行结果，每个游标都有一个名字，用户可以通过游标逐一获取记录，，并赋给主变量，由主语言进行处理

嵌入式 SQL （续 VI ）定义与使用游标的语句

• declare ：定义一个游标，使之对应一个 select 语句。declare 游标名 cursor for select 语句

[for update of < 列名 >]

for update任选项，表示该游标可用于对当前行的指定列的修改与删除。

• open ：打开一个游标，执行游标对应的查询，结果集合为该游标的活动集。

open 游标名

嵌入式 SQL （续）

• fetch ：在活动集中将游标移到特定的行，并取出该行数据放到相应的宿主变量中。

fetch 游标名 into [ 主变量表 ]

• close ：关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行 open语句。

close 游标名

嵌入式 SQL （续 X ） SQL 语句执行信息反馈

– 良好的应用程序必须提供对错误的处理 ,应用程序需要知道SQL语句是否正确执行了，发生错误时的错误代码，执行时遇到特殊情况时的警告信息。

– SQL通讯域SQLCA是一结构 ,每一嵌入 SQL语句的执行情况在其执行完成后写入 SQLCA结构中的各变量中 , 根据 SQLCA中的内容可以获得每一嵌入 SQL语句执行后的信息 ,应用程序就可以做相应的处理。

– 为了说明 (SQLCA),必须在应用程序中包括 :EXEC SQL INCLUDE SQLCA;

嵌入式 SQL （续 XI ） 连接数据库

– EXEC SQL CONNECT :username IDENTIFIED BY :password;

结束工作– EXEC SQL COMMIT WORK [RELEASE]– EXEC SQL ROLLBACK WORK [RELEAS

E]

动态 SQL

如果在预编译时下列信息不能确定，就必须使用动态 SQL技术，如： SQL 语句正文，主变量个数，主变量的数据类型， SQL 语句中引用的数据库对象等。

动态 SQL允许在程序运行过程中“临时”组装 SQL 语句，主要有三种形式：– 语句可变– 条件可变– 数据库对象，查询条件均可变