資料庫原理（三）

SQL

Sql查詢語言概覽

Sql最早的版本是由IBM開發，它最初的被叫做Sequel，在20世紀70年代早期作為System R項目的一部分。Sequel語言一直發展至今，其名稱已經變為SQL(結構化查詢語言)。Sql已經很明顯的确立了自己作為标準的關系資料庫語言的地位。

Sql語言包括一下幾個部分：

l 資料定義語言（Data-Definition Language,DDL）:SQL DDL提供定義關系模式、删除關系以及修改關系模式的指令。

l 資料操縱語言（Data-Manipulation Language,DML）SQL DML提供從資料庫查詢資訊，以及在資料庫中插入元組、删除元組、修改元組的能力。

l 完整性（integrity）:SQL DDL包括定義完整性限制的指令，儲存在資料庫中的資料必須滿足所定義的完整性月火速。破壞完整性限制的更新是不允許的。

l 視圖定義（view definition） ：SQL DDL包括定義視圖的指令。

l 事務控制（transaction control）：SQL 包括定義事務的開始和結束的指令

l 嵌入式SQL和動态SQL（embedded SQL and dynamic SQL）:嵌入式和動态SQL定義SQL語言如何嵌入到通用程式設計語言中。

l 授權（authorization）：SQL DDL 包括定義對關系和視圖的通路權限的指令。

Sql資料定義

DDL不僅能夠定義關系，還可以定義每個 每個關系的資訊：

l 每個關系的模式

l 每個屬性的取值類型

l 完整性限制

l 每個關系維護的索引集合

l 每個關系的安全性和權限資訊

l 每個關系在磁盤上的實體存儲結構

基本類型：

Char（n）：固定長度的字元串，使用者指定長度n，全稱character

Varchar（n）：可變長度字元串，使用者指定最大長度n，等價于全稱character varying。

Int：整型，全稱integer

Smallint：小整數類型。

Numeric（p,d）：定點數，精度由使用者指定，這個數有p位數字，其中有d位在小數點右邊

Real，double precision：浮點數與雙精度浮點數，精度與機器相關。

Float（n）：精度至少為n位的浮點數。

基本模式定義：

用create table 定義sql關系：

Create table department

(

dept_name varchar(20),

building varchar(15),

budget numeric(12,2),

primary key(dept_name)

);

Primary key（A），聲明屬性A構成關系的主碼。主碼屬性必須非空且唯一，也就是說沒有一個元組在主碼屬性上取空值，關系中也沒有兩個元組在所有主碼屬性上取值相同。雖然主碼的聲明是可選的，但為每個關系指定一個主碼通常會更好。

Foreign key(A1,A2,A3…..) references:

Foreign key 聲明表示關系中任意元組在屬性（A1,A2…）上的取值必須對應與關系s中某元組在主碼屬性上的值。

Not null：一個屬性上的not null 限制表明在該屬性上不允許空值。

一個新建立的關系最初是空的，可以用insert 指令将資料加載到關系中。

Insert into instructor  values(10211,’Smith’,’Biology’,6600);

值的順序應該遵循對應屬性在關系模式中列出的順序。

用delete指令删除元組：

Delete from student；

将删除student的所有元組。

如果要從sql資料庫中去掉一個關系，我們使用drop table 指令

Drop talbe r；

不僅删除r的所有元組，還删除了關系模式；

我們用alter table 指令為已有關系增加屬性。關系中的所有元組在新屬性上的取值将被設為null。

Alter table r add A D；

其中r是現有關系的名字，A是待添加屬性的名字，D是待添加屬性的域。

我們也可以通過指令去除某些屬性：

Alter table r drop A;

其中r是現有關系的名字，A是關系中一個屬性的名字。很多資料庫系統比你更不支援去掉屬性，金瓜它們允許去掉整個表。

Sql查詢的基本結構

單關系查詢

Select name from instructor ;

其結果是由屬性名為name的單個屬性構成的關系。

“找出所有教師所在的系名”:

Select dept_name from instructor;

在關系模型的形式化數學定義中，關系是一個集合。是以，重複的元組不會出現在關系中。在實踐中，去除重複是相當費時的，是以sql允許在關系以及sql表達式結果中出現重複。是以在上述的sql查詢中，每個系名在instructor關系的元組沒出現一次，都會在查詢結果中列出一次。

去除重複的方法。可以在select 後加入關鍵字distinct。

Select distinct dept_name from instructor;

上述查詢的結果中，每個系名最多隻出現一次；

Sql允許我們使用關鍵字all來顯式指明不去除重複。

Select all dept_name from instructor;

保留重複元組是預設的，是以可以省去all。

Select 子句還可以帶有+，-，*，/運算符的表達式。運算對象通常可以是常數或元組的屬性。

例如：

查詢：

Select ID,name,dept_name,salary*1.1

From instructor;

Where 子句允許我們隻選出那些在from子句的結果關系中滿足特定謂詞的元組。

“找出所有在computer science系并且工資超過70000美元的教師的姓名”，該查詢用sql可以寫為：

Select name from instructor where dept_name=’Comp.Sci’ and salary >70000;

Sql允許在where子句中使用邏輯連接配接詞and，or和not。

邏輯連接配接詞的運算對象可以是包含比較運算符<,>/<=,>=等表達式。

多關系查詢

通常查詢需要從多個關系中擷取資訊。

“找出所有教師的行in個，以及他們所在系的名稱和所在建築的名稱”。

老驢instructor關系的模式，我們可以從dept_name 屬性中得到系名，但是系所在建築的名稱是在department關系的building屬性中給出的。為了回答查詢，instructor關系中的每個元組必須與department關系中的元組比對，後者在dept_name上的取值相配與instructor元組在dept_name上的取值。

Select name,instructor,dept_name,building from instructor,department where instructor.dept_name=department.dept_name;

Select,from和where 子句的作用如下：

l Select子句用于列出查詢結果中所需的屬性。

l From 子句是一個查詢求值中需要通路的關系清單

l Where 子句是一個作用在from子句中關系的屬性上的謂詞。

自然連接配接

Natural join 運算作用于兩個關系，并産生一個關系作為結果。不同于連個關系上的笛卡爾積，它将第一個關系的每個元組與第二個關系的所有元組進行連接配接。

“對于大學中所有講授課程的教師，找出他們的姓名以及所講述的所有課程辨別”

Select name,course_id

From instructor,teaches

Where instructor.ID=teacher.ID;

該查詢可以用sql的自然連接配接運算更簡潔的寫作：

Select name,cours_id

From instructor natural join teaches;

以上兩個查詢産生的相同的結果。

在一個sql查詢的from子句中，可以用自然連接配接多個關系結合在一起，如下所示：

Select A1,A2,A3…..n

From r1 natural join r2 natural join 。。。。。 natural join rn

Where

P

“列出教師的名字以及他們所講授課程的名稱”。

次查詢可以用sql寫為：

Select name，title 

From instructor natural join teaches,course

Where teaches.course_id=course.course_id;

下面的sql查詢不會計算出相同的結果：

Select name,title

From instructor natural join teaches natural join course;

為了發揚自然連接配接的優點，同時避免不必要相等屬性帶來的危險，sql提供了一種自然連接配接的構造形式，允許使用者來指定需要哪些列相等。

Select name,title

From (instructor natural join teaches) join course using (course_id)

Join …..using 運算中需要給定一個屬性名清單，其兩個輸入中都必須具有指定名稱的屬性。考慮運算r1 join r2 using（A1，A2），它與r1和r2的自然連接配接類似。

附加的基本運算

更名運算

Select name,course_id

From instructor,teaches

Where instructor.ID=teaches.ID;

更名：

Old-name as new-name;

用名字instructor_name來代替屬性名name:

Select name as instructor_name,course_id

From instructor,teaches

Where instructor.ID=teaches.ID;

“找出滿足下面條件的所有教師的姓名，他們的工資至少比Biology系某一個教師的工資要高”

Select distinct T.name

From instructor as T,instructor as S where T.salary >S.salary and S.dept_name=’Biology’;

T和S可以被認為是instructor關系的兩個拷貝，但更準确的說是instructor關系的别名，想T和S那樣被用來重命名關系的辨別符在sql标準中被稱作相關名稱（correlation name），但通常也被稱作表别名（table alias），或者相關變量（correlation variable）或者元組變量（tuple variable）。

字元串運算

Sql使用單引号來标示字元串，字元串的相等運算是大小寫敏感的。

Sql在字元串上有多種函數。

在字元串上可以使用like操作符來實作模式比對.

l 百分号（%）：比對任意一個子串。

l 下劃線（_）：比對任意一個字元。

模式是大小寫敏感的，也就是大寫字元與小寫字元不比對，反之亦然。

詳細例子：

u ‘Intro%’比對任何以“Intro”打頭的字元串。

u ‘%Comp%’比對任何包含“Comp”子串的字元串，例如‘Intro to Computer Science’ 和‘Computational Biology’。

u ‘_ _ _’比對隻含有三個字元的字元串。

u ‘_ _ _%’比對至少含有三個字元的字元串。

Sql中用比較運算符like來表達模式。

“找出所在建築名稱中包含子串‘Watson’的所有系名”

Select dept_name from department where building like ‘%Watson%’

Like比較運算中用escape關鍵詞來定義轉義字元。

為了說明這一用法，考慮一下模式，它使用反斜線（\）作為轉義字元

like ‘ab\%cd%’ escape ‘\’比對所有以“ab%cd”開頭的字元串。

like ‘ab\\cd%’ escape ‘\’ 比對所有以“ab\cd”開頭的字元串。

星号 “*”可以用在select子句中表示“所有的屬性”，因而如下查詢的select子句中使用instructor.*:

Select instructor .*

From  instructor,teaches

Where instructor.ID=teaches.ID ;

表示instructor中的所有屬性都被選中。

排列元組的顯示次序

Sql為使用者提供了一些對關系中元組顯示次序的控制。Order by子句就可以讓查詢結果按排列順序顯示。為了按字母順序列出在Physics系的所有教師。

Select name

From  instructor

Where dept_name =’Physics’

Order by  name;

Order by 子句預設使用升序。要說明排序順序，我們可以使用desc表示降序，或者使用asc表示升序。

排序可以在多個屬性上進行。假設我們希望按salary的降序列出整個instructor關系。如果有幾位教師的工資相同，就将它們按姓名升序排列。

Select * 

From

 Instructor

Order by salary desc, name asc;

首先是工資，然後是姓名。

Where 子句謂詞

Sql提供between比較運算符來說明一個值是小于或等于某個值。

如果我們想找出工資在90000和10000美元之間的教師的姓名，我們可以使用between比較運算符。

Select name

From instructor

Where salary between 90000 and 100000;

它可以取代

Select name

From instructor

Where salary <=1000000 and salary >=90000;

Likewise ，還可以使用not between比較運算符。

Select  name,course_id

From instructor,teaches

Where instructor.ID=teaches.ID and dept_name=’Biology’;

Sql允許我們用記号（v1，v2…vn）來表示一個分量值分别為v1，v2….vn的n維元組。在元組上可以運用比較運算符。按字典順序可以進行比較運算。（a1，a2）<=(b1,b2)在a1<=b1且a2<=b2時為真。類似的，當兩個元組在所有屬性上相等時，它們是相等的。

Select name,course_id

From instructor,teaches

Where (instructor.ID,dept_name)=(teaches.ID,’Biology’);

集合運算

Sql作用在關系上union，intersect和except運算對應于數學集合論中并集，交集和補集運算。

在2009年秋季學期開設的所有課程的集合

Select course_id

From section

Where semester=’Fall’ and year=2009;

在2010年春季開設的所有課程的集合：

Select course_id

From section

Where semester=’Spring’ and year=2010;

并運算

為了找出在2009年秋季開課，或者在2010年春季開課或兩個學期都開課的所有課程，我們可以寫查詢語句：

（select course_id

From section 

Where semester=’Fall’ and year=2009）

Union

(select course_id

From section

Where semester=’Spring’ and year=2010);

與select子句不同，union運算自動會去除重複。如果我們想保留所有重複，就必須用union all 代替union：

（select course_id

From section 

Where semester=’Fall’ and year=2009）

Union all

(select course_id

From section

Where semester=’Spring’ and year=2010);

交運算

為了找出2009年秋季和2010年春季同時開課的所有課程的集合，我們寫出

（select course_id

From section 

Where semester=’Fall’ and year=2009）

Intersect

(select course_id

From section

Where semester=’Spring’ and year=2010);

Intersect自動去除重複。

如果想保留重複，就必須使用intersect all代替intersect

差運算

為了找出在2009年秋季開課但是在2010年春季不開的所有課程，我們寫：

（select course_id

From section 

Where semester=’Fall’ and year=2009）

Except

(select course_id

From section

Where semester=’Spring’ and year=2010);

前一個關機減去後一個關系的差。

差為正的。自動去除所有重複的。

如果要保留重複則是用except all 代替except：

（select course_id

From section 

Where semester=’Fall’ and year=2009）

Except all

(select course_id

From section

Where semester=’Spring’ and year=2010);

空值

u And: true and unknown 結果是unknown， false and unknown 是false,unknown and unknown 的結果是unknown。

u Or true or unknown 結果是true ，false or unknown 的結果是unknown，unknown or unknown的結果似乎unknown。

u Not： not unknown的結果是unknown。

Select name 

From instructor

Where salary is null;//salary為空值的教師

聚集函數

聚集函數是以值的一個集合（集或者多重集）為輸入、傳回單個值的函數。Sql提供了五個固有聚集函數：

u 平均值：avg

u 最小值：min

u 最大值：max

u 總和：sum

u 計數：count

Sum和avg的輸入必須是數字集，但其他運算符還可作用在非數字資料類型的集合上，如字元集。

基本聚集

“找出Computer Science系教師的平均工資”。

Select avg（salary）

From instructor

Where dept_name=’Comp.Sci’;

該查詢的結果是一個具有單屬性的關系，其中隻包含一個元組，這個元組的數值賭赢Computer Science系教師的平均工資。資料庫系統可以給結果關系的屬性一個任意的名字，該屬性是由聚集産生的，我們可以給屬性賦一個有意義的名稱，如下所示：

Select avg（salary） as avg_salary

From instructor

Where dept_name=’Comp.Sci’;

有些情況下在計算聚集函數前需要先删除重複元組。如果我們确實需要删除重複元組，可以在聚集表達式中使用關鍵詞distinct。比方有這樣一個查詢示例“找出在2010年春季學期講授一門課程的教師數”，在該例中不論一個教師講授了幾個課程段，他隻應該被計算一次。

所需資訊包含在teaches關系中，我們書寫查詢如下：

Select count(distinct ID)

From teaches

Where semester=’Spring’ and year=2010;

我們經常使用聚集函數count計算一個關系中元組的個數。Sql中該函數的寫法是count(*)

是以，要找出course關系中的元組數，可寫成：

Select count(*)

From course;

Sql不允許用count(*)時使用distinct，在用max和min時使用distinct是合法的，盡管結果無差别。我們可以使用關鍵字all替代distinct來說明保留重複元組。但是，既然all是預設的，就沒有必要這麼做了。

分組聚集

有時候我們不僅希望将聚集函數作用在單個元組集上，而且希望将其作用在一組元組集上；在sql可用group by子句實作這個願望。Group by子句中給出的一個或多個屬性是用來構造分組的。

在group by子句中的所有屬性上取值相同的元組将被分在一個組中。

作為示例，“找出每個系的平均工資”，該查詢的書寫如下：

Select dept_name,avg(salary) as avg_salary

From instructor

Group by dept_name;

“找出所有教師的平均工資”，我們查詢寫在如下形式：

Select avg(salary)

From instructor;

在這裡省略了group by子句，是以整個關系被當做是一個分組。

“找出每個系在2010年春季講授一門課程的教師人數”，有關每位教師在每個學期講授每個課程段的資訊在teaches關系中。但是，這些資訊需要與來自instructor關系的資訊進行連接配接，才能得到每位教師所在的系名。這樣我們把查詢寫做如下形式：

Select dept_name ,count(distinct ID) as instr_count

From instructor natural join teaches

Where semester=’Spring’ and year=2010

Group by dept_name;

Having 子句 

有時候，對分組限定條件比對元組限定條件更有用。例如，我們隻對教師平均工資超過42000美元的系感興趣。該條件并不針對單個分組，而是針對group by 子句構成的分組。為表達這樣的查詢，我們使用sql的having 子句。Having 子句中的謂詞在形成分組後才起作用，是以可以使用聚集函數。

 Select dept_name,avg(salary) as avg_salary

From instructor

Group by dept_name

Having avg(salary) >42000;

與select子句的情況類似，任何出現在having 子句中，但沒有被聚集的屬性必須出現在group by 子句中，否則查詢就被當成是錯誤的。

 包含聚集、group by或having 子句的查詢的定義可以通過下述操作序列來定義：

1. 與不帶聚集的查詢情況類似，最先根據from子句來計算出一個關系。

2. 如果出現了where 子句，where 子句中的謂詞将應用到from子句的結果關系上。

3. 如果出現了group by子句，滿足where 謂詞的元組通過group by子句形成分組。數學如果沒有group by子句，滿足where 謂詞的整個元組集被當作一個分組。

4. 如果出現了having子句，它将應用到每個分組上，不滿足having子句謂詞的分組将被抛棄。

5. Select子句利用剩下的分組産生出查詢結果中的元組，即在每個元組上應用聚集函數來得到單個結果元組。

說明同時使用having 和where子句的情況，“對于在2009年講授的每個課程段，如果該課程段有至少2名學生選課，找出選修該課程段的所有學生的總學分（tot_cred）的平均值”

Select course_id,semester,year,sec_id,avg(tot_cred)

From takes natural join student

Where year=2009

Group by course_id,semester,year,sec_id

Having count(ID)>=2;

對空值和布爾值的聚集

空值的存在給聚集運算的處理帶來麻煩。

嵌套子查詢

Sql提供嵌套子查詢機制。子查詢是嵌套在另一個查詢中的select-from-where表達式。子查詢嵌套在where子句中。通常對于集合的成員資格、集合的比較以及集合的基數進行檢查。

集合成員資格

Sql允許測試元組在關系中的成員資格。連接配接詞in測試元組是否是集合中的成員，集合是有select子句産生的一組值構成的。連接配接詞not in 則測試元組是否不是集合中的成員。

“找出2009年秋季和2010年春季學期同時開課的所有課程”

先前，我們通過對兩個集合進行交運算來書寫該查詢，我們下面用另一種寫法：

//(select course_id from section where semester=’Spring’ and year=2010

Select distinct couse_id from section where semester=’Fall’ and year 2009 and course_id in 

(select couse_id from section where semester =’Spring’ and year=2010);

也可以使用類似的方式使用not in。

In和not in操作符也能用于枚舉集合。

Select distinct name from instructor

Where name not in (‘Mozart’,’Einstein’);

例題：

Select count(distinct ID)

From takes

Where (course_id,sec_id,semester,year) in 

(select course_id,sec_id,semester,year from teaches where teaches.ID=10101);

集合的比較

Select distinct T.name  from instructor as T,instructor as S where T.salary>S.salary and S.dept_name=’Biology’;

空關系測試

Sql還有一個特性就是可以測試一個子查詢的結果中是否存在元組。Exists結構在作為參數的子查詢非空時傳回true值。使用exists結構，我們還能用另外一種方法書寫查詢“找出在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);

同樣可以使用not exists 屬性。

Select S.ID,S.name

From student as S

Where not exists

((select course_id from course where dept_name=’Biology’)

Except (select T.course_id from takes as T where S.ID =T.ID));

子查詢

(select course_id from course where dept_name =’Biology’)

重複元組存在性測試

Sql 提供一個布爾函數，用于測試一個子查詢的結果中是否存在重複元組。如果作為子查詢結果中沒有重複的元組，unique結構将傳回true值。我們可以使用unique結構書寫查詢“找出所有在2009年最多開設一次的課程”

Select T.course_id

From course as T

Where unique(select R.course_id from sectioni as R where T.course_id=R.course_id and R.year=2009);

From 子句的子查詢。