Sqlite 学习记录

Sqlite命令行(CLP)
======================================================================================
.help //获取帮助,会列出可用命令,如下:
.ver //获得版本
.open test.db //打开数据库文件
.save test.db //保存一个数据库或新建
.database //获取当前目录下存在的数据库文件,一个文件表示一个数据库
.tables //获取表列表
.indices test //获取表的索引列表
.schema test //提供表名则获取表创建语句,不提供则获取所有表,查询sqlite_master系统视图也可
.show //获取用户shell定义的设置
.nullvale NULL //设置null显示为NULL
.echo on //输出执行的语句

//导出为sql文件
.output file.sql
.dump
.output stdout

//导入sql文件
drop table test;
drop view schema;
.read file.sql
或者直接在命令行中用:
sqlite3 test.db .dump > test.sql
sqlite3 test.db < test.sql
sqlite3 -init test.sql test.db

create table test(id integer primary key, value text); //id主键且自增
create index test_idx on test(value) //创建索引。

//简单增删改查询,和Mysql类似
insert into test(id,value)values(1, 'testz');
insert into test(value)values('testz');

.mode column //查询结果显示列,可选格式:csv,html,insert,line,tabs,tcl,默认为list
.headers on //显示头部信息(包含.mode column)
select * from test; //查询数据,不显示列名的
select last_insert_rowid(); //获取最后插入的ID

//结果输出为csv,其中.mode csv也可以用.dump输出备份sql
.output file.csv
.mode csv //或用.separator ,
select * from test;
.output stdout

//导入到test2表
create table test2(id integer primary key, value text);
.import text.csv test2

//备份源文件,二进制文件,但没sql移植好
sqlite3 test.db vacuum
cp test.db test.Backup

工具
======================================================================================
sqlite3_analyzer 可获取数据库磁盘结构的详细技术信息。

SQL
======================================================================================
使用;作为命令终结符。

select * from test;
insert into test(value)values('testz');
delete from test where id =1;
update teable set value="zz" where id=3

//很多和Mysql语法类似,如 :
like "%test%"、count(*)、group by、having、distinct、as、in、子查询、复合查询、

//读一行从第一行开始。desc或asc,可忽略offset。
select * from test order by desc id limit 1 offset 1

//类似MYSQL的左连接
select * from test a left outer join testb b on a.id=b.id;

字段设计
======================================================================================
日期的默认值:
current_date YYYY-MM-DD
current_time HH::MM::SS
current_timestamp YYYY-MM-DD HH:MM:SS

//check约束,小于7个字符就报错。
create table test(
id integer primary key,
name text not null default 'zzz',
unique(name),
check(length(name)>=7));

//外键test_types表的id
create table test(
id integer primary key,
type_id integer references test_types(id)
on delete restrict //父ID被删时,此数据不删除。
deferrable initially deferred,
name text);

完整规则:
set null:如果父值被删除或不存在,剩余子值改为null。
set default:如果父值被删除或不存在,剩余子值改为默认值。
casecade:更新或删除父值时则子值也被更新或删除。
restrict:更新或删除父值,可能会出现孤立的子值,从而阻止事务。
no action:不干涉操作执行,只观察变化。
deferrable:立即强制实施还是延迟到整个事务结束时。

//关于字段类型
通过值的表示法来判断其类型。有5类型:integer、real(浮点)、text、blob(二进制x开头带引号)、null
一个字段可以存储不同类型的值。
select typeof(3.14) 查询其类型

索引
======================================================================================
//创建name大小写不敏感的索引
create index a_idx test(name collate nocase);

//使用单个字段索引的情况: idx on test(a)
column {=|>|>=|<=|<} expression
expression {=|>|>=|<=|<} column
column IN (expression-list)
column IN (subquery)

//多字段索引的情况: idx on test(a,b,c,d)
只有a和b使用了索引
select * from test where a=1 and b=2 and d=3
表达式a>1称为最右边的索引字段,因为用了不等号,后面的查询条件无法使用索引。
select * from test where a>1 and b=2 and c=3 and d=3

事务
======================================================================================
begin,commit,rollback

主要分为:读事务、写事务。
默认多个事务一起运行时,一个事务的非读操作需要等另一个事务(如果有语句执行)结束才能提交。

为了避免死锁,需要使用正确的事务类型,默认3种:
begin [deferred | immediate | exclusive] transaction;
其中:
deferred
是默认情况,直到必须使用时才获取锁。

immediate
在执行时试图获取预留锁,如果成功获取,其它事务无法修改数据库只能读,提交不会被阻碍,提交后其它事务可修改。

exclusive
试着获取对数据库的排它锁,只有等此事务任意操作完并提交后,其它事务才能开始执行查询或增删等语句。
exclusive比immediate更高的安全级别,immediate开始就不会产生死锁了。

函数

======================================================================================

datetime(time, 'unixepoch', 'localtime')                          查询时间戳为本地时间

上一篇:Entity Framework Code First 常用方法集成


下一篇:duilib进阶教程 -- 图片和文字的位置调整 (5)