SQLite3表与字段

本文讨论SQLite3数据库中如何创建、删除数据表、修改表名，以及如何定义表的字段等操作。

创建表

创建数据表时使用create table语句，基本应用格式如下：

create table <表名> (<字段定义>)

前一篇文章中，在导入CSV数据时选择“禁用类型检测”，创建表时，字段没有指定数据类型，这也是最简单的字段定义方法，即只使用字段名；但在实际工作中，字段的数据类型及其它属性一般都会有明确的定义。

此外，在SQL语句中，表名、字段名等对象名称可以直接使用字面量，如create table t1(f1)；也可以使用一对双引号或一对方括号定义，如create table "t1"("f1")；、create table[t1]([f1]);如果对象名称中包含SQL关键字或特殊字符，则应该使用双引号或方括号定义。

字段定义

字段定义中，首先需要确定字段名称，然后是数据类型，SQLite3中有四种基本的数据类型，分别是integer（整数）、real（浮点数）、text（文本）、blob（二进制大对象）。

默认情况下，字段数据是可以为空的（null），如果不允许字段数据为空，定义字段时需要添加not null子句。

指定字段默认值时使用default关键字，如default 0。如果字段数据不能为空，可以设置一个常用的默认值，在添加记录时，如果没有指定此字段的数据，则保存为默认值。

check关键字可以指定数据的规则，如check(f2 in (0,1,2))表示f2字段的值只能是0、1、2中的一个。

唯一约束，使用unique关键字定义。定义为唯一约束的字段，其数据在每条记录中是唯一的，即此字段数据不能重复。

关于数值、文本、日期和时间等类型的数据操作，在后续文章中还会有详细讨论。

下面的代码会在打开的SQLite3数据库中创建t1表。

create table t1(
f1 text unique not null,
f2 integer check(f2 in (0,1,2)) default 0,
f3 text,
f4 text
);

可以在SQLite Browser中打开或新建数据库文件，并在“执行SQL”页中输入代码，然后使用键盘F5功能键或点击工具栏中的执行按钮执行代码，执行成功后，可以在“数据库结构”页中看到新创建的t1表，如下图所示。

创建数据表

本例，创建的t1表中包含4个字段，分别是：

• f1，定义为文本类型，不能为空，且数据唯一。
• f2，定义为整数类型，只能是0、1、2中的一个值，默认为0。
• f3，定义为文本类型。
• f4，定义为文本类型。

下面的代码可以在t1表中添加一条记录。

insert into t1(f1,f2,f3,f4)
values('user01',1,'2024-12-19 11:40:32','111');

然后，可以使用如下代码查询添加的数据。

select * from t1;

代码执行结果如下图所示。

查询数据

此外，还可以在“浏览数据”页中查看数据，如下图所示。

浏览数据

rowid字段与自增长字段

管理数据时，经常在记录中使用整数类型的序号作为记录的唯一标识，在SQLite数据库的表中，会自动包含一个名为rowid的字段，其功能就是自动管理记录序号，在SQL语句中可以使用rowid字段名调用，如下面的代码。

select rowid,* from t1;

代码会显示t1表中的rowid字段和全部显式定义的字段数据，执行结果如下图所示。

使用rowid字段

请注意，如果需要使用rowid字段数据，只能显式调用字段名，使用星号（*）通配符只能获取显式创建的字段。需要创建自定义的ID字段时，可以参考如下代码。

create table t1a(
recid integer primary key autoincrement,
f1 text unique not null,
f2 integer check(f2 in (0,1,2)) default 0,
f3 text,
f4 text
);

代码会创建t1a表，其中，recid字段定义为integer类型，使用primary key关键字定义为主键字段，并使用autoincrement关键字定义为自增长字段；这里，recid字段的表现会与默认的rowid字段相同，表中，recid字段数据会从1开始，每条记录会自动加1。

下面的代码会在t1a表中添加3条记录。

insert into t1a(f1,f2)
values('user01',1),
('user02',2),
('user03',0);

下面的代码可以查询t1a表的所有记录。

select * from t1a;

代码查询结果如下图所示。

查询数据

当数据库中有表定义了自增长（autoincrement）字段以后，SQLite3数据库会自动创建一个名为sqlite_sequence的系统表；其中包含了数据表中自增长字段的最大值；可以通过如下代码查询sqlite_sequence表的数据

select * from sqlite_sequence;

代码查询结果如下图所示。

查看sqlite_sequence表数据

sqlite_sequence表中包含了两个字段，其中，name字段为表名称，seq字段为自增长字段最大数据。

此外，在添加记录时，可以使用last_insert_rowid()函数获取新记录的ID值，如下面的代码。

insert into t1a(f1,f2)values('user04',1);
select last_insert_rowid();

代码中，在使用insert into语句添加记录后，使用select语句显示了新记录的ID值。

重置表与自增长字段

SQLite3数据库中，没有像SQL Server、MySQL等数据库中的truncate语句，无法直接重置表；在SQLite3数据库中，如果需要清空表并重置自增长字段数据，需要两步操作，一是删除表中的所有数据，二是删除sqlite_sequence表中对应的自增长字段数据。如下面的代码，将删除t1a表全部数据，并将recid字段数据重置。

delete from t1a;
delete from sqlite_sequence where name='t1a';

执行代码后，在t1a表中添加记录时，recid字段会重新从1开始计数。

请注意，如果表中只使用默认的rowid字段处理记录ID，会自动根据记录数据进行处理。

删除表

不再需要某个数据表时，可以使用drop table语句删除，应用格式如下：

drop table <表名>;

请注意，删除数据表后，如果表中有自定义的自增长字段，还应该将sqlite_sequence表中对应的记录删除。

修改表名

修改表的名称时使用alter table语句和rename子句，应用格式如下：

alter table <表名> rename to <新表名>;

如下面的代码可以将t1a修改为t1b。

alter table t1a rename to t1b;

操作后请改回t1a，下面的示例会继续使用t1a表名。

添加字段

添加字段时同样使用alter table语句，此时需要使用add column子句，应用格式如下：

alter table <表名> add column <字段定义>

在表中添加字段时，一次只能添加一个字段定义，如下面的代码。

alter table t1a add column f5 integer;

执行代码会在t1a表中添加f5字段，其类型为integer。

sqlite_master表

sqlite_master表是SQLite3数据库的系统表，其中保存了数据库对象的信息，其字段包括：

• type，text类型，保存对象的类型，其值包括index、table、view、trigger，分别表示索引、表、视图和触发器对象。
• name，text类型，保存对象名称。
• tbl_name，text类型，保存对象关联的表的名称，如索引所在表；如果类型为表，则此字段与name字段数据相同
• rootpage，integer类型，数据库对象的存储位置。
• sql，text类型，对象创建的SQL语句。

可以使用如下代码查询当前数据库中sqlite_master表的内容。

select * from sqlite_master;

截止目前，示例数据库中显示的sqlite_master表数据如下图所示。

sqlite_master表