Oracle 语句整理

最新推荐文章于 2021-11-30 11:22:49 发布

原创最新推荐文章于 2021-11-30 11:22:49 发布 · 207 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle sql

闲来无事，整理整理sql，都是比较常用的,希望可以有用

--建表语句

create table user(
       id number not null primary key,
       username varchar2(40) not null,
       age number,
       address varchar2(100),
       role_id number
)
comment on column user.id is 'ID';
comment on column user.username is '用户名';

create table user_role(
       role_id number not null,
       role_name varchar2(40)
)

--新建和A表相同结构的A2表

create table A2 as select * from A;

--插入数据
insert into user (id,username) values (1,'admin');
commit;

insert into A2 select * from A;--子查询插入数据

--删除数据
delete from table user where id=1;
commit;

update A2 set c_id=(select c_id from A where id='453453');--子查询更新数据

--修改数据
update user set username='test' where id=1;
commit;

--查询数据
select * from user;

--删除表
drop table user;

--修改表的名称
rename user to t_user;

--查看表结构
describe user;

--添加列
alter table user add password varchar2(40);

--删除列
alter table user drop column password;

--修改列长度
alter table user modify (password varchar2(30));

--创建自增的序列
create sequence user_seq minvalue 1 maxvalue 999999999 increment by 1 start with 1 cache 20 order nocycle;
select user_seq.currval from user;

--创建索引
create unique index user_px on user.id;
create index username_px on user.username;

--创建视图
create or replace force view user_view as select * from user with read only;
select * from user_view;

--添加主键
alter table user_role add constraint pk_userrole_id primary key(role_id);

--添加外键
alter table user add constraint fk_roleid foreign key(role_id) peferences user_role(role_id);

--删除主键
alter table user_role drop constraint pk_userrole_id;

--删除索引
drop index user_px;

--左关联
select * from user u left join user_role r on u.role_id=r.role_id;

--右关联
select * from user u right join user_role r on u.role_id=r.role_id;

--内关联
select * from user u inner join user_role r on u.role_id=r.role_id;

--全关联
select * from user u full join user_role r on u.role_id=r.role_id;

--笛卡尔积
select * from user u,user_role r where u.role_id=r.role_id;

--分页查询
select s.* from (select rownum as num,t.* from user t where rownum<20) s where s.num>5;

--以下为一些函数的用法
select lower('MF MR'),upper('mf mr'),initcap('MF MR') from dual ;--大小写
select length('张三'),lengthb('张三'),lengthc('张三') from dual;--取长度
select concat('dfs','12') from dual;--拼接
select substr('abcdefg',3,4),substr('abcdefg',-1) from dual;--截取
select instr('corporate floor','or') from dual;--取字符串所在位置
select trim(leading 'a' from 'aaaaaaddfaaaaaa ') from dual;--截断连续的前置a
select trim(trailing 'a' from 'aaaaaaddfaaaaaa ') from dual;--截断连续的后置a
select trim(both 'a' from 'aaaaaaadfdgdaaaaaaaa') from dual;--截断连续的前后a
select lpad('adgag', 20,'-'),rpad('123456',10, '*') from dual;--左铺垫右铺垫,铺垫的位数,铺垫的字符串
select replace('TEST','T','D') from dual;--替换
select round(12.554,2),round(45.554,0),round(455.623,-1) from dual;--四舍五入
select trunc(15.794,1),trunc(45.245),trunc(15.79,-2) from dual;--截断
select ceil(45.245) from dual;--取整
select abs(-55.5) from dual;--取绝对值
select mod(146,54) from dual;--取余数
select sysdate from dual;--系统日期
select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';--显示当前日期显示格式
select to_char(sysdate, 'yyyyMMdd')-'20180207' from dual;日期加减
select months_between(to_date(to_char(sysdate, 'yyyyMMdd'),'yyyyMMdd'),to_date('20180105','yyyyMMdd')) from dual;--两个日期的月间隔
select add_months(sysdate,6) from dual;--几个月的是哪天
select next_day(sysdate,'星期日') from dual;--下一个星期日
select last_day(sysdate) from dual;--月底
select to_char(sysdate, 'yyyy/mm/dd') from dual;--格式转换
select to_char(sysdate, 'fmyyyy/mm/dd') from dual;--fm消除前置的0和空格
select to_char(sysdate, 'fmyyyy"年"mm"月"') from dual;
select to_char(sysdate, 'sssss') from dual;--当前距离零点的秒数
select to_char(458,'9999.000') from dual;
select to_char(458,'$0099900.00') from dual;
select to_char(1458,'l99,999.000') from dual;
select to_char('1404','9G999D99') from dual;

select to_char(321,'xxxxx') from dual;

select distinct call_id from t_call_info;--去重
select count(distinct call_id) from t_call_info;--去重后的数量，只可针对一个字段做操作
select * from t_call_info where callnum between 0 and 2;--在...和...之间,含上下界
select * from t_call_info where callnum in (0, 2);--在...之内,不能超过1000个值
select * from t_call_info where call_id like 'E%';--匹配首字母E的数据
select * from t_call_info where call_id like '_E%';--匹配第二个字母为E的数据
select * from t_call_info where call_id like '%/_%' escape '/'; --用/做转义
select * from t_call_info where condition is null;--字段为空的数据
select * from t_call_info where condition is not null;--字段不为空的数据
select * from t_call_info where callnum=0 and callnum=2;--并且
select * from t_call_info where callnum=0 or callnum=2;--或者
select * from t_call_info order by call_id;--排序desc降序,asc升序

select to_number('abc12','xxxxxxxx') from dual;--十六进制转换十进制
select to_date('2018-05-02 13:14:00','yyyy-MM-dd HH24:mi:ss') from dual;
select to_char(sysdate,'yyyy') "当前",
to_char(to_date('98','yy'),'yyyy') "yy98",
to_char(to_date('08','yy'),'yyyy') "yy08",
to_char(to_date('98','rr'),'yyyy') "rr98",
to_char(to_date('08','rr'),'yyyy') "rr08"
from dual;
select call_id,nvl(condition,0) from t_call_info;--非空返回当前值，为空返回0
select call_id,nvl2(condition,0,1) from t_call_info;--非空返回0，为空返回1
select call_id,
case callnum
    when 0 then '零'
    when 1 then'一'
    else called_flag
end cal
from t_call_info ;--分支
select call_id,
case
    when callnum=0 then '零'
    when callnum=1 then '一'
    else called_flag
end as cal
from t_call_info ;
select call_id,
decode( callnum
    , 0 , '零'
    , 1 , '一'
    , called_flag) cal
from t_call_info ;--decode oracle自定义的
select sum(callnum),min(callnum),max(callnum),avg(callnum),count(callnum) from t_call_info;
select call_id,sum(callnum) from t_call_info group by call_id;--分组
select call_id,avg(callnum) from t_call_info group by call_id having avg(callnum)>1;--having是在结果中再次筛选。gaving一定得出现在group by 子句得后面。不能独立存在。
select call_id,avg(callnum) from t_call_info where called_id='5702' group by call_id having avg(callnum)>1;

select * from t_call_info where exists (select call_id from t_call_info where called_id like 'EE%');

--并集，union 不重复，union all可重复

select * from TEST_TRAN_INFO
union
select * from TEST_TRAN_INFO2;
select * from TEST_TRAN_INFO
union all
select * from TEST_TRAN_INFO2;

--层次结构的数据查询

select call_id ,called_id from t_call_info start with call_id ='02640' connect by nocycle prior call_id = called_id;