- List item
SQLite简介:
SQLite是一款轻量级的开源的嵌入式数据库,由D.Richard Hipp在2000年发布。SQLite使用方便,性能出众,广泛应用于消费电子、医疗、工业控制、军事等各种领域。
原文
Linux平台上SQLite数据库教程(一)——终端使用篇
SQLite SQL语句
1、sqlite命令
创建或打开一个数据库:sqlite3 dbname.db(所在目录必须具有读写权限)
版本查看:sqlite3 -version
创建数据库:sqlite3 xxxx.db
退出sqlite命令行:.quit或.exit或.q
列出当前显示格式的配置:.show
查看数据库中的表:.tables
显示表结构:.schema
退出数据库:ctrl+d
.dump:生成整个数据库的脚本在终端显示
.output stdout:将输出打印到屏幕
.output filename:导出数据库到SQL文件
.read filename:从SQL文件导入数据库
.output filename.csv:格式化输出数据到CSV格式
.import [filename.csv ] newtable:从CSV文件导入数据到表中
sqlite3 [database] .dump > [filename]:备份数据库
实例:sqlite3 mytable.db .dump > backup.sql
sqlite3 [database ] < [filename ]:恢复数据库
实例:sqlite3 mytable.db < backup.sql
2、字段类型
NULL: 空值
INTEGER: 整数,依据值的大小可以依次被存储为1,2,3,4,5,6,7,8个字节
REAL: 所有值都是浮动的数值,被存储为8字节的IEEE浮动标记序号
TEXT: 文本,值为文本字符串,使用数据库编码存储(TUTF-8, UTF-16BE or UTF-16-LE).
BLOB: 值是BLOB数据,如何输入就如何存储,不改变格式
SQL语句中部分的带双引号或单引号的文字被定义为文本,如果文字没带引号并没有小数点或指数则被定义为整数,如果文字没带引号但有小数点或指数则被定义为实数,如果值是空则被定义为空值,BLOB数据使用符号X'ABCD'来标识。
smallint 16位的整数。
interger 32位的整数。
decimal(p,s) 精确值p是指全部有几个十进制数,s是指小数点后可以有几位小数。如果没有特别指定,则系统会默认为p=5 s=0 。
float 32位元的实数。
double 64位元的实数。
char(n) n 长度的字串,n不能超过 254。
varchar(n) 长度不固定且其最大长度为 n 的字串,n不能超过 4000。
graphic(n) 和 char(n) 一样,不过其单位是两个字节, n不能超过127。这个形态是为了支持两个字节长度的字体,如中文字。
vargraphic(n) 可变长度且其最大长度为n的双字元字串,n不能超过2000
date 包含了 年份、月份、日期。
time 包含了 小时、分钟、秒。
timestamp 包含了 年、月、日、时、分、秒、千分之一秒。
3、表操作
建立表
create table table_name(field type1,field type1,….);
table_name是要创建数据表的名称,field是数据库表内字段名字,type则是字段类型。例如:
CREATE TABLE student(
ID INTEGER PRIMARY KEY,
LastName varchar(255),
FirstName varchar(255)
);
删除表
DROP TABLE tableName;
查看表
SELECT * FROM tablename WHERE someField = ‘value’ COLLATE NOCASE;
4、字段操作
A、向表中添加新记录
insert into tabelname values (value1, value2,…);
实例:
insert into people values(1,‘A’,10);
insert into people values(2,‘B’,13);
insert into people values(3,‘C’,9);
insert into people values(4,‘C’,15);
insert into people values(5,NULL,NULL);
字符串要用单引号括起来
B、查询表中所有记录
select * from tablename ;
实例: select * from people;
按某个字段查询表中没有重复的条目
SELECT distinct someField FROM table
C、按指定条件查询表中记录
select * from tablename where field=value;
实例:
在表中搜索名字是A的项所有信息
select * from people where name=‘A’;
在表中搜索年龄>=10并且<=15的项的所有信息
select * from people where age>=10 and age<=15;
在表中搜索名字是C的项,显示其name和age
select name,age from people where name=‘C’;
显示表中的前2项所有信息
select * from people limit 2;
显示以年龄排序表中的信息
select * from people order by age;
D、按指定条件删除表中记录
delete from where
实例:
删除表中名字是’C’的项
delete from pople where name=‘C’;
E、更新表中记录
update set , … where ;
实例:
将表中年龄是15并且ID是4项,名字改为CYG
update people set name=‘CYG’ where id=4 and age=15;
用一张表TableB里的一个字段fieldB内容给另外一张表TableA里的一个字段fieldA赋值:
UPDATE TableA SET fieldA = TableB.fieldB
如果是同一张表TableA中,用一个字段field1的值给表中的另外一个字段field2值赋值:
UPDATE TableA SET field2 = field1
如果需要把一个字符串(‘someString’)和一个字段field1的值进行连接,然后赋值给一个字段field2:
UPDATE TableA SET field2 = field1 || ‘someString’
F、在表中添加字段
alter table add column ;
实例:
在people表中添加一个addr字段
alter table people add column addr;
G、删除表中的一个字段
删除people表中字段addr,操作流程如下:
将people表重命名为temp
重新创建people表
将temp表中的相应字段内容复制到people表中
删除temp表
SQL语句如下:
alter table people rename to temp;
create table people(id,name,age);
insert into people select id,name,age from temp;
drop table temp;
H、表的导入
把一张表TableA里的数据导入到另外一张表TableB中(两张表中的结构和字段必须一样):
INSERT INTO TableB SELECT * FROM TableA
5、分组统计
CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));
GROUP BY 进行分组统计数据,命令如下:
SELECT NAME, SUM(SALARY) SALARY_SUM, COUNT(1) COUNT_NUM FROM COMPANY GROUP BY NAME;
6、排序
ORDER BY 进行排序,命令如下:
SELECT NAME, SUM(SALARY) SALARY_SUM, COUNT(1) COUNT_NUM FROM COMPANY GROUP BY NAME ORDER BY SALARY_SUM ASC;
7、从excel表中导入数据
A、将Excel之中存储的数据另存为csv的格式bookroom.csv,注意不要带表头,只要数据就行
B、根据要导入的表属性创建表
create table bookroom(id integer, roomname nvarchar(20), mapfilename nvarchar(20));
C、设置数据的分隔符
.separator ‘,’;
D、将数据导入表
.import bookroom.csv bookroom
.import <输入文件名> <插入表名>
E、查阅插入的数据
select * from bookroom;
五、SQLite数据库编程
SQLite数据库编程最常用到的是sqlite3 *类型。从数据库打开开始,sqlite就要为sqlite3 *类型准备好内存,直到数据库关闭,整个过程都需要用到sqlite3 *类型。当数据库打开时开始,sqlite3 *类型的变量就代表了要操作的数据库,即句柄。
1、sqlite3_open
int sqlite3_open(char *path,sqlite3 **db);
功能:打开sqlite数据库
path:数据库文件路径(如果不存在,则创建)
db:指向sqlite句柄的指针
返回值:如果是SQLITE_OK则表示操作正常。其他的返回值参考sqlite3.h文件定义的宏。
2、sqlite3_close
int sqlite3_close(sqlite3 *db);
功能:关闭sqlite数据库
放回值:成功返回0,失败返回错误码
3、sqlite3_errmsg
const char *sqlite3_errmsg(sqlite3 *db);
返回值:返回错误信息
4、执行SQL语句
typedef int (*sqlite3_callback)(void *, int ,char **, char **);
int sqlite3_exec(sqlite3 *db , const char *sql ,
sqlite3_callback callback ,void *arg, char **errmsg );
db参数是前面sqlite3_open函数得到的指针
sql参数是一条字符串格式sql语句,以\0结尾。
callback参数是回调函数,当这条语句执行之后,sqlite3会去调用你提供的这个函数。
arg参数是可以传递的指针参数,传到回调函数里面,如果不需要传递指针给回调函数,可以填NULL。
errmsg 参数是错误信息。sqlite3里面有很多固定的错误信息。执行sqlite3_exec后,如果执行失败可以查阅这个指针。
返回值:成功返回0,失败返回错误码
如果调用sqlite3_exec失败,printf("%s\n",errmsg)可以得到一串字符串错误信息。
sqlite3_callback callback和void *arg都可以填NULL。NULL表示不需要回调。比如insert操作,delete操作,就没有必要使用回调。而当做select时,就要使用回调,因为sqlite3把数据查出来,得通过回调告诉你查出了什么数据。
typedef int (*sqlite3_callback)(void *para,int n_column,char **column_value,char **column_name);
para参数:传入的特殊指针(比如类指针、结构指针),然后操作指针指向的数据。
n_column:是记录有多少个字段(即记录有多少列)。
char **column_value:是个关键值,查出来的数据都保存在这里,它实际上可以看做是一个一维的指针数组,每个元素都是一个char *值,是一个字段的内容(用字符串来表示,以\0结尾)。
char **column_nam:跟column_value是对应的,表示这个字段的字段名称 。
程序实例sqlite3_callback.c:
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#define MAX 100
typedef int (*sqlite3_callback)(void *, int , char **, char **);
int show_table_info(void arg, int n_column, char column_value, char column_name)
{
int i = 0;
for(i = 0; i < n_column; i++)
{
printf("%s\t", column_name[i]);
}
printf("\n********************************\n");
for(i = 0; i < n_column; i++)
{
printf("%s\t", column_value[i]);
}
printf("\n\n");
return 0;
}
int exec_sql_string(char *sql_string, sqlite3 *db)
{
char *errmsg;
if(sqlite3_exec(db, sql_string, show_table_info, NULL, &errmsg) != 0)
{
fprintf(stderr, “Fail to exec sql(%s) : %s.\n”, sql_string,errmsg);
return -1;
}
return 0;
}
int main(int argc,char *argv[])
{
sqlite3 *db;
int result;
char sql_buf[MAX];
if(argc < 2)
{
fprintf(stderr, "usage : %s argv[1].\n", argv[0]);
exit(EXIT_FAILURE);
}
result = sqlite3_open(argv[1], &db);
if(result != SQLITE_OK)
{
fprintf(stderr, "Fail to sqlite3_open %s : %s.\n", argv[1], sqlite3_errmsg(db));
exit(EXIT_FAILURE);
}
while(1)
{
printf("sqlite >");
fgets(sql_buf, sizeof(sql_buf), stdin);
sql_buf[strlen(sql_buf) - 1] = '\0';
if(strncmp(sql_buf, ".quit", 5) == 0)
break;
exec_sql_string(sql_buf,db);
}
result = sqlite3_close(db);
if(result != 0)
{
fprintf(stderr, "Fail to sqlite3_open %s : %s.\n", argv[1], sqlite3_errmsg(db));
exit(EXIT_FAILURE);
}
exit(EXIT_SUCCESS);
}
编译:gcc -o sqlite3_callback sqlite3_callback.c -lsqlite3
5、不使用回调函数执行SQL语句
int sqlite3_get_table(sqlite3 *db, const char *sql, char ***resultp,
int *nrow, int *ncolumn,char **errmsg);
功能:执行sql操作
db : 数据库句柄
sql : sql语句
resultp : 用来指向sql执行结果的指针
nrow : 满足条件的记录的数目
ncolumn : 每条记录包含的字段数目
从第0索引到第ncolumn-1索引都是字段的名称
从第ncolumn索引开始,后面都是字段的值
errmsg : 错误信息指针的地址
返回值:成功返回0,失败返回错误码
程序实例sqlite3_nocallback.c:
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#define MAX 100
int exec_sql_string(char *sql_string, sqlite3 *db)
{
char *errmsg, **dbResult;
int nRow, nColumn;
int result, i, j, index;
result = sqlite3_get_table(db, sql_string, &dbResult, &nRow, &nColumn, &errmsg);
if(0 != result)
{
fprintf(stderr, "Fail to exec sql(%s) : %s.\n", sql_string, errmsg);
return -1;
}
//字段名字
for(j = 0; j < nColumn; j++)
{
printf("%s\t", dbResult[j]);
}
printf("\n");
index = nColumn;//从它开始是字段对应的值
for(i = 0; i < nRow; i++)//查询到总共记录个数
{
for(j = 0; j < nColumn; j++)
{
printf("%s\t", dbResult[index]);
index++;
}
printf("\n");
}
//释放查询结果所分配的内存
sqlite3_free_table(dbResult);
return 0;
}
int main(int argc, char *argv[])
{
sqlite3 *db;
int result;
char sql_buf[MAX];
if(argc < 2)
{
fprintf(stderr, “usage : %s argv[1].\n”, argv[0]);
exit(EXIT_FAILURE);
}
result = sqlite3_open(argv[1], &db);
if(result != SQLITE_OK)
{
fprintf(stderr, “Fail to sqlite3_open %s : %s.\n”, argv[1], sqlite3_errmsg(db));
exit(EXIT_FAILURE);
}
while(1)
{
printf(“sqlite >”);
fgets(sql_buf, sizeof(sql_buf), stdin);
sql_buf[strlen(sql_buf) - 1] = ‘\0’;
if(strncmp(sql_buf,".quit",5) == 0)
break;
exec_sql_string(sql_buf, db);
}
result = sqlite3_close(db);
if(result != 0)
{
fprintf(stderr, "Fail to sqlite3_open %s : %s.\n", argv[1], sqlite3_errmsg(db));
exit(EXIT_FAILURE);
}
exit(EXIT_SUCCESS);
}
编译: gcc -o sqlite3_nocallback sqlite3_nocallback.c -lsqlite3