友情连接

1、学生成绩管理系统数据库设计–MySQL

学生成绩管理系统数据库设计–MySQL

2、邮件管理数据库设计–MySQL

邮件管理数据库设计–MySQL

3、SQL Server医疗信息管理系统数据库【英文版-源码】–（Medical Management System Database）

SQL Server医疗信息管理系统数据库【英文版-源码】–（Medical Management System Database）

4、SQL Server电影院数据库管理系统【英文版-源码】–（Movie Theatre Management System Database）

SQL Server电影院数据库管理系统【英文版-源码】–（Movie Theatre Management System Database）

一、 数据库的创建

设计一个数据库，描述数据库的功能（100字左右）。数据库至少包含三张数据表，每张表不少于5个字段，每张表至少添加5条测试记录。要求为每张数据表设置主键，表与表之间的数据关联设置外键，字段选择合适的数据类型。

医疗信息管理系统：鉴于现在疫情严重，设计该数据库是为了方便诊所更好的实现医疗就诊信息管理，从而实现病人预约—接待员统计—医生看病—护士护理整个就诊流程所产生信息存储、查询、修改等，该数据库共包含6张表，分别为：诊所职工信息表、病人基本信息表、药品信息表、就诊预约表、病人病历记录表、处方表。

点击获取源码：

MySQL医疗信息管理系统数据库（源码）

创建数据库

所有的表和视图

诊所职工信息表

病人信息表

药品信息表

病人预约表

病人病历信息表

处方表

@H_46_126@

二、基于数据库必须完成以下内容

2.1 数据查询

①　 多表连接查询 join on 查询入职在2019年6月入职的医生中，每个医生治疗的病人，要求显示：医生姓名、治疗病人的数量，按治疗病人的数量从大到小排序； 查询语句：

SELECT  	u.name, p.number
From 	 	users_info AS u INNER JOIN
 				(SELECT doctor_id, COUNT(*) AS number From patients_info 
				GROUP BY doctor_id) AS p ON u.id = p.doctor_id
	WHERE  	MONTH(u.hire_date) = 6
	ORDER BY 	p.number DESC;

查询结果：

②　 高级查询 select 聚合函数 from where group by having 查询用药种类大于1的病人，要求显示：病人id, 姓名，病情描述、诊断结果、治疗方案，用药数量，按病人id排序 查询语句：

SELECT 		pa.pt_id,
				pa.name,
				c.description,
				c.diagnosis,
				c.therapy,
				PR.number
FROM 		patients_info AS pa, case_history AS c,
				(SELECT c_id, COUNT(c_id) AS number FROM prescriptions 
				GROUP BY c_id HAVING COUNT(c_id) > 1) AS pr
WHERE 		pa.pt_id = c.pt_id AND
				c.c_id = pr.c_id
		ORDER BY 	pa.pt_id;

查询结果：

③　 子查询>= > <= = in >=all <all =any（任选2个） 查询用药相同的病人，要求显示：药品名、用药使用量，病人姓名、性别、年龄、病情描述、诊断结果、治疗方案，先按药品名升序排，后按药品使用量降序排 查询语句：

SELECT 		dr.name AS drug_name,
				pr.quantITy AS DOSage,
				pa.name,
				pa.gender,
				pa.age,
				ca.description,
				ca.diagnosis,
				ca.therapy
FROM 		patients_info AS pa INNER JOIN
				 case_history AS ca ON pa.pt_id = ca.pt_id INNER JOIN
					prescriptions AS pr ON pr.c_id = ca.c_id INNER JOIN
						drugs_info AS dr ON dr.d_id = pr.d_id
WHERE 		dr.d_id IN (SELECT p.d_id FROM prescriptions AS p, 
				case_history AS c WHERE p.c_id = c.c_id GROUP BY p.d_id 
				HAVING COUNT(c.pt_id) > 1)
		ORDER BY 	drug_name ASC, dosage DESC;

查询结果：

2.2 函数

①　 自定义一个函数，要基于数据表并调用该函数。 创建函数：根据诊所职工id获取职工类型 创建语句：

DELIMITER //
CREATE FUNCTION get_user_tyPE_by_id(id INT)
RETURNS VArchAR(300)
BEgin
RETURN (
SELECT 		CONCAT('name: ', u.name, '   ', 'user_type: ', u.user_type)
FROM 		users_info AS u 
WHERE 		u.id = id);
END//
	 DELIMITER ;

调用函数截图：

②　 自定义一个函数，要求函数体中包含其中一种流程控制语句

创建函数：输入用户id， 查看用户薪资水平，薪资小于5000显示“一般”，大于等于5000小于10000显示“中等”，大于等于10000显示“高薪” 创建语句：

DELIMITER //
CREATE FUNCTION check_salary_level(id INT) 
RETURNS VARCHAR(8)
BEGIN
	DECLARE u_salary INT;
	SELECT salary into u_salary FROM users_info AS u WHERE u.id = id;
	IF u_salary < 5000 THEN
		RETURN '一般';
	ELSeiF u_salary >= 10000 THEN
		RETURN '高薪';
	ELSE
		RETURN '中等';
END IF;
END//
DELIMITER ;

调用函数截图：

2.3 视图

（1） 创建检查视图，并查看视图 创建病人视图：要求显示病人基本信息和病历信息 创建语句：

	CREATE VIEW v_patients AS
	SELECT 		pa.pt_id,
					pa.name,
					pa.gender,
					pa.age,
					ca.description,
					ca.diagnosis,
					ca.therapy
	FROM 		patients_info AS pa INNER JOIN
						ase_history AS ca ON pa.pt_id = ca.pt_id;

查看视图：

2.4 存储过程

（1） 自定义带输入参数的存储过程，完成调用。 创建存储过程：每增加一个病人，对应的诊断医生薪资自动增加99 创建语句：

DELIMITER //
	CREATE defineR = current_USER PROCEDURE add_patient(
	IN pname VARCHAR(50) charset 'utf8',
	IN pgender VARCHAR(10),
	IN page TINYINT,
	IN ptel VARCHAR(20),
	IN paddress VARCHAR(100) charset 'utf8',
	IN pcreation_date DATE,
	IN pdoctor_id INT
	)
	DETERMINISTIC
	BEGIN
	INSERT INTO patients_info (name, gender, age, tel, address, creation_date, doctor_id)
	VALUES (pname, pgender, page, ptel, paddress, pcreation_date, pdoctor_id);
	UPDATE users_info
	SET salary = salary + 99
	WHERE id = pdoctor_id;
	COMMIT;
	END//
			DELIMITER ;

调用截图：

（2） 自定义带输入、输出参数的存储过程，完成调用。 创建存储过程：每创建一个处方，需要从药品信息表的药品库存里扣除处方所包含的相应药品数量，并输出扣除后的该药品的库存数量 创建语句：

	DELIMITER //
	CREATE DEFINER = CURRENT_USER PROCEDURE add_prescription(
	IN d_quantity INT,
	IN case_id INT,
	IN drug_id VARCHAR(30),
	IN p_type VARCHAR(20),
	OUT d_stock_number INT -- 库存数量
)
	DETERMINISTIC
	BEGIN
	INSERT INTO prescriptions (quantity, c_id, d_id, type)
	VALUES (d_quantity, case_id, drug_id, p_type);
	UPDATE drugs_info
	SET stock_number = stock_number - d_quantity
	WHERE d_id = drug_id;
	SELECT stock_number INTO d_stock_number FROM drugs_info WHERE d_id = drug_id;
COMMIT;
END//
	DELIMITER ;

调用截图：

2.5 触发器

①　 创建触发器，实现数据的修改 。并对比触发器执行前后的对比。 触发器功能： 实时更新药品库存量 创建语句：

DELIMITER //
CREATE TRIGGER update_info 
AFTER INSERT ON prescriptions FOR each ROW  
BEGIN
      UPDATE drugs_info SET stock_number = stock_number - new.quantity
      WHERE new.d_id = d_id;
END//
DELIMITER ;

触发器执行前后的对比截图：