每天学一点之关联查询、select七大子句和子查询

最新推荐文章于 2023-07-25 09:15:13 发布
最新推荐文章于 2023-07-25 09:15:13 发布
阅读量1k 收藏 2
点赞数 1
分类专栏： mysql 文章标签： 数据库 mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_52370789/article/details/129559069
版权

关联查询

关联查询的七种结果

（1）内连接

（2）外连接：左外连接、右外连接、全外连接（mysql使用union代替全外连接）
在这里插入图片描述

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

  • 怎么避免笛卡尔积现象？当然是加条件进行过滤。
  • 思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？
    不会，次数还是两张表记录条数的乘积,只不过显示的是有效记录。

关于表的别名：

select e.ename,d.dname from emp e,dept d;

表的别名有什么好处？

  • 第一：执行效率高。
  • 第二：可读性好

内连接：实现A∩B

假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
AB两张表没有主副之分，两张表是平等的。

格式：

select 字段列表
from A表 inner join B表
on 关联条件
where 等其他子句;

或

select 字段列表
from A表 , B表
where 关联条件 and 等其他子句;

案例分析：

#查询有部门的员工和有员工的部门
#不使用内连接的写法
select * from t_employee emp,t_department dept where emp.did=dept.did;

#使用内连接,内连接和表的顺序无关，通过内连接查询的数据必须满足关联条件
select * from t_employee emp inner join t_department dept on emp.did=dept.did;
SELECT * FROM t_department dept INNER JOIN t_employee emp ON emp.did = dept.did;

#查询薪资高于20000的男员工的姓名和他所在的部门的名称
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did
WHERE salary>20000 AND gender = '男'

左外连接:实现A 和 A - A∩B

外连接：
假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接最重要的特点是：主表的数据无条件的全部查询出来。

格式：

select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

案例分析：

#A的情况：
#查询所有员工的姓名和所在的部门   会以左边的表为主表，查询出所有的数据，哪怕不符合关联条件可以有为null的数据. 哪怕该员工没有部门
select ename,dname from t_employee emp left join t_department dept on emp.did=dept.did;

#A-A∩B的情况：
#查询所有没有部门的员工
select ename,dname from t_employee emp left join t_department dept
on emp.did=dept.did where emp.did is null;

右外连接：实现B 和 B-A∩B

格式：

select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

案例分析：

#查询所有部门，以及所有部门下的员工信息   会以右边的表为主表，查询出所有的数据，哪怕不符合关联条件.  哪怕该部门没有员工
select * from t_employee emp right join t_department dept on emp.did=dept.did;

#查询没有员工的部门
select * from t_employee emp right join t_department dept on emp.did=dept.did where emp.did is null;

用union代替全外连接:实现A∪B 或 A∪(B-A∩B) 或B∪(A-A∩B) 和 A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B）

格式：

select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

union

select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

案例分析：

#查询所有的员工和部门
select * from t_employee emp left join t_department dept on emp.did=dept.did
union
select * from t_employee emp right join t_department dept on emp.did=dept.did;

或
select * from t_employee emp left join t_department dept on emp.did=dept.did
union
select * from t_employee emp right join t_department dept on emp.did=dept.did where emp.did is null;

#查询那些没有部门的员工和所有没有员工的部门
#没有部门的员工
SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

UNION 

#所有没有员工的部门
SELECT *
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

特殊的关联查询：自连接

两个关联查询的表是同一张表，通过取别名的方式来虚拟成两张表

格式：

select 字段列表
from 表名 别名1 inner/left/right join 表名 别名2
on 别名1.关联字段 = 别名2.关联字段
where 其他条件

案例分析：

#查询员工的编号，姓名，薪资和他领导的编号，姓名，薪资
#这些数据全部在员工表中
#把t_employee表，即当做员工表，又当做领导表
#领导表是虚拟的概念，我们可以通过取别名的方式虚拟
SELECT emp.eid "员工的编号",emp.ename "员工的姓名" ,emp.salary "员工的薪资",
	mgr.eid "领导的编号" ,mgr.ename "领导的姓名",mgr.salary "领导的薪资"
FROM t_employee emp INNER JOIN t_employee mgr
#t_employee emp：如果用emp.，表示的是员工表的
#t_employee mgr：如果用mgr.，表示的是领导表的
ON emp.mid = mgr.eid

#表的别名不要加""，给列取别名，可以用""，列的别名不使用""也可以，但是要避免包含空格等特殊符号。

select 语句的7大子句

7大子句顺序

（1）from：从哪些表中筛选
（2）on：关联多表查询时，去除笛卡尔积
（3）where：从表中筛选的条件
（4）group by：分组依据
（5）having：在统计结果中再次筛选
（6）order by：排序
（7）limit：分页
必须按照（1）-（7）的顺序编写子句。注意：on后主要跟关联条件，where后跟字段作为条件，having后跟分组函数作为条件

案例分析：

#查询每个部门的男生的人数，并且显示人数超过5人的，按照人数降序排列，
#每页只能显示10条，我要第2页
SELECT did,COUNT(*) "人数"
FROM t_employee
WHERE gender = '男'
GROUP BY did
HAVING COUNT(*)>5
ORDER BY 人数 DESC
LIMIT 10,10

#测试on
SELECT * FROM t_employee ON eid > 10;  -- X
SELECT * FROM t_employee emp LEFT JOIN t_department dept ON emp.eid = 10;  -- √
#测试where
SELECT * FROM t_employee WHERE eid > 10; -- √
SELECT AVG(salary) FROM t_employee WHERE AVG(salary)>15000 GROUP BY did;  -- X

group by与分组函数

(group by单独使用没有任何意义，必须和分组函数结合使用)
在这里插入图片描述

  • 可以使用GROUP BY子句将表中的数据分成若干组
SELECT column, group_function(column)
FROM table
[WHERE	condition]
[GROUP BY	group_by_expression];

明确：WHERE一定放在FROM后面GROUP BY 前面

  • 在SELECT列表中所有未包含在分组函数中的列都应该包含在 GROUP BY子句中
SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id ;
  • 包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;
  • 使用多个列分组
    在这里插入图片描述
SELECT   department_id dept_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id ;

having与分组函数

在这里插入图片描述

SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>10000 ;

having与where的区别？
（1）where是从表中筛选的条件，而having是统计结果中再次筛选因此后面的条件必须是select后面出现的字段或分组函数
（2）where后面一定不能加“分组/聚合函数”，而having后面可以跟分组函数

#测试having
SELECT * FROM t_employee HAVING eid > 10; -- √
SELECT AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did HAVING AVG(salary) > 15000;
SELECT did,AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did HAVING eid > 10; -- X
SELECT did,AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did HAVING did > 1; -- √
SELECT did,AVG(salary) FROM t_employee WHERE did IS NOT NULL AND did > 1 GROUP BY did; -- √


#统计部门平均工资高于8000的部门和平均工资
SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000 #错误
GROUP BY department_id;

在这里插入图片描述

#统计部门平均工资高于8000的部门和平均工资
SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id
HAVING   AVG(salary)>8000 ;

order by

order by：

  • 降序：desc

  • 升序：用么默认，要么加asc

limit

limit index,pageSize
index：当前页的起始索引
pageSize：每页显示的条数
pageNum：当前页的页码
index = (pageNum-1)*pageSize
pageNum=1，pageSize=4，limit 0,4
pageNum=4，pageSize=4，limit 12,4
pageNum=8，pageSize=4，limit 28,4
pageNum=4，pageSize=6，limit 18,6
limit 4-->limit 0,4

子查询

嵌套在另一个查询中的查询，根据位置不同，分为：where型，from型，exists型。注意：不管子查询在哪里，子查询必须使用()括起来。

1、where型

where型子查询：将某个SQL语句查询的数据作为SQL的条件查询

  • 1、where型子查询中，查询的字段只能有一列，否则：Operand should contain 1 column(s)
  • 2、where型子查询中，子查询的SQL若返回了多行数据，则不能使用"="，但是可以使用in
  • 3、where型子查询中，子查询的SQL若返回了多行数据，则不能使用">“、”<“、”>=“、”<="，但是可以使用all或any

    all(子查询结果)：大于最大值
    any(子查询结果)：大于最小值
    < all(子查询结果)：小于最小值
    < any(子查询结果)：小于最大值

#查询薪资最高的员工信息
SELECT eid,ename,MAX(salary) FROM t_employee 
WHERE salary = (SELECT MAX(salary) FROM t_employee);

#查询每个部门中最高薪资的员工信息
select *from t_employee where salary in(select max(salary) from t_employee where did is not null group by did);
#不加where is not null 会把did为空的分为一个新的组，即不需要显示没有部门的员工信息

#查询大于每个部门的平均薪资的员工信息
SELECT * FROM t_employee 
WHERE salary > ALL(SELECT AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did);

#查询至少大于一个部门的平均薪资的员工信息
SELECT * FROM t_employee 
WHERE salary > ANY(SELECT AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did);

2、from型

  • 子查询的结果是多行多列的结果，类似于一张表格。

  • 必须给子查询取别名，即临时表名，表的别名不要加“”和空格。

#查询每个部门的信息以及每个部门的最高薪资，最低薪资，平均薪资
SELECT dept.*,temp.maxSalary,temp.minSalary,temp.avgSalary
FROM t_department dept
LEFT JOIN 
(SELECT did,MAX(salary) maxSalary,MIN(salary) minSalary,AVG(salary) avgSalary FROM t_employee GROUP BY did) temp
ON dept.did = temp.did;

#查询每个部门的编号，名称，平均工资
select 部门编号, 部门名称， 平均工资
from 部门表 inner join (select 部门编号，avg(薪资) from 员工表  group by 部门编号) temp
on 部门表.部门编号 = temp.部门编号

3、exists型

查询那些有员工的部门
select 部门编号, 部门名称 from 部门表
where exists (select * from 员工表  where 部门表.部门编号 = 员工表.部门编号);
努力奋斗的JAVA小余
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
关联查询(多表查询)
qq_44843223的博客
11-21 3055
目录 关联查询（多表查询） 内连接 左[外]连接 右[外]连接
select 连接查询
Lovely_red_scarf的博客
07-19 681
什么时连接查询 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询 1: 连表查询只有当你需要表与表之间的信息 当你想要把不同表的信息显示在一个表中 那么你可以根据相同的信息或者不同的信息来操作 让两个表通过一个表显示 2:连表是让多个表的你需要的信息显示在一个虚拟表中 3: 只是适用于你查询的时候让查询的多个表的信息汇总在一个表中 当你的这个语句结束就没有了效果 笛卡尔积现象 在连表查询的时候经常会出现一种笛卡尔积现象 表1 有m行，表2有n行，结果=m*n行 迪科尔基
SQL子查询、关联查询
yifan_1001的博客
06-09 2277
嵌套在一个SQL语言中的DQL语句，该DQL被称为子查询DQL中使用子查询在SELECT字句中，将当前子查询结果作为一个字段展示WHERE字句中，将当前子查询结果作为过滤条件使用DML中使用：将一个查询结果集用于增删改操作，该子查询的结果集只有一个值，该子查询结果集是多个值多行单列子查询是可以检索出若干个值。因为作为过滤条件使用，一般配合：IN,NOT IN使用。因为等于是不能同时等于好几个值的，只能同于其中之一，将该子查询当做一张表使用可以将一个查询结果集当做一张表创建出来。
关联查询和子查询
m0_58679504的博客
07-02 651
推荐使用子查询，因为子查询查找的范围更小。关联查询是两个表的积中查找，子查询是和中查找。如果是左外连接，就把学生表放在左边；如果是右外连接，就把学生表放在右边。左外连接：grade中12无学生，但是还是会显示出来（子查询先运行，将查询的结果当成另一个查询的条件。2、内连接：找到两个表中的关联数据。将查询的两个结果进行union。1、全连接：两个表作笛卡尔积。union：重复的只出现一次。1、查询全部一年级的学生信息。，找到左边表中所有的数据）找的是学生，就以学生为基准。，找到右边表中所有的数据。
select --- 联合查询
Lovely_red_scarf的博客
07-23 309
联合查询 将多条查询语句的结果合并成一个结果 语法 语法： 查询语句1 union 查询语句2 union … 应用场景 要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时 特点 1、要求多条查询语句的查询列数是一致的！ 2、要求多条查询语句的查询的每一列的类型和顺序最好一致 3、union关键字默认去重，如果使用union all 可以包含重复项 #引入的案例：查询部门编号>90或邮箱包含a的员工信息 SELECT * FROM employees W
【MySQL】关联查询-(01)
SUNxRUN的博客
10-20 2936
MySQL关联查询语句的使用。
MySQL必知必会笔记（四）SELECT语句 联结表 高级联结 组合查询 全文本搜索
wdt3385的专栏
12-10 806
第十五章 联结表     Sql最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用sql的select能执行的最重要的操作，能很好的理解联结及其语法是学习sql的一个极为重要的组成部分。    外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。        好处：供应商信息不重复，不浪费空间和时间，方便日后修改，一个表信息改动不
关联子查询
lazydoggy
05-19 207
    何为关联子查询？这里引用《Oracle Database 10g SQL开发指南》中的解释： 关联子查询会引用外部查询中的一列或多列。这种子查询之所以被称为关联子查询，是因为子查询的确与外部查询有关。当问题的答案需要依赖于外部查询中包含的每一行中的值时，通常就需要使用关联子查询。       products表中有如下数据： SQL&gt; SELECT * FROM prod...
将2条select语句进行关联查询
hlvy
07-20 2229
sql: with tmp_tab_1 as (select * from( select f.id ID , f. fund_group_id FUND_GROVP_ID , f. fund_id FUND_ID , f. fund_percent FUND_PERCENT,u. fund_SHORT_NAME , n. status SALE_STATVS from T_...
一文详解SQL关联子查询
阿里云云栖号
03-30 6992
简介：本文主要介绍什么是关联子查询以及如何将关联子查询改写为普通语义的sql查询。 本文主要介绍什么是关联子查询以及如何将关联子查询改写为普通语义的sql查询。 在背景介绍中我们将讲讲常见的关联子查询的语义，关联子查询语法的好处以及其执行时对数据库系统的挑战。第二章中我们将主要介绍如何将关联子查询改写为普通的查询的形式，也就是解关联。第三章中我们会介绍解关联中的优化方法。 一 背景介绍 关联子查询是指和外部查询有关联的子查询，具体来说就是在这个子查询里使用了外部查询包含的列。 因为这种可以使用.
sql:关联子查询
qq_41805514的博客
08-13 1995
关联子查询会在细分的组内进行比较的时候使用。 假设要按照商品的种类与平均销售单价进行比较，正常的逻辑是：先取出平均值再与销售单价进行对比 select product_id, product_name, sale_price from Product where sale_price &gt; (select avg(sale_price) from ...
关联查询
weixin_43652535的博客
06-06 228
一 、关联查询（ 将查询结果组合为一张新的临时表） select A.num, A.name, B.name from A,B Where A.nid = B.nid 或者： select A.num, A.name, B.name from A inner join B on A.nid = B.nid 二、左关联查询（显示A表中的所有数据，如果B中无对应关系，则值为null） select A...
[SQL挖掘机] - 关联子查询
最新发布
统计小白er & paul_work
07-25 647
关联子查询是指在一个查询中使用另一个查询的结果作为条件或数据源来进行关联的查询。它通过嵌套查询语句来实现，将内部查询的结果作为外部查询的一部分。关联子查询通常用于解决需要在两个或多个数据表之间建立连接和关联的情况。通过使用关联子查询，我们可以根据内部查询的结果来动态地过滤、匹配或检索数据。通过关联子查询，我们可以在数据库查询中实现更加灵活和复杂的数据关联和操作。它提供了一种强大的工具来处理多表连接、条件过滤和数据匹配等需求。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

努力奋斗的JAVA小余

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值