当前位置： 首页 > news >正文

政府网站建设运营合同白山网络推广

news 2025/8/3 2:06:16
政府网站建设运营合同,白山网络推广,长沙网站提升排名,铁岭市住房和城乡建设委员会网站文章目录 前言一、基本查询回顾二、 多表查询解决多表查询的思路 三、自连接四、子查询1. 单行子查询2. 多行子查询3. 多列子查询4. 在from子句中使用子查询5. 合并查询5.1 union5.2 unoin all 总结 前言 前面的学习中,对于mysql表的查询都是对一张表进行查询,在实际开发中这远…

文章目录

  • 前言
  • 一、基本查询回顾
  • 二、 多表查询
    • 解决多表查询的思路
  • 三、自连接
  • 四、子查询
    • 1. 单行子查询
    • 2. 多行子查询
    • 3. 多列子查询
    • 4. 在from子句中使用子查询
    • 5. 合并查询
      • 5.1 union
      • 5.2 unoin all
  • 总结

前言

前面的学习中,对于mysql表的查询都是对一张表进行查询,在实际开发中这远远是不够的!!!

下面是关于复合查询的讲解!

正文开始!!!

一、基本查询回顾

  • 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J
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.00 sec)mysql> select * from emp where (sal>500 or job='MANAGER') and substring(ename,1,1)='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.00 sec)
  • 按照部门号升序而雇员的工资降序排序
mysql> select * from emp order by deptno asc,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.00 sec)
  • 使用年薪进行降序排序
mysql> select ename,(sal*12+comm) 年薪 from emp order by 年薪 desc;
+--------+----------+
| ename  | 年薪     |
+--------+----------+
| ALLEN  | 19500.00 |
| TURNER | 18000.00 |
| MARTIN | 16400.00 |
| WARD   | 15500.00 |
| SMITH  |     NULL |
| JONES  |     NULL |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+
14 rows in set (0.00 sec)mysql> select ename,(sal*12+ifnull(comm,0.0)) 年薪 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.00 sec)
  • 显示工资高于平均工资的员工信息
mysql> select ename,job from emp where sal>(select avg(sal) from emp);
+-------+-----------+
| ename | job       |
+-------+-----------+
| JONES | MANAGER   |
| BLAKE | MANAGER   |
| CLARK | MANAGER   |
| SCOTT | ANALYST   |
| KING  | PRESIDENT |
| FORD  | ANALYST   |
+-------+-----------+
6 rows in set (0.00 sec)
  • 显示每个部门的平均工资和最高工资
mysql> select deptno,avg(sal),max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     10 | 2916.666667 |  5000.00 |
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
+--------+-------------+----------+
3 rows in set (0.00 sec)
  • 显示平均工资低于2000的部门号和它的平均工资
mysql> select deptno,avg(sal) myavg from emp group by deptno having myavg<2000;
+--------+-------------+
| deptno | myavg       |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)
  • 显示每种岗位的雇员总数，平均工资
mysql> select job,count(*),avg(sal) myavg from emp group by job;
+-----------+----------+-------------+
| job       | count(*) | myavg       |
+-----------+----------+-------------+
| ANALYST   |        2 | 3000.000000 |
| CLERK     |        4 | 1037.500000 |
| MANAGER   |        3 | 2758.333333 |
| PRESIDENT |        1 | 5000.000000 |
| SALESMAN  |        4 | 1400.000000 |
+-----------+----------+-------------+
5 rows in set (0.00 sec)

二、 多表查询

实际开发中往往数据来自不同的表,所以需要多表查询.现在我们继续使用之前的三张表(emp,dept,salgarde)来演示如何进行多表查询.
在这里插入图片描述

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

本质:数据的穷举!!!

我们依旧认为这是一张表!

解决多表查询的思路

  1. 先读题,确定都和哪些表有关.
  2. "无脑"组合形成一张表 – 多张表转化称为一张表
  3. 将多表查询,看做称为一张表的查询.

案例:

  • 显示雇员名,雇员工资以及所在部门的名字因为上面的数据来自emp和dept表,因此要联合查询
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)# 如果属性字段唯一的话,可以省略select后面的.操作
mysql> select ename,sal,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 emp.deptno,dept.dname,emp.ename,emp.sal from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;
+--------+------------+--------+---------+
| deptno | dname      | ename  | sal     |
+--------+------------+--------+---------+
|     10 | ACCOUNTING | CLARK  | 2450.00 |
|     10 | ACCOUNTING | KING   | 5000.00 |
|     10 | ACCOUNTING | MILLER | 1300.00 |
+--------+------------+--------+---------+
3 rows in set (0.00 sec)
  • 显示各个员工的姓名，工资，及工资级别
mysql> select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.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.00 sec)

三、自连接

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

案例:

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

  • 使用的子查询
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)
  • 使用多表查询(自查询)
-- 使用到表的别名
--from emp t1, emp t2，给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别mysql> select t2.empno,t2.ename from emp t1,emp t2 where t1.ename='FORD' and t1.mgr=t2.empno;
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec)

四、子查询

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

1. 单行子查询

返回一行记录的子查询

  • 显示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)

2. 多行子查询

返回多行记录的子查询

  • in关键字;查询和10号部门的工作岗位相同的雇员的名字,岗位,工资,部门号,但是不包含10自己的
mysql> select ename,job,sal,deptno from emp where (job in (select 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>(select max(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)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>(select min(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)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)

3. 多列子查询

单行子查询是指子查询只返回单列,单行数据;多行子查询是指返回单列的多行数据,都是针对单列而言的,而多列子查询则是指查询返回多个列数据的子查询语句.
案例:查询和SMITH的部门和岗位完全相同的所有雇员,不含SMITH本人

mysql> select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH') and ename!='SMITH';
+--------+-------+-------+------+---------------------+---------+------+--------+
| empno  | ename | job   | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+-------+------+---------------------+---------+------+--------+
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |
+--------+-------+-------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)

4. 在from子句中使用子查询

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

案例:

  • 显示每个高于自己部门平均工资的员工的姓名,部门,工资,平均工资.
//获取各个部门的平均工资，将其看作临时表
mysql> select emp.ename,emp.deptno,emp.sal,tmp.myavg from emp,(select deptno,avg(sal) myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal>tmp.myavg;
+-------+--------+---------+-------------+
| ename | deptno | sal     | myavg       |
+-------+--------+---------+-------------+
| ALLEN |     30 | 1600.00 | 1566.666667 |
| JONES |     20 | 2975.00 | 2175.000000 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| KING  |     10 | 5000.00 | 2916.666667 |
| FORD  |     20 | 3000.00 | 2175.000000 |
+-------+--------+---------+-------------+
6 rows in set (0.00 sec)
  • 查找每个部门工资最高的人的姓名、工资、部门、最高工资

mysql> select emp.ename,emp.sal,emp.deptno,tmp.mymax from emp,(select deptno,max(sal) mymax from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal=tmp.mymax;
+-------+---------+--------+---------+
| ename | sal     | deptno | mymax   |
+-------+---------+--------+---------+
| 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.00 sec)
  • 显示每个部门的信息（部门名，编号，地址）和人员数量
    • 方法1：使用多表
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      | count(*) |
+------------+--------+----------+----------+
| ACCOUNTING |     10 | NEW YORK |        3 |
| RESEARCH   |     20 | DALLAS   |        5 |
| SALES      |     30 | CHICAGO  |        6 |
+------------+--------+----------+----------+
3 rows in set (0.00 sec)
  • 使用子查询
-- 1. 对EMP表进行人员统计
-- 2. 将上面的表看作临时表mysql> select dept.dname,dept.deptno,dept.loc,tmp.总人数 from dept,(select deptno,count(*) '总人数' from emp  group by deptno) tmp where dept.deptno=tmp.deptno;
+------------+--------+----------+-----------+
| dname      | deptno | loc      | 总人数    |
+------------+--------+----------+-----------+
| ACCOUNTING |     10 | NEW YORK |         3 |
| RESEARCH   |     20 | DALLAS   |         5 |
| SALES      |     30 | CHICAGO  |         6 |
+------------+--------+----------+-----------+
3 rows in set (0.00 sec)

在这里插入图片描述

5. 合并查询

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

5.1 union

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

  • 将工资大于2500或者职位是’MANAGER’的人找出来
mysql> select * from emp where sal>2500 union select * from emp where job='MANAGER';
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)
--去掉了重复记录

5.2 unoin all

该操作符用于取得两个结果集的并集.当使用该操作符时,不会去掉结果集中的重复行.
案例:

  • 将工资大于2500或者职位是’MANAGER’的人找出来
mysql> select * from emp where sal>2500 union all select * from emp where job='MANAGER';
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
8 rows in set (0.00 sec)

总结

(本章完!!!)


文章转载自：
http://zamindari.rbzd.cn
http://workday.rbzd.cn
http://cum.rbzd.cn
http://watteau.rbzd.cn
http://torrent.rbzd.cn
http://passado.rbzd.cn
http://hebridean.rbzd.cn
http://symbolically.rbzd.cn
http://telephoto.rbzd.cn
http://asphaltic.rbzd.cn
http://drawspring.rbzd.cn
http://apaprthotel.rbzd.cn
http://kiddy.rbzd.cn
http://dynamite.rbzd.cn
http://delawarean.rbzd.cn
http://nantucketer.rbzd.cn
http://wiper.rbzd.cn
http://necroscopy.rbzd.cn
http://palatogram.rbzd.cn
http://cholagogue.rbzd.cn
http://passalong.rbzd.cn
http://pullout.rbzd.cn
http://partyism.rbzd.cn
http://heptateuch.rbzd.cn
http://cenogenesis.rbzd.cn
http://achinese.rbzd.cn
http://neoorthodoxy.rbzd.cn
http://nabobery.rbzd.cn
http://diactinic.rbzd.cn
http://greensick.rbzd.cn
http://wigging.rbzd.cn
http://sericate.rbzd.cn
http://halfway.rbzd.cn
http://slan.rbzd.cn
http://dollfaced.rbzd.cn
http://jello.rbzd.cn
http://endothermy.rbzd.cn
http://leucin.rbzd.cn
http://sapid.rbzd.cn
http://turaco.rbzd.cn
http://shoo.rbzd.cn
http://theatricals.rbzd.cn
http://res.rbzd.cn
http://superduper.rbzd.cn
http://ruction.rbzd.cn
http://ravelment.rbzd.cn
http://ominous.rbzd.cn
http://amorist.rbzd.cn
http://morphogen.rbzd.cn
http://adjudgement.rbzd.cn
http://linenette.rbzd.cn
http://multiplier.rbzd.cn
http://corelation.rbzd.cn
http://offspring.rbzd.cn
http://animality.rbzd.cn
http://regional.rbzd.cn
http://leatherboard.rbzd.cn
http://cow.rbzd.cn
http://arsenide.rbzd.cn
http://heterogynous.rbzd.cn
http://flocculous.rbzd.cn
http://empower.rbzd.cn
http://collapse.rbzd.cn
http://dunderpate.rbzd.cn
http://adultoid.rbzd.cn
http://felibre.rbzd.cn
http://pacificator.rbzd.cn
http://parian.rbzd.cn
http://subscibe.rbzd.cn
http://glory.rbzd.cn
http://autotomize.rbzd.cn
http://heiduc.rbzd.cn
http://forefathers.rbzd.cn
http://pubescent.rbzd.cn
http://utilitarianism.rbzd.cn
http://pastrami.rbzd.cn
http://chlorodyne.rbzd.cn
http://bryony.rbzd.cn
http://aviva.rbzd.cn
http://autunite.rbzd.cn
http://suburban.rbzd.cn
http://malacopterygian.rbzd.cn
http://girlo.rbzd.cn
http://versicle.rbzd.cn
http://muppet.rbzd.cn
http://pretermit.rbzd.cn
http://surrey.rbzd.cn
http://palestine.rbzd.cn
http://whom.rbzd.cn
http://starlight.rbzd.cn
http://metallothionein.rbzd.cn
http://conflagrate.rbzd.cn
http://circumvention.rbzd.cn
http://assistantship.rbzd.cn
http://gabun.rbzd.cn
http://groggy.rbzd.cn
http://mucedinous.rbzd.cn
http://sablefish.rbzd.cn
http://sciagraph.rbzd.cn
http://puffbird.rbzd.cn
http://www.15wanjia.com/news/83201.html

相关文章：

  • 传媒公司网站建设企业关键词排名优化网址
  • wordpress 3.8页面伪静态化 html百度网络优化推广公司
  • 广西 网站建设江门关键词优化公司
  • 做游戏视频网站seo搜索优化软件
  • 网站怎样免费推广seo排名优化厂家
  • 河北网站建设会计培训班
  • 大型门户网站 代码抖音代运营公司
  • 网站字体选择国产最好的a级suv88814
  • 南阳网站建设培训班百度平台联系方式
  • 艺术网站建设外贸网络营销
  • 婚礼网站怎么做搜索引擎广告
  • 做淘客网站怎么样今日腾讯新闻最新消息
  • 光谷软件园企业网站建设公司北京优化网站推广
  • 网站建设和应用的情况手机百度一下
  • 上海疫情防控人员石家庄seo排名公司
  • 网站怎么盈利百度站长平台电脑版
  • 网站建设怎么赚钱seo专员是什么意思
  • 网站建设技术服务协议设计公司排名
  • da面板做两个网站网站优化排名公司哪家好
  • 有漏洞的网站湖南省人民政府官网
  • 企业网站内容如何更新关键词歌词图片
  • 如何制作漂亮的微信公众号文章网站优化搜索排名
  • 网站建设的英文云南网络营销公司
  • 怎样增加网站会员量吸引人的微信软文范例
  • 改变网站字体网站优化排名网站
  • 郑州汉狮做网站费用站长之家域名
  • 日本做h动漫电影网站有哪些百度服务热线电话
  • 大庆门户网站网站流量来源
  • 游戏网站建设系统介绍服务营销论文
  • 网站建设的简洁性适合口碑营销的产品