oracle SQL语法总结

SQL

增

create

#创建新表
CREATE TABLE Persons
(
Id_P int,
Name varchar(255),
City varchar(255)
)

# 有条件的复制表
create table tableA as select * from tableB where name='张三'

# 仅复制表结构
create table tableA as select * from tableB where 1=2

insert

# 插入一行
insert into tableA(ID,NAME)
values (1,'张三');

# 批量插入
insert into tableA(ID,NAME)
select ID,NAME from tableB;

alter

# 增加字段
ALTER TABLE table_name
ADD column_name datatype

删

# 删除表
drop table tableA

# 清空表
truncate table tableA

#删除表中特定行
delete from tableA where name='张三'

#删除字段
alter table tablename drop (column)

改

# 修改数据
update tableA
set date1 = sysdate
where name ='张三'

# 同步数据
update tableA
set ID=(select ID from  tableB where name='张三')
where name='张三' 

# 同步数据
update tableA
set ID = (select ID from tableB where tableA.name=tableB.name)
where ID is null

查

group by

select name,sum(orderprice) as sum_price from tableA
group by name

having

# 相当于在 groupby 的基础上再筛选一次
select name,sum(orderprice) as sum_price from tableA
group by name
having sum(orderprice)>100

case when

# 一般的case when语法
select product_id,product_type_id,
case when product_type_id=1 then 'Book'
when product_type_id=2 then 'Video'
when product_type_id=3 then 'DVD'
when product_type_id=4 then 'CD'
else 'Magazine' end as type_name
from products 

# 有条件的统计
select 
sum(case when name='张三' then 1 else 0 end) as COL1
from tableA

join

select * from 
(select cola1,cola2 from tableA)tableA
left join
(select colb1,colb2 from tableB)tableB
on tableA.cola1=tableB.colb1

union

数据表上下拼接，
若有重复行union只会保留一行，
若有重复行union all 会保留所有行

select id,name from tableA
union 
select id,name from tableB


select id,name from tableA
union all
select id,name from tableB

字符串处理 (拼接，截取，查找，替换)

拼接

# 拼接(concat只能连接两个，当连接多个时，需要嵌套使用)
select 'aaa'||'bbb'||'cccc' as newstr from dual
select concat('aaa','bbb') as newstr from dual
select concat(concat('aaa','bbb'),'ccc') as newstr from dual

截取

1) substr("ABCDEFG", 0); //返回：ABCDEFG，截取所有字符 
2) substr("ABCDEFG", 2); //返回：CDEFG，截取从C开始之后所有字符 
3) substr("ABCDEFG", 0, 3); //返回：ABC，截取从A开始3个字符 
4) substr("ABCDEFG", 0, 100); //返回：ABCDEFG，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。 
5) substr("ABCDEFG", -3); //返回：EFG，注意参数-3，为负值时表示从尾部开始算起，字符串排列位置不变。

查找

INSTR（string,subString,position,ocurrence）
string：源字符串
subString：要查找的子字符串
position：查找的开始位置
ocurrence：源字符串中第几次出现的子字符串

select INSTR('helloworldhelloword','wo', 3, 2) as loc from dual 

替换

select replace('aaabbb', 'bbb', 'ccc') from dual //返回aaaccc,把bbb换成ccc

select replace('a b c',' ','') from dual //返回'abc', 去除所有空格
select trim(' a b c ') from dual //返回'a b c', 去除左右两边的空格

select REGEXP_REPLACE('Ab 1234567890', '[^0-9]','x') from dual //返回'xxx1234567890',把非数字替换成x(包括空格)
select REGEXP_REPLACE('Ab 1234567890', '[0-9]','x') from dual //返回'Ab xxxxxxxxxx',把数字替换成x

日期处理

日期字符转换

select to_char(sysdate,'yyyy-mm-dd') from dual //日期转字符
select to_date('2019-12-31','yyyy-mm-dd') from dual //字符转日期

日期操作

1)当前时间
select sysdate from dual

2)日期上增加整数个月
select add_months(sysdate,1) from dual

3)本月第一天(add_months增加一个月可得下一个月的第一天,再减一天可得本月的最后一天)
select trunc(sysdate, 'month') from dual

4)今年的第一天
select trunc(sysdate,'yy') from dual

5)返回 年/月/日
select extract(year from sysdate) from dual
select extract(month from sysdate) from dual
select extract(day from sysdate) from dual

6)全月第几天(1-31)
to_char(sysdate,'DD')

7)全年第几天(1-366)
to_char(sysdate,'DDD')

高阶操作

行转列

已有表：

name	subject	point
张三	语文	60
张三	数学	70
张三	英语	80
李四	语文	65
李四	数学	75
李四	英语	85

目标表：

name	语文	数学	英语
张三	60	70	80
李四	65	75	85

select 
name,
sum(decode(subject,'语文',point,null)) as 语文,
sum(decode(subject,'数学',point,null)) as 数学,
sum(decode(subject,'英语',point,null)) as 英语
from
(
select * from table
)
group by name

循环loop

begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
  for i in reverse 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

declare
  i binary_integer := 1;
begin
  while i < 11 loop
     dbms_output.put_line(i);
     i := i + 1;
  end loop;
end;