提问
 找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,极速登录

Oracle数据库基础

axing 社区微信达人实名认证 番薯互助团队 文档共创团队 互助叫兽、助理编辑
发表于 2018-1-11 16:24:33 | 显示全部楼层 |取消关注该作者的回复
整理文件发现自己之前刚学Oracle时整理的sql基础,分享下。
--[数据类型]
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;


评分

参与人数 2F豆 +76 收起 理由
郁金香 + 10 牛皮!
星痕 + 66 很棒,为分享静神点赞

查看全部评分

此帖共有 107 位番薯登录后查看
回复

使用道具 举报

星痕 社区微信达人番薯互助团队 文档共创团队 互助叫兽、助理编辑、VIP1
发表于 2018-1-11 16:36:10 | 显示全部楼层 |取消关注该作者的回复
  • 评论

回复 支持 反对

使用道具 举报

沈阳易泰电子 实名认证 渐入佳境(Lv2)
发表于 2018-1-11 16:38:24 | 显示全部楼层 |取消关注该作者的回复
干货,支持
  收起(1)
  • axing axing
    2018-01-11 16:41 评论
  • 评论

回复 支持 反对

使用道具 举报

axing 社区微信达人实名认证 番薯互助团队 文档共创团队 互助叫兽、助理编辑
发表于 2018-1-11 16:41:03 | 显示全部楼层 |取消关注该作者的回复
  收起(1)
  • 星痕 星痕 加油
    2018-01-11 16:44 评论
  • 评论

回复 支持 反对

使用道具 举报

郁金香  初出茅庐(Lv3)
发表于 2018-1-11 16:52:54 | 显示全部楼层 |取消关注该作者的回复
  收起(1)
  • axing axing 还好这次不是内内
    2018-01-11 16:58 评论
  • 评论

回复 支持 反对

使用道具 举报

w帆 社区微信达人实名认证 番薯互助团队 互助砖家、助理编辑
发表于 2018-1-11 19:12:27 | 显示全部楼层 |取消关注该作者的回复
  收起(2)
  • w帆 w帆 : 不错哟
    2018-01-11 19:12 评论
  • axing axing : 回复 w帆 :
    2018-01-11 20:11 评论
  • 评论

回复 支持 反对

使用道具 举报

laobiah 社区微信达人 渐入佳境(Lv2)
发表于 2018-1-12 08:28:21 | 显示全部楼层 |取消关注该作者的回复
666666
  • 评论

回复 支持 反对

使用道具 举报

果果果 社区微信达人 渐入佳境(Lv2)
发表于 2018-8-22 17:49:02 | 显示全部楼层 |取消关注该作者的回复
  • 评论

回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册  

本版积分规则

what a fine day
任务进行中

联系管理员@兔子酱|联系帆软|免责声明|手机版|帆软社区|Copyright © 帆软软件有限公司 ( 苏ICP备14031611号-3 )

GMT+8, 2018-12-12 03:01 , Processed in 0.453379 second(s), 126 queries , Gzip On.

返回顶部 返回列表