MySql中表的復(fù)合查詢(xún)實(shí)現(xiàn)示例

更新時(shí)間：2024年11月01日 09:26:57 作者：桀桀桀桀桀桀

在MySQL中,復(fù)合查詢(xún)是對(duì)多張表進(jìn)行查詢(xún)的操作,包括多表查詢(xún)、自連接、子查詢(xún)等,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

復(fù)合查詢(xún)

? 本篇開(kāi)始將介紹在MySql中進(jìn)行復(fù)合查詢(xún)的操作。平時(shí)在開(kāi)發(fā)過(guò)程中只對(duì)一張表進(jìn)行查詢(xún)的操作是遠(yuǎn)遠(yuǎn)不夠的，更多的都是多張表一起查詢(xún)，所以本篇將介紹多張表中的復(fù)合查詢(xún)，主要介紹多表查詢(xún)、自連接以及子查詢(xún)。

導(dǎo)入數(shù)據(jù)庫(kù)

? 本篇中使用的數(shù)據(jù)庫(kù)如下，若想要與本篇進(jìn)行相同的操作，可以先導(dǎo)入與本篇相同的數(shù)據(jù)庫(kù)，按步驟：

在某目錄下創(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ù)庫(kù)
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 '部門(mén)編號(hào)',
  `dname` varchar(14) DEFAULT NULL COMMENT '部門(mén)名稱(chēng)',
  `loc` varchar(13) DEFAULT NULL COMMENT '部門(mén)所在地點(diǎn)'
);


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇員編號(hào)',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇員職位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領(lǐng)導(dǎo)編號(hào)',
  `hiredate` datetime DEFAULT NULL COMMENT '雇傭時(shí)間',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工資月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '獎(jiǎng)金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部門(mén)編號(hào)'
);


DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等級(jí)',
  `losal` int(11) DEFAULT NULL COMMENT '此等級(jí)最低工資',
  `hisal` int(11) DEFAULT NULL COMMENT '此等級(jí)最高工資'
);


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ǔ)查詢(xún)

? 先對(duì)以上數(shù)據(jù)庫(kù)進(jìn)行一些基本查詢(xún)，如下：

? 查詢(xún)工資高于500或者崗位為MANAGER的雇員，同時(shí)還需要滿足他們的姓名首字母為大寫(xiě)的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';

? 按照部門(mén)號(hào)升序而雇員的工資降序排序：

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);

? 顯示每個(gè)部門(mén)的平均工資和最高工資：

mysql> select deptno, format(max(sal), 2), format(avg(sal), 2) from emp group by deptno;

? 顯示平均工資低于2000的部門(mén)號(hào)和他的平均工資：

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;

多表查詢(xún)

? 實(shí)際的開(kāi)發(fā)場(chǎng)景中數(shù)據(jù)往往來(lái)自不同的表，所以需要多表查詢(xún)，多表查詢(xún)的核心思想：先選定來(lái)源的數(shù)據(jù)有哪些表，然后對(duì)表格進(jìn)行笛卡爾積連接（從第一張表中取出一條記錄，和第二個(gè)表中所有記錄進(jìn)行組合，接著從第一張表中取出第二條數(shù)據(jù)，以此類(lèi)推不加過(guò)濾），形成一張表格，然后在這一張表格中進(jìn)行查詢(xún)，如下為emp和dept兩個(gè)表的笛卡爾積：

在這里插入圖片描述

? 進(jìn)行多表查詢(xún)其實(shí)就是將多表進(jìn)行笛卡爾積連接，然后進(jìn)行在單表中查詢(xún)，所以多表查詢(xún)的本質(zhì)也是單表查詢(xún)，只不過(guò)通常笛卡爾積連接形成的單表是中間生成的表。對(duì)于以上生成的連接表數(shù)據(jù)太過(guò)冗余，我們通常需要找的是兩個(gè)表中有關(guān)聯(lián)的數(shù)據(jù)，比如emp和dept中的deptno就存在相同的字段，所以可以將以上表格給過(guò)濾，如下：

在這里插入圖片描述

? 假若我們需要查詢(xún)顯示部門(mén)號(hào)為10的部門(mén)名，員工名和工資，如下：

-- 先將表格使用笛卡爾積連接起來(lái)，然后篩選出有效數(shù)據(jù)，接著篩選部門(mén)號(hào)為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;

? 顯示各個(gè)員工的姓名，工資，以及工資級(jí)別：

mysql> select ename, sal, grade from salgrade, emp where sal between losal and hisal;

自連接

? 自連接同樣也是多表查詢(xún)中較為特殊的一種，因?yàn)樾枰褂玫芽柗e連接起來(lái)的是兩個(gè)相同的表。

? 查詢(xún)員工FORD的上級(jí)領(lǐng)導(dǎo)的編號(hào)和姓名，如下：

-- 多表查詢(xún)思路
select t2.ename, t2.empno from emp as t1, emp as t2 where t1.ename = 'FORD' and t2.empno = t1.mgr;

-- 子查詢(xún)思路
mysql> select ename, empno from emp where empno in (select mgr from emp where ename = 'FORD');

子查詢(xún)

? 子查詢(xún)值得是嵌入在其他sql語(yǔ)句中的select語(yǔ)句，也叫嵌套查詢(xún)

單行子查詢(xún)

? 單行子查詢(xún)就是返回一行記錄的子查詢(xún)。

? 查詢(xún)與SMITH同一部門(mén)的員工，如下：

mysql> select * from emp where deptno = (select deptno from emp where ename = 'SMITH');

多行子查詢(xún)

? 多行子查詢(xún)就是返回多行記錄的子查詢(xún)。

? in關(guān)鍵字：查詢(xún)和10號(hào)部門(mén)的工作崗位相同的雇員的名字，崗位，工資，部門(mén)號(hào)，但是不包含10號(hào)自己的，如下：

mysql> select ename, job, sal, deptno from emp
    -> where job in (select job from emp where deptno = 10) and deptno!=10;

? all關(guān)鍵字：查詢(xún)工資比部門(mén)30的所有員工的工資的工資搞的員工的姓名，工資和部門(mén)號(hào)，如下：

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)鍵字：查詢(xún)工資比部門(mén)30的任意員工的工資高（高于最低工資）的員工的姓名、工資和部門(mén)號(hào)（包含自己的部門(mén)），如下：

mysql> select ename, sal, deptno from emp where sal > any(select sal from emp where deptno = 30);

多列子查詢(xún)

? 上文中的單列子查詢(xún)和多列子查詢(xún)都是返回的單列多行數(shù)據(jù)，針對(duì)的是多列，而多列子查詢(xún)則是指返回多個(gè)列數(shù)據(jù)的子查詢(xún)語(yǔ)句，如下：

? 查詢(xún)和SMITH的部門(mén)和崗位完全相同的所有雇員，不包含SMITH本人。

mysql> select ename from emp where ename != 'SMITH' and (deptno, job) = (select deptno, job from emp where ename = 'SMITH');

from中使用子查詢(xún)

? 通常我們?cè)趂rom子句的后面都是直接跟的表名，但是即使是select出來(lái)的子句也生成了一個(gè)暫時(shí)的表，我們只需要將這個(gè)暫時(shí)的表給加個(gè)別名就可以使用了，如下：

? 查詢(xún)每個(gè)高于自己部門(mén)平均工資的員工的姓名、部門(mén)、工資、平均工資，如下：

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;

? 查找每個(gè)部門(mén)工資最高的人的姓名、工資、部門(mén)、最高工資，如下：

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;

? 顯示每個(gè)部門(mén)的信息（部門(mén)名、編號(hào)、地址）和人員數(shù)量，如下：

-- 子查詢(xún)
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;

合并查詢(xún)

? 為了合并多個(gè)select的執(zhí)行結(jié)果，可以使用集合操作符union，union all。

union

? 改操作符用于取得兩個(gè)結(jié)果集的并集，當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行。

? 查詢(xún)工資大于2500或者職位為MANAGER的人找出來(lái)。

mysql> select * from emp where sal > 2500 union select * from emp where job = 'MANAGER';

union all

? 該操作用于取得兩個(gè)結(jié)果的并集，當(dāng)使用該操作時(shí)，不會(huì)去掉結(jié)果中的重復(fù)行，如下：

? 將工資大于2500或職位是MANAGER的人找出來(lái)，如下：

mysql> select * from emp where sal > 2500 union all select * from emp where job = 'MANAGER';

到此這篇關(guān)于MySql中表的復(fù)合查詢(xún)實(shí)現(xiàn)示例的文章就介紹到這了,更多相關(guān)MySql表復(fù)合查詢(xún)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片