SQLite中的SQL

常规语法

字符串常量建议使用单引号，虽然支持双引号；
二进制数据使用（x‘0fff’）这样的十六进制形式；
注释建议使用（-- This is comment），虽然支持C段注释（/* This is comment */）;
SQL关键字不区分大小写；

DDL(Data Definition Language)，数据定义语言

表的创建

 create [temp] table table_name (column_definitions [,constraints]);
 create [temp] table table_name as （select 子查询）


如：
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
);
在上述命令中，创建了一个名为 users 的表，包括四个列：id、name、age 和 email。其中，id 列是主键，name 列是文本类型且不能为空，age 列是整数类型，email 列是文本类型且唯一。

CREATE TABLE users_copy AS
	SELECT id, name, age, email
	FROM users
	WHERE age > 18;
在上述命令中，创建了一个名为 users_copy 的表，使用 SELECT 子句从 users 表中查询符合条件的数据，并将查询结果插入到新表中。新表包括四个列：id、name、age 和 email，与原表相同。

其中temp或者temporary表示创建临时表，constraints是表约束条件，比如unique(name，phone)表示唯一，其中的column_definitions包括字段的类型和列字段约束条件，比如not null，default ‘UNKNOW’,collate nocase。

表的修改

alter table table_name rename to new_name; /* 修改表名字 */
alter table table_name add column column_def;/* 添加列 */


如：
ALTER TABLE users RENAME TO customers;
将名为 users 的表重命名为 customers。

ALTER TABLE customers ADD COLUMN phone TEXT;
向名为 customers 的表中添加了一个名为 phone 的文本类型列。

ALTER TABLE customers MODIFY COLUMN age INTEGER NOT NULL;
将名为 customers 的表中的 age 列的数据类型修改为整数类型，并设置为不能为空。

DML(Data Manipulation Language)，数据操作语言

查询记录

select [distinct] heading(列名)
		from tables_name where '行限定条件'
		group by columns having '组限定条件'
		order by limit count , offset count

如：
select * from sqlite_master;
查询数据库中所有的表信息；
select name from sqlite_master where type='table'
查询数据库中所有的表名；
select count(*) from subdev where id=xxx;
查询id为目标的xxx的记录的个数；
select count(*) from pragma_table_info("subdev") where name="deviceId";
查询subdev表中名为deviceId的列个数；

SELECT DISTINCT name, age
		FROM users
		WHERE age > 18
		GROUP BY age
		HAVING count(*) > 1
		ORDER BY age DESC, name ASC
		LIMIT 10 OFFSET 20;


SELECT DISTINCT name, age：查询 users 表中 name 和 age 两列的数据，并去重
FROM users：指定查询的表为 users
WHERE age > 18：限定查询条件，只查询年龄大于 18 岁的用户数据
GROUP BY age：按照年龄进行分组
HAVING count(*) > 1：限定分组条件，只查询分组后数量大于 1 的数据
ORDER BY age DESC, name ASC：按照年龄降序排列，年龄相同的用户按照姓名升序排列
LIMIT 10 OFFSET 20：限定查询结果的数量，取前 10 条数据，跳过前 20 条数据

过滤顺序：
from tables_name -> where (行过滤条件) ->group ->having(组过滤条件) ->distinct(去重复) ->select heading(列过滤条件) ->order ->limit，offset
多表连接
内连接 通过条件找到两个表匹配的行；
交叉连接 简单的将两个表各行进行组合；
左外连接、右外连接、全外连接 类似内连接，只是左连接会保留左表中没匹配的行
自然连接 系统自动根据同名列进行匹配，容易有歧义，不推荐使用
尽管SQL允许隐式连接：

select * from foods food_types where foods.id=food_type.food_id;

显示连接的语法：

select heading from left_table jointype right_table on join_condition;

例如：

select * from foods inner join food_types on foods.id=food_types.id;
select * from foods left outer join food_types on foods.id=food_types.id;
select * from foods cross join food_types;

聚合
聚合函数包括sum()、avg()、count()、min()、max()等，需要注意，使用这些函数的时候，需要对数据进行group分组或者使用where进行条件约束，隐式分组。

别名
可以在from后的表名指定别名，在别的地方方便引用；或者在select后面也可以指定列的别名，这样输出的时候header将使用别名，as关键词可以省略，建议还是带上：

select f.name, t.name
from foods f ,food_types t
where f.type_id = t.id;

子查询
使用in关键词连接让一个查询作为另外一个查询的输入；
也可以使用子查询作为order by的条件：

select * from foods f
order by (select count(type_id) from foods where type.id=f.type_id) desc;

复合查询
union 联合查询结果，删除重复行
intersect 找到查询结果的交集
except 找到查询结果的差集，A except B;在A中找到B中不存在的行。

条件结果

select name, (select
				case
					when count(*) >4 then 'Very High'
					when count(*) =4 then 'High'
					when count(*) in (2,3) then  'Moderate'
					else 'Low')
				end
			from foods_episodes where food_id = f.id) frequency
from foods f
where frequency like '%High%';

插入记录

insert into table_name(column_list) values(value_list);

更新记录

update table_name set update_list where predicate;

删除记录

delete from table_name where predicate;

数据完整性

1.唯一性约束：unique(a,b)
2.主键约束：primary key(a,b) autoincrement(阻止主键回收)
3.默认值：default ‘UNKNOW’/current_time,current_date,current_timestamp
4.not null 约束：no null
5.check约束：check(length(phone)>7)
6.存储约束：null，text，real，integer，blob
7.外键约束：

create table foods(
	id integer primary key,
	type_id integer references food_types(id)
	on delete(update) restrict
	defeerable initially deffered(immediate),
	name text);

restrict表示不允许删除父值
set null表示删除父值之后子值改为null
set default 表示设置为默认值
cascade 删除父值之后，删除所有关联子值，可能产生不可预料结果，不推荐
no action 不执行任何操作，观察报错

视图

动态查询表，SQLite不支持view的修改、插入，可以配合触发器完成。

create view view_name as (select 查询子句)
drop view view_name

索引

为了加速查询而生成的临时表

create [unique] index on table(a,b) 

其中unique进行唯一过滤

触发器

create trigger on_update_foods_view
[insert/delete/update] on table_name
（SQL执行脚本）

对于产生可变更的view，在[insert/delete/update]前添加instead of即可

事务

一个begin…commit/rollback为一个事务，默认每一个sql就是一个事务结构，执行成功将自动提交。

savepoint point_name;
rollback to point_name;

事务的冲突解决有：replace,ignore,fail,abort,rollback
默认处理方式为abort，可以在操作关键词后面加or + 解决方法修改
select or fail * from foods；

死锁问题

如上所述，每一个SQL语句都是一条隐式的事务。
对于需要加快数据库操作速度，或者追求原子性的操作，可以显式定义事务，事务分为三种DEFERRED 、IMMEDIATE 和EXCLUSIVE,
事务取消了sql语句的自动提交，在事务结束时再根据执行结果，让用户选择COMMIT或者ROLLBACK:

begin immediate/exclusive/deffered
......
commit/rollback

在自定义事务的时候，sql语句本身需要根据对db的不同操作，获取不同的锁，这些锁包括按锁的级别依次是：UNLOCKED /SHARED /RESERVERD /PENDING /EXCLUSIVE，如select需要获取SHARED锁，update需要RESERVERD 锁，commit需要获取EXCLUSIVE 锁。
因为一个事务中存在锁升级的情况，获取需要考虑事务中锁升级导致的死锁问题。

经验总结：
如果在性能允许的前提下，可以约定使用begin immediate或者begin exclusive来避免死锁，或者只用单线程。

详细可以参考

https://www.cnblogs.com/Gaimo/p/16098038.html
https://blog.51cto.com/u_15878482/5858225