谁的工资最低,列出员工名字和工资
select ename,min(salary)
from emp_hiloo
//错误
select max(ename),min(salary)
from emp_hiloo
//错误
select ename,salary
from
where salary=min(salary)
//错误
select ename,salary
from
having salary=min(salary)
//错误
select min(salary)
from emp_hiloo
select ename,salary
from emp_hiloo
where salary=(select min(salary) from emp_hiloo)
子查询－－非关联子查询
子查询中的表和主查询中的表之间没有建立关联，主查询的表中的字段和子查询的表中的字段没有写成条件表达式
先执行子查询，结果可以0到多个；当子查询的结果有多个时，把去重后的结果传递给子查询
哪个部门的平均工资比32部门的平均工资高？
//下面这个方法对吗？
select deptno,avg(salary)
from
where avg(salary)>(select avg(salary) from emp_hilo where deptno=32)
//下面这个方法正确
select deptno,round(avg(salary)) avgsal
from
group by deptno
having round(avg(salary))>(select round(avg(salary)) from emp_hilo where deptno=32)
谁跟张无际的职位一样
select ename,job
from
where job=(select job from emp_hiloo where ename='zhangwuji');
=后不能跟两个及以上值
单值运算符
多值运算符
子查询的返回结果是多个时，要用多值运算符in any all
where job=any(select job from emp_hiloo where ename='zhangwuji');
where job in (select job from emp_hiloo where ename='zhangwuji');
where job in (select job from emp_hiloo where ename='zhangwuji')and ename<>'zhangwuji';
