1.Sqlite基本介绍
SQLite是一个Acid的数据库管理系统,不是一般的C/S结构,而是被连接到程序中,成为应用的一部分,减少了跨进程通信的开销,采用写入数据时加锁,但同时也支持多任务操作。
SQLite只支持部分触发器,不支持对Alter table的删除和修改列,不进行类型检查。
主要优点:
1.零配置,无需安装。
2.跨平台,
3.最大支持2T
4.足够小,3万行代码
5.开源
主要有两部分构成:前端解析系统,后端引擎。
前端包括:词法分析器,将SQL分割成标识符,传递给解析器。
语法分析器:在指定上下文赋予标识符具体含义。
代码生成器:将标识符组装成sQL之后,生成虚拟机能够执行的代码。
后端引擎:
虚拟机:将前端生成的代码进行执行,
B/B+树:将数据的元组集合排序,帮助vm进行搜索,删除等操作。
页面调度程序:在原始程序的上层实现了面向页面的数据库文件抽象。管理缓存,文件锁定,和事务的ACID等。
2.SQLite的SQL
SQLite 的SQL复合SQL92标准,但是有些功能没有实现。
1.创建数据库
使用:sqlite3+ 数据库名,例如sqlite3 school.db
创建表
将实体数据拆分到各个表中,可以减小单表的体积,提升操作速度。
SQL:create [temp | temporary] table 表名 (字段名 数据类型 约束,字段名 数据类型 约束……);
每条sql都以分号结尾,temp 关键字表示该表是临时表,临时表会在这次连接会话结束时自动销毁。
SQLite的内置数据类型只有5种,但是创建表时不仅仅能使用这些类型:
数据类型 | 说明 |
---|---|
Null | 数值为空 |
INTEGER | 整型 |
REAL | 浮点型 |
TEXT | 字符型,使用数据库编码utf-8或者utf-16.utf-16le |
BLOB | 只是数据块,完全按照输入存放。 |
SQLite除了整型主列外,其他任何列的类型都是未定的,可以存任何类型的值,引擎会在查询过程中对其进行转换。
SQLite没有boolean类型,和日期类型。
类型相关的内容可以参考这篇文章:
http://www.cnblogs.com/bpasser/archive/2011/11/02/2233455.html
类型近似
类型近似是指,存储在该列的数据的推荐类型。任何列都可以存储任何类型,只是会优先 选择一些存储类型,优先选择的类型称为该数据的类型近似。
当创建表时,某一列的数据类型与内置的5种数据类型近似时会自动转换为近似的类型。
类型近似的转换规则表:
数据类型 | Affinity类型 |
---|---|
Interger INT TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 | INTEGER |
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVECHAR(70) NVARCHAR(100) TEXT CLOB | TEXT |
BLOB NO DATATYPE | NONE |
REAL DOUBLE DOUBLE PRECISION FLOAT | REAL |
NUMERIC DECIMAL BOOLEAN DATE DATETIME | NUMERIC |
约束
约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。
约束可以是列级或表级。
列级约束仅适用于列,表级约束被应用到整个表。
以下是在 SQLite 中常用的约束。
NOT NULL 约束:确保某列不能有 NULL 值。
DEFAULT 约束:当某列没有指定值时,为该列提供默认值。
UNIQUE 约束:确保某列中的所有值是不同的,或者对表约束时列的组合是唯一的。
PRIMARY Key 约束:唯一标识数据库表中的各行/记录。
CHECK 约束:CHECK 约束确保某列中的所有值满足一定条件。
以创建classes表为例
CREATE TABLE classes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
mayjor varchar(20)CHECK (length(name)>3),
year integer not null ,
);
主键约束
PRIMARY KEY 约束唯一标识数据库表中的每个记录。在一个表中可以有多个 UNIQUE 列,但只能有一个主键。在设计数据库表时,主键是很重要的。主键是唯一的 ID。
一个表必须有一个主键,默认情况有一个默认的主键rowid,64位整形,自动增长。
外键约束
关系完整性概念,也称为外键,外键确保表中的关键值必须从里一个表中引用,且该数据必须在另一个表中存在,否则将操作失败或者引发其他连锁反应,用户可以定制该反应。
外键引用的表称为父表,含有外键的表称为子表。Android 开启外键功能,执行以下语句:
PRAGMA foreign_keys = ON;
外键约束的格式
FOREIGN KEY (子表的列) REFERENCES 父表(父表的列)
[ON {DELETE | UPDATE } 对应的操作
[NOT] DEFERRABLE INITIALLY {DEFERRED | IMMEDIATE}]
第二行表示,删除或者更新父表时子表对应的操作,
第三行表示,操作的时机,是立即执行还是等待事务结束执行,默认为立即执行,deferable 意为可延缓的。
例如:CREATE TABLE students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name varchar(20)CHECK (length(name)>3),
tel_no varchar(11) not null unique,
cls_id integer not null,
FORENIGN KEY (cls_id) PREFERENCES classes(id),
UNIQUE(name,tel_no)
);
在students中插入数据时,cls_id必须要在classes表中存在,可以指定父表删除和更新对应id行时子表students的操作
操作可以有以下几种:
NO ACTION :不做操作
RESTRICT:禁止删除或更新父键
SET NULL:子键设为null
SET DEFAULT:设为默认值
CASCADE::父表操作时,同时更新或删除子表,有点类似触发器
插入数据
语法:insert into 表名 (字段列表)values (字段对应的值)
如:insert into classes (id , majyor,year) values( 1, “chuanbo “,”2015”);
当字段列表包含表中全部列时可以省略字段列表。
批量插入
insert into 表名(字段列表) 数据集;
创建表stu :create table stu (id integer primary key ,name varchar(20),tel_no varchar(11) not null,cls_id integer not null);
将students的数据复制到stu:insert into stu select * from students;也可以指定某些字段进行插入
快速的复制表:create table stu as select * from stdents; as关键字
此时,创建的表stu是没有约束的表,这种方式可以用来进行数据库升级,将原始表复制到临时表,使用临时表创建新表,删除原始表。
查询数据 Select 语句
数据库的重要功能就是保存数据然后进行查询检索。
SQLite支持除了右外连接和全外连接的所有操作。
操作的输出又可以作为另一个操作的输入,形成嵌套关系。
==select 相当于一个输出结果的关键字==,select的通用格式:
select [distinct] 字段列表 from 表列表 where 条件 group by 字段列表 order by 字段列表 limit 数量,offset 开始位置
从from开始将数据集进行条件筛选,最终得到结果。
from 有多张表时,会将各个表的数据输出。select 后的字段列表的字段前可以加上表名,如:
select classes.mayjor , students.name from classes ,students where classes.id =2;
字段和表的别名
在多表查询时,可能有相同的字段,在表明较长时可能会使SQL较复杂,使用别名来替代表名和字段名可以简化我们的SQL。
如:
select classes.major ,students.name from classes ,students where classes.id = 2 and classes.year > 2011;
别名简化:
select classes.major as clsmj ,students.name as stunm from classes as cls,students as stu where cls.id = 2 and cls.year >2011;
where 子句
where 用于确定筛选条件,可以包含算术运算操作和逻辑操作
+ ,-,*,/,=,!=,>,<,<=,>=
逻辑操作包括:or not,and
还有like 关键字,用于模糊匹配,如
select * from students where name like “t%”;输出包含所有包含t开头的名字。
group by 分组
分组是对聚合查询结果进行操作的,按照字段进行分组输出,如:
select count(*) ,cls_id from students group by cls_id;
得到学生表中的class.id 相同的人数和cls_id两列
having 过滤 groupby的结果
group by的结果 也可以进行筛选,使用having
如:
select count(*),cls_id from students group by cls_id having cls_id >2;
以cls_id分组输出cls_id 大于2 的students的人数
排序order by
asc 升序,desc降序
对select 的结果进行排序,如: select * from students order by name asc,year desc;
数量限定 limit
通常由于数据量较大只显示一部分数据,如分页显示
select * from students limit 10,offset 2;
显示students的前10条,并且从rowid 为2 开始,即从第三条开始检索。
distinct 去重
distinct 放在select后将查询结果的重复行去除,如:
select distinct cls_id from students;
查询cls_id同时去除重复的cls_id
聚合函数
聚合函数可以对字段、结果集做处理。有:
函数名 | 作用 |
---|---|
count() | 计算数据集的数量 |
Avg() | 统计某个字段的平均值 |
Min() | 数据集的最小值 |
Max() | 数据集的最大值 |
Length() | 计算字段长度 |
Upper() | 字符串类型字段值转为大写 |
Lower() | 转为小写 |
Abs() | 计算数值的绝对值 |
聚合函数放在select后,
例如统计students表中各个班级的人数
==select 相当于一个输出结果的关键字==
select count(*) ,cls_id from students group by cls_id;
students 名字的平均长度:
select avg(length(name)) from students;输出一个整数;
多表连接,根据where查多表
通过students的cls_id,可以查询classes中对应id的行每个字段信息,如:
select * from students ,classes where students.cls_id == classes.id;
得到的结果是每个student的字段和具有相同cls_id的classes里的字段,students的每一行都要在classes所有行中查找对应的id;
内连接,A inner join B on 条件
根据关系代数的交叉操作,对两个表进行筛选,。两个表中都符合条件的数据才会被输出。
同样是上面的功能:
内连接实现:select * from students inner join classes on students.cls_id = classes.id;
左外连接 left outer join on
与inner join 的区别是如果条件不匹配,那么结果集会返回左表中的数据,右表中的数据由空值填充。
select * from students left outer join classes on students.cls_id = classes.id;
如果students有cls_id在classes中不存在则数据结果集会返回students的行,在该结果集中classes的字段用空值填充。
右外连接和全外连接
右外连接和左外连接相反,左表又不匹配的行在结果集中用空值填充,全外连接,左表或者右表又不匹配的都用空值填充。SQLite不支持这两种查询。
子查询
SQLite 子查询
子查询或内部查询或嵌套查询是在另一个 SQLite 查询内嵌入在 WHERE 子句中的查询。
使用子查询返回的数据将被用在主查询中作为条件,以进一步限制要检索的数据。
子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用,可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。
以下是子查询必须遵循的几个规则:
1 子查询必须用括号括起来。
2 子查询在 SELECT 子句中只能有一个列,除非在主查询中有多列,与子查询的所选列进行比较。
3 ORDER BY 不能用在子查询中,虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY,功能与 GROUP BY 相同。
4 子查询返回多于一行,只能与多值运算符一起使用,如 IN 运算符。
5 BETWEEN 运算符不能与子查询一起使用,但是,BETWEEN 可在子查询内使用。
update
update用于更新表的字段值,其实是个字段赋值语句。
update table students set name =”houson” , tel_no = “119” where id=0;
update 如果主键重复了可能会失败。
delete
delete from .. where ..
用于删除表的数据,delete from classes ,将删除classes中所有数据
修改表alter
Sqlite 的修改表包括:重命名和增加字段,不包括删除字段等。
alter table students rename to stu_table;
alter table students add column age default >0;
要删除字段需要创建,临时表进行数据复制。drop掉原始表。
实现如下
create table stu_tmp (id integer primary key autoincrement ,name varchar(20) not null,tel_no varchar(11) not null ,cls_id integer not null) ;
insert into stu_tmp id,name,tel_no select id,name,tel_no from students;
drop table students;
alter table stu_tmp rename to students
创建索引
当表比较大,而查询频率较高时,可以使用索引表为某些字段进行查询加速,索引就像书的目录存储了每条记录的地址。
create index [unique] 索引名 on 表名 (字段列表)
创建索引表会使数据库体积增大,减慢insert ,delete,insert 的操作,需要权衡使用。
SQLite根据查询条件判断是否使用索引。
当条件与索引字段判等时,使用索引,当多个字段时,从第一个不等的字段开始,后续字段不使用索引。
如:
create index student_table_index on students (id ,name,cls_id);
select * from students where id = 1 and cls_id = 2;
由于name没有出现在查询条件中,因此b开始和索引字段不判等,只有id 字段会使用索引。
select * from students where id >1 and name =”houson” and cls_id =4;
此时,虽然字段的名字都有但是,id是>,不是= ,所以不判等。不使用索引
创建视图 view
视图是动态生成的虚拟表,存储的是一条select 语句的结果集。操作视图就是相当于执行这条select语句,view不会存储到数据库中,方便简化查询。
create [TEMP | TEMPORARY] view 视图名 as select语句;
select * from view_name ;
视图(View)是一种虚表,允许用户实现以下几点:
用户或用户组查找结构数据的方式更自然或直观。
限制数据访问,用户只能看到有限的数据,而不是完整的表。
汇总各种表中的数据,用于生成报告。
SQLite 视图是只读的,因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器,当尝试 DELETE、INSERT 或 UPDATE 视图时触发,需要做的动作在触发器内容中定义。
删除视图
要删除视图,只需使用带有 view_name 的 DROP VIEW 语句。DROP VIEW 的基本语法如下:
sqlite> DROP VIEW view_name;
创建 触发器
触发器用于当特定表的操作操作时的回调函数,
create [temp | temporary] trigger 触发器名 [before | after ] [insert | update | delete | select of 字段列表 ] on 表名
begin
操作
end;
三大要素:触发器名,表名,操作。before|after表示操作在事件发生之前还是之后
可以用于生成日志。
例如:create table delete_log (stu_id ,name );
create triggle delete_trig after delete on students
begin
insert into delete_log (stu_id , name) values(old.id,old.name);
end;
还可以添加时间字段datetime(‘now’);
删除时被删除的数据为old,插入时为new
drop命令
用于删除物理存储介质,表、视图、索引、触发器等。
drop [table | view | index | trigger] name;
数据库事务
事务是数据库操作的执行单元,定义了一条或多条SQL语句,要么不被执行,要么全被执行。begin、commit、roolback三条命令。
begin
delete from students where id = 1;
rollback;
rollback回滚表示delete没有被执行。
事务保证了原子性,提升SQL执行效率。
SQLite资料:http://www.runoob.com/sqlite/sqlite-data-types.html