【MySQL 保姆级讲授】 复合查询--超等详细（10）

1. 复合查询的作用

复合查询的重要作用包罗：

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

2. 创建将进行操纵的表

2.1 员工表 emp

创建表：

1. CREATE TABLE emp (
2.     empno INT PRIMARY KEY,
3.     ename VARCHAR(10),
4.     job VARCHAR(10),
5.     mgr INT,
6.     hiredate DATE,
7.     sal DECIMAL(7,2),
8.     comm DECIMAL(7,2),
9.     deptno INT
10. );

复制代码

插入数据：

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

复制代码

2.2 部门表 dept

创建表：

1. CREATE TABLE dept (
2.     deptno INT PRIMARY KEY,
3.     dname VARCHAR(255),
4.     loc VARCHAR(255)
5. );

复制代码

插入数据：

1. INSERT INTO dept (deptno, dname, loc)
2. VALUES
3. (10, 'ACCOUNTING', 'NEW YORK'),
4. (20, 'RESEARCH', 'DALLAS'),
5. (30, 'SALES', 'CHICAGO'),
6. (40, 'OPERATIONS', 'BOSTON');

复制代码

2.3 薪资等级表

创建表：

1. CREATE TABLE salgrade (
2.     grade TINYINT PRIMARY KEY,
3.     losal SMALLINT,
4.     hisal SMALLINT
5. );

复制代码

插入数据：

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

复制代码

3. 基本查询回顾

• 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J
  说明：（薪资>500 or job =MANAGR）and 姓名首写为J
  下令：
  
  1. select * from emp
  2. where (sal>500 or job='MANAGER') and ename like 'J%';

  复制代码
  
  
  
• 按照部门号升序而雇员的工资降序排序
  说明：asc–升序；desc–降序
  下令：
  
  1. select * from emp
  2. order by deptno asc, sal desc;

  复制代码
  
  
  
• 使用年薪进行降序排序
  说明：年薪=月薪*12+ifnull(comm,0); comm可能为空值NUll，空值NULL和任何值进行运算都为空置，必要使用ifnull()函数
  下令：
  错误示例：
  
  1. select ename, sal*12+ifnull(comm,0) as '年薪'  from emp
  2. order by '年薪' desc;

  复制代码
  
  
  同学们可以发现，年薪并没有进行排序，这是为什么呢？
  答：在这个查询中，'年薪' 被看成字符串常量来处理，而不是列别名(此时的列别名是年薪)。因此，这个查询不会按预期的方式工作。精确的写法应该是去掉引号。
  精确示例：
  
  1. select ename, sal*12+ifnull(comm,0) as '年薪'  from emp
  2. order by 年薪 desc;

  复制代码
  
  
  
• 表现工资最高的员工的名字和工作岗位
  说明：select max(sal) from emp 只能聚合出最高薪，把此最高薪作为条件。
  下令：
  
  1. select ename, job from emp
  2. where sal=(select max(sal) from emp)

  复制代码
  
  
  
• 表现工资高于平均工资的员工信息
  说明：select avg(sal) from emp 把该查询的结果作为条件
  下令：
  
  1. select * from emp
  2. where sal>(select avg(sal) from emp);

  复制代码
  
  
  
• 表现每个部门的平均工资和最高工资
  说明：先把部门聚合，然后使用聚合函数；最高工资max()，平均工资avg()，使用format()函数格式平均数
  下令：
  
  1. select deptno, max(sal) '最高工资', format(avg(sal),2) '最低工资' from emp
  2. group by deptno;

  复制代码
  
  
  
• 表现平均工资低于2000的部门号和它的平均工资
  说明：having 平均工资 <2000
  下令：
  错误示例：
  
  1. select deptno, format(avg(sal),2) '平均工资' from emp
  2. group by deptno
  3. having 平均工资 < 2000;

  复制代码
  
  
  同学们可以发现，这里的 平均工资<2000 并没有起作用，这是为什么呢？
  答：因为 having 子句用于过滤分组后的结果，format(avg(sal),2) 处理后的结果不再属于分组后的结果，重命名的 ‘平均工资’ 的列不再是分组后的列名，分组后的列名是avg(sal)。
  精确示例：
  下令：
  
  1. select deptno, format(avg(sal),2) '平均工资' from emp
  2. group by deptno
  3. having avg(sal) <2000;

  复制代码
  
  
  下令：
  
  1. select deptno, avg(sal) '平均工资' from emp
  2. group by deptno
  3. having 平均工资 <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 有 m 行，表 B 有 n 行，那么它们的笛卡尔积将包罗 m×n行，每行是表 A 中的一行与表 B 中的一行的组合。
界说
在数据库中，笛卡尔积是通过 CROSS JOIN 操纵实现的。假如没有指定任何毗连条件，结果集将包罗所有可能的行组合()。CROSS JOIN 可以显式地使用 CROSS JOIN 关键字，也可以通过逗号, 分隔表名并在 WHERE 子句中不指定任何毗连条件来实现。
示例：
创建表t1:

1. create table  t1(name char(10));
3. insert into t1 values('李明'), ('李华'), ('李刚');

复制代码

创建表t2:

1. create table  t2(name char(10));
3. insert into t2 values('高渐离'), ('王昭君'), ('嫦娥');

复制代码

查询两个表：
下令：select * from t1 cross join t1;

或 下令：select * from t1, t2;

图解：

4.3 内毗连 inner join

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

1. SELECT *
2. FROM table1
3. INNER JOIN table2
4. ON table1.column_name = table2.column_name;

复制代码

ON 子句的重要作用：

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

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

1.         # 使用 inner join 时可以省略inner，单独使用join
2.         select emp.ename, emp.sal, dept.dname
3.         from emp inner join dept
4.         on emp.deptno = dept.deptno;

复制代码

图解：

一种简单的写法：

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

复制代码

4.4 交织毗连 cross join

语法：

1. SELECT t1.id, t1.name, t2.city
2. FROM t1
3. CROSS JOIN t2;

复制代码

CROSS JOIN 天生的是笛卡尔积，但你可以通过 WHERE 子句来过滤结果集，从而实现类似于 INNER JOIN 的效果。
示例：
表现雇员名、雇员工资以及地点部门的名字因为上面的数据来自emp和dept表，因此要联合查询
下令：

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

复制代码

图解：

4.5 左外毗连 left join

界说：返回左表中的所有记载，以及右表中与之匹配的记载；假如右表没有匹配，则结果中右表的字段为 NULL。
语法：

1. SELECT *
2. FROM table1
3. LEFT JOIN table2
4. ON table1.column_name = table2.column_name;

复制代码

示例：
创建两张表：

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

复制代码

查询所有门生的成绩，假如这个门生没有成绩，也要将门生的个人信息表现出来
下令：

1. select * from stu left join exam
2. on stu.id = exam.id;

复制代码

由上图图可以看出，左边表的内容去全部表现出来，右边表内进行对左表进行匹配。当左边表和右边表没有匹配时，也会表现左边表的数据，但是，右边表的数据为空。
4.6 右外毗连 right join

界说：返回右表中的所有记载，以及左表中与之匹配的记载；假如左表没有匹配，则结果中左表的字段为 NULL。
语法：

1. SELECT *
2. FROM table1
3. RIGHT JOIN table2
4. ON table1.column_name = table2.column_name;

复制代码

示例：
创建两张表：

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

复制代码

查询所有门生的成绩，假如这个门生没有成绩，也要将门生的个人信息表现出来
下令：

1. select * from exam left join stu
2. on stu.id = exam.id;

复制代码

由上图可知，右侧表的内容全部表现出来，左侧的表进行匹配，假如匹配不到数据，则表现空。
4.7 自毗连

界说：自毗连是指在同一张表毗连查询。
语法：

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

复制代码

注：自毗连的表必须起别名
示例：
表现员工的姓名及上级领导姓名（mgr是员工领导的编号，empno是员工的编号）
领导也是员工，领导和员工都在emp表。
下令：

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

复制代码

5. 子查询

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

界说：返回一行记载的子查询
示例：

• 表现SMITH同一部门的员工
  下令：
  
  1. select ename from emp
  2. where emp.deptno = (select deptno from emp where ename='SMITH');

  复制代码
  
  
  
• 表现员工FORD的上级领导的编号和姓名（mgr是员工领导的编号；empno是员工编号）
  下令：
  
  1. select empno, ename from emp
  2. where empno = (select mgr from emp where ename='FORD');

  复制代码
  
  
  

5.2 多行子查询

界说：返回多行记载的子查询
5.2.1 in 关键字

界说：用于检查某个值是否在一个列表中。它可以用在子查询中，也可以直接列出具体的值。
也可以使用 not in。
示例：
查询和10号部门的工作岗位相同的雇员，打印出他们的名字，岗位，工资，部门号，但是不包罗10号自
己的
下令：

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

复制代码

5.2.2 all 关键字

界说：用于比较一个值与子查询返回的所有值。通常与比较运算符（如 =, >, <, >=, <=, <>）一起使用。
有时可以使用 max() 或 min() 聚合函数取代。
示例：
表现工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

• 使用 all 关键字
  下令：
  
  1. select ename, sal, deptno from emp
  2. where sal > all(select sal from emp where deptno =30);

  复制代码
  
  
  
• 使用 max() 聚合函数
  下令：
  
  1. select ename, sal, deptno from emp
  2. where sal > (select max(sal) from emp group by deptno haivng deptno =30);

  复制代码
  
  
  

5.2.3 any 关键字

界说：用于比较一个值与子查询返回的任意一个值。通常与比较运算符（如 =, >, <, >=, <=, <>）一起使用。
有时可以使用 max() 或 min() 聚合函数取代。
示例：
表现工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包罗本身部门的员工）

• 使用 any 关键字
  下令：
  
  1. select ename, sal, deptno from emp
  2. where sal > any(select sal from emp where deptno = 30);

  复制代码
  
  
  
• 使用 min() 聚合函数
  下令：
  
  1. select ename,sal, ddptno from emp
  2. where sal > (select min(sal) from emp group by deptno having deptno=30);

  复制代码
  
  
  

5.3 多列子查询

5.3.1 多列单行子查询

单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言
的，而多列子查询则是指查询返回多个列数据的子查询语句。
示例：
查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
下令：

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

复制代码

5.3.2 多列多行子查询

多列多行子查询和多行子查询类似，也是用 in 关键字进行查询。
示例：
查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
下令：

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

复制代码

5.4 from子句中使用子查询

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

• 表现每个高于本身部门平均工资的员工的姓名、部门、工资、平均工资
  下令：
  
  1. select t1.ename, t1.deptno, t1.sal, t2.avgsal
  2. from emp t1, (select deptno, avg(sal) avgsal from emp group by deptno) t2
  3. where t1.age > t2.avgsal and t1.deptno=t2.deptno;

  复制代码
  
  
  
• 查找每个部门工资最高的人的姓名、工资、部门、最高工资
  下令：
  
  1. select t1.ename, t1.sal, t1.deptno, t2.maxsal
  2. from emp t1, (select deptno, max(sal) maxsal from emp group by deptno) t2
  3. where t1.sal = t2.maxsal and t1.deptno= t2.deptno;

  复制代码
  
  
  
• 表现每个部门的信息（部门名，编号，地址）和人员数目
  （1）使用多表查询：
  
  1. select t2.dname, t2.deptno,t2.loc, count(*)
  2. from emp t1, dept t2
  3. where t1.deptno = t2.deptno
  4. group by t2.dname,t2.deptno,t2.loc;

  复制代码
  
  
  （2）使用子查询
  
  1. select t1.dname,t1.deptno,t1.loc, t2.count
  2. from dept t1, (select deptno, count(*)  count from emp group by deptno) t2
  3. where 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 存储引擎。
    
  • 磁盘临时表的优点是可以处理更大的数据集，但速率相对较慢。
    
  
  

示例：
表现每个高于本身部门平均工资的员工的姓名、部门、工资、平均工资
下令：

1. select t1.ename, t1.deptno, t1.sal, t2.avgsal
2. from emp t1, (select deptno, avg(sal) avgsal from emp group by deptno) t2
3. where t1.age > t2.avgsal and t1.deptno=t2.deptno;

复制代码

7. 归并查询

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

该操纵符用于取得两个结果集的交集。当使用该操纵符时，会自动去掉结果集中的重复行。
示例：
将工资大于2500或职位是MANAGER的人找出来
下令：

1. select * from emp where sal > 2500 union
2. select * from emp where job = 'MANAGER';

复制代码

7.2 union all 并集

该操纵符用于取得两个结果集的并集。当使用该操纵符时，不会去掉结果集中的重复行。
示例：
工资大于25000或职位是MANAGER的人找出来
下令：

1. select * from emp where sal >2500 union all
2. select * from emp where job = 'MANAGER';

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。