Oracle命令小结

create  table  teacher(
    id number,
    name varchar2(32),
    primary key (id));
insert into teacher(id ,name) values (1,'qyj');
commit ;
 --创建序列
 --创建默认序列规则是从1开始，每次加一
 create  sequence  teacher_seq;
select teacher_seq.nextval from dual;
select teacher_seq.currval from dual;
create table teacher1(id number,name varchar2(32),pwd varchar2(32),primary key (id));
insert into teacher1(id, name, pwd) VALUES (teacher_seq.nextval,'zs','123');
select * from teacher1;
insert into teacher1 values (teacher_seq.nextval,'zs','456');
select * from teacher1;
--创建序列规则是从一百开始每次加一
create sequence teacher_seq2 start with 100;
select teacher_seq2.nextval from dual;
--创建序列规则是从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;

select * from teacher1;
update teacher1 set pwd='123456' where id=4;
update teacher1
set  pwd='654321' ,name='lss'
where id=5;
select * from teacher1;
insert into teacher1 values (teacher_seq.nextval,'wueuwiso','321456');
--lpad将数字补齐六位，空位用0补齐
select lpad(id,6,'0') from teacher1;
select lpad(name,6,'-') from teacher1;
select rpad(name,6,'-') from teacher1;
select rpad(id,6,'0')from teacher1;
select upper(name) from teacher1;      --大写
select lower(upper(name))from teacher1;--小写
update teacher1 set name='YangBaiLao' where id=7;
select initcap(name)from teacher1;     --首字母大写
select id,length(id),name,length(name) from teacher1;--求字符串长度
select  id,pwd,name,substr(name,1,3) from teacher1; --字符串截取，1表示从字符串第一位开始截取，往后截取三个数字
select  id,pwd,name,substr(name,-1,3) from teacher1; --字符串截取，-1表示从字符串的倒数第一位开始截取，往后截取3个数字
select *
from teacher1;
--查找指定的字符串在原字符串中出现的位置，并返回，如果没有返回则返回0；否则返回下标
select name,instr(name,'s')from teacher1;
select name,instr2(name,'a',3)from teacher1;
select name,reverse(name) from teacher1;--字符反转
select name,concat('hello:',name) from teacher1;
--去除字符串两边的空格符
select trim('  hello java  ') from dual;
select length('  hello java  '),trim('  hello java  '),length(trim('  hello java  ')) from dual;
--replace 字符串替换
select name,replace(name,'a','x')from teacher1;

select name,regexp_replace(name,'an*','x')from teacher1;
select name,regexp_replace(name,'a.g','x')from teacher1;
--数值--
select round(123.456,2)from dual;--123.46
select round(123.454,2) from dual; --123.45(四舍五入)
select ceil(123.45)from dual;--向上取整124
select floor(123.45)from dual;--向下取整123
select mod(32,7)from DUAL;--取模4
select mod(30,6) from dual;--取模0
select abs(123)from dual;
select abs(-123) from dual;--取绝对值123
select sign(12) from dual;
select sign(-12) from dual;--判断正负 正数返回1，0返回0，负数返回-1
select sign(-22) from dual;
select sqrt(3) from dual;--开平方
select power(1.414,2) from dual;--1.414的平方
select trunc(1234.5678,3) from dual;--保留小数点后三位
select to_char(123.45,'0000.000')from  dual;--将数字转化成字符串0123.450
select to_char(123456789.98,'$000,000,000.00')from dual;
create  table T_date(
    id number,
    d1 date,
    d2 timestamp
);
select * from T_date;
insert into T_date (id, d1,d2)
values (1,sysdate,systimestamp);
select * from T_date;
select d1,add_months(d1,1)from T_date where id=1;--月份加一
select last_day(add_months(d1,1)) from T_date where id=1;--这个月的最后一天
select sysdate from dual;
select systimestamp from dual;
select trunc(sysdate),sysdate from dual;
select trunc(sysdate,'yyyy'),sysdate from dual;
select trunc(sysdate,'hh'),sysdate from dual;