进阶6 连接查询

连接查询概述

1. 引入：
   连接查询又称为多表查询，当整体查询的语句涉及多张表时，要用到连接查询
2. 笛卡尔乘积现象：
   表1有m行，表2有n行，结果=m*n行
   发生原因是没有有效的连接条件，需要添加
3. 分类:
   $$按年代分类\{\begin{array}{l}①sql92标准仅仅支持内连接\\ ②sql99标准（推荐使用）在MySQL中支持内连接、外连接（全外连接除外）和交叉连接\end{array}$$
   $$按功能分类\{\begin{array}{l}①内连接：等值连接，非等值连接，自连接\\ ②外连接：左外连接，右外连接，全外连接\\ ③交叉连接\end{array}$$

use girls;
select * from beauty;
select * from boys;
select name,boyName from beauty,boys;#与实际不符 笛卡尔乘积现象 【用第一张的每一行匹配第二张表的每一行】
select name,boyname from beauty,boys where beauty.boyfriend_id = boys.id;

一、sql92语法

（一）内连接

1、等值连接

• 语法：

  select 查询列表 
  from 表1 别名1,表2 别名2,……
  where 等值的连接条件 
  【and 筛选条件】
  【group by 分组字段】
  【having 分组后的筛选】
  【order by 排序字段】
  

• 特点：
  ①多表等值连接的结果为多表的交集部分
  ②n表连接，至少需要n-1个连接条件
  ③多表的顺序没有要求
  ④一般需要为表起别名 【所有的连接查询都是】
  ⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
  ⑥执行原理：用第一张表中的每一行去跟第二张表中的每一行匹配，逐个检查是否满足连接条件

#1. 用第一张表中的每一行去跟第二张表中的每一行匹配，逐个检查是否满足连接条件
#案例1：查询女神名和对应的男神名 
select name,boyname from beauty,boys where beauty.boyfriend_id=boys.id;
#案例2：查询员工名和对应的部门名称 
use myemployees;
select first_name,department_name from employees,departments 
	where employees.department_id=departments.department_id;

#2. 为表起别名
/*
①提高语句的简洁度 
②区分多个重名的字段 
注：如果为表起了别名，则查询的字段不能使用之前的表名来限定【原因是先执行from子句】
*/
#查询员工名、工种号、工种名 
select first_name,employees.job_id,job_title from employees,jobs where employees.job_id=jobs.job_id;
select first_name,e.job_id,job_title from employees as e,jobs as j where e.job_id=j.job_id;

#3. 两个表的顺序可以调换
#【连接查询的原理是用一张表的行去匹配另一张表，两个表之间谁去匹配谁不影响】
select e.first_name,e.job_id,j.job_title from jobs as j ,employees as e where e.job_id=j.job_id;

#4. 可以做筛选 
#案例1：查询有奖金的员工名、部门名 
select first_name,department_name from employees e,departments d 
	where e.department_id=d.department_id and e.commission_pct is not null;
#案例2：查询城市名中第二个字符为o的部门名和城市名 
select department_name,city from departments d,locations l 
	where d.location_id=l.location_id and l.city like '_o%';

#5. 可以加分组 
#案例1：查询每个城市的部门个数 【locations】,【departments】
select count(*) '城市个数',city from locations l,departments d where l.	location_id=d.location_id 
	group by l.city;#先执行from子句，再执行where子句，相当于把两个表连接起来了，再执行group by子句和select子句
#案例2：查询有奖金的员工所在的部门的名字、部门的领导编号以及部门的最低工资【按照部门分组，涉及到两张表】
select department_name,d.manager_id,min(salary) from departments d,employees e 
	where commission_pct is not null and d.department_id=e.department_id group by e.department_id;
#由于不能保证部门名与领导编号之间是一一对应的关系，所以
select department_name,d.manager_id,min(salary) from departments d,employees e
	where d.department_id=e.department_id and commission_pct is not null group by d.department_name,d.manager_id;
#在employees表中同一个部门会对应着不同的领导
select department_id,manager_id,count(*) from employees group by department_id,manager_id;
#在departments表中一个部门最多只有一个领导者
select *from departments;
#从而每个部门的内部应该是有分组，每个组有组长分管不同的组员
#第一步：将employees表和jobs表连接起来，找出有奖金的员工所在部门名称【先连接，再分组】
select * from employees e,departments d where commission_pct is not null and d.department_id=e.department_id;
#第二步：在第一步生成的表的基础上按照部门编号（部门名称）分组，并查询每个部门的领导编号和最低工资 
select d.department_name,d.manager_id,min(salary) from employees e,departments d 
	where commission_pct is not null and d.department_id=e.department_id group by e.department_id;
#若考虑同属一个部门的员工直属领导不同
select d.department_name,d.manager_id,min(salary) from employees e,departments d 
	where commission_pct is not null and d.department_id=e.department_id group by department_name,manager_id;
#注意执行原理：先连接，再分组
    
#6. 可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序 
select job_title,count(*) from jobs j,employees e where j.job_id=e.job_id 
	group by e.job_id order by count(*) desc;
select job_title,count(*) from jobs j,employees e where j.job_id=e.job_id 
	group by job_title order by count(*) desc;
    
#7. 可以实现三表连接 
#案例：查询员工名、部门名和所在的城市 【employees】 【departments】【locations】
select first_name,department_name,city from employees e,departments d,locations l 
	where e.department_id=d.department_id and d.location_id=l.location_id;
#可以在上面的基础上继续加筛选、排序、分组。

2、非等值连接

• 语法：

    select 查询列表 
    from 表1 别名1,表2 别名2,……
    where 非等值的连接条件 
    【and 筛选条件】
    【group by 分组字段】
    【having 分组后的筛选】
    【order by 排序字段】
  

#创建工资等级
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);
select *from job_grades;
#案例：查询员工的工资和工资级别 
select employee_id,salary,grade_level from employees e,job_grades j_g 
	where salary between j_g.lowest_sal and highest_sal;
#非等值连接可以追加筛选、分组、排序等条件 

3、自连接

• 语法：

    select 查询列表 
    from 表1 别名1,表1 别名2,……
    where 等值的连接条件 
    【and 筛选条件】
    【group by 分组字段】
    【having 分组后的筛选】
    【order by 排序字段】
  

#案例：查询员工名以及他的上级的名称 
select e.employee_id,e.first_name,l.employee_id,l.first_name from employees e,employees l 
	where e.manager_id=l.employee_id;

例题

#例题1：显示员工表的最大工资，工资平均值 
select max(salary) '最大工资',round(avg(salary),2) '工资平均值' from employees;
#例题2：查询员工表中的employee_id,job_id,last_name,按department_id降序，salary升序
select employee_id,job_id,last_name from employees order by department_id desc,salary asc;
#例题3：查询员工表的job_id中包含a和e的，并且a在e前面 
select * from employees where job_id like '%a%e%';
#例题4：
/*已知表student，里面有id(学号)，name，gradeId(年级编号)
已知表grade，里面有id(年级编号)，name(年级名)
已知表result，里面有id，score，studentNo(学号)
*/
#要求查询姓名、年级名、成绩
#select s.name,g.name,r.score from student s,grade g,result r where s.gradeId=g.id and s.id=r.studentNo;
#例题5：显示当前日期，以及去前后空格，截取子字符串的函数 
select curdate();
#trim();substr();

#例题1：显示所有员工的姓名、部门号和部门名称 
select first_name,e.department_id,d.department_name from employees e,departments d
	where d.department_id=e.department_id;
#例题2：查询90号部门员工的job_id和90号部门的location_id 【employees】【departments】
select job_id,location_id from employees e,departments d 
	where e.department_id=90 and d.department_id=90 and e.department_id=d.department_id;
#例题3：选择所有有奖金的员工的last_name,department_name,location_id,city
select last_name,department_name,d.location_id,city from employees e,departments d,locations l 
	where e.department_id=d.department_id and d.location_id=l.location_id and e.commission_pct is not null;
#例题4：选择city在Toronto工作的员工的last_name,job_id,department_id,department_name
select last_name,job_id,d.department_id,department_name from employees e,departments d,locations l 
	where l.city='Toronto' and e.department_id=d.department_id and d.location_id=l.location_id;
#例题5：查询每个工种、每个部门的部门名、工种名和最低工资 
select department_name,job_title,min(salary) '最低工资' from employees e,jobs j,departments d 
	where e.department_id=d.department_id and e.job_id=j.job_id group by e.job_id,e.department_id;
#例题6：查询每个国家下的部门个数大于2的国家编号【locations】【departments】
select country_id,count(*) '部门个数' from locations l,departments d 
	where d.location_id=l.location_id group by country_id having count(*)>2;
#例题7：选择指定员工的姓，员工号，以及他的管理者的姓名和员工号 【kochar】
select e.last_name 'employees',e.employee_id 'Emp#',m.last_name 'manager',m.employee_id 'Mgr#'
	from employees e,employees m where e.manager_id=m.employee_id and e.employee_id=101;

二、sql99语法

• 语法（将连接条件与分组前筛选条件分开了，提高了可读性）：

    select 查询列表 
    from 表1 别名1 【连接类型】
    join 表2 别名2 
    on 连接条件 
    【where 筛选条件】
    【group by 分组】
    【having 筛选条件】
    【order by 排序列表】
  

• 分类
  $$分类（重点介绍）\{\begin{array}{l}(!)内连接：inner\\ 外连接\{\begin{array}{l}(!)左外连接：left【outer】\\ (!)右外连接：right【outer】\\ 全外连接：full【outer】\end{array}\\ 交叉连接：cross\end{array}$$

（一）内连接

• 语法：

    select 查询列表 
    from 表1 别名1 
    inner join 表2 别名2
    on 连接条件
    【where 筛选条件】
    【group by 分组】
    【having 筛选条件】
    【order by 排序列表】    
  

• 分类：等值连接 非等值连接 自连接
• 特点：
  ①添加排序、分组、筛选
  ②inner可以省略
  ③筛选条件放在where后面，连接条件放在on的后面
  ④inner join连接和sql92中的等值连接的实现效果是一样，都是查询多表的交集

1、等值连接（与92语法的执行原理完全一致）

#案例1：查询员工名、部门名（调换位置也可以）
select department_name,first_name from employees e 
	inner join departments d on e.department_id=d.department_id;
#案例2：查询名字中包含e的员工名和工种名 
select first_name,job_title from employees e inner join jobs j
	on e.job_id=j.job_id where first_name like '%e%';
#案例3：查询部门个数>3的城市名和部门个数（添加分组和筛选）
select city, count(*) from departments d inner join locations l 
	on d.location_id=l.location_id group by city having count(*)>3;
#案例:4：查询部门的员工个数>3的部门名和员工个数，并按员工个数降序（添加排序）
select department_name,count(*) from departments d inner join employees e 
	on d.department_id=e.department_id group by department_name having count(*)>3 order by count(*) desc;
#案例5：查询员工名、部门名、工种名，并按部门名降序 
select first_name,department_name,job_title from employees e inner join departments d 
	on e.department_id=d.department_id inner join jobs j on e.job_id=j.job_id order by department_name desc;

2、非等值连接

#案例1：查询员工的员工号，工资和工资级别 
use myemployees;
select employee_id,salary,grade_level from employees e 
	inner join job_grades j_g on salary between lowest_sal and highest_sal;
#案例2：查询每个工资级别的员工人数>20的工资级别，并按工资级别降序 
select grade_level,count(*) from employees e join job_grades j_g on salary 
	between lowest_sal and highest_sal group by grade_level having count(*)>20 order by grade_level desc;

3、自连接

#案例3：查询名字中包含字母k的员工的员工号，员工名，以及他的领导的编号，领导的名字 
select e.employee_id,e.first_name,m.employee_id,m.first_name from employees e join employees m 
	on e.manager_id=m.employee_id where e.first_name like '%k%';

（二）外连接

• 应用场景：查询一个表中有，另一个表中没有的记录
• 特点：
  ①外连接的查询结果为主表中的所有记录
  如果从表中有和它匹配的，则显示匹配的值
  如果从表中没有和它匹配的，则显示为null
  外连接的查询结果=内连接结果+主表中有而从表中没有的记录
  ②左外连接：left join左边的为主表
  右外连接：right join右边的为主表
  ③左外和右外交换两个表的顺序，可以实现同样的结果
  ④全外连接=内连接的结果+表1中有但表2没有的记录+表2中有但表1中没有的记录

use girls;
select * from beauty;
select * from boys;

1、左外or右外连接

#案例1：查询男朋友不在男神表中的女神名
#最好选从表中的主键列
select g.`name`,b.* from beauty g left join boys b on g.boyfriend_id=b.id where b.id is null;
#使用右外连接 
select g.name from boys b right join beauty g on g.boyfriend_id=b.id where b.id is null;
#变形：将boys作为主表
select g.*,b.* from boys b left join beauty g on g.boyfriend_id=b.id;
#案例2：查询哪个部门没有员工 
use myemployees;
select d.* from departments d 
	left join employees e on d.department_id=e.department_id where e.employee_id is null;

2、全外连接（MySQL不支持）

/*
#案例
use girls;
select g.*,b.* from beauty g full outer join boys b on g.boyfriend_id=b,id;
*/

（三）交叉连接

【99语法用交叉连接来实现笛卡尔乘积】【92语法中用,就可以实现】

use girls;
select g.*,b.* from beauty g cross join boys b;#没有连接条件 

三、sql92VSsql99

功能：sql99支持的较多
可读性：sql99实现了连接条件和筛选条件的分离，可读性较高

练习题

#案例一、查询编号>3的女神的男朋友信息，如果有就列出详细，如果没有，用null填充 
select g.id '女神编号',g.name,b.* from beauty g left join boys b on g.boyfriend_id=b.id where g.id>3;
#案例二、查询哪个城市没有部门 【locations】【departments】
use myemployees;
select l.city from locations l left join departments d on d.location_id=l.location_id 
	where d.department_id is null;#从表的主键列为null
#案例三、查询部门名为SAL或IT的员工信息 【departments】【employees】 一个部门名称对应好几个部门编号
select e.* ,d.department_name,d.department_id from departments d left join employees e on e.department_id=d.department_id 
	where department_name='SAL' or department_name='IT';#用外连接，IT中没有员工的两个部门也显示出来了 ，内连接不行 
select * from departments;