多张表联合起来查询称为连接查询。查询分类:内连接、外连接、全连接(不用)。如果两张表连接时没有任何条件限制,最后结果就是两张表的乘积,这叫笛卡儿积现象。所以表连接需要加条件:select userName,roleName from user,role where user.id=role.id;但是匹配次数并没有减少,只是满足条件的才被select出来了。
Notations:表起别名,提升效率。表的连接查询都需要起别名。如:
select u.userName,r.roleName from user u,role r where u.id=r.id;
一,去除重复记录
需要使用distinct关键字,其只能出现在所有字段最前方。
例:select count(distinct(job)) from emp;
// 计算工作岗位的数量
二,内连接
分为等值连接,非等值连接,自连接。
(1)等值连接
举例:
select u.userName,r.roleName from user u join role r on u.id=r.id;
与前面的相比,将where和逗号换成了join和on,最后结果一样。但是这个结构更清晰,而且还可以加where进行筛选。
Notations:join前可以加inner,没有影响,只是可读性更好
(2)非等值连接
select u.userName,r.roleName from user u join role r,on r.id between u.createdBy and u.id;
on后面的条件不是等值就是非等值连接。
(3)内连接
内连接指在一张表中连接,只是把它看成了两张表。比如一张表有三列:员工,员工编号,员工的领导编号,那么员工的领导编号就会等于领导的员工编号,需要使用内连接找到每个员工对应的领导。例:
select a.userCode from user a join user b on b.createdBy=a.id;
// 在同一张表中,要找到与某行的createdBy相等的id的那行。
三,外连接
分为左外连接、右外连接。内连接的特点需要匹配出完全符合条件的。例如:
select u.userCode,r.roleCode from user u right join role r on u.id=r.id;
这就是把满足匹配的数据输出,同时把r中不满足的也一起输出,在u中对应null。因为join前加了right,是右外连接。输出结果:
+----------+----------+
| userCode | roleCode |
+----------+----------+
| admin | ADMIN |
| liming | MANAGER |
| NULL | EMPLOYEE |
+----------+----------+
因为第三行并不是符合条件输出的,而是r必须全部输出,所以userCode是NULL。right表示将join关键字右边的表看成是主表,稍带查询左边的表。因此在外连接之中,两张表产生了主次关系。join前right后可以加上outer,和inner一样,是可以省去的。再如:
select a.userName from user a left join user b on a.id=b.userRole;
这个结果userName会全部显示,符合条件的行的userName会显示多次。
四,三张表连接
语法:
select...from a join b on a和b的连接条件 join c on a和c的连接条件;
一次连接中内外连接可以同时使用。如:
select a.addressDesc,b.productName,r.roleName
-> from address a left join role r
-> on a.id=r.id
-> join bill b
-> on b.id=a.id;
显示结果:
+-------------------------------------------+--------------------------+-----------------+
| addressDesc | productName | roleName |
+-------------------------------------------+--------------------------+-----------------+
| 杭州市上城区潮鸣寺巷44号 | 洗发水、护发素 | 系统管理员 |
| 杭州市西湖区文新街3号 | 香皂、肥皂、药皂 | 经理 |
| 杭州市上城区美术馆后街23号 | 大豆油 | 普通员工 |
| 杭州市滨江区滨江门南大街14号 | 橄榄油 | NULL |
| 杭州市西湖区三墩路南三巷3号 | 洗洁精 | NULL |
| 杭州市江干区下沙工业区18号 | 美国大杏仁 | NULL |
+-------------------------------------------+--------------------------+-----------------+
因为第一个join加了左外连接,所以addressDesc会全部显示出来,导致后面roleName部分是NULL。