偶尔会使用sqlite数据库，不是经常用，但用的时候，总是想不全命令，所以写下这篇算是笔记吧！以后方便复习和使用sqlite数据库。
sqlite可视化相关软件参考地址：
SQLite可视化管理工具汇总(更新中)_Android&Java&C-CSDN博客_sqlite可视化管理工具
linux下安装sqlite3
- 在线安装：sudo apt-get install sqlite3
- 如果在编程时，错误提示找不到sqlite3.h文件，则需安装库文件：sudo apt-get install libsqlite3 - dev
- 安装库工具Sqlite database browser<以表格形式显示库，便于查看与操作>：sudo apt-get install sqlitebrowser
- 编程中编译需要连接库：-lsqliite3
.db与.sql相互转换
1、 .sql -> .db
//在终端输入“sqlite3 数据库名.db”进入sqlite3命令行；
//执行sql语句生成db文件：
sqlite> .read 数据库名.sql
sqlite> .exit2、 .db -> .sql
//在终端输入“sqlite3 数据库名.db”进入sqlite3命令行；
//执行sql语句生成sql文件：
sqlite> .output 数据库名.sql
sqlite> .dump //导出整个数据库
//sqlite> .dump tb1 //导处tb1表
sqlite> .exitsqlite3的命令
一、系统命令(以“.”开头的命令)
- .help //
- .quit/.exit //退出
- .schema //查看表的结构图
- .databass //查看打开的数据库
- .table //查看表
二、sqlite命令
（一）、创建或打开一个数据库
sqlite3 wangl.db //创建并打开wangl.db数据库（二）、创建数据库表
create table stu(nu integer,name char,score float);//创建stu表
create table stu(nu integer primary key,name char,score float);//将nu作为主键不能插入相同的数据（三）插入数据
- 插入所有数据
insert into stu values(1,"wangliang",99);- 部分插入
insert int stu(nu,score) values(2,45);
insert into stu(name) values("xiaoming");（四）、查询数据
- 查看表中的成员信息
select *from stu;- 部分查询
select nu from stu;
select nu,name from stu;
select *from stu where nu=1;
select *from stu where name="wangliang";//查询name="wangl"的数据- 条件查询
select nu,name from stu where score<60;
select *from stu where score>60 and nu<4;
select *from stu where score>60 or nu=2;- 顺序查找
SELECT *FROM stu order by name asc //如果是字符按字母顺序，如果是数值按大小(升序)
select * from stu where nu >=10 and nu<=15 order by nu desc//降序- like语句
LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，LIKE 运算符将返回真（true），也就是 1。这里有两个通配符与 LIKE 运算符一起使用：
- 百分号(%)：代表零个、一个或多个数字或字符。
- 下划线(_)：下划线（_）代表一个单一的数字或字符。
/**************************
1|xiaoming|99.0
2|xiaohong|97.0
3|xiaofang|93.0
4|xiaoxia|88.0
5|xiaowang|80.0
**************************/
select *from stu where name like 'xiao%';
select *from stu where name like '%g';
select *from stu where name like '%h%';
select *from stu where name like 'xiao_ing';（五）删除表
- 删除表
drop table stu;- 删除表的所有内容
delete from stu;- 删除某一行
delete from stu where nu=3;- 删除表的某一列
不支持直接删除一列，但可以间接删除
create table stu1 as select nu,name from stu;//赋值stu表中指定列到stu1表
drop table stu;//删除原表stu
alter table stu1 rename to stu;//将新表名改为原表名（六）修改表
- 修改表中的内容
update stu set name="xiaohong" where nu=1;- 移动表(当删除某行后，ID顺序被打乱，如1,2，4,5)
update stu set Id=Id-1 where Id>2（七）、增加表的内容
- 增加一列
alter table stu add QQ integer;
alter table stu add weixin text;（八）、查看数据库中是否包含某个表
sqlite会自动维护一个系统表sqlite_master，该表存储了我们所创建的各个table, view, trigger等等信息。
sqlite_master表结构如下：
CREATE TABLE sqlite_master (
type TEXT, //类型取值一般为table
name TEXT, //表名
tbl_name TEXT, //表名
rootpage INTEGER,
sql TEXT //创建表或者视图的sql语句，可以从该sql语句中判断某字段是否存在
);
查询某个表是否存在命令：
SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='查询的表名';//value(0).toBool()//第一个有值就有此表查询sqlite中所有表:
select name from sqlite_master where type='table' order by name;（九）、查看表中是否含有某列
SELECT sql from sqlite_master where name = '查询的表名';//value(0).toString().contains("查询的列名")，为真怎包含该列（十）、JION
SQLite的Join子句用于结合两个或多个数据库中表的记录。JOIN是一种通过共同值来结合两个表中字段的手段。
SQL定义了三种主要类型的连接：
交叉连接（CROSS JOIN）
交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 列，则结果表有 x+y 列。由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。
语法：
SELECT ... FROM table1 CROSS JOIN table2 ...;内连接（INNER JOIN）
内连接（INNER JOIN）根据连接谓词结合两个表（table1和table2）的列值来创建一个新的结果表。查询会把table1中的每一行与table2中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A和B行的每个匹配对的列值会合并成一个结果行。
内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的。
语法：
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...;为了避免冗余，并保持较短的措辞，可以使用USING表达式声明内连接（INNER JOIN）条件。这个表达式指定一个或多个列的列表：
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...;自然连接（NATURAL JOIN）类似于JOIN…USING，只是它会自动测试存在两个表中的每一列的值之间相等值：
SELECT ... FROM table1 NATURAL JOIN table2...;外连接（OUTER JOIN）
外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但SQLite只支持左外连接（LEFT OUTER JOIN）。外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING或NATURAL关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来，外连接的列使用NULL值，将它们附加到结果表中。
左外连接（LEFT OUTER JOIN）的语法：
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...;为了避免冗余，并保持较短的措辞，可以使用USING表达式声明外连接（OUTER JOIN）条件。这个表达式指定一个或多个列的列表：
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...;
下面将创建了三张表，如下图所示来讲解，JION的用法。
查询学生姓名，课程，成绩:
方法一(传统方式)：
select student.S_name,teacher.T_course,student.S_grade from student,teacher\
WHERE student.T_number=teacher.T_number; 
方法二(inner join)：
select student.S_name,teacher.T_course ,student.S_grade from\
student inner join teacher on student.T_number=teacher.T_number;可以去掉前缀(后面一样)：
select S_name,T_course ,S_grade from student inner join teacher\
on student.T_number=teacher.T_number;方法三(cross join)：
select S_name,T_course ,S_grade from student CROSS join teacher\
on student.T_number=teacher.T_number;方法四(left outer join)：
select S_name,T_course ,S_grade from student left outer join teacher\
on student.T_number=teacher.T_number;更改某个值(将张三语文成绩更改为100分)：
update student set S_grade=100 where S_name='张三' \
and student.T_number=(select T_number from teacher);
sqlite相关函数
一、打开sqlite数据库
int sqlite3_open(char *path,sqlite3 **db);
path --- 路径/库文件
db --- 指向sqlite句柄指针
返回值：0 --- 成功
失败返回错误码
ret = sqlite3_open("my.db", &db);
if (ret != SQLITE_OK)
{
fprintf(stderr, "open : %s\n", sqlite3_errmsg(db));
return -1;
}二、关闭sqlite数据库
int sqlite3_close(sqlite3 *db)
三、SQlite编程接口
（一）sqlite3_exec(）
int sqlite3_exec(sqlite3 *db,const char *sql,sqlite3_callback callback,void *,char **errmsg）
db --- 数据库句柄
sql --- SQL语句
callback --- 回调函数
errmsg --- 错误信息指针的地址
int callback(void *para, int f_num, char **f_value, char **f_name) //
{
int i;
for (i = 0; i < f_num; i++)
printf("%s : %s\n", f_name[i], f_value[i]);//f_name为数据库数据名、f_value为数据库数据值
return 0;
}para --- 传递给回调函数的参数
f_num --- 记录包含字段数(列数)
f_value --- 包含每个字段值得指针数组
f_name --- 包含每个字段名称的指针数组
（二）sqlite3_get_table()
int sqlite3_get_table(sqlite3 *db,const char *sql,char ***resultp,int *nrow,int *ncolumn,char **errmsg)
db --- 数据库句柄
sql --- SQL语句
nrow --- 行
ncolumn --- 列
errmsg --- 错误信息指针的地址
（三）程序实现
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlite3.h>
/*
* 何时调用callback函数：表中要要查询的数据的时候；
* 查询到有几条，函数调用几次；
*/
int callback(void *para, int f_num, char **f_value, char **f_name)
{
int i;
for (i = 0; i < f_num; i++)
printf("%s : %s\n", f_name[i], f_value[i]);
return 0;
}
int main()
{
int ret;
sqlite3 *db;
char *errmsg;
char sql[128];
/* 打开数据库文件 */
ret = sqlite3_open("wl.db", &db);
if (ret != SQLITE_OK) {
fprintf(stderr, "open : %s\n", sqlite3_errmsg(db));
return -1;
}
/* 创建表 */
sprintf(sql, "create table stu(nu Integer primary key, name char, score float)");
ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
if (ret != SQLITE_OK) {
fprintf(stderr, "create fail : %s\n", errmsg);
return -1;
}
/* 插入数据 */
int nu = 1;
char name[20] = "xiai";
float score = 89;
sprintf(sql, "insert into stu values(%d, '%s', %f)", nu, name, score);
ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
if (ret != SQLITE_OK) {
fprintf(stderr, "create fail : %s\n", errmsg);
return -1;
}
/* 修改数据 */
nu = 1;
strcpy(name, "xiaowang");
sprintf(sql, "update stu set name='%s' where nu='%d'", name, nu);
ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
if (ret != SQLITE_OK) {
fprintf(stderr, "create fail : %s\n", errmsg);
return -1;
}
/* 查询数据 */
#if 0
char test[20];
sprintf(sql, "select * from stu");
ret = sqlite3_exec(db, sql, callback, test, &errmsg);
if (ret != SQLITE_OK) {
fprintf(stderr, "create fail : %s\n", errmsg);
return -1;
}
#else
char **result;
int row;
int column;
sprintf(sql, "select * from stu");
ret = sqlite3_get_table(db, sql, &result, &row, &column, &errmsg);
if(ret != SQLITE_OK) {
fprintf(stderr, "select fail : %s\n", errmsg);
return -1;
}
printf("row = %d, column = %d\n", row, column);
int i;
int j;
result += column;
for (i = 0; i < row; i++) {
for (j = 0; j< column; j++) {
printf("%s ", *result++);
}
printf("\n");
}
#endif
sqlite3_close(db);
}sqlite存储数据类型
除了一些基本的数据类型，还可以存储下列类型：
- datetime - 日期时间型
- text - 字符文本型
- binary - 二进制数据类型
- image - 二进制数据类型(数据类型用来存储变长的二进制数据，最大可达231-1或大约20亿字节)
- BLOB - 二进制大对象(是一个可以存储二进制文件的容器。典型的BLOB是一张图片或一个声音文件，由于它们的尺寸，必须使用特殊的方式来处理（例如：上传、下载或者存放到一个数据库）)
- cursor - 特殊数据型(cursor 数据类型是一种特殊的数据类型，它包含一个对游标的引用。这种数据类型用在存储过程中，而且创建表时不能用)
- timestamp - 特殊数据型(timestamp 数据类型是一种特殊的数据类型，用来创建一个数据库范围内的唯一数码。 一个表中只能有一个timestamp列。每次插入或修改一行时，timestamp列的值都会改变。尽管它的名字中有“time”， 但timestamp列不是人们可识别的日期。在一个数据库里，timestamp值是唯一的)
- Uniqueidentifier - 特殊数据型(Uniqueidentifier数据类型用来存储一个全局唯一标识符，即GUID。GUID确实是全局唯一的。这个数几乎没有机会在另一个系统中被重建。可以使用NEWID 函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列)
扫一扫
2253
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
