MySQL-复合查询

目录

一、回顾基本查询：

二、多表查询：

三、子查询：

1、基本子查询：

2、关键字：

3、多列子查询：

4、子查询临时表：

5、合并查询：

四、内外连接：

1、内连接;

2、外连接：

         前面讲的对表进行基本的增删查改都是针对一张表进行的查询，而在实际的开发中，很多时候会涉及到多表的查询，例如下：准备了三张表，SQL语句如下：

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);


DROP TABLE IF EXISTS `emp`;
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 '部门编号'
);


DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);


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

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

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

表数据如下：

准备好这些表数据后，就可以开始下一步了：

一、回顾基本查询：

示例一、在emp表查询工资(sal)低于2000的员工信息，并按照工资列降序排序：

补充一点：其实并不是只有磁盘上的表结构导入到mysql，真实存在的表才叫做表，中间筛选出来的，包括最终结果，可以看作全部都是逻辑上的表。只要是mysql上查出来的全部都是表。

示例二、查询emp表中工资最高的员工的信息：

示例三、查询平均工资低于2000的部门号和它的平均工资：

示例四、查询每种岗位的员工总数：

二、多表查询：

实际的开发中往往数据来自不同的表，所以需要用到多表查询，例如我想要查询员工名和员工所在部门的名字，因为员工名在emp表，部门名在dept表，所以要想同时显示的话就要进行联合查询：

但是可以看到，两张表一起查询就明显有问题，就是数据量明显反常，而且出现了两列的deptno列，但是问题是吗，明明员工只属于一个部门，怎么联合查询就突然变成了三个部门呢？这是因为联合查询是要加限制条件的，如果不加限制条件的话就会这样：

因为联合查询就是从第一张表中选出一条记录和第二张表的所有记录进行组合，然后以此类推第二条记录和第二张表的所有记录进行组合、第三条……不加过滤条件得到的结果就是笛卡尔积。

因此对两张表进行笛卡尔积的时候要加正确的过滤条件才能得到我们真正想要的信息，员工属于一个部门，那么emp表中和dept表中都有deptno字段，我们要的也就是这两个字段相等的数据：

举两个例子直观感受一下：

示例一、显示部门号为10的部门名，员工名和工资：

示例二、显示各个员工的姓名，工资，及工资级别：

三、子查询：

1、基本子查询：

子查询在上面查询工资最高的员工这里就用过了，其实就是把多条select语句合并为一条来查询，再举个例子：查询emp表中和JONES同一个部门的员工;

2、关键字：

in关键字：

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

all关键字：

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

any关键字：

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

3、多列子查询：

就是在查询时可以指定多列为查询条件：

示例：查询和MARTIN的部门和岗位完全相同的所有人员，不包含MARTIN自己：

4、子查询临时表：

正常查询出来的数据就是一个临时表，我们也还可以对这个临时表再次进行组合/查询：

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

示例二、查找每个部门工资最高的人的姓名、工资、部门、最高工资：

5、合并查询：

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

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

示例、查询工资大于等于2500或职位是MANAGER的员工：

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

四、内外连接：

1、内连接;

语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

其实上面所说的利用where子句对两种表形成的笛卡儿积进行筛选就是一种内连接，例如查询JONES这名员工的名字和部门名称：

两种写法产生的结果都是一样的，不过更推荐使用标准写法，可读性会好很多。

2、外连接：

外连接又分左外连接和右外连接：

左外连接：（在联合查询中，左侧的表完全显示）

语法：

select 字段名  from 表名1 left join 表名2 on 连接条件;

首先准备两张表：

学生表:
create table stu(id int, name varchar(20)); 
insert into stu values(1,'张三'),(2,'李四'),(3,'王五'),(4,'赵六');
 
成绩表:
create table exam_result(id int, score int);
insert into exam_result values(1, 80),(2,90),(5, 100);

右外连接：（在联合查询中，左侧的表完全显示）

语法：

select 字段 from 表名1 right join 表名2  on  连接条件;