SQLite中的SQL

创建数据库

表是探索SQLite中SQL的起点，也是关系数据库中信息的标准单元。所有的操作都以表为中心，表由行和列组成。

创建表

像关系模型一样，SQL是由几个部分组成的。可以用如下定义的create table命令创建表：

CREATE [temp] table table_name (column_definitions [, constraints]);

用temp或temporary关键字声明的表是临时表——只存在于当前会话。一旦连接断开，就会被自动销毁。temp旁边的方括号表示该项式可选项。

create table命令至少需要一个表名和一个字段名。column_definitions由用逗号分隔的字段列表组成，每个字段定义包括一个名称，一个域和一个逗号分隔的字段约束。例如：

CREATE TABLE contacts (id integer primary key,
                    name text not null collate nocase,
                    phone text not null default 'UNKNOWN',
                    unique (name,phone) );

• 字段id声明为integer类型，限制为主键；
• 字段name声明为text类型，约束不能为null，并且排序不区分大小写；
• 字段phone是text类型，也有两条约束。
• 表一级的约束是unique，定义在字段name和phone上。

修改表

可以用alter table命令修改表的部分结构。SQLite版的alter table命令既可以修改表明，也可以增加字段。该命令的一般格式为：

alter table table_name {rename to new_name | add column column_def}

花括号括起来选项列表，表示必须从各选项中选择一个。

ALTER TABLE contacts
    add column email text not null default '' collate nocase;

---

数据库查询

设计数据库和创建表的最终目的是使用数据。和数据一起工作是SQL数据操作语言的工作(DML)。DML的核心是select命令，它也是查询数据库的唯一命令。

关系操作

select命令与操作管道

从语法上说，select命令用一些列字句将关系操作组成在一起，每个子句代表一种特定的关系操作。

SELECT [distinct] heading
FROM tables
WHERE predicate
GROUP BY columns
HAVING predicate
ORDER BY columns
LIMIT count,offset;

LIKE和GLOB操作符

LIKE的作用与相等（=）类似，都是通过一个模式来进行字符串匹配的。例如要查询表foods中所有名称以字符"J"开始的食品：

SELECT id,name FROM foods WHERE name LIKE 'J%';

模式中的百分比（%）可与任意0个或多个字符匹配。下划线（_）可与任意单个字符匹配。百分号是贪婪匹配，它会匹配除了该字符本身外的所有在字符之间的内容。如果百分号在模式的最左边或者最右边，它将匹配字符的另外一边。

SELECT id,name FROM foods WHERE name LIKE '%ac%P%';

GLOB操作符在行为上与LIKE操作符非常类似。关键的不同在于它有些像UNIX/Linux文件名替换语法。
也就是说，它会使用文件名替换相关通配符，例如*和_，并且匹配是大小写敏感的。

SELECT id,name FROM foods WHERE name glob 'Pine*';

限定和排序

可以用limit和offset关键字限定结果集的大小和范围。limit指定返回记录的最大数量，offset指定偏移的记录数。例如，下面的命令是使用limit和offset返回food_types表中id排第2的Cereal记录：

SELECT * FROM food_types ORDER BY id LIMIT 1 OFFSET 1;

关键字offset在结果集中跳过一行，关键字limit限制最多返回一行。order by子句使记录集在返回之前按一个或多个字段值进行排序。以逗号分隔的一系列字段，每个字段项都可能配合排序方向——asc（默认的升序）或desc(降序)。例如：

SELECT * FROM food WHERE name LIKE 'B%' 
ORDER BY type_id desc, name LIMIT 10;