C的MySql简单图书馆
MySql中的表
create table book_information(
ISBN varchar(30) primary key,
book_name varchar(50) not null,
author varchar(20) not null,
publisher varchar(20) not null,
pub_date date not null,
book_price double not null,
book_id int(4) not null,
book_status BOOLEAN not null
);
insert into book_information values(
20180120,'q','q','q','1999.10.10',10,1,1
);
console的图书馆
#define _CRT_SECURE_NO_WARNINGS
#include <WinSock2.h> /*socket通信,系统头文件,必须放在第一个*/
#include <Windows.h>
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include "mysql.h"
#include "zlog.h"
#define MAX_LENGTH 200
#define HOST "localhost"
#define USERNAME "root"
#define PASSWORD "root"
#define DATABASE "db1"
#define BOOKTABLE "book_information"
MYSQL* mysql = NULL;
MYSQL_RES *res_ptr = NULL;
MYSQL_FIELD *field = NULL;
MYSQL_ROW result_row;
zlog_category_t *zc = NULL;
void setConsole();
void showAll();
void addBook();
void modifyBook();
void deleteBook();
void queryBook();
void showMenu();
int inquire();
void insertBookIntoDB();
void log_init();
void log_finish();
int main(){
int num;
setConsole();
log_init();
mysql = mysql_init(NULL);
if (mysql == NULL){
zlog_info(zc,"mysql_init failed\n");
zlog_fini();
exit(1);
}
if (NULL == mysql_real_connect(mysql, HOST, USERNAME, PASSWORD, DATABASE, 0, NULL, 0)){
zlog_info(zc, "mysql_real_connect failed\n");
zlog_fini();
exit(1);
}
showMenu();
scanf("%d", &num);
fflush(stdin);
while (num){
switch (num){
case 1:
showAll();
break;
case 2:
addBook();
break;
case 3:
modifyBook();
break;
case 4:
deleteBook();
break;
case 5:
queryBook();
break;
case 6:
printf("\t 欢迎再次访问图书馆!\n");
Sleep(100);
exit(0);
break;
default:
break;
}
if (inquire()){
scanf("%d", &num);
fflush(stdin);
}
else{
break;
}
}
mysql_close(mysql);
log_finish();
return 0;
}
void setConsole(){
SetConsoleTitle(L"xiaolixi library");
system("mode con cols=100 lines=30");
HANDLE hOut = GetStdHandle(STD_OUTPUT_HANDLE);
WORD att = FOREGROUND_RED | FOREGROUND_GREEN |
FOREGROUND_INTENSITY | BACKGROUND_BLUE;
SetConsoleTextAttribute(hOut, att);
}
void showAll(){
const char* query_str = "select * from " BOOKTABLE;
my_ulonglong row;
unsigned int column;
int i;
MYSQL_FIELD* field;
MYSQL_ROW result_row;
if (0 != mysql_real_query(mysql, query_str, strlen(query_str))){
zlog_info(zc, "mysql_real_query failed\n");
}
else{
res_ptr = mysql_store_result(mysql);
if (res_ptr){
row = mysql_num_rows(res_ptr);
column = mysql_num_fields(res_ptr);
printf("\t检索到%10d行 %10d列\n", row, column);
while (field = mysql_fetch_field(res_ptr)){
printf("%-10s", field->name);
}
printf("\n");
while (result_row = mysql_fetch_row(res_ptr)){
for (i = 0; i < column; ++i){
printf("%-10s", result_row[i]);
}
printf("\n");
}
printf("\n");
mysql_free_result(res_ptr);
}
}
}
void addBook(){
char ch;
do{
insertBookIntoDB();
printf("\ncontinue add book(y/n)");
scanf("%c", &ch);
fflush(stdin);
} while (ch == 'y' || ch == 'Y' );
}
void modifyBook(){
char modify[MAX_LENGTH];
char modify_str[MAX_LENGTH];
char ch;
const char* modifybook = "update " BOOKTABLE " %s";
do{
printf("update " BOOKTABLE);
fgets(modify, MAX_LENGTH, stdin);
fflush(stdin);
modify[strlen(modify) - 1] = '\0';
sprintf(modify_str, modifybook, modify);
if (0 == mysql_real_query(mysql, modify_str, strlen(modify_str))){
printf("update book success\n");
}
else{
zlog_info(zc, "mysql_real_query failed");
zlog_info(zc, mysql_error(mysql));
}
printf("\ncontiune modify(y/n)");
scanf("%c", &ch);
fflush(stdin);
} while (ch == 'y' || ch == 'Y');
}
void deleteBook(){
char isbn[MAX_LENGTH];
char delete_str[MAX_LENGTH];
char ch;
const char* deletebook = "delete from " BOOKTABLE " %s" ;
do{
printf("delete from " BOOKTABLE);
fgets(isbn, MAX_LENGTH, stdin);
fflush(stdin);
isbn[strlen(isbn) - 1] = '\0';
fflush(stdin);
sprintf(delete_str, deletebook, isbn);
if (0 == mysql_real_query(mysql, delete_str, strlen(delete_str))){
printf("delete book success\n");
}
else{
zlog_info(zc, "mysql_real_query failed");
zlog_info(zc, mysql_error(mysql));
}
printf("\ndelete again(y/n)");
scanf("%c", &ch);
fflush(stdin);
} while(ch == 'y' || ch == 'Y');
}
void showMenu(){
system("cls");
printf("\n\n");
printf("\t\t========================================\n");
printf("\t\t= welcome to xiaolixi library =\n");
printf("\t\t========================================\n");
printf("\t\t= 1: show all informations =\n");
printf("\t\t= 2: add books =\n");
printf("\t\t= 3: modify books =\n");
printf("\t\t= 4: delete books =\n");
printf("\t\t= 5: query books =\n");
printf("\t\t= 6: exit books system =\n");
printf("\t\t========================================\n");
printf("enter your choice:");
}
int inquire(){
char ch = 0;
printf("\t 返回到主菜单?(y/n)");
scanf("%c", &ch);
fflush(stdin);
if (ch == 'y' || ch == 'Y'){
showMenu();
return 1;
}
else{
printf("\t 欢迎再次访问图书馆!\n");
Sleep(20);
return 0;
}
}
void queryBook(){
char query[MAX_LENGTH];
char query_str[MAX_LENGTH];
char ch;
const char* querybook = "select %s";
MYSQL_RES *res_ptr;
MYSQL_FIELD *field;
MYSQL_ROW result_row;
int row, column;
int i;
do{
printf("select ");
fgets(query, MAX_LENGTH, stdin);
fflush(stdin);
query[strlen(query) - 1] = '\0';
sprintf(query_str, querybook, query);
if (0 == mysql_real_query(mysql, query_str, strlen(query_str))){
res_ptr = mysql_store_result(mysql);
if (res_ptr){
row = mysql_num_rows(res_ptr);
column = mysql_num_fields(res_ptr);
printf("\t\t检索到%10d行 %10d列\n", row, column);
while (field = mysql_fetch_field(res_ptr)){
printf("%-10s", field->name);
}
printf("\n");
while (result_row = mysql_fetch_row(res_ptr)){
for (i = 0; i < column; ++i){
printf("%-10s", result_row[i]);
}
printf("\n");
}
printf("\n");
mysql_free_result(res_ptr);
}
}
else{
zlog_info(zc, "mysql_real_query failed");
zlog_info(zc, mysql_error(mysql));
}
printf("query again(y/n)");
scanf("%c", &ch);
fflush(stdin);
} while (ch == 'y' || ch == 'Y');
}
void log_init(){
int rc;
rc = zlog_init("test_default.conf");
if (rc) {
printf("init failed\n");
exit(1);
}
zc = zlog_get_category("my_cat");
if (!zc) {
printf("get cat fail\n");
zlog_fini();
exit(1);
}
}
void log_finish(){
zlog_fini();
}
void insertBookIntoDB(){
static char isbn[31];
static char book_name[50];
static char author[50];
static char publisher[50];
static char pub_date[50];
static char book_price[50];
static char book_id[50];
static char book_status[50];
static char query_str[MAX_LENGTH];
static const char* insert_str = "insert into " BOOKTABLE " values(\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",%s,%s,%s)";
static const char* isbn_query_str = "select ISBN from " BOOKTABLE " where ISBN=\"%s\"";
MYSQL_RES* res_ptr = NULL;
printf("input book's ISBN:");
scanf("%s", isbn);
fflush(stdin);
sprintf(query_str, isbn_query_str, isbn);
if (0 != mysql_real_query(mysql, query_str, strlen(query_str))){
zlog_info(zc, "mysql_real_query failed\n");
zlog_info(zc, mysql_error(mysql));
}
else{
res_ptr = mysql_store_result(mysql);
if (res_ptr){
if (0 == mysql_num_rows(res_ptr)){
printf("input information:\n\tbook_name:");
scanf("%s", book_name);
printf("\tauthor:");
scanf("%s", author);
printf("\tpublisher:");
scanf("%s", publisher);
printf("\tpub_date:");
scanf("%s", pub_date);
printf("\tbook_price:");
scanf("%s", book_price);
printf("\tbook_id:");
scanf("%s", book_id);
printf("\tbook_status:");
scanf("%s", book_status);
fflush(stdin);
sprintf(query_str, insert_str, isbn, book_name, author, publisher, pub_date, book_price, book_id, book_status);
if (0 != mysql_real_query(mysql, query_str, strlen(query_str))){
zlog_info(zc, "insert book failed. please try again and the error reason is \n");
zlog_info(zc, mysql_error(mysql));
}
else{
printf("%s\nsuccess\n", query_str);
}
}
else{
zlog_info(zc, "book already existing\n");
}
}
else{
zlog_info(zc, mysql_error(mysql));
}
mysql_free_result(res_ptr);
}
}
用到了MySql和zlog的c接口动态库