SQLite数据库

       偶尔会使用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> .exit

2、 .db -> .sql

//在终端输入“sqlite3 数据库名.db”进入sqlite3命令行;
//执行sql语句生成sql文件:
    sqlite> .output 数据库名.sql
    sqlite> .dump    //导出整个数据库
    //sqlite> .dump tb1 //导处tb1表
    sqlite> .exit

sqlite3的命令 

  一、系统命令(以“.”开头的命令)

  1. .help  //
  2. .quit/.exit //退出
  3. .schema  //查看表的结构图
  4. .databass  //查看打开的数据库
  5. .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. 百分号(%):代表零个、一个或多个数字或字符。
  2. 下划线(_):下划线(_)代表一个单一的数字或字符。
/**************************
    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存储数据类型

除了一些基本的数据类型,还可以存储下列类型:

  1. datetime - 日期时间型
  2. text  -  字符文本型
  3. binary -  二进制数据类型
  4. image - 二进制数据类型(数据类型用来存储变长的二进制数据,最大可达231-1或大约20亿字节)
  5. BLOB -  二进制大对象(是一个可以存储二进制文件的容器。典型的BLOB是一张图片或一个声音文件,由于它们的尺寸,必须使用特殊的方式来处理(例如:上传、下载或者存放到一个数据库))
  6. cursor - 特殊数据型(cursor 数据类型是一种特殊的数据类型,它包含一个对游标的引用。这种数据类型用在存储过程中,而且创建表时不能用)
  7. timestamp - 特殊数据型(timestamp 数据类型是一种特殊的数据类型,用来创建一个数据库范围内的唯一数码。 一个表中只能有一个timestamp列。每次插入或修改一行时,timestamp列的值都会改变。尽管它的名字中有“time”, 但timestamp列不是人们可识别的日期。在一个数据库里,timestamp值是唯一的)
  8. Uniqueidentifier - 特殊数据型(Uniqueidentifier数据类型用来存储一个全局唯一标识符,即GUID。GUID确实是全局唯一的。这个数几乎没有机会在另一个系统中被重建。可以使用NEWID 函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列)

  • 2
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值