SQLite：DDL（数据定义语言）的基本用法

1 主要内容说明

SQLite 的 DDL 语句主要用于数据库对象的定义和修改，包括创建和删除表、索引、视图等。常用的 DDL 操作包括：

• 创建表 (CREATE TABLE)：用于定义表结构和约束。
• 修改表 (ALTER TABLE)：用于修改表结构，添加列。
• 删除表 (DROP TABLE)：删除表及其数据。
• 创建索引 (CREATE INDEX)：提高查询效率。
• 删除索引 (DROP INDEX)：删除索引。
• 创建视图 (CREATE VIEW)：定义一个虚拟表，简化查询。
• 删除视图 (DROP VIEW)：删除视图定义。

本文使用的工具为dbeaver编辑器，dbeaver编辑器下载地址。开发者同样也可以另外使用终端等其他方式来编写数据库相关内容的代码。

数据库代码通常情况下不区分大小写的，本文为了方便观察都将代码以小写的方式编写。

2 相关内容说明

2.1 创建表格（create table）

• 在 SQLite 中，creat table 语句用于定义新表的结构。

2.1.1 SQLite常见的数据类型

2.1.1.1 integer（整型）

• 用于存储整数值。
• 大小范围：支持从 -2⁶³ 到 2^63-1 的范围（8 字节）。
• 常见用途：用于表示 ID、计数、年龄等没有小数的数值。
• 示例如下

	stock integer default 0 -- 表示库存数量，默认为 0
	age integer check(age >= 0) -- 表示年龄，必须大于等于 0

2.1.1.2 text（文本型）

• 用于存储字符串或文本。
• 长度没有限制。
• 常见用途：用于存储名称、描述、电子邮件地址等字符数据。
• 示例如下：

	product_name text not null -- 表示商品名称，不能为空
	email text unique -- 表示电子邮箱，必须唯一

2.1.1.3 real（浮点型）

• 用于存储浮点数（带小数点的数值）。
• 以 8 字节的 IEEE 754 双精度存储。
• 常见用途：表示价格、重量、长度等带有小数的数值。
• 示例如下：

	price real check(price >= 0) -- 表示商品价格，必须大于等于 0

2.1.1.4 datetime（日期时间）

• SQLite 没有专门的日期时间类型，但可以使用 text、real 或 integer 存储日期时间。
• 如果为 text，通常存储为 ISO8601 格式（YYYY-MM-DD HH:MM:SS）。
• 如果为 real，表示自公元 0 年以来的天数。
• 如果为 integer，表示自 Unix 纪元（1970-01-01）以来的秒数。
• 常见用途：记录时间戳。
• 示例如下：

	created_at datetime default current_timestamp -- 表示记录的创建时间，默认为当前时间

2.1.2 源码1 （创建表格）

-- 创建一个名为 products 的表，用于存储商品信息
create table products (
    product_id integer primary key autoincrement,  -- 商品 ID，主键，自动递增
    product_name text not null,                    -- 商品名称，必填字段
    price real check(price >= 0),                  -- 商品价格，必须大于等于 0
    stock integer default 0,                       -- 库存数量，默认为 0
    created_at datetime default current_timestamp  -- 创建时间，默认为当前时间
);

2.1.3 源码1运行效果

• 表格创建成功后得到一个如下图的表格
  

2.2 修改表（alter table）

• 在 SQLite 中，alter table 语句用于修改现有表的结构。

2.2.1 添加列（add column）

• SQLite 支持在表中添加新列，但不能删除或直接修改现有列。

2.2.1.1 语法

• 添加列的语法如下：

	alter table table_name add column_name datatype [constraint];

2.2.1.2 添加列，源码2

• 假设已有一个表users，结构如下：

create table users (
    id integer primary key autoincrement,
    name text not null,
    age integer
);

• 效果如下
  

• 现在要为users表添加一个email列：

	-- 向表 users 添加新列 email，用于存储用户邮箱
	-- 新列的默认值为 NULL，因为 SQLite 在添加列时不允许直接设置非空或唯一约束
	alter table users add column email text;	

• 执行后，users表可以得到如下表结构：
  

2.2.2 重命名表（rename to）

• 表名从 users 改为 customers，但表内数据和结构保持不变。

2.2.2.1 语法

• 重命名表格的语法如下：

	alter table old_table_name rename to new_table_name;

2.2.2.2 重命名表，源码3

• 假设已有一个表users，结构如下：

create table users (
    id integer primary key autoincrement,
    name text not null,
    age integer
);

• 将 users 表重命名为 customers，如下

	-- 将表 users 重命名为 customers(顾客)
	alter table users rename to customers;

• 表名称变化效果如下：
  

2.2.3 重命名列（rename column）

重命名列（SQLite 3.25.0+）

2.2.3.1 语法

• 重命名列的语法如下

	alter table table_name rename column old_column_name to new_column_name;

2.2.3.2 重命名列，源码4

• 假设已有一个表users，结构如下：

create table users (
    id integer primary key autoincrement,
    name text not null,
    age integer
);

• 效果如下
  
• 将 users 表中的 name 列重命名为 full_name，如下

	-- 将列 name 重命名为 full_name
	alter table users rename column name to full_name;

• 重命名列后效果如下
  

2.2.4 重建表实现–删除列

SQLite 不支持直接删除列，可以通过以下步骤间接实现。

2.2.4.1 间接删除列步骤

目标：从 users 表格中 删除 age 列

2.2.4.1.1 创建旧表（包含age列），源码5如下：

-- 创建旧表 old_users，包含 age 列
create table old_users (
    id integer primary key autoincrement, -- 用户 ID，主键，自动递增
    age integer check(age >=0),			  -- 用户年龄，约束检查大于等于0	
    name text not null,                   -- 用户名，必填字段
    email text,                           -- 用户邮箱
    created_at datetime default current_timestamp, -- 用户创建时间
    is_active integer default 1          -- 用户是否激活
);

2.2.4.1.2 源码5具体效果图

• 新建一个旧表，包含age列
  

2.2.4.1.3 创建新表（没有age列），源码6如下：

-- 创建新表 new_users，其结构与 users 表类似，但不包含 age 列
create table new_users (
    id integer primary key autoincrement, -- 用户 ID，主键，自动递增
    name text not null,                   -- 用户名，必填字段
    email text,                           -- 用户邮箱
    created_at datetime default current_timestamp, -- 用户创建时间
    is_active integer default 1          -- 用户是否激活
);

2.2.4.1.4 源码6具体效果图

• 新建一个新表，不包含age列
  

2.2.4.1.5 将旧表的数据复制到新表（insert into）

• 将旧表的数据复制到新表，源码如下：

-- 复制数据到新表，忽略 age 列
	insert into new_users (id, name, email, created_at, is_active)
	select id, name, email, created_at, is_active from old_users;

2.2.4.1.6 删除旧表

• 删除旧表，源码如下：

	-- 删除旧表 old_users
	drop table old_users;

2.2.4.1.7 将新表重命名为旧表名

• 将新表重命名为旧表名，源码如下：

	-- 将新表 new_users 重命名为 old_users
	alter table new_users rename to old_users;

2.3 删除表（drop table）

DROP TABLE 是用来删除 SQLite 数据库中指定表的语句。使用该语句时，表的数据、结构以及与该表相关的所有约束（如索引、触发器）都会被永久删除，无法恢复。因此，在执行 DROP TABLE 之前，确保已备份重要数据。

2.3.1 删除表基本语法

删除表基本语法如下：

	drop table [if exists] table_name;

• table_name：要删除的表的名称。
• if exists：可选项，表示在删除表之前检查该表是否存在。如果表不存在，SQLite 不会抛出错误，而是忽略该语句。如果表存在，它会被删除。

2.3.2 删除表相关内容

2.3.2.1 删除表

	-- 删除表 users
	drop table users;

• 说明：这将完全删除 users 表，包括表中的所有数据和结构。

2.3.2.2 删除表（如果存在的话）

	-- 删除表 users，如果表不存在则不抛出错误
	drop table if exists users;

说明：如果 users 表存在，它会被删除；如果不存在，SQLite 会忽略此命令，且不会抛出错误。

2.3.3 删除表时的注意事项

2.3.3.1 不可恢复

使用 drop table 删除表后，表中的所有数据都会被永久删除，无法恢复。执行之前请确认已做好备份。

2.3.3.2 删除表与依赖关系

如果表被其他表的外键约束引用，则在删除表时可能会引发错误。可以考虑先删除或更新外键约束。

2.3.3.3 删除索引和触发器

删除表时，所有与该表相关的索引和触发器也会被自动删除。

2.3.3.4 删除多个表

SQLite 不支持在单一 drop table 语句中删除多个表。如果需要删除多个表，必须分别执行多个 drop table 语句。例如：

	-- orders 订单
	drop table if exists users;
	drop table if exists orders;

2.3.4 源码7（删除表）

说明：

• 先创建表 users，插入了两条记录。
• 然后使用 DROP TABLE 删除 users 表及其所有数据。

-- 创建 users 表
create table users (
    id integer primary key autoincrement,
    name text not null,
    email text unique
);

-- 插入数据
insert into users (name, email) values ('小蜗牛764号', 'xiaowoniu764@example.com');
insert into users (name, email) values ('小蜗牛765号', 'xiaowoniu765@example.com');

-- 删除表 users
drop table if exists users;

2.4 创建索引（create index）

2.4.1 索引对照说明

举例，假设users表有 100,000 条记录

2.4.1.1 全表扫描

	select * from users where age > 30;

• 如果age字段没有索引，SQLite 会扫描 100,000 条记录。
• 范围：整张表。
• 性能：随着数据量增长，性能下降明显。

2.4.1.2 索引查找

	create index idx_users_age on users(age);
	select * from users where age > 30;

• 如果age字段有索引，SQLite 会通过索引快速定位age > 30的记录。
• 范围：仅扫描索引中满足条件的部分记录。
• 性能：即使表中有百万条记录，索引查找依然高效。

2.4.2 创建索引语法

	create [unique] index index_name
	on table_name (column1, column2, ...);

• unique：可选项，表示索引将强制列值唯一，即该列的每个值只能出现一次。如果尝试插入重复的值，则会报错。
• index_name：要创建的索引的名称。
• table_name：索引所属的表。
• column1, column2, …：要为其创建索引的列。

2.4.2.1 创建普通索引

• 这个索引将加速 users 表中基于 name 列的查询。

	-- 创建一个名为 idx_users_name 的索引，索引将基于 users 表的 name 列
	create index idx_users_name on users(name);

2.4.2.2 创建唯一索引

• 这个索引不仅加速基于 email 列的查询，还强制 email 列中的值唯一。如果尝试插入重复的 email 值，将会报错。

	-- 创建一个名为 idx_users_email 的唯一索引，索引基于 users 表的 email 列
	create unique index idx_users_email on users(email);

2.4.2.3 创建复合索引

• 这个索引可以加速基于 name 和 age 列的查询。

复制代码
	-- 创建一个基于 users 表的 name 和 age 列的复合索引
	create index idx_users_name_age on users(name, age);

2.4.3 创建索引举例

2.4.3.1 创建示例表并插入数据，源码9

-- 创建一个 users 表
create table users (
    id integer primary key autoincrement, 		-- 用户 ID，主键，自动递增
    name varchar(20) not null,                  -- 用户名，必填字段，最多20个字符
    email varchar(30) unique,                   -- 用户邮箱，必须唯一，最多30个字符
    age integer                                 -- 用户年龄，可为空
);

-- 插入示例数据
insert into users (name, email, age) values 
('小蜗牛763号', 'xiaowoniu763@example.com', 25),   -- 插入第1条用户数据
('小蜗牛764号', 'xiaowoniu764@example.com', 5),    -- 插入第2条用户数据
('小蜗牛765号', 'xiaowoniu765@example.com', 23),   -- 插入第3条用户数据
('小蜗牛766号', 'xiaowoniu766@example.com', 21),   -- 插入第4条用户数据
('小蜗牛767号', 'xiaowoniu767@example.com', 22);   -- 插入第5条用户数据

2.4.3.2 源码9表格样式效果

• 效果如下
  

2.4.3.3 创建索引

• 为 name 列创建一个索引以加速查询：

	-- 为 users 表的 name 列创建索引
	create index idx_users_name on users(name);

2.4.3.4 查询性能的对比

2.4.3.4.1 不使用索引的查询

• 如果没有索引，查询 name 为 ‘小蜗牛764号’ 的用户时，SQLite 需要扫描整个表（即全表扫描）。

	-- 查询 name 为 'xiaowoniu764号' 的用户
	select * from users where name = '小蜗牛764号';

• 执行此查询时，SQLite 会依次检查每一行的 name 列，直到找到匹配的行。

2.4.3.4.2 使用索引的查询

• 有了索引后，SQLite 可以直接使用索引快速定位目标行，而不需要扫描整个表。

	-- 同样查询 name 为 '小蜗牛764号' 的用户
	select * from users where name = '小蜗牛764号';

• 执行此查询时，SQLite 会利用索引 idx_users_name，只需从索引中查找匹配的行即可，大大提高查询速度。

2.4.3.4.3 查看是否使用了索引

• 可以通过explain query plan 查看查询是否使用了索引
• 查询计划（假设未创建索引）

	explain query plan select * from users where name = '小蜗牛764号';

• 使用索引则输出，SEARCH TABLE users USING INDEX idx_users_name (name=?)，如下：
  
  表示 SQLite 使用了索引 idx_users_name 来加速查询。

• 未使用索引时，效果如下
  
  表示 SQLite 对 users 表进行了全表扫描。

2.5 删除索引（drop index）

• 如果索引不再需要，可以将其删除：

	-- 删除 idx_users_name 索引
	drop index idx_users_name;

2.6 创建视图（create view）

• 视图 是基于一个或多个表的查询结果定义的虚拟表。它不存储实际数据，只保存 SQL 查询逻辑。
• 每次访问视图时，数据库会动态执行视图定义中的查询语句，生成结果集。

2.6.1 创建视图的语法

	create [temp | temporary] view view_name as
	select statement;

• TEMP 或 TEMPORARY：可选项，创建临时视图，视图只在当前数据库连接中有效。
• view_name：视图的名称。
• SELECT statement：定义视图的数据查询语句。

2.6.2 创建视图举例

2.6.2.1 创建表格，用以视图显示，使用源码9

-- 创建一个 users 表
create table users (
    id integer primary key autoincrement, 		-- 用户 ID，主键，自动递增
    name varchar(20) not null,                  -- 用户名，必填字段，最多20个字符
    email varchar(30) unique,                   -- 用户邮箱，必须唯一，最多30个字符
    age integer                                 -- 用户年龄，可为空
);

-- 插入示例数据
insert into users (name, email, age) values 
('小蜗牛763号', 'xiaowoniu763@example.com', 25),   -- 插入第1条用户数据
('小蜗牛764号', 'xiaowoniu764@example.com', 5),    -- 插入第2条用户数据
('小蜗牛765号', 'xiaowoniu765@example.com', 23),   -- 插入第3条用户数据
('小蜗牛766号', 'xiaowoniu766@example.com', 21),   -- 插入第4条用户数据
('小蜗牛767号', 'xiaowoniu767@example.com', 22);   -- 插入第5条用户数据

2.6.2.2 源码9效果图

2.6.2.3 创建视图，显示表users中年龄>22的内容

-- 创建视图 view_adult_users
-- 该视图筛选出 users 表中年龄大于 22 的用户
create view view_adult_users as
select *          -- 查询 users 表的所有列
from users         -- 数据来源于 users 表
where age > 22;    -- 只包含年龄大于 22 的用户

2.6.2.4 查询视图

• 查询视图

	select * from view_user_age_group;

2.6.2.5 查询视图效果

2.6.2.6 再添加一条信息

	insert into users (name, email, age) values ('小蜗牛768号', 'xiaowoniu768@example.com', 28)

2.6.2.7 再次查询视图

	select * from view_user_age_group;

• 视图可以根据表格的更新，而更新添加内容，通过保存的逻辑显示方式展示表格内容。
  

2.7 删除视图（drop view）

2.7.1 语法

	drop view [if exists] view_name;

2.7.2 举例

	-- 删除视图 view_adult_users
	drop view if exists view_adult_users;

3.结语

DDL关于表格的内容，DML关于数据的内容，DQL关于查询的内容。如果是鸿蒙开发者，重心主要放在数据的查询和使用上。

本文关于DDL的内容只是简单基本的用法，用以基础入门，以及为了后续的重新拾起少花时间而编写的。因为放于网络上查找的观看效率，比在电脑本地对内容的观看效率要高。

由于笔者的能力有限，创作的内容有所不足在所难免，也敬请读者包涵和指出，万分感谢！

4.定位日期

完成创建表、修改表、删除表的内容；
2024-12-3；
1:03；

2024-12-4；
00:03；