Unified Modeling Language引言 SQL是关系数据库的标准语言...

第3章关系数据库语言SQL

内容提纲

1）SQL简介

SQL数据库的体系结构

SQL的组成

2）SQL的数据定义

SQL模式、基本表和索引的创建和撤销

3）SQL的数据查询

SELECT语句的句法

SELECT语句的三种形式及各种限定

基本表的连接操作

2

内容提纲

4）SQL的数据更新

插入、删除和修改语句

5）视图

创建和撤消

视图的更新限制

6）嵌入式SQL（自学）

7）存储过程与SQL/PLM（自学）

3

引言

SQL是关系数据库的标准语言

对关系模型的发展和商用DBMS的研制起着重要的作用

SQL语言是介乎于关系代数和元组演算之间的一种语言

SQL语言的9个核心词汇

Create, Alter, Drop, Select, Insert, Update, Delete

Grant, Revoke

本章详细介绍SQL的核心部分内容

数据定义

数据查询

数据更新

4

SQL简介

SQL发展史

1970年

美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型

1972年

IBM公司开始研制实验型关系数据库管理系统SYSTEM R，配制的查询语言称为SQUARE语言

1974年

把SQUARE修改为SEQUEL语言

1978年，

SEQUEL简称为SQL，即“结构式查询语言”

5

SQL简介

SQL发展史

SQL86

1986年10月，ANSI SQL标准

1987年6月，ISO SQL标准

SQL2

1992年8月，ISO SQL标准

SQL3

1999年，ISO SQL标准

在未来很长一段时间，SQL仍将是关系数据库领域的主流语言

在软件工程、人工智能领域，SQL已显示出相当大的潜力

6

SQL简介


用户1 用户2 用户3 用户4

视图1 视图2

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

存储文件1 存储文件2 存储文件3 存储文件4

SQL用户

View

Base table

Stored file

7

SQL简介


1）一个SQL模式（Schema）是表和约束的集合

2）一个表由行集构成，一行是列的序列

3）表类型：基本表、视图和导出表

4）基本表与存储文件是M:N联系

存储文件与外部存储器的物理文件是一一对应的

5）SQL语句执行对基本表和视图查询等操作

在用户看来，两者是一样的，都是表

6）SQL用户可以是应用程序，也可以是终端用户

8

SQL简介

SQL组成

1）数据定义语言，即SQL DDL

用于定义SQL模式、基本表、视图、索引等结构

2）数据操纵语言，即SQL DML

数据查询

数据更新：包括插入、删除和修改三种操作

3）嵌入式SQL语言规则

SQL语句嵌入在宿主语言程序中的规则

4）数据控制语言，即SQL DCL

授权、完整性规则的描述、事务控制等

9

SQL简介

SQL特点

1）灵活和强大的查询功能

SELECT语句能完成相当复杂的查询操作

2）SQL不是一个应用开发语言

只提供对数据库的操作功能，不能完成屏幕控制、菜单管理、报表生成等功能

3）SQL是国际标准语言

有利于各种数据库之间交换数据，有利于程序的移植，有利于实现高度的数据独立性，有利于实现标准化

4）SQL的词汇不多

完成核心功能只用了9个英语动词，它的语法结构接近英语，因此容易学习和使用

10

SQL的数据定义

SQL模式的创建和撤销

注

模式（Schema）一词，来自于“ISO SQL标准”协议

Schema在SQL数据库中相当于一个容器

数据库所有的对象如表、视图、索引、用户、存储过程、触发器等都位于容器内

创建SQL模式，就是定义一个存储空间

在商业DBMS中，大多都使用Database代替Schema

11

SQL的数据定义

SQL模式的创建和撤销

创建

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>

撤销

DROP SCHEMA <模式名> ［CASCADE│RESTRICT］

方式

CASCADE（级联式）

RESTRICT（约束式）

商业DBMS中

CREATE Database … …

DROP Database … …

12

SQL的数据定义

常用数据类型

1）数值型：Integer，Smallint，Numeric(p, d) / DEC(p,d)

2）字符串型：Char(n)，Varchar(n)

3）位串型：Bit(n)，Bit Varying(n)

4）时间型：DATE，TIME, DATETIME

注

在很多DBMS中支持自定义数据类型，如在Oracle中

CREATE DOMAIN <域名>

[AS] <数据类型> [列级完整性约束] ;

DROP DOMAIN <标识符> ;

13

SQL的数据定义

常用数据类型

示例：在Oracle中定义数据类型与应用

定义

Create domain COLOR Char(6) Default ’???’

Constraint VALID_COLORS

Check ( value in

(‘Red’, ’Yellow’, ’Green’, ’???’ ) ) ;

使用

Create Table PART (

… …

partcolor COLOR ,

… … ) ;14

SQL的数据定义

基本表的创建、修改和撤销

表的创建

句法

CREATE TABLE <表名> (

<列名> <类型> [列级完整性约束条件]

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

[ ,<表级完整性约束条件> ] ) ;

如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

列级完整性约束条件

NOT NULL / [NULL] ：列值是否可以为空

UNIQUE ：列值唯一，不得重复

DEFAULT ：列值空缺时，由系统填写默认值15

SQL的数据定义


表的创建

表级完整性约束条件

主键（PRIMARY KEY）子句

格式：PRIMARY KEY ( <列名表> )

作用：提供实体完整性约束的说明

说明

系统一般自动在主键上建索引

主键为单属性时，可直接在属性后的列级完整性约束条件中，使用PRIMARY KEY定义主键

16

SQL的数据定义


表的创建


外键（FOREIGN KEY）子句

格式：Foreign key [外键名] ( <列名表1> )

references <主表名> [ (列名表2) ]

[ on delete < restrict｜cascade｜set null > ]

作用：提供参照完整性约束的说明

17

SQL的数据定义


表的创建


外键（FOREIGN KEY）子句

on delete < restrict｜cascade｜set null > 子句的说明

RESTRICT ：主表的主键值行不得删除

CASCADE ：主表主键值行删除，从表的相关行随之删除

SET NULL ：主表主键值行删除，从表的相关行的列值设为NULL，前提是该列的值可以为NULL

18

SQL的数据定义


表的创建


检查（CHECK）子句

格式：CHECK (约束表达式)

作用：对某元组某属性取值的约束说明

说明

表创建后是一个空表，需要使用DML（insert，update，delete）语句装入或维护数据行

19

SQL的数据定义


表的创建

示例 S（SNO，SNAME，AGE，SEX，NativePlace）

字段含义：学号，姓名，年龄，性别，籍贯

T（TNO，TNAME，TITLE，SEX）

字段含义：教师号，教师姓名，职称，性别

Create Table S (

SNO CHAR(3) ,

SNAME CHAR(8) NOT NULL,

AGE Integer NULL,

SEX CHAR(1) DEFAULT ‘M’,

NativePlace VARCHAR(20) ,

Primary Key (SNO) ,

Check (SEX IN (‘M’, ‘F’) ) ) ;

Create Table T (

TNO CHAR(3) Primary Key ,

TNAME CHAR(8) UNIQUE ,

TITLE CHAR(6) NOT NULL,

SEX CHAR(1) DEFAULT ‘M’ ,

Check (TITLE IN ( ‘教授’, ‘副教授’,

‘讲师’, ‘助教’ ) ) ) ;

SNO SNAME AGE SEX NativePlace

S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

20

SQL的数据定义


表的创建

示例 C（CNO，CNAME，Credit，CreditHours，CPNO，TNO）

字段含义：课程号，课程名，学分，学时数，先修课号，授课教师号

CNO CNAME Credit CreditHours CPNO TNO

C1 Math 3 48 NULL T1

C2 English 4 64 NULL T2

C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

★

★

★

★

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

★

Create Table C (

CNO CHAR(3) Primary Key ,

CNAME CHAR(8) , Credit Integer ,

CreditHours Integer ,

CPNO CHAR(3) NULL ,

TNO CHAR(3) NOT NULL ,

Foreign Key (CPNO) References C(CNO) ON DELETE SET NULL ,

Foreign Key (TNO) References T ON DELETE SET NULL ) ;

?

?

21

SQL的数据定义


表的创建

示例 SC（SNO，CNO，Grade）

字段含义：学号，课程号，成绩


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

★




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

★

★

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

★

★

★

★

★

Create Table SC (

SNO CHAR(3) , CNO CHAR(3) ,

GRADE DEC(5, 2) ,

Primary Key (SNO, CNO) ,

Foreign Key (SNO) References S(SNO) ON DELETE restrict ,

Foreign Key (CNO) References C ON DELETE cascade ,

Check ( GRADE Between 0 And 100) ) ;

?

?

22

SQL的数据定义


表的创建

问关于表T、S、C、SC的创建顺序，下列选项正确的是？

A） C → T → SC → S

B） T → S → C → SC

C） S → T → C → SC

D） S → T → SC → C

E） T → C → S → SC

F） SC → C → S → T

G） SC → C → T → S

?

23

SQL的数据定义


表的修改

句法

ALTER TABLE <表名> <修改动作… …>

增加属性

Alter table <表名> add <属性名> <类型> ；

删除属性

Alter table <表名> drop <属性名> [cascade｜restrict]

CASCADE：引用该列的视图和约束随之删除

RESTRICT：只在没有被引用时才可删除

不是所有的DBMS都支持

24

SQL的数据定义


表的修改

句法

ALTER TABLE <表名> <修改动作… …>

修改属性的类型

Alter table <表名> modify <列名> <类型>

modify → alter column

其他修改，如

补充定义主键，撤销主键定义

补充定义外键，撤销外键定义

定义和撤销别名

25

修改基本表示例：

[例1]向S表增加“入学时间”列，其数据类型为日期型。

ALTER TABLE S ADD S_entrance DATE；

不论基本表中原来是否已有数据，新增加的列一律为空值。

[例2]将年龄的数据类型由整数（假设原来的数据类型是整数）改为字符型。

ALTER TABLE S ALTER COLUMN Sage char(4)；

[例3]增加课程名称必须取唯一值的约束条件。

ALTER TABLE C ADD UNIQUE(Cname);

SQL的数据定义


表的撤销

句法

DROP TABLE <表名> [cascade｜restrict]

说明

CASCADE：删除该基本表时，所有引用该基本表的视图和约束一起自动被删除

RESTRICT：没有视图和约束引用该基本表时，才能撤销

27

谁可以建立索引 DBA 或表的属主（即建立表的人）

DBMS一般会自动建立以下列上的索引

PRIMARY KEY

UNIQUE

谁维护索引DBMS自动完成

使用索引DBMS自动选择是否使用索引以及使用哪些索引

SQL的数据定义索引概念

是物理存取路径，不属于逻辑数据模式

RDBMS通常在主键上自动建立索引

查询、更新时自动起作用（适当建立索引会提高查询速度）

创建索引

CREATE [UNIQUE] INDEX <索引名>

ON 基表名 ( <列名> [ASC ｜DESC]

[ { , <列名> [ASC ｜DESC] } ] ) ;

撤销索引DROP INDEX <索引名列表> ;

UNIQUE索引，每个索引键值对应唯一的数据记录

建立索引

[例1]为学生-课程数据库中的Student，Course，SC三个表建立索引。

CREATE UNIQUE INDEX Stusno ON Student(Sno)；

CREATE UNIQUE INDEX Coucno ON Course(Cno)；

CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)；

Student表按学号升序建唯一索引

Course表按课程号升序建唯一索引

SC表按学号升序和课程号降序建唯一索引

删除索引

DROP INDEX <索引名>；

删除索引时，系统会从数据字典中删去有关该索引的

描述。

[例] 删除Student表的Stusname索引

DROP INDEX Stusname；

SQL的数据查询

SELECT语句的基本结构

句型

SELECT <目标表的列名或列表达式序列>

FROM < 表名、视图名或导出表序列>

WHERE <行条件表达式>

只有SELECT和FROM子句是每个SQL查询语句所必需的

语义

示例

查询选修了DB课程的学生的姓名

SELECT A1, … , An

FROM R1, … , Rn

WHERE F ;

ΠA1, … , An(σF(R1× … ×Rn) )

32

SQL的数据查询


示例

ΠSNAME(σCNAME=‘DB‘ AND S.SNO=SC.SNO AND SC.CNO=C.CNO(SSCC))

Select Sname From S, SC, C

Where Cname=‘DB’

And S.Sno=SC.Sno

And SC.Cno=C.Cno

S.SNO SNAME … C.CNO CNAME … SC.SNO SC.CNO GRADE

… … … … … … … … …

… … … … … … … … …

S C SC


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

S




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

C

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

SC

1000行

1000行

10000行

C（CNO，CNAME，Credit，CreditHours，CPNO，TNO）

S（SNO，SNAME，AGE，SEX，NativePlace）


SC（SNO，CNO，Grade）

33

SQL的数据查询


示例

ΠSNAME(σCNAME=‘DB‘(S⋈SC⋈C))

DBMS的优化

ΠSNAME(ΠSNO,SNAME(S)⋈ΠSNO(ΠCNO(σCNAME=‘DB‘(C))⋈ΠSNO,CNO(SC)))

Select Sname From S

Where Sno in ( Select Sno From SC

Where Cno in ( Select Cno From C

Where

Cname=‘DB’ )) ;

ΠCNO(σCNAME=‘DB‘ (C)) ΠSNO,CNO(SC)

⋈

ΠSNO,SNAME (S) ΠSNO( )

⋈

ΠSNAME ( )

1000→1 10000

<= 10001000<= 1000

<= 1000

<= 1000

34

SQL的数据查询


常见的3种SELECT语句写法

示例：查询选修‘C2’课程的学生学号和姓名

1）连接查询

Select S.Sno, Sname

From S, SC

Where S.Sno=SC.Sno and Cno=‘C2’

语句的执行步骤

1）笛卡尔积

2）等值连接

2）选择和投影


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 NULL

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 NULL

S4 C4 75

35

SQL的数据查询




2）IN嵌套查询

Select Sno, Sname From S Where Sno in

( Select Sno From SC Where Cno=‘C2’ ) ;

Select Sno, Sname From S Where ‘C2’ in

( Select Cno From SC Where Sno=S.Sno ) ;

区别

1）由里到外，内层查询（执行1次）结果供给外层使用

2）依赖于外层值，内层（执行n次）结果供给外层使用


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 NULL

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 NULL

S4 C4 75

?

36

嵌套查询

不相关子查询：

子查询的查询条件不依赖于父查询

由里向外逐层处理。即每个子查询在上一级查询处理之前求解

，子查询的结果用于建立其父查询的查找条件。

嵌套查询

相关子查询：子查询的查询条件依赖于父查询

首先取外层查询中表的第一个元组，根据它与内层查询相关的

属性值处理内层查询，若WHERE子句返回值为真，则取此元

组放入结果表

然后再取外层表的下一个元组

重复这一过程，直至外层表全部检查完为止

SQL的数据查询




3）EXISTS嵌套查询

Select Sno, Sname From S

Where EXISTS ( Select * From SC

Where Sno=S.Sno and Cno=‘C2’ ) ;

说明

EXISTS表示“”，判定内层查询结果是否为非空，

即至少存在一个元组（满足外查询的条件）


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 NULL

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 NULL

S4 C4 75

39

EXISTS谓词的子查询

EXISTS谓词

存在量词

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则外层的WHERE子句返回真值

若内层查询结果为空，则外层的WHERE子句返回假值

由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义

NOT EXISTS谓词若内层查询结果非空，则外层的WHERE子句返回假值

若内层查询结果为空，则外层的WHERE子句返回真值

SQL的数据查询


基本句型的综合应用

1）检索学习课程号为C2课程的学生学号与成绩

ΠSNO，Grade（σCNO = 'C2'（SC））

Select Sno, Grade

From SC

Where Cno=‘C2’ ;

2）检索学习课程号为C2的学生学号与姓名

ΠSNO，SNAME（σCNO = 'C2'（S ⋈ SC））

略





41

SQL的数据查询



3）检索至少选修LIU所授课程一门课程的学生学号与姓名

ΠSNO，SNAME（σTNAME = 'LIU'（S ⋈ SC ⋈ C ⋈ T））

Select S.Sno, Sname From S, SC, C, T

Where Tname=‘LIU’ and S.Sno= SC.Sno

and SC.Cno=C.Cno and C.TNO=T.Tno ;

Select Sno, Sname From S Where Sno in (

Select Sno From SC Where Cno in (

Select Cno From C Where Tno in (

Select Tno From T Where

Tname=‘LIU’ ) ) ) ;





42

SQL的数据查询



4）检索选修课程号为C2或C4的学生学号

ΠSNO（σCNO = 'C2‘ ∨ CNO = 'C4'（SC））

Select Sno From SC

Where Cno = ‘C2’ or Cno = ‘C4’ ;

Select Sno From SC

Where Cno in (‘C2’, ‘C4’) ;

Select Sno From SC

Where Cno =SOME(‘C2’, ‘C4’) ;





43

SQL的数据查询



5）检索至少选修课程号为C2和C4的学生学号

Π1（σ1 = 4 ∧ 2 = 'C2‘ ∧ 5 = 'C4'（SC×SC）） Select X.Sno

From SC as X, SC as Y

Where X.Sno=Y.Sno

and X.Cno=‘C2’

and Y.Cno=‘C4’ ;

SELECT Sno

FROM SC

WHERE Cno=' C2 ' AND Sno IN

(SELECT Sno FROM SC WHERE Cno=' C4 ')；





FROM子句中，可以用 as为表和视图取一别名，这种别名只在本句中有效,as可不写

44

学号课程号成绩

984101 C4 85

984101 C2 90

984102 C2 85

984103…

C4…

90…


984101 C4 85

984101 C4 85

984101 C4 85

984101…

C4…

85…


984101 C4 85

984101 C2 90

984102 C2 85

984103 C4 90


984101 C4 85

984101 C2 90

984102 C2 85

984103 C4 90

SC SC

SC×SC

思考：至少选了2门课的学生学号？至少有两个人选的课程号？

45

SQL的数据查询



6）检索不学C2课的学生姓名与年龄

ΠSNAME，AGE(S)－ ΠSNAME，AGE(σCNO = 'C2‘(S ⋈ SC))

不能用连接查询写

Select Sname, Age From S

Where Sno not in (

Select Sno From SC Where Cno=‘C2’ ) ;

Select Sname, Age From S Where not Exists (

Select * From SC

Where Sno = S.Sno and Cno=‘C2’ ) ;





46

SQL的数据查询



7）检索学习全部课程的学生姓名

ΠSNAME（S ⋈（ΠSNO，CNO（SC）÷ ΠCNO（C）））

Select Sname From S

Where not Exists (

Select * From C

Where not Exists (

Select * From SC

Where Sno=S.Sno and Cno=C.Cno) );





47

SQL的数据查询




理解

逐行扫描S表中的每条记录，对每一个Sno，都顺序扫描C表，读取全部的Cno，形成（Sno，Cno）

对于每一个Sno的（Sno，Cno）全集，都在SC表中查找有没有与之对应的行

若一个Sno的（Sno，Cno）全集，都在SC表中找到对应关系，则这个Sno的学生就选修了全部的课程

S1,C2

S1,Cn

S C SC S1 C1

.

.

.

Cn

S1,C1

… … …





48

SQL的数据查询




理解

在SQL中没有全称量词，涉及到“全部”的问题只能通过“( x)P ≡ ┐( x(┐P))”的转化来解决，即：

查询“没有一门课程是他没选修的”的学生姓名

SQL规定：子查询的where句可以调用上面任意层次的主查询中关系的属性





49

SQL的数据查询



8）检索所学课程包含学生S3所学课程的学生学号

ΠSNO，CNO（SC）÷ ΠCNO（σSNO = 'S3‘（SC））

Select Distinct Sno From SC as X

Where not Exists (

Select * From SC as Y

Where Y.Sno=‘S3’ and not Exists (

Select * From SC as Z

Where Sno=X.Sno and Cno=Y.Cno) );





52

SQL的数据查询



8）检索所学课程包含学生S3所学课程的学生学号

理解

“包含学生S3所学课程”是“全部”的一个限定条件

思考：查询选修了‘LIU’老师所授全部课程的学生学号？

S1,C2

S1,Cn

SC As X

S1 C1←S3 . . .

Cn←S3

S1,C1

… … …

SC As Y

SC As Z





53

SQL的数据查询

SELECT语句的完整结构

句型

执行过程

① 读取FROM子句中的数据源，执行笛卡尔积（×） ② 选择（σ）满足WHERE子句值的元组 ③ 按GROUP BY子句指定的列值，将元组进行分组 ④ 在③分出的所有组中，选取满足HAVING子句值的组 ⑤ 按SELECT子句的指定项，求值投影（Π）输出 ⑥ 按ORDER BY子句对⑤式的输出进行排序

SELECT <目标表的列名或列表达式序列>

FROM < 表名、视图名或导出表序列>

［WHERE <行条件表达式>］

［GROUP BY <列名序列>

［HAVING <组条件表达式>］］

［ORDER BY <列名［ASC│DESC］>, … …］

⑤

①

②

③

④

⑥

54

SQL的数据查询


查询中的5个聚合函数

函数释义

COUNT ( * ) ：统计元组的个数 COUNT

COUNT ( <列名>) ：对指定列，统计列值个数

SUM SUM ( <列名>) ：对数值型列，求和

AVG AVG ( <列名>) ：对数值型列，求平均值

MAX MAX ( <列名>) ：对指定列，求最大值

MIN MIN ( <列名>) ：对指定列，求最小值

55

SQL的数据查询


应用示例

统计选修课程的人次数

Select COUNT(Sno) as 人次数

From SC ;

统计选修课程的人数

Select COUNT(Distinct Sno) as 人数

From SC ;

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75





56

SQL的数据查询


应用示例

统计男学生的总人数和平均年龄

Select COUNT(*) as 总人数,

AVG(Age) as 平均年龄From S

Where Sex = 'M' ;

按性别统计学生的总人数和平均年龄

Select Sex, COUNT(*) as 总人数,

AVG(Age) as 平均年龄From S

Group By Sex ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川





57

SQL的数据查询


应用示例

统计每门课程的学生选修人数，要求显示课程号、课程名和学生人数

Select C.Cno, Cname, COUNT(Sno) as 学生人数From C, SC

Where C.Cno = SC.Cno

Group By C.Cno, Cname ;




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75





58

SQL的数据查询


应用示例

按教师号统计每位教师每门课程的学生选修人数，要求：

1）仅显示选修人数在3人（>=3）以上的信息

2）显示TNO、CNO和选修人数

3）显示时，查询结果按选修人数降序排列，人数相同按TNO升序、 CNO降序排列





59

SQL的数据查询


应用示例

Select Tno, C.Cno, COUNT(Sno) as 选修人数From C, SC

Where C.Cno = SC.Cno

Group By Tno, C.Cno

Having COUNT(*)>=3

Order By 3 DESC, Tno, C.Cno DESC




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75





60

SQL的数据查询


［GROUP BY <列名序列>［HAVING <组条件表达式>］］

作用

1）数据按GROUP BY子句列名序列中的列值进行分组 2）组内数据按SELECT子句中的聚合函数进行计算 3）提取满足HAVING子句的条件表达式值的分组

注意

HAVING子句支持聚合函数

WHERE子句不支持聚合函数

SELECT子句只能取聚合函数或GROUP BY子句指的列

Select {列名表 1} , 聚合函数

… …

Group By {列名表 2}

… … ;

{列名表 1} {列名表 2}

61

GROUP BY子句

GROUP BY子句分组：

细化聚合函数的作用对象

未对查询结果分组，聚合函数将作用于整个查询结果

对查询结果分组后，聚合函数将分别作用于每个组

作用对象是查询的中间结果表

按指定的一列或多列值分组，值相等的为一组

GROUP BY子句（续）

HAVING短语与WHERE子句的区别：

作用对象不同

WHERE子句作用于基表或视图，从中选择满足条

件的元组

HAVING短语作用于组，从中选择满足条件的组。

SQL的数据查询


［ORDER BY <列名︱列序号>［ASC︱DESC］

［｛, <列名︱列序号>［ASC︱DESC］｝］

对查询结果按子句中指定列的值排序，如果ORDER BY后有多个列名

先按第一列名值排序

再对于第一列值相同的行，按第二列名值排序

依次类推… …

列序号是在SELECT子句中出现的序号（选的列是聚集函数或表达式时）

ASC表示升序，DESC表示降序，缺省时表示升序

64

SQL的数据查询


应用示例

问：在使用聚合函数进行数据统计时，为什么有的语句使用了Group By子句，有的没有？

什么情况下，应该有？可以没有？

65

SQL的数据查询

查询中的限制和规定

Select子句的规定

子句描述查询输出的表格结构，即输出值的列名或表达式

格式

SELECT［ALL︱DISTINCT］<列名或列表达式序列>︱ *

释义

All︱Distinct，［保留︱消除］查询结果中的重复行

* 是对From子句中表的所有列的简写

列表达式

列名、常量、算数运算符、函数、聚合函数

66

SQL的数据查询


别名

用关键字AS为列、表、表达式起别名，AS可以省略

集合运算

(Select查询1) UNION［ALL］(Select查询2)

(Select查询1) INTERSECT［ALL］(Select查询2)

(Select查询1) EXCEPT［ALL］(Select查询2)

备注

并交差运算的前提：相同的关系模式

关键字ALL，表示保留运算后的重复元组

67

SQL的数据查询


示例

Select NativePlace, Sname, Sex

From S ;

Select Sno, Sname, Age, Sex, NativePlace

From S ;

Select *

From S ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

68

SQL的数据查询


示例

Select Sname, Year(getdate()) – Age as BirthYear

From S AS X ;

Select Sname as 姓名, ‘BirthYear :’ as BIRTH,

2011 – Age as YEAY, Lower(Sex) as 性别

From S ;

说明

函数getdate(), year(), month(), day()

函数upper(), lower()


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

69

SQL的数据查询


示例

Select *

From S

Where Sex=‘F’ Or NativePlace=‘四川’

(Select * From S Where Sex='F')

UNION

(Select * From S Where NativePlace=‘四川’)

(Select * From S Where Sex='F')

UNION ALL

(Select * From S Where NativePlace='四川')


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

70

SQL的数据查询


示例

Select Sno

From SC ;

Select All Sno

From SC ;

Select Distinct Sno

From SC ;

Select top 5 Sno

From SC ;

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

SNO

S1

S1

S1

S2

S2

S2

S2

S3

S3

S4

S4

SNO

S1

S2

S3

S4

SNO

S1

S1

S1

S2

S2

71

SQL的数据查询

条件表达式中的比较操作

构成Where语句的条件运算符

查询条件运算符

算数比较判断 = , > , < , >= , <= , != , <> , !> , !<

限定比较判断单值比较运算 + <ALL│ANY│SOME│UNIQUE>

区间判断 BETWEEN … AND… , NOT BETWEEN … AND…

集合成员资格判断 IN , NOT IN

存在量词判断 EXISTS , NOT EXISTS

字符匹配判断 LIKE , NOT LIKE

空值比较判断 IS NULL , IS NOT NULL

逻辑运算判断 NOT , AND , OR

72

SQL的数据查询


算数比较运算

查询学号是S3的学生姓名

Select Sname From S Where Sno = ‘S3’ ;

查询年龄小于18的学生姓名

Select Sname From S Where Age < 18 ;

查询性别为男的学生的姓名、年龄和籍贯

Select Sname, Age, NativePlace

From S

Where Sex = ‘M’ ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

73

SQL的数据查询


区间运算

查询年龄［不］在17 ~ 19岁之间的学生的学号

Select Sno From S Where Age Between 17 and 19 ;

Select Sno From S Where Age>=17 and Age<=19 ;

Select Sno From S

Where Age not Between 17 and 19 ;

Select Sno From S

Where not (Age>=17 and Age<=19) ;

Select Sno From S Where Age<17 or Age>19 ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

74

SQL的数据查询


字符匹配运算

查询籍贯［不］是上海的学生姓名

Select Sname From S Where NativePlace = ‘上海’ ;

Select Sname From S

Where NativePlace Like ‘%上海%’ ;

Select Sname From S

Where NativePlace Like ‘%上%海%’ ;

Select Sname From S Where NativePlace <> ‘上海’ ;

Select Sname From S

Where NativePlace Not Like ‘%上海%’ ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

75

SQL的数据查询


字符匹配运算

注解

LIKE与通配符“%”和“_”

使用格式：[not] Like <匹配串> [Escape <换码字符>]

“%” ：字符串中所处位置的0~n个字符

“_” ：字符串中所处位置的 1 个字符

换码字符

查询课程名以“DB_C%A”开头的课程号和学分

Select Cno, Credit From C

Where Cname like ‘DB\_C\%A%’ Escape ‘\’ ;




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

76

SQL的数据查询


空值比较运算

查询［未］缺考学生的学号和课程号

Select Sno, Cno From SC

Where Grade is Null ;

Select Sno, Cno From SC

Where Grade is not Null ;

说明，在Where语句中

“is NULL”不能用“= NULL”替换

“is not NULL”不能用“<> NULL”替换

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 NULL

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 NULL

S4 C4 75

77

SQL的数据查询


空值比较运算

查询［未］缺考过的学生的学号

Select Distinct Sno From SC

Where Grade is Null ;


Where Grade is not Null ;

Select Sno From S

Where Sno not in ( Select Sno From SC

Where Grade is Null ) ;

?

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 NULL

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 NULL

S4 C4 75

78

SQL的数据查询


集合成员运算

查询［未］选修‘C2’或‘C3’课程的学生学号

Select Sno From SC

Where Cno = ‘C2’ or Cno = ‘C3’ ;

Select Sno From SC

Where Cno = (‘C2’, ‘C3’) ;

Select Sno From SC Where Cno in (‘C2’, ‘C3’) ;

Select Sno From SC Where Cno =SOME(‘C2’, ‘C3’) ;

Select Sno From SC Where Cno not in (‘C2’, ‘C3’) ;

Select Sno From SC Where Cno <>ALL(‘C2’, ‘C3’) ;

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 NULL

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 NULL

S4 C4 75

?

? ?

79

SQL的数据查询


集合成员运算

查询［未］选修‘C2’或‘C3’课程的学生学号

Select Sno From S

Where Sno not in (

Select Sno from SC

Where Cno in (‘C2’, ‘C3’) ) ;

Select Sno From S

Where Sno <>ALL (

Select Sno from SC

Where Cno in (‘C2’, ‘C3’) ) ;

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 NULL

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 NULL

S4 C4 75


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

80

SQL的数据查询


集合成员运算

查询［未］选修‘LIU’老师所授课程的学生学号

Select Sno From SC, C, T

Where Tname=‘LIU’

and SC.Cno=C.Cno and C.TNO=T.Tno ;

Select Sno From SC Where Cno in (

Select Cno From C Where Tno in (

Select Tno From T Where

Tname=‘LIU’ ) ) ;

Select Sno From S

Where Sno not in

( 选过…的学号 ) ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

81

SQL的数据查询


集合成员运算

查询选修了‘LIU’老师所授全部课程的学生姓名

Select Sname From S

Where not Exists (

Select * From C

Where Tno in (Select Tno From T

Where Tname=‘LIU’ )

and not Exists (

Select * From SC

Where Sno=S.Sno

and Cno=C.Cno) );


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

82

SQL的数据查询


集合成员运算

查询至少有一门成绩超过S4一门成绩的学生学号


Where Grade >Some(

Select Grade From SC

Where Sno=‘S4’ ) ;

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 NULL

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 NULL

S4 C4 75

83

SQL的数据查询


集合成员运算

查询平均成绩最高的学生学号

使用导出表

Select S#

From ( Select Sno, AVG(Grade)

From SC Group By Sno

) AS TmpTable(S#, AVG_Grade)

Where AVG_Grade >= ALL ( Select AVG(Grade)

From SC

Group By Sno ) ;

84

(

)

AS RESULT ( S#, AVG_GRADE )

SELECT S#, AVG_GRADE

FROM

WHERE AVG_GRADE > 80;

导出表的使用

在FROM子句中使用子查询,给子查询的结果起表名和相应列名

例：检索平均成绩超过80分的学生学号和平均成绩SELECT S#，AVG（GRADE）FROM SC

GROUP BY S#

HAVING AVG（GRADE）> 80；

用导出表：SELECT S#，AVG（GRADE）FROM SC

GROUP BY S#

第三章关系数据库语言SQL 3.3 SQL的数据查询 85

SQL的数据查询

嵌套查询的改进写法

导出表

在From子句中使用子查询，子查询必须起别名

命名的导出表只在From中起作用，离开From子句失效

WITH语句和临时视图（SQL Server 2005以上版本）

作用类同于导出表，但语句的逻辑组织比含导出表更清晰

WITH TmpTable(S#, AVG_Grade) AS

Select Sno, AVG(Grade) From SC Group By Sno

Select S# From TmpTable

Where AVG_Grade >= ALL ( Select AVG_Grade

From TmpTable ) ;

86

SQL的数据查询


集合空否的测试

Select …

From R

Where ［NOT］EXISTS ( Select *

From S

Where S.A=R.A and … ) ;

说明

当内层集合非空，返回True，否则返回False

87

SQL的数据查询


集合中是否有重复元组的判定

查询只选修了一门课的学生的学号和姓名

Select Sno, Sname From S

Where unique( Select Sno From SC

Where Sno=S.Sno ) ;

Select S.Sno, Sname

From S, SC

Where S.Sno=SC.Sno

Group by S.Sno, Sname

Having Count(Cno) = 1 ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

88

SQL的数据查询

基本表的联接操作

略

89

SQL的数据查询

SQL3中的递归查询

略

90

SQL的数据更新

数据插入

句法

1）单元组的插入

INSERT INTO 基本表名 [(列名表)] VALUES(元组值)

2）多元组的插入：不是所有版本的DBMS都支持

INSERT INTO 基本表名 [(列名表)]

VALUES (元组值), … , (元组值)

3）查询结果的插入

INSERT INTO 基本表名 [(列名表)] <SELECT查询语句>

4）表的插入：

INSERT INTO 基本表名1 [(列名表)] TABLE 基本表名2

91

SQL的数据更新

数据插入

说明

若插入的元组值，其属性个数、次序和域，与表结构的定义一致，则列名表可以省略

否则需要列名表，且列名表的属性个数和次序，需与待插入值一一对应

数据插入受关系完整性约束的制约

数据类型

实体完整性：主键值唯一，非空

参照完整性：不允许引用不存在的实体

用户自定义的完整性

92

SQL的数据更新

数据插入

示例

Insert Into S(Sno, Sname, Age, Sex, NativePlace)

Values('S5', 'GU', 20, 'M', NULL) ;

Insert Into S Values('S5', 'GU', 20, 'M', NULL) ;

Insert Into S(Sno, Sname, Age, Sex)

Values('S5', 'GU', 20, 'M') ;

Insert Into S(Sname, Sno, Sex, Age)

Values('GU', 'S5', 'M', 20) ;

Insert Into SC(S#, C#) Values('S5', 'C8');

Insert Into SC Values('S5', 'C8', NULL);





93

数据插入

示例

Insert Into S_Grade(S#, AVG_Grade)

Select Sno, AVG(Grade) From SC

Where Sno in (Select Sno From S Where Sex＝'M')

Group By Sno

Having AVG(Grade)＞80 ;

Insert Into SC(Sno, Cno) TABLE SC4 ;

SQL的数据更新 C（CNO，CNAME，Credit，CreditHours，CPNO，TNO）




94

SQL的数据更新

数据插入

示例，问：

Insert Into SC Values('S4', 'C569', 105) ;

Insert Into SC Values(‘S4’, ‘C5’,105) ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

?

?

95

SQL的数据更新

数据插入

示例，问：

Insert Into SC Values(‘S4’, ‘C5’, 95) ;

Insert Into SC Values(‘S4’, ‘C4’, 95) ;

如图示4条相关数据的

插入顺序是什么？


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

?

?




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

96

?

②

④

③

①

SQL的数据更新

数据删除

句法

DELETE FROM <基本表名> [ WHERE 条件表达式 ]

语义

是从基本表中删除满足条件表达式的元组

注意

DELETE语句只能从一个基本表中删除元组

WHERE子句中条件可以嵌套，也可以是来自几个基本表的复合条件

如果没有WHERE子句，则删除表的所有元组，表成为空表

97

SQL的数据更新

数据删除

示例

Delete From SC Where Grade is NULL ;

Delete From C Where Cname Like ‘%M%’ ;

Delete From SC Where Cno in (

Select Cno From C Where Cname = ‘MATHS’ ) ;

Delete From SC, C

Where SC.Cno = C.Cno and Cname = ‘MATHS’ ;

Delete From SC Where Cno = ‘C4’ and Grade < (

Select AVG(Grade) From SC Where Cno = ‘C4’ );

?




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

98

SQL的数据更新

数据删除

示例，问

Delete From S Where Sname Like ‘%U%’ ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

?

Create Table SC (

SNO CHAR(3) , CNO CHAR(3) , GRADE DEC(5, 2) ,

Primary Key (SNO, CNO) ,

Foreign Key (SNO)

References S(SNO) ON DELETE restrict ,

Foreign Key (CNO)

References C ON DELETE cascade ,

Check ( GRADE Between 0 And 100) ) ;

99

SQL的数据更新

数据删除

示例，问


Delete From SC Where Sno in (

Select Sno From S

Where Sname Like ‘%U%’ );



S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

?

100

SQL的数据更新

数据删除

示例，问

如图示4条相关数据的删除顺序是什么？

Drop Table SC

与

Delete From SC

的区别是什么


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

?

②

④

③

①

?

101

SQL的数据更新

数据删除

当LIU老师不再教授课程时，删除其基本信息

Delete From T

Where Tname = ‘LIU’

AND Tno Not in （ Select Tno

From C）

当PM课程没人选修时，删除这门课程信息

当WANG同学不再选修课程时，删除其基本信息


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

? ?

102

SQL的数据更新

数据修改

句法

UPDATE <基本表名>

SET 列名=值表达式[，列名=值表达式…]│ROW = (元组)

[ WHERE 条件表达式 ]

语义

修改基本表中满足条件表达式元组的指定属性值

说明

WHERE子句表示要修改的元组需满足的条件

SET子句表示要修改的列及其新值或元组值

UPDATE语句一次只能修改一个表中的元组

105

SQL的数据更新

数据修改

示例

Update C Set Cname=‘SE’ Where Cno=‘C3’ ;

Update SC Set Grade = Grade * 1.1

Where Sno in (

Select Sno From S Where Sex = ‘F’ ) ;

Update SC, S Set Grade = Grade * 1.1

Where SC.Sno = S.Sno

and Sex = ‘F’ ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

?

?




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1 106

SQL的数据更新

数据修改

示例


Where Cno＝'C4' and Grade < (

Select AVG(Grade) From SC Where Cno＝'C4');

Update C

Set Row = ('C3', ‘SE', 4, 64 , 'C1', ‘T2' )

Where Cno = 'C3' ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

?




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1 107

SQL的数据更新

数据修改

示例

Update SC Set Grade = 60

Where Grade is NULL ;

Update SC Set Grade = Grade + 60

Where Grade is NULL ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

? ?




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1 108

SQL的数据更新

数据修改

示例

Update SC Set Grade is NULL

Where Grade = 70 and Cno in (

Select Cno From C Where Cname = 'MATH' ) ;

Update SC Set Grade = NULL

Where Grade = 70 and Cno in (

Select Cno From C Where Cname = 'MATH' ) ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

?

?




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1 109

SQL的数据更新

数据修改

示例


Where Sno= 'S2' and Cno= 'C3' ;

Update C Set Tno = 'T3' Where Cno= 'C3' ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

?

?

110

SQL的数据更新

数据修改

示例

Update C Set Cno = 'C3' Where Cno= 'C4' ;

Update C

Set Cno=NULL

Where Cno= 'C4' ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川




C3 PM 2 32 C2 T2

C4 DB 3.5 56 C1 T1

SNO CNO Grade

S1 C2 80

S1 C3 70

S1 C4 85

S2 C1 60

S2 C2 75

S2 C3 90

S2 C4 NULL

S3 C1 85

S3 C4 80

S4 C2 85

S4 C4 75

TNO TNAME TITLE SEX

T1 ZHAO 讲师 M

T2 LIU 教授 F

?

?

?

111

视图

虚表

创建视图仅定义视图结构和抽取数据的规则

不存储视图对应的数据

在使用视图时动态地从相应的基本表中获取数据

SQL

视图1 视图2

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

存储文件1 存储文件2 存储文件3 存储文件4

外模式

模式

内模式

View

用户的局部数据视角





112

视图

创建句法

CREATE VIEW <视图名> [列名表] AS <查询语句>

说明

[列名表]是否省略，要看具体情况

通过函数或表达式定义视图的列时或需在视图中取更合适的名字时→必须在列名表中定义新的列名(也有规定：多表连接选出同名列时)

对视图查询，和对基本表的查询一样，没有限制

对视图的更新（insert、update、delete）是受限制的

撤销句法

DROP VIEW <视图名>

113

建立视图（续）

RDBMS执行CREATE VIEW语句时只是把视图定义存入

数据字典，并不执行其中的SELECT语句。

在对视图查询时，首先判断视图是否存在，如果存在，则

从数据字典中取出视图的定义，把定义中的子查询和对视

图的查询结合起来，转换成等价的对基本表的查询。

视图

视图的更新限制

对视图的数据更新，最终要更新到基本表上

只有“行列子集视图”的更新是不受限制的

行列子集视图

定义在单个基本表上

定义时仅使用了选择、投影操作

包含了基本表的候选键和所有的非空字段

其他形式的视图都不允许更新操作，如

视图的列定义中使用了表达式或函数

视图定义来自多表连接

视图定义中用到GROUP BY子句或聚集函数

115

视图

示例

构建平均成绩子模式，要求显示学号、姓

名、平均成绩

G视图不允许更新

G(S#，SNAME，Gavg)

Create View G ( S#, SNAME,Gavg)

AS Select S.SNO, SNAME, AVG(Grade)

From S, SC

Where S.SNO = SC.SNO

GROUP BY S.SNO, SNAME;


S1 WANG 20 M 北京

… … … … …

SNO CNO Grade

S1 C2 80

… … …

S# SNAME Gavg

S1 WANG 75

… … …

S SC

G





116

视图

示例

Create View S_MALE

as

Select Sno, Sname, Age

From S

Where Sex = ‘M’ ;

视图S_MALE是可以更新的

Insert into S_MALE

Values (‘S5’, ‘LI’, 19) ;

Insert into S

Values (‘S5’, ‘LI’, 19, ‘M’, NULL) ;

Create Table S (

SNO CHAR(3) ,

SNAME CHAR(8) NOT NULL,

AGE Integer NULL,


NativePlace VARCHAR(20) ,

Primary Key (SNO) ,

Check (SEX IN (‘M’, ‘F’) ) ) ;


S1 WANG 20 M 北京

S2 LIU 18 F 山东

S3 HU 17 M 上海

S4 XIA 19 F 四川





117

视图

示例

Create View Grade-AVG(Sname, AVG_Grade)

as

Select Sname, AVG(Grade) From SC, S

Where SC.SNO = S.SNO

Group by Sname ;

Create View Grade-AVG

as

Select Sname, AVG(Grade) as AVG_Grade From SC, S

Where SC.SNO = S.SNO

Group by Sname ;

118

视图

优点

提供逻辑数据独立性

应该是映像的作用

简化用户观点

数据安全保护

Create View G ( S#, SNAME, C#, SCORE)

AS

Select S.SNO, SNAME, CNO, Grade

From S, SC

Where S.SNO = SC.SNO


S1 WANG 20 M 北京

… … … … …

SNO CNO Grade

S1 C2 80

… … …

S# SNAME C# SCORE

S1 WANG C2 80

… … … …

S SC

G

119

视图分为普通视图与检查视图。

通过检查视图更新基表数据时，只有满足检查条件的更新语句才能成功执行。创建检查视图的语法格式如下。

create view 视图名 [ (视图字段列表) ]

as

select语句with [ local | cascaded ] check option

检查视图

检查视图分为local检查视图与cascade检查视图。local：只要满足本视图的条件就可以更新cascade：级联视图，必须满足所有针对该视图的所有视图的条件才可以更新。默认cascade

local与cascade检查视图

local 检查视图与cascade检查视图

cascaded检查视图

检查条件：c<60

a e c c h fcascaded检查视图

检查条件：c>50

local检查视图

检查条件：c>50

操作1：

通过该视图执行update

操作，将c赋值为70。

将无法完成此操作

操作2：

通过该视图执行update操

作，将c赋值为70。

此操作可以成功执行

a e c h f

a b

基本表1

c d e

基本表2

f g h

基本表3

local与cascade检查视图

嵌入式SQL

略

125

存储过程与SQL/PSM

略

126

如图所示成绩管理系统的部分表中，S存储学生数据，C存储课程数据，SC存储学生选修课程的数据，假设S表、C表和SC表的定义如下：

Create Table S ( SNO CHAR(3), SNAME CHAR(8) NOT NULL, AGE Integer,


Primary Key (SNO) ) ;

Create Table C ( CNO CHAR(3), CNAME CHAR(18) NOT NULL, Credit Integer,

CHours Integer, Primary Key(CNO) ) ;

Create Table SC ( SNO CHAR(3), CNO CHAR(3),

GRADE Integer, Primary Key (SNO, CNO),

Foreign Key (SNO) References S(SNO),

Foreign Key (CNO) References C(CNO),

Check (GRADE Between 0 And 100) ) ;

问：请指出上述表实例的数据中存在的5处错误，并简述理由？

127

128

C 表 CNO CNAME Credit CHours

5 C1 Math 3 48

6 C2 NULL 4 64

7 C3 PM 2 32

8 C4 DB 3 48

S 表 SNO SNAME AGE SEX

1 S1 WANG 20 M

2 S2 ZHAO 18 F

3 S3 HU 17 M

4 NULL XIA 19 F

SC 表 SNO CNO Grade

9 S1 C2 80

10 S1 C3 70

11 S1 C4 115

12 S2 C1 60

13 S2 C2 75

14 S2 C2 90

15 S2 C4 80

16 S3 C1 85

17 S3 C2 80

18 S3 C4 85

19 S4 C4 75

An Introduction to Database

System

自身连接

自身连接：一个表与其自己进行连接

需要给表起别名以示区别

由于所有属性名都是同名属性，因此必须使用别名前缀

[思考题]查询每一门课的先修课程名

SELECT FIRST.Cname 课程名,SECOND.Cname 先修课程名

FROM Course FIRST,Course SECOND

WHERE FIRST.Cpno = SECOND.Cno;


System

自身连接（续）

FIRST表（Course表）

Cno Cname Cpno Ccredit

1 数据库 5 4

2 数学 2

3 信息系统 1 4

4 操作系统 6 3

5 数据结构 7 4

6 数据处理 2

7 PASCAL语言 6 4


System


Cno Cname Cpno Ccredit

1 数据库 5 4

2 数学 2

3 信息系统 1 4

4 操作系统 6 3

5 数据结构 7 4

6 数据处理 2

7 PASCAL语言 6 4

SECOND表（Course表）

An Introduction to Database System


查询结果：

课程名先修课程名

数据库数据结构

信息系统数据库

操作系统数据处理


System

[例]查询每一门课的间接先修课号（即先修课的先修课）

SELECT FIRST.Cno，SECOND.Cpno

FROM Course FIRST，Course SECOND

WHERE FIRST.Cpno = SECOND.Cno；

An Introduction to Database System


查询结果：

Cno Pcno

1 7

3 5

5 6


System

[例]查询每一门课的间接先修课名（即先修课的先修课）

SELECT FIRST.Cname,THREE.Cname

FROM Course FIRST,Course SECOND,Course THREE

WHERE FIRST.Cpno = SECOND.Cno

AND SECOND.Cpno=THREE.Cno;

Unified Modeling Language引言 SQL是关系数据库的标准语言...

Documents

Transcript of Unified Modeling Language引言 SQL是关系数据库的标准语言...