【转】sqlite3 小结

原文 https://www.cnblogs.com/aze-003/p/5262409.html

sqlite3 小结

 

sqlite安装

DDL（数据定义语言）：create、alter、drop

DML（数据操作语言）：insert、update、delete

DQL（数据查询语言）：select

sqlite3 命令：

   .header on　　.mode [csv/column/html/insert/line/list/tabs/tcl]　　.databases

　　.exit(.quit)　　.help　　.import FILE TABLE　　.schema ?TABEL?　　.dump ?TABLE?　　.show

sqlite3 语法：

　　不区分大小写（除GLOB与glob）

数据类型：

　　null、integer、real、text（文本字符串）、blob

--使用 SQLite .dump 点命令来导出完整的数据库在一个文本文件中
$sqlite3 testDB.db .dump > testDB.sql

--恢复
$sqlite3 testDB.db < testDB.sql

创建、附加、分离数据库：

1 --创建数据库
2 $sqlite3 DatabaseName.db 

 1 --附加数据库 
 2 ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
 3 
 4 --如：
 5 sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';
 6 
 7 
 8 --分离数据库
 9 DETACH DATABASE 'Alias-Name';
10 
11 --如：
12 sqlite> DETACH DATABASE 'TEST';

创建、删除(drop)、更改表(基于'TABLE'）；插入、查询、更新、删除(delete)表项(基于'FROM')

 1 sqlite> CREATE TABLE COMPANY(
 2        ID INT PRIMARY KEY     NOT NULL,
 3        NAME           TEXT    NOT NULL,
 4        AGE            INT     NOT NULL,
 5        ADDRESS        CHAR(50),
 6        SALARY         REAL
 7     );
 8 
 9 sqlite>.tables
10 
11 sqlite>.schema company
12 
13 
14 --//DROP TABLE database_name.table_name;
15 
16 sqlite>DROP TABLE COMPANY;
17 
18 
19  /*
20 INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
21     VALUES (value1, value2, value3,...valueN); 
22 INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
23 */
24 sqlite>INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
25     VALUES (1, 'Paul', 32, 'California', 20000.00 );
26 sqlite>INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
27 
28 --SELECT column1, column2, columnN FROM table_name;
29 
30 
31 UPDATE table_name
32     SET column1 = value1, column2 = value2....columnN=valueN
33     [ WHERE  CONDITION ];
34 
35 
36 //ALTER
37 //重命名已有表
38 ALTER TABLE database_name.table_name RENAME TO new_table_name;
39  
40 
41 //在已有表中添加一个新的列
42 ALTER TABLE database_name.table_name ADD COLUMN column_def...; 

关键字：DISTINCT　　GROUP BY　　HAVING　　ORDER BY　　LIMIT　　OFFSET

 1 //LIMIT    OFFSET
 2 SELECT column1, column2, columnN
 3     FROM table_name
 4     LIMIT [num of rows] OFFSET [row num];
 5 
 6 
 7 
 8 
 9 
10 //在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。
11     SELECT column-list
12     FROM table_name
13     WHERE [ conditions ]
14     [GROUP BY column1, column2....columnN]
15     [ORDER BY column1, column2, .. columnN] [ASC | DESC];
16 
17 sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;;
18 
19 
20     SELECT
21     FROM
22     WHERE
23     GROUP BY
24     HAVING
25     ORDER BY
26 
27 
28 //HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前
29     SELECT column1, column2
30     FROM table1, table2
31     WHERE [ conditions ]
32     GROUP BY column1, column2
33     HAVING [ conditions ]
34     ORDER BY column1, column2
35 
36 sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;
37 
38 
39 // DISTINCT
40     SELECT DISTINCT column1, column2,.....columnN
41     FROM table_name
42     WHERE [condition]

sqlite3约束：

• NOT NULL 约束：确保某列不能有 NULL 值。
• DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
• UNIQUE 约束：确保某列中的所有值是不同的。
• PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
• CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

删除约束

SQLite 支持 ALTER TABLE 的有限子集。在 SQLite 中，ALTER TABLE 命令允许用户重命名表，或向现有表添加一个新的列。重命名列，删除一列，或从一个表中添加或删除约束都是不可能的。

JOIN[INNER/OUTER/NATURAL]　　UNION　　UNION ALL　　表、列别名

 1 //ON、USING 用在join中
 2 //内连接（INNER JOIN）是最常见的连接类型，是默认连接类型。INNER 关键字是可选的。
 3 //外链接 OUTER 关键字必选
 4 SELECT ... FROM table1 LEFT [OUTER/INNER] JOIN table2 ON conditional_expression ...
 5 
 6 SELECT ... FROM table1 LEFT [OUTER/INNER] JOIN table2 USING ( column1 ,... ) ...
 7 
 8 //自然连接（NATURAL JOIN）类似于 JOIN...USING，只是它会自动测试存在两个表中的每一列的值之间相等值：
 9 SELECT ... FROM table1 NATURAL JOIN table2...
10 
11 //UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。
12     SELECT column1 [, column2 ]
13     FROM table1 [, table2 ]
14     [WHERE condition]
15 
16     UNION
17 
18     SELECT column1 [, column2 ]
19     FROM table1 [, table2 ]
20     [WHERE condition]
21 
22 //UNION ALL 运算符用于结合两个 SELECT 语句的结果，包括重复行。
23     SELECT column1 [, column2 ]
24     FROM table1 [, table2 ]
25     [WHERE condition]
26 
27     UNION ALL
28 
29     SELECT column1 [, column2 ]
30     FROM table1 [, table2 ]
31     [WHERE condition]
32 
33 
34 //表 别名
35     SELECT column1, column2....
36     FROM table_name AS alias_name
37     WHERE [condition];
38 
39 sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT
40             FROM COMPANY AS C, DEPARTMENT AS D
41             WHERE  C.ID = D.EMP_ID;
42 
43 //列 别名
44     SELECT column_name AS alias_name
45     FROM table_name
46     WHERE [condition];
47 
48 sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
49             FROM COMPANY AS C, DEPARTMENT AS D
50             WHERE  C.ID = D.EMP_ID;

  

SQLite 触发器

 SQLite 触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。以下是关于 SQLite 的触发器（Trigger）的要点：

• SQLite 的触发器（Trigger）可以指定在特定的数据库表发生 DELETE、INSERT 或 UPDATE 时触发，或在一个或多个指定表的列发生更新时触发。
• SQLite 只支持 FOR EACH ROW 触发器（Trigger），没有 FOR EACH STATEMENT 触发器（Trigger）。因此，明确指定 FOR EACH ROW 是可选的。
• WHEN 子句和触发器（Trigger）动作可能访问使用表单 NEW.column-name 和 OLD.column-name 的引用插入、删除或更新的行元素，其中 column-name 是从与触发器关联的表的列的名称。
• 如果提供 WHEN 子句，则只针对 WHEN 子句为真的指定行执行 SQL 语句。如果没有提供 WHEN 子句，则针对所有行执行 SQL 语句。
• BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
• 当触发器相关联的表删除时，自动删除触发器（Trigger）。
• 要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是database.tablename。
• 一个特殊的 SQL 函数 RAISE() 可用于触发器程序内抛出异常。

语法

创建 触发器（Trigger） 的基本语法如下：

 1 CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name
 2     ON table_name
 3     BEGIN
 4      -- Trigger logic goes here....
 5     END;
 6 /*event_name 可以是在所提到的表 table_name 上的 INSERT、DELETE 和 UPDATE 数据库操作。您可以在表名后选择指定 FOR EACH ROW */
 7 CREATE TRIGGER database_name.trigger_name [BEFORE|AFTER] INSERT ON table_name FOR EACH ROW
 8     BEGIN
 9        stmt1;
10        stmt2;
11        ....
12     END;
13 
14 //以下是在 UPDATE 操作上在表的一个或多个指定列上创建触发器（Trigger）的语法：
15     CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_name
16     BEGIN
17      -- Trigger logic goes here....
18     END;
19 
20 
21 //如：将创建一个名为 AUDIT 的新表。每当 COMPANY 表中有一个新的记录项时，日志消息将被插入其中：
22 sqlite> CREATE TABLE COMPANY(
23        ID INT PRIMARY KEY     NOT NULL,
24        NAME           TEXT    NOT NULL,
25        AGE            INT     NOT NULL,
26        ADDRESS        CHAR(50),
27        SALARY         REAL
28     );
29 
30 sqlite> CREATE TABLE AUDIT(
31         EMP_ID INT NOT NULL,
32         ENTRY_DATE TEXT NOT NULL
33     );
34 
35 /*在这里，ID 是 AUDIT 记录的 ID，EMP_ID 是来自 COMPANY 表的 ID，DATE 将保持 COMPANY 中记录被创建时的时间戳。所以，现在让我们在 COMPANY 表上创建一个触发器*/
36 
37 sqlite> CREATE TRIGGER audit_log AFTER INSERT
38     ON COMPANY
39     BEGIN
40        INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
41     END;
42 
43 
44 //从 sqlite_master 表中列出所有触发器
45 sqlite> SELECT name FROM sqlite_master
46     WHERE type = 'trigger';
47 
48 //列出特定表上的触发器，则使用 AND 子句连接表名
49 sqlite> SELECT name FROM sqlite_master
50     WHERE type = 'trigger' AND tbl_name = 'COMPANY';
51 
52 
53 //删除已有的触发器
54 sqlite> DROP TRIGGER trigger_name;

  

VIEW

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

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

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

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

1     CREATE [TEMP | TEMPORARY] VIEW view_name AS
2     SELECT column1, column2.....
3     FROM table_name
4     WHERE [condition];
5 
6 
7     DROP VIEW view_name;

事务　　索引-->indexed by

总结出处：http://wiki.jikexueyuan.com/project/sqlite/