SQL基礎語句入門
SQL語句入門
起因
學校開設資料庫相關的課程了，打算總結一篇關於基礎SQL語句的文章。
SQL介紹
SQL最早版本是由IBM開發的，一直發展到至今。
SQL語言有如下幾個部分：
- 數據定義語言DDL：提供定義關係模式、刪除關係以及修改關係模式的命令
- 數據操縱語言DML：提供從資料庫中查詢資訊以及在資料庫中插入元組、刪除元組以及修改元組的能力
- 完整性：SQL DDL包括定義完整性的約束命令
- 視圖定義：SQL DDL包括定義視圖的命令
- 事務控制：SQL包括定義事務的開始點和結束點命令
- 嵌入式SQL和動態SQL：定義如何嵌入如C++等這樣的程式語言
- 授權：SQL DDL包括定義對關係和視圖的訪問許可權的命令
SQL標準支援多種固有類型：
- char(n) 具有用戶指定長度為n的字元串
- varchar(n) 最大長度為n的可變長字元串
- int 整數
- smallint 小正數(依賴於機器的整數類型的子集)
- numeric(p,d) 指定精度的定點數。此數字有p位(加上一個符號位)小數點右邊有p位數中的d位數字
- real 單精度
- double 雙精度
- float(n) 精度至少為n的浮點數
SQL語句入門
首先說明一下，SQL語句後面的分號是可選的。
創建關係
CREATE TABLE student
(
Sname VARCHAR(20),
Sid INT,
PRIMARY KEY(Sid)
);
上述語句會創建一個student的關係(表)，它包含學生姓名和學生id號碼。其中，Sname學生姓名的最大長度不超過20個字元，Sid為整型，並且PRIMARY KEY(Sid)對關係進行了完整性約束，說明學號作為主鍵，也就是說同一個學生他的id號碼是唯一的。
SQL還支援許多完整性約束，例如外碼與非空，下面僅對這兩種進行介紹：
外碼聲明關係中的任意元組在屬性上的取值必須對應於另外的某個關係的某元組在主碼屬性上的取值，其形式為foreign key(A,B,C)references X，其中A B C為屬性，X為指定的關係
非空完整性約束表示某個屬性的取值不能為空，其形式A INT not null表示類型為整型名為A的屬性不能為空
下面我們創建一個與課程相關的表，要求主鍵(主碼)為課程id且不為空，外碼為上面student關係的學生姓名屬性來做演示：
CREATE TABLE course
(
Cid INT NOT NULL,
Sname VARCHAR(20),
Cname VARCHAR(20),
PRIMARY KEY(Cid),
FOREIGN KEY(Sname) REFERENCES student
);
此時Sname的取值必須是student關係的Sname中的的一個取值。
刪除關係
刪除操作有兩種：
下方這條語句會刪除名為course的關係
DROP TABLE course;
而
DELETE FROM course;
則會刪除course關係中的所有元組（數據），但該關係保留
說白一點就是前者連錶帶數據一塊刪除，後者只剩一個空表
增加與刪除屬性
有時候會向關係中添加一個新的屬性，則需要用到以下語句
ALTER TABLE student ADD Sage INT;
該語句向student中添加了一個名為Sage，類型為整型的屬性，用於表示學生的年齡
在student中原有的元組在Sage的取值都將為空，即null（意思就是老數據原來沒有年齡，現在新建了年齡這一列，之前的老數據該列上的取值都為空）
刪除屬性就很簡單了，我們刪除student關係中的Sage屬性為例，語句如下
ALTER TABLE student DROP Sage;
但是注意，很多資料庫都不支援這一點，執行該語句可能會報錯。
查詢
SQL查詢結構由三個子居構成select，from，where
查詢以在from子句列出的關係作為輸入，並在這些關係上進行where和select子句中的指定運算，然後產生一個關係作為結果。
為方便演示，先向student表中插入如下幾條數據
INSERT INTO student VALUES('CairBin',1);
INSERT INTO student VALUES('Bob',2);
INSERT INTO student VALUES('Alice',3);
INSERT INTO student VALUES('Alice',4);
INSERT INTO course VALUES(4,'Alex','chemistry');
INSERT INTO course VALUES(1,'CairBin','math');
INSERT INTO course VALUES(2,'CairBin','chemistry');
INSERT INTO course VALUES(3,'Alice','chemistry');
單關係查詢
我們現在想要獲取student關係中的所有學生的姓名，也就是Sname屬性列下的所有數據，則可以使用如下語句
SELECT Sname FROM student;
當然可以跟隨多個屬性，例如輸出學生姓名和id
SELECT Sname,Sid FROM student;
另外，在關係模型的形式化數學定義中，關係是一個集合，因此重複的元組不會出現在集合中。但是對於實際操作來講，去重是相當費時的，所以SQL允許在資料庫關係和資料庫表達式的結果中出現重複。
（但是包含主碼聲明的關係中不可以，因為這回違背主碼的完整性約束。例如上方的student關係，它不允許兩個名為Alice的學生的id號碼相同）
我么執行下方語句
SELECT Sname FROM student;
結果如下
| Sname |
|---|
| CairBin |
| Bob |
| Alice |
| Alice |
這在SQL表達式結果中出現重複，如果想要強行去重，則需要用到關鍵字distinct
我們對該語句進行變形
SELECT DISTINCT Sname FROM student;
查看結果發現兩個Alice只剩下一個了
select子句中還可含有加減乘除運算符+ - * /
例如查詢每個學生的id，並輸出它們各自乘10的結果
SELECT Sid*10 FROM student;
where子句會篩選處那些在from子句所指定關係中滿足特定條件的元組，並且where子句允許使用如下邏輯連詞以及運算符
| 邏輯連詞以及運算符 | 描述 |
|---|---|
| and | 且 |
| not | 非 |
| or | 或 |
| < | 小於 |
| <= | 小於等於 |
| > | 大於 |
| >= | 大於等於 |
| = | 等於 |
| <> | 不等於 |
例如我們要輸出student中id大於1的學生姓名
Select Sname from student where Sid>1;
這裡應當注意，以下語句是不符合SQL標準的，對於一些資料庫或者它們的早期版本會報錯（例如MySQL，儘管在後來版本進行了支援，但是不要這樣做）
SELECT Sname FROM student WHERE 1<Sid;
多關係查詢
例如我們要輸出course中Sname屬性與student的Sname屬性取值相同的課程名和學生名：
SELECT Cname,student.Sname FROM course,student WHERE course.Sname = student.Sname;
更名運算
更名運算用到了as子句，它既可以出現在from中也可以出現在select中，作用是重新命名
（由於早期SQL沒有as關鍵字，在像Oracle這樣的資料庫中，不允許form出現as關鍵字，如果要做別名，則需將oldName as newName改成oldName newName）
我們先看看原語句效果
Select Sid from student where Sname = 'Alice';
使用as子句
Select Sid as 學號 from student where Sname = 'Alice';
當然還有as在from中的情況，這種情況是為了方便指代關係名
例如
SELECT course.Cname,student.Sname FROM course,student WHERE course.Sname = student.Sname;
利用as可以這樣寫
SELECT c.Cname,s.Sname FROM course AS c,student AS s WHERE c.Sname = s.Sname;
看到這裡可能會有人問，這有什麼意義？當然，對於簡短的語句效果不太明顯，但是對於下方這種語句你就能看到它的好處了
SELECT s FROM(
SELECT Sname AS s, Sid AS id FROM student
) AS T
WHERE T.id>1;
字元串運算
SQL標準是對字元串大小寫敏感的，但是在一些資料庫系統如MySQL、SQL Server中是不區分大小寫的。
SQL還允許對字元串進行函數操作，如upper()將字元串轉大寫，lower()轉小寫，trim()去掉字元串後面的空格。不同資料庫系統提供的函數可能會不同。
在字元串上可使用like運算符來實現模式匹配，模式是大小寫敏感的（但MySQL中，或在PostgreSQL中使用ilike時例外）
一般用兩個特殊字元來描述模式，一個是百分號%，另外一個是下劃線_
前者是個通配符，也就是說表示某個字元後面所有的位；後者是個佔位符，僅表示某個字元後的一位，下面舉例
SELECT Sname FROM student WHERE Sname LIKE 'C%';
輸出結果是CairBin
SELECT Sname FROM student WHERE Sname LIKE 'C_';
輸出結果是一張空表
前者會查找Sname列所有以C開頭的數據，後者會查找Sname列所有以C開頭但C後面僅有一位（也就是說總長度為兩字元）的數據
當然，SQL允許使用轉義字元來表示特殊字元
SELECT Sname FROM student WHERE Sname LIKE 'C/%%' ESCAPE '/';
該語句會用/來表示轉義字元，作用是查找以C和百分號開頭的數據。在語句中/%%應當分成/%和%來看，前者是個轉義字元，表示普通的百分號，後者是個通配符表示後面的任意位為任意數字。
排序
SQL為用戶提供了對關係中元組顯示次序的一些控制，order by子句可以讓查詢結果中的元組按排列順序顯示。
例如，升序輸出student關係中的學生id
SELECT Sid FROM student ORDER BY Sid;
該子句還有兩個關鍵字，asc表示升序，desc表示降序
接下來進行降序輸出
SELECT Sid FROM student ORDER BY Sid DESC;
between謂詞
X between a and b表示X屬性列中取大於等於a小於等於b的值
SELECT * FROM student WHERE Sid BETWEEN 1 AND 3;
該語句相當於下方語句
SELECT * FROM student WHERE Sid >= 1 AND Sid <= 3;
集合運算
SQL的union、intersect、except對應數學上集合的交集、並集、差集運算
例如student表中學號小於2與學號大於3的學生資訊元組組成的集合的並集
(SELECT * FROM student where Sid <2) union (select * from student where Sid>3);
注意：MySQL不支援except
聚集函數
聚集函數是以值集為輸入並返回單個值的函數。SQL標準提供了五個固有的聚集函數：
- 平均值
avg() - 最小值
min() - 最大值
max() - 求和
sum() - 計數
count()
基本聚集操作比較簡單，例如要求student關係中所有學生id的平均值
SELECT AVG(Sid) FROM student;
還有一種操作是分組聚集，它可以將聚集函數作用在一組元組集上
例如求student中每組同名學生的id之和
SELECT Sname,SUM(sid) FROM student GROUP BY Sname;
在有些時候，某屬性的取值可能為空，但需要對該屬性列進行聚集，但SQL聚集函數並不認為結果為null，而是認為應該跳過該值。
另外，在SQL:1999中引入了布爾類型，它可以取true、false、unknown，聚集函數some()和every()可應用於布爾值的集合，並分別計算這些值的析取(or)和合取(and)
having子句
有些時候我們對分組有限定條件要求，例如求student中每組同名學生的id之和，但是要求和在5以上的，這種操作需要利用having子句
SELECT Sname,SUM(sid) FROM student GROUP BY Sname HAVING SUM(sid)>5;
注意，任何出現在having子句中但是沒被聚集的屬性都需要出現在group by子句中
資料庫修改
刪除
刪除與查詢類似，例如刪除student中id為1的學生資訊
DELETE FROM student WHERE Sid = 1;
插入
例如我們向student插入一個名為Alex且id為5的一條數據，最簡單的一條insert語句如下：
INSERT INTO student VALUES('Alex',5);
這種情況values後的括弧中的值的順序與關係中屬性的順序一致。
還有一種情況是指定屬性和順序，如下：
INSERT INTO student(Sid,Sname) VALUES(5,'Alex');
該語句指定了兩個屬性，第一個值對應的屬性為id，第二個值對應的屬性為學生名。
更新
某些情況下，我們不想改變元組的所有值，但是要改變元組的局部值，這就需要用到update語句
如我們將之前插入的名為Alexid為5的學生姓名改為CairBin
UPDATE student SET Sname='CairBin' WHERE Sid = 5;
SQL提供case結構，它在單條update語句中執行多條更新。
例如我們將course關係中所有為chemistry的學科改成math，math改成chemistry
UPDATE course SET Cname=
CASE Cname
WHEN 'math' THEN 'chemistry'
WHEN 'chemistry' THEN 'math'
END;
當然該語句還支援else，當所有when的條件都不符合時就會取else後的值
UPDATE course SET Cname=
CASE Cname
WHEN 'math' THEN 'chemistry'
WHEN 'chemistry' THEN 'math'
ELSE
'English'
END;
結束
本文章並沒有給出專門介紹SQL嵌套子查詢相關的部分，這是因為我認為嵌套子查詢基本上是原有語句的組合併且在舉例的時候也用到過（尤其是更名運算那部分，所以我打算將這部分放到後面的文章去寫。
