《新SQL學習繪本》筆記

不同DBMS的SQL會有所差異。本篇筆記是使用OSQL（MSDE 2000）為基礎。

一. SQL基礎

1.資料庫與表格：
（1）製作資料庫：CREATE DATABASE xxx;
（2）執行：GO
（3）指定資料庫：USE xxx;
（4）製作表格：CREATE TABLE xxx (xxx INT, zzz VARCHAR(30), ...);

2.限制：
（1）PRIMARY KEY：主鍵。禁止資料重複、禁止 NULL 值出現
（2）UNIQUE：禁止資料重複
（3）CHECK：條件式。事先準備條件式，禁止使用不符合條件的資料
（4）NOTNULL：禁止 NULL 值出現
（5）DEFAULT = 值：將指定的值設為預設值
（6）欄位限制：price INT UNIQUE NOTNULL....
（7）表格限制：PRIMARY KEY (title, price, ...)

3.資料的登錄與取得：
（1）登錄資料：INSERT INFO xxx (title, price) VALUES ('xxx', 100);
（2）取出所有資料：SELECT * FROM xxx;

4.對資料做排序：
（1）遞增：SELECT * FROM xxx ORDER BY price ASC;
（3）遞減：SELECT * FROM xxx ORDER BY price DESC;
（3）多條件排序：SELECT * FROM xxx ORDER BY price ASC, id DESC;

5.其他的選項：
（1）資料群組化：SELECT price, COUNT(title) FROM xxx GROUP BY price;
（2）刪除重複資料：SELECT DISTINCT title FROM xxx;
（3）變更欄名來顯示：SELECT code AS bookcode FROM xxx;
（4）流水號：id INT IDENTITY (0, 10) //開始值為0；每次會被加上10




二. 基本操作

1.WHERE 句：
（1）取出符合條件的資料：SELECT * FROM xxx WHERE score = 100;
（2）把符合條件的資料排序後再取出：SELECT * FROM xxx WHERE score <= 100 ORDER BY score DESC;

2.操作文字的運算子：
（1）文字連結運算子：SELECT name || score FROM xxx; //SQL Server中則是使用 +
（2）字串的比較：SELECT name xxx WHERE name LIKE '%小%'; //顯示有含「小」的字串；若使用'小_'，則顯示在「小」的後面有一個字的字串

3.其他運算子：
（1）指定值的範圍：SELECT name FROM xxx WHERE score BETWEEN 10 AND 100;
（2）指定欄的值是否為NULL：SELECT name FROM xxx WHERE score IS NULL; //相反為IS NOT NULL
（3）取出與()內指定的值一致的資料：SELECT name FROM xxx WHERE score IN (50, 40, ...); //相反為 NOT IN
（4）指定取出的件數：SELECT TOP 10 name FROM xxx BY name //若要取出前10%，則寫成 TOP 10 PERCENT

4.INSERT 敘述：
（1）在特定欄位做值的登錄：INSERT INTO xxx (name, code) VALUES('a', 1);
（2）把舊有的表格登錄到別的表格：INSERT INTO xxx (id, name) SELECT no, name FROM yyy;

5.UPDATE 敘述：
（1）更新資料：UPDATE xxx SET price = 100 WHERE code = 2;

6.DELETE 敘述：
（1）刪除資料：DELETE FROM xxx WHERE name = 'abc';
（2）刪除表格：DELETE FROM xxx;




三. 主要函數的使用

1.數值函數：
（1）CEILING：傳回不小於引數的最小整數值。在 Oracle 中則是使用 CEIL
（2）FLOOR：傳回不大於引數的最大整數值
（3）RAND：在 0 以上 1.0 以下的範圍內，取得一個亂數

2.字串函數：
（1）LEN：傳回字串的文字數。在 Oracle、MySQL 中則是使用 LENGTH
（2）SUBSTRING：從字串取出特定的部分來傳回。在 Oracle、MySQL 中則是使用 SUBSTR
（3）LTRIM：去掉字串前面有空白的字元
（4）RTRIM：去掉字串後面有空白的字元
（5）UPPER：把字串轉成大寫
（6）LOWER：把字串轉成小寫

3.日期函數：
（1）GETDATE：取得現在的時間。在 Oracle、MySQL 中則是使用 SYSDATE
（2）DATEADD：對日期數或時間的值做加法
（3）DATEDIFF：取得日期數之間的差

4.合成函數：
（1）AVG：針對列中所含的值，求出平均值
（2）SUM：針對列中所含的值，求出總和
（3）COUNT：求出欄位中的列數。若要求出把重複的列去掉之後的列數，可以使用COUNT(DISTINCT name)
（4）MAX：求出欄位中的最大值
（5）MIN：求出欄位中的最小值
（6）GROUP BY、HAVING：若要以函數的處理結果為條件，取出特定的資料。SELECT name, SUM(score) FROM xxx GROUP BY name HAVING (SUM(score)>=300); //只取出 score 的總合為 300 分以上的人

5.轉換函數：
（1）CAST：將資料型別做轉換。CAST(0.245 AS VARCHAR) //將數值 0.245 轉為字串




四. 複雜的資料操作：

1.結合：
（1）交叉結合：將多個表格(或視界)中所有的行單純地連結起來。SELECT * FROM CROSS JOIN yyy; 或 SELECT * FROM xxx, yyy;
//取出某一方表格的所有欄位時，使用 xxx.*；若雙方的表格有同樣的欄位名稱，而想要指定其中一方時，則使用 xxx.name
（2）內部結合：從交叉結合的結果中，把指定欄位中的值一致的資料取出來。SELECT * FROM xxx INNER JOIN yyy ON id = no; 或 SELECT * FROM xxx, yyy WHERE xxx.id = yyy.no;
（3）左外部結合：左側表格的資料會全部被取出來，即使右側的表格中沒有一致的資料。SELECT * FROM xxx LEFT JOIN yyy ON id = no;
（4）右外部結合：右側表格的資料會全部被取出來，即使左側的表格中沒有一致的資料。SELECT * FROM xxx RIGHT JOIN yyy ON id = no;
（5）全外部結合：將左外部、右外部的功能結合起來，無論值有沒有一致，所有資料都會被取出。SELECT * FROM xxx FULL JOIN yyy ON id = no;
（6）使用 WHERE 句的外部結合：SELECT * FROM xxx, yyy WHERE id *= no; //左外部結合為 *=；右外部結合為 =*
在 Oracle 中：SELECT * FROM xxx, yyy WHERE id *= no(+); //左外部結合，在右側的欄位名後加上 (+)；右外部結合，則在左側的欄位名後加上 (+)

2.視界：從一或多個表格中，只把需要的部分暫時取出
（1）從一個表格製作：CREATE VIEW viw_xxx AS SELECT no, price FROM xxx WHERE id = 'A' //製作視界時，結尾不必加上「;」
（2）從不同表格製作：CREATE VIEW viw_xxx AS SELECT no, price FROM xxx,yyy WHERE xxx.id = yyy.id
（3）登錄資料：INSERT INTO viw_xxx (no, name) VALUES (1, 'A');
（4）更新資料：UPDATE viw_xxx SET name = 'B' WHERE no = 1;
（5）刪除資料：DELETE FROM viw_xxx WHERE no = 1;
（6）刪除視界：DROP VIEW viw_xxx;

3.集合運算子：
（1）UNION：資料會自動重新排序，且重複資料只顯示一筆。SELECT no, id FROM xxx UNION SELECT no1, id1 FROM yyy;
（2）UNION ALL：資料不會自動重新排序，且可以有重複資料。SELECT no, id FROM xxx UNION ALL SELECT no1, id1 FROM yyy;
（3）INTERSECT：針對 SELECT 敘述取出來的資料進行比較，並把一致的資料取出來。SELECT no, id FROM xxx INTERSECT SELECT no1, id1 FROM yyy;
（4）EXCEPT：取出左邊 SELECT 敘述裡沒有和右邊 SELECT 敘述裡重複到的資料。SELECT no, id FROM xxx EXCEPT SELECT no1, id1 FROM yyy; //在 Oracle 中則使用 MINUS

4.限定述語：
（1）ALL：會和所有用子查詢取得的資料做比較。SELECT * FROM xxx WHERE price < ALL (SELECT price FROM yyy WHERE no =5); //相反則為 <>ALL 或 !=ALL
（2）EXISTS：在子查詢中取出的資料存在時會傳回 TRUE；反之，傳回 FALSE。若為 TRUE，則會執行主查詢。SELECT * FROM xxx WHERE EXISTS (SELECT name FROM yyy WHERE no = 5); //反之為 NOT FALSE
（3）ANY：將子查詢取出的資料與任意的值一致的資料做比對。SELECT name, price FROM xxx WHERE price = ANY (SELECT price FROM xxx WHERE id = 1); //也可使用 SOME。在 SQL Server 中，ANY 後面只能指定子查詢；在 Oracle 中則可以直接指定常數值

5.索引編號：
（1）建立索引：CREATE INDEX idx_abc ON xxx(no); //若要禁止有相同的值重複出現，則可以寫成 CREATE UNIQUE INDEX ...
（2）刪除索引：DROP INDEX idx_abc;




五. 保護資料的機制

1.交易功能：等一連串的處理結束後，才會實際反映出處理結果，可防止事故或輸入錯誤造成資料發生異常
（1）宣告方式：
BEGIN TRANSACTION;
INSERT INTO xxx ...
（2）送出：COMMIT;
（3）回復：ROLLBACK;

2.鎖定：
（1）互斥鎖定：其他用戶無法參照或操作正被操作中的資料。SELECT * FROM xxx WITH (TABLOCK)
（2）共享鎖定：其他用戶只能參照正被操作中的資料，但無法操作它。SELECT * FROM xxx WITH (HOLDLOCK)
其他鎖定、範例請參考：http://caryhsu.blogspot.com/2011/09/sql-server.html
使用UPDLOCK應注意的死結問題：http://support.microsoft.com/kb/179362/zh-tw




六. 與程式之間的合作

1.SQL 的程式設計：
（1）BEGIN ~ END 區塊：
BEGIN
    SELECT GETDATE();
    PRINT 'abc';
    ...
END
（2）製作預存程序：CREATE PROCEDURE procedure_abc @a INT AS SELECT no, name FROM xxx //@a 為變數名稱
（3）執行預存程序：procedure_abc 999
（4）刪除預存程序：DROP PROCEDURE procedure_abc

2.擴充 SQL 的變數：
（1）變數的宣告：DECLARE @a INT
（2）將值帶入：SET @a = 999
（3）顯示變數：PRINT @a




七. 附錄

1.變更欄位的結構：
（1）追加新欄位：ALTER TABLE xxx ADD name VARCHAR(30); //在 Oracle 中則使用 ALTER TABLE xxx ADD (name VARCHAR(30));
（2）追加有預設值的欄位：ALTER TABLE xxx ADD name VARCHAR(30) DEFAULT 'abc'; //在 Oracle 中則使用 ALTER TABLE xxx ADD (name VARCHAR(30) DEFAULT 'abc');
（3）刪除某欄位：ALTER TABLE xxx DROP COLUMN name; //在 Oracle 中則使用 ALTER TABLE xxx DROP (name);；在 MySQL 中則使用 ALTER TABLE xxx DROP name;

2.追加限制條件：
（1）追加 PRIMARY KEY、UNIQUE、CHECK 限制：ALTER TABLE xxx ADD PRIMARY KEY (price); //在 Oracle 中則使用 ALTER TABLE xxx ADD CONSTRAINT PRIMARY KEY (price);
（2）追加 NOT NULL 限制：ALTER TABLE xxx ALTER COLUMN name VARCHAR(30) NOT NULL; //在 Oracle 中則使用 ALTER TABLE xxx MODIFY (name NOT NULL);
＊若要追加 PRIMARY KEY，則該欄位必須事先有指定 NOT NULL 才行

3.變更表格名與欄位名：
（1）變更表格名：EXEC sp_rename 'xxx', 'yyy'; //在 Oracle 中則使用 RENAME xxx TO yyy;；在 MySQL 中則使用 ALTER TABLE xxx TO yyy;
（2）變更欄位名：EXEC sp_rename 'xxx.[price]', 'price1'; //在 MySQL 中則使用 ALTER TABLE xxx CHANGE price price1 INTEGER;

4.其他的變更：
（1）追加預設值：在 Oracle 中使用 ALTER TABLE xxx MODIFY (price DEFAULT 999); //在 MySQL 中則使用 ALTER TABLE xxx ALTER COLUMN price SET DEFAULT 999;
（2）解除預設值：在 MySQL 中使用 ALTER TABLE xxx ALTER price DROP DEFAULT;
（3）變更某欄位的資料型別：在 Oracle 中使用 ALTER TABLE xxx MODIFY (price INT); //在 MySQL 中則使用 ALTER TABLE xxx MODIFY price INT;
（4）解除限制：在 Oracle 中使用 ALTER TABLE xxx ALTER price DROP CONSTRAINT UNIQUE;

5.外部鍵：用來表示使用者要參照哪一個表格中的哪一列
（1）製作表格時設定外部鍵：
<1>欄位限制的情況: no INT REFERENCES xxx(name) //no 為外部鍵的欄位名稱；xxx 為父表格名稱；name 為父鍵的欄位名稱
<2>表格限制的情況: FOREIGN KEY (no) REFERENCES xxx (name)
（2）後來再設定外部鍵：ALTER TABLE yyy ADD FOREIGN KEY (no) REFERENCES xxx(name); //yyy 為子表格名稱。在 Oracle 中則使用 ALTER TABLE yyy ADD CONSTRAINT FOREIGN KEY (no) REFERENCES xxx(name);；MySQL 同 SQL Server 語法