数据库经典算法

---

layout:post
title:“数据库-子查询”
date:2019-5-22 19:00:00
description:‘database-subquery’
tags:
- database
- sql
categories:
- 大二下

# 子查询

## any,all(或some)的用法

* =any与in等效
* 如果子查询中包含null数据，所有all相关的比较运算不会返回任何结果

## 子查询适用范围

### DDL

* select+相关子查询
* from+不相关子查询with a as()
* where+子查询
* group by 不能用子查询
* having相当于where
* order by也可以用，但用的很少

### DML

另外，子查询还可以用在update,insert,delete,create中。

#### create&insert:复制表结构

在oracle中：可以有

create table empnew as select * from emp--根据查询结果，直接创建一个表
select * into empnew from emp--并把查询内容插入进去[1]

[1]上面一个语句相当于以下两个：

select * into empnew from emp where 1=2
insert into empnew select * from emp--复制插入

#### delete

只有在查询的时候，才有连接的概念,要删除多个表只能写多个delete

delete from emp--delete from emp没有*
where deptno in (select deptno from emp
				group by deptno
				having avg(sal)<2500)

#### update

例题：将在平均工资低于2500的部门工作的员工的补助设为平均工资的10%

--思路：更新所依据的条件是动态的，因而要使用相关子查询
begin transaction
update emp
set comm=(select avg(sal)*0.1
         from emp t
         where t.deptno=emp.deptno)
where  deptno in (select deptno from emp
                 group by deptno
                 having avg(sal)<2500)
rollback        

update中子查询的本质：得到查询结果，将查询结果整体写回到原表中

--错误写法示例
select sal*2,sal*0.5
set sal=sal*2,comm=sal*0.5
--在赋值号右边的都是旧值，在左边的都是新值

### case语句

begin transaction
update emp
set sal=case when sal<=2500 then sal*1.5
			 when sal>2500 then sal*1.1
			 end

case deptno when 10 then sal*1.5
			when 20 then sal*1.1
			end

select ename,sal,case when sal<=1500 then '低'
					  when sal>1500 and sal<=3000 then '中'
					  else '高'
					  end 工资等级

### 例题

#### 一：20的排在最前面，其他的按大小顺序排

##### 方法一

##### 方法二

with pxb as(--先执行一个子查询得到一个视图
select 20 deptno 1,pxh
union
select 30 deptno 2,pxh--用select凭空造出来一个排序表
union
select 10 deptno ,2 pxh--
)
select ename,sal,emp.deptno
from emp join pxb on emp.deptno=pxb.deptno
order by pxh

##### 方法三

select ename,sal,emp.deptno
from emp join pxb on emp.deptno=pxb.deptno
order by case deptno when 20 then 1
					 else 2
					 end
		 ,deptno

#### 二：找到所有提供了所有平均工资高于3000的职位的部门

select dname
from dept
where not exists(select job from emp
                group by job having avg(sal)>3000
                except 
                 select job from emp
                 where deptno=dept.deptno
                 )

思路：要求部门包含所有平均工资高于2500的职位

——如何描述包含关系A包含于B？

* 值和值的判断，in,>,any

* 集合和集合的判断？
   * 交并差运算
   * exists（表），是相关子查询的运算，true表示至少存在一行

sql中没有集合相等的判断，因此不能用交并差来表示包含关系；

存在差集运算，且可以用exists()表示空集，因此用A∪B-B=∅表示包含关系