子查询

1.子查询

• 概念

  子查询简单的来说就是一条sql语句里面嵌套了另外一条或者多条sql语句。

  一般使用在比较的值不确定，需要另外一个select语句执行以后才能得到。

• 子查询(第一种格式，子句出现在where或者having后面)

  select ...
  from ...
  where col_name = (select语句)
  group by ...
  having col_name = (select语句)
  order by ...;
  

• 查询工资比 Smith 工资高的员工信息

  • 因为我们不知道Smith薪水，所以先查询出Smith工资，再去比较(940.00)

    select salary 
    from s_emp
    where last_name = 'Smith';
    

  • 然后再去查询比940高的员工信息

    select *
    from s_emp
    where salary > 940
    

  • 将两条sql合并到一块

    select *
    from s_emp
    where salary > (
        select salary 
        from s_emp
        where last_name = 'Smith'
    );
    

• 查询以 N 开头，名字由四个字符组成，该员工所在部门员工的基本信息

  • 查询以 N 开头，名字由四个字符组成员工所在的部门编号(41)

    select dept_id
    from s_emp
    where last_name like 'N___';
    

  • 查询41号部门的员工信息

    select *
    from s_emp
    where dept_id in (41);
    

  • 合并两条sql

    select *
    from s_emp
    where dept_id in (
        select dept_id
        from s_emp
        where last_name like 'N___'
    );
    

• 查询平均工资比 41 号部门的平均工资 高 的部门中员工的信息

  • 查询41号部门平均工资(1247.5)

    select avg(salary)
    from s_emp
    where dept_id = 41;
    

  • 查询部门平均工资比1247.5高的部门(31,32,35,50,33,10)

    select dept_id
    from s_emp
    group  by dept_id
    having avg(salary) > 1247.5;
    

  • 查询上述部门的员工信息

    select *
    from s_emp
    where dept_id in (31,32,35,50,33,10);
    

  • 组合

    select *
    from s_emp
    where dept_id in (
    	select dept_id
        from s_emp
        group  by dept_id
        having avg(salary) > (
            select dept_id
    	    from s_emp
        	group  by dept_id
    	    having avg(salary) > 1247.5
        )
    
    );
    

• 查询比 Smith 所在部门最高工资 高 的员工的信息

  • 求部门(41)

    select dept_id 
    from s_emp
    where last_name='Smith';
    

  • 求最高工资(1450)

    select max(salary) 
    from s_emp 
    where dept_id=41;
    

  • 提収员工信息

    select id,last_name,salary 
    from s_emp 
    where salary > 1450;
    

  • 合并

    select id,last_name,salary 
    from s_emp 
    where salary > (
    	select max(salary) 
    	from s_emp 
    	where dept_id= (
    		select dept_id 
    		from s_emp
    		where last_name='Smith'
    	)
    )
    

• 练习

  • 查看员工名字为 Chang 的员工所在部门一样的员工信息
  • 查看所有和 Chang 不同部门的员工信息
  • 查询和 Chang 相同职位的其他员工信息
  • 查询公司里面工资比 平 均工资 低的员工的信息
  • 查看 Chang 员工所在部门薪资总和
  • 查看部门平均工资大于 32 号部门平均工资的部门 id

• 子查询第二种形式

  在查询过程中需要用来一张不存在的表，这张表需要用户通过 select 子句查询的结果进 行构建，然后再利用这张构建的表实现最终的查询。

select ...
from table_name,(select语句)
where col_name = (select语句)
group by ...
having col_name = (select语句)
order by ...;

• 查询平均工资比 41 号部门的平均工资 高的部门中员工的信息,并且显示出当前部门的 平均工资

  • 查询41号部门的平均工资(1247.5)

    select avg(salary)
    from s_emp
    where dept_id = 41;
    

  • 查询品骏工资比1247.5高的的部门(31,32,35,50,33,10))

    select dept_id
    from s_emp
    group by dept_id
    having avg(salary) > 1247.5
    

  • 查询该部门的员工信息

    select *
    from s_emp
    where dept_id in (31,32,35,50,33,10);
    

  • 查询部门平均薪水

    select avg(salary),dept_id
    from s_emp
    group by dept_id
    

  • 查询员工所在部门的平均薪水

    select emp.*,dd.gg
    from s_emp,(
    	select avg(salary) gg,dept_id deptid
    	from s_emp
    	group by dept_id
    )  dd
    where emp.dept_id = dd.deptid and emp.dept_id in (31,32,35,50,33,10)
    

 alter session set nls_language = 'english'

select round(sysdate,'MONTH')
from dual

select to_char(sysdate,'yyyy/mm/dd')
from dual

select to_char(salary,'L999999.00')
from s_emp
select *
from s_emp
select *
from s_dept


select e.last_name,d.name
from s_emp e,s_dept d
where e.dept_id(+)=d.id
union
select e.last_name,d.name
from s_emp e,s_dept d
where e.dept_id=d.id(+)

insert into s_emp (last_name,id )values('xuan',17)
select *
from s_emp


select e.last_name,d.name
from s_emp e,s_dept d
where e.dept_id(+)=d.id
minus
select e.last_name,d.name
from s_emp e,s_dept d
where e.dept_id=d.id(+)


select dept_id 
from s_emp
where last_name like'N___'

select *
from s_emp
where dept_id in(41)


select avg(salary)
from s_emp
where dept_id = 41

select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5

select *
from s_emp
where dept_id in(
      select dept_id
      from s_emp
      group by dept_id
      having avg(salary)>(
      select avg(salary)
      from s_emp
      where dept_id = 41))
      
      

select *
from s_emp
where dept_id in(
      select dept_id
      from s_emp
      where last_name='Chang'
)

select dept_id
from s_emp
where 


select avg(salary)
from s_emp
where dept_id = 32

select avg(salary),dept_id
from s_emp
group by dept_id
having avg(salary)>(
       select avg(salary)
       from s_emp
       where dept_id = 32
)

--查询平均工资比 41 号部门的平均工资高的部门中员工的信息,
--并且显示出当前部门的 平均工资 

select avg(salary)
from s_emp
where dept_id = 41


select dept_id
from s_emp
group by dept_id
having avg(salary)>(
      select avg(salary)
      from s_emp
      where dept_id = 41 
)

select *
from s_emp
where dept_id in (
      select dept_id
from s_emp
group by dept_id
having avg(salary)>(
      select avg(salary)
      from s_emp
      where dept_id = 41 
)
)

&kk

select avg(salary)
from s_emp
where dept_id = 41

select *
from s_emp
where dept_id in(select dept_id
      from s_emp
      group by dept_id
      having avg(salary)>(
            select avg(salary)
             from s_emp
              where dept_id = 41

))
select avg(salary),dept_id
from s_emp
group by dept_id


select emp.*,gg.avgs
from s_emp emp ,(
     select avg(salary) avgs,dept_id did
     from s_emp
     group by dept_id

)gg
where emp.dept_id = gg.did 
and emp.dept_id in (
    select dept_id
    from s_emp
    group by dept_id
    having avg(salary)>(
            select avg(salary)
            from s_emp
            where dept_id = 41

        )
)

• 拼接sql

  select emp.*,dd.gg
  from s_emp,(
  	select avg(salary) gg,dept_id deptid
  	from s_emp
  	group by dept_id
  )  dd
  where emp.dept_id = dd.deptid 
  and 
  	emp.dept_id in (
      	select dept_id
  		from s_emp
  		group by dept_id
  		having avg(salary) > (
  	        select avg(salary)
  			from s_emp
  			where dept_id = 41
          )
      );
  

2.子查询总结

• 两种形式
  • where 或 having 里面用到值，通过 select 获取到的
  • select 时需要一张额外的表，select 获取到的

3.运行时参数(了解)

• 概念

  sql 语句中的值,我们可以使用一个参数来代替,然后每次运行的时候都可以重新输入这 个值

• 格式

  &参数名;

  参数名可以任意。运行时参数可以是数字也可以是字符串，也可以是sql子句，一条sql语句可以拥有多个运行时参数。

• 参数是数字

  select * 
  from s_emp
  where id = &id;
  

• 参数是字符串(加单引号)

  select *
  from s_emp
  where last_name = &name;
  

• 参数是字符串(不加单引号)

  select *
  from s_emp
  where last_name = '&name';
  

• 参数出现多个

  select *
  from s_emp
  where salary > &a; and salary < &b;
  

• 参数为子句

  select *
  from s_emp
  where &a;
  
  
  select *
  &a;
  

万事开头难，中间难，最后很难！！！**