mysql5

-- mysql的子查詢(嵌套查詢)

-- 概念：在某些查詢的邏輯中，需要引入另一個(gè)查詢作為條件或者數(shù)據(jù)來源進(jìn)行輔助。

-- 先執(zhí)行子查詢 再執(zhí)行主查詢

-- 查詢與Smith在同一部門工作的其它員工信息

-- 1.查詢出Smith所在的部門

select deptno from emp where ename='Smith'

-- 2.查詢出與Smith在同一部門工作的其他員工信息

-- 分析：select * from emp where deptno=(Smith所在的部門編號(hào)) and ename <>'Smith'? (!=)

select * from emp

where deptno=(select deptno from emp where ename='Smith')

and ename <>'Smith' (!=)(括號(hào)里的是子查詢)

-- 基本的語法

-- SELECT * FROM 表名

-- where deptno=(子查詢)

-- and ename <> 'Smith'

-- 說明：整個(gè)外部這部分SQL被稱之為主查詢(外部查詢)，括號(hào)內(nèi)的查詢稱之為子查詢()

-- 子查詢使用的位置

-- 1.可以出現(xiàn)在from子句(from后面)，可以理解為是一張臨時(shí)表(當(dāng)子查詢出現(xiàn)多行多列的時(shí)候)

-- 2.可以出現(xiàn)在where子句(where后面)

-- 3.也可以出現(xiàn)在having子句中

-- 通常來說比較常用的子查詢一般會(huì)出現(xiàn)在where或from中

-- 1.查詢比Smith月薪高的員工的信息

-- SELECT * FROM emp WHERE sal >(子查詢：查詢出Smith的月薪)

select * from emp where sal>(select sal from emp where ename = 'Smith')

-- 子查詢會(huì)分為：?jiǎn)涡凶硬樵?/p>

-- 1.查詢的結(jié)果為一行一列的數(shù)據(jù)。子查詢就是一個(gè)簡(jiǎn)單的數(shù)據(jù)

-- 例如：(select sal from emp where ename = 'Smith')

-- 2.如果是單行子查詢就可以使用單行運(yùn)算符： > < >= <= = != 進(jìn)行子查詢的比較

-- 查詢與James是同一個(gè)職位同一個(gè)部門的其他的員工的信息

-- 子查詢：1.查詢這個(gè)人的職位， 2.查詢James的部門

SELECT job from emp where ename='James'

SELECT deptno FROM emp WHERE ename='James'

SELECT * FROM emp

where job= (SELECT job from emp where ename='James')

and deptno =? (SELECT deptno FROM emp WHERE ename='James')

and ename <>'James'

-- 說明：子查詢中可以嵌套其他的子查詢，層數(shù)一般限制在32層內(nèi)(嵌套層數(shù)不要過多，會(huì)影響SQL的執(zhí)行效率)

-- 查詢?cè)滦礁哂贏llen 所在部門平均月薪的員工信息

-- 1. 查詢Allen所在部門

-- 2. 查詢Allen所在部門的平均工資

-- 3. 查詢?cè)滦礁哂贏llen所在部門的平均工資

select deptno from emp where ename='Allen'

select AVG( sal) from emp where deptno= (Allen所在部門)

select* from emp where sal>(Allen所在部門的平均工資)

select deptno from emp where ename = 'Allen'

select * from emp

where sal>(select AVG( sal) from emp

where deptno= (select deptno from emp where ename='Allen'))

-- 多行子查詢 ：返回的是多行一列的數(shù)據(jù)

-- 注意：當(dāng)子查詢返回多行記錄的時(shí)候，我們使用? > < >= <= = !=? 這類比較運(yùn)算符是無法查詢的

-- 多行子查詢一定要使用多行比較運(yùn)算符： IN ANY(任何一個(gè)) ALL(所有)? ? age in （20，30）

-- ANY和ALL的較規(guī)則：主要是使用在多行子查詢中進(jìn)行>或<的操作

-- < ANY 表示比最大的小

-- > ANY 表示比最小的大

-- = ANY 相當(dāng)于in

-- < ALL 表示比最小的小

-- > ALL 表示比最大的大

-- 查詢是經(jīng)理的員工信息

select* from emp where? empno? in (SELECT DISTINCT mgr FROM emp where mgr is not null)

SELECT DISTINCT mgr FROM emp where mgr is not null?

-- DISTINCT 去除一樣相同的值 這里表示經(jīng)理幾個(gè)人有同一個(gè)經(jīng)理會(huì)顯示幾個(gè)相同的經(jīng)理名

-- is not null去掉空值 這里表示老板沒有經(jīng)理 值為空 ，所以要設(shè)置為非空

-- 查詢比20號(hào)部門所有人的月薪都要高的信息

-- 1.查詢20號(hào)部門月薪最高的

SELECT? MAX(sal) FROM emp WHERE deptno = 20 -- 查詢20號(hào)部門工資最大值

SELECT * FROM emp

WHERE sal>(SELECT? MAX(sal) FROM emp WHERE deptno = 20)

-- 使用多行子查詢：1.先查詢20號(hào)部門的工資

SELECT sal FROM emp WHERE deptno =20

SELECT * FROM emp

WHERE sal>ALL(SELECT sal FROM emp WHERE deptno =20)

-- 比最大的大用>ALL

-- 查詢部門的編號(hào)，部門的名稱，部門所在的地址，部門的人數(shù)

-- 子查詢會(huì)查詢出每個(gè)部門的總?cè)藬?shù)

SELECT * FROM dept;

SELECT * FROM emp;

-- 子查詢會(huì)查詢出每個(gè)部門的總?cè)藬?shù)

-- COUNT(*)* 這里等于empno里的值沒有空值

SELECT deptno,COUNT(*) AS empnum FROM emp WHERE deptno IS NOT NULL GROUP BY deptno

-- 主查詢

SELECT d.deptno,d.dname,d.loc,IFNULL(t.empnum,0) from dept d? LEFT JOIN (

SELECT deptno,COUNT(*) AS empnum FROM emp WHERE deptno IS NOT NULL GROUP BY deptno

) t -- ()里為表2，from 表1join表2 on 等值

ON d.deptno=t.deptno

-- 1.查詢張三的同班同學(xué)的信息

-- 1.先查詢出張三所在的學(xué)校

SELECT sclass from student WHERE sname='張三'

-- 2. 查詢出張三同班同學(xué)的信息

select * from student? WHERE sclass=(SELECT sclass from student WHERE sname='張三') and sname !='張三'

-- 2.查詢總成績(jī)比張三總成績(jī)高的學(xué)號(hào)，姓名，總成績(jī)

-- 2.1 查詢出張三的總成績(jī)

select sum(g.sgrade) from grade g ,student s

WHERE g.sid=s.sid and s.sname='張三'

-- 2.2 主查詢

SELECT s1.sid,s1.sname,SUM(g1.sgrade) FROM student s1,grade g1

WHERE s1.sid=g1.sid

GROUP BY s1.sid,s1.sname HAVING SUM(g1.sgrade)>(select sum(g.sgrade) from grade g ,student s

WHERE g.sid=s.sid and s.sname='張三')

-- 3. 查詢1班總成績(jī)最高的同學(xué)的學(xué)號(hào)。姓名?？偝煽?jī)

-- 子查詢:先查詢出1班的總成績(jī)

-- 主查詢：查詢出一班總成績(jī)最高的(可以使用>=All)

SELECT * FROM student;

select * FROM grade;

SELECT sum(g.sgrade)AS'總成績(jī)' from grade g,student s WHERE s.sid=g.sid and s.sclass='一班'? GROUP BY s.sid

select s1.sid,s1.sname,sum(g1.sgrade) from student s1,grade g1

where s1.sid=g1.sid GROUP BY s1.sid,s1.sname

HAVING SUM(g1.sgrade)>= All

(SELECT sum(g.sgrade)AS'總成績(jī)' from grade g,student s

WHERE s.sid=g.sid and s.sclass='一班'? GROUP BY s.sid)

-- 4.查詢總成績(jī)比3號(hào)同學(xué)總成績(jī)高的人的姓名，總成績(jī)

SELECT * FROM student;

select * FROM grade;

select sum(g.sgrade) from student s,grade g? WHERE s.sid=g.sid and s.sid='3'

select s.sname sum(g.sgrade) from student s1.grade g1

where s1.sid=g1.sid having sum(g.sgrade) >=ALL

(select sum(g.sgrade) from student s,grade g? WHERE s.sid=g.sid and s.sid='3')

-- 1.查詢員工表中工資最高的員工的工號(hào)，姓名，工資和部門號(hào)

select sal FROM emp

select * from emp

SELECT? empno,ename,sal,deptno FROM emp? where? sal =(select max(sal) FROM emp )

-- 2.查詢薪水大于1200的員工，按照部門編號(hào)進(jìn)行分組，分組后的平均工資>1500，查詢各分組的平均工資，按照工工資進(jìn)行倒序排序

select deptno, AVG(sal) '平均工資'

from emp? where sal> '1200'

GROUP BY deptno

HAVING AVG(sal)>'1500'

ORDER BY '平均工資' DESC

-- 3.查詢每個(gè)部門和其所在的部門的名稱(使用2中方法:等值連接，內(nèi)連接)

select * from emp ;

select * from dept;

select dname,empno from dept d ,emp e

where

d.deptno=e.deptno

select empno ,dname from emp INNER JOIN dept ON emp.deptno=dept.deptno

-- 5.查詢雇員名第二個(gè)字母不是a的雇員的姓名，所在的組名(部門名稱)，工資所在的等級(jí)。(emo dept salgrade)

select ename,dname,grade

from emp e INNER JOIN dept d ON e.deptno=d.deptno

INNER JOIN salgrade s on e.sal BETWEEN s.losal and s.hisal

WHERE ename NOT LIKE '_a%'

-- 6. 查詢每個(gè)員工和其經(jīng)理的姓名

SELECT * from emp

SELECT e1.ename,e2.ename? from emp e1 ,emp e2 where e1.mgr=e2.empno

-- 7.查詢每個(gè)員工和其經(jīng)理的姓名(包含公司的老板) -- 外連接

SELECT * from emp

select ename from

SELECT e1.ename,e2.ename? from emp e1 LEFT OUTER JOIN emp e2 ON e1.mgr=e2.empno

-- 8.查詢每個(gè)員工的姓名以及其所在的部門的名稱(包括沒有員工的部門)

SELECT * from emp ;

SELECT * FROM dept ;

select ename,dname from emp e

RIGHT OUTER JOIN? dept d

on e.deptno=d.deptno

-- 9.查詢每個(gè)部門中工資最高的人的姓名，薪水，和部門的編號(hào)

-- 9.1 求出每個(gè)部門的最高工資

SELECT MAX(sal) mas_sal,deptno from emp GROUP BY deptno

-- 9.2 主查詢

select ename,sal,e.deptno

FROM emp e INNER JOIN (SELECT MAX(sal) max_sal,deptno from emp GROUP BY deptno

) t -- 取個(gè)別名 t

ON e.deptno=t.deptno and e.sal=t.max_sal

-- 10. 查詢每個(gè)部門平均工資所在的等級(jí) (非等值連接)

-- 求每個(gè)部門的平均工資

SELECT deptno,AVG( sal) avg_sal FROM emp GROUP BY deptno

-- 求出平均工資所在的等級(jí)

SELECT * from emp ;

SELECT * from dept;

SELECT * FROM salgrade;

select deptno,avg_sal,grade

from salgrade s INNER JOIN (SELECT deptno,AVG( sal) avg_sal FROM emp GROUP BY deptno

)t

ON t.avg_sal BETWEEN s.losal and s.hisal

-- 11.查詢每個(gè)部門內(nèi)平均工資等級(jí)

-- 先求出每個(gè)人的工資等級(jí)

select * FROM emp;

SELECT * FROM salgrade;

select ename,deptno,grade

FROM emp e INNER JOIN salgrade s

ON e.sal BETWEEN s.losal and s.hisal

-- 再按組進(jìn)行分組求平均值

SELECT t.deptno,avg(t.grade)

FROM (select ename,deptno,grade

FROM emp e INNER JOIN salgrade s

ON e.sal BETWEEN s.losal and s.hisal

) t

GROUP BY t.deptno

-- 12.查詢員工中那些人是經(jīng)理

-- 1.查詢員工表中經(jīng)理的編號(hào)(mgr)

select DISTINCT mgr from emp

select ename from emp where? empno in(select DISTINCT mgr from emp)

select ename from emp INNER JOIN(select DISTINCT mgr from emp

)t

on emp.empno=t.mgr

SELECT * from emp;

SELECT * from dept;

-- 13.查詢平均薪水最高的部門的編號(hào)

-- 1.查詢出每個(gè)部門的平均工資和部門的編號(hào)

select deptno,AVG( sal) avg_s from emp GROUP BY deptno?

-- 2.查詢出平均工資最高的值

select MAX(t.avg_s) FROM(select deptno,AVG( sal) avg_s from emp GROUP BY deptno?

)t

-- 3.組合

select t1.deptno,t1.avg_s from (select deptno,AVG( sal) avg_s from emp GROUP BY deptno?

)t1

WHERE t1.avg_s =(

select MAX(t2.avg_s)

FROM(select deptno,AVG( sal) avg_s from emp GROUP BY deptno?

)t2

)

select deptno,AVG( sal) FROM emp

GROUP BY deptno HAVING AVG( sal)

>=All(select AVG( sal) from emp GROUP BY deptno? )

-- 14.查詢平均薪水最高的部門的名稱

SELECT * from emp;

SELECT * from dept;

select d.dname? FROM emp e,dept d WHERE d.deptno=e.deptno

GROUP BY d.deptno HAVING AVG( e.sal)

>=All(select AVG( sal) from emp? GROUP BY deptno? )

-- 15.查詢平均工資的等級(jí)最低的部門的名稱

-- 1查詢每個(gè)部門的平均工資

SELECT avg(sal) avg_sal,deptno from emo group by deptno

SELECT deptno,AVG( sal) avg_sal FROM emp GROUP BY deptno

-- 2.查詢平均工資的等級(jí)

SELECT * from emp;

SELECT * from dept;

SELECT * FROM salgrade;

select t.deptno,t.avg_sal,MIN(grade) from salgrade s INNER JOIN

(SELECT deptno,AVG( sal) avg_sal FROM emp GROUP BY deptno ) t

ON t.avg_sal BETWEEN s.losal AND s.hisal

INNER JOIN dept d on t.deptno=d.deptno

-- 3.查詢平均工資等級(jí)最低的值

SELECT? MIN(grade)FROM (

select t.deptno,t.avg_sal,s.grade from salgrade s INNER JOIN

(SELECT deptno,AVG( sal) avg_sal FROM emp GROUP BY deptno ) t

ON t.avg_sal BETWEEN s.losal AND s.hisal

INNER JOIN dept d on t.deptno=d.deptno

)

-- 4.查詢平均工資的等級(jí)最低的部門 編號(hào)

SELECT deptno FROM (

)

-- 視圖view： 視圖就是一張?zhí)摂M表

LIMIT 分頁

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 1. select * from emp; 2. select empno, ename, job from em...
    海納百川_4d26閱讀 1,997評(píng)論 0贊 4
  • 引出 ?請(qǐng)思考如下問題？ –查詢所有員工的每個(gè)月工資總和，平均工資？ –查詢工資最高和最低的工資是多少？ –查詢公...
    C_cole閱讀 7,388評(píng)論 0贊 3
  • 1.簡(jiǎn)介 數(shù)據(jù)存儲(chǔ)有哪些方式？電子表格，紙質(zhì)文件，數(shù)據(jù)庫(kù)。 那么究竟什么是關(guān)系型數(shù)據(jù)庫(kù)？ 目前對(duì)數(shù)據(jù)庫(kù)的分類主要是...
    喬震閱讀 2,020評(píng)論 0贊 2
  • 5.多表查詢 多表查詢 目的：從多張表獲取數(shù)據(jù) 前提：進(jìn)行連接的多張表中有共同的列 等連接 通過兩個(gè)表具有相同意義...
    喬震閱讀 1,546評(píng)論 0贊 0
  • 從新生的那一刻 你就很好的偽裝 騙過了夏，染紅了秋 只等一場(chǎng)救贖 寒霜過后 你如愿以償?shù)?贏來了一次洗禮 洗盡了歲...
    董言閱讀 405評(píng)論 3贊 7

友情鏈接更多精彩內(nèi)容