select 连接查询

最新推荐文章于 2022-11-10 19:29:41 发布

Lovely_red_scarf

最新推荐文章于 2022-11-10 19:29:41 发布

阅读量664

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/Lovely_red_scarf/article/details/107434918

版权

mysql 专栏收录该内容

17 篇文章 1 订阅

订阅专栏

什么时连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

1: 连表查询只有当你需要表与表之间的信息当你想要把不同表的信息显示在一个表中那么你可以根据相同的信息或者不同的信息来操作让两个表通过一个表显示

2:连表是让多个表的你需要的信息显示在一个虚拟表中

3: 只是适用于你查询的时候让查询的多个表的信息汇总在一个表中当你的这个语句结束就没有了效果

笛卡尔积现象

在连表查询的时候经常会出现一种笛卡尔积现象
如果两张表进行连接查询的时候没有连接条件时，查询结果就是两张表的记录条数的乘积

表1 有m行，表2有n行，结果=m*n行
迪科尔基相当于拿着第一个表的每一个记录去匹配第二个表的所有记录就相当于表1 的第一条去对应表2的所有记录然后表1的第二条再对应表2的所有记录然后依次类推

避免笛卡尔积的现象时可以通过表的关系来添加连接条件来避免的

避免笛卡尔积现象的话会减少匹配次数吗？
不会，匹配的次数还是不变的，并不能提高执行效率，

sql92与sql99语法

sql92一些老的dba还在使用的语法
sql99 新语法 join
sql99 语法结构更清晰，因为表连接条件使用on和和后面的过滤条件使用where分离了，使人更清晰的知道哪里是连表哪里是过滤

查询的表关系

表的连接

表的连接可以分为
    内连接：
        等值连接
        非等值连接
        自连接

外连接：
        左外连接
        右外连接
        全外连接

交叉连接

等值连接

内连接之等值连接：最大的特点是：条件是等量关系

等值连接就是找到两个表内相同的条件字段然后from两个表中间用逗号隔开。利用两个表内相同的条件来查找

① 多表等值连接的结果为多表的交集部分，也就是多表内的条件字段
②n表连接，至少需要n-1个连接条件,3个表连接至少需要两个条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选



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


# 查询员工名，工种号， 工种名 
# 连接查询就是找到连接表之间的对应的字段信息 
select last_name 员工名, employees.job_id, jobs.job_title from employees, jobs where employees.job_id = jobs.job_id;
# 两个表的顺序是可以调换

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


#4、可以加筛选


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

select last_name, departments.department_name from employees as e, departments where commission_pct is not null and e.department_id = departments.department_id;

#案例2：查询城市名中第二个字符为o的部门名和城市名

select d.department_name, l.city from departments as d, locations as l where d.location_id =l.location_id and d.department_name like "_o%";



#5、可以加分组
#案例1：查询每个城市的部门个数
select l.city, COUNT(*) from departments d, locations l WHERE d.location_id = l.location_id GROUP BY city;


#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

select d.department_name, d.manager_id, MIN(salary) from departments d, employees e where d.department_id = e.department_id and commission_pct is not null group by d.department_name,d.manager_id;



#6、可以加排序


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


select jobs.job_title 工种名, COUNT(*) 员工个数 from jobs, employees e where jobs.job_id = e.job_id group by jobs.job_title  order by COUNT(*) desc;




#7、可以实现三表连接？

#案例：查询员工名、部门名和所在的城市


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
	and city like "%e%";

非等值连接


#案例1：查询员工的工资和工资级别


SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';

自连接

一个表可以自己和自己连接然后查询，然后自己和自己连接进行起不同的别名然后查询

#3、自连接

#案例：查询 员工名和上级的名称
 
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`;

Pravtices

	# 一显示员工表最大薪资，工资平均值
	
	select MAX(salary) 最大薪资, AVG(salary) 平均薪资 from employees;
	
	
	# 二 查询员工表的employee_id,job_id,last_name, ，按department_id 降序，salary升序
	
	
	select employee_id, job_id,last_name from employees order by department_id desc ;
	
	
	# 查询员工被的job_id种包含 a和e的，并且a在e前面
	
	select job_id from employees where job_id like "%a%e%";
	
	
	#案例：查询 员工名和上级的名称
	
	
	select e.last_name, e.manager_id from employees e,employees m
	
	where e.employee_id = m.manager_id
	;
	
	
	
	# 1 显示所有员工的姓名，部门和部门名称
	
	select e.last_name, d.department_id,d.department_name from employees e, departments d  where e.department_id = d.department_id;
	
	# 查询90号部门员工的jo_id 和90号部门的location_id 
	
	
	select e.last_name, e.job_id, e.department_id, d.location_id  from employees e, departments d  where e.department_id = d.department_id and e.department_id = 90;
	
	
	# 查询所有有奖金的员工的last_name, department_name ,location_id, city 
	
	select  last_name, d.department_name, d.location_id, l.city
	from employees e, departments d, locations l   where e.department_id = d.department_id and d.location_id = l.location_id and e.commission_pct is not null;
	
	
	
	# 4 选择city在Toronto工作的员工的last_name,job_id,department_id, department_name;
	select last_name, e.job_id, d.department_id, d.department_name from employees e, departments d,locations l where e.department_id = d.department_id and d.location_id = l.location_id  and  l.city = 'Toronto';
	
	
	# 查询每个工种，每个部门的部门名，工种名和最低薪资
	select  job_title, d.department_name, MIN(salary) from jobs, departments d, employees e where jobs.job_id = e.job_id and d.department_id = e.department_id group by job_title, d.department_name;
	
	
	# 6 : 查询每个国家下的部门个数大于2的国家编号
	
	select  l.country_id ,COUNT(*) 部门个数 from departments d, locations l
	
where d.location_id = l.location_id   group by l.country_id HAVING COUNT(*) > 2 ;   -- 一涉及的group by 分组就要考虑是分组前还是分组后 分组后的就是having,count(*)在department 和location表中都不存在就是分组后出现的  需要放在having后


/*
# 选择指定员工的姓名， 员工号， 以及他的管理者的姓名和员工号，结果类似玉下面的格式

   employees     Emp#   manager  Mgr#
		kochhar 101   king     100
	
	*/
	
	select e.last_name employees, e.employee_id "Emp#", m.last_name as manager, m.employee_id "Mgr#" from employees e, employees m

	where e.manager_id = m.employee_id  and e.last_name = "kochhar";

sql99语法

sql99语法也就是1999年又推出的新的语法
inner/left/right join 内连接/左连接/右连接

连表的 join后的筛选条件就是把之前的where更换成了on 然后筛选条件放在on后

语法

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

其中的连接类型分为
内连接（★）：inner
外连接
	左外(★):left 【outer】
	右外(★)：right 【outer】
	全外：full【outer】
交叉连接：cross

内连接

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

特点

①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集


# inner join 就是sql92的等值连接的使用找到两个表的 相同的条件 将sql92语法的等值连接 转化为iner join  on 

#1、等值连接
#案例1.查询员工名、部门名

select last_name as 员工名, 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 on e.job_id = jobs.job_id and last_name like "%e%";

-- 筛选条件可以放在on后然后使用and连接 也可以使用where
select last_name, job_title 工种名 from employees e inner join  jobs on e.job_id = jobs.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 employees e inner join departments d on e.department_id = d.department_id group by department_name HAVING COUNT(*) > 3  order by COUNT(*) desc ;


#5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
-- 多重连接就是多使用几次inner join  on  每一次的inner join只可以连接两个表
select  last_name 员工名, department_name  部门名, job_title 工种名
 from employees e inner join departments d on e.department_id = d.department_id inner join jobs on e.job_id = jobs.job_id  order by department_name desc ;

非等值连接使用inner join

使用join 进行非等值的连接


#二）非等值连接

#查询员工的工资级别

SELECT salary,grade_level
FROM employees e
 JOIN job_grades g
 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
 
 
 #查询工资级别的个数>20的个数，并且按工资级别降序
 SELECT COUNT(*),grade_level
FROM employees e
 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
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`;
 
  #查询姓名中包含字符k的员工的名字、上级的名字
 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 '%k%';

外连接

什么是外连接？
外连接和内连接有什么区别？
内连接：

假设A和B表连接，使用内连接的话，凡是A和B 表能够匹配上的记录查询出来
外连接：

假设A和B表进行连接，使用外连接的话，AB两张表中有一张是主表，一张是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表匹配上，副表自动模拟出null与之匹配

外连接的分类

右外连接（右连接）表示右边的表是主表
左外连接（左连接）表示左边的是主表


应用场景：用于查询一个表中有，另一个表没有的记录
 
 特点：
 1、外连接的查询结果为主表中的所有记录
	如果从表中有和它匹配的，则显示匹配的值
	如果从表中没有和它匹配的，则显示null
	外连接查询结果=内连接结果+主表中有而从表没有的记录
 2、左外连接，left join左边的是主表
    右外连接，right join右边的是主表
 3、左外和右外交换两个表的顺序，可以实现同样的效果 
 4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
 5： 外连接的outer 可以省略不写 一般都是省略的

外连接的重要特点是：主表的数据无条件的查出
外连接的重点是: 找出主副表


#案例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;

在这里插入图片描述

主表和从表的区分
如果查询的结果主要来自哪个表哪个表就是主表，如果查询哪个部门没有员工的信息，部门表和员工表，那么部门表就是主表因为我们主要是想查看没有部门的信息


# 查询哪个城市没有部门

-- 左外连接
select city, d.department_id from locations l left join departments d on l.location_id = d.location_id where d.department_id is null;

-- 右外连接

select l.*, d.department_id from departments d right join locations l on d.location_id = l.location_id where d.department_id is null;


# 查询部门名为SAL或IT的员工信息
select d.department_name, e.employee_id,e.last_name from employees e inner join departments d on e.department_id = d.department_id where d.department_name = 'SAL' or d.department_name = 'IT';
-- in

select d.department_name, e.employee_id,e.last_name from employees e inner join departments d on e.department_id = d.department_id where d.department_name in ('SAL','IT');

-- 左连接
select d.*, employee_id from departments d left join employees e on d.department_id = e.department_id where d.department_name in ('SAL','IT');

-- 右连接
select e.last_name, d.*from employees e right join departments d on e.department_id = d.department_id  where d.department_name = 'SAL' or d.department_name = 'IT';

连接查询的选择

在这里插入图片描述

交叉连接和全外连接

 #全外
 
 
 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;

Lovely_red_scarf

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
select 连接查询

什么时连接查询含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询1: 连表查询只有当你需要表与表之间的信息当你想要把不同表的信息显示在一个表中那么你可以根据相同的信息或者不同的信息来操作让两个表通过一个表显示2:连表是让多个表的你需要的信息显示在一个虚拟表中3: 只是适用于你查询的时候让查询的多个表的信息汇总在一个表中当你的这个语句结束就没有了效果笛卡尔积现象在连表查询的时候经常会出现一种笛卡尔积现象表1 有m行，表2有n行，结果=m*n行迪科尔基
复制链接

扫一扫