Oracle笔记（2）

create table TEST (
ID NUMBER primary key ,
NAME varchar(20),
SEX varchar(20)
)

insert into test(id,name,sex) values (1,‘这是1’,‘男’);
insert into test(id,name,sex) values (2,‘这是2’,‘女’);
insert into test(id,name,sex) values (3,‘这是3’,‘男’);
insert into test(id,name,sex) values (4,‘这是4’,‘女’);
insert into test(id,name,sex) values (5,‘这是5’,‘女’);
insert into test(id,name,sex) values (6,‘这是6’,‘男’);
insert into test(id,name,sex) values (7,‘这是7’,‘男’);
insert into test(id,name,sex) values (8,‘这是8’,‘男’);
insert into test(id,name,sex) values (9,‘zheshi9’,null);
insert into test(id,name,sex) values (10,‘ZHESHI10’,null);

select id,name,sex from test

– ASCLL码
select ASCII(‘A’) A, ASCII(‘a’) a from test
–CHR(n)
select CHR(55) from test
–concat 拼接
select concat(name,sex) from test
–initcap 首字母大写
select id,INITCAP(name) from test where id=10;
–instr 查询返回的位置
select id,INSTR(name, ‘这’) from test
–length 字段大小
select id, LENGTH(name) from test
– lower 字段全小写
select id, lower(name) from test
– upper 字段全大写
select id,UPPER(name) from test
–ltrim 砍掉第一个字段
select id,LTRIM(name,‘e’) from test
–replace 替换掉
select id,REPLACE(name, ‘这是’, ‘zheshi’) from test
–substr 截取从i个字段开始j个字段
select SUBSTR(‘hahaha’, 2, 3) from test

日期转化

–取年
select TO_CHAR(SYSDATE,‘yyyy’) from test where id =1
–取月
select TO_CHAR(SYSDATE,‘MM’) from test where id =1
–取日
select TO_CHAR(SYSDATE,‘dd’) from test where id =1
–取时
select TO_CHAR(SYSDATE,‘HH’) from test where id =1
–取分
select TO_CHAR(SYSDATE,‘mi’) from test where id =1
–取秒
select TO_CHAR(SYSDATE,‘ss’) from test where id =1
– 取年月日时分秒
select TO_CHAR(SYSDATE,‘yyyy-MM-dd HH12:mi:ss’) from test where id =1