数据库系统-学习记录6

ch6.数据库语言SQL（续）

多表查询

SQL的积和自然连接

  可以在where子句中设定两表之间的用于连接的属性，例如：

  现有两关系：
  Movies(title, year, length, genre, studioName, producerC#)
  MovieExec(name, address, cert#, netWorth)

select name
from Movies, MovieExec
where title = 'Star Wars' and producerC# = cert#;

  表示从两表中选出title为’Star Wars’、且Movies中的producerC#和MovieExec中的cert#相等的元组，并输出Movies中的name属性

消除属性歧义

  因为多表查询时，不同的关系可能会有重名的属性，所以可以用（表名.属性）的形式来消除歧义，例如：

SELECT MovieStar.name, MovieExec.name
FROM MovieStar, MovieExec
WHERE MovieStar.address = MovieExec.address;

元组变量

  有时候，需要在同一个关系中进行多表查询（即在from子句中多次使用同一个关系），此时可以定义元组变量以进行区分，例如：

SELECT Star1.name, Star2.name
FROM MovieStar Star1, MovieStar Star2
WHERE Star1.address = Star2.address
AND Star1.name < Star2.name;

  在此例中，对同一个关系MovieStar定义了两个变量：Star1和Star2，因此便可以让同一个关系中的同一属性出现两次

查询的并集、交集、差集

  SQL语句提供了UNION、INTERSECT和EXCEPT操作来进行并集、交集和差集操作

子查询

  子查询：从一个查询中进行另一个查询

产生标量值的子查询

  标量：一个能成为元组字段值的原子

  例如：

SELECT name
FROM MovieExec
WHERE cert# =
	( SELECT producerC#
	FROM Movies
	WHERE title = ‘Star Wars’);

  会先在关系Movies中筛选出title为’Star Wars’的元组的producerC#属性A，随后在关系MovieExec中筛选出cert#等于属性A的元组的name属性

关系的条件表达式

exists R #当R非空时，值为真
s in R #s在R中
s not in R #s不在R中
s > all R #s大于R中的所有值
s <> all R #s不等于R中的所有值，相当于s not in R
s > any R #s大于R中的至少一个元素
s = any R #s等于R中的至少一个元素，相当于s in R

元组的条件表达式

  当元组t和关系R具有相同数量的属性时，可以使用t in R或t > any($R$)来进行运算，例如：

SELECT name
FROM MovieExec
WHERE cert# IN
    ( SELECT produceC#
    FROM Movies
    WHERE ( title, year_ ) IN
        ( SELECT movieTitle, movieYear
        FROM StarsIn
        WHERE starName = ‘Harrison Ford’
    )
);

  表示的运算为：
  1、从关系StarsIn中筛选出starName为’Harrison Ford’的元组的movieTitle、movieYear属性，记做关系A
  2、从关系Movies中筛选出(title, year_)属于关系A/的元组的produceC#属性，记做关系B
  3、从关系MovieExec中筛选出cert#属于关系B的元组的name属性，此即查询结果

关联子查询

  关联子查询：复杂的嵌套子查询要求一个子查询计算多次，每次赋给查询中的某项来自子査询外部的某个元组变量的值。例如：

SELECT title
FROM Movies Old
WHERE year_ < ANY
    (SELECT year_
    FROM Movies
    WHERE title = Old.title
    );

  会首先在关系Movies中，选出title/和关系Movies的元组变量Old中的title/相等的元组，如果Old中的某元组的year_小于Movies中的至少一个元组的year_，则筛选出这个元组的title

From子句中的子查询

  在from子句中，也可以通过圆括号来使用子查询，例如：

SELECT name
FROM MovieExec, (SELECT producerC#
    FROM Movies, StarsIn
    WHERE title = movieTitle AND
        year_ = movieYear AND
        starName = ‘Harrison Ford’
    )Prod
WHERE cert# = Prod.producerC#;

  此处的子查询产生了一个新的关系，被命名为Prod，注意到这个新的关系在where子句中有出现

SQL的连接表达式

  积：cross join
  $\theta$连接：A join B on（A、B为关系名）
  自然连接：natural join
  （全/左/右）外连接：natural [full/left/right] outer join

全关系操作

  将关系作为一个整体（而不是单个或几个元组）的操作

消除重复元组

  在关系前加distinct，表示删除这个关系中的重复元素，例如：

select distinct name

  表示删除关系name中的重复元素

并集、交集和差集中的重复

  默认情况下，求并、交、差会删除重复的元组
  如果不想删除重复的元组，可以在集合命令后使用all：

R union all S
R intersect all S
R except all S

分组和聚集

  对于关系代数中的$\gamma$，在SQL中可以使用聚集符号和group by，例如：

select sno, AVG(grade) as AVG_g
from sc
group by sno
having AVG_g >= 60;

  表示对关系sc按sno分组，并求出各组grade的平均值

聚集函数

sum # 求和
avg # 求均值
count # 求总数
min # 求最小值
max # 求最大值

关于分组、聚集中的空值

  空值（NULL）在聚集操作中会被忽略
  在分组时，空值会被作为一般的值对待

having子句

  如果要在分组中进行筛选，则可以在group by子句之后增加having子句，例如：

SELECT name, SUM( length )
FROM MovieExec, Movies
WHERE producerC# = cert#
GROUP BY name
HAVING MIN( year ) < 1930;

  表示按name分组后，还筛选出了分组year的最小值小于1930的组

数据库更新

  数据库更新的指令不会返回一个结果，它会改变数据库的状态

插入

  使用insert into R(…) values(…)的形式，例如：

INSERT INTO StarsIn(movieTitle, movieYear, starName)
VALUES(‘The Maltese Falcon’, 1942, ‘Sydney Greenstreet’);

  表示向StarsIn关系中插入一条数据

  插入语句中也可以嵌套子查询

删除

  使用delete from R where <condition>的形式，例如：

DELETE FROM StarsIn
WHERE movieTitle = ‘The Maltese Falcon’ AND
movieYear = 1942 AND
starName = ‘Sydney Greenstreet’;

  表示从关系StarsIn中删除满足这三个条件的元组

修改

  使用update R set <new-value assignments> where <condition>的形式，例如：

UPDATE MovieExec
SET name = ‘Pres. ‘ || name
WHERE cert# IN (SELECT presC# FROM Studio);

  表示对关系MovieExec中满足条件的元组的name进行修改（在name前加上“Pres.”）

SQL中的事务

可串行化

  为了预防同时操作带来的逻辑混乱，SQL允许规定一个特定的事务必须对于别的事务是可串行化的——同一时刻只有一个事务，不同事务之间没有重叠

原子性

  是数据库操作的某些组合需要原子地执行——它们要么都执行要么都不执行

事务

  对可串行化和原子性问题的解决方案/将把数据库操作分组为事务

只读事务

  如果一个事务对数据只读不写，则可以更自由地让该事务与别的事务并发执行

set transaction read only # 告知SQL系统下一个事务是只读事务
set transaction read write # 通知SQL下一个事务可以写数据（默认）

读脏数据

  脏数据：还没有提交的事务所写的数据
  脏读：对脏数据的读取

  SQL允许指定一个给定的事务是否可以脏读：

set transaction read write # 表明事务可以写数据
	isolation level read uncommitted; # 允许事务读取脏数据

四种SQL隔离层次