資料庫作業14：SQL總結

模式的定義與洗掉

定義模式

CREATE SCHEMA<模式名>AUTHORIZATION<用戶名>;

• 如果沒有指定<模式名>，那么<模式名>隱含為<用戶名>，

洗掉模式

DROP SCHEMA <模式名><CASCADE|RESTRICT>;

• CASCADE是級聯，表示在洗掉模式的同時把該模式中所有的資料庫物件全部洗掉
• RESTRICT是限制，表示如果模式中已經定義了下屬的資料庫物件，則拒絕該洗掉陳述句的執行，

基本表的定義、洗掉與修改

定義基本表

CREATE TABLE<表名>(<列名><資料型別>[列級完整性約束條件]
					[<列名><資料型別>[列級完整性約束條件]]
					...
					[,<表級完整性約束條件>]);/*表級寫在最后*/

• 建表的同時通常還可以定義與該表有關的完整性約束條件，這些完整性約束條件被存入系統的資料字典中，當用戶操作表中資料時由關系資料庫管理系統自動檢查該才做是否違背這些完整性約束條件，如果完整性約束條件涉及該表的多個屬性列，則必須定義在表級上，否則既可以定義在列級也可以定義在表級，

修改基本表

ALTER TABLE <表名>
[ADD[COLUMN]<新列名><資料型別>[完整性約束]]
[ADD<表級完整性約束>]
--增加新列、新的列級完整性約束條件和新的表級完整性約束條件
[DROP [COLUMN]<列名>[CASCADE|RESTRICT]]
--用于洗掉表中的列
[DROP CONSTRAINT<完整性約束名>[RESTRICT|CASCADE]]
--用于洗掉指定的完整性約束條件
[ALTER COLUMN<列名><資料型別>]
--修改原有的列定義，包括修改列名和資料型別，

洗掉基本表

DROP TABLE<表名>[RESTRICT|CASCADE];

• T-SQL中不支持級聯洗掉，應先查詢外鍵將外鍵洗掉

SELECT NAME
FROM SYS.FOREIGN_KEY_COLUMNS F JOIN SYS.OBJECTS O ON F.CONSTRAINT_OBJECT_ID=O.OBJECT_ID
WHERE F.PARENT_OBJECT_ID=OBJECT_ID(<表名>)

• 然后洗掉外鍵約束名：

ALTER TABLE <表名> DROP CONSTRAINT <外鍵名>

詳情可查看：資料庫作業3：第三章例題實踐

索引的建立與洗掉

建立索引

CREATE [UNIQUE][CLUSTER]INDEX<索引名>
ON<表名>(<列名>[<次序>][,<列名>[<次序>]]...);

• 每個<列名>后面還可以用<次序>指定索引值的排列次序，可選ASC（升序）或DESC（降序），默認值為ASC，

修改索引

ALTER INDEX <舊索引名>RENAME TO<新索引名>;

在T-SQL中與標準SQL仍有差別，詳情可查看：資料庫作業4：插入資料、單表查詢以及整體實作！

洗掉索引

在SQL中，洗掉索引使用DROP INDEX陳述句，其一般格式為：

DROP INDEX <索引名>;

• 在T-SQL中，陳述句必須為其設定表名和索引名：

DROP INDEX <索引名> ON <表名>;

詳情可查看：資料庫作業4：插入資料、單表查詢以及整體實作！

資料查詢

SQL提供了SELECT陳述句進行資料查詢，該陳述句具有靈活的使用方式和豐富的功能，其一般格式為：

SELECT [ALL|DISTINCT]<目標列運算式>[,<目標列運算式>]...
FROM<表名或視圖名>[,<表名或視圖名>...]|(<SELECT陳述句>)[AS]<別名>
[WHERE<條件運算式>]
[GROUP BY<列名1>[HAVING<條件運算式>]]
[ORDER BY<列名2>[ASC|DESC]];

• 如果有GROUP BY子句，則將結果按<列名1>的值進行分組，該屬性列值相等的元組為一個組，通常會在每組中作用聚集函式，如果GROUP
  BY子句帶HAVING短語，則只有滿足指定條件的組才予以輸出，
  如果有ORDER BY子句，則結果表還要按<列名2>的值的升序或降序排序，

WHERE子句常用的查詢條件

字符匹配

謂詞LIKE可以用來進行字串的匹配，其一般語法格式如下：

[NOT] LIKE '<匹配串>' [ESCAPE'<換碼字符>']

• <匹配串>可以是一個完整的字串，也可以含有通配符%和_，其中：
• %代表任意長度的字串，
• _代表任意單個字符，

ORDER BY子句

用戶可以用ORDER BY子句對查詢結果按照一個或多個屬性列的升序(ASC)或降序(DESC)排序，默認值為升序，

聚集函式

COUNT * --統計元組個數
COUNT([DISTINCT|ALL]<列名>) --統計一列中值的個數
SUM([DISTINCT|ALL]<列名>)  --計算一列值的總和（此列必須時數值型）
AVG([DISTINCT|ALL]<列名>)  --計算一列值的平均值（此列必須是數值型）
MAX([DISTINCT|ALL]<列名>)  --求一列值的最大值
MIN([DISTINCT|ALL]<列名>)  --求一列值的最小值

• 如果指定DISTINCT短語，則表示在計算時要取消置頂列中的重復值，如果不指定DISTINCT短語或指定ALL短語，則表示不取消重復值，

連接查詢

等值與非等值連接查詢

連接查詢的WHERE子句中用來連接兩個表的條件稱為連接條件或連接謂詞，其一般格式為：

[<表名1>.]<列名1><比較運算子>[<表名2>.]<列名2>

此外連接謂詞還可以使用下面形式：

[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

• 當連接運算子為=時，稱為等值連接，使用其他運算子稱為非等值連接，

嵌套查詢

在SQL語言中，一個SELCECT-FROM-WHERE陳述句成為一個查詢塊，將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢成為嵌套查詢，例如：

SELECT Sname --外層查詢或父查詢
FROM Student
WHERE Sno IN
	(SELECT Sno--內層查詢或子查詢
	FROM SC
	WHERE Cno='2');

• 本例中，下層查詢塊是嵌套在上層查詢塊條件中的，上層的查詢塊稱為外層查詢或父查詢，下層查詢塊稱為內層查詢或子查詢，

帶有ANY(SOME)或ALL謂詞的子查詢

子查詢回傳單值時可以用比較運算子，但回傳多值時要用ANY（有的系統用SOME）或ALL為此修飾符，而是用ANY或ALL謂詞時必同時使用比較運算子，

> ANY  大于子查詢結果中的某個值

> ALL  大于子查詢結果中的所有值

< ANY  小于子查詢結果中的某個值

< ALL  小于子查詢結果中的所有值

>=ANY  大于等于子查詢結果中的某個值

>=ALL  大于等于子查詢結果中的所有值

<=ANY  小于等于子查詢結果中的某個值

<=ALL 小于等于子查詢結果中的所有值

= ANY  等于子查詢結果中的某個值

= ALL  等于子查詢結果中的所有值（通常沒有實際意義）

!=(或<>) ANY  不等于子查詢結果中的某個值

!=(或<>) ALL  不等于子查詢結果中的任何一個值

帶有EXISTS謂詞的子查詢

EXISTS代表存在量詞，帶有EXISTS謂詞的子查詢不回傳任何資料，只產生邏輯真值“true”或邏輯假值“false”;

集合查詢

集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT，
ps：參加集合操作的各查詢結果的列數必須相同；對應項的資料型別也必須相同，

基于派生表的查詢

子查詢不僅可以出現在WHERE子句中，還可以出現在FROM子句中，這是子查詢生成的臨時派生表成為主查詢的查詢物件，

SELECT陳述句的一般格式

SELECT陳述句時SQL的核心陳述句，從前面的例子可以看到其陳述句成分豐富多樣，總結一下他們的一般格式，
SELECT陳述句的一般格式：

	SELECT[ALL|DISTINCT]<目標列運算式>[別名][,<目標列運算式>[別名]]...
	FROM<表名或視圖名>[別名][,<表明或視圖名>[別名]...]|(<SELECT陳述句>)[AS]<別名>
	[WHERE<條件運算式>]
	[GROUP BY<列名1>[HAVING<條件運算式>]]
	[ORDER BY<列名2>[ASC|DESC]];

資料更新

插入資料

插入元組

插入元組的INSERT陳述句格式為：

INSERT
INTO<表名>[<屬性列1>[,<屬性列2>]...)]
VALUES(<常量1>[,<常量2>]...);

插入子查詢結果

子查詢不僅可以嵌套在SELECT陳述句中用以構造父查詢的條件，也可以嵌套在INSERT陳述句中用以生成要插入的批量資料，
格式如下：

INSERT
INTO<表名>[(<屬性列1>[,<屬性列2>...])]
子查詢；

修改資料

修改操作又稱為更新操作，其陳述句的一般格式為：

UPDATE<表名>
SET<列名>=<運算式>[,<列名>=<運算式>]...
[WHERE<條件>];

洗掉資料

洗掉陳述句的一般格式為：

DELETE
FROM<表名>
[WHERE<條件>];

空值的處理

所謂空值就是“不知道”或“不存在”或“無意義”的值，

取空值一般有以下幾種情況：

• 該屬性應該有一個值，但目前不知道它的具體值，
• 該屬性不應該有值，
• 由于某種原因不便于填寫，

空值是一個很特殊的值，含有不確定性，對關系運算帶來特殊的問題，需要做特殊的處理，

視圖

視圖是從一個或幾個基本表(或視圖)匯出的表，它與基本表不同，是一個虛表，資料庫中只存放視圖的定義，而不存放視圖對應的資料，這些資料仍存放在原來的基本表中，

定義視圖

建立視圖

SQL語言用CREATE VIEW命令建立視圖，其一般格式為：

CREATE VIEW <視圖名>[(<列名>[,<列名>]...)]
AS<子查詢>
[WITH CHECK OPTION];

• 子查詢可以是任意的SELECT陳述句，是否可以含有ORDER BY子句和DISTINCT短語，則取決于具體系統的實作，

洗掉視圖

該陳述句的格式為：

DROP VIEW<視圖名>[CASCADE];

• 視圖洗掉后視圖的定義將從資料字典中洗掉，如果該視圖上還匯出了其他視圖，則使用CASCADE級聯洗掉陳述句把該視圖和由它匯出的所有視圖一起洗掉，

查詢視圖

視圖定義后，用戶就可以像對基本表一樣對視圖進行查詢了，

更新視圖

• 是指通過視圖來插入（INSERT）、洗掉(DELETE)和修改(UPDATE)，
• 由于試圖是不實際存盤資料的虛表，因此對視圖的更新最終瑤轉換為對基本表的更新，

授權：授予與識訓

SQL中使用GRANT和REVOKE陳述句向用戶授予或識訓對資料的操作權限，GRANT陳述句向用戶授予權限，REVOKE陳述句識訓已經授予的用戶權限，

GRANT

GRANT陳述句的一般格式為：

GRANT <權限>[,<權限>]...
ON<物件型別><物件名>[,<物件型別><物件名>]...
TO<用戶>[,<用戶>]...
[WITH GRANT OPTION];

• "[WITH GRANT OPTION];"是指被授權用戶還可以授予其他用戶權限，但不允許回圈授予；

REVOKE

授予用戶的權限可以由資料庫管理員或其他授權者用REVOKE陳述句識訓，REVOKE陳述句的一般格式為：

REVOKE<權限>[,<權限>]...
ON<物件型別><物件名>[,<物件型別><物件名>]...
FROM<用戶>[,<用戶>]...[CASCADE|RESTRICT];

資料庫角色

角色的創建

創建角色的SQL陳述句格式是：

CREATE ROLE<角色名>

給角色授權

GRANT<權限>[,<權限>]...
ON<物件型別>物件名
TO<角色>[,<角色>]...

• 資料庫管理員和用戶可以利用GRANT陳述句將權限授予某一個或幾個角色，

將一個角色授予其他的角色或用戶

GRANT<角色1>[,<角色2>]...
TO<角色3>[,<用戶1>]...
[WITH ADMIN OPTION]

• 授予或者是角色的創建者，或者擁有在這個角色上的ADMIN OPTION， 如果制定了WITH ADMIN
  OPTION子句，則獲得某種權限的角色或用戶還可以把這種權限再授予其他角色，

視圖機制

[例4.14]建立計算機系學生的視圖，把對該視圖的SELECT 權限授予王平，把該視圖上的所有操作權限授予張明，

GO
CREATE VIEW CS_Student
AS
	SELECT *
	FROM Student
	WHERE Sdept='CS';

GRANT SELECT
ON CS_Student
TO 王平;

GRANT ALL PRIVILEGES
ON CS_Student
TO 張明;
GO

審計

審計功能就是資料庫管理系統達到C2以上安全級別必不可少的一項指標，
審計日志是將用戶對資料庫的所有操作記錄在上面，

完整性約束命名子句

SQL還在CREATE TABLE陳述句中提供了完整性約束命名子 CONSTRAINT，用來對完整性約束條件命名，從而可以靈活地增加、洗掉一個完整性約束條件，

完整性約束命名子句

CONSTRAINT<完整性約束條件名><完整性約束條件>

• <完整性約束條件>包括NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK短語等，

域中的完整性限制

SQL支持域的概念，并可以用CREATE DOMAIN陳述句建立一個域以及該域應該滿足的完整性約束條件，然后就可以用域來定義屬性，

斷言

在SQL中可以使用資料定義語言中的CREATE ASSERTION陳述句，通過宣告性斷言來指定更具一般性的約束，

創建斷言的陳述句格式

CREATE ASSERTION<斷言名><CHECK 子句>

• 每個斷言都被賦予一個名字，<CHECK 子句>中的約束條件與WHERE子句的條件運算式類似，

觸發器

• 任何用戶對表的增、刪、改操作均由服務器自動激活相應的觸發器，在關系資料庫管理系統核心層進行集中的完整性控制，
• 觸發器類似于約束，但是比約束更加靈活，可以實施更為復雜的檢查和操作，具有更精細和更強大的資料控制能力，

定義觸發器

SQL使用CREATE TRIGGER命令建立觸發器，其一般格式為：

CREATE TRIGGER<觸發器名>
{BEFORE|AFTER}<觸發事件>ON<表名>
REFERENCING NEW|OLD ROW AS<變數>
FOR EACH{ROW|STATEMENT}
[WHEN<觸發條件>]<觸發動作體>

存盤程序和函式

存盤程序是由程序化SQL陳述句書寫的程序，這個程序經編譯和優化后存盤在資料庫服務器中，因此稱它為存盤程序，使用時只要呼叫即可，

存盤程序

創建存盤程序

CREATE OR REPLACE PROCEDURE 程序名 ([引數1,引數2,...])
AS<程序化SQL語言>;

執行存盤程序

CALL/PERFORM PROCEDURE 程序名([引數1,引數2,...]);

修改存盤程序

使用ALTER PROCEDURE 重命名一個存盤程序：

ALTER PROCEDURE 程序1 RENAME TO 程序2；

洗掉存盤程序

DROP PROCEDURE 程序名();

函式

函式的定義陳述句格式

CREATE OR REPLACE FUNCTION 函式名 ([引數1,引數2,...])RETURNS<型別>
AS <程序化SQL塊>;

函式的執行陳述句格式

CALL/SELECT 函式名 ([引數1,引數2,...]);

修改函式

可以使用ALTER FUNCTION重命名一個自定義函式；

ALTER FUNCTION 程序名1 RENAME TO 程序名2；

可以使用ALTER FUNCTION重新編譯一個函式：

ALTER FUNCTION 函式名 COMPILE;

有關觸發器，存盤程序和函式的T-SQL陳述句請查看：資料庫作業12：第五章例題（5.1~5.20）

至此，SQL總結已全部完成！在過往的學習中，經歷了不少困難，解決困難之后還是有很大識訓！在以后的學習中也應該更加努力！

感謝閱讀~