SQLite3基本操作
SQLite3基本操作
SQLite3
SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，而且已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至2019年已经有19个年头，SQLite也迎来了一个版本 SQLite 3已经发布。
数据类型
| 数据类型 | 含义 |
|---|---|
| NULL | 表示该值为NULL值 |
| INTEGER | 无符号整型值 |
| REAL | 浮点值 |
| TEXT | 文本字符串，存储使用的编码方式为UTF-8、UTF-16BE、UTF-16LE。 |
| BLOB | 存储Blob数据，该类型数据和输入数据完全相同，1表示true，0表示false。 |
约束
表的每一列都有一些限制属性，比如有的列的数据不能重复，有的则限制数据范围等，约束就是用来进一步描述每一列数据属性的。SQLite 数据库常用约束如下：
| 名称 | 定义 |
|---|---|
| not null | 非空 |
| unique | 唯一 |
| primary key | 主键 |
| foreign key | 外键 |
| check | 条件检查 |
| default | 默认 |
操作语句和命令
操作语句要加;
命令前要加.
安装
sudo apt-get install sqlite sqlite3//安装应用程序
sudo apt-get install libsqlite3-dev//安装库+头文件，用代码操作数据库必须安装
sudo apt-get install sqlitebrowser//图形化工具建立数据库
启动和退出
启动：命令行输入：sqlite3
退出：输入.exit
表
创建表：create table 表名(列名1 数据类型 约束, 列名2 数据类型 约束);
查看所有表：.tables
查看表的数据结构:.schema 表名
删除表:drop table 表名
修改表（增加列）：alter table 表名 add 列名 数据类型 内容;
重命名表：alter table 旧表名 rename to 新表名;
查看表中的数据：select * from 表名
插入数据：insert into 表名 values(数据);
更新数据：update 表名 set 列名1='' where 列名2='';
删除数据：delete from 表名 where 列名='';
.header on //显示列名
.mode column //列对齐
子句
order排序:
select * from employee order by age;
where:
SELECT column1, column2, columnN FROM table_name WHERE [condition]
//condition中可使用比较或逻辑运算符指定条件，比如 >、<、=、>=,<= ,LIKE、NOT，等等。
group by：
GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
select dep, sum(salary) from employee group by dep;
having:
having子句是group by的条件子句，where子句先发生，然后才是having 子句执行。HAVING子句中能够使用三种要素：常数，聚合函数，GROUP BY子句中指定的列名(聚合建)， 用having就一定要和group by连用， 用group by不一有having（它只是一个筛选条件用的）
select dep, sum(salary) from employee where id>3 group by dep having sum(salary)>5000;
//统计各部门的工资总和并且要求id值大于3
in
and
between
like
not in
order by
函数
命令行语句：
| 函数 | 描述 |
|---|---|
| count | 计算一个数据库表中的行数 |
| max | MAX 聚合函数允许我们选择某列的最大值。 |
| min | MIN 聚合函数允许我们选择某列的最小值 |
| avg | AVG 聚合函数计算某列的平均值 |
| sum | SUM 聚合函数允许为一个数值列计算总和 |
| random | 返回一个介于伪随机整数 |
| abs | ABS 函数返回数值参数的绝对值 |
| upper | 函数把字符串转换为大写字母 |
| lower | 函数把字符串转换为小写字母 |
| lengthLENGTH | 函数返回字符串的长度 |
| sqlite_version | 函数返回 SQLite 库的版本 |
执行函数：
//打开一个数据库，若该数据库不存在，则自动创建该数据库
//执行成功，返回SQLITE_OK（0），执行出错，返回其他错误信息（非0值）
int sqlite3_open(const char * dbname，sqlite3 **db)；
//关闭一个打开的数据库
//执行成功，返回SQLITE_OK(0)，执行出错，返回其他错误信息（非0值）
int sqlite3_close(sqlite3 *pDB)；
//函数的作用：获取最近调用的API接口返回的错误说明，并在下次调用任何sqlite API函数时自动清除
const char * sqlite3_errmsg(sqlite3 *pDB)；
//函数的作用：编译或者执行0个或多个sql语句，查询结果返回给回调函数
//执行成功，返回SQLITE_OK(0)，执行出错，返回错误信息
int sqlite3_exec(sqlite3 *db,const char * sql,int(*callback)(void *arg,int,char *,char **),void *arg,char **errmsg)；
/*
参数：
db:数据库的句柄；
sql:sql语句；
arg:用户传入的参数，可以为NULL，该参数指针最终会传入到回调函数callback中，供给用户在回调函数中使用；
callback:回调函数；
errmsg:返回的错误信息；
*/
//函数的作用：执行sql语句，通过一维数组返回结果，一般该函数用于数据记录查询
//执行成功，返回SQLITE_OK；执行出错，返回其他值；
int sqlite3_get_table(sqlite3*pDB,const char *sql,char ***pResult,int *rowcount,int * columncount,char **errmsg)；
/*
参数：
pDB:数据库的句柄
sql:SQLite3语句
pResult:用于保存查询到的结果
rowcount:结果集的行数
columncount:结果集的列数
errmsg:错误的信息
*/
like
它主要是针对字符型字段的，它的作用是在一个字符型字段列中检索包含对应子串的。
select * from 表名 where 字段名 like 对应值（子串），
A) %
包含零个或多个字符的任意字符串：
LIKE'Mc%' 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。
LIKE'%inger' 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。
LIKE'%en%' 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。
B) _（下划线）
任何单个字符：LIKE'_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。
C) [ ]
指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符：
LIKE'[CK]ars[eo]n' 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。
LIKE'[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。
D) [^]
不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符：LIKE'M[^c]%' 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）。
E）\(*\)
它同于DOS命令中的通配符，代表多个字符：c*c代表cc,cBc,cbc,cabdfec等多个字符。
F) ？
同于DOS命令中的？通配符，代表单个字符 :b?b代表brb,bFb等
G) #
大致同上，不同的是代只能代表单个数字。k#k代表k1k,k8k,k0k 。
F) [!]
排除 它只代表单个字符
参考资料：
https://mp.weixin.qq.com/s?__biz=MzUxMjEyNDgyNw==&mid=2247487776&idx=1&sn=6f41a0b12195e1a8c808a9d317dd132e&chksm=f96863d4ce1feac277bc0218ea9f7f65754b0e652c8543149df598f4c8ef87f3e1646de0de4c&token=1206659739&lang=zh_CN&scene=21#wechat_redirect
https://zhuanlan.zhihu.com/p/117433402
