标签：00 rows affected sal -- 08 sec emp MySQL

【MySQL--08】复合查询

在之前我们对表的查询都是对一张表进行查询，再实际开发中这还远远不够。

我们仍然使用雇员信息表scott_data.sql

--使用source 加上sql文件路径就可以讲表加入到数据库内--

mysql> source /home/Lxy/mysql/mysql/lesson8/scott.sql
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Query OK, 0 rows affected (0.18 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.19 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 14 rows affected (0.04 sec)
Records: 14  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.09 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

1.1基本查询回顾

查询工资高于500或者岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写

这条sql其实分为2个部分，第一部分（工资高于500或者岗位为MANAGER的雇员）,第二部分（他们的姓名首字母为大写）因此分析后不是很难写

mysql> select * from emp where (sal>500 or job = 'MANAGER') and ename like 'J%';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007900 | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.02 sec)

按照部门号升序而雇员的工资降序排序

select * from emp order by deptno,sal desc;

mysql> select * from emp order by deptno,sal desc;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.01 sec)

使用年薪进行降序排序

这里需要注意的是，如果comm为空的话就设置为0，否则无法计入统计

mysql> select ename,sal*12+ifnull(comm,0) as '年薪' from emp order by 年薪 desc;
+--------+----------+
| ename  | 年薪     |
+--------+----------+
| KING   | 60000.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| JONES  | 35700.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| ALLEN  | 19500.00 |
| TURNER | 18000.00 |
| MARTIN | 16400.00 |
| MILLER | 15600.00 |
| WARD   | 15500.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| SMITH  |  9600.00 |
+--------+----------+
14 rows in set (0.00 sec)

显示工资最高的员工的名字和工作岗位

mysql> select ename,job from emp where sal=(select max(sal) from emp);
+-------+-----------+
| ename | job       |
+-------+-----------+
| KING  | PRESIDENT |
+-------+-----------+
1 row in set (0.01 sec)

显示工资高于平均工资的员工信息

mysql> select ename,sal from emp where sal>(select avg(sal) from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.01 sec)

显示每个部门的平均工资和最高工资

mysql> select deptno,format(avg(sal),2),max(sal) from emp group by deptno;
+--------+--------------------+----------+
| deptno | format(avg(sal),2) | max(sal) |
+--------+--------------------+----------+
|     10 | 2,916.67           |  5000.00 |
|     20 | 2,175.00           |  3000.00 |
|     30 | 1,566.67           |  2850.00 |
+--------+--------------------+----------+
3 rows in set (0.00 sec)

显示平均工资低于2000的部门号和它的平均工资

mysql> select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal<2000;
+--------+-------------+
| deptno | avg_sal     |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)

显示每种岗位的雇员总数，平均工资

mysql> select job,count(*) ,format(avg(sal),2) from emp group by job;
+-----------+----------+--------------------+
| job       | count(*) | format(avg(sal),2) |
+-----------+----------+--------------------+
| ANALYST   |        2 | 3,000.00           |
| CLERK     |        4 | 1,037.50           |
| MANAGER   |        3 | 2,758.33           |
| PRESIDENT |        1 | 5,000.00           |
| SALESMAN  |        4 | 1,400.00           |
+-----------+----------+--------------------+
5 rows in set (0.00 sec)

1.2多表查询

刚刚我们所写的都是一些复杂的单表查询，但是在实际开发中往往数据来自不同的表，所以需要多表查询。下面我们继续使用emp/dept表来演示如何进行多表查询。

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

1.2.1联合查询

我们为了更好的展示联合查询的结果，我们手动构建一个较为简单的表 t1,t2，并向其插入两条简单的记录。

mysql> create table t1(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.24 sec)

mysql> create table t2( id int, name varchar(20) );
Query OK, 0 rows affected (0.27 sec)

mysql> insert into t1 values(1,'t1数据1'),(2,'t1数据2');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(1,'t2.数据1'),(2,'t2数据2');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

查询t1/t2 表数据

mysql> select * from t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | t1数据1   |
|    2 | t1数据2   |
+------+-----------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | t2数据1   |
|    2 | t2数据2   |
+------+-----------+
2 rows in set (0.00 sec)

此时我们将t1,t2表进行联合查询

mysql> select * from t1,t2;
+------+-----------+------+-----------+
| id   | name      | id   | name      |
+------+-----------+------+-----------+
|    1 | t1数据1   |    1 | t2数据1   |
|    2 | t1数据2   |    1 | t2数据1   |
|    1 | t1数据1   |    2 | t2数据2   |
|    2 | t1数据2   |    2 | t2数据2   |
+------+-----------+------+-----------+
4 rows in set (0.00 sec)

我们能够发现下面结论：

从第一张表中选出第一条记录，和第二个表的所有记录进行组合。
然后从第一张表中取第二条记录，和第二张表中的所有记录组合
不加过滤条件，得到的结果称为 笛卡尔积

假如我们只需要id相等的放在这张表中，我们就可以添加条件进行筛选

mysql> select * from t1,t2 where t1.id = t2.id;
+------+-----------+------+-----------+
| id   | name      | id   | name      |
+------+-----------+------+-----------+
|    1 | t1数据1   |    1 | t2数据1   |
|    2 | t1数据2   |    2 | t2数据2   |
+------+-----------+------+-----------+
2 rows in set (0.00 sec)

显示雇员名，雇员工资以及所在部门的名字

就可以使用联合查询，sql如下

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

显示部门号为10的部门名，员工名和工资

mysql> select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and dept.deptno=10;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| CLARK  | 2450.00 | ACCOUNTING |
| KING   | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
3 rows in set (0.00 sec)

显示各个员工的姓名，工资及工资级别

mysql> select ename, sal, grade from emp, salgrade where emp.sal between losal and hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.01 sec)

1.3 自连接

自连接是指在同一张表连接查询

比如：显示员工FORD的上级领导的编号和姓名(mgr是员工领导的编号--empno)

使用子查询

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

使用多表查询(子查询)

使用到了表的别名，from emp leader,emp worker 给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别

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

1.4子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。其实我们在上面已经使用过类似的操作了。

1.4.1单行子查询

返回一行记录的子查询

显示SMITH同一部门的员工

我们要返回SMITH同一部门的员工首先要查询SMITH所属于哪个部门

mysql> select * from emp where deptno=(select deptno from emp where ename='smith');
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
| 007876 | ADAMS | CLERK   | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |
| 007902 | FORD  | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+---------+------+---------------------+---------+------+--------+
5 rows in set (0.00 sec)

1.4.2多行子查询

返回多行记录的子查询

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

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 |
+-------+---------+---------+--------+
| JONES | MANAGER | 2975.00 |     20 |
| BLAKE | MANAGER | 2850.00 |     30 |
| SMITH | CLERK   |  800.00 |     20 |
| ADAMS | CLERK   | 1100.00 |     20 |
| JAMES | CLERK   |  950.00 |     30 |
+-------+---------+---------+--------+
5 rows in set (0.00 sec)

all关键字: 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

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的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

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.4.3多列子查询

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

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

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

1.4.4 在from子句中使用子查询

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

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

mysql> select ename, deptno, sal, format(asal,2) from emp,   (select avg(sal) asal, deptno dt from emp group by deptno) tmp  where emp.sal > tmp.asal and emp.deptno=tmp.dt;
+-------+--------+---------+----------------+
| ename | deptno | sal     | format(asal,2) |
+-------+--------+---------+----------------+
| KING  |     10 | 5000.00 | 2,916.67       |
| JONES |     20 | 2975.00 | 2,175.00       |
| SCOTT |     20 | 3000.00 | 2,175.00       |
| FORD  |     20 | 3000.00 | 2,175.00       |
| ALLEN |     30 | 1600.00 | 1,566.67       |
| BLAKE |     30 | 2850.00 | 1,566.67       |
+-------+--------+---------+----------------+
6 rows in set (0.03 sec)

查找每个部门工资最高的人的姓名，工资，部门，最高工资

mysql> select emp.ename, emp.sal, emp.deptno, ms from emp,  (select max(sal) ms, deptno fromemp group by deptno) tmp   where emp.deptno=tmp.deptno and emp.sal=tmp.ms;
+-------+---------+--------+---------+
| ename | sal     | deptno | ms      |
+-------+---------+--------+---------+
| BLAKE | 2850.00 |     30 | 2850.00 |
| SCOTT | 3000.00 |     20 | 3000.00 |
| KING  | 5000.00 |     10 | 5000.00 |
| FORD  | 3000.00 |     20 | 3000.00 |
+-------+---------+--------+---------+
4 rows in set (0.02 sec)

显示每个部门的信息(部门名，编号，地址)和人员数量

mysql> select dept.dname, dept.deptno, dept.loc,count(*) '部门人数' from emp, dept  where emp.deptno=dept.deptno  group by dept.deptno,dept.dname,dept.loc;
+------------+--------+----------+--------------+
| dname      | deptno | loc      | 部门人数     |
+------------+--------+----------+--------------+
| ACCOUNTING |     10 | NEW YORK |            3 |
| RESEARCH   |     20 | DALLAS   |            5 |
| SALES      |     30 | CHICAGO  |            6 |
+------------+--------+----------+--------------+
3 rows in set (0.00 sec)

1.4.5 合并查询

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

1.4.5.1 union

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

将工资大于2500或置为是MANAGER的人找出来

mysql> select ename, sal, job from emp where sal>2500 union select ename, sal, job from emp whhere job='MANAGER';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+
6 rows in set (0.00 sec)

1.4.5.2 union all

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

将工资大于2500或者职位是MANAGER的人找出来

mysql> select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='MANGER';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
+-------+---------+-----------+
5 rows in set (0.00 sec)

(本篇完)

标签：00,rows,affected,sal,--,08,sec,emp,MySQL
From： https://blog.51cto.com/xingyuli/6275113

【MySQL--08】复合查询

【MySQL--08】复合查询

1.1基本查询回顾

1.2多表查询

1.2.1联合查询

1.3 自连接

1.4子查询

1.4.1单行子查询

1.4.2多行子查询

1.4.3多列子查询

1.4.4 在from子句中使用子查询

1.4.5 合并查询

1.4.5.1 union

1.4.5.2 union all

相关文章

赞助商

阅读排行