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

1. SQLite ALTER 下令

SQLite 的 ALTER TABLE 下令允许在不完全重修表的环境下修改现有的表布局。通过 ALTER TABLE，您可以实验如重定名表名、添加新列等操纵，但无法实验复杂的修改，如删除列或修改列的数据类型。
语法

• 重定名表
  用于重定名现有表的根本语法如下：
  1. ALTER TABLE table_name RENAME TO new_table_name;

  复制代码
• 添加新列
  用于在现有表中添加新列的根本语法如下：
  1. ALTER TABLE table_name ADD COLUMN column_definition;

  复制代码

实例

示例 1：重定名表

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

1. ALTER TABLE EMPLOYEES RENAME TO WORKERS;

复制代码

实验后，表名将被更改为 WORKERS。
示例 2：向表中添加列

假设我们有以下 WORKERS 表：

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

复制代码

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

1. ALTER TABLE WORKERS ADD COLUMN PHONE_NUMBER TEXT;

复制代码

此操纵将为 WORKERS 表添加一个新的 PHONE_NUMBER 列。
查询效果：

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

复制代码

新列 PHONE_NUMBER 会被添补为 NULL 值，直到用户提供干系数据。

---

2. SQLite TRUNCATE TABLE

SQLite 中没有 TRUNCATE TABLE 下令，但可以使用 DELETE 下令来删除表中的全部数据。
语法

删除表中全部记录的根本语法如下：

1. DELETE FROM table_name;

复制代码

假如要重置自增列（假如存在）并清空表内容，可以实验以下操纵：

1. DELETE FROM sqlite_sequence WHERE name = 'table_name';

复制代码

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

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

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

复制代码

要删除 CUSTOMERS 表中的全部数据并重置自增列，可以实验以下下令：

1. DELETE FROM CUSTOMERS;
2. DELETE FROM sqlite_sequence WHERE name = 'CUSTOMERS';

复制代码

实验后，表将被清空，而且自增列的值会被重置为 1。

---

3. SQLite 视图 (View)

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

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

1. CREATE VIEW view_name AS
2. SELECT column1, column2, ...
3. FROM table_name
4. WHERE condition;

复制代码

示例 1：创建视图

假设我们有以下 EMPLOYEES 表：

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

复制代码

假如要创建一个视图 DEV_EMPLOYEES，只包罗职位为 Developer 的员工，可以实验以下 SQL：

1. CREATE VIEW DEV_EMPLOYEES AS
2. SELECT ID, NAME, AGE, SALARY
3. FROM EMPLOYEES
4. WHERE POSITION = 'Developer';

复制代码

查询视图：

1. SELECT * FROM DEV_EMPLOYEES;

复制代码

效果：

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

复制代码

删除视图

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

1. DROP VIEW DEV_EMPLOYEES;

复制代码

---

4. SQLite 事件 (Transaction)

事件是数据库中实验的一系列操纵，这些操纵要么全部乐成，要么全部失败。事件确保了数据库操纵的完备性和划一性。
事件有四个核心属性：原子性（Atomicity）、划一性（Consistency）、隔离性（Isolation）和长期性（Durability），简称 ACID。
语法

• 开始事件：
  1. BEGIN TRANSACTION;

  复制代码
• 提交事件：
  1. COMMIT;

  复制代码
• 回滚事件：
  1. ROLLBACK;

  复制代码

示例 1：事件的使用

假设我们有一个 ORDERS 表，存储了客户的订单信息。我们要实验一个事件来添加新的订单记录，并在过程中处置惩罚错误。

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

复制代码

假如发生错误，事件将回滚，全部的插入操纵都将取消：

1. ROLLBACK;

复制代码

---

5. SQLite 子查询

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

• SELECT 语句中的子查询：
  1. SELECT column_name
  2. FROM table_name
  3. WHERE column_name OPERATOR
  4. (SELECT column_name FROM table_name WHERE condition);

  复制代码
• INSERT 语句中的子查询：
  1. INSERT INTO table_name (column1, column2)
  2. SELECT column1, column2 FROM table_name WHERE condition;

  复制代码

示例 1：子查询与 SELECT 语句

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

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

复制代码

---

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

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

1. CREATE TABLE PRODUCTS (
2.     ID INTEGER PRIMARY KEY AUTOINCREMENT,
3.     NAME TEXT NOT NULL,
4.     PRICE REAL NOT NULL,
5.     STOCK INTEGER NOT NULL
6. );

复制代码

2. 插入商品记录

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

复制代码

查询 PRODUCTS 表：

1. SELECT * FROM PRODUCTS;

复制代码

查询效果：

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

复制代码

3. 使用 ALTER 下令添加新列

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

1. ALTER TABLE PRODUCTS ADD COLUMN CATEGORY TEXT;

复制代码

查询效果：

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

复制代码

4. 删除全部库存记录并重置自增列

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

1. DELETE FROM PRODUCTS;
2. DELETE FROM sqlite_sequence WHERE name = 'PRODUCTS';

复制代码

5. 创建视图表现代价高于 500 的商品

假设我们必要创建一个视图，表现全部代价大于 500 的商品：

1. CREATE VIEW EXPENSIVE_PRODUCTS AS
2. SELECT NAME, PRICE
3. FROM PRODUCTS
4. WHERE PRICE > 500;

复制代码

查询视图：

1. SELECT * FROM EXPENSIVE_PRODUCTS;

复制代码

查询效果：

1. NAME        | PRICE
2. ----------- | ------
3. Laptop      | 1200.00
4. Smartphone  | 800.00

复制代码

---