sqlite3 小结-白红宇

sqlite3 小结

阅读量：6900 次

发布时间：2019-06-27

本文共 7411 字，大约阅读时间需要 24 分钟。

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

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

DQL（数据查询语言）：select

.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>.tables10 11 sqlite>.schema company12 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_name32     SET column1 = value1, column2 = value2....columnN=valueN33     [ WHERE  CONDITION ];34 35 36 //ALTER37 //重命名已有表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-list12     FROM table_name13     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     SELECT21     FROM22     WHERE23     GROUP BY24     HAVING25     ORDER BY26 27 28 //HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前29     SELECT column1, column230     FROM table1, table231     WHERE [ conditions ]32     GROUP BY column1, column233     HAVING [ conditions ]34     ORDER BY column1, column235 36 sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;37 38 39 // DISTINCT40     SELECT DISTINCT column1, column2,.....columnN41     FROM table_name42     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     UNION17 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 ALL28 29     SELECT column1 [, column2 ]30     FROM table1 [, table2 ]31     [WHERE condition]32 33 34 //表 别名35     SELECT column1, column2....36     FROM table_name AS alias_name37     WHERE [condition];38 39 sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT40             FROM COMPANY AS C, DEPARTMENT AS D41             WHERE  C.ID = D.EMP_ID;42 43 //列 别名44     SELECT column_name AS alias_name45     FROM table_name46     WHERE [condition];47 48 sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT49             FROM COMPANY AS C, DEPARTMENT AS D50             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_name16     BEGIN17      -- 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         REAL28     );29 30 sqlite> CREATE TABLE AUDIT(31         EMP_ID INT NOT NULL,32         ENTRY_DATE TEXT NOT NULL33     );34 35 /*在这里，ID 是 AUDIT 记录的 ID，EMP_ID 是来自 COMPANY 表的 ID，DATE 将保持 COMPANY 中记录被创建时的时间戳。所以，现在让我们在 COMPANY 表上创建一个触发器*/36 37 sqlite> CREATE TRIGGER audit_log AFTER INSERT38     ON COMPANY39     BEGIN40        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_master46     WHERE type = 'trigger';47 48 //列出特定表上的触发器，则使用 AND 子句连接表名49 sqlite> SELECT name FROM sqlite_master50     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 AS2     SELECT column1, column2.....3     FROM table_name4     WHERE [condition];5 6 7     DROP VIEW view_name;

事务　　索引-->indexed by

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

转载于:https://www.cnblogs.com/aze-003/p/5262409.html

你可能感兴趣的文章