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