MySQL 创建基本数据并插入数据
Bitgeek 2022-07-06 MySQL
# 1.学生表
-- 1、学生表
-- Student(s_id,s_name,s_birth,s_sex) :学生编号、姓名、年月、性别
CREATE TABLE
IF NOT EXISTS `Student` (
`s_id` VARCHAR (20),
`s_name` VARCHAR (20) NOT NULL DEFAULT '',
`s_birth` VARCHAR (20) NOT NULL DEFAULT '',
`s_sex` VARCHAR (10) NOT NULL DEFAULT '',
PRIMARY KEY (`s_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据
INSERT INTO Student VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO Student VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO Student VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO Student VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO Student VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO Student VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO Student VALUES ('07', '郑竹', '1989-07-01', '女');
INSERT INTO Student VALUES ('08', '王菊', '1990-01-20', '女');
# 2.课程表
-- 2、课程表
-- Course(c_id,c_name,t_id) :课程编号、 课程名称、 教师编号
CREATE TABLE
IF NOT EXISTS `Course` (
`c_id` VARCHAR (20),
`c_name` VARCHAR (20) NOT NULL DEFAULT '',
`t_id` VARCHAR (20) NOT NULL,
PRIMARY KEY (`c_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据
INSERT INTO Course VALUES ('01', '语文', '02');
INSERT INTO Course VALUES ('02', '数学', '01');
INSERT INTO Course VALUES ('03', '英语', '03');
# 3.教师表
-- 3、教师表
-- Teacher(t_id,t_name) :教师编号、教师姓名
CREATE TABLE
IF NOT EXISTS `Teacher` (
`t_id` VARCHAR (20),
`t_name` VARCHAR (20) NOT NULL DEFAULT '',
PRIMARY KEY (`t_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据
INSERT INTO Teacher VALUES ('01', '张三');
INSERT INTO Teacher VALUES ('02', '李四');
INSERT INTO Teacher VALUES ('03', '王五');
# 4.成绩表
-- 4、成绩表
-- Score(s_id,c_id,s_score) :学生编号、课程编号、分数
CREATE TABLE
IF NOT EXISTS `Score` (
`s_id` VARCHAR (20),
`c_id` VARCHAR (20),
`s_score` INT (3),
PRIMARY KEY (`s_id`, `c_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据
INSERT INTO Score VALUES ('01', '01', 80);
INSERT INTO Score VALUES ('01', '02', 90);
INSERT INTO Score VALUES ('01', '03', 99);
INSERT INTO Score VALUES ('02', '01', 70);
INSERT INTO Score VALUES ('02', '02', 60);
INSERT INTO Score VALUES ('02', '03', 80);
INSERT INTO Score VALUES ('03', '01', 80);
INSERT INTO Score VALUES ('03', '02', 80);
INSERT INTO Score VALUES ('03', '03', 80);
INSERT INTO Score VALUES ('04', '01', 50);
INSERT INTO Score VALUES ('04', '02', 30);
INSERT INTO Score VALUES ('04', '03', 20);
INSERT INTO Score VALUES ('05', '01', 76);
INSERT INTO Score VALUES ('05', '02', 87);
INSERT INTO Score VALUES ('06', '01', 31);
INSERT INTO Score VALUES ('06', '03', 34);
INSERT INTO Score VALUES ('07', '02', 89);
INSERT INTO Score VALUES ('07', '03', 98);
