SQLite 高级语法介绍

Alter命令

SQLite 的 ALTER TABLE 命令不通过执行一个完整的转储和数据的重载来修改已有的表。您可以使用 ALTER TABLE 语句重命名表，使用 ALTER TABLE 语句还可以在已有的表中添加额外的列。

在 SQLite 中，除了重命名表和在已有的表中添加列，ALTER TABLE 命令不支持其他操作。

重命名已有的表的 ALTER TABLE：

ALTER TABLE database_name.table_name RENAME TO new_table_name;

alter table person rename to new_person;

在已有的表中添加一个新的列的 ALTER TABLE：

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

alter table new_person add column salary real;

Truncate Table

在 SQLite 中，并没有 TRUNCATE TABLE 命令，但可以使用 SQLite 的 DELETE 命令从已有的表中删除全部的数据，但建议使用 DROP TABLE 命令删除整个表，然后再重新创建一遍。

DELETE FROM table_name;

DELETE FROM PERSON;
VACUUM;

这样，PERSON 表中的记录完全被删除，使用 SELECT 语句将没有任何输出。

视图（View）

视图（View）只不过是通过相关的名称存储在数据库中的一个 SQLite 语句。视图（View）实际上是一个以预定义的 SQLite 查询形式存在的表的组合。

视图（View）可以包含一个表的所有行或从一个或多个表选定行。视图（View）可以从一个或多个表创建，这取决于要创建视图的 SQLite 查询。

视图（View）是一种虚表，允许用户实现以下几点：

• 用户或用户组查找结构数据的方式更自然或直观。

• 限制数据访问，用户只能看到有限的数据，而不是完整的表。

• 汇总各种表中的数据，用于生成报告。

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

创建视图

SQLite 的视图是使用 CREATE VIEW 语句创建的。SQLite 视图可以从一个单一的表、多个表或其他视图创建。

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

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

在 PERSON 表中创建一个视图。

CREATE VIEW PERSON_VIEW AS
SELECT ID, NAME, AGE
FROM  PERSON;

查询 COMPANY_VIEW，与查询实际表的方式类似。

select * from person_view;

id          name        age
----------  ----------  ----------
1           Jack        21
2           Mary        20
3           Mike        22
4           Amy         19
5           Alen        18
6           Ren         21
7           Lucy        20
8           John        17
9           Kate        21

删除视图

要删除视图，只需使用带有 view_name 的 DROP VIEW 语句。

DROP VIEW view_name;

Autoincrement（自动递增）

SQLite 的 AUTOINCREMENT 是一个关键字，用于表中的字段值自动递增。我们可以在创建表时在特定的列名称上使用 AUTOINCREMENT 关键字实现该字段值的自动增加。

关键字 AUTOINCREMENT 只能用于整型（INTEGER）字段。

create table person (
   id integer primary key autoincrement,
   ...
 );

这样插入数据id就会自动增加啦。

insert into person(name, age, dept) values('Nate', 21, 2);

既然提到了Autoincrement，就说明下rowid。SQLite数据库中的表均有一个特殊的rowid字段，它是一个不重复的64位有符号整数，默认起始值为1。rowid别名为oid或rowid，但在创建表的SQL声明语句中只能使用rowid作为关键字。如果在创建表的时候设置了类型为integer的具有自动增长性的主键，那么这时的主键相当于是rowid的别名。

sqlite> select rowid, name from person;
rowid       name
----------  ----------
1           Jack
2           Mary
3           Mike
4           Amy

rowid只能是单调递增的，它由SQLite内部维护，不能自己指定。对于失败的插入操作，rowid也可能在原来的基础上执行了自增。删除或回滚操作并不会减小rowid的值。当rowid达到所能表示的最大值时，这时如果有新纪录要插入，系统就会从之前没有被使用过的（或从已经被删除的记录的)rowid中随机取出一个作为rowid。若没有可用的rowid，系统就会抛出SQLITE_FULL的错误。

rowid字段在表中默认是隐藏的，也就是说，除非SQL查询语句中指定返回rowid，否则查询结果不会包含有rowid字段。

可以在创建表时使用WITHOUT ROWID以声明不生成rowid字段，例如：

create table if not exists person(
   id integer primary key,
   ...
) without rowid; 

将关键字AUTOINCREMENT使用在声明为WITHOUT ROWID的表不但不起作用，还将引发一个错误。

事务（Transaction）

事务（Transaction）是一个对数据库执行工作单元。事务（Transaction）是以逻辑顺序完成的工作单位或序列，可以是由用户手动操作完成，也可以是由某种数据库程序自动完成。

事务（Transaction）是指一个或多个更改数据库的扩展。例如，如果您正在创建一个记录或者更新一个记录或者从表中删除一个记录，那么您正在该表上执行事务。重要的是要控制事务以确保数据的完整性和处理数据库错误。

实际上，您可以把许多的 SQLite 查询联合成一组，把所有这些放在一起作为事务的一部分进行执行。

事务属性

事务（Transaction）具有以下四个标准属性，通常根据首字母缩写为 ACID：这些属性的介绍在我的SQLite入门介绍（一）博客中有说明。

事务控制

使用下面的命令来控制事务：

• BEGIN TRANSACTION：开始事务处理。

• COMMIT：保存更改，或者可以使用 END TRANSACTION 命令。

• ROLLBACK：回滚所做的更改。

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

BEGIN TRANSACTION

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

BEGIN;
or 
BEGIN TRANSACTION;

COMMIT

COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令。

COMMIT 命令把自上次 COMMIT 或 ROLLBACK 命令以来的所有事务保存到数据库。

COMMIT 命令的语法如下：

COMMIT;
or
END TRANSACTION;

ROLLBACK

ROLLBACK 命令是用于撤消尚未保存到数据库的事务的事务命令。

ROLLBACK 命令只能用于撤销自上次发出 COMMIT 或 ROLLBACK 命令以来的事务。

ROLLBACK 命令的语法如下：

ROLLBACK;

实例

在我创建的表 PERSON 中的数据如下：

id          name        age         dept_id     salary
----------  ----------  ----------  ----------  ----------
1           Jack        21          2
2           Mary        20          1
3           Mike        22          3
4           Amy         19          1
5           Alen        18          2
6           Ren         21          3
7           Lucy        20          1
8           John        17          0
9           Kate        21          1

现在我们开始一个事务，最后使用 ROLLBACK 取消所有的更改。

begin;
delete from person where id = 9;
insert into person values(9, 'Mini', 16, 1, 5000);
rollback;

然后我们查询 PERSON 表会发现数据没有修改。

现在我们使用 COMMIT 命令提交所有的更改。

begin;
delete from person where id = 9;
insert into person values(9, 'Mini', 16, 1, 5000);
commit;

id          name        age         dept_id     salary
----------  ----------  ----------  ----------  ----------
1           Jack        21          2
2           Mary        20          1
3           Mike        22          3
4           Amy         19          1
5           Alen        18          2
6           Ren         21          3
7           Lucy        20          1
8           John        17          0
9           Mini        16          1           5000.0

事务的创建可以让数据库对多修改更有效率。

子查询

子查询或内部查询或嵌套查询是在另一个 SQLite 查询内嵌入在 WHERE 子句中的查询。

使用子查询返回的数据将被用在主查询中作为条件，以进一步限制要检索的数据。

子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。

以下是子查询必须遵循的几个规则：

• 子查询必须用括号括起来。

• 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。

• ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。

• 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。

• BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。

SELECT

子查询通常与 SELECT 语句一起使用。基本语法如下：

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

让我们用 PERSON 表来检查 SELECT 语句中的子查询使用：

select * from person 
where id in (select id from person where age > 20);

id          name        age         dept_id     salary
----------  ----------  ----------  ----------  ----------
1           Jack        21          2
3           Mike        22          3
6           Ren         21          3

INSERT

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

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

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

INSERT INTO PERSON_COPY
     SELECT * FROM PERSON 
     WHERE ID IN (SELECT ID 
                  FROM PERSON) ;

UPDATE

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

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

假设，我们有 PERSON_COPY 表，是 PERSON_COPY 表的备份。
下面的实例把 PERSON 表中所有 AGE 大于或等于 20 的数据的 SALARY 更新为原来的 0.50 倍：

UPDATE person set salary = salary * 0.5 
where age in 
(select age from person_copy where age >= 20);

DELETE

子查询可以与 DELETE 语句结合使用，就像上面提到的其他语句一样。

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

删除 PERSON 表中所有 AGE 大于或等于 20 的记录：

DELETE FROM PERSON
     WHERE AGE IN (SELECT AGE FROM PERSON_COPY
                   WHERE AGE >= 20 );

结束语：本文仅用来学习记录，参考查阅。