​《資料庫系統概念》4-DDL、集合運算、嵌套子查詢

一、DDL

a) SQL Data Definition

SQL的基本資料類型有char(n)、varchar(n)、int、smallint、numeric(p,d)、real,double precision、float(n)等，int smallint real float依賴機器的精度

b) char(n)不夠的用空格補齊，比較兩個char(n)時會先補齊成一樣的長度；比較char和varchar時有的資料庫會先補齊，但有的不會，是以存儲字元串時最好都用varchar；

c)表結構的定義：

類似Create table department (dept_name varchar(20), budget numeric(12,2), primary key(dept_name));

定義表結構的通用形式為：

Create table r  

(A1, D1,

… ,

<integrity-constraint1>,

<integrity-constraint2>,

…);

常用的一緻性限制類型有：主鍵、外鍵、非空

二、集合運算和null

a) 集合運算包括并集union、交集intersect、差集except。比如要查詢2009年秋季開課的課程和2010年春季開課課程分别為：

select course_id

from section

where semester=’Fall’ and year=2009

和

where semester=’Spring’ and year=2010

要得出兩個季度所有的課程可以用Union；使用intersect可以查找到兩個季度都開課的課程；而使用except可以得到第一個結果集中存在但第二個結果集不存在的内容，這三種操作如果不需要去重，可以對應使用union

all, intersect all, except al。

b)Null

null與其它值類型的算術運算結果都為null；

比較運算中，1<null的結果為unknow，這樣除了“是”與“否”兩種邏輯結果，有多了一個unknow；AND, OR, NOT邏輯運算遇到unknow時的情況依次為：

AND ：

true,unknown=unknown

false,unknown=false

unknown, unknown= unknown

OR：

true, unknown=true

false, unknown= unknown

NOT：

NOT unknown= unknown

三、嵌套子查詢（Nested Subqueries）

子查詢是嵌套在另一個查詢中的select-from-where表達式，用于對集合的成員資格進行檢查以及對集合的比較。

a)檢查集合成員資格

比如前面用交集操作實作的查詢也可以寫為：

where semester=’Fall’ and year=2009 and

course_id in (select course_id

where semester=’Spring’ and year=2010)

可見SQL實作同一查詢目的的方法可以是多樣的。

b)集合的比較

集合比較用到的寫法有>some, >=some, =some, >all等，比如要查找比生物系中至少一位教師工資高的人，可以寫為：

select distinct T.name

from instructor as T, instructor as S

where T.salary > S.salary and S.dept name = ‘Biology’

也可以使用>some的寫法：

select name

from instructor

where salary > some (select salary

    from instructor

    where dept name = ‘Biology’);

c)空關系測試

可以用exist來測試關系中是否存在元組，對應還有not exist

前面要查詢的2009秋季和2010春季都開課的課程，也可以寫為：

select course id

from section as S

where semester = ‘Fall’ and year= 2009 and

    exists (select *

        from section as T

        where semester = ‘Spring’ and year= 2010 and S.course id= T.course id);

d)測試重複元組

使用unique來檢查關系中是否存在重複元組，對應也有not unique。比如要查找2009年秋季至多開課一次的課程：

select T.course id

from course as T

where unique (select R.course id

    from section as R

    where T.course id= R.course id and R.year = 2009);

對于當時沒開課的課程，因為結果為empty，unique對empty的計算結果也是true

e)From子句中的子查詢

在from子句中也可以使用子查詢，因為任何select-from-where傳回的結果都是關系，是以可以在其上面繼續使用from子句。

查詢平均薪水超過42000的部門，如果使用having子句可以是：

select dept name, avg (salary) as avg_salary

group by dept name

having avg (salary) > 42000;

也可以采用From子查詢的方式：

select dept name, avg_salary

from (select dept name, avg (salary) as avg salary

    group by dept name)

where avg_salary > 42000;

同時還可以為from子查詢的的表和字段重命名：

from (select dept name, avg (salary)

    as dept_avg (dept name, avg_salary)

where avg salary > 42000;

f)With子句

with子句用來定義臨時關系，這個定義隻對包含with子句的查詢有效。比如查詢擁有最多預算的部門，可以使用子查詢，但子查詢往往結構複雜、可讀性差，而使用with子句就會好很多：

with max budget (value) as

    (select max(budget)

    from department)

select budget

from department, max budget

where department.budget = max budget.value;

雖然with子句隻能在緊接着的查詢中使用，但比子查詢友善的是，它可以被多次使用。

g)标量查詢

标量查詢是指傳回結果隻是一個值的子查詢，比如查詢每個部門的員勞工數：

select dept_name,

    (select count(*)

    where department.dept_name = instructor.dept name)

    as num instructors

from department;

由于使用了count，這兒的子查詢結果隻有一個值，雖然這仍然是一張表，但資料庫會自動從表中取出值使用。标量查詢可應用于select, where, having等處。而且編譯時無法確定子查詢結果确實是一個值，如果不是，在運作時會報錯。

四、資料的修改

a)Insert

插入資料時可以直接使用select的結果，但下面的寫法會造成死循環，插入無限多條：

insert into student

    select *

    from student;

而且資料庫産品一般會提供批量插入的方式，用于快速地從格式化文本讀取并插入大批量的資料。

b)Update

更新資料時可以使用case when來區分不同的情況：

update instructor

set salary = case

            when salary <= 100000 then salary * 1.05

            else salary * 1.03

        end

此外，set子句也可以使用子查詢

學習資料：Database System Concepts, by Abraham Silberschatz, Henry F.Korth, S.Sudarshan