sqlite3
原文链接： sqlite3
golang 在内存中打开
sql.Open("sqlite3", ":memory:")
DB.SetMaxOpenConns(1) // this resolved the "table not found" in memory error for me.
创建数据库
sqlite3 ./phalcon_test.sqlite < "schemas/sqlite/phalcon_test.sql"
打开或创建
sqlite3 ./phalcon_test.sqlite
格式化输出
.header on 显示表头 默认off
.mode list|column|insert|line|tabs|tcl|csv 改变输出格式
.timer on
.separator "X" 更改分界符号为X
显示数据库结构
显示表结构：
sqlite> .schema [table]
获取所有表和视图：
sqlite > .tables
获取指定表的索引列表：
sqlite > .indices [table ]
.databases
.tables
.schema #显示所有字段结构
.schema 表名 #显示指定字段结构
查询
.tables
.schema builds
delete from builds where build_id = 72;
select * from builds where build_id = 72; ## 修复drone
退出
.quit
.exit
导出数据库到 SQL 文件：
sqlite > .output [filename ]
sqlite > .dump
sqlite > .output stdout
从 SQL 文件导入数据库：
sqlite > .read [filename ]
格式化输出数据到 CSV 格式：
sqlite >.output [filename.csv ]
sqlite >.separator ,
sqlite > select * from test;
sqlite >.output stdout
从 CSV 文件导入数据到表中：
sqlite >create table newtable ( id integer primary key, value text );
sqlite >.import [filename.csv ] newtable
备份数据库：
/* usage: sqlite3 [database] .dump > [filename] /
sqlite3 mytable.db .dump > backup.sql
恢复数据库：
/ usage: sqlite3 [database ] < [filename ] */
sqlite3 mytable.db < backup.sql
Unique index
create [UNIQUE] INDEX [if not exists] schema.index_name ON table_name (column,) [where ]
#SQL 结构化的查询语言 Structured Query Language
###分类
- DDL 数据定义语言:
create 、drop 、 alter - DML：数据操作语言:
CURD：增删改查 - DCL：数据控制语言
- 数据查询语言（DQL:Data Query Language）
- 指针控制语言（CCL）
- 事务处理语言（TPL）
##SQLite常用语法（只记录SQLite独特的部分）
sqlite： 以.开头以;结尾
sqlite>.help //输出帮助信息
sqlite>.open xxx.db //创建数据库
sqlite>.header on //显示标题
sqlite>.mode column //设置显示模式(默认为list，设置为column，其他模式可通过.help查看mode相关内容)
退出sqlite终端命令：
sqlite>.quit
或
sqlite>.exit
sqlite>.show //列出当前显示格式的配置：
sqlite>.schema table_name //查看指定表的创建语句
sqlite>.schema //查看所有表的创建语句
sqlite>.database //查看数据库文件信息命令
sqlite>.dump table_name //以sql语句的形式列出表内容
sqlite>.separator symble //设置显示信息的分隔符
sqlite>.width width_value //设置每一列的显示宽度
在SQLite中需要注意：
设置外键约束之后要启用外键约束（启用之前插入的不合法的数据，是被允许的，所以要谨慎，这可能是一个bug）
启用外键语法：
pragma foreign_keys=on;
SQLite支持哪些数据类型
NULL 值为NULL
INTEGER 值为带符号的整型，根据类别用1，2，3，4，6，8字节存储
REAL 值为浮点型，8字节存储
TEXT 值为text字符串，使用数据库编码(UTF-8, UTF-16BE or UTF-16-LE)存储
BLOB 值为二进制数据，具体看实际输入
但实际上，sqlite3也接受如下的数据类型：
smallint 16 位元的整数
interger 32 位元的整数
decimal(p,s) p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值 ，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。
float 32位元的实数。
double 64位元的实数。
char(n) n 长度的字串，n不能超过 254。
varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。
graphic(n) 和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。
vargraphic(n) 可变长度且其最大长度为 n 的双字元字串，n不能超过 2000。
date 包含了 年份、月份、日期。
time 包含了 小时、分钟、秒。
timestamp 包含了 年、月、日、时、分、秒、千分之一秒。
实际上：SQLite是无类型的. 这意味着你可以保存任何类型的数据到你所想要保存的任何表的任何列中,
无论这列声明的数据类型是什么(只有自动递增Integer Primary Key才有用). 对于SQLite来说对字段不指定类型是完全有效的.
##SQL语法
###创建表以及增、删、改、查
create table 表名（
字段名 数据类型 说明 ,
... ... ... ,
... ... ...
）;
外键约束：
字段名 references 主表（主键）
示例：
create table user(
uid int primary key auto_increment, //主键自增
uname varchar(40) UNIQUE NOT NULL, //唯一且不为空
password varchar(40),
email varchar(60),
phone varchar(20) UNIQUE NOT NULL,
balance int(10) NOT NULL DEFAULT 999,//不不空且默认值是999
);
外键约束示例：
create table type(
tid Integer primary key autoincrement, //注意把autoincrement写到后面，放到前面可能会不起作用 并且只能用Integer类型（在SQLite中）
tname varchar(20)
);
create table goods(
id Integer primary key autoincrement,
name varchar(20) not null,
num int check(name>=0), //check约束
price int,
address varchar(50),
tid references type(tid) //外键约束：goods表中的tid字段依赖于type表中的tid字段
)
约束条件：
主键约束：primary key
外键约束：foreigh key
唯一约束：unique
检查约束：check（表达式）
默认约束：default(默认值)
非空约束：not null
增删改查语法：
C(Create 增加)：
insert into 表名 （字段名） values（值）[,(值),(值)]
R(Retrieve 查询)
select 字段名 from 表名
[where 条件]
[group by 分组条件]
[having 筛选条件]
[order by 排序字段[asc|desc]]
[limit n]
limit 2 前两位
limit 3,4 跳过3条后的再显示4条
U(Update 修改)
update 表名 set 字段=值[,字段=值] [where 条件]
D(Delete 删除)
delete from 表名[where 条件]
where 条件：>、 <、 = 、!= 、>= 、<= 、<> (注：不等于)
not and or
in(值，值，值)
between ... and ...
模糊查询：% ：任意多个字符_ ** : 任意一个字符**
###表链接
注：能用链接查询尽量不要用嵌套子查询（效率问题）
分类：
内连接：两张表中同时存在的记录
inner join //可以简写成 join
外连接：
左外连接：以A表作为主表，主表的信息会全部显示，如果B表有对应的数据显示数据，否则显示null
A left join B
右外连接：A right join B <=等价于=> B left join A //(sqlite不支持)
全连接：A full join B <=等价于=> 左外+右外 //(sqlite不支持)
语法：
select 字段
from 表名1 join 表名2
on 表1.键 = 表2.键
扩展：自链接（自己连接自己）
示例：
type表：
id tname pid
1 食品 0
2 饼干 1
3 饮料 1
4 可乐 3
5 可口可乐 4
6 百事 4
7 家电 0
8 冰箱 7
需要解决的问题
现要求查询显示如下：
编号 类别名称 父类名称
2 饼干 食品
3 饮料 食品
4 可乐 饮料
5 可口可乐 可乐
6 百事 可乐
8 冰箱 家电
解决（使用自链接）
查询语句如下：
select a.id as 编号 a.tname as 类别名称 ,b.tname as 父类名称
from type as a join type as b
on a.pid = b.id
###查询语句练习
现有如下表：
sqlite> select * from goods;
id name num address tid
---------- ---------- ---------- ---------- ----------
1 bb 20 beijing 1
2 asdf 40 beijing 2
3 nljksdff 40 shanghai 2
4 sdff 100 shanghai 1
sqlite> select * from type;
tid tname
---------- ----------
1 玩具
2 服装
3 饮料
查询：
//按照address分组，并显示各组的总数（条目）
sqlite> select address,count(*) from goods group by address;
address count(*)
---------- ----------
beijing 2
shanghai 2
//按照address分组，查询各地的产品数量和总量
sqlite> select address,count(*) as 产品数量,sum(num) as 总量 from goods g
address;
address 产品数量 总量
---------- ---------- ----------
beijing 2 60
shanghai 2 140
//按照address分组，查询各地的产品数量和总量，并筛选总量大于100的
sqlite> select address,count(*) as 产品数量,sum(num) as 总量 from goods g
address having sum(num)>100;
address 产品数量 总量
---------- ---------- ----------
shanghai 2 140
//按照产品数量降序排列，并取得前两条数据
sqlite> select * from goods order by num desc limit 2;
id name num address tid
---------- ---------- ---------- ---------- ----------
4 sdff 100 shanghai 1
2 asdf 40 beijing 2
//按照产品数量降序排列，并跳过结果集中的第一条数据，再显示两条数据
sqlite> select * from goods order by num desc limit 1,2;
id name num address tid
---------- ---------- ---------- ---------- ----------
2 asdf 40 beijing 2
3 nljksdff 40 shanghai 2
//查询商品地址在'beijing'或'shanghai'中的商品信息
sqlite> select * from goods where address in('beijing','shanghai');
id name num address tid
---------- ---------- ---------- ---------- ----------
1 bb 20 beijing 1
2 asdf 40 beijing 2
3 nljksdff 40 shanghai 2
4 sdff 100 shanghai 1
//查询商品表中商品数量最大的商品信息
sqlite> select * from goods where num = (select max(num) from goods);
id name num address tid
---------- ---------- ---------- ---------- ----------
4 sdff 100 shanghai 1
//查询goods表中name中包含f的所有商品类别信息
sqlite> select * from type where tid in(select tid from goods where name like %f%');
tid tname
---------- ----------
1 玩具
2 服装
//查询goods中不存在的商品类别信息
sqlite> select * from type where tid not in(select tid from goods);
tid tname
---------- ----------
3 饮料
//先按照tid对goods分组，计算各组的num的平均值，列出平均值小于100的tid
sqlite> select tid from goods group by tid having avg(num)<100;
tid
----------
1
2
//先按照tid对goods分组，计算各组的num的平均值，获取平均值小于100的tid并显示其类别信息
sqlite> select * from type where tid in (select tid from goods group by tid having avg(num)<100);
tid tname
---------- ----------
1 玩具
2 服装
//goods与type 内链接
sqlite> select type.*,goods.* from type join goods on type.tid = goods.tid;
tid tname id name num address tid
---------- ---------- ---------- ---------- ---------- ---------- -
--
1 玩具 1 bb 20 beijing 1
2 服装 2 asdf 40 beijing 2
2 服装 3 nljksdff 40 shanghai 2
1 玩具 4 sdff 100 shanghai 1
//goods与type 内链接
sqlite> select goods.*,tname from type join goods on type.tid = goods.tid;
id name num address tid tname
---------- ---------- ---------- ---------- ---------- ----------
1 bb 20 beijing 1 玩具
2 asdf 40 beijing 2 服装
3 nljksdff 40 shanghai 2 服装
4 sdff 100 shanghai 1 玩具
//type与goods 左链接
sqlite> select goods.*,tname from type left join goods on type.tid = goods.tid;
id name num address tid tname
---------- ---------- ---------- ---------- ---------- ----------
1 bb 20 beijing 1 玩具
4 sdff 100 shanghai 1 玩具
2 asdf 40 beijing 2 服装
3 nljksdff 40 shanghai 2 服装
饮料
//goods与type 左链接 相当于 type与goods 右链接
sqlite> select goods.*,tname from goods left join type on type.tid = goods.tid;
id name num address tid tname
---------- ---------- ---------- ---------- ---------- ----------
1 bb 20 beijing 1 玩具
2 asdf 40 beijing 2 服装
3 nljksdff 40 shanghai 2 服装
4 sdff 100 shanghai 1 玩具
//自链接（这个没啥意义）
sqlite> select a.*,b.* from type as a join type as b on a.tid = b.tid;
tid tname tid tname
---------- ---------- ---------- ----------
1 玩具 1 玩具
2 服装 2 服装
3 饮料 3 饮料
查询男女的人数
Select sex,count(*)
From student
Group by sex;
男同学的平均年龄比女同学的平均年龄大几岁？
select (select avg(age) from student where sex = '男')-(select avg(age)
from student where sex='女') as 最美年龄差;
查询和学号是2的同名的同学
Select * from student
Where name=(select name from student where id=2)
查询年龄第二大的女生
Select * from student where sex = ‘女’ and age=(select max(age) from student
Where age <(select max(age) from student)
)
查询年龄最大的同学
Select * from student order by age desc limit 1;
该sql语句不能把所有年龄最大的同学全部包含,如果最大的年龄的同学不唯一
Select * from student where age =(select max(age) from student)
Select * from stduent where age=(select age from student order by age desc limit 1)
查询年龄最大的男女同学
Select max(age) from student group by sex
男女最大年龄差
Select ((select max(age)from student where sex=’男’)-(select min(age) from student where sex=’女’)) as 最大年龄差