1、创建索引
create sequence cst_seq
increment by 1
start with 1
nomaxvalue
nocycle
nocache;
2、将索引添加到数据库表格的某列
INSERT INTO cst_customer(CUST_ID) VALUES(CST_SEQ.NEXTVAL);
3、修改数据库表格字段属性
alter table cst_customer modify Cust_Name varchar(20) null;
4、赋予用户创建索引的权限
grant create sequence to c##test;
5、查询索引( cst_seq)的下一个值
select cst_seq.nextval from dual;
6、Oracle分页查询语法
select * from cst_customer where rownum <= 2 and rownum >=1;
7、查询表格中每列的列名和属性的语句
select * from user_tab_columns where table_name = 'CST_CUSTOMER';
8、查询所有序列
select * from user_objects ubs where ubs.OBJECT_TYPE='SEQUENCE';
9、查询表格字段的注释
select * from user_col_comments where table_name = 'T_COMM_PROP';
10、修改列名
alter table 表名 rename column 旧列名 to 新列名;
11、查询某一序列的下一个值
Select PLAN_INST_SEQ.nextval from dual;
# 插入
INSERT INTO T_FILE_MAPPING(ID) VALUES(FILE_MAPPING_SEQ.NEXTVAL)
12、组内排序:
(1)row_number() over(partition by ‘列名1’order by ‘列名2’desc)
(2)rank() over(partition by ‘列名1’order by ‘列名2’desc)
(3)dense_rank() over(partition by ‘列名1’order by ‘列名2’desc)
相同点:按列名1进行分组按列名2进行排序
区别一:假如列1组内有相同数值的列2值,则row_number会按其他规则继续排序,
Rank() over会使相同值的数据的序号相同,但是是跳远式的接下来的元素就不是接着的序号了,dense_rank()会使相同值的数据的序号相同,但不是跳远式接下来的序号接着排
*例:
--每门课程第一名只取一个:
select * from (select name,course,row_number() over(partition by course order by score desc) rank from student) where rank=1;
--每门课程第一名取所有:
select * from (select name,course,dense_rank() over(partition by course order by score desc) rank from student) where rank=1;
--每门课程第一名取所有:
select * from (select name,course,rank() over(partition by course order by score desc) rank from student) where rank=1;