前言
重点参考前辈们写的优秀文章，这里只做一些的补充
准备
1.MySQL安装
参考链接：/xiaokang01/p/12092160.html
安装前注意这一步要勾选Documentation，否则找不到include，lib文件夹，当然你之后运行安装软件再添加下图左边方框最下方的 Documentation也可以
2.DEV-C++连接MySQL
参考链接：/yy64578537/article/details/104372747
值得提一句，如果下载MySQL.DevPak /projects/dev-cpp/files/webupdate/webupdate/
长时间不能响应访问，按照下方操作进行换源。
1.基本函数
官网接口函数说明Mysql For C API Function Descriptions:/doc/c-api/5.7/en/c-api-functions.html
参考链接： /Mculover666/article/details/105680319
2.数据库的连接
mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "gbk");//这句话很关键，防止在命令行中中文乱码
#include <stdio.h>#include <mysql.h>#include <string.h>int main(){MYSQL mysql;mysql_init(&mysql);mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "gbk");//这句话很关键，防止在命令行中中文乱码if(NULL == mysql_real_connect(&mysql,"127.0.0.1","root","password","cesdb",3306,NULL,0)){printf("MySQL connect fail.\n");mysql_close(&mysql);return -1;}else printf("MySQL connect success!\n");mysql_close(&mysql);}
3.系统代码实现
//1） 新生入学信息增加，学生信息修改。 //2） 课程信息维护（增加新课程，修改课程信息，删除没有选课的课程信息）。 //3） 录入学生成绩，修改学生成绩。 //4） 按系统计学生的平均成绩、最好成绩、最差成绩、优秀率、不及格人数。 //5） 按系对学生成绩进行排名，同时显示出学生、课程和成绩信息。 //6） 输入学号，显示该学生的基本信息和选课信息。 #include <stdio.h>#include <mysql.h>#include <string.h>void insertintostudent(MYSQL &mysql);void updatestudent(MYSQL &mysql);void insertintocourse(MYSQL &mysql); void updatecourse(MYSQL &mysql);void deletefromcourse(MYSQL &mysql);void insertintosc(MYSQL &mysql);void updatesc(MYSQL &mysql);void count(MYSQL &mysql);void rankingbysdept(MYSQL &mysql);void selectbysno(MYSQL& mysql);MYSQL_RES* res;MYSQL_ROW row;char sql[150];int main(){MYSQL mysql;mysql_init(&mysql);mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "gbk");if(NULL == mysql_real_connect(&mysql,"127.0.0.1","root","1010990249","cesdb_u12975",3306,NULL,0)){printf("MySQL connect fail.\n");mysql_close(&mysql);return -1;}else printf("MySQL connect sucessed!\n");int op=1;while(op){// char value = 1;// (void) mysql_init (&mysql);// mysql_options(&mysql, MYSQL_OPT_RECONNECT, (char *)&value); // mysql_ping(&mysql);system("cls");printf("\n\n");printf("Menu for Linear Table On Sequence Structure \n");printf("-------------------------------------------------\n");printf("1. Insert into student 2. Update student\n");printf("3. Insert into course4. Update course\n");printf("5. Delete from course\n");printf("6. Insert into sc 7. Update sc\n");printf("8. Count AVG,MAX,MIN,EXCELLENT Rate,BELOW60\n");printf("9. Ranking by grade\n");printf("10.Query by sno\n");printf("0. exit\n");printf("-------------------------------------------------\n");printf(" 请选择你的操作[0~10]:");scanf("%d",&op);switch(op){case 1:{int num;printf("请输入需要增加的人数：");scanf("%d",&num);while(num){insertintostudent(mysql);printf("\n");num--;}system("pause");break;}case 2:{updatestudent(mysql);system("pause");break;}case 3:{int num;printf("请输入需要增加的课程数：");scanf("%d",&num);while(num){insertintocourse(mysql);printf("\n");num--;}system("pause");break;}case 4:{updatecourse(mysql);system("pause");break;}case 5:{deletefromcourse(mysql);system("pause");break;}case 6:{insertintosc(mysql);system("pause");break;}case 7:{updatesc(mysql);system("pause");break;}case 8:{count(mysql);system("pause");break;}case 9:{rankingbysdept(mysql);system("pause");break;}case 10:{selectbysno(mysql);system("pause");break;}} }mysql_close(&mysql);}//end of main//student 数据区 char sno[20];char sname[20];char ssex[2];int sage;char sdept[10];char scholarship[10];//courser 数据区char cno[4]; char cname[40];char cpno[4];int ccredit;//sc 数据区char scsno[20]; char sccno[4]; int grade;//1.insertintostudentvoid insertintostudent(MYSQL &mysql){//char sno[9];char sname[20];char ssex[2];int sage;char sdept[10];char scholarship[]="否";strcpy(scholarship,"否");printf("请输入学生的学号：");scanf("%s",sno);printf("请输入学生的姓名：");scanf("%s",sname);printf("请输入学生的性别：");scanf("%s",ssex);printf("请输入学生的年龄：");scanf("%d",&sage);printf("请输入学生的院系：");scanf("%s",sdept);sprintf(sql,"insert into student values('%s','%s','%s','%d','%s','%s');",sno,sname,ssex,sage,sdept,scholarship);if(!mysql_query(&mysql,sql)){printf("新生信息添加成功！\n"); }else {printf("新生信息添加失败！"); fprintf(stderr,"%s\n",mysql_error(&mysql)); } }void updatestudent(MYSQL &mysql){printf("请输入学生的学号：");scanf("%s",sno);sprintf(sql,"select sname from student where sno='%s';",sno);if(mysql_query(&mysql,sql)) {fprintf(stderr,"%s\n",mysql_error(&mysql));return ;}else {res=mysql_use_result(&mysql);if((row=mysql_fetch_row(res))==NULL) {printf("该学号无效！");return ;}}mysql_free_result(res);printf("(1)姓名 (2)性别 (3)年龄 (4)院系 (5)奖学金 (0)退出\n");int select=1;while(select) {printf("请输入选择：");scanf("%d",&select);switch (select){case 1:{printf("请输入学生的姓名：");scanf("%s",sname);sprintf(sql,"update student set sname='%s' where sno='%s';",sname,sno);break;}case 2:{printf("请输入学生的性别：");scanf("%s",ssex);sprintf(sql,"update student set ssex='%s' where sno='%s';",ssex,sno);break;} case 3:{printf("请输入学生的年龄：");scanf("%d",&sage);sprintf(sql,"update student set sage=%d where sno='%s';",sage,sno);break;}case 4:{printf("请输入学生的院系：");scanf("%s",sdept);sprintf(sql,"update student set sdept='%s' where sno='%s';",sdept,sno);break;}case 5:{printf("请输入学生是否获得奖学金(是/否)：");scanf("%s",scholarship);sprintf(sql,"update student set sdept='%s' where sno='%s';",scholarship,sno);break;}case 0:{break;}}if(!mysql_query(&mysql,sql)){printf("学生信息修改成功！");}else {printf("学生信息修改失败！");fprintf(stderr,"%s\n",mysql_error(&mysql));system("pause");}}}void insertintocourse(MYSQL &mysql){printf("请输入课程号：");scanf("%s",cno);printf("请输入课程名：");scanf("%s",cname);printf("请输入先修课程：");scanf("%s",cpno);printf("请输入课程学分：");scanf("%d",&ccredit);sprintf(sql,"insert into course values('%s','%s','%s','%d');",cno,cname,cpno,ccredit);if(!mysql_query(&mysql,sql)){printf("课程信息添加成功！\n"); }else {printf("课程信息添加失败！\n"); fprintf(stderr,"%s\n",mysql_error(&mysql)); } }void updatecourse(MYSQL &mysql){char newcname[40];printf("请输入课程名：\n");scanf("%s",cname);sprintf(sql,"select * from course where cname='%s';",cname);if(mysql_query(&mysql,sql)) {fprintf(stderr,"%s\n",mysql_error(&mysql));return ;}else {res=mysql_use_result(&mysql);if((row=mysql_fetch_row(res))==NULL) {printf("系统中无该课程！");return ;}}mysql_free_result(res);printf("(1)课程号 (2)课程名 (3)先修课 (4)学分 (0)退出\n");int select=1;while(select) {printf("请输入选择：");scanf("%d",&select);switch (select){case 1:{printf("请输入课程号：");scanf("%s",cno);sprintf(sql,"update course set cno='%s' where cname='%s';",cno,cname);break;}case 2:{printf("请输入课程名：");scanf("%s",newcname);sprintf(sql,"update course set cname='%s' where cname='%s';",newcname,cname);break;} case 3:{printf("请输入先修课程：");scanf("%d",&cpno);sprintf(sql,"update course set sage='%s' where cname='%s';",cpno,cname);break;}case 4:{printf("请输入学分：");scanf("%d",&ccredit);sprintf(sql,"update course set ccredit= %d where cname='%s';",ccredit,cname);break;}case 0:{return ;}}if(!mysql_query(&mysql,sql)){printf("课程信息修改成功！\n");}else {printf("课程信息修改失败！\n");fprintf(stderr,"%s\n",mysql_error(&mysql));}}}void deletefromcourse(MYSQL &mysql){strcpy(sql,"delete from course where cno not in(select cno from sc);");if(!mysql_query(&mysql,sql)){printf("课程信息删除成功！\n");}else {printf("课程信息删除失败！\n");fprintf(stderr,"%s\n",mysql_error(&mysql));}}void insertintosc(MYSQL &mysql){printf("请输入学号：");scanf("%s",scsno);printf("请输入课程号：");scanf("%s",sccno);printf("请输入成绩：");scanf("%d",&grade);sprintf(sql,"insert into sc values('%s','%s',%d);",scsno,sccno,grade);if(!mysql_query(&mysql,sql)){printf("成绩录入成功！\n"); }else {printf("成绩录入失败！"); fprintf(stderr,"%s\n",mysql_error(&mysql)); } }void updatesc(MYSQL &mysql){printf("请输入学号和课程号：");scanf("%s %s",scsno,sccno);sprintf(sql,"select * from sc where sno='%s' and cno='%s';",scsno,sccno);if(mysql_query(&mysql,sql)) {fprintf(stderr,"%s\n",mysql_error(&mysql));return ;}else {res=mysql_use_result(&mysql);if((row=mysql_fetch_row(res))==NULL) {printf("系统中无该学号和课程对应的成绩！");return ;}}mysql_free_result(res);printf("请输入更改后的成绩：");scanf("%d",&grade);sprintf(sql,"update sc set grade=%d where sno='%s' and cno='%s';",grade,scsno,sccno);if(!mysql_query(&mysql,sql)){printf("成绩更新成功！\n"); }else {printf("成绩更新失败！\n"); fprintf(stderr,"%s\n",mysql_error(&mysql)); } } //统计 void count(MYSQL &mysql){//略}void rankingbysdept(MYSQL &mysql){strcpy(sql,"select DISTINCT Sdept from student,sc where student.sno=sc.sno;");if(mysql_query(&mysql,sql)){fprintf(stderr,"%s\n",mysql_error(&mysql)); return ;} MYSQL_RES *res1;MYSQL_ROW row1;res1=mysql_store_result(&mysql);char a[4][9];int i=0;while((row1=mysql_fetch_row(res1))!=NULL){strcpy(a[i],row1[0]);i++;}while(i){i--;sprintf(sql,"select student.sno,student.sdept,cno,grade from sc,student where sdept='%s' order by grade desc",a[i]);if(mysql_query(&mysql,sql)){fprintf(stderr,"%s\n",mysql_error(&mysql)); return ;}MYSQL_RES *res2=mysql_store_result(&mysql);MYSQL_ROW row2;while((row2=mysql_fetch_row(res2))!=NULL){printf("%10s %5s %5s %5s\n",row2[0],row2[1],row2[2],row2[3]);}mysql_free_result(res2);}mysql_free_result(res1);}void selectbysno(MYSQL& mysql){printf("请输入学号：");scanf("%s",sno);sprintf(sql,"select distinct student.*,cno from student,sc where student.sno=sc.sno and student.sno='%s'",sno);if(mysql_query(&mysql,sql)){fprintf(stderr,"%s\n",mysql_error(&mysql)); return ;}res=mysql_store_result(&mysql);while((row=mysql_fetch_row(res))!=NULL){printf("%s %10s %10s %s %s %s %s\n",row[0],row[1],row[2],row[3],row[4],row[5],row[6]);}mysql_free_result(res);}
