SQLite数据库

  1. 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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值