MairaDB 增删改查 连接 分组

第三章作业

一、具体题意

已知医院数据库,库中包括三个表:

医生(职工号 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 并不一定完全兼容, 仅供参考!

  1. 在医生关系中增加一个属性电话 c(11)。
ALTER TABLE 医生 ADD 电话 CHAR(11);
  1. 修改处方表属性数量为 smallint
ALTER TABLE 处方 MODIFY 数量 SMALLINT;
  1. 查询 “刘欣林” 医生所开的处方, 查询结果包含姓名, 职称, 药编号, 数量, 日期, 并按药编号降序排列。
SELECT 医生.姓名, 医生.职称, 药.药编号, 处方.数量, 处方.日期 FROM 处方, 医生, 药 WHERE 医生.姓名='刘欣林' AND 处方.药编号=药.药编号 AND 处方.职工号=医生.职工号 ORDER BY 药编号 DESC;
  1. 查询 2007 年的处方, 查询结果包含医生姓名, 药名, 数量, 日期, 结果按日期升序排列。
SELECT 医生.姓名, 药.药名, 处方.日期 FROM 处方, 医生, 药 WHERE YEAR(处方.日期)=2007 AND 处方.药编号=药.药编号 AND 处方.职工号=医生.职工号 ORDER BY 处方.日期 ASC;
  1. 建立一个包含医生姓名, 职称, 处方号, 药名, 数量, 单位, 单价, 生产厂家的视图 view1。
CREATE VIEW view1 AS SELECT 医生.姓名, 医生.职称, 处方.处方号, 药.药名, 处方.数量, 药.单位, 药.单价, 药.生产厂家 FROM 医生, 处方, 药 WHERE 处方.药编号=药.药编号 AND 处方.职工号=医生.职工号;
  1. 建立一个视图 view2, 视图中存放外科医生所开的处方号, 医生姓名, 职称, 药名和数量。
CREATE VIEW view2 AS SELECT 处方.处方号, 医生.姓名, 医生.职称, 药.药名, 处方.数量 FROM 医生, 处方, 药 WHERE 医生.职称='外科医生' AND 处方.药编号=药.药编号 AND 处方.职工号=医生.职工号;
  1. 查询处方表中, 每个处方药物的总价 (处方表中用药数量 * 药表中药物的单价), 查询结果中包括处方号, 总药价和职工号, 并按总药价升序。
SELECT 处方.处方号, (药.单价 * 处方.数量) 总药价, 医生.职工号 FROM 药, 处方, 医生 WHERE 处方.药编号=药.药编号 AND 处方.职工号=医生.职工号 ORDER BY 总药价 ASC;
  1. 统计2007年所开处方药物的总价,查询结果包括处方号,总药价,姓名和年份,结果按姓名降序排列。
    // 看不懂题目
SELECT 处方.处方号, (药.单价 * 处方.数量) 总药价, 医生.姓名, YEAR(处方.日期) FROM 药, 处方, 医生 WHERE YEAR(处方.日期)=2007 AND 处方.药编号=药.药编号 AND 处方.职工号=医生.职工号 ORDER BY 总药价 ASC;
  1. 查询和药名为“感康”出自同一生产厂家的药品信息。
SELECT fir.* FROM 药 AS fir, 药 AS sec WHERE sec.药名='感康' AND fir.生产厂家=sec.生产厂家;
  1. 查询和医生“刘风云”同一职称的医生姓名,所在部门和职称。
    // 这里不排除 刘凤云 本身, 毕竟他和他本身也符合题意
SELECT fir.姓名, fir.部门, fir.职称 FROM 医生 AS fir, 医生 AS sec WHERE sec.姓名='刘风云' AND fir.职称=sec.职称;
  1. 查询每个医生所开处方药价的总和。
SELECT 处方.职工号, SUM((处方.数量 * 药.单价)) 总和 FROM 处方, 药 WHERE 处方.药编号=药.药编号 GROUP BY 处方.职工号;
  1. 查询每个医生所开处方的个数,显示医生姓名和处方个数,并按处方个数降序排列。
SELECT 医生.姓名, count((处方.处方号)) 处方数 FROM 处方, 医生 WHERE 处方.职工号=医生.职工号 GROUP BY 处方.职工号 ORDER BY 处方数 DESC;
  1. 修改药品“感冒通”的单价为5元.
UPDATE 药 SET 单价=5 WHERE 药名='感冒通';
  1. 修改职工“谢天力”的职称为“主任医师”
UPDATE 医生 SET 职称='主任医师' WHERE 姓名='谢天力';
  1. 删除“广州制药厂”的药品。
DELETE FROM 药 WHERE 生产厂家='广州制药厂';
  1. 删除4月份开出的处方。
DELETE FROM 处方 WHERE MONTH(日期)=4;

参考代码:

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;
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');