『数据库系统概论』学习笔记之四

『数据库系统概论』学习笔记之四

第三章 關係數據庫標准語言SQL

引言：
        因此章內容太多，故分成兩篇記之。閱讀前一部分請點擊這裡。

3.3    查詢
        數據庫查詢是數據庫的核心操作。動詞：Select。Select的定義很繁雜，略過不記，只需記住主體語句就可。Select語句相當於關係運算中的投影運算，但投影運算會去掉重復元組，而在SQL中要去掉重復元組須在Select語句後指定Distinct方可。

SQL表达式的基本结构由select子句、from子句和where子句构成，其中where子句可以省略，如下所示：
Select A1, A2, …, An　　对应投影，列出要显示的属性
From r1, r2, …, rm　　　对应笛卡尔积，对关系进行扫描
Where P　　　　　　　　对应谓词，指出查询条件

常用的查詢條件
比較：=、>、<、>=、<=、!=、!>、!<、Not +上述比較運算符
確定范圍：Between ? And ?、Not Between ? And ?
確定集合：In、Not In
字符匹配：Like、Not Like
空值：Is Null、Is Not Null
多重條件：And、Or

一、單表查詢

        單表查詢比較簡單，比如：

--從Employees表中找出所有年齡在20到30歲之間的“蜀”部門的職員姓名和年齡：
　　Select Ename,Eage from Employees where Edept='蜀' and Eage between 20 and 30;

1、字符串操作
        如果我们要对定长的字符串进行整体匹配，可以用"＝"，否则只能用like操作符。另外字符串除了进行匹配之外，还可以按照字典顺序等进行比较，例如字符串"a">字符串"b"。
另外需要特别注意的是，字符串常量可以括在双引号中，也可以括在单引号中。如果习惯使用单引号，则一定要注意字符串本身的单引号的转义问题，因为在很多时候，字符串是需要用单引号的。例如：This is Bonny's Article。
        在like操作符的模式中：
⑴用百分号%（有时是星号*）匹配任意子串；
⑵用下划线_（有时是问号?）匹配任意一个字符；
⑶模式中是要区分字母的大小写的；
⑷在模式中为了使用特殊字符%和_等，允许用反斜线字符\将特殊字符转义成普通字符。例如："a\%b%"和"a\\b"等等。

--查詢程序名稱以“fm_”開頭，倒數第三個字符是“9”的所有子程序的詳細情況：
  select *
  from Programs
  where Pname like 'fm\_%i__' ESCAPE '\' 

/**//*說明：
ESCAPE 'escape_character' 
允許萬用字元在字元字串中進行搜尋，而不是用來做為萬用字元，escape_character 是置於萬用字元前方的字元，用以代表這項特殊用途。
*/

2、排序
        用Order By對查詢結果按照一個或多個屬性列的升序（ASC）或降序（DESC）排列

3、集合函數
        主要有Count、Sum、Max、Min、Avg五種。

4、分組
        Group by子句將照查詢結果表按某一列或多列值分組，值相同的為一組。
        Having子句將分組後的結果按指定條件進行篩選。

--查詢各個子程序號及相應被賦予權限的人數
  select Pid,count(Pid)
  from Authority
  Group by pid

--查詢被賦予了三個以上權限的職員代號
  select Eid
  from Authority
  Group by Eid
  Having count(*)>3

二、連接查詢
        同時涉及兩個以上表的查詢稱之為連接查詢。
1、等值和非等值連接
        連接運算符為=的稱之為等值連接，其它的叫非等值連接。
2、自身連接
        這種情況發生在一個表與其自身進行連接的時候。
3、外連接
        適用於同時需要查詢滿足條件和不滿足條件的信息。

--查詢所有職員的權限分配情況
  select E.*,A.Pid
  from Employees E,Authority A
  where E.Eid=A.Eid(*)

--以上語句是標准SQL語法，Transact-SQL語法為：
  select E.*,A.Pid
  from Employees E left join Authority A ON E.Eid=A.Eid

4、復合條件連接
        where子句中有多個連接條件的。各個連接條件用AND連接。

三、嵌套查詢
        將一個查詢塊嵌套在另一個查詢塊的Where子句或Having短語的條件中的查詢稱之為嵌套查詢。
        注意：子查詢的Select語句中不能用Order by子句。
        嵌套查詢的求解方法為由裡向外處理，即每個子查詢在其上級查詢處理之前處理，子查詢的結果用於建立父查詢的查找條件。
1、集合成員資格的確認：
        连接词in和not in用来判断一个元素是否在某个集合中。例如：

--查詢與劉備有相同權限的人員姓名
  select E.Ename from Employees E where Eid in (
select Eid from Authority A where Pid In(
select Pid from authority B where Eid = (
select Eid from Employees where Ename='劉務' )))

--查詢部門為“蜀”和“吳”以外的人員資料
  select * from Employees
  where Edept not in ("蜀", "吳")

2、集合的比較：
        ⑴"至少比一个…"的集合比较运算符还有：<some、<=some、>some、>=some、=some和<>some。在这里=some等价于in；
⑵"比所有都…"的集合比较运算符有：<all、<=all、>all、>=all、=all和<>all。在这里 <>all 等价于not in。

--查詢平均年齡最大的部門名稱
    select Edept from Employees
    Group by Edept
    Having avg(Eage)>=all (select avg(Eage) from Employees Group by Edept)

3、集合基數的測試
        ⑴测试一个子查询的结果是否有元组，即测试其是否为空关系。使用关键词exists和not exists，還有exists或Not exists的子查詢不返回任何數據，只返回“True”或“Flase”；
⑵测试一个子查询的结果是否有重复元组。使用关键词unique和not unique。

--查詢賦予了代號為frmsys990子程序權限的人員姓名：
　　select Ename from Employees E where exists (
select * from Authority A where A.Eid=E.Eid and pid='frmsys990')

四、集合查詢
        涉及多個Select查詢語句的查詢。標准SQL主要是Union操作。
        注意：Union操作會自動去掉重復的元組，參加Union操作的各結果表的列數必須相同，對應的數據類型也必須相同。

--查詢部門“蜀”的職員資料及查詢年齡大於30歲的職員資料
　　select * from Employees where Edept='蜀'
　　Union
　　select * from Employees Where Eage>30

--等價於
　　select * from Employees where Edept='蜀' and Eage>30

3.5    視圖
        视图是从一个或几个基本表或其他视图导出的表定义。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。因此视图是一个虚表。视图在概念上与基本表等同，用户可以在视图上再定义视图。
        使用视图有以下作用：
1、视图能够简化用户的操作；
2、视图能使用户以多种角度看待同一数据；
3、视图对重构数据库提供了一定程度的逻辑独立性；
4、视图能够对机密数据提供安全保护。
由于视图是不实际存储数据的虚表，因此对视图的更新（INSERT、DELETE、UPDATE）最终要转换为对基本表的更新。具體的更新與各DBMS的定義有關。

3.5.1    定義視圖
        格式：Create View <視圖名>[(<列名1>[,<列名2>]...)] AS <子查詢> [With Check Option]
        說明：1、子查詢是任意複雜的Select語句，但通常不允許含有Order by和Distinct短語。
       　　　2、With Check Option表示對視圖進行更新操作時需要滿足子查詢中的條件表達式。
        　　　3、組成視圖的列名可以省略，但有三種情況是不能省略的。（1）某個目標列不是單純的屬性名；（2）多表連接時選擇了多個相同的列名；（3）需要為某列取用更合適的名字。

--建立部門為“蜀”的職員資料視圖
　　Create View V_Employees
　　As
　　select Eid,Ename,Eage from Employees where Edept='蜀'

--刪除上面建立的視圖
　　Drop View V_Employees

3.5.2    查詢視圖
        視圖可以像表一樣被查詢。DBMS執行視圖查詢前，會首先檢查表與視圖是否存在，存在的時候會取出視圖的定義，把用戶的查詢與定義的子查詢結合起來，轉換成對表的查詢，然後才執行修正後的查詢。

--在視圖中查詢年齡在30歲以上的職員姓名
　　select Ename from V_Employees where Eage>30

3.5.3    更新視圖
        基本操作與表的一樣，但存在一些約束，並且不是每一個DBMS都允許對視圖更新。

未完待續。。。

版權聲明：此為原創，轉載請注明出處：www.cnblogs.com Bonny.wong(讓思想飛翔) 2005.2.25