Oracle速查手册

create user [用户名] identified by [密码];--创建用户
         --如何修改用户密码：
  alter user [用户名] identified by [新密码]
2.grant connect to [用户名]--赋予用户登录权限
  grant create session to [用户名]
3.grant resource to [用户名] --赋予CRUD
4.grant [权限命] on[表明]  to [用户名]
  revoke [权限命] on[表明] from [用户名]
  grant [权限命] on[表明]  to [用户名] with grant option
 [权限包括] select，update，insert，delete，all

--[数据类型]
 number   -10 38次方---  10 38次方
 char     定长，最多存储2000字符，查询效率高
 varchar2 可变长，最多存储4000字符，查询效率低
 clob     最大存储4G
 blob     存储二进制数据，4G
 
 create table [表名](
        字段 数据类型[not null or primary key，如果没有not null，默认可以为空] ,
        字段 数据类型[not null or check ，如果没有not null，默认可以为空] ,
        字段 数据类型[not null or unique ，如果没有not null，默认可以为空] 
 )
 修改表的操作
 添加字段
 alter table [表名] add (字段名，数据类型)
 删除字段
 alter table [表名] drop column [字段名]
 修改字段
 alter table [表名] modify (字段名，数据类型)
 给已有的表重命名
 rename [原表名] to [新表名]
 添加主键
 alter table [表名] add constraint [约束名] primary key(字段);--如果有多个，称之为联合主键
 添加外键
 alter table [从表名] add constraint [约束名] foreign key (从表名的外键字段) references 主表(主键字段) on delete cascade
      
 删除约束
 alter table [从表名] drop constraint [约束名] 
 创建表
 create table [表名] as select * from [表2] -- 复制表2 
 create table [表名] as select * from [表2] where 1=2 --复制表结构
 create table [表名] as select [字段1],[字段2] from [表名]
 插入数据
 insert into [表名] values (值1,值2,.......值n);
 insert into [表名] (字段1,字段2,....字段n) values (值1,值2,.......值n);
 insert into [表名] select [字段1，字段2，.....字段n] from [表名] where 字段='值'；
 insert into [表名] ([字段1，字段2，.....字段n]) select [字段1，字段2，.....字段n] from [表名] where 字段='值'；
 insert all into [表名1] values (1004,'haha') 
             into [表名2] values (1005,'heihei') select * from dual;
 ---删除数据
 delete from [表名] where 字段='值'; --速度慢，要写日志：把日志写在回滚表中
 truncate table [表名] --速度快，不需要写日志
 drop table [表名]
 drop user [用户名] cascade  
 drop view [视图名]
 
 
 修改数据
 update [表名] set [字段]='值' where 字段='值';
 update [表名] set [字段]='值' and [字段1]='值'  where 字段='值';
 
 查询数据
 select 字段1，字段2，......字段n from [表名];
 select 字段1，字段2，......字段n from [表名] where 字段='值';
 select 字段1，字段2，......字段n from (select 字段1，字段2，
 ......字段n from [表名] where 字段='值' ) where 字段='值'
 select 字段1，字段2，......字段n from [表名] where 字段 = select 字段1 from [表名];
 组函数--聚合函数
 MAX,MIN,AVG,COUNT,SUM
 常用的函数
 substr()  upper() lower() instr() lpad() rpad() trim() 
 to_char() to_date() trunc() round() nvl() nvl2()
 initcap() next_day() length() replace() mod()
 ------------------
 case
  when 表达式 then 值1
  when 表达式 then 值2
  else 默认值
  end 
  
  decode(字段,
       '值1','值1结果',
       '值2','值2结果',
       '值3','值3结果',
       '默认值'
       )
 -----case 和 decode 区别：decode只能判断等值的表达式
            case可以判断区间表达式
  联合查询
  左连接：
  select * from [表名1] left outer join [表名2] on 表名1.字段=表名2.字段
  select * from [表名1],[表名2]  where 表名1.字段=表名2.字段(+)
  右连接
  select * from [表名1] right outer join [表名2] on 表名1.字段=表名2.字段
  select * from [表名1],[表名2]  where 表名1.字段(+)=表名2.字段
  全链接
  select * from [表名1] full outer join [表名2] on 表名1.字段=表名2.字段 
  
  ---去重复，(有重复的数据只显示一次)
  select * from emp20
  union 
  select * from emp
  ---不去重复(有重复的数据全部显示)
  select * from emp20
  union all 
  select * from emp
  
  ---(差集[emp20中除了交集以外的部分])
  select * from emp20
  minus 
  select * from emp
  
  ---交集
  select * from emp20
  intersect 
  select * from emp
  
  分组:group by 
  排序:order by desc降序 默认升序  asc
  分组之后过滤:having
  单行过滤:where
  
  select * from emp
  where .....
  group by .....
  having ......
  order by .....
  
  使用 group by 要注意的事项：
  select 后面的字段必须由：组函数或者是分组的字段
  
  ---分页
  select * from 
  (select rownum no, e.* from 
          (select * from emp order by sal desc) 
      e where rownum <=10) where no >1;
      
  --- 创建视图

  CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view[(aliaslist)]
  AS subquery
  [WITH CHECK OPTION [CONSTRAINT cname]
  [WITH READ ONLY]
  OR REPLACE表示如果视图已经存在则重新创建
  FORCE表示创建视图而不管基表是否存在，NOFORCE表示只有基表存在的情况下才创建视图（默认情况）
  view表示视图名
  aliaslist表示别名列表（别名之间用逗号隔开，别名的个数需要和子查询中选择的字段或表达式的个数一致）
  subquery表示子查询
  WITH CHECK OPTION表示只有可访问的行在视图中才能被插入或更新
  cname表示为约束指定的名称
  WITH READ ONLY表示在该视图中不可执行DML操作
  
  insert into [视图名] values('值1','值2',.....'值n');//此数据插入视图对应的表中
  update [视图名] set [字段]= '值' where [字段]= '值';
  delete from [视图名] where [字段]= '值';
  
 如果视图中包含下面的内容，就不可以从视图中删除数据
  组函数
  GROUP BY子句
  DISTINCT关键字 
  伪列ROWNUM关键字
 如果视图中包含下面的内容，就不可以在视图中更新数据：
  组函数
  GROUP BY子句
  DISTINCT关键字
  伪列ROWNUM关键字
  用表达式定义的字段
 如果视图中包含下面的内容，就不可以在视图中插入数据：
  组函数
  GROUP BY子句
  DISTINCT关键字
  伪列ROWNUM关键字
  用表达式定义的字段
  基本中的NOT NULL字段不在视图中
  /*
     创建序列
  */
 create sequence seq_semple
        increment by 1
        start with 1
        maxvalue 9999
        nocycle
        nocache
  -- 修改序列
  alter sequence seq_semple
        increment by 2
        maxvalue 9999
        nocycle
        nocache
insert into test values(seq_semple.nextval,'小明','123');
insert into test values(seq_semple.nextval,'小花','1234');
insert into test values(seq_semple.nextval,'小丽','234');
insert into test values(seq_semple.nextval,'小红','12345');

commit;
select * from test;

select seq_semple.nextval, seq_semple.currval from dual;
select seq_semple.currval from dual;

--删除序列
drop sequence seq_semple;
/*
     索引
*/
--创建索引

create table t(
       tid number primary key,
       tname varchar2(20),
       tel number unique not null--默认自动创建索引
)
--创建索引
create index i_test1
on test(tname,tpsw)
--删除索引
drop index i_test1;

--系统权限
     create user jm identified by jm123;
     create user tom identified by tom123;
     --让用户jm需要登录；并且让用户同时具有赋权限的功能
     grant create session to jm with admin option
     --让jm给tom赋权限（登录）
     grant create session to tom;
     --收回jm的登录权限，但是，tom依然可以登录；(不是级联回收)
     revoke create session from jm;
--对象权限 emp表在scott【方案】，给mqx用户赋予访问(select)emp表的权限 
     grant select on scott.emp to mqx;
     --给jm赋予查emp表的权限，同时权限下放
     grant select on scott.emp to jm with grant option;  
     grant select on scott.emp to tom;
     --回收jm的访问emp表的权限(tom不能访问到emp表，证明是级联回收)
     revoke select on emp from jm;
     
--如何创建角色
     create role joy;
     --给角色授权
     grant create table,create view,create sequence,
           create procedure to joy;
     --给用户授予角色
     grant joy to jm;
--创建存储过程
     --public void show(){System.out.println();}
     create or replace procedure p_pro
     is
     begin
            insert into test values(2,'狗狗','234');
     end;
     --执行存储过程
     begin
     p_pro;
     end;
     /*public static void show(String msg){
              System.out.println(msg);  
     }
     public static void main(String args[]){
            String msg = "Hello World";
            show(msg);
     }
     
     */
     
     --有参数的存储过程
     create or replace procedure sp_pro(
            iid number,
            iname varchar2,
            ipass varchar2
     )
     is
     begin
            insert into test values(iid,iname,ipass);
     end;
     --执行存储过程
     begin
           sp_pro (3,'猪猪','345');
     end;
     --根据ID查找动物的名字；
     create or replace procedure sp_p
     (
            iid in number,
            outname out varchar2,
            outtpsw out number
     )
     is
     begin
            select tname,tpsw into outname,outtpsw from test where tid=iid;
     end sp_p;
     /*
            int i =0;
            String str = "a";
            String s;
            Scanner input = new Scanner(System.in);
            s = input.next();
     */
     declare
          oname varchar2(20);--接受返回来的值
          outpsw varchar2(20);
          iid number:='&请输入要查询的ID'; 
     begin
          sp_p(iid,oname,outpsw); 
          dbms_output.put_line('动物的名称：'||oname||'动物的编号：'||outpsw);    
          --System.out.println("Hello World!");
          exception
          when no_data_found then
          dbms_output.put_line('朋友输入的数据有误，请重新输入');      
     end;
     commit;
     select * from test;
     truncate table test;
--删除存储过程
     drop procedure [存储过程名];
--查询所有存储过程  
     select * from user_objects where
      object_type='PROCEDURE';
      
--创建函数
      create or replace function f_m1
      return varchar2
      is
      begin
             return '我真是一个天才啊！';
      end;
      /*
      public String findById(int id){
             String msg=null;
             if(id==1){
                    msg="星期一";
             }else if(id==2){
                    msg="星期二";
             }
             return msg;
      }
      */
      --执行函数
      select f_m1 from dual;
      create or replace function f_m2(idd number)
      return varchar2
      is
      outname varchar2(20);
      begin
              select tname into outname from test where tid=idd;
              return outname;
      end;
      --执行函数【命令窗口执行】
      var sname varchar2;
      call f_m2(2) into:sname;
  --查询所有函数名称
  select object_name from user_objects 
  where object_type='FUNCTION';
  --创建触发器
  create or replace trigger t_test
  before insert on test
  for each row
  begin
       --dbms_output.put_line('用户名'|| :new.tname);
       --dbms_output.put_line('密码'|| :new.tpsw);
       insert into msgtable values (seq_se.nextval,'宠物名：'||:new.tname||'宠物编号'||:new.tpsw);
   end;
   --调用存储过程向test表中插入数据
   begin
   sp_pro(13,'美国摩登考拉No.1','888');
   end;
   alter table msgtable modify (msg varchar2(50));
       commit;
   --删除触发器
   drop trigger t_test;
   select * from msgtable;
   /*
   游标
   */
   declare
   cursor cul is select * from test;
   srow test%rowtype;
   begin
      open cul;
      fetch cul into srow;
        while cul%found loop 
        dbms_output.put_line(srow.tid||srow.tname||srow.tpsw);       
        fetch cul into srow;
           end loop;
         close cul;
   end;
  
  declare
  v_num number:=10086;
  v_context varchar2(20) default '本人很聪明！';
  v_ip constant number(3,1):=10.1;
  v_sal emp.sal%type;
  v_emp emp%rowtype;
  begin
        v_context:='我们都是好孩子';
        dbms_output.put_line(v_context);
  end;
  declare
  v_row emp%rowtype;
  begin
  select * into v_row from emp where empno = 7369;
       dbms_output.put_line(v_row.ename);
  end;
  declare
  age number:= 20;
  begin
    age:='&请输入你的年龄';
       if age<20 then
          dbms_output.put_line('我还不够法定结婚年龄！');
       else if age >22 then
          dbms_output.put_line('我刚大学毕业！');
       else 
          dbms_output.put_line('我该的啦！');
       end if;
       end if;      
  end;