SQL（下）

全关系操作

以下介绍把关系作为一个整体而不是单个或一定数量的元组进行操作的操作符

---

消除重复

因为实现的时候进行了排序，代价挺大
select distinct name

---

并，交，差中的重复

！！注意：select语句默认不去重，但！并，交，差操作默认是去重的
阻止去重
union/intersect/except all

---

分组和聚集

SUM/AVG/MIN/MAX/COUNT（列名）
count(*) //计算由from和where创建的关系中的元组个数
count(distinct x)  //计算列x中不重复的值的个数

分组后聚集操作符应用在每个分组上

select name,sum(length)
from movieExec,movies
where producerC#=cert#
group by name

！！注意：空值在任何聚集操作中都被忽视
例如：count(*)是某个关系的元组个数，count(A)是属性A非空的元组的个数
除了计数之外，对空包执行的聚集操作结果均为NULL，空包的计数结果为0
但！在分组的时候，NULL是被作为一般值对待的。分组中的一个或多个属性可以被赋予NULL值

例
关系R(A,B)只有一个元组，且两个字段的值均为NULL
select A,count(B) from R group by A   结果为(NULL,0)
select A,sum(B) from R group by A   结果为(NULL,NULL)

SELECT COUNT(B) FROM R       
求B属性非空元组个数之和。结果：0
SELECT COUNT(*) FROM R        
求关系中所有的元组数目之和。结果：1

---

HAVING

select name,sum(length)
from movieExec,movies
where producerC#=cert#
group by name
having min(year)<1930

from+where形成关系R，group by对R进行分组，having对分组进行筛选，在把select的投影应用到筛选后的每个分组上
不需要分组就可以判定的条件写在where中，否则写在having中
！！注意：只有出现在group by中的属性才能以不聚集的方式出现在having和select子句中

---

插入

insert into R(A1,……,An) values(v1,……,vn)
所有属性值都给出且按顺序的话，就不用写R后面那个A列表
还可使用子查询往关系中插入计算出的元组集合

insert into studio(name)
         select  distinct  studioName
         from movies
         where studioName not in
                   (select  name
                     from studio)

删除

delete from R where C

修改

update R set 赋值 where C

update movieExec
set name='pres.'||name
where cert# in (select presC# from studio)

---

SQL中的事务

保证事务正确执行的ACID性质
A：原子性
事务中的操作或者都执行或者都不执行。
C：一致性
事务执行前后数据内在的逻辑始终是成立的。
I ：独立性
两个事务同时执行不相互干扰。
D：持久性
事务完成后即使系统故障，事务的结果长期保存。

---

原子性
在一个操作执行过程中出现硬件或软件崩溃的话，这个操作可能使数据库置于不可接收状态（银行转账的例子）
数据库操作的某些组合需要原子地执行（要么都执行，要么都不执行）
可以将对数据库的所有修改都在一个本地工作区中执行，只有在所有工作都完成后才将修改提交到数据库，于是所有改变成为数据库的一部分，并且对其它操作可见

---

事务是必须原子地执行的一个或多个数据库操作的集合
SQL要求默认事务以可串行化方式执行（一个时刻只有一个事务，相互之间没有重叠）
每条语句自身就是一个事务
可将几条语句组成一个事务
以start transaction来标记事务的开始
标记结束有两种方式
1.commit使事务成功结束，对数据库的修改被提交，持久的建立在数据库中。在commit之前，改变是试探性的，对其他事务可不可见均有可能
2.rollback使事务不成功结束，任何由该事务引起的修改都被撤销

---

只读事务
当一个事务只读数据而不写数据的时候，就可以更自由地让该事务与别的事务并发执行

告知SQL系统下一个事务是只读事务的语句
set transaction read only

告知SQL系统下一个事务是可写事务的语句（默认）
set transaction read write

---

读脏数据
脏数据dirty data：还没有提交的事务所写的数据
有的时候可以允许脏读来避免DBMS用来防止脏读所做的耗时工作以及为了等到不可能出现脏读而造成的并发性损失

指定一个给定的事务是否可以脏读
set transaction read write isolation level read uncommitted

隔离层次read uncommitted读未提交

---

SQL 标准用三个现象定义四个级别的事务隔离
(1) 脏读(dirty reads)：一个事务读取了另一个未提交的并行事务写的数据。
(2) 不可重复读(non-repeatable reads)：允许其他事务修改当前事务读取的数据
(3) 幻读(phantom read)：其他事务可以插入与当前事务所发出语句的搜索条件匹配的新行

---

其它隔离层次
事务的隔离层次只影响该事务可以看到那些数据，不影响其它事务所看到的数据
四种隔离层次：
级别依次变高从最不串行到最串行
1.读未提交
isolation level read uncommitted
2.读提交
写事务阻止其它读写事务，但读事务不会阻止其它任何事务，所以没法重复读
isolation level read committed
3.可重复读
阻止了别人修改和删除，但没阻止别人添加，所以会有幻像
isolation level repeatable read
4.可串行化
默认情况
事务必须完全在另一个事务之前或之后执行
级别最高，大量加锁，请求超时
isolation level serializable
第一次检索得到的元组，在第二次检索时一定同样出现，并且第二次检索时还会出现新插入的幻像元组（phantom）
保证已存在的数据不会变了，但还存在新加进来的数据

---

总结
隔离层次               脏读              同一元组             幻像
read uncommitted      可读              不保持               可读
read committed        不可读            不保持               可读
repeatable read       不可读            保持                 可读
serializable          不可读            保持                 不可读