【MySQL】MySQL-04-高级查询（1）

最新推荐文章于 2023-11-01 22:08:37 发布
最新推荐文章于 2023-11-01 22:08:37 发布
阅读量232 收藏 1
点赞数
分类专栏： MySQL数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/f_zmmfs/article/details/103647121
版权

1. 分组查询

# 语法
select ...
from ...
[where ...]
[group by ...]
[order by ...]

(0) 注意事项

  • where用于分组前筛选，对象是原始数据表
  • group用于分组后筛选，对象是分组后的结果集
  • 分组函数做条件放到having字句中
  • 优先采用分组前筛选
  • 添加排序放到最后

(1) 简单分组

样例1

查询每个工种的最高工资

select max(salary), job_id
from employees
group by job_id;

样例2

查询每个位置的部门个数

select count(*), location_id
from departments
group by location_id;

添加筛选条件

查询邮箱中包含a字符的，每个部门的平均工资。

select avg(salary), department_id
from employees
where email like "%a%"
group by department_id;

添加复杂筛选条件

查询哪个部门的员工个数>2

  1. 查询每个部门的员工个数
select count(*), department_id
from employees
group by department_id;
  1. 根据1的结果筛选
select count(*), department_id
from employees
group by department_id
having count(*) >2; # 分组后筛选，where属于分组前筛选，针对表不针对组

查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

1.查询每个工种有奖金的员工的最高工资

select max(salary),job_id
from employees
where commission_pct is not null
group by job_id
  1. 删选分组结果中>12000
select max(salary),job_id
from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;

(2) 表达式分组

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

select count(*) as cnt ,length(last_name) as len
from employees 
group by len
having cnt > 5;

(3) 多字段分组

查询每个部门每个工种的员工的平均工资

select avg(salary) ,department_id, job_id
from employees 
group by department_id, job_id;

(4) 带排序分组

查询每个部门每个工种的员工的平均工资，并排序

select avg(salary) ,department_id, job_id
from employees 
group by department_id, job_id;
order by avg(salary) desc;

2.连接查询（多表查询）

(0) 背景

直接多表查询问题

直接进行多表查询会让第一章表的每一行与第二张表的每一行一一匹配一次。例如 5*6=30

原因：没有有效的连接条件

直接多表查询：笛卡尔乘积
select name,boyname 
from boys,beauty;

修复方式：
select name,boyname 
from boys,beauty
where beauty.boyfriend_id = boys.id;

(1) 分类

按版本：

  • SQL92：仅支持内连接
  • SQL99：内连接+外连接（不支持全外）+交叉连接

按功能：

  • 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外链接
    • 右外连接
    • 全外连接
  • 交叉连接

(2) SQL92标准

等值连接

  • 多表等值连接为多表的交集部分
  • n表连接至少需要n-1个连接条件
  • 多表顺序没有要求
  • 一般需要为表AS别名
匹配boyfriend_id和id
select name,boyname 
from boys,beauty
where beauty.boyfriend_id = boys.id;

开发中用别名方式：
select a.name,b.boyname 
from boys a,beauty b
where b.boyfriend_id = a.id;
别名生成之后不能用原名了
select a.name,beauty.boyname # 报错
from boys a,beauty b
where b.boyfriend_id = a.id;

# 查询员工名 工种号 工种名
SELECT 
	e.last_name,e.job_id,j.job_title
FROM # 下面的顺序可以调换
	employees e, jobs j
WHERE
	e.job_id = j.job_id
	
	
# 加入筛选条件
# 查询有将近的员工名和部门名
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;

# 加入分组
# 查询每个城市的部门个数
SELECT COUNT(*) ,city
FROM departments d, locations l
WHERE d.location_id = l.location_id
GROUP BY city;

# 加入排序
# 查询每个工种的工种名称和员工个数，并且按照员工的个数进行排序
SELECT job_title,count(*)
FROM  employees e, jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY count(*) DESC;

# 支持多表连接

非等值连接

上面的e.job_id = j.job_id的等于号换成其他连接符即可

# 查询员工的工资级别
SELECT salary,grade_level
FROM employees e,job_grades j
WHERE	salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接

单张表的连接：一张表作为多张表的功能

# 查询员工名以及领导的名字
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;

(3) SQL99标准

语法

select colm
from table1 [连接类型]
join table2 
on 连接条件
where 筛选条件


连接类型：
	内连接：inner(可以省略)
	外连接：
		左外连接：left outer
		右外连接：right outer
		全外连接： full outer
	交叉连接： cross

内连接

# 等值连接
# 查询员工名和部门名
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
on e.department_id = d.department_id;

# 多表连接就写多个
INNER JOIN ... 
on ...

# 非等值连接
# 查询员工工资级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades j
ON salary BETWEEN j.lowest_sal AND j.highest_sal;

# 自连接
# 同一张表起多个别名即可

外连接

用于查询一个表中有，另一个表没有的。

查询结果分为两部分：

  • 符合内连接不服
  • NULL部分

查询结果：

  • 主表中的所有记录

分类：

  • 左外链接：left join 左边的是主表
  • 右外连接：right join 右边的是主表
# 查询没有男朋友的女的
SELECT b.name,boys.*
FROM beauty b
LEFT JOIN boys 
on b.boyfriend_id = boys.id
# where boys.id is not NULL 

# 查询哪个部门没有员工
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;


# 全外连接
从表中有，主表中没有的行也可以查出来

交叉连接

结果集是笛卡尔积

SELECT beauty.*, boys.*
FROM beauty
CROSS JOIN boys

3.子查询

(0) 简介

定义

出现在其他语句内部的select语句，称为子查询或者内查询。

内部嵌套了其他select语句的查询语句，称为外查询或主查询

分类

出现的按照位置：

  • select
    • 支持标量子查询
  • from
    • 支持表子查询
  • where having
    • 支持标量子查询（单行子查询）
    • 支持列子查询（多行子查询）
    • 支持行子查询
  • exist （相关子查询）
    • 支持表子查询

结果集的行列数不同：

  • 标量子查询（结果集只有一行一列）
  • 列子查询（一列多行）
  • 行子查询（一行多列）
  • 表子查询（多行多列）

(1)where having

特点：

  • 子查询放到小括号内
  • 一般放到条件的右侧
  • 标量子查询 搭配单行操作符（条件运算符 < > =）
  • 列子查询 搭配多行操作符（in any some all）
  • 子查询优先执行

标量子查询-单行子查询

# 谁的工资比Abel高
# 先查询Abel工资
SELECT salary
FROM employees 
WHERE last_name = 'Abel'
# 查询员工的薪资大于value
SELECT * 
FROM employees
WHERE salary > ?
# 整合
SELECT * 
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees 
	WHERE last_name = 'Abel'
);


# 查询员工的姓名，job_id和工资  要求job_id与141号员工相同 salary比143号员工多
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
)
AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

列子查询-多行子查询

IN / NOT IN: 等于列表中的任意一个
ANY / SOME ： 和子查询返回的某一个值比较
ALL ： 和子查询返回的所有值比较


# 返回location_id是1400或1700部门中所有员工的姓名
# 查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400,1700);

# 查询员工姓名，要求其部门号是上一个中的某一个
SELECT last_name
FROM employees 
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN (1400,1700)
);

行子查询

结果集是一行多列或者多行多列

# 查询员工编号最小并且工资最高的员工信息
# 查询最小的员工编号
# 查询最高工资
# 查询员工信息
SELECT MIN(employee_id)
FROM employees;

SELECT MAX(salary)
FROM employees;

SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);

# 行子查询的格式
SELECT *
FROM employees
WHERE (employee_id,salary) =(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

(3) select

# 查询每个部门员工的个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.department_id
)
FROM departments d;

(4)from

子查询结果充当一张表，并且必须起别名，否则无法找到。

# 每个部门平均工资的工资等级
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

SELECT * FROM job_grades;

SELECT ag_dep.*, g.grade_level
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
)ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

(5)exists

exists(查询语句)

结果为 1或者0

# 查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.department_id = e.department_id
);
mmciel
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
如何使用使用 HAVING 与 ORDER BY？
qq_44745905的博客
10-19 8058
@R星校长 使用having子句进行分组筛选 简单来说，having子句用来对分组后的数据进行筛选，即having针对查询结果中的列发挥筛选数据作用。因此having通常与Group by连用。 基本格式： select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名] [having 字段名 筛选条件] 表Info的数据信息仍如下： category count digest a 5 a2002 a 2 a2001 a 11 a200.
Mysql高级-day04.pdf
04-15
mysql相关内容，包括mysql常用工具、mysql日志、mysql主从复制，比如mysqladmin客户端程序，mysqlbinlog日志管理工具、mysqldump客户端工具可以进行数据迁移
MySQL高级知识-查询与索引优化分析
02-20
手写手写SQL顺序机读顺序总结-SQL解析顺序SQL解析SQLJOINs七种JOIN图解实验：练习1、A、B两表共有2、A、B两表共有+A的独有3、A、B两表共有+B的独有4、A的独有5、B的独有6、AB全有MySQLFullJoin的实现因为MySQL不支持FULLJOIN,下面是替代方法leftjoin+union(可去除重复数据)+rightjoin实现如下面代码7、A的独有+B的独有MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。你可以简单理解为“排好序的快速查找结构”一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存
mysql第6天之分组查询
指针先生
11-01 66
案例六、查询领导编号>120的每个领导手下的最低工资大于5000的领导编号是哪个，以及其最低工资。案例九、查询每个部门(部门编号不为零)每个工种的员工的平均工资，再按照平均工资的高低进行排序。案例七、按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些。案例六、查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资。①查询查询领导编号大于120的每个领导领导编号及手下员工的最低工资。①查询每个工种有奖金的员工的最高工资和工种编号。案例八、查询每个部门每个工种的员工的平均工资。
DQL（数据查询语言）语言学习
weixin_43572928的博客
11-26 1082
数据查询，单表查询，多表查询
MySQL基础练习
lkking的博客
04-03 199
#进阶6：分组查询 /* 语法： select 查询列表 from 表名 where 筛选条件 group by 分组列表； 特点： 1.查询列表往往是 分组函数和被分组的字段 2.分组查询中的筛选分为两类 筛选的基表 使用的关键字 位置 分组前筛选 原始表 where group by 之前 分组后筛选 分组后的结果表 having group by 之后 */ #案例1 ： ...
MySQL案例—分组查询
señoritaw的博客
04-25 370
#分组查询 语法： select 分组函数,列(要求出现在group by的后面) from 表名 【where 筛选条件】 group by 分组的列表 【order by 子句】; 特点：1、分组查询中的筛选条件分为两类 位置 关键字 分组前的筛选 group by的前面 where 分组后的筛选 group by的后面 having 注意：分组函数做为筛选条件一定是放在having子句中能用分组前筛选的，
MySQLday02_2
qq_34159161的博客
04-28 593
三、分组查询 一、语法 select 分组函数，分组后的字段 from 表 【where 筛选条件】 group by 分组的字段 【having 分组后的筛选】 【order by 排序列表】 二、特点 使用关键字 筛选的表 位置 分组前筛选 where 原始表 group by的前面 分组后筛选...
MySQL操作汇总1——查询操作
qq_38596491的博客
05-12 2482
基础查询： select 查询列表 from 表名 #1.查询单个字段 SELECT last_name FROM employees; #2.查询多个字段 SELECT last_name,salary,email FROM employees; #3.查询表中的所有字段 如果有输出顺序要求的话： SELECT `employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,
MySQL的DQL连接查询
dancheng1的博客
01-21 271
MySQL列表页 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询 笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行 发生原因：没有有效的连接条件 如何避免：添加有效的连接条件 分类：        按年代分类：        sql92标准:仅仅支持内连接        sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接        ...
MySQL笔记及习题(一)--基本查询
WGS.
04-21 1104
1.基本查询 select VERSION() 版本号; #查询当前版本号+起别名 select DISTINCT stu_num, stu_age from student; #去重 select CONCAT("小","王王"); #字符串拼接 select CONCAT(stu_name,stu_age) from student; #将学生表中的姓名和年龄拼接成一个字段 模糊查...
MySQL 高级 - day-04.rar
08-17
MySQL 高级 - day-04.rarMySQL 高级 - day-04.rarMySQL 高级 - day-04.rar
31-视频32MySQL高级查询
09-13
31--[视频]32MySQL高级查询
99-智慧园区数据平台方案.pptx
04-27
99-智慧园区数据平台方案.pptx
node-v12.11.1-x86.msi
最新发布
04-27
Node.js，简称Node，是一个开源且跨平台的JavaScript运行时环境，它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立，旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎，可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型，这使得它非常适合处理大量并发连接，从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外，Node.js使用了模块化的架构，通过npm（Node package manager，Node包管理器）,社区成员可以共享和复用代码，极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展，它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等，因此，开发者可以用JavaScript编写全栈应用程序，这一点大大提高了开发效率和便捷性。 在实践中，许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台，如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能，简化了开发流程，并且能更快地响应市场需求。
基于Springboot+Vue华强北商城二手手机管理系统-毕业源码案例设计.zip
04-27
网络技术和计算机技术发展至今，已经拥有了深厚的理论基础，并在现实中进行了充分运用，尤其是基于计算机运行的软件更是受到各界的关注。加上现在人们已经步入信息时代，所以对于信息的宣传和管理就很关键。系统化是必要的，设计网上系统不仅会节约人力和管理成本，还会安全保存庞大的数据量，对于信息的维护和检索也不需要花费很多时间，非常的便利。 网上系统是在MySQL中建立数据表保存信息，运用SpringBoot框架和Java语言编写。并按照软件设计开发流程进行设计实现。系统具备友好性且功能完善。 网上系统在让售信息规范化的同时，也能及时通过数据输入的有效性规则检测出错误数据，让数据的录入达到准确性的目的，进而提升数据的可靠性，让系统数据的错误率降至最低。 关键词：vue；MySQL；SpringBoot框架 【引流】 Java、Python、Node.js、Spring Boot、Django、Express、MySQL、PostgreSQL、MongoDB、React、Angular、Vue、Bootstrap、Material-UI、Redis、Docker、Kubernetes
Excel模版：工资条模板
04-27
Excel工资条模板是一种预先设计好的电子表格文件，主要用于生成和打印员工的工资单，让员工清楚了解自己的工资组成和扣款详情。模板通常包含了以下几个关键部分： 1. **员工信息区**： - 姓名 - 员工编号/工号 - 部门 - 职位 2. **工资构成区**： - 基本工资 - 岗位工资 - 绩效奖金 - 加班工资 - 其他补贴（如交通补贴、餐补、全勤奖等） - 各项津贴（如高温补贴、取暖费等） - 其他应发收入（如年终奖、提成、福利等） 3. **扣款项目区**： - 社保扣款（养老保险、医疗保险、失业保险、工伤保险、生育保险） - 住房公积金 - 个人所得税 - 其他扣款（如迟到、旷工、违规罚款等） - 预借还款（如有） 4. **工资结算区**： - 应发工资总额 - 扣款总额 - 实发工资 5. **备注栏**： - 用于标注本月工资的特殊情况说明，如请假、调休、加班等情况。 6. **签名栏**： - 供员工确认工资数额无误后签名，也可以
29-【智慧城市与政府治理分会场】10亿大数据助推都市治理-30页.pdf
04-27
29-【智慧城市与政府治理分会场】10亿大数据助推都市治理-30页.pdf
mysql-connector-odbc-8 rpm
09-22
mysql-connector-odbc-8 rpm是一个用于在Linux系统中连接MySQL数据库的ODBC驱动程序。ODBC是一种开放的数据库连接接口，允许应用程序与不同类型的数据库进行通信，而不需要了解特定数据库的详细信息。 mysql-...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值