连接查询概念
连接查询又称为多表查询或者多表连接,当查询的字段来自于多个表或者涉及到多个表时,就需要使用连接查询。
笛卡尔积错误
笛卡尔积原本是代数的概念,他的意思是对于两个不同的集合A,B。对于A中的每一个元素,都有对于在B中的所有元素做连接运算 。可以见得对于两个元组分别为m,n的表。笛卡尔积后得到的元组个数为m * n个元组。而对于mysql来说,默认的连接就是笛卡尔积连接。所以查询的时候如果将m*n条记录全部查询出来就会出现笛卡尔积错误。
连接查询分类
一、按年代分类
sql92
标准,mysql中仅支持内连接。sql99
标准,mysql中支持所有的内连接、左右外连接、交叉连接。
二、按功能分类
- 内连接
内连接又分为:等值连接、非等值连接、自连接。 - 外连接
外连接又分为:左外连接、右外连接、全外连接。 - 交叉连接
三、sql99基本语法
- sql结构
select 查询列表
from 表1 别名 连接类型
join 表2 别名
on 连接条件
where 筛选条件
group by 分组
having 筛选条件
order by 排序列表;
内连接(sql92语法)
一、等值连接
- 等号条件
描述:查询员工名与对应的部门名。
示例:select last_name, department_name from employees, departments where employees.department_id = departments.department_id ;
- 添加字段识别
描述:查询员工号,工种号,工种名。
示例:select employee_id, employees.job_id, job_title from employees, jobs where employees.job_id = jobs.job_id;
或者使用别名:
select employee_id, e, job_title from employees as e, jobs j where e.job_id = j.job_id;
注意:表名起完别名后要查询的字段就不能使用原表名进行限定,如下会报错:
select employees.employee_id, e, job_title from employees as e, jobs j where e.job_id = j.job_id;
- 添加其他筛选条件
描述:查询有奖金的员工名、部门名。
示例:select last_name, e.department_name, commission_pct from employees e, departments d where e.department_id = d.department_id and commission_pct is not null;
- 添加分组
描述:查询每个城市的部门个数。
示例:select count(*), city from departments d, loactions l where d.location_id = l.location_id group by city;
- 添加排序
描述:查询每个工种的工种名和员工个数,并按员工个数进行降序。
示例:select job_title, count(*) from jobs j, employees e where j.job_id = e.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;
- 总结
1.多表等值连接的结果为多表的交集部分;
2.n表连接,至少需要n-1个连接条件;
3.多表的顺序没有要求;
4.一般需要为表起别名;
5.可以搭配其他条件使用。
二、非等值连接
- 非等号条件
描述:查询员工的工资和工资级别。
示例:select salary, salary_level from employees e, job_grades g where e.salary between g.lowest_sal and g.highest_sal;
三、自连接
-
概念
自连接意思就是在自己这张表中查找对应的信息,如员工表中每个员工有自己的manage_id,而manager也属于员工,manager的员工信息也在员工表中,所以查找员工的manger信息就在一张表中查询,就需要用到自连接。 -
查询员工名称和上级的名称。
select e1.employee_id, e1.employee_name, e2.employee_id, e2.employee_name from employees e1, employees e2 where e1.manager_id = e2.employees_id;
内连接(sql99语法)
一、sql99基本语法
- sql结构
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
二、等值连接
-
inner join…on
描述:查询员工名与对应的部门名。
示例:select last_name, department_name from employees e inner join departments d on e.department_id = d.department_id ;
-
添加筛选
描述:查询名字中包含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的城市名和部门个数。
示例:select cout(*), city 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 employees e inner join departments d on e.department_id = d.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;
三、非等值连接
-
非等号条件
描述:查询员工的工资和工资级别。
示例:select salary, salary_level from employees e inner join job_grades g on e.salary between g.lowest_sal and g.highest_sal;
-
添加筛选
描述:查询每个工资级别的个数>20的个数,并按工资级别降序。
示例:select salary, count(*), grade_level from employees e inner 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 ;
四、自连接
- 查询员工姓名和员工领导名。
select e.last_name, m.last_name from employees e inner join employees m on e.employees_id = m.manager_id;
五、外连接
-
应用场景
用于查询主表中的记录,不论从表是否匹配。 -
特点
1、外连接的查询结果为主表中的所有记录,如果从表中有和主表匹配的,则显示匹配的值,如果从表中没有和主表匹配的,则显示null。所以外连接结果=内连接结果+主表中有而从表中没有的记录。
2、左外连接:left join左边的是主表;右外连接:right join右边的是主表。
3、左外和右外交换两个表的顺序可以实现同样的效果。 -
左外连接查询女表中的男朋友情况。
select be.name, bo.* from beauty be left out join boys bo on be.boyfriend_id = bo.id;
显示结果为所有女记录和有男朋友则显示,没有则显示null。 -
右外连接查询女表中的男朋友情况。
select be.name, bo.* from boys bo right outer join beauty be on be.boyfriend_id = bo.id;
显示结果为所有女记录和有男朋友则显示,没有则显示null。 -
查询没有员工的部门信息。
分析:员工表中每个员工有部门ID,但是所有的员工的部门ID不一定包含所有的部门,所以要查没有员工的部门信息,需要外连接,所以主表为部门表,从表为员工表,查询主表所有部门信息,无论从表是否匹配,如果从表匹配则显示匹配信息,如果不匹配则显示null,而显示null的记录就是没有员工的部门信息。
示例:select d.*, e.employee_id from departments d left out join employees e on d.department_id = e.department_id where e.employee_id is null;
-
查询部门名为SAL或IT的员工信息。
分析:部门名为SAL或IT的部门不一定有员工,所以需要使用外连接,就算是null没有员工,也是员工信息,另外部门名一样,部门ID不一定一样。
select e.*, d.department_name from departments d left join employees e on d.department_id = e.department_id where d.department_name in ("SAL", "IT");
六、全外连接
-
语法
select be.*, bo.* from beauty full outer join boys bo on be.boyfriend_id = bo.id;
-
特点
全外连接 = 两表内连接的结果 + 表1中有但表2中没有 + 表2中有但表1中没有;全外连接没有主表和从表,互为主从表。
mysql中是不支持全外连接的。
七、交叉连接
select be.*, bo.* from beauty be cross join boys bo;
sql99语法,显示的是两张表的笛卡尔乘积。
sql92语法是:select be.*, bo.* from beauty be, boys bo;