【MySQL 保姆级教学】复合查询--超级详细（10）

复合查询

1. 复合查询的作用

复合查询的主要作用包括：

数据整合：通过连接多个表，你可以将分散在不同表中的相关信息整合到一起，形成一个完整的数据集。这对于需要从多个来源获取信息的报告和分析非常有用。
提高效率：相比于多次查询不同的表并将结果手动合并，复合查询可以一次性完成所有操作，减少了与数据库的交互次数，提高了性能。
数据关联：在数据库设计中，通常会使用规范化来减少数据冗余，这意味着相关数据会被存储在不同的表中。复合查询可以帮助你根据表之间的外键关系重新关联这些数据。
条件筛选：复合查询可以让你基于多个表的数据进行复杂的条件筛选。例如，你可以选择满足特定条件的所有记录，即使这些条件涉及多个表中的字段。
简化应用逻辑：在应用程序中，直接使用复合查询可以从数据库层面上处理复杂的逻辑，这样可以简化应用程序代码，并且将业务逻辑更多地放在数据库层面，这有助于维护和优化。

2. 创建将进行操作的表

2.1 员工表 emp

创建表：

CREATETABLE emp (
    empno INTPRIMARYKEY,
    ename VARCHAR(10),
    job VARCHAR(10),
    mgr INT,
    hiredate DATE,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2),
    deptno INT);

插入数据：

INSERTINTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000.00,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',1100.00,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);

2.2 部门表 dept

创建表：

CREATETABLE dept (
    deptno INTPRIMARYKEY,
    dname VARCHAR(255),
    loc VARCHAR(255));

插入数据：

INSERTINTO dept (deptno, dname, loc)VALUES(10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');

2.3 薪资等级表

创建表：

CREATETABLE salgrade (
    grade TINYINTPRIMARYKEY,
    losal SMALLINT,
    hisal SMALLINT);

插入数据：

INSERTINTO salgrade (grade, losal, hisal)VALUES(1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

3. 基本查询回顾

查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J 说明：（薪资>500 or job =MANAGR）and 姓名首写为J 命令：select*from empwhere(sal>500or job='MANAGER')and ename like'J%';
按照部门号升序而雇员的工资降序排序 说明：asc–升序；desc–降序命令：select*from emp orderby deptno asc, sal desc;
使用年薪进行降序排序 说明：年薪=月薪*12+ifnull(comm,0); comm可能为空值NUll，空值NULL和任何值进行运算都为空置，需要使用ifnull()函数命令：错误示例：select ename, sal*12+ifnull(comm,0)as'年薪'from emporderby'年薪'desc; 同学们可以发现，年薪并没有进行排序，这是为什么呢？答：在这个查询中，'年薪' 被当作字符串常量来处理，而不是列别名(此时的列别名是年薪)。因此，这个查询不会按预期的方式工作。正确的写法应该是去掉引号。正确示例：select ename, sal*12+ifnull(comm,0)as'年薪'from emporderby 年薪 desc;
显示工资最高的员工的名字和工作岗位 说明：select max(sal) from emp 只能聚合出最高薪，把此最高薪作为条件。命令：select ename, job from empwhere sal=(selectmax(sal)from emp)
显示工资高于平均工资的员工信息 说明：select avg(sal) from emp 把该查询的结果作为条件命令：select*from empwhere sal>(selectavg(sal)from emp);
显示每个部门的平均工资和最高工资 说明：先把部门聚合，然后使用聚合函数；最高工资max()，平均工资avg()，使用format()函数格式平均数命令：select deptno,max(sal)'最高工资',format(avg(sal),2)'最低工资'from empgroupby deptno;
显示平均工资低于2000的部门号和它的平均工资 说明：having 平均工资 <2000 命令：错误示例：select deptno,format(avg(sal),2)'平均工资'from empgroupby deptnohaving 平均工资 <2000; 同学们可以发现，这里的 平均工资<2000 并没有起作用，这是为什么呢？答：因为 having 子句用于过滤分组后的结果，format(avg(sal),2) 处理后的结果不再属于分组后的结果，重命名的 ‘平均工资’ 的列不再是分组后的列名，分组后的列名是avg(sal)。正确示例：命令：select deptno,format(avg(sal),2)'平均工资'from empgroupby deptnohavingavg(sal)<2000;命令：select deptno,avg(sal)'平均工资'from empgroupby deptnohaving 平均工资 <2000;
显示每种岗位的雇员总数，平均工资 说明：以 job 聚合命令：select job，count(*), format(avg(sal),2) from emp group by job;

4. 多表查询

4.1 多表查询的定义

多表查询是指在数据库中从两个或多个表中检索数据的SQL查询。这种查询允许用户基于某些条件将不同表中的数据连接起来，从而获得更复杂和全面的信息。多表查询是关系型数据库管理系统的强大功能之一，它能够帮助用户分析和处理分布在多个表中的数据。

在进行多表查询时，通常会使用到JOIN操作，JOIN可以分为几种类型，包括：

INNER JOIN（内连接）：返回两个表中匹配的所有行。只有当两个表中存在匹配的数据时，才会产生结果记录。
LEFT JOIN（左连接）：返回左表中的所有记录，以及右表中与之匹配的记录；如果右表没有匹配，则结果中右表的字段为NULL。
RIGHT JOIN（右连接）：返回右表中的所有记录，以及左表中与之匹配的记录；如果左表没有匹配，则结果中左表的字段为NULL。
FULL OUTER JOIN（全外连接）：返回左表和右表中的所有记录，当某一边没有匹配时，另一边的字段将填充为NULL。
CROSS JOIN（交叉连接）：返回左表和右表的笛卡尔积，即左表的每一行与右表的每一行组合。

4.2 笛卡尔积

笛卡尔积（Cartesian Product）是指两个表中所有行的组合。具体来说，如果表 A 有

行，表 B 有

行，那么它们的笛卡尔积将包含

m×n

行，每行是表 A 中的一行与表 B 中的一行的组合。

定义
在数据库中，笛卡尔积是通过 CROSS JOIN 操作实现的。如果没有指定任何连接条件，结果集将包含所有可能的行组合()。CROSS JOIN 可以显式地使用 CROSS JOIN 关键字，也可以通过

逗号, 分隔表名

并在 WHERE 子句中不指定任何连接条件来实现。

示例：
创建表t1:

createtable  t1(name char(10));insertinto t1 values('李明'),('李华'),('李刚');

创建表t2:

createtable  t2(name char(10));insertinto t2 values('高渐离'),('王昭君'),('嫦娥');

查询两个表：

命令：

select * from t1 cross join t1;

在这里插入图片描述
或命令：

select * from t1, t2;

在这里插入图片描述

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

4.3 内连接 inner join

定义：返回两个表中匹配的所有行。只有当两个表中存在匹配的数据时，才会产生结果记录。
语法：

SELECT*FROM table1 
INNERJOIN table2 
ON table1.column_name = table2.column_name;

ON 子句的主要作用：

指定连接条件： ON 子句用于指定两个表之间的连接条件，这些条件决定了哪些行应该被组合在一起。
过滤结果集：通过 ON 子句，可以有效地过滤掉不符合条件的行，从而减少结果集的大小，提高查询性能。
提高查询的可读性和维护性：使用 ON 子句可以使查询更加清晰和易于理解，特别是对于复杂的多表查询。将连接条件放在 ON 子句中，而不是放在 WHERE 子句中，可以使查询结构更加明确，便于维护和调试。

示例：

显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自emp和dept表，因此要联合查询
命令：

# 使用 inner join 时可以省略inner，单独使用joinselect emp.ename, emp.sal, dept.dname
    from emp innerjoin dept
    on emp.deptno = dept.deptno;

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

一种简单的写法：

select ename, sal, dname
from emp join dept
on emp.deptno = emp.deptno

4.4 交叉连接 cross join

语法：

SELECT t1.id, t1.name, t2.city 
FROM t1 
CROSSJOIN t2;

CROSS JOIN 生成的是笛卡尔积，但你可以通过 WHERE 子句来过滤结果集，从而实现类似于 INNER JOIN 的效果。

示例：
显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自emp和dept表，因此要联合查询

命令：

select emp.ename, emp.sal, dept.dname
from emp , dept
where emp.deptno = dept.deptno;

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

4.5 左外连接 left join

定义：返回左表中的所有记录，以及右表中与之匹配的记录；如果右表没有匹配，则结果中右表的字段为 NULL。

语法：

SELECT*FROM table1 
LEFTJOIN table2 
ON table1.column_name = table2.column_name;

示例：
创建两张表：

-- 建两张表createtable stu (id int, name varchar(30));-- 学生表insertinto stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');createtable exam (id int, grade int);-- 成绩表insertinto exam values(1,56),(2,76),(11,8);

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来
命令：

select*from stu leftjoin exam
on stu.id = exam.id;

在这里插入图片描述
由上图图可以看出，左边表的内容去全部显示出来，右边表内进行对左表进行匹配。当左边表和右边表没有匹配时，也会显示左边表的数据，但是，右边表的数据为空。

4.6 右外连接 right join

定义：返回右表中的所有记录，以及左表中与之匹配的记录；如果左表没有匹配，则结果中左表的字段为 NULL。

语法：

SELECT*FROM table1 
RIGHTJOIN table2 
ON table1.column_name = table2.column_name;

示例：
创建两张表：

-- 建两张表createtable stu (id int, name varchar(30));-- 学生表insertinto stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');createtable exam (id int, grade int);-- 成绩表insertinto exam values(1,56),(2,76),(11,8);

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来
命令：

select*from exam leftjoin stu
on stu.id = exam.id;

在这里插入图片描述
由上图可知，右侧表的内容全部显示出来，左侧的表进行匹配，如果匹配不到数据，则显示空。

4.7 自连接

定义：自连接是指在同一张表连接查询。

语法：

SELECT*FROM table1 t1 , table1 t2 
WHERE t1.column_name = t2.id;

注：自连接的表必须起别名

示例：

显示员工的姓名及上级领导姓名（mgr是员工领导的编号，empno是员工的编号）
领导也是员工，领导和员工都在emp表。
命令：

select t1.ename, t2.ename '领导'from emp t1, emp t2
where t1.mgr  = t2.empno;

在这里插入图片描述

5. 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

5.1 单行子查询

定义：返回一行记录的子查询

示例：

显示SMITH同一部门的员工 命令：select ename from emp where emp.deptno =(select deptno from emp where ename='SMITH');
显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号；empno是员工编号） 命令：select empno, ename from empwhere empno =(select mgr from emp where ename='FORD');

5.2 多行子查询

定义：返回多行记录的子查询

5.2.1 in 关键字

定义：用于检查某个值是否在一个列表中。它可以用在子查询中，也可以直接列出具体的值。
也可以使用 not in。

示例：

查询和10号部门的工作岗位相同的雇员，打印出他们的名字，岗位，工资，部门号，但是不包含10号自
己的

命令：

select ename, job, sal, deptno
from emp
where job in(select job from emp where deptno =10)and deptno <>10;

在这里插入图片描述

5.2.2 all 关键字

定义：用于比较一个值与子查询返回的所有值。通常与比较运算符（如 =, >, <, >=, <=, <>）一起使用。
有时可以使用 max() 或 min() 聚合函数代替。

示例：

显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

使用 all 关键字命令：select ename, sal, deptno from empwhere sal >all(select sal from emp where deptno =30);
使用 max() 聚合函数命令：select ename, sal, deptno from empwhere sal >(selectmax(sal)from emp groupby deptno haivng deptno =30);

5.2.3 any 关键字

定义：用于比较一个值与子查询返回的任意一个值。通常与比较运算符（如 =, >, <, >=, <=, <>）一起使用。
有时可以使用 max() 或 min() 聚合函数代替。

示例：
显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

使用 any 关键字命令：select ename, sal, deptno from empwhere sal >any(select sal from emp where deptno =30);
使用 min() 聚合函数命令：select ename,sal, ddptno from empwhere sal >(selectmin(sal)from emp groupby deptno having deptno=30);

5.3 多列子查询

5.3.1 多列单行子查询

单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言
的，而多列子查询则是指查询返回多个列数据的子查询语句。

示例：
查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

命令：

select*from emp
where(deptno,job)=(select deptno, job from emp where ename='SMITH')and ename <>'SMITH';

在这里插入图片描述

5.3.2 多列多行子查询

多列多行子查询和多行子查询类似，也是用 in 关键字进行查询。

示例：
查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

命令：

select*from emp
where(deptno,job)=(select deptno, job from emp where ename='SMITH')and ename <>'SMITH';

在这里插入图片描述

5.4 from子句中使用子查询

子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用（章节6讲解临时表）。

示例：

显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资 命令：select t1.ename, t1.deptno, t1.sal, t2.avgsal from emp t1,(select deptno,avg(sal) avgsal from emp groupby deptno) t2where t1.age > t2.avgsal and t1.deptno=t2.deptno;
查找每个部门工资最高的人的姓名、工资、部门、最高工资 命令：select t1.ename, t1.sal, t1.deptno, t2.maxsalfrom emp t1,(select deptno,max(sal) maxsal from emp groupby deptno) t2where t1.sal = t2.maxsal and t1.deptno= t2.deptno;
显示每个部门的信息（部门名，编号，地址）和人员数量 （1）使用多表查询：select t2.dname, t2.deptno,t2.loc,count(*)from emp t1, dept t2where t1.deptno = t2.deptnogroupby t2.dname,t2.deptno,t2.loc;（2）使用子查询select t1.dname,t1.deptno,t1.loc, t2.countfrom dept t1,(select deptno,count(*) count from emp groupby deptno) t2where t1.deptno=t2.deptno;

6. 临时表

MySQL一切为表，查询结束、分组结束······后就会生一个临时表。

在某些情况下，MySQL 会使用临时表来存储中间结果。这些情况包括但不限于以下几种：

复杂的查询：当查询涉及多个JOIN、UNION、GROUP BY、ORDER BY 或子查询时，MySQL 可能会创建临时表来存储中间结果。
分组和排序：如果查询包含 GROUP BY 或 ORDER BY 子句，并且结果集较大，MySQL 可能会使用临时表来存储中间结果，以便进行分组或排序操作。
子查询：在某些情况下，特别是当子查询的结果集较大时，MySQL 可能会将子查询的结果存储在临时表中。
临时表显式创建：用户可以显式地创建临时表来存储中间结果，以提高查询性能或简化复杂查询。

MySQL 使用两种类型的临时表：

内存临时表（Memory Temporary Table）： - 这种临时表存储在内存中，使用 MEMORY 存储引擎。- 内存临时表的优点是速度快，但受限于可用内存大小。如果数据量超过内存限制，MySQL 会自动将其转换为磁盘临时表。
磁盘临时表（Disk Temporary Table）： - 这种临时表存储在磁盘上，使用 MyISAM 或 InnoDB 存储引擎。- 磁盘临时表的优点是可以处理更大的数据集，但速度相对较慢。

示例：
显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
命令：

select t1.ename, t1.deptno, t1.sal, t2.avgsal 
from emp t1,(select deptno,avg(sal) avgsal from emp groupby deptno) t2
where t1.age > t2.avgsal and t1.deptno=t2.deptno;

在这里插入图片描述

7. 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all

7.1 union 交集

该操作符用于取得两个结果集的交集。当使用该操作符时，会自动去掉结果集中的重复行。

示例：
将工资大于2500或职位是MANAGER的人找出来
命令：

select*from emp where sal >2500unionselect*from emp where job ='MANAGER';

在这里插入图片描述

7.2 union all 并集

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

示例：
工资大于25000或职位是MANAGER的人找出来
命令：

select*from emp where sal >2500unionallselect*from emp where job ='MANAGER';

在这里插入图片描述

标签： mysql 数据库 sql

本文转载自: https://blog.csdn.net/2302_79527141/article/details/143318370
版权归原作者 Code哈哈笑 所有，如有侵权，请联系我们删除。

【MySQL 保姆级教学】复合查询--超级详细（10）

复合查询

1. 复合查询的作用

2. 创建将进行操作的表

2.1 员工表 emp

2.2 部门表 dept

2.3 薪资等级表

3. 基本查询回顾

4. 多表查询

4.1 多表查询的定义

4.2 笛卡尔积

4.3 内连接 inner join

4.4 交叉连接 cross join

4.5 左外连接 left join

4.6 右外连接 right join

4.7 自连接

5. 子查询

5.1 单行子查询

5.2 多行子查询

5.2.1 in 关键字

5.2.2 all 关键字

5.2.3 any 关键字

5.3 多列子查询

5.3.1 多列单行子查询

5.3.2 多列多行子查询

5.4 from子句中使用子查询

6. 临时表

7. 合并查询

7.1 union 交集

7.2 union all 并集

发表评论