SQL (可以唸字母 S. Q. L. 或
sequel)
用來建立、操作及存取關聯式資料庫 (Relational Database)
的語言就是 SQL。而對於 SQL 的標準化作業，主要是由 ANSI 與
ISO 這兩個組織所推動。
SQL
語言可大致分為幾個類別
Data definition 資料定義 (DDL)
用來定義資料庫、資料表、檢視表、索引、預存程序、觸發程序、函數等資料庫物件。常見的指令有：
Data manipulation 資料操縱 (DML)
用來處理資料表裡的資料，常見的指令有：
Queries 資料查詢 (DQL)
用來查詢資料表裡的資料：
Data control 資料控制 (DCL)
用來控制資料表、檢視表的存取權限，常見的指令有：
Transaction controls 交易
交易是單一工作單元。
如果交易成功，便會確定交易期間所修改的所有資料，且會成為資料庫中永久的內容。
如果交易發現錯誤，必須取消或回復，便會清除所有的資料修改。
- COMMIT: 完成交易作業
- ROLLBACK:
交易作業異常，將已變動的資料回復到交易開始的狀態
SQL 語法 Syntax
讓我們先來聊聊組成 SQL 語法的元素有什麼。
資料表 (Database Tables)
資料庫中最重要的物件就是資料表 (table)，資料庫由一個或一個上的資料表所構成，每個資料表名稱在資料庫中都是唯一的，資料表中每一直行
(column) 稱之為欄位，每個欄位都有其資料型態 (data type)，由不同欄位所組成的橫列
(row)，稱之為記錄 (record)，我們舉一個叫做 "customers"
的資料表作為例子：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
此資料表共包含 2 筆記錄，5 個欄位 (S_Id, Name, City, Address,
Phone)
資料表名稱有區分大小寫 (case-sensitive)，但某些資料庫在
Windows
作業系統中是不分大小寫的，而為了方便維護最好統一你的命名方式。
SQL 敘述句 (Statements)
我們利用 SQL 敘述句來跟資料庫溝通、下達指令，SQL
語言是由命令 (commands) 、子句 (clauses) 、運算子 (operators) 及
函數 (functions) 所組成，通常一個 SQL
敘述句由一段命令句開始描述您要對資料庫要求的動作，接著可能會接著條件語句，最後以分號
";" 結束：
有些資料庫並不強制一定要加上結尾分號。
SELECT * FROM customer;
上面這條 SQL 敘述句表示從 customer 資料表取出所有資料。
SQL 語句不區分大小寫 (case-insensitive)，如 select
* from customer; 和上例是相同的結果。
什麼是命令 (Commands)
建立新的資料庫、資料表、欄位及、索引等，或建立查詢表、排序、過濾資料、查詢、修改、新增及刪除資料等動作。(即
CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE 等命令)
什麼是子句 (Clauses)
子句是用來設定及操作你的 SQL query，例如：
| 子句 |
說明 |
| FROM |
指定作用的資料表 |
| WHERE |
設定查詢條件 |
| GROUP BY |
設定分組條件 |
| ORDER BY |
設定輸出的排序 |
什麼是運算子 (Operators)
運算子用來幫助 SQL
語句處理數值、字串或進行邏輯運算及比較條件。
什麼是函數 (Functions)
SQL 語言內建許多函數可以直接在 SQL
語句裡面使用，像是取得某欄位加總後數值、取得某欄位內最大或最小值等等。
SQL 敘述句的書寫習慣
將所有的執行語句中的 SQL 關鍵字大寫是一個良好的 SQL
書寫習慣，這會幫助你更輕易的去檢視你的 SQL 語法。
此外，你可以將較長的 SQL
語句拆成多行書寫，這會讓你比較容易閱讀！
SELECT column_one, column_two
FROM table_name
WHERE table_id = 123;
SQL 的資料型別 (Data Types)
SQL
基本上有下列幾種資料型別，我們必需了解儲存各類型資料值要分別採用何種資料型別欄位以便進行資料庫設計。
- 數值資料 (Numeric Types) - 11, 2.5492, -91
- 字串資料 (String Types) - '學習 SQL 語言'
- 空值 (NULL) - (欄位為空)
- 布林值 (Boolean) - true / false
數值資料 (Numeric Data)
SQL 的數值型態有 integer, float, money 等，使用數值資料有一個好處就是你能搭配內建的數值函數來做資料處理，例如
SUM()
函數即可直接獲得該數值欄位的總合。
字串(元)資料 (Character
& Strings Data)
儲存字元或符號之資料型別。
日期/時間資料 (Date Data)
用來記錄日期/時間的資料型別，有 date, time, timestamp 等。
布林值 (Boolean Data)
true/false, Yes/No, 1/0。
空值 (NULL Data)
空值，沒有資料存在於欄位。通常在建立資料表時，你可以設定欄位是否允許空值。
CREATE TABLE customer (
C_id INT PRIMARY KEY,
Name VARCHAR(10) NOT NULL,
Address VARCHAR(255) NULL,
Phone VARCHAR(10) NULL
);
NULL 值和一個空字串 '' 是有不一樣意涵的。例如，下列 SQL
敘述句：
INSERT INTO customer (phone) VALUES (NULL);
INSERT INTO customer (phone) VALUES ('');
其中第一個敘述句的意味著不知道電話號碼；而第二個則是意味著沒有電話。
在大多的資料庫中，你不能索引允許有 NULL
值的欄位。你必須聲明要索引的欄位為 NOT NULL，此外，你也不能插入
NULL 到具有索引的欄位中。
另外值得注意的是，NULL 這字面常量 (Literal)
在某些特殊情形下使用代表著不同含意：
- 若你將 NULL 插入資料表中的 TIMESTAMP
欄位，則代表目前的日期和時間。
- 若你將 NULL 插入一個 AUTO_INCREMENT
欄位，則代表目前順序中的下一個號碼。
SQL 運算子 Operators
SQL 有 3
種類型的運算子，分別是算數運算子、邏輯運算子及比較運算子。
算數運算子 (Arithmetic
Operators)
| 運算子 |
用途 |
用法 |
結果 |
| + |
加法 |
1 + 2 |
3 |
| - |
減法 |
5 - 4 |
1 |
| * |
乘法 |
6 * 6 |
36 |
| / |
商數 |
8 / 5 |
1.6 |
| % |
餘數 |
8 % 5 |
3 |
邏輯運算子 (Logical Operators)
比較運算子 (Comparison
Operators)
| 運算子 |
語意 |
用法 |
結果 |
| < |
小於 |
3 < 5 |
True |
| > |
大於 |
8 > 1 |
True |
| <= |
小於等於 |
20 <= 10 |
False |
| >= |
大於等於 |
4 >= 6 |
False |
| = |
等於 |
2 = 2 |
True |
| <> |
不等於 |
7 <> 6 |
True |
| BETWEEN |
在某個範圍內 |
|
|
| LIKE |
符合模式條件 |
|
|
| IN |
值存在於IN集合中 |
資料定義 DDL
CREATE DATABASE
敘述句 (SQL CREATE DATABASE Statement)
CREATE DATABASE 是我們用來建立一個新資料庫的語法。
CREATE DATABASE 語法 (Syntax)
CREATE DATABASE database_name;
例如，我們想建立一個叫做 foo 的資料庫：
CREATE DATABASE foo;
這樣就建立完成，簡單吧！
接著我們就可以新增資料表到此資料庫中囉 - CREATE
TABLE
CREATE TABLE
敘述句 (SQL CREATE TABLE Statement)
CREATE TABLE 是我們在資料庫中用來建立一個新資料表的語法。
CREATE TABLE 語法 (Syntax)
CREATE TABLE table_name (
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
···
);
data_type 用來指定該欄位資料儲存的資料型別，你可以另外去細看各家資料庫的文件看有提供哪些型別喔！(像是
MySQL)
假設我們現在想建立一個 "customers"
資料表，其中包含這幾個欄位 - C_Id, Name, Address, Phone：
CREATE TABLE customers (
C_Id INT,
Name varchar(50),
Address varchar(255),
Phone varchar(20)
);
這樣就建好了！新建資料表看起來會像是這個樣子：
接著，我們就可以用 INSERT
INTO 來塞入資料囉！
ALTER TABLE
敘述句
(SQL ALTER TABLE Statement)
ALTER TABLE
是用來對已存在的資料表結構作更改。語法型式如下：
ALTER TABLE table_name ...;
假設現在我們已經建立好一個 "customers" 資料表：
接著，我們要如何...
增加欄位 (ADD COLUMN)
語法
ALTER TABLE table_name ADD column_name datatype;
例如，如果我們想增加一個 Discount 欄位：
ALTER TABLE customers ADD Discount VARCHAR(10);
更改欄位資料型別
(ALTER COUMN TYPE)
語法
ALTER TABLE table_name ALTER COLUMN column_name datatype;
例如，更改 Discount 欄位的資料型別：
ALTER TABLE customers ALTER COLUMN Discount DECIMAL(18, 2);
刪除欄位 (DROP COLUMN)
語法
ALTER TABLE table_name DROP COLUMN column_name;
例如，刪除 Discount 欄位：
ALTER TABLE customers DROP COLUMN Discount;
DROP TABLE / TRUNCATE
TABLE / DROP DATABASE Statement
我們可以用 DROP TABLE / TRUNCATE TABLE / DROP DATABASE 來刪除資料表或資料庫。
刪除資料表 (DROP TABLE)
完全刪除整個資料表。
DROP TABLE table_name;
僅刪除資料表內容，但保留結構
(TRUNCATE TABLE)
TRUNCATE TABLE table_name;
資料表還在，只是資料清空了。
刪除資料庫 (DROP DATABASE)
DROP DATABASE database_name;
SQL Constraint 限制
Constraint
用來有條件地限制哪些資料才可以被存入資料表中，也就是對欄位作約束。這些限制可以在建立資料表時
CREATE TABLE
指定條件，或是之後再修改 ALTER
TABLE 指定。
SQL
有哪幾種類型的限制條件？
NOT NULL 限制 (SQL NOT NULL
Constraint)
NOT NULL
用來限制該欄位不能接受空值，而在預設的情況下，一個欄位是允許接受空值的。
當某欄位限制為 NOT NULL
時，則在新增資料時該欄位一定要有值。
例如，我們建立一張 customer 資料表，並限制其 C_Id 及 Name
欄位值不能是空值：
CREATE TABLE customer (
C_Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(255),
Phone VARCHAR(20)
);
UNIQUE 唯一限制 (SQL
UNIQUE Constraint)
UNIQUE 用來保證欄位在資料表中的唯一性，約束資料表中的欄位不能有重複的資料。
一個資料表可有多個 UNIQUE 欄位，此外 UNIQUE
欄位中可以接受 NULL 值。
假設我們要對「customer」資料表限制「C_Id」欄位不能有重複值存在：
在建立資料表時 CREATE
TABLE...
CREATE TABLE customer (
C_Id INT NOT NULL UNIQUE,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(255),
Phone VARCHAR(20)
);
或這樣寫：
CREATE TABLE customer (
C_Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(255),
Phone VARCHAR(20),
UNIQUE (C_Id)
);
替唯一鍵命名與多欄位的唯一限制
我們限制 C_Id 及 Name 這兩個欄位共同唯一，CONSTRAINT
後面接著的即是此唯一鍵的名稱。
CREATE TABLE customer (
C_Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(255),
Phone VARCHAR(20),
CONSTRAINT u_Customer_Id UNIQUE (C_Id, Name)
);
更改資料表限制 ALTER TABLE...
ALTER TABLE customer ADD UNIQUE (C_Id);
替唯一鍵命名與多欄位的唯一限制：
ALTER TABLE customer
ADD CONSTRAINT u_Customer_Id UNIQUE (C_Id, Name);
移除資料表限制 ALTER TABLE...
MySQL
ALTER TABLE customer DROP INDEX u_Customer_Id;
SQL Server / Oracle / MS Access
ALTER TABLE customer DROP CONSTRAINT u_Customer_Id;
PRIMARY KEY
主鍵限制 (SQL PRIMARY KEY Constraint)
PRIMARY KEY
用來保證欄位在資料表中的唯一性，主鍵欄位中的每一筆資料在資料表中都必需是獨一無二的。
PRIMARY KEY 有點類似 UNIQUE
加上 NOT NULL。
一個資料表中只能有一個 PRIMARY KEY，但是可以有多個
UNIQUE。
假設我們要將 customer 資料表中的 C_Id 欄位設為主鍵：
在建立資料表時 CREATE
TABLE...
CREATE TABLE customer (
C_Id INT NOT NULL PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(255),
Phone VARCHAR(20)
);
或這樣寫：
CREATE TABLE customer (
C_Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(255),
Phone VARCHAR(20),
PRIMARY KEY (C_Id)
);
替主鍵命名與多欄位的組合鍵 (Composite Primary Keys)：
CREATE TABLE customer (
C_Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(255),
Phone VARCHAR(20),
CONSTRAINT pk_Customer_Id PRIMARY KEY (C_Id, Name)
);
我們限制 C_Id 及 Name 這兩個欄位為主鍵，CONSTRAINT
後面接著的即是此主鍵的名稱。
當主鍵包含多個欄位時，我們稱之為組合鍵 (Composite Key)。
更改資料表限制 ALTER TABLE...
ALTER TABLE customer ADD PRIMARY KEY (C_Id);
替主鍵命名與多欄位的組合鍵：
ALTER TABLE customer
ADD CONSTRAINT u_Customer_Id PRIMARY KEY (C_Id, Name);
移除資料表限制 ALTER TABLE...
MySQL
ALTER TABLE customer DROP PRIMARY KEY;
SQL Server / Oracle / MS Access
ALTER TABLE customer DROP CONSTRAINT pk_PersonID;
FOREIGN KEY
外鍵限制 (SQL FOREIGN KEY Constraint)
外鍵是一個 (或多個)
指向其它資料表中主鍵的欄位，它限制欄位值只能來自另一個資料表的主鍵欄位，用來確定資料的參考完整性
(Referential Integrity)。
如果想在 MySQL
資料庫中使用外鍵限制，必需讓資料表使用 InnoDB 儲存引擎。
讓我們簡單了解一下什麼是外鍵：
這是一個客戶資料表 customers
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
而這是客戶訂單的資料表 orders
| O_Id |
Order_No |
C_Id |
| 1 |
2572 |
3 |
| 2 |
7375 |
3 |
| 3 |
7520 |
1 |
| 4 |
1054 |
2 |
在這裡我們會想有一個限制，就是在客戶訂單資料表中的客戶，都一定要在
customers 資料表中存在。所以我們需要在 orders
資料表中設定一個外鍵，再將此外鍵指向 customers
資料表中的主鍵，以確定所有在 orders
資料表中的客戶都存在於 customers
資料表中，才不會有任何幽靈訂單的出現！
FOREIGN KEY Constraint
假設我們要將 customer 資料表中的 C_Id 欄位設為外鍵：
在建立資料表時 CREATE
TABLE...
CREATE TABLE orders (
O_Id INT NOT NULL,
Order_No INT NOT NULL,
C_Id INT,
PRIMARY KEY (O_Id),
FOREIGN KEY (C_Id) REFERENCES customers(C_Id)
);
替外鍵命名與多欄位的外鍵：
CREATE TABLE orders (
O_Id INT NOT NULL PRIMARY KEY,
Order_No INT NOT NULL,
C_Id INT,
CONSTRAINT fk_Cusomer_Id FOREIGN KEY (C_Id) REFERENCES customers(C_Id)
);
我們限制 C_Id 為外鍵，CONSTRAINT
後面接著的即是此外鍵的名稱，另一個重點是記得 customers
資料表中需將 C_Id 設為主鍵。
更改資料表限制 ALTER TABLE...
ALTER TABLE orders
ADD FOREIGN KEY (C_Id) REFERENCES customers(C_Id);
替外鍵命名與多欄位的外鍵：
ALTER TABLE orders
ADD CONSTRAINT fk_Cusomer_Id FOREIGN KEY (C_Id) REFERENCES customers(C_Id);
移除資料表限制 ALTER TABLE...
MySQL
ALTER TABLE orders DROP FOREIGN KEY fk_Cusomer_Id;
SQL Server / Oracle / MS Access
ALTER TABLE orders DROP CONSTRAINT fk_Cusomer_Id;
CHECK 檢查限制 (SQL CHECK
Constraint)
CHECK
限制用來約束欄位中的可用值，以保證該欄位中的資料值都會符合您設定的條件。
假設我們要限制 customer 資料表中的 C_Id
欄位值都必需要大於 0：
在建立資料表同時 CREATE
TABLE...
CREATE TABLE customer (
C_Id INT NOT NULL CHECK (C_Id>0),
Name VARCHAR(50) NOT NULL,
Address VARCHAR(255),
Phone VARCHAR(20)
);
或這樣寫：
CREATE TABLE customer (
C_Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(255),
Phone VARCHAR(20),
CHECK (C_Id>0)
);
在 MySQL 增加 CHECK 限制不會有錯誤，但是沒有用，CHECK
並不會被執行喔！
替檢查限制命名與多欄位的檢查：
CREATE TABLE customer (
C_Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(255),
Phone VARCHAR(20),
CONSTRAINT chk_Customer CHECK (C_Id>0 AND Name!='XXX')
);
更改資料表限制 ALTER TABLE...
ALTER TABLE customer ADD CHECK (C_Id>0);
替主鍵命名與多欄位的組合鍵：
ALTER TABLE customer
ADD CONSTRAINT chk_Customer CHECK (C_Id>0 AND Name!='XXX');
移除資料表限制 ALTER TABLE...
ALTER TABLE customer DROP CONSTRAINT chk_Customer;
DEFAULT 預設值限制
(SQL DEFAULT Constraint)
DEFAULT 限制用來設定欄位的預設值。當你在 INSERT
資料時若該欄位沒指定值則會採用預設值。
假設我們要設定 customer 資料表中的 Address 欄位預設值為
"未知"：
在建立資料表同時 CREATE
TABLE...
CREATE TABLE customer (
C_Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(255) DEFAULT '未知',
Phone VARCHAR(20)
);
更改資料表限制 ALTER TABLE...
ALTER TABLE customer ALTER COLUMN Address SET DEFAULT '未知';
SQL Server
ALTER TABLE customer ADD DEFAULT '未知' FOR Address;
移除資料表限制 ALTER TABLE...
ALTER TABLE customer ALTER COLUMN Address DROP DEFAULT;
SQL Server
ALTER TABLE table_name DROP constrain_name;
Oracle
ALTER TABLE table_name MODIFY column_name DEFAULT NULL;
AUTO INCREMENT 欄位
(SQL AUTO INCREMENT column)
AUTO INCREMENT 欄位會自動遞增資料行的值，因為每次新增資料時欄位值都會自動遞增也就是說
AUTO INCREMENT
欄位值會是唯一的，該欄位用途就像是一個識別碼或流水號，而
AUTO INCREMENT 常與 Primary Key 一起搭配使用。
設定 AUTO INCREMENT 欄位
MySQL
CREATE TABLE customers (
C_Id INT AUTO_INCREMENT,
Name varchar(50),
Address varchar(255),
Phone varchar(20),
PRIMARY KEY (C_Id)
);
MySQL 語法使用 AUTO_INCREMENT 這關鍵字。注意要將 AUTO_INCREMENT
欄位指定為 PRIMARY KEY，否則會有錯誤！
新增一筆資料：
INSERT INTO customers (Name, Address, Phone)
VALUES ('姓名XXX', '地址XXX', '電話XXX');
C_Id 欄位不需要指定值，MySQL 預設會由1開始逐列自動遞增
(2, 3, 4...)。不過你也可以替 AUTO_INCREMENT
欄位指定一個起始值，語法如下：
ALTER TABLE table_name AUTO_INCREMENT=起始數字;
SQL Server
CREATE TABLE customers (
C_Id INT IDENTITY PRIMARY KEY,
Name varchar(50),
Address varchar(255),
Phone varchar(20)
);
SQL Server 語法使用 IDENTITY 這關鍵字。
新增一筆資料：
INSERT INTO customers (Name, Address, Phone)
VALUES ('姓名XXX', '地址XXX', '電話XXX');
SQL Server 預設會由1開始逐列自動遞增 (2, 3, 4...)。不過你也可以替
IDENTITY 欄位指定一個起始值與遞增量，語法如下：
將 IDENTITY 改成 IDENTITY(起始值, 遞增量)
Oracle
Oracle 比較不一樣，我們需要先建立一個 Sequence，然後遞增值則從這個
Sequence 抓。
Sequence 建立語法：
CREATE SEQUENCE sequence_name
START WITH 1
INCREMENT BY 1;
新增一筆資料：
INSERT INTO customers (C_Id, Name, Address, Phone)
VALUES (sequence_name.NEXTVAL ,'姓名XXX', '地址XXX', '電話XXX');
NEXTVAL 用來取得下一個值。
MS Access
CREATE TABLE customers (
C_Id INT PRIMARY KEY AUTOINCREMENT,
Name varchar(50),
Address varchar(255),
Phone varchar(20)
);
Access 語法使用 AUTOINCREMENT 這關鍵字。
新增一筆資料：
INSERT INTO customers (Name, Address, Phone)
VALUES ('姓名XXX', '地址XXX', '電話XXX');
Access 預設會由 1 開始逐列自動遞增 (2, 3, 4...)。不過你也可以替
AUTOINCREMENT 欄位指定一個起始值與遞增量，語法如下：
將 AUTOINCREMENT 改成 AUTOINCREMENT(起始值, 遞增量)
CREATE INDEX
敘述句 (SQL CREATE INDEX Statement)
如同如果一本書有索引 (index)
您可以更快的找到需要的資料，資料庫也是同樣道理，如果一張資料表中沒有索引，在查詢資料時就必需先把整張資料表讀過一遍
(scan table)
再慢慢去找資料，非常的沒有效率，因此替資料表加上 "適當的"
索引會大大加快查詢速度哦。
為什麼說適當的？因為一個設有索引的資料表當你在 INSERT、UPDATE
或 DELETE
時，索引也需要額外花資源去更新，所以最好只在常被查詢到的欄位作索引，不然得不償失啊！
CREATE INDEX 語法 (SQL
CREATE INDEX Syntax)
CREATE INDEX index_name ON table_name (column_name);
建立多欄位索引
(Multiple-Column Index)
CREATE INDEX index_name ON table_name (column_name1, column_name2...);
什麼時候建立多欄位索引比較合適？如果您常對一張資料表查詢
WHERE column_name1='xxx' AND column_name2='yyy'，這時你就可以對
column_name1 及 column_name2 這兩個欄位建立一個共同索引。
DROP INDEX 敘述句 (SQL
DROP INDEX Statement)
我們要怎麼將索引移除掉？就是使用 DROP INDEX。
MySQL
ALTER TABLE table_name DROP INDEX index_name;
SQL Server
DROP INDEX table_name.index_name;
Oracle
DROP INDEX index_name;
MS Access
DROP INDEX index_name ON table_name;
View 檢視表、視圖 (SQL View)
View 是藉由 SQL SELECT 查詢動態組合生成的資料表 (亦即
View
是由查詢得到的結果集組合而成的資料表)。View
內的資料紀錄是由其它實際存在的資料表中產生的，它就像是一個虛擬資料表，實際上資料庫
(或說是硬碟) 裡面是不存在這一個資料表的 (只存在此 View
的相關定義)，但是我們使用上卻有如實際存在的資料表 -
所有的 SQL 查詢語法都可以操作在此 View 上。
資料表是一種實體結構 (physical structure)，而 View
是一種虛擬結構 (virtual structure)。
View 有哪些特性
- 加強資料庫的安全性，View 可以將實體資料表結構隱藏起來，同時限制使用者只可以檢視及使用哪些資料表欄位。
- 檢視表是唯讀的，亦即外部使用者無法直接透過 View
去修改內部資料。
- 將複雜的 SQL 查詢包裝在 View
中，可以簡化查詢的複雜度。
- 當資料表結構有變更時，只需要更改 View
的設定，不需更改程式。
建立 View (SQL CREATE VIEW)
CREATE VIEW view_name [(column_list)] AS
SELECT column_name(s)
FROM table_name
WHERE condition;
例如：
CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
一個 View
可以由查詢實體資料表而建立，亦可以查詢其它已存在的 View
而建立。
更新 View (SQL CREATE OR
REPLACE VIEW)
如果加上 OR REPLACE 子句的意思就是若同名的 View
已經存在就覆蓋取代它。如果 View 不存在，我們可以把 CREATE
OR REPLACE VIEW 看做是如同 CREATE VIEW；而如果 View
已存在，我們可以把 CREATE OR REPLACE VIEW 看做是 ALTER VIEW。
CREATE OR REPLACE VIEW view_name [(column_list)] AS
SELECT column_name(s)
FROM table_name
WHERE condition;
刪除 View (SQL DROP VIEW)
DROP VIEW view_name;
View 使用實例 (Example)
假設這是一個產品訂單資料表 p_orders：
| Product |
Price |
Quantity |
| LCD |
4000 |
100 |
| CPU |
5000 |
200 |
我們可以建立一個方便查詢各產品售出總額的 View：
CREATE VIEW view_p_sum (Product, P_SUM) AS
SELECT Product, Price*Quantity FROM p_orders GROUP BY Product;
接著，你就可以像操作一般資料表：
SELECT * FROM view_p_sum
| Product |
P_SUM |
| CPU |
1000000 |
| LCD |
400000 |
資料操縱 DML
INSERT INTO 敘述句
(SQL INSERT INTO Statement)
INSERT INTO 是用來新增資料至某資料表 (table)。
INSERT INTO 語法 (SQL
INSERT INTO Syntax)
INSERT INTO table_name (column1, column2, column3...)
VALUES (value1, value2, value3...);
或，可以簡寫成這樣：
INSERT INTO table_name
VALUES (value1, value2, value3...);
使用簡寫的語法每個欄位的值都必需要依序輸入。
INSERT INTO 用法 (Example)
假設我們想從下面的 customers 資料表中新增一顧客的資料：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
我們可以使用以下的 INSERT INTO 敘述句：
INSERT INTO customers (C_Id, Name, City, Address, Phone)
VALUES (3, '李三', '高雄縣', 'ZZ路300號', '07-12345678');
或，簡寫：
INSERT INTO customers
VALUES (3, '李三', '高雄縣', 'ZZ路300號', '07-12345678');
查詢新增後的結果如下：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
只輸入幾個特定的欄位值
你也可以只輸入幾個特定的欄位值：
INSERT INTO customers (C_Id, Name, City)
VALUES (3, '李三', '高雄縣');
查詢新增後的結果如下：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
|
|
一次新增多筆資料
(INSERT INTO SELECT)
語法：
INSERT INTO table_name
VALUES (value1_1, value2_2, value3_3,···),
(value2_1, value2_2, value2_3,···),
(value3_1, value3_2, value3_3,···),
······;
或利用子查詢，從其它的資料表中取得資料來作一次多筆新增：
INSERT INTO table_name (column1, column2, column3,...)
SELECT othercolumn1, othercolumn2, othercolumn3,...
FROM othertable_name;
在子查詢中你也可以利用 WHERE、GROUP
BY 及 HAVING
等子句來作有條件的新增資料。
UPDATE 敘述句 (SQL UPDATE
Statement)
如果我們要修改資料表中的資料我們就會需要用到 UPDATE。
UPDATE 語法 (SQL UPDATE Syntax)
UPDATE table_name
SET column1=value1, column2=value2, column3=value3···
WHERE some_column=some_value;
WHERE 條件式記得要加哦！這樣才會只更新特定某(幾)筆資料，不然
"全部的" 資料都會更改。
UPDATE 敘述句用法 (Example)
假設我們想從下面的 customers
資料表中更改顧客王二的連絡電話：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
我們可以使用以下的 UPDATE 敘述句：
UPDATE customers SET Phone='03-87654321' WHERE Name='王二';
更新後的結果如下：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-87654321 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
DELETE FROM 敘述句
(SQL DELETE FROM Statement)
DELETE FROM 是用來刪除資料表中的資料。
DELETE FROM 語法 (SQL
DELETE FROM Syntax)
DELETE FROM table_name
WHERE column_name operator value;
WHERE 條件式記得要加哦！不然 "全部的"
資料都會刪除了。
DELETE FROM 敘述句用法
(Example)
假設我們想從下面的 customers
資料表中刪除顧客王二的資料：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
我們可以使用以下的 DELETE FROM 敘述句：
DELETE FROM customers WHERE Name='王二';
刪除後的資料表現在變成：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
一次刪除資料表中所有的資料
我們還可以這樣一次刪除某資料表中所有的資料：
DELETE FROM table_name;
或者：
DELETE * FROM table_name;
SELECT INTO 敘述句
(SQL SELECT INTO Statement)
SELECT INTO
用來從某資料表查詢所得之資料集結果新增到另一個新建的資料表中。此一指令常用來複製備份資料表，或將資料表輸出至另一資料庫中。
SELECT INTO 語法 (SQL
SELECT INTO Syntax)
SELECT table_column1, table_column2, table_column3...
INTO new_table_name [IN another_database]
FROM table_name;
其中 new_table_name
為欲新建的資料表名稱，該資料表會自動建立，且不可與已經存在的資料表名稱相同；而
another_database 為至外部資料庫的路徑。
SELECT INTO 敘述句用法
(Example)
例如，我們現在想備份顧客資料表 customers：
SELECT * INTO customers_backup FROM customers;
或者，我們想把它備份到 backup 資料庫中：
SELECT *
INTO customers_backup IN 'backup.mdb'
FROM customers;
我們也可以只複製資料表結構：
SELECT * INTO new_table_name FROM table_name WHERE 0=1;
MySQL 資料庫不支援 SELECT INTO，但你可以用
INSERT...SELECT 來達到同樣的功能。
資料查詢 DQL
SELECT 敘述句 (SQL SELECT
Statement)
SELECT 很可能是最常用到的 SQL
語句，它是用來從資料庫取得資料，這個動作我們通常稱之為查詢
(query)，資料庫依 SELECT 查詢的要求會返回一個結果資料表
(result table)，我們通常稱之為資料集 (result-set)。
SELECT 語法 (SQL SELECT Syntax)
SELECT 敘述句主要由兩部分構成，第一部分是要 "拿什麼"
資料 (若有多項用逗號隔開)；第二部分則為 "從哪拿"。
SELECT table_column1, table_column2, table_column3...
FROM table_name;
SELECT 查詢用法 (SQL SELECT
Example)
假設我們想從下面的 customers 資料表中取得顧客的姓名
(Name) 及電話 (Phone)：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
我們可以下這個 SELECT 查詢敘述句：
SELECT Name, Phone FROM customers;
返回的結果 (資料集) 會像這樣：
| Name |
Phone |
| 張一 |
02-12345678 |
| 王二 |
03-12345678 |
| 李三 |
07-12345678 |
SELECT *
如果我們想一次取得整張資料表裡所有的資料我們可以在
SELECT 語句裡用 * 這個特殊符號。
SELECT * FROM customers;
這個 SELECT 查詢返回的資料集會是：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
若非必要，我們應該盡量避免使用 SELECT *，因為一次取得整張資料表會比較耗費系統資源，記住一個原則，取得需要的資料就好，不多拿也不少拿。例如若只想知道客戶的電話不需要連他的住址也一起取得。
WHERE 查詢子句 (SQL WHERE
Clause)
我們可以進一步在 SELECT 查詢語句使用 WHERE
關鍵字搭配運算子來取出 "符合條件" 的紀錄值。
WHERE 語法 (SQL WHERE Syntax)
SELECT table_column1, table_column2...
FROM table_name
WHERE column_name operator value;
WHERE 子句用法 (Example)
假設我們想從下面的 customers 資料表中查詢 "王二"
的資料：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
| 4 |
陳四 |
台北市 |
AA路400號 |
02-87654321 |
我們可以下 "取出表中姓名欄位值是王二"
如此條件的 SELECT 敘述句：
SELECT * FROM customers WHERE Name = '王二';
查詢的結果如下：
| C_Id |
Name |
City |
Address |
Phone |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
查詢條件中的 "字串值" 需要用單引號 ''
包住，否則會出現錯誤；另一方面，對於數值型別則不可以加上引號，否則也會出現錯誤。被單引號圍住的字元即表示其資料型別為字串，但有些資料庫也接受用雙引號。
多個查詢條件式
你可以使用 AND 或 OR
運算子同時下達多個條件：
- AND 運算子表示其左右兩條件皆需符合
- OR 運算子表示其左右兩條件至少需符合一個
SELECT table_column1, table_column2...
FROM table_name
WHERE column_name1 operator value1
AND column_name2 operator value2
[AND|OR]...;
舉個例子，我們想在下面這個 customers
資料表中查詢住在台北市而且薪水大於 5
萬的顧客姓名及連絡電話：
| C_Id |
Name |
City |
Address |
Phone |
Salary |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
25000 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
30000 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
30000 |
| 4 |
陳四 |
台北市 |
AA路400號 |
02-87654321 |
50000 |
可以這樣查詢：
SELECT Name, Phone FROM table_name
WHERE City = '台北市' AND Salary >= 50000;
查詢的結果如下：
| Name |
Phone |
| 陳四 |
02-87654321 |
利用圓括號 ()
可以來執行更複雜的查詢條件，圓括號內圍住的的條件式會優先判斷。例如：
WHERE 條件一 AND (條件二 OR 條件三)
此 SQL 陳述式會優先對條件二與條件三做 OR
邏輯運算，所得結果再接著和條件一做 AND 邏輯運算。
ORDER BY 關鍵字 (SQL ORDER
BY Keyword)
我們可以將 SELECT
取得的資料集依某欄位來作排序，而排序分別可以由小至大
(ascending; 預設)，或由大至小 (descending)。
ORDER BY 語法 (SQL ORDER BY
Syntax)
SELECT table_column1, table_column2...
FROM table_name
ORDER BY column_name1 ASC|DESC, column_name2 ASC|DESC...
ORDER BY 查詢用法 (Example)
從下面的 employees
資料表中取出所有員工的資料並依職稱來作排序 (即字母順序)：
| E_Id |
Name |
Title |
| 1 |
Allen |
crew |
| 2 |
Tom |
manager |
| 3 |
Chris |
crew |
| 4 |
Bill |
crew |
我們可以下這個 SELECT 敘述句：
SELECT * FROM employees ORDER BY Title;
查詢的結果如下：
| E_Id |
Name |
Title |
| 1 |
Allen |
crew |
| 3 |
Chris |
crew |
| 4 |
Bill |
crew |
| 2 |
Tom |
manager |
若語句中沒加上 ASC 或 DESC 關鍵字，預設默認為
ASC。
依職稱來作正向排序之後再依名稱來作反向排序：
SELECT * FROM employees ORDER BY Title ASC, Name DESC;
查詢的結果如下：
| E_Id |
Name |
Title |
| 3 |
Chris |
crew |
| 4 |
Bill |
crew |
| 1 |
Allen |
crew |
| 2 |
Tom |
manager |
SELECT
語句中用來作排序的欄位名稱，除了欄位名稱外，也可以使用
SELECT 語句查詢的欄位順序。如，SELECT 關鍵字後的第一個欄位
(table_column1) 為 1，第二個欄位 (table_column2) 則為 2。因此，我們也可以將上例中的
SQL 敘述句改寫成：
SELECT * FROM employees ORDER BY 3 ASC, 2 DESC;
SELECT DISTINCT (SQL
SELECT DISTINCT Statement)
一個資料表的某欄位中可能會有多個紀錄都是相同值的情況，在
SELECT
查詢語句中我們可使用 DISTINCT
關鍵字過濾重複出現的紀錄值。
SELECT DISTINCT 語法
(SELECT DISTINCT Syntax)
SELECT DISTINCT table_column1, table_column2...
FROM table_name;
SELECT DISTINCT 查詢用法
(Example)
假設我們想從下面的 customers
資料表中查詢顧客分佈在哪幾個縣市：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
| 4 |
陳四 |
台北市 |
AA路400號 |
02-87654321 |
我們可以下這個 SELECT 查詢敘述句：
SELECT DISTINCT City FROM customers;
返回的結果如下：
原本資料表的 City
欄位中有兩個重複值台北市，可是我們只想知道有哪幾個縣市有顧客而已，故我們使用
DISTINCT 關鍵字來限制僅取出欄位中 "不相同" 的值。
若接著 SELECT DISTINCT
後面有指定兩個以上的欄位，則要符合所有欄位值皆同樣重複的情況下該筆資料才會被捨棄。若只有其中一個欄位值相同但其它欄位值並不同，則仍會取出該筆資料。
TOP, LIMIT, ROWNUM
子句 (SQL TOP, LIMIT, ROWNUM Clause)
TOP (SQL Server), LIMIT (MySQL), ROWNUM (Oracle)
這些語法其實都是同樣的功能，都是用來限制您的 SQL
查詢語句最多只影響幾筆資料，而不同的語法則只因不同的資料庫實作時採用不同的名稱。
我們來看看以下的例子：
我們怎麼從下面這個 customers 資料表中？
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
| 4 |
陳四 |
台北市 |
AA路400號 |
02-87654321 |
只取出前兩筆資料呢？
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
MySQL - LIMIT 語法 (SQL LIMIT
Syntax)
SELECT table_column1, table_column2...
FROM table_name LIMIT number;
你可以這樣下 LIMIT 查詢語句：
SELECT * FROM customers LIMIT 2;
SQL Server - TOP 語法 (SQL TOP
Syntax)
SELECT TOP number|percent table_column1, table_column2...
FROM table_name;
你可以這樣下 TOP 查詢語句：
SELECT TOP 2 * FROM customers;
或者這樣：
SELECT TOP 50 PERCENT * FROM customers;
Oracle - ROWNUM 語法 (ROWNUM
Syntax)
SELECT table_column1, table_column2...
FROM table_name WHERE ROWNUM <= number;
你可以這樣下 ROWNUM 查詢語句：
SELECT * FROM customers WHERE ROWNUM <= 2;
(NOT) IN 運算子 (SQL (NOT) IN
Operator)
IN 搭配 WHERE
子句可以用來限定必需符合某些欄位值為條件來搜尋資料表中的特定資料。
IN 語法 (SQL IN Syntax)
SELECT table_column1, table_column2, table_column3...
FROM table_name
WHERE column_name
IN (value1, value2, value3...);
IN 運算子查詢用法 (Example)
假設我們想從下面的 customers
資料表中取得顧客張一或李三的資料：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
我們可以這樣作 SQL 查詢：
SELECT * FROM customers
WHERE Name IN ('張一', '李三');
查詢後返回的結果會是：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
NOT IN
相反的，NOT IN
就是不包含在條件裡的的資料我通通要了，如上例多加上 NOT：
SELECT * FROM customers
WHERE Name NOT IN ('張一', '李三');
查詢後返回的結果會是：
| C_Id |
Name |
City |
Address |
Phone |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
(NOT) BETWEEN 運算子
(SQL (NOT) BETWEEN Operator)
不同於 IN
運算子是依不連續的值作為 WHERE
查詢子句的篩選條件，BETWEEN
則是用來限定依某範圍內連續的值作為條件來搜尋資料表中的特定資料。
作為查詢範圍條件的欄位型態可為數值、日期或字串，其中字串是依照字母排列順序來界定範圍。
BETWEEN 語法 (SQL BETWEEN Syntax)
SELECT table_column1, table_column2, table_column3...
FROM table_name
WHERE column_name
BETWEEN value1 AND value2;
BETWEEN 運算子查詢用法
(Example)
假設我們想從下面的 customers 資料表中查詢訂單數量介於
1000~4000 的顧客資料：
| C_Id |
Name |
City |
Address |
Phone |
Quantity |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
1000 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
3000 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
5000 |
我們可以這樣作 SQL 查詢：
SELECT * FROM customers
WHERE Quantity
BETWEEN 1000 AND 4000;
查詢後返回的結果會是：
| C_Id |
Name |
City |
Address |
Phone |
Quantity |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
1000 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
3000 |
NOT BETWEEN
相反的，NOT BETWEEN 就是不包含在這範圍裡的的資料我通通要了，如上例多加上
NOT：
SELECT * FROM customers
WHERE Quantity
NOT BETWEEN 1000 AND 4000;
查詢後返回的結果會是：
萬用字元 (SQL Wildcards)
萬用字元是與 LIKE
運算子一起搭配使用的，我們可以利用萬用字元來建立一個模式
(pattern)，進而依此模式為條件來進行資料查詢。
萬用字元種類
| 萬用字元 |
意義 |
| % |
用來代替「零個」至「多個」字元 |
| _ |
用來代替「一個」字元 |
| [charlist] |
用來代替「一個」在列舉範圍內的字元 |
| [^charlist] 或 [!charlist] |
用來代替「一個」不在列舉範圍內的字元 |
萬用字元用法 (Example)
| 模式 |
模式意義 |
| abc% |
所有以 abc 開頭的字串。如 abc、abcx 或 abcxxx
等字串皆符合此模式。 |
| %abc% |
所有包含 abc 的字串。如 abc、xxabcy 或 abcxxxx
等字串皆符合此模式。 |
| a_ |
以 a 為開頭的兩個字。如 ab、ac 或 ad
等字串皆符合此模式，但 a
就不符合囉，因為此模式限定 a
之後一定要接任一字元。 |
| a[bcd] |
以 a 為開頭其後接著 b 或 c 或 d 其中一個字元。如 ab、ac
或 ad 等字串皆符合此模式，但 ae 就不符合囉。 |
| a[^bcd] |
以 a 為開頭其後接著非 b 或 c 或 d
的任何一個字元。如 ax、ay 或 az
等字串皆符合此模式，但 ab 就不符合囉。 |
例如，找住在台北縣市的客戶：
SELECT * FROM customers WHERE Address LIKE '台北%';
(NOT) LIKE 運算子 (SQL
(NOT) LIKE Operator)
LIKE 運算子搭配 WHERE
子句可以依一特定模式
(Pattern) 為條件來搜尋資料表中的特定資料。
LIKE 語法 (SQL LIKE Syntax)
SELECT table_column1, table_column2, table_column3...
FROM table_name
WHERE column_name LIKE pattern;
LIKE 運算子查詢用法 (Example)
假設我們想從下面的 customers
資料表中取得住在台北縣市的顧客資料：
| C_Id |
Name |
Address |
Phone |
| 1 |
張一 |
台北市XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣ZZ路300號 |
07-12345678 |
| 4 |
陳四 |
台北縣AA路400號 |
02-87654321 |
我們可以使用這樣的 LIKE 查詢語句：
SELECT * FROM customers WHERE Address LIKE '台北%';
查詢結果如下：
| C_Id |
Name |
Address |
Phone |
| 1 |
張一 |
台北市XX路100號 |
02-12345678 |
| 4 |
陳四 |
台北縣AA路400號 |
02-87654321 |
NOT LIKE
相反的，NOT LIKE
就是不包含在條件裡的的資料我通通要了，如上例多加上 NOT：
SELECT * FROM customers
WHERE Address NOT LIKE '台北%';
查詢後返回的結果會是：
| C_Id |
Name |
Address |
Phone |
| 2 |
王二 |
新竹縣YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣ZZ路300號 |
07-12345678 |
AS (Alias、別名) (SQL Alias)
在 SQL 中我們可以替資料表或欄位名稱取一個別名 (Alias)，這可以使名稱複雜的
SQL 查詢語句更易讀且可以有更直觀的查詢結果。
AS 語法 -
資料表別名 (SQL AS Syntax for Tables)
SELECT table_column1, table_column2, table_column3...
FROM table_name AS alias_name;
AS 語法 -
欄位別名 (SQL AS Syntax for Columns)
SELECT table_column AS alias_name
FROM table_name;
SQL AS 別名用法 (Example)
假設我們想從下面的 orders 資料表中查詢資料：
| Customer_Name |
Product_Name |
Quantity |
| 張一 |
滑鼠 |
1000 |
| 王二 |
螢幕 |
2000 |
| 李三 |
印表機 |
500 |
| 陳四 |
螢幕 |
1500 |
我們可以這樣查詢：
SELECT Customer_Name AS Customer,
Product_Name AS Product,
Quantity
FROM orders;
查詢結果如下：
| Customer |
Product |
Quantity |
| 張一 |
滑鼠 |
1000 |
| 王二 |
螢幕 |
2000 |
| 李三 |
印表機 |
500 |
| 陳四 |
螢幕 |
1500 |
有注意到嗎？返回結果的欄位名稱變了。
再舉個例子，如果我們想取得各產品總銷售量可以這樣查詢：
SELECT o.Product, SUM(Quantity) AS Total
FROM orders AS o
GROUP BY o.Product;
查詢結果如下：
| Product |
Total |
| 滑鼠 |
1000 |
| 螢幕 |
3500 |
| 印表機 |
500 |
JOIN 連接 (SQL JOIN)
SQL JOIN (連接)
是利用不同資料表之間欄位的關連性來結合多資料表之檢索。
SQL JOIN
是結合多個資料表而組成一抽象的暫時性資料表以供資料查詢，在原各資料表中之紀錄及結構皆不會因此連接查詢而改變。
例如，這是一個客戶資料表 customers：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
而這是產品訂單的資料表 orders：
| O_Id |
OrderNo |
C_Id |
| 1 |
2572 |
3 |
| 2 |
7375 |
3 |
| 3 |
7520 |
1 |
| 4 |
1054 |
1 |
| 5 |
1257 |
5 |
其中，C_Id 是客戶資料表中的主鍵
(Primary Key)
欄位，我們怎麼將這兩張不同的資料表依相關欄位來作個連接結合以便查詢呢？這就是接下來的主題
Join！
SQL 的 JOIN
查詢有哪幾種類型？
- INNER JOIN
內部連接
- LEFT (OUTER) JOIN
左外部連接
- RIGHT (OUTER)
JOIN 右外部連接
- FULL (OUTER) JOIN
全部外部連接
- CROSS JOIN
交叉連接
- NATURAL JOIN
自然連接
INNER JOIN
關鍵字 (SQL INNER JOIN Keyword) - 內部連接
INNER JOIN (內部連接)
為等值連接，必需指定等值連接的條件，而查詢結果只會返回符合連接條件的資料。
INNER JOIN 語法 (SQL INNER
JOIN Syntax)
SELECT table_column1, table_column2...
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
或這樣寫：
SELECT table_column1, table_column2...
FROM table_name1
INNER JOIN table_name2
USING (column_name);
INNER JOIN 查詢用法 (Example)
這是一個客戶資料表 customers：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
而這是產品訂單的資料表 orders：
| O_Id |
Order_No |
C_Id |
| 1 |
2572 |
3 |
| 2 |
7375 |
3 |
| 3 |
7520 |
1 |
| 4 |
1054 |
1 |
| 5 |
1257 |
5 |
現在我們想列出所有客戶的訂單編號資料，我們可以作一個
INNER JOIN 查詢：
SELECT customers.Name, orders.Order_No
FROM customers
INNER JOIN orders
ON customers.C_Id=orders.C_Id;
其中用點號連接之 XXX.YYY 表示 XXX 資料表中的 YYY
欄位。
查詢結果如下：
| Name |
Order_No |
| 李三 |
2572 |
| 李三 |
7375 |
| 張一 |
7520 |
| 張一 |
1054 |
查詢結果只會返回符合連接條件的資料！
LEFT JOIN
關鍵字 (SQL LEFT JOIN Keyword) - 左外部連接
LEFT JOIN 可以用來建立左外部連接，查詢的 SQL 敘述句 LEFT
JOIN 左側資料表 (table_name1)
的所有記錄都會加入到查詢結果中，即使右側資料表
(table_name2) 中的連接欄位沒有符合的值也一樣。
LEFT JOIN 語法 (SQL LEFT JOIN
Syntax)
SELECT table_column1, table_column2...
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
有些資料庫的語法會是 LEFT OUTER JOIN。
LEFT JOIN 查詢用法 (Example)
這是一個客戶資料表 customers：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
而這是產品訂單的資料表 orders：
| O_Id |
Order_No |
C_Id |
| 1 |
2572 |
3 |
| 2 |
7375 |
3 |
| 3 |
7520 |
1 |
| 4 |
1054 |
1 |
| 5 |
1257 |
5 |
現在我們想查詢所有客戶與其訂單狀況的資料，我們可以作一個
LEFT JOIN 查詢：
SELECT customers.Name, orders.Order_No
FROM customers
LEFT JOIN orders
ON customers.C_Id=orders.C_Id;
查詢結果如下：
| Name |
Order_No |
| 張一 |
7520 |
| 張一 |
1054 |
| 王二 |
|
| 李三 |
2572 |
| 李三 |
7375 |
LEFT JOIN
會返回左側資料表中所有資料列，就算沒有符合連接條件，而右側資料表中如果沒有匹配的資料值就會顯示為
NULL。
RIGHT JOIN
關鍵字 (SQL RIGHT JOIN Keyword) - 右外部連接
相對於 LEFT JOIN，RIGHT
JOIN 可以用來建立右外部連接，查詢的 SQL 敘述句 RIGHT JOIN
右側資料表 (table_name2)
的所有記錄都會加入到查詢結果中，即使左側資料表
(table_name2) 中的連接欄位沒有符合的值也一樣。
RIGHT JOIN 語法 (SQL RIGHT
JOIN Syntax)
SELECT table_column1, table_column2···
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
有些資料庫的語法會是 RIGHT OUTER JOIN。
RIGHT JOIN 查詢用法 (Example)
這是一個客戶資料表 customers：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
而這是產品訂單的資料表 orders：
| O_Id |
Order_No |
C_Id |
| 1 |
2572 |
3 |
| 2 |
7375 |
3 |
| 3 |
7520 |
1 |
| 4 |
1054 |
1 |
| 5 |
1257 |
5 |
現在我們想查詢所有訂單與相應的客戶之資料，我們可以作一個
RIGHT JOIN 查詢：
SELECT customers.Name, orders.Order_No
FROM customers
RIGHT JOIN orders
ON customers.C_Id=orders.C_Id;
查詢結果如下：
| Name |
Order_No |
| 李三 |
2572 |
| 李三 |
7375 |
| 張一 |
7520 |
| 張一 |
1054 |
|
1257 |
RIGHT JOIN
會返回右側資料表中所有資料列，就算是沒有符合連接條件，而左側資料表中如果沒有匹配的資料值就會顯示為
NULL。
FULL JOIN
關鍵字 (SQL FULL JOIN Keyword) － 全部外部連接
FULL JOIN 即為 LEFT
JOIN 與 RIGHT
JOIN
的聯集，它會返回左右資料表中所有的紀錄，不論是否符合連接條件。
FULL JOIN 語法 (SQL FULL JOIN
Syntax)
SELECT table_column1, table_column2...
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
FULL JOIN 查詢用法 (Example)
這是一個客戶資料表 customers：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
而這是產品訂單的資料表 orders：
| O_Id |
Order_No |
C_Id |
| 1 |
2572 |
3 |
| 2 |
7375 |
3 |
| 3 |
7520 |
1 |
| 4 |
1054 |
1 |
| 5 |
1257 |
5 |
我們來作一個 FULL JOIN 查詢：
SELECT customers.Name, orders.Order_No
FROM customers
FULL JOIN orders
ON customers.C_Id=orders.C_Id;
查詢結果如下：
| Name |
Order_No |
| 李三 |
2572 |
| 李三 |
7375 |
| 張一 |
7520 |
| 張一 |
1054 |
|
1257 |
| 王二 |
|
MySQL 資料庫中沒有 FULL JOIN，但是可以用 UNION
來模擬。
CROSS JOIN
關鍵字 (SQL CROSS JOIN Keyword) - 交叉連接
交叉連接為兩個資料表間的笛卡兒乘積 (Cartesian product)，兩個資料表在結合時，不指定任何條件，即將兩個資料表中所有的可能排列組合出來，以下例而言
CROSS JOIN 出來的結果資料列數為 3×5=15 筆，因此，當有 WHERE、ON、USING
條件時不建議使用。
CROSS JOIN 語法 (SQL CROSS
JOIN Syntax)
SELECT table_column1, table_column2...
FROM table_name1
CROSS JOIN table_name2;
或這樣寫：
SELECT table_column1, table_column2...
FROM table_name1, table_name2;
或這樣寫：
SELECT table_column1, table_column2...
FROM table_name1
JOIN table_name2;
FULL JOIN 查詢用法 (Example)
這是一個客戶資料表 customers：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
而這是產品訂單的資料表 orders：
| O_Id |
Order_No |
C_Id |
| 1 |
2572 |
3 |
| 2 |
7375 |
3 |
| 3 |
7520 |
1 |
| 4 |
1054 |
1 |
| 5 |
1257 |
5 |
我們來作一個 CROSS JOIN 查詢：
SELECT customers.Name, orders.Order_No
FROM customers
CROSS JOIN orders;
查詢結果如下：
| Name |
Order_No |
| 張一 |
2572 |
| 王二 |
2572 |
| 李三 |
2572 |
| 張一 |
7375 |
| 王二 |
7375 |
| 李三 |
7375 |
| 張一 |
7520 |
| 王二 |
7520 |
| 李三 |
7520 |
| 張一 |
1054 |
| 王二 |
1054 |
| 李三 |
1054 |
| 張一 |
1257 |
| 王二 |
1257 |
| 李三 |
1257 |
NATURAL
JOIN 關鍵字 (SQL NATURAL JOIN Keyword) - 自然連接
自然連接有 NATURAL JOIN、NATURAL LEFT JOIN、NATURAL RIGHT JOIN，兩個表格在進行
JOIN 時，加上 NATURAL
這個關鍵字之後，兩資料表之間同名的欄位會被自動結合在一起。
NATURAL JOIN 語法 (SQL
NATURAL JOIN Syntax)
SELECT table_column1, table_column2...
FROM table_name1
NATURAL JOIN table_name2;
NATURAL JOIN 查詢用法 (Example)
這是一個客戶資料表 customers：
| C_Id |
Name |
City |
Address |
Phone |
| 1 |
張一 |
台北市 |
XX路100號 |
02-12345678 |
| 2 |
王二 |
新竹縣 |
YY路200號 |
03-12345678 |
| 3 |
李三 |
高雄縣 |
ZZ路300號 |
07-12345678 |
而這是產品訂單的資料表 orders：
| O_Id |
Order_No |
C_Id |
| 1 |
2572 |
3 |
| 2 |
7375 |
3 |
| 3 |
7520 |
1 |
| 4 |
1054 |
1 |
| 5 |
1257 |
5 |
現在我們想列出所有客戶的訂單編號資料，我們可以作一個
NATURAL JOIN 查詢：
SELECT customers.Name, orders.Order_No
FROM customers
NATURAL JOIN orders;
查詢結果如下：
| Name |
Order_No |
| 李三 |
2572 |
| 李三 |
7375 |
| 張一 |
7520 |
| 張一 |
1054 |
注意到了嗎？返回結果同等於下面這個 INNER
JOIN 查詢:
SELECT customers.Name, orders.Order_No
FROM customers
INNER JOIN orders
ON customers.C_Id=orders.C_Id;
UNION 運算子 (SQL UNION Operator)
UNION 運算子用來將兩個(以上) SQL
查詢的結果合併起來，而由 UNION 查詢中各別 SQL
語句所產生的欄位需要是相同的資料型別及順序。
UNION 查詢只會返回不同值的資料列，有如 SELECT
DISTINCT。
UNION 就是像是 OR (聯集)，如果紀錄存在於第一個查詢結果集或第二個查詢結果集中，就會被取出。
UNION 與 JOIN
不同的地方在於，JOIN 是作橫向結合 (合併多個資料表的各欄位)；而
UNION 則是作垂直結合 (合併多個資料表中的紀錄)。
UNION 語法 (SQL UNION Syntax)
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2;
UNION 查詢返回的資料集欄位名稱，通常會依據第一個 SELECT
查詢的欄位名稱。
UNION 運算子查詢用法
(Example)
這是公司在台灣地區銷售的產品資料表 products_taiwan：
| P_Id |
P_Name |
| 1 |
LCD |
| 2 |
CPU |
| 3 |
RAM |
這是公司在中國大陸地區銷售的產品資料表 products_china：
| P_Id |
P_Name |
| 1 |
Keyboard |
| 2 |
CPU |
| 3 |
LCD |
現在我們想查出公司所有產品類別的資料，我們可以作一個
UNION 查詢：
SELECT P_Name FROM products_taiwan
UNION
SELECT P_Name FROM products_china;
查詢結果如下：
| P_Name |
| LCD |
| CPU |
| RAM |
| Keyboard |
UNION ALL
相反的，UNION ALL 則會列出所有的資料，不論是否重複。
例如查詢：
SELECT P_Name FROM products_taiwan
UNION ALL
SELECT P_Name FROM products_china;
查詢結果如下：
| P_Name |
| LCD |
| CPU |
| RAM |
| Keyboard |
| CPU |
| LCD |
INTERSECT
運算子 (SQL
INTERSECT Operator)
相對於 UNION 像是 OR
(聯集)，INTERSECT 運算子則像是 AND (交集)，如果紀錄存在於第一個查詢結果集內同時亦存在於第二個查詢結果集內時，才會被取出。
INTERSECT 語法 (SQL INTERSECT
Syntax)
SELECT column_name(s) FROM table_name1
INTERSECT
SELECT column_name(s) FROM table_name2;
兩個 SELECT
查詢所產生的欄位需要是相同的資料型別及順序。
INTERSECT 查詢返回的資料集欄位名稱，通常會依據第一個
SELECT 查詢的欄位名稱。
INTERSECT 運算子查詢用法
(Example)
這是公司在台灣地區銷售的產品資料表 products_taiwan：
| P_Id |
P_Name |
| 1 |
LCD |
| 2 |
CPU |
| 3 |
RAM |
這是公司在中國大陸地區銷售的產品資料表 products_china：
| P_Id |
P_Name |
| 1 |
Keyboard |
| 2 |
CPU |
| 3 |
LCD |
現在我們想查出在台灣地區及中國大陸地區皆有銷售的產品類別的資料，我們可以作一個
INTERSECT 查詢：
SELECT P_Name FROM products_taiwan
INTERSECT
SELECT P_Name FROM products_china;
查詢結果如下：
MySQL 目前還不支援 INTERSECT。
MINUS 運算子 (SQL MINUS Operator)
當 MINUS 運算子結合了兩個 SELECT 查詢語句，它會將 (第一個查詢結果集)
減去 (同時存在於第一個查詢結果集與第二個查詢結果集的資料紀錄)，然後返回其結果。
MINUS 語法 (SQL MINUS Syntax)
SELECT column_name(s) FROM table_name1
MINUS
SELECT column_name(s) FROM table_name2;
MINUS 查詢返回的資料集欄位名稱，通常會依據第一個 SELECT
查詢的欄位名稱。
兩個 SELECT
查詢所產生的欄位需要是相同的資料型別及順序。
MINUS 運算子查詢用法
(Example)
這是公司在台灣地區銷售的產品資料表 products_taiwan：
| P_Id |
P_Name |
| 1 |
LCD |
| 2 |
CPU |
| 3 |
RAM |
這是公司在中國大陸地區銷售的產品資料表 products_china：
| P_Id |
P_Name |
| 1 |
Keyboard |
| 2 |
CPU |
| 3 |
LCD |
現在我們想查出有在台灣地區銷售但是沒有在中國大陸地區銷售的產品，我們可以作一個
MINUS 查詢：
SELECT P_Name FROM products_taiwan
MINUS
SELECT P_Name FROM products_china;
查詢結果如下：
MySQL 目前還不支援 MINUS。
子查詢 (SQL Subquery)
我們可以將一個 SQL 查詢語句塞入另一個 SQL
查詢語句中，這就是子查詢 (subquery)。子查詢是一個查詢內的查詢，我們可以用來連接資料表，或在不能使用單一語句來完成的查詢時我們就需要用到子查詢。
子查詢語法 (SQL Subquery
Syntax)
SELECT table_column1, table_column2, table_column3...
FROM table_name
WHERE 欄位名稱 比較運算子 (SELECT 子查詢);
內部的 SELECT 查詢稱為子查詢，而外部的 SELECT
查詢即稱為主查詢 (main query)。
子查詢用法 (Example)
我們現在利用子查詢來達到跟 MINUS
相同的查詢結果。
這是公司在台灣地區銷售的產品資料表 products_taiwan：
| P_Id |
P_Name |
| 1 |
LCD |
| 2 |
CPU |
| 3 |
RAM |
這是公司在中國大陸地區銷售的產品資料表 products_china：
| P_Id |
P_Name |
| 1 |
Keyboard |
| 2 |
CPU |
| 3 |
LCD |
現在我們想查出有在台灣地區銷售但是沒有在中國大陸地區銷售的產品，我們可以作一個子查詢：
SELECT P_Name FROM products_taiwan
WHERE P_Name NOT IN
(SELECT P_Name FROM products_china);
查詢結果如下：
(NOT) EXISTS
運算子
(SQL (NOT) EXISTS Operator)
EXISTS 運算子可以連接子查詢，用來判斷子查詢是否有返回的結果，如果有結果返回則為真、否則為假。若
EXISTS 為真，就會繼續執行外查詢中的 SQL；若 EXISTS
為假，則整個 SQL 查詢就不會返回任何結果。
NOT EXISTS 則是相對於 EXISTS，判斷為假才會繼續執行外查詢。
EXISTS 運算子用法 (Example)
我們以 IN 運算子來與
EXISTS 作一比較，下列兩個 SQL 查詢皆會返回同樣的結果：
SELECT * FROM table_a
WHERE EXISTS
(SELECT * FROM table_b WHERE table_b.id=table_a.id);
相當於：
SELECT * FROM table_a
WHERE id
in (SELECT id FROM table_b);
MySQL 目前還不支援 EXISTS。
CASE 關鍵字 (SQL CASE Keyword)
CASE 類似於程式語言裡的 if/then/else
語句，用來作邏輯判斷。
CASE 語法 (SQL CASE Syntax)
CASE
WHEN condition THEN result
[WHEN···]
[ELSE result]
END;
或是：
CASE expression
WHEN value THEN result
[WHEN···]
[ELSE result]
END;
若省略了 ELSE 子句且洽無相符的條件則會返回 NULL。
CASE 關鍵字用法 (Example)
假設我們作一個問卷調查：您喜歡這個網站嗎？ 1.喜歡 2.不喜歡 3.還OK
問卷結果資料表 questionnaire 如下：
| Name |
Answer |
| 張一 |
1 |
| 王二 |
2 |
| 李三 |
3 |
我們可以作以下這個 SQL 查詢：
select Name, case Answer
when 1 then '喜歡'
when 2 then '不喜歡'
when 3 then '還OK'
END
FROM questionnaire;
或是：
select Name, case
when Answer=1 then '喜歡'
when Answer=2 then '不喜歡'
when Answer=3 then '還OK'
END
AS Answer
FROM questionnaire;
查詢結果如下：
| Name |
Answer |
| 張一 |
喜歡 |
| 王二 |
不喜歡 |
| 李三 |
還OK |
SQL 函數
函數 (SQL Functions)
SQL 主要有三大類內建函數，分別是聚合函數 (Aggregate
Functions)、字串函數 (String Functions) 與數學運算函數 (Mathematical
Functions)。
聚合函數 (SQL Aggregate
Functions)
字串函數 (SQL String Functions)
數值函數 (SQL Mathematical
Functions)
AVG() 函數 (SQL AVG() Function)
AVG() 函數用來計算一數值欄位的平均值。
AVG() 語法 (SQL AVG() Syntax)
SELECT AVG(column_name) FROM table_name;
AVG() 函數查詢用法 (Example)
假設我們想從下面的 students
資料表中查詢學生的平均身高：
| S_Id |
Name |
Height |
| 1 |
張一 |
170 |
| 2 |
王二 |
176 |
| 3 |
李三 |
173 |
我們可以下這個 SELECT 查詢敘述句：
SELECT AVG(Height) FROM students;
返回的結果如下：
COUNT() 函數 (SQL COUNT() Function)
COUNT() 函數用來計算符合查詢條件的欄位紀錄總共有幾筆。
COUNT() 語法 (SQL COUNT() Syntax)
SELECT COUNT(column_name) FROM table_name;
若欄位值為 NULL，則該筆記錄不會被 COUNT 計算進去。
COUNT() 函數查詢用法 (Example)
假設我們想從下面的 orders 資料表中查詢 "張一"
總共有幾筆訂單：
| O_Id |
Price |
Customer |
| 1 |
1000 |
張一 |
| 2 |
2000 |
王二 |
| 3 |
500 |
李三 |
| 4 |
1300 |
張一 |
| 5 |
1800 |
王二 |
我們可以下這個 SELECT 查詢敘述句：
SELECT COUNT(Customer) FROM orders WHERE Customer='張一';
返回的結果如下：
COUNT(*)
COUNT(*) 是用來計算資料表中記錄的總筆數。
SELECT COUNT(*) FROM table_name;
以上例，如果我們想查詢 orders
資料表中有多少筆資料，可以這樣查詢：
SELECT COUNT(*) FROM orders;
查詢的結果如下：
COUNT(DISTINCT column_name)
COUNT 配合 DISTINCT
可以用來找出資料表中有多少筆不相同的資料 。
SELECT COUNT(DISTINCT column_name) FROM table_name;
以上例，如果我們想查詢 orders
資料表中有多少位不同的顧客，SQL 查詢如下：
SELECT COUNT(DISTINCT Customer) FROM orders;
返回的結果如下：
| COUNT(DISTINCT Customer) |
| 3 |
MAX() 函數 (SQL MAX() Function)
MAX() 函數用來取得特定欄位中的最大紀錄值。
MAX() 語法 (SQL MAX() Syntax)
SELECT MAX(column_name) FROM table_name;
MAX() 函數查詢用法 (Example)
假設我們想從下面的 orders
資料表中查詢單筆訂單的最高金額：
| O_Id |
Price |
Customer |
| 1 |
1000 |
張一 |
| 2 |
2000 |
王二 |
| 3 |
500 |
李三 |
| 4 |
1300 |
張一 |
| 5 |
1800 |
王二 |
我們可以下這個 SELECT 查詢敘述句：
SELECT MAX(Price) FROM orders;
返回的結果如下：
MIN() 函數 (SQL MIN() Function)
MIN() 函數用來取得特定欄位中的最小紀錄值。
MIN() 語法 (SQL MIN() Syntax)
SELECT MIN(column_name) FROM table_name;
MIN() 函數查詢用法 (Example)
假設我們想從下面的 orders
資料表中查詢單筆訂單的最低金額：
| O_Id |
Price |
Customer |
| 1 |
1000 |
張一 |
| 2 |
2000 |
王二 |
| 3 |
500 |
李三 |
| 4 |
1300 |
張一 |
| 5 |
1800 |
王二 |
我們可以下這個 SELECT 查詢敘述句：
SELECT MIN(Price) FROM orders;
返回的結果如下：
SUM() 函數 (SQL SUM() Function)
SUM() 函數用來計算一數值欄位的總合。
SUM() 語法 (SQL SUM() Syntax)
SELECT SUM(column_name) FROM table_name;
SUM() 函數查詢用法 (Example)
假設我們想從下面的 orders
資料表中查詢所有訂單的金額總合：
| O_Id |
Price |
Customer |
| 1 |
1000 |
張一 |
| 2 |
2000 |
王二 |
| 3 |
500 |
李三 |
| 4 |
1300 |
張一 |
| 5 |
1800 |
王二 |
我們可以下這個 SELECT 查詢敘述句：
SELECT SUM(Price) FROM orders;
返回的結果如下：
GROUP BY 敘述句 (SQL GROUP
BY Statement)
GROUP BY 敘述句搭配聚合函數 (aggregation function)
使用，是用來將查詢結果中特定欄位值相同的資料分為若干個群組，而每一個群組都會傳回一個資料列。若沒有使用
GROUP BY，聚合函數針對一個 SELECT
查詢，只會返回一個彙總值。
聚合函數指的也就是 AVG()、COUNT()、MAX()、MIN()、SUM()
等這些內建函數。
GROUP BY 語法 (SQL GROUP BY
Syntax)
SELECT column_name(s), aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name1, column_name2...;
GROUP BY 查詢用法 (Example)
假設我們想從下面的 orders
資料表中查詢個別顧客的訂單金額總合：
| O_Id |
Price |
Customer |
| 1 |
1000 |
張一 |
| 2 |
2000 |
王二 |
| 3 |
500 |
李三 |
| 4 |
1300 |
張一 |
| 5 |
1800 |
王二 |
我們可以下這個 SELECT 查詢敘述句：
SELECT Customer, SUM(Price) FROM orders
GROUP BY Customer;
返回的結果如下：
| Customer |
SUM(Price) |
| 張一 |
2300 |
| 王二 |
3800 |
| 李三 |
500 |
了解 GROUP BY 的用途了嗎？它將 Customer
欄位值相同的資料都分作同一組來計算 (加總) 囉！
若 GROUP BY
後面指定兩個以上的欄位時，則要符合所有欄位值皆相同資料才會被分為一組。
HAVING 子句 (SQL HAVING Clause)
HAVING 子句是用來取代 WHERE
搭配聚合函數 (aggregate function) 進行條件查詢，因為
WHERE 不能與聚合函數一起使用。
聚合函數指的也就是 AVG()、COUNT()、MAX()、MIN()、SUM()
等這些內建函數。
HAVING 語法 (SQL HAVING Syntax)
SELECT column_name(s), aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name1, column_name2...
HAVING aggregate_function(column_name) operator value;
HAVING 子句查詢用法 (Example)
假設我們想從下面的 orders 資料表中查詢訂單金額總合小於
1000 的顧客：
| O_Id |
Price |
Customer |
| 1 |
1000 |
張一 |
| 2 |
2000 |
王二 |
| 3 |
500 |
李三 |
| 4 |
1300 |
張一 |
| 5 |
1800 |
王二 |
我們可以下這個 SELECT 查詢敘述句：
SELECT Customer, SUM(Price) FROM orders
GROUP BY Customer
HAVING SUM(Price)<1000;
返回的結果如下：
| Customer |
SUM(Price) |
| 李三 |
500 |
ASCII() 函數 (SQL ASCII() Function)
ASCII() 函數是用來取得字元的 ASCII 碼。相對於 ASCII()
的函數為 CHAR()。
ASCII() 語法 (Syntax)
ASCII(字元);
ASCII() 函數查詢用法 (Example)
我們這樣查詢：
SELECT ASCII('a'),
ASCII('A');
可以得到結果：
| ASCII('a') |
ASCII('A') |
| 97 |
65 |
CHAR() 函數 (SQL CHAR() Function)
CHAR() 函數可以依 ASCII 碼取得其相對應之字元。相對於
CHAR() 的函數為 ASCII()。
CHAR() 語法 (Syntax)
CHAR(ASCII碼);
CHAR() 函數查詢用法 (Example)
我們這樣查詢：
SELECT CHAR(65),
CHAR(97);
可以得到結果：
| CHAR('65') |
CHAR('97') |
| A |
a |
CONCAT() 函數 (SQL CONCAT()
Function)
CONCAT() 函數用來合併多個欄位的值。
MySQL 語法 - CONCAT(str1, str2,...)
假設我們有一個 customers 資料表：
我們這樣查詢：
SELECT CONCAT(C_Id, '-', Name) FROM customers;
可以得到結果：
| CONCAT(C_Id, Name) |
| 1-Smith |
| 2-Brad |
SQL Server 語法 +
SQL Server 是用加號 + 來作合併。
同上例，我們可以這樣：
SELECT C_Id + '-' + Name FROM customers;
得到結果：
| CONCAT(C_Id, Name) |
| 1-Smith |
| 2-Brad |
Oracle 語法 - CONCAT(), ||
Oracle 跟 MySQL 一樣都有 CONCAT()
這個函數，但它最多只能輸入兩個參數。在 Oracle
中如果您想合併多資料值，可以使用 ||。
同上例，我們可以如此：
SELECT C_Id || '-' || Name FROM customers;
返回的結果如下：
| CONCAT(C_Id, Name) |
| 1-Smith |
| 2-Brad |
LENGTH()、LEN() 函數 (SQL
LENGTH()、LEN() Function)
LENGTH()、LEN() 函數是用來取得字串長度。
LENGTH()、LEN() 語法 (Syntax)
SELECT LENGTH(column_name) FROM table_name;
MySQL 函數名稱是用 LENGTH()；Oracle一樣用 LENGTH()；SQL Server
則是用 LEN()。
LENGTH()、LEN()
函數查詢用法 (Example)
例如，下面是一個 customers 資料表：
我們如此下 SQL：
SELECT LENGTH(Name) FROM customers;
可以得到：
REPLACE() 函數 (SQL REPLACE()
Function)
REPLACE() 函數用來以新字串取代原字串內容。
REPLACE() 語法 (SQL REPLACE()
Syntax)
SELECT REPLACE(str, from_str, to_str) FROM table_name;
函數意義為，在字串 str 中，將所有字串 from_str，取代為字串
to_str。
REPLACE() 函數查詢用法
(Example)
假設我們有一個 customers 資料表：
我們可以如此：
SELECT REPLACE(Name, 'Smith', 'ReplaceSmith') FROM customers;
返回的結果如下：
| REPLACE(Name, 'Smith', 'ReplaceSmith') |
| ReplaceSmith |
| Brad |
UCASE() 函數 (SQL UCASE() Function)
UCASE() 函數用來將英文字母轉換為大寫。
UCASE() 語法 (SQL UCASE() Syntax)
SELECT UCASE(column_name) FROM table_name;
UCASE() 函數用法 (Example)
假設我們想從下面的 customers
資料表中查詢顧客姓名並以大寫返回結果：
我們可以這樣寫：
SELECT UCASE(Name) FROM customers;
返回的結果如下：
LCASE() 函數 (SQL LCASE() Function)
LCASE() 函數用來將英文字母轉換為小寫。
LCASE() 語法 (SQL LCASE() Syntax)
SELECT LCASE(column_name) FROM table_name;
LCASE() 函數用法 (Example)
假設我們想從下面的 customers
資料表中查詢顧客姓名並以小寫返回結果：
我們可以這樣寫：
SELECT LCASE(Name) FROM customers;
返回的結果如下：
MID() 函數 (SQL MID() Function)
MID() 函數用來截取字串欄位值。
MID() 語法 (SQL MID() Syntax)
SELECT MID(column_name, start [,length]) FROM table_name;
從字串索引值 start 開始截取共 length 長度的字串。沒指定
length 則取到尾。而 start 是從 1 開始。
MID() 函數用法 (Example)
假設我們想從下面的 customers
資料表中取得顧客姓名的前兩個字：
我們可以這樣寫：
SELECT MID(Name, 1, 2) FROM customers;
返回的結果如下：
ABS() 函數 (SQL ABS() Function)
ABS() 函數用來取得絕對值。
ABS() 語法 (Syntax)
ABS(數值);
ABS() 函數用法 (Example)
我們如此 SQL：
SELECT ABS(-20), ABS(20);
得到結果：
CEIL()、CEILING() 函數
(SQL CEIL()、CEILING() Function)
CEIL()、CEILING() 函數是用來無條件進入數值。
CEIL()、CEILING() 語法 (Syntax)
CEIL(數值);
MySQL 函數名稱是用 CEIL()；Oracle 一樣用 CEIL()；SQL Server
則是用 CEILING()。
CEIL()、CEILING() 函數用法
(Example)
我們這樣寫：
SELECT CEIL(-2.1), CEIL(2.1);
可以得到結果：
| CEIL(-2.1) |
CEIL(2.1) |
| -2 |
3 |
FLOOR() 函數 (SQL FLOOR() Function)
FLOOR() 函數用來無條件捨去數值。
FLOOR() 語法 (Syntax)
FLOOR(數值);
FLOOR() 函數用法 (Example)
我們這樣 SQL：
SELECT FLOOR(-10.3), FLOOR(10.3);
可以得到結果：
| FLOOR(-10.3) |
FLOOR(10.3) |
| -11 |
10 |
POWER() 函數 (SQL POWER() Function)
POWER() 函數是用來計算並取得數值的 N 次方值。
POWER() 語法 (Syntax)
POWER(數值, N次方);
POWER() 函數用法 (Example)
我們如此 SQL：
SELECT POWER(10, 2);
可以得到結果：
ROUND() 函數 (SQL ROUND() Function)
ROUND()
函數用來對數值欄位值進行四捨五入計算。
ROUND() 語法 (SQL ROUND() Syntax)
SELECT ROUND(column_name, decimals) FROM table_name;
decimals 用來設定要四捨五入到小數點第幾位，0
表示個位數。
ROUND() 函數用法 (Example)
假設我們想從下面的 orders
資料表中查詢各筆訂單的金額，並四捨五入去除小數點位數：
| O_Id |
Price |
| 1 |
1000.45 |
| 2 |
2000.14 |
| 3 |
500.68 |
我們可以下這個 SELECT 查詢敘述句：
SELECT ROUND(Price, 0) FROM orders;
返回的結果如下：
| ROUND(Price, 0) |
| 1000 |
| 2000 |
| 501 |
SQRT() 函數 (SQL SQRT() Function)
SQRT() 函數是用來計算並取得數值的平方根。
SQRT() 語法 (Syntax)
SQRT(數值);
SQRT() 函數用法 (Example)
我們這樣下 SQL：
SELECT SQRT(100);
可以得到結果：
PI() 函數 (SQL PI() Function)
PI() 函數是用來取得圓周率。
PI() 語法 (Syntax)
PI();
PI() 函數用法 (Example)
我們這樣下 SQL：
SELECT PI();
可以得到：
EXP() 函數 (SQL EXP() Function)
EXP() 函數可以用來求得以自然對數 e 為基底的指數值。
EXP() 語法 (Syntax)
EXP(次方);
EXP() 函數用法 (Example)
我們如此 SQL：
SELECT EXP(1), EXP(10);
可以得到：
| EXP(1) |
EXP(10) |
| 2.718281828459 |
22026.465794807 |
LOG() 函數 (SQL LOG() Function)
LOG() 函數可以用來作對數運算。
LOG() 語法 (Syntax)
LOG(基底, 數值);
另外，還有這些類似的函數可以用：
LN(數值);
LOG2(數值);
LOG10(數值);
LOG() 函數用法 (Example)
我們如此 SQL：
SELECT LOG(10, 100),
LOG2(4),
LOG10(1000),
LN(1);
可以得到：
| LOG(10, 100) |
LOG2(4) |
LOG10(1000) |
LN(1) |
| 2 |
2 |
3 |
0 |
三角函數 (SQL Trigonometric
Function)
SQL 內建許多函數來幫助您進行三角運算。
SIN() & ASIN() 語法 (Syntax)
SIN(弧度);
ASIN(正弦值);
我們這樣下 SQL：
SELECT SIN(1.2), ASIN(-0.6);
可以得到：
| SIN(1.2) |
ASIN(-0.6) |
| 0.93203908596723 |
-0.64350110879328 |
COS() & ACOS() 語法 (Syntax)
COS(弧度);
ACOS(餘弦值);
我們如此 SQL：
SELECT COS(1.2), ACOS(-0.6);
可以得到：
| COS(1.2) |
ACOS(-0.6) |
| 0.36235775447667 |
2.2142974355882 |
TAN() & ATAN() & COT() 語法
(Syntax)
TAN(弧度);
ATAN(正切值);
COT(弧度);
我們這樣下 SQL：
SELECT TAN(1.2), ATAN(-0.6), COT(1);
可以得到：
| TAN(1.2) |
ATAN(-0.6) |
cot(1) |
| 2.5721516221263 |
-0.54041950027058 |
0.64209261593433 |
DEGREE() & RADIANS() 語法 (Syntax)
DEGREE(弧度);
RADIANS(角度);
我們這樣下 SQL：
SELECT RADIANS(180), DEGREES(PI());
可以得到：
| RADIANS(180) |
DEGREES(PI()) |
| 3.1415926535898 |
180 |
NOW() 函數 (SQL NOW() Function)
NOW() 函數用來取得目前的日期時間。
NOW() 語法 (SQL NOW() Syntax)
SELECT NOW() FROM table_name;
NOW() 函數用法 (Example)
我們下這個 SQL 查詢：
SELECT NOW();
可以得到結果：
| Now() |
| 2016-06-08 12:32:53 |
資料控制 DCL
SQL CREATE USER
建立資料庫使用者帳號
要連線並登入到資料庫，我們必需要有一組帳號和密碼。
建立資料庫使用者語法
(Syntax)
MySQL
CREATE USER 'username'@'hostname' IDENTIFIED BY '密碼';
hostname
表示允許這帳號能從什麼地方連線到資料庫。localhost
表示只允許從本地端登入；%
是萬用字元，表示允許從任何地方登入。
例如：
CREATE USER 'mike'@'%' IDENTIFIED BY 'hello123';
建立新帳號 mike 並設定其密碼為 hello123，且允許從任何地方登入
(從本地端或遠端連線都可)。
注意！建立新帳號後，這帳號預設沒有權限可以對資料庫做任何事，接著你必須授與資料庫使用權限給這帳號。
SQL DROP USER
刪除資料庫使用者帳號
如果你想刪除一個使用者帳號，禁止再用這帳號登入到資料庫。
語法 (Syntax)
MySQL
DROP USER 'username'@'hostname';
用法例如：
DROP USER 'mike'@'%';
SQL GRANT
授與資料庫使用權限
建立一個新帳號後，你要授與資料庫使用權限給這位使用者，這帳號才能開始連線進去資料庫操作。
GRANT 語法 (Syntax)
MySQL
GRANT type_of_permission ON database_name.table_name TO 'username'@'hostname';
hostname
表示允許這帳號能從什麼地方連線到資料庫。localhost
表示只允許從本地端登入；%
是萬用字元，表示允許從任何地方登入。
例如，授與 mike
所有資料庫和所有資料表的所有操作權限：
GRANT ALL PRIVILEGES ON *.* TO 'mike'@'%';
用上面指令授與權限後，要記得下這個指令讓權限開始生效：
FLUSH PRIVILEGES;
MySQL 有這些常見的權限類型：
- ALL PRIVILEGES - 所有的權限
- CREATE - 可以建立資料表或資料庫的權限
- DROP - 可以刪除資料表或資料庫的權限
- DELETE - 可以在資料表中刪除資料的權限
- INSERT - 可以新增資料到資料表的權限
- SELECT - 可以查詢資料表的權限
- UPDATE - 可以更新資料表中的資料的權限
- GRANT OPTION - 可以授權使用權限給其他使用者的權限
你可以用逗點分隔多個權限：
GRANT SELECT,INSERT ON customers.* TO 'mike'@'%';
SQL REVOKE
撤銷資料庫使用權限
你隨時可以撤銷使用者的資料庫使用權限。
REVOKE 語法 (Syntax)
MySQL
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'hostname';
用法例如：
REVOKE ALL PRIVILEGES FROM 'mike'@'%';
用上面指令撤銷權限後，要記得下這個指令讓撤銷權限開始生效：
FLUSH PRIVILEGES;
我們並介紹如何用 SQL 來做出以下的運算：
排名 (Rank)
中位數 (Median)
累積總計 (Running Total)
總合百分比 (Percent to Total)
累積總合百分比 (Cumulative Percent to Total)
排名 (Rank)
列出每一行的排名是一個常見的需求，可惜 SQL
並沒有一個很直接的方式達到這個需求。要以 SQL
列出排名，基本的概念是要做一個表格自我連結 (Self Join)，將結果依序列出，然後算出每一行之前
(包含那一行本身)
有多少行數。這樣講讀者聽得可能有點困惑，所以最好的方式是用一個實例來介紹。假設我們有以下的表格：
Total_Sales 表格
| John |
10 |
| Jennifer |
15 |
| Stella |
20 |
| Sophia |
40 |
| Greg |
50 |
| Jeff |
20 |
要找出每一行的排名，我們就打入以下的 SQL 語句：
SELECT a1.Name, a1.Sales, COUNT(a2.Sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales OR (a1.Sales=a2.Sales AND a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;
結果:
| Name |
Sales |
Sales_Rank |
| Greg |
50 |
1 |
| Sophia |
40 |
2 |
| Stella |
20 |
3 |
| Jeff |
20 |
3 |
| Jennifer |
15 |
5 |
| John |
10 |
6 |
我們先來看 WHERE
子句。在字句的第一部分 (a1.Sales <= a2.Sales)，我們算出有多少筆資料
Sales 欄位的值是比自己本身的值小或是相等。如果在 Sales
欄位中沒有同樣大小的資料，那這部分的 WHERE
子句本身就可以產生出正確的排名。
子句的第二部分，(a1.Sales=a2.Sales AND a1.Name = a2.Name)，則是讓我們在
Sales 欄位中有同樣大小的資料時 (像 Stella 及 Jeff 這兩筆資料)，仍然能夠產生正確的排名。
中位數 (Median)
要算出中位數，我們必須要能夠達成以下幾個目標：
- 將資料依序排出，並找出每一行資料的排名。
- 找出『中間』的排名為何。舉例來說，如果總共有 9
筆資料，那中間排名就是 5 (有 4 筆資料比第 5
筆資料大，有 4 筆資料比第 5 筆資料小)。
- 找出中間排名資料的值。
來看看以下的例子。假設我們有以下的表格：
Total_Sales 表格
| John |
10 |
| Jennifer |
15 |
| Stella |
20 |
| Sophia |
40 |
| Greg |
50 |
| Jeff |
20 |
要找出中位數，我們就鍵入：
SELECT Sales Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales);
結果:
讀者將會發現，第 2 行到第 6 行是跟產生 排名
的語句完全一樣。第 7 行則是算出中間的排名。DIV 是在 MySQL
中算出商的方式。在不同的資料庫中會有不同的方式求商。第
1 行則是列出排名中間的資料值。
累積總計 (Running Total)
算出累積總計是一個常見的需求，可惜以 SQL
並沒有一個很直接的方式達到這個需求。要以 SQL
算出累積總計，基本上的概念與列出排名類似：第一是先做個表格自我連結
(Self Join)，然後將結果依序列出。在做列出排名時，我們算出每一行之前
(包含那一行本身)
有多少行數；而在做累積總計時，我們則是算出每一行之前 (包含那一行本身)
的總合。
來看看以下的例子。假設我們有以下的表格：
Total_Sales 表格
| John |
10 |
| Jennifer |
15 |
| Stella |
20 |
| Sophia |
40 |
| Greg |
50 |
| Jeff |
20 |
要算出累積總計，我們就鍵入以下的 SQL 語句：
SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales OR (a1.Sales=a2.Sales AND a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;
結果:
| Name |
Sales |
Running_Total |
| Greg |
50 |
50 |
| Sophia |
40 |
90 |
| Stella |
20 |
110 |
| Jeff |
20 |
130 |
| Jennifer |
15 |
145 |
| John |
10 |
155 |
在以上的 SQL 語句中， WHERE
子句和 ORDER BY
子句讓我們能夠在有重複值時能夠算出正確的累積總計。
總合百分比 (Percent to Total)
要用 SQL
算出總合百分比，我們需要用到算排名和累積總計的概念，以及運用子查詢的做法。在這裡，我們把子查詢放在外部查詢的
SELECT
子句中。讓我們來看以下的例子：
Total_Sales 表格
| John |
10 |
| Jennifer |
15 |
| Stella |
20 |
| Sophia |
40 |
| Greg |
50 |
| Jeff |
20 |
要算出總合百分比，我們鍵入以下的 SQL 語句：
SELECT a1.Name, a1.Sales, a1.Sales/(SELECT SUM(Sales)
FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales OR (a1.Sales=a2.Sales AND a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;
結果:
| Name |
Sales |
Pct_To_Total |
| Greg |
50 |
0.3226 |
| Sophia |
40 |
0.2581 |
| Stella |
20 |
0.1290 |
| Jeff |
20 |
0.1290 |
| Jennifer |
15 |
0.0968 |
| John |
10 |
0.0645 |
SELECT SUM(Sales) FROM Total_Sales
這一段子查詢是用來算出總合。總合算出後，我們就能夠將每一行一一除以總合來求出每一行的總合百分比。
積總合百分比 (Cumulative Percent to Total)
要用 SQL 累積總合百分比算出，我們運用類似總合百分比的概念。兩者的不同處在於在這個情況下，我們要算出到目前為止的累積總合是所有總合的百分之幾，而不是光看每一筆資料是所有總合的百分之幾。讓我們來看看以下的例子：
Total_Sales 表格
| John |
10 |
| Jennifer |
15 |
| Stella |
20 |
| Sophia |
40 |
| Greg |
50 |
| Jeff |
20 |
要算出累積總合百分比，我們鍵入：
SELECT a1.Name, a1.Sales, SUM(a2.Sales)/(SELECT
SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;
結果:
| Name |
Sales |
Pct_To_Total |
| Greg |
50 |
0.3226 |
| Sophia |
40 |
0.5806 |
| Stella |
20 |
0.7097 |
| Jeff |
20 |
0.8387 |
| Jennifer |
15 |
0.9355 |
| John |
10 |
1.0000 |
SELECT SUM(Sales) FROM Total_Sales
這一段子查詢是用來算出總合。我們接下來用累積總計 SUM(a2.Sales)
除以總合來求出每一行的累積總合百分比。
MySQL
語法匯整
接觸 MySQL
多年的凍仁一直以來都沒有好好的指令記完，一般都會使用
phpMyAdmin 這類的 GUI 來操作 MySQL，所以把常用的 select, insert,
update, drop
背熟就偷笑了！可只有這些對凍仁而言是不夠的，當 GUI
失靈時還是得依賴指令來完成任務，再還未精通前就讓凍仁在自家基地放本小抄了。
1. 基礎語法
| 操作功能 |
SQL 語法 |
說明
|
| 建立資料庫 |
create database 資料庫名稱; |
|
| 列出所有資料庫 |
show databases; |
|
| 刪除資料庫 |
drop database 資料庫名稱; |
|
| 使用資料庫 |
use 資料庫名稱; |
|
| 建立資料表 |
create table 資料表名稱(
sn integer auto_increment primary key,
name char(20),
mail char(50),
home char(50),
messages char(50)
); |
常用資料庫資料型態
1. INT (整數)
2. CHAR (1~255字元字串)
3. VARCHAR (不超過255字元不定長度字串)
4. TEXT (不定長度字串最多65535字元)
|
| 列出資料表欄位資訊 |
describe 資料表名稱; |
|
| 修改資料表欄位 |
alter table 資料表名稱
change column 原來欄位名稱
新欄位名稱資料型態; |
|
| 新增資料表欄位 |
alter table 資料表名稱 add column 欄位名稱 資料型態; |
|
| 刪除資料表欄位 |
alter table 資料表名稱 drop column 欄位名稱; |
|
| 刪除資料表 |
drop table 資料表名稱; |
|
| 清空資料表 |
truncate table 資料表名稱; |
只清除資料並保留結構、欄位、索引 … |
| 插入欄位資料 |
insert into 資料表名稱(欄位1,欄位2,欄位3,欄位4, ......
欄位N)
values('值1','值2','值3','值4', ...... '值N'); |
|
| 更新修改欄位資料 |
update 資料表名稱 set 欄位1='值1',欄位2='值2',欄位3='值3',...
欄位N='值N'
where 條件式 (例如 sn='5' 或 name='塔司尼' ); |
|
| 查詢單一欄位資料 |
select 欄位名 from 資料表名稱; |
|
| 查詢多個欄位資料 |
select 欄位名, 欄位名, 欄位名 from 資料表名稱; |
|
| 查詢欄位資料的唯一值 |
select distinct 欄位名 from
資料表名稱; |
重複值只列一次 |
| 查詢所有欄位資料 |
select * from 資料表名稱; |
|
| 條件式查詢 |
select * from 資料表名稱 where 條件式 (例如 sn='5'); |
（=, <, >, !=） |
| 條件式查詢 and |
select * from 資料表名稱 where 條件式1 and
條件式2; |
|
| 條件式查詢 or |
select * from 資料表名稱 where 條件式1 or
條件式2; |
|
| 查詢某一範圍 between |
select * from 資料表名稱 where 欄位名 between
值1 and 值2; |
值為數字 |
| 查詢空值欄位的資料 |
select * from 資料表名稱 where 欄位名 is
null |
not null; |
| 查詢特定筆數資料 |
select * from 資料表名稱 limit 8, 10; |
第9筆開始選取10筆 |
| 查詢結果遞增排序 |
select * from 資料表名稱 order by
欄位名; |
|
| 查詢結果遞減排序 |
select * from 資料表名稱 order by
欄位名 desc ; |
|
| 查詢比對字串列出單一欄位 |
select 欄位名 from 資料表名稱 where 欄位名 like
'%字串%'; |
|
| 查詢比對字串列出所有欄位 |
select * from 資料表名稱 where 欄位名 like
'%字串%'; |
|
| 刪除條件值資料 |
delete from 資料表名稱 where 條件式 (例如 sn='5' 或
id='91001' ); |
|
| 刪除條件值資料 |
delete from 資料表名稱 where 條件式1
and 條件式2; |
|
| 刪除條件值資料 |
delete from 資料表名稱 where 條件式1 or
條件式2; |
|
| 比對刪除條件值資料 |
delete from 資料表名稱 where 欄位名 like
'%字串%'; |
|
2. 進階語法
| 操作功能 |
SQL 語法 |
說明
|
| 查看正在執行的行程 |
show processlist; |
|
| 查看 Master 狀態 |
show master status; |
|
| 查看 Slave 狀態 |
show slave status\G; |
|
| 查看 binlog 狀態 |
show binary logs; |
|
| 清除過時的 binlog |
purge binary logs to 'mysql-bin.000006'; |
將刪除 mysql-bin.000001 ~ 5 的 binlog |
3. mysqldump － MySQL 匯出語法
mysqldump 是個匯出 Database (資料庫) 的指令，也是 IT
人員最常用到的 MySQL 資料庫匯出方法之一。
[ jonny@trusty
~ ]
$ mysqldump -u root -p 資料庫名稱 > database_name.sql [Enter]
- --all-databases: 匯出所有資料庫。
- --no-data: 不匯出資料。
- --routine: 匯出 stored routines (預存程序)
和自訂函數。
- --single-transaction:
該選項從伺服器轉儲數據之前發出一個BEGIN SQL語句。
- --skip-lock-tables: 不鎖定 table (資料表)。
- --trigger: 匯出 trigger (觸發器)。
站內連結：
★ MySQL
大學筆記 on Windows XP
相關連結：
★ 刪除整個資料表，使用
Delete, Truncate 與 Drop 的差異 @ 月神的咖啡館
★ How
to setup MySQL Master/Slave Replication with existing data — The WP Guru
★ MySQL
5.1 Reference Manual
資料來源：
★ MySQL
基礎語法手冊 | InspireGate 派克空間
★ MySQL 5.1 mysqldump
導出備份 | IT 客
★ Jax
的工作紀錄: (轉載)mysqldump 5.1 資料備份詳細指令 [MySQL]
★ mysqldump
備份還原和導入匯出語句大全詳解 @ 資訊園