Oracle语句&函数

oracle语句

ddl语句

create 建表

CREATE TABLE 表名( 
   id   number, 
   name varchar2 (20), 
);  

create 备份表

create table bf as select * from 原表

改列名

alter table 表名 rename column 旧列名 to 新列名;

改列的字段类型

1、有数据情况

方式一（不会改变列的位置）

    ALTER TABLE 表名 ADD 临时列 目标数据类型;  --新建临时列
    UPDATE 表名 SET 临时列= 目标列; --将目标列的数据复制到临时列中
    UPDATE 表名 SET 目标列= NULL; --将目标列的数据清空
    alter table 表名 modify 目标列 目标数据类型; --更改目标列的数据类型
    UPDATE 表名 SET 目标列 = 临时列; --将临时列的数据复制回目标列
    ALTER TABLE 表名 DROP COLUMN 临时列; --删除临时列。

方式二

Alter Table 表名 Add  临时列 新类型; -- 新增一个临时字段 
update 表名 set 临时列 = 旧列; -- 设置 临时列 字段的值为 旧列
alter table 表名 drop (旧列); -- 删除字段旧列
alter table 表名 rename column 临时列名 to 旧列名; -- 修改 临时列名 为 旧列名

2、无数据情况

alter table 表名 modify 列名 新类型;  
alter table 表名 modify(  列名  新类型)

增加列

alter table 表名  add 列名 类型   default 值 ; 

删除列

alter table 表名 drop column 列名

为字段添加注释

comment on  column 表名.列名  is '注释信息';

dml语句

插入

INSERT INTO 表名 (列1, 列2,...) VALUES (值1, 值2,....)  字符用单引号

修改

UPDATE 表名 SET 列名 = 新值 WHERE 列名称 = 某值

删除

DELETE FROM 表名称 WHERE 列名 = 值

复制表数据 insert into select

insert into biao(x1,x2) select x1,x2 from biao;

select语句

解锁复制数据

select * from 表名 for update

distinct

select distinct f.name from food f;

group having

select name as "食物名",count(*) as "数量" from food 
group by name
having count(*) > 10;
--查找数量大于10的食物名

rownum 分页查询

select * from (select rownum hanghao,f.* from food f) t 
where t.hanghao >= 1 and t.hanghao <=10;
--查找前10条food记录

排序查询

select * from food order by id desc nulls last;
--据food的id降序排序，NULL值放到最后。

多表查询

内连接：有对应的就连接 inner join on
左外：左表的都用上 =（+）
右外：右表都用上 right outer join on
全外：两表都用上 full outer join on
交叉：记录数的两表记录数的乘积 cross join on

oracle函数

wm_concat()

wm_concat() 

–实现行转列，即将查询出的某一列值使用逗号进行隔开拼接，成为一条数据。

select name , wm_concat(price) from food group by name;

instr() 找字符位置

instr() 

–字符查找,返回位置。

instr('helhoh','h',2,2)   =   6
--从第二个位置开始找第2个h的位置 ，没有返回0
instr('helhoh','h',-1,1)   =   6
--从倒数第1个位置开始找第1个h的位置 ，没有返回0

ceil

–返回大于等于参数的最小整数

round

四舍五入

保留4为小数
select round('12.25467',4) from dual;  -- 12.2547

to_date

–函数作用：将字符串转换为日期类型

select to_date('2023-05-19 8:54:57','yyyy-mm-dd hh24:mi:ss') from dual;
select * from xx where xx.rq between to_date('2023-11-17 0:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2023-11-18 23:59:59','yyyy-mm-dd hh24:mi:ss')

to_char

–函数作用：将其他类型转换为字符串类型

//sysdate 当前日期时间
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
select to_char(field,'yyyy-MM-dd HH24:mi:ss') from dual
//field  为 date类型字段

concat

concat(expression1, expression2)

–函数作用：字符串拼接函数

select concat('left', 'Right') from dual

注意：也可以利用 || 进行拼接，select ‘a’||‘b’ from dual

over partition by

 over(partition by field_name, order by field_name)

–函数作用：over函数是一个分析函数，和聚合函数搭配在一起使用可以简洁代码

select name, grade, classes,
sum(grade) over(partition by classes) sum,  --某班级总成绩,sum值一直是组内所有成绩的总和
sum(grade) over(partition by classes order by grade desc) sum --加上排序后sum值是按顺序逐步累加的值,越来越大，直到变成组内所有成绩的总和
from biao;

注意：按照以前的写法先进分组统计产生临时表关联原表才可以取到其他信息，现在则不需要了

通常和max(),min(),avg(),sum()等聚合函数一起使用

trunc()

trunc()

–函数作用：是截取日期或数字，根据规则返回指定的值

//截取日期
select  trunc(sysdate,'dd') from   dual ;--返回当前年月日

//截取数值（不四舍五入），trunc(a,b) a参数是源数据，如果b没有就是取整；如果b是正数保留小数点后b位；如果b是负数马，则从小数点左边开始截取。

select trunc(126.567) from dual union all      --126
select trunc(126.567,2) from dual  union all   --126.56
select trunc(126.567,-2) from dual  		   --100

rank()

rank() over(partition by field_name order by field_name)

函数作用：根据分区排名,不连续 1 1 3 4。
gift-price
a-10
a-10
b-20
a-30
b-40

用例：select rank() over(partition by gift order by price) rank_num, t.* from biao t;
rank_num-gift-price
1-a-10
1-a-10
3-a-30
1-b-20
2-b-40

dense_rank()

dense_rank() over(partition by field_name order by field_name)

函数作用：根据分区排名,连续 1 1 2 3。
gift-price
a-10
a-10
b-20
a-30
b-40

用例：select dense_rank() over(partition by gift order by price) rank_num , t.* from biao t;
rank_num-gift-price
1-a-10
1-a-10
2-a-30
1-b-20
2-b-40

row_number()

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
gift-price
a-10
a-10
b-20
a-30
b-40

用例：select row_number() over(partition by gift order by price) rank_num , t.* from biao t;
rank_num-gift-price
1-a-10
2-a-10
3-a-30
1-b-20
2-b-40

截取字符串 substr

select substr('abcde', 2, 3) from dual   --  bcd
位置2开始截取长度为3的字符串
select instr('abcbde', 'b',1,2) from dual -- 4
从位置1 开始查找第二次出现字符 b  的位置
select replace('abcbde', 'b','x') from dual -- axcxde
把所有b替换为x

注意：oracle字符串索引从1开始

trim()

函数作用：去掉左右两端的空白字符

用例：

select trim('    zifuchuan    ') from dual;

注意：仅去掉左边空白字符用ltrim，仅去掉右边空白字符用rtrim

sign(n)

函数作用：取数字n的符号,大于0返回1, 小于0返回-1, 等于0返回0

用例：

select sign(8),sign(-8),sign(0) from dual;

round(number[,decimal])

函数作用：对数字n进行四舍五入处理，保留decimal位小数

用例：select round(100.21),round(100.56),round(100.58,1),round(105.34,-1) from dual;

coalesce(expression1,expression2…)

函数作用：返回表达式中第一个不为空的值，如果全为空则返回空值

用例：

select coalesce(null,6,7) value from dual;

decode()

decode(field_name, value1, new_value1, value2, new_value2, default_value)

函数作用：类似if…else…语句块，针对某个字段，如果它的值为value1,则转换为newValue1，如果值为value2，则转换为newValue2，其他情况显示默认值

replace()

replace(field_name, sub_str, replace_str)

函数作用：将指定的字符串替换为指定的字符串

translate()

select translate('abcd','a','1') from dual;  -- 1bcd
select translate('abcd','a','123') from dual; --1bcd
select translate('abcd','abc','123') from dual; --123d
select translate('abcd','abc','12') from dual; --12d
select translate('abcd','abc','1') from dual; --1d
select translate('abcd','e','1') from dual; --abcd

nvl()

nvl(e1,e2)

返回第一个不为空的参数

nvl2()

nvl2(expression1,expression2,expression3)

e1 非null ，则e2 , null 则e3

case when … then … else … end

select case when name= 'hangbao' then '汉堡' when name = 'kele' then '可乐' else '香蕉' end name_ch from food

行转列

方式一 (group by 聚合函数)

select name,
max(decode(course,'物理'，score,'')) as 物理，
max(decode(course,'化学'，score,'')) as 化学，
max(decode(course,'信息'，score,'')) as 信息 from  biao group by name;

方式二 （oracle 自带 pivot ）

select * from (
select name,course,score from biao
)
pivot (
max(score) for course in ('物理' as 物理, '化学' as 化学 , '信息' as  信息)
);

列转行

方式一 (union all)

select name,'物理' as course,物理 as score from biao1
union all
select name,'化学' as course,化学 as score from biao1
union all
select name,'信息' as course,信息 as score from biao1;

方式二 （oracle 自带 unpivot ）

select name,course,score from(
select name,物理，化学，信息 from biao1
) 
unpivot( 
score for course in (
物理 as '物理',
化学 as '化学',
信息 as '信息'
)
)