/*
创建表
*/
/*年级表*/
DROP TABLE IF EXISTS `class_grade`;
CREATE TABLE `class_grade` (
`gid` int(11) NOT NULL AUTO_INCREMENT,
`gname` varchar(32) NOT NULL,
PRIMARY KEY (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*班级表*/
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
`grade_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_class_grade` (`grade_id`),
CONSTRAINT `fk_class_grade` FOREIGN KEY (`grade_id`) REFERENCES `class_grade` (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*老师表*/
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*课程表*/
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*学生表*/
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*成绩表*/
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*班级任职表:*/
DROP TABLE IF EXISTS `teach2cls`;
CREATE TABLE `teach2cls`(
`tcid` int(11) NOT NULL AUTO_INCREMENT,
`tid` int(11) NOT NULL,
`cid` int(11) NOT NULL,
PRIMARY KEY (`tcid`),
KEY `fk_teach2cls_class` (`cid`),
KEY `fk_teach2cls_teacher` (`tid`),
CONSTRAINT `fk_teach2cls_class` FOREIGN KEY (`cid`) REFERENCES `class` (`cid`),
CONSTRAINT `fk_teach2cls_teacher` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*
验证创建表和数据
*/
mysql> use db4;
Database changed
mysql> desc class;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| cid | int(11) | NO | PRI | NULL | auto_increment |
| caption | varchar(32) | NO | | NULL | |
| grade_id | int(11) | NO | MUL | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> select * from class;
+-----+--------------+----------+
| cid | caption | grade_id |
+-----+--------------+----------+
| 1 | 一年一班 | 1 |
| 2 | 二年一班 | 2 |
| 3 | 三年二班 | 3 |
| 4 | 二年二班 | 2 |
+-----+--------------+----------+
4 rows in set (0.00 sec)
mysql>
mysql> desc student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| gender | char(1) | NO | | NULL | |
| class_id | int(11) | NO | MUL | NULL | |
| sname | varchar(32) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> select * from student limit 5;
+-----+--------+----------+-----------+
| sid | gender | class_id | sname |
+-----+--------+----------+-----------+
| 1 | 女 | 1 | 乔丹 |
| 2 | 女 | 1 | 艾弗森 |
| 3 | 男 | 2 | 科比 |
| 4 | 男 | 1 | 张一 |
| 5 | 女 | 1 | 张二 |
+-----+--------+----------+-----------+
5 rows in set (0.00 sec)
mysql> desc teacher;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| tname | varchar(32) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> select * from teacher;
+-----+-----------+
| tid | tname |
+-----+-----------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 朱云海 |
| 5 | 李杰 |
+-----+-----------+
5 rows in set (0.00 sec)
mysql> desc course;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| cid | int(11) | NO | PRI | NULL | auto_increment |
| cname | varchar(32) | NO | | NULL | |
| teacher_id | int(11) | NO | MUL | NULL | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> select * from course;
+-----+--------+------------+
| cid | cname | teacher_id |
+-----+--------+------------+
| 1 | 生物 | 1 |
| 2 | 体育 | 1 |
| 3 | 物理 | 2 |
| 4 | 美术 | 2 |
+-----+--------+------------+
4 rows in set (0.00 sec)
mysql> desc score;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| student_id | int(11) | NO | MUL | NULL | |
| course_id | int(11) | NO | MUL | NULL | |
| score | int(11) | NO | | NULL | |
+------------+---------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> select * from score limit 5;
+-----+------------+-----------+-------+
| sid | student_id | course_id | score |
+-----+------------+-----------+-------+
| 1 | 1 | 1 | 60 |
| 2 | 1 | 2 | 59 |
| 3 | 2 | 2 | 99 |
| 6 | 2 | 1 | 8 |
| 8 | 2 | 3 | 68 |
+-----+------------+-----------+-------+
5 rows in set (0.00 sec)
mysql> desc class_grade;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| gid | int(11) | NO | PRI | NULL | auto_increment |
| gname | varchar(32) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> select * from class_grade;
+-----+-----------+
| gid | gname |
+-----+-----------+
| 1 | 一年级 |
| 2 | 二年级 |
| 3 | 三年级 |
+-----+-----------+
3 rows in set (0.00 sec)
mysql> desc teach2cls;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| tcid | int(11) | NO | PRI | NULL | auto_increment |
| tid | int(11) | NO | MUL | NULL | |
| cid | int(11) | NO | MUL | NULL | |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> select * from teach2cls;
+------+-----+-----+
| tcid | tid | cid |
+------+-----+-----+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 3 | 2 |
+------+-----+-----+
4 rows in set (0.00 sec)