Sqlite学习笔记

    2014-04-08 00:00     0 条评论

sqlite权威指南

1.Sqlite简介

1.1 sqlite体系结构

接口

接口位于栈的顶部,由sqlite C API组成。程序、脚本语言还有与sqlite交互的库文件最终通过它与sqlite交互

编译器

虚拟机

后端

1.2 Sqlite的特性和设计理念

零配置

sqlite在设计之初,就明确了不需要DBA。基本无需配置。

移植性

紧凑性

sqlite的设计是轻量级的,只包含1个头文件,一个库文件,以及关系数据库不需要外部数据库的服务器。所有这些文件都打包在0.5MB内。

sqlite使用可变长度的记录,只分配数据存储每个字段的最小值。在varchar(100)列中,如果放入2字节的字段,则只占3字节的空间(额外的一个字节用于记录它的类型信息),而不是100

简单性

灵活性

  • 强大而灵活的关系型数据库前端,
  • 简单而紧凑的B-tree后端

自由授权

可靠性

sqlite的核心软件(库和工具)由约7万(70,000)行代码组成,但是它的源代码中还包括4千5百万(45,000,000)行的测试代码。平均一行代码就有700行测试代码

易用性

###1.3 性能和限制
一般情况下,sqlite的局限性主要有以下2个方面:

  1. 并发。sqlite的锁机制是粗颗粒的,它允许多个读,但是一次只允许一个写。写锁会在写期间排它地锁定数据库,其它人在此期间不能访问数据库。
  2. 网络。

sqlite尚未实现的特性,Unsupported sql :

  1. 完整的触发器支持。sqlite几乎支持所有的标准触发器功能,包括递归触发器和instead of 触发器。但是对于所有的触发器类型,当受触发器查询影响的每一行进行评估的时候,sqlite需要for each row行为
  2. 完整的修改表结构支持。目前sqlite只支持rename table 和add column烈性的alter table命令。其它类型的alter table操作,例如drop column,alter column以及add constraint还未实现
  3. 右外连接和全外连接支持
  4. 可更新的视图。sqlite中的视图是可读的,您不能在视图上执行delete,insert或者update操作。但是您可以创建一个启动对视图进行delete ,insert或update的触发器,在触发器内创建完成你需要执行的操作
  5. 窗口功能.ANSI SQL 99的新特性之一就是窗口功能(windowing function)。该功能提供结果集的后处理分析,例如排序,平均移动和之后计算等。sqlite目前仅支持一部分,但是还有部分不支持,比如不支持rank(),row_number()等
  6. 授权和撤销。由于sqlite能读写普通的磁盘文件,因此,唯一可以应用的访问权限就是所在操作系统的普通文件的访问权限。授权(Grand)和撤销(revoke)命令一般是在高端系统中使用的。

2 入门(getting start)

命令行程序

shell模式下的CLP(comman line program)

.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ?ON|OFF?      Turn output mode suitable for EXPLAIN on or off.
                         With no args, it turns EXPLAIN on.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices ?TABLE?       Show names of all indices
                         If TABLE specified, only show indices for tables
                         matching LIKE pattern TABLE.
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.stats ON|OFF          Turn stats on or off
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.trace FILE|off        Output each SQL statement as it is run
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
.timer ON|OFF          Turn the CPU timer measurement on or off

数据库管理

创建数据库

/*执行下列语句,假如当前目录包含数据库文件,则打开数据库文件;若不存在,则不会立马创建数据库文件*/
sqlite> sqlite3 test.db

/* 执行建表语句之后,就会在当前目录创建数据库文件 */
sqlite> create table test_table (id integer primary key ,value text);

获得数据库的schema信息

/* 获取所有的表,视图信息.注意:行末没有分号*/
sqlite> .tables

/* 显示一个表的索引 */
sqlite> .indices test_table

/* .schema 不带任何参数,显示数据库所有对象(表,视图,索引,触发器) 的定义语句*/
sqlite> .schema

/* 更详细的schema信息可以查询sqlite_master表信息 */
sqlite> select * from sqlite_master;


导出数据

sqlite> .output file.sql
sqlite> .dump
sqlite> .output stdout

导入数据

有2种方法导入数据,

  1. 使用.read命令导入(执行)文件中包含的命令
  2. 如果文件中包含逗号或者其他分隔符分隔的值(comma-separated values ,CVS),可以使用.import [table]命令

格式化

/*修改CLP的提示符*/
sqlite>.prompt 'root>'

/* .mode 命令 */

导出带分隔号的数据

sqlite>.output text.csv
sqlite>.separator ,
sqlite>select * from test_table;
sqlite>.output stdout

执行无人值守维护(Performing Unattended Maintenance)

root$ sqlite3 test.db .dump > testdb.sql

root$ sqlite3 test2.db < testdb.sql

root$ sqlite3 -init testdb.sql test3.db

备份数据库

/* 方法1 备份sql */
root$ sqlite test.db .dump > test.sql

/* 方法2 备份二进制文件*/
root$ sqlite3 test.db vacuum
root$ cp test.db test.backup

获得数据库信息

可以使用数据库的 schema pragma 来获得数据库信息,定义如下:

  • pragma database_list:查看附加的数据库
  • pragma index_info : 查看指定索引名称的索引信息 pragma index_info(index_name)
  • index_list:查看指定表名的索引列表。pragma index_list(table_name);
  • table_info: 查看指定表名的表信息。pragma table_info(table_name);

获取数据库文件信息

如果需要了解物理数据库结构的信息,可以使用SQLite Analyzer工具。SQLite Analyzer提供有关sqlite数据库磁盘结构的详细技术信息。这些信息包括数据库、表盒索引分类的单个对象,以及聚合的统计信息。它提供从数据库属性,如页面大小,表的总数,索引,文件大小和页面平均密度(利用率)到单个数据库对象的详细说明等信息。

SQLite Analyzer下载地址

3 sqlite中的sql

3.1 创建数据库

-- 创建表
create table contacts ( id integer primary key,
                        name text not null collate nocase,
                        phone text not null default 'UNKNOWN',
                        unique (name,phone) );



-- 修改表,语法 alter table table { rename to name | add column column_def }


3.2 数据库查询

limit ,offset关键字(ps:使用limit并不会提高查询效率):

select * from food_types order by id limit 1 offset 2;

/* 和上一条sql语句等效 */
select * from food_types order by id limit 2,1;

** 不要依赖于没有使用分组(group by)的非聚合字段的sql语句,因为他们的结果是不确定的 **

比如下面的结果是不确定的,没有实际意义的:

select type_id, count(*) from foods;

应该使用类似于如下的sql

select type_id, count(*) from foods group by type_id;

多表连接查询


{% include site/setup %}连接是多表数据工作的关键。连接操作的结果作为输入,供select语句的其他部分(过滤)处理。

sqlite支持6中不同类型的连接

select foods.name, food_types.name
        from foods, food_types
        where foods.type_id=food_types.id limit 10;
内连接

inner join是最简单的连接查询

Select *
From foods inner join food_types on foods.type_id = food_types.id
交叉连接

如果2个表没有通过任何方式管理,即没有连接条件,select会产生一种更基础的连接(也是最根本的),称为交叉连接或者笛卡尔积。

select *from foods, food_types ;--foods的行数 * food_types的行数
外连接
  • 左外连接
select *
from foods left outer join foods_episodes on foods.id=foods_episodes.food_id;

  • 右外连接(sqlite不支持)
  • 全外连接(sqlite不支持)

子查询

子查询是指select语句中又嵌套select语句,子查询最常应用的地方是where字句,特别是在in操作符中,in操作符是一个双目操作,输入一个值和一列值,如果输入的单个值存在于列中,返回真,否则为假。

-- select子查询可以用来从其他表向结果集添加额外数据。
select name,
        (select count(id) from foods_episodes where food_id=f.id) as count
        from foods f order by count desc limit 10;


-- 在子查询中可以使用Order by 字句。下面sql就是根据各个食品所属组的食品数量由大到小排序。
select * from foods f
order by (select count(type_id)
from foods where type_id=f.type_id) desc;

--使用from子句,有时可能想与其它结果进行连接,而不是单一的基本表.
select f.name, types.name from foods f
inner join (select * from food_types where id=6) types
on f.type_id=types.id;

#### 复合查询(Compound queries)

符合查询和子查询相反,它是使用三种特殊的关系操作符(联合、交叉连接和差集)处理多个查询的结果。在sqlite中使用关键字union,intersectexcept

union操作输入2个关系,A和B,将两者联合成一个只包含A和B中非重复字段的单一关系。默认情况下,union会消除重复数据。如果想在结果中保留重复数据,可以使用union all。

eg,下面sql语句要找出foods中最高频率和最低频率的食品:

select f.*, top_foods.count from foods f
inner join
  (select food_id, count(food_id) as count from foods_episodes
     group by food_id
     order by count(food_id) desc limit 1) top_foods
  on f.id=top_foods.food_id
union
select f.*, bottom_foods.count from foods f
inner join
  (select food_id, count(food_id) as count from foods_episodes
     group by food_id
     order by count(food_id) limit 1) bottom_foods
  on f.id=bottom_foods.food_id
order by top_foods.count desc;

intersect操作输入2个关系A和B,选择那些既在A又在B的行,下面的sql语句是找出episodes介于3~5之间的处于前10的食品。

select f.* from foods f
inner join
  (select food_id, count(food_id) as count
     from foods_episodes
     group by food_id
     order by count(food_id) desc limit 10) top_foods
  on f.id=top_foods.food_id
intersect
select f.* from foods f
  inner join foods_episodes fe on f.id = fe.food_id
  inner join episodes e on fe.episode_id = e.id
  where e.season between 3 and 5
order by f.name;

except操作输入2个关系A和B,找出所有在A中但不在B的行,通过将上述sql中的intersect改为except,可以找出episodes介于3~5之间的不在前10的食品

4 sqlite中的高级sql

###4.1 CRUD操作

-- insert 语句
sqlite> insert into foods (name, type_id) values ('Cinnamon Bobka', 1);
insert into foods
values (null,
       (select id from food_types where name='Bakery'),
       'Blackberry Bobka');
-- 插入多行
sqlite> create table foods2 (id int, type_id int, name text);
sqlite> insert into foods2 select * from foods;
sqlite> select count(*) from foods2;

-- update 语句
update foods set name='CHOCOLATE BOBKA'
where name='Chocolate Bobka';

-- delete 语句
delete from foods where name='CHOCOLATE BOBKA';

4.2 数据完整性


{% include site/setup %}
数据完整性用于定义和保护表内部或表之间的数据关系。一般有四种完整性:域完整性、实体完整性、引用完整性和用户定义完整性。
域完整性涉及控制字段内的值,实体完整性涉及表中的行。引用完整性涉及表之间的行--具体的说就是外键关系。用户定义的完整性包罗万象

实体完整性

unique 限制(唯一性约束)

主键约束

域完整性

默认值、not null,check,排序规则

外键约束(引用完整性)

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

外键约束的规则定义如下:

  • set null:如果主表记录被删除或者不存在了,剩余的子值改为null
  • set default:主表记录被删除了,则子表值改为默认值
  • cascade:更新主表时,级联更新字表记录。删除主表记录时,子表记录级联删除
  • restrict:更新或者删除主表可能会出现孤立的子值,从而阻止(终止)事务
  • no action:不干涉操作执行,只是观察变化。在整个语句(如果约束定义为deferred的事务也一样)的结尾报出错误
  • sqlite只是deferrable子句,用于指定约束是立即执行还是延迟到整个事务结束时。

排序规则

关键字collate定义字段的排序规则。

sqlite支持的数据类型

五种亲和类型: integer ,real , text ,blob, null

视图

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;


--drop

drop view details;

索引

语法格式:create index [unique] index_name on table_name (columns)

sqlite> create table foo(a text, b text);
sqlite> create unique index foo_idx on foo(a,b);

#### 触发器

触发器用于在指定的数据库事件在指定表上触发时,执行指定的sql命令。
触发器语法如下:
create [temp|temporary] triger name [before|after] [insert | delete | update | update of columns] on table;

触发器是通过触发器名称,动作(触发器消息体)和表来定义的。

更新触发器:

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

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

错误处理:sqlite提供一个特殊的sql函数raise()供触发器调用,该函数允许在触发器内产生错误。raise()的定义如下:

raise (resolution ,error_message) ;

第一个参数是冲突解决策略(abort,fail,ignore,rollback等),第二个参数是错误消息。若使用ignore,当前触发器的剩余语句、促使触发器执行的sql命令,以及被触发器执行的触发器,都将终止。如果促使触发器执行的sql语句本身是另一个触发器的组成者,那么该触发器在出发行为的下一个sql命令处继续执行。

4.3 事务

事务的范围

事务由3个命令控制,begin,commit,rollback.
sqlite也支持savepoint和release操作命令,这些命令扩展了事务的灵活性,包含多个语句的工作体可以设置savepoint,回滚可以返回到某个savepoint。

eg:

savepoint test;

rollback [transaction] to test;

冲突的解决

违反约束会导致事务的终止。在对数据进行很多修改的过程中,命令终止会带来什么后果?在大多数数据库都是简单的将签名所做的修改全部取消。这也是数据库处理违反约束的方式。

sqlite有其独特的方法允许你指定不同的方式来处理约束违反,这种功能称为冲突解决
eg:

update foods set id=800-id;

当update执行到388个记录时,id为412的记录已经存在,违反了唯一性约束。因此该命令终止,但是在违反约束之前,sqlite已经更新了387个记录了,应该如何处理?默认行为是终止命令并回滚所有的修改,保存事务的完整性。如果你想保存已经修改的387个记录该怎么办?其实只要使用恰当的冲突解决方案就可以。sqlite提供了5种可能的冲突解决方案,它们可以用来解决冲突(约束违反):
* replace
* ignore
* fail
* abord,sqlite中的默认解决方法。
* rollback
*冲突解决方法既可以在sql命令中指定,也可以在表盒索引的定义中执行。具体的讲,冲突解决方法可以在insert , update , create table, create index中指定。此外,它在触发器中也有具体的含义。

数据库锁

sqlite有5种不同的锁状态: unlocked(未加锁),shared(共享) , reserved(预留) ,pending(未决),exclusive(排它)。每一个数据库连接在同一时间只能处于其中某个状态,每种状态都有一种锁与之对应。

在未加锁状态下,连接还没有访问数据库,当连接一个数据库,甚至已经使用begin开始了一个事务时,连接都还处于未加锁状态。

共享状态,能够从数据库中读数据,多个连接可以同时获得并保持共享锁,也就是说,多个连接可以同时从同一个数据库中读数据。但哪怕只有一个共享锁没有释放,也不允许任何连接写数据。

预留锁,如果一个连接想要写数据库,必须首先获得一个预留锁。一个数据库只能有一个预留锁。该预留锁可以与共享锁共存,它是写数据库的第一个阶段。预留锁既不组织其他拥有共享锁的连接继续读数据库,也不组织其他连接获得新的共享锁。如果一个连接获得了预留锁,它就可以开始处理数据库修改操作了,尽管这些操作只能在缓存区中进行,而不是实际写到磁盘中。

获得未决锁之后,其他连接就不能再获得新的共享锁了,单已经拥有了共享锁的连接仍然可以继续正常度数据库,此时,拥有未决锁的连接等待其他拥有共享锁的连接完成工作并释放其共享锁。

排它锁,一旦所有的共享锁都被释放,拥有未决锁的连接就可以将锁提升为排它锁,此时就可以自由的对数据库进行修改。所有以前缓存的修改都会被写到数据库文件中。

事务的类型

sqlite有三种事务的类型,deferred, ummediate , exclusive.

  • deferred直到必须使用时才获取锁。因为对于延迟事务,begin不会做什么事情--它从未锁定状态开始。这是默认的状态。如果仅仅使用begin开始一个事务,那么事务就是延迟的,提报给刘在未锁定状态,多个连接可以在同一时刻未创建任何锁的情况下开始延迟事务。这种情况下,第一个对数据库的读操作获取共享锁,类似的,第一个对数据库的写操作视图获得预留锁。
  • 由begin开始的immediate事务在begin执行时视图获取预留锁。
  • exclusive事务会视图获取对数据库的排它锁。它和immediate工作方式类似,但是一旦成功,exclusive事务保证数据库中没有其他活动连接,所以就可以对数据库进行任意的读写操作。

4.4 数据库管理

附加数据库

attach [database] filename as database_name;

--detach
detach [database] database_name;

数据库清理

sqlite有2个数据库清理命令,reindex,vacuum.
reindex用于重建索引,2中形式如下:

reindex collation_name;
reindex table_name|index_name

vacuum通过重构十几块文件清理那些未使用的空间。

数据库配置

连接缓冲区大小

--查看
sqlite > pragma cache_size;
--修改
sqlite > pragma cache_size = 10000;

获取数据库信息

--列出所有附着的数据库
sqlite > pragma database_list;

-- 列出索引内相关字段的信息。索引名作为参数
sqlite > pragma index_info(index_name)

-- 列出表中的索引信息.表名作为参数
sqlite > pragma index_list(table_name)

-- 列出表中所有表字段的信息
sqlite > pragma table_info(table_name)

##Sqlite内部机制及新特性

B-tree 和Pager模块

B-tree 为sqlite VDBE提供 O(logN)级时间复杂度 查询和插入,通过双向遍历记录实现O(1)级时间复杂度的删除。B-tree是自平衡的,并能够对碎片清理和内存再分配进行管理

本文地址:https://www.yhawaii.net/32.html
版权声明:本文为原创文章,版权归  所有,欢迎分享本文,转载请保留出处!

 发表评论


表情