-- 第 15 章实验数据：教材 teaching 库的五张表和教材中的数据
-- 整段执行一次即可。五次实验共用，想从头再做时重新执行。
CREATE DATABASE IF NOT EXISTS teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;
-- 删除本章实验建立的存储过程和函数
DROP PROCEDURE IF EXISTS s_count;
DROP PROCEDURE IF EXISTS info_count;
DROP PROCEDURE IF EXISTS pass_or_fail;
DROP PROCEDURE IF EXISTS sum100;
DROP PROCEDURE IF EXISTS max_i;
DROP PROCEDURE IF EXISTS odd_sum;
DROP PROCEDURE IF EXISTS showstudentsnums;
DROP PROCEDURE IF EXISTS cal_courses;
DROP PROCEDURE IF EXISTS dept_students;
DROP PROCEDURE IF EXISTS cal_students;
DROP PROCEDURE IF EXISTS double_it;
DROP PROCEDURE IF EXISTS fetch3;
DROP PROCEDURE IF EXISTS dept_names;
DROP FUNCTION IF EXISTS cal_depts;
DROP FUNCTION IF EXISTS isprime;
DROP FUNCTION IF EXISTS get_level;
-- 删除旧的表。先暂时关闭外键检查，库里有别的表引用它们时也能删除
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS sc, tc, avgsal, classroom, s, c, t;
SET FOREIGN_KEY_CHECKS = 1;
-- 课程表
CREATE TABLE c (
cno CHAR(4) NOT NULL,
cn VARCHAR(30) NOT NULL,
ct INT,
PRIMARY KEY (cno)
);
-- 学生表
CREATE TABLE s (
sno CHAR(4) NOT NULL,
sn VARCHAR(20) NOT NULL,
sex CHAR(2),
age INT,
maj VARCHAR(30),
dept VARCHAR(30),
PRIMARY KEY (sno)
);
-- 教师表
CREATE TABLE t (
tno CHAR(4) NOT NULL,
tn VARCHAR(20) NOT NULL,
sex CHAR(2),
age INT,
prof VARCHAR(20),
maj VARCHAR(30),
dept VARCHAR(30),
sal DECIMAL(8,2),
PRIMARY KEY (tno)
);
-- 选课表
CREATE TABLE sc (
sno CHAR(4) NOT NULL,
cno CHAR(4) NOT NULL,
score DECIMAL(5,2),
PRIMARY KEY (sno, cno),
FOREIGN KEY (sno) REFERENCES s (sno),
FOREIGN KEY (cno) REFERENCES c (cno)
);
-- 授课表
CREATE TABLE tc (
tno CHAR(4) NOT NULL,
cno CHAR(4) NOT NULL,
tcdate DATE,
PRIMARY KEY (tno, cno),
FOREIGN KEY (tno) REFERENCES t (tno),
FOREIGN KEY (cno) REFERENCES c (cno)
);
INSERT INTO c VALUES
('c1','Java程序设计',40), ('c2','程序设计基础',48), ('c3','线性代数',48),
('c4','数据结构',64), ('c5','数据库系统',56), ('c6','数据挖掘',32),
('c7','高等数学',60), ('c8','控制理论',32);
INSERT INTO s VALUES
('s1','王彤','女',18,'计算机','信息学院'),
('s2','苏乐','男',20,'信息','信息学院'),
('s3','林欣怡','女',19,'信息','信息学院'),
('s4','陶然','男',19,'自动化','工学院'),
('s5','魏立','男',17,'数学','理学院'),
('s6','何欣荣','女',21,'计算机','信息学院'),
('s7','赵琳琳','女',19,'数学','理学院'),
('s8','李轩','男',18,'自动化','工学院');
INSERT INTO t VALUES
('t1','刘杨','男',38,'教授','计算机','信息学院',3800.00),
('t2','石丽','女',28,'讲师','计算机','信息学院',2800.00),
('t3','顾伟','男',35,'副教授','计算机','信息学院',3200.00),
('t4','赵礼','男',45,'教授','自动化','工学院',4267.90),
('t5','赵希希','女',33,'副教授','数学','理学院',3332.67),
('t6','张刚','男',30,'讲师','计算机','工学院',2600.00);
INSERT INTO sc VALUES
('s1','c1',90.50), ('s1','c2',88.00),
('s2','c4',70.00), ('s2','c5',57.00), ('s2','c6',81.50), ('s2','c7',NULL),
('s3','c1',75.00), ('s3','c2',76.50), ('s3','c4',85.00),
('s4','c1',93.00), ('s4','c2',82.00), ('s4','c6',NULL), ('s4','c7',68.00),
('s5','c2',79.00), ('s5','c7',84.00),
('s7','c3',77.00), ('s7','c5',62.00), ('s7','c7',100.00),
('s8','c2',66.00), ('s8','c3',96.00);
INSERT INTO tc VALUES
('t1','c1','2021-09-03'), ('t1','c2','2021-09-04'),
('t2','c5','2021-03-06'), ('t2','c6','2021-09-06'),
('t3','c2','2021-03-08'), ('t3','c4','2021-09-08'),
('t4','c3','2021-03-10'),
('t5','c7','2021-09-12'), ('t5','c8','2021-03-15');