Transcript Chapter 4: SQL

Chapter 3: SQL
Database System Concepts, 5th Ed.
©Silberschatz, Korth and Sudarshan
See www.db-book.com for conditions on re-use
Chapter 3: SQL
 Data Definition（database/table/index/key/view）
（数据定义语言）
 Data Manipulation
(数据操纵语言)
 Basic Query Structure (select/from/where)
（基本查询结构）
 Set Operations
 Aggregate Functions
 Nested Subqueries
 Complex Queries
 Views
 Modification of the Database
 Joined Relations**
Database System Concepts, 5th Edition, Oct 5, 2006
3.2
©Silberschatz, Korth and Sudarshan
History（SQL 语言的发展历史）
 IBM Sequel language developed as part of System R project at the
IBM San Jose Research Laboratory
 Renamed Structured Query Language (SQL)
 ANSI and ISO standard SQL:

SQL-86

SQL-89

SQL-92

SQL:1999 (language name became Y2K compliant!)

SQL:2003
 Commercial systems offer most, if not all, SQL-92 features, plus
varying feature sets from later standards and special proprietary
features.

Not all examples here may work on your particular system.
Database System Concepts, 5th Edition, Oct 5, 2006
3.3
©Silberschatz, Korth and Sudarshan
SQL 命令类型
 Data Definition Language(数据定义语言)

CREATE、DROP和ALTER
 Data Manipulation Language(数据操纵语言)

SELECT、INSERT、UPDATE和DELETE命令
 Data Control Language (数据控制语言)

GRANT、REVOKE等
Database System Concepts, 5th Edition, Oct 5, 2006
3.4
©Silberschatz, Korth and Sudarshan
SQL 语言的约定
SQL对象命名约定
SQL对象包括数据库、表、视图、属性名等。这些对象名必须符合一定规
则或约定，一般应遵守下列规则。

数据对象名可以为1-30个字符（在MS Access为64个字符），但有些
DBMS限制为8个字符，例如：Oracle数据库就是如此。

数据对象名应以字母开头，其余字符可以由字母、数字、下划线组成
。
SQL语句结构和书写准则
 在SQL语句语法格式中的一些约定符号：

尖括号“<>”中的内容为实际语义。

中括号“[ ]”中的内容为任选项。

[，. . .]意思是“等等”，即前面的项可以重复。
Database System Concepts, 5th Edition, Oct 5, 2006
3.5
©Silberschatz, Korth and Sudarshan

大括号“{}”与竖线“|”表明此处为选择项，在所列出的各项中仅需
选择一项。
例如：{ A | B | C | D }意思是A、B、C、D中取其一。


SQL中的数据项（包括列项、表和视图）分隔符为“，”；其字符串常
数的定界符用单引号“′”表示。
在编写SQL语句时，遵从某种准则以提高语句的可读性，使其易于编辑，是
很有好处的。
以下是一些通常的准则：

SQL语句对大小写不敏感
为了提高SQL语句的可读性，子句开头的关键字通常采用大写形式。

SQL语句可写成一行或多行，习惯上每个子句占用一行。

关键字不能在行与行之间分开，并且很少采用缩写形式。

SQL语句的结束符为分号“；”，分号必须放在语句中的最后一个子句
后面，但可以不在同一行。
Database System Concepts, 5th Edition, Oct 5, 2006
3.6
©Silberschatz, Korth and Sudarshan
Data Definition Language
（数据定义语言）
Allows the specification of not only a set of relations but also
information about each relation, including:
 The schema for each relation.
 The domain of values associated with each attribute.
 Integrity constraints
 The set of indices to be maintained for each relations.
 Security and authorization information for each relation.
 The physical storage structure of each relation on disk.
Database System Concepts, 5th Edition, Oct 5, 2006
3.7
©Silberschatz, Korth and Sudarshan
SQL语言的数据定义功能
SQL语言的数据定义功能包括定义数据库、定义基本表、定义索引
和定义视图。其基本语句如表所示。
SQL的数据定义语句
操作方式
操作对象
创建语句
删除语句
修改语句
数据库
CREATE
DATABASE
DROP
DATABASE
ALTER
DATABASE
基本表
CREATE
TABLE
DROP
TABLE
ALTER
TABLE
索引
CREATE
INDEX
DROP
INDEX
视图
CREATE VIEW
Database System Concepts, 5th Edition, Oct 5, 2006
DROP VIEW
3.8
©Silberschatz, Korth and Sudarshan
数据库的创建与删除

创建数据库
SQL使用命令CREATE DATABASE创建数据库，其一般语法如下：
CREATE
DATABASE
<数据库名>；
【例】创建一个简单数据库。
CREATE DATABASE

MyDb；
删除数据库
SQL使用命令DROP DATABASE删除一个或多个数据库，其一般语法如下：
DROP
DATABASE
<数据库名1> [，<数据库名2>][,…]；
【例】删除数据库MyDb。
DROP DATABASE MyDb；
Database System Concepts, 5th Edition, Oct 5, 2006
3.9
©Silberschatz, Korth and Sudarshan
数据库中的基本对象

数据库是由许多表组成的

基本表是独立存储在数据库中的表
在SQL中一个关系对应一个基本表，一个（或多个）基本表
对应一个存储文件，基本表对应的数据必须在数据库中存放。
存储文件的物理结构对用户而言是透明的，用户无需关心。
一个基本表可以根据需要带一个或多个索引，索引也存放
在存储文件中。

视图是由一个或几个基本表导出的，它的外部形式也
是一个表，是由基本表中选取的行和列组成的
视图本身不独立，它依附于基本表，在数据库中不存放视
图对应的数据，而只存放关于视图的定义，因此视图只是一个
虚表。
Database System Concepts, 5th Edition, Oct 5, 2006
3.10
©Silberschatz, Korth and Sudarshan
Create Table Construct
（创建表结构）
 An SQL relation is defined using the create table command:
create table r (A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1),
...,
(integrity-constraintk))
 r is the name of the relation
 each Ai is an attribute name in the schema of relation r
 Di is the data type of values in the domain of attribute Ai
 Example:
create table branch
(branch_name char(15) not null,
branch_city
char(30),
assets
integer)
Database System Concepts, 5th Edition, Oct 5, 2006
3.11
©Silberschatz, Korth and Sudarshan
创建基本表
创建基本表的结构是建立数据库最重要的一步，其一般语法如下：
CREATE TABLE <表名>
（<列名> <数据类型>[<列级完整性约束条件>]
[，<列名> <数据类型>[<列级完整性约束条件>]][,…]
[,<表级完整性约束条件>][,…])；
说明：
<表名>是指要创建的基本表的名称，该名称应符合具体DBMS的标识符的
命名规则。<列名>指的是表的属性名称。
Database System Concepts, 5th Edition, Oct 5, 2006
3.12
©Silberschatz, Korth and Sudarshan
Domain Types in SQL
(数据类型)
 char(n). Fixed length character string, with user-specified length n.
 varchar(n). Variable length character strings, with user-specified maximum




length n.
int. Integer (a finite subset of the integers that is machine-dependent).
smallint. Small integer (a machine-dependent subset of the integer
domain type).
numeric(p,d). Fixed point number, with user-specified precision of p digits,
with n digits to the right of decimal point.
real, double precision. Floating point and double-precision floating point
numbers, with machine-dependent precision.
 float(n). Floating point number, with user-specified precision of at least n
digits.
 More are covered in Chapter 4.
Database System Concepts, 5th Edition, Oct 5, 2006
3.13
©Silberschatz, Korth and Sudarshan
定义表的各个属性时应指出其相应的数据类型和长度，不同DBMS支持
的数据类型不完全相同，表3-2列出的是IBM DB2 SQL支持的主要数据类
型：
类型表示
类型说明
SMALLINT
半字长二进制整数
INT或INTEGER
全字长二进制整数
DECIMAL(p[,q])
十进制数，共p位（含小数点），其中小数点后q位
FLOAT
双字长浮点数
字
符
型
CHAR(n)或CHARTER(n)
长度为n的定长字符串,如果省略n,字符串长度被假定为1
VARCHAR (n)
最大长度为n的可变长字符串
日
期
时
间
型
DATE
日期型，格式为YYYY-MM-DD
TIME
时间型，格式为HH.MM.SS
TIMESTAMP
日期加时间
数
值
型
Database System Concepts, 5th Edition, Oct 5, 2006
3.14
©Silberschatz, Korth and Sudarshan
Integrity Constraints in Create Table
（完整性约束）
 not null
 primary key (A1, ..., An )
Example: Declare branch_name as the primary key for branch
.
create table branch
(branch_name char(15),
branch_city char(30),
assets
integer,
primary key (branch_name))
primary key declaration on an attribute automatically ensures
not null in SQL-92 onwards, needs to be explicitly stated in
SQL-89
Database System Concepts, 5th Edition, Oct 5, 2006
3.15
©Silberschatz, Korth and Sudarshan
 关于完整性约束条件，需要说明以下几点：

完整性约束条件分为列级完整性约束条件和表级完整性约束条件，它们之间的
区别在于：列级完整性约束条件只能用于列，而表级完整性约束条件能够用于
一张表中的多列。

SQL的完整性约束条件有以下几种：

NOT NULL 或NULL约束
这个约束条件为列级完整性约束条件。NOT NULL 为不允许该
列存在空值，而NULL 为允许该列存在空值。

UNIQUE约束
UNIQUE约束是惟一性约束。即不允许表中的某一列或者某几列
有重复的属性值。

DEFAULT约束
DEFAULT约束为默认值约束，是列级完整性约束条件。当向表
中插入一个新行时，如果对于特定列没有指定数值，则使用
DEFAULT子句指定的默认值。
Database System Concepts, 5th Edition, Oct 5, 2006
3.16
©Silberschatz, Korth and Sudarshan
 CHECK约束
CHECK约束是检验约束，为插入列中的数据指定约束条件。
 PRIMARY
KEY 约束
PRIMARY KEY 约束即主键约束，是表级完整性约束条件。表
中的主键可以是一列或列组，PRIMARY KEY 约束可以使得主键的
数值在每一行中各不相同。
注意：PRIMARY KEY约束类似于UNIQUE约束，差别在于PRIMARY
KEY约束中的列不可以为空。
 FOREIGN
KEY约束
FOREIGN KEY约束是参照完整性约束，是用于约束外键的，也
是表级完整性约束条件。
Database System Concepts, 5th Edition, Oct 5, 2006
3.17
©Silberschatz, Korth and Sudarshan
Drop and Alter Table Constructs
（删除和修改表结构）
 The drop table command deletes all information about the dropped
relation from the database.
 The alter table command is used to add attributes to an existing relation:
alter table r add A D
where A is the name of the attribute to be added to relation r and D is the
domain of A.

All tuples in the relation are assigned null as the value for the new
attribute.
 The alter table command can also be used to drop attributes of a relation:
alter table r drop A
where A is the name of an attribute of relation r

Dropping of attributes not supported by many databases
Database System Concepts, 5th Edition, Oct 5, 2006
3.18
©Silberschatz, Korth and Sudarshan
【例1】创建“学生表”student，它由学号id、姓名name、
性别sex、班级号class、出生日期birthday等5个属性组成。
其中学号不能为空，值必须惟一，且姓名也必须惟一和非空
。
CREATE TABLE student
UNIQUE,
( id CHAR(8) NOT NULL
name VARCHAR(20) NOT NULL UNIQUE,
sex CHAR(1) NOT NULL,
class CHAR(4), birthday DATE);
Database System Concepts, 5th Edition, Oct 5, 2006
3.19
©Silberschatz, Korth and Sudarshan
【例2】创建“班级信息表”class，它由班级号id、班级名
name、班长monitor等3个属性组成，其中班级号是主键，班
长是外键，它是学生表中学号的某个值。
CREATE TABLE class
( id CHAR(4) NOT NULL ,
name VARCHAR(50) NOT NULL ,
monitor CHAR(8));
Database System Concepts, 5th Edition, Oct 5, 2006
3.20
©Silberschatz, Korth and Sudarshan
 删除基本表
当某个基本表不再需要时，可以使用DROP
语句将它删除。其一般语法为：
DROP TABLE
TABLE
<表名>；
【例3】删除student表。
DROP TABLE student；
该语句一旦执行，基本表的定义、数据、此表上建
立的索引和视图都将自动被删除掉。
Database System Concepts, 5th Edition, Oct 5, 2006
3.21
©Silberschatz, Korth and Sudarshan
Alter Table Constructs
（修改表结构）
SQL语言用ALTER TABLE命令修改基本表，其基本语法为：
ALTER TABLE <表名>
[ADD (<新列名><数据类型>[完整性约束] [,…])]
[DROP <完整性约束名>]
[MODIFY (<列名> <数据类型>[,…])]；
基本表的修改分3种情况：
 使用ADD子句增加新列和新的完整性约束条件
【例4】在student表中增加“籍贯native_place”列，数据类型为字符型。
ALTER TABLE student
ADD native_place VARCHAR(50)；
Database System Concepts, 5th Edition, Oct 5, 2006
3.22
©Silberschatz, Korth and Sudarshan
 使用DROP子句删除指定的完整性约束条件
【例5】 删除student表学生姓名必须取惟一值的约束条件。
ALTER TABLE student
DROP UNIQUE(name)；
 使用MODIFY子句修改基本表的列定义
【例6】 将student 表name列的数据类型改为定长字符串型。
ALTER TABLE student
MODIFY name char(8) NOT NULL；
注意：
(1)修改列定义时，要将原来的列级约束条件写上，否则原有的列
级约束会不起作用。
(2)修改列定义时，有可能会破坏已有的数据，应事先作好备份工
作。
(3)SQL未提供删除属性列的语句，只能采取间接的方法。
Database System Concepts, 5th Edition, Oct 5, 2006
3.23
©Silberschatz, Korth and Sudarshan
建立索引
索引的概念
索引是建立在列上的一种数据库对象，它对表中的数据提供逻辑顺
序，当在数据库表中搜索某一行时，可以通过使用索引来找到它的物理
位置。索引建立后，什么时候使用索引以及使用哪一个索引（当有多个
索引存在时），由DBMS内部根据情况自行决定，不需要人员干预。索引
是动态的，每当数据库表的数据更新一次，相应的索引也随之更新。
Database System Concepts, 5th Edition, Oct 5, 2006
3.24
©Silberschatz, Korth and Sudarshan

建立索引
在SQL语言中，建立索引使用CREATE INDEX命令，其一般语法为：
CREATE [UNIQUE] [CLUSTER] INDEX<索引名>
ON <表名>(<列名>[<次序>][，<列名>[<次序>]][,…])；
说明：
(1)表名是要建立索引的基本表的名字。
(2)列名是被建立索引的列的名称。索引可以建立在某一列或多个列上。
(3)次序是指按照该列名的索引值的排列顺序。次序可以取值ASC（升序）
或DESC（降序），默认值是ASC。
(4)UNIQUE表示创建的索引是惟一索引，索引列上的数据不能有重复值。
(5)CLUSTER表示要建立的是聚簇索引。聚簇索引是指索引项的顺序与表中
记录的物理顺序一致的索引。
Database System Concepts, 5th Edition, Oct 5, 2006
3.25
©Silberschatz, Korth and Sudarshan
【例8】为学生表student和班级信息表class建立索引。其中，student表按
学号id升序建立惟一索引，class表按班级号降序建立惟一索引。
CREATE UNIQUE INDEX stuid_ind
ON student(id ASC)；
CREATE UNIQUE INDEX claid_ind
ON class(id DESC)；
【例9】为表student在姓名name列上建立一个聚簇索引。
CREATE CLUSTER INDEX stu_cluind ON student(name)；
该语句执行后，student表中记录的物理顺序将按照name列值的升序存
放。一个基本表只能建立一个聚簇索引，因为表中的数据不能以多种方式
进行物理排序。可以在经常查询的列上建立聚簇索引，以提高查询效率。
但对于经常更新的列则不宜建立聚簇索引，因为建立聚簇索引后，更新索
引列数据时，要更新表中记录的物理顺序，开销较大。
Database System Concepts, 5th Edition, Oct 5, 2006
3.26
©Silberschatz, Korth and Sudarshan

删除索引
在SQL语言中，使用DROP INDEX命令删除索引，其语法如下：
DROP INDEX <索引名>；
【例10】删除为student表建立的索引stu_cluind。
DROP INDEX stu_cluind；
删除索引时，系统会同时从数据库中删去有关该索引的描述。
对
于数据库系统而言，索引一经建立，一般不应随意删除。SQL没有提供修
改索引的语句，对于一些在使用中证明不合适的索引，只能先删除后重建
。
Database System Concepts, 5th Edition, Oct 5, 2006
3.27
©Silberschatz, Korth and Sudarshan
视图
视图的概念
视图不包含任何数据，只是定义在一个或多个基表上或其他视图上，
并且提供一种访问基表数据的方法。在物理磁盘上存储的有关视图的信
息是：视图的名称和视图的定义。视图的所有数据来自基本表。因此，
当基本表的数据发生变化时，对应视图中的查询出的数据也会随之变化
。
视图一经定义，就可以象基本表一样执行查询、删除等操作，也可以
在视图上定义新的视图，但对视图的更新操作则有一定限制。
视图的作用
 可以满足不同用户的需求
不同的用户对数据库操作有不同的需求，即使相同的数据也可能有不
同的操作要求。一张基本表可能有很多属性列，利用视图，用户可以把
自己感兴趣的属性列集中起来，放在一个视图中，此后用户可以将视图
作为一张表来对待。
Database System Concepts, 5th Edition, Oct 5, 2006
3.28
©Silberschatz, Korth and Sudarshan
 可以简化数据读取
查询数据时，通常要用SELECT语句编写复杂的连接、统计、函数等
，以产生所需要的结果。使用视图，可以隐蔽这种复杂性。可以将经常
用到的复杂查询的语句定义为视图，不必每次查询都写上复杂查询条件
，这样就简化了用户的查询操作。
 保证了基本表数据和应用程序的逻辑独立性
当应用程序通过视图来访问数据时，视图实际上成为应用程序和基
本表数据之间的桥梁。如果应用程序直接调用基本表，则一旦基本表的
数据发生变化时，应用程序必须随之改动。而通过视图访问数据，则可
以通过改变视图来适应基本表的变化，使应用程序不必作改变，保证了
基本表数据和应用程序的逻辑独立性。
 可以提供安全机制
利用视图可以限制数据访问。如果某个用户需要访问表中的某些列
，但另一些属性列必须对该用户保密，则可以利用视图达到此目的，将
视图建立在该用户需要访问的那些列上。
Database System Concepts, 5th Edition, Oct 5, 2006
3.29
©Silberschatz, Korth and Sudarshan
视图的建立
SQL语言用CREATE VIEW语句来建立视图，其一般格式为：
CREATE VIEW <视图名> [(<列名>[，<列名>]…)]
AS <子查询>
[WITH CHECK OPTION]；
说明：
(1)选项WITH CHECK OPTION确保用户只能查询和修改他们所看到的数据，强制所有在
视图上使用的数据修改语句满足定义视图时的条件。
(2)组成视图的各属性列可以显式指定，也可以省略。如果省略不写，则组成视图的
各属性列由子查询中SELECT子句的各目标列组成。
下列情形建立视图时必须显式指定属性列：
1)视图的某列不是原属性列，而是统计函数或者表达式。
2)多表连接时选出了两个或者多个同名列作为视图的属性列。
3)需要对视图中的某些列重新命名。
Database System Concepts, 5th Edition, Oct 5, 2006
3.30
©Silberschatz, Korth and Sudarshan
【例11】建立仅包含部门主管视图。
CREATE VIEW mgr_vu
AS SELECT *
FROM employee
WHERE JOB=’ 部门主管’ ;
【例12】为21号部门的所有员工的员工号、姓名及月薪建立视图。
CREATE VIEW sal21_vu (eno,ename,salary)
AS SELECT emp_id,ename,sal
FROM employee
WHERE dept_id=’21’ ;
Database System Concepts, 5th Edition, Oct 5, 2006
3.31
©Silberschatz, Korth and Sudarshan
【例13】为21号部门月薪超过1500的员工的员工号、姓名及月薪建立视图。
CREATE VIEW sal21_vu1 (eno,ename,salary)
AS SELECT emp_id,ename,sal
FROM employee
WHERE dept_id=’21’ AND sal>1500;
该题也可以对定义的视图进行进一步的筛选。由于视图不仅可以建立在
基本表之上，还可以建立在视图上，因此该语句也可写作：
CREATE VIEW sal21_vu1
AS SELECT *
FROM sal21_vu
WHERE sal>1500;
Database System Concepts, 5th Edition, Oct 5, 2006
3.32
©Silberschatz, Korth and Sudarshan
【例14】为各部门的平均月薪建立视图
CREATE VIEW salavg_vu(dno,sal_avg)
AS SELECT dept_id,AVG(sal)
FROM employee
GROUP BY dept_id;
本例中因使用了统计函数，因而对视图的列作了显式指定。
视图还可以建立在多个表或视图之上。
【例15】为产品开发部的员工的员工号、姓名、月薪以及工龄建立视图。
CREATE VIEW dept_vu(eno,name,salary,wage)
AS SELECT emp_id,ename ,sal ,2004-year(workdate)
FROM employee,dept
WHERE employee.dept_id= dept.dept_id
AND dept.dname=’ 产品开发部’ ;
Database System Concepts, 5th Edition, Oct 5, 2006
3.33
©Silberschatz, Korth and Sudarshan
SQL语言的数据操纵功能
增加数据： insert
修改数据： update
删除数据： delete
Database System Concepts, 5th Edition, Oct 5, 2006
3.34
©Silberschatz, Korth and Sudarshan
数据更新
插入记录

插入单条记录
INSERT INTO <表名> [(<属性列1>[,<属性列2>]…)]
VALUES (<常量1>[,<常量2>]…);
注意：
（1）属性列的个数与常量的个数要相等，且顺序一致，否则会产生语法错误。
（2）在表结构定义中未说明为NOT NULL的属性列，如果没有出现在INTO子句后，这
些列将取空值。已经说明为NOT NULL的属性列，则必须出现在INTO子句后。
（3）如果INTO子句后没有指定任何列，则VALUES子句后面的常量个数必须与基本表
中列的个数相等，且类型、顺序一致，否则会出语法错误或导致赋值不正确。
Database System Concepts, 5th Edition, Oct 5, 2006
3.35
©Silberschatz, Korth and Sudarshan
【例3-47】插入一条部门新记录。
INSERT INTO dept (dept_id,dname,tel)
VALUES (‘31’,’产品开发部’,’08667864532’);
该语句等价于：
INSERT INTO dept
VALUES (‘31’,’ 产品开发部’,’08667864532’);
【例3-48】插入一条员工新记录。
INSERT INTO employee
VALUES(‘1311’, ‘淳’,NULL,NULL,to_date(’2004/08/15’),800,’11’);
Database System Concepts, 5th Edition, Oct 5, 2006
3.36
©Silberschatz, Korth and Sudarshan
Basic Query Structure
 SQL is based on set and relational operations with certain
modifications and enhancements
 A typical SQL query has the form:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
 Ai represents an attribute
 Ri represents a relation
 P is a predicate.
 This query is equivalent to the relational algebra expression.
A ,A ,,A ( P (r1  r2   rm ))
1
2
n
 The result of an SQL query is a relation.
Database System Concepts, 5th Edition, Oct 5, 2006
3.37
©Silberschatz, Korth and Sudarshan
The select Clause
 The select clause list the attributes desired in the result of a query

corresponds to the projection operation of the relational algebra
 Example: find the names of all branches in the loan relation:
select branch_name
from loan
 In the relational algebra, the query would be:
branch_name (loan)
 NOTE: SQL names are case insensitive (i.e., you may use upper- or
lower-case letters.)

E.g. Branch_Name ≡ BRANCH_NAME ≡ branch_name

Some people use upper case wherever we use bold font.
Database System Concepts, 5th Edition, Oct 5, 2006
3.38
©Silberschatz, Korth and Sudarshan
The select Clause (Cont.)
 SQL allows duplicates in relations as well as in query results.
 To force the elimination of duplicates, insert the keyword distinct after
select.
 Find the names of all branches in the loan relations, and remove
duplicates
select distinct branch_name
from loan
 The keyword all specifies that duplicates not be removed.
select all branch_name
from loan
Database System Concepts, 5th Edition, Oct 5, 2006
3.39
©Silberschatz, Korth and Sudarshan
The select Clause (Cont.)
 An asterisk in the select clause denotes “all attributes”
select *
from loan
 The select clause can contain arithmetic expressions involving the
operation, +, –, , and /, and operating on constants or attributes of
tuples.
 The query:
select loan_number, branch_name, amount  100
from loan
would return a relation that is the same as the loan relation, except that
the value of the attribute amount is multiplied by 100.
Database System Concepts, 5th Edition, Oct 5, 2006
3.40
©Silberschatz, Korth and Sudarshan
The where Clause
 The where clause specifies conditions that the result must satisfy

Corresponds to the selection predicate of the relational algebra.
 To find all loan number for loans made at the Perryridge branch with
loan amounts greater than $1200.
select loan_number
from loan
where branch_name = 'Perryridge' and amount > 1200
 Comparison results can be combined using the logical connectives and,
or, and not.
 Comparisons can be applied to results of arithmetic expressions.
Database System Concepts, 5th Edition, Oct 5, 2006
3.41
©Silberschatz, Korth and Sudarshan
The where Clause (Cont.)
 SQL includes a between comparison operator
 Example: Find the loan number of those loans with loan amounts between
$90,000 and $100,000 (that is,  $90,000 and  $100,000)
select loan_number
from loan
where amount between 90000 and 100000
Database System Concepts, 5th Edition, Oct 5, 2006
3.42
©Silberschatz, Korth and Sudarshan
The from Clause
 The from clause lists the relations involved in the query

Corresponds to the Cartesian product operation of the relational algebra.
 Find the Cartesian product borrower X loan
select 
from borrower, loan
 Find the name, loan number and loan amount of all customers
having a loan at the Perryridge branch.
select customer_name, borrower.loan_number, amount
from borrower, loan
where borrower.loan_number = loan.loan_number and
branch_name = 'Perryridge'
Database System Concepts, 5th Edition, Oct 5, 2006
3.43
©Silberschatz, Korth and Sudarshan
The Rename Operation
 The SQL allows renaming relations and attributes using the as clause:
old-name as new-name
 Find the name, loan number and loan amount of all customers; rename the
column name loan_number as loan_id.
select customer_name, borrower.loan_number as loan_id, amount
from borrower, loan
where borrower.loan_number = loan.loan_number
Database System Concepts, 5th Edition, Oct 5, 2006
3.44
©Silberschatz, Korth and Sudarshan
Tuple Variables
 Tuple variables are defined in the from clause via the use of the as
clause.
 Find the customer names and their loan numbers for all customers
having a loan at some branch.
select customer_name, T.loan_number, S.amount
from borrower as T, loan as S
where T.loan_number = S.loan_number

Find the names of all branches that have greater assets than
some branch located in Brooklyn.
select distinct T.branch_name
from branch as T, branch as S
where T.assets > S.assets and S.branch_city = 'Brooklyn'
Keyword as is optional and may be omitted
borrower as T ≡ borrower T
Database System Concepts, 5th Edition, Oct 5, 2006
3.45
©Silberschatz, Korth and Sudarshan
String Operations
 SQL includes a string-matching operator for comparisons on character
strings. The operator “like” uses patterns that are described using two
special characters:

percent (%). The % character matches any substring.

underscore (_). The _ character matches any character.
 Find the names of all customers whose street includes the substring
“Main”.
select customer_name
from customer
where customer_street like '% Main%'
 Match the name “Main%”
like 'Main\%' escape '\'
 SQL supports a variety of string operations such as

concatenation (using “||”)

converting from upper to lower case (and vice versa)

finding string length, extracting substrings, etc.
Database System Concepts, 5th Edition, Oct 5, 2006
3.46
©Silberschatz, Korth and Sudarshan
Ordering the Display of Tuples
 List in alphabetic order the names of all customers having a loan in
Perryridge branch
select distinct customer_name
from borrower, loan
where borrower loan_number = loan.loan_number and
branch_name = 'Perryridge'
order by customer_name
 We may specify desc for descending order or asc for ascending
order, for each attribute; ascending order is the default.

Example: order by customer_name desc
Database System Concepts, 5th Edition, Oct 5, 2006
3.47
©Silberschatz, Korth and Sudarshan
Duplicates
 In relations with duplicates, SQL can define how many copies of tuples
appear in the result.
 Multiset versions of some of the relational algebra operators – given
multiset relations r1 and r2:
1.
 (r1): If there are c1 copies of tuple t1 in r1, and t1 satisfies
selections ,, then there are c1 copies of t1 in  (r1).
2. A (r ): For each copy of tuple t1 in r1, there is a copy of tuple
A (t1) in A (r1) where A (t1) denotes the projection of the single
tuple t1.
3. r1 x r2 : If there are c1 copies of tuple t1 in r1 and c2 copies of tuple
t2 in r2, there are c1 x c2 copies of the tuple t1. t2 in r1 x r2
Database System Concepts, 5th Edition, Oct 5, 2006
3.48
©Silberschatz, Korth and Sudarshan
Duplicates (Cont.)
 Example: Suppose multiset relations r1 (A, B) and r2 (C) are as
follows:
r1 = {(1, a) (2,a)}
r2 = {(2), (3), (3)}
 Then B(r1) would be {(a), (a)}, while B(r1) x r2 would be
{(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)}
 SQL duplicate semantics:
select A1,, A2, ..., An
from r1, r2, ..., rm
where P
is equivalent to the multiset version of the expression:
A ,A ,,A ( P (r1  r2   rm ))
1
Database System Concepts, 5th Edition, Oct 5, 2006
2
n
3.49
©Silberschatz, Korth and Sudarshan
Set Operations
 The set operations union, intersect, and except operate on relations
and correspond to the relational algebra operations 
 Each of the above operations automatically eliminates duplicates; to
retain all duplicates use the corresponding multiset versions union all,
intersect all and except all.
Suppose a tuple occurs m times in r and n times in s, then, it occurs:

m + n times in r union all s

min(m,n) times in r intersect all s

max(0, m – n) times in r except all s
Database System Concepts, 5th Edition, Oct 5, 2006
3.50
©Silberschatz, Korth and Sudarshan
Set Operations
 Find all customers who have a loan, an account, or both:
(select customer_name from depositor)
union
(select customer_name from borrower)
 Find all customers who have both a loan and an account.
(select customer_name from depositor)
intersect
(select customer_name from borrower)
 Find all customers who have an account but no loan.
(select customer_name from depositor)
except
(select customer_name from borrower)
Database System Concepts, 5th Edition, Oct 5, 2006
3.51
©Silberschatz, Korth and Sudarshan
Aggregate Functions
 These functions operate on the multiset of values of a column of
a relation, and return a value
avg: average value
min: minimum value
max: maximum value
sum: sum of values
count: number of values
Database System Concepts, 5th Edition, Oct 5, 2006
3.52
©Silberschatz, Korth and Sudarshan
Aggregate Functions (Cont.)
 Find the average account balance at the Perryridge branch.
select avg (balance)
from account
where branch_name = 'Perryridge'
 Find the number of tuples in the customer relation.
select count (*)
from customer
 Find the number of depositors in the bank.
select count (distinct customer_name)
from depositor
Database System Concepts, 5th Edition, Oct 5, 2006
3.53
©Silberschatz, Korth and Sudarshan
Aggregate Functions – Group By
 Find the number of depositors for each branch.
select branch_name, count (distinct customer_name)
from depositor, account
where depositor.account_number = account.account_number
group by branch_name
Note: Attributes in select clause outside of aggregate functions must
appear in group by list
Database System Concepts, 5th Edition, Oct 5, 2006
3.54
©Silberschatz, Korth and Sudarshan
Aggregate Functions – Having Clause
 Find the names of all branches where the average account balance is
more than $1,200.
select branch_name, avg (balance)
from account
group by branch_name
having avg (balance) > 1200
Note: predicates in the having clause are applied after the
formation of groups whereas predicates in the where
clause are applied before forming groups
Database System Concepts, 5th Edition, Oct 5, 2006
3.55
©Silberschatz, Korth and Sudarshan
Nested Subqueries
 SQL provides a mechanism for the nesting of subqueries.
 A subquery is a select-from-where expression that is nested within
another query.
 A common use of subqueries is to perform tests for set membership, set
comparisons, and set cardinality.
Database System Concepts, 5th Edition, Oct 5, 2006
3.56
©Silberschatz, Korth and Sudarshan
Example Query
 Find all customers who have both an account and a loan at the bank.
select distinct customer_name
from borrower
where customer_name in (select customer_name
from depositor )
 Find all customers who have a loan at the bank but do not have
an account at the bank
select distinct customer_name
from borrower
where customer_name not in (select customer_name
from depositor )
Database System Concepts, 5th Edition, Oct 5, 2006
3.57
©Silberschatz, Korth and Sudarshan
Example Query
 Find all customers who have both an account and a loan at the
Perryridge branch
select distinct customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number and
branch_name = 'Perryridge' and
(branch_name, customer_name ) in
(select branch_name, customer_name
from depositor, account
where depositor.account_number =
account.account_number )
 Note: Above query can be written in a much simpler manner. The
formulation above is simply to illustrate SQL features.
Database System Concepts, 5th Edition, Oct 5, 2006
3.58
©Silberschatz, Korth and Sudarshan
Set Comparison
 Find all branches that have greater assets than some branch located
in Brooklyn.
select distinct T.branch_name
from branch as T, branch as S
where T.assets > S.assets and
S.branch_city = 'Brooklyn'
 Same query using > some clause
select branch_name
from branch
where assets > some
(select assets
from branch
where branch_city = 'Brooklyn')
Database System Concepts, 5th Edition, Oct 5, 2006
3.59
©Silberschatz, Korth and Sudarshan
Definition of Some Clause
 F <comp> some r t  r such that (F <comp> t )
Where <comp> can be:     
0
5
6
) = true
(5 < some
0
5
) = false
(5 = some
0
5
) = true
(5  some
0
5
) = true (since 0  5)
(5 < some
(read: 5 < some tuple in the relation)
(= some)  in
However, ( some)  not in
Database System Concepts, 5th Edition, Oct 5, 2006
3.60
©Silberschatz, Korth and Sudarshan
Example Query
 Find the names of all branches that have greater assets than all
branches located in Brooklyn.
select branch_name
from branch
where assets > all
(select assets
from branch
where branch_city = 'Brooklyn')
Database System Concepts, 5th Edition, Oct 5, 2006
3.61
©Silberschatz, Korth and Sudarshan
Definition of all Clause
 F <comp> all r t  r (F <comp> t)
(5 < all
0
5
6
) = false
(5 < all
6
10
) = true
(5 = all
4
5
) = false
(5  all
4
6
) = true (since 5  4 and 5  6)
( all)  not in
However, (= all)  in
Database System Concepts, 5th Edition, Oct 5, 2006
3.62
©Silberschatz, Korth and Sudarshan
Example Query
 Find all customers who have an account at all branches located in
Brooklyn.
select distinct S.customer_name
from depositor as S
where not exists (
(select branch_name
from branch
where branch_city = 'Brooklyn')
except
(select R.branch_name
from depositor as T, account as R
where T.account_number = R.account_number and
S.customer_name = T.customer_name ))
 Note that X – Y = Ø  X Y
 Note: Cannot write this query using = all and its variants
Database System Concepts, 5th Edition, Oct 5, 2006
3.63
©Silberschatz, Korth and Sudarshan
Test for Absence of Duplicate Tuples
 The unique construct tests whether a subquery has any duplicate
tuples in its result.
 Find all customers who have at most one account at the Perryridge
branch.
select T.customer_name
from depositor as T
where unique (
select R.customer_name
from account, depositor as R
where T.customer_name = R.customer_name and
R.account_number = account.account_number and
account.branch_name = 'Perryridge')
Database System Concepts, 5th Edition, Oct 5, 2006
3.64
©Silberschatz, Korth and Sudarshan
Example Query
 Find all customers who have at least two accounts at the Perryridge
branch.
select distinct T.customer_name
from depositor as T
where not unique (
select R.customer_name
from account, depositor as R
where T.customer_name = R.customer_name and
R.account_number = account.account_number and
account.branch_name = 'Perryridge')
 Variable from outer level is known as a correlation variable
Database System Concepts, 5th Edition, Oct 5, 2006
3.65
©Silberschatz, Korth and Sudarshan
Derived Relations
 SQL allows a subquery expression to be used in the from clause
 Find the average account balance of those branches where the average
account balance is greater than $1200.
select branch_name, avg_balance
from (select branch_name, avg (balance)
from account
group by branch_name )
as branch_avg ( branch_name, avg_balance )
where avg_balance > 1200
Note that we do not need to use the having clause, since we compute
the temporary (view) relation branch_avg in the from clause, and the
attributes of branch_avg can be used directly in the where clause.
Database System Concepts, 5th Edition, Oct 5, 2006
3.66
©Silberschatz, Korth and Sudarshan
With Clause
 The with clause provides a way of defining a temporary view whose
definition is available only to the query in which the with clause occurs.
 Find all accounts with the maximum balance
with max_balance (value) as
select max (balance)
from account
select account_number
from account, max_balance
where account.balance = max_balance.value
Database System Concepts, 5th Edition, Oct 5, 2006
3.67
©Silberschatz, Korth and Sudarshan
Complex Queries using With Clause
 Find all branches where the total account deposit is greater than the
average of the total account deposits at all branches.
with branch_total (branch_name, value) as
select branch_name, sum (balance)
from account
group by branch_name
with branch_total_avg (value) as
select avg (value)
from branch_total
select branch_name
from branch_total, branch_total_avg
where branch_total.value >= branch_total_avg.value
Database System Concepts, 5th Edition, Oct 5, 2006
3.68
©Silberschatz, Korth and Sudarshan
Test for Empty Relations
 The exists construct returns the value true if the argument subquery is
nonempty.
 exists r  r  Ø
 not exists r  r = Ø
Database System Concepts, 5th Edition, Oct 5, 2006
3.69
©Silberschatz, Korth and Sudarshan
Views
 In some cases, it is not desirable for all users to see the entire logical
model (that is, all the actual relations stored in the database.)
 Consider a person who needs to know a customer’s name, loan number
and branch name, but has no need to see the loan amount. This person
should see a relation described, in SQL, by
(select customer_name, borrower.loan_number, branch_name
from borrower, loan
where borrower.loan_number = loan.loan_number )
 A view provides a mechanism to hide certain data from the view of
certain users.
 Any relation that is not of the conceptual model but is made visible to a
user as a “virtual relation” is called a view.
Database System Concepts, 5th Edition, Oct 5, 2006
3.70
©Silberschatz, Korth and Sudarshan
View Definition
 A view is defined using the create view statement which has the
form
create view v as < query expression >
where <query expression> is any legal SQL expression. The view
name is represented by v.
 Once a view is defined, the view name can be used to refer to the
virtual relation that the view generates.
 When a view is created, the query expression is stored in the
database; the expression is substituted into queries using the view.
Database System Concepts, 5th Edition, Oct 5, 2006
3.71
©Silberschatz, Korth and Sudarshan
Example Queries
 A view consisting of branches and their customers
create view all_customer as
(select branch_name, customer_name
from depositor, account
where depositor.account_number =
account.account_number )
union
(select branch_name, customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number )
 Find all customers of the Perryridge branch
select customer_name
from all_customer
where branch_name = 'Perryridge'
Database System Concepts, 5th Edition, Oct 5, 2006
3.72
©Silberschatz, Korth and Sudarshan
Views Defined Using Other Views
 One view may be used in the expression defining another view
 A view relation v1 is said to depend directly on a view relation v2 if v2
is used in the expression defining v1
 A view relation v1 is said to depend on view relation v2 if either v1
depends directly to v2 or there is a path of dependencies from v1 to
v2
 A view relation v is said to be recursive if it depends on itself.
Database System Concepts, 5th Edition, Oct 5, 2006
3.73
©Silberschatz, Korth and Sudarshan
View Expansion
 A way to define the meaning of views defined in terms of other views.
 Let view v1 be defined by an expression e1 that may itself contain uses
of view relations.
 View expansion of an expression repeats the following replacement
step:
repeat
Find any view relation vi in e1
Replace the view relation vi by the expression defining vi
until no more view relations are present in e1
 As long as the view definitions are not recursive, this loop will
terminate
Database System Concepts, 5th Edition, Oct 5, 2006
3.74
©Silberschatz, Korth and Sudarshan
数据查询
SELECT语句的结构
SQL语言提供的SELECT语句的一般格式如下：
SELECT [ALL|DISTINCT]<目标列表达式> [，<目标列表达式>][,…]
FROM <表名或视图名> [,<表名或视图名>] [,…]
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY] <列名2> [ASC|DESC]]；
说明：
SELECT语句的含义是，根据WHERE子句指定的条件，从FROM子句后面的基本
表或视图中找出满足条件的记录，再按照SELECT子句指定的目标列表达式，选
出这些记录相应的列形成结果集返回。其中，SELECT子句和FROM子句是必选的
，而WHERE子句、GROUP BY子句、HAVING子句以及ORDER BY子句都是可选的。
Database System Concepts, 5th Edition, Oct 5, 2006
3.75
©Silberschatz, Korth and Sudarshan
选择行和列--选择和投影运算的实现
下面以人事工资管理系统的员工表employee和部门表dept为例介绍SELECT语
句的使用方法。
Employee (emp_id,ename,job,mgr_id, workdate,sal, comm,dept_id)
员工号
姓名
职位 主管
参加工作时间 月薪 岗位津贴 部门号
Dept(dept_id，dname， tel)
部门号， 部门名称， 电话
用下面语句创建表结构：
CREATE TABLE employee (emp_id CHAR(4) PRIMARY KEY,
ename VARCHAR(20), job VARCHAR(9) ,
mgr_id CHAR(4),workdate DATE,
sal
SMALLINT, comm
SMALLINT,
dept_id CHAR(2) NOT NULL
FOREIGN KEY (dept_id) REFERENCES Dept(dept_id),
FOREIGN KEY(mgr_id) REFERENCES employee(emp_id));
Database System Concepts, 5th Edition, Oct 5, 2006
3.76
©Silberschatz, Korth and Sudarshan
CREATE TABLE dept (dept_id CHAR(2) PRIMARY KEY,
dname VARCHAR(12) ,
tel VARCHAR(20));

选择运算的实现
 查询一个表的所有行
在SELECT语句中只要没有WHERE子句，查询结果就包含了所有行。
【例19】查询表employee的所有员工的员工号和姓名。
SELECT emp_id,ename
FROM employee;
 查询满足条件的某些行
查询满足条件的某些行，可以通过WHERE子句来实现。
【例20】查询月薪超过1800的员工姓名和月薪。
SELECT ename,sal
FROM employee
WHERE sal>1800;
Database System Concepts, 5th Edition, Oct 5, 2006
3.77
©Silberschatz, Korth and Sudarshan
【例3-14】查询员工号为1001的员工姓名及部门号。
SELECT ename,dept_id
FROM employee
WHERE emp_id=’1001’;

投影运算的实现

查询表的全部列
【例3-15】查询部门表中部门号为11的全部内容。
SELECT dept_id,dname,tel FROM dept
WHERE dept_id=’11’;
在SQL语言中，可以用星号“*”代表所有列名，列的显示顺序与基
本表中列的顺序一致。
【例3-16】下面语句的结果等价于【例3-15】的语句。
SELECT * FROM dept
WHERE dept_id=’11’;
Database System Concepts, 5th Edition, Oct 5, 2006
3.78
©Silberschatz, Korth and Sudarshan
查询表的部分列
【例21】查询全体员工的员工号，姓名和参加工作时间。
SELECT emp_id,ename,workdate

FROM employee ;
 查询经过计算的值
SELECT子句的<目标列表达式>不仅可以是基本表的属性，也可以是
表达式，包括算术表达式、字符串常量和函数等。
【例22】查询全体员工的姓名及年薪。
SELECT ename,sal*12
FROM employee ;
结果:
ename
sal*12
吴伟
15600
岳玲
13200
王斌
18000
徐欢
9600
Database System Concepts, 5th Edition, Oct 5, 2006
3.79
©Silberschatz, Korth and Sudarshan
【例23】上例查询如果在<目标列表达式>中使用字符串常量，结果会更
清晰。
SELECT ename,’年薪：’,sal*12
FROM employee ;
查询结果如下：
ename
年薪：
sal*12
吴伟
年薪：
15600
岳玲
年薪：
13200
王斌
年薪：
18000
徐欢
年薪：
9600
Database System Concepts, 5th Edition, Oct 5, 2006
3.80
©Silberschatz, Korth and Sudarshan
【例24】可以通过指定别名来改变查询结果的列标题，这样也可以使结
果更清晰。
SELECT ename AS ‘姓名’ ,sal*12
AS ‘年薪’
FROM employee ;
查询结果如下：
姓名
年薪
吴伟
15600
岳玲
13200
王斌
18000
徐欢
9600
Database System Concepts, 5th Edition, Oct 5, 2006
3.81
©Silberschatz, Korth and Sudarshan
SQL的运算符
SQL言使用的运符包括算术运算符、比较运算符、逻辑运算符等
。

算术运算符
算术运算符有4种：+、-、*、/

比较运算符

基本比较运算符
共9种：=、>、<、<=、>=、！=或<>、
！>（不大于）、！<（不小于）

特殊比较运算符
有4类：BETWEEN…AND…、IN、LIKE 和 IS NULL
Database System Concepts, 5th Edition, Oct 5, 2006
3.82
©Silberschatz, Korth and Sudarshan

运算符BETWEEN…AND…
运算符BETWEEN…AND…是用于确定记录的范围，即某属性值在
指定的范围之内（包括边界值）的记录，其中BETWEEN后面是下限
，AND后面是上限。
【例3-21】查询月薪在1000到1500之间的员工姓名及月薪。
SELECT ename,sal
FROM employee
WHERE sal BETWEEN 1000 and 1500 ;
查询结果包括那些月薪等于1000和月薪等于1500的记录。

运算符IN
运算符IN是用于查找某属性值包含在指定集合内的记录，IN后
面跟的是指定集合。
【例3-22】查找部门号属于11和21的员工姓名及部门号。
SELECT ename,dept_id
FROM employee
WHERE dept_id IN (‘11’,’21’) ;
Database System Concepts, 5th Edition, Oct 5, 2006
3.83
©Silberschatz, Korth and Sudarshan

运算符LIKE
运算符LIKE可用来进行字符串的匹配，LIKE后面跟的是匹配模式
。匹配模式可以是一个包含通配符“%”（百分号）和“_”（下划
线）的字符串。
– “%”代表任意长度（长度可以为0）的字符串。
– “_”代表任意单个字符。
【例3-23】查询所有姓张员工的姓名。
SELECT ename FROM employee
WHERE ename LIKE ‘张%’ ;
【例3-24】查找所有1990年参加工作的员工姓名及参加工作时间。
SELECT ename,workdate FROM employee
WHERE workdate LIKE ‘%1990’ ;
【例3-25】查找姓名第2个字为“小”的员工姓名。
SELECT ename FROM employee
WHERE ename LIKE ‘__小%’ ;
Database System Concepts, 5th Edition, Oct 5, 2006
3.84
©Silberschatz, Korth and Sudarshan

运算符IS NULL
运算符IS NULL用来测试某个属性值是否为空。
【例3-27】在employee表中，职位是总经理的员工在属性mgr_id这栏
应该不填，因为他没有上司。因此查询总经理的姓名的语句应为：
SELECT ename
FROM employee
WHERE mgr_id IS NULL ;
【例3-28】可能有这样的情况：新进员工暂时没有岗位津贴，等试用
期满后再领岗位津贴。查询没有岗位津贴的员工姓名。
SELECT ename
FROM employee
WHERE comm IS NULL ;
Database System Concepts, 5th Edition, Oct 5, 2006
3.85
©Silberschatz, Korth and Sudarshan

逻辑运算符
SQL语言的逻辑运算符有3种：NOT、AND、OR。
【例3-29】查询月薪超过1500的部门主管的姓名及月薪。
SELECT ename,sal
FROM employee
WHERE sal>1500 AND job=’ 部门主管’ ;
【例3-30】查询月薪超过1500的员工和所有部门主管的姓名及月薪。
SELECT ename,sal
FROM employee
WHERE sal>1500 OR job=’ 部门主管’ ;
Database System Concepts, 5th Edition, Oct 5, 2006
3.86
©Silberschatz, Korth and Sudarshan
【例3-31】查询领有岗位津贴的员工姓名。
SELECT ename
FROM employee
WHERE comm IS NOT NULL ;
【例3-32】查找部门号不属于11和21的员工姓名及部门号。
SELECT ename,dept_id
FROM employee
WHERE dept_id NOT IN (‘11’,’21’) ;
Database System Concepts, 5th Edition, Oct 5, 2006
3.87
©Silberschatz, Korth and Sudarshan
3.3.4 对查询结果排序
SQL语言中用ORDER BY子句实现对查询结果的排序，可以根据包含的
一列或者多列的表达式进行ASC（升序）或DESC（降序）的排列，默认值
是ASC。
【例3-33】查询所有员工的姓名及月薪，结果按月薪的降序排列。
SELECT ename,sal
FROM employee
ORDER BY sal DESC ;
ORDER BY子句指定的排序列可以不只一个。
Database System Concepts, 5th Edition, Oct 5, 2006
3.88
©Silberschatz, Korth and Sudarshan
【例3-34】查询所有员工的姓名、部门号及月薪，结果按部门号升序排列，
同一部门按月薪降序排列。
SELECT ename,dept_id,sal
FROM employee
ORDER BY dept_id ,sal DESC ;
说明：
上例中dept_id称为主排序关键字，sal成为次排序关键字。
注意：
(1)ORDER BY子句不改变基本表中行或列的顺序，只改变查询显 示的顺序
。
(2)ORDER BY子句指定排序的列必须出现在SELECT子句的列表达式中。
(3)排序是查询语句的最后一步工作，所以ORDER BY子句一般放在查询语句
的最后。
Database System Concepts, 5th Edition, Oct 5, 2006
3.89
©Silberschatz, Korth and Sudarshan
3.3.5 消除重复行
基本表中不相同的行，经过对某些指定列进行投影运算后，可能会变
成完全相同的行，显示结果不直观，这时需要用DISTINCT选项消除重复
的行。
【例3-35】查询表employee中的所有职位。
SELECT DISTINCT job
FROM employee ;
注意：在一个SELECT语句中DISTINCT只能出现一次，并且DISTINCT必须
在所有列名之前，否则会发生语法错误。与DISTINCT选项含义相反的是
ALL选项，在SELECT语句中使用ALL选项，表示结果重复的行也将显示。
ALL选项是默认选项。
Database System Concepts, 5th Edition, Oct 5, 2006
3.90
©Silberschatz, Korth and Sudarshan
3.3.6 SQL的统计函数
SQL语言提供了许多统计函数，主要的统计函数见表3-3。
统计函数
语义
COUNT([DISTINCT|ALL]*)
统计表的记录个数
COUNT([DISTINCT|ALL]<列名>)
统计一列中值不为NULL值的个数
SUM([DISTINCT|ALL]<列名>)
计算一列值的总和（此列必须为数值型）
AVG([DISTINCT|ALL]<列名>)
计算一列值的平均值（此列必须为数值型）
MAX([DISTINCT|ALL]<列名>)
给出一列值中的最大值
MIN([DISTINCT|ALL]<列名>)
给出一列值中的最小值
Database System Concepts, 5th Edition, Oct 5, 2006
3.91
©Silberschatz, Korth and Sudarshan
【例3-36】统计员工总人数。
SELECT COUNT（*）
FROM employee ;
也可以写成：
SELECT COUNT（emp_id）
FROM employee ;
【例3-37】统计部门号“11”的部门领取岗位津贴的人数。
SELECT COUNT(comm)
FROM employee
WHERE dept_id=’11’ ;
这里统计的是属性列comm不为空值的行数。
Database System Concepts, 5th Edition, Oct 5, 2006
3.92
©Silberschatz, Korth and Sudarshan
【例3-38】统计部门号“11”的部门全体员工人数。
SELECT COUNT(*)
FROM employee
WHERE dept_id=’11’ ;
【例3-39】查询最早参加工作时间和最晚参加工作时间。
SELECT MIN(workdate), MAX(workdate)
FROM employee;
【例3-39】统计所有员工的岗位津贴总数及平均岗位津贴。
SELECT SUM(comm), AVG(comm)
FROM employee ;
注意：
除COUNT（*）外，所有的统计函数都不包括取值为空值的行。
Database System Concepts, 5th Edition, Oct 5, 2006
3.93
©Silberschatz, Korth and Sudarshan
3.3.7 数据分组
利用GROUP BY子句可以将查询结果按照一列或者多列分组，值相等的
为一组。
 基于单列的分组
【例3-40】按部门号查询各部门的平均月薪。
SELECT dept_id,AVG(sal)
FROM employee GROUP BY dept_id ;
 基于多列的分组
GROUP BY子句还可以作用于多列上，此时的数据分组意义是分大组
之后再分小组。
【例3-41】分各部门各职位统计月薪总额。
SELECT dept_id,job,sum(sal)
FROM employee GROUP BY dept_id,job ;
说明：先按照部门号分组，部门号相同的组再按职位细分，最后部门号
和职位完全相同的行才会分在一组，然后每组统计一个月薪总额。
Database System Concepts, 5th Edition, Oct 5, 2006
3.94
©Silberschatz, Korth and Sudarshan

HAVING子句
如果分组后还要根据一定条件对这些组进行筛选，则使用HAVING子句
来实现。
【例3-42】按部门号查询各部门的平均月薪，要求只显示平均月薪在1000
以上的部门编号和平均月薪。
SELECT dept_id,AVG(sal)
FROM employee
GROUP BY dept_id
HAVING AVG(sal)>1000;
注意:
WHERE子句和HAVING子句有相似之处，即后面都跟指定条件；但是它们
又有区别：前者直接用于SELECT子句中，作用于基本表或视图；而后者一
定跟在GROUP BY子句后面，作用于分组。
Database System Concepts, 5th Edition, Oct 5, 2006
3.95
©Silberschatz, Korth and Sudarshan
3.3.8 连接查询

连接查询的概念
如果一个查询需要从两个或两个以上的数据表中获取数据时，则称之
为连接查询。
连接查询包括广义笛卡尔积、等值连接、自然连接、外连接、内连接
、左连接、右连接和自连接等。

广义笛卡尔积
广义笛卡尔积是不带连接条件的连接操作。两个表的广义笛卡尔积即
是两个表中所有记录的交叉组合，其形成的结果集是所有连接种类中最
大的。比如：表1有3条记录，表2有5条记录，则广义笛卡尔积产生
3*5=15条记录。由于这种连接操作是不带条件的表的拼接，因此实际意
义不大。
Database System Concepts, 5th Edition, Oct 5, 2006
3.96
©Silberschatz, Korth and Sudarshan

等值连接
[<表名1>.]<列名1> = [<表名2>.]<列名2>
等值连接又称为内连接。若将查询结果的目标列中重复的列去掉，则称
为自然连接，在实际中等值连接一般以自然连接的形式出现。
【例3-43】查询每位员工的员工号，姓名，部门号、部门名称及部门电话
。
SELECT emp_id,ename,dept.dept_id,dname,tel
FROM employee,dept
WHERE employee.dept_id=dept.dept_id ;
说明：
(1)如果属性列名在参加连接的各表中是惟一的，可以省略表名前缀；如果
属性列名是两个表共同的属性，则一定要加表名前缀。
(2)在书写连接查询时，为了简化，可以为表名取别名，别名应该简单。别
名只在本次查询有效。
Database System Concepts, 5th Edition, Oct 5, 2006
3.97
©Silberschatz, Korth and Sudarshan
【例3-44】查询每位员工的员工号，姓名，部门号、部门名称及部门电话
。
SELECT emp_id,ename,d.dept_id,dname,tel
FROM employee e,dept d
WHERE e.dept_id=d.dept_id ;

不等连接
当连接条件中的比较运算符不为“=”时，此时的连接查询称为
不等连接。
Database System Concepts, 5th Edition, Oct 5, 2006
3.98
©Silberschatz, Korth and Sudarshan
假设有JOB表（职位表），它包含两个属性：职位job_level和标准月薪std_sal，
该表包含记录如下：
job_level
std_sal
部门经理
2200
出纳
1200
【例3-45】列出可提供给employee表中每位员工比现在薪水高的职位。
SELECT ename,sal,job_level,std_sal
FROM
employee,job
WHERE std_sal>sal
查询结果如下：
ename
sal
job_level
吴伟
1300
部门经理
2200
岳玲
1100
部门经理
2200
王斌
1500
部门经理
2200
徐欢
800
部门经理
2200
岳玲
1100
出纳
1200
徐欢
800
出纳
1200
Database System Concepts, 5th Edition, Oct 5, 2006
3.99
std_sal
©Silberschatz, Korth and Sudarshan
 插入子查询的结果
SELECT语句可以作为子查询嵌套在INSERT语句中，用以插入批量记录
。其语句格式一般为：
INSERT INTO <表名> [(<属性列1>[,<属性列2>]…)]
子查询;
【例3-49】求出每个部门平均月薪，将部门号和平均月薪放入一张新表
dept_sal中。
先创建新表的结构：
CREATE TABLE dept_sal (dept_id CHAR(2),avg_sal SMALLINT);
然后将子查询求出的数据批量插入新表中：
INSERT INTO dept_sal (dept_id, avg_sal)
SELECT dept_id,AVG(sal) FROM employee GROUP BY dept_id;
Database System Concepts, 5th Edition, Oct 5, 2006
3.100
©Silberschatz, Korth and Sudarshan
3.4.2 修改记录
SQL语言修改记录的语句为UPDATE。该语句有3种形式：修改单条记录
、修改多条记录以及使用子查询修改记录。其一般语句格式为：
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>][,…]
[WHERE <条件>];
SQL语言的修改语句功能是将表中符合WHERE子句条件的记录找出，以
表达式的值替代相应属性列的值。
Database System Concepts, 5th Edition, Oct 5, 2006
3.101
©Silberschatz, Korth and Sudarshan
 修改单条记录
【例3-50】修改1311号员工的月系薪为1700。
UPDATE employee
SET sal=1700
WHERE emp_id=’1311’ ;
 修改多条记录
【例3-51】所有员工月薪上调5%。
UPDATE employee
SET sal=sal*1.05 ;
【例3-52】21号部门的所有员工取消岗位津贴。
UPDATE employee
SET comm=0
WHERE dept_id=’21’ ;
Database System Concepts, 5th Edition, Oct 5, 2006
3.102
©Silberschatz, Korth and Sudarshan
 用子查询修改记录
UPDATE语句可以和SELECT语句联合使用。后者作为子查询嵌套。这种
情况也属于批量修改。
【例3-53】将产品开发部的部门主管的岗位津贴加500。
UPDATE employee
SET comm=comm+500
WHERE job=’ 部门主管’ AND dept_id=
( SELECT dept_id
FROM dept
WHERE dept.dname=’ 产品开发部’) ;
说明：
(1) 因为对应产品开发部的部门号只有一个，所以子查询检索出的记录只
有一条，因此子查询前面可以用“=”。
(2)这条UPDATE语句的WHERE子句有两个条件，它们是逻辑与关系。根据这
两个条件查询找出的记录可能是多条。
Database System Concepts, 5th Edition, Oct 5, 2006
3.103
©Silberschatz, Korth and Sudarshan
3.4.3 删除记录
DELETE语句一般格式：
DELETE
FROM <表名>
[WHERE <条件>] ;
DELETE语句的功能是：先按照WHERE子句中指定的条件范围将记录找出
来，然后进行删除。
 删除单条记录
【例3-54】删除员工号为1045的记录。
DELETE
FROM employee
WHERE emp_id=’1045’ ;
Database System Concepts, 5th Edition, Oct 5, 2006
3.104
©Silberschatz, Korth and Sudarshan
 删除多条记录
【例3-55】删除部门号为31的所有记录。
DELETE
FROM employee
WHERE dept_id=’31’ ;
【例3-56】删除所有员工记录。
DELETE
FROM employee ;
删除所有记录，是清空表中数据，因此不加WHERE子句。这类操作的
执行应非常小心。
Database System Concepts, 5th Edition, Oct 5, 2006
3.105
©Silberschatz, Korth and Sudarshan
 使用子查询删除
SELECT语句同样也可以和DELETE语句联合使用。
【例3-57】删除产品开发部和事业推广部的所有员工的记录。
DELETE FROM
employee
WHERE dept_id IN ( SELECT dept_id
FROM dept
WHERE dept.dname=’ 产品开发部’
OR dept.dname=’ 事业推广部’) ;
因为SELECT子查询得到的记录一般不止一条，所以子查询前面的运算
符不能为“=”，而应该用表示取值范围的“IN”。
Database System Concepts, 5th Edition, Oct 5, 2006
3.106
©Silberschatz, Korth and Sudarshan
Modification of the Database – Deletion
 Delete all account tuples at the Perryridge branch
delete from account
where branch_name = 'Perryridge'
 Delete all accounts at every branch located in the city ‘Needham’.
delete from account
where branch_name in (select branch_name
from branch
where branch_city = 'Needham')
Database System Concepts, 5th Edition, Oct 5, 2006
3.107
©Silberschatz, Korth and Sudarshan
Example Query
 Delete the record of all accounts with balances below the average at
the bank.
delete from account
where balance < (select avg (balance )
from account )

Problem: as we delete tuples from deposit, the average balance
changes

Solution used in SQL:
1. First, compute avg balance and find all tuples to delete
2. Next, delete all tuples found above (without recomputing avg or
retesting the tuples)
Database System Concepts, 5th Edition, Oct 5, 2006
3.108
©Silberschatz, Korth and Sudarshan
Modification of the Database – Insertion
 Add a new tuple to account
insert into account
values ('A-9732', 'Perryridge', 1200)
or equivalently
insert into account (branch_name, balance, account_number)
values ('Perryridge', 1200, 'A-9732')
 Add a new tuple to account with balance set to null
insert into account
values ('A-777','Perryridge', null )
Database System Concepts, 5th Edition, Oct 5, 2006
3.109
©Silberschatz, Korth and Sudarshan
Modification of the Database – Insertion
 Provide as a gift for all loan customers of the Perryridge branch, a $200
savings account. Let the loan number serve as the account number for the
new savings account
insert into account
select loan_number, branch_name, 200
from loan
where branch_name = 'Perryridge'
insert into depositor
select customer_name, loan_number
from loan, borrower
where branch_name = 'Perryridge'
and loan.account_number = borrower.account_number
 The select from where statement is evaluated fully before any of its
results are inserted into the relation (otherwise queries like
insert into table1 select * from table1
would cause problems)
Database System Concepts, 5th Edition, Oct 5, 2006
3.110
©Silberschatz, Korth and Sudarshan
Modification of the Database – Updates
 Increase all accounts with balances over $10,000 by 6%, all other
accounts receive 5%.

Write two update statements:
update account
set balance = balance  1.06
where balance > 10000
update account
set balance = balance  1.05
where balance  10000

The order is important

Can be done better using the case statement (next slide)
Database System Concepts, 5th Edition, Oct 5, 2006
3.111
©Silberschatz, Korth and Sudarshan
Case Statement for Conditional Updates
 Same query as before: Increase all accounts with balances over
$10,000 by 6%, all other accounts receive 5%.
update account
set balance = case
when balance <= 10000 then balance *1.05
else balance * 1.06
end
Database System Concepts, 5th Edition, Oct 5, 2006
3.112
©Silberschatz, Korth and Sudarshan
Update of a View
 Create a view of all loan data in the loan relation, hiding the amount
attribute
create view loan_branch as
select loan_number, branch_name
from loan
 Add a new tuple to branch_loan
insert into branch_loan
values ('L-37‘, 'Perryridge‘)
This insertion must be represented by the insertion of the tuple
('L-37', 'Perryridge', null )
into the loan relation
Database System Concepts, 5th Edition, Oct 5, 2006
3.113
©Silberschatz, Korth and Sudarshan
Updates Through Views (Cont.)
 Some updates through views are impossible to translate into
updates on the database relations

create view v as
select loan_number, branch_name, amount
from loan
where branch_name = ‘Perryridge’
insert into v values ( 'L-99','Downtown', '23')
 Others cannot be translated uniquely

insert into all_customer values ('Perryridge', 'John')

Have to choose loan or account, and
create a new loan/account number!
 Most SQL implementations allow updates only on simple views
(without aggregates) defined on a single relation
Database System Concepts, 5th Edition, Oct 5, 2006
3.114
©Silberschatz, Korth and Sudarshan
Joined Relations**
 Join operations take two relations and return as a result another
relation.
 These additional operations are typically used as subquery
expressions in the from clause
 Join condition – defines which tuples in the two relations match, and
what attributes are present in the result of the join.
 Join type – defines how tuples in each relation that do not match any
tuple in the other relation (based on the join condition) are treated.
Database System Concepts, 5th Edition, Oct 5, 2006
3.115
©Silberschatz, Korth and Sudarshan
Joined Relations – Datasets for Examples
 Relation loan
 Relation borrower
 Note: borrower information missing for L-260 and loan
information missing for L-155
Database System Concepts, 5th Edition, Oct 5, 2006
3.116
©Silberschatz, Korth and Sudarshan
Joined Relations – Examples
 loan inner join borrower on
loan.loan_number = borrower.loan_number
 loan left outer join borrower on
loan.loan_number = borrower.loan_number
Database System Concepts, 5th Edition, Oct 5, 2006
3.117
©Silberschatz, Korth and Sudarshan
Joined Relations – Examples
 loan natural inner join borrower
 loan natural right outer join borrower
Database System Concepts, 5th Edition, Oct 5, 2006
3.118
©Silberschatz, Korth and Sudarshan
Joined Relations – Examples
 loan full outer join borrower using (loan_number)
 Find all customers who have either an account or a loan (but not both)
at the bank.
select customer_name
from (depositor natural full outer join borrower )
where account_number is null or loan_number is null
Database System Concepts, 5th Edition, Oct 5, 2006
3.119
©Silberschatz, Korth and Sudarshan
End of Chapter 3
Database System Concepts, 5th Ed.
©Silberschatz, Korth and Sudarshan
See www.db-book.com for conditions on re-use