数据库系统设计课程总结3-sql 进阶

①行列转换，该表是一个公司的员工表

现在一个需求：得到每个部门的不同职位的人数

with t as(
select empno,deptno
        ,case job when 'ANALYST' then 1 end ANALYST
        ,case job when 'MANAGER' then 1 end MANAGER
        ,case job when 'CLERK' then 1 end CLERK
        ,case job when 'PRESIDENT' then 1 end PRESIDENT
        ,case job when 'SALESMAN' then 1 end SALESMAN
from emp)

select deptno,isnull(sum(ANALYST),0) ANALYST
             ,isnull(sum(MANAGER),0) MANAGER
             ,isnull(sum(CLERK) ,0)CLERK
             ,isnull(sum(PRESIDENT) ,0)PRESIDENT
             ,isnull(sum(SALESMAN),0) SALESMAN
from t 
group by deptno

运行结果：

②分页查询记录，三种排名方法：
还是上面的emp表：

with t1 as(
select row_number() over(order by sal) xh,empno,ename,sal
from emp)
select *
from t1 
where xh >= 6 and xh <= 10

select top 5 empno,ename,sal
from emp
order by sal

----紧密的排名
select dense_rank() over(order by sal) xh,empno,ename,sal
from emp
order by sal

select row_number() over(order by sal) xh,empno,ename,sal
from emp
order by sal

select rank() over(order by sal) xh,empno,ename,sal
from emp
order by sal

③关系除法

-------提供了工资在2000-5000之间的所有职位的部门
-------查询提供了已知给定职位的部门
select deptno,job
from emp
where sal between 2000 and 5000

--A包含B ==== B-A=空

select deptno
from dept
where not exists
( 
    select distinct job
    from emp
    where sal>2000 and sal<5000
    except 
    select job
    from emp
    where emp.deptno=dept.deptno            
)

④关于sql的更新

update tabla set a=a+1,b=a+1
更新前若a=1，则更新后，a=b=2
---等号右侧都是旧值，即b的更新不受a的改变的影响
---按批处理的

⑤视图的注意事项

----视图所查询的语句可以对应具体的物理存储地址 可更新
----视图通过计算得到的，不是通过映射得到的，不可进行更新
----用过计算结果的视图不可再次更新Eg：group by， distinct 计算字段的不可更新

----不存储数据 但是可以操作数据 是一个数据的映射
----作用
----①简化查询 
----②用来控制用户的操作权限，用户只能看到应该看到的数据不能看基层表
----提供安全性的访问控制

----当基层表发生改变，只要改变视图层即可，不要改变用户的使用方法

with check option :
----检查视图更新的效果与视图检查是否存在不一致的情况,若不一致则拒绝修改
----更新之后查询的数据发生变化