SQL学习笔记五：DQL（Data Query Language）：分组查询、多表连接查询、子查询

最新推荐文章于 2024-07-26 19:45:00 发布
最新推荐文章于 2024-07-26 19:45:00 发布
阅读量232 收藏
点赞数
分类专栏： 数据分析 文章标签： sql 学习 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_51118741/article/details/118094331
版权

文章目录

六、分组查询

语法：

select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

特点：

  • 1、和分组函数一同查询的字段必须是group by后出现的字段
  • 2、筛选分为两类：分组前筛选和分组后筛选
针对的表位置连接的关键字
分组前筛选原始表group by前 where
分组后筛选group by后的结果集group by后 having

  • 问题1：分组函数做筛选能不能放在where后面
    答：不能

  • 问题2：where——group by——having
    一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

  • 3、分组可以按单个字段也可以按多个字段

  • 4、可以搭配着排序使用

#引入：查询每个部门的员工个数

SELECT COUNT(*) FROM employees WHERE department_id=90;

1.简单的分组

#案例1：查询每个工种的员工平均工资

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

#案例2：查询每个位置的部门个数

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_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、分组后筛选

#案例：查询哪个部门的员工个数>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 job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

#案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资

manager_id>102

SELECT manager_id,MIN(salary)
FROM employees
GROUP BY manager_id
HAVING MIN(salary)>5000;

4.添加排序

#案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;

5.按多个字段分组

#案例：查询每个工种每个部门的最低工资,并按最低工资降序

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

练习

#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 有m行，表2有n行，结果=m*n行
    发生原因：没有有效的连接条件
    如何避免：添加有效的连接条件

  • 分类：

    • 按年代分类：
      sql92标准:仅仅支持内连接
      sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

    • 按功能分类：

      • 内连接：
        等值连接
        非等值连接
        自连接
      • 外连接：
        左外连接
        右外连接
        全外连接
        交叉连接
SELECT * FROM beauty;

SELECT * FROM boys;


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

一、sql92标准

1、等值连接

  • ① 多表等值连接的结果为多表的交集部分
  • ②n表连接，至少需要n-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、为表起别名

  • ①提高语句的简洁度

  • ②区分多个重名的字段

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

#查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.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、可以加筛选

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

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 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、可以加排序

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

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

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

/*
select salary,employee_id from employees;
select * from job_grades;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES ('A', 1000, 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);

*/

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`;

一、传统模式下的连接 ：等值连接——非等值连接

1.等值连接的结果 = 多个表的交集
2.n表连接，至少需要n-1个连接条件
3.多个表不分主次，没有顺序要求
4.一般为表起别名，提高阅读性和性能

二、sql99语法：通过join关键字实现连接

含义：1999年推出的sql语法
支持：
等值连接、非等值连接 （内连接）
外连接
交叉连接

语法：

select 字段，…
from 表1
【inner|left outer|right outer|cross】join 表2 on 连接条件
【inner|left outer|right outer|cross】join 表3 on 连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

三、自连接

案例：查询员工名和直接上级的名称

sql99

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.manager_id=m.employee_id;

sql92

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

八、子查询

含义：

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询

特点：

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空

② 多行子查询
结果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替

我要毕业！！
关注
专栏目录
查询的分组与汇总、创建多表连接查询和子查询
w_ll_an的博客
03-30 515
聚合函数 数量 select count(*)from 表名； select count(0)from 表名； select count(字段名)from 表名； 求和 select sum(字段名)from 表名； 平均值 select avg(字段名)from 表名; ...
OCP学习笔记-007 SQL语言之一：DQL
大龄IT民工
06-06 1001
命令行提示符修改一些基本参数的永久修改查看历史数据。
SQL查询进阶
trytrylmt的博客
02-05 561
一、分组查询 把具有相同的数据值的行放在同一组中。 指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。 GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。 SELECT col, COUNT(*) AS num FROM mytable GROUP BY col ORDER BY num; WHERE 过滤行，HAVING 过滤分组...
SpringJPA - QueryDSL类使用介绍
最新发布
a_beiyo的博客
07-26 984
QueryDSL（Query Domain Specific Language）是一个用于在 Java 中构建类型安全的动态查询的框架。它通过一种类似于 SQL 的语法，但是在编译时进行类型检查，从而避免了传统字符串查询带来的潜在错误和安全隐患。QueryDSL 主要用于简化复杂查询的构建，支持多种持久化框架，包括 JPA、Hibernate、JDO 等。QueryDSL 是一个强大的查询框架，通过类型安全的 DSL 提供了在 Java 中构建复杂查询的便捷方式。
高级SQL查询-（聚合查询，分组查询，联合查询）
热门推荐
淡沫初夏zz的博客
03-08 1万+
高级SQL查询-（聚合查询，分组查询，联合查询）
详解QueryDSL使用Group by查询，自定义返回对象
境里婆娑
07-04 958
Querydsl是一个框架，它可以通过它提供的的API帮助我们构建静态类型的SQL-like查询，也就是在上面我们提到的组织查询方式。Querydsl是出于以类型安全的方式维护HQL查询的需要而诞生的。HQL查询的增量构造需要String连接，这导致难以阅读的代码。通过纯字符串对域类型和属性的不安全引用是基于字符串的HQL构造的另一个问题。随着域模型的不断变化，类型安全性在软件开发中带来了巨大的好处。域更改直接反映在查询中，而查询构造中的自动完成功能使查询构造更快，更安全。要统计字段需要new出来。
MySQL学习笔记：DQL查询语句详解
"本篇是关于MySQL学习笔记的第六部分，主要关注DQL（Data Query Language），即查询语句的使用。通过创建一个名为`student02`的表，包含了id、NAME、age、sex、address、math和english等字段，并插入一些学生数据，...
MySQL基础 （SQLyog） 学习笔记整理——DQL基础查询、条件查询、排序查询
ANN1999_的博客
04-24 1035
MySQL基础 （SQLyog） 学习笔记整理——DQL基础查询、条件查询、排序查询
SQL操作全攻略：从数据库到数据查询
SUM, AVG, MAX, MIN）、模糊查询（LIKE）、分组查询（GROUP BY）、分页查询（LIMIT）、内连接（INNER JOIN）、外连接（LEFT JOIN, RIGHT JOIN, FULL JOIN）以及子查询。 这篇笔记提供了学习SQL基础知识的全面指导...
SQL学习笔记(完整)
b517146180的博客
07-04 2068
入门MySql
java and dsl_java – 具有元组条件的QueryDSL和SubQuery
weixin_30030255的博客
02-20 462
我试图在QueryDSL中编写一个查询来获取由其parentId分组的表中最旧的元素.SQL等价物应该是：SELECT a.* FROM child aINNER JOIN(SELECT parentId, MAX(revision) FROM child GROUP BY parentId) bON (a.parentId = b.parentId AND a.revision = b.revi...
QueryDSL JPA 查询示例
来啊 快活啊
07-12 7786
发博词QueryDSL 及QueryDSL JPA重点关注对象，Expression，Expressions,ExpressionUtils,JPAExpressions，QBean,QList,QTuple,JPAQuery,Operation,,Projections,QueryResults等。查询示例1.//queryDSL查询返回一个对象QCustomer customer = QCust
sql分组查询
菜鸡的博客
10-11 5316
sql分组查询
Querydsl使用groupBy的一个bug
weixin_43617546的博客
07-08 2402
querydsl使用groupby的一个不记录 java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found ',' 最近发现在使用Querydsl fetchCount()的时候使用groupBy()会报错，经过多次测试后发现，如果使用fetchCount()之前使用了groupBy()并且有多个字段分组，如groupBy(qUser.name,
Querydsl Reference Guide(中文版):2. 教程
白石的专栏
05-20 1446
Querydsl 定义了一种通用的静态类型语法，用于在持久域模型数据之上进行查询。 JDO 和 JPA 是 Querydsl 的主要集成技术。 本指南介绍了如何将 Querydsl 与 JPA 结合使用。 Querydsl for JPA 是 JPQL 和 Criteria 查询的替代方案。 它以完全类型安全的方式将 Criteria 查询的动态特性与 JPQL 的表达能力以及所有这些结合起来。
五、多表连接查询——数据分组
python伊甸园的博客
12-17 2734
五、多表连接查询——数据分组 1.数据分组背景 当汇总成绩时，需要年级之间的横向对比，比如高中一年级，然后将一年级1班、2班、...，一直到所有班级的成绩进行对比，这个时候就需要分组汇总进行统计聚合。 2.如何使用分组查询 (1)语法如下： select 列名或者聚合函数 from 表名 [where 条件表达式] group by 分组列; (2)示例1： 根据class列...
高级SQL查询-聚合，分组，联合查询
weixin_51143430的博客
07-29 1099
高级SQL查询 聚合查询 COUNT函数 SUM函数 AVG函数 MAX函数 MIN函数 IF NULL函数 分组查询GROUP BY 分组条件查询HAVING 联合查询（多表查询） 笛卡尔积 内连接 实战1： 查询张三的成绩： 先进行内连接查询，得到一个笛卡尔积，再把其中无效的数据删除。 1.进行内连接查询 2.去掉无效数据（on 过滤条件)...
第六章：使用QueryDSL的聚合函数
恒宇少年De成长之路
04-24 1万+
在企业级项目开发过程中，往往会经常用到数据库内的聚合函数，一般ORM框架应对这种逻辑问题时都会采用编写原生的SQL来处理，而QueryDSL完美的解决了这个问题，它内置了SQL所有的聚合函数下面我们简单介绍我们常用的几个聚合函数。 本章目标 基于SpringBoot平台整合QueryDSL完成常用聚合函数使用。 构建项目 我们使用idea来创建一个SpringBoot项目，pom....
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值