MySql中表的復(fù)合查詢實現(xiàn)示例
更新時間：2024年11月01日 09:26:57 作者：桀桀桀桀桀桀
在MySQL中,復(fù)合查詢是對多張表進(jìn)行查詢的操作,包括多表查詢、自連接、子查詢等,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
復(fù)合查詢
? 本篇開始將介紹在MySql中進(jìn)行復(fù)合查詢的操作。平時在開發(fā)過程中只對一張表進(jìn)行查詢的操作是遠(yuǎn)遠(yuǎn)不夠的，更多的都是多張表一起查詢，所以本篇將介紹多張表中的復(fù)合查詢，主要介紹多表查詢、自連接以及子查詢。
導(dǎo)入數(shù)據(jù)庫
? 本篇中使用的數(shù)據(jù)庫如下，若想要與本篇進(jìn)行相同的操作，可以先導(dǎo)入與本篇相同的數(shù)據(jù)庫，按步驟：
- 在某目錄下創(chuàng)建sql文件：soctt_data.sql
- 將如下內(nèi)容復(fù)制到soctt_data.sql文件中
- 然后登陸進(jìn)mysql，執(zhí)行命令：source 某目錄/scott_data.sql
mysql> source /home/jzhong/mysql/scott_data.sql Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | scott | | sys | | testdb | +--------------------+ 6 rows in set (0.00 sec) -- 切換使用數(shù)據(jù)庫 mysql> use scott Database changed mysql> show tables; +-----------------+ | Tables_in_scott | +-----------------+ | dept | | emp | | salgrade | +-----------------+ 3 rows in set (0.00 sec)
? soctt_data.sql:
DROP database IF EXISTS `scott`; CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE `scott`; DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部門編號', `dname` varchar(14) DEFAULT NULL COMMENT '部門名稱', `loc` varchar(13) DEFAULT NULL COMMENT '部門所在地點' ); DROP TABLE IF EXISTS `emp`; CREATE TABLE `emp` ( `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇員編號', `ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名', `job` varchar(9) DEFAULT NULL COMMENT '雇員職位', `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領(lǐng)導(dǎo)編號', `hiredate` datetime DEFAULT NULL COMMENT '雇傭時間', `sal` decimal(7,2) DEFAULT NULL COMMENT '工資月薪', `comm` decimal(7,2) DEFAULT NULL COMMENT '獎金', `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部門編號' ); DROP TABLE IF EXISTS `salgrade`; CREATE TABLE `salgrade` ( `grade` int(11) DEFAULT NULL COMMENT '等級', `losal` int(11) DEFAULT NULL COMMENT '此等級最低工資', `hisal` int(11) DEFAULT NULL COMMENT '此等級最高工資' ); insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK'); insert into dept (deptno, dname, loc) values (20, 'RESEARCH', 'DALLAS'); insert into dept (deptno, dname, loc) values (30, 'SALES', 'CHICAGO'); insert into dept (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON'); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10); insert into salgrade (grade, losal, hisal) values (1, 700, 1200); insert into salgrade (grade, losal, hisal) values (2, 1201, 1400); insert into salgrade (grade, losal, hisal) values (3, 1401, 2000); insert into salgrade (grade, losal, hisal) values (4, 2001, 3000); insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);
表的基礎(chǔ)查詢
? 先對以上數(shù)據(jù)庫進(jìn)行一些基本查詢，如下：
? 查詢工資高于500或者崗位為MANAGER的雇員，同時還需要滿足他們的姓名首字母為大寫的J，如下：
-- 使用模糊匹配 mysql> select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%'; -- 使用字符串函數(shù)進(jìn)行匹配 mysql> mysql> select * from emp where (sal > 500 or job = 'MANAGER') and substring(ename, 1, 1) = 'J';
? 按照部門號升序而雇員的工資降序排序：
mysql> select * from emp order by deptno asc, sal desc;
? 使用年薪進(jìn)行降序排序，如下：
mysql> select ename, sal * 12 + ifnull(comm, 0) 年薪 from emp order by 年薪 desc;
? 顯示工資最高的員工的名字和工作崗位：
mysql> select ename, job from emp where
-> sal = (select max(sal) from emp);
? 顯示工資高于平均工資的員工信息：
mysql> select * from emp where sal > (select avg(sal) from emp);
? 顯示每個部門的平均工資和最高工資：
mysql> select deptno, format(max(sal), 2), format(avg(sal), 2) from emp group by deptno;
? 顯示平均工資低于2000的部門號和他的平均工資：
mysql> select deptno, avg(sal) from emp group by deptno having avg(sal) < 2000;
? 顯示每種崗位雇員的總數(shù)以及平均工資：
mysql> select deptno, avg(sal), count(*) from emp group by deptno;
多表查詢
? 實際的開發(fā)場景中數(shù)據(jù)往往來自不同的表，所以需要多表查詢，多表查詢的核心思想：先選定來源的數(shù)據(jù)有哪些表，然后對表格進(jìn)行笛卡爾積連接（從第一張表中取出一條記錄，和第二個表中所有記錄進(jìn)行組合，接著從第一張表中取出第二條數(shù)據(jù)，以此類推不加過濾），形成一張表格，然后在這一張表格中進(jìn)行查詢，如下為emp和dept兩個表的笛卡爾積：
? 進(jìn)行多表查詢其實就是將多表進(jìn)行笛卡爾積連接，然后進(jìn)行在單表中查詢，所以多表查詢的本質(zhì)也是單表查詢，只不過通常笛卡爾積連接形成的單表是中間生成的表。對于以上生成的連接表數(shù)據(jù)太過冗余，我們通常需要找的是兩個表中有關(guān)聯(lián)的數(shù)據(jù)，比如emp和dept中的deptno就存在相同的字段，所以可以將以上表格給過濾，如下：
? 假若我們需要查詢顯示部門號為10的部門名，員工名和工資，如下：
-- 先將表格使用笛卡爾積連接起來，然后篩選出有效數(shù)據(jù)，接著篩選部門號為10的數(shù)據(jù) mysql> select emp.deptno, dept.loc, emp.ename, emp.sal from emp, dept where dept.deptno = emp.deptno and dept.deptno = 10;
? 顯示各個員工的姓名，工資，以及工資級別：
mysql> select ename, sal, grade from salgrade, emp where sal between losal and hisal;
自連接
? 自連接同樣也是多表查詢中較為特殊的一種，因為需要使用笛卡爾積連接起來的是兩個相同的表。
? 查詢員工FORD的上級領(lǐng)導(dǎo)的編號和姓名，如下：
-- 多表查詢思路 select t2.ename, t2.empno from emp as t1, emp as t2 where t1.ename = 'FORD' and t2.empno = t1.mgr; -- 子查詢思路 mysql> select ename, empno from emp where empno in (select mgr from emp where ename = 'FORD');
子查詢
? 子查詢值得是嵌入在其他sql語句中的select語句，也叫嵌套查詢
單行子查詢
? 單行子查詢就是返回一行記錄的子查詢。
? 查詢與SMITH同一部門的員工，如下：
mysql> select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
多行子查詢
? 多行子查詢就是返回多行記錄的子查詢。
? in關(guān)鍵字：查詢和10號部門的工作崗位相同的雇員的名字，崗位，工資，部門號，但是不包含10號自己的，如下：
mysql> select ename, job, sal, deptno from emp
-> where job in (select job from emp where deptno = 10) and deptno!=10;
? all關(guān)鍵字：查詢工資比部門30的所有員工的工資的工資搞的員工的姓名，工資和部門號，如下：
mysql> select ename, sal, deptno from emp where sal > all(select sal from emp where deptno = 30); -- 使用max聚合函數(shù) mysql> select ename, sal, deptno from emp where sal > (select max(sal) from emp where deptno = 30);
? any關(guān)鍵字：查詢工資比部門30的任意員工的工資高（高于最低工資）的員工的姓名、工資和部門號（包含自己的部門），如下：
mysql> select ename, sal, deptno from emp where sal > any(select sal from emp where deptno = 30);
多列子查詢
? 上文中的單列子查詢和多列子查詢都是返回的單列多行數(shù)據(jù)，針對的是多列，而多列子查詢則是指返回多個列數(shù)據(jù)的子查詢語句，如下：
? 查詢和SMITH的部門和崗位完全相同的所有雇員，不包含SMITH本人。
mysql> select ename from emp where ename != 'SMITH' and (deptno, job) = (select deptno, job from emp where ename = 'SMITH');
from中使用子查詢
? 通常我們在from子句的后面都是直接跟的表名，但是即使是select出來的子句也生成了一個暫時的表，我們只需要將這個暫時的表給加個別名就可以使用了，如下：
? 查詢每個高于自己部門平均工資的員工的姓名、部門、工資、平均工資，如下：
mysql> select ename, deptno, sal, myavg from emp, (select avg(sal) as myavg, deptno as dt from emp group by deptno) as tmp where emp.deptno = tmp.dt and emp.sal > tmp.myavg;
? 查找每個部門工資最高的人的姓名、工資、部門、最高工資，如下：
mysql> select ename, sal, deptno, mymax from emp, (select max(sal) mymax, deptno dt from emp group by deptno) as tmp where emp.deptno = tmp.dt and emp.sal = tmp.mymax;
? 顯示每個部門的信息（部門名、編號、地址）和人員數(shù)量，如下：
-- 子查詢 mysql> select dept.deptno, dept.dname, dept.loc, mycnt from dept, (select count(*) mycnt, deptno dt from emp group by deptno) as tmp where tmp.dt = dept.deptno; -- 多表 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;
合并查詢
? 為了合并多個select的執(zhí)行結(jié)果，可以使用集合操作符union，union all。
union
? 改操作符用于取得兩個結(jié)果集的并集，當(dāng)使用該操作符時，會自動去掉結(jié)果集中的重復(fù)行。
? 查詢工資大于2500或者職位為MANAGER的人找出來。
mysql> select * from emp where sal > 2500 union select * from emp where job = 'MANAGER';
union all
? 該操作用于取得兩個結(jié)果的并集，當(dāng)使用該操作時，不會去掉結(jié)果中的重復(fù)行，如下：
? 將工資大于2500或職位是MANAGER的人找出來，如下：
mysql> select * from emp where sal > 2500 union all select * from emp where job = 'MANAGER';
到此這篇關(guān)于MySql中表的復(fù)合查詢實現(xiàn)示例的文章就介紹到這了,更多相關(guān)MySql表復(fù)合查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MYSQL表，文章主要圍繞MySQL表的相關(guān)資料如創(chuàng)建表、刪除表、修改表、等展開內(nèi)容,需要的朋友可以參考一下，希望對你有所幫助2021-11-11
這篇文章主要為大家詳細(xì)介紹了mysql 5.7.11 安裝配置教程，感興趣的小伙伴們可以參考一下2016-06-06
大家好，本篇文章主要講的是MySQL導(dǎo)入與導(dǎo)出備份詳解，感興趣的同學(xué)趕快來看一看吧，對你有幫助的話記得收藏一下，方便下次瀏覽2021-12-12
MySQL中查詢某一天, 某一月, 某一年的數(shù)據(jù)代碼詳解
本文通過實例代碼給大家介紹了MySQL中, 如何查詢某一天, 某一月, 某一年的數(shù)據(jù) ,需要的朋友可以參考下2019-06-06
SQL是一種特殊目的的編程語言,是一種數(shù)據(jù)庫查詢和程序設(shè)計語言,用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng),這篇文章主要給大家介紹了SQL語法的相關(guān)資料,需要的朋友可以參考下2021-07-07
Mysql學(xué)習(xí)之?dāng)?shù)據(jù)庫檢索語句DQL大全小白篇
這篇文章主要介紹了Mysql數(shù)據(jù)庫檢索語句DQL大全，本文適合數(shù)據(jù)庫初學(xué)者，小白也能看懂，有需要的朋友可以收藏閱讀，希望可以有所幫助2021-09-09
MySQL中KEY、PRIMARY KEY、UNIQUE KEY、INDEX 的區(qū)別
本文給大家分享的是mysql索引中的KEY、PRIMARY KEY、UNIQUE KEY、INDEX 的區(qū)別，即主鍵索引，唯一索引和普通索引的區(qū)別，希望大家能夠喜歡2017-07-07
使用bin-log日志還原數(shù)據(jù)庫的例子，供大家學(xué)習(xí)參考2013-02-02
MySQL Workbench 是一款強(qiáng)大的數(shù)據(jù)庫設(shè)計和管理工具,本文主要介紹了MySQL Workbench 安裝教程,文中通過圖文介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2025-03-03
這篇文章主要介紹了MySQL大庫搭建主從的一種思路分享，幫助大家更好的理解和學(xué)習(xí)使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2021-03-03