oracle-01基础篇

待我称王封你为后i 2022-04-08 14:39 234阅读 0赞

# \#oracle的安装配置和使用 #

## \#\#一、 oracle的简介 ##

### \#\#\#1. oracle数据库的特点 ###

>     持大数据量、多用户的高性能的事务处理  
>     Oracle遵守数据存取语言、操作系统、用户接口和网络通信协议的工业标准  
>     实施安全性控制和完整性控制  
>     支持分布式数据库和分布处理  
>     具有可移植性、可兼容性和可连接性  
>     全球化、跨平台的数据库

### \#\#\#2. oracle认证 ###

>     初级(辅助认证OCA)；  
>     专业/中级(专业认证OCP)-资料费/考试费/培训费万元左右  
>     大师/高级(专家级认证OCM)-中国大陆约千于人拥有OCM的认证/考试费约2000$，原厂培训费约2000$。

## \#\#二、安装数据库 ##

## \#\#三、简单使用oracle数据库 ##

### \#\#\#1.了解Oracle服务器 ###

> Oracle服务器由两大部分组成，Oracle数据库和Oracle实例。
> 
>     Oracle数据库  
>         Oracle数据库：位于硬盘上实际存放数据的文件，这些文件组织在一起，成为一个逻辑整体，即为Oracle数据库。  
>         因此，在Oracle看来，数据库是只硬盘上文件的集合，必须要与内存中的实例合作，才能对外提供数据管理服务。
> 
>     Oracle实例  
>         位于物理内存中的数据结构。它由一共享的内存池和多个后台进程组成，共享的内存池可以被所有的进程访问，  
>         用户如果要存储数据库（也就是硬盘上的文件）里的数据，必须通过实例才能实现，不能直接读取硬盘上的文件。  
>

### \#\#\#2.登录 ###

用户名和密码

管理员用户:  
        用户名sys(最大的权限)密码在安装时设置的密码；还具有创建新的数据库的权限。  
        用户名system(次之)密码在安装时设置的密码  
    普通用户:  
        用户名scott密码tiger

忘记密码  
      
    控制台:sqlplus / as sysdba;  
    在以SYSDBA身份登陆时可以修改其他用户的密码  
    create user wyp identified by 123456;  
    alter user user01 identified by user10;

解锁  
    alert user scott account unlock  
连接字符串

通过网络访问远端服务器上的数据库时，用于描述数据库访问地址的字符串，  
    通常的结构是“主机名（或IP）：端口号：服务名”，例如：jdbc:oracle:thin:@localhost:1521:orcl(SID)

###    \#\#\#3.权限管理 ###

在为一个Oracle数据库系统创建用户之后,这些用户既不能与数据库服务器连接,也不能做任何事情,除非他们具有执行特定数据库操作的权限.  
    oracle内置权限：（SELECT \* FROM SYSTEM\_PRIVILEGE\_MAP查）；

Oracle中的数据库访问权限类型共有两种:  
         系统权限: 允许用户执行特定的数据库动作，如创建表、创建索引、连接实例等  
        对象权限: 允许用户操纵一些特定的对象，如读取视图，可更新某些列、执行存储过程等

>     1）常用系统权限
> 
>        CREATE SESSION                     创建会话
> 
>        CREATE SEQUENCE                    创建序列
> 
>        CREATE SYNONYM                     创建同名对象
> 
>        CREATE TABLE                       在用户模式中创建表
> 
>        CREATE ANY TABLE                   在任何模式中创建表
> 
>        DROP TABLE                         在用户模式中删除表
> 
>        DROP ANY TABLE                     在任何模式中删除表
> 
>        CREATE PROCEDURE                   创建存储过程
> 
>        EXECUTE ANY PROCEDURE              执行任何模式的存储过程
> 
>        CREATE USER                        创建用户
> 
>        DROP USER                          删除用户
> 
>        CREATE VIEW                        创建视图

使用系统权限

>         赋予权限语法：GRANT privilege \[, privilege...\] TO user \[, user| role, PUBLIC...\] \[WITH ADMIN OPTION\];

示例：分配权限  
        SQL> GRANT CREATE SESSION,CREATE TABLE,CREATE USER TO scott;  
        SQL> GRANT EXECUTE ANY PROCEDURE TO scott WITH ADMIN OPTION;  
        SQL> GRANT EXECUTE ANY PROCEDURE TO PUBLIC; --将EXECUTE ANY PROCEDURE授予所有用户

示例：使用系统表  
        SQL> CREATE TABLE tb1 AS SELECT \* FROM USER\_TABLES;   
        SQL> CONN sys as sysdba;   
        SQL> ALTER USER robinson QUOTA 10M ON USERS;  
        SQL> CREATE TABLE tb1 AS SELECT \* FROM USER\_TABLES;

查看系统权限  
        dba\_sys\_privs  --针对所有用户被授予的系统权限  
        user\_sys\_privs --针对当前登陆用户被授予的系统权限

回收系统权限语法：REVOKE \{privilege | role\} FROM \{user\_name | role\_name | PUBLIC\}

示例：回收执行存储过程的权限  
         SQL> REVOKE EXECUTE ANY PROCEDURE FROM scott;  
        SQL> select grantee,privilege,admin\_option from dba\_sys\_privs

>     2）对象权限
> 
>        对象权限            表   视图   序列   过程
> 
>        修改(alter)         √            √
> 
>        删除(delete)        √    √
> 
>        执行(execute)                             √
> 
>        索引(index)         √
> 
>        插入(insert)        √    √
> 
>        关联(references)    √    √
> 
>        选择(select)        √    √      √
> 
>        更新(update)        √    √

对象授权语法：

>        GRANT object\_priv｜ALL \[(columns)\]  
>        ON object  
>        TO \{user|role|PUBLIC\}  
>        \[WITH GRANT OPTION\];

ALL：所有对象权限  
       PUBLIC：授给所有的用户  
       WITH GRANT OPTION：允许用户再次给其它用户授权

示例：  
        SQL> GRANT SELECT ON emp TO robinson;  
        SQL> GRANT UPDATE(sal,mgr) ON emp TO robinson WITH GRANT OPTION;  
           SQL> GRANT SELECT ON dept TO PUBLIC;

USER\_SYS\_PRIVS            用户拥有的系统权限  
    USER\_TAB\_PRIVS            用户拥有的对象权限

回收对象权限语法：

>         REVOKE \{privilege \[, privilege...\]|ALL\}  
>         ON object  
>         FROM   \{user\[, user...\]|role|PUBLIC\}  
>         \[CASCADE CONSTRAINTS\];

### \#\#\#4.PL/SQLDeveloper工具的使用 ###

## \#\#四、查询中常用的函数 ##

###     1.字符函数 ###

大小写转换函数  
           LOWER (strexp)    返回字符串,并将所有的字符小写  
           UPPER (strexp)     返回字符串,并将所有的字符大写  
           INITCAP(strexp)    将字符串的(每个单词的)第一个字母变为大写,后面的小写;

字符处理函数  
            1、CONCAT(strexp, strexp):              连接两个字符串   
            2、Substr(str,start\_index,length)    从指定的位置截取指定长度的字符串   
            3、LENGTH(strexp):返回字符串的长度   
            4、LPAD( string1, padded\_length, \[ pad\_string \] )   在列的左边粘贴字符  
                其中string1是需要粘贴字符的字符串      
                padded\_length是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成padded\_length;  
                pad\_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参数未写，lpad函数将会在string1的左边粘贴空格。   
            5、RPAD(粘贴字符)    RPAD  在列的右边粘贴字符  
            6、Trim():截取字符串两端特殊字符  
                Trim('s' from 'string');可以指定从字符串两边要截取的特殊字符

###     2.数字函数 ###

Round：传回一个数值,该数值是按照指定的小数位数进行四舍五入运算的结果。  
        ROUND( number, decimal\_places )  
            number : 需四舍五入处理的数值，  
            decimal\_places : 四舍五入 , 小数取几位 ( 预设为 0 )  
        ROUND(45.926, 2)                45.93  
          
        TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。  
        TRUNC(45.926, 2)                 45.92

mod（number1，number2）两个数值相除并返回其余数。运算符执行 number1 除以 number2 操作   
        MOD(1600, 300)

###     3.转换函数 ###

隐含的类型转换   
        显式的类型转换  
!\[\](imgs/1.png)

显式的类型转换  
            TO\_CHAR函数处理数字  
                TO\_CHAR(number, ‘fmt’)：是字符类型的函数，转化数字为字符  
                使用TO\_CHAR函数将数字作为字符显示   
                数字格式控制符    描述  
                9    代表一位数字，如果当前位有数字，显示数字，否则不显示(小数部分仍然会强制显示）  
                0    强制显示该位，如果当前位有数字，显示数字，否则显示0  
                $    增加美元符号显示  
                L    增加本地货币符号显示  
                .    小数点符号  
                ,    千分位符号  3,000,000,000.00

TO\_NUMBER函数  
                使用TO\_NUMBER函数将字符转换为数字  
                TO\_NUMBER(char \[, 'fmt'\]))

TO\_CHAR 函数操作日期

TO\_CHAR(date, ‘fmt’) ：是字符类型的函数，转化日期为字符  
                格式(‘fmt’)  
                1、必须用单引号括起来,并且是大小写敏感  
                2、可包含任何有效的日期格式  
                3、fmt值的宽度正好能容纳所有的有效数字   
                  
                格式控制符    描述  
                YYYY  YYY YY    以数字表示全年(分别代表4位、三位、两位)的数字年  
                YEAR    年的拼写  
                MM(mm)    两位数字月  
                MONTH    月的全拼  
                MON    月名称的缩写  
                DD    数字日  
                DAY    星期的全拼  
                D     星期中的第几天   
                DY    表示三位缩写的星期

TO\_DATE函数  
                使用TO\_DATE函数将字符转换为日期  
                TO\_DATE(char \[, 'fmt'\]))  
                格式控制符    描述  
                HH，HH12    一天中的第几个小时，12进制表示法   
                HH24    一天中的第几个小时，取值为00~23   
                MI    一小时中的分钟  
                SS    一分钟中的秒  
                AM    显示上午或下午

###        4.日期函数 ###

add\_months(date\_value,number\_of\_months):用于从一个日期值增加或减少一些月份  
            select add\_months(sysdate,12) "明年今日" from dual;  
            select add\_months(sysdate,-12) "去年今日" from dual;

current\_date:返回当前会话时区中的当前日期  
            select sessiontimezone,current\_date from dual;  
            alter session set time\_zone='-11:00'//修改当前会话时区

extract(date\_field from datetime\_value):找出日期或间隔值的字段值  
            select extract(month from sysdate) "当前月份" from dual;

last\_day(date\_value):返回指定日期中的月份的最后一天的日期  
            select last\_day('2000-02-01') "润月" from dual;

next\_day( date, weekday ):返回指定时间的下一个星期的指定星期对应的日期  
            select next\_day(sysdate,'星期一') from dual;

months\_between(f,s) 日期f和s间相差月数  
            select months\_between(sysdate,'04-5月-09')from dual;

###     5.其他函数 ###

NVL 函数  
            NVL(expr1,expr2)将空值转换为替换的值  
            支持多种数据格式可以是日期,字符,数字  
            NVL的两个参数数据类型必须匹配 ,否则出错。  
        示例：  
            NVL(comm,0)  
            NVL(hiredate,'01-JAN-97')   
            NVL(job,'No Job Yet')  
            select NVL(null,'01-JAN-97') from dual

NVL2函数  
            NVL2(expr0,expr1,expr2)  
            如果expr0不为Null,返回expr1, 为Null,返回expr2.    
            expr0可以为任何数据类型

NULLIF函数  
            NULLIF(expr1,expr2)  
            比较两个表达式，如果相等返回空值，如果不等返回第一个表达式。  
              
            SELECT first\_name, LENGTH(first\_name) "expr1",   
                   last\_name,  LENGTH(last\_name)  "expr2",  
                   NULLIF(LENGTH(first\_name),   
                   LENGTH(last\_name)) result  
            FROM   employees;

###     6.嵌套函数 ###

单行函数可被嵌入到任何层  
        在嵌套的时候，最内层的函数最先被执行，执行的结果被传递到它的上层函数，作为参数使用，然后依次从内向外执行，直到所有的函数执行完。

\#\#五、dual表

Oracle提供的最小的工作表，只有一行一列，具有某些特殊功用途

Oracle提供的最小的表，不论进行何种操作（不要删除记录），它都只有一条记录——'X'。

例如：执行select \* from dual，里面只有一条记录；执行insert into dual values('Y')后，再次查询dual表，仍然显示一条记录。  
    是sys用户下的一张内部表，所有用户都可以使用DUAL名称访问，无论什么时候这个表总是存在。

例如：执行一个查看当前日期的语句 select sysdate from dual，这条语句在放在放在任何一个oracle数据库当中都不会报错，  
    所以一般做一些特定查询的时候用这个表是最稳妥的。

常见用途：

查看当前用户  
        select user from dual;  
    用做计算器  
        select 7\*9\*10-10 from dual；  
    调用系统函数  
        获得当前系统时间  
        select to\_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;  
        获得主机名  
        select sys\_context('userenv','terminal') from dual;  
        获得当前locale  
        select sys\_context('userenv','language') from dual;  
        获得一个随机数  
        select DBMS\_RANDOM.random from dual;  
    查看序列值  
        创建序列aaa 以1开始，每次加1  
        create sequence aaa increment by 1 start with 1;  
        获得序列aaa 的下一个序列值  
        select aaa.nextval from dual;  
        获得序列aaa 的当前序列值  
        select aaa.currval from dual;

## \#\#六、序列 ##

###     1.了解序列: ###

是一数据库对象，利用它可生成唯一的整数。  
    一般使用序列自动地生成表主键值或唯一键值,不直接连接到数据库中的任何表

###     2.创建序列语法 ###

CREATE SEQUENCE \[user.\]sequence\_name  
                \[increment by n\]   
                 \[start with n\]  
                 \[maxvalue n|\]  
                \[minvalue n \];  
                \[cache\]  
    \[NOCYCLE|CYCLE\]

>  INCREMENT BY： 指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略该子句时，缺省值为1。  
>     START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。  
>             对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。  
>     MAXVALUE：指定序列可生成的最大值。  
>     MINVALUE：指定序列的最小值  
>     NOCYCLE：一直累加，不循环  
>     CYCLE：累加到最大值循环  
>     cache:默认值是20

序列里的缓冲是大量并发提取序列值时提升性能的一种手段 ，由于序列操作是原子操作，如果没有缓冲，序列的访问将只能由一个事务独占，  
    独占期间阻塞其它事务的访问，且一定会涉及硬盘操作。这样如果有对一个序列的大量并发操作的话，系统性能将会下降。   
    缓冲是指事先成批的提取序列值，缓冲在内存中，供多个事务同时访问（每个事务占一个缓冲的序列值），且访问期间如果没有用尽缓冲是不用写硬盘的，可在大幅度提高并发访问序列的性能。  
    但带来的问题是序列值可能不连续（但肯定保证唯一），如并发访问序列值后有些事务回滚等操作，都会带来缓冲序列值的不连续或丢失。

###     3.创建序列 ###

create sequence  emp_seq
        increment by 1
        start with 1
        minvalue 1
        maxvalue 9999

###     4.查看用户定义的序列 ###

select t.sequence_name,t.min_value,t.max_value,
                           t.increment_by  from user_sequences t

###        5.使用序列 ###

--currval :返回序列的当前值  
    --nextval 返回序列首次引用时的起始值，以后使用nextval的引用将使用increment by 子句增加序列值，并返回新值

查看某序列的值  
    select emp\_seq.currval from dual  
    使用序列  
    select emp\_seq.nextval from dual

使用序列示例：  
    insert into emplouees(employees\_id,last\_name….) values(emp\_seq.nextval,’zhang’……)  
    alter sequence  emp\_seq increment by 2 minvalue 3 maxvalue 99999  
    drop  sequence  emp\_seq

## \#\#七、plsql ##

##        1.什么是PL/SQL ##

>   
>           PL/SQL是Oracle数据库对SQL语句的扩展，增加了编程语言的特点.  
>           数据操作和查询语句被包含在PL/SQL代码的过程性单元中，经过逻辑判断、循环等操作完成复杂的功能或者计算

###     2.PL/SQL的优点 ###

>   
>         使一组语句功能形成模块化程序开发  
>         使用过程性语言控制程序结构  
>         可以对程序中的错误进行处理  
>         集成在数据库中  
>         有助于提高程序性能

###     3.PL/SQL块的基本结构 ###

DECLARE – 可选部分  
             变量、常量以及类型等  
        BEGIN – 必要部分  
             SQL语句  
             PL/SQL语句  
        EXCEPTION – 可选部分  
              程序出现异常时，捕捉异常并处理异常  
        END; – 必要部分

###     4.PL/SQL块例子： ###

示例1：  
        DECLARE  
          v\_dept\_id    employees.department\_id%TYPE;  
        BEGIN  
             SELECT department\_id INTO v\_dept\_id  FROM employees  WHERE employee\_id = 100;  
             DELETE from departments WHERE department\_id = v\_dept\_id;  
             COMMIT;  
        EXCEPTION  
            WHEN OTHERS THEN  
             ROLLBACK;  
             INSERT INTO exception\_table (message) VALUES ('Some error occurred in the database.');  
             COMMIT;  
        END;  
    ------------------------------------------------------------------------------  
        DECLARE  
          v\_dept\_id    emp.deptno%TYPE;  
        BEGIN  
             SELECT deptno INTO v\_dept\_id  FROM emp  WHERE empno = 100;  
             DELETE from dept WHERE deptno = v\_dept\_id;  
             COMMIT;  
        EXCEPTION  
            WHEN OTHERS THEN  
             ROLLBACK;  
             INSERT INTO exception\_table (message) VALUES ('Some error occurred in the database.');  
             COMMIT;  
        END;  
    ----------------------------------------------------------------------------------

示例2：      
        begin  
         dbms\_output.put\_line('hello world');--调用dbms\_output包中的put\_line函数来输出内容  
        end;  
        注意，如果在sqlplus中，需要执行set serveroutput on来开启显示结果

5.pl/sql中执行的命令

在PL/SQL程序块中可以使用各种SQL命令，但是使用的方法根据命令不同也各不相同  
        使用SELECT 命令，可以从数据库中取出单行数据  
        使用DML命令，修改数据库中的行  
        使用COMMIT 或ROLLBACK 命令控制事务  
        通过EXECUTE IMMEDIATE，执行DDL和DCL语句

##    \#\#八、函数和存储过程 ##

### \#\#\#1.函数 ###

函数用来执行复杂的计算，并返回计算的结果    
    
    语法结构:  
      
    create or replace FUNCTION  funname   \[(parameter,...)\]  
            RETURN datatype  
    IS  
    begin  
          pl/sql\_block;  
    end funname ;

创建一个PL/SQL函数，只返回计算的结果值，函数不能像存储过程那样，对数据库进行操作。  
    只能使用in模式参数传入参数值  
    在函数的声明中，必须包括一个带有数据类型的RETURN 子句，表示函数计算后的最终返回函数的结果类型。  
    在PL/SQL块中至少包括一个有效的RETURN语句，以便返回函数的最终计算结果。

>     好处：  
>         可以实现用简单的SQL语句不能实现的计算  
>         提高查询的效率  
>     使用规则：  
>         只能使用函数，而不是过程  
>         函数中不允许DML语句  
>         形参必须为IN  
>         必须返回Oracle支持数据类型，不能使用PL/SQL数据类型  
>         必须有EXECUTE权限

### \#\#\#2.存储过程        ###

用于在数据库中完成特定的操作或者任务

create or replace PROCEDURE name   
            \[(parameter,...)\]  
    IS  
    --声明变量  
    BEGIN  
       pl/sql\_block;  
    EXCEPTION  
        …………….  
    END;  
      
    parameter的语法如下：  
    parameter\_name \[IN | OUT | IN OUT\] datatype  
            \[\{:= | DEFAULT\} expr\]

示例：

插入数据的存储过程,参数有外部传入

create or replace procedure  insertdeptproc（v\_deptno IN number,   v\_dname IN varchar2,  v\_loc   IN varchar2)  
        is   
        begin  
          insert into dept(deptno,dname,loc)  values(v\_deptno,v\_dname,v\_loc);  
          commit;  
        end;

exec insertproce(77, ‘行政部’,’北京’);

jdbc中调用   call insertproce(77, ‘行政部’,’北京’);

## \#\#九、分页查询 ##

select \* from   
    (  
    select emp.\*,rownum rn   
        from emp   
        where rownum<=10   
    )  
     where rn>=5;

分页查询格式1  
        在查询的最外层控制分页的最小值和最大值。查询语句如下：

SELECT \* FROM     
        (    
        SELECT A.\*, ROWNUM RN     
        FROM  A     
        )    
        WHERE RN BETWEEN 21 AND 40    
      
    分页查询格式2  
          
        SELECT \* FROM     
        (    
        SELECT A.\*, ROWNUM RN     
        FROM  A     
        WHERE ROWNUM <= 40    
        )    
        WHERE RN >= 21

--mysql 分页  
    select \* from emp limit startRow,pageSize;

--sqlserver 分页  
    select top 10 from emp;

--oracle 分页 rownum（伪列）  
      
    --前10行  
    select emp.\*,rownum from emp where rownum<=10;  
    --第5行到第10行  
    select emp.\*,rownum from emp where rownum between 5 and 10;  
      
      select e.\* from    
       (select emp.\*,rownum rn from emp where rownum<=10 order by sal desc) e  
      where e.rn > 5 ;  
      
      select e.\* from    
       (select emp.\*,rownum rn from emp ) e  
      where e.rn > 5 and e.rn <=10;  
      
      select e.\* from    
       (select emp.\*,rownum rn from emp ) e  
      where e.rn between 6 and 10;