数据库实验---江西中医药大学

实验五：

# （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 表。

这次就写这么多，大家如果需要实验九在评论区说，如果人多考虑出一期教程。