连接查询(多表查询) 又称多表查询,当查询的字段涉及多个表的时候,就要用到连接查询 分类: 为表起别名: 提高语句的简洁度区分多个重名字段注意:如果为表起了别名,则查询的字段就不能使用原来的别名去限定 内连接 查询A、B 交集部分数据 语
又称多表查询,当查询的字段涉及多个表的时候,就要用到连接查询
分类:
为表起别名:
查询A、B 交集部分数据
语法:
隐式内连接
select 字段列表 from 表1,表2 where 筛选条件 ;
显式内连接
select 字段列表 from 表1 【inner】 join 表2 on 连接条件 ... ;
查询每一个员工的姓名 , 及关联的部门的名称
表结构: emp , dept
连接条件: emp.dept_id = dept.id
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
若果有员工没有部门,则不会显示
select e.name, d.name from emp as e join dept as d on e.dept_id = d.id;
左外连接相当于查询表A(左表)的所有数据和中间绿色的交集部分的数据。
表1的位置为左表,表2的位置为右表
select 字段列表 from 表1 left 【outer】 join 表2 on 条件...
select 字段列表 from 表1 right 【outer】 join 表2 on 条件...
想把右外连接改成左外连接,并且查询结果不改变,可以把right改为left,并且把表1和表2的位置调换
例题:
select e.*, d.name from emp as eleft outer join dept as don e.dept_id = d.id
表结构: emp, dept
连接条件: emp.dept_id = dept.id
(右外连接)
select d.*, e.* from emp e # 左表right outer join dept as d # 右表 on e.dept_id = d.id;
将右外改为左外
select d.*, e.* from dept as d left outer join emp as e on e.dept_id = d.id;
自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。
select 字段列表 from 表1 as 别名1 join 表1 as 别名2 on 条件....
注意:自连接表一定要起别名
对于自连接查询,可以是内连接查询,也可以是外连接查询。
普通员工和领导其实都属于员工,都在员工表当中,每个员工又有一项manager_id记录这他的领导的id值
用内连接
select a.name , b.name from emp as a , emp as b where a.managerid = b.id;
select a.name as '员工', b.name as '领导' from emp as a left join emp as b on a.managerid = b.id;
select g_name,b_name from boy,bearty
where girl.boufriend_id = boy.id
会用第一个表中的每一行和第二个表中的每一行进行逐个匹配,然后进行筛选,如果匹配会筛选出来
可以自由调换表的顺序
select last_name,department_name
from employees,department
where employees.'department_id',department.'department_id'
当语句中经常出表名作用域的时候,每条语句就会很长,为精简语句
select e.last_name,e.job_id,j.job_title
from employees as e , job as j
where e.'job_id'= j.job_id
查询有奖金的员工名、部门名
select last_name,department_name
from employees as e , department as d
where e.'department_id' = d.'department_id'
and e.'commission_pct'is not null
已经有一个where筛选了不能再用where,用and
查询城市名中第二个字符为o的部门名和城市名
select department_name,city
from department as d,location as l
where d.'location_id' = l.'location_id'
and city like '_o%'
查询每个城市的部门个数
select count(*) as 个数 , city
from department as d = location as l
group by city
查询有奖金的每个部门的部门名,和部门的领导编号和该部门的最低工资标准
select department_name , manager_id,min(salary)
from employees as e , department as d
where e.'department_id' = d.'department_id'
and commission_pct is not null
group by department_name,d,manager_id ;
select job_title , count(*)
from employees as e , job as j
where e.'job_id'= j.job_id
group by job_title
order by count(*) desc
select last_name,department_name,city
from employees as e , department as d , location as l
where e.'department_id' = d.'department_id'
and d.'location_id' = l.'location_id'
也就是把上面的等于号换成了不等于(大于、小于、不等)
select salary,grade_level
from employees e,job_frades g
where salary between g.'lowest_sal' and g.'higthest_sal'
and g.'lowest_sal'='A';
只查看等级为A的把原来这一张表当做多张表来使用,由表中的数据找到另一个数据,再由找到的数据回过头来找另一个数据,可以这样往复下去
select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees.e,employees.m
e代表员工表,m代表领导表。其实都在一张表里,重命名来避免冲突where e.'manager_id' = m.'employees_id'
语法:
select 查寻列表
from 表1 as 别名 【连接类型】
join 表2 as 别名
on【连接条件】
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
查询员工名,部门名
select last_name,department_name
from department d
innner join demployees as e
on e.'department_id' = d.'department_id'
查询名字中包含e的员工名和工种名(添加了筛选条件)
select last_name,job_id
from employees as e
inner join as j
on e.'job_id' = j.'job_id'
where e.'last_name' like '%e%' ;
查询部门个数>3的城市名和部门个数(添加分组、筛选条件)
select city,count(*) as 部门个数
from departments as d
inner hoin locations as l
on d.'location_id' = l.'location_id'
group by city
having count(*)>3
查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序
select count(*) , department_id
from employees as e
inner hoin departments as d
on e.'department_id' = d.'department_id'
group by department_name
having count(*)>3
order by count(*) desc
查询员工的工资级别
select salary,grade_level
from employees as e
join job_grades as g
on e.'salary' between g.'lowest_sal' and g.'hightest_sal' ;
查询每个工资级别的个数,并按照级别降序。
select count(*),grade_level
from employees as e
join job_grades as g
on e.'salary' between g.'lowest_sal' and g.'hightest_sal'
having count(*)>20
order by grade_lecel desc
把原来这一张表当做多张表来使用,由表中的数据找到另一个数据,再由找到的数据回过头来找另一个数据,可以这样往复下去
select m.employee_id,m.last_name
from employees as e
e代表员工表,m代表领导表。其实都在一张表里,重命名来避免冲突join employees as m
on e.'manager_id' = m.'employees_id'
用于查询主表的时候,主表中没有,但是附表中,主表通过外连接附表来查询数据
特点:
外连接查询结果为主表中的所有数据。
如果主表中有与之匹配的显示匹配值。
表中没有与之匹配的显示null
外连接查询结果=内连接结果+主表中有而从表中没有的数据
左外连接,left join左边的是主表
右外连接,right join右边的是主表。
左外和右外交换两个表的顺序可以实现同样的效果。
查询男朋友 不在男神表的的女神名
左外连接
SELECT b.*,bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.'boyfriend_id' = bo.'id'
WHERE b.'id' IS NULL;
查询哪个部门没有员工
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;
全外
USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.'boyfriend_id' = bo.id;
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
功能:sql99支持的较多
可读性:sql99实现连接条件和筛选条件的分离,可读性较高
来源地址:https://blog.csdn.net/mankeywang/article/details/124000347
--结束END--
本文标题: 连接查询(多表查询)——MySQL
本文链接: https://www.lsjlt.com/news/374348.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0