oracle基本使用

1.oracle :ORACLE 数据库系统是美国 ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组软件产品 2.oracle特点： 1)支持多用户、大事务量的事务处理 2)数据安全性和完整性控制 3)支持分布式数据处理 4)可移植性 3.oracle体系结构： 1)数据库： Oracle数据库是数据的物理存储，和其它数据库不一样，这里的数据库是一个操作系统。只有一个库。可以看作是 Oracle 就只有一个大数据库。 2)实例： 一个Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有 n 个实例 3)数据文件：数据文件是数据库的物理存储单位。 4)表空间： 表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。用于管理数据文件 5)用户： 用户是在表空间下建立的。用户登陆后只能看到和操作自己的表,ORACLE的用户与 MYSQL 的数据库类似，每建立一个应用需要创建一个用户 总结：Oracle只有一个数据库，数据库下可以有多个实例，实例之间相互独立。实例下面可有多个表空间，表空间对应多个数据文件。 在开发中每个程序会对应地创建一个用户，每个用户都是建立在表空间下，用户操作数据库时，实际操作的是表空间下对应的数据文件 2.软件的安装 1.window 2003安装：在本机上解压后直接挂载到虚拟机中 2.创建虚拟网卡（仅主机模式），设置网络适配器，设置本地操作系统虚拟网卡ip 3.安装Oracle数据库 4.sqlplus远程连接Oracle 5.PLSQL Developer远程连接可视化工具软件的安装 3.表空间的创建： create tablespace waterboss //waterboss为表空间名称 datafile ‘c:\waterboss.dbf‘ //指定表空间的数据文件 size 100m //数据文件所占空间大小 autoextend on //是否可扩展空间 next 10m //每次扩展的大小 删除表空间： drop tablespace 表空间名 4.用户的创建： create user wateruser //wateruser为用户名 identified by itcast //设置用户密码 default tablespace waterboss //指定默认的表空间 用户的赋权： grant dba to wateruser //dba为最高权限，默认情况下不能使用自己创建的用户登录 删除用户 drop user 用户名 登录时如果输入错误密码次数过多，用户将被锁住，可通过如下方式解锁 alter user 用户名 identified by 密码 account unlock 5.表的创建、修改与删除（与MySQL类似） CREATE TABLE 表名称( 字段名 类型(长度) primary key, 字段名 类型(长度), ....... ); Oracle表中数据类型： 字符型 char:固定长度的字符类型，最多存储2000个字节 varchar2:可变长度的字符类型，最多存储 4000 个字节 LONG：大文本类型。最大可以存储 2 个 G 数值型 NUMBER 例如： NUMBER(5) 最大可以存的数为 99999 默认长度18 NUMBER(5,2) 最大可以存的数为 999.99 日期型 DATE：日期时间型，精确到秒 TIMESTAMP：精确到秒的小数点后 9 位 二进制型 CLOB : 存储字符,最大可以存 4 个 G BLOB：存储图像、声音、视频等二进制数据,最多可以存 4 个 G Oracle中对于字段的约束同MySQL： primary key not null unique 6.表的增、删、改：（与MySQL类似） 创建表： create table 表名( 字段名 类型 约束, 字段名 类型 约束, 字段名 类型 约束, 字段名 类型 约束 ); 删除表： drop table 表名 修改表： alter table 表名称 add(列名 类型,列名 类型); alter table 表名称 modify(列名 类型,列名 类型); alter table 表名称 add(列名 类型,列名 类型); alter table 表名称 rename column 原列名 to 新列名 alter table 表名称 drop 列名 7.表数据的增删改： 添加：insert into t_owners values (?,?,?,?,?,?,?) 修改：update t_owners set name=?,addressid=?,housenumber=?,watermeter=?,adddate=?,ownertypeid=? where id=? 删除：delete from t_owners where id=? commit的使用： 进行增删改时，需要使用 commit; 查询不需要commit 8.jdbc连接oracle实现增删改(DbUtils+c3p0连接池) 传统方式： 1.注册驱动（Class.forName("...")） 2.DriveManage获取连接，需要传入路径，用户名，密码 3.获取执行对象（PreparedStatement） 4.设置参数 5.执行sql语句 采用c3p0连接池结合dbutils工具类： c3p0连接池配置文件： <named-config name="oracle"> <property name="driverClass">oracle.jdbc.driver.OracleDriver</property> //指定驱动的位置 <property name="jdbcUrl">jdbc:oracle:thin:@192.168.80.10:1521:orcl</property> //连接的路径，指定了协议，IP，端口，实例 <property name="user">wateruser</property> //登录Oracle的用户名 <property name="password">itheima</property> //登录的密码 <property name="initialPoolSize">5</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">20</property> </named-config> JdbcUtils工具类： private static final ComboPooledDataSource dataSource=new ComboPooledDataSource("oracle"); //可以通过配置中的name属性来加载配置文件创建连接池 增删改查步骤： 1、创建QueryRunner对象； 2、调用update方法或者query方法；

Oracle查询
1.单表查询，同MySQL查询
    1)select * from 表名 where name=‘...‘         //oracle中字符串只能使用‘‘
    2)select * from 表明 where like name like ‘%刘%‘    //%表示一个或多个字符，_表示一个字符，否定用not like 
    3)and or >= <= > < between and 和MySQL一样
    4)select * from 表名 where name is null   //查找空的值，否定：is not null
    5)select distinct name from 表名        //得到的结果为去重后的记录
    6)select * from 表名 order by usernum desc    //按照usernum逆序排序，asc为顺序
    
2伪列    //系统会为每一个表生成的列，默认情况下不显示
    1)rowid:表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的物理地址。使用 ROWID 可以快速的定位表中的某一行。
    2)rownum:在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回1,第二行返回2,以此类推。

3.聚合函数
    1)求和  sum        select sum(usenum) from t_account where year=‘2012‘
    2)求平均值 avg
    3)最大值 max   最小值 min  
    4)个数 count
    
4.分组 group by     //如果是分组查询，select后面只能有聚合函数或者用于分组的字段。
    select areaid,sum(money) from t_account group by areaid  
    
5.分组后条件查询having ：对结果进行筛选
    
2.多表查询
    内连接：select * from 表1，表2 ... where ...
    外连接:    select * from 表1 left join 表2 on ...    //左外连接
            select * from 表1 right join 表2 on ...    //右外连接
            外连接在Oracle中的语法：
            select * from t1,t2 where t1.id=t2.tiid(+)
            select * from t1,t2 where t1.id(+)=t2.tiid
3.子查询        //子查询中，如果是一条记录，作为结果相当于一个具体的值，可放再查询条件，结果中
    分为单行子查询和多行子查询：    //单行子查询返回一条记录，多行子查询返回多条记录
        如放在> = 等的后面只能是单行子查询
        如放在in any all 后面可以是多行子查询
        
    1)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‘ )
    2)from 子查询        //将查询结果作为被查询的表再查询
    3)select 子查询        //将查询结果作为另一个查询的结果，此子查询只能是单行子查询
    
4.分页
    1)采用伪列和子查询实现分页
        select * from (select rownum r,t.* from T_account)where r>10 and r<=20
        
    2)基于排序的分页
        select * from (select rownum r,t.* from (select * from t_account order by usenum) t)where r>10 and r<=20

    伪列中rownum的生成原理：每生成一条记录，就生成一个rownum,
    所以select rownum,t.* from t_account t order by usenum ,这条语句先生成rownum ,再排序，所以最终的表中rownum为乱序
    
    mysql的分页： select * from table limit 3,5;        //表示从第三条开始（从零开始数），查询五条
    
5函数
    数值函数:
        四舍五入：ROUND(数值,保留小数位数);
        数值截取：TRUNC(数值,保留小数位数);  ROUND(100.567,2)=100.57    TRUNC(100.567,2)=100.56
        
    日期函数：
        ADD_MONTHS(日期,值)            ：增加指定的月
        last_day(sysdate)            ：求所在月的最后一天
        tranc(sysdate,‘mm‘)            :日期截取，丢掉月份后面的数据
        
    转换函数：
        select TO_CHAR(1024) from dual    ：数字转字符串    //其中dual为伪表，用于测试，其中只有一个x的值
        to_number(‘100‘)                :字符串转数字
        to_char(日期,‘yyyy-mm-dd‘)    ：将日期类型转换成字符串类型    //分钟，用mi表示
        to_date(字符串,‘yyyy-mm-dd‘)：将字符串类型转换成日期类型；
        注意：在mysql中如果添加日期数据，就以字符串的形式添加即可（会自动转化为日期数据），但是在oracle中不行；
            insert into t_owners values(to_date(‘2018-7-18‘,‘yyyy-mm-dd‘))
        

    其它函数：
        空值处理函数：
            nvl(maxnum,0)        //如果maxnum不为空显示值，为空显示零
            nvl2(检测的值，如果不为 null 的值，如果为 null 的值)

        decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值) :【功能】根据条件返回相应值

        case            //可通过这个函数实现行列转换，使用在select后面
            when 条件1 then 条件1成立返回值
            when 条件2 then 条件2成立返回值
            when 条件3 then 条件3成立返回值
            ...
            else 缺省值
            end;            //必须要end关键字，
        例如：
            select name,(case
                            when ownertypeid= 1 then ‘居民‘
                            when ownertypeid= 2 then ‘行政事业‘
                            when ownertypeid= 3 then ‘商业‘
                            end ) from T_OWNERS

                            
    三种排名方式：
        rank        相同的值排名相同，排名跳跃  SELECT rank() OVER (ORDER BY USENUM desc) 排名,t.* FROM T_ACCOUNT t; 下面类似
        dense_rank    相同的值排名相同，排名连续
        row_number    返回连续的排名，无论值是否相等
    集合运算：
        UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
        UNION(并集)，返回各个查询的所有记录，不包括重复记录。
        INTERSECT(交集)，返回两个查询共有的记录。
        MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之
        后剩余的记录。

    如：select * from t_owners where id<=7
        union
        select * from t_owners where id>=5


分组和排序后面的字段可有多个：
    select * from t_account 
    group by year,month         //year 和 month 均相同才分为一组
    order by year,month;        //先按year排序，再按月份排序
    
    

 

oracle基本使用

标签：精确   有一个   仅主机模式   独立   dbf   code   primary   size   erb