关系数据库标准语言SQL

Download Report

Transcript 关系数据库标准语言SQL 

关系数据库标准语言
SQL
要点
 SQL语言简介
 SQL语言及其使用方式
第2页
2015/7/20
3.1 SQL概述
 Structured
Query Language, 1974年提出
 关系数据库的国际标准语言

大多数数据库均用SQL作为共同的数据存取语
言和标准接口，实现不同数据库系统之间的互
操作
 目前仍被不断扩充
 介于关系代数和关系演算之间，三者可相互
转换
第3页
2015/7/20
SQL的特点

综合统一：


高度非过程化：


操作对象、查询结果、更新数据均可以是元组的集合
提供两种使用方式：


用户只需提出“做什么”，而无需指明“怎么做 ”
面向集合的操作方式：


集DDL、DML、DCL功能于一体，可独立完成数据库
生命周期中的全部活动，语言风格统一
独立式、嵌入式
语言简捷，易学易用
第4页
2015/7/20
SQL功能
关键动词
数据查询
SELECT
数据定义
CREATE, DROP, ALTER
数据操纵
INSERT, UPDATE, DELETE
数据控制
GRANT, REVOKE
SQL语言的基本概念

SQL支持关系数据库的三级模式结构



存储文件：组成关系数据库的内模式，对用户透明
基表(Base Table)：组成关系数据库的模式，一个关系对应一个基表，
一或多个基表对应一个存储文件
视图(View)：组成关系数据库的外模式，从一个或多个基表中导出，
不独立存储在数据库中
SQL
视图1
基本表1
第5页
2015/7/20
基本表2
存储文件1
外模式
视图2
基本表3
基本表4 模式
存储文件2
内模式
3.2 数据定义
 SQL的数据定义语言(DDL)
操作方式
操作对象
创建
删除
模式
CREATE
SCHEMA
DROP SCHEMA
第6页
2015/7/20
基表
CREATE TABLE
DROP TABLE
视图
CREATE VIEW
DROP VIEW
索引
CREATE INDEX
DROP INDEX
修改
ALTER TABLE
模式的定义与删除
 定义模式
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
例如：
CREATE SCHEMA “S-T” AUTHORIZATION WANG;
注意：要创建模式，调用该命令的用户必须拥有DBA权限，
或者获得了DBA授予的CREATE SCHEMA的权限
定义模式实际上定义了一个命名空间，在这个空间中可以进
一步定义该模式包含的数据库对象，例如基本表，视图等。
CREATE SCHEMA TEST AUTHORIZATION ZHANG
第7页
2015/7/20
CREATE TABLE TAB1(COL1 SMALLINT,
COL2 INT);
 模式删除
DROP SCHEMA <模式名> <CASCADE | RESTRICT>
其中， CASCADE和RESTRICT两者必选其一
CASCADE（级联），表示在删除模式的同时把该模式中的
所有的数据库对象全部一起删除。
RESTRICT（限制），表示如果该模式中已经定义了下属
的数据库对象，则拒绝该删除语句的执行。
例如：
DROP SCHEMA ZHANG CASCADE;
第8页
2015/7/20
CREATE TABLE <表名> (
<列名><数据类型>[列级完整性约束]
[, <列名><数据类型>[列级完整性约束]]…
[, <表级完整性约束>] );

数据类型：


定义基表
不同的数据库系统有自己的数据类型规定，但一般都包
括INTEGER, FLOAT, CHAR(n), VARCHAR(n)等
完整性约束条件

列级完整性约束条件：涉及表的某一列


表级完整性约束条件：涉及表的一个或多个列

第9页
2015/7/20
如对数据类型的约束，对数据格式的约束，对取值范围或集合的
约束，对空值NULL(空值，不知道或不能用的值)的约束，对取
值的唯一性UNIQUE约束，对列的排序说明等
如订货关系中规定发货量不得超过订货量，如教授工资不得低于
1000元等
SQL支持的数据类型

SMALLINT 半字长的整数

INT 全字长的整数

FLOAT 浮点数

CHAR(n) 长度为n的定长字符串

VARCHAR(n) 最大长度为n的变长字符串

DEC(p,q) 十进制数，共p位，小数点后有q位

DATE 日期型, 格式YYYY-MM-DD

TIME 时间型，格式为HH.MM.SS

TIMESTAMP 日-时戳（日期加时间）
第10页
2015/7/20
完整性约束
 CREATE




TABLE的完整性约束
NOT NULL 属性值禁止为空
UNIQUE 取值唯一
PRIMARY KEY (A1, ..., An) 主码--若干属性列
CHECK(P) P为条件表达式
• SQL-92以后版本中，一个属性若有
PRIMARY KEY声明则隐含有NOT NULL约束
第11页
2015/7/20
例：建立学生管理的相关基表
CREATE TABLE Student
(Sno CHAR(5) NOT NULL UNIQUE,
Sname CHAR(20) UNIQUE,
Ssex CHAR(1),
Sage INTEGER,
CREATE TABLE Course
Sdept CHAR(15));
(Cno CHAR(8) NOT NULL UNIQUE,
Cname CHAR(20),
Cpno INTEGER,
Ccredit INTEGER);
CREATE TABLE SC
(Sno CHAR(5) NOT NULL UNIQUE,
Cno CHAR(8) NOT NULL UNIQUE,
第12页
2015/7/20
Grade INTEGER);
例：建立图书管理的相关基表
CREATE TABLE Borrows
(CardNo INTEGER NOT NULL UNIQUE,
Name CHAR(10),
Dept CHAR(20));
CREATE TABLE Books
(BookNo INTEGER NOT NULL UNIQUE,
SortNo CHAR(10),
Title CHAR(30),
Author CHAR(12),
Price FLOAT,
LoanNo INTEGER);
第13页
2015/7/20
CREATE TABLE Loans
(CardNo INTEGER NOT NULL UNIQUE,
BookNo INTEGER NOT NULL UNIQUE,
Title CHAR(30),
Date CHAR(10));
例：完整性约束
CREATE TABLE branch
(branch-name char(15) not null,
branch-city
char(30),
assets
integer)；
CREATE TABLE branch
(branch-name char(15),
branch-city
char(30),
assets
Integer,
PRIMARY KEY (branch-name),
CHECK (assets >= 0))；
第14页
2015/7/20
ALTER TABLE <表名>
[ADD <列名> <数据类型>
[列级完整性约束]]
[DROP <完整性约束名>]
[MODIFY <列名> <数据类型>];
例
ALTER TABLE Loans
ADD XX INT;
ALTER TABLE Loans
MODIFY Cardno SMALLINT;
ALTER TABLE Loans
第15页
2015/7/20
DROP UNIQUE(BookNo);
修改基表
删除基本表
DROP TABLE <表名> ;
例
DROP TABLE Loans;
第16页
2015/7/20
CREATE [UNIQUE] [CLUSTER]
INDEX <索引名> ON <表名>
(<列名> [<ASC|DESC>]
[, <列名> [<ASC|DESC>]]…);





例
建立索引
加快检索速度
UNIQUE表示索引的每一个索引值只对应唯一的数据记录
CLUSTER：建聚簇索引，即索引项顺序与表中记录的物
理顺序一致，一个基表只能建一个聚簇索引
ASC(升序，缺省)、DESC(降序)
索引建立后由系统使用和维护，不需用户干预
CREATE UNIQUE INDEX IB ON Borrows(CardNo);
CREATE UNIQUE INDEX IS ON Student(Sno);
第17页
CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);
2015/7/20
DROP INDEX <索引名> ;
• 例
DROP INDEX IB;
DROP INDEX SCno;
第18页
2015/7/20
删除索引
3.3 查询
数据库查询是数据库的核心操作，SQL 提供了
基于集合和关系的查询操作，具有丰富的功能
和灵活的使用方式
 一个 SQL 查询的结果是一个关系
 查询可分为





第19页
2015/7/20
单表查询：查询只涉及一个表
连接查询：查询同时涉及两个以上的表
嵌套查询：一个查询块嵌套在另一个查询块中
视图查询：在视图基础上的查询
SQL询语句的格式
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]…
FROM <表名或视图名>[, <表名或视图名>]…
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];

查询语句的典型格式
select A1, A2, ..., An
from r1, r2, ..., rm
where P
在做每个SQL查询时都试着写出其关系代数表达式
 这个查询与下面的关系代数表达式 等价：
第20页
2015/7/20
A1, A2, ..., An(P (r1 x r2 x ... x rm))
3.3.1 单表查询
一、选择表中若干列



查询指定列：在<目标列表达式>中指定预查属性
查询全部列：在<目标列表达式>中使用*
查询经计算的值：在<目标列表达式>中可使用常
量、表达式、函数等
SELECT Sno, Sname
FROM Student;
SELECT *
FROM Student;
SELECT Sname, ‘Year of Birth is’, 2002-Sage
FROM Student;
第21页
2015/7/20
查询实例
SELECT Sname, ‘Year of Birth is’, 2002-Sage，
ISLOWER(Sdept)
FROM Student;
结果为：
Sname
‘year of Birth:’ 2002-Sage ISLOWER(Sdept)
————————————————————————
李勇
Year of Birht:
1982
cs
刘晨
Year of Birht:
1983
is
王敏
Year of Birht:
1984
ma
张立
Year of Birht:
1983
is
第22页
2015/7/20
定义别名

用户可以通过指定别名来改变查询结果的列标题，这对
于含算术表达式、常量、函数名的目标列表达式尤为有
用。例如对于上例，可以定义如下列别名：
SELECT Sname NAME, ’Year of Birth:’ BIRTH,
1996-Sage YEAR, ISLOWER(Sdept) DEPARTMENT
FROM Student;
结果为：
NAME
第23页
2015/7/20
BIRTH
YEAR
DEPARTMENT
——————————————————-----——————
李勇
Year of Birth:
1982
cs
刘晨
Year of Birth:
1983
if
王敏
Year of Birth:
1984
ma
张立
Year of Birth:
1983
if
access数据库中SQL例
别名表示：字段 as 别名
注：ISLOWER( )函数在access中无法识别
SELECT Sname as NAME, 'Year of Birth:' as
BIRTH, 1996-Sage as YEAR, Sdept as
DEPARTMENT
FROM Student;
第24页
2015/7/20
二、选择表中若干元组





第25页
2015/7/20
取消取值重复的列：指定DISTINCT短语
查询满足条件的元组：在WHERE子句中指
定条件
对查询结果排序：使用ORDER BY
使用集函数：COUNT, SUM, AVG, MAX,
MIN
对查询结果分组：使用GROUP BY，
HAVING
DISTINCT 短语


SQL 允许重复的元组/行存在，如果需要去掉重复的元
组/行，必须指定DISTINCT 短语，缺省为ALL
例: 查询选修了课程的学生学号
SELECT Sno FROM SC;
或
SELECT ALL Sno FROM SC;
第26页
2015/7/20
结果为 ：
Sno
———
95001
95001
95001
95002
95002
SELECT DISTINCT Sno
FROM SC;
结果为：
Sno
———
95001
95002
WHERE子句
查询满足条件的元组：在WHERE子句中指定条件
 WHERE子句常用的查询条件:

查询条件
比较
BETWEEN AND, NOT BETWEEN AND
确定集合
IN, NOT IN
字符匹配
LIKE, NOT LIKE
多重条件
2015/7/20
=,>,<,>=,<=,!=,<>,!>,!<; NOT+上述比较运算符
确定范围
空值
第27页
谓 词
IS NULL, IS NOT NULL
AND, OR
Where子句－比较大小
例 查询计算机系全体学生的名单
SELECT Sname FROM Student WHERE Sdept = ‘CS’;
例 查询考试成绩有不及格的学生的学号
SELECT DISTINCT Sno FROM SC WHERE Grade < 60;
例 查询所有年龄在20岁以下的学生姓名及其年龄
第28页
2015/7/20
SELECT Sname, Sage
SELECT Sname, Sage
FROM Student
FROM Student
WHERE Sage<20;
WHERE NOT Sage>=20;
Where子句－确定范围
例 查询年龄在20~40岁之间的学生姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 40;
例 查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
第29页
2015/7/20
注意BETWEEN后是范围的下限，AND后是范
围的上限
Where子句－确定集合
例 查询信息系(IS)、数学系(MA)和计算机系(CS)学生的姓名和性别
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN (‘IS’, ‘MA’, ‘CS’);
例 查询除信息系(IS)、数学系(MA)和计算机系(CS)以外其它
系学生的姓名和性别
SELECT Sname, Ssex
FROM Student
WHERE Sdept NOT IN (‘IS’,’MA’,’CS’)
第30页
2015/7/20
Where子句－字符串匹配
谓词LIKE可以用来进行字符串的匹配
格式：[NOT] LIKE ’<匹配串>’[ESCAPE’<换码字符>’]
匹配串中%代表任意长度的字符串，如a%b：acb, addgb, ab
匹配串中_ 代表任意单个字符，如a_b：acb afb
例 查询学号为
95001的学生
的详细情况：
SELECT *
FROM Student
WHERE Sno LIKE ‘95001’;
SELECT *
相当于
第31页
2015/7/20
FROM Student
WHERE Sno = ‘95001’;
通配符例子
例 查询所有姓刘的学生的详细信息
SELECT *
FROM Student
WHERE Sname LIKE ‘刘%’;
例 查询所有全名为三个字且中间汉字为“明”的学生的详细信息
SELECT *
FROM Student
WHERE Sname LIKE ‘_ _明_ _’;
第32页
2015/7/20
注意一个汉字占两个字符的位置
换码字符
例 查询DB_Design课程的课程号和学分
SELECT Cno, Credit
FROM Course
WHERE Cname LIKE ‘DB\_Design’ ESCAPE ‘\’ ;
注：ESCAPE ‘\’表示‘\’为转义换码字符，紧跟其后的_转义为普通_字符
例 查询以“DB_”开头，且倒数第3个字符为 i 的课程的详细情况
SELECT *
FROM Course
WHERE Cname LIKE ‘DB\_%i_ _’ ESCAPE ’\’ ;
第33页
2015/7/20
Where子句－涉及空值的查询
例：某些学生选修课程后没有参加考试，所以有选修课记录，
但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号：
SELECT Sno, Cno
FROM SC
WHERE Grade IS NULL;
注意这里的“IS” 不能用等号代
替
例 查询所有有成绩的学生学号和课程号
SELECT Sno, Cno
FROM SC
WHERE Grade IS NOT NULL;
第34页
2015/7/20
Where子句－多重条件查询
例 查询计算机系年龄在20岁以下的学生姓名
SELECT Sname
FROM Student
WHERE Sdept = ‘CS’ AND Sage <20;
例 查询信息系(IS)、数学系(MA)和计算机系(CS)学生的姓名
和性别
SELECT Sname, Ssex
FROM Student
WHERE Sdept = ‘IS’ OR Sdept = ‘ MA’ OR Sdept = ‘CS’;
第35页
2015/7/20
其它表达方式？
练习
 关系：图书(书号,书名,作者,出版社,单价）






第36页
2015/7/20
BOOK(Bno, Bname, Author, Press, Price)
查询“数据库”一书的书号和单价
查询单价在20至50元之间的图书信息
查询北京某出版社出版的图书信息
查询作者是张一，王二，刘三的书的信息
查询所有图书的书号，书名和单价信息
查询缺少出版社信息的图书的书号和书名
三、对查询结果排序

ORDER BY 子句 可对查询结果按照一个或多个属性列的
升序（ASC）或降序（DESC）排列，缺省值为升序
例 查询选修了3号课程的学生的学号及其成绩，查询结果按
分数的降序排列
SELECT Sno, Grade FROM SC
WHERE Cno = ‘3’ ORDER BY Grade DESC;
例 查询全体学生情况，查询结果按所在系的系号升序排列，
同一系中的学生按年龄降序排列（见mdb示例）
SELECT * FROM Student
ORDER BY Sdept, Sage DESC;
第37页
2015/7/20
四、使用集函数
COUNT([DISTINCT|ALL]*)
统计元组个数
COUNT([DISTINCT|ALL]<列名>) 统计一列中值的个数
SUM([DISTINCT|ALL]<列名>)
计算一列值的总和
AVG([DISTINCT|ALL]<列名>)
计算一列值的平均值
MAX([DISTINCT|ALL]<列名>)
求一列值中的最大值
MIN([DISTINCT|ALL]<列名>)
求一列值中的最小值
第38页
2015/7/20
实例
例 查询学生总人数
SELECT COUNT(*)
FROM Student;
例 计算1号课程的学生平均成绩
例 查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno)
FROM SC;
SELECT AVG(Grade)
FROM SC
WHERE Cno = ‘1’;
例 查询选修1号课程的学生最高分数
第39页
2015/7/20
SELECT MAX(Grade)
FROM SC
WHERE Cno = ‘1’;
五、对查询结果分组
子句将查询结果按某一列或多
列分组，值相等的为一组。
 对查询结果分组的目的是为了细化集函数
的作用对象，分组后每个组都有一个函数
值
 如果分组后还要求按一定的条件对这些组
进行筛选，最终只输出满足指定条件的组，
则可以是使用HAVING短语指定筛选条件
 GROUP BY
第40页
2015/7/20
分组实例
例 求各个课程号及相应的选课人数
SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;
例 查询选修了3门以上课程的学生学号
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;
第41页
2015/7/20
练习
数据库模式如下：



仓库(仓库号，负责人)
货物(货物号，货物名，货物描述)
入库记录(货物号，仓库号，数量，入库日期，经办人号)
用SQL实现以下查询要求：





第42页
2015/7/20
查询仓库中有多少种不同的货物
查询每天入库数量的最大值
统计2003年3月20日以后每天入库的货物总数量
按货物号统计每一种货物在仓库中的总数量
查询“A-001”号经办人当日经办的入库记录数
SELECT COUNT(DISTINCT 货物号) FROM 入库记录
GROUP BY 仓库号；
SELECT 入库日期, MAX(数量) FROM 入库记录
GROUP BY 入库日期；
SELECT入库日期, SUM(数量) FROM 入库记录
GROUP BY 入库日期 HAVING 入库日期 > “2003/03/20”；
SELECT 货物号，SUM(数量) FROM 入库记录
GROUP BY 货物号
SELECT 入库日期 COUNT(*) FROM 入库记录
WHERE 经办人号 = “A-001”
GROUP BY 入库日期;
第43页
2015/7/20
 Where与Having的区别


第44页
2015/7/20
Where作用于基本表，从中选出符合条件的行
Having作用于组，从中选出符合条件的组
3.3.2 连接查询

连接(Join)查询



连接查询的类型






第45页
2015/7/20
查询涉及两个以上的表(在FROM子句中体现)
From 子句 对应于关系代数中笛卡儿乘积运算, 它
给出待扫描的关系/表(指定多个表)，给出连接条件
等值连接查询
非等值连接查询
自然连接查询
自身连接查询
外连接查询
符合条件连接查询
一、等值与非等值连接查询

连接条件的形式



连接查询的执行过程

第46页
2015/7/20
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
 比较运算符: = (等值连接)、>、<、>=、<=、!=
 连接字段必须是可比的
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2>
AND [<表名2>.]<列名3>
首先在表1中找到第一个元组，然后从头开始扫描表2，
逐一查找满足连接条件的元组，找到后就将表1中的第
一个元组与该元组拼接起来，形成结果表中的一个元组。
表2全部查找完后，再找表1中第二个元组，然后再从头
开始扫描表2。重复上述操作，直至表1中的全部元组都
处理完毕为止。
例子

例32 查询每个学生及其选修课程的情况
学生情况存放Student表中，学生选课情况存放在SC
表中，所以本查询实际上涉及Student与SC两个表。通
过公共属性Sno实现联系
表名前缀
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;
第47页
2015/7/20
连接谓词
等值连接
使用表名前缀
为避免混淆，
若属性名在各
表中唯一，则
可省略
自然连接和广义笛卡尔积连接

自然连接



等值连接的特例
在等值连接的基础上将目标列中重复的属性去掉
广义笛卡尔积连接


不带连接条件的连接
结果是两表中元组的交叉乘积
例 查询每个学生及其选修课程的情况
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC WHERE Student.Sno = SC.Sno;
第48页
2015/7/20
SELECT Student.*, SC.* FROM Student, SC;
二、自身连接
 一个表与其自己进行连接
例 查询每一门课的间接先修课
别名
SELECT FIRST.Cno, SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;
第49页
2015/7/20
见P104表
三、外连接
是连接运算的扩充
 除了满足连接条件的元组正常输出外，所有不满
足连接条件的元组也进行连接，并用空值NULL来
填充并输出
 外连接符：*（或+），表示增加一个万能行
 外连接分类：右外连接、左外连接、全外连接

例 查询每个学生及其选修课程的情况
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC
WHERE Student.Sno = SC.Sno(*);
第50页
2015/7/20
四、复合条件连接

WHERE子句中有多个连接条件
例 查询选修了2号课程且成绩在90分以上的学生学号和姓名
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno
AND SC.Cno = ‘2’
AND SC.Grade > 90;
第51页
2015/7/20
多表连接

两个以上的表的连接
例 查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno, Sname, Cname
FROM Student, Course, SC
WHERE Student.Sno = SC.Sno
AND SC.Cno = Course.Cno;
第52页
2015/7/20
3.3.3 嵌套查询
在SQL语言中，一个SELECT-FROM-WHERE语
句称为一个查询块。
 一个查询块嵌套在另一个查询块的WHERE子句或
HAVING 短语的条件中的查询。

如
第53页
2015/7/20
SELECT Sname FROM Student
WHERE Sno IN
(SELECT Sno FROM SC
WHERE Cno = ‘2’);
其等效不嵌套的查询语句?
嵌套查询
允许多层嵌套，体现了SQL的结构化特色
 术语






父查询
子查询
不相关子查询：子查询的查询条件不依赖于父查询
相关子查询：子查询的查询条件依赖于父查询的某
个属性值
求解方法

由里向外，子查询的结果用于建立其父查询的查找
条件
ORDER BY 只对最终结果排序有意义，子
查询不能使用
★
第54页
2015/7/20
一、带有IN谓词的子查询
 嵌套查询中，子查询的结果往往是一个
集合，所以经常使用谓词IN
例 查询与“刘晨”在同一个系学习的学生
– 先分步实现此查询
– 使用嵌套实现此查询
(1) SELECT Sdept
FROM student
WHERE Sname = ‘刘晨’
结果为 ‘IS’
(2) SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept=‘IS’；
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname = ‘刘晨’);
第55页
2015/7/20
（不相关子查询）
例子
例 查询选修了课程名为“信息系统”的学
生学号和姓名
SELECT Sno, Sname
(3)
FROM Student
使用嵌套查询实现
WHERE Sno IN
(SELECT Sno
(2)
FROM SC
（不相关子查询）
WHERE Cno IN
(SELECT Cno
(1)
FROM Course
WHERE Cname=‘信息系统’));
使用连接查询实现
★ 多种实现方式，
嵌套查询具有结构化
程序设计特点
第56页
2015/7/20
SELECT Sno, Sname
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND
SC.Cno = Course.Cno AND
Course.Cname = ‘信息系统’;
二、带有比较运算符的子查询
当用户能确切知道内层查询返回的是单值时，
可以使用比较运算符(>,<,>=,<=,!=等)
 注意：子查询一定要跟在比较运算符之后

例 查询与“刘晨”在同一个系学习的学生
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept =
(SELECT Sdept
FROM Student
WHERE Sname = ‘刘晨’);
第57页
2015/7/20
（不相关子查询）
三、带有ANY或ALL谓词的子查询
 与比较运算符(>,<,>=,<=,!=等)配合使用
第58页
2015/7/20

>ANY(ALL) 大于子查询结果中的某个(所有)值

<ANY(ALL) 小于子查询结果中的某个(所有)值

>=ANY(ALL) 大于等于子查询结果中的某个(所有)值

<=ANY(ALL) 小于等于子查询结果中的某个(所有)值

=ANY(ALL) 等于子查询结果中的某个(所有)值

!=ANY(ALL) 不等于子查询结果中的某个(任何)值
例1
例 查询其他系中比信息系某一学生年龄小的学
生姓名和年龄
也可以用集函数来实现
SELECT Sname, Sage
FROM Student
WHERE Sage < ANY
(SELECT Sage
FROM Student
WHERE Sdept = ‘IS’)
AND Sdept <> ‘IS’;
第59页
2015/7/20
SELECT Sname, Sage
FROM Student
WHERE Sage <
(SELECT MAX(Sage)
FROM Student
WHERE Sdept = ‘IS’)
AND Sdept <> ‘IS’;
例2
例 查询其他系中比信息系所有学生年龄都小的
学生姓名和年龄
也可以用集函数来实现
SELECT Sname, Sage
FROM Student
WHERE Sage < ALL
(SELECT Sage
FROM Student
WHERE Sdept = ‘IS’)
AND Sdept <> ‘IS’;
第60页
2015/7/20
SELECT Sname, Sage
FROM Student
WHERE Sage <
(SELECT MIN(Sage)
FROM Student
WHERE Sdept = ‘IS’)
AND Sdept <> ‘IS’;
转换关系
 事实上，用集函数实现子查询通常比直接
用ANY或ALL查询效率要高
 ANY、ALL与集函数及IN的等价转换关系
=
ANY
ALL
第61页
2015/7/20
！=
IN
NOT IN
<
<=
>
>=
<MAX <=MAX
>MIN
>=MIN
<MIN
>MAX >=MAX
<=MIN
四、带有EXISTS谓词的子查询
 不需要返回具体数据，而只关心是否有返回
值，即返回逻辑真或假
例 查询所有选修了一号课程的学生姓名
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT * FROM SC
WHERE Sno = Student.Sno AND Cno = ‘1’);
第62页
2015/7/20
（相关子查询）
例子
例 查询没有选修一号课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno = ‘1’);
（相关子查询）
第63页
2015/7/20
替换关系

一些带EXISTS或NOT EXISTS谓词的子查询不能
被其他形式的子查询替换，但所有带IN谓词、比
较运算符、ANY和ALL谓词的子查询都能用
EXISTS谓词等价替换
例 查询与“刘晨”在同一个系学习的学生
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname = ‘刘
晨’);
第64页
2015/7/20
SELECT Sno, Sname, Sdept
FROM Student S1
WHERE EXISTS
(SELECT *
FROM Student S2
WHERE S2.Sdept = S1.Sdept
AND S2.Sname = ‘刘晨’);
全称量词
SQL语言中没有全称量词 (for all)，但可使用存在量词

进行转换后实现： (x) P  (x(P))

x表示课程，
P表示谓词“选
修”
例 查询选修了全部课程的学生姓名
（可理解为： 查询没有一门课程不选修的学生姓名）
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT * FROM SC
WHERE Sno = Student.Sno
AND Cno = Course.Cno));
第65页
2015/7/20
逻辑蕴函


SQL语言中的逻辑运算包括非(NOT)、与(AND)、或(OR)
而常用的逻辑运算还包括异或、等价、蕴涵等，SQL语言
中没有逻辑蕴函(Implication)运算，但可利用谓词演算转
换一个逻辑蕴函：
P  Q  P  Q

第66页
2015/7/20
在蕴涵运 算 PQ中 ，只有当 前提为 True并且结果 为
False的情况下逻辑蕴涵的结果为False，其余的情况下
逻辑蕴涵的结果均为True。逻辑蕴涵运算的条件和结果：
P
Q
结果
True
True
True
True
False
False
False
True
True
False
False
True
例子
查询至少选修了学生95002号选修的全部课程的学生学号
该查询的逻辑蕴涵：查询学号为x的学生，对所有的课程
y，只要学生95002号选修了y，则学生x也选修了y。
该谓词用
p表示
该查询有如下等价形式：
(y ) p  q
 (y (( p  q ))
该谓词
用q表示
 (y ((p  q )))
 y ( p  q )
其语义为：不存在这样的课程y ，学生95002号选修了，
而学生x 没选
第67页
2015/7/20
例 查询至少选修了学生95002号选修的全部课
程的学生学号
例子
根据上述谓词演算表达式“不存在这样的课程y ，学生
95002号选修了，而学生x 没选”，易得如下SQL语句：
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
(SELECT * FROM SC SCY
WHERE SCY.Sno = ‘95002’
AND NOT EXISTS
(SELECT * FROM SC SCZ
WHERE SCZ.Sno = SCX.Sno
AND SCZ.Cno = SCY.Cno));
第68页
2015/7/20
95002选修的课
选课情
况
学生x
课堂练习
 检索选修了数据库原理并且成绩比张三高的
所有学生的姓名和成绩。
 检索选修了离散数学、数据结构，数据库原
理的学生姓名。
 检索没有不及格课程的学生姓名。
第69页
2015/7/20
3.3.4 集合查询
SELECT 语句的查询结果是元组的集合，所以多
个SELECT语句的查询结果可进行集合操作
 集合操作运算符


并UNION, 交INTERSECT, 差MINUS(EXCEPT)
例 查询计算机科学系的学生及年龄不大于19岁的学生
第70页
2015/7/20
SELECT * FROM Student
WHERE Sdept = ‘CS’
UNION
SELECT * FROM Student
WHERE Sage <= 19；
例子
查询有贷款帐户或有存款帐户的客户:
(select customer-name from depositor)
union
(select customer-name from borrower);
 查询同时有贷款帐户和存款帐户的客户:
(select customer-name from depositor)
intersect
(select customer-name from borrower);
 查询有存款帐户但没有贷款帐户的客户:
(select customer-name from depositor)
except
(select customer-name from borrower);

第71页
2015/7/20
说明
UNION将多个查询结果合并，并自动去掉重复元
组。但系统要求参加UNION操作的各结果表的列
数和对应数据类型必须相同
 标准SQL中没有直接提供INTERSECT和
EXCEPT操作，但可以用其他方法来实现

例 查询计算机系的学生与年龄不大于19岁的学生的交集
即：查询计算机系中年龄不大于19岁的学生
SELECT * FROM Student
WHERE Sdept=‘CS’ AND Sage<=19;
第72页
2015/7/20
转换例子
例 查询选修课程1的学生集合与选修课程2的学生集合的交集
即：查询既选修了课程1又选修了课程2的学生
SELECT Sno FROM SC
WHERE Cno=‘1’ AND Sno IN
(SELECT Sno FROM SC
WHERE Cno=‘2’);
例 查询计算机系的学生与年龄不大于19岁的学生的差集
即：查询计算机系中年龄大于19岁的学生
第73页
2015/7/20
SELECT *
FROM Student
WHERE Sdept=‘CS’ AND Sage>19;
例子


查询同时有贷款帐户和存款帐户的客户:
select customer-name
from depositor, borrow
where customer.cno = borrower.cno;
或 select customer-name
from depositor
where customer.cno in (select cno from borrower);
查询有存款帐户但没有贷款帐户的客户:
select customer-name
from depositor
where customer.cno not in (select cno from
borrower);
第74页
2015/7/20
3.4 数据更新
插入数据
修改数据
删除数据
第75页
2015/7/20
3.4.1 插入数据
一、插入单个元组
INSERT
INTO <表名> [(<属性列1>[,<属性列2>…])]
VALUES(<常量1>[,<常量2>]…)；


第76页
2015/7/20
INTO子句中没有出现的属性列，新记录在该列
上将取空值，但注意说明了NOT NULL的列不能
取空值
如果INTO子句中没有指明任何列名，则新记录
的每个属性列必须有值
例 插入一条选课记录（‘95020’，‘1’）
INSERT INTO SC(Sno, Cno)
VALUES(‘95002’, ‘1’);
例子
新插入的记录Grade取空值
例 将一个新学生记录插入到Student表中
INSERT INTO Student (Sno,
Sname, Ssex)
VALUES(‘95002’, ‘陈冬’, ‘男’);
没有赋值的属性取空值
例 将一个新学生记录插入到Student表中
INSERT INTO Student
VALUES(‘95002’, ‘陈冬’, ‘男’, ‘IS’, 18);
第77页
2015/7/20
二、插入子查询结果
 (可能是一组元组)
INSERT INTO <表名> [(<属性列1>[,<属性列2>…])]
子查询；
例 对每一个系，求学生的平均年龄，并把结果存入数据库
先建立一个新表，存放系名和学生平均年龄，然后插入数据
CREATE TABLE Deptage
(Sdept CHAR (15),
Avgage SMALLINT);
第78页
2015/7/20
INSERT INTO Deptage(Sdept, Average)
SELECT Sdept, AVG(Sage)
FROM Student GROUP BY Sdept;
3.4.2 修改数据
 修改一个或一组元组的值
UPDATE <表名>
SET <列名> = <表达式>[，<列名> = <表达式
>]…
[WHERE <条件>]；
一、修改某一个元组的值
例 将学生95001的年龄改成22岁
UPDATE Student
SET Sage = 22
WHERE Sno = ‘95001’;
第79页
2015/7/20
修改数据
二、修改多个元组的值
例 将所有学生的年龄增加1岁
UPDATE Student
SET Sage = Sage + 1;
三、带子查询的修改语句
例 将计算机系全体学生的成绩置零
第80页
2015/7/20
UPDATE SC
SET Grade = 0
WHERE ‘CS’ =
(SELECT Sdept FROM Student
WHERE Student.Sno = SC.Sno);
3.4.3 删除数据
 删除一个或多个元组
DELETE
FROM <表名>
[WHERE <条件>]；
一、删除某一个元组的值
例 删除学号为95019的学生记录
DELETE FROM Student
WHERE Sno = ‘95019’;
第81页
2015/7/20
删除数据
二、删除多个元组的值
例 删除所有的学生选课记录
DELETE FROM SC;
三、带子查询的删除语句
例 删除计算机系所有学生的选课记录
第82页
2015/7/20
DELETE FROM SC
WHERE ‘CS’ =
(SELETE Sdept
FROM Student
WHERE Student.Sno = SC.Sno);
3.5 视图
 视图



用户看待数据的多种角度性可借用视图来实现，
从而可以隐藏部分信息
视图是从一个或几个基本表或视图导出的表。
数据库中只存放视图定义，不存放相应数据，
数据仍存放在原表中
视图经定义后，即可和表一样使用
 视图操作


第83页
2015/7/20
定义视图、查询视图、删除视图
更新视图（有一定限制，并非所有视图都可更
新）
3.5.1 定义视图
CREATE VIEW <视图名> [(<列名>[，<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];




第84页
2015/7/20
子查询中通常不含ORDER BY和DISTINCT短语
WITH CHECK OPTION表示对视图进行更新操作的行须
满足子查询中的条件表达式
组成视图的属性列名或者全部指定或者全部省略，但下
列三种情况时必须指定列名
 目标列是集函数或列表达式、多表连接时出现的同名
列、启用新列名
属性列名可以是虚拟列(基表中没有的列)，其值由表达式
计算得到
例子
例 建立信息系学生的视图
省略对目标
列名的指定
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage CREATE VIEW IS_Student
AS
FROM Student
SELECT Sno, Sname, Sage
WHERE Sdept = ‘IS’;
FROM Student
WHERE Sdept = ‘IS’
WITH CHECK OPTION;
WITH CHECK OPTION表示对视图进行更新操作时，
DBMS会自动加上条件Sdept = ‘IS’
★ 以上为行列子集视图：从单个基表导出的，只是去掉了
第85页
2015/7/20
某些行和列，但保留了码的视图
从多个基表导出的视图
例 建立信息系选修了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’;
第86页
2015/7/20
带表达式的视图
如：建立关于学生学号、姓名和出生年份的视图
CREATE VIEW BT_S(Sno, Sname, Sbirth) AS
SELECT Sno, Sname, 2002-Sage
FROM Student;
如：建立关于学生学号及其平均成绩的视图
CREATE VIEW S_G(Sno, Gavg) AS
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno;
第87页
2015/7/20
虚拟列
目标列是列表
达式，需指定
目标列名
虚拟列
目标列是集函数，
需指定目标列名
从其它视图中导出视图
如：建立信息系选修了1号课程且成绩在90分以上
的学生视图
CREATE VIEW IS_S2 AS
SELECT Sno, Sname, Grade
FROM IS_S1
WHERE Grade >= 90;
其中：
第88页
2015/7/20
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’;
说明
 CREATE

VIEW语句的执行结果
DBMS执行CREATE VIEW语句的结果只是把视
图的定义存入数据字典，并不执行其中的
SELECT语句。

只有在对视图进行查询时，才按视图的定义从
基表中将数据查出
第89页
2015/7/20
删除视图
DROP VIEW <视图名>;
例
DROP VIEW IS_S1;
DROP VIEW IS_Student;
 基表(或视图)被删除后，建立在其基础上的
视图也需显式地一一删除
第90页
2015/7/20
3.5.2 查询视图

首先进行视图有效性检查，检查查询的表、视
图等是否存在。如果存在，则结合视图定义中
的子查询和用户查询，将视图查询转换成为对
基表的查询并执行。这一过程又称为视图消解
例 查询信息系年龄小于20岁的学生
SELECT Sno, Sage
FROM IS_Student
WHERE Sage < 20;
第91页
2015/7/20
视图消解
SELECT Sno, Sage
FROM Student
WHERE Sdept = ‘IS’
AND Sage < 20;
例子
例 查询平均成绩大于等于90的学生学号和平均成绩
SELECT *
FROM S_G
WHERE Gavg >= 90;
正确的对
应查询是
SELECT Sno, AVG(Grade)
FROM SC
X
视图消解 WHERE AVG(Grade) >= 90
GROUP BY Sno;
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 90;
★ 目前，多数关系数据库系统对于行列子集视图的查询
第92页
2015/7/20
都能进行正确的转换，但对非行列子集视图的查询就
不一定能做转换了，因此这类查询应直接对基表进行
3.5.3更新视图
通过视图来插入、删除和修改数据
 由于有些视图的更新不能唯一地有意义地转换
成相应基表的更新，因此目前关系数据库系统
一般只允许对行列子集视图进行更新

例 将视图 IS_Student 中学号为95002的学生姓名改为“刘辰”
UPDATE IS_Student
SET Sname = ‘刘辰’
WHERE Sno = ‘95002’;
第93页
2015/7/20
转换为
UPDATE Student
SET Sname = ‘刘辰’
WHERE Sno = ‘95002’
AND Sdept = ‘IS’;
例子
例 删除视图 IS_Student 中学号为95002的
学生的信息
DELETE
FROM IS_Student
WHERE Sno = ‘95029’;
第94页
2015/7/20
转换为
DELETE
FROM Student
WHERE Sno = ‘95029’
AND Sdept = ‘IS’;
3.5.4视图的作用
 1.视图能够简化用户的操作
 2.视图使用户能从多种角度看待同一数据
 3.视图对重构数据库提供了一定程度的逻辑
独立性
 4.视图能够对机密数据提供安全保护
 5.适当地利用视图可以更清晰的表达查询
第四版新增
第95页
2015/7/20
作业
P127
3、4、5、11