进阶6：连接查询

1.连接查询

1. 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

select name,boyName from beauty,boys;

2. 笛卡尔乘积现象:表1 有m行，表2 有n行，结果有m*n行

• 发生原因：没有有效的连接条件
• 如何避免：添加有效的连接条件

正解：
select name,boyName from beauty,boys where beauty.boyfriend_id=boys.id;

3. 分类：

• 按年代分：
  sql92标准：仅仅支持内连接
  sql99标准【推荐】：支持内连接 + 外连接（左外和右外） + 交叉连接

• 按功能分

•       * 1. 内连接
        	
                    * 1.1 等值连接
                    * 1.2 非等值连接
                    * 1.3 自连接
  
        * 2. 外连接
                  * 2.1 左外连接
                  * 2.2 右外连接
                  * 2.3 全外连接
  
        * 3.  交叉连接
  

2.sql92标准

2.1.等值连接

2.1.1特点

1. 多表的等值连接的结果为多表的交集部分
2. n表连接，至少需要n-1个连接条件
3. 多表的顺序没有要求
4. 一般需要为表起别名
5. 可以搭配前面介绍的所有子句使用，比如排序，分组，筛选。

案例1：查询女神名和对应的男神名

select NAME,boyName from beauty,boys where beauty.boyfriend_id=boys.id;

案例2：查询员工名和对应的部门名

select last_name,department_name from employees,departments where employees.department_id=departments.department_id;

2.1.2.为表起别名

1. 提高语句的简洁度     
2. 区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

案例3：查询员工名、工种号、工种名

select last_name,employees.job_id,job_title from employees,jobs where employees.'job_id'=jobs.'job_id';

select e.last_name,e.job_id,j.job_title from employees as e,jobs as j where e.job_id=j.job_id;

2.1.3.两个表的顺序可以调换

查询员工名、工种号、工种名

‘select e.last_name,e.job_id,j.job_title from jobs as j,employees as e where e.job_id=j.job_id;’

2.1.4.可以加筛选？

案例4：查询有奖金的员工名、部门名

select e.last_name,d.department_name from employees as e,departments as d where e.department_id=d.department_id and e.commission_pct is not null;

案例5.查询城市名中第二个字符为o的部门名和城市名

select department_name,city from departments d,locations l where d.location_id = l.location_id and city like '_o';

2.1.5.可以加分组？

案例1.查询每个城市的部门个数

select count(*) 个数，city from departments d,locations l where d.location_id=l.location_id group by city;

案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资。

select min(salary),department_name,d.manager_id from departments d,employees e where d.department_id=e.department_id and e.commission_pct is not null group by department_name,manager_id;

2.1.6.可以加排序吗？

案例1.查询每个工种的工种名和员工的个数，并且按员工个数降序。

select count(*),job_title from employees e,jobs j where e.job_id=j.job_id group by job_title order by count(*) desc;

2.1.7.可以三表连接？

案例：查询员工名、部门名和所在的城市

select last_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.2.非等值连接

案例1.查询员工的工资和工资级别

select salary,grade_level from employees e,job_grades j where salary between j.lowest_sal and j.highest_sal;

2.3.自连接

案例：查询员工名和上级的名称

select e.employee_id,e.last_name,m.manager_id,m.last_name from employees e,employees m where e.employee_id=m.manager_id;

3.sql99语法

1. 语法：

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

2. 分类：
   • 内连接（重要）：inner
   • 外连接
     * 左外（重要）：left 【outer】
     * 右外（重要）：right【outer】
     * 全外：full【outer】
   • 交叉连接：cross

3.1内连接

1. 语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

2. 分类：
   等值
   非等值
   自连接

3. 特点：

• a.添加排序，分组，筛选
• b.inner可以省略
• c.筛选条件放到where后面，连接条件放到on后面，提高分离性，便于阅读
• d.inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集。

3.1.1.等值连接

案例1.查询员工名、部门名

select e.last_name,d.department_name from employees e inner join departments d on d.department_id=e.department_id;

案例2.查询名字中包含e的员工名和工种名（添加筛选）

select last_name,job_title from employees e INNER JOIN jobs j on e.job_id=j.job_id where e.last_name like "%e%";

案例3.查询部门个数>3的城市名和部门个数（添加分组和筛选）

• p1:查询每个城市的部门个数
• p2：在p1结果上筛选满足条件的城市

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 employees e inner join departments d on e.department_id=d.department_id group by department_name having count(*)>3 order by count(*) desc;

案例5：查询员工名、部门名、工种名，并按部门名排序（三表连接）

select last_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;

3.1.2.非等值连接

案例1：查询员工的工资级别

SELECT salary,grade_level from employees e inner join job_grades j on salary between lowest_sal and highest_sal;

案例2.查询工资级别>2的个数，并且按工资级别降序

SELECT grade_level,count(*) from employees e inner join job_grades j on salary between lowest_sal and highest_sal group by grade_level having count(*)>2 order by grade_level;

3.1.3.自连接

案例1.查询员工的名字，上级的名字

select e.last_name "员工名",m.last_name "上级名字" from employees e inner join employees m on e.manager_id=m.employee_id;

案例2.查询姓名中包含字符k员工的名字，上级的名字

select e.last_name "员工名",m.last_name "上级名字" from employees e inner join employees m on e.manager_id=m.employee_id where e.last_name like "%k%";

3.2外连接

1. 应用场景：用于查询一个表中有，另一个表没有的记录

2. 特点：

• 外连接的查询结果为主表中的所有记录
  如果从表中有和它匹配的，则显示匹配的值
  如果从表中没有和它匹配的，则显示null
  外连接查询结果=内连接结果+主表中有而从表中没有的记录

• 左外连接：left join左边的是主表
  右外连接：right join右边的是主表

• 左外和右外交换两个表的顺序，可以实现同样的效果

• 全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1中没有的

引入：查询男朋友不在男神表的女神名

• 左外连接：
  select b.name,bo.* from beauty b left outer join boys bo on b.boyfriend=bo.id where bo.id is null;

• 右外连接
  select b.name,bo.* from boys bo right outer join beauty b on b.boyfriend=bo.id where bo.id is null;

案例1.查询哪个部门没有员工

• 左外
  select d.*,e.* from departments d left outer join employees e on d.department_id=e.department_id where e.employee_id is null;

• 右外
  select d.*,e.* from employees e right outer join departments d on d.department_id=e.department_id where e.employee_id is null;

• 全外连接：

select b.*,bo.* from beauty b full outer join boys bo on b.boyfriend_id=bo.id;

• 交叉连接 -----笛卡尔乘积

select b.*,bo.* from beauty b cross join boys bo;

4.练习

1.显示员工的最大工资，工资平均值

select max(salary),avg(salary) 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 job_id 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.id;

5.显示当前日期，以及去前后空格，截取字符串的函数

select now();
select trim(''); select trim(字符 from '');
select substr(str,startindex); 索引从1开始
select substr(str,startindex,length);

6.显示所有员工的姓名，部门号和部门名称

select last_name,e.department_id,department_name from employees e,departments d where e.department_id=d.department_id;

7.查询90号部门员工的job_id和90号部门的location_id

select job_id,location_id from employees e,departments d where e.department_id=d.department_id and e.department_id=90;

8.选择所有有奖金的员工的last_name,department_id,location_id,department_name

select last_name,e.department_id,location_id,department_name from employees e,departments d where e.department_id=d.department_id and commission_pct is not null;

9.选择city在Toronto工作的员a工的last_name,job_id,department_id,department_name

select last_name,job_id,e.department_id,department_name from employees e,departments d,locations l where e.department_id=d.department_id and d.location_id=l.location_id and city = 'Toronto';

10.查询每个工种，每个部门的部门名，工种名和最低工资

select department_name,job_title,min(salary) from employees e,departments d,jobs j where d.department_id=e.department_id and e.job_id=j.job_id group by department_name;

11.查询每个工种、每个部门的部门名，工种名和最低工资

select department_name,job_title,min(salary) from departments d,jobs j,employees e where d.department_id=e.department_id and e.job_id=j.job_id group by department_name;

12.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#
kochar 101 king 100

‘select concat(e.last_name,’ ',101) employees,m.last_name ‘Emp#’,manager_id as ‘manager Mgr#’ from employees e,employees m where e.manager_id=m.employee_id;`

13.查询每个国家下的部门个数大于2的国家编号

select country_id,count(*) from departments d,locations l where d.location_id=l.location_id group by country_id having count(*)>2;

14.查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有则用null填充

select bo.* from boys bo left outer join beauty b on bo.id=b.boyfriend_id where b.id>3;

15.查询哪个城市没有部门

select l.* from locations l left outer join departments d on l.location_id=d.location_id where d.department_id is null;

16.查询部门名为SAL或IT的员工信息

select e.* from employees e left outer join departments d on e.department_id=d.department_id where department_name="SAL" or department_name="IT";
select e.* from employees e left outer join departments d on e.department_id=d.department_id where department_name in ("SAL","IT");