MySQL笔记（三）

MySQL

五、多表查询

5.1 什么是连接

表employee中有emp_id(主键)，fname，lname，dept_id,etc.表department中有dept_id（主键），name。
需要查询每个雇员的姓名以及他们所在部门的名称，查询语句将指示服务器使用employee.dept_id列作为两个表之间的桥梁，从而实现在同一查询结果集中包含来自两个表的列，这种操作被称为连接。

5.1.1 笛卡尔积

select e.fname,e.lname,d.name
from employee e join department d;

组合两张表的所有数据，产生笛卡尔积。n个雇员，m个部门，产生n*m行数据。这种连接称为交叉连接，实际中很少使用。

5.1.2 内连接

要使查询结果集中只有n行，则需要描述这两个表是如何关联的。前面提到employee.dept_id列起到连接的作用，因此from子句中应该增加这一信息。

select e.fname,e.lname,d.name
from employee e join department d
on e.dept_id=d.dept_id;

如果一个表中的dept_id列中存在某个值，但该值在另一张表的相关联列中不存在，那么相关行的链接会失败，在结果集中将会排除包含该值的行。这种连接类型成为内连接。如果想包含某个表的所有行，而不考虑每行是否在另一个表中存在匹配，那么可以使用外连接。

上一例中没有指定连接类型，服务器默认使用内连接。具体制定连接类型的语句：from employee e inner join department d;

如果连接两个表的列名相同，可以使用using子句替代on子句：

select e.fname,e.lname,d.name
from employee e join department d
using (dept_id);

5.1.3 ANSI连接语法

select e.fname,e.lname,d.name
from employee e,department d
where e.dept_id=d.dept_id;

5.2 连接更多的表

对于3个表的连接，在from子句中将包含3个表和两种连接类型，以及两个on子句。下面首先给出另一个两表连接查询的例子。

select a.account_id,c.fed_id
from account a inner join customer c
    on a.cust_id=c.cust_id
where c.cust_type_cd='B';

该查询返回所有商务账户（类型B）的账户ID和税务号码。如果需要再增加employee表以查询开设此账户的柜员姓名，就需要采用下面的方法：

select a.account_id,c.fed_id,e.fname,e.lname
from account a inner join customer c
    on a.cust_id=c.cust_id
    inner join employee e
    on a.open_emp_id-e.emp_id
where c.cust_type_cd='B';

from中各表出现的顺序不重要

SQL是一种非过程化的语言

5.2.1 将子查询结果作为查询表

select a.account_id,a.cust_id,a.open_date,a.product_cd
from account a inner join
    (select emp_id,assigned_branch_id
    from employee
    where start_date<'2007-01-01'
        and (title='Teller' or title='Head Teller')) e
on a.open_emp_id=e.emp_id
inner join
    (select branch_id
    from branch
    where name='Woburn Branch') b
on e.assigned_branch_id=b.branch_id;

从第3行开始的第一个子查询的别名为e，它用于查找所有有经验的柜员。从第9行开始的第二个子查询的别名为b，它用于查找Woburn支行的ID。首先，account表与用employee ID与子查询e相连接，然后使用branch ID与子查询b相连接。

5.2.2 两次使用同一个表

需要给重复使用的表的实例定义不同的别名，以便服务器能够在各子句中正确引用。

5.3 自连接

同上，要给表定义不同的别名。

5.4 相等连接和不等连接

大多数查询使用的是相等连接，但有时通过限定值的范围实现对表的连接。

select e.emp_id.e.fanme,e.lname,e.start_date
from employee e ineer join product p
    on e.start_date>=p.date_offered
    and e.start_date<=p.date_retired
where p.name='no-fee checking';

该查询连接的两个表并没有外键关联，其意图是找出所有在no-fee checking产品存续期钱入职的银行雇员。因此，雇员的入职时间必须位于产品的提供日期与产品的结束日期之前。

有时还需要不等的自连接：银行柜员象棋比赛对阵表

select e1.fname,e1.lname,'VS' vs,e2.fname,e2.lname
from employee e1 inner join employee e2
    on e1.emp_id<e2.emp_id
where e1.title='Teller' and e2.title='Teller';

六、使用集合

6.1 集合理论基础

6.2 集合理论在SQL中的应用