数据库
数据库基本概念
数据(Data)
能够输入计算机并能被计算机程序识别和处理的信息集合
数据库 (Database)
数据库是在数据库管理系统管理和控制之下,存放在存储介质上的数据集合
常用的数据库
大型数据库
Oracle公司是最早开发关系数据库的厂商之一,其产品支持最广泛的操作系统平台。目前Oracle关系数据库产品的市场占有率名列前茅。
IBM 的DB2是第一个具备网上功能的多媒体关系数据库管理系统,支持包Linux在内的一系列平台。
中型数据库
Server是微软开发的数据库产品,主要支持windows平台。
小型数据库
mySQL是一个小型关系型数据库管理系统,开发者为瑞典MySQL AB公司,2008年被Sun公司收购,开放源码。
数据库的安装
本地安装: sudo dpkg -i *.deb
在线安装:sudo apt-get install-sqlite3
查看安装是否成功: sqlite3
退出: .quit
基于嵌入式的数据库
基于嵌入式Linux的数据库主要有SQLite, Firebird, Berkeley DB, eXtremeDB
Firebird是关系型数据库,功能强大,支持存储过程、SQL兼容等
SQLite关系型数据库,体积小,支持ACID事务
Berkeley DB中并没有数据库服务器的概念,它的程序库直接链接到应用程序中
eXtremeDB是内存数据库,运行效率高
SQLite 基础
SQLite的源代码是C,其源代码完全开放。SQLite第一个Alpha版本诞生于2000年5月。 他是一个轻量级的嵌入式数据库。
SQLite有以下特性:
零配置,无需安装和管理配置;
储存在单一磁盘文件中的一个完整的数据库;
数据库文件可以在不同字节顺序的机器间自由共享;
支持数据库大小至2TB;
足够小,全部源码大致3万行c代码,250KB;
比目前流行的大多数数据库对数据的操作要快;
创建数据库
手工创建
使用SQLite3工具,通过手工输入SQL命令行完成数据库创建.
用户在Linux的命令行界面中输入SQLite3可启动SQLite3工具
代码创建
在代码中常动态创建数据库
在程序运行过程中,当需要进行数据库操作时,应用程序会首先尝试打开数据库,此时如果数据库并不存在,程序则会自动建立数据库,然后再打开数据库
数据库常用命令介绍
系统命令, 都以’.'开头
sqlite3 my.db 打开指定数据库_my.db
.exit
.quit
.table 查看表
.schema 查看表的结构
sql语句, 都以‘;’结尾
1-- 创建一张表
create table stuinfo(id integer, name text, age integer, score float);
2-- 插入一条记录
insert into stuinfo values(1001, 'zhangsan', 18, 80);
insert into stuinfo (id, name, score) values(1002, 'lisi', 90);
3-- 查看数据库记录
select * from stuinfo;
select * from stuinfo where score = 80;
select * from stuinfo where score = 80 and name= 'zhangsan';
select * from stuinfo where score = 80 or name='wangwu';
select name,score from stuinfo; 查询指定的字段
select * from stuinfo where score >= 85 and score < 90;
4-- 删除一条记录
delete from stuinfo where id=1003 and name='zhangsan';
5-- 更新一条记录
update stuinfo set age=20 where id=1003;
update stuinfo set age=30, score = 82 where id=1003;
6-- 删除一张表
drop table stuinfo;
7-- 增加一列
alter table stuinfo add column sex char;
8-- 删除一列
create table stu as select id, name, score from stuinfo;
drop table stuinfo;
alter table stu rename to stuinfo;
数据库设置主键:
create table info(id integer primary key autoincrement, name vchar);
在终端下运行sqlite3 <.db>,出现如下提示符
SQLite version 3.7.2
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite>
<.db> 是要打开的数据库文件。若该文件不存在,则自动创建
显示所有命令
sqlite> .help
退出sqlite3
sqlite>.quit
显示当前打开的数据库文件
sqlite>.database
显示数据库中所有表名
sqlite>.tables
查询表中所有记录
sqlite>select * from <table_name>;
按指定条件查询表中记录
sqlite>select * from <table_name> where ;
向表中添加新记录
sqlite>insert into <table_name> values (value1, value2,…);
按指定条件删除表中记录
sqlite>delete from <table_name> where
更新表中记录
sqlite>update <table_name> set <f1=value1>, <f2=value2>… where ;
在表中添加字段
sqlite>alter table
在表中删除字段
Sqlite中不允许删除字段,可以通过下面步骤达到同样的效果
sqlite> create table stu as select no, name, score from student
sqlite> drop table student
sqlite> alter table stu rename to student
SQLite编程接口
int sqlite3_open(char *path, sqlite3 **db);
功能:打开sqlite数据库
path:数据库文件路径
db:指向sqlite句柄的指针
返回值:成功返回0 SQLITE_OK,失败返回错误码(非零值)
int sqlite3_close(sqlite3 *db);
功能:关闭sqlite数据库
返回值:成功返回0 SQLITE_OK,失败返回错误码
const char *sqlite3_errmg(sqlite3 *db);
返回值:返回错误信息
int sqlite3_exec(
sqlite3* db, /* An open database /
const char sql, / SQL to be evaluated /
int (callback)(void arg,int,char,char**), /* Callback function /
void * arg, / 1st argument to callback */
char *errmsg / Error msg written here */
);
功能:执行一条sql语句
参数:db 数据库句柄
sql sql语句
callback 回调函数,只有在查询时,才传参
arg 为回调函数传递参数
errmsg 错误消息
返回值:成功 SQLITE_OK
查询回调函数:
int (callback)(void arg,int ncolumns ,char** f_value,char** f_name), /* Callback function */
功能:查询语句执行之后,会回调此函数
参数:arg 接收sqlite3_exec 传递来的参数
ncolumns 列数
f_value 列的值得地址
f_name 列的名称
返回值:0,
不使用回调函数执行SQL语句
int sqlite3_get_table(sqlite3 *db, const char *sql, char **resultp, intnrow, int *ncolumn, char **errmsg);
功能:执行SQL操作
db:数据库句柄
sql:SQL语句
resultp:用来指向sql执行结果的指针
nrow:满足条件的记录的数目
ncolumn:每条记录包含的字段数目
errmsg:错误信息指针的地址
返回值:成功返回0,失败返回错误码
Typedef int (*sqlite3_callback)(void *, int,
char **, char **);
Int sqlite3_exec(sqlite3 *db, const char *sql, sqlite3_callback callback, void *, char **errmsg);
功能:执行SQL操作
db:数据库句柄
sql:SQL语句
callback:回调函数
errmsg:错误信息指针的地址
返回值:成功返回0,失败返回错误码
typedef int (*sqlite3_callback)(void *para, int f_num, char **f_value, char **f_name);
功能:每找到一条记录自动执行一次回调函数
para:传递给回调函数的参数
f_num:记录中包含的字段数目
f_value:包含每个字段值的指针数组
f_name:包含每个字段名称的指针数组
返回值:成功返回0,失败返回-1
代码示范1(学生信息管理demo)
stu.c
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#define DATABASE "student.db"
#define N 128
int do_insert(sqlite3 *db)
{
int id;
char name[32] = {};
char sex;
int score;
char sql[N] = {};
char *errmsg;
printf("Input id:");
scanf("%d", &id);
printf("Input name:");
scanf("%s", name);
getchar();
printf("Input sex:");
scanf("%c", &sex);
printf("Input score:");
scanf("%d", &score);
sprintf(sql, "insert into stu values(%d, '%s', '%c', %d)", id, name, sex, score);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
printf("Insert done.\n");
}
return 0;
}
int do_delete(sqlite3 *db)
{
int id;
char sql[N] = {};
char *errmsg;
printf("Input id:");
scanf("%d", &id);
sprintf(sql, "delete from stu where id = %d", id);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
printf("Delete done.\n");
}
return 0;
}
int do_update(sqlite3 *db)
{
int id;
char sql[N] = {};
char name[32] = "zhangsan";
char *errmsg;
printf("Input id:");
scanf("%d", &id);
sprintf(sql, "update stu set name='%s' where id=%d", name,id);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
printf("update done.\n");
}
return 0;
}
int callback(void *arg, int f_num, char ** f_value, char ** f_name)
{
int i = 0;
for(i = 0; i < f_num; i++)
{
// printf("%-8s %s", f_value[i], f_name[i]);
printf("%-8s", f_value[i]);
}
printf("++++++++++++++++++++++");
putchar(10);
return 0;
}
int do_query(sqlite3 *db)
{
char *errmsg;
char sql[N] = "select count(*) from stu where name='zhangsan';";
if(sqlite3_exec(db, sql, callback,NULL , &errmsg) != SQLITE_OK)
{
printf("%s", errmsg);
}
else
{
printf("select done.\n");
}
}
int do_query1(sqlite3 *db)
{
char *errmsg;
char ** resultp;
int nrow;
int ncolumn;
if(sqlite3_get_table(db, "select * from stu", &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
return -1;
}
else
{
printf("query done.\n");
}
int i = 0;
int j = 0;
int index = ncolumn;
for(j = 0; j < ncolumn; j++)
{
printf("%-10s ", resultp[j]);
}
putchar(10);
for(i = 0; i < nrow; i++)
{
for(j = 0; j < ncolumn; j++)
{
printf("%-10s ", resultp[index++]);
}
putchar(10);
}
return 0;
}
int main(int argc, const char *argv[])
{
sqlite3 *db;
char *errmsg;
int n;
if(sqlite3_open(DATABASE, &db) != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(db));
return -1;
}
else
{
printf("open DATABASE success.\n");
}
if(sqlite3_exec(db, "create table if not exists stu(id int, name char , sex char , score int);",
NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
printf("Create or open table success.\n");
}
while(1)
{
printf("********************************************\n");
printf("1: insert 2:query 3:delete 4:update 5:quit\n");
printf("********************************************\n");
printf("Please select:");
scanf("%d", &n);
switch(n)
{
case 1:
do_insert(db);
break;
case 2:
do_query(db);
// do_query1(db);
break;
case 3:
do_delete(db);
break;
case 4:
do_update(db);
break;
case 5:
printf("main exit.\n");
sqlite3_close(db);
exit(0);
break;
default :
printf("Invalid data n.\n");
}
}
return 0;
}
注意:运行程序需要链接数据库库文件
gcc stu.c -lsqlite3
代码示范2(在线词典管理)
create table
CREATE TABLE record(name text,data text,word text);
CREATE TABLE usr(name char PRIMARY KEY,pass integer);
词典文件:
text 文件
net.h
#include <stdio.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#include <string.h>
#include <unistd.h>
#include <sqlite3.h>
#include <signal.h>
#include <time.h>
#define N 32
#define SERVERPORT 5001
#define SERVERIP "127.0.0.1"
#define BACKLOG 5
#define DATABASE "my.db"
#define R 1 //register
#define L 2 //login
#define Q 3 //query
#define H 4 //history
//define msg struct
typedef struct{
int type; //msg type
int flag; //is or not administrator
char name[N];
char data[256];//msg data
}MSG;
client.c
#include "net.h"
int do_register(int socketfd,MSG *msg){
msg->type = R;
printf("Input name:");
scanf("%s",msg->name);
getchar();
printf("Input passwd:");
scanf("%s",msg->data);
if(send(socketfd,msg,sizeof(MSG),0)<0){
printf("fail to send\n");
return -1;
}
if(recv(socketfd,msg,sizeof(MSG),0)<0){
printf("Fail to recv.\n");
return -1;
}
//printf("%s\n",msg->data);
return 0;
}
int do_login(int socketfd,MSG *msg){
msg->type = L;
msg->flag = 0;
int result;
printf("Input name:");
scanf("%s",msg->name);
//check usr wthere administrator or not
result = strncmp(msg->name,"root",4);
if(((result ==0)&&(msg->name[4]=='\0'))){
msg->flag=1;
}
//printf("msg->flag:%d\n",msg->flag);
getchar();
printf("Input passwd:");
scanf("%s",msg->data);
if(send(socketfd,msg,sizeof(MSG),0)<0){
printf("fail to send\n");
return -1;
}
if(recv(socketfd,msg,sizeof(MSG),0)<0){
printf("Fail to recv.\n");
return -1;
}
if(strncmp(msg->data,"OK",3)==0){
printf("Login ok!\n");
return 1;
}else{
printf("%s\n",msg->data);
}
return 0;
}
int do_query(int socketfd,MSG *msg){
msg->type = Q;
puts("----------------");
while(1){
printf("Input query word :");
scanf("%s",msg->data);
getchar();
//'#' Return to upper level
if(strncmp(msg->data,"#",1)==0)
break;
//send server query word
if(send(socketfd,msg,sizeof(MSG),0)<0){
printf("send");
return -1;
}
//wait server return MSG
if(recv(socketfd,msg,sizeof(MSG),0)<0){
printf("recv");
return -1;
}
printf("%s\n",msg->data);
}
return 0;
}
int do_history(int socketfd,MSG *msg){
msg->type = H;
//send server query word
if(send(socketfd,msg,sizeof(MSG),0)<0){
printf("send");
return -1;
}
//recv server return
while(1){
recv(socketfd,msg,sizeof(MSG),0);
if(msg->data[0]=='\0')
break;
//Printf history record
printf("%s\n",msg->data);
}
return 0;
}
int main(int argc, char *argv[]){
int fd;
struct sockaddr_in serveraddr;
int n;
MSG msg;
//check argument
if(argc != 3){
printf("Usage:%s serverip port.\n",argv[0]);
return -1;
}
//socket
if((fd = socket(AF_INET,SOCK_STREAM,0))<0){
perror("socket");
return -1;
}
//argument filling
bzero(&serveraddr,sizeof(serveraddr));
serveraddr.sin_family = AF_INET;
serveraddr.sin_addr.s_addr = inet_addr(argv[1]);
serveraddr.sin_port = htons(atoi(argv[2]));
//connetc
if(connect(fd,(struct sockaddr*)&serveraddr,sizeof(serveraddr))<0){
perror("connetc");
return -1;
}
//index
while(1){
printf("*****************************************************************\n");
printf("* 1.register 2.login 3.quit *\n");
printf("*****************************************************************\n");
printf("Please choose:");
scanf("%d",&n);
while(getchar()!='\n');
//options
switch(n){
case 1:
do_register(fd,&msg);
break;
case 2:
if(do_login(fd,&msg)==1){
goto next;
}
break;
case 3:
close(fd);
exit(0);
break;
default:
printf("Invalid data cmd.\n");
}
}
next:
while(1){
printf("*****************************************************\n");
printf("* 1.query_word 2.history_record 3.quit *\n");
printf("*****************************************************\n");
printf("Please choose:");
scanf("%d", &n);
while(getchar()!='\n');
switch(n){
case 1:
do_query(fd,&msg);
break;
case 2:
do_history(fd,&msg);
break;
case 3:
close(fd);
exit(0);
break;
default:
printf("Invalid data cmd.\n");
}
}
return 0;
}
server.c
#include "net.h"
int do_client(int acceptfd,sqlite3 *db);
void do_register(int acceptfd,MSG *msg,sqlite3 *db);
int do_login(int acceptfd,MSG *msg,sqlite3 *db);
int do_query(int acceptfd,MSG *msg,sqlite3 *db);
int do_history(int acceptfd,MSG *msg,sqlite3 *db);
int history_callback(void* arg,int f_num,char** f_value,char** f_name);
int do_searchword(int acceptfd, MSG *msg, char word[]);
int get_date(char *date);
int main(int argc, char *argv[])
{
int fd;
struct sockaddr_in serveraddr;
// int n;
// MSG msg;
int pid;
int acceptfd;
sqlite3 *db;
//open DATABASE
if(sqlite3_open(DATABASE,&db) != SQLITE_OK){
printf("%s\n",sqlite3_errmsg(db));
return -1;
}else{
printf("open DATABASE success.\n");
}
//socket
if((fd = socket(AF_INET,SOCK_STREAM,0))<0){
perror("socket");
return -1;
}
//set addr Fast reuse
int b_reuse;
setsockopt(fd,SOL_SOCKET,SO_REUSEADDR,&b_reuse,sizeof(int));
//argument filling
bzero(&serveraddr,sizeof(serveraddr));
serveraddr.sin_family = AF_INET;
serveraddr.sin_addr.s_addr = inet_addr(SERVERIP);
serveraddr.sin_port = htons(SERVERPORT);
//bind
if(bind(fd,(struct sockaddr*)&serveraddr,sizeof(serveraddr))<0){
perror("bind");
return -1;
}
//listen
if(listen(fd,BACKLOG)<0){
perror("listen");
return -1;
}
//handle Zombie process
signal(SIGCHLD,SIG_IGN);
//blocking
while(1){
if((acceptfd = accept(fd,NULL,NULL))<0){
perror("accept");
return -1;
}
if((pid = fork())<0){
perror("fork");
return -1;
}else if(!pid){
//handle
close(fd);
do_client(acceptfd,db);
break;
}else{
close(acceptfd);
}
}
return 0;
}
int do_client(int acceptfd,sqlite3 *db){
MSG msg;
while(recv(acceptfd,&msg,sizeof(msg),0)>0){
// printf("type:%d\n",msg.type);
switch(msg.type){
case R:
do_register(acceptfd,&msg,db);
break;
case L:
do_login(acceptfd,&msg,db);
break;
case Q:
do_query(acceptfd,&msg,db);
break;
case H:
do_history(acceptfd,&msg,db);
break;
default:
printf("Invalid data msg.\n");
}
}
printf("client exit.\n");
close(acceptfd);
exit(0);
return 0;
}
void do_register(int acceptfd,MSG *msg,sqlite3 *db){
char *errmsg;
char sql[128];
sprintf(sql,"insert into usr values('%s',%s);",msg->name,msg->data);
printf("%s\n",sql);
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
printf("%s\n",errmsg);
strcpy(msg->data,"usr name already exist.");
}else{
printf("client register ok\n");
strcpy(msg->data,"OK!");
}
if(send(acceptfd,msg,sizeof(MSG),0)<0){
perror("fail to send");
return;
}
return;
}
int do_login(int acceptfd,MSG *msg,sqlite3 *db){
char *errmsg;
char sql[128];
int nrow;
int ncloumn;
char **resultp;
sprintf(sql,"select * from usr where name='%s'and pass='%s';",msg->name,msg->data);
printf("%s\n",sql);
if(sqlite3_get_table(db,sql,&resultp,&nrow,&ncloumn,&errmsg)!=SQLITE_OK){
printf("%s\n",errmsg);
return -1;
}else{
printf("get_table ok\n");
}
//query success
if(nrow ==1){
strcpy(msg->data,"OK");
}
if(nrow == 0){
strcpy(msg->data,"usr/passwd wrong.");
}
printf("%s*********\n",msg->data);
if(send(acceptfd,msg,sizeof(MSG),0)<0){
perror("fail to send");
return -1;
}
return 0;
}
int do_serchword(int acceptfd,MSG *msg,char word[]){
FILE *fp;
int len = 0;
char temp[512] = {};
int result;
char *p;
//Open file ,read,compare;
if((fp=fopen("dict.txt","r"))==NULL){
perror("fopen");
strcpy(msg->data,"Failed to open dict.txt");
send(acceptfd,msg,sizeof(MSG),0);
return -1;
}
//Printf,client query word
len = strlen(word);
printf("%s,len=%d\n",word,len);
//read file,query word
while(fgets(temp,513,fp)!=NULL){
printf("temp:%s\n",temp);
result = strncmp(temp,word,len);
if(result <0){
continue;
}else if(result >0 || ((result ==0)&&(temp[len]!=' '))){
break;
}
p = temp+len;
printf("found word:%s\n",p);
while(*p==' '){
p++;
}
//Ignore Spaces
strcpy(msg->data,p);
printf("found word:%s\n",msg->data);
//close FILE
fclose(fp);
return 1;
}
fclose(fp);
return 0;
}
int get_date(char *date){
time_t t;
struct tm *tp;
time(&t);
//time form transformation
tp = localtime(&t);
sprintf(date,"%d-%d-%d %d:%d:%d",tp->tm_year+1900,tp->tm_mon+1,tp->tm_mday,tp->tm_hour,tp->tm_min,tp->tm_sec);
// printf("get date:%s\n",date);
return 0;
}
int do_query(int acceptfd,MSG *msg,sqlite3 *db){
char word[64];
int found = 0;
char date[128];
char sql[128];
char *errmsg;
//take query word
strcpy(word,msg->data);
found = do_serchword(acceptfd,msg,word);
printf("Query finsh.\n");
//Query success
if(found==1){
//get sys time
get_date(date);
sprintf(sql,"insert into record values('%s','%s','%s')",msg->name,date,word);
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
printf("%s\n",errmsg);
return -1;
}else{
printf("Insert record done.\n");
}
}else{
strcpy(msg->data,"Not found!");
}
//return result to client
send(acceptfd,msg,sizeof(MSG),0);
return 0;
}
//Query history_sent to client
int history_callback(void* arg,int f_num,char **f_value,char **f_name){
//record name date word
int acceptfd;
MSG msg;
acceptfd = *((int*)arg);
sprintf(msg.data,"%s %s %s",f_value[0],f_value[1],f_value[2]);
send(acceptfd,&msg,sizeof(MSG),0);
return 0;
}
int do_history(int acceptfd,MSG *msg,sqlite3 *db){
char sql[128]={};
char *errmsg;
//check usr wthere administrator or not
if(msg->flag==0){
sprintf(sql,"select * from record where name='%s'",msg->name);
}else{
sprintf(sql,"select * from record");
}
//Query DATABASE
if(sqlite3_exec(db,sql,history_callback,(void*)&acceptfd,&errmsg)!=SQLITE_OK){
printf("%s\n",errmsg);
}else{
printf("Query record done.\n");
}
//send over msg to client
msg->data[0]='\0';
send(acceptfd,msg,sizeof(MSG),0);
return 0;
}