进入SQLite
sqlite3
:进入SQLitesqlite3 数据库路径
:如果该路径存在SQLite数据库,则打开数据库;否则等到添加数据表,视图等数据库对象时,在该路径创建数据库
创建表
创建表命令的关键字是CREATE TABLE
,创建表的命令图如下所示。
注意:sqlite_开头的表名是保留表名,供SQLite内部使用
TEMP/TEMPORARY
SQLite支持创建正式表(默认)和临时表。
创建正式表:
- 关键字:
CREATE TABLE
- 示例:
CREATE TABLE default_table
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
value VARCHAR
);
创建临时表:
- 关键字:
CREATE TEMP TABLE
或者CREATE TEMPORARY TABLE
- 示例
CREATE TEMP TABLE temp_table
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
value VARCHAR
);
IF NOT EXISTS
- 背景:数据库中已经存在一张名称为
table
的表 - 操作:再创建一张名称为
table
的表 - 结果
- 不使用
IF NOT EXISTS
时:报错[SQLITE_ERROR] SQL error or missing database (table default_table already exists)
- 使用
IF NOT EXISTS
时:不报错,也不创建表。
- 不使用
schema-name
schema-name
表示要创建的表所属的数据库。只能填main
, temp
和附加数据库。其中main
对应正式表;temp
对应临时表;附加数据库对应在某一个附加数据库中创建的表。
要把数据库attached_to_sqlite_study.db
作为当前数据库的附加库,只需要执行下述命令即可。如果附加库已存在,则直接关联上,否则先创建一个数据库,再做关联。
-- 把数据库attached_to_sqlite_study.db作为当前数据库的附加库,并取别名(schema-name)为attached
ATTACH DATABASE 'attached_to_sqlite_study.db' AS 'attached';
select-stmt
CREATE TABLE ... AS SELECT
语句根据查询语句的结果,创建与填充表内容。
假如已经存在表default_table
,
id | value |
---|---|
1 | a |
2 | b |
3 | c |
4 | d |
5 | e |
6 | f |
则命令
CREATE TABLE IF NOT EXISTS select_table AS SELECT * FROM default_table WHERE id < 5;
创建出的表select_table
的结构和数据为
id | value |
---|---|
1 | a |
2 | b |
3 | c |
4 | d |
- 注意:通过查询语句创建出来的表中有rowid没有
主键
和约束
,每个列的默认值都是NULL
,同时排序方式为BINARY
column-def和table-constraint
除了CREATE TABLE ... AS SELECT
类操作外,创建表必须包含列信息。创建没有列信息的数据表会报错。 另外部分约束是针对多列或者整个表而言(table-constraint),可以集中写在列信息后面。表约束也可以用于约束某一列。约束的检查出现在增(INSERT)和改(UPDATE和部分ALERT)时,删和查的时候一般不会检查约束的限制。
- 代码示例
-- 尝试创建一个没有列的数据表
CREATE TABLE no_column_table();
- 报错:
[SQLITE_ERROR] SQL error or missing database (near ")": syntax error)
定义列的命令图如下所示。
包括了列名(column-name)、类型(type-name)和列约束(column-constraint)。其中只有列名是必须的。 - 类型:SQLite中,列的类型不会被用来限制存入数据的类型。实际上SQLite采用的是动态类型。
- 列约束:可选的列约束包括主键、空判断、逻辑判断、默认值、排序方式、外键、生成列等。
列约束的命令图如下所示。
表约束的命令图如下所示
列约束和表约束限制了数据的取值范围,可以通过下面命令暂时忽略。
PRAGMA ignore_check_constraints=ON
数据类型
SQLite中包含5个主要存储类型NULL,INTEGER,REAL,TEXT和BLOB;还有5个主要声明类型:NUMERIC,INTEGER,,REAL,TEXT和BLOB。
每个值都被存储为下表所示类型之一。
存储类型 | 描述 |
---|---|
NULL | 空 |
INTEGER | 整型,字节数可以是0,1,2,3,4,6,和8,读出来都是8字节有符号整型 |
REAL | 浮点类型,8字节IEEE浮点数 |
TEXT | 文本类型,支持的编码类型有UTF-8,UTF-16BE和UTF-16LE |
BLOB | 块数据,输入是什么样就存储成什么样 |
为了兼容其他SQL数据库,SQLite还有些其他类型,但是实际上都是上面这五个类型。比如:BOOLEAN:其实是INTEGER类型,1代表true;0代表false。
对应与存储类型,在声明列的类型时也有五种
SQLite的声明类型实际上是动态类型,其亲和力使得每个类型都不会对数据的类型产生限制。
声明类型 | 描述 |
---|---|
NUMERIC | 支持转换为所有5种存储类型: 整数被存储为INTEGER(64位有符号整型存储不了时,存储为REAL); 浮点数(包括小数部分为0的浮点数)被存储为REAL(按10进制保留15位有效数字); |
INTEGER | 和NUMERIC相似,但是小数部分为0的浮点数会被转换为整型存储 |
REAL | 和NUMERIC相似,但是整数会被转换为小数部分为0的浮点数存储 |
TEXT | 可能被存储为NULL,TEXT和BLOB三种类型,数字类数据会被存储为TEXT类型 |
BLOB | 不转化 |
声明类型可以自定义,自定义的类型会按照下述规则指向上面表里的五个声明类型之一。
存储条件 | 实际声明类型 |
---|---|
类型名称包含INT | INTEGER |
类型名称包含CHARCLOB 或TEXT | TEXT |
类型名称包含BLOB 或者没写类型名称 | BLOB |
类型名称包含REAL ,FLOA ,和DOUB | REAL |
其他 | NUMERIC |
DEFAULT语句
DEFAULT语句为一列提供了默认值,如果插入一行数据时没有填该列,则该列被DEFAULT语句对应的值填充,否则填充NULL。默认值本身也可以是NILL。DEFAULT后面可以接表达式、具体值和时间。
表达式里面还有比较丰富的结构,有待本人研究。时间可以填CURRENT_TIME
、CURRENT_DATE
和CURRENT_TIMESTAMP
,格式分别为HH:MM:SS
、YYYY-MM-DD
和YYYY-MM-DD HH:MM:SS
。
COLLATE语句
排序语句用于关键字的排序依据。可以配置为BINARY(默认)、NOCASE和RTRIM。
- BINARY:使用memcmp()比较数据。
- NOCASE:即不区分大小写的对比模式。除了把大写字母视为小写字母外,和
BINARY
相同。 - RTRIM:除了会忽略末尾的空格外,和
BINARY
相同。
(GENERATED ALWAYS )AS语句
GENERATED ALWAYS AS
用于修饰生成的列,可以简写为AS
。生成的列的值,由同一行其他列的值计算而得到。生成的列可以是虚拟列VIRTUAL
(默认)或者是存储列STORED
。生成列可以包含其他生成列,但是不能直接或间接地包括自身。
区别 | VIRTUAL | STORED |
---|---|---|
赋值时机 | 查询的时候 | 插入行的时候 |
消耗 | CPU | 存储空间 |
添加列 | 可以通过ALTER TABLE 添加 | 不能通过ALTER TABLE 添加 |
下面的命令创建了一个名为generate_table
的表。注意:生成的列不需要都在末尾,可以在表的任意位置。
CREATE TABLE IF NOT EXISTS generate_table(
id INTEGER PRIMARY KEY AUTOINCREMENT,
rand INTEGER DEFAULT (random()%10),
-- 生成虚拟列
generated_virtual INTEGER GENERATED ALWAYS AS ( id + rand ) VIRTUAL,
-- 生成存储列
generated_stored INTEGER GENERATED ALWAYS AS ( id + rand ) STORED,
-- 生成虚拟列
generated_default INTEGER GENERATED ALWAYS AS ( generated_virtual + generated_stored )
);
按默认值添加三行,结果如下:
id | rand | generated_virtual | generated_stored | generated_default |
---|---|---|---|---|
1 | 9 | 10 | 10 | 20 |
2 | 7 | 9 | 9 | 18 |
3 | 4 | 7 | 7 | 14 |
使用生成列需要注意以下下事项:
- 生成列不能是主键或者主键的一部分(未来有可能对存储列放宽要求)
- 生成列的表达式部分只能引用同一行中的常量字面量,同时只能使用标量确定型函数。不能使用
子查询
、聚合函数
、窗口函数
或者表值函数
(table-valued)。 - 生成列可以包含其他生成列,但是不能直接或间接地包括自身。
- 生成列不能读取
ROW ID
列的值,但是可以读取INTEGER PRIMARY KEY
的值。 - 表中至少要有一列不是生成的列。
ALTER TABLE ADD COLUMN
命令可以用来添加虚拟列,但是不能用来添加存储列。- 表达式的数据类型和排序逻辑不影响列本身的逻辑。
- 生成列功能的引入版本为3.31.0 (2020-01-22)。
PRIMARY KEY
PRIMARY KEY
代表表的主键,一张表只能有一个主键。主键的性质如下:
- 主键可以由一个或者多个列构成,当
PRIMARY KEY
用作列约束时,代表主键只由这一列构成。 - 对于没有
ROWID
的表来说,主键是必须存在的;而对于其他表来说,是不一定存在的。 - 如果主键仅由一列构成,同时这一列的类型是
INTEGER
,则 - 主键列的组合不能重复。
- NULL也可以作为主键,而且
NULL
被认为不同于其它所有值,包括其它NULL
。在SQL标准
下,主键不应该被允许传NULL
,但是由于历史BUG,SQLite支持传NULL
。
UNIQUE约束
UNIQUE
和PRIMARY KEY
的含义相似,即不能相同。两者通常都是通过创建唯一索引(CREATE UNIQUE INDEX
)达到去重的目的。仅无ROWID
表上的INTEGER PRIMARY KEY
和PRIMARY KEYs
例外。INTEGER PRIMARY KEY
是RowId的拷贝,而PRIMARY KEYs
由多个列构成。
注意:这里的INTEGER PRIMARY KEY
不包含特殊情况:INTEGER PRIMARY KEY DESC
。
Check约束
Check约束用于在插入和更新数据时检查数据是否符合要求。Check约束的表达式结果都会被转换为NUMERIC
类型,转换方法和Cast命令用的相同。如果是0(包括整型0和浮点类型0.0)则发生约束冲突,即不符合要求;如果是其他值(包括NULL),则代表符合要求。Check约束既可以约束当前列,也可以约束其他列,也可以约束列之间的关系等。
下面的命令创建了一个名为check_table
的表。其中列rand10
被rand10 > 0
和rand100 > rand10
两个Check约束限制;而列rand100
被约束NULL
限制。由于返回值为NULL
代表符合约束条件,所以相当于列rand100
没有被约束
CREATE TABLE IF NOT EXISTS check_table(
id INTEGER PRIMARY KEY AUTOINCREMENT,
rand10 INTEGER CHECK ( rand10 > 0 ) CHECK ( rand100 > rand10 ) DEFAULT (random()%10),
rand100 INTEGER CHECK ( NULL ) DEFAULT (random()%100)
);
多次插入默认行后,出现了多个报错,它们分为两种,如下所示
[SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: rand10 > 0)
[SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: rand10 > 0)
同时也成功插入了两行,如下表所示。成功添加的数据都满足所有Check约束。
id | rand10 | rand100 |
---|---|---|
1 | 1 | 5 |
2 | 1 | 77 |
NOT NULL约束
NOT NULL约束限制了一列的值不能是NULL
。和Check约束一样,NOT NULL约束在插入和更新数据时检测,因此如果出现数据库损坏等问题,查出的数据也有可能是NULL。
conflict-clause语句
PRIMARY KEY
, NOT NULL
, UNIQUE
等语句给数据提出了要求,conflict-clause
用于指定数据和要求冲突时的处理方式。可配置的处理方式有ABORT
(默认)、ROLLBACK
、FAIL
、IGNORE
和REPLACE
。当遇到适用的冲突时,它们有不同的处理方式,如下。
关键字 | 处理方式 |
---|---|
ABORT | 1. 中止当前SQL语句; 2. 返回 SQLITE_CONSTRAINT 异常;3. 回退当前SQL语句所做的任何更改,但是由同一事务中先前SQL语句引起的更改被保留,并且该事务 保持活动 。 |
ROLLBACK | 1. 中止当前SQL语句; 2. 返回 SQLITE_CONSTRAINT 异常;3. 除了在每个命令上创建的隐含事务外,如果没有事务处于活动状态,则与 ABORT 算法相同。 |
FAIL | 1. 中止当前SQL语句; 2. 返回 SQLITE_CONSTRAINT 异常;3. 不会回退先前对失败的SQL语句所做的更改,也不会结束事务。 FAIL行为仅适用于 唯一性 、NOT NULL 和CHECK 约束。违反外键约束会导致ABORT 。 |
IGNORE | 1. 跳过包含该约束冲突的一行,并继续处理SQL语句的后续行,就像没有出错一样;IGNORE 不返回唯一性 、NOT NULL 和UNIQUE 约束错误。但是处理外键约束 的方法与ABORT 类似。 |
REPLACE | 1. 在插入或更新当前行之前删除导致约束冲突的预先存在的行,并且命令继续正常执行; 2. 如果发生 NOT NULL 约束冲突,如果该列有默认值 ,则将NULL 值替换为该列的默认值 ,否则则使用ABORT 算法。3. 如果发生 CHECK 约束或外键 约束冲突,则使用类似ABORT 的方法 |
ROWID
ROWID是默认存在的一列,类型时有符号64位整型。从3.8.2 (2013-12-06)版本开始,SQLite支持了没有ROWID的表