MySQL学习总结（三）DQL语言之分组查询/连接查询/笛卡尔乘积现象/sql92标准/内连接/等值连接/非等值连接/自连接/sql99标准/外连接/左外连接/右外连接/全外连接/交叉连接

最新推荐文章于 2021-12-30 17:02:48 发布
最新推荐文章于 2021-12-30 17:02:48 发布
阅读量236 收藏 1
点赞数 3
分类专栏： # MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/allenfoxxxxx/article/details/116007502
版权

一、分组查询

（一）格式

SELECT 分组函数,字段（要求是出现在GROUP BY后面的字段）
FROM 表
【WHERE 筛选条件】
GROUP BY 分组的列表
【ORDER BY 子句】

（二）简单的分组查询

案例1：查询每个工种的最高工资

SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

在这里插入图片描述
案例2：查询每个位置上的部门个数

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

（三）添加分组前筛选条件的分组查询

案例1：查询每个部门邮箱中包含a字符的员工的平均工资

SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

案例2：查询每个领导手下有奖金的员工的最高工资

SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

（四）添加分组后筛选条件的分组查询

案例1：查询哪个部门的员工个数>2

#1.先查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees 
GROUP BY department_id;
#2.根据1的结果进行筛选，查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees 
GROUP BY department_id
HAVING COUNT(*)>2;

在这里插入图片描述

案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和其最高工资

#1.先查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
#2.根据1的结果继续筛选，最高工资>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的领导编号是哪个，以及其最低工资

#1，查询每个领导手下的员工的固定最低工资
SELECT MIN(salary),manager_id
FROM employees
GROUP BY manager_id;
#2.添加分组前筛选条件：编号>102
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id;
#3.添加分组后筛选条件：最低工资>5000
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

小结
分组查询中的筛选条件分为两类：

数据源位置关键字
分组前筛选条件原始表GROUP BY 子句的前面WHERE
分组后筛选条件分组后的结果集GROUP BY子句的后面HAVING

①分组函数做条件肯定是放在HAVING子句中
②能用分组前筛选的，就优先考虑分组前筛选

（五）按表达式或函数分组查询

案例：按员工姓名长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

#1.查询每个姓名长度的员工个数
SELECT COUNT(*) LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name);
#2.添加筛选条件
SELECT COUNT(*) LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
Having COUNT(*)>5;

当然，也可以起别名，并用别名进行分组查询：

SELECT COUNT(*) c,LENGTH(last_name) len_name
FROM employees
GROUP BY len_name
Having c>5;

（六）按多个字段分组查询

GROUP BY可以支持单个字段分组查询，也支持多个字段分组查询（多个字段之间用逗号隔开，没有顺序要求）
案例：查询每个部门每个工种的员工的平均工资

SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

在这里插入图片描述

（七）添加排序的分组查询

案例：查询每个部门每个工种的员工的平均工资，并且按平均工资从高到低显示

SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

SQL语句的执行顺序：

SELECT 分组函数,分组后的字段  #5
FROM 表 #1
【WHERE 筛选条件】#2
GROUP BY 分组的字段 #3
【HAVING 分组后的筛选】 #4
【ORDER BY 排序列表】 #6

练习题1：查询各个领导手下员工的最低工资，其中最低工资不能低于6000，没有领导的员工不计算在内

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

练习题2：查询各个工种的员工个数

SELECT COUNT(*) 个数,job_id
FROM employees
GROUP BY job_id;

二、连接查询

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

（一）笛卡尔乘积现象

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行

案例：查询员工的姓和其对应的部门名
如果这样进行多表查询会出现笛卡尔乘积现象：

SELECT last_name,department_name
FROM employees,departments

员工表共有107行，部门表有27行，而上面这个查询的结果有2889行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;#连接条件

（二）分类

  • 按年代分类：
    • sql92标准：仅仅支持内连接
    • sql99标准（推荐）：支持内连接+外连接（左外和右外）+交叉连接
  • 按功能分类：
    • 内连接：
      • 等值连接
      • 非等值连接
      • 自连接
    • 外连接：
      • 左外连接
      • 右外连接
      • 全外连接（MySQL不支持，其他数据库支持，如Oracle）
    • 交叉连接

（三）sql92标准（仅仅支持内连接）

1. 等值连接

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

（1）起别名的等值连接查询

在多表查询中，多个表中可能有同名的字段，因此我们就需要在SELECT后边的字段标明是查询哪个表的该字段，当然我们也可以给表起别名。

作用：
①提高语句的简洁度
②区分多个重名的字段

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

案例：查询员工名、工种号、工种名

SELECT last_name,e.job_id,job_title #此处起了别名，就不能用employee.job_id
FROM employees e,jobs j #两个表的顺序可以调换
WHERE e.job_id=j.job_id;

在这里插入图片描述

（2）添加筛选条件的等值连接查询

由于连接查询需要有连接条件，而连接条件是写在WHERE关键字后的，而我们知道筛选条件也是写在WHERE后的，如果此时还有筛选条件，那么就要用到AND关键字，连在WHERE后。

案例1：查询有奖金的员工名和所在部门名

SELECT last_name,department_name
FROM employee 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 city LIKE '_o%';
（3）添加分组的等值连接查询

案例：查询每个城市的部门个数，并按城市名排序

SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY city;
（4）添加排序的等值连接查询

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

SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;
（5）三表连接的等值连接查询

案例：查询在第一个字母为s的城市工作的所有员工的姓和所在部门名，并按部门名降序排序

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. 非等值连接

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

已知有一张工资级别的表job_grades：
在这里插入图片描述

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

在这里插入图片描述

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;

练习题：查询每个国家下的部门个数大于2的国家编号

SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY country_id
HAVING 部门个数>2;

（四）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后面，连接条件放在ON后面，提高分离性，便于阅读
④sql99标准中的等值连接和sql92标准中的等值连接效果是一样的，都是查询多表的交集

（1）等值连接
①普通等值连接查询

案例：查询员工名、部门名

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;
②添加筛选条件的等值连接查询

案例：查询名字中包含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的城市名和部门个数

#1.查询每个城市的部门个数
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.locaton_id=l.location_id
GROUP BY city
#2.在1结果上筛选满足条件的
HAVING COUNT(*)>3;
④添加排序的等值连接查询

案例：查询员工个数>3的部门名和员工个数，并按个数排序

#1.查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name
#在1结果上筛选员工个数>3的记录，并排序
HAVING COUNT(*) >3
ORDER BY COUNT(*) DESC;
⑤三表连接的等值连接查询

案例：查询员工名和对应部门名、工种名，并按部门名降序排序

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）非等值连接

案例：查询员工个数>20的工资级别，并且按工资级别降序排序

SELECT COUNT(*) 员工个数,grade_level 
FROM employees e
INNER 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）自连接

案例：查询姓名中包含字符k的员工的名字、上级的名字

SELECT e.last_name,m.last_name
FROM employees e
INNER 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. 左外和右外其实就是交换两个表的顺序，它们可以实现同样的效果
（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;

在这里插入图片描述

（2）右外连接

其实就是左外连接调换一下顺序
案例：查询哪个部门没有员工

SELECT d.*,e.employee_id
FROM employees e
LEFT OUTER JOIN departments d
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;
（3）全外连接

全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的
MySQL不支持全外连接

SELECT d.*,e.employee_id
FROM departments d
FULL OUTER JOIN employees e
ON d.department_id=e.department_id;

3.交叉连接

其实结果就是笛卡尔乘积

SELECT last_name,department_name
FROM employees
CROSS JOIN departments;

练习题1：查询哪个城市没有部门（要求右外连接）

SELECT city
FROM departments d
RIGHT OUTER JOIN locations l
ON d.location_id=l.location_id
WHERE d.department_id IS NULL;

练习题2：查询部门名为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');

练习题3：查询没有员工的部门个数

SELECT COUNT(*)
FROM departments d
LEFT JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;

4.总结

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

ZaynFox
关注
专栏目录
《MySQL：DQL语言学习_连接查询_sql92标准》
yexiangCSDN的专栏
03-11 219
待定
尚硅谷MySQL学习笔记(Day_2)-DQL语言介绍：连接查询（sql92标准）
weixin_47261774的博客
11-28 161
DQL语言介绍：连接查询（sql92标准）
mysql 分组函数、分组查询、连接查询（多表查询 / sql92标准）练习3
Anna_Liqi的博客
11-27 255
导入这三个sql文件，就能跟着操作啦 https://cloud.189.cn/t/NFF7NzyyEZNv (访问码:rtu8) 里面有3张表。分别一次性执行就能创建成功啦。 # 三、分组函数、分组查询、连接查询（多表查询） -- 3.1.1 分组函数 /* 功能：用作统计使用，又称为聚合函数或统计函数或组函数 分类： sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数 特点： 1、sum、avg一般用于处理数值型 max、min、c
Oracle学习：分组数据（group by）与笛卡尔积
weixin_42374938的博客
07-06 600
一、分组数据 基本格式： select …, 分组函数 from emp where … group by … （1） 统计各个部门的平均工资 select deptno, avg(sal) from emp group by deptno; （2）统计各个部门不同工种的平均工资 select deptno, job, avg(sal) from emp group by deptno, job; 注：select 后面没有出现在分组函数中的列名， 一定要出现在 group by 子句中。而在 grou
分组原理(GROUP BY子句)5：分组集的笛卡尔积及其他运算
黄邦勇帅的博客
06-27 1627
分组原理(GROUP BY子句)5：分组集的笛卡尔积及其他运算 若觉得本文写得还可以，请多多关注本人所作书籍《C++语法详解》电子工业出版社出版，作者 黄勇 本文为原创文章，转载请注明出处，或注明转载自“黄邦勇帅(原名：黄勇) 八、分组集的笛卡尔积及其他运算 1、分组集是分组的集合，在SQL Server中，支持对分组集的笛卡尔积运算，比如，集合A的属性集为(a1, a2, a3)，集合B的属性集...
MySQL消除笛卡尔积的方法_SQL查询中的笛卡尔积现象解决方法
热门推荐
weixin_33180243的博客
01-28 1万+
一、 笛卡尔积笛卡尔积是指在数学中，两个集合X和Y的笛卡尓积(Cartesian product)，又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。笛卡尔积又叫笛卡尔乘积，是一个叫笛卡尔的人提出来的。 简单的说就是两个集合相乘的结果。假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, ...
MySQL操作汇总5——视图
qq_38596491的博客
06-02 230
一、概念 MySQL从5.0.1版本出现的新特性。一种虚拟表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了SQL逻辑，不保存查询结果。 本质————封装！ #案例：查询姓张的学生名和专业名 SELECT stuName,majorName FROM stuinfo s INNER JOIN major m ON s.`majorID` = m.`id` WHERE s.`stuName` LIKE '张%'; #视图:封装该 内连接多表查询————只保存了sql逻辑，不
MySQL的DQL语言：6、连接查询
卓汶的博客
12-14 355
连接查询 含义：又称多表查询，当查询的字段来自多个表时，就会用到连接查询 笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行 发生原因：没有有效的连接条件 如何避免：添加有效的连接条件 如下： #未加条件时 select name,boyName from boys,beauty; # 此时出现的表有m*n行，用boys表中的数据去挨个匹配beauty表中的数据 #添加条件后 select name,boyName from boys,beauty where beauty.boyfriend_id
SQL语言之 - - - DQL语言(数据查询语言)
不想掉头发$的博客
11-09 1431
前言 初步学习了数据库与MySQL，接下来就一起了解SQL语言中的DQL语言的相关知识，数据查询语言(DQL)全称是Data Query Language，主要分为八个大类，是用来进行数据库中数据的查询的，即最常用的select语句。 一、基础查询 注意事项！ 1.建议在最开始先启用库 use 库名 2.着重号 (``)：避免字段名与关键字冲突而使用的标识符 3.执行,格式化 - - - 前提选中的命令行时合法的 - - -...
数据库03—DQL数据查询之分组查询、连接查询、子查询
weixin_46704813的博客
09-19 189
文章目录分组查询一、分组函数二、分组查询1.语法2.特点3.简单的分组查询4.添加筛选条件添加筛选前分组添加分组后的筛选连接查询（多表查询）一、基本概念1.含义2.笛卡尔乘积现象3.分类二、sql92标准1.等值连接2、为表起别名3.两个表的顺序可以调换4.可加筛选5.可加分组6.可以加排序7.三表连接8.非等值连接9.自连接 datediff：时间差 分组查询 一、分组函数 1.功能：用作统计使用，又称为聚合函数或统计函数或组函数 2.分类： sum 求和、avg 平均值、max 最大值 、min 最小值
04_DQL 的连接查询 - 内连接、外连接 + 案例
蕾欧娜与26的博客
06-07 196
这篇博客介绍 SQL 中数据查询语言中的连接查询。连接查询又称为多表查询，当查询字段来自多个表示，我们就需要用到连接查询。 我们先来看看连接查询的分类。 连接查询分类 按年代分类，可以分为 sql92 标准（仅支持内连接）和 sql99 标准（支持所有的内连接 + 左外和右外 + 全外连接）。 按功能分类 内连接（等值连接、非等值连接、自连接） 外连接（左外连接、右外连接、全外连接） 交叉连接 SQL99 语法 今天我们介绍 sql99 语法。 基本的语法： SELECT 查询列表 FROM 表1
MySQL视图
weixin_43572928的博客
12-07 335
视图创建，更新，修改，删除，视图和表的区别
二、数据查询语言（二）
CltCj的博客
11-27 2290
数据查询语言（二）
MySQL基础——TCL事务控制语言
weixin_43722898的博客
12-30 462
TCL Transaction Control Language 事务控制语言 事务： 一个或一组SQL语句组成的一个执行单元，这个执行单元要么全部执行，要么全部不执行
mysql李玉婷网课配套笔记（三） 分组查询、连接查询bilibili
qq1003735377的博客
11-26 187
sql92语法： 等值连接，非等值连接，自连接等 基础函数汇总： 一、单行函数 1、字符函数 concat 拼接 substr 截取子串 upper 变大写 lower 变小写 replace 替换 length 获取字节长度 trim 去前后空格 lpad 左填充 rpad 右填充 instr 获取子串第一次出现的索引 2、数学函数 ceil 向上取整 round 四舍五入 floor 向下取整 mod ...
【数据库学习】八 分组查询
愿万事胜意
06-03 1889
学习要点 使用GROUP BY子句可以像切蛋糕那样对表进行分割。通过使用聚合函数和GROUP BY子句，可以根据“商品种类”，“登记日期”等将表分割后再进行汇总。 聚合键中包含NULL时候，在结果中会以“不确定”行(空行)的形式表现出来。 使用聚合函数和GROUP BY子句的时候应该注意以下4点。 只能写在SELECT子句之中。 GROUP BY子句中不能够使用SELECT子句中的别名 ...
Mysql之视图
weixin_56674682的博客
11-17 531
什么是视图？视图就是一张虚拟的表，视图只保存sql的逻辑，并不保存具体数据，数据是动态生成的 为什么要使用视图？1.可以对sql进行一个复用 2.简化了复杂的sql查询 3.提高了数据的安全性 语法： CREATE OR REPLACE VIEW 视图名 AS SELECT语句; 案例：查询姓张的学生名和专业名 CREATE OR REPLACE VIEW v1 AS SELECT studentname,majorname FROM student s JOIN major..
sql关联，笛卡尔乘积问题。
戈多
10-09 1946
mysql语句拼写时存在笛卡尔乘积的问题，如果想要以A表为主，去其关联表的部分字段，即：A中有N条，关联B之后也是N条，则条件应作为B的关联条件，而不是where之后的条件。 例： SELECT * FROM t_ord_item a LEFT JOIN t_register_item b ON b.itemid = a.itemid AND( b.registid = 'demo|0004
MySQL中多表连接查询总结与实践
小小默：进无止境
06-06 8057
连接查询：将多张表（>=2）进行 记录的连接（按照某个指定的条件进行数据拼接）。连接查询的意义：在用户查看数据的时候，数据往往来源于多张表。连接查询的真谛：根据从左表拿出一条记录到右表匹配根据条件进行过滤。多表如是。【1】连接查询的分类SQL 中将连接查询分为四类：内连接，外连接，自然连接和交叉连接。其中外连接又分为左外连接和右外连接。【2】交叉连接交叉连接：cross join。从一张表中循
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值