oracle常用语句

 

--创建表---
create table usermess(
       names varchar2(20) not null,
       age int not null
      
);
---给表中添加列---
alter table usermess add(
      email varchar2(50)
);
---修改表中列的属性---
alter table usermess modify(
      email varchar2(20)
);
---设置该表的列为不可用状态--
alert table usermess set unused(age);
---删除中的列---
alter table usermess drop(age);
----修改表的名字----
rename usermess to myinfo;
---删除表---
drop table myinfo;
---删减表--
delete from myinfo;
---查看表中不重复的字段--
select distinct(job) from emp;

select * from emp where sal between 1000 and 2500;

select * from emp where sal in(1000,1250);

select * from emp where mgr is null;

select * from emp where sal>2500 or job='manager';

select * from emp where deptno='20' order by sal desc;

select * from emp where deptno='30' order by sal desc,comm asc;

select * from emp order by sal desc;
---多行子查询（in and）---
select * from emp where job in (select distinct job from emp where deptno = 10) and deptno = 10;
---大于部门为30的最高工资，其他部门数据---
select * from emp where sal>all (select sal from emp where deptno =30);
---除了比部门为30的最低工资，任意其他部门数据---
select * from emp where sal>any (select sal from emp where deptno =30);
----多列子查询----
select * from emp where (deptno,job)=(select deptno,job from emp where ename = 'SMITH');
SELECT * FROM DEPT;
----相关子查询----
--（效率好）--
select * from emp where exists (select 1 from dept where dept.deptno = emp.deptno and dept.loc ='NEW YORK');
----连接查询----
--（内连接）--
select * from emp,dept where emp.deptno = dept.deptno;
select * from emp e,dept d where d.deptno = e.deptno and d.deptno=20;
select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
select * from emp manger,emp worker where manger.empno = worker.mgr and worker.ename='MARTIN';
select * from emp e inner join dept d on d.deptno=e.deptno and d.deptno=20;
----内置函数---
select ceil(13.2) from dual;
select floor(13.2) from dual;
select mod(10,3) from dual;
select power(-2,3) from dual;
select round(3.15454) from dual;
select sqrt(10) from dual;
select trunc(45.92),trunc(45.92,1),trunc(45.92,-1) from dual;--*
select abs(-12.555) from dual;
select acos(0.3) from dual;
select asin(0.8) from dual;
select atan(8.3) from dual;
select atan2(18,3) from dual;
select cos(0.5) from dual;
select exp(4) from dual;
select ln(4) from dual; --*
select log(2,8) from dual;
select sign(-1),sign(0),sign(1) from dual;
select sin(0.5) from dual;
select tan(45*3.14159265359/180) from dual;
---字符参数----
select instr('goodssdd','d',5) from dual;
select length('lifei') from dual;
select lower('lifei') from dual;
select replace('我是李妃','是','叫') from dual;
select substr('hello',3,5) from dual;
select translate('abcdefgh','abcde','123456') from dual;
select trim('  hello  ') from dual;
---其他函数---
select ascii('a') from dual;
select chr(48) from dual;
select concat('hello ','word') from dual;
select initcap('nice to meet you') from dual;
select lpad('123',10,'*') from dual; ---*
select ltrim('level','l'),ltrim('bee','e') from dual;
select rpad('la',10,'oa') from dual;
select rtrim('morning','ing') from dual;  ---*
select upper('oracle') from dual;
-----日期函数-----
select sysdate ,sysdate+100,sysdate-100,sysdate-DATE'2009-01-01' from dual;
select sysdate today,add_months(sysdate,2) from dual;
select months_between(sysdate,date'2011-11-1') from dual;---相差月数
select current_date from dual;
select extract(year from sysdate) from dual;
select last_day(sysdate) from dual;
select next_day(sysdate,'星期四') from dual;---指定星期几得到那时的日月
------转换函数----
select to_char(n'上述事实上') from dual;
select to_number('￥451.25','L999D99') from dual;
---空函数---
select nvl(comm,0) from emp ---如果指定字段为空时，设置为0
select nvl2(comm,'888',0) from emp---不是空的取第二个，空的取第三个
----聚合函数----
select avg(sal) from emp;
select count(distinct sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select sum(sal) from emp;
-----集合操作符----
select * from emp where sal>2500----并集（去掉重复行，以第一列结果排序）
union
select * from emp where job='MANAGER';

select * from emp where sal>2500----并集不去重复行，不排序
union all
select * from emp where job='MANAGER';

select * from emp where sal>2500----交集，排序
intersect
select * from emp where job='MANAGER';

select * from emp where sal>2500----差集，排序（工资高于2500但不是“manager”的雇员）
minus
select * from emp where job='MANAGER';
-----排序函数------
select ename,sal,rank() over(order by sal desc)"no" from emp; --编号连续
select deptno,ename,sal,rank() over(partition by deptno order by sal desc)"no" from emp;--按部门最高排序
select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc)"no" from emp;--编号连续,且分组
select deptno,ename,sal,row_number() over(partition by deptno order by sal desc)"no" from emp;--无并列,编号连续,且分组
----decode函数（Oracle独家提供） if-then-else ----
select ename,sal,deptno,decode(sal,800,88888,1600,8888888,0) sssss from emp where sal is not null;
select decode(sign(10-20),-1,111,222) from dual;
---case表达式---
select ename||' 属于'|| case deptno when 10 then '财务部'when 20 then '研发部' else 'not found' end from emp;
select ename,sal, case when sal>=5000 then '高层主管' when sal >=3000 then '穷人啊' else '穷人中的穷人啊' end as 你妹的 from emp;
----表分区----
--范围分区
create table student(
       studentid integer not null,
       studentname varchar2(20),
       score integer)
       partition by range(score)(
       partition p1 values less than(60),
       partition p2 values less than(75),
       partition p3 values less than(85),
       partition p4 values less than(maxvalue)
);
drop table student;
----列表分区
create table student(
       studentid integer not null,
       studentname varchar2(20),
       score integer)
       partition by list(score)(
       partition p1 values (10,30,50,70,90),
       partition p2 values (20,40,60,80,100),
       partition p3 values(default)
);
alter table student add partition p5 values less than(95);
alter table student merge partitions p2,p3 into partition p3;--合并
alter table student split partition p3 at (75) into(partition p2,partition p3);
alter table student drop partition p1;
alter table student rename partition p2 to qq;
select count(*) from student partition (p1);
-----事务------
set transaction read only;
insert into student(studentid,studentname,score) values(1,'zhangsan',30);
select * from student;
commit;
--设置事务回滚保持点
insert into student values(1,'zhangsan',100);
savepoint sp1;
insert into student values(1,'lishi',50);
select * from student;
rollback to sp1;
select * from student;
---并发事务
--（三种隔离级别）
set transaction isolation level read committed;
set transaction isolation level read serializable;
set transaction isolation level read read only;

-----共享方式的表封锁
lock table student in share mode nowait;
-----独占方式的表封锁
lock table student in exclusive mode nowait;
-----共享更新封锁方式
lock table student in share update mode nowait;
--悲观锁
create table test(id primary key,name,location,value)as select deptno,dname,loc,1 from dept;
select * from test where id=10 for update;--行级锁(开两个能看出效果)
drop table test;
commit;
----创建表空间
create tablespace "QIDX"
logging
datafile 'd:\sample.ora' size 100M
extent management local--本地管理--
uniform segment space management auto;--磁盘扩展方法（大小可变）--
---创建用户
create user lifei identified by 123456
default tablespace qidx
quota 50M on qidx;
---修改密码
alter user lifei identified by 123456;
--修改空间配额
alter user lifei quota 60M on qidx;
--删除用户(包含数据库对象)
drop user lifei cascade;
----权限-----
grant create session to lifei;
select * from user_sys_privs;--查看当前用户所拥有的系统权限
revoke create session from lifei;--撤销权限
---对象权限（update,select,insert,execute,delete）-
grant select,insert on emp to lifei;--(切换到scott用户)
select *  from scott.emp;--切换到lifei用户，查scott用户的表
revoke select on emp from lifei;--(切换到scott用户,撤销授权)
select * from session_privs;--当前用户权限
select * from dba_roles;--查询角色
create role myRole;--创建角色
grant create session,create table to myRole;--角色授权
grant myRole to lifei;--用户授予角色
select * from role_sys_privs where role='MYROLE';--查询角色系统权限
revoke create session from myRole;--撤销角色权限
drop role myRole;--删除角色
------同义词------
--公有同义词
create or replace public synonym e for scott.emp;--创建一个同义词，指向scott的emp表
select * from e;--查询同义词
drop public synonym e;--删除同义词(必须是系统权限)
--方案同义词
grant create synonym to scott;
create or replace synonym e for scott.emp;--(私有；只有创建者才能查询)
select * from e;
drop synonym e;
---索引--
grant create any index to scott;
create index e_in on emp(ename) pctfree 30 tablespace qidx;--创建索引
set autotrace on explain;--查看执行计划
select ename,empno,sal,deptno from emp where ename='KING';--查询数据
--位图索引(值少)
create bitmap index bit_emp_job on emp(job);
--反向键索引
create index reidx_emp on emp(sal) reverse;
--函数索引
create index funidx_emp on emp(sal+comm+sal*0.2);
select * from emp where (sal+comm+sal*0.2) < 2000;
---索引查询
select * from user_indexes;
select * from all_indexes where table_name='EMP';
select * from dba_indexes where table_name='EMP';
--查询索引列
select * from user_ind_columns;
--修改索引
grant alter any index to scott;
alter index e_in rename to new_emp_index;
--合并索引
alter index new_emp_index coalesce;
--重建索引
alter index new_emp_index rebuild;
--重建索引，转移表空间
alter index new_emp_index rebuild tablespace qidx;
--删除索引
drop index new_emp_index;
---视图---
grant create view to scott;
--单表
create or replace view v_e_d
as
select empno,ename,job,hiredate,deptno from emp;
--多表
create view v_dept_emp
as
select e.deptno,e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
--修改视图(没法修改只是覆盖)
create or replace view v_e_d
as
select empno,ename from emp;
--删除视图
drop view v_e_d;
-----PLSQL编程------
--number
--p>s
create table test(
       vulue number(5,3)
);
insert into test values(84.987);
insert into test values(84.98765);
insert into test values(845.987);--错
insert into test values(0.98765);
insert into test values(84.987654);
insert into test values(1.9876);
select * from test;
drop table test;
--p<s
create table test(
       vulue number(4,5)
);
insert into test values(1);--错（最少小数一个0；整数为0） 
insert into test values(0.1);--错（最少小数一个0）
insert into test values(0.01);
insert into test values(0.001);
insert into test values(0.0001);
insert into test values(0.00001);
insert into test values(0.000001);--大于s位四舍五入
select * from test;
drop table test;
--s<0
create table test(
       vulue number(5,-2)
);
insert into test values(12345); --小数点左两位四舍五入
insert into test values(123456);
insert into test values(1234567);
insert into test values(12345678);--值大于p+s位
select * from test;
drop table test;
------char
----设置字节数或者字符数
--char(1 char);
--char(1 byte);
create table test(
       vulue char(2 char)
);
insert into test values('中');
insert into test values('ss');
select * from test;
drop table test;
-----过程函数申明复合数据类型
---记录
declare
     type myrecord is record
     (
          names varchar2(10),
          age smallint
     );
     mr myrecord;
begin
     mr.names:='张三';
     mr.age:=21;
     dbms_output.put_line('姓名:'||mr.names||'年龄:'||mr.age);
end;
/
----
declare
     type myrecord is record
     (
          no number(2),
          names varchar2(10),
          city scott.dept.loc%type  
     );
     deptrecord myrecord;
begin
     select deptno,dname,loc into deptrecord from dept where deptno=30;
     dbms_output.put_line(deptrecord.no ||':'|| deptrecord.names ||':'|| deptrecord.city);
end;
/
---索引表(往对应索引中放值)
declare
     type mytabletype is table of dept.dname%type not null
     index by binary_integer;
     my mytabletype;
begin
     select dname into my(3) from dept where deptno=20;
     select dname into my(1) from dept where deptno=30;
     dbms_output.put_line('my(3)'|| my(3));
     dbms_output.put_line('my(1)'|| my(1));
end;
/
---可变数组
declare
     type myvarraytype is varray(10) of scott.dept.dname%type not null;
     namearray myvarraytype:=myvarraytype('1','2','3');
begin
     select dname into namearray(1) from scott.dept where deptno=20;
     select dname into namearray(2) from scott.dept where deptno=30;
     dbms_output.put_line(namearray(1));
     dbms_output.put_line(namearray(2));
     dbms_output.put_line(namearray(3));
end;
/
--集合相关函数的运用
declare
     type myvarraytype is varray(10) of scott.dept.dname%type not null;
     v_varray myvarraytype := myvarraytype('1','2','3');
begin
     dbms_output.put_line('最后一个元素值为:'||v_varray(v_varray.last));
     v_varray.extend(2);
     v_varray(5) :='a';
     for i in 1..v_varray.count loop
     dbms_output.put_line(v_varray(i));
     end loop;
end;
/
------PLSQL流程控制-----
--if...then...else...end if
declare
     nsal number;
begin
     select avg(sal) into nsal from emp;
     if nsal > 2000 then
        dbms_output.put_line('ok');
     else
        dbms_output.put_line('no');
     end if;
end;
/
--if...then...elseif...then...else...end if;
declare
     nsal number;
begin
     select avg(sal) into nsal from emp;
     if nsal > 20 then
        dbms_output.put_line('ok');
     elsif nsal > 100 then
        dbms_output.put_line('ye');
     else
        dbms_output.put_line('no');
     end if;
end;
/
--case
declare
     v_sal number(7,2);
begin
     select sal into v_sal from emp where ename='SCOTT';
     dbms_output.put_line('v_sal'||v_sal);
   case
     when v_sal<1000 then
     dbms_output.put_line('scott<1000');
     when v_sal>=1000 and v_sal<2000 then
     dbms_output.put_line('2000>scott>1000');
     else
     dbms_output.put_line('scott>2000');
     end case;
end;
--等值多分枝表(case 后面有值)
declare
     v_deptno emp.deptno%type;
begin
     v_deptno:=&no;
     case v_deptno
      when 10 then
        update emp set comm=100 where deptno=v_deptno;
      when 20 then
        update emp set comm=80 where deptno=v_deptno;
      when 30 then
        update emp set comm=50 where deptno=v_deptno;
      else
        dbms_output.put_line('not have');
      end case;
end;
--loop...end loop
set serveroutput on;   
declare
    v_i number:=1;
    v_s number:=0;
begin
 loop
 exit when v_i>100;
 v_s:=v_i+v_s;
 v_i:=v_i+1;
 end loop;
 dbms_output.put_line(v_s);
 end;
 /
--for..loop..end loop
declare
  v_s number:=0;
begin
  for v_i in 1..100 loop
  v_s:=v_s+v_i;
  end loop;
  dbms_output.put_line(v_s);
end;
---exception
declare
  empjob emp.job%type;
begin
  select job into empjob from emp where empno=0000;
  dbms_output.put_line('这名员工的工作是'||empjob);
 exception
  when no_data_found then
   dbms_output.put_line('工作不存在');
  when too_many_rows then
   dbms_output.put_line('返回了多条数据');
  when others then
   dbms_output.put_line('未知错误');
end;
/
--自定义异常
declare
  pk exception;
  icount int :=0;
begin
  select count(*) into icount from emp where empno='7369';
  if icount >0 then
     raise pk;
  end if;
 exception
  when pk then
   dbms_output.put_line('表中有这条数据了');
end;
/
--系统捕获自定义异常****************
declare
   pk exception;
   pragma exception_init(pk,-1);
   icount int:=0;
begin
  insert into emp(empno,ename,sal) values('7369','qidx',2500);
  exception
   when pk then
     dbms_output.put_line('表中已有这个主键');
     dbms_output.put_line(sqlcode ||':'||sqlerrm(sqlcode));--Oracle中内置的错误函数sqlcode,sqlerrm
end;
/