資料庫學習三 SQL 語言

📢 本文由 gemini-3-flash-preview 翻譯

SQL Series
資料庫學習一資料庫導論: https://blog.yexca.net/archives/86
資料庫學習二關聯模型: https://blog.yexca.net/archives/87
資料庫學習三 SQL 語言: 本文
資料庫學習四關聯資料庫理論: https://blog.yexca.net/archives/89
資料庫學習五範式: https://blog.yexca.net/archives/90
資料庫學習六資料庫設計: https://blog.yexca.net/archives/91
資料庫學習七資料庫的控制功能: https://blog.yexca.net/archives/92

SQL 語言的分類

DDL (Data Definition Language, 資料定義語言)

在資料庫系統中，每一個資料庫、資料庫中的資料表、檢視表和索引等都是資料庫的物件，要建立和刪除一個資料庫物件，都可以透過 SQL 語言來完成。DDL 包括 CREATE、ALTER 和 DROP 等。

DML (Data Manipulation Language, 資料操作語言)

DML 是指用來新增、修改和刪除資料庫中資料的語句，包括 INSERT、DELETE 和 UPDATE 等。

DQL (Data Query Language, 資料查詢語言)

查詢是資料庫的基本功能，查詢操作透過 SQL 資料查詢語言來實現。例如，用 SELECT 查詢資料表中的內容。

DCL (Data Control Language, 資料控制語言)

DCL 包括資料庫物件的權限管理和交易管理等。

(一)、DDL 資料定義語言

1. 建立資料庫

1
create database <資料庫名>

2. 建立基本資料表

1
2
3
4
5
6
7
create table <資料表名>
(
    <欄位名> <資料類型> [欄位級完整性條件約束],
    [···,]
    [資料表級完整性條件約束,]
    [···]
)

<1> 資料類型

INT / INTEGER : 整數
FLOAT(n) : 浮點數，精度至少為 n 位數字
NUMERIC(p, d) / DECIMAL(p, d) / DEC(p, d) : 定點數，由 p 位數字 (不包括正負號、小數點) 組成，小數點後面有 d 位數字
CHAR(n) : 長度為 n 的定長字串
DATETIME : 日期時間型

1
2
3
4
5
6
-- 例如
create table test
(
    f1 char(10),
    f2 int
)

<2> 欄位級完整性條件約束

not null: 不可為空值
unique: 唯一
not null unique: 不可為空值且唯一
default: 定義一個預設值

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 例如
create table test
(
    f1 int not null, 
    -- 該欄位不可為空
    
    f2 int unique, 
    -- 該欄位值可以為空，但每列應不一樣
    
    f3 int not null unique, 
    -- 該欄位不可為空且每列不一樣
    
    f4 char(5) default 'nashi' 
    -- 若新增列未賦值，此欄位預設賦值 'nashi'
)

<3> 資料表級完整性條件約束

名稱	子句	對應關聯完整性
主鍵子句	PRIMARY KEY	實體完整性
檢查子句	CHECK	使用者自定義完整性
外鍵子句	FOREIGN KEY	參照完整性

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
-- 例如
create table student
(
    sid int,
    primary key(sid) -- not null unique
)
-- 或者
create table student
(
    sid int primary key
)

create table course
(
    cid int,
    sid int,
    score int,
    check(score >= 0 and score <= 100),
    foreign key(sid) references student(sid)
)

對於完整性的檢查

斷言 (Assertion，一般不主張使用)

1
2
3
4
CREATE ASSERTION <斷言名> <check子句>  
-- 其中check子句與where子句的運算式類似
-- 刪除斷言名稱
DROP ASSERTION <斷言名>;

觸發器 (Trigger)

當對一個資料表進行增、刪、改的時候，對觸發器裡面的條件進行檢查，如果成立，則執行觸發器裡面的動作，否則不執行裡面的動作。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TRIGGER <觸發器名>           
-- 每當觸發事件發生時，該觸發器被啟動
{BEFORE|AFTER} <觸發事件> ON <資料表名> 
-- 指明觸發器啟動的時間是在執行觸發事件前或後
REFERERCING NEW|OLD ROW AS <變數>  
-- REFERENCING指出參照的變數
FOR EACH{ROW|STATEMENT}            
-- 定義觸發器的類型，指明動作主體執行的頻率
[WHEN <觸發事件>] <觸發動作主體>      
-- 僅當觸發條件為真時才執行觸發動作主體
 
-- 刪除觸發器
DROP TRIGGER <觸發器名> ON <資料表名>

3. 修改基本資料表結構

增加一個新欄位

1
2
3
4
alter table <資料表名> add <欄位名> <類型>

-- 例如
alter table test add f3 datetime

修改一個欄位

1
2
3
4
5
6
7
alter table <資料表名> alter column <欄位名> <新類型>
-- MySQL
-- alter table <資料表名> modify <欄位名> <新類型>

-- 例如
alter table test alter column f3 float
-- alter table test modify f3 float

刪除一個欄位

1
2
3
4
5
6
7
alter table <資料表名> drop column <欄位名>
-- MySQL
-- alter table <資料表名> drop <欄位名>

-- 例如
alter table test drop column f3
-- alter table test drop f3

4. 刪除基本資料表

1
2
3
4
drop table <資料表名>

-- 例如
drop table test

(二)、DML 資料操作語言

以下內容的範例基於這幾張資料表：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- 註：為方便理解使用中文，在實際開發中，請儘量不要使用中文
-- 學生表
create table student
(
    學號 char(5) not null unique,
    姓名 char(8),
    性別 char(2),
    出生日期 datetime,
    班號 char(5)
)
-- 成績表
create table score
(
    學號 char(5),
    課程號 char(5),
    分數 int
)
-- 教師表
create table teacher
(
    教師編號 char(5) not null unique,
    姓名 char(8),
    性別 char(2),
    出生日期 datetime,
    職稱 char(6),
    系別 char(10)
)
-- 課程表
create table course
(
    課程號 char(5) not null unique,
    課程名 char(20),
    任課教師編號 char(5)
)

1. INSERT 插入語句

向 SQL 的基本資料表中插入資料有兩種方式：直接插入值組 (Tuple) 或插入一個查詢的結果值。

直接插入值組

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
insert into 表名[(欄位序列)] values (值組)[, ···]
-- 或者
insert into 表名[(欄位序列)]
(table (值組)[, (值組), ···])

-- 例如
insert into student
values 
('108', '張三', '男', '2000-01-01', '09033'),
('109', '李四', '男', '2000-05-05', '09034')
-- 插入指定欄位
insert into student(學號，姓名，性別，出生日期)
values (110, '王五', '男', '2000-04-18')

插入一個查詢的結果值

1
2
3
4
5
insert into 表名[(欄位序列)] select <查詢語句>

-- 例如
insert into student
select 111, '趙六', '男', '2000-03-19', '09035'

2. DELETE 刪除語句

1
2
3
4
5
6
7
delete from <資料表名> [where <條件運算式>]

-- 例如
delete from student -- 刪除整個資料表的所有內容
-- 刪除學號為 111 的資料列
delete from student
where 學號 = 111

3. UPDATE 修改語句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
update <資料表名>
set <欄位名> = <值運算式>[, ···]
[where <條件運算式>]

-- 例如
update student
set 班號 = '09058'
where 學號 = 110
-- 或者
update student
set 班號 = '09057', 性別 = '女'
where 學號 = 110 and 班號 = '09058'

(三)、DQL 資料查詢語言

SQL 資料查詢功能是透過 SELECT 語句實現，完整語法如下：

1
2
3
4
5
6
select <目標表的欄位名或列表達式序列>
from <關聯表序列>
[where <列條件運算式>]
[group by <欄位序列>]
[having <群組條件運算式>]
[order by <欄位名> [asc|desc]]

1. 投影查詢

使用 SELECT 命令可以選擇查詢資料表中的任意欄位，當為多個欄位時，中間使用逗號 , 分隔。當 FROM 子句中的關聯表不止一個時，每個資料表的名稱都要寫出。

1
2
3
4
5
6
7
8
9
-- 例如
select 1 + 1
select 學號 * 100 from student
-- 查詢學生學號與分數
select student.學號, score.分數
from student, score
-- 查詢所有欄位
select *
from student

使用 DISTINCT 可以去除重複列。

1
2
3
-- 去除重複列，查詢被選修的課程
select distinct 課程號
from score

使用 AS 可為查詢結果自定義欄位名稱。

1
2
3
-- 例如
select 學號 as 'sno'
from student

2. 選擇查詢

選擇查詢就是指定查詢條件，只從來源表提取或顯示滿足該查詢條件的記錄。

選擇查詢通常使用下列幾類運算子：

運算子	代碼	描述
集合成員運算子	IN NOT IN	在集合中不在集合中
字串比對運算子	LIKE	與 `_` 和 `%` 進行單個或多個字元比對
空值比較運算子	IS NULL IS NOT NULL	為空值不為空值
算術運算子	> >= «br /><= = <>	大於大於等於小於小於等於等於不等於
邏輯運算子	AND OR NOT	與 (And) 或 (Or) 非 (Not)

<1> 條件運算式的比較運算

1
2
3
4
5
6
--- 查詢分數在 60-80 之間的記錄
select *
from score
where 分數 between 60 and 80
-- 使用 between 指包含 60 與 80，即 [60, 80]
-- 等價於 where 分數 >= 60 and 分數 <= 80

<2> 字串的比對運算

1
where [not] like '比對字串' [escape '逸出字元']

其中 '比對字串' 可以是完整字串，也可以包含萬用字元 % 和 _。

% : 代表任意長度的字元
_ : 代表任意單個字元

<3> 集合的比較運算

1
2
3
4
-- 查詢成績為 85、86 或 88 的記錄
select *
from score
where 分數 in (85, 86, 88)

<4> 邏輯組合運算

1
2
3
4
-- 查詢 09031 班的所有女學生
select *
from student
where 班號 = '09031' and 性別 = '女'

3. 排序查詢

透過使用 ORDER BY 子句可以對查詢結果進行排序。

1
2
3
4
5
6
7
8
-- 升序 asc (預設，可省略)
select 學號, 姓名
from student
order by 學號
-- 降序 desc
select 學號, 姓名
from student
order by 學號 desc

也可以指定多個欄位排序，將會先排序第一個，然後再排序第二個。

1
2
3
4
-- 以課程號升序，分數降序檢索結果
select *
from score
order by 課程號 asc, 分數 desc

ORDER BY 子句必須放在查詢的最後！

4. 聚合函數

聚合函數實現資料統計等功能，用於對一組值進行計算並返回單一的值。

通常與 group by 子句一起使用，常用的聚合函數有：

函數名	描述
AVG	平均值
COUNT	計算指定運算式中選擇的項數 COUNT(*) 統計查詢輸出的列數
MIN	最小值
MAX	最大值
SUM	數值總和
STDWV	計算指定運算式中所有資料的標準差
STDEVP	總體標準差

聚合函數參數的一般格式為：

1
[all|distinct] expr

其中 ALL 表示對所有值進行聚合函數運算，為預設值。而 DISTINCT 指定每個唯一值都被考慮。expr 為運算式。

1
2
3
4
-- 查詢 '09031' 班學生人數
select count(*) as '人數'
from student
where 班號 = '09031'

5. 資料分組

因為聚合函數只會輸出一個結果，查詢某個欄位和聚合函數放在一起時會報錯。

可以透過資料分組計算某一個類別的聚合函數結果，即聚合函數作用範圍變成了每組的所有記錄。

如果想使用聚合函數作為篩選條件，必須放在 HAVING 子句後。

1
2
3
4
5
6
-- 查詢最低分大於 70，最高分小於 90 的學生學號
select 學號
from score
where 分數 is not null
group by 學號
having min(分數) > 70 and max(分數) < 90

當 WHERE 子句、GROUP BY 子句、HAVING 子句和聚合函數同時出現在一個查詢中時，SELECT 命令的執行順序如下：

執行 WHERE 子句，從資料表中選取列
由 GROUP BY 對選取的列進行分組
執行聚合函數
執行 HAVING 子句選取滿足條件的分組

1
2
3
4
-- 查詢每個課程的平均分
select 課程號, avg(分數)
from score
group by 課程號

聚合函數作為條件只能放在 HAVING 子句後，不能放在 WHERE 子句後。
當 SELECT 子句中包含 欄位名稱 與 聚合函數 時，所有的 欄位名稱 都應該被分組。

6. 資料表連接查詢 (Join)

在 FROM 子句中指定要連接的資料表，在 WHERE 子句中給出連接條件。

1
2
3
select <欄位名1>[, ···]
from <資料表1>[, ···]
where 連接條件

對於兩個資料表的公共欄位，需要提供前綴，例如 student.學號。

在 SQL Server 中，連接分為內連接和外連接。

<1> 內連接 INNER JOIN

Θ 連接、等值連接、自然連接。

內連接是最基本的連接方式，使用 INNER JOIN 關鍵字，不使用該關鍵字時預設均為內連接。

等值連接

即兩個資料表之間的連接關係為「等於」。

1
2
3
4
5
6
7
8
9
-- 查詢所有學生的姓名、課程號和分數量
select student.姓名, score.課程號, score.分數
from student, score
where student.學號 = score.學號
-- 即
select student.姓名, score.課程號, score.分數
from student
inner join score
on student.學號 = score.學號

為簡化輸入，可以使用資料表的別名。

1
2
3
4
-- 上一個範例的簡化
select x.姓名, y.課程號, y.分數
from student x, score y
where x.學號 = y.學號

非等值連接

即兩個資料表之間的連接關係不是「等於」。

自連接

將同一個資料表進行連接，這種連接稱之為自連接。

1
2
3
4
5
6
-- 查詢選修了 '3-105' 並且成績高於 '109' 號同學成績的學生記錄
select x.課程號, x.學號, x.分數
from score x, score y
where x.課程號 = '3-105' and x.分數 > y.分數
    and y.學號 = '109' and y.課程號 = '3-105'
order by x.分數 desc

<2> 外連接 OUTER JOIN

左外連接 : LEFT OUTER JOIN 或 LEFT JOIN

右外連接 : RIGHT OUTER JOIN 或 RIGHT JOIN

全外連接 : FULL OUTER JOIN 或 FULL JOIN

1
2
3
4
5
-- 左外連接範例
-- 查詢所有課程的任課教師的姓名
select course.課程名, teacher.姓名
from course left join teacher
on (course.任課教師編號 = teacher.教師編號)

7. 子查詢

<1> 一般子查詢

當一個查詢是另一個查詢的條件時，換言之，當從資料表中選取資料列的條件依賴於該表本身或其他資料表的聯合資訊時，需要使用子查詢來實現。

子查詢最常用於 SELECT 語句中的 WHERE 子句中。

1
2
3
4
5
6
7
8
-- 查詢與學號為 '105' 的同學同年出生的學生
select 學號, 姓名, year(出生日期) as '出生年份'
from student
where year(出生日期) = (
						select year(出生日期)
    					from student
    					where 学号 = '105'
						)

<2> 相互關聯子查詢 (Correlated Subquery)

在一般子查詢中，每個子查詢僅執行一次，返回的值為主查詢的 WHERE 子句所用。

當主查詢在判斷每個待選列時，必須「喚醒」子查詢，然後反饋給主查詢。即內層的條件涉及到外層屬性的子查詢稱為相互關聯子查詢。

1
2
3
4
5
6
7
8
9
-- 查詢成績比該課程平均成績低的學生
select 學號, 課程號, 分數
from score a
where 分數 < (
    select avg(分數)
    from score b
    where a.課程號 = b.課程號 
        and 分數 is not null
    )

<3> 帶 EXISTS 測試的子查詢

EXISTS 代表存在量詞 ∃，只返回邏輯真值或邏輯假值。

1
2
3
4
5
6
7
8
-- 查詢所有任課教師的姓名和系別
select 姓名, 系別
from teacher a
where exists (
    select *
    from course b
    where a.教師編號 = b.任課教師編號
    )

或者還有 NOT EXISTS ，將結果取反。

8. 查詢結果的聯集、交集、差集運算

可以使用以下運算子將兩個 SELECT 查詢語句的結果進行相應運算：

UNION : 聯集 (並)
INSTERSECT : 交集
EXCEPT : 差集

1
2
3
4
5
6
7
8
-- 聯集的範例
-- 查詢所有教師和學生的姓名、性別和出生年份
select 姓名, 性別, year(出生日期) as '出生年份'
from teacher
union
select 姓名, 性別, year(出生日期) as '出生年份'
from student
-- order by 出生年份

(四)、DCL 資料控制語言

資料控制控制的是使用者對資料的存取權限，是由 DBA (資料庫管理員) 決定的。

資料庫的安全性

不安全的因素：

非授權對資料庫的惡意存取和破壞。
資料庫中重要資料的洩露。
安全環境的脆弱性。

安全性控制：

使用者身分鑑別：靜態密碼鑑別、動態密碼鑑別、生物特徵鑑別、智慧卡鑑別。
存取控制。
自主存取控制方法 (使用者可以自定義和分配其他使用者的操作權限)。

自主存取控制

DBMS (資料庫管理系統) 資料控制應具有以下功能：

透過 GRANT 和 REVOKE 將授權通知系統，並存入資料字典。
當使用者提出請求時，根據授權情況檢查是否執行操作請求。

SQL 標準包括 DELETE 、INSERT 、SELECT 和 UPDATE 權限。SELECT 權限對應於 READ 權限，SQL 還包括了 REFERENCES 權限，用來限制使用者在建立關聯時定義外鍵的能力。

1. 授權的語句格式

1
2
3
4
grant <權限>[, ···] 
on <物件類型> <物件名>[, ···]
to <使用者>[, ···]
[with grant option]

不同類型的操作物件有不同的權限，常見的權限如下：

物件	物件類型	操作權限
屬性欄	TABLE	SELECT、INSERT、UPDATE、DELETE、 ALL PRIVILEGES (4 種權限的總和)
檢視表	TABLE	SELECT、INSERT、UPDATE、DELETE、 ALL PRIVILEGES (4 種權限的總和)
資料表	TABLE	SELECT、INSERT、UPDATE、DELETE、ALTER、INDEX ALL PRIVILEGES (6 種權限的總和)
資料庫	DATABASE	CREATETAB 建立資料表的權限，可由 DBA 授予一般使用者

PUBLIC : 接受權限的使用者可以是單個或多個具體的使用者，PUBLIC 參數可將權限賦予全體使用者。
WITH GRANT OPTION : 若指定了此子句，那麼獲得權限的使用者還可以將權限賦予其他使用者。

2. 收回權限語句格式

1
2
3
revoke <權限>[, ···]
on <物件類型> <物件名>[, ···]
from <使用者>[, ···]

資料庫的角色

角色指的是一類人，比如 CEO、總監、一般職員，可以給一類人授權。

角色的建立

1
create role <角色名>

角色授權

1
grant <權限> on <物件類型> <物件名> to <角色1>[, ···]

把角色授權給其他使用者或角色

1
2
grant <角色> to <角色>或<使用者名字>
[with admin option]

with admin option 意味著這個使用者可以把這權限授予其他角色或使用者。

角色權限的收回

1
revoke <權限> on <物件名> from <角色名字>

檢視表 (View) 機制

檢視表是從一個或者多個基本資料表或檢視表中導出的表，是一個虛擬表。

為不同的使用者定義不同的檢視表，把不需要的資料隱藏起來，這樣使用者就不會進行誤操作。

檢視表的建立

1
2
3
create view <檢視表名(欄位列表)>
as select <查詢子句>
[with check option]

子查詢的 SELECT 語句通常不允許含有 ORDER BY 子句和 DISTINCT 語句。
WITH CHECK OPTION 表示對 UPDATE 、INSERT 、DELETE 操作時，保證被操作的列滿足檢視表定義中的謂語條件 (即子查詢中的條件運算式)。
組成檢視表的屬性欄位名稱或者全部省略，或者全部指定。如果省略，則隱含該檢視表由 SELECT 子查詢目標欄位的主屬性組成。

1
2
3
4
5
6
7
8
-- 關聯模式：student(sno, sname, sex, SD, email)
-- 建立 '計算機系 (CS)' 學生的檢視表，並且進行修改操作時
-- 保證檢視表中只有計算機系的孩子會受到影響
create view cs-student
    as select sno, sname, sex
    from student
    where SD = 'CS'
    with check option

檢視表的刪除

1
2
3
4
drop view <檢視表名>

--例如
drop view cs-student

稽核 (Audit)

把對資料庫的所有操作都記錄到稽核記錄 (Audit Log) 中，然後就可以透過記錄審查裡面是否有一些非法的行為。

1
2
3
4
-- 對修改“SC”資料的操作進行稽核
AUDIT UPDATE ON SC;
-- 取消對“SC”表的一切稽核
NOAUDIT UPDATE ON SC;

資料加密

透過一些加密演算法，把明文變成密文，這樣別人就無法查看。

索引 (Index)

利用索引可以使資料庫程式無需對整個資料表進行掃描，就可以在其中找到所需資料。

資料庫中的索引是某個資料表中一欄或者若干欄位值的集合，以及相應指向資料表中實體標識這些值的資料頁的邏輯指標清單。

由內層模式 (Internal Schema) 定義 (內層模式定義所有的內部記錄類型、索引和檔案的組織方式)。

建立索引

1
2
create [unique] [cluster] index <索引名>
    on <資料表名>(欄位名[次序], ···)

次序：可選 ASC (預設) 或 DESC。

UNIQUE ：表明此索引的每一個索引值只對應唯一的資料記錄。

CLUSTER ：表明要建立的索引是叢集索引 (Clustered Index)，意指索引項的順序是與資料表中記錄的實體順序一致的索引組織。

刪除索引

1
drop index <索引名>