sqlite基础学习

1. 前言

• 本文资料来源网络公开资源，并根据个人实践见解纯手打整理，如有错误请随时指出。
• 本文主要用于个人积累及分享，文中可能引用其他技术大牛文章（仅引用链接不转载），如有侵权请告知必妥善处理。
• 参考资料和引用链接：Android基础教程-SQLite高级操作-视频课程

---

2. 学习内容

主要说明sqlite工具的基础使用（windows环境）和sqlite基本知识和常用语法使用两大块内容

---

2.1. windows下sqlite工具使用

工具下载地址，点击Downloadan按钮，之后进入如下下载页面：

---

2.1.1. 调用sqlite3工具

找到sqlite工具zip解压后的sqlite3.exe，打开cmd，输入如下（路径请替换）：

F:\do\self\sqlite-base-doc\sqlite-tools-win32-x86-3180000>sqlite3

通用命令帮助，这个可以简单了解下，随用随查:

sqlite>.help

如下图：

---

2.1.2. 创建数据库

sqlite>.open test01.db

或，在未调用sqlite3命令时，直接调用

F:\do\self\sqlite-base-doc\sqlite-tools-win32-x86-3180000>sqlite3 test02.db

---

2.1.3. 数据库常用命令

命令	说明
.databases	查询数据库，数据库的名称和文件
.tables	查询表，所有表的名称
.schema ‘tablename’	查询表的完整信息
.quit或.exit	退出SQLite

更多：http://www.runoob.com/sqlite/sqlite-commands.html

---

2.2. sqlite基本知识

2.2.1关于sqlite

sqLite支持大部分SQL92规范，但也有未实现的SQL特性，请见链接：
http://www.sqlite.org/omitted.html

---

2.2.2常用方法

create

关键字	名称	说明
Table	建表	…
View	建view	view只读，不能对数据或状态进行改动
Index	索引	…
Trigger	触发器	只支持行触发器，语句触发器不支持

约束条件

均为建表时添加，增删改查等操作时起约束作用

• not null
  非空

• unique
  唯一

• primary key
  主键，直观就是_id

• check
  插入时对某些值进行检查，比如某整型字段age，约束该字段的值必须大约或小于10

• default
  这个字段约定插入时未赋值时的默认值

测试代码：

sqlite> create table t3 (id int primary key not null, name, idcard not null unique, age default 18 check(age>=18));

---

insert select delete update

增删改查

---

drop

删除表、view等

---

alter

除了重命名表和在已有的表中添加列，alter table 命令不支持其他操作

sqlite> alter table test01 rename to test010;

sqlite> alter table test010 add column alias char(20);

---

attach

附着数据库。sqlite是单文件的，所有的表都在一个库文件里。
数据库较大时，需要将数据库分成多个数据库，提高速度，这几个数据库是attach在一起的。
例如：

xxx>sqlite3 test01.db

sqlite>.databases

此时可以看到有“main”数据库 xxx\xxx\test01.db，如下图：

然后，将test02.db attach 到 test01.db上：

sqlite>attach database 'test02.db' as '02';

再查看databases，可以看到如下已attach成功：

---

detach

sqlite>detach database '02';

---

group by

查询数据时，根据某一列中数据相同的进行分组

如，t3表中age列

select * from t3 group by age;

假设表中有2人年龄22，2人年龄18，这是结果会显示22岁的2人中后插入的那个人和18岁的2人中后插入的那个人来那个条数据。不太好举例，用的不多。

比如计算某个月的航班费用总和之类的情况可能会用到

select sum(flight_fee) from table_xx group by month;

---

having

在group by某列后，进行条件限制的条件语句，如

...group by age having age>20;

---

distinct

某列可能有重复的值，使用distinct去除重复值

如筛选出非重复的用户名字：

select distinct name from tableuser；

---

order by

排序 asc升序，desc降序

select * from t3 order by age asc;

---

limit

限制输出数据条数

输出3条：

select * from t3 limit 3;

按年龄升序，从第0位置数据开始提取3条：

select * from t3 order by age asc limit 0,3;

或

select * from t3 order by age asc limit 3 offset 0;

---

join , on

• 交叉连接 - cross join
• 内连接 - inner join
• 外连接 - outer join

cross join

两张表交叉连接，表1和表2每行相匹配，最终输出结果的数据条数是两个表行数相乘，列数相加。生成数据量可能相当大，使用需谨慎，用到的情况但也比较少，不做过多学习。

inner join

常用，默认join方式，inner关键字可省略
根据on条件检出符合条件的两表数据

outer join

是inner join的扩展模式，但sqlite只支持left outer join（不支持right、full），且left关键字不可省略。

与 inner join的显著异同：

1. 表1 left outer join 表2 ，当表1中的数据根据on条件对应到表2，表1中有不符合条件的数据时，查询结果仍会显示表1中多余的数据；

2. 表2 left outer join 表1 ，当表2中的数据根据on条件对应到表1，表1中有不符合条件的数据时，查询结果将不会显示表1中多余数据，此时和inner join查询结果相同；

inner join和outer join可用的表达式

• 表达式on
  常用条件声明

• 表达式using

  使用某一个两表中共有的字段，当这两个字段中的值相同是，才会被捡出

  此表达式有一定使用价值，在一定条件下可以简化语句。

• 表达式nature

  自动检测两表中共有的字段，当共有字段的值相同是，才会被捡出

  此表达式较局限。

示例和代码

http://www.runoob.com/sqlite/sqlite-joins.html

---

like ， GLOB

• like大小写不敏感，GLOB大小写敏感，且GLOB关键字必须大写
• like通配符为%和_，GLOB通配符为*和?
• % 和 * 应用类似， _ 和 ? 应用类似

示例和代码

http://www.runoob.com/sqlite/sqlite-like-clause.html

http://www.runoob.com/sqlite/sqlite-glob-clause.html

---

unions

合并多个select语句的结果，不返回任何重复的行，如：两段select语句中间加上unions关键字即可。

---

null， is not null， is null

• null 空，无值；不是’null’字符串，也不是空格或零；可以这样使用，如：

  ...update table set name=null where id=12;

• is not null 作为运算符，如：

  ...where name is not null;

• is null 作为运算符，同is not null用法

---

其它运算符

可参看：http://www.runoob.com/sqlite/sqlite-operators.html

---

函数

可参看：http://www.runoob.com/sqlite/sqlite-functions.html

---

子查询

子查询是嵌套在另一个语句的where语句中的，也就是说不只是可以用在select，insert、update、delete均可用
一般跟在运算符如 =、<、>、>=、<=、IN后
如：

...where age in (select age from agetable where age > 30);

---

事务transaction

sqlite数据库是ACID数据库，是事务完全的，在事务过程可以当保证数据的正确性。

命令	说明
begin transaction	在语句之前
commit \ end transaction	在语句之后
rollback	commit \ end transaction执行前有效