mysql5

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

-- 概念：在某些查询的逻辑中，需要引入另一个查询作为条件或者数据来源进行辅助。

-- 先执行子查询再执行主查询

-- 查询与Smith在同一部门工作的其它员工信息

-- 1.查询出Smith所在的部门

select deptno from emp where ename='Smith'

-- 2.查询出与Smith在同一部门工作的其他员工信息

-- 分析：select * from emp where deptno=(Smith所在的部门编号) and ename <>'Smith' (!=)

select * from emp

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

and ename <>'Smith' (!=)(括号里的是子查询)

-- 基本的语法

-- SELECT * FROM 表名

-- where deptno=(子查询)

-- and ename <> 'Smith'

-- 说明：整个外部这部分SQL被称之为主查询(外部查询)，括号内的查询称之为子查询()

-- 子查询使用的位置

-- 1.可以出现在from子句(from后面)，可以理解为是一张临时表(当子查询出现多行多列的时候)

-- 2.可以出现在where子句(where后面)

-- 3.也可以出现在having子句中

-- 通常来说比较常用的子查询一般会出现在where或from中

-- 1.查询比Smith月薪高的员工的信息

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

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

-- 子查询会分为：单行子查询

-- 1.查询的结果为一行一列的数据。子查询就是一个简单的数据

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

-- 2.如果是单行子查询就可以使用单行运算符： > < >= <= = != 进行子查询的比较

-- 查询与James是同一个职位同一个部门的其他的员工的信息

-- 子查询：1.查询这个人的职位， 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'

-- 说明：子查询中可以嵌套其他的子查询，层数一般限制在32层内(嵌套层数不要过多，会影响SQL的执行效率)

-- 查询月薪高于Allen 所在部门平均月薪的员工信息

-- 1. 查询Allen所在部门

-- 2. 查询Allen所在部门的平均工资

-- 3. 查询月薪高于Allen所在部门的平均工资

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'))

-- 多行子查询：返回的是多行一列的数据

-- 注意：当子查询返回多行记录的时候，我们使用 > < >= <= = != 这类比较运算符是无法查询的

-- 多行子查询一定要使用多行比较运算符： IN ANY(任何一个) ALL(所有) age in （20，30）

-- ANY和ALL的较规则：主要是使用在多行子查询中进行>或<的操作

-- < ANY 表示比最大的小

-- > ANY 表示比最小的大

-- = ANY 相当于in

-- < ALL 表示比最小的小

-- > ALL 表示比最大的大

-- 查询是经理的员工信息

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 去除一样相同的值这里表示经理几个人有同一个经理会显示几个相同的经理名

-- is not null去掉空值这里表示老板没有经理值为空，所以要设置为非空

-- 查询比20号部门所有人的月薪都要高的信息

-- 1.查询20号部门月薪最高的

SELECT MAX(sal) FROM emp WHERE deptno = 20 -- 查询20号部门工资最大值

SELECT * FROM emp

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

-- 使用多行子查询：1.先查询20号部门的工资

SELECT sal FROM emp WHERE deptno =20

SELECT * FROM emp

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

-- 比最大的大用>ALL

-- 查询部门的编号，部门的名称，部门所在的地址，部门的人数

-- 子查询会查询出每个部门的总人数

SELECT * FROM dept;

SELECT * FROM emp;

-- 子查询会查询出每个部门的总人数

-- 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.查询张三的同班同学的信息

-- 1.先查询出张三所在的学校

SELECT sclass from student WHERE sname='张三'

-- 2. 查询出张三同班同学的信息

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

-- 2.查询总成绩比张三总成绩高的学号，姓名，总成绩

-- 2.1 查询出张三的总成绩

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班总成绩最高的同学的学号。姓名。总成绩

-- 子查询:先查询出1班的总成绩

-- 主查询：查询出一班总成绩最高的(可以使用>=All)

SELECT * FROM student;

select * FROM grade;

SELECT sum(g.sgrade)AS'总成绩' 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'总成绩' from grade g,student s

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

-- 4.查询总成绩比3号同学总成绩高的人的姓名，总成绩

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.查询员工表中工资最高的员工的工号，姓名，工资和部门号

select sal FROM emp

select * from emp

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

-- 2.查询薪水大于1200的员工，按照部门编号进行分组，分组后的平均工资>1500，查询各分组的平均工资，按照工工资进行倒序排序

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

from emp where sal> '1200'

GROUP BY deptno

HAVING AVG(sal)>'1500'

ORDER BY '平均工资' DESC

-- 3.查询每个部门和其所在的部门的名称(使用2中方法:等值连接，内连接)

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.查询雇员名第二个字母不是a的雇员的姓名，所在的组名(部门名称)，工资所在的等级。(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. 查询每个员工和其经理的姓名

SELECT * from emp

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

-- 7.查询每个员工和其经理的姓名(包含公司的老板) -- 外连接

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.查询每个员工的姓名以及其所在的部门的名称(包括没有员工的部门)

SELECT * from emp ;

SELECT * FROM dept ;

select ename,dname from emp e

RIGHT OUTER JOIN dept d

on e.deptno=d.deptno

-- 9.查询每个部门中工资最高的人的姓名，薪水，和部门的编号

-- 9.1 求出每个部门的最高工资

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 -- 取个别名 t

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

-- 10. 查询每个部门平均工资所在的等级 (非等值连接)

-- 求每个部门的平均工资

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

-- 求出平均工资所在的等级

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

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

-- 11.查询每个部门内平均工资等级

-- 先求出每个人的工资等级

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

-- 再按组进行分组求平均值

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.查询员工中那些人是经理

-- 1.查询员工表中经理的编号(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

on emp.empno=t.mgr

SELECT * from emp;

SELECT * from dept;

-- 13.查询平均薪水最高的部门的编号

-- 1.查询出每个部门的平均工资和部门的编号

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

-- 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.查询平均工资的等级最低的部门的名称

-- 1查询每个部门的平均工资

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

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

-- 2.查询平均工资的等级

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.查询平均工资等级最低的值

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.查询平均工资的等级最低的部门编号

SELECT deptno FROM (

)

-- 视图view：视图就是一张虚拟表

LIMIT 分页

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 217,509评论 6赞 504
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,806评论 3赞 394
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 163,875评论 0赞 354
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,441评论 1赞 293
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,488评论 6赞 392
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,365评论 1赞 302
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,190评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,062评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,500评论 1赞 314
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,706评论 3赞 335
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,834评论 1赞 347
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,559评论 5赞 345
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,167评论 3赞 328
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,779评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,912评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,958评论 2赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,779评论 2赞 354

mysql5

推荐阅读更多精彩内容