MySQL——连接查询

最新推荐文章于 2024-07-19 00:12:38 发布

我真的很帅阿

最新推荐文章于 2024-07-19 00:12:38 发布

阅读量339

点赞数

分类专栏： MySQL数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_61028090/article/details/132678903

版权

MySQL数据库专栏收录该内容

16 篇文章 0 订阅

订阅专栏

2023.9.4

连接查询相关sql92语句笔记：

#连接查询。 又称多表查询，当查询的字段来自多个表时，就会用到连接查询。
#等值连接
/*
①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面的子句使用，如：排序、分组、筛选
*/
#找女神对应的男神名
SELECT NAME,boyName FROM `beauty`,`boys` WHERE `beauty`.`boyfriend_id`=`boys`.`id`;
#查询员工名和对应的部门名
SELECT `last_name`,`department_name` FROM `employees`,`departments` WHERE `employees`.`department_id`=`departments`.`department_id`;
#查询员工名，工种号，工种名
SELECT `last_name`,`jobs`.`job_id`,`job_title` FROM `employees`,`jobs` WHERE `employees`.`job_id`=`jobs`.`job_id`;
#查询有奖金的员工名、部门名
SELECT `last_name`,`department_name` FROM `employees`,`departments` WHERE `employees`.`department_id`=`departments`.`department_id` AND `employees`.`commission_pct` IS NOT NULL;
#查询城市名中第二个字符为o的部门名和城市名
SELECT `department_name`,`city` FROM `departments`,`locations` WHERE `departments`.`location_id`=`locations`.`location_id` AND `locations`.`city` LIKE '_o%';
#查询每个城市的部门个数
 SELECT COUNT(*),`city` FROM `departments`,`locations` WHERE `departments`.`location_id`=`locations`.`location_id` GROUP BY `city`;
#查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT `department_name`,`departments`.`manager_id`,MIN(salary) FROM `departments`,`employees` WHERE `departments`.`department_id`=`employees`.`department_id` AND `commission_pct` IS NOT NULL GROUP BY `department_name`,`departments`.`manager_id`;
#查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT `job_title`,COUNT(*) FROM `jobs`,`employees` WHERE `jobs`.`job_id`=`employees`.`job_id` GROUP BY `job_title` ORDER BY COUNT(*) DESC;
#查询员工名、部门名和所在的城市
SELECT `last_name`,`department_name`,`city` FROM `employees`,`departments`,`locations` WHERE `employees`.`department_id`=`departments`.`department_id` AND `departments`.`location_id`=`locations`.`location_id`
#内连接 之 非等值连接
#查询员工的工资和工资级别
SELECT salary,`grade_level`
FROM `employees`,`job_grades`
WHERE salary BETWEEN `job_grades`.`lowest_sal` AND `job_grades`.`highest_sal`;     
#内连接 之 自连接
#查询员工名和上级的名称
SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,m.`last_name`
FROM `employees` AS e,`employees` AS m
WHERE e.`employee_id`=m.`manager_id`;

连接查询sql92课后习题：

#1.显示所有员工的姓名，部门号和部门名称。
SELECT e.`last_name`,e.`department_id`,d.`department_name`
FROM `employees` e,`departments` d
WHERE e.`department_id`=d.`department_id`;
#2.查询90号部门员工的job_id和90号部门的location_id
SELECT `job_id`,`location_id`
FROM `employees` e,`departments` d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;
#3.选择所有有奖金的员工的last_name , department_name , location_id , city
SELECT e.`last_name`,d.`department_name`,l.`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 e.last_name , e.job_id , d.department_id , d.department_name,l.city
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';
#5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT `department_name`,`job_id`,MIN(salary)
FROM `employees` e,`departments` d
WHERE e.`department_id`=d.`department_id`
GROUP BY `job_id`,`department_name`;
#6.查询每个国家下的部门个数大于2的国家编号
SELECT `country_id`,COUNT(*) 部门个数
FROM `locations` l,`departments` d
WHERE d.`location_id`=l.`location_id`
GROUP BY `country_id`
HAVING COUNT(*)>2;
#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
#employees	Emp#	manager	Mgr#
#kochhar		101	king	100
SELECT e.`last_name` employees,e.`employee_id` 'Emp#',m.`last_name` manager,m.`employee_id` 'Mgr#'
FROM `employees` e,`employees` m
WHERE e.`manager_id`=m.`employee_id`
AND e.`last_name`='kochhar';

sql99语法的连接查询相关笔记：

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

sql99语法
/*
语法：
	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语法中的等值连接效果是一样的，都是查询多表的交集
*/

#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 e.`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.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT `last_name`,`job_title`,`department_name`
FROM `employees` e
INNER JOIN `jobs` j ON e.`job_id`=j.`job_id`
INNER JOIN `departments` d ON d.`department_id`=e.`department_id`
ORDER BY `department_name` DESC;

#2、非等值连接
#查询员工的工资级别
SELECT salary,`grade_level`
FROM `employees` e
JOIN `job_grades` j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),`grade_level`
FROM `employees` e
JOIN `job_grades` j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY `grade_level`
HAVING COUNT(*)>20
ORDER BY `grade_level` DESC;

#3、自连接
#查询员工的名字、上级的名字
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%'
AND m.`last_name` LIKE '%k%';

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

#引入：查询男朋友 不在男神表的的女神名
# 左外
SELECT b.name,bo.*
FROM `beauty` b
LEFT OUTER JOIN  `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NOT NULL;
# 右外
SELECT b.name,bo.*
FROM `boys` bo
LEFT OUTER JOIN  `beauty` b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NOT NULL;

 
#案例1：查询哪个部门没有员工
SELECT `department_name`,`employee_id`
FROM `departments` d
LEFT OUTER JOIN `employees` e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

#全外连接语法如下  （MySQL不支持）
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;

课后习题：

#课后习题
#一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
SELECT b.`name`,bo.*
FROM `beauty` b
LEFT OUTER JOIN `boys` bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id`>3;
#二、查询哪个城市没有部门
SELECT `city`
FROM `locations` l
LEFT OUTER JOIN `departments` d
ON l.`location_id` = d.`location_id`
WHERE d.`department_id` IS NULL;
#三、查询部门名为SAL或IT的员工信息
# 这题我用的内连接，尚硅谷老师用的外连接。
# 外连接会比内连接多两条数据，但这两条数据全是null值。
# 原因是名为‘IT’的部门对应的id有两个没有员工存在。但个人认为内连接更符合题目要求。

#内连接做法
SELECT e.*
FROM `employees` e
JOIN `departments` d
ON e.`department_id` = d.`department_id`
WHERE d.`department_name`='SAL'
OR d.`department_name`='IT';
#外连接做法
SELECT e.*,d.department_name,d.`department_id`
FROM departments  d
LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE d.`department_name` IN('SAL','IT');

用尚硅谷的两张图总结一下本章：图一左上是左外连接，右上是右外连接，中间是内连接；

图二左上是左外连接+条件，右上是右外连接+条件，左下是全外连接，右下是全外连接+条件。