进入SQLite

  • sqlite3：进入SQLite
  • sqlite3 数据库路径：如果该路径存在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，

idvalue
1a
2b
3c
4d
5e
6f

则命令

CREATE TABLE IF NOT EXISTS select_table AS SELECT * FROM default_table WHERE id < 5;

创建出的表select_table的结构和数据为

idvalue
1a
2b
3c
4d
  • 注意：通过查询语句创建出来的表中有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不转化

声明类型可以自定义，自定义的类型会按照下述规则指向上面表里的五个声明类型之一。

存储条件实际声明类型
类型名称包含INTINTEGER
类型名称包含CHARCLOB或TEXTTEXT
类型名称包含BLOB 或者没写类型名称BLOB
类型名称包含REAL，FLOA，和DOUBREAL
其他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。生成列可以包含其他生成列，但是不能直接或间接地包括自身。

区别VIRTUALSTORED
赋值时机查询的时候插入行的时候
消耗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 )
);

按默认值添加三行，结果如下：

idrandgenerated_virtualgenerated_storedgenerated_default
19101020
279918
347714

使用生成列需要注意以下下事项：

  • 生成列不能是主键或者主键的一部分（未来有可能对存储列放宽要求）
  • 生成列的表达式部分只能引用同一行中的常量字面量，同时只能使用标量确定型函数。不能使用子查询、聚合函数、窗口函数或者表值函数（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约束。

idrand10rand100
115
2177
NOT NULL约束

NOT NULL约束限制了一列的值不能是NULL。和Check约束一样，NOT NULL约束在插入和更新数据时检测，因此如果出现数据库损坏等问题，查出的数据也有可能是NULL。

conflict-clause语句

PRIMARY KEY, NOT NULL, UNIQUE等语句给数据提出了要求，conflict-clause用于指定数据和要求冲突时的处理方式。可配置的处理方式有ABORT（默认）、ROLLBACK、FAIL、IGNORE和REPLACE。当遇到适用的冲突时，它们有不同的处理方式，如下。

关键字处理方式
ABORT1. 中止当前SQL语句；
2. 返回SQLITE_CONSTRAINT异常；
3. 回退当前SQL语句所做的任何更改，但是由同一事务中先前SQL语句引起的更改被保留，并且该事务保持活动。
ROLLBACK1. 中止当前SQL语句；
2. 返回SQLITE_CONSTRAINT异常；
3. 除了在每个命令上创建的隐含事务外，如果没有事务处于活动状态，则与ABORT算法相同。
FAIL1. 中止当前SQL语句；
2. 返回SQLITE_CONSTRAINT异常；
3. 不会回退先前对失败的SQL语句所做的更改，也不会结束事务。
FAIL行为仅适用于唯一性、NOT NULL和CHECK约束。违反外键约束会导致ABORT。
IGNORE1. 跳过包含该约束冲突的一行，并继续处理SQL语句的后续行，就像没有出错一样；
IGNORE不返回唯一性、NOT NULL和UNIQUE约束错误。但是处理外键约束的方法与ABORT类似。
REPLACE1. 在插入或更新当前行之前删除导致约束冲突的预先存在的行，并且命令继续正常执行；
2. 如果发生NOT NULL约束冲突，如果该列有默认值，则将NULL值替换为该列的默认值，否则则使用ABORT算法。
3. 如果发生CHECK约束或外键约束冲突，则使用类似ABORT的方法

ROWID

ROWID是默认存在的一列，类型时有符号64位整型。从3.8.2 (2013-12-06)版本开始，SQLite支持了没有ROWID的表

# sqlite # 数据库 # 学习
Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

认识一下JavaScrip中的元编程

avatar 华为云开发者联盟
cover

世界读书日 | 开发者必读书单重磅来袭，华为云DTSE专家天团力荐

avatar 华为云开发者联盟
cover

重磅新品发布！云耀数据库HRDS，享受轻量级的极致体验

avatar 华为云开发者联盟