????????SQL：Structured Query Language，結構化查詢語言

? ? ? ? SQL是用于管理關系型數據庫并對其中的數據進行一系列操作（包括數據插入、查詢、修改刪除）的一種語言

? ? ? ? 分類：數據定義語言DDL、數據操縱語言DML、數據控制語言DCL、事務處理、存儲過程和函數、觸發器

一. SQL數據定義語言

? ? ? ? DDL包含創建、修改、刪除表的語句，創建、修改、刪除索引的語句，創建、修改、刪除視圖的語句。

	創建????????	修改	刪除
表	create table	alter table	drop table
索引	create index	alter index	drop index
視圖	create view	alter view	drop view

1 創建表

create table <表名>{<列名1> <數據類型> [列級完整性約束],<列名2> <數據類型> [列級完整性約束],[表級完整性約束]
};

1.1 表的數據類型

	數據類型	描述
文本型	char(n)	長度為n的定長字符串
	varchar(n)	長度為n的變長字符串
數字型	int	整數（4字節）
	smallint	短整數（2字節）
	bigint	大整數（8字節）
	float	單精度浮點數
	double	雙精度浮點數
	decimal(p,d)	定點數，由p位數字組成，小數點后有d位數字
	boolean	布爾型
時間型	date	日期，包含年、月、日，格式為YYYY-MM-DD
	time	時間，包含時、分、秒，格式為HH:MM:SS
	timestamp	時間戳，格式為YYYY-MM--DD HH:MM:SS

1.2 完整性約束

? ? ? ? 非空約束（not null）：指定某列不可為空

? ? ? ? 唯一約束（unique）：指定某列不能重復

? ? ? ? 主鍵約束（primary key）：指定某列位主鍵，主鍵屬性必須是非空且唯一的

? ? ? ? 外鍵約束（foreign key ... references）：指定某列為外鍵，同時需指定外鍵引用其他表的主鍵（可以與本表的每個主鍵，進行外鍵約束）

1.3 修改表

? ? ? ? 添加列：

alter table <表名> add [column] <列名> <數據類型>;

? ? ? ? 刪除列：

alter table <表名> drop [column] [restrict | cascade];

?restrict：如果該列被其他列引用，則無法刪除該列

?cascade：引用該列的其他列會和該列同時刪除

? ? ? ? 修改列：

alter table <表名> alter column <列名> <數據類型>;

1.4 刪除表

drop table <表名> add [restrict|cascade];

注：drop table不僅刪除表中的所有元組，還刪除了該表的關系模式

2 創建索引

? ? ? ? 為了提高查詢速率，可以在一個表上建立一個或者多個索引，以加速查詢。

create [unique] [cluster] index <索引名>
on <表名> (<列名> [<次序>], <列名> [<次序>]);

? ? ? ? 含義：在on字句中指定的一列或者多列上建立索引，還可以指定某列索引值的排列次序

? ? ? ? 次序：ASC（升序）、DESC（降序）

? ? ? ? unique：此索引的值必須唯一

? ? ? ? cluster：此索引是聚集/聚簇索引

2.1 修改、刪除索引

? ? ? ? 修改：

alter index <舊索引名> rename to <新索引名>;

? ? ? ? 刪除：

drop index <索引名>;

3 創建視圖

? ? ? ? 視圖是數據庫中的一個查詢的查詢結果構成的“虛關系”。

? ? ? ? 將重復執行的SQL查詢存儲在數據庫中，并對其進行命名，在之后的查詢中可通過名字進行查詢，可以減少重復工作。

? ? ? ? 一個視圖就是一個命名的存儲在數據庫中的查詢，視圖中的數據即為該查詢對應的查詢結果

create view <視圖名> [(<列名1>,<列名2>)] as <子查詢>;

子查詢：任何select語句

? ? ? ? 在執行create view語句時并不執行其中的子查詢語句，只是把視圖的定義存入數據字典。

select * from <視圖名> where 條件;

3.1 修改視圖

alter view <視圖名> as <子查詢>;

3.2 刪除視圖

drop view <視圖名> [cascade];

該語句將把指定的視圖定義從數據字典中刪除

cascade：該視圖和由該視圖導出的視圖都會被刪除

3.3?物化視圖

? ? ? ? 視圖是一種“虛關系”，實際查詢時需要根據定義查詢底層關系，當存在大量這樣的查詢時會有較高的成本。

? ? ? ? 某些數據庫支持物化視圖，像存儲表一樣將創建的視圖關系，“物化”存儲在數據庫中。

? ? ? ? 物化視圖的創建、修改與刪除語法同視圖類似，區別是多列關鍵字materialized

create materialized view <物化視圖名> [(<列名1>,<列名2>)] as <子查詢>;

二.SQL數據操縱語言

? ? ? ? DML包括數據的查詢、插入、刪除和修改

1 數據查詢

? ? ? ? 數據查詢是從數據庫中獲取滿足一定條件的數據，是數據庫的核心操作。利用SQL進行數據查詢的基本語法為：

select [all | distinct] <列表達式>
from <表名或視圖名>
where 條件
group by <列名1>,<列名2>
having 條件
order by <列表達式> [<次序>];

select子句：指定要顯示的屬性列

from子句：指定從哪些表或者視圖中查詢數據

where子句：篩選滿足條件的元組

group by子句：指定按照哪些列對元組進行分組

having子句：對分組結果進行篩選

order by子句：將查詢結果按照指定順序進行排序

1.1 選擇和投影

? ? ? ? 選擇操作可以選擇表中的若干元組，通過where子句實現

? ? ? ? 投影操作可以選擇表中的若干列，通過select子句實現

1.2 投影操作

? ? ? ? 投影操作可以選擇表中的若干列，主要體現在select子句后面的列表達式

? ? ? ? 查詢指定列：select句子中依次列出要查找列的名字

? ? ? ? 查詢全部列：用*號替代所有列

? ? ? ? 查詢經過計算的值：對列進行計算后的表達式

? ? ? ? 用戶可以通過AS指定列別名來改變查詢結果的列標題

select <列名> as <別名> from <表名或視圖名>;

1.3 選擇操作

? ? ? ? 選擇操作通過where子句選擇若干滿足條件的元組

? ? ? ? where子句后面跟著一個條件表達式，滿足該條件表達式的元組會被返回

查詢條件	謂詞
比較	= , > , < , >= , <= , != , <> , !> , !< , Not+上述比較符號
確定范圍	between and, not between and
確定集合	in, not in
空值	is null, is not null
邏輯運算	and, or
字符串運算	like, not like, _ , % , escape

????????like可以用來查詢與匹配串匹配的字符串。其語法格式為：

[not] like "<匹配串>" [escape "<轉碼字符>"];

匹配串一般由字符和通配符（ _ 或者 %）組成

_：表示匹配任意一個字符

%：表示匹配任意多個字符

當字符串的所有字符均可匹配成功時，該字符串的like查詢結果為真

當匹配串本身就包含通配符時，需要在匹配串中包含的通配符前加上轉義字符來紀念性轉移，同時需要使用escape "<轉碼字符>" 來指定轉碼字符

1.4 聚集操作

? ? ? ? 為了查詢一些數據聚集之后的結果，需要使用聚集函數。常見的聚集函數有：

聚集函數	含義
count([distinct | all *])	統計元組個數
count([distinct | all] <列名>)	統計一列值的個數
sum([distinct | all] <列名>)	統計一列值的總和
avg([distinct | all] <列名>)	統計一列值平均值
max([distinct | all] <列名>)	統計一列值的最大值
min([distinct | all] <列名>)	統計一列值的最小值

其中，distinct表示去除重復值，all表示保留所有值

1.5 分組操作

? ? ? ? group by子句可以將查詢的滿足條件的元組按某一列或者多列的值進行分組，值相等的為一組。

? ? ? ? 當進行分組，并且使用聚集函數時，聚集函數分別作用于每個組。

? ? ? ? 可以使用having句子對分組之后得到的組進行篩選。

? ? ? ? 任何沒有出現在group by子句中的列，如果出現在select子句中的話，那么它一定是出現在聚集函數內部。

1.6 排序操作

? ? ? ? order by將查詢結果按照一列或者多列的值進行排序，desc降序，asc升序

1.7 連接操作

? ? ? ? 同時涉及兩個以上表的查詢稱為連接查詢，通常需要在from子句中指定需要連接的表，在where子句中指定連接條件。常見的格式為：

[<表名1>.]<列名1> <比較運算符> [<表名2>.]<列名2>;
[<表名1>.]<列名1> between [<表名2>.]<列名2> and [<表名2>.]<列名3>;

注：如果連接操作的兩個表是同一個表，則需要在from子句中使用as來重命名表的名字，以區分。

? ? ? ? 連接操作的三種寫法：

select * from 表1, 表2 where 表1.列1==表2.列2;

select * 
from ((表1 inner join 表2 on 表1.列1=表2.列2);

?這里連接的方式有內連接（inner join，join）、左連接（left join，left outer join）、右連接（right join，right outer join）、外連接（full join，full outer join）、笛卡爾積（cross join）

select * 
from ((表1 inner join 表2 using(列)));

using使用表示表1和表2具有同名字段

1.8 外連接操作

? ? ? ? 外連接是連接運算的擴展，用來處理缺失值。

? ? ? ? 左外連接取出左表中所有與右表中的任一元組都不匹配的元組，用控制填充所有右表中的屬性，再將產生的元組加到左表和右表自然連接的結果中。

????????右外連接取出右表中所有與左表中的任一元組都不匹配的元組，用控制填充所有左表中的屬性，再將產生的元組加到右表和左表自然連接的結果中。

? ? ? ? 全外連接的查詢結果是左外連接和右外連接查詢結果的并集。

1.9 嵌套查詢

? ? ? ? 一個select-from-where語句是一個查詢塊，將一個查詢塊嵌套在另外一查詢塊的where子句、from子句或者having子句的條件中的查詢稱為嵌套查詢。上層的查詢稱為外層查詢或者父查詢，下層的查詢稱為內層查詢或者子查詢。在求解嵌套查詢時，先求解子查詢，然后基于子查詢的求解結果來求解父查詢。

? ? ? ? 有些嵌套查詢可以使用連接操作實現。當內層查詢的查詢結果是一個值時，使用=,>,<等比較運算符；當內層查詢的結果是一個集合時，使用in, not in, any, all 等。

? ? ? ? 當子查詢的查詢條件不依賴于父查詢的查詢時為不相關子查詢，依賴于父查詢的某個屬性的查詢為相關子查詢。

1.10 集合操作

? ? ? ? SQL語句查詢結果是元組的集合，因此，可以將查詢結果進行集合操作，SQL中支持的集合操作主要包括并（union）、交（intersect）和差（except），參加集合操作的各查詢結果列數必須相同，對應的數據類型也需要相同。

? ? ? ? 直接使用union、intersect、except表示去重，union/intersect/except all表示不去重

1.11 關系代數與SQL的轉換

? ? ? ? 關系代數是關系型數據庫理論的一部分，是SQL的基礎，SQL在執行時需要先轉換為等價的關系代數表達式。以下是轉換規則：

關系代數運算	對應SQL語句	關系代數運算	對應SQL語句
選擇運算	where	連接運算	join
投影運算	select	賦值運算	as
并運算	union	除運算	not exists
差運算	except	去重運算	distinct
笛卡爾積運算	from	廣義投影運算	select
重命名運算	as	聚集運算	聚集函數
交運算	intersect	分組運算	group by

2 數據更新

? ? ? ? 數據更新包括插入數據、刪除數據、修改數據

? ? ? ? 在執行數據更新時會檢查所插入、修改和刪除的元組是否破壞表中的完整性約束。如果不滿足完整性約束，則可能會執行失敗。

2.1 插入數據

insert into <表名> [(<列名1>,<列名2>)] values (<常量1>,<常量2>);

如果沒有在into子句中出現的列，新元組在這些列上的取值為空值。into子句中的列名可以省略，那么values子句中則要按表中列的順序依次列出所有列的取值。

2.2 插入子查詢結果

insert into <表名> [(<列名1>,<列名2>)]
子查詢;

2.3 修改數據

update <表名> set <列名1>=<表達式1> [,<列名2>=<表達式2>] where 條件;

?如果沒有where子句，則列對應的數據將全部被修改

2.4 刪除數據

delete from <表名> where 條件;

如果沒有where子句，則表對應的數據將全部被刪除

三. SQL數據控制語言

? ? ? ? SQL數據控制語言：

? ? ? ? ? ? ? ? 權限授予：用于向用戶授予數據庫操作權限

? ? ? ? ? ? ? ? 權限收回：用于向用戶收回數據庫操作權限

1 權限授予

grant <權限>[,<權限>]
on <對象類型><對象名>[,<對象類型><對象名>]
to <用戶名>[,<用戶名>]
[with grant option];

該語句的含義為將某些對象（由on子句指定，對象可以是表的形式）的某些操作權限（由grant子句指定）授予給某些用戶（由to子句指定）：

? ? ? ? with grant option：被授權用戶可以把該grant語句授予他的權限再授予其他用戶

? ? ? ? 權限：查詢數據權限（select）、插入新數據權限（insert）、更新數據權限（update）、刪除數據權限（delete）

? ? ? ? 可以指定grant語句的用戶：數據庫管理員、數據庫的創建者，擁有grant子句中指定的用戶

2 權限收回

revoke <權限>[,<權限>]
on <對象類型><對象名>[,<對象類型><對象名>]
from <用戶名>[,<用戶名>]
[cascade | restrict];

該語句的含義為收回某些用戶(由FROM子句指定)對某些對象(由ON子句指定)的某些操作權限(由REVOKE子句指定)

????????CASCADE:支持級聯收回，即由這些用戶授予了以上權限的用戶的這些權限也會被收回(默認選項)

????????RESTRICT:不支持級聯收回

四.存儲過程和函數

? ? ? ? 存儲過程和函數是實現經過編譯并存儲在數據庫中的一段SQL語句的集合

? ? ? ? 存儲過程和函數可以對一段代碼進行封裝，以便日后調用

? ? ? ? 數據庫中創建存儲過程的語句為create procedure，并通過call語句加存儲過程名來調用存儲過程

? ? ? ? 數據庫中創建函數的語句為create function，并通過函數名來調用函數

? ? ? ? 存儲過程的優勢：降低網絡開銷、安全性高、性能高

? ? ? ? 存儲過程的劣勢：開發調式難、可移植性差、難以支持分布式數據庫

1 創建和調用存儲過程

create procedure <存儲過程名> ([參數,參數])
begin
<SQL語句>
end <終止符>

call <存儲過程名()>;

括號內指定參數列表（也可以沒有參數，但括號不可省略）

終止符表示存儲過程結束，使用 delimiter <符號> 來定義終止符

參數格式為:[in| out | inout] 參數名 參數數據類型

? ? ? ? in表示該參數為輸入參數，out表示該參數為輸出參數，inout表示該參數既可以為輸入也可以為輸出

2 存儲過程和函數的作用

? ? ? ? 存儲過程和函數都用于提高數據庫性能，減少頻繁訪問數據庫和減少網絡延遲等方式加速執行效率。

? ? ? ? 函數：簡單的計算型任務，例如字符串或日期拼接、返回單個值等。

? ? ? ? 存儲過程：復雜的業務邏輯、更新和刪除相關操作。存儲過程需要顯式地被調用，并且可以包含各種復雜的控制結構和代碼塊。

3 創建和調用函數

create function <函數名> ([參數,參數])
returns <數據類型>
begin
<SQL語句>
end <終止符>

delimiter ;
select <函數名(參數)>;

參數格式與存儲過程相似，但只能是in參數，returns返回數據類型，return返回數據值

4 存儲過程和函數區別

? ? ? ? 存儲過程可以通過out或者inout參數返回多個值，而函數只能返回returns子句中指定的某一類型的單值或表對象。

? ? ? ? 存儲過程的參數可以為in、out、inout，而函數的參數只能是in類型。

? ? ? ? 存儲過程可以通過call語句作為一個獨立的部分來調用和執行，而函數可以作為查詢語句的一部分來調用。此外，由于函數可以返回表對象，因此函數的返回結果可以用在查詢語句的from子句中。

? ? ? ? 創建函數時必須指定返回值數據類型，且函數體內必須有一個returns語句。

? ? ? ? 存儲過程中可以執行更新表的數據庫操作，而函數不行。

5 變量和流程控制

? ? ? ? 變量：可以在存儲過程和函數中聲明比使用變量

? ? ? ? 流程控制：可以用來改變存儲過程和函數內部語句的執行順序。包括IF、LOOP、WHILE、REPEAT、LEAVE等。

5.1 變量

? ? ? ? 可以在存儲過程和函數中聲明并使用變量，變量的作為范圍是在begin...end語塊中

? ? ? ? 變量定義：

declare <變量名> <數據類型> [default <默認值>];

? ? ? ? 變量賦值：

set <變量名>=<表達式>;

5.2 IF語句

? ? ? ? IF語句包含多個判斷條件，根據判斷結果是否為真來選擇執行哪個分支。

if <表達式> then <sql語句>;
[elseif <表達式> then <sql語句>;
elseif <表達式> then <sql語句>;
]
else <sql語句>;
end if;

5.3 LOOP語句

? ? ? ? LOOP語句時循環語句，用來重復執行一些語句：

[<標簽>:] loop <sql語句> end loop [<標簽>];

程序執行時，會重復執行loop后的語句塊，知道循環被退出。

在loop語句中，使用leave子句可跳出循環。

loop語句中必須包含leave子句，否則會陷入死循環

標簽可以用來標志一個loop語句，為可選項。

5.4 WHILE語句

? ? ? ? while語句也是循環語句，當滿足while語句的循環條件時，循環會一直執行；當不滿足while語句的循環條件時，會跳出循環：

[<標簽>:] while <表達式> 
do <sql語句>
end while [<標簽>];

5.5 刪除存儲過程和函數

drop procedure <存儲過程名>;
drop function <函數名>;

五.觸發器

????????觸發器是與表相關的特殊的存儲過程，在滿足特定條件時，它會被觸發執行
????????觸發器是定義在基本表上的，當基本表被修改(比如插入、刪除、更新數據)時，會激活定義在其上的觸發器，該基本表稱為觸發器的目標表
????????觸發器可以用來保證數據庫的完整性

1 創建觸發器

create trigger <觸發器名>
<觸發時機> <觸發事件> on <表名>
for each row
<觸發動作體>;

觸發器名：要創建的觸發器的名字

觸發時機：指定了觸發執行的時間，可以為before（觸發事件之前）或after（觸發事件之后）

觸發事件：指定了當發生何種事件時，觸發器會被激活，包括insert、delete、update

表名：指定了觸發器時在哪張表上創建的

觸發動作體：觸發事件發生后要執行的SQL語句塊

? ? ? ? 對于觸發事件作用的每一行（for each row），會執行觸發動作體

? ? ? ? 對于觸發事件作用的每一行，在觸發事件發生之前該行稱為old，在觸發事件發生之后稱為new

? ? ? ? 可以使用old和new來訪問觸發事件發生前后的元組的值

2 刪除觸發器

drop trigger <觸發器名>;

觸發器時基于激活的，對數據庫中每行數據的修改都會調用觸發器。因此可能會導致數據庫性能的降低，所以要避免編寫太多的觸發器。

觸發器應用場景：數據完整性、數據審計、數據安全性、數據備份和同步