医药数据库系统原理与应用3（SQL语言）2

1.复合查询

等值和非等值连接查询

select Doctor *,CureFee *,Patient *
from Doctor,Patient,CureFee
where Doctor dID=CureFee dID
and Patient pID=CureFee pID;
//查询每个医生及其诊治病人的情况

自然连接：在等值连接中把目标列中重复的属性列去掉

自身连接：同一张表的连接。表与表自身连接时需要起别名以示区别，由于所有属性名都是同名属性，因此必须使用别名前缀。

select Second.dName,Second.Title
from Doctor First,Doctor Second
where First.dName='王丹' and First.Department=Second.Department;
//查询与王丹医生同一科室的医生的姓名、职称

外连接：将主体表中不满足连接条件的记录一并输出。

左外连接：列出左边关系中所有的记录，而不管其是否满足连接要求。

右外连接：列出右边关系中所有的记录，而不管其是否满足连接要求。

select Doctor. *,CureFee.pID,CureFee.Fee
from Doctor left join CureFee
on Doctor.dID=CureFee.dID;
//左外连接：查询所有医生诊治病人的情况

select Patient.*,CureFee.dID,CureFee.Fee
from CureFee right join Patient
on CureFee.pID=Patient.pID;
//右外连接：查询所有病人被诊治的情况

复合条件连接：where子句中含有多个连接条件

2.嵌套查询

一个select-from-where语句称为一个查询块，将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询。

带有in谓词的子查询、带有比较运算符的子查询、带有exists谓词的子查询

select dName
from Doctor
where dID in(
select dID 
from CureFee
where pID in(
select pID from Patient
where pName='刘丽'));
//查询诊治病人刘丽的医生的姓名

select dID,pID,Fee
from CureFee X
where Fee>(
select acg(Fee)//某一个医生的病人的平均就诊费用
from CureFee Y
where Y.dID=X.dID;
//查询每个医生的病人就诊总费用超出该医生接诊的所有病人的平均就诊总费用的医生ID、病人ID、就诊费用

select pName
from Paitent
where exists(
select *
from CureFee
where Patient.pID=CureFee.pID
and dID='d1');
//查询d1医生诊治的病人的姓名

3.集合查询

union：并集运算，可以将两个以上的查询结果合并成一个结果，并在后续的结果集中去除前面结果集中已有的数据行。（列数相同、列项相同）并

union将多个查询结果合并起来时，系统自动去掉重复记录。如果需要保留所有的重复数据，可以使用union all

intersect可以返回多条查询语句中都包含的重复数据。交

expect可以比较左右两个查询结果集的差异，并从左侧的查询结果集中返回在右侧结果集找不到的数据，即从左侧的结果集中减去右侧结果集中相同的数据后得到的结果。差

4.数据更新

插入数据：insert into     values

更新数据：update    set     where

删除数据：delete from   where

insert into Doctor(dID,dName,Title,Department,Assisent)
values('d4','李灿','主治医师','呼吸科','d5'),
      ('d5','朱城','实习医生','呼吸科','');//插入


insert into CureFee(pID,dID)
select pID,'d5'from patient//插入查询结果

update top(3) Patient
set Tel='000'
where Job='学生'
order by birth ASC(DESC);
//将Patient表中所有职业为学生且出生日期最早（或最晚）的三个人的电话改为000


delete from CureFee
where pID
in(select pID 
from Patient
where pName='崔慧');
//删除崔慧病人的所有诊治记录

5.视图

视图是虚表，用户可以通过它来浏览表中感兴趣的部分或全部数据，而数据的物理存放位置依然存放于基本表中，当基本表的数据发生变化时，视图中的数据也随之改变。

创建视图

create view 视图名

as

子查询

（with check option）

子查询不允许含有order by 子句

with check option 表示对视图对当时视图进行操作时，要保证操作的行满足视图定义中子查询的条件。

create view Dr
as
select dID,dName,Title,Department,Assisant
from Doctor
where Title='主任医师'
with check option;
//创建名为Dr的视图，视图仅包含职称为主任医师的信息，并要求进行更新操作时仍保证该视图只存主治医师的信息。

select *
from Dr
where Department='呼吸科';
//查询Dr视图呼吸科的主任医师

insert into Pati
values('李明','男','学生');//在视图中插入一条新数据；

update Dr
set dName='朱城' ,Assisant='d1'
where dID='d5';
//修改视图

delete from Pati
where sex='男'
//删除Pati视图中男性的记录

6.视图的作用

视图可以隔离用户对表的直接访问，提高系统的安全性，还可以简化应用程序的开发。

视图使不同用户以不同角度看待同一数据；

视图可以根据需要定制数据，保证数据的安全；

视图可以简化查询操作，但不能简化查询语句的编写；

视图可以简化应用程序开发；

视图为重构数据库提供了一定程度的逻辑独立性；