文章目錄
- 前言
- 一、基本要求
- 二、步驟
-
- 1. 寫一個sql腳本
- 2. 編寫代碼
- 3. 編譯
- 三、運作現象
前言
通過這幾天學習Mysql資料庫,對其也有了基本的了解,為了加深印象,于是就寫了一個最簡易的學生資訊管理系統。
一、基本要求
- 通過已知使用者名和密碼進行登入;
- 可以顯示菜單;
- 可以随時插入學生資訊;
- 可以删除學生資訊;
- 可以通過學生姓名或學号顯示學生所有資訊;
還可以修改學生資訊,添加學生表格屬性等等,,,這些實作都基本類似上述的(這些不想寫了,最簡易的學生資訊管理系統)。
二、步驟
1. 寫一個sql腳本
包括建立資料庫,使用資料庫,建立學生資訊表格,插入大部分學生資訊:student_information.sql:
create database school_db character set gbk;
use school_db;
create table students (
student_id int unsigned not null primary key,
name char(10) not null,
sex char(4) not null,
birth date not null,
politily char(20) not null
);
insert into students values (201080701,"張三","男","1994-12-2","自動化1");
insert into students values (201080702,"李四","男","1989-10-8","自動化2");
insert into students values (201080703,"王五","男","1994-8-8","自動化2");
insert into students values (201080704,"路人1","女","1994-7-9","自動化2");
insert into students values (201080705,"路人2","男","1994-12-8","自動化2");
insert into students values (201080706,"路人3","女","1994-11-8","自動化2");
insert into students values (201080707,"路人4","男","1992-1-23","自動化2");
執行腳本:
- 第一種方法: mysql -h localhost -u root -p 密碼 < student_information.sql;
- 第二種方法:已經登入了mysql, source student_information.sql;
2. 編寫代碼
#include <stdlib.h>
#include <stdio.h>
#include <mysql.h>
#include <ctype.h>
#include <curses.h>
//#define MAX_PASSWD_LEN 16
void printf_mune(void);
char *getPasswd(void);
int insert_student(void);
int delete_student(void);
int inquiry_student(void);
int main(int argc,char** argv)
{
char user[20];
char passwd[20];
char c;
int i;
printf("***********************************************************\n");
printf("* welcome to student information managent system *\n");
printf("***********************************************************\n");
// printf(" user: ______________\b\b\b\b\b\b\b\b\n");
// printf(" psaawd:______\b\b\b\b\b\b\n");
while(1){
printf(" user:");
scanf("%s", user);
printf(" passwd:");
scanf("%s",passwd);
/* while ((c=getch())!='\n')
{
if (i<MAX_PASSWD_LEN && isprint(c)) //isprint函數判斷c是否是可列印字元
{
passwd[i++] = c;
putchar('*');
}
else if (i>0 && c=='\b')
{
--i;
putchar('\b');
putchar(' ');
putchar('\b');
}
}
putchar('\n');
passwd[i] = '\0'; */
if(!strncmp(user, "qigaohua", 8)){
if(!strncmp(passwd, "123456", 6))
break;
else {
printf("passwd error\n");
continue;
}
}else{
printf("user error\n");
continue;
}
}
printf("login sucess\n");
while(1){
printf_mune();
getchar(); //問題1 必須加
c = fgetc(stdin);
switch(c){
case 'I':
insert_student();
break;
case 'D':
delete_student();
break;
case 'S':
inquiry_student();
break;
case 'Q':
return 0;
break;
default:
;
}
}
printf("exit sucess\n");
return 0;
}
/*getc()和getchar()函數想必大家都經常用到,但它們都在輸入的同時顯示輸入内容,并由回車終止輸入。
為了不顯示輸入内容,我們調用另外一個函數getch(),它包含在頭檔案中。該函數可以在輸入的同時不顯示
輸入内容,并在輸入完成後不需回車而自動終止輸入。與此同時,該頭檔案中還包含另外一個函數getche(),
它和getch()功能相同,唯一的差別是輸入的同時顯示輸入的内容。本文我們隻用到getch();*/
/*char *getPasswd()
{
unsigned char c;
passwd =(char*)malloc(8);
int i = 0;
while ((c=getch())!='\r')
{
if (i<MAX_PASSWD_LEN && isprint(c)) //isprint函數判斷c是否是可列印字元
{
passwd[i++] = c;
putchar('*');
}
else if (i>0 && c=='\b')
{
--i;
putchar('\b');
putchar(' ');
putchar('\b');
}
}
putchar('\n');
passwd[i] = '\0';
return passwd;
}*/
void printf_mune(void)
{
printf("The following operations can be performed\n");
printf("1. insert student information input I\n");
printf("2. delete student information input D\n");
printf("3. inquiry student information input S\n");
printf("4. exit student information managent system input Q\n");
}
int insert_student(void)
{
MYSQL mysql;
char sql[100];
char k;
char id[10], name[20], sex[5],birth[15],potility[20];
mysql_init(&mysql);
if(!mysql_real_connect(&mysql, "localhost", "root",NULL,"school_db",0,NULL,0)){
printf("ERROR: %d--%s\n", mysql_errno(&mysql), mysql_error(&mysql));
exit(-1);
}
mysql_set_character_set(&mysql, "utf8");
while(1) {
printf("please input student information\n");
printf("student_id: ");
fflush(stdin);
scanf("%s",id);
// fgets(id, 10, stdin);
printf("\nname: ");
scanf("%s",name);
// fgets(name, 20, stdin);
printf("\nsex: ");
scanf("%s",sex);
printf("\nbirth: ");
scanf("%s",birth);
// fgets(birth, 15, stdin);
printf("\npolitily: ");
scanf("%s",potility);
// fgets(potility, 20, stdin);
printf("\nplease input y or n or q\n");
// read(stdin, k, 1);
getchar(); //注意要加
k = fgetc(stdin);
if(k == 'n')
continue;
else if(k == 'q')
break;
else if(k == 'y'){
sprintf(sql, "insert into students values (\"%s\", \"%s\", \"%s\", \"%s\", \"%s\");", id, name,sex, birth, potility);
printf("\n%s",sql);
if(mysql_query(&mysql, sql)){
printf("\ninsert failed: %d--%s\n", mysql_errno(&mysql), mysql_error(&mysql));
continue;
}
printf("\ninsert sucess\n");
printf("you want exit? input q\n");
getchar();
k = fgetc(stdin);
if(k == 'q')
break;
}
}
mysql_close(&mysql);
return 0;
}
int delete_student(void)
{
MYSQL mysql;
char sql[100];
char id[10], name[20];
char k;
mysql_init(&mysql);
if(!mysql_real_connect(&mysql, "localhost", "root",NULL,"school_db",0,NULL,0)){
printf("ERROR: %d--%s\n", mysql_errno(&mysql), mysql_error(&mysql));
exit(-1);
}
mysql_set_character_set(&mysql, "utf8");
while(1){
printf("please input you want delete student id or name, selset i or n or q\n");
getchar(); //注意
k = fgetc(stdin);
if(k == 'i') {
printf("student id: ");
scanf("%s",id);
//fgets(id, 10, stdin);
sprintf(sql, "delete from students where student_id=\"%s\";", id);
}else if(k == 'n'){
printf("\nstudent name: ");
scanf("%s",name);
//fgets(name, 20, stdin);
sprintf(sql, "delete from students where name=\"%s\";", name);
}else if(k == 'q')
break;
printf("\nyou are sure? y or n or q\n");
getchar();
k = fgetc(stdin);
if(k == 'n')
continue;
else if(k == 'y'){
if(mysql_query(&mysql, sql)){
printf("delete failed: %d--%s\n", mysql_errno(&mysql), mysql_error(&mysql));
continue;
}
printf("delete sucess\n");
}
else if(k == 'q')
break;
}
mysql_close(&mysql);
return 0;
}
int inquiry_student(void)
{
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
MYSQL_FIELD *filed;
char sql[100];
char id[10], name[20];
char k;
int column,i;
my_ulonglong backrows;
mysql_init(&mysql);
if(!mysql_real_connect(&mysql, "localhost", "root",NULL,"school_db",0,NULL,0)){
printf("ERROR: %d--%s\n", mysql_errno(&mysql), mysql_error(&mysql));
exit(-1);
}
mysql_set_character_set(&mysql, "utf8");
while(1){
printf("please input you want inquiry student id or name, selset i or n or q\n");
getchar(); //标記
k = fgetc(stdin);
if(k == 'i') {
printf("student id: ");
scanf("%s", id);
//fgets(id, 10, stdin);
sprintf(sql, "select * from students where student_id=\"%s\";", id);
printf("sql request:%s\n", sql);
}else if(k == 'n') {
printf("\nstudent name: ");
scanf("%s", name);
//fgets(name, 20, stdin);
sprintf(sql, "select * from students where name=\"%s\";", name);
printf("sql request:%s\n", sql);
}else if(k == 'q')
break;
printf("\nyou are sure? y or n or q\n");
getchar();
k = fgetc(stdin);
if(k == 'n')
continue;
else if(k == 'y'){
if(mysql_query(&mysql, sql)){
printf("delete failed: %d--%s\n", mysql_errno(&mysql), mysql_error(&mysql));
continue;
}
res = mysql_store_result(&mysql); //首先判斷res是否為NULL來判斷是否有資料,但是失敗了,不知道為什麼,最後用了下面函數
backrows = mysql_num_rows(res);//傳回上面函數傳回結果的行數
if(backrows != 0){
column = mysql_num_fields(res);
while((row = mysql_fetch_row(res))){
i = 0;
while((filed = mysql_fetch_field(res)) != NULL && (i < column)){
printf("%s:", filed->name);
printf("%s", row[i++]);
printf("\n");
}
printf("\n");
}
}else{
printf("no data found\n");
continue;
}
printf("inquiry sucess\n");
}
else if(k == 'q')
break;
}
mysql_close(&mysql);
return 0;
}
3. 編譯
gcc -o students students.c -I /usr/include/mysql/ -L /usr/lib/mysql/ -lmysqlclient -lcurses
三、運作現象
最後選擇Q退出整個系統。
雖然所要求的功能基本實作,但是比預先心裡所想的還有許多差距,看看程式就知道,改了有改,确實沒時間了,就這樣吧。
關注公衆号"小敗日記",搬磚過程遇到的問題,大家一起探讨,資源共享