多表查询导学.doc

多表查询一、多表连接当进行数据库设计时，为了减少数据冗余，提高数据库应用系统的性能。在完成关系模式转换时，依据转换原则，一个实体一张表，实体间的联系依据不同的联系类型进行适当的处理，在联系表中保留联系实体的主键(如：在借阅表中只保留读者的图书证号，以及书籍表中的图书ID)。尽量让每张表只描述一件事情。但是往往单表上的数据不能满足用户的查询要求。用户需要查看的数据可能来自多张表。如：检索借过我的成功可以复制的图书证号。分析：1、 关于书籍明细在book表中2、 关于借书明细在record表中得知：完成以上查询必须来自两张表。处理方法一：1、 将book表，record表进行连接,组建一张临时表。该表字段取自book，recordSelect * from book,record （两表连接方式，参看表连接示意）观察以上数据，表之间如何实现连接？解答：A表的每一行跟B表的所有行进行一次合并。这样合并的结果将会出现某些不符合实际的数据，例如之上圈上红色矩形框的数据，在借阅记录中呈现的是B001的记录，而在book表中呈现的却不是B001的描述，这样的数据是不符合实际描述的。因此对于连接后的结果集，我们只需保留图书证号相同的记录。Select * from book,recordWhere book.图书ID=record.图书ID最后再筛选出我的成功可以复制的记录Select * from book,recordWhere book.图书ID=record.图书ID and 书名=我的成功可以复制连接查询的本质特点：将多张表连接成依据一定的连接条件合并成一张临时的单表。对于该连接表的查询就如同之前所学的单表查询，轻而易举。总结：把多张表具有相同意义的字段其属性值相等的合并在一起的连接方式成为等值连接。问题一：是不是只有设置主外健关系的表才能实施表连接呢？解答：不是这是两个不同的概念，设置主外健主要是强调引用关系，限制用户的输入行为以保证数据的完整性。有没有设置主外健关系，是不会影响表连接。但是应该这么说，表连接的条件一般是建立在有引用特性的字段上。即属性意义相同的。二、操作细节1、为表取别名select * from book a,record bWhere a.图书ID=b.图书ID -为表取别名and 书名=我的成功可以复制2、 相同字段名应该强制加上表名.属性字段，如果为表取了别名则应别名.字段select 图书id,书名from book,record -由于book，record中都有图书IDWhere book.图书ID=record.图书ID and 书名=我的成功可以复制正确检索语句：select book.图书id,书名from book,recordWhere book.图书ID=record.图书IDand 书名=我的成功可以复制注意：select book.图书id,书名from book a,record b Where a.图书ID=b.图书IDand 书名=我的成功可以复制 -已为表取别名，不能用原名标识正确检索：select a.图书id,书名from book a,record bWhere a.图书ID=b.图书IDand 书名=我的成功可以复制三、特殊连接方式自连接 当涉及到满足某一条件下，行与行之间的比较，就需要把行与行合并成一行，这就需要进行自表连接。 例如：查看同时入住过16013,15019的旅客有哪些？首先：Select * from 入住记录 where 房号 in(16013,15019)以上检索方式将检索出既住过15019又住过16013的旅客，但是也把只住过15019或者16013的旅客也检索出来。因此改写条件语句为且的条件Select * from 入住记录 where 房号 = 16013 and 房号=15019但是一行中只记录该旅客的一次入住记录，因此之上检索出来的结果集为空集试想:如果把某用户的两次入住记录合并在一起，就可以检索该用户是否存在记录既住过15019又住过16013取别名Select * from 入住记录 a,入住记录 b where a.房号 = 16013 and b.房号=15019第二部分 子查询如：检索借过我的成功可以复制的图书证号。分析：1、 关于书籍明细在book表中2、 关于借书明细在record表中除了用连接表查询，还可以通过以下方法A、 先检索出我的成功可以复制的图书idSelect * from book where 书名=我的成功可以复制B、 再由图书ID获取借过该图书ID的图书证号Select 图书证号 from record where 图书id=？将两条语句合并Select 图书证号from record where 图书id=(Select 图书id from book where 书名=我的成功可以复制)之上：将某一查询语句作为某一查询的条件，这样的查询方式成为子查询。注意： 由于主查询语句在where语句中与子查询结果进行比较时是针对某一字段值进行比较的。因此其形式如下： Where 字段 判断关键字(select 子句)用于判断字段时是否等于子句中的返回值，或者是否在子句中的返回值列表中。因此在该语句形式中，子句中的显示字段必须跟where后的比较字段具有可比性字段，而且只能有一个字段。-情形一：子查询返回单一值（单列单行），才可以用各种算术运算符号，而且比较的字段必须具有一定关联性Select * from book where 书名=我的成功可以复制Select 记录号,图书证号, 图书id from record where 图书id=(Select 图书id from book where 书名=我的成功可以复制) 情形二：当子查询返回的结果集不止一行，返回多值（单列多行）-查询不在安全黑名单select * from 旅客where 身份证号码(select 身份证号码from安全黑名单) -错误的select * from 旅客where 身份证号码 not in (select 身份证号码from 安全黑名单) 思考之上查询能否用select * from 旅客,安全黑名单where 旅客.身份证号码安全黑名单.身份证号码注意：In 等同于 =anyNot in 等同于 !=all主要关键区别在于 in不可前置运算符号，而any ，all可以配合(,=,!=,=,=)等运算符号使用。情形三：当子查询返回的结果集为多行多列-检索所有入住过房号为16013的旅客的姓名-处理：走过旅客表判断该行的旅客身份证号码是否等于入住过16013的旅客身份证。Exists只是判断子查询结果集是否存在，如果存在返回1，否则返回01.取主查询一条记录。2根据这条记录判断子查询记录数是否大于零3如果大于零则将主查询中该记录加入结果集。否则不加入。4.取下一条记录，如果有，则再次从第1步循环到此。如果没有（到记录尾），则输入出查询结果。-运行过程select * from 旅客where 旅客.身份证号码and exists(select * from 入住记录 where 房号=16013 an