oracle

一. ORACLE安装目录

一. ORACLE安装目录结构与卸载

1.开发工具集

10G 【sqlplus (

dos命令:

sqlplus /nolog,

conn /as sysdba

),

       isqlplus(

         http://localhost:5560/isqlplus

        )

        em (

            http://localhost:1158/em

        )

】

11G 【

0G除isqlplus，

sql developer(java编写)

】

 非官方：PLSQL Developer

2. 配置和管理工具

   DBCA 【用来配置和更新数据库】

           NETCA【用来配置网络监听和连接描述符】

          连接描述符位于：%ORALE_HOME%/NETWORL/ADMIN/tnsnames.ora

3. ORACLE文件目录结构 

   安装目录 :F:\oracle

          产品目录:%安装目录%/product/10.1.0

    ORACLE_HOME:%产品目录%/Db_索引编号

           数据文件目录:%产品目录%/oradata/SID名称/

                 1 DBF数据文件  

 

 

                 2 CTL控制文件 【引导文件】

                 3 LOG重做日志文件  【恢复文件】

          连接描述符：%ORALE_HOME%/NETWORL/ADMIN/tnsnames.ora

          监听配置文件【端口】：%ORALE_HOME%/NETWORL/ADMIN/listener.ora

          端口查询文件：%ORALE_HOME%/install/portlist.ini

4.数据管理方式的发展 

   1手工管理阶段

       数据不被保存，还没有文件的概念。一组数据与一个程序直接对应

   2文件管理阶段（ROM）

       数据以文件形式存放。一个应用对应一组数据，应用之间不能共享数据。

   3数据管理阶段（RAM）

       多用户、多应用要共享数据。需要专门的数据管理系统。

5. ORACLE的体系结构 

    ORACLE SERVER

                ---INSTANCE

                   ---内存结构

                         ---SGA（系统全局区，共享池（sql语句），java池（java程序），数据缓冲区（未保存的数据），日志缓冲区）

                        ---PGA（程序全局区 连接的用户占用的内存）

                    ---进程

                         ---后台进程（用于操作数据 PMON,SMON,DBWR,LGWR具体参考《ORACLE体系结构》

                        ---用户进程 （用户连接用户）

                 ---DATABASE

                    1 DBF数据文件  

                     2 CTL控制文件 【引导文件】

                     3 LOG重做日志文件  【恢复文件】

                     4  密码文件(口令文件)，初始化文件（参数文件），dump文件（DBA操作）

6. sql语言操作分类： 

   1 数据定义语言DDL  （定义，操作数据的结构） 【-->java的变量定义】

 

       CREATE : 在数据库中创建新的数据对象 

       ALTER : 修改数据库中对象的数据结构 

       DROP : 删除数据库中的对象 

       DISABLE/ENABLE TRIGGER : 修改触发器的状态 

       UPDATE STATISTIC : 更新表/视图统计信息

       TRUNCATE TABLE : 清空表中数据

       COMMENT : 给数据对象添加注释

       RENAME : 更改数据对象名称

     2数据操作语言DML

       DML(Data Manipulation Language)(CRUD)，用于添加/修改/查询数据库中数据。

       DML包含以下语句：

       INSERT ：将数据插入到表或视图

       DELETE ：从表或视图删除数据

       SELECT：从表或视图中获取数据

       UPDATE ：更新表或视图中的数据

       MERGE ： 对数据进行合并操作(插入/更新/删除)

 

    3数据控制语言DCL

        DCL(Data Control Language)用来向用户赋予/取消对数据对象的控制权限。

       DCL包含以下语句：

       GRANT : 赋予用户某种控制权限

       REVOKE ：取消用户某种控制权限

   4. 事务控制语言(TCL)

        TCL(Transaction Control Language)用来对事务进行管理。

       TCL包含以下语句：

       COMMIT : 保存已完成事务动作结果

       SAVEPOINT : 保存事务相关数据和状态用以可能的回滚操作

       ROLLBACK : 恢复事务相关数据至上一次COMMIT操作之后

       SET TRANSACTION : 设置事务选项

7. 卸载或者重装数据库 

  卸载：

     1停止所有以oracle开始的服务，找到 UI（Universal Installer） 打开卸载界面 点击卸载产品按钮  选择所有的组件 点击删除按钮

 

     2删除服务 使用命令  sc delete 服务名称 删除所有的服务

     3删除注册表 开始->运行->输入 regedit命令打开注册表 找到 HKLM/SofeWare下的oracle 全部删除

     4找到oracle的安装目录 删除 如果无法删除使用软件强制删除

 重装：

     1 如果服务能够启动（ORCL和Listener结尾） 首先sqlplus /nolog 和conn /as sysdba连接  出现字符 历程启动  需要使用命令 startup

     2 服务不能启动  通过杀毒软件 去恢复oracle被删除的部分

     3 如果是Listener结尾启动不了使用netca删掉前面创建的监听 去重新创建一个

     4 如果是ORCL结尾启动不了 使用dbca 删除掉前面创建的orcl的数据库  在重新创建 一个新的

     5 dbca和netca无法打开 点击oracle安装文件点击setup。exe重装数据库

     6 setup.exe无法启动或者多次安装都失败，重装系统  

二 ORACLE管理命令

1 启动sqlplus工具 （ORACLE dba管理数据库的工具） 

           sqlplus /nolog  (无授权信息登录)

              conn /as sysdba(使用本地系统登录 本地用于加入了ORA_DBA组)

           sqlplus 用户名/密码@连接描述符

           sqlplus 用户名/密码  （默认本地ORCL）

2 oracle退出命令 

           exit(quit)  直接退出到dos命令

           disconnect(disconn|disco) 退出连接 在sql命令中

           orapwd（orapwd file=<fname> password=<password>） 重置密码文件

           password(passw) 修改密码 （必须登录之后）

3 操作上一次执行的sql (sql缓冲区中 缓冲区中只能存储一条sql) 

           list(l) 显示上一次缓冲区中sql

           run(r|/)执行上一条缓冲区中的sql

           clear buffer(cl buff)清空缓冲区中的sql

           get sql文件的路径  将文件的内容读取到缓冲区 可以使用 list或者run去查看或者运行

          save(sav) sql文件的路径 将缓冲区的sql写入到文件中

           start(sta|@) sql文件的路径 将文件的内容读取到缓冲区后 并执行 (get sql路径;run)

           edit(ed) 使用ed 文件路径修改文件的内容 或者使用ed命令修改缓冲区中的内容

           clear scr（ dos清屏cls） 在sql命令下清除屏幕上的文字

           spool 文件路径   记录当前用户的所有操作以及输出   spool off（终止）

4 启动和关闭 

        启动数据库orcl服务：

        sqlplus /nolog,

        conn /as sysdba

        startup

         nomount  数据库的instance已经打开  数据库没有打开

    mount数据的控制文件已经打开 并且和instance连接 但是不能远程连接

       open 数据库已经打开并且可以远程连接

       关闭数据库

       shutdown 启动或者关闭监听服务

       dos命令下  lsnrctl start|stop 检查连接描述符是否能连接  tnsping 描述名称

5. 连接描述符(客户端文件）【重点重点重点】

    oracle如果需要连接数据库 必须要确定三个元素

      ip地址 确定到 机器

      端口  确定机器上的某个机器（oracle的进程）（oracle默认的端口 1521）

      sid   确定oracle进程中的某个数据

    如果需要连接到任意一台机器的数据库 必须要配置这个三个元素

    这三个元素统称为 连接描述符  它的文件位于

      %ORACLE_HOME%/network/admin/tnsnames.ora

     该文件 中 #开头表示注释

   #以下 表示一个完整的连接描述符配置  别名可以任意

   #以下 指定了 ip 端口  sid

   # 原理 就是 Socket socket=new Socket("192.168.11.44",1521)

  clkdb =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.44)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

   )

6. 服务器监听文件(服务器端)【重点重点重点】

      %ORACLE_HOME%/network/admin/listener.ora

 

  在这个文件中 SID_LIST_LISTENER 必须添加

  (SID_DESC =

      (SID_NAME = ORCL)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (GLBAL_DBNAME = ORCL)

    )

  完整的配置如下

   SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

    (SID_DESC =

      (SID_NAME = ORCL)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (GLBAL_DBNAME = ORCL)

    )

  )

 

  如果需要其他客户端连接 需要将localhost修改为本机ipd

  LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.10)(PORT = 1521))

    )

  )

  远程 连接的测试语句

  create table aa (id number);

  select * from  tab where tname='AA';

 

7. 窗口

   dos命令窗口  只能执行dos的命令

   sqlplus连接 sql命令窗口

      sqlplus /nolog  未登录 进入sql命令下

   在sql命令下

      host dos命令

      clear scr 清除屏幕（host cls）

      conn 用户名/密码@连接描述符  切换数据库登陆

      exit|quit 退出sql命令到dos命令下

      discon  断开连接 不退出到dos命令

      password 修改密码 必须要登陆

      show user 显示当前用户

      SET PAGESIZE 100  --表述输出多少行 后重新显示表头

      SET LINESIZE 120 --表示 每一行现实的字符数

       desc 对象名（一般对象就是表） 显示表的结构

 

 

8. oracle的启动和关闭

    OracleServiceORCL  【oracle的主服务 必须要 启动该服务器 数据库才能启动】

    OracleOraDb11g_home1TNSListener 【监听服务 用于等待其他的客户端连接】

    关闭主服务

    1>net stop OracleServiceORCL  

    2>   sqlplus / as sysdba

       shutdown

    开启主服务

    1>net start OracleServiceORCL  

    2> sqlplus / as sysdba

       startup

    关闭监听服务

    lsnrctl stop

    启动监听服务

    lsnrctl start

 

 

9. 缓冲区命令

   （缓冲区就是上一次执行的sql语句的缓存）

    list|l命令 显示上一次执行的sql语句

    run|r|/ 执行上一次缓冲区中的sql语句

    clear buffer 清空缓冲区

    save 文件名 将缓冲区的sql写入文件

    get 文件名 将文件中的内容写入缓冲区

start 文件名 将文件中的内容写入缓冲区 并执行（get  + run）   

 

三. ORACLE数据类型

1. 字符串类型：用单引号引起来的字符序列。 

 

           CHAR(length)：固定长度字符串，不足自动以空格补齐长度，最多2000个字节。

 

           如：CHAR(10)  使用length(列) 获取的是定义的长度（length）

 

           VARCHAR2(length)：可变长度字符串，最多4000个字节。 【常用的字符串类型】

           如：VARCHAR2(10)  使用length(列) 获取的是实际数据的长度

           举例  title char(10) length(title)永远都是10

                content varchar2(10) 插入数据 'test' length(content)是实际数据的长度 4

2.数值类型： 

           NUMBER[(precision, scale)]：数值型，可以存储整数、浮点数。最高精度38位。如果没有指定最大位数和精度，就存储38位精度的数字。

           NUMBER(24)  最多24位 整数类型；

           NUMBER(5,3)  总和最多5位，其中小数最大是3位 整数位最大 5-3

           NUMBER  最大默认38位  整数和小数位总和不能超过38位         

3.日期类型

           DATE：存储日期和时间，精确到秒。时间值 【重要的类型】

               默认存放格式：“DD-MON-YYYY”

               默认显示格式：“DD-MON-YY

            java常用日期格式 yyyy-MM-dd hh:mm:ss

            orace转换日期格式  yyyy-MM-dd hh:mi:ss

            select sysdate from dual --输出的是date类型

            select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from  dual  --转换成了char类型

            TIMESTAMP[(seconds_precision)]：存储日期、时间和时区信息，带小数位的秒。时间戳

               如：TIMESTAMP(3)   秒后面小数点为3位。（最多可9位）

           大对象(Large objects)类型：最大存储128TB

                CLOB：Character LOB，用于存储字符数据。

               BLOB：Binary LOB，用于存储二进制数据。如图形、视频剪辑和声音文件。

               BFILE：用于存储二进制文件指针。

4. sql语法注意点 

       关键字不区分大小写 但是【字符串区分大小写】。

       表名和列名不区分大小写。

       语句以分号;结束

       单行注释使用--  多行注释 /**/

 

 

5.解锁scott账号 

    conn /as sysdba --以dba的账号登录

    alter user scott account unlock --解锁scott账号 

    conn scott/tiger  --密码是tiger     

6.显示表结构 

    dos命令下 可以使用 以下命令查看表结构

    desc 表名称  可以列出表结构 

    select * from tab; 查询当前用户下的所有的表   ，视图，同义词

7.常用的sql语句 

   select用于crud【create read update delete】 查询 是用于查询和筛选数据 【重中之重】

   使用java去理解 sql语句

   /*

    List<Emp> list=new ArrayList<Emp>();

    //select * from emp;

    syso("EMPNO ENAME JOB MGR....");

    for(Emp emp:list){

          syso(emp.empno+" "+emp.ename+" "+emp.job......);  

    }

    select ename as 雇员名称,sal as 薪水 from emp;

    syso("雇员名称 薪水....");

     for(Emp emp:list){

          syso(emp.ename+" "+emp.job);  

    }

   */

 

   select ename as 雇员名称,sal as 薪水 from emp;

   select语法结构   SELECT 【列名, 列名2…】| * FROM 表名 where 条件 ;

   【在oracle中字符串 使用''而不是""】

   查看当前方案中的表和其它数据库对象： 

    SELECT * FROM tab;

   查询指定表的所有列数据：

   SELECT * FROM 表名;

   查询指定表的指定列数据：

   SELECT 列名, 列名2… FROM 表名;

   可以在SELECT语句中使用算术运算符：+、-、*、/

   为查询的列取别名：

   SELECT 列名 [AS] 别名, … FROM 表名;

    使用||做连接。 

    举例： select '钱：'||'$'||sal as sal from emp

   使用DISTINCT消除重复内容。 

    举例：select distinct sal as sal from emp

   条件过滤（where）语句： 

       SELECT [DISTINCT] * | [列名 [别名],…]

        FROM 表名

       WHERE 条件;

        WHERE子句中的条件表达式：

       可以包括运算符(算术、比较、逻辑)

        可以使用()。

       可以使用常量、列、函数。

8.oracle常用的运算符 

    算术运算符：+、-、*、/

    连接操作符：将多个字符串或数据值合并成一个字符串 ||

    比较运算符： =、!=（或<>）、<、>、<=、>=

    ANY(值1,值2,值3…)    与列表中的任意一个值进行比较

    ALL(值1,值2,值3…)    与列表中的所有值进行比较

    in(值1,值2,值3)  列的值包含在所给的值中

 

举例：

--any表示等于其中的任意一个值  一般常用是使用in

       select * from emp where job=any('SALESMAN','MANAGER')

       select * from emp where job in('SALESMAN','MANAGER')

   --all 表示和所有值进行 比较 一般用于等于

       select * from emp where job!=all('SALESMAN','MANAGER')

       select * from emp where job not in('SALESMAN','MANAGER')

 

 

       逻辑运算符： 

       AND、OR、NOT

         举例：

             --等价于java &&

select * from emp where sal>2000 and job='MANAGER'

     --等价于java ||

select * from emp where sal>2000 or job='MANAGER'

     --等价于java  !

select * from emp where not sal>2000

        SQL 运算符的优先级从高到低的顺序是：

       算术 、连接、比较、逻辑（NOT、AND、OR）  

      模糊匹配 like

 _代表一个字母  %代表0个或者多个字母

        select * from emp where job like '_A%';  

9.大对象类型(large obects) 最大存储128T

>1.CLOB: CHARACTER LOB 用于存储字符数据

>2.BLOB: BINARY LOB 用于存储二进制数据(图形,视频,声音文件)

>3.BFILE: 用于存储二进制文件指针

四.oracle表的查询 

 1.表的排序

      排序语句：

       SELECT [DISTINCT] * | [列名 [别名],…]

         FROM 表名

        WHERE 条件

        ORDER BY 排序的列 [ASC | DESC],…;

 

       select * from emp order by hiredate desc   ( desc表示降序 从大到小 asc 表示升序)

 

       select * from emp where where job='MANAGER' order by  hiredate desc; (按条件查询出结果后排序)

 

       select * from mep order by hiredate desc,sal desc (按条件一升序后 相同的条件一在进行条件二排序)

 

2. Oracle的伪列 

 

      别名

       在select语句中 每一个被查询的表都有一个别名 默认就是它的表名 可以给表添加一个别名 添加的别名覆盖默认的别名

       select emp.ename from emp t; --错误

       select t.ename from  emp t;

       --如果多表查询时  每一个表都有相同的列名 这时必须要使用 别名

       select t.deptno from emp t,dept d;

 

 

      ROWID 是表中每一条记录的唯一标识符，数据库内部使用它来存储行的物理地址。

 

       该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的某一行。

 

       无法使用update语句修改

 

     ROWNUM 是SQL查询返回的结果集中每一行的行号。 【重点】

       可以用它来限制查询返回的行数。

       ROWNUM是先查到结果集之后再加上去的一个列 。

 

 

         在oracle中使用rownum来进行分页

         select t.* ,rownum as rn from emp where rownum>=10 and rownum<=20这是错误的

 

         因为行的循环查找 从索引1 开始  第一条记录的索引为1  不满足 循环第二条时 记录索引从1 开始 。。。。所以的行的行号都是1 永远不满足

           select * from (select t.*,rownum as rn from emp t) where rn>=10 and rn<=20 这是正确的  （一般使用） 

 

 

                  分页另一种写法

           select * from (select t.*,rownum as rn from emp t where rownum<=20) where rn>=10 正确的 

         select * from emp where rownum<=10 这是正确

        记录：rownum大于1的记录永远不可能成立  rownum<等于任何值都成立

 

 

五.oracle的函数

 

 

1.字符串函数 

      lower(n) 将字符转换成小写         对应java String.toLowerCase

         select lower('AAAA') from dual;  --aaaa

         select lower(ename) from emp;    --列输出结果全部小写

 

      upper(n) 将字符串转换成大写       对应java String.toUpperCase

 

      replace（列名,被替换的字符串，替换的字符串）    对应java String.replace或者 replaceAll

          select ename,replace(job,'MANAGER','经理')  from emp; --job列中 所有的MANAGER都被替换成了经理

          select replace('AAAtestgggg','test','测试') from dual; --AAA测试gggg

 

      instr(列名,被搜索的字符串) 

          select instr('AAAtestgggg','test') from dual; --4 索引从1开始

 

      substr(列名,开始位置,总长度) 

          select instr('AAAtestgggg',4,3) from dual; -- java substring  根据 被截取的字符串.substring(开始位置，结束的位置【不包括结束的位置】)  

  javascript 被截取的字符串.substr（开始位置，长度） 被截取的字符串.substring

                                                        oracle substr

 

      concat(参数1，参数2) 

          select concat('a','b') from dual;  --- ab  等价于 'a'||'b'    java "aaa"+"bbb"  

 

      length(列名) 获取字符的长度      java  String.getLength();

      trim(列名) 去空格  

      ltrim 去左侧的空格

      rtrim 去右侧的空格 

 

         nvl(列名,值) 当列的值为空时 输入第二参数的值 如果不为空 输出当前列的值   【重要】

    select nvl('','bac') from dual -- '' is null 所以输出 bac

 

 nvl2(列名,值1,值2) 当列的值为空时 输出第三个参数的值 如果不为空输出第二个参数的值 【重要】 

 

 

         decode(列名,条件1，值1，条件2,值2......,条件n,值n，默认值) 当列的值等于条件n时输出值n 【重要】

 select decode('2','0','男','1','女','人妖') from dual

2.数字函数            

      mod(5,2) 取余数 等价于java 5%2 

      round(n) 对整数位进行四舍五入 满5进1 

      round(n,p) 对小数位进行四舍五入 比如 round(5.6767,2) 输出为5.68 

      trunc(n) 截断小数位 只保留整数位 

     trunc(n,p) 截断小数位意外的位数 比如 trunc(5.666,2) 输出为5.66 就是保留p位小数

 

 

3.日期函数            

      sysdate 获取系统的当前时间  【重点】

 

      add_months(日期,月数) 将日期加上月数 并返回  比如 (sysdate=2016-3-3)+5个月 =2016-8-3

 

      select sysdate+天数 from dual 将当前日期加上某个数字 表示+天数

 

      months_between(日期1，日期2)  比较日期1和日期2的相差的月份数  结果=日期1-日期2

 

      last_day(日期) 返回日期对应月份的最后一天

 

      round(日期,格式)

            YEAR 获取当年的第一天 

            MONTH 获取当月的第一天 

            DAY 获取 第一个周末的第一天 

 

 

      next_day(日期，第几天) 获取当前日期下一个星期的第几天  

 

    转换函数 【每一个都是重点】

 

      to_date(日期字符串,日期格式)  字符串的格式必须由第二个参数来判断 

 

日期格式 ：

 

年 yyyy，月mm,日dd,小时（hh12小时制，hh24 24小时制） 分钟 mi 秒 ss

举例 :select to_date('2015/03/09','yyyy/mm/dd') from dual to_char(数字) 将数字转换成char类型 

 

to_char(日期,日期格式)  将日期转换成char类型  日期格式 可以任意 输出的内容 根据日期的格式而定

举例   select to_char(sysdate,'hh24')  输出24小时制的当前时间的hour

 

to_number(字符串数字) 将字符串的数字转换成number类型 

举例    to_number('123')输出数字 123  

4.常用的聚合函数

   什么是聚合 ：将多行记录压缩成1行或者多行进行演示

     1 count(*)  count(1) count(列名) 统计数据行的 个数 【重点】

     速度比较（oracle9i之前的说法）

       select count(*) --是最慢的  统计表的行数 先去数据库中了解表的结构

       select count(1) --第二慢的  找到数据库中表的一列

       select count(emp.empno) from emp; --第三慢的 因为已经指定了列名 不需要查表结构

       select count(rowid); --rowid直接定位的物理地址

     2 sum(数字的列) 统计当前列的总和

     3 avg（数字类型的列） 同行当前指定列的平均数

         select sum(sal)/count(rowid) from emp;

         select avg(sal) from emp;

     4 max(任意 列) 统计当前列最大的那个值

     5 min(任意列) 统计当前列种最小的那个值

 

6 wm_concat 合并结果集函数 

       将部门下的所有员工信息,列表显示

列如:研发部 张三/李四/王五

select d.dname,wm_concat(e.ename) from emp e inner join dept d on e.deptno=d.deptno group by d.deptno,d.dname;

7.exists函数

select * from emp e where exists (

select deptno from emp group by deptno having min(sal)=e.sal

)

5.正则表达式函数

>1.REGEXP_SUBSTR

     REGEXP_SUBSTR函数使用正则表达式来指定返回串的起点和终点

select regexp_substr('MY INFO: Anxpp,23，and boy','[[:digit:]]',1,2) from users;

select regexp_substr('MY INFO: Anxpp,22，and boy','my',1,1,'i') from users;

 

>2. REGEXP_INSTR

REGEXP_INSTR函数使用正则表达式返回搜索模式的起点和终点（整数）。如果没有发现匹配的值，将返回0。

select regexp_instr('MY INFO: Anxpp,23，and boy','[[:digit:]]') from users;

>3. REGEXP_LIKE

     通常使用REGEXP_LIKE进行模糊匹配。

select name from users where regexp_like(phone,'666');

>4.REPLACE和REGEXP_REPLACE

     REPLACE函数用于替换串中的某个值。

select replace('MY INFO: Anxpp,23，and boy','an') from users;

 

下面演示使用该函数计算某字串在源串中出现的次数：

select (length('MY INFO: Anxpp,23，and boy')-length(replace('MY INFO: Anxpp,23，and boy','an')))/length('an') from users;

 

REGEXP_REPLACE是REPLACE的增强版，支持正则表达式，扩展了一些功能。

select regexp_replace('电话：023  5868-8888 邮箱：anxppp@163.com',    '.*([[:digit:]]{3})([^[:digit:]]{0,2})([[:digit:]]{4})([^[:digit:]]{0,2})([[:digit:]]{4}).*',

     '(\1)\3\5'

) phone from users;

>5. REGEXP_COUNT

REGEXP_COUNT函数返回在源串中出现的模式的次数，作为对REGEXP_INSTR函数的补充。

虽然COUNT是一个集合函数，操作的是行组，但是REGEXP_COUNT是单行函数，分别计算每一行。

select regexp_count('MY INFO: Anxpp,23，and boy','an') from users;

六.高级查询

1.子查询【重点】

select * from emp where sal>2000 and job='MANAGER'

根据子查询的位置 可以分为 表子查询 列的子查询 条件的子查询  根据数据返回的行和列 分为 单行单列 多行多列多行单列

-- 1表子查询（虚表 内存表）   比条件和连接 更容易理解（多行多列子查询）

select * from (select * from emp where sal>200) where job='MANAGER'

 

--2 列子查询 每个子查询只能返回一行记录（单行单列子查询）

--查询部门名称

select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

select ename,(select dname from dept where rownum=e.empno) as dname from  emp e;

 

--3 条件子查询

--查询所有雇员为 ACCOUNTING的雇员

select deptno from  dept where dname='ACCOUNTING'

select * from  emp where deptno=10

--=只能等于一行  单行单列子查询

select * from  emp where deptno=(select deptno from  dept where dname='ACCOUNTING');

 

--查询所有雇员为 ACCOUNTING RESEARCH的雇员 (多行单列子查询)

select * from  emp where deptno=any(select deptno from  dept where dname='ACCOUNTING' or  dname='RESEARCH');

select * from  emp where deptno in(select deptno from  dept where dname='ACCOUNTING' or  dname='RESEARCH');

 

 

2.集合

{1，2，4}^{2,3,4}={2,4}

{1，2，4}U{2,3,4}={1，2，3,4}

{1，2，3}-{2，3，4}={1}

集合的操作

 

集合操作符：合并多个查询结果

UNION ALL：将多个查询结果合并到一个结果中，有重复行【重点】

UNION：将多个查询结果合并到一个结果中，没有重复行（并集）【重点】

INTERSECT：返回两个查询结果中共有的行 (交集)

MINUS：返回从第一个查询结果中减去第二个查询结果中相同的行之后剩余的行(差集)

 

 

3.复制 表

 create table dept1 as select * from dept

select * from  dept;

select * from dept1;

--交集

select * from dept intersect select * from  dept1;

--并集 union 去掉重复行

select * from dept union select * from  dept1;

--并集 uinon all 不会去重复行

select * from dept union all select * from  dept1;

--差集

select * from dept1 minus select * from  dept;

 

4. 分组

     1.什么是分组

      分组指定的条件  【相同的数据数据被分为一组】最终查询的结果

        就是分组的组数

      表名 Test

       A列  B列

       1  a

       1  b

       2  c

       2  d

       select A from  Test group by A

       --因为根据A这一列 相同的值被分为一组 最终被分为两组 只有两条被显示

         1

         2

       select A,B from Test group by A  --这是错误的

       两组都被压缩成一行

       1 组 存在a和b两个值  不能被压缩成 一行 只能使用聚合函数压缩成

             一行才能显示

 

     --查询部门中薪水最高的薪水

      select * from emp order by deptno;

     2.排序 是针对分组产生 之后的结果在进行排序

      select deptno,max(sal) from emp group by deptno order by deptno

 

     3.having子句 是对分组之后的结果进行筛选

      --查出部门最高的薪水>3000  

       select deptno,max(sal) from emp group by deptno having max(sal)>3000

 

 

5. 表连接【重点】

     单表查询 -》多表连接查询 

   笛卡儿积【交叉连接】{1,2,3}交叉{4,5} 可以参考【笛卡儿积.png】

     1     

     2      4

     3      5

     最终产生的结果  14 15 24 25 34 35   交叉产生的记录数就是 两个集合的个数的乘积

  数据库表的笛卡儿积

    数据行的笛卡尔积

   select * from table1，table2

  举例说明 92语法的内连，左外连 ，右外连

     用户表（UserInfo）

     用户id 用户名

       1     test

       2     user

     文章表 (Article)

       文章id 文章内容 用户id

        1       java      1

        2       oracle    3

 

    1  test   1  java  1

    1  test   2   oracle 3

    2  user   1   java  1

    2  user   2  oracle  3

    //86语法

    select * from userinfo u,arcticle a where u.用户id=a.用户id

    1  test   1  java  1

 

    //92语法

     内联必须都满足条件才显示记录

        select * from userinfo  inner join arcticle a on u.用户id=a.用户id  --是86语法的改进 结果和86语法是相同

     外联

         --左外连 以左边的表为主表 左表如果存在某条记录和右中所有的记录都无法关联 就保留一条记录

        select * from userinfo  left join arcticle a on u.用户id=a.用户id  

         1  test   1  java  1

         2  user   null null null

 

   建议

     多表查询需要先找到两张表的关系

     如果需要做多表连接查询尽量使用 内联 找到两张表的关系  直接将条件 在on后  (如果有特殊情况 如果主表中的记录没有连接也要输出 只能使用左外连接)

 

 七.sql语言操作分类【需要背 面试需要】：

 

1 数据定义语言DDL （DATA DEFINE LANAGUAGE）  （定义，操作数据的结构） 【-->java的变量定义】 

       CREATE : 在数据库中创建新的数据对象 

       ALTER : 修改数据库中对象的数据结构 

       DROP : 删除数据库中的对象 

       DISABLE/ENABLE TRIGGER : 修改触发器的状态 

       UPDATE STATISTIC : 更新表/视图统计信息 

       TRUNCATE TABLE : 清空表中数据 

       COMMENT : 给数据对象添加注释 

       RENAME : 更改数据对象名称 

2数据操作语言DML 【重点关注 工作必用】

       DML(Data Manipulation Language)(CRUD)，用于添加/修改/查询数据库中数据。

       DML包含以下语句：

       INSERT ：将数据插入到表或视图 

       DELETE ：从表或视图删除数据 

       select ：从表或视图中获取数据 

       UPDATE ：更新表或视图中的数据 

       MERGE ： 对数据进行合并操作(插入/更新/删除) 

3.数据控制语言DCL 

       DCL(Data Control Language)用来向用户赋予/取消对数据对象的控制权限。

 

       DCL包含以下语句：

       GRANT : 赋予用户某种控制权限

       REVOKE ：取消用户某种控制权限

4. 事务控制语言(TCL) 【重点】

 

       TCL(Transaction Control Language)用来对事务进行管理。

 

       TCL包含以下语句：

       COMMIT : 保存已完成事务动作结果 

       SAVEPOINT : 保存事务相关数据和状态用以可能的回滚操作 

       ROLLBACK : 恢复事务相关数据至上一次COMMIT操作之后 

       SET TRANSACTION : 设置事务选项 

 

5.crud （create read update,delete）【重点】

     所有的数据库  都是使用这四个单词来描述增删改查 isud  (insert select update delete)

     1 insert语句

       语法：

          INSERT INTO 表名[(列名1,列名2,…)] VALUES(值1, 值2,…);   --列名的个数和值的个数 必须相同              【表列较多 建议使用】

          insert into 表名  values(值1，值2 .。。)   --如果没有指定列名  值得个数必须和表中所有的列的个数相同 列的类型必须匹配   【表列较少建议使用】

          INSERT INTO 表名[(列名2，列名1,…)] VALUES(值2，值1, …); 值得顺序和列的顺序保持一致

          insert into student select '11','test1',25,'女'  from dual   --根据常量来模拟

          insert into student1 select * from student      --可以根据 insert into select语句来进行复制表的数据 （备份表）    seelct查询的列数必须和insert插入的表列数一致 并且列类型匹配

 

日期类型的插入

              insert into student1 values(7,'a',20,'男',sysdate)

   insert into student1 values(7,'a',20,'男',to_date('1987-06-18','yyyy-MM-dd'))

  

2 update语句（注意在修改之前一定先将条件写好）

       语法：

           UPDATE 表名 SET 列名=值[,列名2=值2,…] [WHERE 修改条件];  --

3 delete语句（注意在修改之前一定先将条件写好）

            delete scott.emp where empno='8110'

八.事务【重点】

     事务表示 一段sql的执行 要么同时成功 要么同时失败

任意的dml语句【数据的修改】 必须要提交数据（因为用户的误操作 导致数据的损坏 有可能导致无法挽回的灾难  可以使用 事务的机制来解决这类问题）

 

     commit（提交） 一旦提交了事务 数据就被修改到物理的数据文件中  （ctrl+s） 保存到文件

 

rollback(回滚) 如果除了误操作 需要回滚当前事务 （记事本ctrl+z）(撤回)

事务的acid属性

 

原子性(Atomic)：

指整个数据库事务是不可分割的工作单元。原子性确保在事务中的所有操作要么都发生，要么都不发生。

举例：

--被一起的执行的dml语句位于同一事务当中

insert into scott.emp values(8111,'test','MANAGER',7934,sysdate,3000,100,10);

insert into scott.emp values(8112,'test','MANAGER',7934,sysdate,3000,100,10);

insert into scott.emp values(8113,'test','MANAGER',7934,sysdate,3000,100,10);

rollback;

 

一致性(Consistency)：

一旦一个事务结束了(不管成功与否)，系统所处的状态和它的业务规则是一致的。即数据应当不会被破坏。

举例：

一旦数据被约束成某种 类型或者某些值那么就必须对应满足这些类型或者某些值

Insert into scott.emp values(8113,'test','MANAGER',7934,'2012-12-15',3000,100,10);

 

隔离性(Isolation)：

指多个事务同时操作同一数据时，每个事务都有各自的完整数据空间。未提交的数据  在其他的客户端中是无法看到因为因为隔离性

 

持久性(Durability)：一旦事务完成，事务的结果应该持久化 存到物理磁盘中（就是断电了下次还能 看的到 ）

 

事务的提交方式【了解即可】

默认数据库使用 手动（非自动）提交方式需要操作者 使用commit关键字来提交

 SQL> show autocommit;

 utocommit OFF

设置自动提交

SQL> set autocommit on;

SQL> insert into scott.emp values(8113,'test','MANAGER',7934,sysdate,3000,100,10);

已创建 1 行。

提交完成。

 

隐式的事务提交（不需要使用commit和rollback也会自动提交）

1 自动隐式提交事务：执行一个DDL语句、执行一个DCL语句、从SQL*Plus正常退出(exit,quit)

2自动隐式回滚事务：

强行退出SQL*Plus、客户端到服务器的连接异常中断、系统崩溃  

回滚点

savepoint a;

insert into scott.emp values(8113,'test','MANAGER',7934,sysdate,3000,100,10);

savepoint b;

insert into scott.emp values(8114,'test','MANAGER',7934,sysdate,3000,100,10);

rollback to a; --没有记录被插入

rollback to b  -- 8113被插入 8114被回滚

commit;

事务引发的问题和隔离级别【重点】

事务 对同一行数据进行操作时  必须等先操作的客户端提交或者回滚事务后 另外一个客户端才能操作数据（锁）

事务操作同一行数据出现的问题 （并发的引发的问题）

 

脏读  【是一种错误】

读取到了用户未提交的数据  oracle数据库永远不会出现脏读

 

不可重复读【只是一种设计问题】

在读取的事务中 两次重复读取同一行数据发现数据 被更改了

 

幻读【只是一种设计问题】

在读取的事务中 两次读取某一个条件的多条数据时发现多出了几条数据

  

隔离级别（就是为了解决事务操作同一份数据导致的问题）  

EAD UNCOMMITTED： 读未提交数据。脏读、不可重复读、幻读都可能发生。它的事务隔离性最低。

READ COMMITTED：读已提交数据。解决了脏读。【数据库的默认的隔离级别】

REPEATABLE READ：可重复读。解决不可重复读，脏读。

SERIALIZABLE：串行化。解决任何并发事务问题。最严格的事务

Oracle只支持READ COMMITTED和SERIALIZABLE。

默认为READ COMMITTED

 

设置隔离级别

alter session set isolation_level=serializable;--如果需要演示 必须每个客户端都要执行

  

九.用户管理 

 1.用户操作

    创建用户 create user jyb identified by jyb 【密码区分大小写】

    修改用户 alter user jyb identified by test

    锁定用户  alter user jyb account lock;

    解锁用户 alter user jyb account unlock;

    查询所有的用户       select * from all_users;

    删除用户  drop user jyb 【cascade代表所有关联的对象都被删除】;

2.角色操作 

  创建角色  create role stu;

  删除角色  drop role stu; 

 给角色添加功能权限  grant create session to stu

 给用户添加角色            grant stu to jyb     

 通过dd表可以查询所有的系统表：select * from dictionary where table_name like '%ROLE%'  

 查询所有的角色 select * from dba_roles;

 

3.权限操作 

  系统权限   （针对某些对象进行修改（DDL）的权限）

  grant 系统权限 to 用户|角色

  所有的系统权在 dba_sys_privs表中可以查询

  grant create user to jyb --jyb就能通通过  create user 创建用户

  回收系统权限

  revoke 系统权限 from 用户|角色

 

  对象权限 （针对某个对象（表）进行数据操作（DML）（crud）的权限）

  grant 操作（CRUD|RW） on 对象|all to 用户|角色

  grant insert on jyb.course to jyb  --jyb用户就拥有了访问course表的权限

  回收对象权限 

  revoke  操作（CRUD|RW） on 对象|all from 用户|角色

  角色 （将对象权限或者系统权限赋予给角色就是角色权限）

  系统默认都预留了一些角色 这些角色都拥有系统较高的权限 比如 DBA

4.备份和恢复 （dos备份）

    热备份

       (必须要先连接数据库后  备份数据后导出文件)

 

    冷备份（DBA）

      （不需要连接oracle数据库 直接备份文件）

 

    热备份举例：

    备份（DOS）：

       1》客户端备份 【文件被保存在当前的客户端】【一般使用客户端备份】

       exp 用户名/密码@连接描述符  file=保存的文件.dmp   【tables=(表1，表2....)】  owner=方案名

  

    2》服务器备份 【文件被保存在服务器上】

    被导出的用户必须要添加 grant create any directory to 用户名

    create directory 目录名称 as 'c:/test'

     expdp 用户名/密码@连接描述符 directory=目录名称 dumpfile=jybdp1.dmp  tables=(表1，表2....)

 

还原:

    客户端 还原

    imp 用户名/密码@连接描述符  file=保存的文件.dmp full=y ignore=y

 

    服务器 还原

impdp jyb/jyb@orcl directory=jybdir dumpfile=jybdp1.dmp  tables=(表1，表2....)  

 

十.表操作 

1.crud表的操作

  create user learn_object identified by test;

 

  --给予权限 

   grant dba to learn_object;

 

   -第一种 创建表 

    create table tb_userinfo(

    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

    username varchar2(20) not null, --not null 表示当前的列 不允许插入 null 和 ''

     sex number default 0   --默认值 default 值 当插入数据为空时 自动填上默认值 插入不为空的值 插入当前值

     )

 

     第二种复制表

     create table 表名称 as select * from 表 [where 1!=1]

 

     第三种类型赋值法

     create type  userobj as object (

      d number,

        uname varchar2(20),

        createDate date

     )

  

      create table userinfo_cpy_type of userobj

 

      --修改表名称 

      rename 旧表名to 新表名

 

       --修改列名 

      alter table userinfo rename column 旧列名 to 新列名

 

        --修改列类型 

       alter table userinfo modify username char(20)  

 

        --添加列 

       alter table userinfo add age number default 18

 

        --删除列

       alter table userinfo drop column age 

 

       --查询表的结构

       desc 表名  --命令下

       select * from all_tab_columns where table_name='USERINFO1'  --数组字典表（DD） 在sql语句中

 

 

 

2. 删除操作 

 

 

       删除表：把表中所有的行和表结构都删除。

   DROP TABLE 表名;--Oracle中删除表时并没有直接删除，只是放置到“回收站”

 

   显示回收站中的对象：SHOW RECYCLEBIN;

    恢复回收站中的表：FLASHBACK TABLE 表名 TO BEFORE DROP;

    删除回收站中的表：PURGE TABLE 表名;

    彻底删除 不进回收站：DROP TABLE 表名 PURGE;

 

    截断表：删除表中所有的数据行，重置表的存储空间。

       TRUNCATE TABLE 表名;

        delete from 表名

 

       区别 ：

          1  truncate是ddl语句  delete 是dml语句 都可以删除数据行

          2  truncate不需要提交事物 delete需要提交事物

          3  truncate删除表数据及表的存储 无法恢复 delete删除 可以使用归档日志恢复   

          4  delete可以删除指定条件的记录

          5  如果表结构损坏了 导致数据无法访问 必须重置表 使用truncate 因为delete 不会删除数据 只是隐藏数据 （查询的效率依然地下）

             如果需要根据条件删除 并且希望可以恢复 一般使用delete

 

3. 表分区 （了解）

           意义 ：可以容灾（出现事故后 还有一部分数据保留）

 

    范围(range(字段名))

 

            范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期

 

       格式:

 

            create table XXX() partition by range(表中数值类型的字段名)(partition 分区名 values less than 具体值  [tablespace 表空间名] ,…)

 

    列表(list(字段名))

            该分区的特点是某列的值只有几个

 

           …partition by list(字段名) (partition 分区名 values (值列表)值  [tablespace 表空间名] ,…))

 

    哈希(hash)

 

 

            分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。

 

 

           …partition by hash(表中数值类型的字段名)(partition 分区 [tablespace 表空间名] ,…)

 

 

            … partition by hash(表中数值类型的字段名)PARTITIONS n
STORE IN (s1,….sn)

 

 

    列表(list(字段名))

 

    复合分区(分区组合)  

 

 

 

十一. 约束 

 

      1》约束的查询方式

       --可以通过dd表来查询约束

       select * from all_constraints where table_name='USERINFO'

        约束的类型

         C (Check约束)

         P (主键约束)

         U （唯一约束）

         R (外键约束)

 

        --直接添加不为空的约束

 

       2》not null 约束

       create table tb_userinfo(

 

 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

 

 

                   username varchar2(20) not null, --not null 表示当前的列 不允许插入 null 和 ''

 

 

                )

        --constraints 约束名 可以指定约束名称 如果不指定约束名称 系统会自动分配一个名称  列存在了 添加约束 需要考虑 真实的数据行是否和约束冲突 因为要满足一致性

        alter table userinfo drop constraints nu_age

        alter table userinfo modify age number [constraints nu_age]    not null

 

        --直接添加不为空的约束 给约束添加别名

 

        3》主键约束

       create table tb_userinfo(

 

 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

 

 

                   username varchar2(20) [constraint nullable_username] not null, --添加别名后 会添加一个别名对应的约束

 

 

               )

        

        --主键 它的意义在于 添加一列 用于唯一标识当前行  uniqie约束 知识表示 当前的列不允许出现重复的的值

        --在技术上  主键唯一 不能为空  uniqie 唯一能为空

       alter table userinfo drop constraints SYS_C0011165

       alter table userinfo modify userid constraints pri_userid primary key

 

 

 

       4》check约束

 

        --通过check的方式 添加不为空的约束  check的语法要和where条件一致         

 

     

        create table tb_userinfo(

 

 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

                   username varchar2(20) [constraint check_username]  check(username is not null) --通过添加约束的方式 添加not null

        )     

 

        alter table userinfo modify sex VARCHAR2(20) constraints chk_sex check(sex in('女','男'))

        alter table userinfo modify age number constraints chk_age check(age between 1 and 100)

         

 

        --check约束必须满足where条件  不能使用子查询

       create table tb_userinfo(

               userid number primary key,

               username varchar2(20) not null,

               sex number constraint check_sex check(sex in(0,1))

        )    

 

        5》外键约束

 

       --定义外键  外键的定义 必须能唯一定位到外键对应的记录 比如 知道学生信息后 就唯一确定了他所在的班级

           --外键 如果某个表中存在一列 关联到另外一张的主键 这一列叫做外键列

           --如果某个列被添加了外键 外键列的值 必须是 引用表中主键的值 或者是null

 

       --班级表

 

 

       create table tb_grade(

           cid  number primary key,

           cname varchar2(20) not null

        )

        --cid number check(cid in(select cid from tb_grade)) 用于理解

 

       create table tb_student(

           sid number primary key,

           sname varchar2(20) not null,

           cid number [constraint FOR_STUDENT_CID] references TB_GRADE (CID)         

        )

 

 

        --外键的引用 必须通过alter table的方式来添加

 

 

       alter table tb_student add constraint FOR_STUDENT_CID foreign key (CID)

                      references TB_GRADE (CID)         

 

        --删除外键必须通过名称  未定义名称时 需要通过      dba_constraints表去查询         

        alter table tb_student drop constraint SYS_C008553    

 

 

        --查询表所有的外检 类型  C表示check约束 P代表主键  R代表外检  

        select * from dba_constraints  where table_name='TB_STUDENT'                    

 

十二.序列 

 

 

        --创建序列  minvalue表示范围中最小的那个值 maxvalue表示范围中最大的那个值

 

 

       --start with 表示序列从1开始递增  increment by表示步值（每次累加值）

       create sequence TB_GRADE_SEC

        minvalue 1

        maxvalue 999999999999999999999999999

        start with 1

        increment by 1

        cache 20;

 

        --查询当前值得下一个值

       select TB_GRADE_SEC.NEXTVAL from dual;

 

        --查询当前值

       select TB_GRADE_SEC.Currval from dual;

        select * from tb_grade;

 

        --在insert语句中使用序列  一般使用 序列来控制 主键的值

       insert into tb_grade values(TB_GRADE_SEC.NEXTVAL,'test');

       insert into tb_student values((select max(stuid)+1 from tb_student),'张三',1)

        commit;

               

        --删除序列

       drop sequence tb_grade_sec               

 

 

十三.索引 

        作为一个普通的开发人员 唯一提高性能工具就是索引【重点】

 

        索引候选列需要存储很大范围（重复的范围 每一个值都不一样 就是范围大）的值——“B-树”索引

 

        索引候选列只包含很少范围（比如列上的值 都是某几个枚举的值  男,女）的值——“位图”索引

 

 

        --在列上使用 unique，primary key可以自动添加btree索引

       create table TB_STUDENT

        (

          SID   NUMBER  primary key not null,

          SNAME VARCHAR2(20) unique,

          sex number check(sex in(0,1)),

          CID   NUMBER

        )

 

        --唯一索引（BTree索引） map.put(1,'test') 创建btree的语句 唯一索引 可以直接定位行 效率最高  

        create UNIQUE index index_student_sid on TB_STUDENT(sid)

        --normal索引（BTREE索引） 表示列允许出现重复 但是不能出现大范围的重复 否则效率降低

       create index index_student_sid on TB_STUDENT(sid)  

 

        --创建位图索引 大范围的重复时 使用索引

       create bitmap index bitmap_student_sex on TB_STUDENT(sex)

 

        --创建基于函数的索引

       create index upper_sname on tb_student(upper(sname))

 

        --删除索引

        drop index upper_sname                   

 

 

十四.视图 

    CREATE [OR REPLACE] VIEW 视图名  

          AS 查询语句 [WITH CHECK OPTION] [WITH READ ONLY];

        选项：

       OR REPLACE：视图存在时就替换

       WITH CHECK OPTION：视图的创建条件不能更改

       WITH READ ONLY：视图中的内容不能更改

       --创建视图 用于重复利用相同的sql 可以用于权限控制  可以隐藏机密的数据

       create or replace view vi_student_grade as

       select s.sname,g.cname from tb_student s inner join tb_grade g on s.cid=g.cid where cname='1501' WITH CHECK OPTION

 

        --查询 

       select * from vi_student_grade where cname='1501'

        create or replace view vi_student as select * from tb_student;

 

        --直接通过表更新，删除 

       update tb_student set sname='test' where sid=10;

 

        --单表视图 可以用于间接更新，删除 

      update vi_student set sname='test' where sid=10;

 

        删除视图 

       DROP VIEW 视图名;

 

 

 十五.同义词 

        --创建同义词

 

        CREATE SYNONYM syn_scott_emp FOR scott.emp; 

 

         select * from syn_scott_emp;     

         drop SYNONYM  syn_scott_emp  

                

plsql结构定义

 

    PL/SQL(Procedural Language/SQL)：过程化编程语言

   Oracle对标准SQL语言的过程化扩充

   用来编写包含SQL语句的程序，加入业务逻辑处理功能。

   PL/SQL程序由块组成，每一个块都包含有PL/SQL和SQL语句。块的结构如下：

       [

           declare 变量名  类型;--声明变量 

              变量名 类型;

        ]

        begin

          [过程语句]

          [exception when 异常类型 then 过程语句]

        end;

 

    举例：     

    /** 等价

     {

         System.out.println('helloworld')

      }

    **/

    begin   --等价于{

       dbms_output.put_line('helloworld');

    end;    --等价于}

 

    --带变量的plsql定义

   declare userid number;

    begin

      --变量的赋值 可以通过 变量名:=值

     userid:=1;

      dbms_output.put_line(userid);

    end;

一.异常捕获

>1异常捕获

declare

myVar number;

begin

  begin

    select sal into myVar from emp where ename='SMITH1';

    exception

    when NO_DATA_FOUND then

      dbms_output.put_line('没有数据找到');

  end;

  select sal into myVar from emp where ename='SMITH';

  myVar:=5/0;

  exception

     when ZERO_DIVIDE then

       dbms_output.put_line('除数为0');

     when others then

       dbms_output.put_line('未知的异常');

end;

--自定义异常

declare

myexe exception;

sex varchar2(3):='&请输入性别';

begin

  if(sex not in ('男','女')) then

  raise myexe;

  end if;

  exception

    when myexe then

      dbms_output.put_line('未知性别');

      when others then

        dbms_output.put_line('未知的异常');

end;

--例子

   declare userid number;

    begin

      --变量的赋值 可以通过 变量名:=值

     userid:=1/1;

      dbms_output.put_line(userid);

      --异常的定义  when 异常类型  then 异常处理的代码块

     exception when others then

         dbms_output.put_line('出现异常');

    end;

二.复合类型

   --在过程中调用函数和存储过程

declare curdate date:=sysdate;  

curDateStr varchar2(10);

begin

   curDateStr:=to_char(curdate,'yyyy-MM-dd');

   dbms_output.put_line(curDateStr);

end;

 

--在过程中定义复合类型

   1>数组类型 

   declare  

     type ArrayList is table of number index by binary_integer;

     ua ArrayList;

    begin

      ua(0):=12;

      ua(-1):=20;

      dbms_output.put_line(ua(0));

    end;

 

    2>定义复合类型的对象类型  使用对象的成员可以使用对象.属性名称访问

   declare  

     type userinfo is record(

        userid number,

        userName varchar2(20),

        sex number

     );

     jyb userinfo;

     begin

        jyb.userid:=1;

        jyb.userName:='蒋永兵';

        jyb.sex:=0;

        dbms_output.put_line(jyb.userName||'的用户id是:'||jyb.userid);

     end;     

三. 列类型和行类型

   --定义列类型 通过获取表的列类型给当前变量 

   declare sex_tmp tb_student.sex%type;

    begin

      sex_tmp:='1';

      syso(sex_tmp);

end;

 

    --行类型 通过select into语句抓取一行

   declare student_row tb_student%rowtype;

    sname varchar2(20);

    begin

       --select into用于在过程语句中将表中的数据抓取到变量中    

       --这里是抓取行

      select * into student_row from tb_student where sid=1;

       --抓取行中的某一列到变量

      select sname into sname from tb_student where sid=1;

       syso(student_row.sname);

       syso(sname);

    end;

四. 定义过程的逻辑控制语句  

1>if逻辑控制语句 

   declare sex number:=3;

    begin

       if (sex=0) then

          syso('男');

       elsif(sex=1) then

          syso('女');

       else

          syso('不男不女');

       end if;

    end;

2>循环

--loop循环 相对于java的do while循环 

   declare num number:=1;

    begin

     loop  

         syso(num);

         num:=num+1;

         exit when num=11;

      end loop;

    end;

--while循环 相对于java的while循环 

   declare num number:=1;

    begin

       while (num<=10)loop  

         syso(num);

         num:=num+1;

      end loop;

    end;

 

--for循环 相对于java的for循环 reverse表示反转输出 

   declare num number:=1;

    begin

       for i in  1..10 loop  

         syso(i);

      end loop;

    end;

 

 

--隐式游标方式的循环 用于循环迭代表记录 

 

   begin

       for stu_tmp in (select * from tb_student) loop

          syso(stu_tmp.sname);

       end loop;

end;  

 

五.存储过程的定义 

1>过程（多次编译多次执行）： 

 

       --过程实现计算器

       declare p1 number:=1;

        p2 number:=2;

        sign varchar2(3):='-';

        begin

          if sign='+' then

             syso(p1+p2);

          elsif(sign='-' ) then

             syso(p1-p2);

          elsif(sign='*' ) then

             syso(p1*p2);

          elsif(sign='/' ) then

             syso(p1/p2);

          end if;

        end;

2>存储过程（一次编译 多次执行） 

       --存储过程的定义   

        --存储过程执行只是编译的过程  如果需要执行存储过程的代码 需要在过程中调用

       create or replace procedure pro_arthirm(p1 number,p2 number,sign varchar2)  

        as

        --参数的定义

       begin

       --过程体

         if sign='+' then

             syso(p1+p2);

  elsif(sign='-' ) then

             syso(p1-p2)

          elsif(sign='*' ) then

             syso(p1*p2);

          elsif(sign='/' ) then

             syso(p1/p2);

          end if;

       end;

        --在plsql中调用存储过程

       declare p1 number:=1;

        p2 number:=2;

        sign varchar2(3):='+';

        begin

             pro_arthirm(p1,p2,sign);

        end;

        --在command模式下  需要使用   

        call 过程名称(参数。。。)

        execute（exec） 过程名称(参数。。。)

        show errors 显示存储过程编译之后的错误

 

3>存储过程参数 

       /**    参数类型：

           IN 输入参数。只能获取它的值 不能修改他的值 调用设置的值 可以在存储过程中查看

           OUT 输出参数。只能在过程体中赋值 不能查看到传入的值

           IN OUT 输入输出参数。可以取它的值，也可以给它赋值

            public int arthirm(int p1,int p2,String sign){

               int returnNum=5;

               if(...){

                  returnNum=p1+p2

               }

               return returnNum;

            }

            **/

            create or replace procedure pro_arthirmByReturn(p1 in number,p2 in number,sign in varchar2,returnNum in out number)  

                as

                --参数的定义

               rtnNum number;

                begin

                syso(returnNum);

                --过程体

                 if sign='+' then

                    returnNum:=(p1+p2);

                  elsif(sign='-' ) then

                     returnNum:=(p1-p2);

                  elsif(sign='*' ) then

                     returnNum:=(p1*p2);

                  elsif(sign='/' ) then

                     returnNum:=(p1/p2);

                  end if;

                end;

              declare p1 number:=1;

           p2 number:=2;

                sign varchar2(3):='+';

                returnNumber number:=10;

                begin

                     pro_arthirmByReturn(p1,p2,sign,returnNumber);

                   syso(returnNumber);

                end;

 

4>查询数据库的对象的三中方式 

      select count(*) from user_procedures;--当前用户的存储过程

      select count(*) from all_procedures; --相同权限的用户所有的存储过程 权限下有多少存储过程就输出多少 不会有编译出错

       select count(*) from dba_procedures; --系统所有的存储 如果没有dba的权限会编译出错

 

5>删除存储过程 

       drop procedure 存储过程名称  

二：函数过程的定义   

      CREATE [OR REPLACE] FUNCTION 函数名

       [(参数名 [IN|OUT|IN OUT] 数据类型[, …])]

        RETURN 返回值类型

       {IS | AS}

        BEGIN

            函数的主体

       END [函数名];

          函数和存储过程的区别在于

 

         1  函数可以返回值  存储过程不行

         2  函数可以在sql中使用 存储过程不行

         3  函数是一种特殊的存储过程

        例子  

 

 

         create or replace function  fun_arthirmbyDeclare(p1 in number,p2 in number,sign in varchar2)

            return number

                    as

            resultDNum number;

                    begin

                      if sign='+' then

                         resultDNum:=(p1+p2);

                      elsif(sign='-' ) then

                         resultDNum:=(p1-p2);

                      elsif(sign='*' ) then

                         resultDNum:=(p1*p2);

                      elsif(sign='/' ) then

                         resultDNum:=(p1/p2);

                      end if;

              return resultDNum;

                    end;

 

            --调用函数    

           declare p1 number:=1;

                    p2 number:=2;

                    sign varchar2(3):='+';

                    returnNumber number;

                    begin

                         returnNumber:=fun_arthirmbyDeclare(p1,p2,sign);

                       syso(returnNumber);

                    end;

            --删除函数：

           DROP FUNCTION 函数名;

 

         区分存储过程和函数在user_procedures

       select object_name,object_type from user_objects where object_name in(select object_name from user_procedures)         

 

 

六.触发器的定义        

     CREATE [OR REPLACE] TRIGGER 触发器名

       [BEFORE | AFTER] 激活触发器的事件(insert,update,delete)

        ON 表名

       [FOR EACH ROW]  -- 指定为行级触发器

       [WHEN 触发条件]

        BEGIN

            主体;

        END [触发器名];

        /

       注意：

   多种激活触发器用or来连接：insert or update or delete

    在触发器主体语句中可以用“inserting”、“updating”、“deleting”判断激活事件。

   在行级触发器中，可以通过:old和:new别名访问列的原值和新值。

 

    举例：

     create or replace trigger trg_grade_delete before

        delete on tb_grade

        FOR EACH ROW

        begin

           /**

              在dml操作中 数据的修改是存在新和旧的问题

             insert语句  只有新的数据 

             delete语句  只有旧的数据 

             update语句  有新和旧的问题

             oracle通过var变量的方式存储新旧值 

             :new 

              :old 只能使用在行级触发器上 

          **/

           syso('我删除了一行记录 班级名称是：'||:old.cname );

        end;    

       delete from tb_grade where cid=3;  

     

 演示在触发器中自定义异常以及修改列的值

        create table orders(

           id number primary key,

           sname varchar2(20),

           price number,

 

 

           total number,

           totalPrice number

        )

         --在before触发器中可以修改:new的值 after不行

        create or replace trigger trg_orders before

                insert on orders

                FOR EACH ROW

            declare

            rollbackException exception;  --自定义异常

               begin

               --判断价格小于0  不满足 应该回滚

              if :new.price<0 then

                   raise rollbackException;

               end if;

               --总价=单价*数量

              :new.totalPrice:=:new.price*:new.total;

               /**

               exception when rollbackException then

                  syso('判断价格小于0');

               **/

            end;

 

七.游标定义

  5步骤 经过5个步骤的显示游标 没有5个步骤 隐示游标

>1 定义获取数据的变量

>2 声明游标,并指定查询

>3打开游标

>4抓取数据

>5关闭游标

 

begin

   for i in (select empno,ename from emp) loop--隐示游标

     dbms_output.put_line(i.ename);

     end loop;

end;

 

--1 定义获取数据的变量

declare rowobj emp%rowtype;

--2 声明游标,并指定查询

cursor mycursor is select * from emp;--普通游标

begin

  --3打开游标

  open mycursor;

  --4抓取数据

  loop

    fetch mycursor into rowobj;

    exit when mycursor%notfound;

    dbms_output.put_line(rowobj.ename||rowobj.sal);

  end loop;

  --5关闭游标

  close mycursor;

end;

 

 

--动态游标

enameVar = null

enameVar ='SMITH'

if(enameVar is null)

sql='select * from emp';

else

  sql='select * from emp where ename=enameVar';

  

  

  

--1定义获取数据的变量

declare rowobj emp%rowtype;

enameVar varchar2(20):='SMITH';

sqlVar varchar2(2000):='select * from emp ';

--2 声明动态游标,并指定查询

mycursor sys_refcursor;

begin

  if(enameVar is not null) then

  sqlVar:=sqlVar || 'where ename='||enameVar||'''';

  end if;

  dbms_output.put_line(sqlVar);

  if(not mycursor%isopen) then

  dbms_output.put_line('未打开游标');

  end if;

  --3 打开游标

  open mycursor for sqlVar;

  --4 抓取数据

  loop

    fetch mycursor into rowobj;

    exit when mycursor%notfound;--抓取之后,才能判断是否拥有数据

    dbms_output.put_line(rowobj.ename||rowobj.sal);

   end loop;

   --5关闭游标

   close mycursor;

end;

select * from empwhere ename='SMITH'

 

 

                       

 

Oracle笔记

一. ORACLE安装目录结构与卸载

1.开发工具集

10G 【sqlplus (

dos命令:

sqlplus /nolog,

conn /as sysdba

),

       isqlplus(

         http://localhost:5560/isqlplus

        )

        em (

            http://localhost:1158/em

        )

】

11G 【

0G除isqlplus，

sql developer(java编写)

】

 非官方：PLSQL Developer

2. 配置和管理工具

   DBCA 【用来配置和更新数据库】

           NETCA【用来配置网络监听和连接描述符】

          连接描述符位于：%ORALE_HOME%/NETWORL/ADMIN/tnsnames.ora

3. ORACLE文件目录结构 

   安装目录 :F:\oracle

          产品目录:%安装目录%/product/10.1.0

    ORACLE_HOME:%产品目录%/Db_索引编号

           数据文件目录:%产品目录%/oradata/SID名称/

                 1 DBF数据文件  

 

 

                 2 CTL控制文件 【引导文件】

                 3 LOG重做日志文件  【恢复文件】

          连接描述符：%ORALE_HOME%/NETWORL/ADMIN/tnsnames.ora

          监听配置文件【端口】：%ORALE_HOME%/NETWORL/ADMIN/listener.ora

          端口查询文件：%ORALE_HOME%/install/portlist.ini

4.数据管理方式的发展 

   1手工管理阶段

       数据不被保存，还没有文件的概念。一组数据与一个程序直接对应

   2文件管理阶段（ROM）

       数据以文件形式存放。一个应用对应一组数据，应用之间不能共享数据。

   3数据管理阶段（RAM）

       多用户、多应用要共享数据。需要专门的数据管理系统。

5. ORACLE的体系结构 

    ORACLE SERVER

                ---INSTANCE

                   ---内存结构

                         ---SGA（系统全局区，共享池（sql语句），java池（java程序），数据缓冲区（未保存的数据），日志缓冲区）

                        ---PGA（程序全局区 连接的用户占用的内存）

                    ---进程

                         ---后台进程（用于操作数据 PMON,SMON,DBWR,LGWR具体参考《ORACLE体系结构》

                        ---用户进程 （用户连接用户）

                 ---DATABASE

                    1 DBF数据文件  

                     2 CTL控制文件 【引导文件】

                     3 LOG重做日志文件  【恢复文件】

                     4  密码文件(口令文件)，初始化文件（参数文件），dump文件（DBA操作）

6. sql语言操作分类： 

   1 数据定义语言DDL  （定义，操作数据的结构） 【-->java的变量定义】

 

       CREATE : 在数据库中创建新的数据对象 

       ALTER : 修改数据库中对象的数据结构 

       DROP : 删除数据库中的对象 

       DISABLE/ENABLE TRIGGER : 修改触发器的状态 

       UPDATE STATISTIC : 更新表/视图统计信息

       TRUNCATE TABLE : 清空表中数据

       COMMENT : 给数据对象添加注释

       RENAME : 更改数据对象名称

     2数据操作语言DML

       DML(Data Manipulation Language)(CRUD)，用于添加/修改/查询数据库中数据。

       DML包含以下语句：

       INSERT ：将数据插入到表或视图

       DELETE ：从表或视图删除数据

       SELECT：从表或视图中获取数据

       UPDATE ：更新表或视图中的数据

       MERGE ： 对数据进行合并操作(插入/更新/删除)

 

    3数据控制语言DCL

        DCL(Data Control Language)用来向用户赋予/取消对数据对象的控制权限。

       DCL包含以下语句：

       GRANT : 赋予用户某种控制权限

       REVOKE ：取消用户某种控制权限

   4. 事务控制语言(TCL)

        TCL(Transaction Control Language)用来对事务进行管理。

       TCL包含以下语句：

       COMMIT : 保存已完成事务动作结果

       SAVEPOINT : 保存事务相关数据和状态用以可能的回滚操作

       ROLLBACK : 恢复事务相关数据至上一次COMMIT操作之后

       SET TRANSACTION : 设置事务选项

7. 卸载或者重装数据库 

  卸载：

     1停止所有以oracle开始的服务，找到 UI（Universal Installer） 打开卸载界面 点击卸载产品按钮  选择所有的组件 点击删除按钮

 

     2删除服务 使用命令  sc delete 服务名称 删除所有的服务

     3删除注册表 开始->运行->输入 regedit命令打开注册表 找到 HKLM/SofeWare下的oracle 全部删除

     4找到oracle的安装目录 删除 如果无法删除使用软件强制删除

 重装：

     1 如果服务能够启动（ORCL和Listener结尾） 首先sqlplus /nolog 和conn /as sysdba连接  出现字符 历程启动  需要使用命令 startup

     2 服务不能启动  通过杀毒软件 去恢复oracle被删除的部分

     3 如果是Listener结尾启动不了使用netca删掉前面创建的监听 去重新创建一个

     4 如果是ORCL结尾启动不了 使用dbca 删除掉前面创建的orcl的数据库  在重新创建 一个新的

     5 dbca和netca无法打开 点击oracle安装文件点击setup。exe重装数据库

     6 setup.exe无法启动或者多次安装都失败，重装系统  

二 ORACLE管理命令

1 启动sqlplus工具 （ORACLE dba管理数据库的工具） 

           sqlplus /nolog  (无授权信息登录)

              conn /as sysdba(使用本地系统登录 本地用于加入了ORA_DBA组)

           sqlplus 用户名/密码@连接描述符

           sqlplus 用户名/密码  （默认本地ORCL）

2 oracle退出命令 

           exit(quit)  直接退出到dos命令

           disconnect(disconn|disco) 退出连接 在sql命令中

           orapwd（orapwd file=<fname> password=<password>） 重置密码文件

           password(passw) 修改密码 （必须登录之后）

3 操作上一次执行的sql (sql缓冲区中 缓冲区中只能存储一条sql) 

           list(l) 显示上一次缓冲区中sql

           run(r|/)执行上一条缓冲区中的sql

           clear buffer(cl buff)清空缓冲区中的sql

           get sql文件的路径  将文件的内容读取到缓冲区 可以使用 list或者run去查看或者运行

          save(sav) sql文件的路径 将缓冲区的sql写入到文件中

           start(sta|@) sql文件的路径 将文件的内容读取到缓冲区后 并执行 (get sql路径;run)

           edit(ed) 使用ed 文件路径修改文件的内容 或者使用ed命令修改缓冲区中的内容

           clear scr（ dos清屏cls） 在sql命令下清除屏幕上的文字

           spool 文件路径   记录当前用户的所有操作以及输出   spool off（终止）

4 启动和关闭 

        启动数据库orcl服务：

        sqlplus /nolog,

        conn /as sysdba

        startup

         nomount  数据库的instance已经打开  数据库没有打开

    mount数据的控制文件已经打开 并且和instance连接 但是不能远程连接

       open 数据库已经打开并且可以远程连接

       关闭数据库

       shutdown 启动或者关闭监听服务

       dos命令下  lsnrctl start|stop 检查连接描述符是否能连接  tnsping 描述名称

5. 连接描述符(客户端文件）【重点重点重点】

    oracle如果需要连接数据库 必须要确定三个元素

      ip地址 确定到 机器

      端口  确定机器上的某个机器（oracle的进程）（oracle默认的端口 1521）

      sid   确定oracle进程中的某个数据

    如果需要连接到任意一台机器的数据库 必须要配置这个三个元素

    这三个元素统称为 连接描述符  它的文件位于

      %ORACLE_HOME%/network/admin/tnsnames.ora

     该文件 中 #开头表示注释

   #以下 表示一个完整的连接描述符配置  别名可以任意

   #以下 指定了 ip 端口  sid

   # 原理 就是 Socket socket=new Socket("192.168.11.44",1521)

  clkdb =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.44)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

   )

6. 服务器监听文件(服务器端)【重点重点重点】

      %ORACLE_HOME%/network/admin/listener.ora

 

  在这个文件中 SID_LIST_LISTENER 必须添加

  (SID_DESC =

      (SID_NAME = ORCL)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (GLBAL_DBNAME = ORCL)

    )

  完整的配置如下

   SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

    (SID_DESC =

      (SID_NAME = ORCL)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (GLBAL_DBNAME = ORCL)

    )

  )

 

  如果需要其他客户端连接 需要将localhost修改为本机ipd

  LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.10)(PORT = 1521))

    )

  )

  远程 连接的测试语句

  create table aa (id number);

  select * from  tab where tname='AA';

 

7. 窗口

   dos命令窗口  只能执行dos的命令

   sqlplus连接 sql命令窗口

      sqlplus /nolog  未登录 进入sql命令下

   在sql命令下

      host dos命令

      clear scr 清除屏幕（host cls）

      conn 用户名/密码@连接描述符  切换数据库登陆

      exit|quit 退出sql命令到dos命令下

      discon  断开连接 不退出到dos命令

      password 修改密码 必须要登陆

      show user 显示当前用户

      SET PAGESIZE 100  --表述输出多少行 后重新显示表头

      SET LINESIZE 120 --表示 每一行现实的字符数

       desc 对象名（一般对象就是表） 显示表的结构

 

 

8. oracle的启动和关闭

    OracleServiceORCL  【oracle的主服务 必须要 启动该服务器 数据库才能启动】

    OracleOraDb11g_home1TNSListener 【监听服务 用于等待其他的客户端连接】

    关闭主服务

    1>net stop OracleServiceORCL  

    2>   sqlplus / as sysdba

       shutdown

    开启主服务

    1>net start OracleServiceORCL  

    2> sqlplus / as sysdba

       startup

    关闭监听服务

    lsnrctl stop

    启动监听服务

    lsnrctl start

 

 

9. 缓冲区命令

   （缓冲区就是上一次执行的sql语句的缓存）

    list|l命令 显示上一次执行的sql语句

    run|r|/ 执行上一次缓冲区中的sql语句

    clear buffer 清空缓冲区

    save 文件名 将缓冲区的sql写入文件

    get 文件名 将文件中的内容写入缓冲区

start 文件名 将文件中的内容写入缓冲区 并执行（get  + run）   

 

三. ORACLE数据类型

1. 字符串类型：用单引号引起来的字符序列。 

 

           CHAR(length)：固定长度字符串，不足自动以空格补齐长度，最多2000个字节。

 

           如：CHAR(10)  使用length(列) 获取的是定义的长度（length）

 

           VARCHAR2(length)：可变长度字符串，最多4000个字节。 【常用的字符串类型】

           如：VARCHAR2(10)  使用length(列) 获取的是实际数据的长度

           举例  title char(10) length(title)永远都是10

                content varchar2(10) 插入数据 'test' length(content)是实际数据的长度 4

2.数值类型： 

           NUMBER[(precision, scale)]：数值型，可以存储整数、浮点数。最高精度38位。如果没有指定最大位数和精度，就存储38位精度的数字。

           NUMBER(24)  最多24位 整数类型；

           NUMBER(5,3)  总和最多5位，其中小数最大是3位 整数位最大 5-3

           NUMBER  最大默认38位  整数和小数位总和不能超过38位         

3.日期类型

           DATE：存储日期和时间，精确到秒。时间值 【重要的类型】

               默认存放格式：“DD-MON-YYYY”

               默认显示格式：“DD-MON-YY

            java常用日期格式 yyyy-MM-dd hh:mm:ss

            orace转换日期格式  yyyy-MM-dd hh:mi:ss

            select sysdate from dual --输出的是date类型

            select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from  dual  --转换成了char类型

            TIMESTAMP[(seconds_precision)]：存储日期、时间和时区信息，带小数位的秒。时间戳

               如：TIMESTAMP(3)   秒后面小数点为3位。（最多可9位）

           大对象(Large objects)类型：最大存储128TB

                CLOB：Character LOB，用于存储字符数据。

               BLOB：Binary LOB，用于存储二进制数据。如图形、视频剪辑和声音文件。

               BFILE：用于存储二进制文件指针。

4. sql语法注意点 

       关键字不区分大小写 但是【字符串区分大小写】。

       表名和列名不区分大小写。

       语句以分号;结束

       单行注释使用--  多行注释 /**/

 

 

5.解锁scott账号 

    conn /as sysdba --以dba的账号登录

    alter user scott account unlock --解锁scott账号 

    conn scott/tiger  --密码是tiger     

6.显示表结构 

    dos命令下 可以使用 以下命令查看表结构

    desc 表名称  可以列出表结构 

    select * from tab; 查询当前用户下的所有的表   ，视图，同义词

7.常用的sql语句 

   select用于crud【create read update delete】 查询 是用于查询和筛选数据 【重中之重】

   使用java去理解 sql语句

   /*

    List<Emp> list=new ArrayList<Emp>();

    //select * from emp;

    syso("EMPNO ENAME JOB MGR....");

    for(Emp emp:list){

          syso(emp.empno+" "+emp.ename+" "+emp.job......);  

    }

    select ename as 雇员名称,sal as 薪水 from emp;

    syso("雇员名称 薪水....");

     for(Emp emp:list){

          syso(emp.ename+" "+emp.job);  

    }

   */

 

   select ename as 雇员名称,sal as 薪水 from emp;

   select语法结构   SELECT 【列名, 列名2…】| * FROM 表名 where 条件 ;

   【在oracle中字符串 使用''而不是""】

   查看当前方案中的表和其它数据库对象： 

    SELECT * FROM tab;

   查询指定表的所有列数据：

   SELECT * FROM 表名;

   查询指定表的指定列数据：

   SELECT 列名, 列名2… FROM 表名;

   可以在SELECT语句中使用算术运算符：+、-、*、/

   为查询的列取别名：

   SELECT 列名 [AS] 别名, … FROM 表名;

    使用||做连接。 

    举例： select '钱：'||'$'||sal as sal from emp

   使用DISTINCT消除重复内容。 

    举例：select distinct sal as sal from emp

   条件过滤（where）语句： 

       SELECT [DISTINCT] * | [列名 [别名],…]

        FROM 表名

       WHERE 条件;

        WHERE子句中的条件表达式：

       可以包括运算符(算术、比较、逻辑)

        可以使用()。

       可以使用常量、列、函数。

8.oracle常用的运算符 

    算术运算符：+、-、*、/

    连接操作符：将多个字符串或数据值合并成一个字符串 ||

    比较运算符： =、!=（或<>）、<、>、<=、>=

    ANY(值1,值2,值3…)    与列表中的任意一个值进行比较

    ALL(值1,值2,值3…)    与列表中的所有值进行比较

    in(值1,值2,值3)  列的值包含在所给的值中

 

举例：

--any表示等于其中的任意一个值  一般常用是使用in

       select * from emp where job=any('SALESMAN','MANAGER')

       select * from emp where job in('SALESMAN','MANAGER')

   --all 表示和所有值进行 比较 一般用于等于

       select * from emp where job!=all('SALESMAN','MANAGER')

       select * from emp where job not in('SALESMAN','MANAGER')

 

 

       逻辑运算符： 

       AND、OR、NOT

         举例：

             --等价于java &&

select * from emp where sal>2000 and job='MANAGER'

     --等价于java ||

select * from emp where sal>2000 or job='MANAGER'

     --等价于java  !

select * from emp where not sal>2000

        SQL 运算符的优先级从高到低的顺序是：

       算术 、连接、比较、逻辑（NOT、AND、OR）  

      模糊匹配 like

 _代表一个字母  %代表0个或者多个字母

        select * from emp where job like '_A%';  

9.大对象类型(large obects) 最大存储128T

>1.CLOB: CHARACTER LOB 用于存储字符数据

>2.BLOB: BINARY LOB 用于存储二进制数据(图形,视频,声音文件)

>3.BFILE: 用于存储二进制文件指针

四.oracle表的查询 

 1.表的排序

      排序语句：

       SELECT [DISTINCT] * | [列名 [别名],…]

         FROM 表名

        WHERE 条件

        ORDER BY 排序的列 [ASC | DESC],…;

 

       select * from emp order by hiredate desc   ( desc表示降序 从大到小 asc 表示升序)

 

       select * from emp where where job='MANAGER' order by  hiredate desc; (按条件查询出结果后排序)

 

       select * from mep order by hiredate desc,sal desc (按条件一升序后 相同的条件一在进行条件二排序)

 

2. Oracle的伪列 

 

      别名

       在select语句中 每一个被查询的表都有一个别名 默认就是它的表名 可以给表添加一个别名 添加的别名覆盖默认的别名

       select emp.ename from emp t; --错误

       select t.ename from  emp t;

       --如果多表查询时  每一个表都有相同的列名 这时必须要使用 别名

       select t.deptno from emp t,dept d;

 

 

      ROWID 是表中每一条记录的唯一标识符，数据库内部使用它来存储行的物理地址。

 

       该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的某一行。

 

       无法使用update语句修改

 

     ROWNUM 是SQL查询返回的结果集中每一行的行号。 【重点】

       可以用它来限制查询返回的行数。

       ROWNUM是先查到结果集之后再加上去的一个列 。

 

 

         在oracle中使用rownum来进行分页

         select t.* ,rownum as rn from emp where rownum>=10 and rownum<=20这是错误的

 

         因为行的循环查找 从索引1 开始  第一条记录的索引为1  不满足 循环第二条时 记录索引从1 开始 。。。。所以的行的行号都是1 永远不满足

           select * from (select t.*,rownum as rn from emp t) where rn>=10 and rn<=20 这是正确的  （一般使用） 

 

 

                  分页另一种写法

           select * from (select t.*,rownum as rn from emp t where rownum<=20) where rn>=10 正确的 

         select * from emp where rownum<=10 这是正确

        记录：rownum大于1的记录永远不可能成立  rownum<等于任何值都成立

 

 

五.oracle的函数

 

 

1.字符串函数 

      lower(n) 将字符转换成小写         对应java String.toLowerCase

         select lower('AAAA') from dual;  --aaaa

         select lower(ename) from emp;    --列输出结果全部小写

 

      upper(n) 将字符串转换成大写       对应java String.toUpperCase

 

      replace（列名,被替换的字符串，替换的字符串）    对应java String.replace或者 replaceAll

          select ename,replace(job,'MANAGER','经理')  from emp; --job列中 所有的MANAGER都被替换成了经理

          select replace('AAAtestgggg','test','测试') from dual; --AAA测试gggg

 

      instr(列名,被搜索的字符串) 

          select instr('AAAtestgggg','test') from dual; --4 索引从1开始

 

      substr(列名,开始位置,总长度) 

          select instr('AAAtestgggg',4,3) from dual; -- java substring  根据 被截取的字符串.substring(开始位置，结束的位置【不包括结束的位置】)  

  javascript 被截取的字符串.substr（开始位置，长度） 被截取的字符串.substring

                                                        oracle substr

 

      concat(参数1，参数2) 

          select concat('a','b') from dual;  --- ab  等价于 'a'||'b'    java "aaa"+"bbb"  

 

      length(列名) 获取字符的长度      java  String.getLength();

      trim(列名) 去空格  

      ltrim 去左侧的空格

      rtrim 去右侧的空格 

 

         nvl(列名,值) 当列的值为空时 输入第二参数的值 如果不为空 输出当前列的值   【重要】

    select nvl('','bac') from dual -- '' is null 所以输出 bac

 

 nvl2(列名,值1,值2) 当列的值为空时 输出第三个参数的值 如果不为空输出第二个参数的值 【重要】 

 

 

         decode(列名,条件1，值1，条件2,值2......,条件n,值n，默认值) 当列的值等于条件n时输出值n 【重要】

 select decode('2','0','男','1','女','人妖') from dual

2.数字函数            

      mod(5,2) 取余数 等价于java 5%2 

      round(n) 对整数位进行四舍五入 满5进1 

      round(n,p) 对小数位进行四舍五入 比如 round(5.6767,2) 输出为5.68 

      trunc(n) 截断小数位 只保留整数位 

     trunc(n,p) 截断小数位意外的位数 比如 trunc(5.666,2) 输出为5.66 就是保留p位小数

 

 

3.日期函数            

      sysdate 获取系统的当前时间  【重点】

 

      add_months(日期,月数) 将日期加上月数 并返回  比如 (sysdate=2016-3-3)+5个月 =2016-8-3

 

      select sysdate+天数 from dual 将当前日期加上某个数字 表示+天数

 

      months_between(日期1，日期2)  比较日期1和日期2的相差的月份数  结果=日期1-日期2

 

      last_day(日期) 返回日期对应月份的最后一天

 

      round(日期,格式)

            YEAR 获取当年的第一天 

            MONTH 获取当月的第一天 

            DAY 获取 第一个周末的第一天 

 

 

      next_day(日期，第几天) 获取当前日期下一个星期的第几天  

 

    转换函数 【每一个都是重点】

 

      to_date(日期字符串,日期格式)  字符串的格式必须由第二个参数来判断 

 

日期格式 ：

 

年 yyyy，月mm,日dd,小时（hh12小时制，hh24 24小时制） 分钟 mi 秒 ss

举例 :select to_date('2015/03/09','yyyy/mm/dd') from dual to_char(数字) 将数字转换成char类型 

 

to_char(日期,日期格式)  将日期转换成char类型  日期格式 可以任意 输出的内容 根据日期的格式而定

举例   select to_char(sysdate,'hh24')  输出24小时制的当前时间的hour

 

to_number(字符串数字) 将字符串的数字转换成number类型 

举例    to_number('123')输出数字 123  

4.常用的聚合函数

   什么是聚合 ：将多行记录压缩成1行或者多行进行演示

     1 count(*)  count(1) count(列名) 统计数据行的 个数 【重点】

     速度比较（oracle9i之前的说法）

       select count(*) --是最慢的  统计表的行数 先去数据库中了解表的结构

       select count(1) --第二慢的  找到数据库中表的一列

       select count(emp.empno) from emp; --第三慢的 因为已经指定了列名 不需要查表结构

       select count(rowid); --rowid直接定位的物理地址

     2 sum(数字的列) 统计当前列的总和

     3 avg（数字类型的列） 同行当前指定列的平均数

         select sum(sal)/count(rowid) from emp;

         select avg(sal) from emp;

     4 max(任意 列) 统计当前列最大的那个值

     5 min(任意列) 统计当前列种最小的那个值

 

6 wm_concat 合并结果集函数 

       将部门下的所有员工信息,列表显示

列如:研发部 张三/李四/王五

select d.dname,wm_concat(e.ename) from emp e inner join dept d on e.deptno=d.deptno group by d.deptno,d.dname;

7.exists函数

select * from emp e where exists (

select deptno from emp group by deptno having min(sal)=e.sal

)

5.正则表达式函数

>1.REGEXP_SUBSTR

     REGEXP_SUBSTR函数使用正则表达式来指定返回串的起点和终点

select regexp_substr('MY INFO: Anxpp,23，and boy','[[:digit:]]',1,2) from users;

select regexp_substr('MY INFO: Anxpp,22，and boy','my',1,1,'i') from users;

 

>2. REGEXP_INSTR

REGEXP_INSTR函数使用正则表达式返回搜索模式的起点和终点（整数）。如果没有发现匹配的值，将返回0。

select regexp_instr('MY INFO: Anxpp,23，and boy','[[:digit:]]') from users;

>3. REGEXP_LIKE

     通常使用REGEXP_LIKE进行模糊匹配。

select name from users where regexp_like(phone,'666');

>4.REPLACE和REGEXP_REPLACE

     REPLACE函数用于替换串中的某个值。

select replace('MY INFO: Anxpp,23，and boy','an') from users;

 

下面演示使用该函数计算某字串在源串中出现的次数：

select (length('MY INFO: Anxpp,23，and boy')-length(replace('MY INFO: Anxpp,23，and boy','an')))/length('an') from users;

 

REGEXP_REPLACE是REPLACE的增强版，支持正则表达式，扩展了一些功能。

select regexp_replace('电话：023  5868-8888 邮箱：anxppp@163.com',    '.*([[:digit:]]{3})([^[:digit:]]{0,2})([[:digit:]]{4})([^[:digit:]]{0,2})([[:digit:]]{4}).*',

     '(\1)\3\5'

) phone from users;

>5. REGEXP_COUNT

REGEXP_COUNT函数返回在源串中出现的模式的次数，作为对REGEXP_INSTR函数的补充。

虽然COUNT是一个集合函数，操作的是行组，但是REGEXP_COUNT是单行函数，分别计算每一行。

select regexp_count('MY INFO: Anxpp,23，and boy','an') from users;

六.高级查询

1.子查询【重点】

select * from emp where sal>2000 and job='MANAGER'

根据子查询的位置 可以分为 表子查询 列的子查询 条件的子查询  根据数据返回的行和列 分为 单行单列 多行多列多行单列

-- 1表子查询（虚表 内存表）   比条件和连接 更容易理解（多行多列子查询）

select * from (select * from emp where sal>200) where job='MANAGER'

 

--2 列子查询 每个子查询只能返回一行记录（单行单列子查询）

--查询部门名称

select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

select ename,(select dname from dept where rownum=e.empno) as dname from  emp e;

 

--3 条件子查询

--查询所有雇员为 ACCOUNTING的雇员

select deptno from  dept where dname='ACCOUNTING'

select * from  emp where deptno=10

--=只能等于一行  单行单列子查询

select * from  emp where deptno=(select deptno from  dept where dname='ACCOUNTING');

 

--查询所有雇员为 ACCOUNTING RESEARCH的雇员 (多行单列子查询)

select * from  emp where deptno=any(select deptno from  dept where dname='ACCOUNTING' or  dname='RESEARCH');

select * from  emp where deptno in(select deptno from  dept where dname='ACCOUNTING' or  dname='RESEARCH');

 

 

2.集合

{1，2，4}^{2,3,4}={2,4}

{1，2，4}U{2,3,4}={1，2，3,4}

{1，2，3}-{2，3，4}={1}

集合的操作

 

集合操作符：合并多个查询结果

UNION ALL：将多个查询结果合并到一个结果中，有重复行【重点】

UNION：将多个查询结果合并到一个结果中，没有重复行（并集）【重点】

INTERSECT：返回两个查询结果中共有的行 (交集)

MINUS：返回从第一个查询结果中减去第二个查询结果中相同的行之后剩余的行(差集)

 

 

3.复制 表

 create table dept1 as select * from dept

select * from  dept;

select * from dept1;

--交集

select * from dept intersect select * from  dept1;

--并集 union 去掉重复行

select * from dept union select * from  dept1;

--并集 uinon all 不会去重复行

select * from dept union all select * from  dept1;

--差集

select * from dept1 minus select * from  dept;

 

4. 分组

     1.什么是分组

      分组指定的条件  【相同的数据数据被分为一组】最终查询的结果

        就是分组的组数

      表名 Test

       A列  B列

       1  a

       1  b

       2  c

       2  d

       select A from  Test group by A

       --因为根据A这一列 相同的值被分为一组 最终被分为两组 只有两条被显示

         1

         2

       select A,B from Test group by A  --这是错误的

       两组都被压缩成一行

       1 组 存在a和b两个值  不能被压缩成 一行 只能使用聚合函数压缩成

             一行才能显示

 

     --查询部门中薪水最高的薪水

      select * from emp order by deptno;

     2.排序 是针对分组产生 之后的结果在进行排序

      select deptno,max(sal) from emp group by deptno order by deptno

 

     3.having子句 是对分组之后的结果进行筛选

      --查出部门最高的薪水>3000  

       select deptno,max(sal) from emp group by deptno having max(sal)>3000

 

 

5. 表连接【重点】

     单表查询 -》多表连接查询 

   笛卡儿积【交叉连接】{1,2,3}交叉{4,5} 可以参考【笛卡儿积.png】

     1     

     2      4

     3      5

     最终产生的结果  14 15 24 25 34 35   交叉产生的记录数就是 两个集合的个数的乘积

  数据库表的笛卡儿积

    数据行的笛卡尔积

   select * from table1，table2

  举例说明 92语法的内连，左外连 ，右外连

     用户表（UserInfo）

     用户id 用户名

       1     test

       2     user

     文章表 (Article)

       文章id 文章内容 用户id

        1       java      1

        2       oracle    3

 

    1  test   1  java  1

    1  test   2   oracle 3

    2  user   1   java  1

    2  user   2  oracle  3

    //86语法

    select * from userinfo u,arcticle a where u.用户id=a.用户id

    1  test   1  java  1

 

    //92语法

     内联必须都满足条件才显示记录

        select * from userinfo  inner join arcticle a on u.用户id=a.用户id  --是86语法的改进 结果和86语法是相同

     外联

         --左外连 以左边的表为主表 左表如果存在某条记录和右中所有的记录都无法关联 就保留一条记录

        select * from userinfo  left join arcticle a on u.用户id=a.用户id  

         1  test   1  java  1

         2  user   null null null

 

   建议

     多表查询需要先找到两张表的关系

     如果需要做多表连接查询尽量使用 内联 找到两张表的关系  直接将条件 在on后  (如果有特殊情况 如果主表中的记录没有连接也要输出 只能使用左外连接)

 

 七.sql语言操作分类【需要背 面试需要】：

 

1 数据定义语言DDL （DATA DEFINE LANAGUAGE）  （定义，操作数据的结构） 【-->java的变量定义】 

       CREATE : 在数据库中创建新的数据对象 

       ALTER : 修改数据库中对象的数据结构 

       DROP : 删除数据库中的对象 

       DISABLE/ENABLE TRIGGER : 修改触发器的状态 

       UPDATE STATISTIC : 更新表/视图统计信息 

       TRUNCATE TABLE : 清空表中数据 

       COMMENT : 给数据对象添加注释 

       RENAME : 更改数据对象名称 

2数据操作语言DML 【重点关注 工作必用】

       DML(Data Manipulation Language)(CRUD)，用于添加/修改/查询数据库中数据。

       DML包含以下语句：

       INSERT ：将数据插入到表或视图 

       DELETE ：从表或视图删除数据 

       select ：从表或视图中获取数据 

       UPDATE ：更新表或视图中的数据 

       MERGE ： 对数据进行合并操作(插入/更新/删除) 

3.数据控制语言DCL 

       DCL(Data Control Language)用来向用户赋予/取消对数据对象的控制权限。

 

       DCL包含以下语句：

       GRANT : 赋予用户某种控制权限

       REVOKE ：取消用户某种控制权限

4. 事务控制语言(TCL) 【重点】

 

       TCL(Transaction Control Language)用来对事务进行管理。

 

       TCL包含以下语句：

       COMMIT : 保存已完成事务动作结果 

       SAVEPOINT : 保存事务相关数据和状态用以可能的回滚操作 

       ROLLBACK : 恢复事务相关数据至上一次COMMIT操作之后 

       SET TRANSACTION : 设置事务选项 

 

5.crud （create read update,delete）【重点】

     所有的数据库  都是使用这四个单词来描述增删改查 isud  (insert select update delete)

     1 insert语句

       语法：

          INSERT INTO 表名[(列名1,列名2,…)] VALUES(值1, 值2,…);   --列名的个数和值的个数 必须相同              【表列较多 建议使用】

          insert into 表名  values(值1，值2 .。。)   --如果没有指定列名  值得个数必须和表中所有的列的个数相同 列的类型必须匹配   【表列较少建议使用】

          INSERT INTO 表名[(列名2，列名1,…)] VALUES(值2，值1, …); 值得顺序和列的顺序保持一致

          insert into student select '11','test1',25,'女'  from dual   --根据常量来模拟

          insert into student1 select * from student      --可以根据 insert into select语句来进行复制表的数据 （备份表）    seelct查询的列数必须和insert插入的表列数一致 并且列类型匹配

 

日期类型的插入

              insert into student1 values(7,'a',20,'男',sysdate)

   insert into student1 values(7,'a',20,'男',to_date('1987-06-18','yyyy-MM-dd'))

  

2 update语句（注意在修改之前一定先将条件写好）

       语法：

           UPDATE 表名 SET 列名=值[,列名2=值2,…] [WHERE 修改条件];  --

3 delete语句（注意在修改之前一定先将条件写好）

            delete scott.emp where empno='8110'

八.事务【重点】

     事务表示 一段sql的执行 要么同时成功 要么同时失败

任意的dml语句【数据的修改】 必须要提交数据（因为用户的误操作 导致数据的损坏 有可能导致无法挽回的灾难  可以使用 事务的机制来解决这类问题）

 

     commit（提交） 一旦提交了事务 数据就被修改到物理的数据文件中  （ctrl+s） 保存到文件

 

rollback(回滚) 如果除了误操作 需要回滚当前事务 （记事本ctrl+z）(撤回)

事务的acid属性

 

原子性(Atomic)：

指整个数据库事务是不可分割的工作单元。原子性确保在事务中的所有操作要么都发生，要么都不发生。

举例：

--被一起的执行的dml语句位于同一事务当中

insert into scott.emp values(8111,'test','MANAGER',7934,sysdate,3000,100,10);

insert into scott.emp values(8112,'test','MANAGER',7934,sysdate,3000,100,10);

insert into scott.emp values(8113,'test','MANAGER',7934,sysdate,3000,100,10);

rollback;

 

一致性(Consistency)：

一旦一个事务结束了(不管成功与否)，系统所处的状态和它的业务规则是一致的。即数据应当不会被破坏。

举例：

一旦数据被约束成某种 类型或者某些值那么就必须对应满足这些类型或者某些值

Insert into scott.emp values(8113,'test','MANAGER',7934,'2012-12-15',3000,100,10);

 

隔离性(Isolation)：

指多个事务同时操作同一数据时，每个事务都有各自的完整数据空间。未提交的数据  在其他的客户端中是无法看到因为因为隔离性

 

持久性(Durability)：一旦事务完成，事务的结果应该持久化 存到物理磁盘中（就是断电了下次还能 看的到 ）

 

事务的提交方式【了解即可】

默认数据库使用 手动（非自动）提交方式需要操作者 使用commit关键字来提交

 SQL> show autocommit;

 utocommit OFF

设置自动提交

SQL> set autocommit on;

SQL> insert into scott.emp values(8113,'test','MANAGER',7934,sysdate,3000,100,10);

已创建 1 行。

提交完成。

 

隐式的事务提交（不需要使用commit和rollback也会自动提交）

1 自动隐式提交事务：执行一个DDL语句、执行一个DCL语句、从SQL*Plus正常退出(exit,quit)

2自动隐式回滚事务：

强行退出SQL*Plus、客户端到服务器的连接异常中断、系统崩溃  

回滚点

savepoint a;

insert into scott.emp values(8113,'test','MANAGER',7934,sysdate,3000,100,10);

savepoint b;

insert into scott.emp values(8114,'test','MANAGER',7934,sysdate,3000,100,10);

rollback to a; --没有记录被插入

rollback to b  -- 8113被插入 8114被回滚

commit;

事务引发的问题和隔离级别【重点】

事务 对同一行数据进行操作时  必须等先操作的客户端提交或者回滚事务后 另外一个客户端才能操作数据（锁）

事务操作同一行数据出现的问题 （并发的引发的问题）

 

脏读  【是一种错误】

读取到了用户未提交的数据  oracle数据库永远不会出现脏读

 

不可重复读【只是一种设计问题】

在读取的事务中 两次重复读取同一行数据发现数据 被更改了

 

幻读【只是一种设计问题】

在读取的事务中 两次读取某一个条件的多条数据时发现多出了几条数据

  

隔离级别（就是为了解决事务操作同一份数据导致的问题）  

EAD UNCOMMITTED： 读未提交数据。脏读、不可重复读、幻读都可能发生。它的事务隔离性最低。

READ COMMITTED：读已提交数据。解决了脏读。【数据库的默认的隔离级别】

REPEATABLE READ：可重复读。解决不可重复读，脏读。

SERIALIZABLE：串行化。解决任何并发事务问题。最严格的事务

Oracle只支持READ COMMITTED和SERIALIZABLE。

默认为READ COMMITTED

 

设置隔离级别

alter session set isolation_level=serializable;--如果需要演示 必须每个客户端都要执行

  

九.用户管理 

 1.用户操作

    创建用户 create user jyb identified by jyb 【密码区分大小写】

    修改用户 alter user jyb identified by test

    锁定用户  alter user jyb account lock;

    解锁用户 alter user jyb account unlock;

    查询所有的用户       select * from all_users;

    删除用户  drop user jyb 【cascade代表所有关联的对象都被删除】;

2.角色操作 

  创建角色  create role stu;

  删除角色  drop role stu; 

 给角色添加功能权限  grant create session to stu

 给用户添加角色            grant stu to jyb     

 通过dd表可以查询所有的系统表：select * from dictionary where table_name like '%ROLE%'  

 查询所有的角色 select * from dba_roles;

 

3.权限操作 

  系统权限   （针对某些对象进行修改（DDL）的权限）

  grant 系统权限 to 用户|角色

  所有的系统权在 dba_sys_privs表中可以查询

  grant create user to jyb --jyb就能通通过  create user 创建用户

  回收系统权限

  revoke 系统权限 from 用户|角色

 

  对象权限 （针对某个对象（表）进行数据操作（DML）（crud）的权限）

  grant 操作（CRUD|RW） on 对象|all to 用户|角色

  grant insert on jyb.course to jyb  --jyb用户就拥有了访问course表的权限

  回收对象权限 

  revoke  操作（CRUD|RW） on 对象|all from 用户|角色

  角色 （将对象权限或者系统权限赋予给角色就是角色权限）

  系统默认都预留了一些角色 这些角色都拥有系统较高的权限 比如 DBA

4.备份和恢复 （dos备份）

    热备份

       (必须要先连接数据库后  备份数据后导出文件)

 

    冷备份（DBA）

      （不需要连接oracle数据库 直接备份文件）

 

    热备份举例：

    备份（DOS）：

       1》客户端备份 【文件被保存在当前的客户端】【一般使用客户端备份】

       exp 用户名/密码@连接描述符  file=保存的文件.dmp   【tables=(表1，表2....)】  owner=方案名

  

    2》服务器备份 【文件被保存在服务器上】

    被导出的用户必须要添加 grant create any directory to 用户名

    create directory 目录名称 as 'c:/test'

     expdp 用户名/密码@连接描述符 directory=目录名称 dumpfile=jybdp1.dmp  tables=(表1，表2....)

 

还原:

    客户端 还原

    imp 用户名/密码@连接描述符  file=保存的文件.dmp full=y ignore=y

 

    服务器 还原

impdp jyb/jyb@orcl directory=jybdir dumpfile=jybdp1.dmp  tables=(表1，表2....)  

 

十.表操作 

1.crud表的操作

  create user learn_object identified by test;

 

  --给予权限 

   grant dba to learn_object;

 

   -第一种 创建表 

    create table tb_userinfo(

    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

    username varchar2(20) not null, --not null 表示当前的列 不允许插入 null 和 ''

     sex number default 0   --默认值 default 值 当插入数据为空时 自动填上默认值 插入不为空的值 插入当前值

     )

 

     第二种复制表

     create table 表名称 as select * from 表 [where 1!=1]

 

     第三种类型赋值法

     create type  userobj as object (

      d number,

        uname varchar2(20),

        createDate date

     )

  

      create table userinfo_cpy_type of userobj

 

      --修改表名称 

      rename 旧表名to 新表名

 

       --修改列名 

      alter table userinfo rename column 旧列名 to 新列名

 

        --修改列类型 

       alter table userinfo modify username char(20)  

 

        --添加列 

       alter table userinfo add age number default 18

 

        --删除列

       alter table userinfo drop column age 

 

       --查询表的结构

       desc 表名  --命令下

       select * from all_tab_columns where table_name='USERINFO1'  --数组字典表（DD） 在sql语句中

 

 

 

2. 删除操作 

 

 

       删除表：把表中所有的行和表结构都删除。

   DROP TABLE 表名;--Oracle中删除表时并没有直接删除，只是放置到“回收站”

 

   显示回收站中的对象：SHOW RECYCLEBIN;

    恢复回收站中的表：FLASHBACK TABLE 表名 TO BEFORE DROP;

    删除回收站中的表：PURGE TABLE 表名;

    彻底删除 不进回收站：DROP TABLE 表名 PURGE;

 

    截断表：删除表中所有的数据行，重置表的存储空间。

       TRUNCATE TABLE 表名;

        delete from 表名

 

       区别 ：

          1  truncate是ddl语句  delete 是dml语句 都可以删除数据行

          2  truncate不需要提交事物 delete需要提交事物

          3  truncate删除表数据及表的存储 无法恢复 delete删除 可以使用归档日志恢复   

          4  delete可以删除指定条件的记录

          5  如果表结构损坏了 导致数据无法访问 必须重置表 使用truncate 因为delete 不会删除数据 只是隐藏数据 （查询的效率依然地下）

             如果需要根据条件删除 并且希望可以恢复 一般使用delete

 

3. 表分区 （了解）

           意义 ：可以容灾（出现事故后 还有一部分数据保留）

 

    范围(range(字段名))

 

            范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期

 

       格式:

 

            create table XXX() partition by range(表中数值类型的字段名)(partition 分区名 values less than 具体值  [tablespace 表空间名] ,…)

 

    列表(list(字段名))

            该分区的特点是某列的值只有几个

 

           …partition by list(字段名) (partition 分区名 values (值列表)值  [tablespace 表空间名] ,…))

 

    哈希(hash)

 

 

            分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。

 

 

           …partition by hash(表中数值类型的字段名)(partition 分区 [tablespace 表空间名] ,…)

 

 

            … partition by hash(表中数值类型的字段名)PARTITIONS n
STORE IN (s1,….sn)

 

 

    列表(list(字段名))

 

    复合分区(分区组合)  

 

 

 

十一. 约束 

 

      1》约束的查询方式

       --可以通过dd表来查询约束

       select * from all_constraints where table_name='USERINFO'

        约束的类型

         C (Check约束)

         P (主键约束)

         U （唯一约束）

         R (外键约束)

 

        --直接添加不为空的约束

 

       2》not null 约束

       create table tb_userinfo(

 

 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

 

 

                   username varchar2(20) not null, --not null 表示当前的列 不允许插入 null 和 ''

 

 

                )

        --constraints 约束名 可以指定约束名称 如果不指定约束名称 系统会自动分配一个名称  列存在了 添加约束 需要考虑 真实的数据行是否和约束冲突 因为要满足一致性

        alter table userinfo drop constraints nu_age

        alter table userinfo modify age number [constraints nu_age]    not null

 

        --直接添加不为空的约束 给约束添加别名

 

        3》主键约束

       create table tb_userinfo(

 

 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

 

 

                   username varchar2(20) [constraint nullable_username] not null, --添加别名后 会添加一个别名对应的约束

 

 

               )

        

        --主键 它的意义在于 添加一列 用于唯一标识当前行  uniqie约束 知识表示 当前的列不允许出现重复的的值

        --在技术上  主键唯一 不能为空  uniqie 唯一能为空

       alter table userinfo drop constraints SYS_C0011165

       alter table userinfo modify userid constraints pri_userid primary key

 

 

 

       4》check约束

 

        --通过check的方式 添加不为空的约束  check的语法要和where条件一致         

 

     

        create table tb_userinfo(

 

 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

                   username varchar2(20) [constraint check_username]  check(username is not null) --通过添加约束的方式 添加not null

        )     

 

        alter table userinfo modify sex VARCHAR2(20) constraints chk_sex check(sex in('女','男'))

        alter table userinfo modify age number constraints chk_age check(age between 1 and 100)

         

 

        --check约束必须满足where条件  不能使用子查询

       create table tb_userinfo(

               userid number primary key,

               username varchar2(20) not null,

               sex number constraint check_sex check(sex in(0,1))

        )    

 

        5》外键约束

 

       --定义外键  外键的定义 必须能唯一定位到外键对应的记录 比如 知道学生信息后 就唯一确定了他所在的班级

           --外键 如果某个表中存在一列 关联到另外一张的主键 这一列叫做外键列

           --如果某个列被添加了外键 外键列的值 必须是 引用表中主键的值 或者是null

 

       --班级表

 

 

       create table tb_grade(

           cid  number primary key,

           cname varchar2(20) not null

        )

        --cid number check(cid in(select cid from tb_grade)) 用于理解

 

       create table tb_student(

           sid number primary key,

           sname varchar2(20) not null,

           cid number [constraint FOR_STUDENT_CID] references TB_GRADE (CID)         

        )

 

 

        --外键的引用 必须通过alter table的方式来添加

 

 

       alter table tb_student add constraint FOR_STUDENT_CID foreign key (CID)

                      references TB_GRADE (CID)         

 

        --删除外键必须通过名称  未定义名称时 需要通过      dba_constraints表去查询         

        alter table tb_student drop constraint SYS_C008553    

 

 

        --查询表所有的外检 类型  C表示check约束 P代表主键  R代表外检  

        select * from dba_constraints  where table_name='TB_STUDENT'                    

 

十二.序列 

 

 

        --创建序列  minvalue表示范围中最小的那个值 maxvalue表示范围中最大的那个值

 

 

       --start with 表示序列从1开始递增  increment by表示步值（每次累加值）

       create sequence TB_GRADE_SEC

        minvalue 1

        maxvalue 999999999999999999999999999

        start with 1

        increment by 1

        cache 20;

 

        --查询当前值得下一个值

       select TB_GRADE_SEC.NEXTVAL from dual;

 

        --查询当前值

       select TB_GRADE_SEC.Currval from dual;

        select * from tb_grade;

 

        --在insert语句中使用序列  一般使用 序列来控制 主键的值

       insert into tb_grade values(TB_GRADE_SEC.NEXTVAL,'test');

       insert into tb_student values((select max(stuid)+1 from tb_student),'张三',1)

        commit;

               

        --删除序列

       drop sequence tb_grade_sec               

 

 

十三.索引 

        作为一个普通的开发人员 唯一提高性能工具就是索引【重点】

 

        索引候选列需要存储很大范围（重复的范围 每一个值都不一样 就是范围大）的值——“B-树”索引

 

        索引候选列只包含很少范围（比如列上的值 都是某几个枚举的值  男,女）的值——“位图”索引

 

 

        --在列上使用 unique，primary key可以自动添加btree索引

       create table TB_STUDENT

        (

          SID   NUMBER  primary key not null,

          SNAME VARCHAR2(20) unique,

          sex number check(sex in(0,1)),

          CID   NUMBER

        )

 

        --唯一索引（BTree索引） map.put(1,'test') 创建btree的语句 唯一索引 可以直接定位行 效率最高  

        create UNIQUE index index_student_sid on TB_STUDENT(sid)

        --normal索引（BTREE索引） 表示列允许出现重复 但是不能出现大范围的重复 否则效率降低

       create index index_student_sid on TB_STUDENT(sid)  

 

        --创建位图索引 大范围的重复时 使用索引

       create bitmap index bitmap_student_sex on TB_STUDENT(sex)

 

        --创建基于函数的索引

       create index upper_sname on tb_student(upper(sname))

 

        --删除索引

        drop index upper_sname                   

 

 

十四.视图 

    CREATE [OR REPLACE] VIEW 视图名  

          AS 查询语句 [WITH CHECK OPTION] [WITH READ ONLY];

        选项：

       OR REPLACE：视图存在时就替换

       WITH CHECK OPTION：视图的创建条件不能更改

       WITH READ ONLY：视图中的内容不能更改

       --创建视图 用于重复利用相同的sql 可以用于权限控制  可以隐藏机密的数据

       create or replace view vi_student_grade as

       select s.sname,g.cname from tb_student s inner join tb_grade g on s.cid=g.cid where cname='1501' WITH CHECK OPTION

 

        --查询 

       select * from vi_student_grade where cname='1501'

        create or replace view vi_student as select * from tb_student;

 

        --直接通过表更新，删除 

       update tb_student set sname='test' where sid=10;

 

        --单表视图 可以用于间接更新，删除 

      update vi_student set sname='test' where sid=10;

 

        删除视图 

       DROP VIEW 视图名;

 

 

 十五.同义词 

        --创建同义词

 

        CREATE SYNONYM syn_scott_emp FOR scott.emp; 

 

         select * from syn_scott_emp;     

         drop SYNONYM  syn_scott_emp  

                

plsql结构定义

 

    PL/SQL(Procedural Language/SQL)：过程化编程语言

   Oracle对标准SQL语言的过程化扩充

   用来编写包含SQL语句的程序，加入业务逻辑处理功能。

   PL/SQL程序由块组成，每一个块都包含有PL/SQL和SQL语句。块的结构如下：

       [

           declare 变量名  类型;--声明变量 

              变量名 类型;

        ]

        begin

          [过程语句]

          [exception when 异常类型 then 过程语句]

        end;

 

    举例：     

    /** 等价

     {

         System.out.println('helloworld')

      }

    **/

    begin   --等价于{

       dbms_output.put_line('helloworld');

    end;    --等价于}

 

    --带变量的plsql定义

   declare userid number;

    begin

      --变量的赋值 可以通过 变量名:=值

     userid:=1;

      dbms_output.put_line(userid);

    end;

一.异常捕获

>1异常捕获

declare

myVar number;

begin

  begin

    select sal into myVar from emp where ename='SMITH1';

    exception

    when NO_DATA_FOUND then

      dbms_output.put_line('没有数据找到');

  end;

  select sal into myVar from emp where ename='SMITH';

  myVar:=5/0;

  exception

     when ZERO_DIVIDE then

       dbms_output.put_line('除数为0');

     when others then

       dbms_output.put_line('未知的异常');

end;

--自定义异常

declare

myexe exception;

sex varchar2(3):='&请输入性别';

begin

  if(sex not in ('男','女')) then

  raise myexe;

  end if;

  exception

    when myexe then

      dbms_output.put_line('未知性别');

      when others then

        dbms_output.put_line('未知的异常');

end;

--例子

   declare userid number;

    begin

      --变量的赋值 可以通过 变量名:=值

     userid:=1/1;

      dbms_output.put_line(userid);

      --异常的定义  when 异常类型  then 异常处理的代码块

     exception when others then

         dbms_output.put_line('出现异常');

    end;

二.复合类型

   --在过程中调用函数和存储过程

declare curdate date:=sysdate;  

curDateStr varchar2(10);

begin

   curDateStr:=to_char(curdate,'yyyy-MM-dd');

   dbms_output.put_line(curDateStr);

end;

 

--在过程中定义复合类型

   1>数组类型 

   declare  

     type ArrayList is table of number index by binary_integer;

     ua ArrayList;

    begin

      ua(0):=12;

      ua(-1):=20;

      dbms_output.put_line(ua(0));

    end;

 

    2>定义复合类型的对象类型  使用对象的成员可以使用对象.属性名称访问

   declare  

     type userinfo is record(

        userid number,

        userName varchar2(20),

        sex number

     );

     jyb userinfo;

     begin

        jyb.userid:=1;

        jyb.userName:='蒋永兵';

        jyb.sex:=0;

        dbms_output.put_line(jyb.userName||'的用户id是:'||jyb.userid);

     end;     

三. 列类型和行类型

   --定义列类型 通过获取表的列类型给当前变量 

   declare sex_tmp tb_student.sex%type;

    begin

      sex_tmp:='1';

      syso(sex_tmp);

end;

 

    --行类型 通过select into语句抓取一行

   declare student_row tb_student%rowtype;

    sname varchar2(20);

    begin

       --select into用于在过程语句中将表中的数据抓取到变量中    

       --这里是抓取行

      select * into student_row from tb_student where sid=1;

       --抓取行中的某一列到变量

      select sname into sname from tb_student where sid=1;

       syso(student_row.sname);

       syso(sname);

    end;

四. 定义过程的逻辑控制语句  

1>if逻辑控制语句 

   declare sex number:=3;

    begin

       if (sex=0) then

          syso('男');

       elsif(sex=1) then

          syso('女');

       else

          syso('不男不女');

       end if;

    end;

2>循环

--loop循环 相对于java的do while循环 

   declare num number:=1;

    begin

     loop  

         syso(num);

         num:=num+1;

         exit when num=11;

      end loop;

    end;

--while循环 相对于java的while循环 

   declare num number:=1;

    begin

       while (num<=10)loop  

         syso(num);

         num:=num+1;

      end loop;

    end;

 

--for循环 相对于java的for循环 reverse表示反转输出 

   declare num number:=1;

    begin

       for i in  1..10 loop  

         syso(i);

      end loop;

    end;

 

 

--隐式游标方式的循环 用于循环迭代表记录 

 

   begin

       for stu_tmp in (select * from tb_student) loop

          syso(stu_tmp.sname);

       end loop;

end;  

 

五.存储过程的定义 

1>过程（多次编译多次执行）： 

 

       --过程实现计算器

       declare p1 number:=1;

        p2 number:=2;

        sign varchar2(3):='-';

        begin

          if sign='+' then

             syso(p1+p2);

          elsif(sign='-' ) then

             syso(p1-p2);

          elsif(sign='*' ) then

             syso(p1*p2);

          elsif(sign='/' ) then

             syso(p1/p2);

          end if;

        end;

2>存储过程（一次编译 多次执行） 

       --存储过程的定义   

        --存储过程执行只是编译的过程  如果需要执行存储过程的代码 需要在过程中调用

       create or replace procedure pro_arthirm(p1 number,p2 number,sign varchar2)  

        as

        --参数的定义

       begin

       --过程体

         if sign='+' then

             syso(p1+p2);

  elsif(sign='-' ) then

             syso(p1-p2)

          elsif(sign='*' ) then

             syso(p1*p2);

          elsif(sign='/' ) then

             syso(p1/p2);

          end if;

       end;

        --在plsql中调用存储过程

       declare p1 number:=1;

        p2 number:=2;

        sign varchar2(3):='+';

        begin

             pro_arthirm(p1,p2,sign);

        end;

        --在command模式下  需要使用   

        call 过程名称(参数。。。)

        execute（exec） 过程名称(参数。。。)

        show errors 显示存储过程编译之后的错误

 

3>存储过程参数 

       /**    参数类型：

           IN 输入参数。只能获取它的值 不能修改他的值 调用设置的值 可以在存储过程中查看

           OUT 输出参数。只能在过程体中赋值 不能查看到传入的值

           IN OUT 输入输出参数。可以取它的值，也可以给它赋值

            public int arthirm(int p1,int p2,String sign){

               int returnNum=5;

               if(...){

                  returnNum=p1+p2

               }

               return returnNum;

            }

            **/

            create or replace procedure pro_arthirmByReturn(p1 in number,p2 in number,sign in varchar2,returnNum in out number)  

                as

                --参数的定义

               rtnNum number;

                begin

                syso(returnNum);

                --过程体

                 if sign='+' then

                    returnNum:=(p1+p2);

                  elsif(sign='-' ) then

                     returnNum:=(p1-p2);

                  elsif(sign='*' ) then

                     returnNum:=(p1*p2);

                  elsif(sign='/' ) then

                     returnNum:=(p1/p2);

                  end if;

                end;

              declare p1 number:=1;

           p2 number:=2;

                sign varchar2(3):='+';

                returnNumber number:=10;

                begin

                     pro_arthirmByReturn(p1,p2,sign,returnNumber);

                   syso(returnNumber);

                end;

 

4>查询数据库的对象的三中方式 

      select count(*) from user_procedures;--当前用户的存储过程

      select count(*) from all_procedures; --相同权限的用户所有的存储过程 权限下有多少存储过程就输出多少 不会有编译出错

       select count(*) from dba_procedures; --系统所有的存储 如果没有dba的权限会编译出错

 

5>删除存储过程 

       drop procedure 存储过程名称  

二：函数过程的定义   

      CREATE [OR REPLACE] FUNCTION 函数名

       [(参数名 [IN|OUT|IN OUT] 数据类型[, …])]

        RETURN 返回值类型

       {IS | AS}

        BEGIN

            函数的主体

       END [函数名];

          函数和存储过程的区别在于

 

         1  函数可以返回值  存储过程不行

         2  函数可以在sql中使用 存储过程不行

         3  函数是一种特殊的存储过程

        例子  

 

 

         create or replace function  fun_arthirmbyDeclare(p1 in number,p2 in number,sign in varchar2)

            return number

                    as

            resultDNum number;

                    begin

                      if sign='+' then

                         resultDNum:=(p1+p2);

                      elsif(sign='-' ) then

                         resultDNum:=(p1-p2);

                      elsif(sign='*' ) then

                         resultDNum:=(p1*p2);

                      elsif(sign='/' ) then

                         resultDNum:=(p1/p2);

                      end if;

              return resultDNum;

                    end;

 

            --调用函数    

           declare p1 number:=1;

                    p2 number:=2;

                    sign varchar2(3):='+';

                    returnNumber number;

                    begin

                         returnNumber:=fun_arthirmbyDeclare(p1,p2,sign);

                       syso(returnNumber);

                    end;

            --删除函数：

           DROP FUNCTION 函数名;

 

         区分存储过程和函数在user_procedures

       select object_name,object_type from user_objects where object_name in(select object_name from user_procedures)         

 

 

六.触发器的定义        

     CREATE [OR REPLACE] TRIGGER 触发器名

       [BEFORE | AFTER] 激活触发器的事件(insert,update,delete)

        ON 表名

       [FOR EACH ROW]  -- 指定为行级触发器

       [WHEN 触发条件]

        BEGIN

            主体;

        END [触发器名];

        /

       注意：

   多种激活触发器用or来连接：insert or update or delete

    在触发器主体语句中可以用“inserting”、“updating”、“deleting”判断激活事件。

   在行级触发器中，可以通过:old和:new别名访问列的原值和新值。

 

    举例：

     create or replace trigger trg_grade_delete before

        delete on tb_grade

        FOR EACH ROW

        begin

           /**

              在dml操作中 数据的修改是存在新和旧的问题

             insert语句  只有新的数据 

             delete语句  只有旧的数据 

             update语句  有新和旧的问题

             oracle通过var变量的方式存储新旧值 

             :new 

              :old 只能使用在行级触发器上 

          **/

           syso('我删除了一行记录 班级名称是：'||:old.cname );

        end;    

       delete from tb_grade where cid=3;  

     

 演示在触发器中自定义异常以及修改列的值

        create table orders(

           id number primary key,

           sname varchar2(20),

           price number,

 

 

           total number,

           totalPrice number

        )

         --在before触发器中可以修改:new的值 after不行

        create or replace trigger trg_orders before

                insert on orders

                FOR EACH ROW

            declare

            rollbackException exception;  --自定义异常

               begin

               --判断价格小于0  不满足 应该回滚

              if :new.price<0 then

                   raise rollbackException;

               end if;

               --总价=单价*数量

              :new.totalPrice:=:new.price*:new.total;

               /**

               exception when rollbackException then

                  syso('判断价格小于0');

               **/

            end;

 

七.游标定义

  5步骤 经过5个步骤的显示游标 没有5个步骤 隐示游标

>1 定义获取数据的变量

>2 声明游标,并指定查询

>3打开游标

>4抓取数据

>5关闭游标

 

begin

   for i in (select empno,ename from emp) loop--隐示游标

     dbms_output.put_line(i.ename);

     end loop;

end;

 

--1 定义获取数据的变量

declare rowobj emp%rowtype;

--2 声明游标,并指定查询

cursor mycursor is select * from emp;--普通游标

begin

  --3打开游标

  open mycursor;

  --4抓取数据

  loop

    fetch mycursor into rowobj;

    exit when mycursor%notfound;

    dbms_output.put_line(rowobj.ename||rowobj.sal);

  end loop;

  --5关闭游标

  close mycursor;

end;

 

 

--动态游标

enameVar = null

enameVar ='SMITH'

if(enameVar is null)

sql='select * from emp';

else

  sql='select * from emp where ename=enameVar';

  

  

  

--1定义获取数据的变量

declare rowobj emp%rowtype;

enameVar varchar2(20):='SMITH';

sqlVar varchar2(2000):='select * from emp ';

--2 声明动态游标,并指定查询

mycursor sys_refcursor;

begin

  if(enameVar is not null) then

  sqlVar:=sqlVar || 'where ename='||enameVar||'''';

  end if;

  dbms_output.put_line(sqlVar);

  if(not mycursor%isopen) then

  dbms_output.put_line('未打开游标');

  end if;

  --3 打开游标

  open mycursor for sqlVar;

  --4 抓取数据

  loop

    fetch mycursor into rowobj;

    exit when mycursor%notfound;--抓取之后,才能判断是否拥有数据

    dbms_output.put_line(rowobj.ename||rowobj.sal);

   end loop;

   --5关闭游标

   close mycursor;

end;

select * from empwhere ename='SMITH'

 

 

                       

 

与卸载

1.开发工具集

10G 【sqlplus (

dos命令:

sqlplus /nolog,

conn /as sysdba

),

       isqlplus(

         http://localhost:5560/isqlplus

        )

        em (

            http://localhost:1158/em

        )

】

11G 【

0G除isqlplus，

sql developer(java编写)

】

 非官方：PLSQL Developer

2. 配置和管理工具

   DBCA 【用来配置和更新数据库】

           NETCA【用来配置网络监听和连接描述符】

          连接描述符位于：%ORALE_HOME%/NETWORL/ADMIN/tnsnames.ora

3. ORACLE文件目录结构 

   安装目录 :F:\oracle

          产品目录:%安装目录%/product/10.1.0

    ORACLE_HOME:%产品目录%/Db_索引编号

           数据文件目录:%产品目录%/oradata/SID名称/

                 1 DBF数据文件  

 

 

                 2 CTL控制文件 【引导文件】

                 3 LOG重做日志文件  【恢复文件】

          连接描述符：%ORALE_HOME%/NETWORL/ADMIN/tnsnames.ora

          监听配置文件【端口】：%ORALE_HOME%/NETWORL/ADMIN/listener.ora

          端口查询文件：%ORALE_HOME%/install/portlist.ini

4.数据管理方式的发展 

   1手工管理阶段

       数据不被保存，还没有文件的概念。一组数据与一个程序直接对应

   2文件管理阶段（ROM）

       数据以文件形式存放。一个应用对应一组数据，应用之间不能共享数据。

   3数据管理阶段（RAM）

       多用户、多应用要共享数据。需要专门的数据管理系统。

5. ORACLE的体系结构 

    ORACLE SERVER

                ---INSTANCE

                   ---内存结构

                         ---SGA（系统全局区，共享池（sql语句），java池（java程序），数据缓冲区（未保存的数据），日志缓冲区）

                        ---PGA（程序全局区 连接的用户占用的内存）

                    ---进程

                         ---后台进程（用于操作数据 PMON,SMON,DBWR,LGWR具体参考《ORACLE体系结构》

                        ---用户进程 （用户连接用户）

                 ---DATABASE

                    1 DBF数据文件  

                     2 CTL控制文件 【引导文件】

                     3 LOG重做日志文件  【恢复文件】

                     4  密码文件(口令文件)，初始化文件（参数文件），dump文件（DBA操作）

6. sql语言操作分类： 

   1 数据定义语言DDL  （定义，操作数据的结构） 【-->java的变量定义】

 

       CREATE : 在数据库中创建新的数据对象 

       ALTER : 修改数据库中对象的数据结构 

       DROP : 删除数据库中的对象 

       DISABLE/ENABLE TRIGGER : 修改触发器的状态 

       UPDATE STATISTIC : 更新表/视图统计信息

       TRUNCATE TABLE : 清空表中数据

       COMMENT : 给数据对象添加注释

       RENAME : 更改数据对象名称

     2数据操作语言DML

       DML(Data Manipulation Language)(CRUD)，用于添加/修改/查询数据库中数据。

       DML包含以下语句：

       INSERT ：将数据插入到表或视图

       DELETE ：从表或视图删除数据

       SELECT：从表或视图中获取数据

       UPDATE ：更新表或视图中的数据

       MERGE ： 对数据进行合并操作(插入/更新/删除)

 

    3数据控制语言DCL

        DCL(Data Control Language)用来向用户赋予/取消对数据对象的控制权限。

       DCL包含以下语句：

       GRANT : 赋予用户某种控制权限

       REVOKE ：取消用户某种控制权限

   4. 事务控制语言(TCL)

        TCL(Transaction Control Language)用来对事务进行管理。

       TCL包含以下语句：

       COMMIT : 保存已完成事务动作结果

       SAVEPOINT : 保存事务相关数据和状态用以可能的回滚操作

       ROLLBACK : 恢复事务相关数据至上一次COMMIT操作之后

       SET TRANSACTION : 设置事务选项

7. 卸载或者重装数据库 

  卸载：

     1停止所有以oracle开始的服务，找到 UI（Universal Installer） 打开卸载界面 点击卸载产品按钮  选择所有的组件 点击删除按钮

 

     2删除服务 使用命令  sc delete 服务名称 删除所有的服务

     3删除注册表 开始->运行->输入 regedit命令打开注册表 找到 HKLM/SofeWare下的oracle 全部删除

     4找到oracle的安装目录 删除 如果无法删除使用软件强制删除

 重装：

     1 如果服务能够启动（ORCL和Listener结尾） 首先sqlplus /nolog 和conn /as sysdba连接  出现字符 历程启动  需要使用命令 startup

     2 服务不能启动  通过杀毒软件 去恢复oracle被删除的部分

     3 如果是Listener结尾启动不了使用netca删掉前面创建的监听 去重新创建一个

     4 如果是ORCL结尾启动不了 使用dbca 删除掉前面创建的orcl的数据库  在重新创建 一个新的

     5 dbca和netca无法打开 点击oracle安装文件点击setup。exe重装数据库

     6 setup.exe无法启动或者多次安装都失败，重装系统  

二 ORACLE管理命令

1 启动sqlplus工具 （ORACLE dba管理数据库的工具） 

           sqlplus /nolog  (无授权信息登录)

              conn /as sysdba(使用本地系统登录 本地用于加入了ORA_DBA组)

           sqlplus 用户名/密码@连接描述符

           sqlplus 用户名/密码  （默认本地ORCL）

2 oracle退出命令 

           exit(quit)  直接退出到dos命令

           disconnect(disconn|disco) 退出连接 在sql命令中

           orapwd（orapwd file=<fname> password=<password>） 重置密码文件

           password(passw) 修改密码 （必须登录之后）

3 操作上一次执行的sql (sql缓冲区中 缓冲区中只能存储一条sql) 

           list(l) 显示上一次缓冲区中sql

           run(r|/)执行上一条缓冲区中的sql

           clear buffer(cl buff)清空缓冲区中的sql

           get sql文件的路径  将文件的内容读取到缓冲区 可以使用 list或者run去查看或者运行

          save(sav) sql文件的路径 将缓冲区的sql写入到文件中

           start(sta|@) sql文件的路径 将文件的内容读取到缓冲区后 并执行 (get sql路径;run)

           edit(ed) 使用ed 文件路径修改文件的内容 或者使用ed命令修改缓冲区中的内容

           clear scr（ dos清屏cls） 在sql命令下清除屏幕上的文字

           spool 文件路径   记录当前用户的所有操作以及输出   spool off（终止）

4 启动和关闭 

        启动数据库orcl服务：

        sqlplus /nolog,

        conn /as sysdba

        startup

         nomount  数据库的instance已经打开  数据库没有打开

    mount数据的控制文件已经打开 并且和instance连接 但是不能远程连接

       open 数据库已经打开并且可以远程连接

       关闭数据库

       shutdown 启动或者关闭监听服务

       dos命令下  lsnrctl start|stop 检查连接描述符是否能连接  tnsping 描述名称

5. 连接描述符(客户端文件）【重点重点重点】

    oracle如果需要连接数据库 必须要确定三个元素

      ip地址 确定到 机器

      端口  确定机器上的某个机器（oracle的进程）（oracle默认的端口 1521）

      sid   确定oracle进程中的某个数据

    如果需要连接到任意一台机器的数据库 必须要配置这个三个元素

    这三个元素统称为 连接描述符  它的文件位于

      %ORACLE_HOME%/network/admin/tnsnames.ora

     该文件 中 #开头表示注释

   #以下 表示一个完整的连接描述符配置  别名可以任意

   #以下 指定了 ip 端口  sid

   # 原理 就是 Socket socket=new Socket("192.168.11.44",1521)

  clkdb =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.44)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

   )

6. 服务器监听文件(服务器端)【重点重点重点】

      %ORACLE_HOME%/network/admin/listener.ora

 

  在这个文件中 SID_LIST_LISTENER 必须添加

  (SID_DESC =

      (SID_NAME = ORCL)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (GLBAL_DBNAME = ORCL)

    )

  完整的配置如下

   SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

    (SID_DESC =

      (SID_NAME = ORCL)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (GLBAL_DBNAME = ORCL)

    )

  )

 

  如果需要其他客户端连接 需要将localhost修改为本机ipd

  LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.10)(PORT = 1521))

    )

  )

  远程 连接的测试语句

  create table aa (id number);

  select * from  tab where tname='AA';

 

7. 窗口

   dos命令窗口  只能执行dos的命令

   sqlplus连接 sql命令窗口

      sqlplus /nolog  未登录 进入sql命令下

   在sql命令下

      host dos命令

      clear scr 清除屏幕（host cls）

      conn 用户名/密码@连接描述符  切换数据库登陆

      exit|quit 退出sql命令到dos命令下

      discon  断开连接 不退出到dos命令

      password 修改密码 必须要登陆

      show user 显示当前用户

      SET PAGESIZE 100  --表述输出多少行 后重新显示表头

      SET LINESIZE 120 --表示 每一行现实的字符数

       desc 对象名（一般对象就是表） 显示表的结构

 

 

8. oracle的启动和关闭

    OracleServiceORCL  【oracle的主服务 必须要 启动该服务器 数据库才能启动】

    OracleOraDb11g_home1TNSListener 【监听服务 用于等待其他的客户端连接】

    关闭主服务

    1>net stop OracleServiceORCL  

    2>   sqlplus / as sysdba

       shutdown

    开启主服务

    1>net start OracleServiceORCL  

    2> sqlplus / as sysdba

       startup

    关闭监听服务

    lsnrctl stop

    启动监听服务

    lsnrctl start

 

 

9. 缓冲区命令

   （缓冲区就是上一次执行的sql语句的缓存）

    list|l命令 显示上一次执行的sql语句

    run|r|/ 执行上一次缓冲区中的sql语句

    clear buffer 清空缓冲区

    save 文件名 将缓冲区的sql写入文件

    get 文件名 将文件中的内容写入缓冲区

start 文件名 将文件中的内容写入缓冲区 并执行（get  + run）   

 

三. ORACLE数据类型

1. 字符串类型：用单引号引起来的字符序列。 

 

           CHAR(length)：固定长度字符串，不足自动以空格补齐长度，最多2000个字节。

 

           如：CHAR(10)  使用length(列) 获取的是定义的长度（length）

 

           VARCHAR2(length)：可变长度字符串，最多4000个字节。 【常用的字符串类型】

           如：VARCHAR2(10)  使用length(列) 获取的是实际数据的长度

           举例  title char(10) length(title)永远都是10

                content varchar2(10) 插入数据 'test' length(content)是实际数据的长度 4

2.数值类型： 

           NUMBER[(precision, scale)]：数值型，可以存储整数、浮点数。最高精度38位。如果没有指定最大位数和精度，就存储38位精度的数字。

           NUMBER(24)  最多24位 整数类型；

           NUMBER(5,3)  总和最多5位，其中小数最大是3位 整数位最大 5-3

           NUMBER  最大默认38位  整数和小数位总和不能超过38位         

3.日期类型

           DATE：存储日期和时间，精确到秒。时间值 【重要的类型】

               默认存放格式：“DD-MON-YYYY”

               默认显示格式：“DD-MON-YY

            java常用日期格式 yyyy-MM-dd hh:mm:ss

            orace转换日期格式  yyyy-MM-dd hh:mi:ss

            select sysdate from dual --输出的是date类型

            select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from  dual  --转换成了char类型

            TIMESTAMP[(seconds_precision)]：存储日期、时间和时区信息，带小数位的秒。时间戳

               如：TIMESTAMP(3)   秒后面小数点为3位。（最多可9位）

           大对象(Large objects)类型：最大存储128TB

                CLOB：Character LOB，用于存储字符数据。

               BLOB：Binary LOB，用于存储二进制数据。如图形、视频剪辑和声音文件。

               BFILE：用于存储二进制文件指针。

4. sql语法注意点 

       关键字不区分大小写 但是【字符串区分大小写】。

       表名和列名不区分大小写。

       语句以分号;结束

       单行注释使用--  多行注释 /**/

 

 

5.解锁scott账号 

    conn /as sysdba --以dba的账号登录

    alter user scott account unlock --解锁scott账号 

    conn scott/tiger  --密码是tiger     

6.显示表结构 

    dos命令下 可以使用 以下命令查看表结构

    desc 表名称  可以列出表结构 

    select * from tab; 查询当前用户下的所有的表   ，视图，同义词

7.常用的sql语句 

   select用于crud【create read update delete】 查询 是用于查询和筛选数据 【重中之重】

   使用java去理解 sql语句

   /*

    List<Emp> list=new ArrayList<Emp>();

    //select * from emp;

    syso("EMPNO ENAME JOB MGR....");

    for(Emp emp:list){

          syso(emp.empno+" "+emp.ename+" "+emp.job......);  

    }

    select ename as 雇员名称,sal as 薪水 from emp;

    syso("雇员名称 薪水....");

     for(Emp emp:list){

          syso(emp.ename+" "+emp.job);  

    }

   */

 

   select ename as 雇员名称,sal as 薪水 from emp;

   select语法结构   SELECT 【列名, 列名2…】| * FROM 表名 where 条件 ;

   【在oracle中字符串 使用''而不是""】

   查看当前方案中的表和其它数据库对象： 

    SELECT * FROM tab;

   查询指定表的所有列数据：

   SELECT * FROM 表名;

   查询指定表的指定列数据：

   SELECT 列名, 列名2… FROM 表名;

   可以在SELECT语句中使用算术运算符：+、-、*、/

   为查询的列取别名：

   SELECT 列名 [AS] 别名, … FROM 表名;

    使用||做连接。 

    举例： select '钱：'||'$'||sal as sal from emp

   使用DISTINCT消除重复内容。 

    举例：select distinct sal as sal from emp

   条件过滤（where）语句： 

       SELECT [DISTINCT] * | [列名 [别名],…]

        FROM 表名

       WHERE 条件;

        WHERE子句中的条件表达式：

       可以包括运算符(算术、比较、逻辑)

        可以使用()。

       可以使用常量、列、函数。

8.oracle常用的运算符 

    算术运算符：+、-、*、/

    连接操作符：将多个字符串或数据值合并成一个字符串 ||

    比较运算符： =、!=（或<>）、<、>、<=、>=

    ANY(值1,值2,值3…)    与列表中的任意一个值进行比较

    ALL(值1,值2,值3…)    与列表中的所有值进行比较

    in(值1,值2,值3)  列的值包含在所给的值中

 

举例：

--any表示等于其中的任意一个值  一般常用是使用in

       select * from emp where job=any('SALESMAN','MANAGER')

       select * from emp where job in('SALESMAN','MANAGER')

   --all 表示和所有值进行 比较 一般用于等于

       select * from emp where job!=all('SALESMAN','MANAGER')

       select * from emp where job not in('SALESMAN','MANAGER')

 

 

       逻辑运算符： 

       AND、OR、NOT

         举例：

             --等价于java &&

select * from emp where sal>2000 and job='MANAGER'

     --等价于java ||

select * from emp where sal>2000 or job='MANAGER'

     --等价于java  !

select * from emp where not sal>2000

        SQL 运算符的优先级从高到低的顺序是：

       算术 、连接、比较、逻辑（NOT、AND、OR）  

      模糊匹配 like

 _代表一个字母  %代表0个或者多个字母

        select * from emp where job like '_A%';  

9.大对象类型(large obects) 最大存储128T

>1.CLOB: CHARACTER LOB 用于存储字符数据

>2.BLOB: BINARY LOB 用于存储二进制数据(图形,视频,声音文件)

>3.BFILE: 用于存储二进制文件指针

四.oracle表的查询 

 1.表的排序

      排序语句：

       SELECT [DISTINCT] * | [列名 [别名],…]

         FROM 表名

        WHERE 条件

        ORDER BY 排序的列 [ASC | DESC],…;

 

       select * from emp order by hiredate desc   ( desc表示降序 从大到小 asc 表示升序)

 

       select * from emp where where job='MANAGER' order by  hiredate desc; (按条件查询出结果后排序)

 

       select * from mep order by hiredate desc,sal desc (按条件一升序后 相同的条件一在进行条件二排序)

 

2. Oracle的伪列 

 

      别名

       在select语句中 每一个被查询的表都有一个别名 默认就是它的表名 可以给表添加一个别名 添加的别名覆盖默认的别名

       select emp.ename from emp t; --错误

       select t.ename from  emp t;

       --如果多表查询时  每一个表都有相同的列名 这时必须要使用 别名

       select t.deptno from emp t,dept d;

 

 

      ROWID 是表中每一条记录的唯一标识符，数据库内部使用它来存储行的物理地址。

 

       该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的某一行。

 

       无法使用update语句修改

 

     ROWNUM 是SQL查询返回的结果集中每一行的行号。 【重点】

       可以用它来限制查询返回的行数。

       ROWNUM是先查到结果集之后再加上去的一个列 。

 

 

         在oracle中使用rownum来进行分页

         select t.* ,rownum as rn from emp where rownum>=10 and rownum<=20这是错误的

 

         因为行的循环查找 从索引1 开始  第一条记录的索引为1  不满足 循环第二条时 记录索引从1 开始 。。。。所以的行的行号都是1 永远不满足

           select * from (select t.*,rownum as rn from emp t) where rn>=10 and rn<=20 这是正确的  （一般使用） 

 

 

                  分页另一种写法

           select * from (select t.*,rownum as rn from emp t where rownum<=20) where rn>=10 正确的 

         select * from emp where rownum<=10 这是正确

        记录：rownum大于1的记录永远不可能成立  rownum<等于任何值都成立

 

 

五.oracle的函数

 

 

1.字符串函数 

      lower(n) 将字符转换成小写         对应java String.toLowerCase

         select lower('AAAA') from dual;  --aaaa

         select lower(ename) from emp;    --列输出结果全部小写

 

      upper(n) 将字符串转换成大写       对应java String.toUpperCase

 

      replace（列名,被替换的字符串，替换的字符串）    对应java String.replace或者 replaceAll

          select ename,replace(job,'MANAGER','经理')  from emp; --job列中 所有的MANAGER都被替换成了经理

          select replace('AAAtestgggg','test','测试') from dual; --AAA测试gggg

 

      instr(列名,被搜索的字符串) 

          select instr('AAAtestgggg','test') from dual; --4 索引从1开始

 

      substr(列名,开始位置,总长度) 

          select instr('AAAtestgggg',4,3) from dual; -- java substring  根据 被截取的字符串.substring(开始位置，结束的位置【不包括结束的位置】)  

  javascript 被截取的字符串.substr（开始位置，长度） 被截取的字符串.substring

                                                        oracle substr

 

      concat(参数1，参数2) 

          select concat('a','b') from dual;  --- ab  等价于 'a'||'b'    java "aaa"+"bbb"  

 

      length(列名) 获取字符的长度      java  String.getLength();

      trim(列名) 去空格  

      ltrim 去左侧的空格

      rtrim 去右侧的空格 

 

         nvl(列名,值) 当列的值为空时 输入第二参数的值 如果不为空 输出当前列的值   【重要】

    select nvl('','bac') from dual -- '' is null 所以输出 bac

 

 nvl2(列名,值1,值2) 当列的值为空时 输出第三个参数的值 如果不为空输出第二个参数的值 【重要】 

 

 

         decode(列名,条件1，值1，条件2,值2......,条件n,值n，默认值) 当列的值等于条件n时输出值n 【重要】

 select decode('2','0','男','1','女','人妖') from dual

2.数字函数            

      mod(5,2) 取余数 等价于java 5%2 

      round(n) 对整数位进行四舍五入 满5进1 

      round(n,p) 对小数位进行四舍五入 比如 round(5.6767,2) 输出为5.68 

      trunc(n) 截断小数位 只保留整数位 

     trunc(n,p) 截断小数位意外的位数 比如 trunc(5.666,2) 输出为5.66 就是保留p位小数

 

 

3.日期函数            

      sysdate 获取系统的当前时间  【重点】

 

      add_months(日期,月数) 将日期加上月数 并返回  比如 (sysdate=2016-3-3)+5个月 =2016-8-3

 

      select sysdate+天数 from dual 将当前日期加上某个数字 表示+天数

 

      months_between(日期1，日期2)  比较日期1和日期2的相差的月份数  结果=日期1-日期2

 

      last_day(日期) 返回日期对应月份的最后一天

 

      round(日期,格式)

            YEAR 获取当年的第一天 

            MONTH 获取当月的第一天 

            DAY 获取 第一个周末的第一天 

 

 

      next_day(日期，第几天) 获取当前日期下一个星期的第几天  

 

    转换函数 【每一个都是重点】

 

      to_date(日期字符串,日期格式)  字符串的格式必须由第二个参数来判断 

 

日期格式 ：

 

年 yyyy，月mm,日dd,小时（hh12小时制，hh24 24小时制） 分钟 mi 秒 ss

举例 :select to_date('2015/03/09','yyyy/mm/dd') from dual to_char(数字) 将数字转换成char类型 

 

to_char(日期,日期格式)  将日期转换成char类型  日期格式 可以任意 输出的内容 根据日期的格式而定

举例   select to_char(sysdate,'hh24')  输出24小时制的当前时间的hour

 

to_number(字符串数字) 将字符串的数字转换成number类型 

举例    to_number('123')输出数字 123  

4.常用的聚合函数

   什么是聚合 ：将多行记录压缩成1行或者多行进行演示

     1 count(*)  count(1) count(列名) 统计数据行的 个数 【重点】

     速度比较（oracle9i之前的说法）

       select count(*) --是最慢的  统计表的行数 先去数据库中了解表的结构

       select count(1) --第二慢的  找到数据库中表的一列

       select count(emp.empno) from emp; --第三慢的 因为已经指定了列名 不需要查表结构

       select count(rowid); --rowid直接定位的物理地址

     2 sum(数字的列) 统计当前列的总和

     3 avg（数字类型的列） 同行当前指定列的平均数

         select sum(sal)/count(rowid) from emp;

         select avg(sal) from emp;

     4 max(任意 列) 统计当前列最大的那个值

     5 min(任意列) 统计当前列种最小的那个值

 

6 wm_concat 合并结果集函数 

       将部门下的所有员工信息,列表显示

列如:研发部 张三/李四/王五

select d.dname,wm_concat(e.ename) from emp e inner join dept d on e.deptno=d.deptno group by d.deptno,d.dname;

7.exists函数

select * from emp e where exists (

select deptno from emp group by deptno having min(sal)=e.sal

)

5.正则表达式函数

>1.REGEXP_SUBSTR

     REGEXP_SUBSTR函数使用正则表达式来指定返回串的起点和终点

select regexp_substr('MY INFO: Anxpp,23，and boy','[[:digit:]]',1,2) from users;

select regexp_substr('MY INFO: Anxpp,22，and boy','my',1,1,'i') from users;

 

>2. REGEXP_INSTR

REGEXP_INSTR函数使用正则表达式返回搜索模式的起点和终点（整数）。如果没有发现匹配的值，将返回0。

select regexp_instr('MY INFO: Anxpp,23，and boy','[[:digit:]]') from users;

>3. REGEXP_LIKE

     通常使用REGEXP_LIKE进行模糊匹配。

select name from users where regexp_like(phone,'666');

>4.REPLACE和REGEXP_REPLACE

     REPLACE函数用于替换串中的某个值。

select replace('MY INFO: Anxpp,23，and boy','an') from users;

 

下面演示使用该函数计算某字串在源串中出现的次数：

select (length('MY INFO: Anxpp,23，and boy')-length(replace('MY INFO: Anxpp,23，and boy','an')))/length('an') from users;

 

REGEXP_REPLACE是REPLACE的增强版，支持正则表达式，扩展了一些功能。

select regexp_replace('电话：023  5868-8888 邮箱：anxppp@163.com',    '.*([[:digit:]]{3})([^[:digit:]]{0,2})([[:digit:]]{4})([^[:digit:]]{0,2})([[:digit:]]{4}).*',

     '(\1)\3\5'

) phone from users;

>5. REGEXP_COUNT

REGEXP_COUNT函数返回在源串中出现的模式的次数，作为对REGEXP_INSTR函数的补充。

虽然COUNT是一个集合函数，操作的是行组，但是REGEXP_COUNT是单行函数，分别计算每一行。

select regexp_count('MY INFO: Anxpp,23，and boy','an') from users;

六.高级查询

1.子查询【重点】

select * from emp where sal>2000 and job='MANAGER'

根据子查询的位置 可以分为 表子查询 列的子查询 条件的子查询  根据数据返回的行和列 分为 单行单列 多行多列多行单列

-- 1表子查询（虚表 内存表）   比条件和连接 更容易理解（多行多列子查询）

select * from (select * from emp where sal>200) where job='MANAGER'

 

--2 列子查询 每个子查询只能返回一行记录（单行单列子查询）

--查询部门名称

select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

select ename,(select dname from dept where rownum=e.empno) as dname from  emp e;

 

--3 条件子查询

--查询所有雇员为 ACCOUNTING的雇员

select deptno from  dept where dname='ACCOUNTING'

select * from  emp where deptno=10

--=只能等于一行  单行单列子查询

select * from  emp where deptno=(select deptno from  dept where dname='ACCOUNTING');

 

--查询所有雇员为 ACCOUNTING RESEARCH的雇员 (多行单列子查询)

select * from  emp where deptno=any(select deptno from  dept where dname='ACCOUNTING' or  dname='RESEARCH');

select * from  emp where deptno in(select deptno from  dept where dname='ACCOUNTING' or  dname='RESEARCH');

 

 

2.集合

{1，2，4}^{2,3,4}={2,4}

{1，2，4}U{2,3,4}={1，2，3,4}

{1，2，3}-{2，3，4}={1}

集合的操作

 

集合操作符：合并多个查询结果

UNION ALL：将多个查询结果合并到一个结果中，有重复行【重点】

UNION：将多个查询结果合并到一个结果中，没有重复行（并集）【重点】

INTERSECT：返回两个查询结果中共有的行 (交集)

MINUS：返回从第一个查询结果中减去第二个查询结果中相同的行之后剩余的行(差集)

 

 

3.复制 表

 create table dept1 as select * from dept

select * from  dept;

select * from dept1;

--交集

select * from dept intersect select * from  dept1;

--并集 union 去掉重复行

select * from dept union select * from  dept1;

--并集 uinon all 不会去重复行

select * from dept union all select * from  dept1;

--差集

select * from dept1 minus select * from  dept;

 

4. 分组

     1.什么是分组

      分组指定的条件  【相同的数据数据被分为一组】最终查询的结果

        就是分组的组数

      表名 Test

       A列  B列

       1  a

       1  b

       2  c

       2  d

       select A from  Test group by A

       --因为根据A这一列 相同的值被分为一组 最终被分为两组 只有两条被显示

         1

         2

       select A,B from Test group by A  --这是错误的

       两组都被压缩成一行

       1 组 存在a和b两个值  不能被压缩成 一行 只能使用聚合函数压缩成

             一行才能显示

 

     --查询部门中薪水最高的薪水

      select * from emp order by deptno;

     2.排序 是针对分组产生 之后的结果在进行排序

      select deptno,max(sal) from emp group by deptno order by deptno

 

     3.having子句 是对分组之后的结果进行筛选

      --查出部门最高的薪水>3000  

       select deptno,max(sal) from emp group by deptno having max(sal)>3000

 

 

5. 表连接【重点】

     单表查询 -》多表连接查询 

   笛卡儿积【交叉连接】{1,2,3}交叉{4,5} 可以参考【笛卡儿积.png】

     1     

     2      4

     3      5

     最终产生的结果  14 15 24 25 34 35   交叉产生的记录数就是 两个集合的个数的乘积

  数据库表的笛卡儿积

    数据行的笛卡尔积

   select * from table1，table2

  举例说明 92语法的内连，左外连 ，右外连

     用户表（UserInfo）

     用户id 用户名

       1     test

       2     user

     文章表 (Article)

       文章id 文章内容 用户id

        1       java      1

        2       oracle    3

 

    1  test   1  java  1

    1  test   2   oracle 3

    2  user   1   java  1

    2  user   2  oracle  3

    //86语法

    select * from userinfo u,arcticle a where u.用户id=a.用户id

    1  test   1  java  1

 

    //92语法

     内联必须都满足条件才显示记录

        select * from userinfo  inner join arcticle a on u.用户id=a.用户id  --是86语法的改进 结果和86语法是相同

     外联

         --左外连 以左边的表为主表 左表如果存在某条记录和右中所有的记录都无法关联 就保留一条记录

        select * from userinfo  left join arcticle a on u.用户id=a.用户id  

         1  test   1  java  1

         2  user   null null null

 

   建议

     多表查询需要先找到两张表的关系

     如果需要做多表连接查询尽量使用 内联 找到两张表的关系  直接将条件 在on后  (如果有特殊情况 如果主表中的记录没有连接也要输出 只能使用左外连接)

 

 七.sql语言操作分类【需要背 面试需要】：

 

1 数据定义语言DDL （DATA DEFINE LANAGUAGE）  （定义，操作数据的结构） 【-->java的变量定义】 

       CREATE : 在数据库中创建新的数据对象 

       ALTER : 修改数据库中对象的数据结构 

       DROP : 删除数据库中的对象 

       DISABLE/ENABLE TRIGGER : 修改触发器的状态 

       UPDATE STATISTIC : 更新表/视图统计信息 

       TRUNCATE TABLE : 清空表中数据 

       COMMENT : 给数据对象添加注释 

       RENAME : 更改数据对象名称 

2数据操作语言DML 【重点关注 工作必用】

       DML(Data Manipulation Language)(CRUD)，用于添加/修改/查询数据库中数据。

       DML包含以下语句：

       INSERT ：将数据插入到表或视图 

       DELETE ：从表或视图删除数据 

       select ：从表或视图中获取数据 

       UPDATE ：更新表或视图中的数据 

       MERGE ： 对数据进行合并操作(插入/更新/删除) 

3.数据控制语言DCL 

       DCL(Data Control Language)用来向用户赋予/取消对数据对象的控制权限。

 

       DCL包含以下语句：

       GRANT : 赋予用户某种控制权限

       REVOKE ：取消用户某种控制权限

4. 事务控制语言(TCL) 【重点】

 

       TCL(Transaction Control Language)用来对事务进行管理。

 

       TCL包含以下语句：

       COMMIT : 保存已完成事务动作结果 

       SAVEPOINT : 保存事务相关数据和状态用以可能的回滚操作 

       ROLLBACK : 恢复事务相关数据至上一次COMMIT操作之后 

       SET TRANSACTION : 设置事务选项 

 

5.crud （create read update,delete）【重点】

     所有的数据库  都是使用这四个单词来描述增删改查 isud  (insert select update delete)

     1 insert语句

       语法：

          INSERT INTO 表名[(列名1,列名2,…)] VALUES(值1, 值2,…);   --列名的个数和值的个数 必须相同              【表列较多 建议使用】

          insert into 表名  values(值1，值2 .。。)   --如果没有指定列名  值得个数必须和表中所有的列的个数相同 列的类型必须匹配   【表列较少建议使用】

          INSERT INTO 表名[(列名2，列名1,…)] VALUES(值2，值1, …); 值得顺序和列的顺序保持一致

          insert into student select '11','test1',25,'女'  from dual   --根据常量来模拟

          insert into student1 select * from student      --可以根据 insert into select语句来进行复制表的数据 （备份表）    seelct查询的列数必须和insert插入的表列数一致 并且列类型匹配

 

日期类型的插入

              insert into student1 values(7,'a',20,'男',sysdate)

   insert into student1 values(7,'a',20,'男',to_date('1987-06-18','yyyy-MM-dd'))

  

2 update语句（注意在修改之前一定先将条件写好）

       语法：

           UPDATE 表名 SET 列名=值[,列名2=值2,…] [WHERE 修改条件];  --

3 delete语句（注意在修改之前一定先将条件写好）

            delete scott.emp where empno='8110'

八.事务【重点】

     事务表示 一段sql的执行 要么同时成功 要么同时失败

任意的dml语句【数据的修改】 必须要提交数据（因为用户的误操作 导致数据的损坏 有可能导致无法挽回的灾难  可以使用 事务的机制来解决这类问题）

 

     commit（提交） 一旦提交了事务 数据就被修改到物理的数据文件中  （ctrl+s） 保存到文件

 

rollback(回滚) 如果除了误操作 需要回滚当前事务 （记事本ctrl+z）(撤回)

事务的acid属性

 

原子性(Atomic)：

指整个数据库事务是不可分割的工作单元。原子性确保在事务中的所有操作要么都发生，要么都不发生。

举例：

--被一起的执行的dml语句位于同一事务当中

insert into scott.emp values(8111,'test','MANAGER',7934,sysdate,3000,100,10);

insert into scott.emp values(8112,'test','MANAGER',7934,sysdate,3000,100,10);

insert into scott.emp values(8113,'test','MANAGER',7934,sysdate,3000,100,10);

rollback;

 

一致性(Consistency)：

一旦一个事务结束了(不管成功与否)，系统所处的状态和它的业务规则是一致的。即数据应当不会被破坏。

举例：

一旦数据被约束成某种 类型或者某些值那么就必须对应满足这些类型或者某些值

Insert into scott.emp values(8113,'test','MANAGER',7934,'2012-12-15',3000,100,10);

 

隔离性(Isolation)：

指多个事务同时操作同一数据时，每个事务都有各自的完整数据空间。未提交的数据  在其他的客户端中是无法看到因为因为隔离性

 

持久性(Durability)：一旦事务完成，事务的结果应该持久化 存到物理磁盘中（就是断电了下次还能 看的到 ）

 

事务的提交方式【了解即可】

默认数据库使用 手动（非自动）提交方式需要操作者 使用commit关键字来提交

 SQL> show autocommit;

 utocommit OFF

设置自动提交

SQL> set autocommit on;

SQL> insert into scott.emp values(8113,'test','MANAGER',7934,sysdate,3000,100,10);

已创建 1 行。

提交完成。

 

隐式的事务提交（不需要使用commit和rollback也会自动提交）

1 自动隐式提交事务：执行一个DDL语句、执行一个DCL语句、从SQL*Plus正常退出(exit,quit)

2自动隐式回滚事务：

强行退出SQL*Plus、客户端到服务器的连接异常中断、系统崩溃  

回滚点

savepoint a;

insert into scott.emp values(8113,'test','MANAGER',7934,sysdate,3000,100,10);

savepoint b;

insert into scott.emp values(8114,'test','MANAGER',7934,sysdate,3000,100,10);

rollback to a; --没有记录被插入

rollback to b  -- 8113被插入 8114被回滚

commit;

事务引发的问题和隔离级别【重点】

事务 对同一行数据进行操作时  必须等先操作的客户端提交或者回滚事务后 另外一个客户端才能操作数据（锁）

事务操作同一行数据出现的问题 （并发的引发的问题）

 

脏读  【是一种错误】

读取到了用户未提交的数据  oracle数据库永远不会出现脏读

 

不可重复读【只是一种设计问题】

在读取的事务中 两次重复读取同一行数据发现数据 被更改了

 

幻读【只是一种设计问题】

在读取的事务中 两次读取某一个条件的多条数据时发现多出了几条数据

  

隔离级别（就是为了解决事务操作同一份数据导致的问题）  

EAD UNCOMMITTED： 读未提交数据。脏读、不可重复读、幻读都可能发生。它的事务隔离性最低。

READ COMMITTED：读已提交数据。解决了脏读。【数据库的默认的隔离级别】

REPEATABLE READ：可重复读。解决不可重复读，脏读。

SERIALIZABLE：串行化。解决任何并发事务问题。最严格的事务

Oracle只支持READ COMMITTED和SERIALIZABLE。

默认为READ COMMITTED

 

设置隔离级别

alter session set isolation_level=serializable;--如果需要演示 必须每个客户端都要执行

  

九.用户管理 

 1.用户操作

    创建用户 create user jyb identified by jyb 【密码区分大小写】

    修改用户 alter user jyb identified by test

    锁定用户  alter user jyb account lock;

    解锁用户 alter user jyb account unlock;

    查询所有的用户       select * from all_users;

    删除用户  drop user jyb 【cascade代表所有关联的对象都被删除】;

2.角色操作 

  创建角色  create role stu;

  删除角色  drop role stu; 

 给角色添加功能权限  grant create session to stu

 给用户添加角色            grant stu to jyb     

 通过dd表可以查询所有的系统表：select * from dictionary where table_name like '%ROLE%'  

 查询所有的角色 select * from dba_roles;

 

3.权限操作 

  系统权限   （针对某些对象进行修改（DDL）的权限）

  grant 系统权限 to 用户|角色

  所有的系统权在 dba_sys_privs表中可以查询

  grant create user to jyb --jyb就能通通过  create user 创建用户

  回收系统权限

  revoke 系统权限 from 用户|角色

 

  对象权限 （针对某个对象（表）进行数据操作（DML）（crud）的权限）

  grant 操作（CRUD|RW） on 对象|all to 用户|角色

  grant insert on jyb.course to jyb  --jyb用户就拥有了访问course表的权限

  回收对象权限 

  revoke  操作（CRUD|RW） on 对象|all from 用户|角色

  角色 （将对象权限或者系统权限赋予给角色就是角色权限）

  系统默认都预留了一些角色 这些角色都拥有系统较高的权限 比如 DBA

4.备份和恢复 （dos备份）

    热备份

       (必须要先连接数据库后  备份数据后导出文件)

 

    冷备份（DBA）

      （不需要连接oracle数据库 直接备份文件）

 

    热备份举例：

    备份（DOS）：

       1》客户端备份 【文件被保存在当前的客户端】【一般使用客户端备份】

       exp 用户名/密码@连接描述符  file=保存的文件.dmp   【tables=(表1，表2....)】  owner=方案名

  

    2》服务器备份 【文件被保存在服务器上】

    被导出的用户必须要添加 grant create any directory to 用户名

    create directory 目录名称 as 'c:/test'

     expdp 用户名/密码@连接描述符 directory=目录名称 dumpfile=jybdp1.dmp  tables=(表1，表2....)

 

还原:

    客户端 还原

    imp 用户名/密码@连接描述符  file=保存的文件.dmp full=y ignore=y

 

    服务器 还原

impdp jyb/jyb@orcl directory=jybdir dumpfile=jybdp1.dmp  tables=(表1，表2....)  

 

十.表操作 

1.crud表的操作

  create user learn_object identified by test;

 

  --给予权限 

   grant dba to learn_object;

 

   -第一种 创建表 

    create table tb_userinfo(

    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

    username varchar2(20) not null, --not null 表示当前的列 不允许插入 null 和 ''

     sex number default 0   --默认值 default 值 当插入数据为空时 自动填上默认值 插入不为空的值 插入当前值

     )

 

     第二种复制表

     create table 表名称 as select * from 表 [where 1!=1]

 

     第三种类型赋值法

     create type  userobj as object (

      d number,

        uname varchar2(20),

        createDate date

     )

  

      create table userinfo_cpy_type of userobj

 

      --修改表名称 

      rename 旧表名to 新表名

 

       --修改列名 

      alter table userinfo rename column 旧列名 to 新列名

 

        --修改列类型 

       alter table userinfo modify username char(20)  

 

        --添加列 

       alter table userinfo add age number default 18

 

        --删除列

       alter table userinfo drop column age 

 

       --查询表的结构

       desc 表名  --命令下

       select * from all_tab_columns where table_name='USERINFO1'  --数组字典表（DD） 在sql语句中

 

 

 

2. 删除操作 

 

 

       删除表：把表中所有的行和表结构都删除。

   DROP TABLE 表名;--Oracle中删除表时并没有直接删除，只是放置到“回收站”

 

   显示回收站中的对象：SHOW RECYCLEBIN;

    恢复回收站中的表：FLASHBACK TABLE 表名 TO BEFORE DROP;

    删除回收站中的表：PURGE TABLE 表名;

    彻底删除 不进回收站：DROP TABLE 表名 PURGE;

 

    截断表：删除表中所有的数据行，重置表的存储空间。

       TRUNCATE TABLE 表名;

        delete from 表名

 

       区别 ：

          1  truncate是ddl语句  delete 是dml语句 都可以删除数据行

          2  truncate不需要提交事物 delete需要提交事物

          3  truncate删除表数据及表的存储 无法恢复 delete删除 可以使用归档日志恢复   

          4  delete可以删除指定条件的记录

          5  如果表结构损坏了 导致数据无法访问 必须重置表 使用truncate 因为delete 不会删除数据 只是隐藏数据 （查询的效率依然地下）

             如果需要根据条件删除 并且希望可以恢复 一般使用delete

 

3. 表分区 （了解）

           意义 ：可以容灾（出现事故后 还有一部分数据保留）

 

    范围(range(字段名))

 

            范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期

 

       格式:

 

            create table XXX() partition by range(表中数值类型的字段名)(partition 分区名 values less than 具体值  [tablespace 表空间名] ,…)

 

    列表(list(字段名))

            该分区的特点是某列的值只有几个

 

           …partition by list(字段名) (partition 分区名 values (值列表)值  [tablespace 表空间名] ,…))

 

    哈希(hash)

 

 

            分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。

 

 

           …partition by hash(表中数值类型的字段名)(partition 分区 [tablespace 表空间名] ,…)

 

 

            … partition by hash(表中数值类型的字段名)PARTITIONS n
STORE IN (s1,….sn)

 

 

    列表(list(字段名))

 

    复合分区(分区组合)  

 

 

 

十一. 约束 

 

      1》约束的查询方式

       --可以通过dd表来查询约束

       select * from all_constraints where table_name='USERINFO'

        约束的类型

         C (Check约束)

         P (主键约束)

         U （唯一约束）

         R (外键约束)

 

        --直接添加不为空的约束

 

       2》not null 约束

       create table tb_userinfo(

 

 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

 

 

                   username varchar2(20) not null, --not null 表示当前的列 不允许插入 null 和 ''

 

 

                )

        --constraints 约束名 可以指定约束名称 如果不指定约束名称 系统会自动分配一个名称  列存在了 添加约束 需要考虑 真实的数据行是否和约束冲突 因为要满足一致性

        alter table userinfo drop constraints nu_age

        alter table userinfo modify age number [constraints nu_age]    not null

 

        --直接添加不为空的约束 给约束添加别名

 

        3》主键约束

       create table tb_userinfo(

 

 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

 

 

                   username varchar2(20) [constraint nullable_username] not null, --添加别名后 会添加一个别名对应的约束

 

 

               )

        

        --主键 它的意义在于 添加一列 用于唯一标识当前行  uniqie约束 知识表示 当前的列不允许出现重复的的值

        --在技术上  主键唯一 不能为空  uniqie 唯一能为空

       alter table userinfo drop constraints SYS_C0011165

       alter table userinfo modify userid constraints pri_userid primary key

 

 

 

       4》check约束

 

        --通过check的方式 添加不为空的约束  check的语法要和where条件一致         

 

     

        create table tb_userinfo(

 

 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

                   username varchar2(20) [constraint check_username]  check(username is not null) --通过添加约束的方式 添加not null

        )     

 

        alter table userinfo modify sex VARCHAR2(20) constraints chk_sex check(sex in('女','男'))

        alter table userinfo modify age number constraints chk_age check(age between 1 and 100)

         

 

        --check约束必须满足where条件  不能使用子查询

       create table tb_userinfo(

               userid number primary key,

               username varchar2(20) not null,

               sex number constraint check_sex check(sex in(0,1))

        )    

 

        5》外键约束

 

       --定义外键  外键的定义 必须能唯一定位到外键对应的记录 比如 知道学生信息后 就唯一确定了他所在的班级

           --外键 如果某个表中存在一列 关联到另外一张的主键 这一列叫做外键列

           --如果某个列被添加了外键 外键列的值 必须是 引用表中主键的值 或者是null

 

       --班级表

 

 

       create table tb_grade(

           cid  number primary key,

           cname varchar2(20) not null

        )

        --cid number check(cid in(select cid from tb_grade)) 用于理解

 

       create table tb_student(

           sid number primary key,

           sname varchar2(20) not null,

           cid number [constraint FOR_STUDENT_CID] references TB_GRADE (CID)         

        )

 

 

        --外键的引用 必须通过alter table的方式来添加

 

 

       alter table tb_student add constraint FOR_STUDENT_CID foreign key (CID)

                      references TB_GRADE (CID)         

 

        --删除外键必须通过名称  未定义名称时 需要通过      dba_constraints表去查询         

        alter table tb_student drop constraint SYS_C008553    

 

 

        --查询表所有的外检 类型  C表示check约束 P代表主键  R代表外检  

        select * from dba_constraints  where table_name='TB_STUDENT'                    

 

十二.序列 

 

 

        --创建序列  minvalue表示范围中最小的那个值 maxvalue表示范围中最大的那个值

 

 

       --start with 表示序列从1开始递增  increment by表示步值（每次累加值）

       create sequence TB_GRADE_SEC

        minvalue 1

        maxvalue 999999999999999999999999999

        start with 1

        increment by 1

        cache 20;

 

        --查询当前值得下一个值

       select TB_GRADE_SEC.NEXTVAL from dual;

 

        --查询当前值

       select TB_GRADE_SEC.Currval from dual;

        select * from tb_grade;

 

        --在insert语句中使用序列  一般使用 序列来控制 主键的值

       insert into tb_grade values(TB_GRADE_SEC.NEXTVAL,'test');

       insert into tb_student values((select max(stuid)+1 from tb_student),'张三',1)

        commit;

               

        --删除序列

       drop sequence tb_grade_sec               

 

 

十三.索引 

        作为一个普通的开发人员 唯一提高性能工具就是索引【重点】

 

        索引候选列需要存储很大范围（重复的范围 每一个值都不一样 就是范围大）的值——“B-树”索引

 

        索引候选列只包含很少范围（比如列上的值 都是某几个枚举的值  男,女）的值——“位图”索引

 

 

        --在列上使用 unique，primary key可以自动添加btree索引

       create table TB_STUDENT

        (

          SID   NUMBER  primary key not null,

          SNAME VARCHAR2(20) unique,

          sex number check(sex in(0,1)),

          CID   NUMBER

        )

 

        --唯一索引（BTree索引） map.put(1,'test') 创建btree的语句 唯一索引 可以直接定位行 效率最高  

        create UNIQUE index index_student_sid on TB_STUDENT(sid)

        --normal索引（BTREE索引） 表示列允许出现重复 但是不能出现大范围的重复 否则效率降低

       create index index_student_sid on TB_STUDENT(sid)  

 

        --创建位图索引 大范围的重复时 使用索引

       create bitmap index bitmap_student_sex on TB_STUDENT(sex)

 

        --创建基于函数的索引

       create index upper_sname on tb_student(upper(sname))

 

        --删除索引

        drop index upper_sname                   

 

 

十四.视图 

    CREATE [OR REPLACE] VIEW 视图名  

          AS 查询语句 [WITH CHECK OPTION] [WITH READ ONLY];

        选项：

       OR REPLACE：视图存在时就替换

       WITH CHECK OPTION：视图的创建条件不能更改

       WITH READ ONLY：视图中的内容不能更改

       --创建视图 用于重复利用相同的sql 可以用于权限控制  可以隐藏机密的数据

       create or replace view vi_student_grade as

       select s.sname,g.cname from tb_student s inner join tb_grade g on s.cid=g.cid where cname='1501' WITH CHECK OPTION

 

        --查询 

       select * from vi_student_grade where cname='1501'

        create or replace view vi_student as select * from tb_student;

 

        --直接通过表更新，删除 

       update tb_student set sname='test' where sid=10;

 

        --单表视图 可以用于间接更新，删除 

      update vi_student set sname='test' where sid=10;

 

        删除视图 

       DROP VIEW 视图名;

 

 

 十五.同义词 

        --创建同义词

 

        CREATE SYNONYM syn_scott_emp FOR scott.emp; 

 

         select * from syn_scott_emp;     

         drop SYNONYM  syn_scott_emp  

                

plsql结构定义

 

    PL/SQL(Procedural Language/SQL)：过程化编程语言

   Oracle对标准SQL语言的过程化扩充

   用来编写包含SQL语句的程序，加入业务逻辑处理功能。

   PL/SQL程序由块组成，每一个块都包含有PL/SQL和SQL语句。块的结构如下：

       [

           declare 变量名  类型;--声明变量 

              变量名 类型;

        ]

        begin

          [过程语句]

          [exception when 异常类型 then 过程语句]

        end;

 

    举例：     

    /** 等价

     {

         System.out.println('helloworld')

      }

    **/

    begin   --等价于{

       dbms_output.put_line('helloworld');

    end;    --等价于}

 

    --带变量的plsql定义

   declare userid number;

    begin

      --变量的赋值 可以通过 变量名:=值

     userid:=1;

      dbms_output.put_line(userid);

    end;

一.异常捕获

>1异常捕获

declare

myVar number;

begin

  begin

    select sal into myVar from emp where ename='SMITH1';

    exception

    when NO_DATA_FOUND then

      dbms_output.put_line('没有数据找到');

  end;

  select sal into myVar from emp where ename='SMITH';

  myVar:=5/0;

  exception

     when ZERO_DIVIDE then

       dbms_output.put_line('除数为0');

     when others then

       dbms_output.put_line('未知的异常');

end;

--自定义异常

declare

myexe exception;

sex varchar2(3):='&请输入性别';

begin

  if(sex not in ('男','女')) then

  raise myexe;

  end if;

  exception

    when myexe then

      dbms_output.put_line('未知性别');

      when others then

        dbms_output.put_line('未知的异常');

end;

--例子

   declare userid number;

    begin

      --变量的赋值 可以通过 变量名:=值

     userid:=1/1;

      dbms_output.put_line(userid);

      --异常的定义  when 异常类型  then 异常处理的代码块

     exception when others then

         dbms_output.put_line('出现异常');

    end;

二.复合类型

   --在过程中调用函数和存储过程

declare curdate date:=sysdate;  

curDateStr varchar2(10);

begin

   curDateStr:=to_char(curdate,'yyyy-MM-dd');

   dbms_output.put_line(curDateStr);

end;

 

--在过程中定义复合类型

   1>数组类型 

   declare  

     type ArrayList is table of number index by binary_integer;

     ua ArrayList;

    begin

      ua(0):=12;

      ua(-1):=20;

      dbms_output.put_line(ua(0));

    end;

 

    2>定义复合类型的对象类型  使用对象的成员可以使用对象.属性名称访问

   declare  

     type userinfo is record(

        userid number,

        userName varchar2(20),

        sex number

     );

     jyb userinfo;

     begin

        jyb.userid:=1;

        jyb.userName:='蒋永兵';

        jyb.sex:=0;

        dbms_output.put_line(jyb.userName||'的用户id是:'||jyb.userid);

     end;     

三. 列类型和行类型

   --定义列类型 通过获取表的列类型给当前变量 

   declare sex_tmp tb_student.sex%type;

    begin

      sex_tmp:='1';

      syso(sex_tmp);

end;

 

    --行类型 通过select into语句抓取一行

   declare student_row tb_student%rowtype;

    sname varchar2(20);

    begin

       --select into用于在过程语句中将表中的数据抓取到变量中    

       --这里是抓取行

      select * into student_row from tb_student where sid=1;

       --抓取行中的某一列到变量

      select sname into sname from tb_student where sid=1;

       syso(student_row.sname);

       syso(sname);

    end;

四. 定义过程的逻辑控制语句  

1>if逻辑控制语句 

   declare sex number:=3;

    begin

       if (sex=0) then

          syso('男');

       elsif(sex=1) then

          syso('女');

       else

          syso('不男不女');

       end if;

    end;

2>循环

--loop循环 相对于java的do while循环 

   declare num number:=1;

    begin

     loop  

         syso(num);

         num:=num+1;

         exit when num=11;

      end loop;

    end;

--while循环 相对于java的while循环 

   declare num number:=1;

    begin

       while (num<=10)loop  

         syso(num);

         num:=num+1;

      end loop;

    end;

 

--for循环 相对于java的for循环 reverse表示反转输出 

   declare num number:=1;

    begin

       for i in  1..10 loop  

         syso(i);

      end loop;

    end;

 

 

--隐式游标方式的循环 用于循环迭代表记录 

 

   begin

       for stu_tmp in (select * from tb_student) loop

          syso(stu_tmp.sname);

       end loop;

end;  

 

五.存储过程的定义 

1>过程（多次编译多次执行）： 

 

       --过程实现计算器

       declare p1 number:=1;

        p2 number:=2;

        sign varchar2(3):='-';

        begin

          if sign='+' then

             syso(p1+p2);

          elsif(sign='-' ) then

             syso(p1-p2);

          elsif(sign='*' ) then

             syso(p1*p2);

          elsif(sign='/' ) then

             syso(p1/p2);

          end if;

        end;

2>存储过程（一次编译 多次执行） 

       --存储过程的定义   

        --存储过程执行只是编译的过程  如果需要执行存储过程的代码 需要在过程中调用

       create or replace procedure pro_arthirm(p1 number,p2 number,sign varchar2)  

        as

        --参数的定义

       begin

       --过程体

         if sign='+' then

             syso(p1+p2);

  elsif(sign='-' ) then

             syso(p1-p2)

          elsif(sign='*' ) then

             syso(p1*p2);

          elsif(sign='/' ) then

             syso(p1/p2);

          end if;

       end;

        --在plsql中调用存储过程

       declare p1 number:=1;

        p2 number:=2;

        sign varchar2(3):='+';

        begin

             pro_arthirm(p1,p2,sign);

        end;

        --在command模式下  需要使用   

        call 过程名称(参数。。。)

        execute（exec） 过程名称(参数。。。)

        show errors 显示存储过程编译之后的错误

 

3>存储过程参数 

       /**    参数类型：

           IN 输入参数。只能获取它的值 不能修改他的值 调用设置的值 可以在存储过程中查看

           OUT 输出参数。只能在过程体中赋值 不能查看到传入的值

           IN OUT 输入输出参数。可以取它的值，也可以给它赋值

            public int arthirm(int p1,int p2,String sign){

               int returnNum=5;

               if(...){

                  returnNum=p1+p2

               }

               return returnNum;

            }

            **/

            create or replace procedure pro_arthirmByReturn(p1 in number,p2 in number,sign in varchar2,returnNum in out number)  

                as

                --参数的定义

               rtnNum number;

                begin

                syso(returnNum);

                --过程体

                 if sign='+' then

                    returnNum:=(p1+p2);

                  elsif(sign='-' ) then

                     returnNum:=(p1-p2);

                  elsif(sign='*' ) then

                     returnNum:=(p1*p2);

                  elsif(sign='/' ) then

                     returnNum:=(p1/p2);

                  end if;

                end;

              declare p1 number:=1;

           p2 number:=2;

                sign varchar2(3):='+';

                returnNumber number:=10;

                begin

                     pro_arthirmByReturn(p1,p2,sign,returnNumber);

                   syso(returnNumber);

                end;

 

4>查询数据库的对象的三中方式 

      select count(*) from user_procedures;--当前用户的存储过程

      select count(*) from all_procedures; --相同权限的用户所有的存储过程 权限下有多少存储过程就输出多少 不会有编译出错

       select count(*) from dba_procedures; --系统所有的存储 如果没有dba的权限会编译出错

 

5>删除存储过程 

       drop procedure 存储过程名称  

二：函数过程的定义   

      CREATE [OR REPLACE] FUNCTION 函数名

       [(参数名 [IN|OUT|IN OUT] 数据类型[, …])]

        RETURN 返回值类型

       {IS | AS}

        BEGIN

            函数的主体

       END [函数名];

          函数和存储过程的区别在于

 

         1  函数可以返回值  存储过程不行

         2  函数可以在sql中使用 存储过程不行

         3  函数是一种特殊的存储过程

        例子  

 

 

         create or replace function  fun_arthirmbyDeclare(p1 in number,p2 in number,sign in varchar2)

            return number

                    as

            resultDNum number;

                    begin

                      if sign='+' then

                         resultDNum:=(p1+p2);

                      elsif(sign='-' ) then

                         resultDNum:=(p1-p2);

                      elsif(sign='*' ) then

                         resultDNum:=(p1*p2);

                      elsif(sign='/' ) then

                         resultDNum:=(p1/p2);

                      end if;

              return resultDNum;

                    end;

 

            --调用函数    

           declare p1 number:=1;

                    p2 number:=2;

                    sign varchar2(3):='+';

                    returnNumber number;

                    begin

                         returnNumber:=fun_arthirmbyDeclare(p1,p2,sign);

                       syso(returnNumber);

                    end;

            --删除函数：

           DROP FUNCTION 函数名;

 

         区分存储过程和函数在user_procedures

       select object_name,object_type from user_objects where object_name in(select object_name from user_procedures)         

 

 

六.触发器的定义        

     CREATE [OR REPLACE] TRIGGER 触发器名

       [BEFORE | AFTER] 激活触发器的事件(insert,update,delete)

        ON 表名

       [FOR EACH ROW]  -- 指定为行级触发器

       [WHEN 触发条件]

        BEGIN

            主体;

        END [触发器名];

        /

       注意：

   多种激活触发器用or来连接：insert or update or delete

    在触发器主体语句中可以用“inserting”、“updating”、“deleting”判断激活事件。

   在行级触发器中，可以通过:old和:new别名访问列的原值和新值。

 

    举例：

     create or replace trigger trg_grade_delete before

        delete on tb_grade

        FOR EACH ROW

        begin

           /**

              在dml操作中 数据的修改是存在新和旧的问题

             insert语句  只有新的数据 

             delete语句  只有旧的数据 

             update语句  有新和旧的问题

             oracle通过var变量的方式存储新旧值 

             :new 

              :old 只能使用在行级触发器上 

          **/

           syso('我删除了一行记录 班级名称是：'||:old.cname );

        end;    

       delete from tb_grade where cid=3;  

     

 演示在触发器中自定义异常以及修改列的值

        create table orders(

           id number primary key,

           sname varchar2(20),

           price number,

 

 

           total number,

           totalPrice number

        )

         --在before触发器中可以修改:new的值 after不行

        create or replace trigger trg_orders before

                insert on orders

                FOR EACH ROW

            declare

            rollbackException exception;  --自定义异常

               begin

               --判断价格小于0  不满足 应该回滚

              if :new.price<0 then

                   raise rollbackException;

               end if;

               --总价=单价*数量

              :new.totalPrice:=:new.price*:new.total;

               /**

               exception when rollbackException then

                  syso('判断价格小于0');

               **/

            end;

 

七.游标定义

  5步骤 经过5个步骤的显示游标 没有5个步骤 隐示游标

>1 定义获取数据的变量

>2 声明游标,并指定查询

>3打开游标

>4抓取数据

>5关闭游标

 

begin

   for i in (select empno,ename from emp) loop--隐示游标

     dbms_output.put_line(i.ename);

     end loop;

end;

 

--1 定义获取数据的变量

declare rowobj emp%rowtype;

--2 声明游标,并指定查询

cursor mycursor is select * from emp;--普通游标

begin

  --3打开游标

  open mycursor;

  --4抓取数据

  loop

    fetch mycursor into rowobj;

    exit when mycursor%notfound;

    dbms_output.put_line(rowobj.ename||rowobj.sal);

  end loop;

  --5关闭游标

  close mycursor;

end;

 

 

--动态游标

enameVar = null

enameVar ='SMITH'

if(enameVar is null)

sql='select * from emp';

else

  sql='select * from emp where ename=enameVar';

  

  

  

--1定义获取数据的变量

declare rowobj emp%rowtype;

enameVar varchar2(20):='SMITH';

sqlVar varchar2(2000):='select * from emp ';

--2 声明动态游标,并指定查询

mycursor sys_refcursor;

begin

  if(enameVar is not null) then

  sqlVar:=sqlVar || 'where ename='||enameVar||'''';

  end if;

  dbms_output.put_line(sqlVar);

  if(not mycursor%isopen) then

  dbms_output.put_line('未打开游标');

  end if;

  --3 打开游标

  open mycursor for sqlVar;

  --4 抓取数据

  loop

    fetch mycursor into rowobj;

    exit when mycursor%notfound;--抓取之后,才能判断是否拥有数据

    dbms_output.put_line(rowobj.ename||rowobj.sal);

   end loop;

   --5关闭游标

   close mycursor;

end;

select * from empwhere ename='SMITH'

 

 

                       

 

Oracle笔记

一. ORACLE安装目录结构与卸载

1.开发工具集

10G 【sqlplus (

dos命令:

sqlplus /nolog,

conn /as sysdba

),

       isqlplus(

         http://localhost:5560/isqlplus

        )

        em (

            http://localhost:1158/em

        )

】

11G 【

0G除isqlplus，

sql developer(java编写)

】

 非官方：PLSQL Developer

2. 配置和管理工具

   DBCA 【用来配置和更新数据库】

           NETCA【用来配置网络监听和连接描述符】

          连接描述符位于：%ORALE_HOME%/NETWORL/ADMIN/tnsnames.ora

3. ORACLE文件目录结构 

   安装目录 :F:\oracle

          产品目录:%安装目录%/product/10.1.0

    ORACLE_HOME:%产品目录%/Db_索引编号

           数据文件目录:%产品目录%/oradata/SID名称/

                 1 DBF数据文件  

 

 

                 2 CTL控制文件 【引导文件】

                 3 LOG重做日志文件  【恢复文件】

          连接描述符：%ORALE_HOME%/NETWORL/ADMIN/tnsnames.ora

          监听配置文件【端口】：%ORALE_HOME%/NETWORL/ADMIN/listener.ora

          端口查询文件：%ORALE_HOME%/install/portlist.ini

4.数据管理方式的发展 

   1手工管理阶段

       数据不被保存，还没有文件的概念。一组数据与一个程序直接对应

   2文件管理阶段（ROM）

       数据以文件形式存放。一个应用对应一组数据，应用之间不能共享数据。

   3数据管理阶段（RAM）

       多用户、多应用要共享数据。需要专门的数据管理系统。

5. ORACLE的体系结构 

    ORACLE SERVER

                ---INSTANCE

                   ---内存结构

                         ---SGA（系统全局区，共享池（sql语句），java池（java程序），数据缓冲区（未保存的数据），日志缓冲区）

                        ---PGA（程序全局区 连接的用户占用的内存）

                    ---进程

                         ---后台进程（用于操作数据 PMON,SMON,DBWR,LGWR具体参考《ORACLE体系结构》

                        ---用户进程 （用户连接用户）

                 ---DATABASE

                    1 DBF数据文件  

                     2 CTL控制文件 【引导文件】

                     3 LOG重做日志文件  【恢复文件】

                     4  密码文件(口令文件)，初始化文件（参数文件），dump文件（DBA操作）

6. sql语言操作分类： 

   1 数据定义语言DDL  （定义，操作数据的结构） 【-->java的变量定义】

 

       CREATE : 在数据库中创建新的数据对象 

       ALTER : 修改数据库中对象的数据结构 

       DROP : 删除数据库中的对象 

       DISABLE/ENABLE TRIGGER : 修改触发器的状态 

       UPDATE STATISTIC : 更新表/视图统计信息

       TRUNCATE TABLE : 清空表中数据

       COMMENT : 给数据对象添加注释

       RENAME : 更改数据对象名称

     2数据操作语言DML

       DML(Data Manipulation Language)(CRUD)，用于添加/修改/查询数据库中数据。

       DML包含以下语句：

       INSERT ：将数据插入到表或视图

       DELETE ：从表或视图删除数据

       SELECT：从表或视图中获取数据

       UPDATE ：更新表或视图中的数据

       MERGE ： 对数据进行合并操作(插入/更新/删除)

 

    3数据控制语言DCL

        DCL(Data Control Language)用来向用户赋予/取消对数据对象的控制权限。

       DCL包含以下语句：

       GRANT : 赋予用户某种控制权限

       REVOKE ：取消用户某种控制权限

   4. 事务控制语言(TCL)

        TCL(Transaction Control Language)用来对事务进行管理。

       TCL包含以下语句：

       COMMIT : 保存已完成事务动作结果

       SAVEPOINT : 保存事务相关数据和状态用以可能的回滚操作

       ROLLBACK : 恢复事务相关数据至上一次COMMIT操作之后

       SET TRANSACTION : 设置事务选项

7. 卸载或者重装数据库 

  卸载：

     1停止所有以oracle开始的服务，找到 UI（Universal Installer） 打开卸载界面 点击卸载产品按钮  选择所有的组件 点击删除按钮

 

     2删除服务 使用命令  sc delete 服务名称 删除所有的服务

     3删除注册表 开始->运行->输入 regedit命令打开注册表 找到 HKLM/SofeWare下的oracle 全部删除

     4找到oracle的安装目录 删除 如果无法删除使用软件强制删除

 重装：

     1 如果服务能够启动（ORCL和Listener结尾） 首先sqlplus /nolog 和conn /as sysdba连接  出现字符 历程启动  需要使用命令 startup

     2 服务不能启动  通过杀毒软件 去恢复oracle被删除的部分

     3 如果是Listener结尾启动不了使用netca删掉前面创建的监听 去重新创建一个

     4 如果是ORCL结尾启动不了 使用dbca 删除掉前面创建的orcl的数据库  在重新创建 一个新的

     5 dbca和netca无法打开 点击oracle安装文件点击setup。exe重装数据库

     6 setup.exe无法启动或者多次安装都失败，重装系统  

二 ORACLE管理命令

1 启动sqlplus工具 （ORACLE dba管理数据库的工具） 

           sqlplus /nolog  (无授权信息登录)

              conn /as sysdba(使用本地系统登录 本地用于加入了ORA_DBA组)

           sqlplus 用户名/密码@连接描述符

           sqlplus 用户名/密码  （默认本地ORCL）

2 oracle退出命令 

           exit(quit)  直接退出到dos命令

           disconnect(disconn|disco) 退出连接 在sql命令中

           orapwd（orapwd file=<fname> password=<password>） 重置密码文件

           password(passw) 修改密码 （必须登录之后）

3 操作上一次执行的sql (sql缓冲区中 缓冲区中只能存储一条sql) 

           list(l) 显示上一次缓冲区中sql

           run(r|/)执行上一条缓冲区中的sql

           clear buffer(cl buff)清空缓冲区中的sql

           get sql文件的路径  将文件的内容读取到缓冲区 可以使用 list或者run去查看或者运行

          save(sav) sql文件的路径 将缓冲区的sql写入到文件中

           start(sta|@) sql文件的路径 将文件的内容读取到缓冲区后 并执行 (get sql路径;run)

           edit(ed) 使用ed 文件路径修改文件的内容 或者使用ed命令修改缓冲区中的内容

           clear scr（ dos清屏cls） 在sql命令下清除屏幕上的文字

           spool 文件路径   记录当前用户的所有操作以及输出   spool off（终止）

4 启动和关闭 

        启动数据库orcl服务：

        sqlplus /nolog,

        conn /as sysdba

        startup

         nomount  数据库的instance已经打开  数据库没有打开

    mount数据的控制文件已经打开 并且和instance连接 但是不能远程连接

       open 数据库已经打开并且可以远程连接

       关闭数据库

       shutdown 启动或者关闭监听服务

       dos命令下  lsnrctl start|stop 检查连接描述符是否能连接  tnsping 描述名称

5. 连接描述符(客户端文件）【重点重点重点】

    oracle如果需要连接数据库 必须要确定三个元素

      ip地址 确定到 机器

      端口  确定机器上的某个机器（oracle的进程）（oracle默认的端口 1521）

      sid   确定oracle进程中的某个数据

    如果需要连接到任意一台机器的数据库 必须要配置这个三个元素

    这三个元素统称为 连接描述符  它的文件位于

      %ORACLE_HOME%/network/admin/tnsnames.ora

     该文件 中 #开头表示注释

   #以下 表示一个完整的连接描述符配置  别名可以任意

   #以下 指定了 ip 端口  sid

   # 原理 就是 Socket socket=new Socket("192.168.11.44",1521)

  clkdb =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.44)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

   )

6. 服务器监听文件(服务器端)【重点重点重点】

      %ORACLE_HOME%/network/admin/listener.ora

 

  在这个文件中 SID_LIST_LISTENER 必须添加

  (SID_DESC =

      (SID_NAME = ORCL)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (GLBAL_DBNAME = ORCL)

    )

  完整的配置如下

   SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

    (SID_DESC =

      (SID_NAME = ORCL)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (GLBAL_DBNAME = ORCL)

    )

  )

 

  如果需要其他客户端连接 需要将localhost修改为本机ipd

  LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.10)(PORT = 1521))

    )

  )

  远程 连接的测试语句

  create table aa (id number);

  select * from  tab where tname='AA';

 

7. 窗口

   dos命令窗口  只能执行dos的命令

   sqlplus连接 sql命令窗口

      sqlplus /nolog  未登录 进入sql命令下

   在sql命令下

      host dos命令

      clear scr 清除屏幕（host cls）

      conn 用户名/密码@连接描述符  切换数据库登陆

      exit|quit 退出sql命令到dos命令下

      discon  断开连接 不退出到dos命令

      password 修改密码 必须要登陆

      show user 显示当前用户

      SET PAGESIZE 100  --表述输出多少行 后重新显示表头

      SET LINESIZE 120 --表示 每一行现实的字符数

       desc 对象名（一般对象就是表） 显示表的结构

 

 

8. oracle的启动和关闭

    OracleServiceORCL  【oracle的主服务 必须要 启动该服务器 数据库才能启动】

    OracleOraDb11g_home1TNSListener 【监听服务 用于等待其他的客户端连接】

    关闭主服务

    1>net stop OracleServiceORCL  

    2>   sqlplus / as sysdba

       shutdown

    开启主服务

    1>net start OracleServiceORCL  

    2> sqlplus / as sysdba

       startup

    关闭监听服务

    lsnrctl stop

    启动监听服务

    lsnrctl start

 

 

9. 缓冲区命令

   （缓冲区就是上一次执行的sql语句的缓存）

    list|l命令 显示上一次执行的sql语句

    run|r|/ 执行上一次缓冲区中的sql语句

    clear buffer 清空缓冲区

    save 文件名 将缓冲区的sql写入文件

    get 文件名 将文件中的内容写入缓冲区

start 文件名 将文件中的内容写入缓冲区 并执行（get  + run）   

 

三. ORACLE数据类型

1. 字符串类型：用单引号引起来的字符序列。 

 

           CHAR(length)：固定长度字符串，不足自动以空格补齐长度，最多2000个字节。

 

           如：CHAR(10)  使用length(列) 获取的是定义的长度（length）

 

           VARCHAR2(length)：可变长度字符串，最多4000个字节。 【常用的字符串类型】

           如：VARCHAR2(10)  使用length(列) 获取的是实际数据的长度

           举例  title char(10) length(title)永远都是10

                content varchar2(10) 插入数据 'test' length(content)是实际数据的长度 4

2.数值类型： 

           NUMBER[(precision, scale)]：数值型，可以存储整数、浮点数。最高精度38位。如果没有指定最大位数和精度，就存储38位精度的数字。

           NUMBER(24)  最多24位 整数类型；

           NUMBER(5,3)  总和最多5位，其中小数最大是3位 整数位最大 5-3

           NUMBER  最大默认38位  整数和小数位总和不能超过38位         

3.日期类型

           DATE：存储日期和时间，精确到秒。时间值 【重要的类型】

               默认存放格式：“DD-MON-YYYY”

               默认显示格式：“DD-MON-YY

            java常用日期格式 yyyy-MM-dd hh:mm:ss

            orace转换日期格式  yyyy-MM-dd hh:mi:ss

            select sysdate from dual --输出的是date类型

            select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from  dual  --转换成了char类型

            TIMESTAMP[(seconds_precision)]：存储日期、时间和时区信息，带小数位的秒。时间戳

               如：TIMESTAMP(3)   秒后面小数点为3位。（最多可9位）

           大对象(Large objects)类型：最大存储128TB

                CLOB：Character LOB，用于存储字符数据。

               BLOB：Binary LOB，用于存储二进制数据。如图形、视频剪辑和声音文件。

               BFILE：用于存储二进制文件指针。

4. sql语法注意点 

       关键字不区分大小写 但是【字符串区分大小写】。

       表名和列名不区分大小写。

       语句以分号;结束

       单行注释使用--  多行注释 /**/

 

 

5.解锁scott账号 

    conn /as sysdba --以dba的账号登录

    alter user scott account unlock --解锁scott账号 

    conn scott/tiger  --密码是tiger     

6.显示表结构 

    dos命令下 可以使用 以下命令查看表结构

    desc 表名称  可以列出表结构 

    select * from tab; 查询当前用户下的所有的表   ，视图，同义词

7.常用的sql语句 

   select用于crud【create read update delete】 查询 是用于查询和筛选数据 【重中之重】

   使用java去理解 sql语句

   /*

    List<Emp> list=new ArrayList<Emp>();

    //select * from emp;

    syso("EMPNO ENAME JOB MGR....");

    for(Emp emp:list){

          syso(emp.empno+" "+emp.ename+" "+emp.job......);  

    }

    select ename as 雇员名称,sal as 薪水 from emp;

    syso("雇员名称 薪水....");

     for(Emp emp:list){

          syso(emp.ename+" "+emp.job);  

    }

   */

 

   select ename as 雇员名称,sal as 薪水 from emp;

   select语法结构   SELECT 【列名, 列名2…】| * FROM 表名 where 条件 ;

   【在oracle中字符串 使用''而不是""】

   查看当前方案中的表和其它数据库对象： 

    SELECT * FROM tab;

   查询指定表的所有列数据：

   SELECT * FROM 表名;

   查询指定表的指定列数据：

   SELECT 列名, 列名2… FROM 表名;

   可以在SELECT语句中使用算术运算符：+、-、*、/

   为查询的列取别名：

   SELECT 列名 [AS] 别名, … FROM 表名;

    使用||做连接。 

    举例： select '钱：'||'$'||sal as sal from emp

   使用DISTINCT消除重复内容。 

    举例：select distinct sal as sal from emp

   条件过滤（where）语句： 

       SELECT [DISTINCT] * | [列名 [别名],…]

        FROM 表名

       WHERE 条件;

        WHERE子句中的条件表达式：

       可以包括运算符(算术、比较、逻辑)

        可以使用()。

       可以使用常量、列、函数。

8.oracle常用的运算符 

    算术运算符：+、-、*、/

    连接操作符：将多个字符串或数据值合并成一个字符串 ||

    比较运算符： =、!=（或<>）、<、>、<=、>=

    ANY(值1,值2,值3…)    与列表中的任意一个值进行比较

    ALL(值1,值2,值3…)    与列表中的所有值进行比较

    in(值1,值2,值3)  列的值包含在所给的值中

 

举例：

--any表示等于其中的任意一个值  一般常用是使用in

       select * from emp where job=any('SALESMAN','MANAGER')

       select * from emp where job in('SALESMAN','MANAGER')

   --all 表示和所有值进行 比较 一般用于等于

       select * from emp where job!=all('SALESMAN','MANAGER')

       select * from emp where job not in('SALESMAN','MANAGER')

 

 

       逻辑运算符： 

       AND、OR、NOT

         举例：

             --等价于java &&

select * from emp where sal>2000 and job='MANAGER'

     --等价于java ||

select * from emp where sal>2000 or job='MANAGER'

     --等价于java  !

select * from emp where not sal>2000

        SQL 运算符的优先级从高到低的顺序是：

       算术 、连接、比较、逻辑（NOT、AND、OR）  

      模糊匹配 like

 _代表一个字母  %代表0个或者多个字母

        select * from emp where job like '_A%';  

9.大对象类型(large obects) 最大存储128T

>1.CLOB: CHARACTER LOB 用于存储字符数据

>2.BLOB: BINARY LOB 用于存储二进制数据(图形,视频,声音文件)

>3.BFILE: 用于存储二进制文件指针

四.oracle表的查询 

 1.表的排序

      排序语句：

       SELECT [DISTINCT] * | [列名 [别名],…]

         FROM 表名

        WHERE 条件

        ORDER BY 排序的列 [ASC | DESC],…;

 

       select * from emp order by hiredate desc   ( desc表示降序 从大到小 asc 表示升序)

 

       select * from emp where where job='MANAGER' order by  hiredate desc; (按条件查询出结果后排序)

 

       select * from mep order by hiredate desc,sal desc (按条件一升序后 相同的条件一在进行条件二排序)

 

2. Oracle的伪列 

 

      别名

       在select语句中 每一个被查询的表都有一个别名 默认就是它的表名 可以给表添加一个别名 添加的别名覆盖默认的别名

       select emp.ename from emp t; --错误

       select t.ename from  emp t;

       --如果多表查询时  每一个表都有相同的列名 这时必须要使用 别名

       select t.deptno from emp t,dept d;

 

 

      ROWID 是表中每一条记录的唯一标识符，数据库内部使用它来存储行的物理地址。

 

       该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的某一行。

 

       无法使用update语句修改

 

     ROWNUM 是SQL查询返回的结果集中每一行的行号。 【重点】

       可以用它来限制查询返回的行数。

       ROWNUM是先查到结果集之后再加上去的一个列 。

 

 

         在oracle中使用rownum来进行分页

         select t.* ,rownum as rn from emp where rownum>=10 and rownum<=20这是错误的

 

         因为行的循环查找 从索引1 开始  第一条记录的索引为1  不满足 循环第二条时 记录索引从1 开始 。。。。所以的行的行号都是1 永远不满足

           select * from (select t.*,rownum as rn from emp t) where rn>=10 and rn<=20 这是正确的  （一般使用） 

 

 

                  分页另一种写法

           select * from (select t.*,rownum as rn from emp t where rownum<=20) where rn>=10 正确的 

         select * from emp where rownum<=10 这是正确

        记录：rownum大于1的记录永远不可能成立  rownum<等于任何值都成立

 

 

五.oracle的函数

 

 

1.字符串函数 

      lower(n) 将字符转换成小写         对应java String.toLowerCase

         select lower('AAAA') from dual;  --aaaa

         select lower(ename) from emp;    --列输出结果全部小写

 

      upper(n) 将字符串转换成大写       对应java String.toUpperCase

 

      replace（列名,被替换的字符串，替换的字符串）    对应java String.replace或者 replaceAll

          select ename,replace(job,'MANAGER','经理')  from emp; --job列中 所有的MANAGER都被替换成了经理

          select replace('AAAtestgggg','test','测试') from dual; --AAA测试gggg

 

      instr(列名,被搜索的字符串) 

          select instr('AAAtestgggg','test') from dual; --4 索引从1开始

 

      substr(列名,开始位置,总长度) 

          select instr('AAAtestgggg',4,3) from dual; -- java substring  根据 被截取的字符串.substring(开始位置，结束的位置【不包括结束的位置】)  

  javascript 被截取的字符串.substr（开始位置，长度） 被截取的字符串.substring

                                                        oracle substr

 

      concat(参数1，参数2) 

          select concat('a','b') from dual;  --- ab  等价于 'a'||'b'    java "aaa"+"bbb"  

 

      length(列名) 获取字符的长度      java  String.getLength();

      trim(列名) 去空格  

      ltrim 去左侧的空格

      rtrim 去右侧的空格 

 

         nvl(列名,值) 当列的值为空时 输入第二参数的值 如果不为空 输出当前列的值   【重要】

    select nvl('','bac') from dual -- '' is null 所以输出 bac

 

 nvl2(列名,值1,值2) 当列的值为空时 输出第三个参数的值 如果不为空输出第二个参数的值 【重要】 

 

 

         decode(列名,条件1，值1，条件2,值2......,条件n,值n，默认值) 当列的值等于条件n时输出值n 【重要】

 select decode('2','0','男','1','女','人妖') from dual

2.数字函数            

      mod(5,2) 取余数 等价于java 5%2 

      round(n) 对整数位进行四舍五入 满5进1 

      round(n,p) 对小数位进行四舍五入 比如 round(5.6767,2) 输出为5.68 

      trunc(n) 截断小数位 只保留整数位 

     trunc(n,p) 截断小数位意外的位数 比如 trunc(5.666,2) 输出为5.66 就是保留p位小数

 

 

3.日期函数            

      sysdate 获取系统的当前时间  【重点】

 

      add_months(日期,月数) 将日期加上月数 并返回  比如 (sysdate=2016-3-3)+5个月 =2016-8-3

 

      select sysdate+天数 from dual 将当前日期加上某个数字 表示+天数

 

      months_between(日期1，日期2)  比较日期1和日期2的相差的月份数  结果=日期1-日期2

 

      last_day(日期) 返回日期对应月份的最后一天

 

      round(日期,格式)

            YEAR 获取当年的第一天 

            MONTH 获取当月的第一天 

            DAY 获取 第一个周末的第一天 

 

 

      next_day(日期，第几天) 获取当前日期下一个星期的第几天  

 

    转换函数 【每一个都是重点】

 

      to_date(日期字符串,日期格式)  字符串的格式必须由第二个参数来判断 

 

日期格式 ：

 

年 yyyy，月mm,日dd,小时（hh12小时制，hh24 24小时制） 分钟 mi 秒 ss

举例 :select to_date('2015/03/09','yyyy/mm/dd') from dual to_char(数字) 将数字转换成char类型 

 

to_char(日期,日期格式)  将日期转换成char类型  日期格式 可以任意 输出的内容 根据日期的格式而定

举例   select to_char(sysdate,'hh24')  输出24小时制的当前时间的hour

 

to_number(字符串数字) 将字符串的数字转换成number类型 

举例    to_number('123')输出数字 123  

4.常用的聚合函数

   什么是聚合 ：将多行记录压缩成1行或者多行进行演示

     1 count(*)  count(1) count(列名) 统计数据行的 个数 【重点】

     速度比较（oracle9i之前的说法）

       select count(*) --是最慢的  统计表的行数 先去数据库中了解表的结构

       select count(1) --第二慢的  找到数据库中表的一列

       select count(emp.empno) from emp; --第三慢的 因为已经指定了列名 不需要查表结构

       select count(rowid); --rowid直接定位的物理地址

     2 sum(数字的列) 统计当前列的总和

     3 avg（数字类型的列） 同行当前指定列的平均数

         select sum(sal)/count(rowid) from emp;

         select avg(sal) from emp;

     4 max(任意 列) 统计当前列最大的那个值

     5 min(任意列) 统计当前列种最小的那个值

 

6 wm_concat 合并结果集函数 

       将部门下的所有员工信息,列表显示

列如:研发部 张三/李四/王五

select d.dname,wm_concat(e.ename) from emp e inner join dept d on e.deptno=d.deptno group by d.deptno,d.dname;

7.exists函数

select * from emp e where exists (

select deptno from emp group by deptno having min(sal)=e.sal

)

5.正则表达式函数

>1.REGEXP_SUBSTR

     REGEXP_SUBSTR函数使用正则表达式来指定返回串的起点和终点

select regexp_substr('MY INFO: Anxpp,23，and boy','[[:digit:]]',1,2) from users;

select regexp_substr('MY INFO: Anxpp,22，and boy','my',1,1,'i') from users;

 

>2. REGEXP_INSTR

REGEXP_INSTR函数使用正则表达式返回搜索模式的起点和终点（整数）。如果没有发现匹配的值，将返回0。

select regexp_instr('MY INFO: Anxpp,23，and boy','[[:digit:]]') from users;

>3. REGEXP_LIKE

     通常使用REGEXP_LIKE进行模糊匹配。

select name from users where regexp_like(phone,'666');

>4.REPLACE和REGEXP_REPLACE

     REPLACE函数用于替换串中的某个值。

select replace('MY INFO: Anxpp,23，and boy','an') from users;

 

下面演示使用该函数计算某字串在源串中出现的次数：

select (length('MY INFO: Anxpp,23，and boy')-length(replace('MY INFO: Anxpp,23，and boy','an')))/length('an') from users;

 

REGEXP_REPLACE是REPLACE的增强版，支持正则表达式，扩展了一些功能。

select regexp_replace('电话：023  5868-8888 邮箱：anxppp@163.com',    '.*([[:digit:]]{3})([^[:digit:]]{0,2})([[:digit:]]{4})([^[:digit:]]{0,2})([[:digit:]]{4}).*',

     '(\1)\3\5'

) phone from users;

>5. REGEXP_COUNT

REGEXP_COUNT函数返回在源串中出现的模式的次数，作为对REGEXP_INSTR函数的补充。

虽然COUNT是一个集合函数，操作的是行组，但是REGEXP_COUNT是单行函数，分别计算每一行。

select regexp_count('MY INFO: Anxpp,23，and boy','an') from users;

六.高级查询

1.子查询【重点】

select * from emp where sal>2000 and job='MANAGER'

根据子查询的位置 可以分为 表子查询 列的子查询 条件的子查询  根据数据返回的行和列 分为 单行单列 多行多列多行单列

-- 1表子查询（虚表 内存表）   比条件和连接 更容易理解（多行多列子查询）

select * from (select * from emp where sal>200) where job='MANAGER'

 

--2 列子查询 每个子查询只能返回一行记录（单行单列子查询）

--查询部门名称

select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

select ename,(select dname from dept where rownum=e.empno) as dname from  emp e;

 

--3 条件子查询

--查询所有雇员为 ACCOUNTING的雇员

select deptno from  dept where dname='ACCOUNTING'

select * from  emp where deptno=10

--=只能等于一行  单行单列子查询

select * from  emp where deptno=(select deptno from  dept where dname='ACCOUNTING');

 

--查询所有雇员为 ACCOUNTING RESEARCH的雇员 (多行单列子查询)

select * from  emp where deptno=any(select deptno from  dept where dname='ACCOUNTING' or  dname='RESEARCH');

select * from  emp where deptno in(select deptno from  dept where dname='ACCOUNTING' or  dname='RESEARCH');

 

 

2.集合

{1，2，4}^{2,3,4}={2,4}

{1，2，4}U{2,3,4}={1，2，3,4}

{1，2，3}-{2，3，4}={1}

集合的操作

 

集合操作符：合并多个查询结果

UNION ALL：将多个查询结果合并到一个结果中，有重复行【重点】

UNION：将多个查询结果合并到一个结果中，没有重复行（并集）【重点】

INTERSECT：返回两个查询结果中共有的行 (交集)

MINUS：返回从第一个查询结果中减去第二个查询结果中相同的行之后剩余的行(差集)

 

 

3.复制 表

 create table dept1 as select * from dept

select * from  dept;

select * from dept1;

--交集

select * from dept intersect select * from  dept1;

--并集 union 去掉重复行

select * from dept union select * from  dept1;

--并集 uinon all 不会去重复行

select * from dept union all select * from  dept1;

--差集

select * from dept1 minus select * from  dept;

 

4. 分组

     1.什么是分组

      分组指定的条件  【相同的数据数据被分为一组】最终查询的结果

        就是分组的组数

      表名 Test

       A列  B列

       1  a

       1  b

       2  c

       2  d

       select A from  Test group by A

       --因为根据A这一列 相同的值被分为一组 最终被分为两组 只有两条被显示

         1

         2

       select A,B from Test group by A  --这是错误的

       两组都被压缩成一行

       1 组 存在a和b两个值  不能被压缩成 一行 只能使用聚合函数压缩成

             一行才能显示

 

     --查询部门中薪水最高的薪水

      select * from emp order by deptno;

     2.排序 是针对分组产生 之后的结果在进行排序

      select deptno,max(sal) from emp group by deptno order by deptno

 

     3.having子句 是对分组之后的结果进行筛选

      --查出部门最高的薪水>3000  

       select deptno,max(sal) from emp group by deptno having max(sal)>3000

 

 

5. 表连接【重点】

     单表查询 -》多表连接查询 

   笛卡儿积【交叉连接】{1,2,3}交叉{4,5} 可以参考【笛卡儿积.png】

     1     

     2      4

     3      5

     最终产生的结果  14 15 24 25 34 35   交叉产生的记录数就是 两个集合的个数的乘积

  数据库表的笛卡儿积

    数据行的笛卡尔积

   select * from table1，table2

  举例说明 92语法的内连，左外连 ，右外连

     用户表（UserInfo）

     用户id 用户名

       1     test

       2     user

     文章表 (Article)

       文章id 文章内容 用户id

        1       java      1

        2       oracle    3

 

    1  test   1  java  1

    1  test   2   oracle 3

    2  user   1   java  1

    2  user   2  oracle  3

    //86语法

    select * from userinfo u,arcticle a where u.用户id=a.用户id

    1  test   1  java  1

 

    //92语法

     内联必须都满足条件才显示记录

        select * from userinfo  inner join arcticle a on u.用户id=a.用户id  --是86语法的改进 结果和86语法是相同

     外联

         --左外连 以左边的表为主表 左表如果存在某条记录和右中所有的记录都无法关联 就保留一条记录

        select * from userinfo  left join arcticle a on u.用户id=a.用户id  

         1  test   1  java  1

         2  user   null null null

 

   建议

     多表查询需要先找到两张表的关系

     如果需要做多表连接查询尽量使用 内联 找到两张表的关系  直接将条件 在on后  (如果有特殊情况 如果主表中的记录没有连接也要输出 只能使用左外连接)

 

 七.sql语言操作分类【需要背 面试需要】：

 

1 数据定义语言DDL （DATA DEFINE LANAGUAGE）  （定义，操作数据的结构） 【-->java的变量定义】 

       CREATE : 在数据库中创建新的数据对象 

       ALTER : 修改数据库中对象的数据结构 

       DROP : 删除数据库中的对象 

       DISABLE/ENABLE TRIGGER : 修改触发器的状态 

       UPDATE STATISTIC : 更新表/视图统计信息 

       TRUNCATE TABLE : 清空表中数据 

       COMMENT : 给数据对象添加注释 

       RENAME : 更改数据对象名称 

2数据操作语言DML 【重点关注 工作必用】

       DML(Data Manipulation Language)(CRUD)，用于添加/修改/查询数据库中数据。

       DML包含以下语句：

       INSERT ：将数据插入到表或视图 

       DELETE ：从表或视图删除数据 

       select ：从表或视图中获取数据 

       UPDATE ：更新表或视图中的数据 

       MERGE ： 对数据进行合并操作(插入/更新/删除) 

3.数据控制语言DCL 

       DCL(Data Control Language)用来向用户赋予/取消对数据对象的控制权限。

 

       DCL包含以下语句：

       GRANT : 赋予用户某种控制权限

       REVOKE ：取消用户某种控制权限

4. 事务控制语言(TCL) 【重点】

 

       TCL(Transaction Control Language)用来对事务进行管理。

 

       TCL包含以下语句：

       COMMIT : 保存已完成事务动作结果 

       SAVEPOINT : 保存事务相关数据和状态用以可能的回滚操作 

       ROLLBACK : 恢复事务相关数据至上一次COMMIT操作之后 

       SET TRANSACTION : 设置事务选项 

 

5.crud （create read update,delete）【重点】

     所有的数据库  都是使用这四个单词来描述增删改查 isud  (insert select update delete)

     1 insert语句

       语法：

          INSERT INTO 表名[(列名1,列名2,…)] VALUES(值1, 值2,…);   --列名的个数和值的个数 必须相同              【表列较多 建议使用】

          insert into 表名  values(值1，值2 .。。)   --如果没有指定列名  值得个数必须和表中所有的列的个数相同 列的类型必须匹配   【表列较少建议使用】

          INSERT INTO 表名[(列名2，列名1,…)] VALUES(值2，值1, …); 值得顺序和列的顺序保持一致

          insert into student select '11','test1',25,'女'  from dual   --根据常量来模拟

          insert into student1 select * from student      --可以根据 insert into select语句来进行复制表的数据 （备份表）    seelct查询的列数必须和insert插入的表列数一致 并且列类型匹配

 

日期类型的插入

              insert into student1 values(7,'a',20,'男',sysdate)

   insert into student1 values(7,'a',20,'男',to_date('1987-06-18','yyyy-MM-dd'))

  

2 update语句（注意在修改之前一定先将条件写好）

       语法：

           UPDATE 表名 SET 列名=值[,列名2=值2,…] [WHERE 修改条件];  --

3 delete语句（注意在修改之前一定先将条件写好）

            delete scott.emp where empno='8110'

八.事务【重点】

     事务表示 一段sql的执行 要么同时成功 要么同时失败

任意的dml语句【数据的修改】 必须要提交数据（因为用户的误操作 导致数据的损坏 有可能导致无法挽回的灾难  可以使用 事务的机制来解决这类问题）

 

     commit（提交） 一旦提交了事务 数据就被修改到物理的数据文件中  （ctrl+s） 保存到文件

 

rollback(回滚) 如果除了误操作 需要回滚当前事务 （记事本ctrl+z）(撤回)

事务的acid属性

 

原子性(Atomic)：

指整个数据库事务是不可分割的工作单元。原子性确保在事务中的所有操作要么都发生，要么都不发生。

举例：

--被一起的执行的dml语句位于同一事务当中

insert into scott.emp values(8111,'test','MANAGER',7934,sysdate,3000,100,10);

insert into scott.emp values(8112,'test','MANAGER',7934,sysdate,3000,100,10);

insert into scott.emp values(8113,'test','MANAGER',7934,sysdate,3000,100,10);

rollback;

 

一致性(Consistency)：

一旦一个事务结束了(不管成功与否)，系统所处的状态和它的业务规则是一致的。即数据应当不会被破坏。

举例：

一旦数据被约束成某种 类型或者某些值那么就必须对应满足这些类型或者某些值

Insert into scott.emp values(8113,'test','MANAGER',7934,'2012-12-15',3000,100,10);

 

隔离性(Isolation)：

指多个事务同时操作同一数据时，每个事务都有各自的完整数据空间。未提交的数据  在其他的客户端中是无法看到因为因为隔离性

 

持久性(Durability)：一旦事务完成，事务的结果应该持久化 存到物理磁盘中（就是断电了下次还能 看的到 ）

 

事务的提交方式【了解即可】

默认数据库使用 手动（非自动）提交方式需要操作者 使用commit关键字来提交

 SQL> show autocommit;

 utocommit OFF

设置自动提交

SQL> set autocommit on;

SQL> insert into scott.emp values(8113,'test','MANAGER',7934,sysdate,3000,100,10);

已创建 1 行。

提交完成。

 

隐式的事务提交（不需要使用commit和rollback也会自动提交）

1 自动隐式提交事务：执行一个DDL语句、执行一个DCL语句、从SQL*Plus正常退出(exit,quit)

2自动隐式回滚事务：

强行退出SQL*Plus、客户端到服务器的连接异常中断、系统崩溃  

回滚点

savepoint a;

insert into scott.emp values(8113,'test','MANAGER',7934,sysdate,3000,100,10);

savepoint b;

insert into scott.emp values(8114,'test','MANAGER',7934,sysdate,3000,100,10);

rollback to a; --没有记录被插入

rollback to b  -- 8113被插入 8114被回滚

commit;

事务引发的问题和隔离级别【重点】

事务 对同一行数据进行操作时  必须等先操作的客户端提交或者回滚事务后 另外一个客户端才能操作数据（锁）

事务操作同一行数据出现的问题 （并发的引发的问题）

 

脏读  【是一种错误】

读取到了用户未提交的数据  oracle数据库永远不会出现脏读

 

不可重复读【只是一种设计问题】

在读取的事务中 两次重复读取同一行数据发现数据 被更改了

 

幻读【只是一种设计问题】

在读取的事务中 两次读取某一个条件的多条数据时发现多出了几条数据

  

隔离级别（就是为了解决事务操作同一份数据导致的问题）  

EAD UNCOMMITTED： 读未提交数据。脏读、不可重复读、幻读都可能发生。它的事务隔离性最低。

READ COMMITTED：读已提交数据。解决了脏读。【数据库的默认的隔离级别】

REPEATABLE READ：可重复读。解决不可重复读，脏读。

SERIALIZABLE：串行化。解决任何并发事务问题。最严格的事务

Oracle只支持READ COMMITTED和SERIALIZABLE。

默认为READ COMMITTED

 

设置隔离级别

alter session set isolation_level=serializable;--如果需要演示 必须每个客户端都要执行

  

九.用户管理 

 1.用户操作

    创建用户 create user jyb identified by jyb 【密码区分大小写】

    修改用户 alter user jyb identified by test

    锁定用户  alter user jyb account lock;

    解锁用户 alter user jyb account unlock;

    查询所有的用户       select * from all_users;

    删除用户  drop user jyb 【cascade代表所有关联的对象都被删除】;

2.角色操作 

  创建角色  create role stu;

  删除角色  drop role stu; 

 给角色添加功能权限  grant create session to stu

 给用户添加角色            grant stu to jyb     

 通过dd表可以查询所有的系统表：select * from dictionary where table_name like '%ROLE%'  

 查询所有的角色 select * from dba_roles;

 

3.权限操作 

  系统权限   （针对某些对象进行修改（DDL）的权限）

  grant 系统权限 to 用户|角色

  所有的系统权在 dba_sys_privs表中可以查询

  grant create user to jyb --jyb就能通通过  create user 创建用户

  回收系统权限

  revoke 系统权限 from 用户|角色

 

  对象权限 （针对某个对象（表）进行数据操作（DML）（crud）的权限）

  grant 操作（CRUD|RW） on 对象|all to 用户|角色

  grant insert on jyb.course to jyb  --jyb用户就拥有了访问course表的权限

  回收对象权限 

  revoke  操作（CRUD|RW） on 对象|all from 用户|角色

  角色 （将对象权限或者系统权限赋予给角色就是角色权限）

  系统默认都预留了一些角色 这些角色都拥有系统较高的权限 比如 DBA

4.备份和恢复 （dos备份）

    热备份

       (必须要先连接数据库后  备份数据后导出文件)

 

    冷备份（DBA）

      （不需要连接oracle数据库 直接备份文件）

 

    热备份举例：

    备份（DOS）：

       1》客户端备份 【文件被保存在当前的客户端】【一般使用客户端备份】

       exp 用户名/密码@连接描述符  file=保存的文件.dmp   【tables=(表1，表2....)】  owner=方案名

  

    2》服务器备份 【文件被保存在服务器上】

    被导出的用户必须要添加 grant create any directory to 用户名

    create directory 目录名称 as 'c:/test'

     expdp 用户名/密码@连接描述符 directory=目录名称 dumpfile=jybdp1.dmp  tables=(表1，表2....)

 

还原:

    客户端 还原

    imp 用户名/密码@连接描述符  file=保存的文件.dmp full=y ignore=y

 

    服务器 还原

impdp jyb/jyb@orcl directory=jybdir dumpfile=jybdp1.dmp  tables=(表1，表2....)  

 

十.表操作 

1.crud表的操作

  create user learn_object identified by test;

 

  --给予权限 

   grant dba to learn_object;

 

   -第一种 创建表 

    create table tb_userinfo(

    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

    username varchar2(20) not null, --not null 表示当前的列 不允许插入 null 和 ''

     sex number default 0   --默认值 default 值 当插入数据为空时 自动填上默认值 插入不为空的值 插入当前值

     )

 

     第二种复制表

     create table 表名称 as select * from 表 [where 1!=1]

 

     第三种类型赋值法

     create type  userobj as object (

      d number,

        uname varchar2(20),

        createDate date

     )

  

      create table userinfo_cpy_type of userobj

 

      --修改表名称 

      rename 旧表名to 新表名

 

       --修改列名 

      alter table userinfo rename column 旧列名 to 新列名

 

        --修改列类型 

       alter table userinfo modify username char(20)  

 

        --添加列 

       alter table userinfo add age number default 18

 

        --删除列

       alter table userinfo drop column age 

 

       --查询表的结构

       desc 表名  --命令下

       select * from all_tab_columns where table_name='USERINFO1'  --数组字典表（DD） 在sql语句中

 

 

 

2. 删除操作 

 

 

       删除表：把表中所有的行和表结构都删除。

   DROP TABLE 表名;--Oracle中删除表时并没有直接删除，只是放置到“回收站”

 

   显示回收站中的对象：SHOW RECYCLEBIN;

    恢复回收站中的表：FLASHBACK TABLE 表名 TO BEFORE DROP;

    删除回收站中的表：PURGE TABLE 表名;

    彻底删除 不进回收站：DROP TABLE 表名 PURGE;

 

    截断表：删除表中所有的数据行，重置表的存储空间。

       TRUNCATE TABLE 表名;

        delete from 表名

 

       区别 ：

          1  truncate是ddl语句  delete 是dml语句 都可以删除数据行

          2  truncate不需要提交事物 delete需要提交事物

          3  truncate删除表数据及表的存储 无法恢复 delete删除 可以使用归档日志恢复   

          4  delete可以删除指定条件的记录

          5  如果表结构损坏了 导致数据无法访问 必须重置表 使用truncate 因为delete 不会删除数据 只是隐藏数据 （查询的效率依然地下）

             如果需要根据条件删除 并且希望可以恢复 一般使用delete

 

3. 表分区 （了解）

           意义 ：可以容灾（出现事故后 还有一部分数据保留）

 

    范围(range(字段名))

 

            范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期

 

       格式:

 

            create table XXX() partition by range(表中数值类型的字段名)(partition 分区名 values less than 具体值  [tablespace 表空间名] ,…)

 

    列表(list(字段名))

            该分区的特点是某列的值只有几个

 

           …partition by list(字段名) (partition 分区名 values (值列表)值  [tablespace 表空间名] ,…))

 

    哈希(hash)

 

 

            分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。

 

 

           …partition by hash(表中数值类型的字段名)(partition 分区 [tablespace 表空间名] ,…)

 

 

            … partition by hash(表中数值类型的字段名)PARTITIONS n
STORE IN (s1,….sn)

 

 

    列表(list(字段名))

 

    复合分区(分区组合)  

 

 

 

十一. 约束 

 

      1》约束的查询方式

       --可以通过dd表来查询约束

       select * from all_constraints where table_name='USERINFO'

        约束的类型

         C (Check约束)

         P (主键约束)

         U （唯一约束）

         R (外键约束)

 

        --直接添加不为空的约束

 

       2》not null 约束

       create table tb_userinfo(

 

 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

 

 

                   username varchar2(20) not null, --not null 表示当前的列 不允许插入 null 和 ''

 

 

                )

        --constraints 约束名 可以指定约束名称 如果不指定约束名称 系统会自动分配一个名称  列存在了 添加约束 需要考虑 真实的数据行是否和约束冲突 因为要满足一致性

        alter table userinfo drop constraints nu_age

        alter table userinfo modify age number [constraints nu_age]    not null

 

        --直接添加不为空的约束 给约束添加别名

 

        3》主键约束

       create table tb_userinfo(

 

 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

 

 

                   username varchar2(20) [constraint nullable_username] not null, --添加别名后 会添加一个别名对应的约束

 

 

               )

        

        --主键 它的意义在于 添加一列 用于唯一标识当前行  uniqie约束 知识表示 当前的列不允许出现重复的的值

        --在技术上  主键唯一 不能为空  uniqie 唯一能为空

       alter table userinfo drop constraints SYS_C0011165

       alter table userinfo modify userid constraints pri_userid primary key

 

 

 

       4》check约束

 

        --通过check的方式 添加不为空的约束  check的语法要和where条件一致         

 

     

        create table tb_userinfo(

 

 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空）

                   username varchar2(20) [constraint check_username]  check(username is not null) --通过添加约束的方式 添加not null

        )     

 

        alter table userinfo modify sex VARCHAR2(20) constraints chk_sex check(sex in('女','男'))

        alter table userinfo modify age number constraints chk_age check(age between 1 and 100)

         

 

        --check约束必须满足where条件  不能使用子查询

       create table tb_userinfo(

               userid number primary key,

               username varchar2(20) not null,

               sex number constraint check_sex check(sex in(0,1))

        )    

 

        5》外键约束

 

       --定义外键  外键的定义 必须能唯一定位到外键对应的记录 比如 知道学生信息后 就唯一确定了他所在的班级

           --外键 如果某个表中存在一列 关联到另外一张的主键 这一列叫做外键列

           --如果某个列被添加了外键 外键列的值 必须是 引用表中主键的值 或者是null

 

       --班级表

 

 

       create table tb_grade(

           cid  number primary key,

           cname varchar2(20) not null

        )

        --cid number check(cid in(select cid from tb_grade)) 用于理解

 

       create table tb_student(

           sid number primary key,

           sname varchar2(20) not null,

           cid number [constraint FOR_STUDENT_CID] references TB_GRADE (CID)         

        )

 

 

        --外键的引用 必须通过alter table的方式来添加

 

 

       alter table tb_student add constraint FOR_STUDENT_CID foreign key (CID)

                      references TB_GRADE (CID)         

 

        --删除外键必须通过名称  未定义名称时 需要通过      dba_constraints表去查询         

        alter table tb_student drop constraint SYS_C008553    

 

 

        --查询表所有的外检 类型  C表示check约束 P代表主键  R代表外检  

        select * from dba_constraints  where table_name='TB_STUDENT'                    

 

十二.序列 

 

 

        --创建序列  minvalue表示范围中最小的那个值 maxvalue表示范围中最大的那个值

 

 

       --start with 表示序列从1开始递增  increment by表示步值（每次累加值）

       create sequence TB_GRADE_SEC

        minvalue 1

        maxvalue 999999999999999999999999999

        start with 1

        increment by 1

        cache 20;

 

        --查询当前值得下一个值

       select TB_GRADE_SEC.NEXTVAL from dual;

 

        --查询当前值

       select TB_GRADE_SEC.Currval from dual;

        select * from tb_grade;

 

        --在insert语句中使用序列  一般使用 序列来控制 主键的值

       insert into tb_grade values(TB_GRADE_SEC.NEXTVAL,'test');

       insert into tb_student values((select max(stuid)+1 from tb_student),'张三',1)

        commit;

               

        --删除序列

       drop sequence tb_grade_sec               

 

 

十三.索引 

        作为一个普通的开发人员 唯一提高性能工具就是索引【重点】

 

        索引候选列需要存储很大范围（重复的范围 每一个值都不一样 就是范围大）的值——“B-树”索引

 

        索引候选列只包含很少范围（比如列上的值 都是某几个枚举的值  男,女）的值——“位图”索引

 

 

        --在列上使用 unique，primary key可以自动添加btree索引

       create table TB_STUDENT

        (

          SID   NUMBER  primary key not null,

          SNAME VARCHAR2(20) unique,

          sex number check(sex in(0,1)),

          CID   NUMBER

        )

 

        --唯一索引（BTree索引） map.put(1,'test') 创建btree的语句 唯一索引 可以直接定位行 效率最高  

        create UNIQUE index index_student_sid on TB_STUDENT(sid)

        --normal索引（BTREE索引） 表示列允许出现重复 但是不能出现大范围的重复 否则效率降低

       create index index_student_sid on TB_STUDENT(sid)  

 

        --创建位图索引 大范围的重复时 使用索引

       create bitmap index bitmap_student_sex on TB_STUDENT(sex)

 

        --创建基于函数的索引

       create index upper_sname on tb_student(upper(sname))

 

        --删除索引

        drop index upper_sname                   

 

 

十四.视图 

    CREATE [OR REPLACE] VIEW 视图名  

          AS 查询语句 [WITH CHECK OPTION] [WITH READ ONLY];

        选项：

       OR REPLACE：视图存在时就替换

       WITH CHECK OPTION：视图的创建条件不能更改

       WITH READ ONLY：视图中的内容不能更改

       --创建视图 用于重复利用相同的sql 可以用于权限控制  可以隐藏机密的数据

       create or replace view vi_student_grade as

       select s.sname,g.cname from tb_student s inner join tb_grade g on s.cid=g.cid where cname='1501' WITH CHECK OPTION

 

        --查询 

       select * from vi_student_grade where cname='1501'

        create or replace view vi_student as select * from tb_student;

 

        --直接通过表更新，删除 

       update tb_student set sname='test' where sid=10;

 

        --单表视图 可以用于间接更新，删除 

      update vi_student set sname='test' where sid=10;

 

        删除视图 

       DROP VIEW 视图名;

 

 

 十五.同义词 

        --创建同义词

 

        CREATE SYNONYM syn_scott_emp FOR scott.emp; 

 

         select * from syn_scott_emp;     

         drop SYNONYM  syn_scott_emp  

                

plsql结构定义

 

    PL/SQL(Procedural Language/SQL)：过程化编程语言

   Oracle对标准SQL语言的过程化扩充

   用来编写包含SQL语句的程序，加入业务逻辑处理功能。

   PL/SQL程序由块组成，每一个块都包含有PL/SQL和SQL语句。块的结构如下：

       [

           declare 变量名  类型;--声明变量 

              变量名 类型;

        ]

        begin

          [过程语句]

          [exception when 异常类型 then 过程语句]

        end;

 

    举例：     

    /** 等价

     {

         System.out.println('helloworld')

      }

    **/

    begin   --等价于{

       dbms_output.put_line('helloworld');

    end;    --等价于}

 

    --带变量的plsql定义

   declare userid number;

    begin

      --变量的赋值 可以通过 变量名:=值

     userid:=1;

      dbms_output.put_line(userid);

    end;

一.异常捕获

>1异常捕获

declare

myVar number;

begin

  begin

    select sal into myVar from emp where ename='SMITH1';

    exception

    when NO_DATA_FOUND then

      dbms_output.put_line('没有数据找到');

  end;

  select sal into myVar from emp where ename='SMITH';

  myVar:=5/0;

  exception

     when ZERO_DIVIDE then

       dbms_output.put_line('除数为0');

     when others then

       dbms_output.put_line('未知的异常');

end;

--自定义异常

declare

myexe exception;

sex varchar2(3):='&请输入性别';

begin

  if(sex not in ('男','女')) then

  raise myexe;

  end if;

  exception

    when myexe then

      dbms_output.put_line('未知性别');

      when others then

        dbms_output.put_line('未知的异常');

end;

--例子

   declare userid number;

    begin

      --变量的赋值 可以通过 变量名:=值

     userid:=1/1;

      dbms_output.put_line(userid);

      --异常的定义  when 异常类型  then 异常处理的代码块

     exception when others then

         dbms_output.put_line('出现异常');

    end;

二.复合类型

   --在过程中调用函数和存储过程

declare curdate date:=sysdate;  

curDateStr varchar2(10);

begin

   curDateStr:=to_char(curdate,'yyyy-MM-dd');

   dbms_output.put_line(curDateStr);

end;

 

--在过程中定义复合类型

   1>数组类型 

   declare  

     type ArrayList is table of number index by binary_integer;

     ua ArrayList;

    begin

      ua(0):=12;

      ua(-1):=20;

      dbms_output.put_line(ua(0));

    end;

 

    2>定义复合类型的对象类型  使用对象的成员可以使用对象.属性名称访问

   declare  

     type userinfo is record(

        userid number,

        userName varchar2(20),

        sex number

     );

     jyb userinfo;

     begin

        jyb.userid:=1;

        jyb.userName:='蒋永兵';

        jyb.sex:=0;

        dbms_output.put_line(jyb.userName||'的用户id是:'||jyb.userid);

     end;     

三. 列类型和行类型

   --定义列类型 通过获取表的列类型给当前变量 

   declare sex_tmp tb_student.sex%type;

    begin

      sex_tmp:='1';

      syso(sex_tmp);

end;

 

    --行类型 通过select into语句抓取一行

   declare student_row tb_student%rowtype;

    sname varchar2(20);

    begin

       --select into用于在过程语句中将表中的数据抓取到变量中    

       --这里是抓取行

      select * into student_row from tb_student where sid=1;

       --抓取行中的某一列到变量

      select sname into sname from tb_student where sid=1;

       syso(student_row.sname);

       syso(sname);

    end;

四. 定义过程的逻辑控制语句  

1>if逻辑控制语句 

   declare sex number:=3;

    begin

       if (sex=0) then

          syso('男');

       elsif(sex=1) then

          syso('女');

       else

          syso('不男不女');

       end if;

    end;

2>循环

--loop循环 相对于java的do while循环 

   declare num number:=1;

    begin

     loop  

         syso(num);

         num:=num+1;

         exit when num=11;

      end loop;

    end;

--while循环 相对于java的while循环 

   declare num number:=1;

    begin

       while (num<=10)loop  

         syso(num);

         num:=num+1;

      end loop;

    end;

 

--for循环 相对于java的for循环 reverse表示反转输出 

   declare num number:=1;

    begin

       for i in  1..10 loop  

         syso(i);

      end loop;

    end;

 

 

--隐式游标方式的循环 用于循环迭代表记录 

 

   begin

       for stu_tmp in (select * from tb_student) loop

          syso(stu_tmp.sname);

       end loop;

end;  

 

五.存储过程的定义 

1>过程（多次编译多次执行）： 

 

       --过程实现计算器

       declare p1 number:=1;

        p2 number:=2;

        sign varchar2(3):='-';

        begin

          if sign='+' then

             syso(p1+p2);

          elsif(sign='-' ) then

             syso(p1-p2);

          elsif(sign='*' ) then

             syso(p1*p2);

          elsif(sign='/' ) then

             syso(p1/p2);

          end if;

        end;

2>存储过程（一次编译 多次执行） 

       --存储过程的定义   

        --存储过程执行只是编译的过程  如果需要执行存储过程的代码 需要在过程中调用

       create or replace procedure pro_arthirm(p1 number,p2 number,sign varchar2)  

        as

        --参数的定义

       begin

       --过程体

         if sign='+' then

             syso(p1+p2);

  elsif(sign='-' ) then

             syso(p1-p2)

          elsif(sign='*' ) then

             syso(p1*p2);

          elsif(sign='/' ) then

             syso(p1/p2);

          end if;

       end;

        --在plsql中调用存储过程

       declare p1 number:=1;

        p2 number:=2;

        sign varchar2(3):='+';

        begin

             pro_arthirm(p1,p2,sign);

        end;

        --在command模式下  需要使用   

        call 过程名称(参数。。。)

        execute（exec） 过程名称(参数。。。)

        show errors 显示存储过程编译之后的错误

 

3>存储过程参数 

       /**    参数类型：

           IN 输入参数。只能获取它的值 不能修改他的值 调用设置的值 可以在存储过程中查看

           OUT 输出参数。只能在过程体中赋值 不能查看到传入的值

           IN OUT 输入输出参数。可以取它的值，也可以给它赋值

            public int arthirm(int p1,int p2,String sign){

               int returnNum=5;

               if(...){

                  returnNum=p1+p2

               }

               return returnNum;

            }

            **/

            create or replace procedure pro_arthirmByReturn(p1 in number,p2 in number,sign in varchar2,returnNum in out number)  

                as

                --参数的定义

               rtnNum number;

                begin

                syso(returnNum);

                --过程体

                 if sign='+' then

                    returnNum:=(p1+p2);

                  elsif(sign='-' ) then

                     returnNum:=(p1-p2);

                  elsif(sign='*' ) then

                     returnNum:=(p1*p2);

                  elsif(sign='/' ) then

                     returnNum:=(p1/p2);

                  end if;

                end;

              declare p1 number:=1;

           p2 number:=2;

                sign varchar2(3):='+';

                returnNumber number:=10;

                begin

                     pro_arthirmByReturn(p1,p2,sign,returnNumber);

                   syso(returnNumber);

                end;

 

4>查询数据库的对象的三中方式 

      select count(*) from user_procedures;--当前用户的存储过程

      select count(*) from all_procedures; --相同权限的用户所有的存储过程 权限下有多少存储过程就输出多少 不会有编译出错

       select count(*) from dba_procedures; --系统所有的存储 如果没有dba的权限会编译出错

 

5>删除存储过程 

       drop procedure 存储过程名称  

二：函数过程的定义   

      CREATE [OR REPLACE] FUNCTION 函数名

       [(参数名 [IN|OUT|IN OUT] 数据类型[, …])]

        RETURN 返回值类型

       {IS | AS}

        BEGIN

            函数的主体

       END [函数名];

          函数和存储过程的区别在于

 

         1  函数可以返回值  存储过程不行

         2  函数可以在sql中使用 存储过程不行

         3  函数是一种特殊的存储过程

        例子  

 

 

         create or replace function  fun_arthirmbyDeclare(p1 in number,p2 in number,sign in varchar2)

            return number

                    as

            resultDNum number;

                    begin

                      if sign='+' then

                         resultDNum:=(p1+p2);

                      elsif(sign='-' ) then

                         resultDNum:=(p1-p2);

                      elsif(sign='*' ) then

                         resultDNum:=(p1*p2);

                      elsif(sign='/' ) then

                         resultDNum:=(p1/p2);

                      end if;

              return resultDNum;

                    end;

 

            --调用函数    

           declare p1 number:=1;

                    p2 number:=2;

                    sign varchar2(3):='+';

                    returnNumber number;

                    begin

                         returnNumber:=fun_arthirmbyDeclare(p1,p2,sign);

                       syso(returnNumber);

                    end;

            --删除函数：

           DROP FUNCTION 函数名;

 

         区分存储过程和函数在user_procedures

       select object_name,object_type from user_objects where object_name in(select object_name from user_procedures)         

 

 

六.触发器的定义        

     CREATE [OR REPLACE] TRIGGER 触发器名

       [BEFORE | AFTER] 激活触发器的事件(insert,update,delete)

        ON 表名

       [FOR EACH ROW]  -- 指定为行级触发器

       [WHEN 触发条件]

        BEGIN

            主体;

        END [触发器名];

        /

       注意：

   多种激活触发器用or来连接：insert or update or delete

    在触发器主体语句中可以用“inserting”、“updating”、“deleting”判断激活事件。

   在行级触发器中，可以通过:old和:new别名访问列的原值和新值。

 

    举例：

     create or replace trigger trg_grade_delete before

        delete on tb_grade

        FOR EACH ROW

        begin

           /**

              在dml操作中 数据的修改是存在新和旧的问题

             insert语句  只有新的数据 

             delete语句  只有旧的数据 

             update语句  有新和旧的问题

             oracle通过var变量的方式存储新旧值 

             :new 

              :old 只能使用在行级触发器上 

          **/

           syso('我删除了一行记录 班级名称是：'||:old.cname );

        end;    

       delete from tb_grade where cid=3;  

     

 演示在触发器中自定义异常以及修改列的值

        create table orders(

           id number primary key,

           sname varchar2(20),

           price number,

 

 

           total number,

           totalPrice number

        )

         --在before触发器中可以修改:new的值 after不行

        create or replace trigger trg_orders before

                insert on orders

                FOR EACH ROW

            declare

            rollbackException exception;  --自定义异常

               begin

               --判断价格小于0  不满足 应该回滚

              if :new.price<0 then

                   raise rollbackException;

               end if;

               --总价=单价*数量

              :new.totalPrice:=:new.price*:new.total;

               /**

               exception when rollbackException then

                  syso('判断价格小于0');

               **/

            end;

 

七.游标定义

  5步骤 经过5个步骤的显示游标 没有5个步骤 隐示游标

>1 定义获取数据的变量

>2 声明游标,并指定查询

>3打开游标

>4抓取数据

>5关闭游标

 

begin

   for i in (select empno,ename from emp) loop--隐示游标

     dbms_output.put_line(i.ename);

     end loop;

end;

 

--1 定义获取数据的变量

declare rowobj emp%rowtype;

--2 声明游标,并指定查询

cursor mycursor is select * from emp;--普通游标

begin

  --3打开游标

  open mycursor;

  --4抓取数据

  loop

    fetch mycursor into rowobj;

    exit when mycursor%notfound;

    dbms_output.put_line(rowobj.ename||rowobj.sal);

  end loop;

  --5关闭游标

  close mycursor;

end;

 

 

--动态游标

enameVar = null

enameVar ='SMITH'

if(enameVar is null)

sql='select * from emp';

else

  sql='select * from emp where ename=enameVar';

  

  

  

--1定义获取数据的变量

declare rowobj emp%rowtype;

enameVar varchar2(20):='SMITH';

sqlVar varchar2(2000):='select * from emp ';

--2 声明动态游标,并指定查询

mycursor sys_refcursor;

begin

  if(enameVar is not null) then

  sqlVar:=sqlVar || 'where ename='||enameVar||'''';

  end if;

  dbms_output.put_line(sqlVar);

  if(not mycursor%isopen) then

  dbms_output.put_line('未打开游标');

  end if;

  --3 打开游标

  open mycursor for sqlVar;

  --4 抓取数据

  loop

    fetch mycursor into rowobj;

    exit when mycursor%notfound;--抓取之后,才能判断是否拥有数据

    dbms_output.put_line(rowobj.ename||rowobj.sal);

   end loop;

   --5关闭游标

   close mycursor;

end;

select * from empwhere ename='SMITH'

 

 分页:

select t.* from (select g.* rownum rn from 表名 g) t where t.rn>=分页开始位置 and t.rn <分页结束位置