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

偏执的太偏执、 2022-04-11 04:23 366阅读 0赞

# 3.1 SQL 概述 #

## 3.1.1 SQL的产生和发展 ##

目前，没有一个数据库系统能够支持SQL标准的所有概念和特性。许多软件厂商对SQL基本命令集还进行了不同程度的扩充和修改，又可以支持标准以外的一些功能特性。

## 3.1.2 ##

### 1. 综合统一 ###

非关系模型(层次模型、网状模型)的数据语言一般都分为：

1.  模式数据定义语言(DDL)：用于定义模式
2.  外模式数据定义语言(外模式DDL或子模式DDL)：定义外模式
3.  数据存储有关的描述语言(DSDL)：定义内模式
4.  数据操纵语言(DML)：进行数据的存取和处置

SQL集数据定义语言、数据操纵语言、数据控制语言的功能于一体，包括以下一系列操作要求：

*  定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库。
 *  对数据库中的数据进行查询和更新。
 *  数据库安全性、完整性控制，以及事务处理。
 *  嵌入式SQL和动态SQL定义。

### 2.高度非过程化 ###

用SQL语言进行数据操作时，只要提出“做什么”而无须指明“怎么做”，减轻了用户负担，有利于提高数据独立性。

### 3. 面向集合的操作方式 ###

操作对象为一条记录，SQL采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。

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

SQL既是独立的语言，又是嵌入式语言。用户可以在终端直接键入SQL命令，也可以嵌入到高级语言程序中，语法基本上是一致的，提供了极大的灵活性与方便性。

### 5. 语言简洁，易学易用 ###

核心功能只用了9个动词，如下表：

<table> 
 <thead> 
  <tr> 
   <th>SQL功能</th> 
   <th align="center">动词</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>数据查询</td> 
   <td align="center">SELECT</td> 
  </tr> 
  <tr> 
   <td>数据定义</td> 
   <td align="center">CREATE、DROP、ALTER</td> 
  </tr> 
  <tr> 
   <td>数据操作</td> 
   <td align="center">INSERT、UPDATE、DELETE</td> 
  </tr> 
  <tr> 
   <td>数据控制</td> 
   <td align="center">GRANT、REMOVE</td> 
  </tr> 
 </tbody> 
</table>

## 3.1.3 SQL的基本概念 ##

数据库三级模式结构，其中外模式包括若干视图(View)和部分基本表(base table)，数据库模式包括若干基本表，内模式包括若干存储文件(stored file)。下图为SQL对关系数据库模式的支持：

![xxx][]

# 3.3 数据定义 #

SQL的数据定义语句表：  
![xxx][]

一个关系数据库管理系统的实例中可以创建多个数据库，一个数据库中可以创建多个模式，一个模式下通常包括多个表、视图和索引等数据库对象。

## 3.3.1 模式的定义与删除 ##

1.  定义模式：模式语句如下：

> CREATE SCHEMA<模式名>AUTHORIZATION<用户名>\[<表定义子句>|<视图定义子句>|<授权定义子句>\];

如果没有指定模式名，那么隐含为用户名。

1.  删除模式：模式语句如下：

> DROP SCHEMA <模式名><CASCADE|RESIRICT>;

其中CASCADE和RESTRICT两者必选其一。选择CASCADE(级联)，表示在删除模式的同时把该模式中所有的数据库对象全部删除；选择RESIRICT(限制)，表示如果该模式中已经定义了下属的数据库对象(如表、视图等)，则拒绝该删除语句的执行。

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

1.  定义基本表

> CREATE TABLE <表名> (<列名><数据类型>\[列级完整性约束条件\]\[,<列名><数据类型>\[列级完整性约束条件\]\]…\[,<表级完整性约束条件>\]);

例子：建立一个”课程“表Cource

CREATE TABLE Cource
        (Cno CHAR(4) PRIMARY KEY,       /*列级完整性约束条件,Cno是主码*/
        Cname CHAR(40) NOT NULL,        /*Cname不能取空值*/
        Cpo CHAR(4),
        Credit SMALLINT,
        FOREIGN KEY (Cpno) REFERENCES Cource (Cno)
            /*表级完整性约束条件,Cpno是外码,被参照表是Cource,被参照列是Cno*/
        );

1.  数据类型  
    下表给出了部分SQL标准支持的数据类型：

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th align="center">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CHAR(n),CHARACTER(n)</td> 
   <td align="center">长度为n的定长字符串</td> 
  </tr> 
  <tr> 
   <td>VARCHAR(n)</td> 
   <td align="center">最大长度为n的变长字符串</td> 
  </tr> 
  <tr> 
   <td>INT,INTEGER</td> 
   <td align="center">长整数(4字节)</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td align="center">短整数(2字节)</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td align="center">大整数(8字节)</td> 
  </tr> 
  <tr> 
   <td>FLOAT(n)</td> 
   <td align="center">可选精度的浮点数,精度至少为n位</td> 
  </tr> 
  <tr> 
   <td>BOOLEAN</td> 
   <td align="center">逻辑布尔量</td> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td align="center">日期,包含年月日,格式为YYYY-MM-DD</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td align="center">时间,包含时分秒,格式为HH:MM:SS</td> 
  </tr> 
 </tbody> 
</table>

### 1. 模式与表 ###

定义基本表一般有三种方法定义它所属的模式：

1.  在表名中明显给出模式名

> CREATE TABLE “S-T”.Student(…); /*Student所属模式是S-T*/

1.  在创建模式语句中同时创建表

3 .设置所属的模式，这样在创建表时表明中不必给出模式名

### 2. 修改基本表 ###

ALTER TABLE <表名>
    [ADD [COLUMN] <新列名><数据类型> [完整性约束]]
    [ADD <表级完整性约束>]
    [DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
    [DROP CONSTRAINT <完整性约束> [RESTRICT|CASCADE]]
    [ALTER COLUMN <列名><数据类型>];

### 3. 删除基本表 ###

DROP TABLE <表名> [RESTRICT|CASCADE];

若选择RESTRICT，则该表的删除是有限制条件的。

若选择CASCADE，则该表的删除没有限制条件。在删除基本表的同时。相关的依赖对象，例如视图，都将被一起删除。

## 3.3.3 索引的建立与删除 ##

当表的数据量比较大时，查询操作会比较耗时。建立索引可以加快查询速度。数据库索引有多种类型，常见索引包括文件上的索引、B+树索引、散列(HASH)索引、位图索引等。(了解一下)

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

### 1. 建立索引 ###

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

表名是要建立的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号隔开。每个列名后可以用次序指定索引值得排列次序，可选ASC(升序)或DESC(降序)，默认值是升序。

UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。

CLUSTER表示要建立的索引是聚簇索引。

### 2.修改索引 ###

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

### 3. 删除索引 ###

DROP INDEX <索引名>;

## 3.3.4 ##

数据字典是关系数据库管理系统内部的一组系统表，记录了数据库所有的定义信息。关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。

# 3.4 数据查询(重难点) #

SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] ...
    FROM <表名或视图名> [,<表名或视图名>...]|(<SELECT语句>)[AS] <别名>
    [WHERE <条件表达式>]
    [GROUP BY <列名1> [HAVING<条件表达式>]]
    [ORDER BY <列名2> [ASC|DESC]];

含义是：根据WHERE子句的条件表达式从FROM子句指定的基本表中找出满足条件的元组，再按SELECT子句中的目标列表达式选出元组中的属性值形成结果表。如果有GROUP BY子句，则将结果按列名1进行分组，该属性列值相等的元组为一个组。如果有ORDER BY子句，则结果表还要按列名2的值得升序或降序排序。

**注意：索引的ASC|DESC是建立对象时方便查询的，而查询的ORDER BY的ASC|DESC是对结果表进行的一次排序。**

## 3.4.1 单表查询 ##

### 1. 选择表中的若干列 ###

1.  查询指定列

SELECT Sno,Sname
    FROM Student;

1.  查询全部列

SELECT *
    FROM Student;

1.  查询经过计算的列  
    目标列表达式不仅可以是表中的属性列，还可以是表达式

SELECT Sname,2017-Sage
    FROM Student;

### 2. 选择表中的若干元组 ###

1.  消除取值重复的行

SELECT DISTINCT Sno
    FROM SC;

1.  查询满足条件的元组

<table> 
 <thead> 
  <tr> 
   <th>查询条件</th> 
   <th align="center">谓词</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>比较</td> 
   <td align="center">=, &gt;, &lt;, &gt;=, &lt;=, !=, &lt;&gt;, !&gt;, !&lt; ; NOT+上述比较运算符</td> 
  </tr> 
  <tr> 
   <td>确定范围</td> 
   <td align="center">BETWEEN AND, NOT BETWEEN AND</td> 
  </tr> 
  <tr> 
   <td>确定集合</td> 
   <td align="center">IN, NOT IN</td> 
  </tr> 
  <tr> 
   <td>字符匹配</td> 
   <td align="center">LIKE, NOT LIKE</td> 
  </tr> 
  <tr> 
   <td>空值</td> 
   <td align="center">IS NULL, IS NOT NULL</td> 
  </tr> 
  <tr> 
   <td>多重条件</td> 
   <td align="center">AND, OR, NOT</td> 
  </tr> 
 </tbody> 
</table>

--------------------

1.  比较大小

SELECT Sname
    FROM Student
    WHERE Sdept='CS';

1.  确定范围

SELECT Sname,Sdept,Sage
    FROM Student
    WHERE Sage BETWEEN 20 AND 25;

1.  确定集合

SELECT Sname,Ssex
    FROM Student
    WHERE Sdept IN ('CS','MA','IS');

1.  字符匹配

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

匹配串可以是一个完整的字符串，也可以含有通配符%和\_(模糊查询)。

*  %代表任意长度的字符串
 *  \_下划线代表任意单个字符
 *  \[\]指定范围( \[a-f\] 或 \[abcdef\] )中的任何单个字符

SELECT *
    FROM Student
    WHERE Sname LIKE '刘%';

如果用户要查询的字符串本身就含有通配符，就要使用ESCAPE '换码字符’短语对通配符进行转义了。

SELECT Cno,Ccredit
    FROM Student
    WHERE Cname LIKE 'DB\_Design' ESCAPE '\';

1.  涉及空值的查询

SELECT Sno,Cno
    FROM SC
    WHERE Grade IS NULL     /*不能写 = null*/

1.  多重条件查询

SELECT Sname
    FROM Student
    WHERE Sdept='CS' AND Sage<20;

--------------------

1.  ORDER BY 子句

SELECT Sno,Grade
    FROM SC
    WHERE Cno='3'
    ORDER BY Grade DESC;

对于空值，排序时显示的次序由具体系统实现来决定。

1.  聚集函数  
    SQL提供了许多聚集函数，主要有：

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th align="center">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>COUNT(*)</td> 
   <td align="center">统计元组个数</td> 
  </tr> 
  <tr> 
   <td>COUNT( [DISTINCT/ALL] &lt;列名&gt; )</td> 
   <td align="center">统计一列中值的个数</td> 
  </tr> 
  <tr> 
   <td>SUM( [DISTINCT/ALL] &lt;列名&gt; )</td> 
   <td align="center">计算一列值的总和(必须是数值型)</td> 
  </tr> 
  <tr> 
   <td>AVG( [DISTINCT/ALL] &lt;列名&gt; )</td> 
   <td align="center">计算一列值得平均值(数值型)</td> 
  </tr> 
  <tr> 
   <td>MAX( [DISTINCT/ALL] &lt;列名&gt; )</td> 
   <td align="center">求一列值中的最大值</td> 
  </tr> 
  <tr> 
   <td>MIN( [DISTINCT/ALL] &lt;列名&gt; )</td> 
   <td align="center">求一列值中的最小值</td> 
  </tr> 
 </tbody> 
</table>

当聚集函数遇到空值时，除COUNT(\*)外，都跳过空值而只处理非空值。

**注意：WHERE子句是不能用聚集函数作为条件表达式的。聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。**

1.  GROUP BY 子句  
    对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果（毫无实际意义）。分组后聚集函数将作用于每一个组，即每一个组都有一个函数值。用GROUP BY子句后，SELECT中的属性列只能有标签属性（就是GROUP BY子句后跟的属性列）和聚集函数。

## 3.4.2 连接查询 ##

若一个查询同时涉及两个以上的表，则称之为连接查询。

### 1. 等值与非等值连接查询 ###

连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词。比较运算符有=、>、<、>=、<=、!=或<>等。当连接运算符为=时，成为等值连接。使用其他运算符称为非等值连接。连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。对于两表或多表中名字唯一的属性列前不用加前缀，但相同属性名的引用必须加上表名前缀。如：

select Student.Sno,Sname,Sage
    from Student,SC
    where Student.Sno=SC.Sno  /*Sno属性列重名，引用时加上表名前缀防止产生歧义*/

### 2. 自身连接 ###

连接操作还可以时一个表与其自己进行连接，称为表的自身连接。当然，自身连接中要为同表取两个别名，一个是FIRST，另一个是SECOND（并没有严格的命名要求，能区别两表即可）。

### 3. 外连接 ###

在通常的连接操作中，只有满足条件的元组才能作为结果输出。有时我们想把某表的悬浮元组保存在结果关系中，而在另一个表中的属性上填控制NULL，这时需要用外连接。左外连接列出左边关系中所有的元组，右外连接列出右边关系中所有的元组。全外连接不仅返回左表中不符合连接条件单符合查询条件的数据行，还返回表中不符合连接条件单符合查询条件的数据行，实际上就是左外连接和右外连接的数学合集（去掉重复），即全外连接=左外连接 UNION 右外连接。  
外连接格式为：

/*a,b,c,d均是属性列，A,B是表*/
    /*下列代码依次对应左外连接右外连接和全连接，其中的outer可以缺省*/
    select A.d,a,b,c
    from A left/right/full [outer] join B on (A.d=B.d);

## 3.4.2 嵌套查询 ##

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

注意：子查询的SELECT语句中不能使用ORDER BY子句。

1.  带有IN谓词的子查询

若子查询的查询条件不依赖于父查询，称为不相关查询。如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询，整个查询语句称为相关嵌套查询。

1.  带有比较运算符的子查询

求解相关子查询不能像求解不相关子查询一样一次将子查询求解出来，然后求解父查询，内层查询由于与外层查询有关，因此必须反复求值。

1.  带有ANY或ALL谓词的子查询

子查询返回单值时可以用比较运算符，但返回多值时要用比较运算符+ANY或ALL谓词修饰符。

1.  带有EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生true或false。