DBA的学习3-优快云博客

本文链接：https://blog.youkuaiyun.com/cuckoooooooooooo/article/details/147190364

连接查询

#### 一、内连接(交集)

> 格式: inner
>
> select [字段] from [表名1]
> inner join [表名2]
> on [连接条件]
> ...;

##### 等值连接查询

```mysql
# 对连接后的查询结果作筛选、分组、排序、过滤
eg:   select e.*,d.dept_name from employees as e
   inner join departments as d
   on e.dept_id=d.dept_id
   where e.employee_id=8;
# 长难句：查询2018年总工资大于30万的员工，按2018年总工资降序排列
select e.employee_id,e.name,sum(s.basic+s.bonus) yearly_salary
from employees e
inner join salary s
on e.employee_id=s.employee_id
where year(s.date)=2018
group by employee_id
having yearly_salary > 300000
order by yearly_salary desc;
```

##### 非等值连接查询

```mysql
# 练习前置
create table tarena.wage_grade(
id int,grade char(1),low int,high int);
# 定义收入级别
insert into tarena.wage_grade values
(1,'A',5000,8000),
(2,'B',8001,10000),
(3,'C',10001,15000),
(4,'D',15001,20000),
(5,'E',20001,1000000);
# 长难句：查询2018年12月员工基本工资级别
select s.employee_id,s.basic,g.grade from salary s
inner join wage_grade g
on s.basic between g.low and g.high
where year(s.date)=2018 and month(s.date)=12;
# 基于上条命令添加员工名称、部门名
select e.name,s.basic,g.grade,d.dept_name from salary s
inner join wage_grade g
on s.basic between g.low and g.high
inner join employees e
on e.employee_id=s.employee_id
inner join departments d
on e.dept_id=d.dept_id
where year(s.date)=2018 and month(s.date)=12
order by s.basic desc;
```

#### 二、外连接

##### 左连接查询

```mysql
SELECT d.dept_name,e.name FROM employees e
LEFT JOIN departments d
ON e.dept_id=d.dept_id
WHERE e.dept_id IS null;

SELECT d.dept_name,e.name FROM departments d
LEFT JOIN employees e
ON d.dept_id=e.dept_id
WHERE e.dept_id IS null;
```

##### 右连接查询

```mysql
SELECT d.dept_name,e.name FROM departments d
RIGHT JOIN employees e
ON d.dept_id=e.dept_id
WHERE d.dept_name IS null;
```

##### 全外连接查询

```mysql
SELECT d.dept_name,e.name FROM departments d
LEFT JOIN employees e
ON d.dept_id=e.dept_id
WHERE e.dept_id IS null
UNION
SELECT d.dept_name,e.name FROM departments d
RIGHT JOIN employees e
ON d.dept_id=e.dept_id
WHERE d.dept_name IS null;
```

#### 三、嵌套查询

##### where之后嵌套查询

```mysql
SELECT * FROM employees WHERE dept_id=(
SELECT dept_id FROM departments
WHERE dept_name="运维部"
);

SELECT * FROM employees WHERE dept_id IN (
   SELECT dept_id FROM departments
WHERE dept_name IN ("人事部","财务部")
);
# 查看2018年12月人事部所有员工的工资情况
SELECT * FROM salary
WHERE year(date)=2018 AND month(date)=12 AND employee_id IN (
   SELECT employee_id FROM employees
WHERE dept_id=(
   SELECT dept_id FROM departments
WHERE dept_name="人事部"
   )
);

SELECT * FROM salary
WHERE year(date)=2018 AND month(date)=12 AND basic > (
SELECT basic FROM salary
WHERE year(date)=2018 AND month(date)=12 AND employee_id=100
);
```

##### having之后嵌套查询

```mysql
# 查询比开发部总人数少的部门人数
SELECT COUNT(name) as cnt,dept_id FROM employees
GROUP BY dept_id
HAVING cnt < (
SELECT COUNT(name) FROM employees
WHERE dept_id=(
       SELECT dept_id FROM departments
       WHERE dept_name="开发部"
   )
);

```

##### from之后嵌套查询

> 将查询的结果作为一个新的表

```mysql
# 查询其他部门总人数比开发部总人数少的部门名称和人数
SELECT d.dept_name,cnt FROM (
SELECT COUNT(name) as cnt,dept_id FROM employees
   GROUP BY dept_id
   HAVING cnt < (
           SELECT COUNT(name) FROM employees
           WHERE dept_id=(
               SELECT dept_id FROM departments
               WHERE dept_name="开发部"
           )
       )
) AS tmp
INNER JOIN departments d
ON tmp.dept_id=d.dept_id;
```

##### select之后嵌套查询

> 将查询外表的字段作为参数，传给第二个自定义字段

```mysql
#
SELECT d.*,(
SELECT COUNT(name) FROM employees e
WHERE e.dept_id=d.dept_id
) cnt
FROM departments d;
```