sqlite3

来源
sqlite3 进入sqlite3数据库命令行

.exit/.quit 退出sqlite3命令行

sqlite3 test.db 创建一个新的数据库

databases 检查创建的数据库是否在数据库列表中

sqlite3 test.db .dump > test.sql 将转换test.db数据库的内容到SQLite的语句中，并将其转储到文本文件test.sql中

sqlite3 test.db < test.sql 从生成的test.sql中恢复

create tablename person(id int primary key not null,name text not null); 创建一个person表

.schema person 查看表的完整信息

drop tablename person 删除person表

.tables 列出附加数据库中的所有的表

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
/*创建一个company表*/

insert into company (id, name, age, address, salary) values (1, 'Paul', 32, 'California', 20000.00);
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 ); 两种插入数据方式（不区分大小写）

SELECT tbl_name FROM sqlite_master WHERE type = 'table'; 列出所有在数据库中创建的表

SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY'; 列出关于 COMPANY 表的完整信息

SELECT CURRENT_TIMESTAMP; 返回当前系统日期和时间值

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

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; 列出了 AGE 大于等于 25 且工资大于等于 65000.00 的所有记录

SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; AGE 大于等于 25 或工资大于等于 65000.00

SELECT * FROM COMPANY WHERE AGE IS NOT NULL; AGE 不为 NULL

SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*'; NAME 以 'Ki' 开始的所有记录，'Ki' 之后的字符不做限制

SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 ); AGE 的值为 25 或 27

SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27; AGE 的值在 25 与 27 之间

SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000); 子查询查找 SALARY > 65000 的带有 AGE 字段的所有记录,后边的 WHERE 子句与 EXISTS 运算符一起使用，列出了外查询中的 AGE 存在于子查询返回的结果中的所有记录

SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000); 子查询查找 SALARY > 65000 的带有 AGE 字段的所有记录，后边的 WHERE 子句与 > 运算符一起使用，列出了外查询中的 AGE 大于子查询返回的结果中的年龄的所有记录：

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; AGE 大于等于 25 且工资大于等于 65000.00（可以使用 AND 运算符来结合 N 个数量的条件）

SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; AGE 大于等于 25 或工资大于等于 65000.00 （可以使用 OR 运算符来结合 N 个数量的条件）

UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6; 更新 ID 为 6 的客户地址

UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00; 修改 COMPANY 表中 ADDRESS 和 SALARY 列的所有值，则不需要使用 WHERE 子句

DELETE FROM COMPANY WHERE ID = 7; 删除 ID 为 7 的客户：

DELETE FROM COMPANY; 从 COMPANY 表中删除所有记录，则不需要使用 WHERE 子句

SELECT * FROM COMPANY WHERE AGE LIKE '2%'; 显示 COMPANY 表中 AGE 以 2 开头的所有记录（百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用。）

SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%'; 显示 COMPANY 表中 ADDRESS 文本里包含一个连字符（-）的所有记录：

SELECT * FROM COMPANY WHERE AGE GLOB '2*'; 显示 COMPANY 表中 AGE 以 2 开头的所有记录（星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。这些符号可以被组合使用。）

SELECT * FROM COMPANY WHERE ADDRESS GLOB '*-*'; 显示 COMPANY 表中 ADDRESS 文本里包含一个连字符（-）的所有记录

SELECT * FROM COMPANY LIMIT 6; 提取前6行（限制了您想要从表中提取的行数）

select * from company limit 2 offset 4; 第(4+1)位开始提取2个记录

SELECT * FROM COMPANY ORDER BY SALARY ASC; 将结果按 SALARY 升序排序

SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC; 结果先按NAME升序排序，如果NAME中有相同的NAME，再按SALARY升序排序

SELECT * FROM COMPANY ORDER BY NAME DESC; 将结果按 NAME 降序排序

SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME; 获取同一个名字的工资总额

SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC; GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
/*HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前*/

SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2 显示名称计数小于 2 的所有记录

SELECT DISTINCT name FROM COMPANY; 获取唯一一次记录

NOT NULL 约束 默认情况下，列可以保存 NULL 值，如果不想某列有NULL值，需要定义此约束。

DEFAULT 约束 在 INSERT INTO 语句没有提供一个特定的值时，为列提供一个默认值

UNIQUE 约束 防止在一个特定的列存在两个记录具有相同的值

PRIMARY KEY 约束 在一个表中可以有多个 UNIQUE 列，但只能有一个主键。在设计数据库表时，主键是很重要的。主键是唯一的 ID。

CHECK 约束 启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表。

ID          DEPT        EMP_ID
----------  ----------  ----------
1           IT Billing  1
2           Engineerin  2
3           Finance     7
/* DEPARTMENT */

CROSS JOIN 交叉连接————第一个表的每一行与第二个表的每一行进行匹配，如果两个输入表分别有 x 和 y 列，则结果表有 x*y 列

SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT; 表COMPANY和表DEPARTMENT交叉连接

INNER JOIN 内连接————把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对（内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的）

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; 表COMPANY.ID和表DEPARTMENT.EMP_ID相等的列

OUTER JOIN 外连接————从一个或两个表中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中

SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; 表COMPANY中没有从表DEPARTMENT中连接的行的值为NULL

UNION 合并两个或多个 SELECT 语句的结果，不返回任何重复的行（为了使用 UNION，每个 SELECT 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但它们不必具有相同的长度。）

UNION ALL 结合两个 SELECT 语句的结果，包括重复行。适用于 UNION 的规则同样适用于 UNION ALL 运算符。

UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7); 设置一些允许空值的值为 NULL

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL; 列出所有 SALARY 不为 NULL 的记录

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL; 列出所有 SALARY 为 NULL 的记录

SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID; 使用 C 和 D 分别作为 COMPANY 和 DEPARTMENT 表的别名

Trigger 触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用

• 在特定的数据库表发生 DELETE、INSERT 或 UPDATE 时触发，或在一个或多个指定表的列发生更新时触发
• SQLite 只支持 FOR EACH ROW 触发器（Trigger），没有 FOR EACH STATEMENT 触发器（Trigger）。因此，明确指定 FOR EACH ROW 是可选的
• 如果提供 WHEN 子句，则只针对 WHEN 子句为真的指定行执行 SQL 语句。如果没有提供 WHEN 子句，则针对所有行执行 SQL 语句
• BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作
• 当触发器相关联的表删除时，自动删除触发器（Trigger)
• 一个特殊的 SQL 函数 RAISE() 可用于触发器程序内抛出异常。

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

Trigger实例

为被插入到新创建的 COMPANY 表（如果已经存在，则删除重新创建）中的每一个记录保持审计试验：

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

为了保持审计试验，我们将创建一个名为 AUDIT 的新表。每当 COMPANY 表中有一个新的记录项时，日志消息将被插入其中：

CREATE TABLE AUDIT(
    EMP_ID INT NOT NULL,
    ENTRY_DATE TEXT NOT NULL
);

ID 是 AUDIT 记录的 ID，EMP_ID 是来自 COMPANY 表的 ID，DATE 将保持 COMPANY 中记录被创建时的时间戳。所以，现在让我们在 COMPANY 表上创建一个触发器

CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

在 COMPANY 表中插入记录，这将导致在 AUDIT 表中创建一个审计日志记录。因此，让我们在 COMPANY 表中创建一个记录

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );

这将在 COMPANY 表中创建如下一个记录：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

同时，将在 AUDIT 表中创建一个记录。这个纪录是触发器的结果，这是我们在 COMPANY 表上的 INSERT 操作上创建的触发器（Trigger）。类似的，可以根据需要在 UPDATE 和 DELETE 操作上创建触发器（Trigger）。

EMP_ID      ENTRY_DATE
----------  -------------------
1           2013-04-05 06:26:00

SELECT name FROM sqlite_master WHERE type = 'trigger'; 从 sqlite_master 表中列出所有触发器

SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'COMPANY'; 列出特定表上的触发器

DROP TRIGGER trigger_name; 删除已有的触发器

Transaction 事务————是指一个或多个更改数据库的扩展。例如，如果您正在创建一个记录或者更新一个记录或者从表中删除一个记录，那么您正在该表上执行事务。重要的是要控制事务以确保数据的完整性和处理数据库错误。
实际上，您可以把许多的 SQLite 查询联合成一组，把所有这些放在一起作为事务的一部分进行执行。

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

事务控制

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

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

sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> ROLLBACK;
/*表中删除 age = 25 的记录，最后，我们使用 ROLLBACK 命令撤消所有的更改。*/

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

INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ; 假设 COMPANY_BKP 的结构与 COMPANY 表相似，且可使用相同的 CREATE TABLE 进行创建，只是表名改为 COMPANY_BKP。现在把整个 COMPANY 表复制到 COMPANY_BKP

UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 ); 假设，我们有 COMPANY_BKP 表，是 COMPANY 表的备份。下面的实例把 COMPANY 表中所有 AGE 大于或等于 27 的客户的 SALARY 更新为原来的 0.50 倍：

DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 ); 假设，我们有 COMPANY_BKP 表，是 COMPANY 表的备份。下面的实例删除 COMPANY 表中所有 AGE 大于或等于 27 的客户记录：

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

COUNT 用来计算一个数据库表中的行数

SELECT count(*) FROM COMPANY; 得到company表的行数

MAX 允许我们选择某列的最大值

SELECT max(salary) FROM COMPANY; 得到salary的最大值

MIN 允许我们选择某列的最小值

SELECT min(salary) FROM COMPANY; 得到salary的最小值

AVG 计算某列的平均值

SELECT avg(salary) FROM COMPANY; 所有的salary'的平均値

SUM 允许为一个数值列计算总和

SELECT sum(salary) FROM COMPANY; 所有的salary的和

RANDOM 返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数

SELECT random() AS Random; 生成一个随机数

ABS 返回数值参数的绝对值

SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC"); 求值

UPPER 把字符串转换为大写字母

SELECT upper(name) FROM COMPANY; 把表中的所有name转换为大写

LOWER 把字符串转换为小写字母

SELECT lower(name) FROM COMPANY; 把表中的所有name转换为小写

LENGTH 返回字符串的长度

SELECT name, length(name) FROM COMPANY; 返回name和name的长度。

sqlite_version 返回 SQLite 库的版本

SELECT sqlite_version() AS 'SQLite Version'; 当前版本