目录
2.1 先将Visual Studio操作MySQL?在Visual Studio中连接MySQL数据库的方法:
2.2 visual studio 2017怎么找到mysql.h
要求:
开发一个图书管理系统,该系统结合MySQL数据库设计而构成的一个数据库管理系统,对图书信息进行添加、删除、修改、查询等操作。
实现功能:
- 录入图书信息
- 实现删除功能
- 实现查找功能
- 实现修改功能
- 保存添加的图书信息
开发及运行环境:
系统开发平台:visual studio 2017(debug x64)
数据库管理平台:MYSQL 8.0 ( MySQL Community Server: mysql-8.0.23-winx64)
一、数据库设计
根据网站下载MySQL软件,参考链接:https://blog.csdn.net/chenriyang0306/article/details/54587034
😃在创建数据库前,先看现在数据库中存在什么表:
在创建数据库前,先看现在数据库中存在什么表:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
1.1 创建数据库:
mysql> create database dp_books;
Query OK, 1 row affected (0.09 sec)
1.2 选择你所创建的数据库
mysql> use dp_books;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| dp_books |
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)
此时你已经进入刚才所建立的数据库dp_books
1.3 创建数据表
创建数据表
mysql> create table tb_book(
-> ID char (10) NOT NULL,
-> bookname char(50) NOT NULL,
-> author char(50) NOT NULL,
-> bookconcern char(100) NOT NULL,
-> PRIMARY KEY(ID)
-> )ENGINE = MYISAM;
Query OK, 0 rows affected (0.44 sec)
mysql> show tables;
+--------------------+
| Tables_in_dp_books |
+--------------------+
| tb_book |
+--------------------+
1 row in set (0.04 sec)
//显示表的结构
mysql> describe tb_book;
+-------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+---------+-------+
| ID | char(10) | NO | PRI | NULL | |
| bookname | char(50) | NO | | NULL | |
| author | char(50) | NO | | NULL | |
| bookconcern | char(100) | NO | | NULL | |
+-------------+-----------+------+-----+---------+-------+
4 rows in set (0.08 sec)
二、C语言开发数据库
2.1 先将Visual Studio操作MySQL?在Visual Studio中连接MySQL数据库的方法:
详见:https://www.jb51.net/softjc/614039.html
2.2 visual studio 2017怎么找到mysql.h
参考:https://blog.csdn.net/weixin_43134135/article/details/82391821
#include<windows.h>
#include<mysql.h>
#pragma comment(lib, "libmysql.lib")
main()
{
}
此时编译成功!
三、文件引用
#include<windows.h>
#include<mysql.h>
#include<stdio.h>
#pragma comment(lib, "libmysql.lib") //避免找不到libmysql.lib
#pragma warning(disable:4996)// 避免出现scanf提示错误!
//参考解决方法:https://blog.csdn.net/sinat_36101354/article/details/54411493
四、变量和函数定义
MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
char ch[2];
void ShowAll();
void AddBook();
void ModifyBook();
void DelteBook();
void QueryBook();
五、主要模块设计
🍎5.1 显示主要菜单
void ShowMenu()
{
system("cls");
printf("\n\n\n\n\n");
printf("\t|***************************************\n");
printf("\t| Welcome To Lemon Books System |\n");
printf("\t|***************************************\n");
printf("\t|\t 1 - 显示所有图书信息 |\n");
printf("\t|\t 2 - 添加图书信息 |\n");
printf("\t|\t 3 - 删除图书信息 |\n");
printf("\t|\t 4 - 查询图书信息 |\n");
printf("\t|\t 6 - 退出 |\n");
printf("\t|***************************************\n");
printf("\t| ENTER YOUR CHOICE(1-6) |\n");
}
main函数:
int main()
{
int n;
mysql_init(&mysql);
ShowMenu();
scanf("%d", &n);
while (n)
{
switch (n)
{
case 1:
ShowAll();
break;
case 2:
AddBook();
break;
case 3:
ModifyBook();
break;
case 4:
DelteBook();
break;
case 5:
QueryBook();
break;
case 6:
exit(0);
default:
break;
}
scanf("%d", &n);
}
}
🍎5.2 显示所有图书信息
void inquire()
{
printf("\t 显示主菜单?(y/n)");
scanf("%s", ch);
if (strcmp(ch, "Y") == 0 || strcmp(ch, "y") == 0)
{
ShowMenu();
}
else
{
exit(0);
}
}
void ShowAll()
{
//连接数据库
if (!mysql_real_connect(&mysql, "127.0.0.1", "root", "pqlcyl", "dp_books", 0, NULL, 0))
{
printf("\n\t Can not connect db_books!\n");
}
else
{
//连接数据库成功!
if (mysql_query(&mysql, "select * from tb_book"))
{
printf("\n\t Query tb_book failed!\n");
}
else
{
/*查询失败*/
result = mysql_store_result(&mysql);
if(mysql_num_rows(result) > 0)
{
printf("\t***************************************\n");
printf("\t 显示记录信息 \n");
printf("\t***************************************\n");
printf("\t 图书编号 图书名 作者 出版社 \n");
printf("\t***************************************\n");
while (row = mysql_fetch_row(result))
{
fprintf(stdout, "\t %s %s %s %s \n", row[0], row[1], row[2], row[3]);
}
printf("\t***************************************\n");
}
else
{
printf("\n\t没有记录信息");
}
mysql_close(&mysql);
}
inquire(); //询问是否显示主菜单
}
}
此时运行出现错误!
问题一:
答:
将Debug改为x64,重新下载MYSQL的mysql-8.0.23-winx64,需要是x64版本
问题二:此刻无法识别“mysql.h”
答:
重新更新该目录下的include文件夹和lib文件夹,以及添加如下图该工程的属性
问题三:发现无法识别
答:添加属性配置的目录
问题四:没有找到libmysql.dll,因此这个应用程序未能启动。重新安装应用程序可能会修复此问题
答:在工程的Debug文件夹中添加下图的2个文件
问题五:
ANS:
参考连接:
🍎5.3 插入图书信息
在SQL中往表插入信息
mysql> insert into tb_book value('xiaoming','f','6','china');
Query OK, 1 row affected (0.02 sec)
mysql> select *from tb_book;
+----------+----------+--------+-------------+
| ID | bookname | author | bookconcern |
+----------+----------+--------+-------------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| xiaoming | f | 6 | china |
| 4 | i | like | eatting |
| 33 | i | love | c++ |
+----------+----------+--------+-------------+
5 rows in set (0.00 sec)
c代码:
void AddBook()
{
int rowcount;
char ID[10];
char bookname[50];
char author[50];
char bookconcern[100];
/*char *bookname;
char *author;
char *bookconcern;*/
char *sql;
char dest[220] = { " " };
//连接数据库
if (!mysql_real_connect(&mysql, "127.0.0.1", "root", "pqlcyl", "dp_books", 0, NULL, 0))
{
printf("\n\t Can not connect db_books!\n");
}
else
{
printf("\t***************************************\n");
printf("\t 添加图书信息 \n");
printf("\t***************************************\n");
if (mysql_query(&mysql, "select * from tb_book"))
{
printf("\n\t Query tb_book failed!\n");
}
else
{
/*查询失败*/
result = mysql_store_result(&mysql);
rowcount = mysql_num_rows(result);
row = mysql_fetch_row(result);
printf("\t ID:");
scanf("%s", ID);
if (mysql_num_rows(result) != NULL)
{
do
{
if (!strcmp(ID, row[0]))
{
printf("\t记录存在,按任意键继续!");
getch();
mysql_free_result(result);
mysql_close(&mysql);
return;
}
} while (row = mysql_fetch_row(result));
}
printf("\t 图书名:");
scanf("%s", &bookname);
sql = "insert into tb_book(ID, bookname, author, bookconcern) values(";
strcat(dest, sql);
strcat(dest, "'");
strcat(dest, ID);
strcat(dest, "','");
//strcat(dest, &bookname);
strcat(dest, bookname);
printf("\t 作者: ");
scanf("%s", &author);
strcat(dest, "','");
//strcat(dest, &author);
strcat(dest, author);
printf("\t 出版社: ");
scanf("%s", &bookconcern);
strcat(dest, "','");
strcat(dest, bookconcern);
//strcat(dest, &bookconcern);
strcat(dest, "')");
printf("%s\n", dest);
if (mysql_query(&mysql, dest) != 0)
{
fprintf(stderr, "不能插入记录!\n", mysql_error(&mysql));
printf("%s\n", mysql_error(&mysql));
}
else
{
printf("\t 插入成功!\n");
}
mysql_free_result(result);
}
inquire();
}
}
问题一:发现不能插入成功!只有单纯数字时可以,这样不符合实际。
答:insert into tb_book value('xiaoming','f','6','china');
通过SQL语句的插表语句,发现value 插入的内容少了 ' ',在strcat()字符串连接时,漏加上。
🍎5.4 修改信息
在SQL修改表信息:
可以看出ID = 2信息改变。
mysql> select * from tb_book;
+----------+----------+--------+-------------+
| ID | bookname | author | bookconcern |
+----------+----------+--------+-------------+
| 1 | uhiu | uhyfr | kj |
| 2 | dasf | adfa | daf |
| xiaoming | pp | 6 | china |
| 4 | i | like | eatting |
| 33 | i | love | c++ |
| 5 | ww | ww | 3432 |
+----------+----------+--------+-------------+
6 rows in set (0.00 sec)
mysql> update tb_book set bookname = "tttt", author = "yyyy", bookconcern ="yyy4" where ID = "2";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb_book;
+----------+----------+--------+-------------+
| ID | bookname | author | bookconcern |
+----------+----------+--------+-------------+
| 1 | uhiu | uhyfr | kj |
| 2 | tttt | yyyy | yyy4 |
| xiaoming | pp | 6 | china |
| 4 | i | like | eatting |
| 33 | i | love | c++ |
| 5 | ww | ww | 3432 |
+----------+----------+--------+-------------+
6 rows in set (0.00 sec)
mysql>
c代码:
void ModifyBook()
{
char ID[10];
char dest[100] = { " " };
char dest1[220] = { " " };
char *bookname;
char *author;
char *bookconcern;
char *sql;
//连接数据库
if (!mysql_real_connect(&mysql, "127.0.0.1", "root", "pqlcyl", "dp_books", 0, NULL, 0))
{
printf("\n\t Can not connect db_books!\n");
}
else
{
printf("输入您想要修改的图书编号\n");
scanf("%s", ID);
sql = "select * from tb_book where id ='";
strcat(dest, sql);
strcat(dest, ID);
strcat(dest, "'");
if (mysql_query(&mysql, dest))
{
printf("\n 查询tb_book数据失败!\n");
}
else
{
result = mysql_store_result(&mysql);
if (mysql_num_rows(result) != NULL)
{
printf("\t发现记录信息,是否显示?(Y/N)");
scanf("%s", ch);
if (strcmp(ch, "Y") == 0 || strcmp(ch, "y") == 0)
{
printf("\t***************************************\n");
printf("\t 显示图书信息 \n");
printf("\t***************************************\n");
printf("\t 图书编号 图书名 作者 出版社 \n");
printf("\t***************************************\n");
while (row = mysql_fetch_row(result))
{
fprintf(stdout, "\t %s %s %s %s \n", row[0], row[1], row[2], row[3]);
}
printf("\t***************************************\n");
printf("\t Modify? (y/n) \n");
scanf("%s", ch);
if (strcmp(ch, "Y") == 0 || strcmp(ch, "y") == 0)
{
printf("\t 图书名:");
scanf("%s", &bookname);
sql = "update tb_book set bookname= '";
strcat(dest1, sql);
strcat(dest1, &bookname);
printf("\t 作者: ");
scanf("%s", &author);
strcat(dest1, "', author= '");
strcat(dest1, &author);
printf("\t 出版社: ");
scanf("%s", &bookconcern);
strcat(dest1, "', bookconcern='");
strcat(dest1, &bookconcern);
strcat(dest1, "' where ID= '");
strcat(dest1, ID);
strcat(dest1, "'");
printf("dest = %s\n", dest);
printf("dest1 =%s\n", dest1);
if (mysql_query(&mysql, dest1) != 0)
{
fprintf(stderr, "不能修改记录!\n", mysql_error(&mysql));
printf("%s\n", mysql_error(&mysql));
}
else
{
printf("\t 修改成功!\n");
}
}
}
}
else
{
printf("没有发现要修改的信息");
}
}
mysql_free_result(result);
}
mysql_close(&mysql);
inquire();
}
问题一:
答:需要添加如下:对于where ID= '2'。ID需要加上 ‘ ’
dest1 = update tb_book set bookname= 'dasf', author= 'adfa', bookconcern='daf' where ID= '2'
🍎5.5 删除图书信息
在SQL删除表信息:
可以看出ID = 2信息被删除。
mysql> select * from tb_book;
+----------+----------+--------+-------------+
| ID | bookname | author | bookconcern |
+----------+----------+--------+-------------+
| 1 | uhiu | uhyfr | kj |
| 2 | tttt | yyyy | yyy4 |
| xiaoming | pp | 6 | china |
| 4 | i | like | eatting |
| 33 | i | love | c++ |
| 5 | ww | ww | 3432 |
+----------+----------+--------+-------------+
6 rows in set (0.00 sec)
mysql> delete from tb_book where ID = "2";
Query OK, 1 row affected (0.03 sec)
mysql> select * from tb_book;
+----------+----------+--------+-------------+
| ID | bookname | author | bookconcern |
+----------+----------+--------+-------------+
| 1 | uhiu | uhyfr | kj |
| xiaoming | pp | 6 | china |
| 4 | i | like | eatting |
| 33 | i | love | c++ |
| 5 | ww | ww | 3432 |
+----------+----------+--------+-------------+
5 rows in set (0.00 sec)
C代码:
void DelteBook()
{
char ID[10];
char *sql;
char dest[100] = { "" };
char dest1[100] = { "" };
//连接数据库
if (!mysql_real_connect(&mysql, "127.0.0.1", "root", "pqlcyl", "dp_books", 0, NULL, 0))
{
printf("\n\t Can not connect db_books!\n");
}
else
{
printf("输入您想要删除的图书编号\n");
scanf("%s", ID);
sql = "select * from tb_book where id = '";
strcat(dest, sql);
strcat(dest, ID);
strcat(dest, "'");
printf("%s", dest);
if (mysql_query(&mysql, dest))
{
printf("\n 查询tb_book数据失败!\n");
}
else
{
result = mysql_store_result(&mysql);
if (mysql_num_rows(result) != NULL)
{
printf("\t发现记录信息,是否显示?(Y/N)");
scanf("%s", ch);
if (strcmp(ch, "Y") == 0 || strcmp(ch, "y") == 0)
{
printf("\t***************************************\n");
printf("\t 显示图书信息 \n");
printf("\t***************************************\n");
printf("\t 图书编号 图书名 作者 出版社 \n");
printf("\t***************************************\n");
while (row = mysql_fetch_row(result))
{
fprintf(stdout, "\t %s %s %s %s \n", row[0], row[1], row[2], row[3]);
}
printf("\t***************************************\n");
printf("\t Delete ? (y/n) \n");
scanf("%s", ch);
if (strcmp(ch, "Y") == 0 || strcmp(ch, "y") == 0)
{
sql = "delete from tb_book where ID = '";
printf("%s", dest1);
strcat(dest1, sql);
strcat(dest1, ID);
strcat(dest1, "'");
printf("%s", dest1);
if (mysql_query(&mysql, dest1) != 0)
{
fprintf(stderr, "不能删除记录!\n", mysql_error(&mysql));
printf("%s\n", mysql_error(&mysql));
}
else
{
printf("\t 删除成功!\n");
}
}
}
}
else
{
printf("没有发现要删除的信息");
}
}
mysql_free_result(result);
}
mysql_close(&mysql);
inquire();
}
ID=4被删除成功!
🍎5.6 查询图书信息
在SQL查询表信息:
可以查询ID = xiaoming 信息。
mysql> select * from tb_book where ID ="xiaoming";
+----------+----------+--------+-------------+
| ID | bookname | author | bookconcern |
+----------+----------+--------+-------------+
| xiaoming | pp | 6 | china |
+----------+----------+--------+-------------+
1 row in set (0.00 sec)
C代码:
void QueryBook()
{
char ID[10];
char *sql;
char dest[100] = { "" };
//连接数据库
if (!mysql_real_connect(&mysql, "127.0.0.1", "root", "pqlcyl", "dp_books", 0, NULL, 0))
{
printf("\n\t Can not connect db_books!\n");
}
else
{
printf("输入您想要查询的图书编号\n");
scanf("%s", ID);
sql = "select * from tb_book where id = '";
strcat(dest, sql);
strcat(dest, ID);
strcat(dest, "'");
printf("%s", dest);
if (mysql_query(&mysql, dest))
{
printf("\n 查询tb_book数据失败!\n");
}
else
{
result = mysql_store_result(&mysql);
if (mysql_num_rows(result) != NULL)
{
printf("\t***************************************\n");
printf("\t 显示图书信息 \n");
printf("\t***************************************\n");
printf("\t 图书编号 图书名 作者 出版社 \n");
printf("\t***************************************\n");
while (row = mysql_fetch_row(result))
{
fprintf(stdout, "\t %s %s %s %s \n", row[0], row[1], row[2], row[3]);
}
printf("\t***************************************\n");
}
else
{
printf("没有发现要查询的信息");
}
mysql_free_result(result);
}
mysql_close(&mysql);
}
inquire();
}
六、关于mysql_real_connect问题
问题:mysql_real_connect只能连接一次,回到主界面再次连接会报错?
答:
将参数localhost换成127.0.0.1即可
原因分析:
凡是以127开头的IP地址,都是回环地址(Loop back address),其所在的回环接口Loop back 一般被理解为虚拟网卡,并不是真正的路由器接口。
通俗的讲,就是我们在主机上发送给127开头的IP地址的数据包会被发送的主机自己接收,根本传不出去,外部设备也无法通过回环地址访问到本机。
(简单理解:单个主机如自己电脑或单个虚拟主机,内部自己访问自己不经外网自个玩)
参考:https://blog.csdn.net/u010565545/article/details/99244959
问题:每次打开工程会发现数据连接断开!
ANS:
每次打开工程都要重新点击下数据连接将其打开。
小结:
本人小白,望指教!
代码链接:https://pan.baidu.com/s/1oT8m6iv8Tyh3JSZ-uNo5Nw
提取码:34xk