您现在的位置是：网站首页> 编程资料编程资料

MySQL复合查询和内外连接的操作代码_Mysql_

2023-05-26 403人已围观

简介 MySQL复合查询和内外连接的操作代码_Mysql_

一、复合查询

1.1 多表查询

实际开发中往往数据来自不同的表，所以需要多表查询，但是可以将多张表做笛卡尔积后的表当做是一张表，也就是单表查询。
我们用一个简单的公司管理系统，有三张表EMP、DEPT、SALGRADE来演示如何进行多表查询。

语法：from 表1, 表2 ...
这样其实就是对多张表进行笛卡尔积，假设是两张表：

第一张表的第一行和第二张表的所有行组合。
第一张表的第二行和第二张表的所有行组合。…

在这里插入图片描述

这样所有的组合，并不都是有意义的，所有一般都是根据条件筛选的。

在多表查询中，有可能多张表有相同的字段，为了便于区分，可以使用表名.字段名表示某一张表的某一个字段。

mysql> select dept.dname, emp.ename, emp.sal from emp,dept where dept.deptno = emp.deptno; +------------+--------+---------+ | dname | ename | sal | +------------+--------+---------+ | RESEARCH | SMITH | 800.00 | | SALES | ALLEN | 1600.00 | | SALES | WARD | 1250.00 | | RESEARCH | JONES | 2975.00 | | SALES | MARTIN | 1250.00 | | SALES | BLAKE | 2850.00 | | ACCOUNTING | CLARK | 2450.00 | | RESEARCH | SCOTT | 3000.00 | | ACCOUNTING | KING | 5000.00 | | SALES | TURNER | 1500.00 | | RESEARCH | ADAMS | 1100.00 | | SALES | JAMES | 950.00 | | RESEARCH | FORD | 3000.00 | | ACCOUNTING | MILLER | 1300.00 | +------------+--------+---------+ 14 rows in set (0.00 sec)

1.2 自连接

自连接是对一张表的查询，但是使用多张表的查询方式，自己做笛卡尔积。
例如：

mysql> show create table emp \G *************************** 1. row *************************** Table: emp Create Table: CREATE TABLE `emp` ( `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号', `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名', `job` varchar(9) DEFAULT NULL COMMENT '雇员职位', `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号', `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间', `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪', `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金', `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号）。
这是员工表，领导也是属于员工，所以无法只用一条简单查找语句对一张表查找两次，这就需要用到复合查询。
子查询 — 即嵌套查询，使用两条查询语句

mysql> select empno, ename from emp where empno = (select mgr from emp where ename = 'FORD'); +--------+-------+ | empno | ename | +--------+-------+ | 007566 | JONES | +--------+-------+ 1 row in set (0.00 sec

使用自连接的两张表查询，这里需要对表使用别名

mysql> select leader.empno, leader.ename from emp as worker, emp leader where worker.mgr = leader.empno and worker.ename='FORD'; +--------+-------+ | empno | ename | +--------+-------+ | 007566 | JONES | +--------+-------+ 1 row in set (0.00 sec)

1.3 子查询

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

1.3.1 单行子查询

返回一行记录的子查询

显示SMITH同一部门的员工

mysql> select deptno, ename from emp where deptno = (select deptno from emp where ename = 'SMITH'); +--------+-------+ | deptno | ename | +--------+-------+ | 20 | SMITH | | 20 | JONES | | 20 | SCOTT | | 20 | ADAMS | | 20 | FORD | +--------+-------+ 5 rows in set (0.00 sec)

1.3.2 多行子查询

返回多行记录的子查询，仍然是一列（一个字段）。

in关键字（属于查询出来的多行中的一行，无法用于比较）：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的
先拆分，找到10号部门的岗位：

mysql> select distinct job from emp where deptno = 10; +-----------+ | job | +-----------+ | MANAGER | | PRESIDENT | | CLERK | +-----------+ 3 rows in set (0.00 sec) -- 然后再复合 mysql> select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno = 10) and deptno <=> 10; +--------+-----------+---------+--------+ | ename | job | sal | deptno | +--------+-----------+---------+--------+ | CLARK | MANAGER | 2450.00 | 10 | | KING | PRESIDENT | 5000.00 | 10 | | MILLER | CLERK | 1300.00 | 10 | +--------+-----------+---------+--------+ 3 rows in set (0.00 sec)

all关键字（与查询结果的所有行比较为真的）：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
同样可以拆分

-- 先找到部门号为30的所有工资 mysql> select sal from emp where deptno = 30; +---------+ | sal | +---------+ | 1600.00 | | 1250.00 | | 1250.00 | | 2850.00 | | 1500.00 | | 950.00 | +---------+ 6 rows in set (0.00 sec) -- 判断 > 所有行 mysql> select ename, sal, deptno from emp where sal > all(select sal from emp where deptno = 30); +-------+---------+--------+ | ename | sal | deptno | +-------+---------+--------+ | JONES | 2975.00 | 20 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | FORD | 3000.00 | 20 | +-------+---------+--------+ 4 rows in set (0.00 sec)

any关键字（与查询结果的任意一行比较为真的）：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工） some是any的别称很少使用。

mysql> select ename, sal, deptno from emp where sal > any (select sal from emp where deptno=30); +--------+---------+--------+ | ename | sal | deptno | +--------+---------+--------+ | ALLEN | 1600.00 | 30 | | WARD | 1250.00 | 30 | | JONES | 2975.00 | 20 | | MARTIN | 1250.00 | 30 | | BLAKE | 2850.00 | 30 | | CLARK | 2450.00 | 10 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | TURNER | 1500.00 | 30 | | ADAMS | 1100.00 | 20 | | FORD | 3000.00 | 20 | | MILLER | 1300.00 | 10 | +--------+---------+--------+ 12 rows in set (0.00 sec)

1.3.3 多列子查询

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

其实很简单，多列也可以比较，和单列一样。

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

mysql> select ename, deptno, job from emp where (job, deptno) = (select job, deptno from emp where ename = 'SMITH') and ename <=> 'SMITH'; +-------+--------+-------+ | ename | deptno | job | +-------+--------+-------+ | SMITH | 20 | CLERK | +-------+--------+-------+ 1 row in set (0.00 sec)

1.3.4 在from子句中使用子查询

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

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

-- 先找到每个部门的平均工资 mysql> select deptno, avg(sal) from emp group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 3 rows in set (0.00 sec) -- 然后再用这张表和原本的表做笛卡尔积，多表查询 mysql> select emp.ename, emp.deptno, emp.sal, format(dept_avg,2) from emp , (select deptno , avg(sal) dept_avg from emp group by deptno) tmp wheree tmp.deptno = emp.deptno and emp.sal > dept_avg; +-------+--------+---------+--------------------+ | ename | deptno | sal | format(dept_avg,2) | +-------+--------+---------+--------------------+ | ALLEN | 30 | 1600.00 | 1,566.67 | | JONES | 20 | 2975.00 | 2,175.00 | | BLAKE | 30 | 2850.00 | 1,566.67 | | SCOTT | 20 | 3000.00 | 2,175.00 | | KING | 10 | 5000.00 | 2,916.67 | | FORD | 20 | 3000.00 | 2,175.00 | +-------+--------+---------+--------------------+ 6 rows in set (0.00 sec)

显示每个部门的信息（部门名，编号，地址）和人员数量
对于有未知的，聚合的信息（人员数量），先将它求出来

-- 首先拿到相关表的信息 mysql> select * from dept limit 3; +--------+------------+----------+ | deptno | dname | loc | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | +--------+------------+----------+ 3 rows in set (0.00 sec) mysql> select * from emp limit 3; +--------+-------+----------+------+---------------------+---------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +--------+-------+----------+------+---------------------+---------+--------+--------+ | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | | 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | +--------+-------+----------+------+---------------------+---------+--------+--------+ 3 rows in set (0.01 sec)

暴力使用多表查询
注意：和聚合函数一起显示的字段，需要被分组，因为聚合函数对于某一个字段是不清楚的，比如count(*)，它是统计总数，并不对应某一个deptno,ename这些字段

mysql> select dept.dname, dept.deptno, dept.loc, count(*) from dept, emp where dept.deptno = emp.deptno group by deptno; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.dept.dname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ---- 注意：和聚合函数一起显示的字段，需要

上一篇：一文搞懂什么是MySQL前缀索引_Mysql_

下一篇：MySQL正则表达式匹配查询(含实例)_Mysql_