黑马程序员技术交流社区

标题: Oracle复习 [打印本页]
作者: cdreamx    时间: 2018-3-21 18:14
标题: Oracle复习

作者: cdreamx    时间: 2018-3-21 18:18
1.Oracle基础知识
  
概述
  		  

       

         
    • ORACLE数据库系统是美国ORACLE公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。
         
          
       

         
    • ORACLE通常应用于大型系统的数据库产品。        
         
       
       

         
    • ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品;作为分布式数据库它实现了分布式处理功能。        
         
      
  
  
特点
  		  

       

         
    • 支持多用户、大事务量的事务处理        
         
    • 数据安全性和完整性控制
         
    • 支持分布式数据处理
         
    • 可移植性
         
      
  
  
Oracle体系结构
  		  
  

2.Oracle数据类型
  字符型
  		  （1）CHAR:固定长度的字符类型，最多存储 2000 个字节  
   
  （2）VARCHAR2:可变长度的字符类型，最多存储 4000 个字节  
   
  （3）LONG:大文本类型。最大可以存储2个G
  
  数值型
  		   (1)NUMBER : 数值类型
   
    e.g.
  NUMBER(5)    最大可以存的数为 99999      
  NUMBER(5,2) 最大可以存的数为  999.99   
  
  日期型
  		  （1）DATE：日期时间型，精确到秒
   
  （2）TIMESTAMP：精确到秒的小数点后 9 位  
  
  二进制型(大数据类型)
  		  （1）CLOB:存储字符,最大可以存4个G
   
  （2）BLOB:存储图像、声音、视频等二进制数据,最多可以存4个G
  









作者: cdreamx    时间: 2018-3-21 18:19
3.Oracle语法
建表操作

创建表空间

    • create tablespace waterboss
    data file 'c:\waterboss.dbf'
    size 100m
    autoextend on
    next 10m

    • waterboss 为表空间名称

    • datafile 用于设置物理文件名称

    • size 用于设置表空间的初始大小

    • autoextend on  用于设置自动增长，如果存储量超过初始大小，则开始自动扩容

    • next 用于设置扩容的空间大小

创建用户

    • create user wateruser
    identified by itcast
    default tablespace waterboss
    • wateruser 为创建的用户名

    • identified by 用于设置用户的密码

    • default tablesapce  用于指定默认表空间名称
用户赋权

    • grant dba to wateruser
      • 给用户wateruser赋予DBA权限后即可登陆
创建表
create table 表名(
字段名 数据类型 约束,
字段名 数据类型 约束
)
修改表
增加字段
ALTER TABLE 表名 ADD(
列名 数据类型 约束
)
修改字段语法
1.修改属性
ALTER TABLE 表名称 MODIFY(
列名 数据类型  约束
)
区别于增加字段,此语法针对存在的字段
2.修改字段名
ALTER TABLE 表名称 RENAME COLUMN 原名称 TO 新列名
删除字段名
1.删除一个字段
ALTER TABLE 表名称 DROP COLUMN 列名
2.删除多个字段
ALTER TABLE 表名称 DROP (列名1,列名2...)
删除表
删除表
DROP TABLE 表名称
数据的增删改
需要进行commit提交
增
插入数据
1.根据字段插入
INSERT INTO 表名(列名1,列名2 ...) VLUAES(值1,值2....)
2.全字段插入
INSERT INTO 表名 VALUES(值1,值2...)
删
删除数据
1.delete
DELETE FROM 表名 WEHRE 条件
2.truncate
TRUNCATE TABLE 表名
区别:
delete删除的数据可以rollback
delete删除可能产生碎片,并且不释放空间
truncate是先催毁表结构,在重构造表结构
改
修改数据
UPDATE 表名 SET 列名1=值1, 列名2=值2,....
查
查询表
简单查询

    • 基础语法
    SELECT * FROM 表名
    • 实例
      • 精确查询
    select * from 表名 where 条件
    • 模糊查询
    select * from 表名 where name like '%关键字%'
    • and与or运算符
    and>or,改变优先级使用()
    • 范围查询
    select * from 表名 where usenum between 10000 and 20000
    • 空值查询
    is null
    is not null
    • 去重查询
    select distinct addressid from T_OWNERS

排序查询

    • 实例
      • 排序查询
    select * from 表名 order by 字段名 (desc)
伪类查询

    • 实例
      • ROWNUM(返回行号),ROWID(返回物理地址)
    select rowid,rownum,t.* from T_OWNERTYPE t
聚合查询

    • 基础聚合
      • 求和sum
    select sum(usenum) from t_account where year='2012'
    • usenum也可以为列的索引
    • 求平均avg
    select avg(usenum) from T_ACCOUNT where year='2012'
    • 求最大值max
    select max(usenum) from T_ACCOUNT where year='2012'
    • 求最小值min
    select min(usenum) from T_ACCOUNT where year='2012'
    • 统计个数count
    select count(usenum) from T_ACCOUNT where year='2012'

    • 分组聚合
      • 按字段分组(返回一条数据)
    select areaid,sum(money) from t_account group by areaid
    • 分组后条件查询
    select areaid,sum(money) from t_account group by areaid havingsum(money)>169000

    分组后只能查询分组字段和聚合函数
连接查询

    • 内连接查询
      • select o.id 业主编号,o.name 业主名称,ot.name 业主类型 from T_OWNERS o,T_OWNERTYPE ot where o.ownertypeid=ot.id
    • 左外连接查询
      • sql 1999
    select ow.id,ow,name,ac.year,ac.month,ac.money from T_OWNERS ow left join T_ACCOUNT ac on ow.id=ac.ownneruuid

    • oracle
    select ow.id,ow,name,ac.year,ac.month,ac.money from T_OWNERS ow, T_ACCOUNT ac on ow.id=ac.ownneruuid(+)

    • 右外连接查询
      • sql 1999
    select ow.id,ow,name,ac.year,ac.month,ac.money from T_OWNERS ow right join T_ACCOUNT ac on ow.id=ac.ownneruuid

    • oracle
    select ow.id,ow,name,ac.year,ac.month,ac.money from T_OWNERS ow, T_ACCOUNT ac on ow.id(+)=ac.ownneruuid
子查询

    • where子查询
      • 单行子查询
        • 只返回一条记录
        • 单行操作符
    • select * from T_ACCOUNT where year='2012' and month='01' and usenum>( select avg(usenum) from T_ACCOUNT where year='2012' and month='01')
    • 多行子查询
      • 返回多条记录
      • 多行操作符
    • from子句中的子查询
      • from中子查询为多行子查询
        • select * from

    (select o.id 业主编号,o.name 业主名称,ot.name 业主类型
    from T_OWNERS o,T_OWNERTYPE ot
    where o.ownertypeid=ot.id)

    where 业主类型='居民'
    • select子句中的子查询
      • select子句查询为单行子查询
        • select id,name,

    (select name from t_address where id=addressid) addressname(重命名)

    from t_owners
    select子查询可以调用外面一层的表字段
    select id,name,
    ( select name from t_address where id=addressid )
    addressname,
    ( select (select name from t_area where id=areaid ) from
    t_address where id=addressid )
    adrename
    from t_owners;

分页查询

    • 简单分页查询
      • select rownum,t.* from T_ACCOUNT t where rownum<=10
    rownum只能使用<或<=
    • 基于排序的分页查询
    select * from
    (select rownum r,t.* from
    (select * from T_ACCOUNT order by usenum desc) t
    where rownum<=20 )
    where r>10



作者: cdreamx    时间: 2018-3-21 18:20
常用单行函数

    • 字符函数
      • 求字符串长度
    select length('ABCD') from dual;
    • 获取字符串的子串
    select substr('ABCD',2,2) from dual;
    • 字符串拼接
    SELECT CONCAT(CONCAT('cat', 'dog'), 'pig') FROM dual;

    • 数值函数
      • 四舍五入
    select round(100.567) from dual
    • 截取,默认截取整数
    select trunc(100.567,2) from dual
    • 取MOD
    select mod(10,3) from dual

    • 日期函数
      • 获取当前信息
    select sysdate from dual
    • 加月函数:在当前函数的基础上加月
    select add_months(sysdate,2) from dual
    • 月最后一天函数
    select last_day(sysdate) from dual
    • 时间截取函数
    截掉小时之后
    select TRUNC(sysdate) from dual
    获取当前月第一天
    select TRUNC(sysdate,'yyyy') from dual
    获取当前年第一天
    select TRUNC(sysdate,'mm') from dual

    • 转换函数
      • 数字转字符串
    select TO_CHAR(1024) from dual
    • 日期转字符串
    select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
    • 字符串转日期
    select TO_DATE('2017-01-01','yyyy-mm-dd') from dual
    • 字符串转数字
    select TO_NUMBER('100') from dual

    • 其他函数
      • 空值处理函数
    NVL（检测的字段，如果为 null 的值）
    select PRICE,MINNUM,NVL(MAXNUM,9999999)
    from T_PRICETABLE where OWNERTYPEID=1

    NVL2（检测的值，如果不为 null 的值，如果为 null 的值）
    select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限')
    from T_PRICETABLE where OWNERTYPEID=1
    两个值类型要相同

    • 条件判断函数
      • decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)
    select name,decode( ownertypeid,1,'居民',2,'行政事业单位',3,'商业'，'dog') as 类型 from T_OWNERS
    • case when then end

    • select name ,(case ownertypeid
    when 1 then '居民'
    when 2 then '行政事业单位'
    when 3 then '商业'
    else '其它'
    end
    ) from T_OWNERS

    • select name,(case
    when ownertypeid= 1 then '居民'
    when ownertypeid= 2 then '行政事业'
    when ownertypeid= 3 then '商业'
    end )
    from T_OWNERS

    拓展

分析函数

    • 相同值排名相同,排名跳跃
    select rank() over(order by usenum desc ),usenum from T_ACCOUNT
    • 相同值排名相同,排名连续
    select dense_rank() over(order by usenum desc ),usenum from T_ACCOUNT
    • 连续排名,无论值是否相等
    select row_number() over(order by usenum desc ),usenum from T_ACCOUNT








欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/) 黑马程序员IT技术论坛 X3.2