数据库MySQL基础（四）：搭建一个简易的成绩管理系统

成绩管理系统

mysql> create database gradesystem;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gradesystem        |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.04 sec)

mysql> use gradesystem;
Database changed
mysql> show tables;
Empty set

---

【1】学生表

学生表（student）：

• 学生ID：sid
• 学生姓名：sname
• 学生性别：gender

sid	sname	gender
1	Tom	male
2	Jack	male
3	Rose	female

mysql> create table student
    -> (
    -> sid int NOT NULL auto_increment,
    -> sname varchar(20) NOT NULL,
    -> gender varchar(10) NOT NULL,
    -> primary key(sid)
    -> );
Query OK, 0 rows affected (0.26 sec)

---

【2】课程表

课程表（course）：

• 课程ID：cid
• 课程名：cname

cid	cname
1	math
2	physics
3	chemistry

mysql> create table course
    -> (
    -> cid int NOT NULL auto_increment,
    -> cname varchar(20) NOT NULL,
    -> primary key(cid)
    -> );
Query OK, 0 rows affected (0.22 sec)

---

【3】成绩表

成绩表（mark）：

• 成绩ID：mid
• 学生ID：sid
• 课程ID：cid
• 成绩：score

mid	sid	cid	score
1	1	1	80
2	2	1	85
3	3	1	60
4	1	2	60
5	2	2	90
6	3	2	50

mysql> create table mark
    -> (
    -> mid int NOT NULL auto_increment,
    -> sid int NOT NULL,
    -> cid int NOT NULL,
    -> score int NOT NULL,
    -> foreign key(sid) references student(sid),
    -> foreign key(cid) references course(cid),
    -> primary key(mid)
    -> );
Query OK, 0 rows affected (0.28 sec)

外键（foreign key）的使用，目标字段一定要在前面先定义，再在后面设置外键索引

---

mysql> show tables;
+-----------------------+
| Tables_in_gradesystem |
+-----------------------+
| course                |
| mark                  |
| student               |
+-----------------------+
3 rows in set (0.05 sec)

mysql> insert into student values(1, 'Tom', 'male'), (2, 'Jack', 'male'), (3, 'Rose', 'female');
mysql> select * from student;
+-----+-------+--------+
| sid | sname | gender |
+-----+-------+--------+
|   1 | Tom   | male   |
|   2 | Jack  | male   |
|   3 | Rose  | female |
+-----+-------+--------+
3 rows in set (0.08 sec)

mysql> insert into course values(1, 'math'), (2, 'physics'), (3, 'chemistry');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from course
    -> ;
+-----+-----------+
| cid | cname     |
+-----+-----------+
|   1 | math      |
|   2 | physics   |
|   3 | chemistry |
+-----+-----------+
3 rows in set (0.05 sec)

mysql> insert into mark values(1, 1, 1, 80),(2, 2, 1, 85),(3, 3, 1, 90), (4, 1, 2, 60),(5, 2, 2, 90), (6, 3, 2, 95);