SQL Sever 基本命令使用復習

• 前言
• 虛擬機的安裝
• 命令和操作
  • 命令語法基礎
  • 大小寫敏感
    • SQL 關鍵字和函式名
    • 列名和索引名
    • 別名
      • 太長不看？
  • 空格
• 資料庫的連接
  • SSMS 的連接
  • 命令列的連接
• 資料庫操作
  • 創建
  • 洗掉
• 約束
  • 完整性約束
  • 常用約束
    • NOT NULL
    • UNIQUE
    • PRIMARY KEY
    • FOREIGN KEY
    • DEFAULT
    • CHECK
• 表操作
  • 資料型別
  • 創建表
  • 洗掉表
  • 修改表中的列
• 查詢
  • 基礎語法
    • AS
    • DISTINCT
    • IS NULL
    • GROUP BY
      • HAVING
    • ORDERED BY
  • 常用函式
  • WHERE
    • 運算子概覽
    • != 和 <>
    • BETWEEN ... AND ...
    • IN
    • LIKE
      • 通配符
      • 匹配模式
  • 連接
    • INNER JOIN 內連接
    • LEFT JOIN 左連接
    • RIGHT JOIN 右連接
    • FULL OUTER JOIN 全連接
      • 小總結
• 子查詢
  • 普通子查詢
    • ANY
    • ALL
    • IN
  • 相關子查詢
• 其他型別的查詢
  • UNION 集合運算查詢
    • UNION
    • UNION ALL
    • 小總結
  • SELECT INTO 結果入表
• 資料操縱
  • INSERT INTO 添加資料
  • UPDATE 修改資料
  • DELETE 洗掉資料
• 視圖
  • 視圖的意義
  • CREATE VIEW 創建視圖
  • ALTER VIEW 修改視圖
  • DROP VIEW 洗掉視圖
  • 查詢視圖
  • 操縱/更新視圖
• 索引
  • 索引的分類
    • 聚集索引
    • 非聚集索引
      • 聚集和非聚集的區別
    • 唯一索引
    • 視圖索引
    • 全文索引
    • XML 索引
  • 索引的操作
    • 創建索引
    • 修改索引
    • 洗掉索引
• 大總結
  • 你能用 ALTER 干啥？
  • 其他
• 后記
• 參考

前言

因為資料庫原理的專業老師要在五一假期后進行考試，所以嘗試寫一個筆記，便于復習和分享，

操作環境：Hyper-v 虛擬機 運行 Windows Sever 2012 R2 + SQL Sever Express 2012 SP2
使用教材：資料庫原理及應用教程 第4版 微課版 主編 陳志泊

虛擬機的安裝

如果你是純理論派的話，不裝也不是不行，可以跳到下一節，

Express 是 SQL Sever 的精簡版，可以免費使用，

SQL Server Express 2005（以下簡稱 SQLExpress） 是由微軟公司開發的 SQL Server 2005（以下簡稱 SQL2005）的縮減版，這個版本是免費的，它繼承了 SQL Server 2005 的多數功能與特性，如：安全性設定、自定義函式和程序、Transact-SQL、SQL、CLR 等，還免費提供了和它配套的管理軟體 SQL Server Management Studio Express，

SQLExpress 有幾個主要的限制：

1. 僅允許本地連接，
2. 資料庫檔案的最大尺寸為4GB，此限制只對資料檔案（后綴名為 mdf），日志檔案（后綴名為 ldf）不受此限，
3. 只使用一個CPU來運算，不能充分利用多CPU服務器的性能，
4. 可使用的記憶體量最高只有1GB，
5. 沒有 SQL Agent，若要做排程服務必須自己撰寫，

因此它是 SQL Server 產品系列中面對低端的產品，是面對桌面型應用，或者小型的內部網路應用的，

筆者在此選擇安裝 Microsoft? SQL Server? 2012 Service Pack 2 (SP2) Express 64位 with Tools （安裝包名：SQLEXPRWT_x64_CHS.exe）的版本，Express即使全部安裝也不會大到哪里去，不像原版的 SQL Sever 有4GB的安裝包大小……作為練習，足夠用了，點擊上面的文字就可以跳轉到下載鏈接，

筆者遇到的困難只有，Windows Sever 2012 R2 不自帶 .NET 3.5 環境，所以需要在“添加角色和功能”界面自行添加，之后才能順利安裝 SQL Sever Express，
盡管考試內容只有命令，但我仍然推薦不取消安裝GUI（用戶互動界面，也就是SQL Server Management Studio，簡稱 SSMS），以防止在輸入錯誤命令的時候無法糾錯或重建資料庫，

安裝教程很多，請自行搜索，不再贅述，

命令和操作

命令語法基礎

實在看不懂的話，或者你自己覺得這沒什么必要，那隨便看看就行，到時候不知道再回來看嘛，

在描述命令列引數的時候，對其格式有些約定俗成的寫法，各系統之間也有差別，一般采用的格式如下：

命令 <必選引數1|必選引數2> [-option {必選引數1|必選引數2|必選引數3}] [可選引數…] {(默認引數)|引數|引數}

命令格式中常用的幾個符號含義如下：

• 尖括號 < >：必選引數，實際使用時應將其替換為所需要的引數，
• 大括號 { }：必選引數，內部使用，包含此處允許使用的引數，
• 方括號 [ ]：可選引數，在命令中根據需要加以取舍，
• 小括號 ( )：指明引數的默認值，只用于 { } 中，
• 豎線 |：用于分隔多個互斥引數，含義為“或”，使用時只能選擇一個，
• 省略號 …：任意多個引數，

大括號和尖括號的區別為：大括號中只能選擇所列舉的必選引數（或之一），尖括號中卻需要根據實際替換必選引數

示例一：

• 命令語法：git help <name>
• 實際使用：git help config 或 git help branch 或 ... （name被替換為實際的內容）

示例二：

• 命令語法：git stash {apply | pop}
• 實際使用：git stash apply 或 git stash pop （只能在必選引數apply或pop中選一個）

大小寫敏感

這一段主要是讓你寫命令寫的更隨意一點用的，

SQL 關鍵字和函式名

SQL 的關鍵字和函式名都不區分大小寫，例如，下面這些陳述句都是等價的：

SELECT NOW();
select now();
sElEcT nOw();

列名和索引名

在 MySQL 里，列名和索引名都不區分大小寫，例如，下面這些陳述句都是等價的：

SELECT name FROM student;
SELECT NAME FROM student;
SELECT nAmE FROM student;

別名

默認情況下，表的別名要區分大小寫，SQL 陳述句中可以使用任意的大小寫（大寫、小寫或大小寫混用）來指定一個別名，如果需要在同一條陳述句里多次用到同一個別名，則必須讓它們的大小寫保持一致，

Linux下區分大小寫的情況：

在Windows下，都不區分大小寫，

太長不看？

各種東西的名字建議區分大小寫（資料庫名，列名，表名等）；SQL 關鍵字和函式不用區分（create、select、from等），在 Windows 下，則完全看你心情，

空格

SQL 陳述句忽略多余的空格，因此，你有足夠的自由空間去整理你的代碼結構和風格，

資料庫的連接

SSMS 的連接

使用GUI（也就是SSMS），在啟動時可能遇到服務器名稱為空，無法鏈接的問題，這個時候，可以點擊下拉框下的“查看更多”嘗試查找，或者運行 services.msc，查找所有顯示名稱如 SQL Server (資料庫名稱) 的服務，

比如，在我的機器上，有一個顯示名稱為 SQL Server (SQLEXPRESS)的服務，實際服務名稱為 MSSQL$SQLEXPRESS，那么這個SQL服務器實體的名稱就為 SQLEXPRESS，可以在服務器名稱的框內輸入 ./SQLEXPRESS來登錄，
之后你理應在物件資源管理器中看到你剛剛登錄的資料庫，
點擊“新建查詢”，即可進行在 SSMS 下的命令輸入、編輯、除錯和運行，

命令列的連接

在 cmd 或 powershell 中輸入 sqlcmd -S localhost -U sa -P 123456 來進行命令列的連接，
以下為 sqlcmd 工具的命令語法，僅供參考即可，

PS C:\Users\Administrator> sqlcmd -?
Microsoft (R) SQL Server 命令列工具
版本 11.0.2100.60 NT x64
著作權所有 (c) 2012 Microsoft，保留所有權利，

用法: Sqlcmd            [-U 登錄 ID]          [-P 密碼]
[-S 服務器]            [-H 主機名]          [-E 可信連接]
[-N 加密連接][-C 信任服務器證書]
[-d 使用資料庫名稱] [-l 登錄超時值]     [-t 查詢超時值]
[-h 標題]           [-s 列分隔符]      [-w 螢屏寬度]
[-a 資料包大小]        [-e 回顯輸入]        [-I 允許帶引號的識別符號]
[-c 命令結束]            [-L[c] 列出服務器[清除輸出]]
[-q "命令列查詢"]   [-Q "命令列查詢" 并退出]
[-m 錯誤級別]        [-V 嚴重級別]     [-W 洗掉尾隨空格]
[-u unicode 輸出]    [-r[0|1] 發送到 stderr 的訊息]
[-i 輸入檔案]         [-o 輸出檔案]        [-z 新密碼]
[-f <代碼頁> | i:<代碼頁>[,o:<代碼頁>]] [-Z 新建密碼并退出]
[-k[1|2] 洗掉[替換]控制字符]
[-y 可變長度型別顯示寬度]
[-Y 固定長度型別顯示寬度]
[-p[1] 列印統計資訊[冒號格式]]
[-R 使用客戶端區域設定]
[-K 應用程式意向]
[-M 多子網故障轉移]
[-b 出錯時中止批處理]
[-v 變數 = "值"...]  [-A 專用管理連接]
[-X[1] 禁用命令、啟動腳本、環境變數[并退出]]
[-x 禁用變數替換]
[-? 顯示語法摘要]

可知，-S 引數用于指定連接 localhost 本地服務器，使用 sa 用戶登錄，密碼為 123456，這是一個內置的管理員賬戶，
若命令列視窗左邊出現 1> 字樣，則代表連接成功，可以開始輸入指令，注意，用分號結束陳述句的慣例并沒有效果，需要用 go 命令進行執行，

僅當 命令終止符 GO（默認）、RESET、ED、!!、EXIT、QUIT 和 CTRL+C 出現在一行的開始（緊跟提示符）時才可以被識別，sqlcmd 忽視同一行中這些關鍵字后輸入的任何內容，

之后我將假設我們在命令列互動的方式下進行操作，若直接在互動視窗下編輯復雜命令顯得有些困難，可以在其他地方編輯好后直接復制過來，或選擇上面的在 SSMS 中除錯指令，

資料庫操作

創建

使用 CREATE DATABASE 陳述句創建資料庫，
語法：

CREATE DATABASE <資料庫名稱>;

在命令列互動視窗中，你輸入的應該是這樣的：

1> create database my_db;
2> go

然后命令列視窗重新顯示 1> ，操作成功完成，
其他的引數可能不那么重要，詳情可參考教材 P77，

隨后，輸入 use 資料庫名 進入資料庫操作，

洗掉

使用 DROP DATABASE 陳述句，

DROP DATABASE <資料庫名稱>;

不說你也應該知道——謹慎使用，

約束

約束的前置知識是資料表的創建，如果對于表創建的規則不熟悉，建議先跳到創建表章節，然后再回來繼續，

SQL約束用于指定表中資料的規則，
約束有列級和表級之分，列級約束作用于單一的列，而表級約束作用于整張資料表，

完整性約束

完整性約束用于保證關系型資料庫中資料的精確性和一致性，對于關系型資料庫來說，資料完整性由參照完整性（referential integrity，RI）來保證，
有很多種約束可以起到參照完整性的作用，這些約束包括主鍵約束（Primary Key）、外鍵約束（Foreign Key）、唯一性約束（Unique Constraint）以及下面提到的其他約束，

不用太糾結于這個概念，

常用約束

下面是 SQL 中常用的約束：

如果想要指定約束名稱，可以在原先列定義約束的位置加上 CONSTRAINT <約束名>，替換掉原來的約束名稱，這是可選的，加上約束名的好處之一是，以后對這個約束進行修改或洗掉可能會更加方便，

NOT NULL

在默認的情況下，表的列接受 NULL 值，NOT NULL 約束強制列不接受 NULL 值，該約束使欄位始終包含值，這意味著，如果不向欄位添加值，就無法插入新記錄或者更新記錄，

UNIQUE

UNIQUE 約束唯一標識資料庫表中的每條記錄，UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證，PRIMARY KEY 約束擁有自動定義的 UNIQUE 約束，

請注意，每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束，

PRIMARY KEY

PRIMARY KEY 約束唯一標識資料庫表中的每條記錄，主鍵必須包含唯一的非NULL值，

每個表都應該有一個主鍵，并且每個表只能有一個主鍵，

FOREIGN KEY

一個表中的 FOREIGN KEY 指向另一個表中的 PRIMARY KEY，

讓我們通過一個實體來解釋外鍵，請看下面兩個表：

"Persons" 表：

"Orders" 表：

"Orders" 表中的 "P_Id" 列指向 "Persons" 表中的 "P_Id" 列，
"Persons" 表中的 "P_Id" 列是 "Persons" 表中的 PRIMARY KEY，
"Orders" 表中的 "P_Id" 列是 "Orders" 表中的 FOREIGN KEY，

FOREIGN KEY 約束用于預防破壞表之間連接的行為，FOREIGN KEY 約束也能防止非法資料插入外鍵列，因為它必須是它指向的那個表中的值之一，

DEFAULT

DEFAULT 約束用于向列中插入默認值，如果沒有規定其他的值，那么會將默認值添加到所有的新記錄，在定義時，在 default 后寫入默認值即可，

CHECK

CHECK 約束用于限制列中的值的范圍，可以提高程式的 魯棒性，

如果對單個列定義 CHECK 約束，那么該列只允許特定的值，
如果對一個表定義 CHECK 約束，那么此約束會基于行中其他列的值在特定的列中對值進行限制，

例子：

• 列約束：

CREATE TABLE Persons
(
    P_Id int NOT NULL CHECK (P_Id>0),
    Name varchar(255) NOT NULL,
    City varchar(255)
)

• 表約束

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    Name varchar(255) NOT NULL,
    City varchar(255),
    CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

表操作

資料型別

最常用的資料型別：

文本類（char和text），在型別名前面加n，就是相應的儲存Unicode字符的資料型別，
無需全部記住，只需認識即可，點此可參考全部資料型別，

創建表

使用 CREATE TABLE 陳述句創建表，
語法：

CREATE TABLE 表名稱
(
    <列名1> <資料型別> [DEFAULT] [{列約束}],
    <列名2> <資料型別> [DEFAULT] [{列約束}],
    ...,
    [表約束]
);

例子：

CREATE TABLE courses (
    course_id   INT         AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL,
    start_date  DATE,
    PRIMARY KEY (employee_id , course_id)
);

關于約束的更多介紹，可以向上到約束章節回看，
表約束和列約束不同，即使是在設定主鍵上，實際意義也略有不同，不過不深究的話，兩者有時功能相似，如何實作全看現實情況和你的需求，不用太過糾結，

洗掉表

使用 DROP TABLE 陳述句，語法：

DROP TABLE 表名

修改表中的列

修改表中的列，使用 ALTER TABLE 陳述句，

若要向表中添加列，請使用以下語法：

ALTER TABLE table_name         
ADD column_name datatype

若要洗掉表中的列，請使用以下語法：

ALTER TABLE table_name
DROP COLUMN column_name

若要更改表中列的資料型別，請使用以下語法：

ALTER TABLE table_name
ALTER COLUMN column_name datatype

SQL 語言作為一種 DDL 語言，可見命令的語法結構還是非常直接的，
大體流程如下：

1. 首先確定要修改的表，ALTER TABLE 要更改的資料表名
2. 然后選擇以下三個可能的列操作：
   • 若是要增加列，則使用 ADD 列名 資料型別 關鍵字（可以以逗號分隔輸入多行）
   • 若是要洗掉列，則使用 DROP COLUMN 要洗掉的資料表名 關鍵字
   • 若是更改表，則輸入 ALTER 要更改的列名 新名字 新型別 命令，

查詢

假設有此表：

這是公司職員的兩張表，上面的是詳細資訊，下面的是發放工資的記錄表，

這兩張資料表將會作為以下語法和連接的示例，

基礎語法

SELECT <列名>
FROM <表名>
[WHERE <查詢條件>]

可以用 * 代表全部列，

AS

使用AS給查詢結果取別名(也可省略as)：

SELECT
u_name as "myName",
u_age as "myAge"
FROM staff;

注：如果需要小寫字母或別名含有空格則需要加上單引號，否則會被決議成大寫字母，

結果：

1> SELECT
2> u_name as 'myName',
3> u_age as 'myAge'
4> FROM staff;
5> go
myName myAge
------ -----------
王一              18
潤二              19
張三              21
李四              21
趙五              22
雷六六             19
期肆一             20

(7 行受影響)

DISTINCT

只輸出有區別的元素，例：

SELECT DISTINCT u_age
FROM staff;

IS NULL

IS NULL 是在 Where 分句中判空的唯一方法，不能使用如 XX=NULL 形式的查找條件，

GROUP BY

現在，我們希望查找每個職員的被發放的總金額（總工資），可以使用 GROUP BY 陳述句對職員ID進行組合：

SELECT u_id, SUM(amount) FROM salary
GROUP BY u_id;

結果：

1> SELECT u_id, SUM(amount) FROM salary
2> GROUP BY u_id;
3> go
u_id
----------- -----------
          1        7100
          2        7300
          3        1700
          4        6400
          5       11000
          9        2900
         11        6300

(7 行受影響)

可以看到，工資被按組排序出來加和輸出了，順帶一提，如果列沒有被命名，那么查詢出來的結果中，列的名字將會是空白的，
但若是沒有 GROUP BY 呢？

1> SELECT u_id, SUM(amount) FROM salary;
2> go
訊息 8120，級別 16，狀態 1，服務器 SQLEXPRESS，第 1 行
選擇串列中的列 'salary.u_id' 無效，因為該列沒有包含在聚合函式或 GROUP BY 子句中，

嗯，報錯了，如果沒有報錯的話，輸出的結果應該會是類似于這樣的：

這肯定不是我們所想要的結果，

HAVING

?HAVING? 子句使你能夠指定過濾條件，從而控制查詢結果中哪些組可以出現在最終結果里面，
?WHERE ?子句對被選擇的列施加條件，而 ?HAVING ?子句則對 ?GROUP BY? 子句所產生的組施加條件，

書里沒有 HAVING 子句的出現，在此不做詳細介紹，

ORDERED BY

ORDER BY 陳述句用于對結果集進行排序，可以在后面指定 ASC 升序或 DESC 降序，默認升序，例：

SELECT * FROM staff ORDER BY u_age ASC

常用函式

用在 SELECT 后面，可以搭配 AS 使用，例：

SELECT * FROM staff
WHERE u_age = (
    SELECT MAX(u_age) FROM staff
);

使用 MAX 函式和嵌套查詢來獲取年齡最大的那條記錄，

WHERE

運算子概覽

下面的運算子可在 WHERE 子句中使用：

!= 和 <>

在某些版本的 SQL SEVER 中，有 =! 運算子，與 <> 相同，都表示兩個值不相等，

BETWEEN ... AND ...

運算子 BETWEEN ... AND 會選取介于兩個值之間的資料范圍，這些值可以是數值、文本或者日期，

SELECT * FROM staff
WHERE u_name
BETWEEN '潤二' AND '趙五';

結果：

1> SELECT * FROM staff
2> WHERE u_name
3> BETWEEN '潤二' AND '趙五';
4> go
u_id        u_name u_age       u_sex
----------- ------ ----------- -----
          1 王一             18   男
          2 潤二             19   女
          3 張三             21   女
          5 趙五             22   女

(4 行受影響)

可見，當運算子被輸入文本時，按照音序來排列，并回傳相應結果，

注意：在某些 DBMS 資料庫管理系統中，這個運算子不包含等號或只包含起始的等號，在 SQL Sever 中，這個運算子包含兩端的等號，

IN

語法：

IN (value1,value2,...)

例子：

1> SELECT u_name, u_age FROM staff
2> WHERE u_age IN (18, 21);
3> go
u_name u_age
------ -----------
王一              18
張三              21
李四              21

(3 行受影響)

LIKE

LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式，

通配符

在搜索資料庫中的資料時，SQL 通配符可以替代一個或多個字符，
SQL 通配符必須與 LIKE 運算子一起使用，

在 SQL 中，可使用以下通配符：

匹配模式

易得，在 u_name 欄位中查詢，
如 %一 的匹配模式，能夠查找到表中的 “王一” 和 “期肆一” 條目，
如 _一 的匹配模式，能夠查找到表中的 “王一” 條目，

連接

我們經常使用4種連接型別：

• (INNER) JOIN： 如果表中有至少一個匹配，則回傳行
• LEFT JOIN： 即使右表中沒有匹配，也從左表回傳所有的行
• RIGHT JOIN： 即使左表中沒有匹配，也從右表回傳所有的行
• FULL JOIN： 只要其中一個表中存在匹配，就回傳行

我們將繼續使用上面提到的兩個資料表作為例子，

INNER JOIN 內連接

INNER JOIN 關鍵字在表中存在至少一個匹配時回傳行，

這是最常見的連接方式，舉例：

SELECT u.u_id, u.u_name, s.amount 
FROM staff as u
INNER JOIN salary as s
ON u.u_id = s.u_id
ORDER BY u.u_id;

注釋：u 即 user，s 即 salary，INNER JOIN 和 JOIN 關鍵字是相同的，
as 關鍵字指定別名，ON 子句指定連接條件，最后結果使用 ORDER BY 排序，

結果：

1> SELECT u.u_id, u.u_name, s.amount
2> FROM staff as u
3> INNER JOIN salary as s
4> ON u.u_id = s.u_id
5> ORDER BY u.u_id;
6> go
u_id        u_name amount
----------- ------ -----------
          1 王一          7100
          2 潤二          7300
          3 張三          1700
          4 李四          6400
          5 趙五          5000
          5 趙五          6000

(6 行受影響)

注釋：INNER JOIN 關鍵字在表中存在至少一個匹配時回傳行，如果 "staff" 表中的行在 "salary" 中沒有匹配，則不會列出這些行，

有時，我們可以省略 INNER JOIN 連接符，下面的陳述句和上面是等效的，

SELECT u.u_id, u.u_name, s.amount 
FROM staff as u, salary as s
WHERE u.u_id = s.u_id
ORDER BY u.u_id;

通過在 FROM 子句中指定多個資料表，SQL 會將他們通過輸入的條件（在這里是 WHERE）自動進行內連接，省略連接符后，就不能使用 ON 子句了，

LEFT JOIN 左連接

注釋：在某些資料庫中，LEFT JOIN 稱為 LEFT OUTER JOIN，

LEFT JOIN 關鍵字從左表（table1）回傳所有的行，即使右表（table2）中沒有匹配，如果右表中沒有匹配，則結果為 NULL，

以下實體中我們把 salary 作為左表，staff 作為右表，回傳所有員工的工資和姓名，

SELECT s.u_id, s.amount, u.u_name
FROM salary as s
LEFT JOIN staff as u
ON u.u_id = s.u_id
ORDER BY s.u_id;

結果：

1> SELECT s.u_id, s.amount, u.u_name
2> FROM salary as s
3> LEFT JOIN staff as u
4> ON u.u_id = s.u_id
5> ORDER BY s.u_id;
6> go
u_id        amount      u_name
----------- ----------- ------
          1        7100 王一
          2        7300 潤二
          3        1700 張三
          4        6400 李四
          5        5000 趙五
          5        6000 趙五
          9        2900 NULL
         11        6300 NULL

(8 行受影響)

可見，左連接匹配，左表中即使有右表沒有的資料，也會回傳左表的記錄，右表回傳空，

RIGHT JOIN 右連接

RIGHT JOIN 關鍵字從右表（table2）回傳所有的行，即使左表（table1）中沒有匹配，如果左表中沒有匹配，則結果為 NULL，
右鏈接是左連接查詢的反向操作，

以下實體中我們把 salary 作為左表，staff 作為右表，回傳所有員工的工資和姓名，

SELECT s.u_id, s.amount, u.u_name
FROM salary as s
RIGHT JOIN staff as u
ON u.u_id = s.u_id
ORDER BY s.u_id;

結果：

1> SELECT s.u_id, s.amount, u.u_name
2> FROM salary as s
3> RIGHT JOIN staff as u
4> ON u.u_id = s.u_id
5> ORDER BY s.u_id;
6> go
u_id        amount      u_name
----------- ----------- ------
       NULL        NULL 雷六六
       NULL        NULL 期肆一
          1        7100 王一
          2        7300 潤二
          3        1700 張三
          4        6400 李四
          5        5000 趙五
          5        6000 趙五

(8 行受影響)

FULL OUTER JOIN 全連接

我似乎沒有看到可以省略 OUTER，如果懶得記的話，就把除了 INNER JOIN 的連接符中間全寫上 OUTER 得了，

同樣的例子：

SELECT s.u_id, s.amount, u.u_name
FROM salary as s
FULL OUTER JOIN staff as u
ON u.u_id = s.u_id
ORDER BY s.u_id;

結果：

1> SELECT s.u_id, s.amount, u.u_name
2> FROM salary as s
3> FULL OUTER JOIN staff as u
4> ON u.u_id = s.u_id
5> ORDER BY s.u_id;
6> go
u_id        amount      u_name
----------- ----------- ------
       NULL        NULL 雷六六
       NULL        NULL 期肆一
          1        7100 王一
          2        7300 潤二
          3        1700 張三
          4        6400 李四
          5        5000 趙五
          5        6000 趙五
          9        2900 NULL
         11        6300 NULL

(10 行受影響)

一目了然，

小總結

內連接 INNER JOIN 更偏向于自然連接，能連接的地方就連接，不能連接的就丟掉，
可見，LEFT JOIN 和 RIGHT JOIN 僅僅是連接符換了一下而已，從代碼上看，剩下的什么都沒變，FROM 后是左表，XX JOIN 后面是右表，左右表連接切換的只是哪個表的資料要被完全顯示，哪個表的資料如果沒有就填空，
左表連接就完全顯示左表，右表連接就完全顯示右表，
FULL OUTER JOIN 相當于一個 左連接+右連接，能夠把左表和右表的資料全部顯示出來，

子查詢

就是嵌套查詢，上面已經稍微的演示過嵌套查詢的實體了，
有遞回思想的話，會很快熟悉這一節的內容，因此介紹會簡略一些，

普通子查詢

不介紹回傳一個值的嵌套子查詢，直接開始說明回傳一組資料的子查詢，

ANY

ANY 意為一組資料中的任意一個，

查詢單詞收入在3000以上的人的姓名，例子：

SELECT DISTINCT u_name
FROM staff
WHERE u_id = ANY (
    SELECT u_id
    FROM salary
    WHERE amount > 3000
);

結果：

1> SELECT DISTINCT u_name
2> FROM staff
3> WHERE u_id = ANY (
4>     SELECT u_id
5>     FROM salary
6>     WHERE amount > 3000
7> );
8> go
u_name
------
李四
潤二
王一
趙五

(4 行受影響)

ANY 的運算表如下：

ALL

ALL 和 ANY 是同一類的運算子，

大概理解一下，按照實際情況使用即可，ANY 以最低標準判斷，ALL 以最高標準判斷，

IN

IN 和 = ANY 等價，
NOT IN 和 <> ALL 等價，

相關子查詢

子查詢中參考父查詢資訊的查詢就叫相關子查詢，不用太糾結于這個概念，用的時候把表的名字標清楚就行，

其他型別的查詢

UNION 集合運算查詢

UNION

UNION 運算子用于合并兩個或多個 SELECT 陳述句的結果集，

請注意，UNION 內部的 SELECT 陳述句必須擁有相同數量的列，列也必須擁有相似的資料型別，同時，每條 SELECT 陳述句中的列的順序必須相同，

為了演示，我們再新建一個 staff2 表，這是第二個部門的職員名單，

列出兩個部門所有不同的雇員名：

SELECT u_name FROM staff
UNION
SELECT u_name FROM staff2;

結果：

1> SELECT u_name FROM staff
2> UNION
3> SELECT u_name FROM staff2;
4> GO
u_name
------
陳冠希
雷六六
李四
劉五四
期肆一
潤二
王一
張建國
張三
趙五

(10 行受影響)

注釋：這個命令無法列出所有雇員，在上面的例子中，我們有兩個名字相同的雇員，他們當中只有一個人被列出來了，UNION 命令只會選取不同的值，

UNION ALL

UNION ALL 命令和 UNION 命令幾乎是等效的，不過 UNION ALL 命令會列出所有的值，

同樣是上面的例子，只不過這次我們使用 UNION ALL：

SELECT u_name FROM staff
UNION ALL
SELECT u_name FROM staff2

結果：

1> SELECT u_name FROM staff
2> UNION ALL
3> SELECT u_name FROM staff2
4> GO
u_name
------
王一
潤二
張三
李四
趙五
雷六六
期肆一
劉五四
張建國
陳冠希
張三

(11 行受影響)

小總結

區分 JOIN 連接 和 UNION 集合，想象 JOIN 是以某種方式把兩張表橫向的連接在一起然后進行操作，而 UNION 是簡單的把兩個表水平方向上的“接上”，是一個簡單的相加/并集操作，

SELECT INTO 結果入表

SELECT INTO 陳述句和普通的 SELECT 陳述句無異，只不過多加了一個 INTO 子句，命令的回傳結果也不是顯示在互動視窗中，而是新建一個表來儲存查詢回傳的資料，

按照這個特性，我們可以用這個命令來復制/備份表，例子：

SELECT *
INTO staff_backup
FROM staff

同樣，能夠在 SELECT 陳述句中使用的子句也同樣可以使用，如 WHERE、JOIN 等，在此不過多贅述，

資料操縱

INSERT INTO 添加資料

INSERT INTO 陳述句用于向資料表中插入新的行，

語法：

INSERT INTO 表名稱 VALUES (值1, 值2,....)

若要指定想要插入資料的列：

INSERT INTO 表名稱 (列1, 列2,...) VALUES (值1, 值2,....)

這使得我們可以向特定的列中插入資料，不必提供所有列的資料，
但若不指定列，那么 VALUES 應該與表中的欄位一一對應，不應缺少資料項或輸入型別與相應欄位不匹配，記得把字符型的資料加上單引號哦，

例子：

INSERT INTO staff (u_id, u_name) VALUES (8, '臨時工');

如上，staff 表中將會新建一個只有 u_id 和 u_name 欄位不為空的記錄，

UPDATE 修改資料

Update 陳述句用于修改表中的資料，

語法：

UPDATE 表名稱 SET 列名稱 = 新值 [, 列名稱2 = 新值, ...]
WHERE 列名稱 = 某值

有點抽象對吧？看例子，如果我們發現潤二同學是個女裝大佬，他的性別不是女，而是男的話，我們需要更新該人在資料表中的條目：

UPDATE staff SET u_sex = '男' WHERE u_name = '潤二'

首先輸入要更新的記錄所在的表名，然后輸入記錄所在的列名，然后使用 WHERE 子句查找到該記錄，并成功把該值修改，

DELETE 洗掉資料

DELETE 陳述句用于洗掉表中的行，

語法：

DELETE FROM 表名稱 WHERE 列名稱 = 值

用 FROM 確定表， WHERE 確定要洗掉的記錄，

潤二同學被發現是女裝大佬以后，竟然開始被公司里年齡最大的趙五歧視，欺負，怎么能有如此不和諧的事情出現呢！為了維護世界的愛與和平，趙五被公司辭退了，為此，我們需要洗掉趙五的記錄，如下：

DELETE FROM staff WHERE u_name = '趙五';

以后，如果有更多的人歧視潤二同學，你也可以更改 WHERE 的條件，使其匹配更多記錄，一并洗掉，

你真棒！作為 DBA，你又一次維護了宇宙中一塊小小地方的愛與和平！請繼續努力吧！

視圖

在 SQL 中，視圖是基于 SQL 陳述句的結果集的可視化的表，

視圖包含行和列，就像一個真實的表，視圖中的欄位就是來自一個或多個資料庫中的真實的表中的欄位，我們可以向視圖添加 SQL 函式、WHERE 以及 JOIN 陳述句，我們也可以提交資料，就像這些來自于某個單一的表，

注釋：資料庫的設計和結構不會受到視圖中的函式、where 或 join 陳述句的影響，

個人理解，視圖就是一張完全以其他真實表的一部分作為元素所組成的虛擬表，

視圖的意義

• 簡化了操作，把經常使用的資料定義為視圖，可以將復雜的SQL查詢陳述句進行封裝，

  如在實際作業中，不同的人員只關注與其相關的資料，而與他無關的資料，對他沒有任何意義，根據這一情況，可以專門為其創建一個視圖，定制用戶資料，聚焦特定的資料，此后當他查詢資料時，只需 select * from view_name; 就可以了，

• 安全性，用戶只能查詢和修改能看到的資料，

  視圖是虛擬的，物理上是不存在的，只是存盤了資料的集合，我們可以不通過視圖將基表中重要的欄位資訊給用戶，可以保證資料的安全性，方便了權限管理，讓用戶對視圖有權限而不是對底層表有權限進一步加強了安全性，

• 邏輯上的獨立性，屏蔽了真實表的結構帶來的影響，

  視圖的存在: 主要是為了對外提供資料支持(外部系統)；隱藏了基表欄位(隱私)；保證了資料庫的資料安全(保護資料庫內部的資料結構)；可以靈活的控制對外的資料: 保證針對每個介面都有一個單獨的資料支持，增強了用戶友好性，

CREATE VIEW 創建視圖

基礎語法：

CREATE VIEW 視圖名(列名1, 列名2, ...) AS
SELECT 列1, 列2.....
FROM 表名
WHERE 條件;

要創建視圖，你要先選擇你要從哪個表里抽調出哪個列哪些記錄作為視圖的組成元素，其中，視圖的列名是可選的，本質上是在 AS 后面構建了一個查詢陳述句，你依舊可以自由的像是使用正常的 SELECT 陳述句那樣使用這個命令，而且不要忘記那個 AS 哦，

完整的視圖創建命令還有一些諸如 WITH CHECK OPTION 之類的引數，不過我們先不研究這么深入，了解他的基本使用即可，

你看，一個簡單的抽調出所有 staff 名字和年齡的視圖只需要這么寫：

CREATE VIEW staff_all(name, age) AS
(
    SELECT u_name, u_age FROM staff
    UNION
    SELECT u_name, u_age FROM staff2
)

新建的視圖有兩列，一列名為 name，一列名為 age，而其他的資料——這個視圖的被提供者被設計成并不需要擔心，也不應該操縱其他的資料項，

ALTER VIEW 修改視圖

語法：

ALTER VIEW <視圖名>[(視圖串列)] AS
子查詢

書上沒寫太多，不過這基本已經是把一個視圖全部重新構建的等級了，可以考慮把 ALTER VIEW 命令看成把一個已經存在的視圖替換成一個新視圖的程序，之后的命令用于定義那個新視圖，

DROP VIEW 洗掉視圖

你懂的，

DROP VIEW <視圖名>

查詢視圖

把視圖當成一個普通的表一樣寫在 FROM 后面，本質上，視圖是一系列查詢的結果，而對視圖的查詢是一個系統借由視圖自動生成的嵌套查詢，

操縱/更新視圖

使用同樣的 INSERT、UPDATE、DELETE 陳述句，把視圖當成表一樣進行操作，

在實際操作中，需要注意的是，由于視圖可能是多個表以某種規則聯系在一起的，所以有一些隊視圖的操作并不能成功的進行——因為他們的原表有規則限制，不允許單獨添加某個資料項，

索引

索引是一種特殊的查詢表，可以被資料庫搜索引擎用來加速資料的檢索，一般索引使用 B+樹、Hash索引等方式建立資料結構，但在這里，我們只需要知道有一個索引表的概念即可，

索引的分類

以下都是概念的介紹，只考慮 SQL 陳述句的話，下面的略讀、大概理解或者直接跳過即可，視圖索引、全文索引、XML索引 基本不會考到，

聚集索引

Clustered Index 聚集索引，在聚集索引中，表中各行的物理順序與鍵值的邏輯（索引）順序相同，

非聚集索引

Non-clustered Index 非聚集索引，如果不是聚集索引，表中各行的物理順序與鍵值的邏輯順序不匹配，聚集索引比非聚集索引有更快的資料訪問速度，

聚集和非聚集的區別

對于兩者概念分辨不清的，可以考慮看下面的參考：

唯一索引

唯一索引不允許兩行具有相同的索引值，

如果現有資料中存在重復的鍵值，則大多數資料庫都不允許將新創建的唯一索引與表一起保存，當新資料將使表中的鍵值重復時，資料庫也拒絕接受此資料，

視圖索引

顧名思義，對視圖的索引，視圖作為參考實表的虛表，每次都動態運算，開銷很大，因此，對于視圖建立索引減少性能和時間開銷是必要的，

然而，若頻繁更新基本表資料，則反復自動重建視圖索引的開銷可能大于視圖索引帶來的性能收益，請斟酌使用，

全文索引

全文索引可以提高資料的搜索速度，在 SQL Sever 2012 中，全文索引被允許創建在每個表最多一個，且只對于文本、二進制、XML 資料型別的列的條件下，

XML 索引

顧名思義，對 XML 進行索引，

索引的操作

創建索引

資料和教材上似乎有些出入，這里選用教材上的主要語法：

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX 索引名
ON 資料表名 (列名, ...);

若指定了 (NON) CLUSTERED，那么將創建一個（非）聚集索引，若指定 UNIQUE，則將創建一個唯一索引，

例子：

CREATE UNIQUE CLUSTERED INDEX index_staff
ON staff(u_id, u_name);

這個命令創建了一個唯一聚集索引，為 u_id 和 u_name 兩列的復合索引，

修改索引

索引一般由 DBMS 自動維護，沒有特殊目的的話，我真的不知道我干嘛要改它，

ALTER INDEX <索引名 或 ALL>
ON 表名
{ REBUILD | DISABLE | REORGANIZE | SET }

1. REBUILD：重新生成索引，
2. DISABLE：禁用索引，
3. SET：設定索引的某些 FLAG，
4. 剩下的我看不明白，書上也沒說，一個例子都沒給，
5. 一個例子都沒有，大概不會考吧，

洗掉索引

有兩種語法可以洗掉索引：

DROP INDEX 表名.索引名

DROP INDEX 索引名 ON 表名

很簡單，對吧？

大總結

你能用 ALTER 干啥？

1. 修改表列，
2. 修改視圖，
3. 修改索引，

可見，修改表列最有用，

其他

其實我也不太清楚要總結什么，就先放這里吧，

后記

感謝大家查看我使用幾天假期做出來的勞動成果，
祝各位五一快樂，考試順利，

寫博客真的非常非常非常的耗時……
當然，也可能是我太菜了，不能手到拈來，歡迎各位大神前來指教，

沒了，

參考

[1] 命令和操作：轉載自 https://www.cnblogs.com/uakora/p/11809501.html
[2] 約束：大部分轉載自 https://www.w3cschool.cn/sql/vgh71oyq.html
[3] 視圖的意義：轉載自 https://zhuanlan.zhihu.com/p/80183774

標籤：其他

上一篇：有償資料爬蟲

下一篇：MySQL資料庫基本操作（二）