第五章 SQLite数据库：5、SQLite 进阶用法：ALTER 命令、TRUNCATE 操作、视图创建、事务控制和子查询的操作

1. SQLite ALTER 命令

SQLite 的 ALTER TABLE 命令允许在不完全重建表的情况下修改现有的表结构。通过 ALTER TABLE，您可以执行如重命名表名、添加新列等操作，但无法执行复杂的修改，如删除列或修改列的数据类型。

语法

1. 重命名表
   用于重命名现有表的基本语法如下：

   ALTER TABLE table_name RENAME TO new_table_name;
   

2. 添加新列
   用于在现有表中添加新列的基本语法如下：

   ALTER TABLE table_name ADD COLUMN column_definition;
   

实例

示例 1：重命名表

假设我们有一个名为 EMPLOYEES 的表，其中存储了员工的信息。若要将表名更改为 WORKERS，可以执行以下操作：

ALTER TABLE EMPLOYEES RENAME TO WORKERS;

执行后，表名将被更改为 WORKERS。

示例 2：向表中添加列

假设我们有以下 WORKERS 表：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Alice       29          New York    50000.0
2           Bob         34          California  60000.0
3           Charlie     27          Texas       55000.0

若要在此表中添加一个名为 PHONE_NUMBER 的列：

ALTER TABLE WORKERS ADD COLUMN PHONE_NUMBER TEXT;

此操作将为 WORKERS 表添加一个新的 PHONE_NUMBER 列。

查询结果：

ID          NAME        AGE         ADDRESS     SALARY      PHONE_NUMBER
----------  ----------  ----------  ----------  ----------  ------------
1           Alice       29          New York    50000.0     NULL
2           Bob         34          California  60000.0     NULL
3           Charlie     27          Texas       55000.0     NULL

新列 PHONE_NUMBER 会被填充为 NULL 值，直到用户提供相关数据。

---

2. SQLite TRUNCATE TABLE

SQLite 中没有 TRUNCATE TABLE 命令，但可以使用 DELETE 命令来删除表中的所有数据。

语法

删除表中所有记录的基本语法如下：

DELETE FROM table_name;

如果要重置自增列（如果存在）并清空表内容，可以执行以下操作：

DELETE FROM sqlite_sequence WHERE name = 'table_name';

备注：sqlite_sequence 是一个自动创建的表，它存储了所有带有 AUTOINCREMENT 属性的表的当前自增值。

示例

假设我们有一个 CUSTOMERS 表，包含以下数据：

ID          NAME        AGE         ADDRESS     PHONE
----------  ----------  ----------  ----------  --------
1           John        28          London      123456789
2           Mary        35          Paris       987654321
3           Steve       40          New York    555555555

要删除 CUSTOMERS 表中的所有数据并重置自增列，可以执行以下命令：

DELETE FROM CUSTOMERS;
DELETE FROM sqlite_sequence WHERE name = 'CUSTOMERS';

执行后，表将被清空，并且自增列的值会被重置为 1。

---

3. SQLite 视图 (View)

SQLite 视图是通过查询一个或多个表来创建的虚拟表。视图只是存储的查询结果，而不是实际的数据。它提供了查询数据的便利，并且可以作为数据的虚拟表示。

创建视图

使用 CREATE VIEW 语句创建视图。视图可以从一个或多个表创建，并且通常用于封装复杂的查询逻辑。

基本语法如下：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

示例 1：创建视图

假设我们有以下 EMPLOYEES 表：

ID          NAME        AGE         POSITION     SALARY
----------  ----------  ----------  ----------  ----------
1           Alice       29          Developer    70000
2           Bob         34          Manager      80000
3           Charlie     27          Developer    60000

如果要创建一个视图 DEV_EMPLOYEES，只包含职位为 Developer 的员工，可以执行以下 SQL：

CREATE VIEW DEV_EMPLOYEES AS
SELECT ID, NAME, AGE, SALARY
FROM EMPLOYEES
WHERE POSITION = 'Developer';

查询视图：

SELECT * FROM DEV_EMPLOYEES;

结果：

ID          NAME        AGE         SALARY
----------  ----------  ----------  ----------
1           Alice       29          70000
3           Charlie     27          60000

删除视图

若要删除视图，可以使用 DROP VIEW 语句：

DROP VIEW DEV_EMPLOYEES;

---

4. SQLite 事务 (Transaction)

事务是数据库中执行的一系列操作，这些操作要么全部成功，要么全部失败。事务确保了数据库操作的完整性和一致性。

事务有四个核心属性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），简称 ACID。

语法

1. 开始事务：

   BEGIN TRANSACTION;
   

2. 提交事务：

   COMMIT;
   

3. 回滚事务：

   ROLLBACK;
   

示例 1：事务的使用

假设我们有一个 ORDERS 表，存储了客户的订单信息。我们要执行一个事务来添加新的订单记录，并在过程中处理错误。

BEGIN TRANSACTION;-- 插入新订单
INSERT INTO ORDERS (customer_id, order_date, total_amount)
VALUES (1, '2025-04-20', 250.00);-- 提交事务
COMMIT;

如果发生错误，事务将回滚，所有的插入操作都将撤销：

ROLLBACK;

---

5. SQLite 子查询

子查询是嵌套在另一个查询中的查询，可以用于 SELECT、INSERT、UPDATE 和 DELETE 语句中。

语法

1. SELECT 语句中的子查询：

   SELECT column_name
   FROM table_name
   WHERE column_name OPERATOR
   (SELECT column_name FROM table_name WHERE condition);
   

2. INSERT 语句中的子查询：

   INSERT INTO table_name (column1, column2)
   SELECT column1, column2 FROM table_name WHERE condition;
   

示例 1：子查询与 SELECT 语句

假设我们有一个 ORDERS 表，记录了客户的订单信息，我们要查询那些订单金额大于 500 的客户。

SELECT * FROM ORDERS
WHERE customer_id IN (SELECT customer_id FROM ORDERS WHERE total_amount > 500);

---

SQLite 综合案例：使用 ALTER、TRUNCATE 和 视图

假设我们有一个电商平台的数据库，下面是针对 PRODUCTS 表的管理操作，并结合 ALTER 命令、事务控制和视图的创建。

1. 创建 PRODUCTS 表

CREATE TABLE PRODUCTS (ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT NOT NULL,PRICE REAL NOT NULL,STOCK INTEGER NOT NULL
);

2. 插入商品记录

INSERT INTO PRODUCTS (NAME, PRICE, STOCK)
VALUES ('Laptop', 1200.00, 50),('Smartphone', 800.00, 100),('Headphones', 150.00, 200);

查询 PRODUCTS 表：

SELECT * FROM PRODUCTS;

查询结果：

ID   | NAME        | PRICE  | STOCK
---- | ----------- | ------ | ------
1    | Laptop      | 1200.00| 50
2    | Smartphone  | 800.00 | 100
3    | Headphones  | 150.00 | 200

3. 使用 ALTER 命令添加新列

假设我们要给 PRODUCTS 表添加一个 CATEGORY 列，用于存储商品类别：

ALTER TABLE PRODUCTS ADD COLUMN CATEGORY TEXT;

查询结果：

ID   | NAME        | PRICE  | STOCK | CATEGORY
---- | ----------- | ------ | ----- | --------
1    | Laptop      | 1200.00| 50    | NULL
2    | Smartphone  | 800.00 | 100   | NULL
3    | Headphones  | 150.00 | 200   | NULL

4. 删除所有库存记录并重置自增列

假设我们需要清空 PRODUCTS 表中的所有记录，并重置自增列的计数器：

DELETE FROM PRODUCTS;
DELETE FROM sqlite_sequence WHERE name = 'PRODUCTS';

5. 创建视图显示价格高于 500 的商品

假设我们需要创建一个视图，显示所有价格大于 500 的商品：

CREATE VIEW EXPENSIVE_PRODUCTS AS
SELECT NAME, PRICE
FROM PRODUCTS
WHERE PRICE > 500;

查询视图：

SELECT * FROM EXPENSIVE_PRODUCTS;

查询结果：

NAME        | PRICE
----------- | ------
Laptop      | 1200.00
Smartphone  | 800.00

---