MySQL连接查询

---

前言

内连接：inner join；左连接：left join；右连接：right join；外连接：union；

• 内连接是两个表的交集，即把匹配的关联数据显示出来；
• 左连接是两个表的交集外加左表剩下的数据；
• 右连接是两个表的交集外加右表剩下的数据；
• 外连接是两个表的并集；
• 交叉连接是笛卡尔的乘积；

MySQL连接查询

等值连接

代码如下（示例）：

-- 查询女神名和对应的男神名
select name,boyName
from beauty,boys
where beauty.boyfriend_id = boys.id;

-- 查询员工名和对应的部门名
select last_name,department_name
from employees e ,departments d
where e.department_id = d.department_id;

-- ========================================join========================================
-- 查询员工名和对应的部门名
select last_name,department_name
from employees
inner join departments
on employees.department_id = departments.department_id;

-- 查询名字中包含e的员工名和工种名（添加筛选）
select last_name,job_title
from employees
inner join jobs
on employees.job_id = jobs.job_id
where employees.last_name like '%e%';

-- 查询部门个数>3的城市名和部门个数（添加分组和筛选）
select city,count(*) 部门个数
from departments d 
inner join locations l
on d.location_id = l.location_id
group by city
having count(*) > 3;

-- 查询哪个部门的部门员工个数>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;

-- 查询员工名、部门名、工种名，并按部门名降序
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;

-- ========================================join筛选，排序，分页========================================
use school;
-- 查询 Java程序设计-1 课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join subject as sub
on sub.subjectno = r.subjectno
where subjectname = 'Java程序设计-1' and studentresult > 80
order by studentresult desc
limit 0,10;

-- 查询 C语言-1 前5名同学的成绩信息（学号，姓名，分数）
select s.studentno,studentname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join subject as sub
on r.subjectno = sub.subjectno
where subjectname = 'C语言-1'
order by studentresult desc
limit 0,5;

非等值连接

代码如下（示例）：

-- 查询员工的工资和工资级别
select salary,grade_level
from employees,job_grades
where salary between lowest_sal and highest_sal;

-- ========================================join========================================
-- 查询员工的工资和工资级别
select e.salary,g.grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;

-- 查询工资级别的个数>20的个数，并且按工资级别降序
select g.grade_level,count(*) 个数
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
group by g.grade_level
having count(*) > 20
order by g.grade_level desc;

自连接

代码如下（示例）：

-- 查询员工名和上级的名字
select a.employee_id,a.last_name '员工',b.employee_id,b.last_name '上级'
from employees a,employees b
where a.manager_id = b.employee_id;

-- 查询父节点下所有的子节点
select a.categoryname as '父栏目', b.categoryname as '子栏目'
from category as a, category as b
where a.categoryid = b.pid;

-- ========================================join========================================
-- 查询员工名和上级的名字
select a.last_name '员工',b.last_name '上级'
from employees a 
join employees b
on a.manager_id = b.employee_id

左、右连接

代码如下（示例）：

-- LEFT JOIN等于LEFT OUTER JOIN， RIGHT JOIN等于RIGHT OUTER JOIN

-- 查询男朋友，不在男神表的女神名
select name
from beauty
left join boys
on beauty.boyfriend_id = boys.id
where boys.id is null;

-- 查询哪个部门没有员工 (左外)
select d.*,e.employee_id
from departments d
left join employees e
on d.department_id = e.department_id
where e.employee_id is null;

-- 查询哪个部门没有员工 (右外)
select d.*,e.employee_id
from employees e
right join departments d
on d.department_id = e.department_id
where e.employee_id is null;

外连接

代码如下（示例）：

-- 两个表的并集
SELECT * FROM table1 LEFT JOIN table2 ON table1.typeId=table2.id
UNION
SELECT * FROM table1 RIGHT JOIN table2 ON table1.typeId=table2.id

交叉连接

笛卡尔的乘积

---