舒淇大胆火爆写真集:【转】 使用SQLite3学习SQL语句的使用

使用SQLite3学习SQL语句的使用

首先有必要对本文中使用的软件和资料来源作出如下的声明:

• 本文中使用的好多例子都来自模仿《A Guide to SQL》（中文版）——Philip J.Pratt著 贾云霞等 译。

• 本文中使用的数据大部分来自http://bbs.tju.edu.cn 的系统统计数据，如果发现有版权的问题，请通知本文作者。

1. SQLite3可能不支持一些东西

一些大型的数据库支持的方便指令在这里好像不太好使，而许多厂商自己的扩展也不能在这里使用。一些内容在SQLite3的wiki上已经有了描述，这里只是我在应用《A Guide to SQL》一书具体例子的时候碰到的一些具体句子。

1.1. 函数和操作符嵌套使用

比如查询不重复的某个列个数，也许可以这样写：

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

出现在GROUP BY子句之后，作为的整体条件出现

SELECT CLASS, COUNT(NAME), AVG(FAVOR) FROM FAVOR_BOARD
        GROUP BY CLASS
        HAVING AVG(FAVOR) > 100         --每类的平均FAVOR应该大于100
        ORDER BY COUNT(NAME);

得到如下的结果：（小贴示：在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分区版面的相关信息。

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

  系统

  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定义：

• CREATE [TEMP | TEMPORARY] VIEW [database-name.] view-name AS select-statement

所以，不能够自己定义列别名，(NAME, CLASS, SECTION, DESCRIPT)这样的部分必须要去掉才可以在SQLite中执行。

此外，SQLite不支持对View的COPY, DELETE, INSERT 或 UPDATE 操作，如果想在实现类似的功能只能在触发器上动点脑筋了。

6. Trigger

SQLite对触发器的支持由于受到其语言实现限定而难以尽如人意，但是对于非常复杂的事务处理，我个人还是喜欢自己使用程序来处理，所以这样一个简单的触发器设计对我来说已经非常好使了。

例子:在修改FAVOR_BOARD表的CLASS列的时候，如果发现增加了一个在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操作符表示了执行该触发器的条件，弥补了SQLite没有IF-ELSE这样逻辑分析语句的缺陷。

本文是我在个人维基上一个没有完成的SQL学习手册类作品，将随着作者对SQL的不断学习进行完善。如果你对文中的内容也有兴趣可以联系作者一同探讨相关的内容。