oracle

show user;
show parameter instance_name;

查看字符编码

select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

desc student
rlwrap sqlplus test/test@orcl;

修改用户权限

GRANT
　　CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
　　ALTER ANY TABLE, ALTER ANY PROCEDURE,
　　DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
　　SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE,
SELECT ANY SEQUENCE, CREATE ANY SEQUENCE,DROP ANY SEQUENCE，create trigger
TO DW_TEST;

增加表空间大小

ALTER TABLESPACE DW_TEST_TBS ADD DATAFILE '/opt/oracle/oradata/biee/DW_TEST_TBS02.dbf' SIZE 7167M AUTOEXTEND ON NEXT 3072M MAXSIZE 32767M;

给用户授权多个表空间，并创建表

alter user test quota unlimited on tushu;
create table a(a varchar2(3)) tablespace tushu;

create table books(id number not null,name varchar2(50),author varchar2(20),isbn varchar2(50),publisher varchar2(20),publishdate date,qty number,constraint books_pk primary key(id));

select 3*3 from dual;

查询用户的所有表,用户名必须大学

select table_name from all_tables where owner='TEST';

修改表的列

alter table books add a varchar2(3) not null;
alter table books drop column a;

列限制

alter table books modify qty number check(qty<100 and qty>0);

insert into books values(xx);

default sysdate;

--
虚拟列，用来计算
comsal number(7,2) generated always as (col1*col2) virtual

alter table books_lib drop constraint xxx;

alter table books drop column id cascade constraint;

alter table books set unset id cascade constraint;
alter table books drop unset columns;

重命名

alter table books rename column qty to books_qty;

重命名表

rename books to book;

drop books;

闪回

flashback table books to before drop;

彻底删除

drop table books cascade constraints purge;

约束：
not null
unique
primary key
foreign key
check

constraint fk_name foreign key(xx) references books(id) on delete cascade

to_date('2010-10-10','YYYY-MM-DD')
col xx for a20

alter table books drop constraint bookxxx

create index index_name on student('name');
https://www.cnblogs.com/liangyihui/p/5886619.html

创建视图

create or replace view vstu as select * from student;

重新编译视图

alter view v_ss compile;

~物化视图

更改会话级别的日期显示

alter session set nls_date_format='YYYY-MM-DD HH:MI:SS';

select distinct dname from dept;
select dname from dept group by dname;

字符串连接
select 'aa' || 'bb' from dual;

between 1000 and 2000
not between 1000 and 2000

and or
a in (1,2,3,4)
<> !=

like 'a%'

a is null

order by desc/asc

rownum

case job
when 'a' then
1.1sql
when 'b' then
1.2sql
else
sql
end "aa"

多表查询基本语法

select t1.,t2. from t1,t2 where t1.name=t2.name

t1 inner join t2 on t1.name=t2.name

t1 left join t2 on
t1 right join t2 on
t1 full join t2 on

in any all

group by
distinct
sum,avg,min,max,count

nvl(col1,0)

group by xx
having xx
order by xxx
--

小计用法rollup

select group_id,sum(salary) from group_test group by rollup(group_id);
select group_id,job,sum(salary) from group_test group by rollup(group_id, job);

小计，总计 cube

select group_id,sum(salary) from group_test group by cube(group_id);
select group_id,job,sum(salary) from group_test group by cube(group_id, job);

update sss set a=1 where a=2
rollback

内置函数

image.png

round

substr,length,upper,lower,round,to_date,nvl