MySQL基础学习（五）——连接查询

学习笔记来源于bilibili尚硅谷mysql教程

连接查询

含义：多表查询，当查询的字段涉及多个表的时候需要进行表连接
笛卡尔乘积现象：表1 m行，表2 n行，结果=m*n行
按功能分类：

内连接——等值连接、非等值连接、自连接；
外连接——左外连接、右外连接、全外连接；
交叉连接

一、sql92标准

• 等值连接
• 非等值连接
• 自连接
• 也支持一部分外连接（用于oracle、sqlserver，mysql不支持）

a、等值连接

语法：

select 查询列表
from 表1 别名，表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

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

select last_name,department_name
from employees e, department d
where e.'department_id' = d.'department_id';

1、为表起别名

案例1：查询员工名、工种号、工种名

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

2、 两个表的顺序也可以调换

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

3、可以加筛选

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

select last_name, department_name, commission_pct
from employees e, department 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%';

4、可以加分组

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

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 departments d, employees e
where d.'department_id' = e.'department_id'
and e.'commission_pct' is not null
group by department_name, d.manager_id;

5、加排序

案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

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

6、三表连接

案例：查询员工名、部门名和所在的城市，并且城市名以s开头，并且按照部门名进行降序

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

总结：

（1）多表等值连接的结果为多表的交集部分
（2）n表连接，至少需要n-1个连接条件
（3）多表的顺序没有要求
（4）一般为表起别名
（5）连接可以搭配所有子句 使用：排序分组筛选

b、非等值连接

案例：查询员工的工资和工资级别（也可以加排序、筛选）
首先创建一个工资级别的表

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);

select salary, grade_level
from employees e, job_grades g
where salary between g.'lowest_sal' and g.'highest-sal';

c、自连接

案例：查询员工名和上级的名称（员工表里是所有的员工）

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

练习题

1、显示员工表的最大工资，工资平均值

select max(salary), avg(salary)
from employees;

2、查询员工表的employee_id， job_id， last_name，按department_id降序，salay升序

select employee_id, job_id, last_name
from employees 
order by department_id desc
and salary asc;

3、查询员工表的job_id 包含a和e的， 并且a在e的前面

select job_id
from employees
where job_id like '%a%e%';

4、已知表student 里面有id（学号），name，gradeId（年纪编号），已知表grade 里面有id（年级编号），name（年级名），已知表result 里面有id，score，studentNo（学号）。要求查询姓名、年纪名、成绩

select s.'name', g.'name', r.'score'
from student s, grade g, result r
where s.id=r.studentNo
and g.id = s.gradeId; 

5、显示当前日期，以及去前后空格，截取子字符串的函数

select now();
select  trim('');
#去指定的字符
select trim(字符 from '');
select substr(str, startIndex);
select substr(str, startIndex, length);

6、查询每个国家下的部门个数大于2的国家编号

select country_id, count(*) 部门个数
from departments d, locations l
where d.'location_id'=l.'location_id'
having count(*)>2;

二、sql99标准【推荐使用】

• 内连接：等值连接、非等值连接、自连接
• 外连接：左外、右外、全外（mysql不支持）
• 交叉连接

语法：连接条件和筛选条件分开了

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

连接类型：

内连接：inner
外连接：左外：left 【outer】、右外right 【outer】、全外：：full【outer】
交叉连接：cross

a、内连接

语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件

特点：

可以添加排序、分组、筛选
inner可以省略
筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读

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 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 department_name, count(*) 个数
from departments d
inner join employees e
on e.'department_id' = d.'department_id'
group by departmenta_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、非等值连接

案例：查询员工工资级别

select salary, grade_level
from employees e
join job_grades g
on e.'salary' between g.'lowest_sal' and g.'highest.sal';

3、自连接

案例：查询员工的名字、上级的名字

select e.last_name, m.last_name
from employees e
join employees m
on e.'manager_id' = m.'employee_id';

案例：查询姓名中包含字符X的员工的名字、上级的名字

select e.last_name, m.last_name
from employees e
join employees m
on e.'manager_id' = m.'employee_id'
where e.'last_name' like '%X%';

b、外连接

• 应用场景：用于查询一个表中有，另一个表中没有
• 特点：外连接的查询结果为主表中的所有记录。如果从表中有和它匹配的，则显示匹配的值，如果从表中没有与它匹配的，则显示null
• 外连接查询结果=内连接的结果+主表中有而从表中没有的记录
• 左外连接，left jolin左边的是主表
• 右外连接，right join右边的是主表
• 筛选条件时，最好选择用从表的主键来筛选
• 左外和右外交换两个表的顺序，可以实现同样的效果
• 全外=内连接的结果+表1中有但表2中没有+表 2中有但表1中没有

案例：查询哪个部门没有员工

• 左外连接

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 d.*, e.employee_id
from employees e
right outer join departments d
on d.'department_id' = e.'department_id'
where e.'employee_id' is null;

c、交叉连接：就是实现笛卡尔积

总结：

• 功能：sql99支持的较多
• 可读性：sql99实现连接条件和筛选条件的分离，可读性较高