一、基础知识
1.1 SQLite介绍
- SQLite:SQLite是一个进程内的库,实现了自给自足的、无服务器的、零配置的、事务性的SQL数据库引擎。它是一个零配置的数据库,这意味着与其他数据库一样,不需要在系统中配置。就像其他数据库,SQLite引擎不是一个独立的进程,可以按应用程序需求进行静态或动态连接。SQLite直接访问其存储文件。是一款轻型的嵌入式数据库。
优点:
- 不需要一个单独的服务器进程或操作的系统
- SQLite不需要配置,不需要安装或管理
- 一个完整的SQLite数据库是存储在一个单一的跨平台的磁盘文件
- SQLite是非常小,是轻量级
- SQLite是自给自足的,不需要任何外部依赖
- SQLite事务是完全兼容ACID的,允许从多个进程或线程安全访问
- SQLite支持SQL92标准的大多数查询语言的功能
- SQLite使用ANSI-C编写的,并提供了简单和易于使用的API
- SQLite支持UNIX和Windows中运行,跨平台特性
数据类型
存储类 | 描述 |
---|---|
NULL | 值是一个NULL值 |
INTEGER | 值是一个带符号的整数,根据值的大小存储在1、2、3、4、6或8字节中 |
REAL | 值是一个浮点值,存储为8字节的IEEE浮点数字 |
TEXT | 值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE或UTF-16LE)存储 |
BLOB | 值是一个blob数据,完全根据它的输入存储 |
4. 亲和(Affinity)类型
任何列仍然可以存储任何类型的数据,当数据插入时,该字段的数据将会优先采用亲缘类型作为该值的存储方式。
亲和类型 | 描述 |
---|---|
TEXT | 数值型数据在被插入前,需要先被转换为文本格式,之后再插入到目标字段中 |
NUMERIC | 当文本数据被插入到亲缘性为NUMERIC的字段中时,如果转换操作不会导致数据信息丢失以及完全可逆,那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据,如果转换失败,SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据,SQLite将不会做任何转换,直接以NULL或BLOB的方式存储该数据。需要额外说明,对于浮点格式的常量文本,如“30000.0”,如果该值可以转换为INTEGER同时又不会丢失数值信息,那么SQLite就会将其转换为INTEGER的存储方式 |
REAL | 其规则基本等同于NUMERIC,唯一的差别是不会将”30000.0”这样的文本数据转换为INTEGER存储方式 |
NONE | 不做任何的转换,直接以该数据所属的数据类型进行存储 |
5. 亲和类型(Affinity)及类型名称
数据类型 | 亲和类型 |
---|---|
INT | INTEGER |
INTEGER | |
TINYINT | |
SMALLINT | |
MEDIUMINT | |
BIGINT | |
UNSIGNED BIG INT | |
INT2 | |
INT8 | |
CHARACTER(20) | TEXT |
VARCHAR(255) | |
VARYING CHARACTER(255) | |
NCHAR(55) | |
NATIVE CHARACTER(70) | |
NVARCHAR(100) | |
TEXT | |
CLOB | |
BLOB | NONE |
no datatype specified | |
REAL | REAL |
DOUBLE | |
DOUBLE PRECISION | |
FLOAT | |
NUMERIC | NUMERIC |
DECIMAL(10.5) | |
BOOLEAN | |
DATE | |
DATETIME |
6. Boolean数据类型
SQLite没有单独的Boolean存储类,相反,布尔值被存储为整数0(false)和1(true)
7. Date与Time数据类型
SQLite没有一个单独的用于存储日期和/或时间的存储类,但是SQLite能够把日期和时间存储为TEXT、REAL或INTEGER值
存储类 | 日期格式 |
---|---|
TEXT | 格式为“YYYY-MM-DD HH:MM:SS.SSSS”的日期 |
REAL | 从公元前4714年11月24日格林尼治时间的正午开始算起的天数 |
INTEGER | 从1970-01-01 00:00:00 UTC算起的秒数 |
8. 约束
约束类型 | 描述 |
---|---|
NOT NULL | 确保某列不能有NULL值 |
DEFAULT | 当某列没有指定值时,为该列提供默认值 |
UNIQUE | 确保某列中的所有值是不同的 |
PRIMARY Key | 唯一标识数据库表中的各行/记录 |
CHECK | 确保某列中的所有值满足一定条件 |
9. 触发器(Trigger)
SQLite的触发器是数据库的回调函数,它会自动执行/指定的数据库时间发生时调用。
- SQLite的触发器(Trigger)可以指定在特定的数据库表发生DELETE、INSERT或UPDATE时触发,或在一个或多个指定表的列发生更新时触发
- 只支持FOR EACH ROW触发器(Trigger),没有FOR EACH STATEMENT触发器(Trigger)。因此,明确指定FOR EACH ROW是可选的
- WHEN子句和触发器(Trigger)动作可能访问使用表单NEW.column-name和OLD.column-name的引用插入、删除或更新的行元素,其中column-name是从与触发器关联的表的列的名称
- 如果提供WHEN子句,则只针对WHEN子句为真的指定行执行SQL语句。如果没有提供WHEN子句,则针对所有行执行SQL语句
- BEFORE或AFTER关键字决定何时执行触发器动作,决定是在关联行的插入、修改或删除之前或者之后执行触发器动作
- 当触发器相关联的表删除时,自动删除触发器(Trigger)
- 要修改的表必须存在于同一数据库中,作为触发器被附加的表或试图,且必须只使用tablename,而不是database.tablename
- 一个特殊的SQL函数RAISE()可用于触发器程序内抛出异常
10.索引(Index)
- 索引(Index)是一种特殊的查找表,数据库搜索引擎用来加快数据检索。简单说,索引是一个指向表中数据的指针
- 有助于加快SELECT查询和WHERE子句,但是也会减慢使用UPDATE和INSERT语句时的数据输入。
- 索引可以创建或删除,但不影响数据
- 使用CREATE INDEX语句创建索引,它允许命名索引,指定表及要索引的一列或多列,并指示索引是升序排列还是降序排列
- 索引也可以是唯一的,与UNIQUE约束类似,在列上或列组合上防止重复条目
- 索引不应该使用在较小表上
- 索引不应该使用在有频繁的大批量的更新或插入操作的表上
- 索引不应该使用在含有大量的NULL值的列上
- 索引不应该使用在频繁操作的列上
11.事务(Transaction)
- 事务(Transaction)是一个对数据库执行工作单元。是以逻辑顺序完成的工作单位或序列,可以用户手动操作完成,也可以是有某种数据库程序自动完成
- 事务(Transaction)是一个或多个更改数据库的扩展。例如,正在创建一个记录或者更新一个记录或者从表中删除一个记录,那么正在改表上执行事务。重要的是要控制事务以确保数据的完整性和处理数据库错误
- 事务属性ACID
- 原子性(Atomicity):确保工作单位内的所有操作都成功完成,否则事务会在出现故障时终止,之前的操作也会回滚到以前的状态
- 一致性(Consistency):确保数据库在成功提交的事务上正确地改变状态
- 隔离性(Isolation):使事务操作相互独立和透明
- 持久性(Durability):确保已提交事务的结果或效果在系统发生故障的情况下仍然存在
- 事务控制
- BEGIN TRANSACTION:开始事务处理
- COMMIT:保存更改,或者可以使用END TRANSACTION命令
- ROLLBACK:回滚所做的更改