聊城做網(wǎng)站信息seo搜索規(guī)則
實(shí)際開(kāi)發(fā)中往往數(shù)據(jù)來(lái)自不同的表，所以需要多表查詢(xún)。本節(jié)我們用一個(gè)簡(jiǎn)單的公司管理系統(tǒng)，有三張表EMP,DEPT,SALGRADE 來(lái)演示如何進(jìn)行多表查詢(xún)。表結(jié)構(gòu)的代碼以及插入的數(shù)據(jù)如下：
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);
可以看到有三張表部門(mén)表dept，員工表emp，工資等級(jí)表salgrade。
多表查詢(xún)
在MySQL中，多表查詢(xún)是指在一個(gè)查詢(xún)中涉及到兩個(gè)或更多的表。這種查詢(xún)通常用于從多個(gè)相關(guān)的數(shù)據(jù)表中檢索數(shù)據(jù)。多表查詢(xún)的關(guān)鍵在于使用連接條件來(lái)關(guān)聯(lián)不同表中的數(shù)據(jù)。
內(nèi)連接
內(nèi)連接（Inner Join）是一種SQL操作，用于從兩個(gè)或多個(gè)表中檢索相關(guān)數(shù)據(jù)。內(nèi)連接通過(guò)在連接條件滿(mǎn)足的情況下返回兩個(gè)表中匹配的行。內(nèi)連接僅返回那些在連接條件下有匹配的行，其他不匹配的行將被排除。
select 列名1,列名2 from 表名1 inner join 表名2 on 條件;以上為內(nèi)連接的標(biāo)準(zhǔn)寫(xiě)法，還有以下寫(xiě)法和內(nèi)連接得到的效果一致，更簡(jiǎn)單。
select 列名1,列名2... from 表名1,表名2... where 條件; 顯示雇員名、雇員工資以及所在部門(mén)的名字
? 因?yàn)樯厦娴臄?shù)據(jù)來(lái)自 EMP 和 DEPT 表，因此要聯(lián)合查詢(xún)
寫(xiě)法一
select emp.ename,emp.sal,dept.dname from emp inner join dept on emp.deptno=dept.deptno;寫(xiě)法二
select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;
左外連接
select 列名1,列名2... from 表名1 left join 表名2 on 條件;左外連接（Left?Join）是一種SQL操作，用于從兩個(gè)或多個(gè)表中檢索相關(guān)數(shù)據(jù)。左外連接返回左表中的所有記錄，以及右表中與左表匹配的記錄，如果右表中沒(méi)有匹配項(xiàng)，左表的記錄仍然會(huì)被返回，但右表的字段會(huì)顯示為 NULL。
現(xiàn)在創(chuàng)建兩張表學(xué)生表stu和成績(jī)表exam，并插入數(shù)據(jù)，其代碼如下：
create table stu (id int, name varchar(30)); -- 學(xué)生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成績(jī)表
insert into exam values(1, 56),(2,76),(11, 8); 查詢(xún)所有學(xué)生的成績(jī)，如果這個(gè)學(xué)生沒(méi)有成績(jī)，也要將學(xué)生的個(gè)人信息顯示出來(lái)
select * from stu left join exam on stu.id=exam.id;
右外連接
右外連接（Right Join）是一種SQL操作，用于從兩個(gè)或多個(gè)表中檢索相關(guān)數(shù)據(jù)。右外連接返回右表中的所有記錄，以及左表中與右表匹配的記錄。如果左表中沒(méi)有匹配項(xiàng)，左表的字段會(huì)顯示為 NULL。
select 列名1,列名2... from 表名1 right join 表名2 on 條件; 對(duì) stu 表和 exam 表聯(lián)合查詢(xún)，把所有的成績(jī)都顯示出來(lái)，即使這個(gè)成績(jī)沒(méi)有學(xué)生與它對(duì)應(yīng)，也要
顯示出來(lái)
select * from stu right join exam on stu.id=exam.id;
自連接
自連接是一種特殊的多表查詢(xún)，可以理解為自己與自己之間進(jìn)行多表查詢(xún)。
select 列名1,列名2... from 表名 as 別名1, 表名 as 別名2;
顯示上級(jí)領(lǐng)導(dǎo)的編號(hào)和姓名（mgr 是員工領(lǐng)導(dǎo)的編號(hào) --empno ）
select distinct leader.empno,leader.ename from emp leader,emp worker where worker.mgr=leader.empno;
子查詢(xún)
子查詢(xún)（Sub Query）是指在一個(gè)查詢(xún)語(yǔ)句中嵌套另一個(gè)查詢(xún)語(yǔ)句，其中內(nèi)層查詢(xún)的結(jié)果作為外層查詢(xún)的條件或數(shù)據(jù)來(lái)源。子查詢(xún)也被稱(chēng)為嵌套查詢(xún)或內(nèi)層查詢(xún)，而包含子查詢(xún)的查詢(xún)語(yǔ)句被稱(chēng)為外層查詢(xún)或父查詢(xún)。
單行子查詢(xún)
select ... from ... where 列名 = (select ... from ...);
顯示 SMITH 同一部門(mén)的員工
select * from dept=(select deptno from emp where ename='smith');
多行子查詢(xún)
select ... from ... where 列名 in (select ... from ...);
select ... from ... where 列名 比較操作符 all(select ... from ...);
select ... from ... where 列名 比較操作符 any(select ... from ...);
在單行子查詢(xún)中，子查詢(xún)的結(jié)果是單行數(shù)據(jù)，所以能進(jìn)行=。如果是多行查詢(xún)，那么此時(shí)就不能進(jìn)行判等，而是使用in，all，any這三個(gè)關(guān)鍵字，來(lái)進(jìn)行范圍判斷。
in：判斷是否是多行數(shù)據(jù)中的一個(gè)
in關(guān)鍵字：查詢(xún)和10號(hào)部門(mén)的工作崗位相同的雇員的名字，崗位，工資，部門(mén)號(hào)，但是不包含10自
己的
select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10) and deptno!=10;
all：判斷是否所有數(shù)據(jù)都滿(mǎn)足條件
all關(guān)鍵字：顯示工資比部門(mén)30的所有員工的工資高的員工的姓名、工資和部門(mén)號(hào)
select ename,job,sal,deptno from emp where sal > all (select sal from emp where deptno=30);
any：判斷是否有數(shù)據(jù)滿(mǎn)足條件
any關(guān)鍵字：顯示工資比部門(mén)30的任意員工的工資高的員工的姓名、工資和部門(mén)號(hào)（包含自己部門(mén)
的員工）
select ename,job,sal,deptno from emp where sal > any (select sal from emp where deptno=30);
多列子查詢(xún)
單行子查詢(xún)是指子查詢(xún)只返回單列，單行數(shù)據(jù)；多行子查詢(xún)是指返回單列多行數(shù)據(jù)，都是針對(duì)單列而言的，而多列子查詢(xún)則是指查詢(xún)返回多個(gè)列數(shù)據(jù)的子查詢(xún)語(yǔ)句。
select ... from ...
where (列1, 列2...) 邏輯運(yùn)算符 (select 列1, 列2... from ...);
查詢(xún)和SMITH的部門(mén)和崗位完全相同的所有雇員，不含SMITH本人
select ename from emp where (deptno,job)=(select deptno,job from emp where ename='smith') and ename!='smith';
在from子句中使用子查詢(xún)
子查詢(xún)語(yǔ)句出現(xiàn)在 from 子句中。這里要用到數(shù)據(jù)查詢(xún)的技巧，把一個(gè)子查詢(xún)當(dāng)做一個(gè)臨時(shí)表使用。
select ... from (select ... from ...) as 別名 where ...;
1.顯示每個(gè)高于自己部門(mén)平均工資的員工的姓名、部門(mén)、工資、平均工資
select ename,deptno,sal,format(asal,2) from emp,(select deptno de,avg(sal) asal from emp group by deptno) tmp where sal>asal and deptno=de;
2.查找每個(gè)部門(mén)工資最高的人的姓名、工資、部門(mén)、最高工資
select ename,sal,emp.deptno from emp,(select deptno,max(sal) msal from emp group by deptno) tmp where sal=msal and emp.deptno=tmp.deptno;
3.顯示每個(gè)部門(mén)的信息（部門(mén)名，編號(hào)，地址）和人員數(shù)量
合并查詢(xún)
在實(shí)際應(yīng)用中，為了合并多個(gè)select的執(zhí)行結(jié)果，可以使用集合操作符 union，union all。
union
該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行。
select ... from ... union select ... from ...;
將工資大于 2500 或職位是 MANAGER 的人找出來(lái)
select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='manager';
union all
該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，不會(huì)去掉結(jié)果集中的重復(fù)行。
select ... from ... union all select ... from ...;將工資大于2500或職位是MANAGER的人找出來(lái)
select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='manager';