SQLite 进阶技巧：ALTER TABLE 和事务控制（八）
- 数据库
- 7天前
- 5热度
- 0评论
在 SQLite 数据库管理中，掌握 ALTER TABLE 命令、视图管理和事务控制是提升数据库操作效率的关键技能。本文将详细介绍这些功能的使用方法、应用场景以及最佳实践，帮助你在实际项目中更好地利用 SQLite。
什么是 ALTER TABLE 命令？
ALTER TABLE 命令用于修改已存在的表结构。在 SQLite 中，ALTER TABLE 主要支持两种操作：重命名表和在现有表中添加新的列。与其他数据库管理系统不同，SQLite 不支持通过 ALTER TABLE 执行更多的复杂操作，如删除列或修改列的数据类型。
重命名表
重命名表的基本语法如下：
ALTER TABLE database_name.table_name RENAME TO new_table_name;假设我们有一个名为 COMPANY 的表，我们可以将其重命名为 OLD_COMPANY：
ALTER TABLE COMPANY RENAME TO OLD_COMPANY;这条命令将 COMPANY 表重命名为 OLD_COMPANY。
添加新列
在现有表中添加新列的基本语法如下：
ALTER TABLE database_name.table_name ADD COLUMN column_def...;继续使用 OLD_COMPANY 表的例子，我们可以在表中添加一个新的列 SEX：
ALTER TABLE OLD_COMPANY ADD COLUMN SEX CHAR(1);执行上述命令后，OLD_COMPANY 表将新增一列 SEX，并且这一列的初始值为 NULL。
示例
假设 COMPANY 表有如下记录：
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0首先，我们将 COMPANY 表重命名为 OLD_COMPANY：
ALTER TABLE COMPANY RENAME TO OLD_COMPANY;然后，在 OLD_COMPANY 表中添加 SEX 列：
ALTER TABLE OLD_COMPANY ADD COLUMN SEX CHAR(1);此时，OLD_COMPANY 表的结构变为：
ID NAME AGE ADDRESS SALARY SEX
---------- ---------- ---------- ---------- ---------- ---
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0清空表数据
SQLite 并没有 TRUNCATE TABLE 命令，但可以通过 DELETE 命令清空表中的所有数据。然而，这种方法不会重置自增列的计数器。为了重置自增列，可以使用以下方法：
DELETE FROM sqlite_sequence WHERE name = 'table_name';示例
假设 COMPANY 表有如下记录：
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0首先，删除表中的所有记录：
DELETE FROM COMPANY;然后，重置自增列的计数器：
DELETE FROM sqlite_sequence WHERE name = 'COMPANY';最后，执行 VACUUM 命令以释放未使用的空间：
VACUUM;视图管理
视图（View）是存储在数据库中的预定义查询结果。视图可以简化复杂的查询，限制数据访问，以及汇总多个表中的数据。SQLite 视图是只读的，不能直接对其进行插入、更新或删除操作。但可以通过触发器来实现这些操作。
创建视图
创建视图的基本语法如下：
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];假设 COMPANY 表有如下记录：
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0我们可以创建一个只包含 ID、NAME 和 AGE 列的视图 COMPANY_VIEW：
CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM COMPANY;查询 COMPANY_VIEW 的结果如下：
ID NAME AGE
---------- ---------- ----------
1 Paul 32
2 Allen 25
3 Teddy 23
4 Mark 25
5 David 27
6 Kim 22
7 James 24删除视图
删除视图的基本语法如下：
DROP VIEW view_name;删除 COMPANY_VIEW 视图：
DROP VIEW COMPANY_VIEW;事务控制
事务（Transaction）是一组对数据库的更改，这些更改要么全部成功，要么全部失败。事务具有四个标准属性，通常称为 ACID 属性：
- 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败。
- 一致性（Consistency）：事务执行前后，数据库保持一致状态。
- 隔离性（Isolation）：事务之间互不影响。
- 持久性（Durability）：事务一旦提交，其结果将永久保存。
事务控制命令
- BEGIN TRANSACTION：开始事务。
- COMMIT：提交事务，保存所有更改。
- ROLLBACK：回滚事务，撤销所有未提交的更改。
示例
假设 COMPANY 表有如下记录：
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0开始一个事务，并删除年龄为 25 的记录，最后回滚事务：
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;检查 COMPANY 表，记录没有变化：
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0再次开始一个事务，删除年龄为 25 的记录，最后提交事务：
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;检查 COMPANY 表，记录已被删除：
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0总结
本文详细介绍了 SQLite 中的 ALTER TABLE 命令、视图管理和事务控制。通过这些功能，你可以更灵活地管理表结构、简化复杂查询，并确保数据的一致性和完整性。希望本文的内容对你在实际项目中使用 SQLite 提供了有价值的参考。