Oracle简单入门

冷不防 2022-06-14 08:48 167阅读 0赞

NAT虚拟网络: 如果本机电脑能上网,就是虚拟机内的系统可以上网  
  
  
1.oracle数据库特点:   
    支持多用户,大事务量的事务处理  
  
  
     数据安全性和完整性控制  
  
  
     支持分布式数据处理  
  
  
     可移植性;   
  
  
2. oracle 体系:  
  
  
     1.只有一个数据库   2.实例:一个数据库可以有多个实例  3: 存在磁盘上的数据文件.   
  
  
     4:表空间:不会直接操作数据文件,而是操作表空间来找到数据文件.---特有的  
  
  
     5.数据库--->表空间--->段----区----数据块 操作磁盘块  
  
  
  
  
3. 用户: 一个数据库下面分为多个用户,用户下建表, 而mysql是数据库下建立表  
  
  
  
  
每个项目都要执行4,5,6步骤  
  
  
4. 创建表空间:  create tablespace 空间名   datafile 'c:\\文件地址\\文件.dbf'  
  
  
     size 文件大小m  autoextend on 超过100兆自动增长  next 10m 增长10m 分号结尾  
    
  
  
  
  
5.  一个表空间可以建立多个用户创建用户:     
       create user 用户名   identified by 密码  default tablespace 表空间  
  
  
  
  
6.赋权限:  grant dba to 用户名;  
  
  
  
  
7.数据类型  
      1. 字符型 : char 2000字节   varchar2 4000字节   long大文本类型,存2G;  
  
  
      2. 数值型 : number(5)  最大99999 (5,2) 最大存999.99  默认18  
  
  
      3. 日期型 : date : 精确到秒,  timestamp: 精确到秒的小数点后9位;  
  
  
      4.  二进制型(大数据类型) : clob : 存储字符, 最大4个G   blob: 存储图像,生硬,视频,4个G    
  
  
  
  
8.修改,删除 drop column一个列,  添加日期: sysdate 当前时间.   
  
  
  非空 not null  唯一约束 unique  检查约束: alter table 表名 modify 列名  check(列名>0)  
  
  
  
  
9.创建工程,引入ojdbc14.jar. 驱动名: oracle.jdbc,driver,OracleDriver,   
   
瘦连接,可以远程连接服务器,本地无需安装oracle;  胖连接,依托本地软件  
  
  
加载三个参数:  jdbc:oracle:thin:@192.168.80.10:1521:orcl  
  
  
  
  
10.数据导出与导入: 在服务器中执行,不用进入任何文件夹  
        
      1.整库导出与导入:  导出: exp 数据库名/密码 full=y file 文件名.dmp   
                         导入 imp 数据库名/密码 full=y  
  
  
  
  
      2.导出: exp 数据库名/密码 owner=用户名 文件名.dmp

导入: imp system/itcast    file=文件名.dmp fromuser=用户名

1.  select 查询 加括号改变优先级  
  
  
  
  
  
  
2.伪列    
   1. rowid ,每一个行数的UID   select rowid, t.\*  from t\_owners t; 物理地址 查询更快  
  
  
   2. rownum ,每一行的行号,   select rownum, t.\*  from t\_owners t;  结果集的序号  
  
  
  
  
  
  
3.分组聚合统计  
    
    分组可以加两个分组.  
  
  
  
  
4.多表查询  左外连接在 where 右边表的id加(+), 右边连接在 where左边表的id加(+)  
  
  
  
  
5.子查询:  单行子查询 大于等于小于一个数  
  
  
  多行子查询  多个数 in,all,any(和子查询返回的任意一个值比较)  
  
  
  
  
  
  
6.分页查询:   简单分页where rownum <=10,  运算符只能是小于等于,小于;  
   
     条件查询分页:   一定注意先产生记录才能根据行号来进行选择记录, 然后就是记录先排序,然后在扫描,行号不会乱  
  
  
  
  
7.单行函数:   伪表 dual  
  
  
  
  
   1.字符函数  
   
     1. 求字符串长度 select length('ABCD') from dual;  
  
  
     2.字符串子串 select substr('ABCD',从第几位截取,截取字符数) 从1开始. from dual;  
  
  
     3.字符串拼接,concat('abc','d')只能拼两个   ,  或者 select 'ab' || 'd' || 'g' 可以拼多个  
    
   
  
  
   2.数值函数  
   
     1. 四舍五入 select round(100.456) from dual; 默认整数四舍五入, (100.343,2)从小数点后两位  
  
  
     2.数字截取  select trunc(100.434,从小数点留下几位) from dual;  
  
  
     3.取余数    mod(10,4)  
    
  
  
  
  
  3.日期函数 日期截取mi按分钟截取.  
  
  
  
  
  
  
  4.转换函数  有自动转换功能  
  
  
     1.数字转为字符串 select to\_char(100) from dual, 靠右显示是数字,靠左是字符串.  
  
  
       日期转字符串  select to\_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual  
  
  
  
  
     2.字符串转为日期:  select to\_date('2016-03-10','yyyy-mm-dd')  
       字符串转为数值:  select to\_number('100') from dual  
  
  
  
  
  
  
  5.其他函数   
   
      1.空值处理函数 NVL  NVL(列名,自己随便写值) 代表如果列值为null, 就显示自己写的值;前后类型一致,  
  
  
        如果不一致 用 NVL2(列名,to\_char(不为null显示的值),为null显示的值)  
  
  
  
  
      2.条件取值 decode  
     
        decode(条件,值1,翻译值1,值2,翻译值2.....缺省值,可变参数)  
  
  
  
  
       SQL1999标准  
             select (case 列名  
                           when month>'01' and moth<='03' then '不好不坏'  
                           when 2 then '好'  
                           when 3 then '坏'  
                           else '其他'  
                     end) from 表  
  
  
8. 行列转换  
  
  
  
  
9. 分析函数 select rank(), over(order by usenum desc) 排名 from 表  ,  
            1.rank() 值相同,序号跳跃  
               
            2.dense\_rank() : 值相同,序号连续  
  
  
            3.row\_number() : 序号连续,不管值相同,相同的值只显示一个  可以做分页  
  
  
  
  
10. 集合运算: 并集 union, 不包括重复,  加 union all ,包括重复   
              交集 intersect   
              差集: minus : 可以做分页  
  
  
1.  select 查询 加括号改变优先级  
  
  
  
  
  
  
2.伪列    
   1. rowid ,每一个行数的UID   select rowid, t.\*  from t\_owners t; 物理地址 查询更快  
  
  
   2. rownum ,每一行的行号,   select rownum, t.\*  from t\_owners t;  结果集的序号  
  
  
  
  
  
  
3.分组聚合统计  
    
    分组可以加两个分组.  
  
  
  
  
4.多表查询  左外连接在 where 右边表的id加(+), 右边连接在 where左边表的id加(+)  
  
  
  
  
5.子查询:  单行子查询 大于等于小于一个数  
  
  
  多行子查询  多个数 in,all,any(和子查询返回的任意一个值比较)  
  
  
  
  
  
  
6.分页查询:   简单分页where rownum <=10,  运算符只能是小于等于,小于;  
   
     条件查询分页:   一定注意先产生记录才能根据行号来进行选择记录, 然后就是记录先排序,然后在扫描,行号不会乱  
  
  
  
  
7.单行函数:   伪表 dual  
  
  
  
  
   1.字符函数  
   
     1. 求字符串长度 select length('ABCD') from dual;  
  
  
     2.字符串子串 select substr('ABCD',从第几位截取,截取字符数) 从1开始. from dual;  
  
  
     3.字符串拼接,concat('abc','d')只能拼两个   ,  或者 select 'ab' || 'd' || 'g' 可以拼多个  
    
   
  
  
   2.数值函数  
   
     1. 四舍五入 select round(100.456) from dual; 默认整数四舍五入, (100.343,2)从小数点后两位  
  
  
     2.数字截取  select trunc(100.434,从小数点留下几位) from dual;  
  
  
     3.取余数    mod(10,4)  
    
  
  
  
  
  3.日期函数 日期截取mi按分钟截取.  
  
  
  
  
  
  
  4.转换函数  有自动转换功能  
  
  
     1.数字转为字符串 select to\_char(100) from dual, 靠右显示是数字,靠左是字符串.  
  
  
       日期转字符串  select to\_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual  
  
  
  
  
     2.字符串转为日期:  select to\_date('2016-03-10','yyyy-mm-dd')  
       字符串转为数值:  select to\_number('100') from dual  
  
  
  
  
  
  
  5.其他函数   
   
      1.空值处理函数 NVL  NVL(列名,自己随便写值) 代表如果列值为null, 就显示自己写的值;前后类型一致,  
  
  
        如果不一致 用 NVL2(列名,to\_char(不为null显示的值),为null显示的值)  
  
  
  
  
      2.条件取值 decode  
     
        decode(条件,值1,翻译值1,值2,翻译值2.....缺省值,可变参数)  
  
  
  
  
       SQL1999标准  
             select (case 列名  
                           when month>'01' and moth<='03' then '不好不坏'  
                           when 2 then '好'  
                           when 3 then '坏'  
                           else '其他'  
                     end) from 表  
  
  
8. 行列转换  
  
  
  
  
9. 分析函数 select rank(), over(order by usenum desc) 排名 from 表  ,  
            1.rank() 值相同,序号跳跃  
               
            2.dense\_rank() : 值相同,序号连续  
  
  
            3.row\_number() : 序号连续,不管值相同,相同的值只显示一个  可以做分页  
  
  
  
  
10. 集合运算: 并集 union, 不包括重复,  加 union all ,包括重复   
              交集 intersect   
              差集: minus : 可以做分页  
  
  
mysql多台数据库之间如何保持一致:写库,产生日志, 读库根据日志来进行显示;  
  
  
  
  
1. 视图: 是一种数据库对象,一种虚拟表,作用可以封装SQL语句,再次查询的时候,不用写SQL语句了.  
  
  
         简化数据操作.着重于特定数据,提供了简单安全机制,提供向后兼容性  
  
  
         列要加别名  
  
  
2. 视图的创建 create \[or replace(OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；)  
  
  
               \[force\]表不存也可存 \] view view\_name as subquery(查询语句)   
  
  
               \[with check option\]是否符合语句条件  \[with read only\] 只读试图  
  
  
  
  
            删除:  drop  view view\_name  
  
  
  
  
  
  
3. 只是单表查询,没有聚合函数,就是简单视图;  查询简单视图 select \* from 视图名 可以自己再次加条件  
  
  
    修改简单视图:  update 表名  set 列名='' where ...;  改视图的表的数据就改了表的数据,占了一条数据大小;  
  
  
    视图是伪表;可以该表基表的数据  
  
  
  
  
4.带检查约束的视图:  with check option ;   修改表时会检查条件where  
  
  
  
  
5.只读视图 : with read only ;   创建带错误(或不存在表)的视图;  force ; 表可能会动态产生会用到这种情况  
  
  
  
  
6. 复杂的视图: SQL语句有聚合函数或多表关联. 查询: 可以加条件;  
  
  
     修改复杂视图(多表关联)的数据: 基表数据会发生变化; 前提是 修改的字段属于键保留表(把主键保留下的表;)  
  
  
  
  
7. 聚合统计的视图: 所有的列不能修改.没有键保留表  
  
  
  
  
8. 物化视图: 上面创建的视图都是虚拟视图,知识SQL语句, 现在创建一张表就是物化视图.执行效率更高,只需查找一张表;  
             缺点: 占用存储空间  
  
  
  
  
     1. create  materialized view 视图名  \[build immediate (创建视图就生成数据,默认)   
  
  
            build deferred(创建视图不生成数据),显示数据第一次就要执行刷新命令, 第二次根据3条件来决定是否刷新  
  
  
  
  
  
  
     2.  refresh \[fast (快速刷新,增量刷新)| complete (完全刷新) | force(自动选择,自动匹配前两个模式,默认的)\]  
            
           创建增量刷新:  前提是: 必须有物化视图日志: 记录基表发生了哪些变化;然后用记录来更新物化视图  
  
  
  
  
           创建日志: create materialized view log on 表名 with (主键或rowid),如果是多表的话,就要创建多个日志  
                     基表发生增删改会有数据  
   
                  
           第二个前提是: 创建物化视图的语句中,必须有rowid或主键,多个表都要有rowid,  
  
  
  
  
            然后增量刷新: refresh fast , 但手动刷新的时候日志被清空,物化视图刷新  
  
  
  
  
     2.1 自动刷新的物化视图, refresh on commit 基表发生commit操作时候,自动刷新视图;  
  
  
  
  
     3.1  on \[commit\] 在基表做提交操作就刷新物化视图; 自动刷新  
   
                 创建自动刷新物化视图: refresh on commit  
           
  
  
  
  
     3.2   on demand 手动刷新(默认的), 执行begin DBMS\_MVIEW.refresh('物化视图表名','c')  end; 来刷新  
  
  
                                             c指的是complete完全刷新  
  
  
                                    窗口执行 EXEC DEMS\_MVIEW.refresh('物化视图表名','c'); 来刷新  
  
  
  
  
序列只能用于SQL 语句  
  
  
9.序列:可以产生连续数字  create sequence 序列名  ,   序列名.nextval伪列 自动增长,可以应用在主键id生成  
  
  
        序列名.currval 当前值, 是固定的; 默认起始值0;  
  
  
  
  
      1.创建复杂序列:   
  
  
        create sequence 序列名  maxvalue 数值 ; increment 增长多少个 start with从多少开始 minvalue 最小值  
          cycle 循环  
  
  
         注意1: 默认有缓存并且值为20个,所以设置最大值和最小值要注意   
  
  
         注意2: cache 最大值-最小值/每次增长 向上取整 大于等于缓存值;  
  
  
         注意3: 循环序列要最小和最大值  
  
  
  
  
      2.修改和删除序列, 起始参数不能更改;  
  
  
  
  
  
  
  
  
10. 同义词: 别名 create\[public(private默认)\] SYNONYM 同义词名称 for object; object 表, 视图....  
  
  
             varchar() 是varchar2() 的同义词;是内置的  , 可以基于同义词再次创建一个同义词;  
  
  
  
  
  
  
11. 索引:  加速数据存取的数据对象, 数据量大的时候用.类似于树,占据存储空间. 1-4 统称为 b tree \* 索引  
  
  
       1.普通索引  create index索引名称 on 表名(经常查询的列名)  主键是系统自建索引;  
                   当查询时候,先找索引,然后再找列;  
  
  
  
  
       2.唯一索引:  这一列的值不会重复create unique index, 不仅有索引还有唯一约束   
  
  
  
  
       3.复合索引:  为多列创建复合索引  on 表名(列1,列名2) ,列的顺序也有要求.  
  
  
  
  
       4.反向键索引: on 表名(列名) reverse  当某个字段的值为连续增长的时候,层数比较多,查询慢   
  
  
                     反向运算,把数值的二进制反过来,数值就随机了,层数少  
  
  
  
  
  
  
        5.位图索引:  适合创建在低基数列上,查询时只能用等号 比如sex='男'; 一个值一个位图.  
                     create bitmp index  
  
  
  
  
  
  
12.视图和索引是数据库通用的  
  
  
  
  
                                                   oracle的编程  
  
  
1.PLSQL :   
   
   declare 声明变量    
  
  
   begin   
         代码逻辑   
  
  
        DBMS\_output.put\_line('自己起的名字' || 变量名);  
  
  
   exception异常处理    
  
  
   end;  
  
  
  
  
   声明变量   变量名   类型(长度);    
   
   赋值:      变量名:=变量值   或  select 列名 into 变量名 from 表名 where  
  
  
              注意：结果必须是一条记录 ，有多条记录和没有记录都会报错  
  
  
  
  
2. 属性类型:    
  
  
          当不确定这个类型  引用类型   表名.列名%type   用于声明变量名类型;  
  
  
          记录型:  引用类型的变量名    表名%rowtype    作用: 标识某个表的行记录类型  
  
  
          当取出表内的某一列值时, 变量名.列名;  
  
  
  
  
3.异常处理:   预定义异常:21种  自定义异常  
  
  
   exception异常处理    
     
      when  异常类型1 then  
        异常处理逻辑(  DBMS\_output.put\_line(错误);)  
  
  
      when  异常类型2 then  
        异常处理逻辑(  DBMS\_output.put\_line(错误);)  
  
  
  
  
4.条件判断  
  
  
 一:  
  if  then  
  
  
  end if;  
  
  
  
  
 二:  
  if 条件  then  
  
  
  else  
  
  
  end if;  
  
  
  
  
 三:  
  
  
   if 条件 then  
  
  
       代码逻辑;  
  
  
   elsif 条件  then  
  
  
     代码逻辑;  
  
  
    else  
       代码逻辑;  
   end if;  
  
  
  
  
  
  
5. 循环: 三种    
  
  
   1.无条件  
  
  
    loop  
       循环语句  
  
  
       exit when 条件; 退出  
    end loop;  
  
  
  
  
   2.有条件  
  
  
       while 条件  
       loop  
       end loop;  
      
  
  
   3.for循环  如: 1-100个数  
  
  
      for 变量名 in 起始值..终止值  
      loop  
       循环语句  
      end loop;   
  
  
  
  
  
  
6. 游标: 就是结果集,然后读取数据的;  
   
  
  
  declare  
  
  
   1.声明游标 cursor 游标名称(参数名 number) is sql语句条件=参数名;  
   
  begin  
   2.使用游标   
            
        open 游标名称(指定参数值);  
  
  
        loop   
  
  
           fetch 游标名称  into 变量;  
  
  
           exit  when 游标名称%notfound;  
  
  
           输出语句 , 注意是输出最后一行后,游标再次循环,直接就退出了,不输出了所有顺序不能颠倒;  
  
  
        end loop;  
  
  
        close 游标名称;  
  
  
   end;  
  
  
  
  
  
  
 2.for循环带参数游标;  
    
   declare  
     声明部分可以省略定义游标类型 ;直接给游标赋值  
  
  
   begin  
  
  
      2.使用游标   
            
        for 变量名 in 游标名称(参数)  
        loop   
          输出  
        end loop;  
   end;  
  
  
  
  
  
  
7.存储函数 又称自定义函数,可以接受一个或多个参数,返回一个结果;  
           用于查询比较多  
  
  
  
  
  create(或修改 replace) function 函数名称(变量名1)  
  (参数名称 参数类型,...)  
  
  
   return 结果变量数据类型(varchar2不用写长度,number)  
  
  
   is  
    变量2声明部分;varchar2(30); 写长度  
  
  
    begin  
       给变量2赋值sql语句;  
       return 变量2;  
     end;  
  
  
  
  
  测试函数  
  select fn\_getaddress(变量名) from dual  
  
  
  
  
8.存储过程, 返回多个值  
  
  
   create or replace(创建或修改)  procedure 存储过程名称  
   (参数名称 参数类型(不指明长度),...)  
  
  
   is或as  
  
  
   begin  
  
  
   end;  
  
  
  
  
     
  
  
  
  
 1. 调用不带传出参数的存储过程 , 可以省略in(因为默认传入参数) ;  
  
  
      1.  call  存储过程名();  
       
      2.begin  
           存储过程名();  
        end ;  
  
  
     
 2.JDBC的第三种调用存储过程  
  
  
    用callablestatement = conn.preparecall("\{call 存储过程名(?,?,?..)\}");  
  
  
    stmt.setString(1,);  
  
  
  
  
  
  
 3.传出参数的存储过程  
  (参数名称 out 参数类型,...)  
  
  
   对传出参数赋值  
   select 要赋的值 into 参数名 from ...;  
  
  
  
  
 4.调用传出参数只能 用 begin  end;  
  
  
   declare   
  
  
      定义变量名  
   begin    
      存储过程名(...,一个参数为上面变量名);  
      变量名就有值了;  
    end;  
  
  
  
  
  5.用java代码调用带传出参数  
     
   //注册传出参数的类型  
   stmt.registeroutparameter(第几个参数, oracleTypes.类型);  
  
  
    stmt.execute()  
   //接受参数  
  
  
   stmt.getlong(第几个参数);  
  
  
  
  
9. 触发器: 存储了PL/SQL程序,  数据确认, 实施复杂的安全性检查, 跟踪表上所做的数据操作等,数据的备份与同步;  
  
  
   分类: (行级 执行多句 或语句级 不管影响多少行,只执行一句话) 前置触发器类似拦截器,  后置触发器,类似于备份;    
  
  
  
  
   create \[或 修改 or replace\]  trlgger 触发器名  
    
       before 或 after  
        
      delete \[or\] insert \[or\] update \[\]of 列名1,列名2\]   
  
  
      on 表名  
        
      for each row 或 when (条件)  
          
    declare  
  
  
    begin  
          PLSQL块, 不用写commit  
    end;  
  
  
  
  
   2.只能用在行级触发器,后置触发器只能读 伪记录变量 :old  :new   
   
  
  
   3.前置触发器建立后不用手动调用