Student Management System (MYSQ Database Edition) __ Database

First, the System review:
This system is a simple student management system, and the former do a student management system from the functional mountain, in fact, is the same. The difference is that the underlying storage part of the previous student management system used a file system that used files to store student records. To the student record of additions and deletions to check the operation is also through the C language related file operation to achieve. And the system here is through the MySQL database to achieve, that is, the bottom of the storage through the MySQL database to achieve, the student record of the deletion of the change is provided by the MySQL C API implementation, that is, to the MySQL server launched SQL statement execution application, Returns the results after the server has finished executing.

Second, the system structure:

System Structure Brief Introduction:
1. Interface section: This part of the interface is not a real graphical interface, but the black window to display the menu and so on. Mainly includes the printing menu, receives the user to enter the related data and so on. Its functions are included in the MEAU.C source file.
2. Middle-tier operation function: This is the middle layer of the whole system, because it receives the module which the user chooses from the interface, and calls down the specific MySQL execution statement.
3. MySQL Bottom operation function: This is the underlying part of the entire system, perform the corresponding operations on the database by executing the SQL statements, and accept the data returned by the database.

System Process Introduction:
1. The user chooses the related function module to pass to the middle layer.
2. The middle tier receives the user's selections, performs related operations, and, depending on the requirements, combines the relevant SQL statements and requests the MySQL server for execution.
3. MySQL server after the execution of the data back to the middle tier (here does not consider the data returned to the MySQL bottom operation function of the process), the middle layer after parsing, output.
4. Returns the final result to the interface.

Loop through the four steps above until you exit the system.

Third, the Code section:

/* Name: specific operation of the MySQL database related function Description: In this file is a number of specific through the MySQL provided by the API database operation of some functions.


Including: Connection database, additions, deletions and other operations, is as the bottom of the MySQL driver function.   * * #include "main.h" static MySQL MySQL, *sock;      Declaration of MySQL handle//Connection database: Successfully returned 0, failed return-1 int connectmysql () {const char * host = "118.89.148.55";      Host IP, where the use of my cloud host//const char * host = "127.0.0.1";       Because it is a native test, the local IP const char * user = "Pan" is filled in; This is changed to your username, that is, the user name of the connection MySQL const char * passwd = "123456";      Change this to your user password const char * db = "Stusys";           Change here to the name of the database you want to connect to unsigned int port = 3306;
     This is the port of the MySQL server, if you have not modified it is 3306.    const char * unix_socket = NULL;      Unix_socket This is under UNIX, I am under windows, so just set it to null unsigned long Client_flag = 0;                          This parameter is generally 0 mysql_init (&mysql);

     You must use this function before you connect to initialize printf ("Connecting to the MySQL database ... \ n");
     if ((sock = Mysql_real_connect (&mysql, host, user, passwd, DB, Port, Unix_socket, client_flag)) = = NULL)//Connect MySQL {PrinTF ("Connection failed because: \ n");
         fprintf (stderr, "%s\n", Mysql_error (&mysql));         return-1;          Returns failed} else return 0;
  Return success}//check number is already present int ifstunumexist (int stu_num) {char query[100];

   int temp_num = 0; Mysql_res * result = NULL; Save result set Mysql_row ROW;

    Represents a row of sprintf in the result set (query, "%s%d", "SELECT COUNT (*) from students where id =", stu_num); Query Success if (Querybymysql (query,&result) = = 0) {if ((row = mysql_fetch_row (result))!= NULL)//Read the data in the result set, return the is the next line.        Because the current cursor is in the first row "before" {temp_num = Atoi (row[0]), because the result set is saved;
            Number of returned if (temp_num = 0)//school number does not exist return-1;          else return 0;
  School Number exists}//release result set mysql_free_result (results);
      else {fprintf (stderr, "fail to check student num!\n");
  return-1; }/* Query statement and return result set note that the results here use a double pointer in order to return the query's result set/int querybymysql (char * query) to the upper function that calls this function.Mysql_res * * result) {if (mysql_query (&mysql, query) = = 0)//Execute Query {if (*result = my Sql_store_result (&mysql)) = = NULL)//Save the results of the query {fprintf (stderr, fail to store result!\n)
                ;
            return-1;

        else return 0;
            else {fprintf (stderr, "fail to query table!\n");
        return-1;
    }//Add a record int addrecordusemysql (Student stus) {char query[100];

    Long Affect_row =-1; Compose an Insert SQL statement sprintf (query, "%s%d, '%s ',%d, '%c ',%d,%d)", "INSERT into students values (", Stus.stu_num, Stus

   . Name,stus.age,stus.sex,stus.grade,stus._class);

       if (mysql_query (&mysql, query) = = 0)//execute query {affect_row = Mysql_affected_rows (&mysql);
       if (Affect_row > 0) return 0;
           else {printf ("MySQL executes insert student record failed!\n");
       return-1; } ElSE {fprintf (stderr, "MySQL insert student record failure!\n");
    return-1;
    The record int deleterecordusemysql (int stu_num) {char query[100] of the specified school number is deleted in the database;

    Long Affect_row =-1;

    Compose a Delete SQL statement sprintf (query, "%s%d", "delete from students where id =", stu_num);

       if (mysql_query (&mysql, query) = = 0)//execute query {affect_row = Mysql_affected_rows (&mysql);
       if (Affect_row > 0) return 0;
           else {printf ("MySQL performs delete student record failed!\n");
       return-1;
        } else {fprintf (stderr, "MySQL Delete student record failed!\n");
    return-1;
     }//Database delete all records int deleteallrecordusemysql () {char query[100];

    Long Affect_row =-1;

    Compose a Delete SQL statement sprintf (query, "%s", "Delete from students");

       if (mysql_query (&mysql, query) = = 0)//execute query {affect_row = Mysql_affected_rows (&mysql);
       if (Affect_row > 0) return 0; else {printF ("MySQL performs deletion of all student records failed!\n");
       return-1;
        } else {fprintf (stderr, "MySQL performs deletion of all student records failed!\n");
    return-1;
    Query a record int searchonerecordusemysql (int stu_num,student * ret_stu) {char query[100] in the database;

    Long Affect_row =-1; Mysql_res * result = NULL; Save result set Mysql_row ROW;

    Represents a row//In the result set, consisting of a query SQL statement sprintf (query, "%s%d", "SELECT * from students where id =", stu_num); if (Querybymysql (query,&result) = = 0)//Execute Query {if ((row = mysql_fetch_row (result)!= NULL)//Read data in result set , the next row is returned.
            Because when the result set is saved, the current cursor extracts the data from the result set Ret_stu->stu_num = Stu_num in the first row "before" {//;
            sprintf (Ret_stu->name, "%s", row[1]);
             Ret_stu->age = Atoi (row[2]);
            sprintf (& (Ret_stu->sex), "%s", row[3]);
            Ret_stu->grade = Atoi (row[4]);

        Ret_stu->_class = Atoi (row[5]); else {printf ("Query result set is empty, query failed!\n ");
        return-1;

        //Release result set mysql_free_result (results);

    return 0;
        else {fprintf (stderr, "MySQL query student record failed!\n");
    return-1;
    }///database query all student record information int showallrecordsusemysql (Student *ret_stu,int *num) {char query[100];

    Long Affect_row =-1; Mysql_res * result = NULL; Save result set Mysql_row ROW;

    Represents a row//In the result set, consisting of a query SQL statement sprintf (query, "%s", "SELECT * from students"); if (Querybymysql (query,&result) = = 0)//Execute Query {while (row = Mysql_fetch_row ()!= NULL)//Read result set Data, the next row is returned.
            Because the current cursor extracts the development data (Ret_stu[*num] from the returned result set) when the result set is saved. Stu_num = Atoi (row[0));
            sprintf ((Ret_stu[*num]). Name, "%s", row[1]);
             (Ret_stu[*num]). Age = Atoi (row[2]);
            sprintf (& (Ret_stu[*num].sex), "%s", row[3]);
            (Ret_stu[*num]). Grade = Atoi (row[4));


           (Ret_stu[*num]). _class = Atoi (row[5)); (*num) + +;
                Increase in the number of students if ((*num) > Max_student) {printf ("Number of returnees exceeding!\n");
            return-1;

        }//Release result set mysql_free_result (results);

    return 0;
        else {fprintf (stderr, "MySQL query student record failed!\n");
    return-1;
    }///Database update modify a student record int modifyrecordusemysql (Student * mod_stu) {char query[100];

    Long Affect_row =-1;  Make up an update modify SQL statement sprintf (query, '%s '%s '%s '%d%s '%s '%d%s ',%s '%d ' = '%c ' students '), "" "Update" Set name = ", Mod_stu->name, ", age =", Mod_stu->age, ", sex =", Mod_stu->sex, ", Grade =", Mod_stu->grade, ", class =", Mod_stu->_

    Class, "Where id =", mod_stu->stu_num);

        if (mysql_query (&mysql, query) = = 0)//execute query {affect_row = Mysql_affected_rows (&mysql);
       If the SQL statement is executed, the number of rows that are affected is greater than 0 if (Affect_row > 0) return 0;
        else {   printf ("MySQL performs update student record failure!\n");
       return-1;
        } else {fprintf (stderr, "MySQL update student record failed!\n");
    return-1;
    Query the current record number and student number in the database (int *ret_id,int *num) {char query[100]; showcurstunumsusemysql

    Long Affect_row =-1; Mysql_res * result = NULL; Save result set Mysql_row ROW;

    Represents a row//In the result set, consisting of a query SQL statement sprintf (query, "%s", "SELECT ID from students"); if (Querybymysql (query,&result) = = 0)//Execute Query {while (row = Mysql_fetch_row ()!= NULL)//Read result set Data, the next row is returned.
            Because the current cursor is in the first row "{Ret_id[*num] = atoi (row[0]) When the result set is saved;"       (*num) + +;

        Increase in the number of students}//release result set mysql_free_result (results);

    return 0;
        else {fprintf (stderr, "MySQL queries all student IDs failed!\n");
    return-1;
    }//exit system void exit (int *flag) {*flag = 1;            Mysql_close (&mysql); Close MySQL connection printf ("Exit the System, goodbye.")
\ n ");
 }

/* Name: middle-tier Operation function Description: This file is a number of middle-tier operation functions, the incoming interface up to receive the parameters (user selection), to the downward use of MySQL interface functions, execute MySQL statement and return the results.


These include: Adding a student record, deleting a student record, and so on. 
    * * #include <stdio.h> #include <conio.h>//To use the Getch () function #include "main.h"//Add a student record int AddRecord () {
    Char temp_str[100];
    Student Add_stu;
    int val = 0;

    char c = 0;

         Student number while (1) {printf ("Please enter the new student number (0-10000):");

         val = Inputtypeofint ();


         scanf ("%d", &val);
         if (Ifstunumexist (val) = = 0)//judgment number is already existing printf ("The school number already exists Oh, change the number bar!\n");
    else break;

    } add_stu.stu_num = val;
     Name printf ("Please enter the newly added student name (20 characters):");


     scanf ("%s", &add_stu.name);

        Age while (1) {printf ("Please enter the age of the new addition student (5-40):");

       val = Inputtypeofint ();

        scanf ("%d", &val);
        if (val>=5 && Val <=) break;
     else printf ("Student's age should be between 5-40 years old Oh!\n"); } add_stu.age = Val;
       Sex while (1) {printf ("Please enter the new Student gender (m\\m representative Male, w\\w):");
        c = GetChar ();

        scanf ("%c", &c);
            if (c = = ' m ' | | | c = = ' m ') {add_stu.sex = ' m ';
        Break
              else if (c = = ' W ' | | c = = ' W ') {add_stu.sex = ' W ';
        Break An else if (!isspace (c))//--->isspace () is a function in the standard library to determine whether a white-space character is a <---{printf ("I go, the student is male or
        Woman Ah!\n ");

        }//Grade while (1) {printf ("Please enter a new student's Grade (0-20):");
        val = Inputtypeofint ();

        scanf ("%d", &val);
        if (val>=0 && Val <=) break;
     else printf ("This student so diao, do not conform to the common sense of!\n");


    } Add_stu.grade = val;

    Class printf ("Please enter the new Student's Class (0-100):");

    val = Inputtypeofint ();
    scanf ("%d", &val);



    Add_stu._class = val;
   Add a record database operation///////if (addrecordusemysql (add_stu) = = 0) {printf ("Insert student record successful.)
        \ n ");
    return 0;
else return-1;

    //delete a student record int deleteonerecord (int *clc_flag) {*clc_flag = 1;
    char c = 0;

    int stu_num = 0,index =-1;

       Input number while (1) {printf ("This classmate, please enter the number of the student you want to delete (0-10000):");

       Stu_num = Inputtypeofint ();

        scanf ("%d", &stu_num);
          if (ifstunumexist (stu_num) = = 1) {printf ("This classmate, sorry, you look for the student does not exist!\n");

          printf ("Please re-enter or cancel this operation (press Esc key to cancel, other keys re-enter) ...");
          c = GetChar ();

          c = Getch ();
              if (Isesc (c) = = 0) {printf ("\ n Oops, cancel this operation Oh!\n");
              *clc_flag = 1;
          return-1;



        else printf ("\ n");

    else break; ///////////mysql Database Delete operation////////////if (deleterecordusemysql (stu_num) = = 0) {printf ("successfully deletes the specified record").
        \ n ");
    return 0; else {printf ("Delete record failed.")
        \ n "); Return-1;
    }///delete all records int Deleteallrecord () {char C; printf ("Do you really want to erase all the student records?")

    (y/y confirm deletion, n/n cancel return) "); while (scanf ("%c", &c)) {if (c = = ' Y ' | | | c = = ' Y ')} {/////Database delete operation//////if (D Eleteallrecordusemysql () = = 0) {printf ("successfully deletes all records.")
                \ n ");
            return 0; } else if (c = = ' n ' | | | c = = ' n ') {printf ("Cancel deletion.)

            \ n ");
        return-1;

} return 0;
    ///query displays a record int showonerecord (int *clc_flag) {int stu_num = 0,index =-1;
    int c = 0;

     Student Stu;

    *clc_flag = 1;

        Input number while (1) {printf ("This classmate, please enter the number of the student you want to query (0-10000):");

        Stu_num = Inputtypeofint ();

        scanf ("%d", &stu_num);
            if (ifstunumexist (stu_num) = = 1) {printf ("This classmate, sorry, you look for the student does not exist!\n");

          printf ("Please re-enter or cancel this operation (press Esc key to cancel, other keys re-enter) ...");            c = GetChar ();
     Accept Carriage Return     c = Getch ();
              Determines whether to press the ESC key if (Isesc (c) = = 0) {printf ("\ n Oops, cancel this operation Oh!\n");
              *clc_flag = 1;
          return false;
        else printf ("\ n"); else break;