MySQL復(fù)合查詢和內(nèi)外連接的操作代碼
更新時(shí)間：2022年09月29日 09:08:27 作者：s_persist
實(shí)際開發(fā)中往往數(shù)據(jù)來自不同的表，所以需要多表查詢，但是可以將多張表做笛卡爾積后的表當(dāng)做是一張表，也就是單表查詢，這篇文章主要介紹了MySQL復(fù)合查詢和內(nèi)外連接,需要的朋友可以參考下
一、復(fù)合查詢
1.1 多表查詢
實(shí)際開發(fā)中往往數(shù)據(jù)來自不同的表，所以需要多表查詢，但是可以將多張表做笛卡爾積后的表當(dāng)做是一張表，也就是單表查詢。
我們用一個(gè)簡單的公司管理系統(tǒng)，有三張表EMP、DEPT、SALGRADE來演示如何進(jìn)行多表查詢。
語法：from 表1, 表2 ...
這樣其實(shí)就是對多張表進(jìn)行笛卡爾積，假設(shè)是兩張表：
- 第一張表的第一行和第二張表的所有行組合。
- 第一張表的第二行和第二張表的所有行組合。…
這樣所有的組合，并不都是有意義的，所有一般都是根據(jù)條件篩選的。
在多表查詢中，有可能多張表有相同的字段，為了便于區(qū)分，可以使用表名.字段名表示某一張表的某一個(gè)字段。
mysql> select dept.dname, emp.ename, emp.sal from emp,dept where dept.deptno = emp.deptno; +------------+--------+---------+ | dname | ename | sal | +------------+--------+---------+ | RESEARCH | SMITH | 800.00 | | SALES | ALLEN | 1600.00 | | SALES | WARD | 1250.00 | | RESEARCH | JONES | 2975.00 | | SALES | MARTIN | 1250.00 | | SALES | BLAKE | 2850.00 | | ACCOUNTING | CLARK | 2450.00 | | RESEARCH | SCOTT | 3000.00 | | ACCOUNTING | KING | 5000.00 | | SALES | TURNER | 1500.00 | | RESEARCH | ADAMS | 1100.00 | | SALES | JAMES | 950.00 | | RESEARCH | FORD | 3000.00 | | ACCOUNTING | MILLER | 1300.00 | +------------+--------+---------+ 14 rows in set (0.00 sec)
1.2 自連接
自連接是對一張表的查詢，但是使用多張表的查詢方式，自己做笛卡爾積。
例如：
mysql> show create table emp \G
*************************** 1. row ***************************
Table: emp
Create Table: 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 '雇傭時(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 '部門編號'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)- 顯示員工FORD的上級領(lǐng)導(dǎo)的編號和姓名（mgr是員工領(lǐng)導(dǎo)的編號）。
- 這是員工表，領(lǐng)導(dǎo)也是屬于員工，所以無法只用一條簡單查找語句對一張表查找兩次，這就需要用到復(fù)合查詢。
- 子查詢 — 即嵌套查詢，使用兩條查詢語句
mysql> select empno, ename from emp where empno = (select mgr from emp where ename = 'FORD'); +--------+-------+ | empno | ename | +--------+-------+ | 007566 | JONES | +--------+-------+ 1 row in set (0.00 sec
使用自連接的兩張表查詢，這里需要對表使用別名
mysql> select leader.empno, leader.ename from emp as worker, emp leader where worker.mgr = leader.empno and worker.ename='FORD'; +--------+-------+ | empno | ename | +--------+-------+ | 007566 | JONES | +--------+-------+ 1 row in set (0.00 sec)
1.3 子查詢
子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢。
1.3.1 單行子查詢
返回一行記錄的子查詢
- 顯示SMITH同一部門的員工
mysql> select deptno, ename from emp where deptno = (select deptno from emp where ename = 'SMITH'); +--------+-------+ | deptno | ename | +--------+-------+ | 20 | SMITH | | 20 | JONES | | 20 | SCOTT | | 20 | ADAMS | | 20 | FORD | +--------+-------+ 5 rows in set (0.00 sec)
1.3.2 多行子查詢
返回多行記錄的子查詢，仍然是一列（一個(gè)字段）。
in關(guān)鍵字（屬于查詢出來的多行中的一行，無法用于比較）：查詢和10號部門的工作崗位相同的雇員的名字，崗位，工資，部門號，但是不包含10自己的
先拆分，找到10號部門的崗位：
mysql> select distinct job from emp where deptno = 10; +-----------+ | job | +-----------+ | MANAGER | | PRESIDENT | | CLERK | +-----------+ 3 rows in set (0.00 sec) -- 然后再復(fù)合 mysql> select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno = 10) and deptno <=> 10; +--------+-----------+---------+--------+ | ename | job | sal | deptno | +--------+-----------+---------+--------+ | CLARK | MANAGER | 2450.00 | 10 | | KING | PRESIDENT | 5000.00 | 10 | | MILLER | CLERK | 1300.00 | 10 | +--------+-----------+---------+--------+ 3 rows in set (0.00 sec)
all關(guān)鍵字（與查詢結(jié)果的所有行比較為真的）：顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號
同樣可以拆分
-- 先找到部門號為30的所有工資 mysql> select sal from emp where deptno = 30; +---------+ | sal | +---------+ | 1600.00 | | 1250.00 | | 1250.00 | | 2850.00 | | 1500.00 | | 950.00 | +---------+ 6 rows in set (0.00 sec) -- 判斷 > 所有行 mysql> select ename, sal, deptno from emp where 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)
any關(guān)鍵字（與查詢結(jié)果的任意一行比較為真的）：顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號（包含自己部門的員工） some是any的別稱很少使用。
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)
1.3.3 多列子查詢
單行子查詢是指子查詢只返回單列，單行數(shù)據(jù)
多行子查詢是指返回單列多行數(shù)據(jù)，都是針對單列而言的，而多列子查詢則是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語句
其實(shí)很簡單，多列也可以比較，和單列一樣。
案例：查詢和SMITH的部門和崗位完全相同的所有雇員，不含SMITH本人
mysql> select ename, deptno, job from emp where (job, deptno) = (select job, deptno from emp where ename = 'SMITH') and ename <=> 'SMITH'; +-------+--------+-------+ | ename | deptno | job | +-------+--------+-------+ | SMITH | 20 | CLERK | +-------+--------+-------+ 1 row in set (0.00 sec)
1.3.4 在from子句中使用子查詢
子查詢語句出現(xiàn)在from子句中。這里要用到數(shù)據(jù)查詢的技巧，把一個(gè)子查詢當(dāng)做一個(gè)臨時(shí)表使用。
顯示每個(gè)高于自己部門平均工資的員工的姓名、部門、工資、平均工資
-- 先找到每個(gè)部門的平均工資 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) -- 然后再用這張表和原本的表做笛卡爾積，多表查詢 mysql> select emp.ename, emp.deptno, emp.sal, format(dept_avg,2) from emp , (select deptno , avg(sal) dept_avg from emp group by deptno) tmp wheree tmp.deptno = emp.deptno and emp.sal > dept_avg; +-------+--------+---------+--------------------+ | ename | deptno | sal | format(dept_avg,2) | +-------+--------+---------+--------------------+ | ALLEN | 30 | 1600.00 | 1,566.67 | | JONES | 20 | 2975.00 | 2,175.00 | | BLAKE | 30 | 2850.00 | 1,566.67 | | SCOTT | 20 | 3000.00 | 2,175.00 | | KING | 10 | 5000.00 | 2,916.67 | | FORD | 20 | 3000.00 | 2,175.00 | +-------+--------+---------+--------------------+ 6 rows in set (0.00 sec)
顯示每個(gè)部門的信息（部門名，編號，地址）和人員數(shù)量
對于有未知的，聚合的信息（人員數(shù)量），先將它求出來
-- 首先拿到相關(guān)表的信息 mysql> select * from dept limit 3; +--------+------------+----------+ | deptno | dname | loc | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | +--------+------------+----------+ 3 rows in set (0.00 sec) mysql> select * from emp limit 3; +--------+-------+----------+------+---------------------+---------+--------+--------+ | 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 | +--------+-------+----------+------+---------------------+---------+--------+--------+ 3 rows in set (0.01 sec)
暴力使用多表查詢
注意：和聚合函數(shù)一起顯示的字段，需要被分組，因?yàn)榫酆虾瘮?shù)對于某一個(gè)字段是不清楚的，比如count(*)，它是統(tǒng)計(jì)總數(shù)，并不對應(yīng)某一個(gè)deptno,ename這些字段
mysql> select dept.dname, dept.deptno, dept.loc, count(*) from dept, emp where dept.deptno = emp.deptno group by deptno; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.dept.dname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ---- 注意：和聚合函數(shù)一起顯示的字段，需要被分組，因?yàn)榫酆虾瘮?shù)對于某一個(gè)字段是不清楚的，比如count(*)，它是統(tǒng)計(jì)總數(shù)，并不對應(yīng)某一個(gè)deptno,ename這些字段 mysql> select dept.dname, dept.deptno, dept.loc, count(*) from dept, emp where dept.deptno = emp.deptno group by 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.00 sec)
使用子查詢
-- 先子查詢出每個(gè)部門的人員數(shù)量 mysql> select deptno, count(*) from emp group by deptno; +--------+----------+ | deptno | count(*) | +--------+----------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+----------+ 3 rows in set (0.00 sec) -- 再和部門表組合，根據(jù)條件篩選。 mysql> select dept.dname, dept.deptno, dept.loc, dept_cnt from dept, (select deptno, count(*) dept_cnt from emp group by deptno) cnt_table where cnt_table.deptno = dept.deptno; +------------+--------+----------+----------+ | dname | deptno | loc | dept_cnt | +------------+--------+----------+----------+ | ACCOUNTING | 10 | NEW YORK | 3 | | RESEARCH | 20 | DALLAS | 5 | | SALES | 30 | CHICAGO | 6 | +------------+--------+----------+----------+ 3 rows in set (0.00 sec)
1.4 合并查詢
在實(shí)際應(yīng)用中，為了合并多個(gè)select的執(zhí)行結(jié)果，可以使用集合操作符 union，union all
1.4.1 union
該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行。
將工資大于2500或職位是MANAGER的人找出來
崗位是MANAGER的必定有工資大于2500的，這里自動(dòng)去重了
mysql> select ename, sal, job from emp where sal>2500 union select ename, sal, job from emp where job='MANAGER'; +-------+---------+-----------+ | ename | sal | job | +-------+---------+-----------+ | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | SCOTT | 3000.00 | ANALYST | | KING | 5000.00 | PRESIDENT | | FORD | 3000.00 | ANALYST | | CLARK | 2450.00 | MANAGER | +-------+---------+-----------+ 6 rows in set (0.00 sec)
1.4.2 union all
該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，不會(huì)去掉結(jié)果集中的重復(fù)行。
mysql> select ename, sal, job from emp where sal>2500 union all select ename, sal, job from emp where job='MANAGER'; +-------+---------+-----------+ | ename | sal | job | +-------+---------+-----------+ | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | SCOTT | 3000.00 | ANALYST | | KING | 5000.00 | PRESIDENT | | FORD | 3000.00 | ANALYST | | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | CLARK | 2450.00 | MANAGER | +-------+---------+-----------+ 8 rows in set (0.00 sec)
二、內(nèi)外連接
2.1 內(nèi)連接
內(nèi)連接inner join實(shí)際上就是利用where子句對兩種表形成的笛卡兒積進(jìn)行篩選，我們前面學(xué)習(xí)的查詢都是內(nèi)連接，也是在開發(fā)過程中使用的最多的連接查詢。
select 字段 from 表1 inner join 表2 on 連接條件 and 其他條件；
前面使用的都是內(nèi)連接。
on后面是兩張表的連接條件，后可以跟where條件語句查詢表。
-- 用前面的寫法 select ename, dname from emp, dept where emp.deptno=dept.deptno and ename='SMITH'; -- 用標(biāo)準(zhǔn)的內(nèi)連接寫法 select ename, dname from emp inner join dept on emp.deptno=dept.deptno and ename='SMITH';
兩張表連接，并不是所有行都是有效的，所有使用連接條件更加明確。
2.2 外連接
外連接分為左外連接和右外連接
2.2.1 左外連接
如果聯(lián)合查詢，左側(cè)的表完全顯示我們就說是左外連接。
什么叫完全顯示呢？
就是兩張表通過連接條件連接，但是左側(cè)的表有不滿足條件的也要全部顯示，右側(cè)的表顯示為NULL。
語法：
select 字段名 from 表名1 left join 表名2 on 連接條件
創(chuàng)建兩張表
mysql> select * from exam; +------+-------+ | id | grade | +------+-------+ | 1 | 56 | | 2 | 76 | | 11 | 8 | +------+-------+ 3 rows in set (0.00 sec) mysql> select * from stu; +------+------+ | id | name | +------+------+ | 1 | jack | | 2 | tom | | 3 | kity | | 4 | nono | +------+------+ 4 rows in set (0.00 sec)
使用內(nèi)連接
mysql> select * from stu inner join exam on exam.id = stu.id; +------+------+------+-------+ | id | name | id | grade | +------+------+------+-------+ | 1 | jack | 1 | 56 | | 2 | tom | 2 | 76 | +------+------+------+-------+ 2 rows in set (0.00 sec)
使用stu為左表的左外連接
mysql> select * from stu left join exam on exam.id = stu.id; +------+------+------+-------+ | id | name | id | grade | +------+------+------+-------+ | 1 | jack | 1 | 56 | | 2 | tom | 2 | 76 | | 3 | kity | NULL | NULL | | 4 | nono | NULL | NULL | +------+------+------+-------+ 4 rows in set (0.00 sec)
使用exam為左表的左外連接
mysql> select * from exam left join stu on exam.id = stu.id; +------+-------+------+------+ | id | grade | id | name | +------+-------+------+------+ | 1 | 56 | 1 | jack | | 2 | 76 | 2 | tom | | 11 | 8 | NULL | NULL | +------+-------+------+------+ 3 rows in set (0.00 sec)
2.2.1 右外連接
和左外連接對稱，左側(cè)不滿足右側(cè)的填充為NULL。
select 字段 from 表名1 right join 表名2 on 連接條件；
mysql> select * from exam right join stu on exam.id = stu.id; +------+-------+------+------+ | id | grade | id | name | +------+-------+------+------+ | 1 | 56 | 1 | jack | | 2 | 76 | 2 | tom | | NULL | NULL | 3 | kity | | NULL | NULL | 4 | nono | +------+-------+------+------+ 4 rows in set (0.00 sec)
列出部門名稱和這些部門的員工信息，同時(shí)列出沒有員工的部門
顯然要以部門表為主
mysql> select dept.deptno, dept.dname, emp.ename, emp.job from dept left join emp on emp.deptno = dept.deptno; +--------+------------+--------+-----------+ | deptno | dname | ename | job | +--------+------------+--------+-----------+ | 20 | RESEARCH | SMITH | CLERK | | 30 | SALES | ALLEN | SALESMAN | | 30 | SALES | WARD | SALESMAN | | 20 | RESEARCH | JONES | MANAGER | | 30 | SALES | MARTIN | SALESMAN | | 30 | SALES | BLAKE | MANAGER | | 10 | ACCOUNTING | CLARK | MANAGER | | 20 | RESEARCH | SCOTT | ANALYST | | 10 | ACCOUNTING | KING | PRESIDENT | | 30 | SALES | TURNER | SALESMAN | | 20 | RESEARCH | ADAMS | CLERK | | 30 | SALES | JAMES | CLERK | | 20 | RESEARCH | FORD | ANALYST | | 10 | ACCOUNTING | MILLER | CLERK | | 40 | OPERATIONS | NULL | NULL | +--------+------------+--------+-----------+ 15 rows in set (0.00 sec)
左外連接是以左表為主
右外連接是以右表為主
到此這篇關(guān)于MySQL復(fù)合查詢和內(nèi)外連接的文章就介紹到這了,更多相關(guān)MySQL復(fù)合查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysql數(shù)據(jù)庫row_number函數(shù)舉例介紹
在MySQL中ROW_NUMBER()是一種用來生成行號的功能函數(shù),通常情況下它用于對查詢結(jié)果進(jìn)行編號,以便方便地查看每行的位置,下面這篇文章主要給大家介紹了關(guān)于mysql數(shù)據(jù)庫row_number函數(shù)舉例介紹的相關(guān)資料,需要的朋友可以參考下2024-02-02
mysql中的concat()函數(shù)模糊查詢代替${}問題
這篇文章主要介紹了mysql中的concat()函數(shù)模糊查詢代替${}問題,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-08-08
centos6.4下mysql5.7.18安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了centos6.4下mysql5.7.18安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-07-07
MySQL、Oracle數(shù)據(jù)庫如何查看最大連接數(shù)和當(dāng)前連接數(shù)
在使用MySQL、Oracle數(shù)據(jù)庫時(shí)了解最大連接數(shù)和當(dāng)前數(shù)據(jù)庫連接數(shù)對于優(yōu)化數(shù)據(jù)庫性能和確保系統(tǒng)穩(wěn)定性非常重要,這篇文章主要給大家介紹了關(guān)于MySQL、Oracle數(shù)據(jù)庫如何查看最大連接數(shù)和當(dāng)前連接數(shù)的相關(guān)資料,需要的朋友可以參考下2024-04-04
這篇文章主要給大家介紹了關(guān)于Mac上安裝Mysql的詳細(xì)步驟及配置,文中通過圖文介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2023-02-02
這篇文章主要介紹了mysql跨庫事務(wù)XA操作,結(jié)合實(shí)例形式分析了php+mysql實(shí)現(xiàn)跨庫事務(wù)XA操作具體操作步驟與相關(guān)注意事項(xiàng),需要的朋友可以參考下2019-04-04
MySQL數(shù)據(jù)庫事務(wù)原理及應(yīng)用
MySQL數(shù)據(jù)庫事務(wù)是指一組數(shù)據(jù)庫操作，要么全部執(zhí)行成功，要么全部回滾。事務(wù)可以確保數(shù)據(jù)的一致性和完整性，避免了多個(gè)用戶同時(shí)對同一數(shù)據(jù)進(jìn)行修改所帶來的問題。MySQL通過事務(wù)日志記錄事務(wù)的操作，支持事務(wù)的回滾和提交等操作2023-04-04
這篇文章主要介紹了MySQL如何查詢客戶端連接情況,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-08-08
mysql中GROUP_CONCAT函數(shù)使用技巧及問題詳解
這篇文章主要給大家介紹了關(guān)于mysql中GROUP_CONCAT函數(shù)使用技巧及問題的相關(guān)資料,GROUP_CONCAT是MySQL中的一個(gè)聚合函數(shù),它用于將多行數(shù)據(jù)按照指定的順序連接成一個(gè)字符串并返回結(jié)果,需要的朋友可以參考下2023-11-11
重新restore了mysql到另一臺(tái)機(jī)器上后mysql 編碼問題報(bào)錯(cuò)
重新restore了mysql到另一臺(tái)機(jī)器上，今天新寫了一個(gè)app，發(fā)現(xiàn)在admin界面下一添加漢字就會(huì)報(bào)錯(cuò)2011-12-12