SQL(Structured Query Language,結(jié)構(gòu)化查詢語言)

2.1 SQL查詢語言概覽

SQL語言包含

• 數(shù)據(jù)定義語言(Data-Definition Language,DDL)。SQL DDL提供定義關(guān)系模式、刪除關(guān)系以及修改關(guān)系模式的命令。
• 數(shù)據(jù)操縱語言(Data-Manipulation Language,DML)。SQL DML提供從數(shù)據(jù)庫中查詢信息以及在數(shù)據(jù)庫中插入元組、刪除元組、修改元組的能力
• 完整性(interity)：SQL DDL包含定義完整性約束的命令，保存在數(shù)據(jù)庫中的數(shù)據(jù)必須滿足所定義的完整性約束。破壞完整性約束的更新是不允許的。
• 視圖定義(view definition):SQL DDL包含定義視圖的命令
• 事務(wù)控制(transaction control)。SQL包含定義事務(wù)的開始點和結(jié)束點的命令。
• 嵌入式SQL(embedded SQL)和動態(tài)SQL(dynamic SQL)：嵌入式和動態(tài)SQL定義SQL語句如何嵌入諸如C、C++和Java這樣的通用編程語言中。
• 授權(quán)(authorization):SQL DDL包括定義對關(guān)系和視圖的訪問權(quán)限的命令。

2.2 SQL數(shù)據(jù)定義

2.2.1 基本類型

• char(n)：具有用戶指定長度n的固定長度的字符串，也可以使用全稱形式character
• varchar(n)：最大長度為n的可變長度的字符串
• int:整數(shù)
• smallint：小整數(shù)(依賴于機器的整數(shù)類型的子集)
• numeric(p,d)：具有用戶指定精度的定點數(shù)。這個數(shù)字有p位數(shù)(算上一個符號位)，并且小數(shù)點右邊有p位中的d位數(shù)字
• real，double precision：浮點數(shù)與雙精度浮點數(shù)，精度依賴于機器
• float(n)：精度至少為n位數(shù)字的浮點數(shù)
• null value：

2.2.2 基本模式定義

使用create table來定義SQL關(guān)系：$createtabler(A_1{D}_1,A_2{D}_2,?,A_n{D}_n,<完整性約束1>,?)$

r是關(guān)系的名字，A表示關(guān)系中的一個屬性名，D表示這個關(guān)系域的值是什么類型

E.g.:

create table branch
(branch_name char(15) not null,
 branch_city char(30),
 assets		 int)

完整性約束:

• primary key($A_{j1},A_{j2},?,A_{jm}$):主碼聲明表示這些屬性構(gòu)成關(guān)系的主碼。主碼屬性必須是非空且唯一的;也就是說沒有元組會在主碼屬性上取空值，并且關(guān)系中也沒有兩個元組會在所有主碼屬性上取值都相同，一般都加上主碼聲明
• foreign key($A_{k1},A_{k2},?,A_{kn}$)references s:外碼聲明表示關(guān)系中任意元組在這些屬性上的取值必須對應(yīng)于關(guān)系$s$中某元組在主碼屬性上的取值
• not null:一個屬性上的非空約束表明該屬性上不允許存在空值。
• chece§:P是一個謂詞，即判斷條件

E.g:

create table instructor
(ID			varchar(5),
 name		varchar(20)not null,
 dept_name	 varchar(20),
 salary		numeric(8,2),
 primary key(ID),
 check(salary>=0))

基本操作：

1.新建的表是空的，使用insert into r values 來插入元素：

insert into instructor values('10211','Smith','Computer Science',66000)

如果違背了完整性約束，將會插入失敗

2.delete:刪除表中的所有元組(關(guān)系不會被刪除)

delete from instructor

3.alter table：為已有關(guān)系增加屬性

alter table r add A D;

r是現(xiàn)有關(guān)系的名稱，A是待添加屬性的名稱，D是待添加屬性的類型，我們也可以通過命令

alter table r drop A;

從關(guān)系中去掉屬性。很多數(shù)據(jù)庫并不支持去掉屬性，盡管它們允許去掉整張表。

2.3 SQL查詢的基本結(jié)構(gòu)

2.3.1 單關(guān)系查詢

找出所有教師的姓名：

	select name
	from   instructor

其結(jié)果是由屬性名為$name$的單個屬性構(gòu)成的關(guān)系

可以在$select$后插入關(guān)鍵字$distinct$去除重復(fù)，也可使用$all$顯式知名不去除重復(fù)(默認是不去重的)

	select distinct dept_name
	from instructor

$select$語句還可以帶運算符的算術(shù)表達式，運算對象可以是常數(shù)或元組的屬性

	select ID,name,dept_name,salary*1.1
	from instructor

$where$子句允許我們只選出那些在$from$子句的結(jié)果關(guān)系中滿足特定謂詞的元組，例：找出計算機系中工資超過70 000美元的老師姓名

	select name
	from instuctor
	where dept_name='Comp.Sci.' and salary > 70000

SQL允許在$where$子句中使用邏輯連詞and、or和not，邏輯連詞的對象可以是包含比較運算符<,<=,>,>=,=和<>的表達式，也允許使用比較運算符來比較字符串、 算術(shù)表達式以及特殊類型，比如日期

2.3.2 多關(guān)系查詢

? 作為示例，如果我們想查詢“所有老師的姓名，以及他們所在系得名稱和系所在額建筑的名稱”，系所在建筑的名稱在的department關(guān)系的building屬性給出。則需要instructor的元組和department的元組匹配，使得department元組在department原則在dept_name上的取值相配于instructor元組在dept_name上的取值。

? SQL為：

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

? 注意到，dept_name屬性既出現(xiàn)在instructor中也出現(xiàn)在department關(guān)系中，用關(guān)系名作前綴來注明所指的是哪個屬性。同時要求二者的dept_name相同保證選取的元組是合理的。

基本查詢語句形式：
$$
\begin{align}
select\ &A_1,A_2,\cdots,A_n\
from\ &r_1,r_2,\cdots,r_m\
where\ &P

\end{align}
$$

• select 用于列出查詢結(jié)果中所需的元素

• from 是在查詢求值中需要訪問的關(guān)系列表

• where子句是作用在from子句中的關(guān)系的屬性上的謂詞

from子句其實就是將這些關(guān)系的元組一一連接起來，不做任何處理

natural join

即自然連接

    select name, course_id
    from instructor natural join teaches;
	//1
	select name, title
    from instructor natural join teaches, course
    where teaches.course_id = course.course_id;
	//2
	select name,title
	from instructor natural join teaches natural join course;
	//3
	select name,title
	from (instructor natural join teaches join course using(course_id))
	//4

? 查詢1是容易的，就是將兩個表自然連接后(ID相同的才會拼在一起)，選出名字和課程代碼。

? 查詢2，3，4是相似的，但我們可以發(fā)現(xiàn)查詢3是錯的，因為instructor和teaches自然連接后，和course有了兩個相同的屬性：dept_name、course_id。在后續(xù)和course的自然連接中，只有者兩個屬性都相同才會拼接在一起，但實際上，計算機系的老師也可能在生物系開課，所以這個查詢時不合實際情況的，故為錯誤的。

? 查詢2是容易理解的，就是將自然連接后的新表再和course做笛卡爾積，要求course_id相等才會加入最終表

? 注意到查詢4出現(xiàn)了一個新的詞組join…using(x)，意思為用x屬性做自然連接，如查詢4中，便是只要求course_id相同即可。

2.4 附加的基本運算

SQL還支持幾種附加的基本運算

2.4.1 更名運算

SQL提供了一種重命名結(jié)果關(guān)系中的屬性的方式，使用形如$as$的子句：
$$old?nameasnew?name$$
as子句既可以出現(xiàn)在select子句中，也可出現(xiàn)在form子句中。

as子句可以把一個長的關(guān)系名替換成短的，這樣再查詢中的其他地方使用起來就更為方便。

另一個作用是為了適用于需要比較同一個關(guān)系中的元組的情況。為此我們需要把一個關(guān)系跟它自身進行笛卡爾積，如果不充命名，就不可能把一個元組與其他元組區(qū)分開來。例如：找到所有工資至少比Biology系某一位老師的工資要高的老師。

	select distinct T.name
	from instructor as T,instructor as S
	where T.salary>S.salary and S.dept_name='Biology'

通過as語句，可以實現(xiàn)同一個表之間的比較。

像例中的T和S那樣被用來重命名關(guān)系的標識再SQL標準中被稱作相關(guān)名稱，但通常也被稱作表別名或相關(guān)變量，或元組變量

2.4.2 字符串運算

? SQL使用一對單引號來標識字符串，例如’Computer’。如果單引號是字符串的組成部分，就使用兩個單引號表示，例如’It’‘s right’，就是字符串"It’s right"。

? SQL允許在字符串上作用多種函數(shù)，例如連接字符串(使用"||")、提取子串、計算字符串長度、大小寫轉(zhuǎn)換(用upper(s)函數(shù)轉(zhuǎn)換為大寫，用lower(s)函數(shù)轉(zhuǎn)換為小寫)、去掉字符串后面的空格(使用trim(s))等。

? 在字符串上可以使用like來實現(xiàn)模式匹配：

• 百分號(%)：%字符匹配任意字串
• 下劃線(_)：_字符匹配任意一個字符

? 模式是大小寫敏感的，也就是說，大寫字符與小寫字符不匹配，反之亦然，例：

• 'Intro%'匹配以“Intro”打頭的任意字符串
• '%Comp%'匹配包含"Comp"子串的任意字符串
• '_ _ _%'匹配至少有3個字符的任意字符串

考慮查詢"找出所在建筑名稱中包含子串’Watson’的所有系名"

	select dept_name
	from department
	where building like '%Watson%'

此外，SQL還允許定義轉(zhuǎn)義字符,用escape關(guān)鍵字來定義轉(zhuǎn)義字符

• like ‘a(chǎn)b\%cd%’ escape ‘\’ 匹配以"ab%cd"開頭的所有字符串
• like ‘a(chǎn)b\cd%’ escape ‘\’ 匹配以"ab\cd"開頭的所有字符串

同時可使用not like來搜索不匹配項。

2.4.3 select子句中的屬性說明

? 星號"*"可以在select子句中表示“所有的屬性”。

2.4.4 排列元組的顯示次序

? order by子句可以讓查詢結(jié)果中的元組按照排列順序顯示，用desc表示降序，或用asc表示升序。

 select *
 from instructor
 order by salary desc,name asc;

按salary降序排列，如果salary相同，就按name升序排列。

2.4.5 where子句謂詞

SQL 提供between比較運算符來說明一個值小于或等于某個值，同時大于或等于另一個值

例如：找到貸款額在100000和90000之間的貸款號碼

	select loan_number
	from loan
	where amount between 90000 and 100000

類似地，可以使用not between比較運算符

SQL允許我們用符號$(v_1,v_2,?,v_n)$來包含一個n維元組，該符號被稱為行構(gòu)造器。在元組上可以使用比較運算符，并將字典順序進行比較運算。例如$a_1\le b_1，$且$a_2\le b_2$時，$(a_1,a_2)\le (b_1,b_2)$為真。

	select name,course_id
	from instructor,teaches
	where (instructor.ID,dept_name)=(teaches.ID,'Biology');

2.5 集合運算

? SQL作用在關(guān)系上地union、intersect和except運算對應(yīng)于數(shù)學(xué)集合論中地和$?$運算。集合運算默認是沒有重復(fù)元素，可在關(guān)鍵字后加all來保留重復(fù)元素。

? 如果一個元組在r中出現(xiàn)m次，s中出現(xiàn)n次，那么

• m+n 次 在 r union all s
• min(m,n) 次 在 r intersect all s
• max(0,m-n)次 在r except all s

例子：

• Find all customers who have a loan, an account, or both

  (select customer_name from depositor)
  union all
  (select customer_name from borrower) 
  

• Find all customers who have both a loan and an account.

  (select customer_name from depositor)
  intersect all 
  (select customer_name from borrower) 
  

• Find all customers who have an account but no loan.

  (select customer_name from depositor) 
  except all
  (select customer_name from borrower)
  

2.6 空值

? 任何含null的比較遠算的結(jié)果都是unknown(既不是謂詞is null也不是is not null)

邏輯運算：

• and: true and unknown 的結(jié)果是unknown，false and unknown 的結(jié)果是false，unknown and unknown 的結(jié)果unknown
• or：true or unknown->true ,false or unknown->unknown, unknown or unknown->unknown
• not：not unknown->unknown

如果where子句謂詞對一個元組計算出false或unknown,就不能加入結(jié)果中

可使用null來查找空值：

	select name
	from instructor
	where salary is null

也可使用unknown來測試一個比較運算是否為unknown：

	select name
	from instructor
	where salary >10000 is unknown

值得注意的是，在謂詞"null= null"會返回unknown，但在select distinct 中，對于元組{(‘A’,null),(‘A’,null)}，則認為這兩份拷貝是相同的，這兩種情況對null的處理不同

2.7 聚集函數(shù)

? 聚集函數(shù)(aggregate function)是以值集(集合或多重集合)為輸入并返回單個值得函數(shù)，SQL提供了5個標準得固有聚集函數(shù)。

• 平均值：avg
• 最小值：min
• 最大值：max
• 總和：sum
• 計數(shù)：count

sum和avg得輸入必須是數(shù)字集，其他運算符可以作用在非數(shù)字數(shù)據(jù)類型得集合上，比如字符串

2.7.1 基本聚集

例如：計算計算機系教師的平均工資：

	select avg(salary) as avg_salary
	from instructor
	where dept_name = 'Comp.Sci.';

可以使用as子句給計算的屬性取個有意義的名字，計算平均值時要保留重復(fù)項，不然顯然是錯的。

當然也可以在其他的聚集函數(shù)中去重，例如：找出在2018年春季學(xué)期授課的教師總數(shù)，顯然，無論教幾門課，教師都應(yīng)該只被計算一次。

	select count(distinct ID)
	from teaches
	where semester='Spring' and year='2018';

此外，SQL不允許在使用count(*)時使用distinct。在max和min時使用distinct時合法的，盡管結(jié)果沒有區(qū)別。同樣，我們也可以顯式地寫出all來表示要保留重復(fù)項

2.7.2 分組聚集

可以使用group by子句將聚集函數(shù)作用在某一組元組集上。group by子句中給出一個或多個屬性用來構(gòu)造分組。在分組子句中的所有屬性上取值相同的元組將被分在一個組內(nèi)。

例如：找出每個系的平均工資

	select dept_name,avg(salary) as avg_salary
	from instructor
	group by dept_name;

稍微復(fù)雜一點的例子：找出每個系在2018年春季學(xué)期授課的教師人數(shù)。由于授課信息在teaches關(guān)系中，所以要和instructor連接一下。

	select dept_name,count(distinct instructor.ID) as instr_count
	from instructor ,teaches
	where instructor.ID = teaches.ID and
				semester='Spring' and year='2018'
	group by dept_name

注意：使用分組時，確保出現(xiàn)在select語句中但沒有在聚集函數(shù)中的屬性，只能是出現(xiàn)在group by子句中的那些屬性，否則是錯誤查詢，例如：

	/*錯誤查詢*/
	select dept_name,ID,avg(salary)
	from instructor
	group by dept_name；

2.7.3 having 子句

having子句是用來篩選分好的組，例如，我們可能只對平均工資大于42000美元的系感興趣，這個約束條件并不針對單個元組，而是針對group by 構(gòu)成的每個分組。

	select dept_name,avg(salary) as avg_salary
	from instructor
	group by dept_name
	having avg(salary) > 42000

類似的，任何出現(xiàn)在having子句中，但沒有被聚集的屬性必須出現(xiàn)在group by子句中

另一個例子：找到至少有三個賬戶且居住在Harrison的客戶的平均存款

	select depositor.customer_name,avg(balance)
	from depositor,account,customer
	where depositor.account_number=account.account_number and
			  depositor.account_number=customer.account_number and
			  customer_city ='Harrison'
	group by depositor.customer_name
	having count(distinct depositor.account_number)>=3

2.7.4 對空值和布爾值的聚集

? 按照以下規(guī)則處理空值：除了count(*)之外所有的聚集函數(shù)都忽略輸入集合中的空值，并規(guī)定空集的count運算值為0，并且當作用在空集上時，其他所有聚集運算返回一個空值。

? 布爾數(shù)據(jù)類型：true、false、unknown,聚集函數(shù)some和every可應(yīng)用于布爾值的集合，并分別計算這些值的析取(or)和合取(and)

2.8 嵌套子查詢

通過將子查詢嵌套在where子句中，可以用子查詢來執(zhí)行對集合成員資格的測試、對集合的比較以及對集合基數(shù)的去欸的確定

2.8.1 集合成員資格

SQL允許測試元組在關(guān)系中的成員資格。連接詞in測試集合成員資格，這里的集合是由select子句產(chǎn)生的一組值構(gòu)成的。連接詞not in測試集合成員資格的缺失。

例如：找出所有在2017年秋季學(xué)期開課但不在2018年春季學(xué)期開課的課程，可寫為

	select distinct course_id
	from section
	where semster='Fall' and year=2017 and
	course_id not in(select course_id
                   from section
                   where semester='Spring' and year=2018)

找出選修了ID為10101的教師所講授的課程段的(不同)學(xué)生的總數(shù)：

	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')

2.8.2 集合比較

some表示某一，all表示全部，可以使用>,<,>=,<=,=,<>(不等于)

例如：找出工資至少比Biology系某位教師的工資要高的所有教師的姓名

	select name
	from instructor
	where salary > some(select salary
                      from  instructor
                      where dept_name='Biology')

找出平均工資最高的系

	select dept_name
	from instructor
	group by dept_name
	having avg(salary) >= all(select avg(salary)
                           	from instructor
                             group by dept_name )

2.8.3 空關(guān)系測試

SQL可測試一個子查詢的結(jié)果中是否存在元組。exists結(jié)構(gòu)在作為參數(shù)的子查詢非空時返回true

$existsr?r\ne ?$

$notexistsr?r=?$

例如：找出在2017秋季學(xué)期和2018春季學(xué)期都開課的所有課程

	select course_id
	from section as S
	where semester='Fall' and year=2017 and
		exists(select *
            from section as T
            where semester='Spring' and year=2018 and
           			  S.course_id=T.course_id);

我們知道$notexists(X?Y)?X?Y=??X?Y$

對于查詢：找出選修了Biology系開設(shè)的所有課程的所有學(xué)生。(即生物系的課是學(xué)生的課的子集)

	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))
                    /*找到學(xué)生S.ID選修的所有課*/

那么我們就可以說“關(guān)系A(chǔ)包含關(guān)系B”等價于“not exists(B except A)”

2.8.4 重復(fù)元組存在性測試

unique、not unique用于測試子查詢結(jié)果中是否存在重復(fù)元組

查詢：找出在2017年最多開設(shè)一次的所有課程

	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=2017);

2.9 數(shù)據(jù)庫的修改

主要是增加、刪除或修改信息

2.9.1 刪除

只能刪除整個元組，而不能只刪除某些屬性上的值
$$\begin{array}{rl}& deletefromr\\ & whereP;\end{array}$$
其中$P$代表一個為此，r代表一個關(guān)系，delete語句首先從r中找出使$P(t)$為真的所有元組$t$,然后把它們從$r$中刪除，where子句可以省略，在省略的情況下$r$中的所有元組都將被刪除出。

一條delete命令只能作用于一個關(guān)系，如果我們想從多個關(guān)系中刪除元組，必須為每個關(guān)系使用一條delete命令

2.9.2 插入

形如

insert into course(course_id,title,dept_name,credits)
			values('CS-437','Darabase Systems','Comp.Sci.',4)

r后面的屬性值可隨意交換，注意對應(yīng)好values中值的順序就行。也可不寫，則按默認的屬性順序插入

大多數(shù)情況適合select語句使用的

例如，我們想讓Music系每個修滿144學(xué)時的學(xué)生稱為Music系的教師，工資為18000美元

  insert into instructor
	select ID,name,dept_name,18000
	from student
	where dept_name='Music' and tot_cred>144;

2.9.3 更新

形如
$$\begin{array}{rl}& updater\\ & setExpressions\\ & whereP;\end{array}$$
其中where子句也可以包含select語句中的where子句的任何合法結(jié)構(gòu)(包括嵌套的select)

例如：給工資低于平均值的教師漲5%的工資

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

此外，SQL提供case結(jié)構(gòu)，可利用單條update語句進行多種情況的更新，以避免更新次序引發(fā)的問題，形如：
$$\begin{array}{rl}& updater\\ & setA=case\\ & whenpre{d}_{1}thenresul{t}_1\\ & whenpre{d}_{2}thenresul{t}_2\\ & ?\\ & whenpre{d}_{n}thenresul{t}_n\\ & elseresul{t}_0\\ & end\end{array}$$
例如文章來源地址http://www.zghlxwxcb.cn/news/detail-840899.html

	update instructor
	set salary = case
		when salary <=100000 then salary *1.05
		else salary*1.03
	 end

到了這里，關(guān)于數(shù)據(jù)庫引論：2.SQL簡介的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！