Mysql连表查询

mysql的连接
语法：
　　select 查询列表
　　rom 表1 别名连接类型
　　join 表2 别名
　　on 连接条件
　　where 筛选条件
　　group by 分组
　　having 筛选条件
　　order by 排序列表
这里用的都是sql99语法
按功能分类：
　　内连接：
　　　　等值连接
　　　　非等值连接
　　　　自连接
　　外连接：
　　　　左外连接
　　　　右外连接
　　　　全外连接
　　　　交叉连接
等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
非等值连接：连接条件不是=
自连接：自己连接自己

连表顺序与效率：表连接时表的数据量大的为主表效率更高

一、内连接

应用场景：用于查询两个表都有的记录，相当于求交集

1.等值连接
案例1：查询员工名、部门名
select last_name,department_name
from employees e 
inner join departments d
on e.department_id=d.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的城市名和部门个数
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 count(*) 个数,department_name
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;
2.非等值连接
案例：查询工资级别的个数>20的个数，并且按工资级别降序
select count(*),grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
group by grade_level
having count(*)>20
order by grade_level desc;
3.自连接
select e.last_name,m.last_name
from employees e
join employees m
on e.manager_id=m.manager_id
where e.last_name like "%k%";

View Code

二、外连接

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

select d.*,e.employee_id
from departments d
left outer join employees e
on d.department_id=e.department_id
where e.employee_id is null;
#案例：查询女神中有男朋友的男朋友信息
select b.name,bo.* from boys bo left outer join beauty b on b.boyfriend_id=bo.id;
#案例：查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
select b.id,b.name,bo.* from beauty b left join boys bo on b.boyfriend_id=bo.id where b.id>3;

View Code

三、全外连接

mysql其实并不支持
全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的

四、交叉连接

交叉连接：没有任何限制条件的连接方式，得到的结果即笛卡尔积

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

View Code