Eva_J 博客链接: https://www.cnblogs.com/Eva-J/articles/10544358.html
表结构如下:
创建表结构及插入数据sql:
/*
数据导入：
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE
IF
EXISTS `class`;
CREATE TABLE `class` ( `cid` INT ( 11 ) NOT NULL AUTO_INCREMENT, `caption` VARCHAR ( 32 ) NOT NULL, PRIMARY KEY ( `cid` ) ) ENGINE = INNODB AUTO_INCREMENT = 5 DEFAULT CHARSET = utf8;-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class`
VALUES
( '1', '三年二班' ),
( '2', '三年三班' ),
( '3', '一年二班' ),
( '4', '二年九班' );
COMMIT;-- ----------------------------
-- Table structure for `course`
-- ----------------------------
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 = 5 DEFAULT CHARSET = utf8;-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course`
VALUES
( '1', '生物', '1' ),
( '2', '物理', '2' ),
( '3', '体育', '3' ),
( '4', '美术', '2' );
COMMIT;-- ----------------------------
-- Table structure for `score`
-- ----------------------------
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,
`num` 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 = 53 DEFAULT CHARSET = utf8;-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score`
VALUES
( '1', '1', '1', '10' ),
( '2', '1', '2', '9' ),
( '5', '1', '4', '66' ),
( '6', '2', '1', '8' ),
( '8', '2', '3', '68' ),
( '9', '2', '4', '99' ),
( '10', '3', '1', '77' ),
( '11', '3', '2', '66' ),
( '12', '3', '3', '87' ),
( '13', '3', '4', '99' ),
( '14', '4', '1', '79' ),
( '15', '4', '2', '11' ),
( '16', '4', '3', '67' ),
( '17', '4', '4', '100' ),
( '18', '5', '1', '79' ),
( '19', '5', '2', '11' ),
( '20', '5', '3', '67' ),
( '21', '5', '4', '100' ),
( '22', '6', '1', '9' ),
( '23', '6', '2', '100' ),
( '24', '6', '3', '67' ),
( '25', '6', '4', '100' ),
( '26', '7', '1', '9' ),
( '27', '7', '2', '100' ),
( '28', '7', '3', '67' ),
( '29', '7', '4', '88' ),
( '30', '8', '1', '9' ),
( '31', '8', '2', '100' ),
( '32', '8', '3', '67' ),
( '33', '8', '4', '88' ),
( '34', '9', '1', '91' ),
( '35', '9', '2', '88' ),
( '36', '9', '3', '67' ),
( '37', '9', '4', '22' ),
( '38', '10', '1', '90' ),
( '39', '10', '2', '77' ),
( '40', '10', '3', '43' ),
( '41', '10', '4', '87' ),
( '42', '11', '1', '90' ),
( '43', '11', '2', '77' ),
( '44', '11', '3', '43' ),
( '45', '11', '4', '87' ),
( '46', '12', '1', '90' ),
( '47', '12', '2', '77' ),
( '48', '12', '3', '43' ),
( '49', '12', '4', '87' ),
( '52', '13', '3', '87' );
COMMIT;-- ----------------------------
-- Table structure for `student`
-- ----------------------------
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 = 17 DEFAULT CHARSET = utf8;-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student`
VALUES
( '1', '男', '1', '理解' ),
( '2', '女', '1', '钢蛋' ),
( '3', '男', '1', '张三' ),
( '4', '男', '1', '张一' ),
( '5', '女', '1', '张二' ),
( '6', '男', '1', '张四' ),
( '7', '女', '2', '铁锤' ),
( '8', '男', '2', '李三' ),
( '9', '男', '2', '李一' ),
( '10', '女', '2', '李二' ),
( '11', '男', '2', '李四' ),
( '12', '女', '3', '如花' ),
( '13', '男', '3', '刘三' ),
( '14', '男', '3', '刘一' ),
( '15', '女', '3', '刘二' ),
( '16', '男', '3', '刘四' );
COMMIT;-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
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 = 6 DEFAULT CHARSET = utf8;-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher`
VALUES
( '1', '张磊老师' ),
( '2', '李平老师' ),
( '3', '刘海燕老师' ),
( '4', '朱云海老师' ),
( '5', '李杰老师' );
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT
View Code