第三章 关系数据库标准语言SQL

3.1 SQL概述

3.1.1 产生与发展

最早在IBM的关系数据库管理系统原型SystemR上实现后来美国国家标准局ANSI批准SQL作为关系数据库语言的美国标准同年公布了SQL标准文本。近些年来SQL标准的内容越来越丰富和复杂。目前没有任何一个数据库系统能够支持SQL标准的所有概念和特性同时不少软件厂商对SQL基本命令集合进行了不同程度的修改和扩充又可以支持标准外的一些功能特性

3.1.2 SQL的特点

SQL的主要特点包括以下几部分

1.综合统一

数据库系统的主要功能是通过数据库支持的数据语言来实现的。
非关系模型的数据语言一般分为

• 模式数据定义语言模式DDL
• 外模式数据定义语言外模式DDL
• 数据存储有关的描述语言DSDL
• 数据操纵语言DML

曾经的用户数据库投入运行后如果需要修改模式必须停止现有数据库的运行修改模式并且编译后再重装数据库十分麻烦。而SQL可以独立完成数据库生命周期中的全部活动。因此用户在数据库系统投入使用后还可以根据需要随时的逐步的修改模式并不影响数据库运行从而使得系统具有良好的可扩展性。

2.高度非过程化

非关系数据库模型的数据操纵语言是面向过程的语言。而使用SQL的时候只需要提出“做什么”而无需知名怎么做因此无需了解存取路径。这个工作由系统自动完成。

3.面向集合的操作方式

SQL采用几何操作方式对于CRUD的操作以及操作对象、查找结果等操作都可以对元组集合进行操作。

4.以统一语法结构提供多种使用方式

SQL既是独立的语言也是嵌入式语言。SQL可以嵌入到高级语言的程序中而且无论是独立使用还是嵌入使用其语法结构是基本一致的吗因此统一了在不同语境下的语法使用。

3.1.3 SQL的基本概念

用户可以使用SQL对基本表和视图进行基本操作他们都是关系集合。
基本表是本身独立存在的表在关系数据库管理系统中一个关系对应一个基本表。一个或者多个基本表对应一个存储文件一个表可以带若干索引索引也可以存放在存储文件中。视图是一个或者几个基本表导出的表他不独立存储在数据库中也就是视图是一个虚表其中的信息是一个或者几个基本表的映射,通常用于供人查看。存储文件是数据的物理形式存储文件的逻辑结构组成了关系数据库的内模式其物理结构对最终用户是隐蔽的

3.2 数据库实例

以下的讲解都会以如下数据库为例子

3.3 数据定义

关系数据库系统支持三级模式结构其模式、外模式和内模式中的基本对象有模式、表、视图和索引等。

一个关系数据库管理系统的实例中可以建立多个数据库一个数据库中可以建立多个模式一个模式下包含多个表、视图和索引等数据库对象。下文中的语句<>括号中的为输入内容[]括号内的是可选内容

3.3.1 模式的定义和删除

模式定义语句如下

CREATE SCHEMA AUTHORIZATION [

|]

创建模式需要拥有数据库管理员权限或者获得了数据库管理员授予的CREATE SCHEMA权限。在创建模式的时候也可以执行创建基本表、视图等语句。定义模式实际上定义了一个命名空间在这个空间中可以进一步定义该模式包含的数据库对象比如基本表、视图、索引等从语句可以看出定义模式的同时需要将该模式的管理权限授权给某用户。

删除模式语句如下

DROP SCHEMA <CASCADE|RESTRICT>

其中CASCADE和RESTRICT两者必选其一CASCADE级联表示删除该模式的同时将模式中所有数据库对象都删除RESTRICT限制指的是如果模式中定义了下属的数据库对象则拒绝执行删除语句除非该模式没有下属的对象。

3.2.2基本表的定义、删除和修改

1.常见数据类型

在学习如何操作基本表之前我们先认识一些常用的数据类型

2.定义基本表

定义基本表的格式如下

CREATE TABLE

(
<列名><数据类型>[列完整性约束条件]
[<列名><数据类型>[列完整性约束条件]…]
[表完整性约束条件]
)

其中主要的列完整性约束条件有
PRIMARY KEY 主键
FOREIGN KEY (外码名) REFERENCES 被参照表(被参照列) 外键
NOT NULL 非空
UNIQUE 唯一值

3.修改基本表

ALTER TABLE

[ADD [COLUME] <新列名><数据类型>[完整性约束]] // 新增表项
[ADD <表级完整性约束>] // 增加表约束
[DROP [COLUME] <列名> [CASCADE|RESTRICT]] // 删除表项
[DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE]] // 删除表约束
[ALTER COLUME <列名><数据类型>] //修改列数据类型

其中ADD用于新增内容DROP用于删除内容。DROP COLUMN用于删除列如果指定了CASCADE则会在删除列之前先删除引用该列的其他对象比如视图如果指定了RESTRICT则如果对象被引用则RDBMS会拒绝删除该列。DROP CONSTRAINT 用于删除指定的完整性约束条件。ALTER COLUMN子句用于修改原有列的定义。

4.删除基本表

DROP TABLE <表名> [RESTRICT|CASCADE]

如果是restrict则删除是有约束条件的基本表不能被其他表的约束所引用比如外键不能有视图也不能有触发器不能有存储过程或者函数。如果有则删除失败。如果选择cascade则该表删除没有限制条件删除基本表的同时相关的依赖一并删除。

5.模式和表

一个模式包含了多个基本表每一个基本表都属于一个模式当定义一个基本表的时候有三种方法定义它所属的模式
方法一在表名中明显给出模式名

CREATE TABLE “S-T”.Student(…) //Student的模式是S-T

方法二在创建模式语句中同时创建表

方法三设置所属模式在创建表时不需要给出模式名

使用语句

SHOW search_path

可以显示当前的搜索路径

6.索引的建立和删除

当表的数据量比较大的时候查询操作会比较耗时因此建立索引可以加快查询速度。数据库所以你类似于图书后面的索引内容能够快速定位到需要查询的内容。数据库索引会有多种类型包括顺序文件上的索引、B+树索引、散列索引等等。索引虽然会加速查询速度但是需要占用一定内存空间当基本表更新的时候索引也要做出变更因此需要一些维护开销。一般来说建立和删除索引由索数据库管理员或者表的属主owner负责完成。关系数据库管理系统在执行查询时会自动选择合适的索引用户不能显式选择索引。索引是关系数据库内部实现技术属于内模式范畴。

常见索引形式
顺序文件索引是针对按照指定属性值升序或者降序的存储关系。
B+树索引是将索引属性组织成B+树的形式B+树的叶节点为属性值和相应的元组指针。B+树具有动态平衡的优点
散列索引是建立若干个同将索引属性按照其散列函数映射到相应的桶中
位图索引是用位向量记录索引属性中可能出现的值每一个位向量对应一个可能值。

建立索引

在SQL语句中建立索引语句如下

CREATE [UNIQUE] [CLUSTER] INDEX 索引名
ON <表名>(<列名>[<次序>] [, <列名>[<次序>]])

表名是基本表名字索引可与建立在该表的一列或者多列上各列之间用逗号分隔次序字段可选ASC升序和DESC降序
UNIQUE指该索引每一个索引值对应唯一的数据记录CLUSTER指建立聚簇索引聚簇索引会在后面介绍到

修改和删除

ALTER INDEX <旧索引名> RENAME TO <新索引名>
DROP INDEX <索引名>

3.4 数据查询

SQL提供SELECT语句用于查询一般格式为

根据WHERE子句条件表达式从FROM子句指定的基本表、视图中找出满足条件的元组
GROUP BY语句则用作将结果按照<列名1>的值进行分组该属性列值相等的元组为一个组ORDER BY用作将结果表按照<列名2>的值升序或者降序排序输出。

3.4.1 单表查询

select子句中的目标列表达式不仅可以使表中属性列也可以是表达式比如

SELECT Sname, 2014-Sage
FROM Student

可以使用DISTINCT消除取值重复的行比如:
查询全体学生的年龄集合不允许有重复值

SELECT DISTINCT Sage FROM Student

使用通配符*可以查询全体记录例如
查询全部学生的详细记录

SELECT * FROM STUDENT

使用WHERE子句实现条件查找常见的查询条件如表所示:

1.确定范围

确定范围使用BETWEEN… AND… 和NOT BETWEEN …AND …前者是范围下限后者是范围上限

2.集合查找

提供集合查询符合集合元素的项比如:

SELECT Sname, Ssex
FROM Student
WHERE Sdept NOT IN (‘CS’,"MA)

NOT IN不在集合汇总 IN在集合中

3.字符匹配模糊查询

使用LIKE可以用来实现模糊查询语法如下

[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘<换码字符>’]

其含义是查找制定属性列与<匹配串>匹配的元组。其中有两种通配符

• %代表任意长度的字符串比如a%b表示a开头b结尾的字符串包括ab,asfqb等
• _代表任意单个字符比如a_b表示a开头b结尾的三元素字符串

另外如果用户查询的字段本来就含有通配符则西药使用ESCAPE字段对通配符进行转义了。比如

SELECT Cno FROM Course
WHERE Cname LIKE ‘DB\_Design’ ESCAPE ‘\’

上述例子指的是'\'是换码字符在\后的_不算做通配符

4.多重条件查询

在WHERE后的判断语句可以使用AND或者OR来连接多个查询条件。括号的优先级最高其次是AND最后是OR

5.ORDER BY子句

用户可以使用ORDER BY子句对查询结果按照一个或者多个属性升序或者降序排序。比如
查询选修了三号课程的学生学号及其成绩分数降序排列

SELECT Sno,Grade
FROM SC
WHERE Cno=‘3’
ORDER BY Grade DESC

6.聚集函数
同时为了进一步方便用户增强检索功能SQL提供了许多聚集函数如下

如果指定了DISTINCT短语那么将会跳过重复值
当聚集函数遇到空值的时候除了CONUT(*)之外都跳过空值而非处理。
TIPWHERE子句中是不能使用聚集函数的只能用于SELECT和GROUP BY中的HAVING子句中。
使用例子如下
1.查询学生总人数

SELECT COUNT(*) FROM Student

2.查询选修了课程的学生人数

SELECT COUNT(DISTINCT Sno) FROM SC

3.计算选修一号课的学生成绩

SELECT AVG(Grade) FROM SC WHERE Cno=‘1’

7.GROUP BY子句

GROUP BY子句将查询结果按某一列或者多列的值分组值相等的为一组。对于查询结果分组的目的是细化聚集函数的作用对象。如果没有对查询结果分组则聚集函数会作用于整个查询结果分组后聚集函数将会作用于每一个组。而HAVING语句则是用于对组进行筛选比如

1.查询选修了三门课以上的学生学号

SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3

SC表是学生-课程表里面存储着学生的选课信息由于一个学生可能会选择多门课那么SC表中同一个学生会有多个记录。上述的SQL语句中GROUP BY指明了组单元为学生学号Sno他会将SC表中同一学号的所有记录都是作为一个组。HAVING语句指明了只有在SC表中存在3条记录以上的学生才会在最终结果中输出。

我们再看多点例子比如
2.求各个课程号以及相应的选课人数

SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno

该例子将SC表中的课程号Cno相同的视作为一个组而COUNT(*)则负责统计同一Cno的相同的记录的数量

3.4.2 连接查询

前面的查询都是针对一个表进行的如果一个查询同时涉及两个及以上的表则称之为连接查询。

1.等值与非等值连接

连接查询到WHERE子句中用来连接两个表的条件称之为连接条件或者连接谓词一般格式是

[<TABLE NAME1>.]<COL NAME1><比较运算符>[<TABLE NAME2>.]<COL NAME>

也可以使用BETWEEN…AND…字段。其中当连接运算符为=的时候为等值连接使用>或者<的时候则是非等值连接

连接谓词中的列名称称为连接字段连接条件中各个连接字段类型必须是可比的但是名字不需要相同

比如
查询每个学生以及其选修课程的状况

SELECT Student.*, SC.*
FROM Student, SC
Where Student.Sno=SC.Sno

在上述例子中关系型数据库管理系统执行连接操作会首先在表Student中找到第一个元组然后从头扫描SC表逐一比较两者的Sno这其实和嵌套循环连接算法思想相似而这样的效率是比较低下的。

如果建立索引可以缩短连接查询的时间。还是上述的例子如果在SC表Sno上建立了索引的话就不用每次全表扫描SC了而是根据Student表中元组的Sno值通过索引找到相应的SC元组。这是索引加快查找速度的实例。

在第二章中我们提到了等值连接和自然连接在语句中奖目标列中重复的属性列去掉则为自然连接

2.自身连接

连接操作不仅可以在两个表之间执行也可以是一个表与自己进行连接称为表的自身连接。他和单表查询的区别在于单表查询是单个表查询的而自身连接是两个相同的表比较。示例如下

3.外连接

在一般的连接操作中只有满足连接条件的数组才能输出如果有的行没有数据的话会不出现在连接结果中被舍弃这是就是悬浮数组。但是有时候我们会希望将悬浮数组保存在结果关系内而在对应的空属性上填上NULL。外连接示例如下

SELECT Student.Sno, Sname,Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno)

左外连接列出左边关系中所有的元组右外连接列出右边关系所有的元组。在上述例子中Student中所有的行都会被查出即使其在SC中是空值也不会被置为悬浮数组而是使用NULL进行填充。

4.多表连接

连接操作支持多个表的连接称为多表连接示例如下

SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno=SC.Sno AND SC.Sno=Course.Sno

3.4.3 嵌套查询

在SQL中一个SELECT-FROM-WHERE语句称之为一个查询快将一个查询块嵌套在另一个查询块的WHERE子句或者HAVING短语的条件中的查询称为嵌套查询栗子如下

SELECT Sname FROM Student
WHERE Sno IN (
SELECT Sno FROM SC
WHERE Cno=‘2’);

上例中查询了选了课程号为2的学生的姓名。上层查询块称为外层查询或者父查询下层查询块称之为内层查询或者子查询。在子查询中禁止使用ORDER BY排序只能对最终输出结果排序。

嵌套查询使得用户可以用多个简单查询构成复杂查询从而增强SQL的查询能力。以层层嵌套的方式构造程序是SQL中结构化的含义所在。

1. 带有IN谓词的子查询
此种情况中子查询的结果往往是一个集合因此谓词IN是嵌套查询中最常用的谓词
比如
查询和刘晨在同一个系学习的学生

SELECT * FROM Student
WHERE Sdept IN (
	SELECT Sdept
	FROM Student
	WHERE Sname="刘晨")

有一些嵌套查询可以用连接运算代替有些是不可以代替的。如果子查询的查询条件不依赖父查询这类子查询称为不相关子查询如果子查询的查询条件依赖于父查询则称为相关子查询。如本例中可以改写为等值连接写法

SELECT S1.Sno, S1,Sname, S1.Sdept
FROM Student S1, Student S2
WHERE S1.Sdept=S2.dept AND S2.Sname='刘晨'

因此是不相关子查询。

2.带有比较运算符的子查询
带有比较运算符是指父查询和子查询之间使用比较运算符进行连接使用比较运算符意味着子查询返回值只可以是单个值不可以是多个值的集合。比如上述的例子“查询和刘晨在同一个系学习的学生”可以改造成比较运算符子查询

SELECT * FROM Student
WHERE Sdept = (
	SELECT Sdept
	FROM Student
	WHERE Sname="刘晨")

3.带有ANYSOME或者ALL谓词的子查询
子查询返回单值的时候可以使用比较运算符但是返回多值的时候可以使用ANY/SOME或者ALL其语义如下

比如查询非计算机系中比计算机系中任一学生年龄都小的学生

SELECT Sname, Sage FROM Student
WHERE Sage<ANY(
		SELECT Sage FROM Student
		WHERE Sdept='CS')

4.带有EXISTS谓词的子查询

EXISTS谓词代表∃表示存在。带有EXISTS谓词的子查询不返回任何数据只产生逻辑值true或者false。例子如下

SELECT Sname
FROM Student
WHERE EXISTS
(SELECT * FROM SC
WHERE Sno=Student.Sno AND Cno=‘1’);

使用了EXISTS后如果内层查询为空则外层的WHERE子句返回真否则返回假

EXISTS的子查询目标列表达式一般为*因为EXISTS只做存在性判断选择哪个列进行查询没有意义。除了EXISTS外还有NOT EXISTS谓词作用相反。

3.4.4集合查询

SELECT语句的查询结果是元组的集合所以多个SELECT语句的结果可以进行集合操作。集合炒作主要包括并操作UNION、交操作INTERSECT、差操作EXCEPT

// 示例UNION并操作查询计算机系统年龄小于19的学生
SELECT * FROM Student
WHERE Sdept=‘CS’
UNION
SELECT * FROM Student
WHERE Sage<=19

3.4.5 基于派生表的查询

子查询不仅可以出现在WHERE子句中还可以出现在FROM子句中这时候子查询生成临时派生表例如

SELECT Sno,Cno
FROM SC,(SELECT Sno,Avg(Grade) FROM SC GROUP BY Sno) AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno=Avg_sc.avg_sno AND SC.Grade>=Avg_sc.avg_grade

其中的子句的子查询生成一个派生表Avg_sc。该表有两个属性主查询将SC表和Avg_sc比按照学号进行连接选出课程成绩大于平均成绩的课程号

3.5 数据更新

1.插入数据

SQL的数据插入语句INSERT可以插入一个元组单个插入或者插入一个子查询一次插入多个

元组插入示例

INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES (‘114514’,‘John’,‘m’,‘CS’,18)

此外子查询可以嵌套在insert语句中以生成要插入的批量数据其格式如下

INSERT INTO <表名>(属性列)
子查询

子查询插入示例

// 将高于平均年龄的学生批量插入到Depte_age表中
INSERT INTO Dept_age(Sdept, Avg_age)
SELECT Sdept,AVG(Sage) FROM Student
GROUP BY Sdept

2.修改数据

修改操作又称为更新操作使用UPDATE关键字示例

UPDATE Student SET Sage=22
WHERE Sno=‘114514’

3.删除数据

删除某一元组

DELETE FROM Student
WHERE Sno=‘114514’

将某个表内容全部删除

DELETE FROM SC

此时表会变为空表但是表仍然存在

3.6 空值的处理

空值指的是“不知道”或“不存在”或“无意义”的值。一般使用NULL指定。空值的判断一般在WHERE中使用IS NULL和IS NOT NULL。同时也可以在定义列的时候指定是否可以取空值在列约束条件处使用not null对列加以限制。空值和其他值的算数运算结果也为空然后空值与其他值的比较运算结果为UNKNOWN。

3.7 视图

视图是从一个或者数个表中导出的数据的集合数据库只存放视图的定义而不存放视图对应的数据这些数据依然在原来的表中。

1.定义视图

建立视图语句如下

CREATE VIEW [(col name list)]
AS <子查询>
[WITH CHECK OPTION];

WITH CHECK OPTION表示对视图进行update\insert\delete的操作时要保证更新插入或者删除的行满足视图定义中的谓语条件。
举例建立信息系学生的全部视图

CREATE VIEW IS_Student
AS SELECT Sno, Sname, Sage FROM Student
WHERE Sdept=‘IS’
WITH CHECK OPTION

如果一个视图时从单个基本表到处的并且只是去掉了基本表的某些行和列的但是保留了主码的则称这类视图为行列子集视图上面的IS_Student就是行列子集视图

视图不仅可以建立在表上也可以在几个视图上建立视图或者在几个视图和表上建立新视图

可以使用带有聚集函数和GROUP BY的子句函数来查询定义视图这种视图被称为分组视图

2.删除视图

DROP VIEW <视图名> [CASCADE]

3.查询视图

关系型数据库管理系统执行对视图的检查的时候首先进行有效性检查检查视图所涉及的表、视图是否存在。如果都存在那么从数据字典中取出视图的定义把定义中的视图和用户查询结合起来然后转化为等价的对基本表的查询然后执行该查询。这一转换过程称之为视图消解