Build a simple database of performance management systems

Topic

Set up

A simple database introduction to the performance management system

It is necessary to construct a simple database of performance management system to record the students ' achievements in several courses. There are three tables in the database for recording student information, course information, and score information, respectively.

The data for the database table is as follows:

Student Table (Student): Student ID, student name and gender

Curriculum: Course ID and course name

Score table: Result ID, student ID, course ID, and score

MySQL in the server is not started yet, please note that the default password for MySQL root account is empty.

Goal

1.MySQL service is in a running state

2. The name of the new database is Gradesystem

3.gradesystem consists of three tables: Student, course, Mark;

• The Student table contains 3 columns: SID (Primary key), sname, gender;
• Course table contains 2 columns: CID (primary key), CNAME;
• The Mark table contains 4 columns: Mid (primary key), Sid, Cid, score, and note the relationship to the other two table primary keys.

4. Insert the data from the above table into each table separately

Tips

• Note ID Auto-increment and key constraints when creating tables
• Each table INSERT statement can be completed with a single statement

Solution

Start MySQL:

sudo service MySQL startmysql-u root

Create a database named gradesystem :

Create Database Gradesystem;use Gradesystem;

To create a student table:

CREATE TABLE student (    SID Int (primary) key,    sname char (TEN),    Gender char (10));

To create a course table:

CREATE TABLE course (    CID Int (TEN) primary key,    sname char (10));

　　

To create a mark table:

CREATE TABLE mark (    mid int (TEN) primary key,    SID Int (TEN), foreign key (SID) references student (SID),    CID Int ( FOREIGN KEY (CID) References course (CID),    score int (100));

　　

studentInsert data:

INSERT into student values (1, ' Tom ', ' Male '), insert into student values (2, ' Jack ', ' Male '), insert into student values (3, ' Rose ', ' female ');

courseInsert data:

Insert into course values (1, ' math '), insert into course values (2, ' Physics '), insert into course values (3, ' chemistry ');

　　

markInsert data:

INSERT into Mark values (7,1,3,95), insert into Mark values (8,2,3,75), insert into Mark values (9,3,3,85);

　　

Finally, you can view these three tables:

SELECT * FROM student;/*+-----+-------+--------+| Sid | sname | Gender |+-----+-------+--------+|   1 | Tom   | male   | |   2 | Jack  | male   | |   3 | Rose  | female |+-----+-------+--------+*/select * from course;/*+-----+-----------+| cid | sname     |+-----+----- ------+|   1 | Math      | |   2 | Physics   | |   3 | Chemistry |+-----+-----------+*/select * FROM mark;/*+-----+------+------+-------+| Mid | Sid  | CID  | score |+-----+------+------+-------+|   7 |    1 |    3 |    | |   8 |    2 |    3 |    | |   9 |    3 |    3 |    |+-----+------+------+-------+*/

　　

Build a simple database of performance management systems