MySQL復(fù)合查詢(xún)的具體使用
更新時(shí)間：2026年03月13日 09:25:32 作者：Oo?Oo?Oo?
本文介紹了MySQL復(fù)合查詢(xún)的基本操作,包括多表查詢(xún)和復(fù)雜條件查詢(xún),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
1. 復(fù)合查詢(xún)
前面所介紹的查詢(xún)都是對(duì) MySQL 一張表進(jìn)行查詢(xún)，但在實(shí)際應(yīng)用中，我們往往需要從多張表中獲取數(shù)據(jù)，這時(shí)就需要使用復(fù)合查詢(xún)
1.1 基本查詢(xún)回顧
- 查詢(xún)工資高于 500 或崗位信息為 “MANAGER” 的雇員，同時(shí)還要滿(mǎn)足他們的姓名首字母為大寫(xiě)的 “J”
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>
- 按照部門(mén)號(hào)升序，雇員工資降序排序整張表
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>
- 使用年薪進(jìn)行降序排序
mysql> select *,sal*12+ifnull(comm,0) as annual_sal from emp order by annual_sal desc; +--------+--------+-----------+------+---------------------+---------+---------+--------+------------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | annual_sal | +--------+--------+-----------+------+---------------------+---------+---------+--------+------------+ | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 60000.00 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 36000.00 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 36000.00 | | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 35700.00 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 34200.00 | | 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 29400.00 | | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 19500.00 | | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 18000.00 | | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 16400.00 | | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 15600.00 | | 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 15500.00 | | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 13200.00 | | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 11400.00 | | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 9600.00 | +--------+--------+-----------+------+---------------------+---------+---------+--------+------------+ 14 rows in set (0.00 sec) mysql>
注：comm 列表示傭金，全稱(chēng) Commission
- 顯示工資最高的員工的姓名和工作崗位
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>
- 顯示工資高于平均工資的員工信息
mysql> select *from emp where sal > (select avg(sal) from emp); +--------+-------+-----------+------+---------------------+---------+------+--------+ | 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 | 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 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | +--------+-------+-----------+------+---------------------+---------+------+--------+ 6 rows in set (0.00 sec) mysql>
- 顯示每個(gè)部門(mén)的平均工資和最高工資
mysql> select deptno,avg(sal) as avg_sal,max(sal) as max_sal from emp group by deptno; +--------+-------------+---------+ | deptno | avg_sal | max_sal | +--------+-------------+---------+ | 20 | 2175.000000 | 3000.00 | | 30 | 1566.666667 | 2850.00 | | 10 | 2916.666667 | 5000.00 | +--------+-------------+---------+ 3 rows in set (0.00 sec) mysql>
- 顯示平均工資低于 2000 的部門(mén)號(hào)和它的平均工資
mysql> select deptno,avg(sal) 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>
- 顯示每種崗位的雇員總數(shù)及其平均工資
mysql> select job,count(*) as total,avg(sal) as avg_sal from emp group by job; +-----------+-------+-------------+ | job | total | avg_sal | +-----------+-------+-------------+ | CLERK | 4 | 1037.500000 | | SALESMAN | 4 | 1400.000000 | | MANAGER | 3 | 2758.333333 | | ANALYST | 2 | 3000.000000 | | PRESIDENT | 1 | 5000.000000 | +-----------+-------+-------------+ 5 rows in set (0.00 sec) mysql>
1.2 多表查詢(xún)
在進(jìn)行多表查詢(xún)的時(shí)候，其實(shí)本質(zhì)上和單表查詢(xún)是一樣的，以下圖中的 emp 和 dept 為例：
- 從第一張表中選出第一條記錄，和第二張表中的所有記錄進(jìn)行組合
- 從第一張表中選取第二條記錄，和第二張表中的所有記錄進(jìn)行組合
- 重復(fù)上述過(guò)程，直到第一張表中的所有記錄都和第二張表的所有記錄組合完畢
- 得到最終的組合記錄表，現(xiàn)在就已經(jīng)將兩張表連接起來(lái)了
- 前面我們說(shuō)過(guò)，“一切皆表” 的思維模式，在多表查詢(xún)這里也同樣適用，將多張表連接起來(lái)，看作是一張表
- 如果有過(guò)濾條件，按照條件對(duì)組合后的表進(jìn)行篩選
- 顯示最終結(jié)果
這個(gè)組合的過(guò)程和數(shù)學(xué)上的笛卡爾積類(lèi)似
- 顯示雇員名，雇員工資以及所在部門(mén)的名字
很明顯，雇員名和雇員工資在 emp 表中，部門(mén)名字在 dept 表中，需要通過(guò)連接查詢(xún)來(lái)實(shí)現(xiàn)
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 ename,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) mysql>
- 顯示部門(mén)號(hào)為 10 的部門(mén)名、雇員名和工資
mysql> select dept.dname,ename,sal from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10; +------------+--------+---------+ | dname | ename | sal | +------------+--------+---------+ | ACCOUNTING | CLARK | 2450.00 | | ACCOUNTING | KING | 5000.00 | | ACCOUNTING | MILLER | 1300.00 | +------------+--------+---------+ 3 rows in set (0.00 sec) mysql>
- 顯示各個(gè)雇員的姓名、工資和工資級(jí)別，并按照級(jí)別升序排序
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.01 sec) mysql> select ename,sal,grade from emp,salgrade where emp.sal between losal and hisal order by grade; +--------+---------+-------+ | ename | sal | grade | +--------+---------+-------+ | SMITH | 800.00 | 1 | | ADAMS | 1100.00 | 1 | | JAMES | 950.00 | 1 | | WARD | 1250.00 | 2 | | MARTIN | 1250.00 | 2 | | MILLER | 1300.00 | 2 | | ALLEN | 1600.00 | 3 | | TURNER | 1500.00 | 3 | | JONES | 2975.00 | 4 | | BLAKE | 2850.00 | 4 | | CLARK | 2450.00 | 4 | | SCOTT | 3000.00 | 4 | | FORD | 3000.00 | 4 | | KING | 5000.00 | 5 | +--------+---------+-------+ 14 rows in set (0.00 sec) mysql>
注：
- 在將多個(gè)表連接的時(shí)候，一定要有連接條件，就是兩張表之間的關(guān)聯(lián)字段
- 如果兩張表都有的字段，要以表名.字段名的方式明確指定，避免歧義
1.3 自連接
自連接是指一張表與其自身進(jìn)行連接查詢(xún)
案例：
- 顯示員工 “FORD” 的上級(jí)領(lǐng)導(dǎo)編號(hào)和姓名
使用子查詢(xún)
mysql> select ename,empno from emp where empno = (select mgr from emp where ename = 'FORD'); +-------+--------+ | ename | empno | +-------+--------+ | JONES | 007566 | +-------+--------+ 1 row in set (0.00 sec) mysql>
使用多表查詢(xún)(自查詢(xún))
mysql> select leader.ename,leader.deptno from emp leader,emp worker where leader.empno = worker.mgr and worker.ename = 'FORD'; +-------+--------+ | ename | deptno | +-------+--------+ | JONES | 20 | +-------+--------+ 1 row in set (0.00 sec) mysql>
在上面的示例中使用自查詢(xún)的時(shí)候，需要注意對(duì)表起別名，因?yàn)橐獏^(qū)分兩張表
mgr 是雇員的上級(jí)領(lǐng)導(dǎo)編號(hào)
empno 是雇員編號(hào)
1.4 子查詢(xún)
子查詢(xún)是指在另一個(gè)查詢(xún)語(yǔ)句的 where 子句中出現(xiàn)的查詢(xún)語(yǔ)句，也叫做嵌套查詢(xún)(前面我們已經(jīng)用過(guò))
1.4.1 單行子查詢(xún)
返回一行記錄的子查詢(xún)
- 顯示 “SMITH” 同一部門(mén)的雇員
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) mysql>
1.4.2 多行子查詢(xún)
返回多行記錄的子查詢(xún)
- 查詢(xún)和 10 號(hào)部門(mén)的工作崗位相同的雇員名字、崗位、工資、部門(mén)號(hào)，但是不包含 10 號(hào)部門(mén)自己的
mysql> select ename,job,sal,deptno from emp where deptno != 10 and job in (select job from emp where deptno = 10); +-------+---------+---------+--------+ | ename | job | sal | deptno | +-------+---------+---------+--------+ | SMITH | CLERK | 800.00 | 20 | | JONES | MANAGER | 2975.00 | 20 | | BLAKE | MANAGER | 2850.00 | 30 | | ADAMS | CLERK | 1100.00 | 20 | | JAMES | CLERK | 950.00 | 30 | +-------+---------+---------+--------+ 5 rows in set (0.00 sec) mysql>
- 顯示工資比部門(mén) 30 的所有員工的工資高的雇員姓名、工資和部門(mén)號(hào)，不包含 30 號(hào)部門(mén)的員工
mysql> select ename,sal,deptno from emp where deptno != 30 and 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) mysql>
- 顯示工資比部門(mén) 30 的任意員工的工資高的雇員姓名、工資和部門(mén)號(hào)，包含 30 號(hào)部門(mén)的員工
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) mysql>
1.4.3 多列子查詢(xún)
單行子查詢(xún)是指子查詢(xún)只返回單列，單行數(shù)據(jù)；多行子查詢(xún)是指返回單列多行數(shù)據(jù)，都是針對(duì)單列而言的，而多列子查詢(xún)是指查詢(xún)返回多個(gè)列數(shù)據(jù)的子查詢(xún)語(yǔ)句
- 查詢(xún)和 “SMITH” 的部門(mén)和崗位完全相同的所有雇員，不包含 “SMITH” 本人
mysql> select * from emp where deptno = (select deptno from emp where ename = 'SMITH') and job = (select 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) mysql>
1.4.4 在 from 子句中使用子查詢(xún)
子查詢(xún)語(yǔ)句出現(xiàn)在 from 子句中，這時(shí)候子查詢(xún)的結(jié)果會(huì)被當(dāng)作一個(gè)臨時(shí)表來(lái)使用，再次體現(xiàn)了 “一切皆表” 的思維模式
- 顯示每個(gè)高于自己部門(mén)平均工資的雇員姓名、部門(mén)、工資和平均工資
mysql> select ename,deptno,sal,dept_avg_sal from emp,
-> (select deptno my_deptno,avg(sal) dept_avg_sal from emp group by deptno) tmp
-> where emp.sal > tmp.dept_avg_sal and emp.deptno = tmp.my_deptno;
+-------+--------+---------+--------------+
| ename | deptno | sal | dept_avg_sal |
+-------+--------+---------+--------------+
| 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 t1emp.ename,t1emp.deptno,t1emp.sal,t2emp.dept_avg_sal from
-> (select ename,deptno,sal from emp) t1emp,
-> (select deptno,avg(sal) dept_avg_sal from emp group by deptno) t2emp
-> where t1emp.sal > t2emp.dept_avg_sal and t1emp.deptno = t2emp.deptno;
+-------+--------+---------+--------------+
| ename | deptno | sal | dept_avg_sal |
+-------+--------+---------+--------------+
| 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>
- 查找每個(gè)部門(mén)工資最高的雇員姓名、工資、部門(mén)和最高工資
mysql> select ename,sal,deptno,dept_max_sal from emp,
-> (select max(sal) dept_max_sal,deptno tdeptno from emp group by deptno) tmp
-> where sal = dept_max_sal and deptno = tdeptno;
+-------+---------+--------+--------------+
| ename | sal | deptno | dept_max_sal |
+-------+---------+--------+--------------+
| 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)
mysql>
- 顯示每個(gè)部門(mén)的信息(部門(mén)名、編號(hào)和地址)和人員數(shù)量
mysql> select dname,dept.deptno,loc,tmp.total from dept, (select count(*) total,deptno from emp group by deptno) tmp where dept.deptno = tmp.deptno; +------------+--------+----------+-------+ | dname | deptno | loc | total | +------------+--------+----------+-------+ | ACCOUNTING | 10 | NEW YORK | 3 | | RESEARCH | 20 | DALLAS | 5 | | SALES | 30 | CHICAGO | 6 | +------------+--------+----------+-------+ 3 rows in set (0.01 sec) mysql>
1.4.5 合并查詢(xún)
在實(shí)際應(yīng)用中，為了合并多個(gè) select 的執(zhí)行結(jié)果，可以使用集合操作符 union，union all
1.4.5.1 union
union 可以獲得兩個(gè)結(jié)果集的并集，會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行
- 查詢(xún)工資大于 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) mysql>
1.4.5.2 union all
union all 可以獲取兩個(gè)結(jié)果集的并集，不會(huì)去掉重復(fù)行
- 查詢(xún)工資大于 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) mysql>
到此這篇關(guān)于MySQL復(fù)合查詢(xún)的具體使用的文章就介紹到這了,更多相關(guān)MySQL復(fù)合查詢(xún)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL定時(shí)任務(wù)EVENT事件的使用方法
本文主要介紹了MySQL定時(shí)任務(wù)EVENT事件的使用方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05
這篇文章主要介紹了CentOS 7中安裝mysql server的步驟分享,需要的朋友可以參考下2015-09-09
mysql中g(shù)roup by與having合用注意事項(xiàng)分享
在mysql中g(shù)roup by分組查詢(xún)我們經(jīng)常會(huì)用到，并且還同時(shí)會(huì)與having合用，下面我介紹group by用法與having合用注意事項(xiàng)，希望此教程對(duì)各位朋友有所幫助2013-10-10
MySQL 索引原理、分類(lèi)、優(yōu)化與面試總結(jié)
文章主要介紹了索引的概念、分類(lèi)及應(yīng)用場(chǎng)景,詳細(xì)講解了索引優(yōu)化（如覆蓋索引優(yōu)化、前綴索引優(yōu)化、主鍵的選擇和最左匹配原則等）,本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),感興趣的朋友一起看看吧2026-04-04
mysql 5.7.17 安裝配置方法圖文教程（windows10）
這篇文章主要為大家分享了mysql 5.7.17 安裝配置方法圖文教程，具有一定的參考價(jià)值，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
Mysql5.7忘記root密碼怎么辦（簡(jiǎn)單且有效方法）
本文給大家分享一個(gè)快速且簡(jiǎn)單的方法來(lái)解決Mysql5.7忘記root密碼問(wèn)題，非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友參考下吧2017-02-02
這篇文章主要介紹了Mysql?using?用法示例代碼詳解，本文通過(guò)三種方法給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-02-02
這里簡(jiǎn)單分享幾個(gè)mysql alter語(yǔ)句用法，方便大家使用2013-02-02
這篇文章主要介紹了MySQL8中的存儲(chǔ)引擎以及文件使用,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-09-09
mysql錯(cuò)誤處理之ERROR 1665 (HY000)
最近一直在mysql的各個(gè)版本直接徘徊，這中間遇到了各種各樣的錯(cuò)誤，將已經(jīng)處理完畢的幾個(gè)錯(cuò)誤整理了一下，分享給大家，這次我們來(lái)看看錯(cuò)誤提示 ERROR 1665 (HY000)2014-07-07