1.创建数据库

//存在会打开，不存在，会创建
sqlite3 TEST.sqlite

//查看数据库表概要 sqlite_master 一个特殊的表，储存了数据的相关信息
.schema sqlite_master

//注释
.help -- 这是帮助信息
.help /*这是帮助信息*/

//检查sqlite数据库列表
.databases

//查看数据库中的表
.tables

//查看建表信息
.schema
.schema Person

//退出sqlite
.quit

//导出完整数据库到文本文件中
sqlite3 TEST.sqlite .dump > TEST.sql

//导入文本文件中数据到数据库
sqlite3 TEST.sqlite < TEST.sql

//附加数据库，如果存在SQLite.sqlite数据库，则把SQLite.sqlite和new绑定。不存在，则会创建，然后绑定。temp和main不能被附加。
ATTACH DATABASE 'SQLite.sqlite' As 'new';

//分离数据库
DETACH DATABASE 'new';

//终端设置
.header on
.mode column
.timer on
.width 10,20,30 //设置输出宽度

默认不区分大小写，但是有部分特殊的是大小写敏感的。比如GLOB和glob。

2.创建表

//语法
CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

//使用
CREATE TABLE Person (
id INT PRIMARK KEY NOT NULL, 
name TEXT NOT NULL, 
age INT NOT NULL, 
score INT);

3.删除表

//语法
DROP TABLE database_name.table_name;

//使用
DROP TABLE Student;

4.插入

//语法1
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);
//语法3
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

//使用
INSERT INTO Person (id, name, age, score) VALUES (0,"zs",10,99);

INSERT INTO Person VALUES (1,"ls",11,98);

INSERT INTO Person (id, name, age) VALUES (2,"ww",11);

//通过拷贝，将一个表拷入另外一个表中
INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

//使用
INSERT INTO Student (id, name, age, score) SELECT id, name, age, score FROM Person;

5.查询

//基本语法，查询具体字段
SELECT column1, column2, columnN FROM table_name;
//简化，查询所有
SELECT * FROM table_name;

//使用
SELECT id,name,age,score FROM Student;
SELECT * FROM Student;

//查询数据库中的表
SELECT tbl_name FROM sqlite_master WHERE type = 'table';

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

6.运算符

//=
SELECT * FROM Student WHERE age = 10;

//>
SELECT * FROM Student WHERE age > 10;

//!=
SELECT * FROM Student WHERE age != 10;

//<>
SELECT * FROM Student WHERE age <> 10;

//>=
SELECT * FROM Student WHERE age >= 10;

//AND
SELECT * FROM Student WHERE age < 12 AND id < 2;

//OR
SELECT * FROM Student WHERE age = 11 OR id = 0;

//IS NOT NULL
SELECT * FROM Student WHERE score IS NOT NULL;

//LIKE
SELECT * FROM Student WHERE name LIKE "Z%";

//GLOB
SELECT * FROM Student WHERE name GLOB "z*";

//IN
SELECT * FROM Student WHERE age IN (10,11);

//NOT IN
SELECT * FROM Student WHERE age NOT IN (10,13);

//BETWEEN AND
SELECT * FROM Student WHERE age BETWEEN 9 AND 12;

//EXISTS
SELECT * FROM Student WHERE EXISTS (SELECT AGE FROM Student WHERE age > 10);

//子查询
SELECT * FROM Student WHERE age > (SELECT age FROM Student WHERE id >= 0);

//AVG()
SELECT AVG(age) FROM Student;

//SUM()
SELECT SUM(age) FROM Student;

//COUNT()
SELECT COUNT(age) FROM Student;

//CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP;

7.更新

//语法
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

//使用
UPDATE Student SET score = 88 WHERE id = 2;

//更新全部
UPDATE Student SET color = "red", score = 90;

8.删除

//语法
DELETE FROM table_name
WHERE [condition];

//使用
DELETE FROM Student WHERE id = 2;

//删除全部
ELETE FROM Student;

//清除未使用的空间，建议使用删除全部后，都清除一次
VACUUM;

9.LIKE

//匹配上了，返回1
//%表示0个或者多个
//_一个
SELECT * FROM Person WHERE name LIKE "z%";
SELECT * FROM Person WHERE name LIKE "z_";

10.GLOB

//同LIKE
//*表示0个或者多个
//?一个
SELECT * FROM Person WHERE name GLOB "l*";
SELECT * FROM Person WHERE name GLOB "l?";

11.LIMIT

//限制返回数量
//语法
SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

//使用
SELECT * FROM Person LIMIT 1;
SELECT * FROM Person LIMIT 1 OFFSET 1;

12.ORDER BY

排序

//语法
SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

//使用
SELECT * FROM Person ORDER BY score;//默认ASC升序
SELECT * FROM Person ORDER BY score DESC;//降序
SELECT * FROM Person ORDER BY score, name ASC;//按照score,name排序

13.GROUP BY

分组

//语法
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

//使用
SELECT * FROM Person GROUP BY name;
SELECT name,score FROM Person GROUP BY name ORDER BY name DESC;

14.HAVING

过滤

//语法
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

//使用
SELECT * FROM Person GROUP BY name HAVING count(name) > 1;

15.DISTINCT

清除重复

//语法
SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

//使用
SELECT DISTINCT name FROM Person;

16.各种语句在SELECT中的位置

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

17.约束

//NOT NULL，不能为空
CREATE TABLE Person (id INT PRIMARK KEY NOT NULL, name TEXT NOT NULL, age INT NOT NULL, score INT);

//DEFAULT，提供一个默认值
CREATE TABLE Student (id INT PRIMARK KEY NOT NULL, name TEXT DEFAULT "_");

//PRIMARY KEY，主键，不能重复
CREATE TABLE Student (id INT PRIMARK KEY NOT NULL, name TEXT DEFAULT "_");

//CHECK，约束，检查输入值，不满足不能输入到表
CREATE TABLE Teacher (id INT PRIMARK KEY NOT NULL, age INT CHECK(age>10));

18.结合

//交叉连接，两者相互叠加
SELECT ... FROM table1 CROSS JOIN table2 ...

//使用
SELECT name,height,age FROM Person CROSS JOIN TEACHER;


//内连接，按照条件合并
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...
SELECT ... FROM table1 NATURAL JOIN table2...

//使用
SELECT name,height FROM Person INNER JOIN TEACHER ON Person.id = TEACHER.id;

//外连接
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

//使用
SELECT name,height FROM Person LEFT OUTER JOIN TEACHER ON Person.id = TEACHER.id;

19.NULL

表示一个值的缺失，在字段中显示为一个空白的值。

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

20.别名

可以暂时把表或者列重命名，不会改变数据库表名。

//语法-表
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
//列
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];


SELECT C.id,C.name,C.age,D.height
FROM Person as C, TEACHER as D
 WHERE C.id = D.id;

SELECT C.id AS ID,C.name AS NAME,D.height AS HEIGHT
FROM Person AS C, TEACHER AS D
WHERE C.id = D.id;

21.触发器

当数据库发生指定事件时，触发器会被调用。

• 发生DELETE/INSERT/UPDATE时触发，或者一个、多个指定表的列发生更新时触发
• 只支持FOR EACH ROW触发
• 没提供WHEN子句，会对所有执行SQL语句触发
• BEFORE或AFTER关键字决定何时执行触发器
• 当触发器关联的表删除时，自动删除触发器
• 要修改的表必须存在同一数据库
• 一个特殊的SQL函数RAISE可以用于触发器程序抛出异常

//语法
CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

//使用 a触发器的标示符
CREATE TRIGGER a AFTER INSERT ON Person
BEGIN
INSERT INTO AUDIT(id,date) VALUES(new.id,datetime("now"));
END;


//列出触发器
SELECT name FROM sqlite_master WHERE type = "trigger";

//特定表的触发器
SELECT name FROM sqlite_master WHERE type = "trigger" AND tbl_name = "Person";

//删除触发器 audit_log是触发器标示符
DROP TRIGGER audit_log;

22.索引

一种特殊的查找表，用于加快数据的检索。
能够加快SELECT和WHERE子句的速度，但是会减慢UPDATE和INSERT的输入。

//基本语法
CREATE INDEX index_name ON table_name;

//单列索引
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);

//使用
CREATE INDEX id_id ON Person(id);

//查看表的索引
.indices Person

//查看数据库所有索引
SELECT * FROM sqlite_master WHERE type = "index";

//删除索引
DROP INDEX index_name;

不应该使用索引的情况：

• 表内容较小
• 大批量插入和更新
• 含有大量NULL值的列上
• 频繁操作的列上

23.ALERT

可以重命名表和增加新的列。

//语法-重命名
ALTER TABLE database_name.table_name RENAME TO new_table_name;
//语法-添加新的列
ALTER TABLE database_name.table_name ADD COLUMN column_def...;

//使用-重命名
ALTER TABLE TEACHER RENAME TO Teachers;
//使用-添加新的列
ALTER TABLE Teachers ADD COLUMN sex char(1);

24.视图

可以创建出一个视图，该视图和表一样，但是不能够执行DELETE/INSERT/UPDATE语句，但是可以添加触发器。

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

//使用-创建
CREATE VIEW PERSONVIEW AS SELECT * FROM Person;

//查询
 select * FROM PERSONVIEW;

//删除
drop view PERSONVIEW;

25.子查询

可以与SELECT/INSERT/UPDATE/DELETE一起使用，进一步限制要检索的数据。

• 子查询必须用括号括起来
• 子查询在SELECT中只能有一个列
• ORDER BY 不能用在子查询中
• 子查询返回多余一行，智能与多值运算符一起使用，如IN
• BETWEEN不能与子查询一起使用，但可以在子查询内使用

//语法-SELECT
SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])


//使用
SELECT * FROM Person WHERE id IN (SELECT id FROM Person WHERE id > 1);

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

//使用
INSERT INTO PersonT SELECT * FROM Person WHERE id IN (SELECT id FROM Person);

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


//语法-DELETE
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

26.自动递增

用于表中的字段自动的递增

//语法
CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

//使用
CREATE TABLE C (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL);

27.SQLite注入

用户通过输入，并将输入内容插入到 SQLite 数据库中，这个时候您就面临着一个被称为 SQL 注入的安全问题。注入通常在请求用户输入时发生，比如需要用户输入姓名，但用户却输入了一个 SQLite 语句，而这语句就会在不知不觉中在数据库上运行。

防治办法就是，插入前进行判断。

28.EXPLAIN

可用EXPLAIN来描述表的细节。

//语法
EXPLAIN [SQLite Query]
EXPLAIN  QUERY PLAN [SQLite Query]

29.日期和时间

函数	作用
date(timestring, modifier, modifier, ...)	以 YYYY-MM-DD 格式返回日期。
time(timestring, modifier, modifier, ...)	以 HH:MM:SS 格式返回时间。
datetime(timestring, modifier, modifier, ...)	以 YYYY-MM-DD HH:MM:SS 格式返回。
julianday(timestring, modifier, modifier, ...)	这将返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数
strftime(format, timestring, modifier, modifier, ...)	这将根据第一个参数指定的格式字符串返回格式化的日期。具体格式见下边讲解。

YYYY年
MM月
DD日
HH时
MM分
SS秒
SSS毫秒
now当前

30.常用函数

函数	作用
COUNT	计算数据库表中的行数
MAX	最大值
MIN	最小值
AVG	平均值
SUM	总和
RANDOM	随机数
ABS	绝对值
UPPER	大写
LOWER	小写
LENGTH	字符串长度
sqlite_version	返回SQLite版本