SQLite3 笔记

SQLite insert
插入一行：
插入一组：
使用select结果插入
多行插入到新表：
多行插入到新表，一步到位：
非常有用的临时表：
sqlite update
更新一条记录：
update 注意 约束
sqlite delete
约束：
唯一约束unique
autoincrement
主键约束
域约束：默认值
时间戳默认值
NOT NULL 约束
check 约束
check 约束 复杂
外键约束
SQLite 
存储类
视图
索引
触发器
视图触发器：可更新的视图
事务
冲突

sqlite> .schema foods
CREATE TABLE foods(
  id integer primary key,
  type_id integer,
  name text );

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

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

验证：
sqlite> select * from foods where name like '%bobka';
id          type_id     name           
----------  ----------  ---------------
10          1           Chocolate Bobka
13          1           Cinnamon Bobka 
413         1           Cinnamon Bobka 
414         1           Blueberry Bobka

sqlite> insert into foods values(null, (select id from food_types where name='Bakery'), 'Blackberry Bobka');
sqlite> select * from foods where name like '%bobka';
id          type_id     name           
----------  ----------  ---------------
10          1           Chocolate Bobka
13          1           Cinnamon Bobka 
413         1           Cinnamon Bobka 
414         1           Blueberry Bobka
415         1           Blackberry Bobk
sqlite>

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 Bobka
13          1           Cinnamon Bobka 
413         1           Cinnamon Bobka 
414         1           Blueberry Bobka
415         1           Blackberry Bobk
416         1           Chocolate Bobka
sqlite> 

[注意]：字段个数要匹配，数据类型也要匹配：
否则，失败。
sqlite> .schema foods
CREATE TABLE foods(
  id integer primary key,
  type_id integer,
  name text );
sqlite> insert into foods select last_insert_rowid()+100, name, type_id from foods where name ='Chocolate Bobka';
Error: UNIQUE constraint failed: foods.id
sqlite>

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

sqlite> create table foods3 as select * from foods;
sqlite> select count(*) from foods3;
count(*)  
----------
416       
sqlite>

sqlite> 
create temp table list as
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;

sqlite> select * from list;
food        name        episodes  
----------  ----------  ----------
Bagels      Bakery      1         
Bagels, ra  Bakery      2         
Bavarian C  Bakery      1         
Bear Claws  Bakery      3         
Black and   Bakery      2         
Bread (wit  Bakery      1         
Butterfing  Bakery      1         
Carrot Cak  Bakery      1         
Chips Ahoy  Bakery      1

sqlite> insert into foods values (416, 1, 'Chocolate Bobka');
Error: UNIQUE constraint failed: foods.id
sqlite>

sqlite> 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 BOBKA
11          1           Chocolate Eclai
12          1           Chocolate Cream
222         9           Chocolates, box
223         9           Chocolate Chip 
224         9           Chocolate Cover
416         1           CHOCOLATE BOBKA
sqlite>

sqlite> .schema foods
CREATE TABLE foods(
  id integer primary key,
  type_id integer,
  name text );
sqlite> 

sqlite> update foods set id=11 where name='CHOCOLATE BOBKA';
Error: UNIQUE constraint failed: foods.id
sqlite>

sqlite> delete from foods where name='CHOCOLATE BOBKA';

create table contacts (
int integer primary key,
name text not null collate nocase,
phone text not null default 'UNKNOWN',
unique (name, phone) 
);

insert into contacts (name, phone) values ('Jerry', 'UNKNOWN');

sqlite> create table maxed_out(id integer primary key autoincrement, x text);
sqlite> insert into maxed_out values(10,'wors');
sqlite> select * from maxed_out;
id          x         
----------  ----------
10          wors      

sqlite> insert into maxed_out values (9223372036854775807,'last one');
sqlite> select * from maxed_out;
id          x         
----------  ----------
10          wors      
9223372036  last one  

sqlite> insert into maxed_out values (NULL,'will not work');
Error: database or disk is full
sqlite>

约束：主键约束 primary key 不允许存在同样的

sqlite> create table pkey(x text, y text, primary key(x,y));
sqlite> insert into pkey values ('x','y');
sqlite> insert into pkey values ('x','x');
sqlite> select rowid, x, y from pkey;
rowid       x           y         
----------  ----------  ----------
1           x           y         
2           x           x         
sqlite> insert into pkey values ('x','x');
Error: UNIQUE constraint failed: pkey.x, pkey.y
sqlite>

域约束：默认值，NULL， NOT NULL ，check，sort

1, default 默认值
create table contacts (
int integer primary key,
name text not null collate nocase,
phone text not null default 'UNKNOWN',
unique (name, phone) 
);

sqlite> insert into contacts (name) values ('Tom');
sqlite> select * from contacts;
int         name        phone     
----------  ----------  ----------
1           Jerry       UNKNOWN   
2           Jerry       021-110   
3           Tom         UNKNOWN   
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           2017-02-10  06:14:30    2017-02-10 06:14:30
2           2017-02-10  06:14:32    2017-02-10 06:14:32
sqlite>

sqlite> .schema contacts
CREATE TABLE contacts (
int integer primary key,
name text not null collate nocase,
phone text not null default 'UNKNOWN',
unique (name, phone) 
);
sqlite> insert into contacts (phone) values ("134-1234-5678");
Error: NOT NULL constraint failed: contacts.name
sqlite>

create table contacts (
id integer primary key,
name text not null collate nocase,
phone text not null default '空号',
unique (name,phone),
check (length(phone)>=7)
);

check 约束 复杂
create table foo (
x integer,
y integer check(y>x),
z integer check (z>abs(y))
);
sqlite> insert into foo values (-2, -1, 2);
sqlite> select * from foo;
x           y           z         
----------  ----------  ----------
-2          -1          2         
sqlite> update foo set z=-3 where z=2;
Error: CHECK constraint failed: foo
sqlite>

外键约束 http://www.sqlite.org/foreignkeys.html
确保foods的type_id 已存在于food_types的id中。

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


CREATE TABLE foods(
  id integer primary key,
  type_id integer references food_types(id)
  on delete restrict
  deferrable initially deferred,
  name text );



    NO ACTION: Configuring "NO ACTION" means just that: when a parent key is modified or deleted from the database, no special action is taken.

    RESTRICT: The "RESTRICT" action means that the application is prohibited from deleting (for ON DELETE RESTRICT) or modifying (for ON UPDATE RESTRICT) a parent key when there exists one or more child keys mapped to it. The difference between the effect of a RESTRICT action and normal foreign key constraint enforcement is that the RESTRICT action processing happens as soon as the field is updated - not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint. Even if the foreign key constraint it is attached to is deferred, configuring a RESTRICT action causes SQLite to return an error immediately if a parent key with dependent child keys is deleted or modified.

    SET NULL: If the configured action is "SET NULL", then when a parent key is deleted (for ON DELETE SET NULL) or modified (for ON UPDATE SET NULL), the child key columns of all rows in the child table that mapped to the parent key are set to contain SQL NULL values.

    SET DEFAULT: The "SET DEFAULT" actions are similar to "SET NULL", except that each of the child key columns is set to contain the columns default value instead of NULL. Refer to the CREATE TABLE documentation for details on how default values are assigned to table columns.

    CASCADE: A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key. For an "ON DELETE CASCADE" action, this means that each row in the child table that was associated with the deleted parent row is also deleted. For an "ON UPDATE CASCADE" action, it means that the values stored in each dependent child key are modified to match the new parent key values.

create table contacts (
int integer primary key,
name text not null collate nocase,
phone text not null default 'UNKNOWN',
unique (name, phone) 
);

collate 定义排序规则：
nocase 忽略大小写
组合起来就是：'Jerry'与'jerry'在插入的时候会被忽略大小写，然后就是一样的，无法二次插入

存储类	描述
NULL	值是一个 NULL 值。
INTEGER	值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL	值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT	值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB	值是一个 blob 数据，完全根据它的输入存储。

视图即虚拟表，也称为派生表。因为视图的内容来自其他的查询结果。
视图不是基本表，尽管感觉起来与表是一样的。基本表的内容是持久的，视图是动态产生的。


视图的起源：如果需要频繁查此表
select f.name, ft.name, e.name
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.episode_id=e.id;


创建视图：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.episode_id=e.id;

sqlite> select * from details limit 10;
fd          tp          ep          ssn       
----------  ----------  ----------  ----------
Bagels      Bakery      The Strike  9         
Bagels, ra  Bakery      The Strike  9         
Bagels, ra  Bakery      The Muffin  8         
Bavarian C  Bakery      The Soup N  7         
Bear Claws  Bakery      The Strong  9         
Bear Claws  Bakery      The Sniffi  5         
Bear Claws  Bakery      The Rainco  5         
Black and   Bakery      The Dinner  5         
Black and   Bakery      The Unders  6         
Bread (wit  Bakery      The Apolog  9         
sqlite> 


删除视图：
drop view details;

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


创建唯一索引
sqlite> drop table foo;
sqlite> create table foo(id integer,name text);
sqlite> create unique index foo_index on foo(id,name);
sqlite> insert into foo(id,name) values(250,'老王'); 
sqlite> insert into foo(id,name) values(11,'张三'); 
sqlite> insert into foo(id,name) values(250,'老王'); 
Error: UNIQUE constraint failed: foo.id, foo.name
sqlite> select * from foo;
id          name      
----------  ----------
250         老王    
11          张三    
sqlite> 


删除索引：
sqlite> drop index foo_index;

sqlite> create index foods_name_idx on foods (name collate nocase);

sqlite> .indices 
foods_name_idx

sqlite> .schema foods
CREATE TABLE foods(
  id integer primary key,
  type_id integer,
  name text );
CREATE INDEX foods_name_idx on foods (name collate nocase);
sqlite>

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

未更新的行用old引用，已更新的行用new引用
所有属性都可以用点来引用

create temp table log(x);
create temp trigger foods_update_log update of name on foods
begin
    insert into log values('update foods:new name='||new.name);
end;

begin;
update foods set name='JUJYFRUIT' where name='JujyFruit';
select * from log;
rollback;



执行结果：
sqlite> create temp table log(x);
sqlite> create temp trigger foods_update_log update of name on foods
   ...> begin
   ...>     insert into log values('update foods:new name='||new.name);
   ...> end;
sqlite> 
sqlite> begin;
sqlite> update foods set name='JUJYFRUIT' where name='JujyFruit';
sqlite> select * from log;
x                              
-------------------------------
update foods:new name=JUJYFRUIT
sqlite> rollback;
sqlite> 

shell执行结果：
chunli@linux:~/work/sqlite$ cat trigger.sql 
create temp table log(x);
create temp trigger foods_update_log update of name on foods
begin
    insert into log values('update foods:new name='||new.name);
end;

begin;
update foods set name='JUJYFRUIT' where name='JujyFruit';
select * from log;
rollback;

chunli@linux:~/work/sqlite$ sqlite3 foods.db < trigger.sql 
update foods:new name=JUJYFRUIT
chunli@linux:~/work/sqlite$

chunli@linux:~/work/sqlite$ cat trigger.sql 
--创建视图
create view foods_view as
    select f.id fid, f.name fname, t.id tid, t.name tname
    from foods f, food_types t;

--创建触发器
create trigger on_update_foods_view instead of update on foods_view
for each row
begin
    update foods set name=new.fname where id=new.fid;
    update food_types set name=new.tname where id=new.tid;
end;

--小实验,更新视图的数据，自动同步到基础表
begin;
    update  foods_view set fname='乐事薯片', tname='Fast Food' where fid=413;
    --查询底层表
    select * from foods f, food_types t where f.type_id=t.id and f.id=413;
rollback;

--查询底层表
select * from foods f, food_types t where f.type_id=t.id and f.id=413;

drop view foods_view;

chunli@linux:~/work/sqlite$ sqlite3 foods.db < trigger.sql 
413|1|乐事薯片|1|Fast Food
413|1|Whataburger|1|Fast Food
chunli@linux:~/work/sqlite$

SQLite事务：
BEGIN [point] / COMMIT[point]/ROLLBACK[point]

默认情况下：SQLite事务是自动提交，执行成功则提交，执行失败则回滚。

begin 事务开始，之后的所有事务都可以取消
commit 提交
rollback 事务回滚


sqlite> begin;
sqlite> delete from foods;
sqlite> select count(*) from foods;
0
sqlite> rollback;
sqlite> select count(*) from foods;
414
sqlite> 
=======================================================
回滚到事务点：
sqlite> begin;
sqlite> select count(*) from foods;
414
sqlite> delete from foods;
sqlite> select count(*) from foods;
0
sqlite> savepoint point1;
sqlite> select count(*) from food_types;
15
sqlite> delete from food_types;
sqlite> select count(*) from food_types;
0
sqlite> rollback to point1;
sqlite> select count(*) from food_types;
15
sqlite> rollback;
sqlite> select count(*) from foods;
414
sqlite>

冲突解决：
SQLite提供5种冲突解决方案：
replace 违反的记录被删除，以新记录代替之
ignore 	违反的记录保持原貌，其它记录继续执行
fail 	终止命令，违反之前执行的操作得到保存
abort 	终止命令，恢复违反之前执行的修改
rollback终止命令和事务，回滚整个事务

语法：

    语句级（可覆盖对象级的冲突解决手段）
    insert/update/create or [resolution] table/index [tbl_name/idx_name] ……
    [resolution]： replace、ignore、fail、abort、rollback

    对象级（定义表格时）
    create table/index [tbl_name/idx_name] ([field_name] [format] [constraint] on conflict [resolution]);
    [constraint]：unique、not null……



sqlite> update foods set id=800-id;
Error: UNIQUE constraint failed: foods.id

sqlite> update or abort foods set id=800-id;
Error: UNIQUE constraint failed: foods.id

sqlite> update or replace foods set id=800-id;
sqlite>