mysql20170404代码实现

CREATE DATABASE IF NOT EXISTS school;

USE school;

CREATE TABLE tblStudent(
StuId INT(4) NOT NULL PRIMARY KEY,
StuName VARCHAR(20) NOT NULL,
StuAge INT(3),
StuSex CHAR(2)

);

CREATE TABLE tblCourse(
CourseId INT(4) NOT NULL PRIMARY KEY,
CourseName VARCHAR(20) NOT NULL,
TeaId INT(4)

);

CREATE TABLE tblScore(
StuId INT(4) NOT NULL PRIMARY KEY,
CourseId INT(4),
Score DECIMAL(20)

);

CREATE TABLE tblTeacher(
TeaId INT(4)NOT NULL PRIMARY KEY,
TeaName VARCHAR(20)

);

DROP TABLE tblTeacher;

INSERT INTO tblstudent VALUES(1,‘张三‘,50,‘男‘),(2,‘李四‘,50,‘男‘),(3,‘王五‘,50,‘男‘);

INSERT INTO tblstudent VALUES(4,‘张小红‘,20,‘女‘),(5,‘李小三‘,20,‘女‘),(6,‘王小妞‘,20,‘女‘);

INSERT INTO tblcourse VALUES(1,‘大学语文‘,2),(2,‘大学英语‘,1),(3,‘芭蕾舞‘,2);

INSERT INTO tblcourse VALUES(4,‘大学体育‘,1),(5,‘游泳‘,3),(6,‘马克思主义哲学‘,2);

DELETE FROM tblcourse;

INSERT INTO tblteacher VALUES(1,‘小葱拌豆腐‘),(2,‘微冷的雨‘),(3,‘帅的离谱‘);

DELETE FROM tblteacher;

INSERT INTO tblscore VALUES(1,1,100),(2,2,99),(3,3,98),(4,1,100),(5,2,99),(6,3,98);

INSERT INTO tblcourse VALUES(4,‘大学体育‘,1),(5,‘游泳‘,3),(6,‘马克思主义哲学‘,2);

INSERT INTO tblscore VALUES(1,1,100),(1,2,99),(1,3,92),(1,4,80),(1,5,99),(1,6,61);
INSERT INTO tblscore VALUES(2,1,87),(2,2,99),(2,3,60),(2,4,100),(1,5,60),(2,6,98);
INSERT INTO tblscore VALUES(3,1,98),(3,2,85),(3,3,66),(3,4,72),(1,5,99),(3,6,98);
INSERT INTO tblscore VALUES(4,1,77),(4,2,99),(4,3,98),(4,4,99),(1,5,62),(4,6,88);
INSERT INTO tblscore VALUES(5,1,66),(5,2,95),(5,3,94),(5,4,80),(1,5,99),(5,6,98);
INSERT INTO tblscore VALUES(6,1,100),(6,2,99),(6,3,98),(6,4,92),(1,5,85),(6,6,70);

DELETE FROM tblscore;

ALTER TABLE tblcourse MODIFY COLUMN CourseId INT(3) ZEROFILL;