舒淇大胆火爆写真集:【转】 使用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的不断学习进行完善。如果你对文中的内容也有兴趣可以联系作者一同探讨相关的内容。