java -sql多表连接查询

笛卡尔乘积现象: 如果连接俩张表查询 最终查询数为俩张表数的乘积

1 关于表的别名
select t.name,d.dname from t_user t, dept d;

• 执行效率高
• 可读性好

// 笛卡尔乘积现象
mysql> select name,dname from t_user,dept;
+-----------+-----------+
| name      | dname     |
+-----------+-----------+
| Ann       | 测试部    |
| Ann       | 开发部    |
| Ann       | 市场部    |
| Ann       | 设计部    |
| Ann       | 采购部    |
| HanMeimei | 测试部    |
| HanMeimei | 开发部    |
| HanMeimei | 市场部    |
.......................................等等
+-----------+-----------+
50 rows in set (0.01 sec)

2 避免笛卡尔现象 -条件过滤(并非减少了记录的匹配次数,只不过显示的是满足条件有效的记录)

 //找出每个人的部门名称 要求显示员工名和部门名
select d.name,.d.dname from t_user t ,dept d where t.deptno =d.deptno;//SQL92语法
+-----------+-----------+
| name      | dname     |
+-----------+-----------+
| LiLei     | 测试部    |
| HanMeimei | 测试部    |
| Lucy      | 采购部    |
| Lili      | 设计部    |
| WeiHua    | 采购部    |
| ZhangWei  | 设计部    |
| Ann       | 采购部    |
| Lisa      | 市场部    |
| ZhangWei  | 测试部    |
| Ka_te     | 市场部    |
+-----------+-----------+

3.内连接(等值连接 非等值连接)

• 等值连接: 条件是等量关系

 //找出每个人的部门名称 要求显示员工名和部门名
select t.name,.d.dname from t_user t ,dept d where t.deptno =d.deptno;//SQL92语法
select t.name,d.dname from t_user t inner join dept d on t.deptno =d.deptno;//SQL99语法
SQL99语法语法:  ....A表 (inner 可以省略 表示内连接) join B表 on 俩表的连接条件 where ...
特点：表的连接条件和后来的where条件分离了

• 非等值连接

//找出每个员工的工资登级 要求显示员工名 工资 工资登级

select t.name,t.sal,s.gread from t_user t, salgrade s where t.sal>=s.losal and t.sal<=s.hisal;

select t.name,t.sal,s.gread from t_user t join salgrade s on t.sal between s.losal and s.hisal;

4.自连接:一张表看做俩张表 自己连接自己 也是等值连接

//找出没有员工的上级领导 要求显示员工名和对应的领导名 都在一张表
select a.name as '员工名', b.name as '领导名' from t_user a join t_user b on a.mgr = b.num;
+-----------+-----------+
| 员工名    | 领导名    |
+-----------+-----------+
| HanMeimei | HanMeimei |
| Lucy      | HanMeimei |
| WeiHua    | HanMeimei |
| ZhangWei  | ZhangWei  |
| Ann       | ZhangWei  |
| Lisa      | Ann       |
| ZWei      | Ann       |
| Ka_te     | Ann       |

5.外连接（一般用外连接居多）

• 内连接：假设A和B表进行连接 使用内连接的话 凡是A表和B表能够匹配上的记录查询处理 这就是内连接 AB俩张表没有主副之分 俩张表是平等的。
• 外连接:假设A和B表进行连接 使用外连接的话，AB表有一张表是是主表,一张表是副表 主要查询主表中的数据 捎带查询副表，当主表中的数据没有和副表的数据匹配上 副表自动模拟出NULL与之匹配
• 外连接的分类 左外链接 右外连接
左外链接: 表示左边的表是主表
  右外连接: 表示右边的表是主表
  outer 可以省略

//找出每个员工的上级领导
// 外连接 
select a.name as '员工名', b.name as '领导名' from t_user a left join t_user b on a.mgr = b.num;
+-----------+-----------+
| 员工名    | 领导名    |
+-----------+-----------+
| HanMeimei | HanMeimei |
| Lucy      | HanMeimei |
| WeiHua    | HanMeimei |
| ZhangWei  | ZhangWei  |
| Ann       | ZhangWei  |
| Lisa      | Ann       |
| ZWei      | Ann       |
| Ka_te     | NULL      |

//没有主副之分的情况
select a.name as '员工名', b.name as '领导名' from t_user a join t_user b on a.mgr = b.num;
+-----------+-----------+
| 员工名    | 领导名    |
+-----------+-----------+
| HanMeimei | HanMeimei |
| Lucy      | HanMeimei |
| WeiHua    | HanMeimei |
| ZhangWei  | ZhangWei  |
| Ann       | ZhangWei  |
| Lisa      | Ann       |
| ZWei      | Ann       |

• 三张表怎么查询
  .... A join B join C on ....: 表示A表和B表先连接之后A表再继续与C表进行连接

//找出每一个员工的部门名称以及工资等级

select e.name,d.dname,s.gread
from t_user e 
join dept d 
on e.deptno = d.deptno 
join salgrade s 
on e.sal between s.losal and s.hisal; 
+--------+-----------+-------+
| name   | dname     | gread |
+--------+-----------+-------+
| Lucy   | 采购部    | 2     |
| ZWei   | 测试部    | 2     |
| Ann    | 采购部    | 4     |
| Ka_te  | 市场部    | 4     |
| WeiHua | 采购部    | 5     |
| Lisa   | 市场部    | 5     |
//--------------外连接---------------------------------
select e.name,d.dname,s.gread
from t_user e 
left join dept d 
on e.deptno = d.deptno 
left join salgrade s 
on e.sal between s.losal and s.hisal; 
+-----------+-----------+-------+
| name      | dname     | gread |
+-----------+-----------+-------+
| HanMeimei | 测试部    | NULL  |
| Lucy      | 采购部    | 2     |
| WeiHua    | 采购部    | 5     |
| ZhangWei  | 设计部    | NULL  |
| Ann       | 采购部    | 4     |
| Lisa      | 市场部    | 5     |
| ZWei      | 测试部    | 2     |
| Ka_te     | 市场部    | 4     |
+-----------+-----------+-------+
//-------------多表查询➕自连接----------------------
//找出每一个员工的部门名称以及工资等级以及上级领导
select e.name  as '员工',d.dname,s.gread,e1.name  as '领导'
from t_user e 
join dept d 
on e.deptno = d.deptno 
join salgrade s 
on e.sal between s.losal and s.hisal
left join t_user e1
on e.mgr = e1.num;
+-----------+-----------+-------+-----------+
| 员工      | dname     | gread | 领导      |
+-----------+-----------+-------+-----------+
| ZhangWei  | 设计部    | 1     | ZhangWei  |
| Lucy      | 采购部    | 2     | HanMeimei |
| ZWei      | 测试部    | 2     | Ann       |
| Ann       | 采购部    | 4     | ZhangWei  |
| Ka_te     | 市场部    | 4     | NULL      |
| HanMeimei | 测试部    | 5     | HanMeimei |
| WeiHua    | 采购部    | 5     | HanMeimei |
| Lisa      | 市场部    | 5     | Ann       |