java_sql子查询

• 子查询:select语句里面嵌套select语句

• 子查询出现的问题:select ..(select).from..(select).where..(select).这三个位置

• where子句中使用子查询

//找出高于平均薪资的员工信息
select *form t_user where sal>avg(sal);  错误写法 
where后面不能加分组函数

//第一步:找出平均薪资
select avg(sal) from t_user;
+----------+
| avg(sal) |
+----------+
| 2099.375 |
+----------+
//第二步:where过滤
select *from t_user where sal > 2099.375;
+----+--------+-----+--------+---------+------+--------+------+------+------+
| id | name   | age | gender | content | job  | deptno | sal  | mgr  | num  |
+----+--------+-----+--------+---------+------+--------+------+------+------+
|  5 | WeiHua |  32 |      0 | NULL    | BBB  |      5 | 4321 |  100 |  103 |
|  7 | Ann    |  36 |      0 | NULL    | DDDD |      5 | 2131 |  104 |  105 |
|  8 | Lisa   |  19 |      0 | ceshi   | DDDD |      1 | 4345 |  105 |  106 |
| 10 | Ka_te  |  17 |      1 | NULL    | FFF  |      1 | 2433 |  110 |  108 |
+----+--------+-----+--------+---------+------+------
// 第一步和第二步合并
select *from t_user where sal >(select avg(sal) from t_user);

• from 后面嵌套子查询

//找出每个部门平均薪水的薪资等级

1.找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from t_user group by deptno;
+--------+--------------------+
| deptno | avgsal             |
+--------+--------------------+
|      2 |              656.5 |
|      5 | 2567.3333333333335 |
|      4 |               1002 |
|      1 |               3389 |
+--------+--------------------+
2.将以上的查询结果当做临时表t ，让t表和salgrade表连接 条件是平均工资在salgrade表俩个字段之间

select t.* , s.gread from (select deptno,avg(sal) as avgsal from t_user group by deptno) t  join salgrade s on t.avgsal between s.losal and s.hisal;
+--------+--------------------+-------+
| deptno | avgsal             | gread |
+--------+--------------------+-------+
|      2 |              656.5 | 1     |
|      5 | 2567.3333333333335 | 4     |
|      4 |               1002 | 1     |
|      1 |               3389 | 5     |
+--------+--------------------+-------+

• 在select后面嵌套子查询

//找出每个员工所在的部门名称 要求显示员工名和部门名
除了俩表关联查询之外 换一种方式查询

 select e.name,(select d.dname from dept d where e.deptno = d.deptno) as dname from t_user e;
+-----------+-----------+
| name      | dname     |
+-----------+-----------+
| HanMeimei | 测试部    |
| Lucy      | 采购部    |
| WeiHua    | 采购部    |
| ZhangWei  | 设计部    |
| Ann       | 采购部    |
| Lisa      | 市场部    |
| ZWei      | 测试部    |
| Ka_te     | 市场部    |
+-----------+-----------+
8 rows in set (0.00 sec)