本文详细介绍了如何使用MySQL实现一个基本的图书管理系统，包括创建表、执行基本操作（如插入、更新、删除和查找）以及内存管理。通过提供一个实际的代码示例，旨在帮助学生和开发者理解在Linux环境下如何构建工程并进行数据库操作。
很早就想写这篇文章，一方面是想把自己的思路做一下总结，另一方面也是想通过这篇文章看看自己还有哪些方面有待加强。但是由于公司的一些项目原因，这个想法一直被搁置。直到今天，有了工作的空隙，终于将之流淌于笔下。
Linux强大的功能体现在网络服务器方面，说到服务器，那肯定离不开对数据的管理，那必然涉及到数据库。MySQL是Linux下功能强大的数据库应用软件。尽管在一些情况下MySQL还需要收费，但是在许多场合下还是免费的。在开源的世界里这无疑是令人鼓舞的，另一个分支是支持windows平台的SQL server,当然各家都有各家的优点，那么在这篇文章中出于对开源的支持，只讨论MySQL.
前段时间在高校实训课程中，让同学们写一个基于MySQL的图书管理系统。从做的效果上看不是很理想，抛开C语言能力不说，单是MySQL的用法，有很多同学就直接搬抄网上的一些接口。对接口所要完成的功能不甚了解。如果要实现最基本的图书管理功能，那么删除，插入，查找，更新这几种功能是一定要有的，在这几种功能之上，我们可以扩展出若干种其它的应用。那么实现这几种功能需要调用的系统API只需要几个就可以：mysql_init(), mysql_real_connect(), mysql_query(), mysql_use_result(), mysql_fetch_row(), mysql_free_result()等等。当然，如果想让程序更健壮，其它接口实现的功能也是必须的。在这里，我们需要完成的功能是基本的图书管理功能。
基于对内存的高效利用思想，程序中用到的大的数据块都是在堆上面分配的，那么为了避免内存出现泄漏和空洞，我们必须在调用结束用相应的方法释放之。以下是我自己写的代码, 在程序中，我实现了makefile的编写。当然，整个程序还不是很完善，有很多的分支没有做到扩展。但是整个程序己基本上实现了上述提到的功能。另外，实训中我也发现很多学生不知道怎么样去建立一个工程，他们把所有的代码都放到一个源文件中，在现代的项目开发中这样肯定是不行的。我把这样的一个demo程序贴出来目的就是为了帮助仍在迷茫中的同学，希望能给你们一个在linux下建立工程的思路。代码中关键的地方都有注释，如下：
bookinfo.c
/***********************************************************************************
*Copyright(C) 2011 xiabing <xiabing_326@163.com>.
*This program is free software, you can redistribute it and/or modify it under the
*terms of the GNU General Public License version 2 as published by the Free Software
*Foundation.
**********************************************************************************/
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include "mysql.h"
#include "global.h"
#define CREATE_BOOK_TABLE "create table book( \
bookno integer auto_increment not null primary key, \
bookname varchar(40), \
pagenu int, \
price float(5), \
author varchar(32), \
publisher varchar(52), \
debit varchar(20), \
de_date varchar(20), \
backer varchar(20), \
back_date varchar(20));"
#define DELETE_SAME_TABLE "drop table book;"
#define SHOW_BOOK_TABLE "select * from book;"
int main(int argc, char **argv)
{
MYSQL book_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;
int res;
char *cmd = "insert into book(bookname,pagenu,price,author,publisher,debit,de_date,backer,back_date) values(";
char *pHeapBook;
char *pCmd;
char yesorno = 'y';
char iudf;
BOOKINFO *sqlbookinfo;
BOOKINFO mysqlbookinfo;
sqlbookinfo = &mysqlbookinfo;
pHeapBook = (char *)malloc(1024);
pCmd = (char *)malloc(1024);
memset(pHeapBook,'\0',1024);
memset(pCmd,'\0',1024);
mysql_init(&book_connection);
if(mysql_real_connect(&book_connection,"localhost","dust","123456","book",0,NULL,0)) //return NULL if fail
{
printf("connection success\n");
//first,we should delete the previous table
res = mysql_query(&book_connection,DELETE_SAME_TABLE); //return null if success
if(res == 0) //success delete book table
{
printf("delete table book successfully.\n");
}
else //the book dose not exist....
{
printf("book table dose not exist, and delete table book fail.\n");
}
//after delete the former table, and then we create a new table
res = mysql_query(&book_connection,CREATE_BOOK_TABLE); //return null if success
if(res == 0)
{
printf("create table book successfully.\n");
}
else
{
printf("create table book fail.\n");
return 0;
}
//ok, now we should give our command
while(1)
{
printf("Please input the cmd you want to exec(i-insert,u-update,d-delete,f-find):");
iudf = getchar();
getchar();
//fflush(stdin);
switch(iudf)
{
case 'i'://insert
//here, we start to insert the info of a book...
InsertABook(sqlbookinfo, pHeapBook, cmd, book_connection);
printf("insert successfully.\n");
break;
case 'u': //update
memset(pCmd,'\0',1024);
printf("Please input the cmd to update:");
//scanf("%s",pCmd);
fgets(pCmd,1024,stdin);
memset(pCmd+strlen(pCmd)-1,'\0',strlen(pCmd));
UpdateABook(sqlbookinfo,pCmd,book_connection);
printf("update successfully.\n");
break;
case 'd': //delete
memset(pCmd,'\0',1024);
printf("Please input the cmd to delete:");
//scanf("%s",pCmd);
fgets(pCmd,1024,stdin);
memset(pCmd+strlen(pCmd)-1,'\0',strlen(pCmd));
DeleteABook(sqlbookinfo,pCmd,book_connection);
printf("delete successfully.\n");
break;
case 'f': //find
memset(pCmd,'\0',1024);
printf("Please input the condition you want to find:");
//scanf("%s",pCmd);
fgets(pCmd,1024,stdin);
memset(pCmd+strlen(pCmd)-1,'\0',strlen(pCmd));
FindABook(pCmd,book_connection);
printf("find successfully.\n");
break;
default:
break;
}
//getchar();
}
//release source
free(pHeapBook);
free(pCmd);
free(sqlbookinfo->bookname);
free(sqlbookinfo->pagenu);
free(sqlbookinfo->price);
free(sqlbookinfo->author);
free(sqlbookinfo->publisher);
free(sqlbookinfo->debit);
free(sqlbookinfo->de_date);
free(sqlbookinfo->backer);
free(sqlbookinfo->back_date);
mysql_close(&book_connection);
}
else
{
printf("connect fail.\n");
}
return 0;
}
func.c
/***********************************************************************************
*Copyright(C) 2011 xiabing <xiabing_326@163.com.com>.
*This program is free software, you can redistribute it and/or modify it under the
*terms of the GNU General Public License version 2 as published by the Free Software
*Foundation.
**********************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "global.h"
#include "mysql.h"
/***************************************************************************
*Function name:AllocSpaceForBook
*Parameters: BOOKINFO **pbookinfo - pointer of a pointer point to bookinfo
*Describes: This function is used to alloc spaces for the variables of a
book, and then initialize the space in the heap. And finally,
we get spaces in the heap and initialized.
*returns: pointer of a pointer initialized.
*Author:xiabing
＊Date:2011/06/29
**************************************************************************/
void AllocSpaceForBook(BOOKINFO **pbookinfo)
{
//alloc memory for the variables
(*pbookinfo)->bookname = (char *)malloc(40);
(*pbookinfo)->pagenu = (int *)malloc(sizeof(int));
(*pbookinfo)->price = (float *)malloc(sizeof(float));
(*pbookinfo)->author = (char *)malloc(32);
(*pbookinfo)->publisher = (char *)malloc(52);
(*pbookinfo)->debit = (char *)malloc(20);
(*pbookinfo)->de_date = (char *)malloc(20);
(*pbookinfo)->backer = (char *)malloc(20);
(*pbookinfo)->back_date = (char *)malloc(20);
//init variables
memset((char *)((*pbookinfo)->bookname),'\0',40);
memset((int *)((*pbookinfo)->pagenu),0,sizeof(int));
memset((float *)((*pbookinfo)->price),0,sizeof(float));
memset((char *)((*pbookinfo)->author),'\0',32);
memset((char *)((*pbookinfo)->publisher),'\0',52);
memset((char *)((*pbookinfo)->debit),'\0',20);
memset((char *)((*pbookinfo)->de_date),'\0',20);
memset((char *)((*pbookinfo)->backer),'\0',20);
memset((char *)((*pbookinfo)->back_date),'\0',20);
return;
}
/***************************************************************************
*Function name:GetAbookinfo
*Parameters: BOOKINFO *pAbookinfo - a pointer point to bookinfo
*Describes: This function is used to get a book info,and then the info of
* a book is stored in each of the spaces in the heap correspond
* to the variables.
*returns: a pointer initialized.
*Author:xiabing
*Date:2011/06/29
***************************************************************************/
void GetABookInfo(BOOKINFO *pAbookinfo)
{
AllocSpaceForBook(&pAbookinfo);
printf("Now,we start to insert a book info to our database.\n");
printf("Please input the info of a book.\n");
printf("bookname:");
scanf("%s",pAbookinfo->bookname);
getchar();
printf("page number:");
scanf("%d",pAbookinfo->pagenu);
printf("price:");
scanf("%f",pAbookinfo->price);
printf("author:");
scanf("%s",pAbookinfo->author);
printf("publisher:");
scanf("%s",pAbookinfo->publisher);
printf("debit:");
scanf("%s",pAbookinfo->debit);
printf("debit date:");
scanf("%s",pAbookinfo->de_date);
printf("backer:");
scanf("%s",pAbookinfo->backer);
printf("back date:");
scanf("%s",pAbookinfo->back_date);
return;
}
/***************************************************************************
*Function name:BookInfoRight
*Parameters: BOOKINFO *pAbookinfo - a pointer point to bookinfo
*Describes: This function is used to judge the contents of a book right or
* not.
*returns: boolean value, right for true, other wise,false.
*Author:xiabing
*Date:2011/06/29
***************************************************************************/
BOOL BookInfoRight(BOOKINFO *pbookinfo)
{
char yesorno;
printf("The contents of a book you want to insert is:\n");
printf("bookname pagenu price author publisher debit de_date backer back_date.\n");
printf("%s %d %f %s %s %s %s %s %s\n",pbookinfo->bookname,*(pbookinfo->pagenu),*(pbookinfo->price),pbookinfo->author, pbookinfo->publisher,pbookinfo->debit,pbookinfo->de_date,pbookinfo->backer,
pbookinfo->back_date);
printf("contents right?(y/n):");
getchar();
yesorno = getchar();
if(yesorno == 'y')
return true;
else
return false;
}
/***************************************************************************
*Function name:MergeCommand
*Parameters: BOOKINFO *pbookinfo - a pointer point to bookinfo
* char *str - result merge from the bookinfo
*Describes: This function is used to merge the command from the bookinfo
* we get.
*returns: str
*Author:xiabing
*Date:2011/06/30
***************************************************************************/
void MergeCommand(BOOKINFO *pbookinfo,char *str)
{
char buf[20];
int len;
memset(buf,'\0',sizeof(buf));//init buf
memset(str,'\'',sizeof(char));//set '
len = sizeof(char);
memcpy(str+len,pbookinfo->bookname,strlen(pbookinfo->bookname)); //set name
len += strlen(pbookinfo->bookname);
memset(str+len,'\'',sizeof(char)); //set '
len += sizeof(char);
memset(str+len,',',sizeof(char)); //set ,
sprintf(buf,"%d",*(pbookinfo->pagenu)); //int to char
len += sizeof(char);
memcpy(str+len,buf,strlen(buf)); //strcat pagenum
len += strlen(buf);
memset(str+len,',',sizeof(char)); //set ,
sprintf(buf,"%f",*(pbookinfo->price)); //float to char
len += sizeof(char);
memcpy(str+len,buf,strlen(buf));
len += strlen(buf);
memset(str+len,',',sizeof(char));//set ,
len += sizeof(char);
memset(str+len,'\'',sizeof(char));//set '
len += sizeof(char);
memcpy(str+len,pbookinfo->author,strlen(pbookinfo->author));//strcat author
len += strlen(pbookinfo->author);
memset(str+len,'\'',sizeof(char)); //set '
len += sizeof(char);
memset(str+len,',',sizeof(char)); //set ,
len += sizeof(char);
memset(str+len,'\'',sizeof(char)); //set '
len += sizeof(char);
memcpy(str+len,pbookinfo->publisher,strlen(pbookinfo->publisher)); //strcat publisher
len += strlen(pbookinfo->publisher);
memset(str+len,'\'',sizeof(char)); //set '
len += sizeof(char);
memset(str+len,',',sizeof(char)); //set ,
len += sizeof(char);
memset(str+len,'\'',sizeof(char)); //set '
len += sizeof(char);
memcpy(str+len,pbookinfo->debit,strlen(pbookinfo->debit)); //strcat debit
len += strlen(pbookinfo->debit);
memset(str+len,'\'',sizeof(char));
len += sizeof(char);
memset(str+len,',',sizeof(char)); //set ,
len += sizeof(char);
memset(str+len,'\'',sizeof(char)); //set '
len += sizeof(char);
memcpy(str+len,pbookinfo->de_date,strlen(pbookinfo->de_date)); //strcat de_date
len += strlen(pbookinfo->de_date);
memset(str+len,'\'',sizeof(char)); //set '
len += sizeof(char);
memset(str+len,',',sizeof(char)); //set ,
len += sizeof(char);
memset(str+len,'\'',sizeof(char)); //set '
len += sizeof(char);
memcpy(str+len,pbookinfo->backer,strlen(pbookinfo->backer)); //strcat backer
len += strlen(pbookinfo->backer);
memset(str+len,'\'',sizeof(char)); //set '
len += sizeof(char);
memset(str+len,',',sizeof(char)); //set ,
len += sizeof(char);
memset(str+len,'\'',sizeof(char)); //set '
len += sizeof(char);
memcpy(str+len,pbookinfo->back_date,strlen(pbookinfo->back_date)); //strcat de_date
len += strlen(pbookinfo->de_date);
memset(str+len,'\'',sizeof(char)); //set '
len += sizeof(char);
memset(str+len,')',sizeof(char));
len += sizeof(char);
memset(str+len,'\0',sizeof(char));
return;
}
/***************************************************************************
*Function name:InsertABook
*Parameters: BOOKINFO *sqlbookinfo - a pointer point to bookinfo
* char *heap - a heap malloc in the heap
* char *cmd - command in the query cmd
* MYSQL book_connection - connect init in the mysql_init
*Describes: This function is used to insert a bookinfo in the mysql table.
*returns: cmd
*Author:xiabing
*Date:2011/06/30
***************************************************************************/
void InsertABook(BOOKINFO *sqlbookinfo, char *heap, char *cmd,MYSQL book_connection)
{
char yesorno = 'y';
int res;
//here, we start to insert the info of a book...
AllocSpaceForBook(&sqlbookinfo);
//now, we are getting to insert a book into the database
printf("Now, we are getting to insert a book into the database.\n");
while(yesorno == 'y')
{
GetABookInfo(sqlbookinfo);
if(BookInfoRight(sqlbookinfo))
{
memcpy(heap,cmd,strlen(cmd));
MergeCommand(sqlbookinfo,heap+strlen(cmd));
printf("The command as this:%s\n",heap);
res = mysql_query(&book_connection,heap);
if(res == 0)
{
printf("book info insert successfully.\n");
}
else
{
printf("book info insert fail.\n");
return;
}
printf("Do you want to continue to insert book?(y/n):");
getchar();
yesorno = getchar();
if(yesorno == 'y')
continue;
else
break;
}
else //fail
{
printf("write again.\n");
}
}
return;
}
/***************************************************************************
*Function name:SendCmd
*Parameters: char *relatecmd - cmd sent by user
* MYSQL book_connection - connect init in the mysql_init
*Describes: This function is used to send cmd to the database.
*returns: true if success, otherwise,fail
*Author:xiabing
*Date:2011/07/01
***************************************************************************/
BOOL SendCmd(MYSQL book_connection,char *relatecmd, char *cmdresult)
{
int res;
char *cmd;
cmd = (char *)malloc(1024);
memset(cmd,'\0',1024);
//char *str = "select bookno,bookname,pagenu,price,author,publisher,debit,de_date,backer,back_date from book where ";
char *str = "select * from book where ";
//get the cmd to be sent
memcpy(cmd,str,strlen(str));
memcpy(cmd+strlen(str),relatecmd,strlen(relatecmd));
memset(cmd+strlen(str)+strlen(relatecmd),'\0',1);
memcpy(cmdresult,cmd,strlen(cmd));
//printf("sendcmd is:%s\n",cmd);
//res = mysql_query(&book_connection,cmd);
free(cmd);
return true;
}
/***************************************************************************
*Function name:FindABook
*Parameters: void *cmd - cmd sent by user
* MYSQL book_connection - connect init in the mysql_init
*Describes: This function is used to find a bookinfo in the mysql table.
*returns: none
*Author:xiabing
*Date:2011/07/01
***************************************************************************/
void FindABook(char *cmd,MYSQL book_connection)
{
int res;
char *rcmd;
BOOL bres = false;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;
rcmd = (char *)malloc(1024);
memset(rcmd,'\0',1024);
bres = SendCmd(book_connection,cmd,rcmd); //send cmd
printf("rcmd is:%s\n",rcmd);
res = mysql_query(&book_connection,rcmd);
if(!res) //success
{
res_ptr = mysql_use_result(&book_connection); //get result
if(res_ptr) //success
{
printf("bookno bookname pagenu price author publisher debit de_date backer back_date\n");
while((sqlrow = mysql_fetch_row(res_ptr)))
{
printf("%s %s %s %s %s %s %s %s %s %s\n",sqlrow[0],sqlrow[1],
sqlrow[2],sqlrow[3],sqlrow[4],sqlrow[5],sqlrow[6],sqlrow[7],sqlrow[8],sqlrow[9]);
}
mysql_free_result(res_ptr);
}
else //fail
{
fprintf(stderr,"connection failed.\n");
if(mysql_errno(&book_connection))
{
fprintf(stderr,"connection error %d:%s\n",mysql_errno(&book_connection),mysql_error(&book_connection));
}
}
}
else
{
printf("cmd fail.\n");
}
free(rcmd);
return;
}
/***************************************************************************
*Function name:UpdateABook
*Parameters: BOOKINFO *sqlbookinfo - a pointer point to bookinfo
* void *index - index to be searched
* void *contents - contents to be replaced
* MYSQL book_connection - connect init in the mysql_init
*Describes: This function is used to update a bookinfo in the mysql table.
*returns: true for success and otherwise false
*Author:xiabing
*Date:2011/07/01
***************************************************************************/
BOOL UpdateABook(BOOKINFO *sqlbookinfo,char *cmd,MYSQL book_connection)
{
int res;
int resf;
char *rcmd;
char *str = "update book ";
char *flush = "flush privileges";
rcmd = (char *)malloc(1024);
memset(rcmd,'\0',1024);
memcpy(rcmd,str,strlen(str));
memcpy(rcmd+strlen(str),cmd,strlen(cmd));
memset(rcmd+strlen(str)+strlen(cmd),'\0',1);
res = mysql_query(&book_connection,rcmd);
if(!res) //success
{
resf = mysql_query(&book_connection,flush);
if(!resf)
{
return true;
}
else
{
return false;
}
}
else //false
{
return false;
}
}
/***************************************************************************
*Function name:DeleteABook
*Parameters: BOOKINFO *sqlbookinfo - a pointer point to bookinfo
* void *index - index to be searched
* void *contents - contents to be replaced
* MYSQL book_connection - connect init in the mysql_init
*Describes: This function is used to delete a bookinfo from the mysql table.
*returns: true for success and otherwise false
*Author:xiabing
*Date:2011/07/01
***************************************************************************/
BOOL DeleteABook(BOOKINFO *sqlbookinfo,char *cmd,MYSQL book_connection)
{
int res;
int resf;
char *rcmd;
char *str = "delete from book where ";
char *flush = "flush privileges";
rcmd = (char *)malloc(1024);
memset(rcmd,'\0',1024);
memcpy(rcmd,str,strlen(str));
memcpy(rcmd+strlen(str),cmd,strlen(cmd));
memset(rcmd+strlen(str)+strlen(cmd),'\0',1);
res = mysql_query(&book_connection,rcmd);
if(!res) //success
{
resf = mysql_query(&book_connection,flush);
if(!resf)
{
return true;
}
else
{
return false;
}
}
else //false
{
return false;
}
}
global.h
/***********************************************************************************
*Copyright(C) 2011 xiabing <xiabing@chinasofti.com>.
*This program is free software, you can redistribute it and/or modify it under the
*terms of the GNU General Public License version 2 as published by the Free Software
*Foundation.
**********************************************************************************/
#ifndef __GLOBAL_H__
#define __GLOBAL_H__
//#defines
#define true 1
#define false 0
//typedefs
typedef unsigned char BOOL;
//structures
typedef struct tag_BOOKINFO
{
char *bookname;
int *pagenu;
float *price;
char *author;
char *publisher;
char *debit;
char *de_date;
char *backer;
char *back_date;
}BOOKINFO;
#endif
Makefile
objects=bookinfo.o func.o
booksql:$(objects)
gcc $(objects) -L /usr/lib/mysql -lmysqlclient -lz -o booksql
bookinfo.o:bookinfo.c /usr/include/mysql/mysql.h global.h
gcc -I /usr/include/mysql -c bookinfo.c
func.o:func.c global.h /usr/include/mysql/mysql.h
gcc -I /usr/include/mysql -c func.c
.PHONY:clean
clean:
-rm booksql $(objects)
扫一扫
498
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
