文章目錄*1.基本查詢回顧* 2.多表查詢* 3.自連接* 4.子查詢* * 4.1單行子查詢 * 4.2多行子查詢 * 4.3多列子查詢 * 4.4在from子句中使用子查詢 * 4.5合并查詢 * * 4.5.1 union * 4.5.2 union all1.基本查詢回顧--------表的內容如下 mysql select * from emp; ±-------±-------±----------±-----±--------------------±--------±--------±------- | 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 | | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | ±-------±-------±----------±-----±--------------------±--------±--------±------- 14 rows in set (0.00 sec) mysql select * from dept; ±-------±-----------±--------- | deptno | dname | loc | ±-------±-----------±--------- | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | ±-------±-----------±--------- 4 rows in set (0.00 sec) mysql select * from salgrade; ±------±------±------ | grade | losal | hisal | ±------±------±------ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | ±------±------±------ 5 rows in set (0.00 sec) * 查詢工資高于500或崗位為MANAGER的雇員同時還要滿足他們的姓名首字母為大寫的J // 使用模糊查詢 select * from emp where (sal500 or job‘MANAGER’) and ename like ‘J%’; // 使用函數 select * from emp where (sal500 or job‘MANAGER’) and substring(ename,1,1)‘J’; mysql select * from emp where (sal500 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 (sal500 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)* 按照部門號升序而雇員的工資降序排序 select * from emp order by deptno asc, sal desc; 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 | CLACK | 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 | ±-------±-------±----------±-----±--------------------±--------±--------±-------* 使用年薪進行降序排序 年薪等于工資*12獎金 需要對獎金進行判斷如果獎金為null則獎金為0 select ename, sal12ifnull(comm,0) as ‘年薪’ from emp order by 年薪 desc; mysql select ename,sal12ifnull(comm,0) as ‘年薪’ from emp order by 年薪 desc; ±-------±--------- | ename | 年薪 | ±-------±--------- | SMITH | 9600.00 | | ALLEN | 19500.00 | | WARD | 15500.00 | | JONES | 35700.00 | | MARTIN | 16400.00 | | BLAKE | 34200.00 | | TEST | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | ±-------±--------- 14 rows in set (0.00 sec)* 顯示工資最高的員工的名字和工作崗位 這里使用分組查詢即可先查出最高的工資然后查詢工資等于最高工資的員工的姓名和工作崗位 select ename,job from emp where sal (select max(sal) from emp); mysql select ename,job from emp where sal (select max(sal) from emp); ±------±---------- | ename | job | ±------±---------- | KING | PRESIDENT | ±------±---------- 1 row in set (0.00 sec)* 顯示工資高于平均工資的員工信息 這里使用分組查詢即可 select ename,sal from emp where sal (select avg(sal) from emp); mysql select ename,sal from emp where sal (select avg(sal) from emp); ±------±-------- | ename | sal | ±------±-------- | JONES | 2975.00 | | BLAKE | 2850.00 | | TEST | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | ±------±-------- 6 rows in set (0.00 sec)* 顯示每個部門的平均工資和最高工資 select deptno,avg(sal),max(sal) from emp group by deptno; mysql select deptno,avg(sal),max(sal) from emp group by deptno; ±-------±------------±--------- | deptno | avg(sal) | max(sal) | ±-------±------------±--------- | 10 | 2425.000000 | 5000.00 | | 20 | 2175.000000 | 3000.00 | | 30 | 1690.000000 | 2850.00 | ±-------±------------±--------- 3 rows in set (0.00 sec)* 顯示平均工資低于2000的部門號和它的平均工資 select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal 2000; mysql select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal 2000; ±-------±------------ | deptno | avg_sal | ±-------±------------ | 30 | 1690.000000 | ±-------±------------ 1 row in set (0.00 sec)* 顯示每種崗位的雇員總數平均工資 select job,count(), avg(sal) from emp group by job; mysql select job,count(), avg(sal) from emp group by job; ±----------±---------±------------ | job | count() | avg(sal) | ±----------±---------±------------ | 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)2.多表查詢------實際開發中往往數據來自不同的表所以需要多表查詢。本節我們用一個簡單的公司管理系統有三張表emp,dept,salgrade來演示如何進行多表查詢。案例顯示雇員名、雇員工資以及所在部門的名字因為上面的數據來自emp和dept表因此要聯合查詢其實我們只要emp表中的deptno dept表中的deptno字段的記錄 select ename,sal,dname from emp,dept where emp.deptnodept.deptno; mysql select ename,sal,dname from emp,dept where emp.deptnodept.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 | | CLACK | 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 dname,ename,sal from emp,dept where emp.deptnodept.deptno and dept.deptno10; ±-----------±-------±-------- | dname | ename | sal | ±-----------±-------±-------- | ACCOUNTING | CLACK | 2450.00 | | ACCOUNTING | KING | 5000.00 | | ACCOUNTING | MILLER | 1300.00 | ±-----------±-------±-------- 3 rows in set (0.00 sec)* 顯示各個員工的姓名工資及工資級別 mysql select ename,sal,grade from emp,salgrade where sal between losal and hisal; mysql select ename,sal,grade from emp,salgrade where 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 | | CLACK | 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)3.自連接-----自連接是指在同一張表連接查詢案例顯示員工FORD的上級領導的編號和姓名mgr是員工領導的編號–empno使用的子查詢select ename,empno from emp where empno(select mgr from emp where ename‘FORD’);使用多表查詢自查詢select e2.ename,e2.empno from emp e1,emp e2 where e1.ename‘FORD’ and e1.mgre2.empno; mysql select e1.ename,e2.empno from emp e1,emp e2 where e1.ename‘FORD’ and e1.mgre2.empno; ±------±------- | ename | empno | ±------±------- | FORD | 007566 | ±------±------- 1 row in set (0.00 sec)4.子查詢-----子查詢是指嵌入在其他sql語句中的select語句也叫嵌套查詢### 4.1單行子查詢返回一行記錄的子查詢*顯示SMITH同一部門的員工select * from emp where deptno(select deptno from emp where ename‘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)### 4.2多行子查詢返回多行記錄的子查詢*in關鍵字查詢和10號部門的工作崗位相同的雇員的名字崗位工資部門號但是不包含10自己的 select ename,job,sal,deptno from emp where job in(select job from emp where deptno10) and deptno10; mysql select ename,job,sal,deptno from emp where job in(select job from emp where deptno10) and deptno10; ±------±--------±--------±------- | 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的所有員工的工資高的員工的姓名、工資和部門號 // 使用聚合函數 select ename,sal,deptno from emp where sal(select max(sal) from emp where deptno30); mysql select ename,sal,deptno from emp where sal(select max(sal) from emp where deptno30); ±------±--------±------- | ename | sal | deptno | ±------±--------±------- | JONES | 2975.00 | 20 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | FORD | 3000.00 | 20 | ±------±--------±------- 4 rows in set (0.01 sec) // 使用all關鍵子 select ename,sal,deptno from emp where salall(select sal from emp where deptno30); mysql select ename,sal,deptno from emp where salall(select sal from emp where deptno30); ±------±--------±------- | 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 deptno30) and deptno30; ±-------±--------±------- | ename | sal | deptno | ±-------±--------±------- | JONES | 2975.00 | 20 | | CLACK | 2450.00 | 10 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | ADAMS | 1100.00 | 20 | | FORD | 3000.00 | 20 | | MILLER | 1300.00 | 10 | ±-------±--------±------- 7 rows in set (0.00 sec) // 使用any關鍵字 mysql select ename,sal,deptno from emp where sal any(select sal from emp where deptno30) and deptno30; ±-------±--------±------- | ename | sal | deptno | ±-------±--------±------- | JONES | 2975.00 | 20 | | CLACK | 2450.00 | 10 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | ADAMS | 1100.00 | 20 | | FORD | 3000.00 | 20 | | MILLER | 1300.00 | 10 | ±-------±--------±------- 7 rows in set (0.00 sec) ### 4.3多列子查詢單行子查詢是指子查詢只返回單列單行數據多行子查詢是指返回單列多行數據都是針對單列而言的而多列子查詢則是指查詢返回多個列數據的子查詢語句案例查詢和SMITH的部門和崗位完全相同的所有雇員不含SMITH本人mysql select * from emp where (deptno,job)(select deptno,job from emp where ename‘SMITH’) and ename‘SMITH’; mysql select * from emp where (deptno,job)in(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.4在from子句中使用子查詢子查詢語句出現在from子句中。這里要用到數據查詢的技巧把一個子查詢當做一個臨時表使用。案例顯示每個高于自己部門平均工資的員工的姓名、部門、工資、平均工資答案 select t1.ename,t1.deptno,t1.sal,t2.myavg from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptnot2.deptno and t1.ssal t2.myavg; 步驟 // 1.根據部門號分組得到每組的平均工資 mysql select avg(sal) from emp group by deptno; ±------------ | avg(sal) | ±------------ | 2916.666667 | | 2175.000000 | | 1566.666667 | ±------------ 3 rows in set (0.00 sec) // 2.根據部門號分組得到每組的平均工資和部門號 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) // 3.將上面得到的結果與emp表做笛卡爾積 mysql select * from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptnot2.deptno; ±-------±-------±----------±-----±--------------------±--------±--------±-------±-------±------------ | empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | myavg | ±-------±-------±----------±-----±--------------------±--------±--------±-------±-------±------------ | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 20 | 2175.000000 | | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 30 | 1566.666667 | | 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 30 | 1566.666667 | | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 20 | 2175.000000 | | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | 1566.666667 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 30 | 1566.666667 | | 007782 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 10 | 2916.666667 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 20 | 2175.000000 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 10 | 2916.666667 | | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 30 | 1566.666667 | | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 20 | 2175.000000 | | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 30 | 1566.666667 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 20 | 2175.000000 | | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 10 | 2916.666667 | ±-------±-------±----------±-----±--------------------±--------±--------±-------±-------±------------ 14 rows in set (0.00 sec) // 5.增加篩選條件 :工資大于平均工資 mysql select * from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptnot2.deptno and t1.sal t2.myavg; ±-------±------±----------±-----±--------------------±--------±-------±-------±-------±------------ | empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | myavg | ±-------±------±----------±-----±--------------------±--------±-------±-------±-------±------------ | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 30 | 1566.666667 | | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 20 | 2175.000000 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 30 | 1566.666667 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 20 | 2175.000000 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 10 | 2916.666667 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 20 | 2175.000000 | ±-------±------±----------±-----±--------------------±--------±-------±-------±-------±------------ 6 rows in set (0.00 sec) // 5.根據題目要求得到結果 mysql select t1.ename,t1.deptno,t1.sal,t2.myavg from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptnot2.deptno and t1.ssal t2.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)查找每個部門工資最高的人的姓名、工資、部門、最高工資答案 select t1.ename,t1.sal,t1.deptno,t2.mymax from emp t1,(select deptno, max(sal) mymax from emp group by deptno) t2 where t1.deptnot2.deptno and t1…salt2.mymax; 步驟 // 1.得到分組之后的部門號和最高工資 mysql select deptno, max(sal) from emp group by deptno; ±-------±--------- | deptno | max(sal) | ±-------±--------- | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | ±-------±--------- 3 rows in set (0.01 sec) // 2.與emp表進行笛卡爾積并進行t1.salt2.mymax的篩選(工資等于最高工資) mysql select * from emp t1,(select deptno, max(sal) mymax from emp group by deptno) t2 where t1.deptnot2.deptno and t1.salt2.mymax; ±-------±------±----------±-----±--------------------±--------±-----±-------±-------±-------- | empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | mymax | ±-------±------±----------±-----±--------------------±--------±-----±-------±-------±-------- | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 30 | 2850.00 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 20 | 3000.00 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 10 | 5000.00 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 20 | 3000.00 | ±-------±------±----------±-----±--------------------±--------±-----±-------±-------±-------- 4 rows in set (0.00 sec) // 3.根據題目要求選擇需要篩選的內容 mysql select t1.ename,t1.sal,t1.deptno,t2.mymax from emp t1,(select deptno, max(sal) mymax from emp group by deptno) t2 where t1.deptnot2.deptno and t1…salt2.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顯示每個部門的信息部門名編號地址和人員數量答案 select t1.deptno,t1.dname,t1.loc,t2.num from dept t1,(select deptno,count() num from emp group by deptno) t2 where t1.deptnot2.deptno; 步驟 // 1.分組得到每一組的人數 mysql select deptno,count() num from emp group by deptno; ±-------±---- | deptno | num | ±-------±---- | 10 | 3 | | 20 | 5 | | 30 | 6 | ±-------±---- 3 rows in set (0.00 sec) // 2.和部門表進行笛卡爾積然后進行條件篩選 mysql select * from dept t1,(select deptno,count() num from emp group by deptno) t2 where t1.deptnot2.deptno; ±-------±-----------±---------±-------±---- | deptno | dname | loc | deptno | num | ±-------±-----------±---------±-------±---- | 10 | ACCOUNTING | NEW YORK | 10 | 3 | | 20 | RESEARCH | DALLAS | 20 | 5 | | 30 | SALES | CHICAGO | 30 | 6 | ±-------±-----------±---------±-------±---- 3 rows in set (0.01 sec) mysql select t1.deptno,t1.dname,t1.loc,t2.num from dept t1,(select deptno,count() num from emp group by deptno) t2 where t1.deptnot2.deptno; ±-------±-----------±---------±---- | deptno | dname | loc | num | ±-------±-----------±---------±---- | 10 | ACCOUNTING | NEW YORK | 3 | | 20 | RESEARCH | DALLAS | 5 | | 30 | SALES | CHICAGO | 6 | ±-------±-----------±---------±---- 3 rows in set (0.00 sec) 暴力解法 mysql select dept.dname,dept.deptno,dept.loc,count() from emp,dept where emp.deptnodept.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.01 sec) 總結解決多表問題的本質想辦法將多表轉化為單表所以mysql中所有select的問題全部都可以轉化成單表問題### 4.5合并查詢在實際應用中為了合并多個select的執行結果可以使用集合操作符 unionunion all#### 4.5.1 union該操作符用于取得兩個結果集的并集。當使用該操作符時會自動去掉結果集中的重復行。案例將工資大于2500或職位是MANAGER的人找出// 1.查出工資大于2500的 mysql select * from emp where sal2500; ±-------±------±----------±-----±--------------------±--------±-----±------- | empno | ename | job | mgr | hiredate | sal | comm | deptno | ±-------±------±----------±-----±--------------------±--------±-----±------- | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | ±-------±------±----------±-----±--------------------±--------±-----±------- 5 rows in set (0.00 sec) // 2.查出jobMANAGER的 mysql select * from emp where job‘MANAGER’; ±-------±------±--------±-----±--------------------±--------±-----±------- | empno | ename | job | mgr | hiredate | sal | comm | deptno | ±-------±------±--------±-----±--------------------±--------±-----±------- | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007782 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | ±-------±------±--------±-----±--------------------±--------±-----±------- 3 rows in set (0.00 sec) // 3.進行合并 mysql select * from emp where sal2500 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 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | ±------±------±----------±-----±--------------------±--------±-----±------- 6 rows in set (0.00 sec) #### 4.5.2 union all操作符用于取得兩個結果集的并集。當使用該操作符時不會去掉結果集中的重復行。案例將工資大于25000或職位是MANAGER的人找出來// 1.查出工資大于2500的 mysql select * from emp where sal2500; ±-------±------±----------±-----±--------------------±--------±-----±------- | empno | ename | job | mgr | hiredate | sal | comm | deptno | ±-------±------±----------±-----±--------------------±--------±-----±------- | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | ±-------±------±----------±-----±--------------------±--------±-----±------- 5 rows in set (0.00 sec) // 2.查出jobMANAGER的 mysql select * from emp where job‘MANAGER’; ±-------±------±--------±-----±--------------------±--------±-----±------- | empno | ename | job | mgr | hiredate | sal | comm | deptno | ±-------±------±--------±-----±--------------------±--------±-----±------- | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007782 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | ±-------±------±--------±-----±--------------------±--------±-----±------- 3 rows in set (0.01 sec) // 3.進行合并 mysql select * from emp where sal2500 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 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | ±------±------±----------±-----±--------------------±--------±-----±------- 8 rows in set (0.00 sec) 【MySQL】MySQL復合查詢--多表查詢自連接子查詢
文章目錄*1.基本查詢回顧* 2.多表查詢* 3.自連接* 4.子查詢* * 4.1單行子查詢 * 4.2多行子查詢 * 4.3多列子查詢 * 4.4在from子句中使用子查詢 * 4.5合并查詢 * * 4.5.1 union * 4.5.2 union all1.基本查詢回顧--------表的內容如下 mysql select * from emp; ±-------±-------±----------±-----±--------------------±--------±--------±------- | 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 | | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | ±-------±-------±----------±-----±--------------------±--------±--------±------- 14 rows in set (0.00 sec) mysql select * from dept; ±-------±-----------±--------- | deptno | dname | loc | ±-------±-----------±--------- | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | ±-------±-----------±--------- 4 rows in set (0.00 sec) mysql select * from salgrade; ±------±------±------ | grade | losal | hisal | ±------±------±------ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | ±------±------±------ 5 rows in set (0.00 sec) * 查詢工資高于500或崗位為MANAGER的雇員同時還要滿足他們的姓名首字母為大寫的J // 使用模糊查詢 select * from emp where (sal500 or job‘MANAGER’) and ename like ‘J%’; // 使用函數 select * from emp where (sal500 or job‘MANAGER’) and substring(ename,1,1)‘J’; mysql select * from emp where (sal500 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 (sal500 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)* 按照部門號升序而雇員的工資降序排序 select * from emp order by deptno asc, sal desc; 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 | CLACK | 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 | ±-------±-------±----------±-----±--------------------±--------±--------±-------* 使用年薪進行降序排序 年薪等于工資*12獎金 需要對獎金進行判斷如果獎金為null則獎金為0 select ename, sal12ifnull(comm,0) as ‘年薪’ from emp order by 年薪 desc; mysql select ename,sal12ifnull(comm,0) as ‘年薪’ from emp order by 年薪 desc; ±-------±--------- | ename | 年薪 | ±-------±--------- | SMITH | 9600.00 | | ALLEN | 19500.00 | | WARD | 15500.00 | | JONES | 35700.00 | | MARTIN | 16400.00 | | BLAKE | 34200.00 | | TEST | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | ±-------±--------- 14 rows in set (0.00 sec)* 顯示工資最高的員工的名字和工作崗位 這里使用分組查詢即可先查出最高的工資然后查詢工資等于最高工資的員工的姓名和工作崗位 select ename,job from emp where sal (select max(sal) from emp); mysql select ename,job from emp where sal (select max(sal) from emp); ±------±---------- | ename | job | ±------±---------- | KING | PRESIDENT | ±------±---------- 1 row in set (0.00 sec)* 顯示工資高于平均工資的員工信息 這里使用分組查詢即可 select ename,sal from emp where sal (select avg(sal) from emp); mysql select ename,sal from emp where sal (select avg(sal) from emp); ±------±-------- | ename | sal | ±------±-------- | JONES | 2975.00 | | BLAKE | 2850.00 | | TEST | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | ±------±-------- 6 rows in set (0.00 sec)* 顯示每個部門的平均工資和最高工資 select deptno,avg(sal),max(sal) from emp group by deptno; mysql select deptno,avg(sal),max(sal) from emp group by deptno; ±-------±------------±--------- | deptno | avg(sal) | max(sal) | ±-------±------------±--------- | 10 | 2425.000000 | 5000.00 | | 20 | 2175.000000 | 3000.00 | | 30 | 1690.000000 | 2850.00 | ±-------±------------±--------- 3 rows in set (0.00 sec)* 顯示平均工資低于2000的部門號和它的平均工資 select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal 2000; mysql select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal 2000; ±-------±------------ | deptno | avg_sal | ±-------±------------ | 30 | 1690.000000 | ±-------±------------ 1 row in set (0.00 sec)* 顯示每種崗位的雇員總數平均工資 select job,count(), avg(sal) from emp group by job; mysql select job,count(), avg(sal) from emp group by job; ±----------±---------±------------ | job | count() | avg(sal) | ±----------±---------±------------ | 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)2.多表查詢------實際開發中往往數據來自不同的表所以需要多表查詢。本節我們用一個簡單的公司管理系統有三張表emp,dept,salgrade來演示如何進行多表查詢。案例顯示雇員名、雇員工資以及所在部門的名字因為上面的數據來自emp和dept表因此要聯合查詢其實我們只要emp表中的deptno dept表中的deptno字段的記錄 select ename,sal,dname from emp,dept where emp.deptnodept.deptno; mysql select ename,sal,dname from emp,dept where emp.deptnodept.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 | | CLACK | 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 dname,ename,sal from emp,dept where emp.deptnodept.deptno and dept.deptno10; ±-----------±-------±-------- | dname | ename | sal | ±-----------±-------±-------- | ACCOUNTING | CLACK | 2450.00 | | ACCOUNTING | KING | 5000.00 | | ACCOUNTING | MILLER | 1300.00 | ±-----------±-------±-------- 3 rows in set (0.00 sec)* 顯示各個員工的姓名工資及工資級別 mysql select ename,sal,grade from emp,salgrade where sal between losal and hisal; mysql select ename,sal,grade from emp,salgrade where 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 | | CLACK | 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)3.自連接-----自連接是指在同一張表連接查詢案例顯示員工FORD的上級領導的編號和姓名mgr是員工領導的編號–empno使用的子查詢select ename,empno from emp where empno(select mgr from emp where ename‘FORD’);使用多表查詢自查詢select e2.ename,e2.empno from emp e1,emp e2 where e1.ename‘FORD’ and e1.mgre2.empno; mysql select e1.ename,e2.empno from emp e1,emp e2 where e1.ename‘FORD’ and e1.mgre2.empno; ±------±------- | ename | empno | ±------±------- | FORD | 007566 | ±------±------- 1 row in set (0.00 sec)4.子查詢-----子查詢是指嵌入在其他sql語句中的select語句也叫嵌套查詢### 4.1單行子查詢返回一行記錄的子查詢*顯示SMITH同一部門的員工select * from emp where deptno(select deptno from emp where ename‘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)### 4.2多行子查詢返回多行記錄的子查詢*in關鍵字查詢和10號部門的工作崗位相同的雇員的名字崗位工資部門號但是不包含10自己的 select ename,job,sal,deptno from emp where job in(select job from emp where deptno10) and deptno10; mysql select ename,job,sal,deptno from emp where job in(select job from emp where deptno10) and deptno10; ±------±--------±--------±------- | 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的所有員工的工資高的員工的姓名、工資和部門號 // 使用聚合函數 select ename,sal,deptno from emp where sal(select max(sal) from emp where deptno30); mysql select ename,sal,deptno from emp where sal(select max(sal) from emp where deptno30); ±------±--------±------- | ename | sal | deptno | ±------±--------±------- | JONES | 2975.00 | 20 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | FORD | 3000.00 | 20 | ±------±--------±------- 4 rows in set (0.01 sec) // 使用all關鍵子 select ename,sal,deptno from emp where salall(select sal from emp where deptno30); mysql select ename,sal,deptno from emp where salall(select sal from emp where deptno30); ±------±--------±------- | 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 deptno30) and deptno30; ±-------±--------±------- | ename | sal | deptno | ±-------±--------±------- | JONES | 2975.00 | 20 | | CLACK | 2450.00 | 10 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | ADAMS | 1100.00 | 20 | | FORD | 3000.00 | 20 | | MILLER | 1300.00 | 10 | ±-------±--------±------- 7 rows in set (0.00 sec) // 使用any關鍵字 mysql select ename,sal,deptno from emp where sal any(select sal from emp where deptno30) and deptno30; ±-------±--------±------- | ename | sal | deptno | ±-------±--------±------- | JONES | 2975.00 | 20 | | CLACK | 2450.00 | 10 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | ADAMS | 1100.00 | 20 | | FORD | 3000.00 | 20 | | MILLER | 1300.00 | 10 | ±-------±--------±------- 7 rows in set (0.00 sec) ### 4.3多列子查詢單行子查詢是指子查詢只返回單列單行數據多行子查詢是指返回單列多行數據都是針對單列而言的而多列子查詢則是指查詢返回多個列數據的子查詢語句案例查詢和SMITH的部門和崗位完全相同的所有雇員不含SMITH本人mysql select * from emp where (deptno,job)(select deptno,job from emp where ename‘SMITH’) and ename‘SMITH’; mysql select * from emp where (deptno,job)in(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.4在from子句中使用子查詢子查詢語句出現在from子句中。這里要用到數據查詢的技巧把一個子查詢當做一個臨時表使用。案例顯示每個高于自己部門平均工資的員工的姓名、部門、工資、平均工資答案 select t1.ename,t1.deptno,t1.sal,t2.myavg from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptnot2.deptno and t1.ssal t2.myavg; 步驟 // 1.根據部門號分組得到每組的平均工資 mysql select avg(sal) from emp group by deptno; ±------------ | avg(sal) | ±------------ | 2916.666667 | | 2175.000000 | | 1566.666667 | ±------------ 3 rows in set (0.00 sec) // 2.根據部門號分組得到每組的平均工資和部門號 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) // 3.將上面得到的結果與emp表做笛卡爾積 mysql select * from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptnot2.deptno; ±-------±-------±----------±-----±--------------------±--------±--------±-------±-------±------------ | empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | myavg | ±-------±-------±----------±-----±--------------------±--------±--------±-------±-------±------------ | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 20 | 2175.000000 | | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 30 | 1566.666667 | | 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 30 | 1566.666667 | | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 20 | 2175.000000 | | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | 1566.666667 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 30 | 1566.666667 | | 007782 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 10 | 2916.666667 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 20 | 2175.000000 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 10 | 2916.666667 | | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 30 | 1566.666667 | | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 20 | 2175.000000 | | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 30 | 1566.666667 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 20 | 2175.000000 | | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 10 | 2916.666667 | ±-------±-------±----------±-----±--------------------±--------±--------±-------±-------±------------ 14 rows in set (0.00 sec) // 5.增加篩選條件 :工資大于平均工資 mysql select * from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptnot2.deptno and t1.sal t2.myavg; ±-------±------±----------±-----±--------------------±--------±-------±-------±-------±------------ | empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | myavg | ±-------±------±----------±-----±--------------------±--------±-------±-------±-------±------------ | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 30 | 1566.666667 | | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 20 | 2175.000000 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 30 | 1566.666667 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 20 | 2175.000000 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 10 | 2916.666667 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 20 | 2175.000000 | ±-------±------±----------±-----±--------------------±--------±-------±-------±-------±------------ 6 rows in set (0.00 sec) // 5.根據題目要求得到結果 mysql select t1.ename,t1.deptno,t1.sal,t2.myavg from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptnot2.deptno and t1.ssal t2.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)查找每個部門工資最高的人的姓名、工資、部門、最高工資答案 select t1.ename,t1.sal,t1.deptno,t2.mymax from emp t1,(select deptno, max(sal) mymax from emp group by deptno) t2 where t1.deptnot2.deptno and t1…salt2.mymax; 步驟 // 1.得到分組之后的部門號和最高工資 mysql select deptno, max(sal) from emp group by deptno; ±-------±--------- | deptno | max(sal) | ±-------±--------- | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | ±-------±--------- 3 rows in set (0.01 sec) // 2.與emp表進行笛卡爾積并進行t1.salt2.mymax的篩選(工資等于最高工資) mysql select * from emp t1,(select deptno, max(sal) mymax from emp group by deptno) t2 where t1.deptnot2.deptno and t1.salt2.mymax; ±-------±------±----------±-----±--------------------±--------±-----±-------±-------±-------- | empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | mymax | ±-------±------±----------±-----±--------------------±--------±-----±-------±-------±-------- | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 30 | 2850.00 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 20 | 3000.00 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 10 | 5000.00 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 20 | 3000.00 | ±-------±------±----------±-----±--------------------±--------±-----±-------±-------±-------- 4 rows in set (0.00 sec) // 3.根據題目要求選擇需要篩選的內容 mysql select t1.ename,t1.sal,t1.deptno,t2.mymax from emp t1,(select deptno, max(sal) mymax from emp group by deptno) t2 where t1.deptnot2.deptno and t1…salt2.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顯示每個部門的信息部門名編號地址和人員數量答案 select t1.deptno,t1.dname,t1.loc,t2.num from dept t1,(select deptno,count() num from emp group by deptno) t2 where t1.deptnot2.deptno; 步驟 // 1.分組得到每一組的人數 mysql select deptno,count() num from emp group by deptno; ±-------±---- | deptno | num | ±-------±---- | 10 | 3 | | 20 | 5 | | 30 | 6 | ±-------±---- 3 rows in set (0.00 sec) // 2.和部門表進行笛卡爾積然后進行條件篩選 mysql select * from dept t1,(select deptno,count() num from emp group by deptno) t2 where t1.deptnot2.deptno; ±-------±-----------±---------±-------±---- | deptno | dname | loc | deptno | num | ±-------±-----------±---------±-------±---- | 10 | ACCOUNTING | NEW YORK | 10 | 3 | | 20 | RESEARCH | DALLAS | 20 | 5 | | 30 | SALES | CHICAGO | 30 | 6 | ±-------±-----------±---------±-------±---- 3 rows in set (0.01 sec) mysql select t1.deptno,t1.dname,t1.loc,t2.num from dept t1,(select deptno,count() num from emp group by deptno) t2 where t1.deptnot2.deptno; ±-------±-----------±---------±---- | deptno | dname | loc | num | ±-------±-----------±---------±---- | 10 | ACCOUNTING | NEW YORK | 3 | | 20 | RESEARCH | DALLAS | 5 | | 30 | SALES | CHICAGO | 6 | ±-------±-----------±---------±---- 3 rows in set (0.00 sec) 暴力解法 mysql select dept.dname,dept.deptno,dept.loc,count() from emp,dept where emp.deptnodept.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.01 sec) 總結解決多表問題的本質想辦法將多表轉化為單表所以mysql中所有select的問題全部都可以轉化成單表問題### 4.5合并查詢在實際應用中為了合并多個select的執行結果可以使用集合操作符 unionunion all#### 4.5.1 union該操作符用于取得兩個結果集的并集。當使用該操作符時會自動去掉結果集中的重復行。案例將工資大于2500或職位是MANAGER的人找出// 1.查出工資大于2500的 mysql select * from emp where sal2500; ±-------±------±----------±-----±--------------------±--------±-----±------- | empno | ename | job | mgr | hiredate | sal | comm | deptno | ±-------±------±----------±-----±--------------------±--------±-----±------- | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | ±-------±------±----------±-----±--------------------±--------±-----±------- 5 rows in set (0.00 sec) // 2.查出jobMANAGER的 mysql select * from emp where job‘MANAGER’; ±-------±------±--------±-----±--------------------±--------±-----±------- | empno | ename | job | mgr | hiredate | sal | comm | deptno | ±-------±------±--------±-----±--------------------±--------±-----±------- | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007782 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | ±-------±------±--------±-----±--------------------±--------±-----±------- 3 rows in set (0.00 sec) // 3.進行合并 mysql select * from emp where sal2500 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 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | ±------±------±----------±-----±--------------------±--------±-----±------- 6 rows in set (0.00 sec) #### 4.5.2 union all操作符用于取得兩個結果集的并集。當使用該操作符時不會去掉結果集中的重復行。案例將工資大于25000或職位是MANAGER的人找出來// 1.查出工資大于2500的 mysql select * from emp where sal2500; ±-------±------±----------±-----±--------------------±--------±-----±------- | empno | ename | job | mgr | hiredate | sal | comm | deptno | ±-------±------±----------±-----±--------------------±--------±-----±------- | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | ±-------±------±----------±-----±--------------------±--------±-----±------- 5 rows in set (0.00 sec) // 2.查出jobMANAGER的 mysql select * from emp where job‘MANAGER’; ±-------±------±--------±-----±--------------------±--------±-----±------- | empno | ename | job | mgr | hiredate | sal | comm | deptno | ±-------±------±--------±-----±--------------------±--------±-----±------- | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007782 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | ±-------±------±--------±-----±--------------------±--------±-----±------- 3 rows in set (0.01 sec) // 3.進行合并 mysql select * from emp where sal2500 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 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | ±------±------±----------±-----±--------------------±--------±-----±------- 8 rows in set (0.00 sec)