4.数据查询语言DQL---分组查询、连接查询

最新推荐文章于 2024-07-11 21:41:59 发布

苦瓜汁西瓜汁

最新推荐文章于 2024-07-11 21:41:59 发布

阅读量484

点赞数

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

本文链接：https://blog.csdn.net/qq_45879990/article/details/119673866

版权

MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

一、分组查询

1.语法：

select 查询的字段，分组函数

from 表

【where 筛选条件】

group by 分组的字段

【order by 排序的字段】;

2.特点

（1）可以按单个字段分组

（2）和分组函数一同查询的字段必须是group by后出现的字段

（3）筛选分为两类：分组前筛选和分组后筛选

	针对的表	位置	连接的关键字
分组前筛选	原始表	group by前	where
分组后筛选	group by后的结果集	group by后	having

（4）分组函数做筛选不能放在where后面

（5）一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

（6）可以按多个字段分组，字段之间用逗号隔开

（7）可以搭配着排序使用

（8）having后可以支持别名

3.操作方法

#1.简单的分组查询

-- 案例1：查询每个工种的员工平均工资 
SELECT AVG(salary),job_id FROM employees GROUP BY job_id; 
-- 案例2：查询每个位置的部门个数 
SELECT COUNT(*),location_id FROM departments GROUP BY location_id; 
-- 案例3：查询每个部门的员工个数
SELECT COUNT(*),department_id FROM employees GROUP BY department_id;

#2.实现分组前的筛选

-- 案例1：查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary),department_id FROM employees 
WHERE email LIKE '%a%' GROUP BY department_id; 
-- 案例2：查询有奖金的每个领导手下员工的平均工资 
SELECT AVG(salary),manager_id FROM employees 
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

#3.分组后筛选

-- 案例1：查询哪个部门的员工个数>5
-- 步骤一：查询每个部门的员工个数 
SELECT COUNT(*),department_id FROM employees GROUP BY department_id; 
-- 步骤二：筛选刚才一结果 
SELECT COUNT(*),department_id FROM employees 
GROUP BY department_id HAVING COUNT(*)>5; 

-- 案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资 
SELECT MAX(salary),job_id FROM employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id HAVING MAX(salary)>12000; 

-- 案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资 
SELECT MIN(salary),manager_id FROM employees 
WHERE manager_id>102 
GROUP BY manager_id 
HAVING MIN(salary)>5000;

#4.按表达式或函数分组

-- 案例：按员工姓名的长度分组，查询每一组员工个数，筛选员工个数>5 
SELECT COUNT(*) c,LENGTH(last_name) len_name 
FROM employees GROUP BY len_name HAVING c>5;

#5.按多个字段分组

-- 案例1：查询每个部门每个工种的员工的平均工资 
SELECT AVG(salary),department_id,job_id 
FROM employees GROUP BY department_id,job_id; 

-- 案例2：查询每个工种每个部门的最低工资,并按最低工资降序 
SELECT MIN(salary),department_id,job_id 
FROM employees 
GROUP BY department_id,job_id 
ORDER BY MIN(salary) DESC;

#6.添加排序

-- 案例1：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示 
SELECT AVG(salary),department_id,job_id 
FROM employees 
GROUP BY department_id,job_id 
ORDER BY AVG(salary) DESC; 

-- 案例2：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序 
SELECT MAX(salary) m,job_id 
FROM employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id HAVING m>6000 ORDER BY m ASC;

4.案例

-- 1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序 
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id 
FROM employees GROUP BY job_id ORDER BY job_id; 

-- 2.查询员工最高工资和最低工资的差距（DIFFERENCE） 
SELECT MAX(salary)-MIN(salary) DIFFRENCE FROM employees; 

-- 3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内 
SELECT MIN(salary),manager_id FROM employees 
WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary)>=6000; 

-- 4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) a 
FROM employees GROUP BY department_id ORDER BY a DESC; 

-- 5.选择具有各个job_id的员工人数 
SELECT COUNT(*) 个数,job_id FROM employees GROUP BY job_id;

二、连接查询

1.概念

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

2.笛卡尔乘积现象

现象：表1 有m行，表2有n行，结果=m*n行

发生原因：如果连接条件省略或无效则会出现

如何避免：添加有效的连接条件

3.分类

（1）按年代分类

sql92标准:仅仅支持内连接

sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

（2）按功能分类：

内连接：等值连接

非等值连接

自连接

外连接：左外连接

右外连接

全外连接

交叉连接

4.sql92操作方法

#1.等值连接

多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名
可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

（1）简单查询

-- 案例1：查询女神名和对应的男神名 
SELECT `name`,boyName FROM boys,beauty 
WHERE beauty.`boyfriend_id`=boys.`id`; 

-- 案例2：查询员工名和对应的部门名 
SELECT last_name,department_name FROM employees,departments 
WHERE employees.`department_id`=departments.`department_id`;

（2）为表起别名

提高语句的简洁度
区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

-- 案例1：查询员工名、工种号、工种名
SELECT last_name,j.job_id,job_title 
FROM employees e,jobs j WHERE e.`job_id`=j.`job_id`;

（3）两个表的顺序可以调换

-- 查询员工名、工种号、工种名 
SELECT e.last_name,e.job_id,j.job_title FROM jobs j,employees e 
WHERE e.`job_id`=j.`job_id`;

（4）可以加筛选

-- 案例1：查询有奖金的员工名、部门名 
SELECT last_name,department_name,commission_pct 
FROM employees e,departments d 
WHERE e.`department_id`=d.`department_id` AND commission_pct IS NOT NULL; 

-- 案例2：查询城市名中第二个字符为o的部门名和城市名 
SELECT department_name,city FROM departments d,locations l 
WHERE d.`location_id`=l.`location_id` AND city LIKE '_o%';

（5）可以加分组

-- 案例1：查询每个城市的部门个 
SELECT COUNT(*) 个数,city FROM departments d,locations l 
WHERE d.`location_id`=l.`location_id` GROUP BY 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）可以加排序

-- 案例1：查询每个工种的工种名和员工的个数，并且按员工个数降序 
SELECT job_title,COUNT(*) FROM jobs j,employees e 
WHERE j.`job_id`=e.`job_id` 
GROUP BY job_title ORDER BY COUNT(*) DESC;

（7）可以实现三表连接

-- 案例1：查询员工名、部门名和所在的城市 
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`;

#2.非等值连接

-- 案例1：查询员工的工资和工资级别 
SELECT salary,grade_level FROM employees,job_grades 
WHERE salary BETWEEN lowest_sal AND highest_sal;

#3.自连接

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

#案例

-- 1.显示所有员工的姓名，部门号和部门名称。 
SELECT last_name,d.department_id,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 last_name , department_name , l.location_id , 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 , job_id , d.department_id , department_name 
FROM employees e,departments d,locations l 
WHERE e.`department_id`=d.`department_id` 
AND d.`location_id`=l.`location_id` AND city='Toronto'; 

-- 5.查询每个工种、每个部门的部门名、工种名和最低工资 
SELECT department_name,job_title,MIN(salary) 最低工资 
FROM employees e,departments d,jobs j 
WHERE e.`department_id`=d.`department_id` 
AND e.`job_id`=j.`job_id` GROUP BY department_name,job_title; 

-- 6.查询每个国家下的部门个数大于2的国家编号 
SELECT COUNT(*),country_id FROM departments d,locations l 
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';

5.sql99操作方法

#语法：

select 查询列表

from 表1 别名【连接类型】

join 表2 别名

on 连接条件

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by 排序列表】

#连接类型：

内连接（★）：inner

外连接

左外(★):left 【outer】

右外(★)：right 【outer】

全外：full【outer】

交叉连接：cross

（1）内连接

#特点

添加排序、分组、筛选
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 last_name LIKE '%e%' AND job_title LIKE '%e%'; 

-- 案例3.查询部门个数>3的城市名和部门个数，（添加分组+筛选） 
SELECT COUNT(*) 部门个数,city 
FROM departments d INNER JOIN locations l 
ON d.`location_id`=l.`location_id` 
GROUP BY city HAVING 部门个数>3; 

-- 案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序） 
SELECT COUNT(*),department_name 
FROM departments d INNER JOIN employees e 
ON e.`department_id`=d.`department_id` 
GROUP BY d.department_name HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC; 

-- 案例5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接） 
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; 

-- 2.非等值连接 
-- 案例1：查询员工的工资级别 
SELECT salary,grade_level 
FROM employees e JOIN job_grades g 
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`; 

-- 案例2：查询工资级别的个数>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;


-- 3.自连接 
-- 案例1：查询员工的名字、上级的名字 
SELECT e.last_name,m.last_name 
FROM employees e JOIN employees m ON e.`manager_id`= m.`employee_id`; 

-- 案例2：查询姓名中包含字符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左边的是主表 outer可省略

右外连接，right join右边的是主表

3、左外和右外交换两个表的顺序，可以实现同样的效果

4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

#案例

-- 1.左外连接 
-- 案例1：查询男朋友 不在男神表的的女神名 
SELECT b.name,bo.* 
FROM beauty b LEFT JOIN boys bo 
ON b.`boyfriend_id`=bo.`id` WHERE bo.`id`IS NULL; 

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

-- 2.右外连接 
-- 案例1：查询男朋友 不在男神表的的女神名 
SELECT b.name,bo.* 
FROM boys bo RIGHT JOIN beauty b 
ON b.`boyfriend_id`=bo.`id` WHERE bo.`id`IS NULL; 

-- 案例2：查询哪个部门没有员工 
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;

（3）交叉连接

-- 三、交叉连接 
-- 笛卡尔乘积 
SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo;

（4）案例

-- 案例1：查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充 
SELECT b.id,b.name,bo.* 
FROM beauty b LEFT OUTER JOIN boys bo 
ON b.`boyfriend_id` = bo.`id` WHERE b.`id`>3; 

-- 案例2：查询哪个城市没有部门 
SELECT l.city,d.department_id 
FROM locations l LEFT JOIN departments d 
ON l.`location_id`=d.`location_id` WHERE department_id IS NULL; 

-- 案例3：查询部门名为SAL或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');