目錄一、什么是多表查詢二、多表查詢的分類2.1 連接查詢2.1.1 內(nèi)連接2.1.2 外連接2.2 子查詢2.2.0 示例2.2.1 單行單列2.2.2 多行單列2.2.3 多行多列2.3 小結(jié)三、案例3.1 數(shù)據(jù)3.2 問題及代碼一、什么是多表查詢

在了解多表查詢之前，我們先看一下什么是單表查詢。

select * from A

這是單表查詢，那什么又是多表查詢呢？

select * from A,B

這是初學(xué)者比較容易理解的多表查詢，就是直接查詢兩張表的字段，當(dāng)然，星號可以改為對應(yīng)的字段名。但是這樣直接查詢兩張表，展示的結(jié)果會出現(xiàn)笛卡爾積。

笛卡爾積：有A，B兩個集合，取A，B所有的組合情況。比如A表有6條數(shù)據(jù)，B表有4條數(shù)據(jù)，那么查詢出來就是24條數(shù)據(jù)。

二、多表查詢的分類

多表查詢總體分為兩類：連接查詢和子查詢。

2.1 連接查詢

連接查詢又分類兩種：內(nèi)連接和外連接。

2.1.1 內(nèi)連接

相當(dāng)于查詢A，B交集數(shù)據(jù)。下面給出它的兩種寫法，掌握一種就夠用了。

-- 隱式內(nèi)連接select 字段列表 from A表,B表 where 條件-- 比如某兩列相同-- 顯示內(nèi)連接select 字段列表 from A表 inner join B表 on 條件-- inner可以省略不寫2.1.2 外連接

左外連接：相當(dāng)于查詢A表所有數(shù)據(jù)和交集部分數(shù)據(jù)。

右外連接：相當(dāng)于查詢B表所有數(shù)據(jù)和交集部分數(shù)據(jù)。

我們常用的就是左外連接，實際上他倆沒什么差別，掌握一種就可以。

-- 左外連接select 字段列表 from A表 left outer join B表 on 條件 -- outer可以省略不寫-- 右外連接select 字段列表 from A表 right outer join B表 on 條件-- outer可以省略不寫2.2 子查詢

查詢中嵌套查詢，稱嵌套查詢?yōu)樽硬樵儭Ｗ硬樵兏鶕?jù)查詢結(jié)果不同，作用不同，總體分為三類：單行單列，多行單列，多行多列。

看到這里可能有點懵逼，子查詢的概念說的好模糊呀，后面給出的三類分別是啥意思呀？別著急，慢慢看，并不難，很容易理解的。

2.2.0 示例

首先來看下什么是子查詢：稱嵌套查詢?yōu)樽硬樵?/p>-- 比如要在一張工資表中查詢比張三工資高的人的信息-- 首先查詢張三的工資select salary from A where name='張三'-- 然后根據(jù)得到的查詢結(jié)果，假設(shè)是3000，再查詢salary比3000大的人的信息select * from A where salary>3000-- 這兩句查詢語句可以合并在一起，我們稱之為子查詢select * from A where salary>(select salary from A where name='張三')

上例看完之后，你應(yīng)該對子查詢有一定的概念了，理解之后再往下看。

2.2.1 單行單列

判斷依據(jù)：作為條件值，使用 > , < , = , != 等進行條件判斷。

上例就是單行單列的查詢。因為嵌套查詢出來的結(jié)果是一個單行單列的表，所以稱為單行單列查詢。嵌套查詢結(jié)果為：

3000

2.2.2 多行單列

判斷依據(jù)：作為條件值，使用 in 等關(guān)鍵字進行條件判斷。

select 字段列表 from 表 where 字段名 in (子查詢)

舉個例子：現(xiàn)有一張員工表(emp)和一張部門表(dept)，員工表中除了姓名、性別等基本信息外，還有對應(yīng)部門的 id 號；部門表中不同的部門對應(yīng)唯一的 id 號。要求查詢“財務(wù)部”和“市場部”所有的員工信息。

-- 一般情況-- 先查詢到財務(wù)部和市場部對應(yīng)的id號,假設(shè)對應(yīng)為3,4select id from dept where name in ('財務(wù)部','市場部')-- 此時查詢結(jié)果是一個多行單列的表-- 然后查詢員工表中對應(yīng)部門id的員工select * from emp where emp.dept_id in (3,4)-- 子查詢select * from emp where emp.dept_id in (select id from dept where name in ('財務(wù)部','市場部'))

多行單列指的就是子查詢（括號里面的查詢）的結(jié)果是多行單列的。嵌套查詢結(jié)果為：

name財務(wù)部市場部2.2.3 多行多列select 字段列表 from (子查詢) where 條件

舉個例子：還是有一張員工表(emp)和一張部門表(dept)，要求查詢出年齡在20歲以上的員工的員工信息和部門信息。

-- 一般情況-- 先查詢20歲以上員工的員工信息select * from emp where age>20-- 此時查詢結(jié)果是一個多行多列的表-- 再查詢對應(yīng)的部門信息select * from emp,dept where emp.dept_id=dept.id-- 子查詢-- 可以將第一次查詢出的表作為一張?zhí)摂M表，在這張?zhí)摂M表與部門表中做篩選，直接將emp改為子查詢即可，要給虛擬表賦一個名字select * from (select * from emp where age>20) as t,dept where t.dept_id=dept.id

多行多列指的是嵌套查詢是多行多列的一張表，我們把這張表稱作“虛擬表”。在本例中，這張?zhí)摂M表的內(nèi)容是20歲以上員工的信息，假設(shè)是下面這樣：

idnameagedept0001

張三

25財務(wù)部0002李四31銷售部0003王五26財務(wù)部2.3 小結(jié)

這一小部分的內(nèi)容看完之后，應(yīng)該是能基本清楚“子查詢是什么”，“什么是虛擬表”，“子查詢的三種不同類型區(qū)別是什么”等問題（講解的內(nèi)容在代碼注釋里面，沒在正文）。如果不清楚，那是我的問題，沒講清楚，歡迎留言批評指正。

三、案例

本例給出四張表，解決相關(guān)問題，鞏固子查詢的知識。四張表分別是：

部門表：部門id，部門名稱，部門所在地；

職務(wù)表：職務(wù)名稱，職務(wù)描述；

員工表：員工id，員工姓名，職務(wù)id，上級領(lǐng)導(dǎo)，入職日期，工資，獎金，所在部門編號；

工資等級表：級別，最低工資，最高工資。

3.1 數(shù)據(jù)DROP TABLE IF EXISTS emp;DROP TABLE IF EXISTS dept;DROP TABLE IF EXISTS job;DROP TABLE IF EXISTS salarygrade;SELECT * FROM empSELECT * FROM deptSELECT * FROM jobSELECT * FROM salarygrade-- 部門表CREATE TABLE dept ( id INT PRIMARY KEY PRIMARY KEY, -- 部門id dname VARCHAR(50), -- 部門名稱 loc VARCHAR(50) -- 部門所在地);-- 職務(wù)表，職務(wù)名稱，職務(wù)描述CREATE TABLE job ( id INT PRIMARY KEY, jname VARCHAR(20), description VARCHAR(50));-- 員工表CREATE TABLE emp ( id INT PRIMARY KEY, -- 員工id ename VARCHAR(50), -- 員工姓名 job_id INT, -- 職務(wù)id mgr INT , -- 上級領(lǐng)導(dǎo) joindate DATE, -- 入職日期 salary DECIMAL(7,2), -- 工資 bonus DECIMAL(7,2), -- 獎金 dept_id INT, -- 所在部門編號 CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id), CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id));-- 工資等級表CREATE TABLE salarygrade ( grade INT PRIMARY KEY, -- 級別 losalary INT, -- 最低工資 hisalary INT -- 最高工資);#解決編碼問題ALTER TABLE dept DEFAULT CHARACTER SET UTF8;ALTER TABLE dept CHANGE dname dname VARCHAR(20) CHARACTER SET UTF8;ALTER TABLE dept CHANGE loc loc VARCHAR(50) CHARACTER SET UTF8;-- 添加4個部門INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'),(20,'學(xué)工部','上海'),(30,'銷售部','廣州'),(40,'財務(wù)部','深圳');#解決編碼問題ALTER TABLE job DEFAULT CHARACTER SET UTF8;ALTER TABLE job CHANGE jname jname VARCHAR(50) CHARACTER SET UTF8;ALTER TABLE job CHANGE description description VARCHAR(50) CHARACTER SET UTF8;-- 添加4個職務(wù)INSERT INTO job (id, jname, description) VALUES(1, '董事長', '管理整個公司，接單'),(2, '經(jīng)理', '管理部門員工'),(3, '銷售員', '向客人推銷產(chǎn)品'),(4, '文員', '使用辦公軟件');#解決編碼問題ALTER TABLE emp DEFAULT CHARACTER SET UTF8;ALTER TABLE emp CHANGE ename ename VARCHAR(50) CHARACTER SET UTF8;-- 添加員工INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1001,'孫悟空',4,1004,'2000-12-17','8000.00',NULL,20),(1002,'盧俊義',3,1006,'2001-02-20','16000.00','3000.00',30),(1003,'林沖',3,1006,'2001-02-22','12500.00','5000.00',30),(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),(1007,'劉備',2,1009,'2001-09-01','24500.00',NULL,10),(1008,'豬八戒',4,1004,'2007-04-19','30000.00',NULL,20),(1009,'羅貫中',1,NULL,'2001-11-17','50000.00',NULL,10),(1010,'吳用',3,1006,'2001-09-08','15000.00','0.00',30),(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),(1013,'小白龍',4,1004,'2001-12-03','30000.00',NULL,20),(1014,'關(guān)羽',4,1007,'2002-01-23','13000.00',NULL,10);-- 添加5個工資等級INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (1,7000,12000),(2,12010,14000),(3,14010,20000),(4,20010,30000),(5,30010,99990);3.2 問題及代碼

1.查詢所有員工信息。查詢員工編號，員工姓名，工資，職務(wù)名稱，職務(wù)描述。

員工編號、員工姓名、工資都在emp表中，職務(wù)名稱、職務(wù)描述都在job表中，所以從這兩個表中查詢對應(yīng)字段；并且加上約束條件：讓員工的職務(wù)id（emp.job_id）和部門id（job.id）相等即可。

SELECT e.id,e.ename,e.salary,j.jname,j.description FROM emp e,job j WHERE e.job_id=j.id

2.查詢員工編號，員工姓名，工資，職務(wù)名稱，職務(wù)描述，部門名稱，部門位置。

分析同上，部門名稱和部門位置在dept表中，只需加上響應(yīng)的字段和約束條件即可。

SELECT e.id,e.ename,e.salary,j.jname,j.description,d.dname,d.loc FROM emp e,job j,dept d WHERE e.job_id=j.id AND e.dept_id=d.id

3.查詢員工姓名，工資，工資等級。

SELECT * FROM emp e,salarygrade s WHERE e.salary>=s.losalary AND e.salary<=s.hisalary

4.查詢出部門編號、部門名稱、部門位置、部門人數(shù)。

部門編號、部門名稱、部門位置都來自于dept表；部門人數(shù)可以根據(jù)emp.dept_id分組，然后使用count(*)計算得出；然后進行子查詢，讓部門表和分組后的表進行內(nèi)連接。

SELECT * FROM dept d,(SELECT dept_id,COUNT(*) FROM emp GROUP BY emp.dept_id) t WHERE t.dept_id=d.id

到此這篇關(guān)于Javaweb MySQL中的多表查詢的文章就介紹到這了,更多相關(guān)mysql多表查詢內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！