数据库原理及应用：第三章

分类：计算机格式：ppt 日期：2006年06月29日

上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
第 3章关系数据库查询语言 SQL
3.1 SQL语言概述
3.2 数据定义语言（ DDL）
3.3 数据查询
3.4 数据更新
3.5 视图
3.6 数据控制
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
3.1 SQL语言概述
SQL, Structured Query Language—结构化查询语言
1974年提出,经过几十年发展已成为国际标准,是一个通用的、功能极强的
关系数据库语言,目前已成为关系数据库的标准语言。设计巧妙，语言简洁。
SQL语言主要组成部分
数据定义语言（ DDL,Data Definition Language）
数据操纵语言（ DML,Data Manipularion Language）
数据控制语言（ DCL,Data Control Language）
表 3.1 SQL语言动词表
SQL功能相关动词
数据定义 Create,Drop,Alter
数据查询 Select
数据操纵 Insert,Update,Delete
数据控制 Grant,Revoke
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
SQL语言支持关系数据库三级模式结构,
外
模式
模式
内模式
存储文件 1 存储文件 2
基本表 1 基本表 2 基本表 3 基本表 4
视图 1 视图 2
SQL
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
3.2 数据定义语言（ DDL）
数据定义语言：数据定义功能包括定义表、定义视图、定义索引。由于视图
是基于基本表的虚表，索引是依附于基本表的，因此 SQL不提供修改视图
定义和修改索引定义的操作。
表 3.2 SQL的数据定义语句
操作对象操作方式
创建删除修改
表 Create Table Drop Table Alter Table
视图 Create View Drop View Alter View
索引 Create Index Drop Index
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
一、定义基本表
建立数据库最重要的一步就是定义一些基本表。
定义基本表命令格式如下：
CREATE TABLE <表名 >(<列名 ><数据类型 >
(长度 )[列级完整性约束条件 ]
[,<列名 ><数据类型 >
[列级完整性约束条件 ]]… );
例 3.1 建立学生档案表 XSDAB，其中属性包括,学号 (XH)、姓名 (XM)、出生年
月 (CSNY)、性别 (XB)、民族 (MZ)、政治面貌（ ZZMM）、系别编号 (XBBH)、
年级 (NJ)、班级 (BJ)、专业编号 (ZYBH)。其中，学号属性不能为空，并且
其值是惟一的。
Create Table XSDAB(XH Char(8) NotNull Unique,XM Char(8),CSNY
Date,XB Char(2),MZ Char(4),ZZMM Char(4),XBBH Char(1),NJ
Char(2),BJ Char(8),ZYBH Char(2)) ;
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
二、修改基本表
ALTER TABLE <表名 > ADD <新列名 > <数据类型 >
(长度 ) [完整性约束 ] | [DROP <完整性约束名 > ]
| [MODIFY <列名 > <数据类型 > (长度 )];
其中,ADD—子句用于增加新列；
DROP—子句用于删除指定的完整性约束条件；
MODIFY—子句用于修改原有的列定义；
,|, —表示上述三个子句只能选其一。新增加的列一律为空值
例 3.2 向 XSDAB表中增加年龄（ NL）一列，数据类型为整型，长度为 2位。
Alter Table XSDAB Add NL Integer(2)；
例 3.3 将 XSDAB中学号的数据类型改为整数。
Alter Table XSDAB Modify Integer；
注意；修改原有的列定义时会破坏已有数据，甚至丢失。
例 3.4 删除关于学号必须取惟一值的约束。
Alter Table XSDAB Drop unique(XH)
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
三、删除基本表
DROP TABLE <表名 >；
例 3.5 删除学生档案表（ XSDAB）。
Drop Table XSDAB；
执行此命令后，基本表不在，数据和索引将自动被删除。
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
四、建立索引
建立索引是加快表的查询速度的有效手段，在一个基本表上可建立一个或多
个索引。
CTRETE INDEX <索引名 > ON <表名 >
(<列名 >[<次序 >][,<列名 >[<次序 >]]… ) ；
各列用逗号分隔，次序为升序（ ASC）和降序（ DESC）
例,Create index Sname on XSDAB(XM);
例,Create index Sno on XSDAB(XH);
五、删除索引
DROP INDEX <索引名 >；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
3.3 数据查询
语句一般格式：
SELECT [ALL | DISTINCT] <目标列表达式 >
FROM <基本表或视图 >
[ WHERE <条件表达式 > ]
[ ORDER BY <列名 > ]
[ GROUP BY <列名 > ] ；
根据子句的条件表达式从子句指定的基本表或视图中找出满足条件的元
组，再按子句中的目标列表达式，选出元组中的属性形成结果表。
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
一、单表查询
1、目标列的设置
⑴ 以字段名（属性名）作为目标表达式，逗号分隔
例 1,Select XH,XM,CSNY From XSDAB；
⑵ 选择表中的全部列，列出全部字段名或用 *号
例 2,Select XH,XM,CSNY,XB,MZ,ZZMM,XBBH,NJ,BJ,ZYBH
From XSDAB；
或 Select * From XSDAB ；
⑶使用表达式 —属性列的计算
例 3,Select XH,XM,YEAR ( DATE ( ) ) — YEAR ( CSNY )
From XSDAB；
DATE（） —— 取系统日期
YEAR（） —— 取日期中的年份
NJ（年龄）属性不存在，通过计算得到
例 4,Select XH，语文 +数学 From 成绩表；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
⑷ 使用函数
COUNT（）统计元组个数 DATE（）
SUM（）计算一列值的总和 YEAR（）
AVG（）计算一列值的平均值 MID（）
MAX（）求一列值中的最大值 VAL（）
MIN（）求一列值中的最小值 IFF（）
⑸ AS的使用
由于表达式的使用，结果列名不再是一个已经存在的属性名，系统自动形成，
为了能够控制名称，可以使用 AS语句对列进行更名。
例 5,Select XH AS 学号,XM AS 姓名，
YEAR ( DATE ( ) ) — YEAR ( CSNY ) AS 年龄
From XSDAB；
⑹ ALL 和 DISTINCT 的使用
ALL——结果表中保留取值重复的行
DISTINCT ——结果表中去掉取值重复的行
例 7,Select NJ From XSDAB；
Select Distinct NJ From XSDAB；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
2,WHERE子句 ——条件表达式的设置
⑴比较大小（ =,<,>,<=,>=,<>)
例 8：查询 XSDAB中年龄小于 20的学生的全部信息
Select XH,XM,CSNY,XB,MZ,ZZMM,XBBH,NJ,BJ,ZYBH
From XSDAB
Where NL<20；
例 9,Select XH,XM From XSDAB Where XB=“男, ；
⑵确定范围
·BETWEEN <表达式 1> AND <表达式 2>
查找属性值在表达式 1和表达式之间的元组
·NOT BETWEEN <表达式 1> AND <表达式 2>
查找属性值在表达式 1和表达式之间的元组
例 10,Select XH,XM,XB
From XSDAB
Where NL Between 18 And 20；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
⑶ 确定集合
· IN 可以用来查找属性值属于指定集合的元组
·NOT IN 可以用来查找属性值属于指定集合的元组
例 11,Select XH,XM,XB
From XSDAB
Where XM IN（, 王二,,, 张三, ）；
⑷字符匹配
[NOT] LIKE ?匹配串 ’
查找指定属性列值与 ‘ 匹配串 ’ 相匹配的元组,‘ 匹配串 ’ 可以是一个完整
的字符串，也可以含有通配符。
* —— 任意多个连续字符？ —— 所在位置的任意一个字符
例 12：查询学号的第 7,8位为, 01”的学生的信息。
Select *
From XSDAB
Where XH Like ?01*?；
例 13,Select * From XSDAB Where XH Like ?男 ’ ；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
⑸ 涉及空值的查询
ISNULL（） IS NULL
NOT ISNULL（） IS NOT NULL
例 14：查询 XSDAB中 CSNY非空的学生信息
Select * From XSDAB
Where Not IsNull(CSNY)；
或,Select * From XSDAB
Where Is Not Null CSNY；
⑹多重条件查询
逻辑运算符 NOT,AND,OR可用来联结多个查询个体。
例 15：查询成绩表中语文和数学均在 90分以上的学生信息。
Select * From 成绩表
Where 语文 >=90 and 数学 >=90；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
3、对显示结果排序
可以使用 ORDER BY 子句指定按照一个或多个属性列的升序或降序重新排
列查询结果。
例 17：查询学生信息并按年龄降序排列。
Select * From XSDAB
Order by NL desc；
例 18,Select * From XSDAB
Where NL>=20
Order by NL desc；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
4、对查询结果分组
GROUP BY 子句可以将查询结果表的各行按一列或多列取值相等
的原则进行分组。
例 19：对性别分组查询
Select XB
From XSDAB
Group by XB；
例 20,Select XB,BJ
From XSDAB
Group by XB,BJ；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
例：设数据库有 3个关系如下：
学生（学号，姓名，性别，年龄，专业）
课程（课程号，课程名，学分，先行课程号）
成绩（学号，课程号，成绩）
用 SQL语言写出下列各项操作
⑴查询所有年龄小于 20岁的男学生
Select *
From 学生
Where 年龄 <20 AND 性别 =?男 ’ ；
⑵查询所有女同学的姓名和年龄
Select 姓名，年龄
From 学生
Where 性别 =?女 ’ ；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
⑶ 查询选修课程号为 C2或 C4的学生的学号
Select 学号
From 成绩
Where 课程号 =?C2? OR 课程号 =?C4?；
⑷查询计算机专业和机械专业学生的情况
Select *
From 学生
Where 专业 =?计算机 ’ OR 专业 =?机械 ’ ；
⑸ 得到一张包括学生学号、课程名、学分和成绩的学生成绩单
Select 学生,学号,课程,课程名,课程,学分，成绩,成绩
From 学生,课程，成绩
Where 学生,学号 = 成绩,学号 AND 课程,课程号 =成绩,课程号 ;
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
二、连接查询
一个查询同时涉及 2个以上的表，称为连接查询。
1、等值与非等值连接查询
用来连接 2个表的条件称为连接条件，格式如下：
<表名 1>, <列名 1> <比较运算符 >
<表名 2>, <列名 2>
连接条件中的各连接字段类型必须是可比的，但不必相同。
注意：①目标列：表名,列名。 ②表要写多个，逗号分隔。
连接查询的过程：
首先在表 1中找到第一个元组，从表名 2中元组头开始顺序扫描，查找满足连
接条件的元组，每找到一个元组，就将表 1中的第一个元组与该元组拼接起
来，形成结果表中的一个元组。表 2全部扫描完毕后，再到表 1中找第二个
元组，再从表 2头开始扫描。
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
例：在学生、课程、成绩三个表中查询学生的学号、姓名、课程号、成绩。
Select 学生,学号,学生,姓名,成绩,课程号,成绩,成绩
From 学生,成绩
Where 学生, 学号 = 成绩, 学号 ;
为了避免混淆，属性名前可加上表名前缀。
自然连接：两个表中相同属性进行等值连接
卡氏连接：即笛卡尔积，或无条件连接
例,Select 学号,姓名,课程号,成绩
From 学生,成绩 ;
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
2、复合条件连接
WHERE子句中有多个条件的连接操作，称为复合条件连接。
例,在学生、课程、成绩三个表中查询学生的学号、姓名、课程号、成绩，
要求成绩在 60分以上。
Select 学生,学号,学生,姓名,成绩,课程号,成绩,成绩
From 学生,成绩
Where 学生, 学号 = 成绩, 学号 AND 成绩 >=60;
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
三、嵌套查询
一个 SELECT—FROM—WHERE语句称为一个查询块，将一个查询块嵌
套在另一个查询块的 WHERE的条件中的查询称为嵌套查询或子查询。
例：查询 CJ1和 CJ2均及格的学生名单。
Select xh,xm
From xsdab
Where xh in ( Select xh
From cj
Where cj1>=60 and cj2>=60）；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
四、集合查询
每一个 SELECT语句都能获得一个或多个元组，若要把多个 SELECT
语句的结果合并为一个结果，可用集合操作来完成。
集合的主要操作包括：
并 —UNION，属性必须相同，类型必须相同。
交 —INTERSECT
差 —MINUS
例题见教材 P64,65,66
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
3.4 数据更新
数据更新包括插入数据、修改数据、删除数据。
一、插入数据插入语句 INSERT
1、插入一个元组
INSERT INTO <表名 >（ <属性列 1>[,<属性列 2>]… ）
VALUES (<常量 1>[,常量 2]… )
其功能是将新元组插入指定表中。其中属性列 1的值为常量 1，属性列 2的值
为常量 2，如果某些属性在 INTO子句中没有出现，则新记录在这些列上将取
空值。
例：向学生档案表中增加一名学生，其学号为 20001308，姓名为赵亮，出生
年有为 197801，性别为男，民族为汉，政治面貌为团员，系别编号为 2，级
为 2000，班级为 2，专业编号为 1。
Insert into xsdab (xh,xm,csny,xb,mz,zzmm,xbbh,nj,bj,zybh)
Values (“20001308”,“赵亮,,“197801”,“男,,“汉,,“团
员,,“2”,“2000”,1)
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
2、插入子查询结果
INSERT INTO <表名 >（ <属性列 1>[,<属性列 2>]… ）
查询语句
其功能的批量插入，一次将子查询的结果全部插入指定表中。
例：将成绩表 2中的语文成绩在 75分及 75分以上的所有同学添加到成绩表 1中。
Insert Into 成绩表 1
Select *
From 成绩表 2 Where 语文 >=75
上面的命令必须要求成绩表 1和成绩表 2的结构完全相同才可执行，若只添加
学号和语文成绩，
可写为：
Insert Into 成绩表 1(学号，语文）
Select 学号，语文
From 成绩表 2 Where 语文 >=75
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
二、修改数据
UPDATE <表名 >
SET <列名 >=<表达式 > [ <列名 >=<表达式 >… ]
[ WHERE <条件 > ]
其功能是修改指定表中满足 WHERE子句条件的元组。其中 SET子句指定修改
方法，即用表达式的值取代相应的属性列值。如果省 WHERE
子句，则表示要修改表中的所有元组。
例：修改一个元组，将成绩表中学号为 20001308的学生的语文成绩更改为 90。
Update 成绩表 Set 语文 =90 Where 学号 =“20001308”
例：修改多个元组的值。将成绩表中所有学语文成绩增加 10%。
Update 成绩表 Set 语文 =语文 *（ 1+10%）
注意：修改语句一次只能操作一个表。如果相联的各表都要修改的话，则通
过多条修改语句分别对多个表修改。
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
三、删除数据
DELETE FROM <表名 > [WHERE <条件 >]
其功能是从指定表中删除满足 WHERE子句
条件的所有元组。如果省略 WHERE子句，则删除全部元组，但表的定义仍在字
典中。
例：删除一个元组的值。学号为 20001409的学生因故退学，将其数据从学生
档案表中删除。
Delete
From xsdab
Where 学号 =“20001409”
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
3.5 视图
视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，
是一个虚表。数据库中只存放视图的定义，不存放视图对应的数据，这些
数据仍存放在原来的基本表中。
视图一经定义，就可以和基本表一样被查询、更新。
一、视图的定义
CREATE VIEW <视图名 > [(<列名 > [,<列名 >]… )]
AS <子查询 >
WITH CHECK OPTION；
说明：⑴子查询可以是任意复杂的 SELECT语句；
⑵列名如省略，默认查询中的列名；
⑶ With Check Option表示对视图更新时要保证子查询中的条件。
例：建立学生档案查询视图，只查学号、姓名。
Create View DA
As
Select XH,XM From XSDAB;
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
二、删除视图
视图建立好后，若导出此视图的基本表被删除了，该视图将失效，
一般不会被删除。
删除语句,DROP VIEW <视图名 >；
一个视图被删除后，由它导出的视图也将失效，应使用 DROP VIEW
语句将它们一一删除。
三、查询视图
SELECT [ALL | DISTINCT] <目标列表达式 >
FROM <视图 >
[ WHERE <条件表达式 > ]
[ ORDER BY <列名 > ]
[ GROUP BY <列名 > ] ；
对视图的查询，首先进行有效性检查，检查查询涉及的表、视图是
否在数据库中存在，如存在，则从数据字典中取出查询涉及的视的定义，
把定义中的子查询和用户对视图的查询结合起来，转换成对基本表的查询，
这称为视图的消解。
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
四、更新视图 ——插入、删除、修改
由于视图是不存在的虚表，更新要转换成对基本表的更新。可在定
义视图时加上 With Check Option子句，以防止不属于视图范围内的
基本表更新。
五、视图的用途
⑴简化用户的操作；
⑵使用户能以多种角度看待同一数据；
⑶对重构数据库提供了一定程度的逻辑独立性；
⑷对机密数据提供安全保护。
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
3.6 数据控制
数据控制也称为数据保护，包括数据的安全性控制、完整性控制、并
发控制和恢复。
⒈安全性：是指保护数据库，防止不合法的使用所造成的数据泄露和破坏。
⒉完整性：是指数据库中的正确性和相容性。
⒊并发控制：是指当多个用户并发地对数据库进行操作时，对它们加以控制、
协调，以保证并发操作正确执行，并保证数据库的一致性。
⒋恢复：是指当发生各种类型的故障，使数据库处于不一致状态时，将数据库
恢复到一致状态的功能。
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
一、授权
GRANT <权限 > [,<权限 > ]…
[ ON <对象类型 > <对象名 > ] TO <用户 > [,<用户 >]…
[ WITH GRANT OPTION ]；
对象对象类型操作权限
视图 TABLE select,insert,update,delete,all privileges
基本表 TABLE alter,index，其它同上
数据库 DATABASE createtab
如指定了 With Grant Option子句，则获得某种权限的用户还可以把这
种权限再授予别的用户。
例 1：把查询 XSDAB的权限授给用户 A，同时允许 A将此权限授给其它用户。
Grant select ON Table XSDAB TO A With Grant
Option;
例 2：把对成绩表的全部权限授给用户 B和 C。
Grant all privileges ON Table 成绩表 TO B,C;
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
二、收回权限
REVOKE <权限 > [,<权限 > ]…
[ ON <对象类型 > <对象名 > ]
FROM <用户 > [,<用户 >]… ;
例：收回用户 A对 XSDAB的查询权限。
Revoke select ON Table XSDAB From A;
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
第三章小结
一、数据定义：
基本表的定义（ CREATE）、修改（ ALTER）、删除（ DROP），索引的
建立、删除。
二、数据查询：
单表、连接、嵌套、集合查询（ SELECT）
三、数据更新：
插入（ INSERT）、修改（ UPDATE）、删除（ DELETE）
四、视图,定义、删除、查询、更新
五、数据控制,授权、收回权限
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
习题课
设有一个数据库，包括四个关系模式：
供应商（供应商代码，供应商姓名，状态，城市）
零件（零件代码，名称，颜色，重量）
工程项目（代码，名称，工程项目所在城市）
供应情况（供应商代码，零件代码，工程项目代码，供应数量）
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
供应商表（ S）零件表（ P）
代码姓名状态城市代码名称颜色重量
S1 精益 20 天津 P1 螺母红 12
S2 盛锡 10 北京 P2 螺栓绿 17
S3 东方红 30 北京 P3 螺丝刀兰 14
S4 丰泰盛 20 天津 P4 螺丝刀红 14
S5 为民 30 上海 P5 齿轮兰 30
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
工程项目表（ J）供应情况表（ SPJ）
代码项目名城市供应商代码零件代码工程代码数量
J1 三建北京 S1 P1 J1 200
J2 一汽长春 S1 P1 J3 100
J3 弹簧厂天津 S2 P3 J1 400
J4 造船厂天津 S2 P3 J5 400
J5 机车厂唐山 S3 P1 J1 200
S3 P3 J1 200
S4 P5 J1 100
S5 P2 J4 100
S5 P3 J1 200
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
用 SQL语句写出下列各项操作：
1、查询供应工程 J1零件的供应商代码
Select 供应商代码
From 供应情况
Where 工程代码 =“J1”；
2、查询供应工程 J1零件 P1的供应商代码
Select 供应商代码
From 供应情况
Where 工程代码 =“J1” AND 零件代码 =“P1”；
3、求供应工程 J1零件为红色的单位代码
Select 供应商代码
From 供应情况，零件
Where 工程代码 =“J1” AND 颜色 =“红, and
供应情况, 零件代码 = 零件, 代码；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
4、求没有使用天津单位生产的红色零件的工程代码。
Select 工程代码
From 供应商，供应情况，零件
Where 颜色 <>“红, and 城市 <>天津 and
供应情况, 零件代码 = 零件, 代码 and
供应商,代码 =供应情况,供应商代码；
5、把全部红色零件颜色改为粉红色
Update 零件
Set 颜色 =“粉红,
Where 颜色 =“红, ；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
6、由 S1供应给 J1的零件 P1今改为由 S2供给，作必要修改。
Update 供应情况
Set 供应商代码 =“S2”
Where 供应商代码 =“S1” and 零件代码 =“P1” and 工程代码 =“J1”；
7、删去全部兰色零件及相应的供应情况
Delete
From 供应情况
Where 零件代码 in ( Select 代码
From 零件
Where 颜色 =“兰, ）；
Delete
From 零件
Where 颜色 =“兰, ；
上一页下一页
第 3章关系数据库查询语言 SQL 数据库原理与应用
谢
谢
您
的
光
临
！
Thank you very much!

课件简介

课件名称：	数据库原理及应用
课件分类：	计算机
课件类型：	电子教案
文件大小：	370.69KB
下载次数：	9
评论次数：	4
用户评分：	6.3

用户列表

陈森

guochao