SQL 語法筆記

來自 W3School 的學習筆記

用 SQL 管理資料庫

部份 SQL 管理指令需要有相關的權限才可以進行操作

Database

建立

CREATE DATABASE <database_name>;

刪除（請小心操作）

DROP DATABASE <database_name>;

查詢所有 Database

SHOW DATABASES;

完整備份

BACKUP DATABASE <database_name>
TO DISK = <filepath>;

增量備份（只備份有新增的部份）

BACKUP DATABASE <database_name>
TO DISK = <filepath>
WITH DIFFERENTIAL;

資料表

資料型態

我覺得就邊做邊去 W3School 看就好了。

你說面試會考怎麼辦？我...我不知道...。

但可以用程式語言的想法去記，例如常見的 int、float、double、decimal，有些東西可以注意：

• MySQL 等 RDBMS 會以類似 datatype(size) 或 datatype(size, d) （特別常出現在浮點數，前面是儲存大小，後面是精度）的方式呈現，例如 char(size) 或是 int(size)，但 boolean
  • 有 Java 經驗的人可能會對這些資料型態的熟悉度比較吃香
• 有些會有可變長度字串概念，如 varchar(size) 或 varbinary(size)，有學過 Kotlin 或 JavaScript 的人應該會覺得這很熟悉，不過有點不太一樣
• SQL Server 出現的寬字元（用於儲存 Unicode）的資料型態會在前面加個 n ，例如 nvarchar(max)，還有 SQL Server 不太喜歡設定 datatype 的大小，只能說每個 RDBMS 的設計理念都不太一樣...
• 注意一下 SQL Server 的 decimal 和 numeric，他們都有精度設定 ( (小數點左邊, 小數點右邊) )

建立

CREATE TABLE <table_name>(
    <column1> <datatype>,
    <column2> <datatype>,
    ...
);

移除或清空

DROP TABLE <table_name>;

只是要清空資料表，但資料表本身不想刪除的話：

TRUNCATE TABLE <table_name>;

修改一張表

新增欄位

ALTER TABLE <table_name>
ADD <column_name> <datatype>;

移除欄位

ALTER TABLE <table_name>
DROP COLUMN <column_name>;

重新命名欄位

ALTER TABLE <table_name>
RENAME COLUMN <old_column_name> to <new_column_name>;

SQL Server:

EXEC sp_rename "table_name.old", "new_name", "COLUMN";

修改一個欄位資料型態

--- MS 系列
ALTER TABLE <table_name>
ALTER COLUMN <column_name> <datatype>;

--- MySQL, 舊版 Oracle
ALTER TABLE <table_name>
MODIFY COLUMN <column_name> <datatype>;

--- 新版 Oracle
ALTER TABLE <table_name>
MODIFY <column_name> <datatype>;

選擇、查詢及條件

一般選擇

SELECT <column_name>, <column_name2>, ... FROM <table_name>;

• From 後面可以接另外一個查詢字串，不一定要 table_name

不重複選擇

SELECT DISTINCT <column_name>, <column_name2>, ... FROM <table_name>;

• 你可以用 COUNT() 來數不重複項目的數量

# 通用寫法
SELECT COUNT(*) AS <CustomName> FROM (SELECT DISTINCT <column_name>, <column_name2>, ... FROM <table_name>);

# 非Access才可以用的寫法
SELECT COUNT(DISTINCT <column_name>, <column_name2>, ...) FROM <table_name>;

過濾結果

使用 WHERE

SELECT  <column_name> FROM <table_name> WHERE <condition>;

e.g:

SELECT * FROM Customers
WHERE CustomerID > 80;

• 你可以用 =、<、>、<=、>=、<>（不等於）作為條件判斷
• 條件後面接 BETWEEN 相當等於 >= 和 <= 的組合，例如 BETWEEN 50 AND 60
• 條件後面接 LIKE 則是用於找 pattern，例如 LIKE s% 等於「找出以 s 為開頭的的字串」
• 條件後面接 IN 則是用於找至少已知的一組資料，例如 IN("Los Angeles", "San Diego") 就是在找所選條件裡面包含 Los Angeles 或 San Diego 的資料
• 可以串連多個 AND 或 OR 或 NOT 來取得符合條件的資料（混用的話記得用括弧分開）
• NOT 加在任何條件前面，包含 BETWEEN、LIKE或 IN ，可以得到相反的結果

使用 AS

可以使用 AS 替某些計算結果的 Column 命名，例如：

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

在這裡 (temp_hi+temp_lo)/2 的 Column 名稱會被命名為 temp_avg

排序

SELECT <column_name>, <column_name2>, ... FROM  <table_name>
ORDER BY <column_name>, <column_name2>, ... ASC|DESC;

• 可以根據數字、字母進行排序
• 可以多欄進行排序，排序順序優先序由左至右
• 可以用 ASC、DESC 表示順向或逆向排序
• 多欄排序狀況下，個別欄位可以指定不同的順逆向排序 （如 ORDER BY <column_name> ASC, <column_name2> DESC）

限制筆數（很 RDBMS specify）

• SQL Server/MS Access 用的是 SELECT TOP，支援 PERCENT 百分比用法
• MySQL 用的是 LIMIT （常接在 WHERE 後面，但不一定要 WHERE）
• Oracle 12 之後用 ORDER BY <column> FETCH First <number> ROWS ONLY （在 WHERE 後面）
  • 舊版則是 WHERE ROWNUM <= <number>
• 都可以用 ORDER BY 來做順向、反向排序

資料操作（插入、更新、移除）

插入資料

INSERT INTO <table_name>(<column_name>, <column_name2> ...)
VALUES (<value1>, <value2>, ...);

• 可以省略 column_name，資料庫會依照資料表 Column 的順序插入
• 可以只選定特定的 Column 插入資料
• 可以一次插入多組資料，像這樣：

INSERT INTO <table_name>(<column_name>, <column_name2> ...)
VALUES
(<value1>, <value2>, ...),
(<value1>, <value2>, ...),
...;

NULL

如果資料插入後，有些欄位沒有資料，那該欄上的資料會變成 NULL

如果要查詢的話，可以用 IS NULL 或 IS NOT NULL 來做條件篩選，例如：

SELECT <column_name>
FROM <table_name>
WHERE <column_name> IS NULL | IS NOT NULL;

NULL 不是空白，也不是空格，NULL 就是空，完全沒有資料的意思

更新資料

UPDATE <table_name>
SET <column1> = <value1>, <column2> = <value2>, ...
WHERE <condition>

• 更新符合條件的數值，所以 WHERE 是必要的，不寫 WHERE 則是整張資料表的資料都會更新

刪除資料

DELETE FROM <table_name> WHERE <condition>

• 同 UPDATE，不寫 WHERE 就是清空整張表的資料
• 如果要讓整張表 Bang 不見是 DROP TABLE <table_name> 而不是 DELETE FROM

SQL Functions

最大最小值

MAX() 和 MIN() ，具體用法如下：

SELECT MAX(<column_name>) | MIN(<column_name>) FROM <table_name> WHERE <condition>;

• 跑出來的結果會是一個暫時命名的欄位，可以用 AS 來命名：

SELECT MAX(<column_name>) AS <CustomName> FROM ...;

• 可以用 GROUP BY 來做到「以另外一個欄位來對資料分組，並取最大/最小值」的效果

SELECT MAX(<column1>) AS <CustomName>, <column2>
FROM <table_name>
GROUP BY <column2>;

數資料總數

COUNT() 可以幫你解決這個問題：

SELECT COUNT(<column_name>) FROM <table_name> WHERE <condition>;

• 可以使用萬用字元 (*)
• 可以使用 DISTINCT 來對不重複的資料做數量統計（如 SELECT COUNT(DISTINCT <column_name> FROM ...)
• 可以用 WHERE，可以用 AS，當然也可以用 GROUP BY

數資料總和

有 SUM() 可以用

SELECT SUM(<column_name>) FROM <table_name> WHERE <condition>;

• AS、WHERE、GROUP BY 都可以用

取平均數

有 AVG() 可以用

SELECT AVG(<column_name>) FROM <table_name> WHERE <condition>;

• 可以用 AS、WHERE和 GROUP BY
• 應用：取平均還高的資料

SELECT * FROM <table_name> WHERE <column1> > (SELECT AVG(<column1>) FROM <table_name>)

進階查詢

找出 Pattern (LIKE)

使用 LIKE:

SELECT <column> FROM <table_name> WHERE <column> LIKE <pattern>;

• 可以用 AND、OR或 NOT 進行兩個 LIKE 的聯合查詢，或是得出相反的結果
• 可以使用萬用字元，如果不使用就是完整匹配模式

萬用字元

通用萬用字元:

字元	意義
%	匹配 0 個或多個字元
_	匹配 1 個字元

非通用萬用字元（RDBMS specify):

字元	意義
[]	匹配任意單一字元（沒有先後順序）
^	匹配任意沒有包含該字元
-	範圍匹配，如 a-z
{}	匹配任意跳脫字元

• 前三項 PostgreSQL、MySQL 不能用，最後一個只有 Oracle DB 可以用
• 如果要做跨 DB System SQL script，那就少用非通用萬用字元
• 不太想記 MS Access 的萬用字元，所以這邊沒有