SQLite3 笔记

py3study

发布于 2020-01-14 12:41:43

2K0

发布于 2020-01-14 12:41:43

文章被收录于专栏：python3python3

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

SQLite insert

查看表结构：

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>

使用select结果插入

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

使用 create table 的这种形式，自增长字段在新表中创建，索引也不会创建，UNIOUE约束都不会被创建。

插入行时的 unique 约束，如果在定义为 unique 的字段中插入重复值，SQLite 会停止并报错。

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

sqlite update

更新一条记录：

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>

update 注意约束

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

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

约束：

唯一约束unique

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');

autoincrement

autoincrement 并不会使用未被使用的数字，而是自动递增，但不会阻挡你自己提供的值

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>

NOT NULL 约束

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>

check 约束

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 约束复杂

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.

4,排序

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'在插入的时候会被忽略大小写，然后就是一样的，无法二次插入

SQLite?

存储类

存储类	描述
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>

本文参与?腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019-07-04 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sqlite