借鉴了一些文章,在此表示感谢。仅学习讨论,如有错误欢迎指正!!!
ps:oracle下标从1开始,列标也是
下面所使用的数据:
create table count_order (
id number(3),
name varchar2(5),
age number(3),
sex varchar2(5)
);
select ct.*,rowid,rownum from count_order ct;
一.伪列
1.rowid
1)是一种数据类型,唯一标识一条记录物理位置的一个id,基于64位编码的18个字符显示。
2)未存储在表中,可以从表中查询,但不支持插入,更新,删除它们的值。
2.rownum
1)rownum是一个序列,是oracle数据库从数据文件或缓冲区中读取数据的顺序。
2)rownum是根据sql查询后得到的结果自动加上去的。
二.count()函数
1.count(*)
select count(*) from count_order;--5 查所有行个数
2.count(1)
select count(1) from count_order;--5 查所有行个数 也可以是其他数字
3.count(列名) 空值不考虑
select count(id) from count_order;--5 以id列为基准 查行个数
select count(age) from count_order;--4 以age列为基准 查行个数
三.order by
1.order by 列名 asc(升序 是默认的 可省略) desc(降序)
select * from count_order order by id;
select * from count_order order by id desc;
2.order by 数字 第一列对应1 以此类推
select * from count_order order by 3 desc;-- 第三列 :age
四.where
where ... and ...
我的理解:
a.where 之后用的是关系运算符 并用 and 进行连接
b.where之后的and类似于java里的&&,也有短路性质。where之后的条件只有全部为true,再去根据条件进行筛选。
1.where 1 = 1 and ...
1 = 1 布尔运算值为true
select * from count_order where 1 = 1 ;--查处所有数据
select * from count_order where 1 = 1 and sex = 'g' ;--类似于java if(1 == 1 && sex.equals("g"))
2.where 1 = 2 and ...
1 = 2 布尔运算值为false
select * from count_order where 1 = 2 and sex = 'g' ;--类似于java if(1 == 2 && sex.equals("g")) 短路了 查不到数据
3.测试 where 之后用的是关系运算符
select * from count_order where true and sex = 'g' ;
五.分析函数
1.row_number()、rank()、dense_rank()
这三个函数都返回唯一的值,要配合分析函数over()一起使用
row_number()类似于rownum,rank()是跳跃排序,dense_rank()是连续排序。
select ct.*,
ROW_NUMBER() over(order by sex) rnum,
RANK() over(order by sex) rank,
DENSE_RANK() over(order by sex) drank
from count_order ct;
2.over()函数初步了解
语法:函数名( [ 参数 ] ) over( [ 分区子句 ] [ 排序子句 [ 滑动窗口子句 ] ])
分区子句:partition by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组。
select ct.*,
ROW_NUMBER() over(partition by sex order by sex) rnum,
RANK() over(partition by sex order by sex) rank,
DENSE_RANK() over(partition by sex order by sex) drank
from count_order ct;
select ct.*,sum(age) over(partition by sex order by sex) sum from count_order ct;
与group by 区别?
group by 前面的列 只能是分组的条件或者组函数
select age,sum(age) from count_order group by age;
而over()前后可以有任何列
六.偏移量函数lead,lag
1.lead(列名,偏移量,查不到值的默认值) :查出同一字段下一个值
lead(列名,1,null) 1偏移量默认值,nul默认值,可省略
select ct.*,
lead(age) over(partition by sex order by age) lead
from count_order ct;
select ct.*,
lead(age,2,22) over(partition by sex order by age) lead
from count_order ct;
2.lag(列名,偏移量,查不到值的默认值) :查出同一字段上一个值
lag(列名,1,null) 1偏移量默认值,nul默认值,可省略
select ct.*,
lag(age,1,0) over(partition by sex order by age) lead
from count_order ct;