02-DQL语言-06连接查询

Yunwei man

于 2021-03-25 17:40:06 发布

阅读量94

点赞数

分类专栏：数据库文章标签： mysql 数据库服务器运维

原文链接：https://www.bilibili.com/video/BV12b411K7Zu?from=search&seid=12636539289398809087

版权

数据库专栏收录该内容

21 篇文章 0 订阅

订阅专栏

在这里插入图片描述

理论

一、多表连接

当查询中涉及到了多个表的字段，需要使用多表连接

select 字段1,字段2
from 表1,表2,…;

笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
如何解决：添加有效的连接条件

二、分类

按年分类：
Sql92
等值
非等值
自连接
也支持一部分外连接（用于oracle、sqlserver、mysql不支持）

Sql99【推荐使用】
内连接
等值
非等值
自连接
外连接
左外
右外
全外（mysql不支持）
交叉连接

三、sql92语法

1、等值连接
语法：

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

特点：
①一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果时多表的交集部分

2、非等值连接
语法：

select 查询列表
from 表1 别名，表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by排序字段】

3、自连接
语法：

select 查询列表
from 表 别名1, 表 别名2
where 等值的连接条件
【and 筛选条件】
【group by分组字段】
【having 分组后的筛选】
【order by 排序字段】

四、SQL99语法

1、内连接
语法：

select 查询列表
from 表1 别名 
【inner】join 表2 别名 
on 连接条件
where 筛选条件
group by分组列表
having 分组后的筛选
order by排序列表
limit子句;

特点：
①表的顺序可以调换
②内连接的结果 = 多表的交集
③n表连接至少需要n-1个连接条件
分类：
等值连接
非等值连接
自连接

2、外连接
语法：

select 查询列表
from 表1 别名
left | right |full【outer】join 表2 别名 on 连接条件
where 筛选条件
group by分组列表
having 分组后的筛选
order by排序列表
limit子句;

3、交叉连接
语法：

select 查询列表
from 表1 别名
cross 表2 别名;

特点：相当于笛卡尔乘积

案例

含义：又称为多表查询，当查询的字段来自多个表时，就会用到连接查询笛卡尔乘积相像：表1 有m行，表2 有n行，结果有 m*n行
发生原因：没有有效的连接条件如何避免：添加有效的连接条件分类：
按年代分类：
sq192标准：仅仅支持内连接
sq199标准【推荐】：支持内连接+外连接（左外+右外）、交叉连接
按功能分类：
    内连接：    
        等职连接
        非等值连接
        自连接
    外连接：
        左外连接
        右外连接
        全外连接
    
    交叉连接

SELECT * FROM beauty;
SELECT * FROM boys;
SELECT NAME,`boyName` 
FROM `beauty`,`boys`
WHERE beauty.boyfriend_id = boys.id;

一、sql92标准

1、等值连接

/*
① 多表等值连接的结果为多表的交集部分
② n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，如排序，分组，筛选
*/
#案例1：查询女神名和对应的男神名

SELECT NAME,`boyName` 
FROM `beauty`,`boys`
WHERE beauty.boyfriend_id = boys.id;

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

SELECT last_name,`department_name` 
FROM employees,departments
WHERE `employees`.`department_id`=`departments`.`department_id`;

2、为表起别名
/*
①提高语句的简洁度
②区分多个崇明的字段
注意：如果为表起别名，则查询字段不能使用原来的表名去限定
*/
#查询员工名、工种号、工种名

SELECT `last_name`,e.`job_id`,`job_title`
FROM employees e,`jobs` j
WHERE e.`job_id` = j.`job_id`;

#3、两个表的顺序是否可以调换
#查询员工名、工种号、工种名

SELECT `last_name`,employees.`job_id`,`job_title`
FROM employees,`jobs`
WHERE jobs.`job_id` = employees.`job_id`;

#4、可以加筛选？
#有奖金的员工名、部门名

SELECT `last_name`,`department_name`,`commission_pct`
FROM employees e, departments d
WHERE e.`department_id`= d.`department_id`
AND e.`commission_pct` IS NOT NULL;

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

SELECT department_name,city
FROM departments d, locations l
WHERE d.`location_id`=l.`location_id`
AND l.`city` LIKE '_o%';

#5、可以加分组？
#案例1：查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM `departments` d ,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.`city`

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

SELECT `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 `department_name`,d.`manager_id`;

#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并按员工个数降序

SELECT `job_title`,COUNT(*)个数
FROM employees e,`jobs` j
WHERE e.`job_id`=j.`job_id`
GROUP BY `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 'S%'
ORDER BY `department_name` DESC;

2、非等值连接

/*
create table job_grades
(grade_level varchar(3),
lower_sal int,
highest_sal int);
insert into job_grades
values(‘A’, 100, 2999);
insert into job_grades
values(‘B’, 3000, 5999);
insert into job_grades
values(‘C’, 6000, 9999);
insert into job_grades
values(‘D’, 10000, 14999);
insert into job_grades
values(‘E’, 15000, 24999);
insert into job_grades
values(‘F’, 25000, 40000);
*/
#案例1：查询员工的工资和工资级别

SELECT salary,`grade_level`,last_name
FROM `employees` e,`job_grades` j
WHERE salary BETWEEN j.`lower_sal` AND j.`highest_sal`
AND j.`grade_level`= 'A';

#3、自连接
#案例：查询员工名和上级的名称

SELECT e.`employee_id`, e.`last_name` ,m.`employee_id`, m.`last_name`
FROM employees e, employees m
WHERE e.`employee_id`=m.`manager_id`;

二、sql99语法

语法：
select 查询列表
from 表1 别名【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类内连接（⭐）：inner 外连接
左外（⭐）：left【outer】
右外（⭐）：right【outer】
全外：full【outer】交叉连接：cross

1、内连接

语法： select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件; 分类：等值非等值字连接
特点：
① 添加排序、分组、筛选
② inner可以省略
③ 筛选条件放在where后面，连接条件放在哦那后面，题号分离性，便于阅读 > ④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 job_title , last_name
FROM employees e
INNER JOIN jobs j
ON e.`job_id` = j.`job_id`
WHERE job_title LIKE '%e%' AND 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的部门名和员工个数，并按个数降序（排序）
#查询哪个部门的部门员工个数 >3的部门名和员工个数

SELECT d.department_name ,COUNT(*) 部门个数
FROM departments d
INNER JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY job_id
HAVING COUNT(*) > 3
ORDER BY COUNT(*)DESC
#--------------------------
#①查询每个部门的部门名和员工个数
SELECT COUNT(*) 部门个数,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
#② 在①结果上筛选员工个数 >3的记录，并排序 
SELECT COUNT(*) 部门个数,department_name
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, department_name ,job_title
FROM departments d 
INNER JOIN employees e ON d.department_id = e.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;

#二）非等值连接
#查询员工的工资级别

SELECT `grade_level`, salary ,last_name
FROM job_grades j
INNER JOIN employees e
ON e.`salary` BETWEEN j.`lower_sal` AND j.`highest_sal`;

#查询工资级别的个数 >20，并且按照工资级别降序

SELECT `grade_level` ,COUNT(*) 个数
FROM job_grades j
INNER JOIN employees e
ON e.`salary` BETWEEN j.`lower_sal` AND j.`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%';

2、外连接

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

#引入：查询没有男朋友的女神名

SELECT * FROM beauty;
SELECT * FROM boys;

#左外连接

SELECT b.name ,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id
WHERE bo.id IS NULL;

#右外连接

SELECT b.name ,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id` = bo.id
WHERE bo.id IS NULL;

#案例1：查询哪个部门没有员工
#左外

SELECT  e.`employee_id`, d.*
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL
#右外
SELECT  e.`employee_id`, d.*
FROM employees e
RIGHT OUTER JOIN  departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL

3、全外（mysql不能使用）

USE girls;
SELECT b.*, bo,*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id ;

4、交叉连接

SELECT b.* , bo.*
FROM beauty b
CROSS JOIN boys bo;

Yunwei man

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
02-DQL语言-06连接查询

一、多表连接当查询中涉及到了多个表的字段，需要使用多表连接select 字段1,字段2from 表1,表2,…;笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接如何解决：添加有效的连接条件二、分类按年分类：Sql92等值非等值自连接也支持一部分外连接（用于oracle、sqlserver、mysql不支持）Sql99【推荐使用】内连接等值非等值自连接外连接左外右外全外（mysql不支持）交叉连接三、sql92语法1、等值连接.
复制链接

扫一扫