MySql中表的復合查詢實現(xiàn)示例
更新時間：2024年11月01日 09:26:57 作者：桀桀桀桀桀桀
在MySQL中,復合查詢是對多張表進行查詢的操作,包括多表查詢、自連接、子查詢等,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
復合查詢
? 本篇開始將介紹在MySql中進行復合查詢的操作。平時在開發(fā)過程中只對一張表進行查詢的操作是遠遠不夠的，更多的都是多張表一起查詢，所以本篇將介紹多張表中的復合查詢，主要介紹多表查詢、自連接以及子查詢。
導入數(shù)據(jù)庫
? 本篇中使用的數(shù)據(jù)庫如下，若想要與本篇進行相同的操作，可以先導入與本篇相同的數(shù)據(jù)庫，按步驟：
- 在某目錄下創(chuàng)建sql文件：soctt_data.sql
- 將如下內(nèi)容復制到soctt_data.sql文件中
- 然后登陸進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 '雇員領導編號', `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);
表的基礎查詢
? 先對以上數(shù)據(jù)庫進行一些基本查詢，如下：
? 查詢工資高于500或者崗位為MANAGER的雇員，同時還需要滿足他們的姓名首字母為大寫的J，如下：
-- 使用模糊匹配 mysql> select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%'; -- 使用字符串函數(shù)進行匹配 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;
? 使用年薪進行降序排序，如下：
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ù)有哪些表，然后對表格進行笛卡爾積連接（從第一張表中取出一條記錄，和第二個表中所有記錄進行組合，接著從第一張表中取出第二條數(shù)據(jù)，以此類推不加過濾），形成一張表格，然后在這一張表格中進行查詢，如下為emp和dept兩個表的笛卡爾積：
? 進行多表查詢其實就是將多表進行笛卡爾積連接，然后進行在單表中查詢，所以多表查詢的本質也是單表查詢，只不過通常笛卡爾積連接形成的單表是中間生成的表。對于以上生成的連接表數(shù)據(jù)太過冗余，我們通常需要找的是兩個表中有關聯(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的上級領導的編號和姓名，如下：
-- 多表查詢思路 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關鍵字：查詢和10號部門的工作崗位相同的雇員的名字，崗位，工資，部門號，但是不包含10號自己的，如下：
mysql> select ename, job, sal, deptno from emp
-> where job in (select job from emp where deptno = 10) and deptno!=10;
? all關鍵字：查詢工資比部門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關鍵字：查詢工資比部門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í)行結果，可以使用集合操作符union，union all。
union
? 改操作符用于取得兩個結果集的并集，當使用該操作符時，會自動去掉結果集中的重復行。
? 查詢工資大于2500或者職位為MANAGER的人找出來。
mysql> select * from emp where sal > 2500 union select * from emp where job = 'MANAGER';
union all
? 該操作用于取得兩個結果的并集，當使用該操作時，不會去掉結果中的重復行，如下：
? 將工資大于2500或職位是MANAGER的人找出來，如下：
mysql> select * from emp where sal > 2500 union all select * from emp where job = 'MANAGER';
到此這篇關于MySql中表的復合查詢實現(xiàn)示例的文章就介紹到這了,更多相關MySql表復合查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了MySQL 如何修改root用戶的密碼，幫助大家更好的使用MySQL，不用擔心忘記密碼，感興趣的朋友可以了解下2020-09-09
死鎖的本質是資源競爭,批量插入如果順序不一致很容易導致死鎖,這篇文章主要給大家介紹了關于Mysql?insert是否也會發(fā)生死鎖的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-02-02
這篇文章主要介紹了windows下忘記MySQL密碼的修改方法,需要的朋友可以參考下2014-03-03
這篇文章主要介紹了mysql回表致索引失效案例講解,本篇文章通過簡要的案例,講解了該項技術的了解與使用,以下就是詳細內(nèi)容,需要的朋友可以參考下2021-08-08
這篇文章主要給大家介紹了利用ssh tunnel鏈接mysql服務器的方法，文中介紹的很詳細，需要的朋友可以參考借鑒，下面來一起看看吧。2017-02-02
mysql一條sql查出多個條件不同的sum或count問題
這篇文章主要介紹了mysql一條sql查出多個條件不同的sum或count問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-05-05
這篇文章主要介紹了MySQL約束攻擊的原理和復現(xiàn)方式,具有很好的參考價值,希望對大家有所幫助,2023-11-11
本文主要介紹了MySQL中空值Null和空字符''的具體使用,文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-05-05
mysql中DATE_FORMAT()函數(shù)的具體使用
在MySQL中,DATE_FORMAT()函數(shù)用于將日期/時間類型的值按照指定的格式進行格式化輸出,具有一定的參考價值,感興趣的可以了解一下2024-05-05
mysql安裝好需要優(yōu)化配置一下,打開c:\windows\my.ini文件2011-03-03