mysql表子查询(嵌套查询)

mysql表子查询(嵌套查询):

1.什么是子查询:

子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询

-- 如何显示与 SMITH 同一部门的所有员工?
-- 1.先查出smith是哪一个部门的  2.在查询员工表中编号和smith相同的
select * from emp where deptno = (select deptno from emp where ename = 'SMITH')


-- 如何查询和部门 10 的工作相同的雇员,但是不含 10 号部门自己的雇员.
-- 1.先查出部门为10的有哪些工作(使用distinct是因为查出的工作有相同的,需要去重)
select * from emp where job in (select DISTINCT job from emp where deptno = 10) and deptno <> 10 

-- 如何查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人)
写法1:select * from (select deptno,job from emp where ename = 'allen') as temp,emp where temp.job = emp.job and temp.deptno = emp.deptno and ename <> 'allen'

写法2:select * from emp where (deptno,job) = (select deptno,job from emp where ename = 'allen') and ename <> 'allen'

-- 请查询 和宋江数学，英语，语文成绩 完全相同的学生
方法1:
select student.chinese,student.english,student.math,student.`NAME` from (select chinese,english,math from student where NAME = '宋江') as temp,student where temp.chinese = student.chinese and temp.english = student.english and temp.math = student.math and NAME <> '宋江'

方法2:
select * from student where (math,english,chinese) = (select math,english,chinese from student where NAME = '宋江') and NAME <> '宋江'

知识扩展:

• 在MySQL中!= 和 <> 的功能一致，在sql92规范中建议是：!=，新的规范中建议为: <>
• 值得一提的是 = 、 <=> 以及 is 这三个运算符的用法（大家都知道 is 专门用来判断是否为 NULL，而 = 则是用来判断非NULL以外的所有数据类型使用。而 <=> 则是前两者合起来）

SELECT * FROM t_user WHERE username != "陈哈哈";

SELECT * FROM t_user WHERE username <> "陈哈哈";

SELECT * from t_user where username is null;

SELECT * from t_user where username <=> null;

SELECT * from t_user where username = '陈哈哈';

SELECT * from t_user where username <=> '陈哈哈';

-- all和any操作符
-- 显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号
select * from emp where sal > all(select sal from emp where deptno = 30)
select * from emp where sal > (select max(sal) from emp where deptno = 30)

-- 如何显示工资比部门 30 的其中一个员工的工资高的员工的姓名、工资和部门号
select * from emp where sal > any(select sal from emp where deptno = 30)
select * from emp where sal > (select min(sal) from emp where deptno = 30)

-- 查找每个部门工资高于本部门平均工资的人的资料,使用一个数据查询小技巧,把一个子查询当做一个临时表使用
SELECT
	ename,
	sal,
	temp.avg_sal,
	emp.deptno 
FROM
	emp,
	( SELECT deptno, AVG( sal ) AS avg_sal FROM emp GROUP BY deptno ) temp 
WHERE
	emp.deptno = temp.deptno 
	AND emp.sal > temp.avg_sal
	
-- 查找每个部门工资最高的人的详细资料
select * from emp,(select deptno,max(sal) max_sal from emp group by deptno) temp where emp.sal = temp.max_sal and emp.deptno = temp.deptno

-- 查询每个部门的信息(包括:部门名,编号,地址)和人员数量
select * from dept,(select count(*) deptNum,deptno deptno from emp group by deptno) temp where dept.deptno = temp.deptno

ecs_goods表

-- 查询ecs_goods表中各个类别中,价格最高的商品
-- 1.先分类别查出ecs_goods表中,每个类别最大的价格,让其当做临时表去查询,在关联ecs_goods表匹配价格相等的数据
select goods_id, ecs_goods.cat_id, goods_name, shop_price from (select cat_id,max(shop_price) as max_price from ecs_goods group by cat_id) as temp,ecs_goods where temp.max_price = ecs_goods.shop_price order by ecs_goods.cat_id