#include "include/mysql.h"
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <signal.h> //当按下ctrl+c前执行一个函数
#include <math.h>
/*定义一些数据库连接需要的宏*/
#define HOST "192.168.50.138" /*MySql服务器地址*/
#define USERNAME "root" /*用户名*/
#define PASSWORD "123456" /*数据库连接密码*/
#define DATABASE "mysql" /*需要连接的数据库*/
#define PORT 5555
void ShowInterface(void);
void InputScore(MYSQL *my_connection); //T
int CountAverage2(MYSQL *my_connection); // A
int ShowScore(MYSQL *my_connection); //L
int Sort_ByAverage_DESC(MYSQL *my_connection); //P
int Search(MYSQL *my_connection); //S
void MysqlInit(void);
struct Student
{
int ID;
float ChineseScore;
float MathScore;
float EnglishScore;
float Average;
} student;
MYSQL *my_connection;
//信号处理函数的参数必须是int ，这是被signal（）约束了
void handleSignal(int sig)
{
printf("Received SIGINT signal. Exiting...\n");
mysql_close(my_connection);
exit(0);
}
int main(void)
{
char CMD;
int res;
char SqlSentence[100] = "CREATE TABLE Student (ID INT PRIMARY KEY,Chinese FLOAT,Math FLOAT,English FLOAT,Average FLOAT);";
MysqlInit();
signal(SIGINT, handleSignal); //注册信号处理函数,ctrl+c前关闭mysql的连接
res = mysql_query(my_connection, SqlSentence);
if (res)
{
printf("创建表失败Error： mysql_query !\n");
fprintf(stderr, "Query error: %s\n", mysql_error(my_connection));
}
else
{
printf("创建表成功");
}
ShowInterface();
while (1)
{
printf("请输入命令=");
scanf(" %c", &CMD);
getchar();
switch (CMD)
{
case 'H':
ShowInterface();
break;
case 'T':
InputScore(my_connection);
break;
case 'A':
CountAverage2(my_connection);
break;
case 'L':
ShowScore(my_connection);
break;
case 'P':
Sort_ByAverage_DESC(my_connection);
break;
case 'S':
Search(my_connection);
break;
case 'C':
system("clear");
break;
case 'Q':
/*不要忘了关闭连接*/
mysql_close(my_connection);
exit(0);
default:
//getchar();
printf("输入错误,");
break;
}
}
return 0;
}
//显示界面
void ShowInterface(void)
{
printf("**********************************************\n");
printf("* 学生成绩管理系统————帮助菜单 *\n");
printf("**********************************************\n");
printf("* H = 显示帮助菜单 *\n");
printf("* T = 成绩录入 *\n");
printf("* A = 计算学生平均分 *\n");
printf("* L = 列出成绩表 *\n");
printf("* P = 按平均成绩由高到低排序 *\n");
printf("* S = 按学校查询学生成绩 *\n");
printf("* C = 清屏 *\n");
printf("* Q = 退出系统 *\n");
printf("**********************************************\n");
printf("* Copyright <C> 2023.08.10 By李海涛 *\n");
printf("**********************************************\n");
}
//输入成绩T
void InputScore(MYSQL *my_connection)
{
char SqlSentence[200] = "";
int Count, res;
float totalScore = 0.0;
printf("请输入学生人数：");
scanf("%d", &Count);
printf("请输入%d名学生的三门课成绩: \n", Count);
printf("学号 语文 数学 外语\n");
while (Count > 0)
{
scanf("%d", &student.ID);
scanf("%f", &student.ChineseScore);
scanf("%f", &student.MathScore);
scanf("%f", &student.EnglishScore);
getchar();
totalScore = student.ChineseScore + student.MathScore + student.EnglishScore;
student.Average = totalScore / 3.0;
sprintf(SqlSentence, "INSERT INTO Student (ID, Chinese, Math, English,Average) VALUES (%d, %f, %f, %f, %f);", student.ID, student.ChineseScore, student.MathScore, student.EnglishScore, student.Average);
res = mysql_query(my_connection, SqlSentence);
if (res)
{
printf("输入成绩失败Error： mysql_query !\n");
fprintf(stderr, "Query error: %s\n", mysql_error(my_connection)); //打印报错信息
}
else
{
printf("sql：%s 执行成功 \n", SqlSentence);
}
totalScore = 0;
Count = Count - 1; //下个学生
}
}
//列出成绩表L
int ShowScore(MYSQL *my_connection)
{
int rowID;
float rowScore;
MYSQL_RES *res_ptr; /*执行结果*/
MYSQL_ROW result_row; /*按行返回查询信息*/
int row, column; /* 定义行数,列数*/
int res;
char SqlSentence[200] = "SELECT ID,Chinese,Math,English,Average FROM Student;";
res = mysql_query(my_connection, SqlSentence);
//printf("res = %d \n", res);
//res == 1 表示失败
if (res)
{
printf("显示成绩失败Error： mysql_query !\n");
fprintf(stderr, "Query error: %s\n", mysql_error(my_connection));
}
else
{
/*mysql_affected_rows会返回执行sql后影响的行数*/
//printf("%llu 行受到影响！\n", mysql_affected_rows(my_connection));
printf("执行语句：%s 成功\n", SqlSentence);
// 把查询结果装入 res_ptr
res_ptr = mysql_store_result(my_connection);
//printf("res_ptr = %p \n", res_ptr);
// 存在则输出
if (res_ptr)
{
// 获取行数，列数
row = mysql_num_rows(res_ptr);
column = mysql_num_fields(res_ptr);
printf("row = %d ,column = %d \n", row, column);
if (row == 0)
{
printf("成绩表为空！请先使用命令T录入学生信息。\n");
//则没有记录
return 0;
}
else
{
printf("学生成绩如下：\n");
printf("学号 语文 数学 外语 平均分\n");
// 执行输出结果,从第二行开始循环（第一行是字段名）
for (int i = 1; i < row + 1; i++)
{
// 一行数据
result_row = mysql_fetch_row(res_ptr);
for (int j = 0; j < column; j++)
{
if (j == 0)
{
rowID = atoi(result_row[j]);
printf(" %d", rowID);
}
else
{
rowScore = atoi(result_row[j]);
}
if (rowID < 10 && rowID > 0)
{
printf(" ");//六个
}
else if (rowID >= 10)
{
printf(" ");//五个
}
if (j != 0)
{
printf("%.1f", rowScore);
if (rowScore == 100)
{
printf(" ");//三个
}
else if (rowScore >= 10 && rowScore < 100)
{
printf(" ");//四个
}
else if (rowScore < 10)
{
printf(" ");//五个
}
}
rowID = 0;
rowScore = 0;
//printf("%s", result_row[j]);
}
printf("\n");
}
}
}
}
}
//计算平均分A
int CountAverage2(MYSQL *my_connection)
{
char SqlSentence[200] = "SELECT ID FROM Student;";
MYSQL_RES *res_ptr;
MYSQL_ROW result_row;
int row, column;
int res;
res = mysql_query(my_connection, SqlSentence);
//printf("res = %d \n", res);
//res == 1 表示失败
if (res)
{
printf("计算平均分Error： mysql_query !\n");
fprintf(stderr, "Query error: %s\n", mysql_error(my_connection));
}
else
{
/*mysql_affected_rows会返回执行sql后影响的行数*/
//printf("%llu 行受到影响！\n", mysql_affected_rows(my_connection));
printf("执行语句：%s 成功\n", SqlSentence);
// 把查询结果装入 res_ptr
res_ptr = mysql_store_result(my_connection);
//printf("res_ptr = %p \n", res_ptr);
// 存在则输出
if (res_ptr)
{
// 获取行数，列数
row = mysql_num_rows(res_ptr);
column = mysql_num_fields(res_ptr);
printf("row = %d ,column = %d \n", row, column);
if (row == 0)
{
printf("成绩表为空！请先使用命令T录入学生信息。\n");
//则没有记录
return 0;
}
else
{
printf("平均分已计算，请使用L命令查看。\n");
}
}
}
}
//平均分由高到低排序P
int Sort_ByAverage_DESC(MYSQL *my_connection)
{
char SqlSentence[200] = "SELECT * FROM Student ORDER BY Average DESC;";
MYSQL_RES *res_ptr; /*执行结果*/
MYSQL_ROW result_row; /*按行返回查询信息*/
int row, column; /* 定义行数,列数*/
int res;
res = mysql_query(my_connection, SqlSentence);
//printf("res = %d \n", res);
//res == 1 表示失败
if (res)
{
printf("显示成绩失败Error： mysql_query !\n");
fprintf(stderr, "Query error: %s\n", mysql_error(my_connection));
}
else
{
/*mysql_affected_rows会返回执行sql后影响的行数*/
//printf("%llu 行受到影响！\n", mysql_affected_rows(my_connection));
printf("执行语句：%s 成功\n", SqlSentence);
// 把查询结果装入 res_ptr
res_ptr = mysql_store_result(my_connection);
//printf("res_ptr = %p \n", res_ptr);
// 存在则输出
if (res_ptr)
{
// 获取行数，列数
row = mysql_num_rows(res_ptr);
column = mysql_num_fields(res_ptr);
printf("row = %d ,column = %d \n", row, column);
if (row == 0)
{
printf("成绩表为空！请先使用命令T录入学生信息。\n");
//则没有记录
return 0;
}
else
{
printf("学号 语文 数学 外语 平均分\n");
// 执行输出结果,从第二行开始循环（第一行是字段名）
for (int i = 1; i < row + 1; i++)
{
// 一行数据
result_row = mysql_fetch_row(res_ptr);
for (int j = 0; j < column; j++)
{
printf(" %s", result_row[j]);
for (int k = 0; k < 4; k++)
{
printf(" ");
}
}
printf("\n");
}
}
}
}
}
//根据学号查信息S
int Search(MYSQL *my_connection)
{
int ID;
char SqlSentence[200] = "";
MYSQL_RES *res_ptr; /*执行结果*/
MYSQL_ROW result_row; /*按行返回查询信息*/
int row, column; /* 定义行数,列数*/
int res;
sprintf(SqlSentence, "SELECT ID, Chinese, English, Math, Average FROM Student WHERE ID = %d;", ID);
res = mysql_query(my_connection, SqlSentence);
//printf("res = %d \n", res);
//res == 1 表示失败
if (res)
{
printf("根据学号查信息Error： mysql_query !\n");
fprintf(stderr, "Query error: %s\n", mysql_error(my_connection));
}
else
{
/*mysql_affected_rows会返回执行sql后影响的行数*/
//printf("%llu 行受到影响！\n", mysql_affected_rows(my_connection));
printf("执行语句：%s 成功\n", SqlSentence);
// 把查询结果装入 res_ptr
res_ptr = mysql_store_result(my_connection);
//printf("res_ptr = %p \n", res_ptr);
// 存在则输出
if (res_ptr)
{
// 获取行数，列数
row = mysql_num_rows(res_ptr);
column = mysql_num_fields(res_ptr);
printf("row = %d ,column = %d \n", row, column);
if (row == 0)
{
printf("成绩表为空！请先使用命令T录入学生信息。\n");
//则没有记录
return 0;
}
else
{
printf("请输入要查询的学生学号：");
scanf("%d", &ID);
printf("学生成绩如下：\n");
printf("学号 语文 数学 外语 平均分\n");
// 执行输出结果,从第二行开始循环（第一行是字段名）
for (int i = 1; i < row + 1; i++)
{
// 一行数据
result_row = mysql_fetch_row(res_ptr);
for (int j = 0; j < column; j++)
{
printf(" %s", result_row[j]);
for (int k = 0; k < 4; k++)
{
printf(" ");
}
}
printf("\n");
}
}
}
}
}
//Mysql数据库初始化
void MysqlInit(void)
{
printf("数据库IP：%s，端口：%d，连接的数据库：%s，用户名：%s，密码：******** \n", HOST, PORT, DATABASE, USERNAME);
my_connection = (MYSQL *)malloc(sizeof(MYSQL)); /*数据库连接*/
mysql_init(my_connection);
if (mysql_real_connect(my_connection, HOST, USERNAME, PASSWORD, DATABASE, PORT, NULL, CLIENT_FOUND_ROWS))
{
printf(" 数据库连接成功！ \n");
/*设置查询编码为 utf8, 支持中文*/
mysql_query(my_connection, "set names utf8");
}
else
{
printf("数据库连接失败！\n");
}
}
//计算学生平均分,废弃
/*
int CountAverage(MYSQL *my_connection)
{
float Chinese, Math, Egnlish, Average;
char SqlSentence_QueryRow[200] = "SELECT ID FROM Student;";
char SqlSentence_QueryScoreChinese[200] = "";
char SqlSentence_QueryScoreEnglish[200] = "";
char SqlSentence_QueryScoreMath[200] = "";
MYSQL_RES *res_ptr;
MYSQL_ROW result_row;
int row, column;
int res;
res = mysql_query(my_connection, SqlSentence);
printf("res = %d \n", res);
//res == 1 表示失败
if (res)
{
printf("显示成绩失败Error： mysql_query !\n");
fprintf(stderr, "Query error: %s\n", mysql_error(my_connection));
}
else
{
printf("%llu 行受到影响！\n", mysql_affected_rows(my_connection));
printf("执行语句：%s 成功\n", SqlSentence);
// 把查询结果装入 res_ptr
res_ptr = mysql_store_result(my_connection);
printf("res_ptr = %p \n", res_ptr);
// 存在则输出
if (res_ptr)
{
// 获取行数，列数
row = mysql_num_rows(res_ptr);
column = mysql_num_fields(res_ptr);
printf("row = %d ,column = %d \n", row, column);
if (row == 0)
{
printf("成绩表为空！请先使用命令T录入学生信息。\n");
//则没有记录
return 0;
}
else
{
// 执行输出结果,从第二行开始循环（第一行是字段名）
for (int ID = 1; i < row + 1; i++)
{
sprintf(SqlSentence_QueryScoreChinese, "SELECT Chinese FROM student_scores WHERE ID = %d;", ID);
sprintf(SqlSentence_QueryScoreMath, "SELECT Chinese FROM student_scores WHERE ID = %d;", ID);
sprintf(SqlSentence_QueryScoreEnglish, "SELECT Chinese FROM student_scores WHERE ID = %d;", ID);
}
}
}
}
}
*/
( 粤ICP备18085999号-1 | 粤公网安备 44051102000585号)
狗仔卡
发表于 2023-8-14 15:29:50
置顶卡
千斤顶
显身卡
发表于 2023-8-16 08:56:42
