数据库概念复习

数据库系统概念复习

第三章SQL

SQL查询语言概览

SQL语言有以下几个部分：
数据定义语言（Data-Definition Language，DDL）：SQL DDL提供定义关系模式，删除关系以及修改关系模式的命令
数据操纵语言（Data-Manipulation Language，DML）：SQL DML提供从数据库中查询信息，以及在数据库中插入元组，删除元组，修改元组的能力
完整性（integrity）：SQL DDL包括定义完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性约束。破环完整性约束的更新是不允许的。
视图定义（view definition）：SQL DDL包括定义视图的命令
事务控制（transaction control）：SQL包括定义事务的开始和结束的命令。
嵌入式SQL和动态SQL（embedded SQL and dynamic SQL）：嵌入式和动态SQL定义SQL语句如何嵌入到通用编程语言，如C，C++和Java中。
授权（authorization）：SQL DDL包括定义对关系和视图的访问权限的命令。

SQL数据定义

基本类型

SQL标准支持多种固有类型，包括：
char(n):固定长度的字符串，用户指定长度n。也可以使用全称character。
varchar(n)：可变长度的字符串，用户指定最大长度n,等价于全称character varying。
int：整数类型（和机器相关的整数类型的子集），等价于全称integer。
smallint:小整数类型（和机器相关的整数类型的子集）。
numeric（p，d）：定点数，精度由用户指定。这个数有p位数字（加上一个符号位），其中d位数字在小数点右边。所以在一个这种类型的字段上，numeric（3，1）可以精确存储44.5，但不能精确存储44.5或者0.32这样的数
real，double precision:浮点数与双精度浮点数，精度与机器相关
float（n）：精度至少为n位的浮点数
每种类型都可能包含一个被称作空值的特殊值。空值表示一个缺失的值，该值可能存在但并不为人所知，或者可能根本不存在。

基本模式定义

我们用create table命令定义SQL关系。下面的命令在数据库中创建了一个department关系

create table department
         (dept_name varchar(20),
         building varchar(15),
         budget numeric(12,2),
         primary key(dept_name));

上面创建的关系具有三个属性，dept_name是最大长度20的字符串，building是最大长度为15的字符串，budget是一个12位的数，其中2位数字在小数点后面。create table命令还指明了dept_name属性是department关系的主码。
create table命令的通用形式是：

create table r
           (A1, D1,
           A2, D2,
           ----
           An, Dn
           <完整性约束1>，
           ----
           <完整性约束k>);

其中r是关系名
每个Ai是关系r模式中的一个属性名，Di是属性Ai的域，也就是说Di指定了属性Ai的类型以及可选的约束，用于限制所允许的Ai取值的集合。
在本章的所有SQL语句都是用分号结束的，在很多SQL实现中，分号是可选的。
SQL支持许多不同的完整性约束。我们只讨论少数几个：
primary key（Aj1,Aj2，… ，Ajm）:primary-key声明表示属性Aj1,Aj2,…,Ajm构成关系的主码。主码属性必须非空且唯一，也就是说没有一个元组在主码属性上取空值，关系中也没有两个元组在所在主码属性上取值相同。
foreign key(Ak1,Ak2，…，Akn)references：foreign key声明表示关系中任意元组在属性（Ak1，Ak2，…，Akn）上的取值必须对应于关系s中某元组在主码属性上的取值。
**not null：**一个属性上的not null约束表明在该属性上不允许空值。换句话说，此约束把空值排除在该属性月之外
SQL禁止破环完整性约束的任何数据库更新

SQL查询的基本结构

SQL查询的基本结构由三个子句构成：select，from和where。查询的输入是在from子句中列出的关系，在这些关系上进行where和select子句中指定的运算，然后产生一个关系作为结果。

单关系查询

select 属性名
from 关系名
可以在select后面加入关键词distinct去除重复，SQL保留重复元组是默认的，所以虽然可以使用关键词all来显式指明不取出重复，但是一般不会使用all
select子句中还可带含有+，-，*，/运算符的算术表达式，运算对象可以是常数或元组的属性
where子句允许我们只选出那些在from子句的结果关系中满足特定谓词的元组。
**SQL允许在where子句中使用逻辑连词and，or和not。**逻辑连词的运算对象可以是包含比较yu’i运算符<,<=,>,>=,=和<>的表达式
SQL允许我们使用比较运算发来比较字符串，算术表达式以及特殊类型，如日期类型

多关系查询

我们考虑涉及多个关系的SQL查询的通用形式。一个SQL查询可以包括三类型的子句：select子句，from子句和where子句。每种子句的作用如下：
select子句用于列出查询结果中所需要的属性
from子句是一个查询求值中需要访问的关系列表
where子句是一个作用在from子句中关系的属性上的谓词
一个典型的SQL查询具有如下形式：

select A1，A2， ... ，An
from r1，r2，...，rm
where P;

每个Ai代表一个属性，每个ri代表一个关系。P是一个谓词。如果省略where子句，则谓词P为true
通过from子句定义了一个在该子句所列出关系上的笛卡儿积，其结果可能是一个非常庞大的关系，创建这样的笛卡尔积通常是没有意义的。
反之，where子句中的谓词用来限制笛卡尔积所建立的组合，只留下那些对所需答案有意义的组合。

自然连接

自然连接运算作用于两个关系，并产生一个关系作为结果。不同于两个关系上的笛卡尔积，它将第一个关系的每个元组与第二个关系的所有元组都进行连接；自然连接只考虑那些在两个关系模式中都出现的属性上取值相同的元组对。
在一个SQL查询的from子句中，可以用自然连接将多个关系结合在一起，如下所示：

select A1，A2， ... ，An
from r1 natural join r2 natural join ... natural join rm
where P;

为了发挥自然连接的优点，同时避免不必要的相等属性带来的危险，SQL提供了一种自然连接的构造形式，允许用户来指定需要那些列相等。下面的查询说明了这个特征：

select name，title
from （instructor natural join teaches）join course using（course_id）；

附加的基本运算

更名运算

由于from子句中的两个关系可能存在同名属性，在这种情况下，结果中就会出现重复的属性名；其次，如果我们在select子句中使用算术表达式，那么结果属性就没有名字；再次，我们也许要改变结果中的属性名字。因此，SQL提供了一个重命名结果关系中属性的方法。即使用如下形式的as子句：

old-name as new-name 

as子句既可以出现在select子句中，也可以出现在from子句中
用来重命名关系的标识符在SQL标准中被称作相关名称，但通常也被称作表别名，或者相关变量，或者元组变量

字符串运算

在字符串上可以使用like操作符来实现模式匹配。我们使用两个特殊的字符来描述模式：
百分号（%）：匹配任意字串
下划线（_）：匹配任意一个字符
模式是大小写敏感的，也就是说，大写字符与小写字符不匹配。
为了将该特殊字符当成普通字符，可以使用反斜线（\）作为转义字符
SQL允许使用not like比较运算符搜寻不匹配项

select子句中的属性说明

**“*”**可以用在select子句中表示“所有的属性”

排列元组的显示次序

order by子句可以让查询结果中元组按排列顺序显示，默认使用升序。可以用desc表示降序，或者用asc表示升序

where子句谓词

为了简化where子句，SQL 提供了between比较运算符来说明一个值是小于或等于某个值，同时大于或等于另一个值的。
类似的，我们还可以使用not between比较运算符。

集合运算

SQL作用在关系上的union,intersect和except运算对应于数学集合论中的∪,∩,－运算

并运算

union运算自动去除重复
想保留所有重复，就必须使用union all代替union

交运算

intersect运算自动去除重复
同union保留重复

差运算

同并，交运算

空值

如果算术表达式的任一输入为空，则该算术表达式（涉及诸如+，-，*或/）结果为空
SQL涉及空值的任何比较运算的结果视为unknown，这是true和false之外的第三个逻辑值
由于在where子句的谓词中可以对比较结果使用诸如and，or和not的布尔运算，所以这些布尔运算的定义也被扩展到可以处理unknown值
and:true and unknown的结果是unknown，false and unknown结果是false，unknown and unknown的结果是unknown
or: true or unknown = true false or unknown = unknown unknown or unknown = unknownn
not:not unknown的结果是unknown

聚集函数

聚集函数是以值的一个集合（集或多重集）为输入，返回单个值的函数。
平均值：avg
最小值：min
最大值：max
总和：sum
计数：count

基本聚集

分组聚集

为了将聚集函数作用到一组元组集上，在SQL中可用group by子句来实现这个愿望
group by子句中给出的一个或多个属性是用来构造分组的。在group by子句中的所有属性上取值相同的元组将被分在一个组中。
当SQL查询使用分组时，一个很重要的事情是需要保证出现在select语句中但没有聚集的属性只能是出现在gruop by子句中的那些属性。

having子句

having子句中的谓词在形成分组后才起作用，因此可以使用聚集函数，having后面的谓词针对group by子句构成的分组
包含聚集，group by或having子句的查询含义可通过下述操作序列来定义：
from-where-group by-having-select

对空值和布尔值的聚集

聚集函数根据以下原则处理空值：除了count（*）外所有的聚集函数都忽略输入集合中的空值。由于空值被忽略，有可能造成参加函数运算的输入值集合为空集。规定空集的count运算值为0，其他所有聚集运算在输入为空集的情况下返回一个空值。

嵌套子查询

SQL提供嵌套子查询机制。子查询是嵌套在另一个查询中的select-from-where表达式。子查询嵌套在where子句中，通常用于对集合的成员资格，集合的比较以及集合的基数进行检查。

集合成员资格

连接词in测试元组是否是集合中的成员，集合是由select子句产生的一组值构成的。连接词not in则测试元组是否不是集合中的成员

集合的比较

通过some和all,some:至少比某一个要大，要小，等于
all比所有的都大，都小
=some等价于in
<>all等价于not in

空关系测试

SQL还有一个特性可测试一个子查询的结果中是否存在元组。exists结构在作为参数的子查询非空是返回true
来自外层查询的一个相关名称可以用在where子句的子查询中。使用了来自外层查询相关名称的子查询被称作相关子查询
可以用not exists结构 测试子查询结果集中是否不存在元组。我们可以使用not exists结构模拟集合包含（即超集）操作：我们可以将“关系A包含关系B”写成“**not exists(B except A) **”
EXCEPT 返回两个结果集的差（即从左查询中返回右查询没有找到的所有非重复值）。

重复元组存在性测试

SQL提供了一个布尔函数，用于测试在一个子查询的结果中是否存在重复元组。如果作为参数的子查询结果中没有重复的元组，unique结构将返回true值。
当有元组的一个属性为空，unique测试就由可能为真

from子句中的子查询

允许在from子句中使用子查询表达式。在此采用的主要观点是：任何select-from-where表达式返回的结果都是关系，因而可以被插入到另一个select-from-where中任何关系可以出现的位置。

with子句

with子句提供定义临时关系的方法，这个定义只对包含with子句的查询有效。

标量子查询

SQL允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回包含单个属性的单个元组；这样的子查询称为标量子查询。

数据库的修改

删除

我们只能删除整个元组，不能只删除某些属性上的值

delete from r
where P

P代表一个谓词，r代表一个关系。delete语句首先从r中找出所有使P（t）为真的元组t，然后把它们从r中删除。如果省略where子句，则r中所有的元组将被删除。
还可以使用drop table语句删除元组和元组所在的关系

插入

要往关系中插入数据，我们可以指定待插入的元组，或者写一条查询语句来生成待插入的元组集合。显然，待插入元组的属性值必须在相应属性的域中。同样，待插入元组的分量数也必须使正确的。

insert into course
        values（'CS-437','Database Systems','Comp.Sci',4）;

元组属性值的排列顺序和关系模式中属性排列的顺序一致.如果不记得，允许指定属性

更新

希望在不改变整个元组的情况下改变其部分属性的值。为达到这一目的，可以使用update语句
例如：

update   instructor
set  salary = salary*1.05
where salary<(select avg(salary)
                         from  instructor);

SQL 提供case结构，我们可以利用它避免更新次序引发的问题
case语句的一般格式如下：

case
        when  pred1  then  result1
        when  pred2  then  result2
        ...
        when  predn  then  resultn
        else  result0
end