MySQL 总结之路（九）----等值链接

声明：mysql 总结之路是在b站上观看视频进行总结的，mysql 在我上大学的时候已经学过了，同时也学过sql  server了，本次总结看看自己能不能温故知新，让自己更加快速的成长。

#进阶 
use myemployees;

use girls;
/*
链接查询 

分类：sql92 只支持链接 

sql99

内链接  外连接  交叉链接 
*/

select * from beauty;
select * from boys;
select * from admin;

#案例1 查询女神名与对应的男神名 
select  name, boyName from beauty,boys 
where beauty.boyfriend_id = boys.id;


#案例2 查询员工名合对应的部门名 
select last_name,department_name from departments,employees
where departments.department_id = employees.department_id;


#2 为表起别名 
/*
注意 取别名后不能使用之前的表名
*/
#查询员工名 、工种号 、工种名 

select e.last_name,e.job_id,j.job_title
from employees as e,jobs as j  #可以调换 
where e.job_id = j.job_id;


#4 可以加筛选 
#案例 ：查询有奖金的员工名、部门名 
select last_name, department_name,commission_pct
from employees as e, departments as d
where e.department_id = d.department_id 
and e.commission_pct is not null;

#案例2 查询城市名中第二个为o 的部门名与部门个数
select department_name, city
from departments as d, locations as l
where d.location_id = l.location_id
and city like '_o%';

#可以添加分组
#案例1 查询每个城市的部门个数 
select count(*) as '个数',city
from departments as d, locations as l
where d.location_id = l.location_id
group by city;

#案例2 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资 。
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  department_name,d.manager_id;

#6 可以加排序 

#案例：查询每个工种的工种名和员工的个数。并且按员工个数降序。
select job_title, count(*) from jobs j, employees e
where e.job_id = j.job_id
group by job_title
order by count(*) desc;

#可以实现三表链接。 
#案例： 查询员工名 部门名、所在地的城市 
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
and city like 's%' 
order by city desc;