oracle序列，数据，数值

序列

创建序列

create sequence teacher_seq;
select teacher_seq.nextval from dual;
select teacher_seq.currval from dual;
#需要先执行上一步

自增长

insert into teacher(id,name,pwd) values (teacher_seq.nextval,'za','123');
select * from teacher;

insert into teacher(id,name,pwd) values (teacher_seq.nextval,'ls','456');
select * from teacher;

序列数量

序列从100开始

create sequence teacher_seq2 start with 100;

限制序列数量5-100

create sequence teacher_seq3 minvalue 5 maxvalue 100;
select teacher_seq3.nextval from dual;

创建序列规则是从1开始，每次加3的等差数列

create sequence teacher_seq4 increment by 3;
select teacher_seq4.nextval from dual;

表中操作

改数据

update teacher set pwd='123456' where id = 4;
update teacher set pwd='654321' ,name = 'lss' where id = 5;
select * from teacher;

对齐

lpad 将数字补齐6位，空位用0补
rpad向右补齐

select lpad(id,6,'0') from teacher;
select lpad(name,6,'-') from teacher;
select rpad(name,6,'-') from teacher;

字符操作

select upper(name) from teacher;
--大写
select lower(upper(name)) from teacher;
--小写
select initcap(name) from teacher;
--首字母大写

精准改字符

update teacher set name = 'YangBaiLao' where id = 7 ;

求字符串长度

select id,length(id),name,length(name) from teacher;

字符串截取

--字符串截取，1表示从字符串的第一位开始截取，截取3个数字
select id,pwd,name,substr(name,1,3) from teacher;
--字符串截取，-3表示从字符串的倒数第3位开始截取，截取2个数字
select id,pwd,name,substr(name,-3,2) from teacher;

查找

--查找 指定的字符串在源字符串中出现的位置，并返回，如果没有找返回0，否则返回位置下标
select name,instr(name,'a') from teacher;
select name,instr2(name,'a',3) from teacher;

字符反转

select name,reverse(name) from teacher;--字符反转

字符串拼接

select name,concat('hello: ',name) from teacher;

trim 去空格

select length(' hello java '), trim(' hello java '),length(trim(' hello java ')),
       ltrim(' hello java '),length(ltrim(' hello java ')) from dual;

replace替换函数

select name,replace(name,'a','x') from teacher;

正则表达式

select name,regexp_replace(name,'a.g','x') from teacher;

数值

四舍五入

select round(123.456,2) from dual;--保留两位小数
select round(123.454) from dual;

向上取整

select ceil(123.45) from dual;--124

向下取整

select floor(123.45) from dual;

取模

select mod(32,7) from dual;--取模 4
select mod(30,6) from dual;--取模 0

绝对值

select abs(128) from dual;--绝对值
select abs(-128) from dual;

判断正负

select sign(-12) from dual;--判断正负

正数返回1，负数返回-1，0返回0

平方根

select sqrt(2) from dual;

多次方

select power(2,3) from dual;--2的三次方

保留小数

select trunc(1234.5678,3) from dual;
--保留三位小数

将数字转换为字符串

select to_char(123.45,'000.00') from dual;

select to_char(123.45,'0000.000') from dual;

自动补齐

select to_char(123456789.98,'000,000,000.00') from dual;