SQL理论课-Class 4

Class_4:

嵌套子查询：（可以转换成表连接）

-- 把查询语句嵌套在where筛选条件中：

select ename
from emp
where hiredate > (select hiredate 
                  from emp
                  where ename = 'king')

-- 里面这个子查询返回一行可以直接使用比较运算符，因为就这一个人这个名字

如果子查询可能返回多行？
— 把运算符号换成in

select name
from student
where ID in(筛选出来的很多行)

用 in 和 not in 查询是否存在于查询集合中：

select ENAME,EMPTNO,SAL
from EMP
where DEPTNO in (select DEPTNO
                 from DEPT
                 where LOC = 'NewYork')
                
select count(salary)
from EMP
where DEPTNO in (Select DEPTNO
                from EMP
                where ENAME = 'blake')

双嵌套：

select *
from EMP
where DETPNO not in(Select DEPTNO
                    from EMP
                    where ENAME = 'blake')  
and JOB in (select JOB
            from EMP
            where DEPTNO in (Select DEPTNO  from EMP  where ENAME = 'blake')
           )

SOME 和 ALL：

运算符+some：满足一个就行

select name 
from instructor
where salary > some (select salary from instructor where dept_name='Biology')
-- 查找出来老师只要工资  比生物系最起码一个老师的工资高就行。

运算符+all ：必须要全部满足才可以

=all 和 in 不是一个概念，in 是满足一个

<>some 和 not in 不是一个概念some=

<>all 和 not in 一个概念

=some 和 in 一个概念

         =    <>          >=     <=
some    in    --         >=min    <=max
all     --    not in     <=max    <=min

select name
from student
where id <>all(select id
               from takes
               where course_id = 'cd=347')
               

select DEPTNO,AVG(SAL)
from EMP
group by DEPTNO
having AVG(SAL) >ALL AVG(select SAL from EMP group by DEPTNO)

select ENAME
from EMP
where EMPNO in (select MGR from EMP)

-- 陷阱：如果是not in，一定要小心NULL，因为NULL运算完了什么也找不到。

-- 加一个
EMPNO is not NULL