实验五:
# (1)将表3.5、表3.6、表3.7的记录分别插入到Doctor表、Patient表以及CureFee表中;
-- 插入Doctor表记录
INSERT INTO doctor VALUES ('1', '张医生','男', '27','内科','no');
INSERT INTO doctor VALUES ('2', '李医生','男', '20','外科','no');
INSERT INTO doctor VALUES ('3', '王医生', '男','30','妇科','no');
-- 插入Patient表记录
INSERT INTO patient VALUES ('1', '张三', '男', '19','no','1');
INSERT INTO Patient VALUES ('2', '李四', '女', '19','no','1');
INSERT INTO Patient VALUES ('3', '王五', '男', '19','no','1');
-- 插入CureFee表记录
INSERT INTO CureFee VALUES ('1', 'd1', 100);
INSERT INTO CureFee VALUES ('1', 'd2', 200);
INSERT INTO CureFee VALUES ('2', 'd2', 300);
INSERT INTO CureFee VALUES ('3', 'd3', 400);
# (2)新建一张表,并将所有医生的ID、其接诊病人的总费用存入到该表中;
-- 创建DoctorFee表
CREATE TABLE DoctorFee (
DoctorID VARCHAR(10) PRIMARY KEY,
TotalFee INT
);
-- 将所有医生的ID和总费用插入DoctorFee表
INSERT INTO DoctorFee (DoctorID, TotalFee)
SELECT DoctorID, SUM(Fee) AS TotalFee
FROM CureFee
GROUP BY DoctorID;
# (3)给每个病人的总费用优惠5%;
-- 更新CureFee表,将每个病人的费用减去5%
UPDATE CureFee SET Fee = Fee * 0.95;
# (4)删除“内科”医生的诊治记录;
-- 先查询内科医生的ID
SELECT DoctorID FROM Doctor WHERE Department = '内科';
-- 删除CureFee表中该医生的记录
DELETE FROM CureFee WHERE DoctorID = 'd1';
# (5)试着从Doctor表删除医生ID为“d2”的医生,是否会出错,为什么?
-- 查询Doctor表和CureFee表中是否有医生ID为"d2"的记录
SELECT * FROM Doctor WHERE DoctorID = 'd2';
SELECT * FROM CureFee WHERE DoctorID = 'd2';
-- 如果有,先删除CureFee表中的记录
DELETE FROM CureFee WHERE DoctorID = 'd2';
-- 再删除Doctor表中的记录
DELETE FROM Doctor WHERE DoctorID = 'd2';
# 加强联系
# 1) 给每张表插入三条记录;
INSERT INTO student(Sno,Sname,Sage,Ssex,Sdept) VALUES('10001','张三',20,'男','CS');
INSERT INTO student(Sno,Sname,Sage,Ssex,Sdept) VALUES('10002','李四',21,'女','MA');
INSERT INTO student(Sno,Sname,Sage,Ssex,Sdept) VALUES('10003','王五',22,'男','EE');
INSERT INTO course(Tno,Tname,Tsex,Tage,Ttitle) VALUES('101','张老师','男',35,'教授');
INSERT INTO course(Tno,Tname,Tsex,Tage,Ttitle) VALUES('102','李老师','女',40,'副教授');
INSERT INTO course(Tno,Tname,Tsex,Tage,Ttitle) VALUES('103','王老师','男',30,'讲师');
INSERT INTO sc(Sno,Cno,Grade) VALUES('10001','001',80);
INSERT INTO sc(Sno,Cno,Grade) VALUES('10002','002',85);
INSERT INTO sc(Sno,Cno,Grade) VALUES('10003','003',90);
# (2) 新建一张表,并将所有学生的学号、平均成绩增加到其中;
CREATE TABLE StudentAvgScore(
Sno CHAR(8) PRIMARY KEY,
AvgScore FLOAT(4)
);
INSERT INTO StudentAvgScore(Sno,AvgScore)
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno;
# (3) 假设所有的学生都选了“008”课程,请将记录一次性插入到SC表中;
INSERT INTO SC(Sno,Cno,Grade)
SELECT Sno,'008',0
FROM student;
# (4) 将 “CS”系成绩不及格学生的成绩加5分;
UPDATE SC
SET Grade = Grade + 5
WHERE Cno IN (
SELECT Cno FROM course WHERE Tno IN (
SELECT Tno
FROM teacher
WHERE Ttitle = '教授' AND Tdept = 'CS'
)
) AND Grade < 60;
实验六
# (1) 建立“呼吸科”医生视图B_D;
CREATE VIEW B_D AS
SELECT *
FROM DOCTOR
WHERE DEPT = '呼吸科';
# (2) 在B_D视图的基础上查询“呼吸科”的“主任医师”的基本信息;
SELECT *
FROM B_D
WHERE TITLE = '主任医师';
# (3) 在B_D视图的基础上更改“李灿”医生的助手为“d3”。
UPDATE B_D
SET ASSISTANT = 'd3'
WHERE NAME = '李灿';
# (1) 建立信息系学生的视图:
# 并向该视图中插入类似以下的语句:
# 是否能插入成功,为什么,如何更改?
CREATE VIEW IS_student AS
SELECT *
FROM Student
WHERE Sdept = 'IS'
WITH CHECK OPTION;
INSERT INTO IS_student (Sno, Sname, Sage)
VALUES ('20121100101', '王一', 20);
# (2) 建立“CS”系学生的视图CS_S;
CREATE VIEW CS_S AS
SELECT *
FROM Student
WHERE Sdept = 'CS';
# (3) 在CS_S视图的基础上查询“CS”系男生的基本信息;
SELECT *
FROM CS_S
WHERE Sgender = '男';
# (4) 在CS_S视图的基础上更改“张三”的出生年月为“1995-02-05”;
UPDATE CS_S
SET Sbirthday = '1995-02-05'
WHERE Sname = '张三';
# (5) 建立“IS”系选修了“001”号课程的学生的视图IS_SC;
CREATE VIEW IS_SC AS
SELECT Student.*
FROM Student, SC
WHERE Student.Sno = SC.Sno AND SC.Cno = '001' AND Student.Sdept = 'IS';
# (6) 在视图IS_S的基础上建立信息系选修了1号课程且成绩在90分以上的学生的视图IS_SC_C;
CREATE VIEW IS_SC_C AS
SELECT *
FROM IS_SC
WHERE Grade >= 90;
# (7) 删除“选修”表(SC表),看看IS_SC视图以及IS_SC_C是否存在。
DROP TABLE SC;
-- 此时查询 IS_SC 和 IS_SC_C 视图会报错,因为它们依赖于已删除的 SC 表。
这次就写这么多,大家如果需要实验九在评论区说,如果人多考虑出一期教程。