oracle 问答

野鹤闲人 / 2023-08-21 / 原文

1、over partition by 按班级维度，取每个班第一名

 select *
    from (select t.id,
                 t.class,
                 t.sroce,
                 rank() over(partition by t.class order by t.sroce desc) n
            from school t)
    where n = 1;

2、扩展

row_number() over(partition by ... order by ...)：返回分组排序后的顺序
rank() over(partition by ... order by ...)：返回分组排序后的排名(并列第一名的情况下返回：第一名，第一名，第三名)
dense_rank() over(partition by ... order by ...)：返回分组排序后的排名（并列第一名的情况下返回：第一名，第一名，第二名)
count(A) over(partition by ... order by ...)：返回分组排序后的总数。
max(A) over(partition by ... order by ...)：返回分组排序后的最大值。
min(A) over(partition by ... order by ...)：返回分组排序后的最小值。
avg(A) over(partition by ... order by ...)：返回分组排序后的平均值。
sum(A) over(partition by ... order by ...)：返回分组排序后的累加求和。
lag(A,1) over(partition by ... order by ...)：取出上一列的A的值放到本列中。　　
lead(A,1) over(partition by ... order by ...)：取出下一列的A的值放到本列中。　　
ratio_to_report(A) over(partition by B) 返回分组后的A在其分区B内的占比，A就是分子，B分的组就是分母

3、mysql和oracle区别

mysql：开源，sql灵活，只有innodb支持事务，默认表级锁

oracle：商业收费，sql严格，完全支持事务，默认行级锁

4、oracle的 function 和 procedure 的区别

function是存储过程的一种

function 必须有返回值，只能返回一个，可以通过sql查询语句调用

procedure：可以没有返回值，通过out可以返回多个；只能单独调用

5、truncate和 delete

truncate：不写rollback日志，所以快但是数据不能恢复；不能删除视图？delete应该也不可以删除吧，DDL

delete：写日志；不腾出表空间；DML

6、rowid和rownum的定义

都是伪列

rowid是物理地址，对应数据的物理存储地址

rownum 是查询结果的排序

7、tablespace和datafile的关系

tablespace：逻辑概念；表空间下面有很多个datafile，共同组成表空间的大小

datafile：物理数据文件；一个只能对应一个表空间

表数据通过hash，分布到tablespace的不同的数据文件下

8、nvl()和nvl2()

nvl(a,b)

nvl2(a,b,c)：都是依次看，不为null直接返回，为null继续看下一个，这里，b和c如果类型不同时，c会转为b的类型

9、union和union all

union：没有重复值，按第一个查询的第一列升序排列

union all：有重复值，不排序

10、minus和 intersect ？？？？

minus：在第一个查询里面但是不在后面查询里面，不重复，按第一个查询的第1列升序

intersect：取每个结果的交集，不重复，按第一个查询的第1列升序

11、三范式

第一范式：字段是最小单位

第二范式：满足1，并且表字段完全依赖全部主键，而非部分主键

第三范式：满足2，并且非外键的字段，必须互不依赖，也不存在传递依赖　　

12、游标和触发器

declare

cusor cur is select * from person;

record person%rowtype;

begin

open cur;

loop

fetch cur into record;

if cur%notfound then

exit;

end if;

dbms_output.putline(to_char(record.id) || ',' || record.name);

end loop;

close cur;

end;