第一:连接【join】
定义:将两张以上的表中的数据整合在一起的机制,称为连接。
1、 笛卡儿积
定义:两张表以上所有的置换。原因是没有指定连接。
两张表如下:
employee表,department表,employee.dept_id是department表的外键。
查询雇员的全名和其所在的部门名,涉及到两张表。
查询语句:
select e.fname,e.lname,d.name from `employee` as e join `department` as d;
查询结果是:五十四行,因为每个雇员重复三次,原因在于没有指定两张表是如何连接的。
第二:内链接
2.1查询语句
select e.fname,e.lname,d.name from `employee` as e innet join `department` as d on e.dept_id=d.dept_id;
相比较之前,增加了on e.dept_id=d.dept_id ,目的确定了两张表的连接具体如何对应的。
注意:内连接查询结果,这出现两张表共有的部分,没有匹配到的则不会出现在查询结果中,如果需要显示,则使用外连接。
一般而言要显示指定内连接还是外连接,本例中加了关键字inner,显示指定内连接,即 inner join;
如果没有显示指定内外连接,mysql默认是内连接 inner join;
2.2 简化查询
如果连接两张表的列名相同,则可以使用using(列名),替代on a.c=b.c语句。
select e.fname,e.lname ,d.name from `employee` as e inner join `department` as d using(dept_id);
注意:一般建议使用on a.c=b.c语句,这样拥有更好的可读性。
第三:连接三张及以上的表
表出现的顺序与查询结果无关。
例子:查询account表的account id和customer表的fed id,及开设此账户的employee表柜员姓名fname,lname,且customer表中的账户类型为B。
select a.account_id,c.fed_id,e.fname,e.lname from `account` as a inner join `customer` as c
on a.cust_id=c.cust_id
inner join `employee` as e
on a.open_emp_id=e.emp_id
where c.cust_type_cd='B';
a、每一个连接join,后面都要指定内连接的条件。本例两个内链接,都在其后立马指定连接条件
b、employee表并没有直接和account表相连,实际上连接的是account和customer两张表内连接形成的临时表。
可以想象成滚雪球,account表和customer表先滚成一个雪球,然后加入了employee表,甚至可以继续加入其他表,,,
第四:将子查询表作为查询表
select a.account_id,a.cust_id,a.open_date,a.product_cd
from `account` as a inner join
(select emp_id,assigned_branch_id
from `employee`
where start_date <'2007-01-01'
and (title='Teller' or title='Head Teller')) as e
on a.open_emp_id=e.emp_id
inner join(
select branch_id from `branch`
where name='Woburn Branch') as b
on e.assigned_branch_id=b.branch_id;
这样相当于把主查询的条件,即where语句,放到每个子查询中了。
坑:别名时刻要添加。
第五:连续两次使用同一个表
要做每次join表,定义两个不同的别名,便于系统区分。
select a.account_id,e.emp_id,b_a.name as
open_branch,b_e.name as emp_branch
from `account` as a inner join `branch` as b_a
on a.open_branch_id=b_a.branch_id
inner join `employee` as e
on a.open_emp_id=e.emp_id
inner join `branch` as b_e
on e.assigned_branch_id=b_e.branch_id
where a.product_cd='CHK';
本例中两次join branch表,第一的别名b_a,第二次别名b_e.
第六:自连接
可以对表自身进行连接。employee表中一个指向自身的外键的列,列名是superi_emp_id,该列指向雇员的主管,除非大boss,没有主管。
本例:查询雇员姓名,和其上级的姓名,boss的上级是null,此查询不输出。
select e.fname,e.lname,e_m.fname as magement_fname,e_m.lname as magement_lname
from `employee` as e
inner join `employee` as e_m
on e.superior_emp_id=e_m.emp_id;
6.1 on子句借助superior_emp_id 这个外键实现employee表的自连接,当然分别指定别名。
6.2 superior emp id 有一行值为null的,查询结果不包含。原因是内连接inner join。
坑:a、连接条件是,e.superior_emp_id =e_m.emp_id
其实我们把他当作独立的两张表,查询条件,第一张表的主管id,对应第二张表的emp id。
b、如果连接条件是e.emp_id=e_m.superior_emp_id
因为别名是e的表,emp id是不重复的,对应到别名e_m的表的superior_emp_id列是重复的,所有查询结果就乱了。
套路excel 的vlookup,就很好理解了。
第七:不等连接
以上诸例的多表查询,都是用相等条件on ,,,=,,,,也就是匹配项的值必须相等。
7.1、有时候,也可以通过限定范围来实现对表的连接。
select e.emp_id,e.fname,e.lname,e.start_date
from `employee` as e inner join `product` as p
on e.start_date >= p.date_offered
and e.start_date <= p.date_retired
where p.name='checking account';
表连接的匹配值没有相等,限定了一个日期范围。
7.2、有时候,不相等的自连接
比如举办针对柜员Teller的象棋比赛。自己不能和自己比赛吧
select e.fname,e.lname,'VS' vs,e2.fname,e2.lname
from `employee` as e inner join
`employee` as e2
on e.emp_id != e2.emp_id
where e.title='Teller' and e2.title='Teller';
以上查询输出结果中,添加了一列,列名vs,值VS,此外输出结果,也相当于笛卡尔积,枚举所有可能,即柜员1,对战符合条件的所有柜员,结果导致所有的雇员对战都会出现两次,例子:即a和b,出现两次,一次是ab,一次是ba。
解决该问题,也很简单,即使让柜员只和大于其工号emp id的对战。
select e.fname,e.lname,'VS' vs,e2.fname,e2.lname
from `employee` as e inner join `employee` as e2
on e.emp_id > e2.emp_id
where e.title='Teller' and e2.title='Teller';