-- 综合案例-学生选课系统
-- 学生选课管理系统用来帮助高校学生选修课程，
-- 学生通过系统可以查看所有选修课程的相关信息，包括课程名、学时、学分、
-- 也可以查看相关授课教师的信息，包括教师姓名、性别、学历、职称，
-- 还可以通过系统查看自己的考试成绩。
-- 教师通过系统可以查看选修自己课程的学生的信息，包括学号、姓名、性别、出生日期、班级，
-- 也可以通过系统录入学生的考试成绩。
-- 一个学生可以选修多门课程，一个课程也可被多名学生选修，一门课程可由多名教师承担。

-- 3创建数据库xsxk
-- 3.1创建前查询服务器上数据库
drop database if exists xsxk;
-- 3.2创建数据库xsxk
create database if not exists xsxk;
-- 3.3创建后查询创建结果
show databases;
-- 3.4切换到xsxk
use xsxk;

-- 4请根据以下需要存储的数据，设计各表结构即表中各列定义。
-- 4.1学生信息
DROP TABLE IF EXISTS  student;
CREATE TABLE student(
	sid CHAR(8),					--  COMMENT '学生编号' 
	sname VARCHAR(50) ,				--  COMMENT '学生名字'
	ssex CHAR(4) ,					--  COMMENT '性别'
	sbirthday date ,				--  COMMENT '出生日期'
	sdepart VARCHAR(50)				--  COMMENT'专业'
);
-- 4.2教师信息
DROP TABLE IF EXISTS  teacher;
CREATE TABLE teacher(
    tid CHAR(4) ,					--  COMMENT '教师编号' ,
    tname VARCHAR(20) ,				--  COMMENT '教师名字' 
    tsex CHAR(4) ,					--  COMMENT '性别'
    tedu VARCHAR(20) ,				--  COMMENT'学历'
    tjob VARCHAR(15)				--  COMMENT'职位'
);
-- 4.3课程信息
DROP TABLE IF EXISTS  course;
CREATE TABLE course(
    cid CHAR(4),					--  COMMENT '课程编号'  
    cname VARCHAR(20),				--  COMMENT '课程名字'
    ctotals INT ,					--  COMMENT '课程节数'
    cpoint FLOAT ,					--  COMMENT '学分'
    tid CHAR(4) 					--  COMMENT '教师编号',   
);
-- 4.4选课信息
DROP TABLE IF EXISTS  sc;
CREATE TABLE sc(
    sid CHAR(8) ,					--  COMMENT '学生编号' 
    cid CHAR(4),					--  COMMENT '课程编号'  
    score FLOAT 					--  COMMENT '成绩',
);
-- 查看表结构
DESCRIBE student;
DESCRIBE teacher;
DESCRIBE course;
DESCRIBE sc;
-- 按如下要求进行表的创建，并在建表后展示表结构查看操作结果
-- 5.1学生表设学号为主键，要求学生姓名不能为空
ALTER TABLE student MODIFY sid CHAR(8) PRIMARY KEY;
ALTER TABLE student MODIFY sname VARCHAR(50) NOT NULL;

-- 5.2教师表设工号为主键，设置职称列默认值为“讲师”
ALTER TABLE teacher MODIFY tid CHAR(4) PRIMARY KEY;
ALTER TABLE teacher MODIFY tjob VARCHAR(15) DEFAULT '讲师';

-- 5.3为教师表教师姓名添加非空约束
ALTER TABLE teacher MODIFY tname VARCHAR(20) NOT NULL;

-- 5.4创建课程表，无需设置主键和外键

-- 5.5为课程表在第三列后添加一列remark，设置为自动增长
ALTER TABLE course ADD remark INT AFTER ctotals;
ALTER TABLE course MODIFY remark INT KEY AUTO_INCREMENT;

-- 5.6为课程表设置教师表的外键授课教师工号
ALTER TABLE course ADD CONSTRAINT FK_course_teacher FOREIGN KEY(tid) REFERENCES teacher(tid);

-- 5.7创建选课表，建立联合主键为课程号和学号，并设置外键。
ALTER TABLE sc ADD PRIMARY KEY(sid,cid);
ALTER TABLE sc ADD CONSTRAINT FK_course_student FOREIGN KEY(sid) REFERENCES student(sid);
-- 不用给课程号设置外键（目前课程表的主键是：remark）

-- 6插入表数据
-- 6.1依据4.1中学生信息插入学生表，要求批量方式完成
select * from student;
INSERT INTO student VALUES
	('10101001','张永峰','男','1993-08-01','电子商务101'),
	('10101002','何小丽','女','1992-11-03','电子商务101'),
	('10101003','张宇','男','1992-08-21','网络技术101'),
	('10102001','王斌','男','1991-07-14','网络技术101'),
	('10102002','包玉明','男','1993-11-15','网络技术101'),
	('10102003','孙平平','女','1992-02-27','网络技术101'),
	('11101001','刘淑芳','女','1994-06-10','电子商务111'),
	('11101002','王亚旭','男','1993-03-18','电子商务111'),
	('11101003','高磊','男','1993-05-11','电子商务111');
-- 6.1依据4.2中教师信息插入教师表，要求逐条插入，注意默认值使用
select * from teacher;
INSERT INTO teacher VALUE('t001','吴亚飞','男','本科',DEFAULT);
INSERT INTO teacher VALUE('t002','李琦','男','硕士研究生','副教授');
INSERT INTO teacher VALUE('t003','王艳红','女','硕士研究生',DEFAULT);
INSERT INTO teacher VALUE('t004','马志超','男','博士研究生','教授');
INSERT INTO teacher VALUE('t005','万丽','女','硕士研究生','助理讲师');

-- 6.1依据4.3中课程信息插入课程表，注意自动增长列及外键的使用
select * from course;
INSERT INTO course VALUES
	('c001','文学欣赏',40,DEFAULT,1.5,'t001'),
	('c002','中国历史文化',60,DEFAULT,2.0,'t003'),
	('c003','视频编辑',70,DEFAULT,2.5,'t002'),
	('c004','音乐欣赏',40,DEFAULT,1.5,'t005');
-- 6.1依据4.4中选课成绩信息插入选课表，要求批量方式完成
select * from sc;
INSERT INTO sc VALUES
	('10101001','c001',73),
	('10101001','c003',81),
	('10101001','c004',51),
	('10101002','c001',78),
	('10101003','c003',69),
	('10102001','c001',50),
	('10102002','c002',68),
	('10102002','c003',95),
	('10102002','c004',75),
	('10102003','c001',85),
	('10102003','c002',78),
	('10102003','c003',67),
	('10102003','c004',NULL),
	('11101001','c001',49),
	('11101001','c002',67),
	('11101001','c004',62),
	('11101002','c001',67),
	('11101002','c002',NULL),
	('11101003','c002',88),
	('11101003','c003',90),
	('11101003','c004',82);

-- 7删去课程表中的remark列
alter table course drop remark;

-- 8将以上四个表按“表名1”方式命名，复制得到备份，数据和原表一致
CREATE TABLE student1 SELECT * FROM student;
CREATE TABLE teacher1 SELECT * FROM teacher;
CREATE TABLE course1 SELECT * FROM course;
CREATE TABLE sc1 SELECT * FROM sc;

-- 9将以上四个表按“表名2”方式命名，复制出表结构
CREATE TABLE student2 SELECT * FROM student WHERE 1=0;
CREATE TABLE teacher2 SELECT * FROM teacher WHERE 1=0;
CREATE TABLE course2 SELECT * FROM course WHERE 1=0;
CREATE TABLE sc2 SELECT * FROM sc WHERE 1=0;

select * from student;
select * from student1;
select * from student2;

-- 关闭safe-update模式
set SQL_SAFE_UPDATES=0;
-- 10对教师表副本进行更新操作，将硕士研究生的职称均改为副教授
UPDATE teacher1 SET tjob='副教授' WHERE tedu='硕士研究生';

-- 11对课程表副本进行更新操作，将所有课程学分均改为4分
UPDATE course1 SET cpoint=4;

-- 12对学生表副本进行更新操作，将学号为10102003的同学，姓名改为“孙平”，性别改为“男”，
-- 出生日期改为“1994-3-24”，班级改为“电子商务111”
UPDATE student1 SET sname='孙平',ssex='女',sbirthday='1994-03-24',depart='电子商务111' WHERE sid='10102003';

-- 13对选课表副本进行删除操作，删去c001课程的相关信息
DELETE FROM sc1 WHERE cid='c001';

-- 14清空选课表副本
TRUNCATE TABLE sc1;

-- 16，导出4张表的结构和数据，并以excel文件形式保存
SHOW VARIABLES LIKE '%secure%'; -- 变量名secure_file_priv 对应的value ,
SELECT * FROM student INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/student.xls";
-- 将字符串的字段转换成gbk格式再导出
SELECT sid,convert((sname) using gbk),convert((ssex) using gbk),sbirthday,convert((sdepart) using gbk) FROM student 
INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/student.xls";
SELECT * FROM teacher INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/teacher.xls";
SELECT * FROM course INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/course.xls";
SELECT * FROM sc INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/sc.xls";

drop database if exists xsxk;
create database if not exists xsxk;
SHOW DATABASES;
use xsxk;

-- 学生信息
drop table if exists Student;
CREATE TABLE Student (
    StudentID INT,
    Name VARCHAR(5),
    Gender CHAR(2),
    DOB DATE,
    Class VARCHAR(50)
);

-- 教师信息
drop table if exists Teacher;
CREATE TABLE Teacher (
    TeacherID CHAR(4),
    Name VARCHAR(5),
    Gender CHAR(2),
    Degree VARCHAR(10),
    Title VARCHAR(10)
);

-- 课程信息
drop table if exists Course;
CREATE TABLE Course (
    CourseID VARCHAR(4),
    CourseName VARCHAR(30),
    RegularScore INT,
    Credits FLOAT(3,2),
    TeacherID CHAR(4)
);

-- 选课信息
drop table if exists Enrollment;
CREATE TABLE Enrollment (
    StudentID INT,
    CourseID VARCHAR(4),
    Score INT
);

-- 5.1学生表设学号为主键，要求学生姓名不能为空
ALTER TABLE Student 
MODIFY Name VARCHAR(5) NOT NULL,
ADD PRIMARY KEY (StudentID);
-- 5.2教师表设工号为主键，设置职称列默认值为“讲师”
ALTER TABLE Teacher 
MODIFY Title VARCHAR(10) DEFAULT '讲师',
ADD PRIMARY KEY (TeacherID);
-- 5.3为教师表教师姓名添加非空约束
ALTER TABLE Teacher 
MODIFY Name VARCHAR(5) NOT NULL;
-- 5.4创建课程表，无需设置主键和外键
-- 5.5为课程表在第三列后添加一列remark，设置为自动增长
ALTER TABLE Course 
ADD remark INT PRIMARY KEY AUTO_INCREMENT AFTER RegularScore;
-- 5.6为课程表设置教师表的外键授课教师工号
ALTER TABLE Course 
ADD FOREIGN KEY (TeacherID) REFERENCES Teacher(TeacherID);
-- 5.7创建选课表，建立联合主键为课程号和学号，并设置外键。
ALTER TABLE Enrollment
ADD PRIMARY KEY (StudentID, CourseID),
ADD FOREIGN KEY (StudentID) REFERENCES Student (StudentID);
-- ADD FOREIGN KEY (CourseID) REFERENCES Course (CourseID);

-- 6.1依据4.1中学生信息插入学生表，要求批量方式完成
INSERT INTO Student VALUES
('10101001', '张永峰', '男', '1993-08-01 00:00:00', '电子商务101'),
('10101002', '何小丽', '女', '1992-11-03 00:00:00', '电子商务101'),
('10101003', '张宇', '男', '1992-08-21 00:00:00', '电子商务101'),
('10102001', '主斌', '男', '1991-07-14 00:00:00', '网络技术101'),
('10102002', '包玉明', '男', '1993-11-15 00:00:00', '网络技术101'),
('10102003', '孙平平', '女', '1992-02-27 00:00:00', '网络技术101'),
('10102004', '翁静静', '女', '1992-05-09 00:00:00', '网络技术101'),
('11101001', '刘淑芳', '女', '1994-06-10 00:00:00', '电子商务111'),
('11101002', '主亚旭', '男', '1993-03-18 00:00:00', '电子商务111'),
('11101003', '高磊', '男', '1993-05-11 00:00:00', '电子商务111');
-- 6.1依据4.2中教师信息插入教师表，要求逐条插入，注意默认值使用
INSERT INTO Teacher VALUES ('t001', '吴亚飞', '男', '本科', DEFAULT);
INSERT INTO Teacher VALUES ('t002', '李琦', '男', '硕士研究生', '副教授');
INSERT INTO Teacher VALUES ('t003', '王艳红', '女', '硕士研究生', DEFAULT);
INSERT INTO Teacher VALUES ('t004', '马志超', '男', '博士研究生', '教授');
INSERT INTO Teacher VALUES ('t005', '万丽', '女', '硕士研究生', '助理讲师');
-- 6.1依据4.3中课程信息插入课程表，注意自动增长列及外键的使用
INSERT INTO Course (CourseID, CourseName, RegularScore, Credits, TeacherID) VALUES
('c001', '文学欣赏', 40, 1.5, 't001'),
('c002', '中国历史文化', 60, 2.0, 't003'),
('c003', '视频编辑', 70, 2.5, 't002'),
('c004', '音乐欣赏', 40, 1.5, 't005');

-- 6.1依据4.4中选课成绩信息插入选课表，要求批量方式完成
INSERT INTO Enrollment VALUES
(10101001, 'c001', 73),
(10101001, 'c003', 81),
(10101001, 'c004', 51),
(10101002, 'c001', 78),
(10101003, 'c003', 69),
(10102001, 'c001', 50),
(10102002, 'c002', 68),
(10102002, 'c003', 95),
(10102002, 'c004', 75),
(10102003, 'c001', 85),
(10102003, 'c002', 78),
(10102003, 'c003', 67),
(10102003, 'c004', DEFAULT),
(11101001, 'c001', 49),
(11101001, 'c002', 67),
(11101001, 'c004', 62),
(11101002, 'c001', 67),
(11101002, 'c002', DEFAULT),
(11101003, 'c002', 88),
(11101003, 'c003', 90),
(11101003, 'c004', 82);

-- 7删去课程表中的remark列
ALTER TABLE Course DROP COLUMN remark;

-- 8将以上四个表按“表名1”方式命名，复制得到备份，数据和原表一致
CREATE TABLE Student1 LIKE Student;
CREATE TABLE Teacher1 LIKE Teacher;
CREATE TABLE Course1 LIKE Course;
CREATE TABLE Enrollment1 LIKE Enrollment;

INSERT INTO Student1 SELECT * FROM Student;
INSERT INTO Teacher1 SELECT * FROM Teacher;
INSERT INTO Course1 SELECT * FROM Course;
INSERT INTO Enrollment1 SELECT * FROM Enrollment;

-- 9将以上四个表按“表名2”方式命名，复制出表结构
CREATE TABLE Student2 LIKE Student;
CREATE TABLE Teacher2 LIKE Teacher;
CREATE TABLE Course2 LIKE Course;
CREATE TABLE Enrollment2 LIKE Enrollment;

-- 10对教师表副本进行更新操作，将硕士研究生的职称均改为副教授
-- 禁用安全模式
SET SQL_SAFE_UPDATES=0;
UPDATE Teacher1 SET Title = '副教授' WHERE Degree = '硕士研究生';

-- 11对课程表副本进行更新操作，将所有课程学分均改为4分
UPDATE Course1 SET Credits = 4.0;

-- 12对学生表副本进行更新操作，将学号为10102003的同学，姓名改为“孙平”，性别改为“男”，出生日期改为“1994-3-24”，班级改为“电子商务111”
UPDATE Student1 SET Name = '孙平', Gender = '男', DOB = '1994-3-24', Class = '电子商务111' WHERE StudentID = 10102003;

-- 13对选课表副本进行删除操作，删去c001课程的相关信息
DELETE FROM Enrollment1 WHERE CourseID = 'C001';

-- 14清空选课表副本
TRUNCATE TABLE Enrollment1;

-- 15将以上正确执行的命令行，保存成脚本文件。