(2022级）成都工业学院数据库原理及应用实验六： SQL DML(增、删、改)

写在前面

1、基于2022级软件工程/计算机科学与技术实验指导书

2、成品仅提供参考

3、如果成品不满足你的要求，请寻求其他的途径

运行环境

window11家庭版

Navicat Premium 16

Mysql 8.0.36

实验要求

在实验三的基础上完成下列查询：

1、在科室表中增加一个二级科室，其科室编码为“10401”

2、在科室表中增加一个二级科室，其上级科室编码为“10600”。

3、在医生表中增加一个医生，要求其级别编码为7。

4、将“一级专家”诊疗费改为120。

5、将医生“万春”在2022年4月18日的值班时段调整为上午。

6、删除医生编号为‘D001’的值班记录。

7、删除医生‘朱强’在2022年4月18日的值班记录。

8、删除工作证号为“D016”的医生。

9、创建一个表work_count，包含两个属性工作证号、值班次数。使用子查询的形式将每个医生的工作证号、值班次数存入表work_count。从work_count查询出医生的工作证号、值班次数。

10、定义一个视图doctor_info,包含工作证号，医生姓名、科室编码、科室名称、医生级别编码、级别名称，其中工作证号来自Doctor表，科室编码来至department表,医生级别编码DoctorLevel表。使用该视图完成查询：查询医生姓名、科室名称、级别名称。

11、基于上题中定义的视图doctor_info，插入一个医生级别（7，‘测试1’）,插入一个医生（’D020’,’测试医生’），观察是否成功，并思考产生这种结果的原因?

实验过程

1、在科室表中增加一个二级科室，其科室编码为“10401”

INSERT into department VALUES("10401","新增科室1","2",NULL);
SELECT * FROM department

2、在科室表中增加一个二级科室，其上级科室编码为“10600”。

INSERT into department VALUES("12345","新增科室2","2",10600);
SELECT * FROM department

3、在医生表中增加一个医生，要求其级别编码为7。

SET FOREIGN_KEY_CHECKS = 0;-- 禁用外键
INSERT INTO doctor VALUES( '1234', '新增医生', '123456', '2022-02-02', 7, '躺平' );
SET FOREIGN_KEY_CHECKS = 1;-- 重新启用外键
SELECT * FROM doctor;

 

4、将“一级专家”诊疗费改为120。

UPDATE doctorlevel SET Drlv_fee="120" WHERE Drlv_no=1;
SELECT * FROM doctorlevel

5、将医生“万春”在2022年4月18日的值班时段调整为上午。

UPDATE doctor INNER JOIN schedule ON doctor.Dr_no=schedule.Dr_no SET Skd_seg=1 WHERE Dr_name="万春" AND Skd_Date="2022-4-18";
SELECT * FROM schedule

6、删除医生编号为‘D001’的值班记录。

DELETE FROM schedule WHERE Dr_no="D001";
SELECT * FROM schedule

7、删除医生‘朱强’在2022年4月18日的值班记录。

DELETE schedule FROM schedule INNER JOIN doctor ON schedule.Dr_no=doctor.Dr_no WHERE Dr_name="朱强" AND Skd_Date="2022-4-18";
SELECT * FROM schedule

8、删除工作证号为“D016”的医生。

SET FOREIGN_KEY_CHECKS = 0; -- 禁用外键
DELETE FROM doctor WHERE Dr_no = 'D016';
DELETE FROM schedule WHERE Dr_no = 'D016';
SET FOREIGN_KEY_CHECKS = 1; -- 重启外键
SELECT * FROM doctor;
SELECT * FROM schedule

9、创建一个表work_count，包含两个属性工作证号、值班次数。使用子查询的形式将每个医生的工作证号、值班次数存入表work_count。从work_count查询出医生的工作证号、值班次数。

CREATE TABLE `work_count` ( `Dr_id` CHAR ( 4 ) NOT NULL, `Skd_time` TINYINT NOT NULL, PRIMARY KEY ( `Dr_id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8mb3;
INSERT INTO work_count ( Dr_id, Skd_time ) SELECT
Dr_no,
count( Skd_seg ) 
FROM
SCHEDULE 
GROUP BY
	Dr_no 
HAVING
	COUNT( Skd_seg )>=(
	SELECT
		MIN( Skd_seg ) 
	FROM
	SCHEDULE 
	);
SELECT * FROM work_count

10、定义一个视图doctor_info,包含工作证号，医生姓名、科室编码、科室名称、医生级别编码、级别名称，其中工作证号来自Doctor表，科室编码来至department表,医生级别编码DoctorLevel表。使用该视图完成查询：查询医生姓名、科室名称、级别名称。

CREATE VIEW `doctor_info` AS SELECT
`doctor`.`Dr_no` AS `Dr_no`,
`doctor`.`Dr_name` AS `Dr_name`,
`doctorlevel`.`Drlv_no` AS `Drlv_no`,
`doctorlevel`.`Drlv_name` AS `Drlv_name`,
`department`.`Dept_no` AS `Dept_no`,
`department`.`Dept_name` AS `Dept_name` 
FROM
	((
			`doctor`
			JOIN `doctorlevel` ON ( `doctor`.`Drlv_no` = `doctorlevel`.`Drlv_no` ))
	JOIN `department` ON ( `doctor`.`Dept_no` = `department`.`Dept_no` ));
SELECT
	Dr_name,
	Drlv_name,
	Dept_name 
FROM
	doctor_info

11、基于上题中定义的视图doctor_info，插入一个医生级别（7，‘测试1’）,插入一个医生（’D020’,’测试医生’），观察是否成功，并思考产生这种结果的原因?

--第一种1393报错：表示您不能通过一个连接视图（join view）修改超过一个基表
INSERT INTO doctor_info ( Dr_no, Dr_name, Drlv_no, Drlv_name, Dept_no, Dept_name )
VALUES
	( NULL, NULL, "7", "测试1", NULL, NULL);
INSERT INTO doctor_info ( Dr_no, Dr_name, Drlv_no, Drlv_name, Dept_no, Dept_name )
VALUES
	( "D020","测试医生",NULL, NULL, NULL, NULL);

--第二种1423报错：表示您尝试插入数据到一个视图，但是视图对应的基表中有一个字段没有默认值，而且在插入操作中没有为该字段提供值。
INSERT INTO doctor_info ( Drlv_no, Drlv_name )
VALUES
	( "7", "测试1" );
INSERT INTO doctor_info ( Dr_no, Dr_name )
VALUES
	( "D020", "测试医生" );

插入失败，原因是：

视图在数据库中是一种虚拟表，它是由一个或多个实际表（基表）上的查询结果构成的。视图本身不存储数据，它只是展示基表数据的一种方式。视图只能查，不能增删改