Oracle常用语法总结

创建table

create table TABLENAME 
(col_name1 number not null, col_name2 varchar2(30) not null)

#描述数据表
describe TABLENAME;

修改列名+ 列属性

alter table TABLENAME rename column COL_NAME_BEFORE to COL_NAME_AFTER;

alter table TABLENAME modify (COL_NAME NEWTYPE)
alter table table_test modify (colname1 varchar2(15)) #example

增加列+删除列

alter table TABLENAME add (COL_NAME TYPE)
alter table table_test add (colname1 varchar2(15))

alter table TABLENAME drop column COL_NAME

删除表以及相关表约束

drop table TABLENAME cascade constraints

临时表

临时表数据被清理的条件：
- 事务提交或回滚 commit/rollback
- 会话结束 （当前界面窗口如sql window或command window）

#会话临时表
create global temporary table TABLENAME (COL_NAME1 TYPE1, COL_NAME2 TYPE2) on commit preserve rows;
#事务临时表
create global temporary table TABLENAME (COL_NAME1 TYPE1, COL_NAME2 TYPE2) on commit delete rows;

Dual表的应用场景

select sysdate from dual
select 1+2*3 as result from dual

主键+外键+唯一性约束

create table TABLENAME 
(col_name1 number not null, col_name2 varchar2(30) not null,
constraint pk_col primary key(col1,col2))
#为已存在的表创建主键
alter table TABLENAME modify(COL_NAME TYPE primary key)
alter table TABLENAME drop primary key
alter table TABLENAME drop constraint pk/fk/unique_name
alter table TABLENAME disable primary key
alter table TABLENAME disable constraint pk/fk/unique_name
alter table TABLENAME enable primary key
alter table TABLENAME rename constraint name1 to name2
#唯一性约束
create table TABLENAME 
(col_name1 number not null, col_name2 varchar2(30) unique,
constraint pk_col primary key(col1,col2))
alter table TABLENAME add constraint name unique(col_name)
#检查性约束
create table employees (employee_id number primary key, grade varchar2(20), salary number, 
constraint chk_salary check(grade in 'MANAGER', 'LEADER', 'STAFF') and 
(grade = 'MANAGER' and salary < 8000 or grade = 'LEADER' and salary < 5000 or grader = 'STAFF' and salary < 3000))

#默认值约束
create table employees (employee_id number primary key, grade varchar2(20), salary number, e_date date default sysdate)

外键约束使得修改主表或者外键表受到一定程度的外键列约束。比如删除主表里customer_id = 1的，如果orders表里也有customer_id = 1的列，就无法执行

#建立orders表到customers表的外键关联
alter table orders add constraint fk_orders_customers foreign key(customer_id) references customers(customer_id) 

• 级联更新

alter table orders add constraint fk_orders_customers foreign key(customer_id) references customers(customer_id) deferrable initially deferred

update customers set customer_id = 3 where customer_id = 1
update orders set customer_id = 3 where customer_id = 1
commit 

• 级联删除

alter table orders add constraint fk_orders_customers foreign key(customer_id) references customers(customer_id) on delete cascade

delete from customers where customer_id = 3
#主外表均被删除

视图

视图用于存储查询，但不存储数据。通过修改、更新视图可以修改基础数据表中和视图直接相关的列表。

create or replace view vw_employees as select employee_id, 
last_name||first_name as employee_name, 
province || '-' || city as location, salary from employees;

lpad() / rpad()

select lpad('21',6,'0') stock_code from dual;
#return 000021
select lpad('1234567',6,'0') stock_code from dual;
#return 123456

lower() / upper() / initcap() / length()

substr()

select substr('1234567890',5,4) from dual;
#return 5678 从第五位开始的4个字符。index从1开始。
select substr('1234567890',5) from dual;
#return 567890

instr()

select instr('big big tiger','big') from dual;
#return 1 返回big首次出现的位置
select instr('big big tiger','big',2) from dual;
# return 5 从第二个字符开始，返回找到的big首次出现的位置
select instr('big big tiger','big',2,2) from dual;
# return 0 从第二个字符开始，返回找到的big第二次出现的位置

ltrim() / rtrim() / trim()

to_char()

select to_char(0.96,'9.99') from dual;
#return .96 9代表0-9任意数字
select to_char(0.96,'0.00') from dual;
#return 0.96 0可保留数字为0的数
select to_char(5897.098,'$/U999,999,999.000') from dual;
#return $/¥5,897.098
select to_char(sysdate,'yyyy-mm-dd') from dual;
#return 2020-04-11

abs() / round() / ceil() / floor() / mod() / sqrt() / power()

sign()

select sign(8) from dual;
#return 1
select sign(-8) from dual;
#return -1
select sign(0) from dual;
#return 0

to_number()

select to_number('257.90') from dual;
#return 257.9

to_date()

select to_date('12/02/09','mm/dd/yy') from dual;
#return 2009-12-02

add_months()

select to_char(add_months(sysdate,2),'yyyy-mm-dd') from dual;
#return 2020-06-11

last_day()

select to_char(last_day(sysdate),'yyyy-mm-dd') from dual;
#return 2020-04-30

months_between()

select months_between(sysdate,to_date('2020-08-01','yyyy-mm-dd')) from dual;
#return -3.643100358422939

current_date

select sessiontimezone, to_char(current_date,'yyyy-mm-dd hh:mi:ss') from dual;

extract()

select extract(year from sysdate) from dual;

nvl()

处理空值

select employee_id, nvl(employee_name, '未知') employee_name,
nvl(sum(s.salary),0) salary from t_employees;
#如果employee_name值为空，返回未知
#如果sum(salary)值为空，返回0

cast()

强制转换列的数据类型

create table tmp_salary as 
select cast(salary_id as varchar2(20)) salary_id,
cast(salary as varchar2(20)) salary
from t_salary

between/in/like/is null/exists

all/some/any

select * from t_salary where salary > all(select distinct salary from t_salary where employee_id = 4 or employee_id = 5);
#salary > 6000 and salary > 7000
select * from t_salary where salary > some/any(select distinct salary from t_salary where employee_id = 4 or employee_id = 5);
#salary > 6000 or salary > 7000

decode()

类似于if elseif else

select e.employee_id, e.employee_name, decode(sign(avg(s.salary)-6000),'1','high_income', '0','avg_income','low_income') incoming
from t_employees e, t_salary s
where e.employee_id = s.employee_id
group by e.employee_id, e.employee_name

rank() / dense_rank() / row_number()

select student_name, rank() over(order by student_age) position from students;
#return 1 2 2 4 4 4 7
select student_name, dense_rank() over(order by student_age) position from students;
#return 1 2 2 3 3 3 4
select student_name, row_number() over(order by student_age) position from students;
#return 1 2 3 4 5 6 7

partition by

select t.*, dense_rank over(partition by department order by salary) position 
from salary t
order by t.employee_id

### !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
select t.*, 
sum(salary) over(partition by department) total_salary,
round(avg(salary) over(partition by department)) average_salary
from salary t
order by t.employee_id
##与group by不同的是输出形式，这个可以保留其他列，新增列会有重复显示。

rows子句

select employee_id, employee_name, sum(salary) over(order by employee_id rows between 1 preceding and 1 following) three_total 
from salary
#如不足3条，则返回有的条数的运算结果
#参见14.8

range子句

select employee_id, employee_name, count(1) over(partition by department order by salary range between 500 preceding and 500 following) employee_count 
from salary
order by employee_id
#参见14.8

first_value() / last_value()

取得第一行的数据

select distinct department, first_value(employee_name) over(partition by department order by salary) employee_name,
first_value(salary) over(partition by department order by salary) salary
from salary
#参见14.8

lead()

向下取

#增加一列next_name记录按部门划分薪资排序每个雇员后面的雇员名（薪资高一位）
select employee_id, employee_name, salary, lead(employee_name,1,'N/A') over(partition by department order by salary) next_name
from salary 
order by employee_id
#参见14.8

lag()

同lead()，向上取

greatest()

函数

## 创建函数
create or replace function hello_world()
return varchar2 as
begin 
	return "Hello World!";
end hello_world;

## 调用函数输出返回值
set serverout on;
declare msg varchar2(20);
begin 
	msg := hello_world();
	dbms_output.put_line(msg);
end;

## Or
select hello_world msg from dual;

## Ex.2
create or replace function get_tax(p_salary number) 
return number as
begin 
	declare tax_salary number;
	begin 
		tax_salary := p_salary - 2000;
		if tax_salary <= 0 then
			return 0;
		end if;
		if tax_salary <= 500 then 
			return tax_salary*5/100;
		end if;
	end;
end get_tax;

## 存储过程
## in参数：可传入数值，但不可在存储过程中更改值
## out参数：可存入数值并输出
create or replace procedure update_students(in_age in number, out_age out number) as 
begin 
	update students set student_age = in_age;
	select student_age into out_age from students where student_id = 1;
	commit;
end update_students;

## 调用
set serverout on;
declare update_age number;
begin
	update_students(20,update_age);
	dbms_output.put_line(update_age); //return 20;
end;

## in out 参数
create or replace procedure swap(inOut_param1 in out number, inOut_param2 in out number) as 
begin 
	declare param number;
	begin
		param := inOut_param1;
		inOut_param1 := inOut_param2;
		inOut_param2 := param;
	end;
end swap;

其他

##查询雇员姓名中第二个字母是A的所有雇员
select * from emp where ename like "_A%"

2. 空字符和null在查询时都需要通过is null来查询