SQLite中的高级SQL

插入记录

使用insert插入记录。insert在表单上工作，使用insert命令可以一次插入一条记录，使用select命令可以一次插入多条记录。insert语句的一般格式为：

insert into table(column list) values(value_list);

例如：

insert into foods (name, type_id) values ('Cinnamon Bobka', 1);

如果在insert语句中为表的所有字段提供值，可以省略字段列表。例如：

sqlite> insert into foods values (NULL, 1, 'Blueberry Bobka');

插入一组行

子查询可以用在insert语句中，既可以作为值列表的一部分，也可以完全替代整个值列表。

sqlite> insert into foods   ...> select last_insert_rowid()+1, type_id, name from foods   ...> where name = 'Chocolate Bobka';sqlite> select * from foods where name like '%Bobka';id          type_id     name           ----------  ----------  ---------------10          1           Chocolate Bobka13          1           Cinnamon Bobka 413         1           Cinnamon Bobka 414         1           Blueberry Bobka415         1           Chocolate Bobka

插入多行

使用select形式的insert可以一次插入多行，例如：

sqlite> create table foods2 (id int, type_id int, name text);sqlite> insert into foods2 select * from foods;sqlite> select count(*) from foods2;count(*)  ----------415       

上述语句表示创建一个新表foods，并插入表foods的所有记录。

还有另一种形式：

sqlite> create table foods3 as select * from foods;

create table与从foods表选择数据插入表两步并为一步。这对创建临时表特别有用：

sqlite> select f.name food, t.name name,   ...> (select count(episode_id) from foods_episodes where food_id = f.id) episodes   ...> from foods f, food_types t   ...> where f.type_id = t.id;

更新记录

update命令用于更新表中的记录，update语句的一般格式为：

update table set update_list where predicate

例如：

sqlite> insert into foods values (415, 1, 'Chocolate Bobka');Error: UNIQUE constraint failed: foods.idsqlite> update foods set name = 'CHOCOLATE BOBKA'   ...> where name = 'Chocolate Bobka';sqlite> select * from foods where name like 'CHOCOLATE%';id          type_id     name           ----------  ----------  ---------------10          1           CHOCOLATE BOBKA11          1           Chocolate Eclai12          1           Chocolate Cream222         9           Chocolates, box223         9           Chocolate Chip 224         9           Chocolate Cover415         1           CHOCOLATE BOBKA

删除记录

使用delete命令可以删除表中的记录。一般格式为：

delete from table where predicate

实体完整性

数据库的所有域都是唯一的、可以定位的。域要可寻址，它相应的行必须是可以寻址的。因此，行必须在某种方式是唯一的。这就是主键的功能。

主键由至少带有unique约束的一个或一组字段组成。

唯一性约束

唯一性（unique）约束是主键的基础。

sqlite> create table contacts (   ...> id integer primary key,   ...> name text not null collate nocase,   ...> phone text not null default 'UNKNOWN',   ...> unique (name, phone));sqlite> insert into contacts (name,phone) values ('Jerry', 'UNKNOWN');sqlite> insert into contacts (name) values ('Jerry');Error: UNIQUE constraint failed: contacts.name, contacts.phonesqlite> insert into contacts (name, phone) values ('Jerry', '555-1212');

在contacts表中，对name和phone的联合有唯一性约束。字段值的联合必须是唯一的。

NULL和UNIQUE:注意NULL不等于任何值，甚至不等于其他NULL.理论上，您可以放入任意多个NULL。

主键约束

在SQLite中，不管你有没有定义主键，都有一个64-bit整型字段，称为rowid。它还有两个别名——_rowid_和oid。SQLite为主键字段提供自动增长特性。如果定义字段类型为integer primary key,SQLite将为该字段创建默认值，该默认值确保整数值是唯一的。实际上，该字段就是rowid的别名。

新创建的rowid不一定是按照严格顺序增长的。如果想要SQLite使用唯一的自动主键值，可以加入关键值autoincrement。

sqlite> select rowid,oid,_rowid_,id,name, phone from contacts;id          id          id          id          name        phone     ----------  ----------  ----------  ----------  ----------  ----------1           1           1           1           Jerry       UNKNOWN   2           2           2           2           Jerry       555-1212  

域完整性

默认值

default为字段提供一个默认值。例如，contacts的name字段默认值为'UNKNOWN'。

default还可以接受3中预定义格式的ANSI/ISO保留字，用于生产日期和时间值。current_time格式为（HH:MM:SS）,current_date格式为（YYYY-MM-DD）,current_timestamp会生成一个日期时间的组合，格式为（YYYY-MM-DD HH:MM:SS）.例如：

sqlite> create table times (id int,   ...> date not null default current_date,   ...> time not null default current_time,   ...> timestamp not null default current_timestamp);sqlite> insert into times (id) values (1);sqlite> insert into times (id) values (2);sqlite> select * from times;id          date        time        timestamp          ----------  ----------  ----------  -------------------1           2015-01-25  09:10:01    2015-01-25 09:10:012           2015-01-25  09:10:16    2015-01-25 09:10:16

NOT NULL约束

NOT NULL约束可以确保字段部位NULL.

check约束

check约束允许定义表达式来测试要插入或者更新的字段值。例如，check约束可能确保电话号码字段的值至少是7位字符长度。

sqlite> create table contacts2   ...> (id integer primary key,   ...> name text not null collate nocase,   ...> phone text not null default 'UNKNOWN',   ...> unique(name, phone),   ...> check (length(phone) >=7));

外键约束

SQLite支持关系理论中的关系完整性概念。关系完整性也叫外键，它确保了一个表中的关键值必须从另一个表中引用，且该数据必须在另一个表中实际存在。经典的例子就是父子关系、主从关系。SQLite支持在创建表时使用外键，可以使用如下语法：

create table table_name(column_definition references foreign_table (column_name) on {delete|update} integrity_action [not] deferrable [initially {deferred|immediate},] ...); 

例如，foods表和food_types表当前的定义如下：

CREATE TABLE foods(  id integer primary key,  type_id integer,  name text );

CREATE TABLE food_types(  id integer primary key,  name text );

可以看出foods表使用了type_id字段，引用自表food_types。如果想要引用完整性确保SQLite保护您的数据，确保foods表的type_id值都存在于food_types的id字段，可以使用如下方式定义foods表：

sqlite> create table foods(   ...> id integer primary key,   ...> type_id integer references food_types(id)   ...> on delete restrict   ...> deferrable initially deferred,   ...> name text);

type_id引用自表food_types的id字段。使用了delete restrict, 该命令告诉SQLite阻止任何这样的删除行为，如果从表food_types中删除某行，将导致foods表的id没有父id的存在。完整的规则定义如下：

• set null:如果父值被删除或者不存在了，剩余的子值将该为null
• set default:如果父值被删除或者不存在了，剩余的子值修改为默认值
• cascade：更新父值时，更新所有匹配的子值。删除父值时，删除所有的子值。要特别注意该选项，cascade的删除功能可能会出现意想不到的效果
• restrict:更新或者删除父值可能会出现孤立的子值，从而阻止（终止）事物。
• no action:使用一种松弛的方法，不干涉操作执行，只是观察变化。在整个语句（如果约束定义为deferred的事物也一样）的结尾报出错误。

SQLite支持deferrable子句，该子句控制定义的约束是立即强制实施还是延迟到整个事务结束时。

排序规则

排序规则涉及文本值如何比较。三种排序规则：

• memcmp()逐字比较文本值
• nocase:非大小写敏感排序算法
• reverse：与二进制排序规则相反。reverse更多地用来测试。

存储类

SQLite有5个原始的数据类型。integer，real，text，blob，NULL。SQLite函数typeof（）根据值的表示法返回其存储类。

sqlite> select typeof(3.14), typeof('3.14'),typeof(314), typeof(x'3142'),typeof(NULL);typeof(3.14)  typeof('3.14')  typeof(314)  typeof(x'3142')  typeof(NULL)------------  --------------  -----------  ---------------  ------------real          text            integer      blob             null        

视图

视图即虚拟表，也称为派生表，因为它们的内容都派生自其他表的查询结果。虽然视图看起来感觉就像基本表一样，但是它们不是基本表。基本表的内容是持久的，而视图的内容是在使用时动态产生的。创建视图的语法如下：

create view name as select-stmt;

sqlite> create view details as   ...> select f.name as fd, ft.name as tp, e.name as ep, e.season as ssn   ...> from foods f   ...> inner join food_types ft on f.type_id = ft.id   ...> inner join foods_episodes fe on f.id = fe.food_id   ...> inner join episodes e on fe.episodes_id = e.id;

使用drop view删除视图

drop view name;

索引

索引是一种用来在某种条件下加速查询的结构。创建索引的命令如下：

create index [unique] index_name on table_name (columns)

如果使用关键字unique，将会在索引上添加约束，索引中的所有值必须是唯一的。这不仅适用于索引，也适用于索引所在字段。unqiue关键字覆盖index中的所有字段，不管是联合值还是单个值：

sqlite> create table foo (a text, b text);sqlite> create unique index foo_idx on foo(a, b);sqlite> insert into foo values ('unique', 'value');sqlite> insert into foo values ('unique', 'value');Error: UNIQUE constraint failed: foo.a, foo.b

若要删除索引，可使用drop index命令

drop index index_name;

排序规则

索引中的每个字段都有相关的排序规则。例如，要在foods.name上创建大小写不敏感的索引，可以使用如下命令：

create index foods_name_idx on foods (name collate nocase);

.indices列出表中索引。也可以使用.schema命令查看更多的信息。

触发器

当具体的表发生特定的数据库事件时，触发器执行对应的SQL命令。命令格式如下：

create [temp|temporary] trigger name [before|after] [insert|delete|update|update of colums] on table action

可以通过关键字before或after来制定这些操作是在事件发生前还是发生后执行。事件包括在具体的表中执行的delete、insert和update命令。

更新触发器

与insert和delete触发器不同，update触发器可以在表的执行字段上定义，这种触发器的一般格式如下：

create trigger name [before|after] update of column on table action

事物

事务由3个命令控制：begin、commit和rollback.begin开始一个事务，begin之后的所有操作都可以取消，如果连接终止前没有发出commit，也会被取消。commit提交事物开始后所执行的所有操作。类似地，rollback还原begin之后的所有操作。例如：

sqlite> begin;sqlite> delete from foods;sqlite> rollback;sqlite> select count(*) from foods;