基本的DDL、DML
创建表，包含日期字段
CREATE TABLE WordTable (id integer PRIMARY KEY autoincrement , word TEXT , updatedate datetime )
插入一行，包含日期字段
INSERT INTO WordTable (id,word,updatedate) VALUES (6,‘ceshi‘,‘2020-08-11 13:42:29‘);
查找，包含日期字段
SELECT * FROM WordTable WHERE updatedate>=‘2020-08-11 10:42:29‘ AND updatedate<=‘2020-08-11 10:42:29‘
查找，按照排序以及限制条目输出
SELECT * FROM TX WHERE updatedate LIKE ‘%:%‘ ORDER BY updatedate ASC LIMIT 1
---》DESC:降序 ASC:升序
---》%：匹配任意长度字符串
---》_：匹配一个字符
---》LIMIT 1：限制输出一条结果，必须在sql语句最后
删除记录
DELETE FROM table WHERE User=‘555‘
更新数据
某一项数值加1
UPDATE User SET Logins=Logins+1 WHERE User=‘555‘
更新多个字段
UPDATE Tx SET Ip=‘11111‘,Name=‘2222‘ WHERE Uid=‘TTTTTTTTTTTTTTT1‘
视图
视图（View） 是一种虚表，允许用户实现以下几点：
- 用户或用户组查找结构数据的方式更自然或直观。
- 限制数据访问，用户只能看到有限的数据，而不是完整的表。
- 汇总各种表中的数据，用于生成报告。
SQLite 视图内容是随着实际数据表实时变化，是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。
//创建
CREATE VIEW my_view AS SELECT Ip FROM TX
//删除
DROP VIEW my_view
索引
原理
- 使用的是B-树原理
影响
- 提高查询的速度
- 创建索引需要耗费一定的时间
- 索引需要占用物理空间
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度
使用场景
- 在经常需要搜索的列上创建
- 索引不应该使用在较小的表上。
- 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
- 索引不应该使用在含有大量的 NULL 值的列上。
单列索引
CREATE INDEX index_name ON table_name (column_name);
唯一索引
使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。
CREATE UNIQUE INDEX index_name on table_name (column_name);
组合索引
CREATE INDEX index_name on table_name (column1, column2);
隐式索引
隐式索引是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。
删除索引
DROP INDEX index_name;
如何正确使用索引
比如通过：CREATE INDEX comp_ind ON table1(x, y, z)创建索引，那么x,xy,xyz都是前导列，而yz，y，z这样的就不是。在WHERE子句中，前导列必须使用等于或者IN操作，最右边的列可以使用不等式，这样索引才可以完全生效。同时，WHERE子句中的列不需要全建立了索引，但是必须保证建立索引的列之间没有间隙。
例子：
//创建索引
CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);
//查询语句
...WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d=‘hello‘
abcd四列都是有效的，因为只有等于和IN操作，并且是前导列。
//查询语句
... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d=‘hello‘
那这里只有a，b和c的索引会是有效的，d列的索引会失效，因为它在c列的右边，而c列使用了不等式，根据使用不等式的限制，c列已经属于最右边。
//查询语句
... WHERE b IN (1,2,3) AND c NOT NULL AND d=‘hello‘
索引将不会被使用，因为没有使用前导列，这个查询会是一个全表查询。
//对于between，or，like语句，都无法使用索引
触发器
SQLite 触发器（Trigger） 是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。
关键字
- new 代表刚创建的行，old刚删除的行
- new在before触发器中赋值，取值；在after触发器中取值。
- new是新插入的数据，old是原来的数据
- insert只会有new，代表着要插入的新记录
- delete只会有old，代表着要删除的记录
INSERT、DELETE 和 UPDATE创建触发器的基本语法
CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
-- 触发器逻辑....
END;
UPDATE指定字段创建触发器语法（INSERT、DELETE没有此特性）
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
-- 触发器逻辑....
END;
列出触发器
- 列出数据库所有触发器
SELECT name FROM sqlite_master
WHERE type = ‘trigger‘;
- 列出特定表触发器
SELECT name FROM sqlite_master
WHERE type = ‘trigger‘ AND tbl_name = ‘tablename‘;
删除触发器
DROP TRIGGER trigger_name;
实例
- 创建COMPANY表
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
- 创建AUDIT表
CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
- 在 COMPANY 表上创建一个触发器
CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime(‘now‘));
END;
- 在 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 表
| EMP_ID | ENTRY_DATE |
|---|---|
| 1 | 2013-04-05 06:26:00 |
事务
- 事务定义了一组SQL命令的边界，这一组命令或者作为一个整体被全部执行，或者全部不执行，这被称作为数据库完整性的原子性原则。
- 默认情况下，SQLite中每条命令都会自成一个事务，自动提交或回滚，此操作模式称为隐式事务或者自动提交模式。
- 事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。
开启事务命令
- 事务（Transaction）可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。此类事务通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令。不过在数据库关闭或发生错误时，事务处理也会回滚。
BEGIN;
或者
BEGIN TRANSACTION
提交事务命令
- 用于把事务调用的更改保存到数据库中的事务命令。
- 把自上次 COMMIT 或 ROLLBACK 命令以来的所有事务保存到数据库。
COMMIT
或者
END TRANSACTION
回滚命令
- 用于撤消尚未保存到数据库的事务的事务命令。
- 只能用于撤销自上次发出 COMMIT 或 ROLLBACK 命令以来的事务。
ROLLBACK
实例
- 删除后再回滚，记录还在
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;
- 删除后提交，删除成功，记录已被删除
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;
专业术语
DML（Data Manipulation Lanaguage，数据操纵语言）
DML就是我们经常用到的SELECT、INSERT、UPDATE和DELETE语句，主要是用来对数据进行CRUD：增查改删（create, read (retrieve), update, delete）操作。
DDL（Data Definition Language，数据定义语言）
DDL就是我们在创建表的时候用到的一些语句，比如说CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构、数据类型、表之间的链接或约束等初始化工作上。
DCL（Data Control Language，数据库控制语言）
DCL是用来设置或更改数据库用户或角色权限的语句，包括GRANT、DENY、REVOKE等语句。