第三章作业
一、具体题意
已知医院数据库,库中包括三个表:
医生(职工号 char(3),姓名 char(8) , 职称 char(10),部门 char(6), 年龄 int) // 电话
处方(处方号 char(3),职工号 char(3),药编号 char(3),数量 int,日期 datetime)
药 (药编号 char(3), 药名 char(12), 单位 char(2), 单价 numeric(6,2) ,生产厂家 char(12))
求:
以下代码运行环境 MariaDB, 和 SQL Server 并不一定完全兼容, 仅供参考!
- 在医生关系中增加一个属性电话 c(11)。
1 2 |
ALTER TABLE 医生 ADD 电话 CHAR(11); |
- 修改处方表属性数量为 smallint
1 2 |
ALTER TABLE 处方 MODIFY 数量 SMALLINT; |
- 查询 “刘欣林” 医生所开的处方, 查询结果包含姓名, 职称, 药编号, 数量, 日期, 并按药编号降序排列。
1 2 |
SELECT 医生.姓名, 医生.职称, 药.药编号, 处方.数量, 处方.日期 FROM 处方, 医生, 药 WHERE 医生.姓名='刘欣林' AND 处方.药编号=药.药编号 AND 处方.职工号=医生.职工号 ORDER BY 药编号 DESC; |
- 查询 2007 年的处方, 查询结果包含医生姓名, 药名, 数量, 日期, 结果按日期升序排列。
1 2 |
SELECT 医生.姓名, 药.药名, 处方.日期 FROM 处方, 医生, 药 WHERE YEAR(处方.日期)=2007 AND 处方.药编号=药.药编号 AND 处方.职工号=医生.职工号 ORDER BY 处方.日期 ASC; |
- 建立一个包含医生姓名, 职称, 处方号, 药名, 数量, 单位, 单价, 生产厂家的视图 view1。
1 2 |
CREATE VIEW view1 AS SELECT 医生.姓名, 医生.职称, 处方.处方号, 药.药名, 处方.数量, 药.单位, 药.单价, 药.生产厂家 FROM 医生, 处方, 药 WHERE 处方.药编号=药.药编号 AND 处方.职工号=医生.职工号; |
- 建立一个视图 view2, 视图中存放外科医生所开的处方号, 医生姓名, 职称, 药名和数量。
1 2 |
CREATE VIEW view2 AS SELECT 处方.处方号, 医生.姓名, 医生.职称, 药.药名, 处方.数量 FROM 医生, 处方, 药 WHERE 医生.职称='外科医生' AND 处方.药编号=药.药编号 AND 处方.职工号=医生.职工号; |
- 查询处方表中, 每个处方药物的总价 (处方表中用药数量 * 药表中药物的单价), 查询结果中包括处方号, 总药价和职工号, 并按总药价升序。
1 2 |
SELECT 处方.处方号, (药.单价 * 处方.数量) 总药价, 医生.职工号 FROM 药, 处方, 医生 WHERE 处方.药编号=药.药编号 AND 处方.职工号=医生.职工号 ORDER BY 总药价 ASC; |
- 统计2007年所开处方药物的总价,查询结果包括处方号,总药价,姓名和年份,结果按姓名降序排列。
// 看不懂题目
1 2 |
SELECT 处方.处方号, (药.单价 * 处方.数量) 总药价, 医生.姓名, YEAR(处方.日期) FROM 药, 处方, 医生 WHERE YEAR(处方.日期)=2007 AND 处方.药编号=药.药编号 AND 处方.职工号=医生.职工号 ORDER BY 总药价 ASC; |
- 查询和药名为“感康”出自同一生产厂家的药品信息。
1 2 |
SELECT fir.* FROM 药 AS fir, 药 AS sec WHERE sec.药名='感康' AND fir.生产厂家=sec.生产厂家; |
- 查询和医生“刘风云”同一职称的医生姓名,所在部门和职称。
// 这里不排除 刘凤云 本身, 毕竟他和他本身也符合题意
1 2 |
SELECT fir.姓名, fir.部门, fir.职称 FROM 医生 AS fir, 医生 AS sec WHERE sec.姓名='刘风云' AND fir.职称=sec.职称; |
- 查询每个医生所开处方药价的总和。
1 2 |
SELECT 处方.职工号, SUM((处方.数量 * 药.单价)) 总和 FROM 处方, 药 WHERE 处方.药编号=药.药编号 GROUP BY 处方.职工号; |
- 查询每个医生所开处方的个数,显示医生姓名和处方个数,并按处方个数降序排列。
1 2 |
SELECT 医生.姓名, count((处方.处方号)) 处方数 FROM 处方, 医生 WHERE 处方.职工号=医生.职工号 GROUP BY 处方.职工号 ORDER BY 处方数 DESC; |
- 修改药品“感冒通”的单价为5元.
1 2 |
UPDATE 药 SET 单价=5 WHERE 药名='感冒通'; |
- 修改职工“谢天力”的职称为“主任医师”
1 2 |
UPDATE 医生 SET 职称='主任医师' WHERE 姓名='谢天力'; |
- 删除“广州制药厂”的药品。
1 2 |
DELETE FROM 药 WHERE 生产厂家='广州制药厂'; |
- 删除4月份开出的处方。
1 2 |
DELETE FROM 处方 WHERE MONTH(日期)=4; |
参考代码:
1 2 3 4 5 6 7 8 9 10 |
Create database 医生药处方 Use 医生药处方 create table 医生(职工号char(3) primary key,姓名char(8) , 职称char(10),部门char(6), 年龄int) create table 药(药编号char(3) primary key, 药名char(12), 单位char(2), 单价numeric(6,2) ,生产厂家char(12)) ENGINE=InnoDB DEFAULT CHARSET=utf8; create table 处方(处方号char(3) primary key ,职工号char(3),药编号char(3),数量int,日期 datetime, foreign key ( 职工号) references 医生(职工号), foreign key (药编号) references 药(药编号)) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
insert into 医生 values('101','赵同欣','主任医师','口腔科', 33); insert into 医生 values('102','万请','医师','皮肤科',24); insert into 医生 values('103','张灵','主任医师','内科',38); insert into 医生 values('104','刘风云','医师','外科',24); insert into 医生 values('105','王进','医师','外科',22); insert into 医生 values('106','陶琴因','主任医师','妇科',42); insert into 医生 values('107','刘欣林','医师','眼科',31); insert into 医生 values('108','谢天力','医师','口腔科',21); insert into 医生 values('109','李年才','医师','内科',26); insert into 药 values('1','白加黑','盒',12.86,'西安制药厂'); insert into 药 values('2','三九胃泰','盒',22,'广州制药厂'); insert into 药 values('3','脑白金','瓶',98.6,'天津制药厂'); insert into 药 values('4','维生素B6','板',5.64,'上海制药厂'); insert into 药 values('5','感康','盒',11.8,'北京制药厂'); insert into 药 values('6','维生素D','瓶',4.8,'西安制药厂'); insert into 药 values('7','皮肤病血毒丸','盒',35.8,'广州制药厂'); insert into 药 values('8','泻痢停','盒',15,'上海制药厂'); insert into 药 values('9','黄金搭档','瓶',75.5,'北京制药厂'); insert into 药 values('10','感冒通','盒',3,'上海制药厂'); insert into 药 values('11','止痛灵','板',18.8,'天津制药厂'); insert into 药 values('12','太太口服液','盒',28.8,'广州制药厂'); insert into 处方 values('001','103','1',2,'2007-2-4'); insert into 处方 values('002','102','7',1,'2008-1-22'); insert into 处方 values('003','105','5',1,'2007-3-14'); insert into 处方 values('004','101','4',1,'2007-5-2'); insert into 处方 values('005','105','5',2,'2006-8-12'); insert into 处方 values('006','103','2',2,'2006-8-18'); insert into 处方 values('007','101','1',1,'2007-7-9'); insert into 处方 values('008','104','4',1,'2007-6-11'); insert into 处方 values('009','105','11',1,'2007-9-17'); insert into 处方 values('010','107','2',3,'2008-1-2'); insert into 处方 values('011','102','7',1,'2006-9-23'); insert into 处方 values('012','107','6',2,'2006-11-8'); insert into 处方 values('013','108','3',3,'2007-4-12'); insert into 处方 values('014','101','3',2,'2007-7-22'); insert into 处方 values('015','103','9',2,'2007-8-16'); insert into 处方 values('016','109','3',1,'2006-11-8'); insert into 处方 values('017','108','9',3,'2008-3-21'); insert into 处方 values('018','105','6',2,'2007-5-24'); insert into 处方 values('019','107','4',2,'2007-6-27'); insert into 处方 values('020','106','9',1,'2007-9-12'); |