/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。
笛卡尔乘积现象：表一有m行，表2 有n行，结果有m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：按年代分类：SQL92标准：仅仅支持内连接
SQL99标准【推荐】：支持内连接、外连接（左外+右外）和交叉连接
按功能分类：
内连接：等值连接，非等值连接，自连接
外连接：左外连接，右外连接，右外连接
交叉连接：
*/

select * from beauty;
select * from boys;
use girls;
select name,boyname from beauty,boys; 

一：SQL92标准：

1.等值连接

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

案例一：查询女生对应的男神名

select name,boyname from beauty,boys 
where beauty.boyfriend_id = boys.id;

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

use myemployees;
show databases;
select last_name,department_name from employees,departments 
where employees.department_id = departments.department_id;

2.为表其别名

/*
提高语句的简洁度 区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能按原来的表名去限定
*/

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

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

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

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

4.可以加筛选

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

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

查询城市名中第二个字符为o的部门名称和城市名称

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

5.可以加分组

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

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

查询有奖金的每个部门的部门名和部门的领导编号和该部门员工的最低工资

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

6.可以加排序

查询每个工种的工种名和员工的个数，并按员工个数降序排序

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

7.可以实现3表连接

查询员工名，部门名(按升序排序)和所在的城市中以S开头的城市

select last_name,department_name,city 
from employees e,departments d,locations l 
where d.department_id=e.department_id 
and d.location_id=l.location_id
and city like 's%'
order by department_name asc;

二、非等值连接

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 salary,grade_level from employees e,job_grades g 
where salary between g.lowest_sal and g.highest_sal
and g.grade_level = 'A'
order by salary desc;

三、自连接

查询员工名和上级的名称

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