alter session set nls_date_language=’american’;
alter session set nls_date_format=’yyyy-mm-dd’;
复制代码

alter table stu_table rename to stu_tbl;--修改表名
alter table stu_tbl rename column stu_sex to sex;--修改列名
alter table stu_tbl add (stu_age number);--添加新列
alter table stu_tbl drop(sex);--删除列
alter table stu_tbl modify(stu_sex varchar2(2));--更改列的数据类型
alter table stu_tbl add constraint pk_stu_tbl primary key(id);--添加约束
复制代码

create table stu_tbl_log as select id,stu_name,stu_age from stu_tbl;--选择无重复的行
select distinct stu_name from stu_tbl;--插入来自其他表中的记录
insert into stu_tbl_log select id,stu_name,stu_age from stu_tbl;
复制代码

select (3+2)/2 from dual;--算术操作符，结果：2.5
select * from stu_tbl where stu_age>=20;--比较操作符
select * from stu_tbl where stu_name like '%a%';--比较操作符：like
select * from stu_tbl where stu_name like 'a___';--比较操作符：like
select * from stu_tbl where stu_age in(20,30);--比较操作符：in
select * from stu_tbl where stu_age between 20 and 30;--比较操作符：between
select stu_name from stu_tbl union all
select stu_name from stu_tbl_log;--集合操作符：union all
select stu_name from stu_tbl union
select stu_name from stu_tbl_log;--集合操作符：union
select stu_name from stu_tbl intersect
select stu_name from stu_tbl_log;--集合操作符：intersect
select stu_name from stu_tbl minus
select stu_name from stu_tbl_log;--集合操作符：minus
复制代码

select id,stu_name||' '||stu_sex as name_sex,stu_age
from stu_tbl;--连接操作符||
复制代码

select u.user_name,sum(oi.order_num*oi.order_price) as total,row_number() over (order by sum(oi.order_num*oi.order_price) desc) as sort from order_item_tbl
  oi,user_tbl  u,order_tbl  o where oi.order_id = o.id and o.user_id = u.id group by u.user_name;