MYsql建立学生成绩表

mysql> show databases;

mysql> create database gradesystem;

mysql> use gradesystem;

mysql> create table tb_class
    -> (
    -> clid int not null comment '班级编号',
    -> clname varchar(20) not null comment '班级名称',
    -> primary key(clid)
    -> );

mysql> show tables;
+-----------------------+
| Tables_in_gradesystem |
+-----------------------+
| tb_class              |
+-----------------------+

mysql> create table tb_student
    -> (
    -> stuid int not null,
    -> stuname varchar(4) not null,
    -> clid int not null comment '班级编号',
    -> primary key(stuid)
    -> );

mysql> show tables;
+-----------------------+
| Tables_in_gradesystem |
+-----------------------+
| tb_class              |
| tb_student            |
+-----------------------+

mysql> create table tb_course
    -> (
    -> cid int not null comment '课程编号',
    -> cname varchar(20) not null comment '课程名称',
    ->  primary key (cid)
    -> );

mysql> show tables;
+-----------------------+
| Tables_in_gradesystem |
+-----------------------+
| tb_class              |
| tb_course             |
| tb_student            |
+-----------------------+

mysql> create table tb_mark(
    ->  mid int not null,
    ->  clid int not null comment '班级编号',
    ->  stuid int not null,
    ->  cid int not null comment '课程编号',
    ->  score decimal(4,1) comment '成绩',
    ->  primary key(mid)
    -> );

mysql> show tables;
+-----------------------+
| Tables_in_gradesystem |
+-----------------------+
| tb_class              |
| tb_course             |
| tb_mark               |
| tb_student            |
+-----------------------+


//ALTER TABLE <数据表名> add constraint FK_主表_从表 foreign key (外键字段) references 主表(主表主键字段);
//ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);


mysql> alter table tb_student add constraint uni_id unique(stuid);



mysql> alter table tb_student add constraint fk_student_clid foreign key(clid) references tb_class(clid);

mysql> alter table tb_mark add constraint fk_mark_clid foreign key(clid) referen
ces tb_class(clid);

mysql> alter table tb_mark add constraint fk_mark_stuid foreign key(stuid) refer
ences tb_student(stuid);

mysql> alter table tb_mark add constraint fk_mark_cid foreign key(cid) reference
s tb_course(cid);


mysql>  insert into tb_course(cid,cname)values
    ->  (1,'C++程序设计'),
    ->  (2,'多媒体技术'),
    ->  (3,'大学英语'),
    ->  (4,'高等数学'),
    ->  (5,'大学体育'),
    ->  (6,'马克思主义政治经济学');

mysql> insert into tb_class(clid,clname)values
    -> (1,'一班'),
    -> (2,'二班'),
    -> (3,'三班'),
    -> (4,'四班'),
    -> (5,'五班'),
    -> (6,'六班'),
    -> (7,'七班'),
    -> (8,'八班'),
    -> (9,'九班'),
    -> (10,'十班');

mysql> insert into tb_student(stuid,stuname,clid)values
    -> (001,'张三',1),
    -> (002,'李四',1),
    -> (003,'王二',1);



//如果该字段不是主键，需要先设置该字段为主键：

//alter table 表名 add primary key(字段名);

//修改字段为自动增长

//alter table 表名 change 字段名 字段名 字段类型 auto_increment;



mysql> alter table tb_mark change mid mid int not null auto_increment;

mysql> insert into tb_mark(clid,stuid,cid,score)values
    -> (1,001,1,80),
    -> (1,001,2,88),
    -> (1,001,3,71),
    -> (1,001,4,60),
    -> (1,001,5,66),
    -> (1,001,6,91),
    -> (1,002,1,77),
    -> (1,002,2,73),
    -> (1,002,3,84),
    -> (1,002,4,93),
    -> (1,002,5,64),
    -> (1,002,6,91),
    -> (1,003,1,97),
    -> (1,003,2,89),
    -> (1,003,3,81),
    -> (1,003,4,79),
    -> (1,003,5,93),
    -> (1,003,6,88);

mysql> show tables;
+-----------------------+
| Tables_in_gradesystem |
+-----------------------+
| tb_class              |
| tb_course             |
| tb_mark               |
| tb_student            |
+-----------------------+

mysql> select * from tb_class;
+------+--------+
| clid | clname |
+------+--------+
|    1 | 一班   |
|    2 | 二班   |
|    3 | 三班   |
|    4 | 四班   |
|    5 | 五班   |
|    6 | 六班   |
|    7 | 七班   |
|    8 | 八班   |
|    9 | 九班   |
|   10 | 十班   |
+------+--------+

mysql> select * from tb_course;
+-----+--------------------------------+
| cid | cname                          |
+-----+--------------------------------+
|   1 | C++程序设计                    |
|   2 | 多媒体技术                     |
|   3 | 大学英语                       |
|   4 | 高等数学                       |
|   5 | 大学体育                       |
|   6 | 马克思主义政治经济学           |
+-----+--------------------------------+

mysql> select *from tb_mark;
+-----+------+-------+-----+-------+
| mid | clid | stuid | cid | score |
+-----+------+-------+-----+-------+
|   2 |    1 |     1 |   1 |  80.0 |
|   3 |    1 |     1 |   2 |  88.0 |
|   4 |    1 |     1 |   3 |  71.0 |
|   5 |    1 |     1 |   4 |  60.0 |
|   6 |    1 |     1 |   5 |  66.0 |
|   7 |    1 |     1 |   6 |  91.0 |
|   8 |    1 |     2 |   1 |  77.0 |
|   9 |    1 |     2 |   2 |  73.0 |
|  10 |    1 |     2 |   3 |  84.0 |
|  11 |    1 |     2 |   4 |  93.0 |
|  12 |    1 |     2 |   5 |  64.0 |
|  13 |    1 |     2 |   6 |  91.0 |
|  14 |    1 |     3 |   1 |  97.0 |
|  15 |    1 |     3 |   2 |  89.0 |
|  16 |    1 |     3 |   3 |  81.0 |
|  17 |    1 |     3 |   4 |  79.0 |
|  18 |    1 |     3 |   5 |  93.0 |
|  19 |    1 |     3 |   6 |  88.0 |
+-----+------+-------+-----+-------+

mysql> select * from tb_student;
+-------+---------+------+
| stuid | stuname | clid |
+-------+---------+------+
|     1 | 张三    |    1 |
|     2 | 李四    |    1 |
|     3 | 王二    |    1 |
+-------+---------+------+


mysql> select tb_student.stuname,tb_mark.score,tb_mark.cid from tb_student,tb_mark where tb_student.stuid=tb_mark.stuid;
+---------+-------+-----+
| stuname | score | cid |
+---------+-------+-----+
| 张三    |  80.0 |   1 |
| 张三    |  88.0 |   2 |
| 张三    |  71.0 |   3 |
| 张三    |  60.0 |   4 |
| 张三    |  66.0 |   5 |
| 张三    |  91.0 |   6 |
| 李四    |  77.0 |   1 |
| 李四    |  73.0 |   2 |
| 李四    |  84.0 |   3 |
| 李四    |  93.0 |   4 |
| 李四    |  64.0 |   5 |
| 李四    |  91.0 |   6 |
| 王二    |  97.0 |   1 |
| 王二    |  89.0 |   2 |
| 王二    |  81.0 |   3 |
| 王二    |  79.0 |   4 |
| 王二    |  93.0 |   5 |
| 王二    |  88.0 |   6 |
+---------+-------+-----+