sqlite3学习之视图&事务&子查询

我不是女神ヾ 2022-03-27 01:41 618阅读 0赞

视图（View）只不过是通过相关的名称存储在数据库中的一个 SQLite 语句。视图（View）实际上是一个以预定义的 SQLite 查询形式存在的表的组合，它可以包含一个表的所有行或从一个或多个表选定行。视图（View）可以从一个或多个表创建，这取决于要创建视图的 SQLite 查询，它也是一种虚表，允许用户实现以下几点：

*  用户或用户组查找结构数据的方式更自然或直观。
 *  限制数据访问，用户只能看到有限的数据，而不是完整的表。
 *  汇总各种表中的数据，用于生成报告。

SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

SQLite 的视图是使用 **CREATE VIEW** 语句创建的。SQLite 视图可以从一个单一的表、多个表或其他视图创建。CREATE VIEW 的基本语法如下：

CREATE [TEMP | TEMPORARY] VIEW view_name AS
    SELECT column1, column2.....
    FROM table_name
    WHERE [condition];

我们可以在 SELECT 语句中包含多个表，这与在正常的 SQL SELECT 查询中的方式非常相似。如果我们使用了可选的 TEMP 或 TEMPORARY 关键字，则将在临时数据库中创建视图。

我们先来假设 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 表创建视图的实例，这个视图只从 COMPANY 表中选取几列，如下：

CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM  COMPANY;

然后，我们就可以查询 COMPANY\_VIEW，与查询实际表的方式类似，如下：

SELECT * FROM COMPANY_VIEW;

要删除视图，只需使用带有 **view\_name** 的 DROP VIEW 语句，语法格式如下：

DROP VIEW view_name;

事务（Transaction）是一个对数据库执行工作单元，它是以逻辑顺序完成的工作单位或序列，可以是由用户手动操作完成，也可以是由某种数据库程序自动完成，它是指一个或多个更改数据库的扩展。例如，如果我们正在创建一个记录或者更新一个记录或者从表中删除一个记录，那么我们正在该表上执行事务，重要的是要控制事务以确保数据的完整性和处理数据库错误，实际上，我们可以把许多的 SQLite 查询联合成一组，把所有这些放在一起作为事务的一部分进行执行。

事务（Transaction）具有以下四个标准属性，通常根据首字母缩写为 ACID：

*  **原子性（Atomicity）：**确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到以前的状态。
 *  **一致性（Consistency)：**确保数据库在成功提交的事务上正确地改变状态。
 *  **隔离性（Isolation）：**使事务操作相互独立和透明。
 *  **持久性（Durability）：**确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。

我们可以使用下面的命令来控制事务：

*  **BEGIN TRANSACTION**：开始事务处理。
 *  **COMMIT**：保存更改，或者可以使用 **END TRANSACTION** 命令。
 *  **ROLLBACK**：回滚所做的更改。

事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。

事务（Transaction）可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。此类事务通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令。不过在数据库关闭或发生错误时，事务处理也会回滚，来看下启动一个事务的语法：

BEGIN;
    
    or 
    
    BEGIN TRANSACTION;

COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令，它把自上次 COMMIT 或 ROLLBACK 命令以来的所有事务保存到数据库，来看下 COMMIT 命令的语法：

COMMIT;
    
    or
    
    END TRANSACTION;

ROLLBACK 命令是用于撤消尚未保存到数据库的事务的事务命令，它只能用于撤销自上次发出 COMMIT 或 ROLLBACK 命令以来的事务，来看下ROLLBACK 命令的语法：

ROLLBACK;

接下来我们就要开始一个事务，并从表中删除 age = 25 的记录，最后，我们使用 ROLLBACK 命令撤消所有的更改：

BEGIN;
    DELETE FROM COMPANY WHERE AGE = 25;
    ROLLBACK;

运行完毕上述sql之后，我们可以检查 COMPANY 表，数据不变。

接下来，再次开始另一个事务，从表中删除 age = 25 的记录，最后我们使用 COMMIT 命令提交所有的更改：

BEGIN;
    DELETE FROM COMPANY WHERE AGE = 25;
    COMMIT;

运行完毕之后，再次检查 COMPANY 表，会发现数据已经改变。

子查询或内部查询或嵌套查询是在另一个 SQLite 查询内嵌入在 WHERE 子句中的查询，我们使用子查询返回的数据将被用在主查询中作为条件，以进一步限制要检索的数据。子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等，来看下子查询必须遵循的几个规则：

*  子查询必须用括号括起来。
 *  子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
 *  ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
 *  子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。
 *  BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。

子查询通常与 SELECT 语句一起使用，来看下基本语法：

SELECT column_name [, column_name ]
    FROM   table1 [, table2 ]
    WHERE  column_name OPERATOR
          (SELECT column_name [, column_name ]
          FROM table1 [, table2 ]
          [WHERE])

接下来，我们来检查 SELECT 语句中的子查询使用：

SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ;

子查询也可以与 INSERT 语句一起使用。INSERT 语句使用子查询返回的数据插入到另一个表中。在子查询中所选择的数据可以用任何字符、日期或数字函数修改，基本语法如下：

INSERT INTO table_name [ (column1 [, column2 ]) ]
               SELECT [ *|column1 [, column2 ]
               FROM table1 [, table2 ]
               [ WHERE VALUE OPERATOR ]

我们先来假设 COMPANY\_BKP 的结构与 COMPANY 表相似，且可使用相同的 CREATE TABLE 进行创建，只是表名改为 COMPANY\_BKP。现在把整个 COMPANY 表复制到 COMPANY\_BKP，语法如下：

INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ;

子查询可以与 UPDATE 语句结合使用。当通过 UPDATE 语句使用子查询时，表中单个或多个列被更新，基本语法如下：

UPDATE table
    SET column_name = new_value
    [ WHERE OPERATOR [ VALUE ]
       (SELECT COLUMN_NAME
       FROM TABLE_NAME)
       [ WHERE) ]

假设，我们有 COMPANY\_BKP 表，是 COMPANY 表的备份，之后，我们把 COMPANY 表中所有 AGE 大于或等于 27 的客户的 SALARY 更新为原来的 0.50 倍：

UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 );

子查询可以与 DELETE 语句结合使用，就像上面提到的其他语句一样，基本语法如下：

DELETE FROM TABLE_NAME
    [ WHERE OPERATOR [ VALUE ]
       (SELECT COLUMN_NAME
       FROM TABLE_NAME)
       [ WHERE) ]

假设，我们有 COMPANY\_BKP 表，是 COMPANY 表的备份，之后我们来尝试删除 COMPANY 表中所有 AGE 大于或等于 27 的客户记录：

DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );

好啦，本次记录就到这里了。

如果感觉不错的话，请多多点赞支持哦。。。