【数据库系统】数据库系统概论====第三章 关系数据库标准语言SQL

【数据库系统】数据库系统概论====第三章 关系数据库标准语言SQL

3.1SQL概述

SQL（Structured Query Language）:结构化查询语言，是关系数据库的标准语言。
SQL是一个通用的、功能极强的关系数据库语言。

3.1.1SQL的产生与发展

SQL标准的进展过程：

3.1.2SQL的特点

1. 综合统一
   （1）集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。
   （2）可以独立完成数据库生命周期中的全部活动：
   定义关系模式，插入数据，建立数据库；对数据库中的数据进行查询和更新；数据库重构和维护；数据库安全性、完整性控制等。
   （3）用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。
   （4）数据操作符统一。
2. 高度非过程化
   （1）菲关系数据模型的数据操纵语言“面向过程”，必须制定存取路径。
   （2）SQL只要提出“做什么”，无须了解存取路径。
   （3）存取路径的选择以及SQL的操作过程由系统自动完成。
3. 面向集合的操作方式
   （1）非关系数据模型采用面向记录的操作方式，操作对象是一条记录。
   （2）SQL采用集合操作方式
   操作对象、查找结果可以是元组的集合；
   一次插入、删除、更新操作的对象可以是元组的集合。
4. 以同一种语法结构提供多种使用方式
   （1）SQL是独立的语言
   能够独立地用于联机交互的使用方式。
   （2）SQL又是嵌入式语言
   SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用。
5. 语言简洁，易用易学
   SQL功能极强，完成核心功能只用了9个动词。
   

3.1.3SQL基本概念

SQL支持关系数据库三级模式结构。

基本表
①本身独立存在的表；
②SQL中一个关系就对应一个基本表；
③一个（或多个）基本表对应一个存储文件；
④一个表可以带若干索引。
存储文件
①逻辑结构组成了关系数据库的内模式；
②物理结构是任意的，对用户透明。
视图
①从一个或几个基本表导出的表；
②数据库中只存放视图的定义而不存放视图对应的数据；
③视图是一个虚表；
④用户可以在视图上再定义视图。

3.2学生-课程数据库

学生-课程模式S-T：

3.3数据定义

SQL的数据定义功能：模式定义、表定义、视图和索引的定义。

3.3.1模式的定义与删除

1. 定义模式
   语句格式如下：
   CREATE SCHEMA <模式名> AUTHORIZATION <用户名>；
   说明：
   ①若没有指定模式名，那么<模式名>隐含为<用户名>。
   ②在CREATE SCHEMA中科院接受CREATE TABLE，CREATE VIEW和GRANT子句。格式如下：
   CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>]|<视图定义子句>|<授权定义子句>]；
   ③执行创建模式语句必须拥有DBA权限，或者DBA授予在CREATE SCHEMA的权限。
2. 删除模式
   语句格式：DROP SCHEMA <模式名> <CASCADE|RESTRICT>；
   说明：
   ①CASCADE和RESTRICT必须二选一。
   ②CASCADE（级联）：删除模式的同时把该模式中所有的数据库对象全部删除。
   ③RESTRICT（限制）：如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。当该模式中没有任何下属的对象时才能执行。

3.3.2基本表的定义、删除与修改

1. 定义基本表
   语句格式：
   CREATE TABLE <表名>
   （<列名> <数据类型>[<列级完整性约束条件>]
   [，<列名><数据类型>[<列级完整性约束条件>]]…
   [，<表级完整性约束条件>]）；
   说明：
   如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。
2. 数据类型
   
3. 模式与表
   每一个基本表都属于某一个模式，一个模式包含多个基本表。
   创建基本表（其他数据库对象也一样）时，若没有指定模式，系统根据搜索路径来确定该对象所属的模式。
   ①显示当前的搜索路径：
   SHOW search_path;
   ②搜索路径的当前默认值是：
   $user，PUBLIC；
   ③DBA用户可以设置搜索路径。
   SET search_path TO “S-T”，PUBLIC；（"S-T"是模式名）
   若搜索路径中的模式名都不存在，系统将给出错误。
   若搜索路径中的存在模式，RDBMS会使用模式列表中第一个存在的模式作为数据库对象的模式名。
   创建基本表
   （以定义一个学生-课程模式S-T为例）
   ①创建表是给出模式名。
   CREATE TABLE “S-T” .Student(…)；/模式名为S-T/
   CREATE TABLE “S-T” .CourdeStudent(…)；
   CREATE TABLE “S-T” .SCt(…)；
   ②再创建模式语句中同时创建表。
   
   ③设置所属模式，再创建表名中不必给出模式名。
   DBA用户设置搜索路径，然后定义基本表。
   SET search_path TO “S-T”，PUBLIC；
   CREATE TABLE Student (…);
   执行结果：建立了S-T.Student基本表。
4. 修改基本表
   语句格式：
   ALTER TABLE <表名>
   [ADD [COLUMN] <新列名> <数据类型> [完整性约束]]
   [ADD <表级完整性约束>]
   [DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
   [DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE]]
   [ALTER COLUMN <列名> <数据类型>]；
   说明：
   ①<表名>是要修改的基本表。
   ②ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。
   ③DROP COLUMN子句用于删除表中的列。
   如果指定了CASCADE短语，则自动删除引用该列的其他对象。
   如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列。
   ④DROP CONSTRAINT子句用于删除指定的完整性约束条件。
   ⑤ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型。
   不论基本表中原来是否已有数据，新增加的列一律为空值。
5. 删除基本表
   语句格式：
   DROP TABLE <表名>[RESTRICT|CASCADE]；
   说明：
   ①RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用。如果存在依赖该表的对象，则此表不能被删除。
   ②CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除。
   ③基本表定义被删除，数据被删除，表上建立的索引、视图、触发器等一般也将被删除。
   DROP TABLE时，SQ2011与3个RDBMS的处理策略比较
   

3.3.3索引的建立与删除

建立索引的目的：加快查询速度。
谁可以建立索引：DBA或表的属主（即建立表的人）。
DBMS一般会自动建立以下列上的索引：
PRIMARY KEY；
UNIQUE；
谁维护索引：DBMS自动完成。
使用索引：DBMS自动选择是否使用索引及使用那些索引。
RDBMS中索引一般采用B+树、HASH索引来实现。
B+树索引具有动态平衡的优点，HASH索引具有查找速度快的特点。采用B+树还是HASH索引则由具体的RDBMS来决定。
索引是关系数据库内部实现技术，属于内模式的范畴。
CREATE INDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引。

1. 建立索引
   语句格式：
   CREATE [UNIQUE] [CLUSTER] INDEX<索引名> ON <表名> （<列名>[<次序>][，<列名>[<次序>]]…）；
   说明：
   ①UNIQUE表明此索引每一个索引值只对应唯一的数据。
   ②CLUSTER表示要建立的索引是聚簇索引。聚簇索引是指索引顺序与表中记录的物理顺序一致的索引组织。
   在最经常查询的列上建立聚簇索引以提高查询效率；
   一个基本表上最多只能建立一个聚簇索引；
   经常更新的列不宜建立局促索引。
2. 删除索引
   语句格式：
   DROP INDEX <索引名>；
   删除索引时，系统会从数据字典中删去有关该索引的描述。

3.3.4数据字典

数据字典是关系数据库管理系统内部的一组系统表。
数据字典记录了数据库中所有的定义信息，包括模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。
RDBMS执行SQL数据定义时，实际就是更新数据字典。

3.4数据查询

语句格式：
SELECT [ALL|DISTINCT]<目标列表达式>
[，<目标列表达式>]…
FROM <表名或视图名>[，<表名或视图名>]…
[WHERE<条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC|DESC]]；
注意：
①语句中的字母不分大小写。
②语句中的“，；”等标点符号为英文状态下的半角字符。
③[ ]中的内容，不是语句必须的内容，只有为了实现某些功能时才添加。

3.4.1单表查询。

功能：对一个表的内容进行查询。

1. 选择表中的若干列
   （1）查询指定列
   格式：在SELECT后面指定列名，FROM后面列所在的表名。
   （2）查询全部列
   功能：选出表中所有属性列。
   格式：在SELECT关键字后面列出所有列名或将<目标列表达式>指定为*。
   （3）查询经过计算的值
   功能：选出表中指定的属性列，并经过计算后输出。
   格式：SELECCT子句的<目标列表达式>可以为：
   （1）算术表达式
   （2）字符串常量
   （3）函数
   （4）列别名
2. 选择表中的若干元组
   （1）消除取值重复的行
   如果没有指定DISTINCT关键字，则缺省为ALL。
   （2）查询满足条件的元组
   查询满足条件的元组可以通过where子句实现，where常用的查询条件如下：
   
   ①比较大小
   ②确定范围
   谓词：BETWEEN…AND…和NOT BETWEEN…AND…
   ③确定集合
   谓词：IN<值表>，NOT IN<值表>
   ④字符匹配
   谓词：[NOT] LIKE’<匹配串>’ [ESCAPE ‘<换码字符>’]
   说明：
   匹配串为固定字符串。
   匹配串为含通配符的字符串。
   使用换码字符串将通配符转义为普通字符。
   ⑤涉及空值的查询
   谓词：IS NULL或IS NOT NULL
   "IS"不能用“=”代替
   多重条件查询
   用逻辑运算符AND和OR来联结多个查询条件，AND的优先级高于OR，可以用括号改变优先级。
   可用来实现多种其他谓词：
   [NOT] IN
   [NOT] BETWEEN…AND…
3. ORDER BY子句
   ORDER BY子句可以按一个或多个属性列排序：
   升序：ASC；
   降序：DESC；
   缺省值为升序。
   当排序列含空值时：（空值默认为最大值）
   ASC：排序列为空值的元组最后显示。
   DESC：排序列为空值的元组最先显示。
4. 聚集函数
   COUNT （[DISTINCT|ALL]*） 统计元组个数
   COUNT ([DISTINCT|ALL]<列名>) 统计一列中值的个数
   SUM （[DISTINCT|ALL]<列名>） 计算一列值的总和（此列必须是数值型）
   AVG （[DISTINCT|ALL]<列名>） 计算一列值的平均值（此值必须是数值型）
   MAX （[DISTINCT|ALL]<列名>） 求一列值的最大值
   MIN （[DISTINCT|ALL]<列名>） 求一列值的最小值
   注意：
   WHERE子句中是不能用聚集函数作为条件表达式的。聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。
5. GROUP BY子句
   GROUP BY子句的作用是：按照指定的一列或多列值分组，值相等的为一组，来细化聚集函数的作用对象。
   说明：
   ①未对查询结果分组，聚集函数将作用于整个查询结果。
   ②对查询结果分组后，聚集函数将分别作用于每个组。
   GROUP BY子句分组后，可以使用HAVING短语指定筛选条件。
   HAVING短语与WHERE子句的区别：
   ①作用对象不同：WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。
   ②WHERE子句中是不能用聚集函数作为条件表达式的。

3.4.2连接查询

1. 等值与非等值连接查询
   连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式：
   格式一：
   [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>；
   其中，比较运算符有：=、>、<、>=、<=、!=
   格式二：
   [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>；
   注意：
   ①当连接运算符为“=”称为等值连接，其他运算符称为非等值连接。
   ②连接谓词中的列名称为连接字段，并且各连接字段类型必须是可比的，但名字不必是相同的。
   连接操作的一种执行方法：嵌套循环法（NESTED-LOOP）
   a.首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。
   b.表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。
   c.重复上述操作，直到表1中的全部元组都处理完毕。
   自然连接：若在等值连接中把目标列中重复的属性列去掉则为自然连接。
2. 自身连接
   定义：一个表与其自己进行连接。
   说明：
   ①需要给表起别名以示区别。
   ②由于所有属性名都是同名属性，因此必须使用别名前缀。
3. 外连接
   普通连接与外连接的区别：
   普通连接操作只输出满足连接条件的元组。
   外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出。
   外连接分为：
   左外连接：列出左边关系中所有的元组。
   LEFT OUT JOIN 表名 ON
   右外连接：列出右边关系中所有的元组。
   RIGHT OUT JOIN 表名 ON
4. 多表连接
   定义：连接操作是两个以上的表进行连接。

3.4.3嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块。
嵌套查询定义：是指将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询。
说明：
①子查询中不能使用ORDER BY子句。
②层层嵌套方式反映了SQL语言的结构化。
③有些嵌套查询可以用连接运算替代。
④外层查询（父查询）、内层查询（子查询）。

1. 带有IN谓词的子查询
   在嵌套查询中，子查询的结果往往是个集合，用IN谓词表示父查询的条件在子查询结果的集合中。
   说明：
   ①不相关子查询：子查询的查询条件不依赖于父查询。
   ②相关子查询：子查询的查询条件依赖于父查询，整个查询语句称为嵌套查询。
2. 带有比较运算符的子查询
   当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=，或<>）。
3. 带有ANY（SOME）或ALL谓词的子查询
   谓词语义：
   ANY----任意一个值
   ALL----所有值
   需要配合使用比较运算符：

>ANY 大于子查询结果中的某个值
>ALL 大于子查询结果中的所有值
<ANY 小于子查询结果中的某个值
<ALL 小于子查询结果中的所有值
≥ANY 大于等于子查询结果中的某个值
≥ALL 大于等于子查询结果中的所有值
≤ANY 小于等于查询结果中的某个值
≤ALL 小于等于子查询结果中的所有值
=ANY 等于查询结果中的某个值
=ALL 等于子查询结果中的所有值（通常没有实际意义）
!=（或<>）ANY 不等于子查询结果中的某个值
!=（或<>）ALL 不等于子查询结果中的任何一个值
说明：
用聚集函数实现子查询要比直接用ANY、ALL效率更高。ANY、ALL谓词与聚集函数、IN谓词的等价转换关系如下表。

4. 带有EXISTS谓词的子查询
（1）EXISTS谓词
EXISTS谓词代表存在量词∃，带有EXISTS谓词的子查询只返回逻辑真值“TRUE"或逻辑假值”FALSE“。
说明：
（1）使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值；否则返回假值。
（2）由EXISTS引出的子查询目标列表都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。
（2）NOT EXISTS
说明：
若内层查询结果非空，则外层的WHERE子句返回假值；
若内层查询结果为空，则外层的WHERE子句返回真值。
（3）不同形式的查询间的替换
①一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换；
②所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
（4）用EXISTS/NOT EXISTS实现全称量词
SQL语言中没有全称量词∀。可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：
（∀x）P≡┐（∃x（┐P））
（5）用EXISTS/NOT EXISTS实现逻辑蕴含
SQL语言中没有蕴含逻辑运算，可以利用谓词演算将逻辑蕴含谓词等价转换为：
P → Q ≡ ┐P ∨ Q

3.4.4集合查询

集合操作的种类：并操作、交操作、差操作

1. 并操作UNION
   UNION：将多个查询结果合并起来，系统自动去掉重复元组。
   UNION ALL：将多个查询结果合并起来时，保留重复元组。
2. 交操作INTERSECT
3. 差操作EXCEPT
   说明：
   参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

3.4.5基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。
如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其默认属性。
注意：通过FROM子句生成派生表时，AS关键字可以省略，但必须为派生关系指定一个别名。而对于基本表，别名是可选择项。

3.4.6SELECT语句的一般格式

SELECT [ALL|DISTINCT]
<目标列表达式>[别名][，<目标列表达式>[别名]]…
FROM<表名或视图名>[别名]
[，<表名或视图名>[别名]]…
[WHERE<条件表达式>]
[GROUP BY<列名1>
[HAVING<条件表达式>]]
[ORDER BY<列名2>ASC|DESC]]

3.5数据更新

3.5.1插入数据

1. 插入元组
   语句格式
   INSERT INTO <表名> [(<属性列1>[，<属性列2>]…)]
   VALUES（<常量1>[，<常量2>]…）；
   功能：将新元组插入指定表中。
   说明：
   INTO子句：属性列的顺序可与表中的顺序不一致，没有指定属性列的默认插入全部。
   VALUES子句：提供的值必须与INTO子句匹配，值与属性列的个数和值的类型要一致。
2. 插入子查询结果
   语句格式：
   INSERT INTO<表名>[[（<属性列1>[，<属性列2>]…）]；
   子查询；
   功能：将子查询结果插入指定表中。
   说明：
   SELECT子句目标列必须与INTO子句匹配，值的个数、类型都要一致。

3.5.2修改数据

语句格式
UPDATE<表名>
SET<列名>=<表达式>[，<列名>=<表达式>]…[WHERE<条件>]；
功能：修改指定表中满足WHERE子句条件的元组。
说明：
①SET子句：指定修改方式，修改的列，修改后取值。
②WHERE：指定要修改的元组，缺省表示修改所有元组。
③在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则。

1. 修改某一个元组的值
2. 修改多个元组的值
3. 带子查询的修改语句

3.5.3删除数据

语句格式
DELETE FROM<表名> [WHERE<条件>]；
功能：删除指定表中满足WHERE子句条件的元组。
说明：
WHERE子句：指定要删除的元组，缺省表示要删除表中的全部元组，表的定义仍在。

1. 删除某一个元组的值
2. 删除多个元组的值
3. 带子查询的删除语句

3.6空值的处理

空值的存在是因为取值有不确定性，对关系运算带来特殊的问题，所以需要做特殊的处理。
SQL语言中允许某些元组的某些属性取空值，一般有以下几种情况：
①该属性有值，但当前不知道它的具体值。
②该属性不应该有值。
③由于某种原因不便于填写。

1. 空值的产生
2. 空值的判断
3. 空值的约束条件
   ①属性定义（或者域定义）中有NOT NULL约束条件的不能取空值；
   ②加了UNIQUE限制的属性不能取空值；
   ③码属性不能取空值。
4. 空值的算术运算、比较运算和逻辑运算
   算数运算：空值与另一个值（包括另一个空值）的算术运算的结果为空值；
   比较运算：空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN;
   逻辑运算：
   
   其中，T表示TRUE，F表示FALSE，U表示UNKNOWN

3.7视图

1. 视图的特点：
   （1）视图是虚表，是从一个或几个基本表（或视图）导出的。
   （2）只存放视图的定义，不存放视图对应的数据。
   （3）表中的数据发生变化，视图中查询出的数据也随之改变。
2. 基于视图的操作：查询、删除、受限更新、定义基于该视图的新视图。

3.7.1定义视图

1. 建立视图
   （1）语句格式
   CREATE VIEW<视图名> [（<列名>[，<列名>]…）] AS <子查询> [WITH CHECK OPTION]；
   说明：
   ①组成视图的属性列名：全部省略或全部指定。
   ②子查询不允许含有ORDER BY子句和DISTINCT短语。
   ③RDBMS执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。
   ④在对视图查询时，按视图的定义从基本表中将数据查出。
   （2）基于多个基表的视图
   （3）基于视图的视图
   （4）带表达式的视图
   （5）分组视图
   （6）不指定属性列
2. 删除视图
   语句的格式：
   DROP VIEW<视图名>[CASCADE]；
   说明：
   ①该语句从数据字典中删除指定的视图定义。
   ②如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除。
   ③删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除。

3.7.2查询视图

视图定义后，用户就可以像基本表一样对视图进行查询了。

1. RDBMS实现视图查询的方法----视图消解法
   第一步：进行有效性检查。
   第二步：转换成等价的对基本表的查询。
   第三步：执行修正后的查询。
2. 视图消解法的局限
   视图消解法不能生成正确查询。

3.7.3更新视图

说明：
更新视图是指通过视图来插入、删除和数据，因为视图不适宜存储数据，因此对视图的更新操作将通过视图消解，转化为对实际表的更新操作。
注意：
为防止在更新视图时出错，定义视图时要加上WITH CHECK OPTION子句。
更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新。

3.7.4视图的作用

1. 视图能够简化用户的操作。
2. 视图使用户能以多种角度看待同一数据。
3. 视图对重构数据库提供了一定程度的逻辑独立性。
4. 视图能够对机密数据提供安全保护。
5. 适当的利用视图可以更清晰地表达查询。