使用SQLite3學習SQL語句的使用
首先有必要對本文中使用的軟件和資料來源作出如下的聲明:
1. SQLite3可能不支持一些東西
一些大型的數(shù)據(jù)庫支持的方便指令在這里好像不太好使，而許多廠商自己的擴展也不能在這里使用。一些內(nèi)容在SQLite3的wiki上已經(jīng)有了描述，這里只是我在應用《A
Guide to SQL》一書具體例子的時候碰到的一些具體句子。
1.1. 函數(shù)和操作符嵌套使用
比如查詢不重復的某個列個數(shù)，也許可以這樣寫：
SELECT COUNT(DISTINCT(BBS_LOGCNT.COUNT)) FROM BBS_LOGCNT;
但是再這里這樣的語法是無法通過的，而必須使用子查詢的方式進行：
SELECT COUNT(BBS_LOGCNT.COUNT) FROM BBS_LOGCNT
WHERE BBS_LOGCNT.COUNT IN (
SELECT DISTINCT(BBS_LOGCNT.COUNT)
FROM BBS_LOGCNT
);
在我使用了全稱方式下，好像這樣的表達方式非常的恐怖?。?！
1.2. ALL 和 ANY
在子查詢中使用這兩個操作符的時候都會出錯誤提示：
-
這個例子不能在SQLite3中順利運行
SELECT * FROM BBS_LOGCNT WHERE COUNT >= ANY (
SELECT FAVOR FROM FAVOR_BOARD
);
2. HAVING
出現(xiàn)在GROUP BY子句之后，作為的整體條件出現(xiàn)
SELECT CLASS, COUNT(NAME), AVG(FAVOR) FROM FAVOR_BOARD
GROUP BY CLASS
HAVING AVG(FAVOR) > 100 --每類的平均FAVOR應該大于100
ORDER BY COUNT(NAME);
得到如下的結(jié)果：（小貼示：在SQLite中使用 .separator ||
命令以生成方便Wiki使用的表格格式）
-
|
CLASS |
COUNT(NAME) |
AVG(FAVOR) |
|
服務 |
1 |
654 |
|
文學 |
2 |
218 |
|
理工 |
2 |
173 |
|
附屬 |
2 |
790.5 |
|
娛樂 |
4 |
1826 |
|
影視 |
4 |
1104.75 |
|
閑聊 |
5 |
592.4 |
3. Insert by Select
想要對favor_board表中的class列進行獨立的處理，因為這個列中的元素是屬于某個更高層次的分類的。所以建立了一個如下的表：
CREATE TABLE BBS_CLASS(
NAME VARCHAR PRIMARY KEY,
SECTION VARCHAR
);
然后將favor_board的class導入到這個表中：(分類所屬的段還沒有設置)
INSERT INTO BBS_CLASS (NAME, SECTION)
SELECT DISTINCT(CLASS), NULL FROM FAVOR_BOARD;
4. EXISTS操作符
舊一些版本的SQLite不支持EXISTS，而對IN是一直支持的，所以以往的一些查詢可以使用IN來替代EXISTS的功能。從3.1版開始一切都可以使用了！
例子：從FAVOR_BOARD中找到屬于2分區(qū)版面的相關信息。
SELECT NAME, CLASS FROM FAVOR_BOARD
WHERE EXISTS (
SELECT * FROM BBS_CLASS
WHERE SECTION = "2" -- 不限定的訪問BBS_CLASS成員
AND FAVOR_BOARD = NAME -- 限定的方式訪問FAVOR_BOARD成員
);
-
|
name |
class |
|
Hardware |
電腦 |
|
test |
系統(tǒng) |
|
Software |
電腦 |
5. VIEW的建立和操作
SQLite3對視圖的操作支持的相當有限，好像只是為了簡化查詢語句的復雜程度而設計的。 在建立一個視圖的時候，我們可以在SQL
Server之類的服務器上使用下面的句子：
-
這個例子不能在SQLite3中順利運行
CREATE VIEW BOARD (NAME, CLASS, SECTION, DESCRIPT) AS
SELECT FAVOR_BOARD.NAME, FAVOR_BOARD.CLASS,
BBS_CLASS.SECTION, FAVOR_BOARD.DESCRIPT
FROM FAVOR_BOARD, BBS_CLASS
WHERE FAVOR_BOARD.CLASS = BBS_CLASS.NAME;
但是從SQLite3的說明書中可以得到如下的Create View定義：
所以，不能夠自己定義列別名，(NAME, CLASS, SECTION,
DESCRIPT)這樣的部分必須要去掉才可以在SQLite中執(zhí)行。
此外，SQLite不支持對View的COPY, DELETE, INSERT 或 UPDATE
操作，如果想在實現(xiàn)類似的功能只能在觸發(fā)器上動點腦筋了。
6. Trigger
SQLite對觸發(fā)器的支持由于受到其語言實現(xiàn)限定而難以盡如人意，但是對于非常復雜的事務處理，我個人還是喜歡自己使用程序來處理，所以這樣一個簡單的觸發(fā)器設計對我來說已經(jīng)非常好使了。
例子:在修改FAVOR_BOARD表的CLASS列的時候，如果發(fā)現(xiàn)增加了一個在BBS_CLASS上沒有的新類名，則將這個名字加入到BBS_CLASS表中去。類似的，也可以在向FAVOR_BOARD進行INSERT操作的時候進行對應的操作。
CREATE TRIGGER NEW_BOARD_CLASS_TRG UPDATE OF CLASS ON FAVOR_BOARD
WHEN NEW.CLASS NOT IN (SELECT BBS_CLASS.NAME FROM BBS_CLASS)
BEGIN
INSERT INTO BBS_CLASS(NAME, SECTION) VALUES(NEW.CLASS, NULL);
END;
其中的WHEN操作符表示了執(zhí)行該觸發(fā)器的條件，彌補了SQLite沒有IF-ELSE這樣邏輯分析語句的缺陷。
本文是我在個人維基上一個沒有完成的SQL學習手冊類作品，將隨著作者對SQL的不斷學習進行完善。如果你對文中的內(nèi)容也有興趣可以聯(lián)系作者一同探討相關的內(nèi)容。
