OCP

1.1 【学习目标】

1.2 【select 语句基本功能】

1.3 【select 语句基本语法】

1.4 【SQL 语句的书写规范】

1.5 【选择所有列】

1.5.1 【熟悉 scott 用户方案】

1.6 【选择特定列】

1.6.1 【伪列: rownum】

1.7 【描述表的结构】 describe

1.8 【列标题的缺省格式】

1.9 【格式化列的宽度】 column … format

1.10 【数学表达式在 select 中】

1.10.1【表达式的优先级】

1.10.2【圆括号改变优先级】

1.11 【空值的定义和处理】

1.11.1 【空值处理 nvl 函数】

1.12 【定义列别名 as、 空格、 双引号】

1.13 【字符串、 连接符||、 引用运算符 q】

1.13.1字符串

1.13.2连接符

1.13.3引用运算符

1.14 【消除重复行】(distinct)

1.14.1对多个列去重

1.14.2 distinct 的误用

1.15 【save 保存查询语句】

基本 SELECT 语句

1.1 【学习目标】

主要知识点:1.select 语句的基本功能2.数学表达式在 select 语句中的应用3.select 语句中 null 值的处理4.定义列的别名、 使用连接运算符5.在 select 语句中消除重复行

1.2 【select 语句基本功能】

select 语句可以从数据库表中检索信息

选择、 投影、 连接

选择: 查询符合条件的行, 过滤掉不符合条件的行投影: 查询符合条件的列, 不显示不符合条件的列连接: 从多个表中查询数据

连接是关系型数据库的核心, 把数据放在不同的表里, 通过关联获得完整信息。表是由行和列组成的, 列构成表的结构, 行组成表的数据。select 语句并不改变数据库中的数据, 只是将数据从数据库中提取显示出来。

1.3 【select 语句基本语法】

[] 代表可选; * 代表所有列语句: 一个语句的结束以; 为标志。子句: 一个语句可以由多个子句组成最基本的 select 语句包含两个子句: select 子句和 from 子句。

1.4 【SQL 语句的书写规范】

1.5 【选择所有列】

* 代表所有列写比较方便, 但是要注意:1.如果不需要查询所有列, 用会把所有数据都查出来, 增加 oracle 处理的代价, 性能就会降低。2.写*时, oracle 需要查询数据字典, 转换为具体列名称。3.sql 开发建议写具体字段名。

1.5.1 【熟悉 scott 用户方案】

用 scott 用户登录数据库user_tables 存储的是当前用户所有表的信息——数据字典表(视图)

SCOTT@orcl>select table_name from user_tables;

SCOTT@orcl>select * from emp;

SCOTT@orcl>select * from dept;

1.6 【选择特定列】

SCOTT@orcl>select empno,ename from emp;

1.6.1 【伪列: rownum】

select 子句中还可以选择伪列。伪列从功能上看像是表中的一列, 实际上并不存储在实际的表中。常见伪列: rownum,rowid(对象号,文件号,块号,行号),currval,nextval伪列上不能进行插入和删除操作。

官方文档: 伪列SQL Language Reference -> 2 Pseudocolumns

rownum 伪列反映的是每一行在当前查询的结果集中的位置

select rownum,deptno,dname,loc from dept;

1.7 【描述表的结构】 describe

不知道具体列名时, 可使用 describe 命令查看表结构。

SCOTT@orcl>desc dept

DESCRIBE :SQL*Plus 命令

官方文档: SQL*Plus 命令

SQLPlus User’s Guide and Reference -> 12 SQLPlus Command Reference

1.8 【列标题的缺省格式】

SCOTT@orcl>select * from emp;

ENAME 是字符类型, HIREDATE 是日期类型:左对齐;EMPNO、 MGR、 SAL 等是数值类型: 右对齐。数据是区分大小写的。

1.9 【格式化列的宽度】 column … format

a接数字表字符长度,9999是数字长度(0为自动补充)

SCOTT@orcl>column hiredate format a10

SCOTT@orcl>col mgr for 9999

SCOTT@orcl>select * from emp;

SCOTT@orcl>col sal for 0999

SCOTT@orcl>select * from emp;

【补充说明】如果列宽度设置不够, 会出现###。

1.10 【数学表达式在 select 中】

select 语句功能强大, 可以在 SELECT 语句中用数学表达式对列值进行计算

需求: 涨工资, 查询每个员工工资增加 300 以后的值

SCOTT@orcl>select ename,sal,sal+300 from emp;

1.10.1【表达式的优先级】


  • 1.10.2【圆括号改变优先级】
  • 需求: 查询员工全年的收入(年终奖 100/每月奖励 100)
  • SCOTT@orcl>select ename,sal,12sal+100,12(sal+100) from emp;

1.11 【空值的定义和处理】

需求: 查询员工工资+奖金总收入

SCOTT@orcl>select ename,sal,comm,sal+comm from emp;

1.11.1 【空值处理 nvl 函数】

  • nvl 函数, 如果是空值, 就转换为 0: nvl(comm,0)
  • SCOTT@orcl>select comm,nvl(comm,0) from emp;

  • SCOTT@orcl>select empno,ename,sal,comm,sal+nvl(comm,0) from emp;

  • SCOTT@orcl>select empno,ename,sal,comm,sal+comm from emp;

1.12 【定义列别名 as、 空格、 双引号】

需求: 查询员工编号、 姓名、 工资。

未使用别名时的显示:

SCOTT@orcl>select empno,ename,sal from emp;

【使用别名目的】 为了以友好的方式显示, 可以定义列的别名 。

SCOTT@orcl>select empno,ename,sal+nvl(comm,0) year from emp;

当别名中有特殊字符(如空格、 / 等)、 别名需要区分大小写时, 要用双引号。

【使用别名注意事项】列的别名不能在数学表达式中运算

1.13 【字符串、 连接符||、 引用运算符 q】

1.13.1字符串


  • SCOTT@orcl>select ‘ena’ from emp;

  • 1.13.2连接符

  • Oracle 使用单引号作为封装或包含其他字符的特殊符号。
  • 需求: SMITH is a CLERK
  • SCOTT@orcl>select ename,job,ename||’ is a '||job from emp;

  • 【特殊情况】需要显示单引号时, 通常在字符串中应该出现的单引号前面再加一个单引号进行转义。需求: SMITH’s job is CLERK
  • 【错误用法】
  • SCOTT@orcl>select ename ||’'s job is '|| job from emp;

  • 【正确用法】 其他逃逸符是不能用的(如escape)
  • SCOTT@orcl>select ename ||’’'s job is '|| job from emp;
  • 此处 ’ 符号为转移符 其他逃逸符是不能用的(不能用escape)

1.13.3引用运算符


  • SCOTT@orcl>select ename||q’<‘s job is >’||job from emp;
  • <可以用其它特殊符号代替

  • {} [] \ <> 等符号内部的字符作为普通字符正常显示。
  • 1.14 【消除重复行】(distinct)
  • 需求: 查询 emp 表中员工工作在哪几个部门
  • SCOTT@orcl>select distinct deptno from emp;
  • SCOTT@orcl>select distinct(deptno) from emp;

1.14.1对多个列去重

  • SCOTT@orcl>select distinct deptno,job from emp;

1.14.2 distinct 的误用

  • distinct 关键字前不能查询其他列;
  • SCOTT@orcl>select empno,distinct deptno from emp;

  • 一个 select 子句中不能出现多个 distinct
  • SCOTT@orcl>select distinct empno,distinct deptno from emp;

  • 1.15 【save 保存查询语句】
  • 执行查询语句
  • SCOTT@orcl>select distinct deptno,job from emp;

  • 查看历史命令
  • list或 者 l
  • SCOTT@orcl>list
  • SCOTT@orcl>1

  • 保存此 sql 语句到操作系统中 /home/oracle/p1_01.sql
  • SCOTT@orcl>save /home/oracle/p1_02.sql

  • 在 sqlplus 中查看保存在操作系统中文件
  • SCOTT@orcl>get /home/oracle/p1_02.sql
  • SCOTT@orcl>!cat /home/oracle/p1_02.sql

  • sqlplus 中执行操作系统中的 sql 脚本/home/oracle/p1_01.sql


  • 目录
  • 1.1 【where 子句基本语法】
  • 1.2 【字符串和日期】
  • 1.2.1 【数据库默认日期格式】
  • 1.2.2 【当前会话默认日期格式】
  • 1.2.3 【修改当前会话日期格式】
  • 1.3 【比较操作符】
  • 1.3.1 【空值的不等于比较】
  • 1.3.2 【where 子句中使用 rownum 伪列】
  • 1.3.3 【范围】 (between and)
  • 1.3.4 【列举 in】
  • 1.3.5 【模糊查询 like】
  • 1.3.5.1【escape 转义】
  • 1.3.6 【空值比较 is null is not null】
  • 1.4 【逻辑运算符】
  • 1.4.1 【AND】
  • 1.4.2 【OR】
  • 1.4.3 【NOT】
  • 1.5 【优先级规则】
  • 1.6 【order by 子句: 排序】
  • 1.7 【升序 asc】
  • 1.8 【降序 desc】
  • 1.9 【按照列的别名排序】
  • 1.10 【按照列序号排序:位置排序】
  • 1.11 【多个列排序:混合排序】
  • 1.12 【排序中的 null 值】
  • 1.13 【order by 的部分限制】
  • 过滤和排序数据
  • 【where 子句: 限制查询检索的行】
  • 只使用 SELECT 和 FROM 子句查询表, 会返回表中所保存数据的所有行。使用 DISTINCT 关键字, 可以排除重复值, 在某种程度上限制了结果行。但如果要从表中查询非常明确的信息, 需要使用 where 子句对返回的结果行进行限制。

1.1 【where 子句基本语法】

where 子句中的条件有许多形式, 只有表中符合这些条件的行才会返回。where 子句中不可以使用列的别名。如果条件列是数字型的, 可以直接写;如果条件列是字符型的, 需要使用单引号;如果条件列是日期型的, 要注意日期格式, 单引号。

需求: 查询 10 号部门的员工姓名

SCOTT@orcl>select deptno,ename from emp where deptno=10;

1.2 【字符串和日期】

1.字符串和日期要用单引号引起来。2.字符串大小写敏感。(区分大小写)

SCOTT@orcl>select * from emp where job=‘clerk’;

SCOTT@orcl>select * from emp where job=‘CLERK’;

3.日期值对于日期的格式敏感。缺省的日期值格式: DD-MON-RR需求: 查询入职日期是 1980-12-17 的员工信息

【补充知识】 转换函数 to_date (备注: hiredate 是 date 类型)

SCOTT@orcl>select * from emp where hiredate=‘17-DEC-80’;

SCOTT@orcl>select * from emp where hiredate=to_date(’

1980-12-17’,‘yyyy-mm-dd’);

1.2.1 【数据库默认日期格式】

注意用户为sysdba

SYS@orcl>select property_name,property_value

2 from database_properties

3 where property_name=‘NLS_DATE_FORMAT’;

1.2.2 【当前会话默认日期格式】

  • 注意用户为scott
  • SCOTT@orcl>select * from v$nls_parameters
  • 2 where parameter=‘NLS_DATE_FORMAT’;

  • 1.2.3 【修改当前会话日期格式】
  • SCOTT@orcl>alter session set nls_date_format =‘yyyy-mm-dd hh24:mi:ss’;
  • SCOTT@orcl>select sysdate from dual;

  • 退出 sqlplus 重新登录后恢复默认
  • 1.3 【比较操作符】

  • SCOTT@orcl>select * from emp where deptno = 10;
  • SCOTT@orcl>select * from emp where deptno <>10;
  • SCOTT@orcl>select * from emp where deptno!=10;



  • 1.3.1 【空值的不等于比较】
  • 【注意】 包含空值的列进行比较, 要对空值进行处理。否则进行比较 空值默认为无限大
  • 需求: 查询工资大于奖金的员工
  • SCOTT@orcl>select * from emp where sal>comm;

  • SCOTT@orcl>select * from emp where sal>nvl(comm,0);

1.3.2 【where 子句中使用 rownum 伪列】

  • 因为 ROWNUM 是对结果集加的一个伪列: 即先查到结果集之后再加上去的一个列(说明: 先要有结果集)。 通俗的说 rownum 是对符合条件结果的序列号。 它总是从 1 开始排起的。所以你选出的结果不可能没有 1, 而有其他大于 1 的值。where 子句中使用 rownum 伪列进行限定时, 使用<或<=, 使用>不返回行。
  • SCOTT@orcl>select rownum,deptno,dname from dept;
  • SCOTT@orcl>select rownum,deptno,dname from dept where rownum=2;
  • SCOTT@orcl>select rownum,deptno,dname from dept where rownum>2;
  • SCOTT@orcl>select rownum,deptno,dname from dept where rownum<=2;

  • rownum< 和 and 并用时, 是在另一个条件 and 基础上的 rownum<也就是说先执行 and 后面的条件, 返回满足条件的数据, 然后再进行 rownum<
  • SCOTT@orcl>select rownum,ename,deptno from emp;

  • SCOTT@orcl>select rownum,ename,deptno from emp where rownum<2;

  • SCOTT@orcl>select rownum,ename,deptno from emp where deptno=10;

  • SCOTT@orcl>select rownum,ename,deptno from emp where rownum<2
  • 2 and deptno=10;

1.3.3 【范围】 (between and)

  • 需求: 查询工资在 800~1000 范围内的员工。
  • SCOTT@orcl>select * from emp where sal between 800 and 1000;
  • SCOTT@orcl>select * from emp where sal between 1000 and 800;

  • 【注意】 between and 同时包括上下限边界, 小值写在前; 否则不返回结果。
  • between and 也可以处理字符、 日期类型。between ‘a’ and ‘c’ 时, 包含’c’ 不包含’ca’、 'cb’等等以 c 字母开头的。【注意】 日期格式, 否则报错
  • SCOTT@orcl>select * from emp where hiredate between ‘09-08-21’ and ‘01-02-14’;

  • SCOTT@orcl>select * from emp where hiredate between ‘09-JUN-81’ and ‘23-JAN-82’

  • SCOTT@orcl>select * from emp where ename between ‘A’ and ‘C’;

1.3.4 【列举 in】

  • 可以使用 IN 操作符来检查一个值是否在值的列表中。
  • SCOTT@orcl>select empno,ename,sal,mgr from
  • 2 emp where mgr in(7902,7566,7788);

  • SCOTT@orcl>select empno,ename,sal,mgr from emp
  • 2 where mgr=7902 or mgr=7566 or mgr=7788;

  • 下面查询使用 NOT IN 检索那些未被 IN 检索出来的行:
  • SCOTT@orcl>select * from emp where comm in(300,500);
  • SCOTT@orcl>select * from emp where comm not in (300,500);

  • 【注意】 如果列表中包含空值, 那么 NOT IN 返回 false。 下面这个查询可以说明这一点 , 它不返回任何行, 因为列表中包含空值:
  • SCOTT@orcl>select * from emp where comm not in (300,500,null);

  • 1.3.5 【模糊查询 like】

  • 需求: 查询姓名中包含 MI 的。
  • SCOTT@orcl>select * from emp where ename like ‘%MI%’;

  • 1.3.5.1【escape 转义】

  • 创建测试表、 修改数据
  • SCOTT@orcl>create table emp1 as select * from emp;
  • SCOTT@orcl>update emp1 set ename=‘OCM_DBA’ where empno=7788;
  • SCOTT@orcl>commit;
  • SCOTT@orcl>select * from emp1;

  • SCOTT@orcl>select * from emp1 where ename like ‘%_%’;

  • 【说明】_代表任一字符转义符(\ 、 $) 后的通配符失去通配功能, 变为普通字符
  • SCOTT@orcl>select * from emp1 where ename like ‘%_%’ escape ‘’;

  • SCOTT@orcl>select * from emp1 where ename like ‘%KaTeX parse error: Expected group after '_' at position 1: _̲%' escape '’;

1.3.6 【空值比较 is null is not null】


  • 空值和空值不能用= 来判断例如: 未知数 x 和未知数 y , 都是未知值, 不能用=
  • 需求: 查询奖金是空值(不是空值) 的员工信息
  • SCOTT@orcl>select * from emp where comm is null;

  • SCOTT@orcl>select * from emp where comm is not null;

1.4 【逻辑运算符】

实现多个条件的组合, 逻辑操作符根据逻辑条件来限定行

1.4.1 【AND】

需求: 查询工资大于等于 1100 并且工作是 CLERK 的员工信息

SCOTT@orcl>select * from emp

2 where sal >=1000 and job=‘CLERK’;

需求: 查询工资在 800~1000 范围内的员工。

SCOTT@orcl>select * from emp where sal>=800 and sal <=1000;

扩展

SCOTT@orcl>select * from emp where sal between 800 and 1000;

1.4.2 【OR】

需求: 查询工资大于 1100 或者工作是 CLERK 的员工信息

SCOTT@orcl>select * from emp where sal >=1100 or job=‘CLERK’;

需求:查询经理号是 7902 或者 7566 或者 7788 的员工

SCOTT@orcl>select empno,ename,sal,mgr from emp

2 where mgr=7902 or mgr=7566 or mgr=7788;

1.4.3 【NOT】

需求:查询工作不是 CLERK、 MANAGER、 ANALYST 的员工

SCOTT@orcl>select ename,job from emp

2 where job not in (‘CLERK’,‘MANAGER’,‘ANALYST’);

SCOTT@orcl>select ename,job from emp

2 where job!=‘CLERK’ and job!=‘MANAGER’ and job!=upper(‘analyst’);

1.5 【优先级规则】

需求:查询工作是 SALESMAN 或者 PRESIDENT 的员工中,工资大于 1500 的员工。

SCOTT@orcl>select ename,job,sal from emp

2 where job=‘SALESMAN’ or job =‘PRESIDENT’ and sal>1500;

结果中出现 sal 小于 1500 的员工,不符合要求。

SCOTT@orcl>select ename,job,sal from emp

2 where (job=‘SALESMAN’ or job=‘PRESIDENT’) and sal>1500;

SCOTT@orcl>select ename,job,sal from emp

2 where job in(‘SALESMAN’,‘PRESIDENT’) and sal>1500;

【order by 子句】

1.6 【order by 子句: 排序】

desc 降序, asc 升序。order by 列名, order by 别名, order by 列序号, order by 表达式

如果有 where 子句, order by 子句跟在 where 子句后面。order by 子句是 select 语句中最后一个子句。

1.7 【升序 asc】

默认是 asc 升序:数字升序:由低到高日期升序:由早到晚字符升序:按字母表顺序(区分大小写)

SCOTT@orcl>select * from emp order by sal;

1.8 【降序 desc】

SCOTT@orcl>select * from emp order by sal desc;

1.9 【按照列的别名排序】

SCOTT@orcl>select ename,sal salary from emp order by salary;

如果列别名用双引号定义, order by 排序时注意格式

SCOTT@orcl>select ename,sal “salary” from emp order by salary;

SCOTT@orcl>select ename,sal “salary” from emp order by “salary”;

1.10 【按照列序号排序:位置排序】

列序号:列在 select 子句中出现的位置

SCOTT@orcl>select empno,ename,deptno from emp order by 3,1;

1.11 【多个列排序:混合排序】

SCOTT@orcl>select * from emp order by deptno,sal desc;

1.12 【排序中的 null 值】

包含 null 值的列排序时, 默认把 null 值看做无限大:

在升序排序时, nulls last

在降序排序时, nulls first

SCOTT@orcl>select empno,ename,sal,comm from emp order by comm;

1.13 【order by 的部分限制】

1.order by 后面不能跟 LOB、 LONG、 LONG ROW 类型的列。2.select 语句中有 distinct 关键字, order by 后面的列 必须出现在 select 子句中。3.order by 根据字符排序时,区分大小写

目录

1.1 【函数】

1.1.1 单行函数

1.1.2 【dual 表】

1.2 【字符函数】

1.2.1 【大小写转换函数】

1.2.1.1【initcap】分隔符

1.2.2 【字符操作函数】

1.2.2.1【concat】 连接字符串、列、表达式

1.2.2.2【substr】截取子串

1.2.2.3【length】获取字符串长度(字符数)

1.2.2.4【instr】后面子串在前面字符串第一次出现的位置。

1.2.2.5【lpad,rpad】左补全,右不全。

1.2.2.6【trim】去掉前后空格,或者 从后面串 首尾去掉前面字符。

1.2.2.7【replace】替换

1.3 【数字函数】

1.3.1 【round】四舍五入

1.3.2 【trunc】截断 取整

1.3.3 【mod】取余数

1.4 【日期函数】

1.4.1 【日期格式】

1.4.1.1【数据库默认日期格式】

1.4.1.2【当前 session 日期格式】

1.4.1.3【sysdate】

1.4.1.4【设置会话时间格式】

1.4.1.5【对日期进行计算】

1.4.2 【months_between】

1.4.3 【add_months】 为日期增加月份

1.4.4 【next_day】 下一个

1.4.5 【last_day】某月最后一天 (当前时间)

1.4.6 【日期 round】对日期四舍五入

1.4.7 【日期 trunc】 对日期取整

1.5 【函数的嵌套】

使用单行函数

1.1 【函数】

单行:输入一行,返回一行。多行:输入多行,返回一行。

1.1.1 单行函数



  • 字符函数、数字函数、日期函数、转换函数
  • 1.1.2 【dual 表】DUAL 表是一个“伪表”(虚拟表),只包含一个列 DUMMY,数据类型为 VARCHAR2(1)。DUAL 表只包含一行,其 DUMMY 列的值为 X。DUAL 表是为了语法上的完整性。

1.2 【字符函数】

1.2.1 【大小写转换函数】

  • lower 小写, upper 大写, initcap 首字母大写。

  • SCOTT@orcl>select empno,ename,job from emp where ename=‘scott’;
  • SCOTT@orcl>select empno,ename,job from emp where ename=upper(‘scott’);

  • SCOTT@orcl>select ename,lower(ename),upper(ename),initcap(ename) from emp;

  • 1.2.1.1【initcap】分隔符
  • SCOTT@orcl>select initcap(‘beijing,enmoedu_ocp%ocm’) from dual;

  • 空格、下划线、 %、!、 $等都是有效的单词分隔符。(特殊符号 标点符号)

1.2.2 【字符操作函数】


  • 1.2.2.1【concat】 连接字符串、列、表达式
  • SCOTT@orcl>select concat(‘good’,‘dba’) from dual;

  • SCOTT@orcl>select ename||’ is '||job from emp;
  • SCOTT@orcl>select concat(concat(ename,’ is '),job) from emp;

  • 1.2.2.2【substr】截取子串
  • SCOTT@orcl>select substr(‘oracle’,1,3) from dual;
  • SCOTT@orcl>select substr(‘oracle’,-5) from dual;

  • 1.2.2.3【length】获取字符串长度(字符数)
  • OCP 考点:单行函数可能发生数据类型改变,此处length显示的number
  • SCOTT@orcl>select length(‘oracle’) from dual;

  • 1.2.2.4【instr】后面子串在前面字符串第一次出现的位置。
  • SCOTT@orcl>select instr(‘oracle’,‘a’) from dual;

  • SCOTT@orcl>select instr(‘oracle’,‘b’) from dual;

  • =0 表示没有。instr 经常用来做判断,判断一个字符串是否在另外一个字符串中。
  • 查询从第 3 个字符开始, *第 4 次出现的位置
  • 查询从倒数第 3 个字符开始向前, *第 2 次出现的位置
  • SCOTT@orcl>select instr(‘12345*’,’*’,3,4) from dual;
  • SCOTT@orcl>select instr(‘12345*’,’*’,-3,2) from dual;

  • 1.2.2.5【lpad,rpad】左补全,右不全。
  • SCOTT@orcl>select sal,lpad(sal,4,0),rpad(sal,4,0) from emp;

  • 1.2.2.6【trim】去掉前后空格,或者 从后面串 首尾去掉前面字符。
  • SCOTT@orcl>select trim(’ hello ') from dual;

  • SCOTT@orcl>select length(’ hello ‘),length(trim(’ hello ')) from dual;

  • SCOTT@orcl>select trim(’*’ from ‘oracle’) from dual;

  • SCOTT@orcl>select trim(’**’ from ‘oracle*’) from dual;
  • 要去除的串 trim_character 只能是单个字符。
  • 官方文档位置:SQL Language Reference -> 5 Functions -> CTRL+F 搜索 trim
  • leading /trailing/both 开头、结尾、两头 删除字符
  • SCOTT@orcl>select trim(leading ‘*’ from ‘oracle’) from dual;
  • SCOTT@orcl>select trim(trailing ‘*’ from ‘oracle’) from dual;
  • SCOTT@orcl>select trim(both ‘*’ from ‘oracle’) from dual;

  • 1.2.2.7【replace】替换
  • SCOTT@orcl>select replace(‘oracle’,‘ora’,‘ORA-’) from dual;
  • SCOTT@orcl>select replace(hiredate,’-’) from emp;

1.3 【数字函数】

round 四舍五入, trunc 截取, mod 取模 取余数。

1.3.1 【round】四舍五入

  • SCOTT@orcl>select round(45.923,2),round(45.923,0),round(49.923,-1) from dual;

1.3.2 【trunc】截断 取整

  • SCOTT@orcl>select trunc(45.923,2),trunc(45.923),trunc(45.923,-1) from dual;

  • 1.3.3 【mod】取余数
  • SCOTT@orcl>select mod(1600,300) from dual;

  • SCOTT@orcl>select 1600/300 from dual;
  • SCOTT@orcl>select trunc(1600/300,0) from dual;
  • SCOTT@orcl>select trunc(1600/300,0),mod(1600,300) from dual;

  • 商: quotient余数: remainder

1.4 【日期函数】

类型: DATE还有一种类型,时间戳,精度更高。

1.4.1 【日期格式】


  • 1.4.1.1【数据库默认日期格式】
  • 注意用户
  • SYS@orcl>select property_name,property_value from database_properties
  • 2 where property_name=‘NLS_DATE_FORMAT’;

  • 1.4.1.2【当前 session 日期格式】
  • SYS@orcl>select * from v$nls_parameters
  • 2 where parameter=‘NLS_DATE_FORMAT’;

  • 1.4.1.3【sysdate】
  • 返回 Oracle 数据库服务器所在操作系统的当前日期和时间。
  • 时间信息包含在 SYSDATE 中,默认不显示。可以通过 TO_CHAR 转换函数提取时间信息或者通过改变 NLS_DATE_FORMAT 会话参数来更改设置。
  • SYS@orcl>select sysdate from dual;

  • 1.4.1.4【设置会话时间格式】
  • SYS@orcl>alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss day’;
  • SYS@orcl>select sysdate from dual;

  • 1.4.1.5【对日期进行计算】

  • SYS@orcl>select sysdate,sysdate+1,sysdate+1/24,sysdate+1/24/60 from dual;

1.4.2 【months_between】

  • SCOTT@orcl>select empno,ename,sal,months_between(sysdate,hiredate) months from emp;

1.4.3 【add_months】 为日期增加月份

  • SCOTT@orcl>select sysdate,add_months(sysdate,1),add_months(sysdate,12),
  • 2 add_months(sysdate,-12) from dual;

1.4.4 【next_day】 下一个

  • SCOTT@orcl>select next_day(sysdate,‘MONDAY’) from dual;

  • SCOTT@orcl>select next_day(sysdate,2) from dual;#下周第二天(西方以周日为第一人)

  • SCOTT@orcl>select next_day(sysdate,‘FRIDAY’) from dual;

1.4.5 【last_day】某月最后一天 (当前时间)

  • SCOTT@orcl>select sysdate,last_day(sysdate) from dual;

1.4.6 【日期 round】对日期四舍五入

  • SCOTT@orcl>select sysdate,round(sysdate),sysdate,round(sysdate,‘mi’),
  • 2 round(sysdate,‘DD’) from dual;

1.4.7 【日期 trunc】 对日期取整

  • SCOTT@orcl>select sysdate,trunc(sysdate),trunc(sysdate,‘dd’),trunc(sysdate,‘mm’),
  • 2 trunc(sysdate,‘yyyy’) from dual;

1.5 【函数的嵌套】

SCOTT@orcl>select ename,concat(lower(substr(ename,1,3)),’_US’) from emp

2 where deptno=10;

目录

1.1 【转换函数】

1.1.1 【隐式数据类型转换:后台转换】

1.1.2 【显示数据类型转换】

1.1.3 【to_char】

1.1.3.1带有日期的 TO_CHAR

1.1.3.2带有数字的 TO_CHAR

1.1.4 【to_number】 字符串转换为数字

1.1.5 【to_date】 字符串转换为日期

1.2 【通用函数】

1.2.1 【NVL 函数】

1.2.2 【NVL2 函数】

1.2.3 【nullif】

1.3 【条件表达式】

1.3.1 【decode】

1.3.2 【case when then else end】

1.3.2.1【case 第二种形式】

转换函数和条件表达式

1.1 【转换函数】

1.1.1 【隐式数据类型转换:后台转换】

COTT@orcl>select * from emp where empno=‘7788’;

隐式转换: 数据类型与默认格式匹配

1.1.2 【显示数据类型转换】

1.1.3 【to_char】

  • 1.1.3.1带有日期的 TO_CHAR

  • SCOTT@orcl>select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss am’) from dual;
  • SCOTT@orcl>select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss AM’) from dual;
  • SCOTT@orcl>select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss PM’) from dual;

  • SCOTT@orcl>select to_char(sysdate,‘Day,“the” ddth “of” Month,yyyy’)from dual;

  • 双引号:格式中字符串不是格式掩码时
  • SCOTT@orcl>select to_char(sysdate,‘FMDAY,“the” ddth “of” Month,yyyy’)from dual;

  • FM :去除开头和结尾的空格(显示紧凑) 都可以用
  • 格式模型:日期格式掩码

  • 官方文档位置:SQL Language Reference -> 5 Functions -> CTRL+F 搜索 to_char ->TO_CHAR (datetime) -> Format Models -> Table 3-15 Datetime Format Elements
  • 1.1.3.2带有数字的 TO_CHAR

  • SCOTT@orcl>select sal,to_char(sal,’$99,999.00’) from emp;

  • 注意位数,如果不够长,显示#乱码。
  • SCOTT@orcl>select sal,to_char(sal,’$999’) from emp;

  • 1.1.4 【to_number】 字符串转换为数字

  • SCOTT@orcl>select to_number(’$12,345.00’,’$99,999.00’) from dual;

1.1.5 【to_date】 字符串转换为日期

  • SCOTT@orcl>select * from emp where hiredate>=‘01-MAY-87’;

  • SCOTT@orcl>select * from emp where hiredate>=‘1987-05-01’;
  • SCOTT@orcl>select * from emp where hiredate>=to_date(‘1987-05-01’,‘yyyy-mm-dd’);

1.2 【通用函数】

1.2.1 【NVL 函数】


  • SCOTT@orcl>select ename,sal*12+nvl(comm,0) from emp;

  • SCOTT@orcl>select ename,mgr,nvl(mgr,‘No Manager’) from emp;

  • SCOTT@orcl>select ename,mgr,nvl(to_char(mgr),‘No Manger’) from emp;

  • 1.2.2 【NVL2 函数】
  • NVL2(expr1,expr2,expr3)如果参数 1 非空 not null,则返回参数 2 的值,否则返回参数 3 的值。
  • SCOTT@orcl>select ename,sal,comm,sal+nvl(comm,0),nvl2(comm,sal+comm,sal) from emp;

  • 1.2.3 【nullif】
  • nullif(expr1,expr2)比较两个表达式是否相同,如果相同,返回 null,如果不同,返回 expr1。
  • SCOTT@orcl>select nullif(10,10) from dual;

  • SCOTT@orcl>select nullif(10,9) from dual;

  • SCOTT@orcl>select nullif(‘a’,‘b’) from dual;

  • SCOTT@orcl>select ename,job,length(ename) n1,length(job) n2,nullif(length(ename),leng
  • th(job)) from emp;

  • 1.3 【条件表达式】

  • 1.3.1 【decode】

  • 需求:工作是 ANALYST 的,工资涨 10%;工作是 CLERK 的,工资涨 15%;工作是 MANAGER 的,工资涨 20%;其他的涨 25%。
  • SCOTT@orcl>select empno,ename,job,sal,
  • 2 decode(job,‘ANALYST’, sal*1.1,
  • 3 ‘CLERK’,sal*1.15,
  • 4 ‘MANAGER’,sal*1.20,
  • 5 sal*1.25)
  • 6 new_sal
  • 7 from emp order by job;

  • decode 处理空值
  • SCOTT@orcl>select comm,decode(comm,null,0,comm) from emp;

  • 1.3.2 【case when then else end】

  • SCOTT@orcl>select empno,ename,sal,job,
  • 2 case job when ‘ANALYST’ then sal*1.1
  • 3 when ‘CLERK’ then sal*1.15
  • 4 when ‘MANAGER’ then sal*1.2
  • 5 else sal*1.25
  • 6 end new_sal
  • 7 from emp order by job;

  • 1.3.2.1【case 第二种形式】
  • SCOTT@orcl>select empno,ename,sal,job,
  • 2 case when job=‘ANALYST’ then sal*1.1
  • 3 when job=‘CLERK’ then sal*1.15
  • 4 when job=‘MANAGER’ then sal*1.2
  • 5 else sal*1.25
  • 6 end new_sal
  • 7 from emp order by job;

  • 目录
  • 1.1 【常用分组函数类型】
  • 1.2 【分组函数基本语法】
  • 1.3 【使用 avg、 max、 min、 sum 函数】
  • 1.4 【使用 COUNT 函数】
  • 1.4.1 count(distinct)
  • 1.5 【分组函数和空值】
  • 1.6 【group by 子句基本语法】
  • 1.7 【使用 group by 子句】
  • 1.8 【根据多个列进行分组】
  • 1.9 【分组函数的误用】
  • 1.10 【having 排除聚组结果】
  • 1.11 【分组函数的嵌套使用】
  • 【总结】
  • 使用分组函数汇总数据

1.1 【常用分组函数类型】

1.2 【分组函数基本语法】

1.3 【使用 avg、 max、 min、 sum 函数】

需求:查询 emp 表销售人员工资的平均值、最大值、最小值、工资总和

SCOTT@orcl>select avg(sal),max(sal),min(sal),sum(sal) from emp;

日期类型、字符类型列不能使用 avg、 sum 函数,可以使用 max、 min 函数

1.4 【使用 COUNT 函数】

需求: 查询 30 号部门的总人数

SCOTT@orcl>select * from emp where deptno=30;

SCOTT@orcl>select count(*) from emp where deptno=30;

SCOTT@orcl>select count(empno) from emp where deptno=30;

SCOTT@orcl>select count(comm) from emp where deptno=30;

【说明】count(comm): 不统计含有空值的行;COUNT(*)返回表中行的总数, 包括重复行与数据列中含有空值的行, 而其他分组函数的统计都不包括空值的行。

1.4.1 count(distinct)


  • SCOTT@orcl>select distinct mgr from emp;
  • SCOTT@orcl>select count(distinct mgr) from emp;

  • 1.5 【分组函数和空值】

  • SCOTT@orcl>select count(comm) from emp;

  • 需求:查询所有 14 名员工的平均奖金
  • SCOTT@orcl>select avg(comm) from emp;

  • SCOTT@orcl>select avg(nvl(comm,0)) from emp;

  • 【group by 创建聚组数据】

  • 计算每个部门的平均工资,需要指定分组的列。

1.6 【group by 子句基本语法】

1.7 【使用 group by 子句】

SCOTT@orcl>select avg(sal) from emp;

SCOTT@orcl>select deptno,avg(sal) from emp group by deptno;

SCOTT@orcl>select deptno,avg(sal) from emp;

select 语句中,没有使用分组函数的列必须在 group by 子句中

SCOTT@orcl>select avg(sal) from emp group by deptno;

group by 后面的列可以不出现在 select 语句中

SCOTT@orcl>select deptno dnum,avg(sal) from emp group by dnum;

group by 后面不允许使用列的别名

1.8 【根据多个列进行分组】

需求:查询每个部门中,每种工作的工资总和。

SCOTT@orcl>select deptno,job,sum(sal) from emp

2 group by deptno,job order by deptno;

1.9 【分组函数的误用】

SCOTT@orcl>select deptno,count(ename) from emp;

select 语句中有分组函数,没有在分组函数中出现的列必须在 group by 子句中

SCOTT@orcl>select deptno,avg(sal) from emp

2 where avg(sal) >2000

3 group by deptno;

不能在 where 子句中对聚组列作出限定,应使用 having 子句

1.10 【having 排除聚组结果】

SCOTT@orcl>select deptno,avg(sal) from emp

2 having avg(sal)>2000

3 group by deptno;

需求:查询部门最高工资大于 2900 的部门

SCOTT@orcl>select deptno,max(sal) from emp

2 group by deptno

3 having max(sal)>2900;

SCOTT@orcl>select deptno,max(sal) from emp

2 having max(sal)>2900

3 group by deptno;

having 子句可以放在 group by 子句前面。

1.11 【分组函数的嵌套使用】

SCOTT@orcl>select max(avg(sal)) from emp group by deptno;

分组函数最多嵌套两层(两个函数),嵌套时必须有 group by

SCOTT@orcl>select count(max(avg(sal))) from emp group by deptno;

嵌套三层失败

【总结】

分组函数: avg/max/min/sum/count分组函数(聚合函数)要处理空值;select 子句中没有被分组函数包括的列,必须出现在 group by 后面;group by 子句中的列可以不出现在 select 语句中;group by 子句中不能使用列的别名;如果对分组函数做限制,不能用 where,需要用 having,having 子句可以写在 group by 子句前;分组函数最多嵌套两层(两个函数)。

目录

1.1 【表连接】

1.2 【笛卡尔积:交叉连接】

1.3 【使用表的别名】

1.4 【连接的类型】

1.5 【等连接】

1.5.1 自然连接

1.6 【非等连接】

1.7 【外连接】

1.7.1 【环境准备】

1.7.2 【左外连接】

1.7.3 【右外连接】

1.7.4 【全外连接】

1.8 【自连接】

1.9 【多表关联】

【总结】

从多个表中获取数据

1.1 【表连接】

需求:查询员工的姓名、部门号、部门名称。

SCOTT@orcl>select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept

2 where emp.deptno=dept.deptno;

注意:同一列名多表使用时,要写上表名做前缀。

SCOTT@orcl>seelct ename,deptno,dname from emp,dept where emp.deptno=dept.deptno

列定义模糊

1.2 【笛卡尔积:交叉连接】

SCOTT@orcl>select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept;

表连接不要忘记写 where 条件。

1.3 【使用表的别名】

SCOTT@orcl>select e.ename,e.deptno,d.deptno,d.dname from emp e,dept d

2 where e.deptno=d.deptno;

1.4 【连接的类型】

分类标准不同

1.5 【等连接】

传统 Oracle 连接语法:

SCOTT@orcl>select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept

2 where emp.deptno=dept.deptno;

SQL1999 语法(标准 SQL)

SCOTT@orcl>select emp.ename,emp.deptno,dept.deptno,dept.dname

2 from emp inner join dept

3 on ( emp.deptno=dept.deptno);

SCOTT@orcl>select emp.ename,emp.deptno,dept.deptno,dept,dname

2 from emp inner join dept

3 using(deptno);

连接列名称相同时,可以使用 using,但列名前不能加修饰

SCOTT@orcl>select emp.ename,deptno,dept.dname

2 from emp inner join dept

3 using(deptno);

1.5.1 自然连接


  • SCOTT@orcl>select emp.ename,deptno,dept.dname
  • 2 from emp natural join dept;

1.6 【非等连接】

需求:查询员工的工资以及对应的工资等级

SCOTT@orcl>select e.empno,e.ename,e.sal,s.losal,s.grade

2 from emp e,salgrade s

3 where e.sal>=s.losal and e.sal<=s.hisal;

SCOTT@orcl>select e.empno,e.ename,e.sal,s.losal,s.hisal,s.grade

2 from emp e,salgrade s

3 where e.sal between s.losal and s.hisal;

SCOTT@orcl>select e.ename,e.sal,s.losal,s.hisal,s.grade from emp e join salgrade s

2 on (e.sal between s.losal and s.hisal)

3 where deptno in(10,20);

1.7 【外连接】

1.7.1 【环境准备】

1.7.2 【左外连接】

  • 需求:列出所有员工及其对应部门信息, 包括没有部门的员工。
  • 传统 Oracle 连接语法:
  • SCOTT@orcl>select e.empno,e.ename,e.sal,d.deptno,d.dname
  • 2 from emp e,dept d
  • 3 where e.deptno=d.deptno(+);

  • SQL:1999 语法:
  • SCOTT@orcl>select e.empno,e.ename,e.sal,d.deptno,d.dname
  • 2 from emp e left outer join dept d on(e.deptno=d.deptno);

1.7.3 【右外连接】

  • 需求:列出所有部门及其对应的员工信息, 包括没有员工的部门。
  • 传统 Oracle 连接语法
  • SCOTT@orcl>select e.empno,e.ename,e.sal,d.deptno,d.dname from emp e,dept d
  • 2 where e.deptno(+)=d.deptno;

  • SQL:1999 语法:
  • SCOTT@orcl>select e.empno,e.ename,e.sal,d.deptno,d.dname
  • 2 from emp e right outer join dept d on(e.deptno=d.deptno);

  • 1.7.4 【全外连接】
  • 需求:查询员工对应的部门信息, 包括没有部门的员工和没有员工的部门
  • SCOTT@orcl>select e.empno,e.ename,e.sal,d.deptno,d.dname
  • 2 from emp e full outer join dept d
  • 3 on (e.deptno=d.deptno);

1.8 【自连接】

(表里有从属关系)

需求:查询员工的姓名以及其经理的姓名

员工的经理号 = 经理的员工号

SCOTT@orcl>select e.empno,e.ename,e.mgr,m.empno,m.ename

2 from emp e,emp m

3 where e.mgr=m.empno;

SCOTT@orcl>select e.empno,e.ename,e.mgr,m.empno,m.ename

2 from emp e,emp m where e.mgr=m.empno(+);

1.9 【多表关联】

需求:查询员工的姓名、部门名称、工资等级

SCOTT@orcl>select e.ename,d.dname,s.grade

2 from emp e,dept d,salgrade s

3 where (e.sal between s.losal and s.hisal) and (e.deptno=d.deptno);

SCOTT@orcl>select e.ename,d.dname,s.grade

2 from emp e join dept d on (e.deptno=d.deptno)

3 join salgrade s on (e.sal between s.losal and s.hisal);

【总结】

等值连接、不等值连接、外连接、自连接笛卡尔积:忽略了连接条件左外连接:列出所有员工的部门信息,包括没有部门的员工。e.deptno=d.deptno(+); 传统 oracle 连接语法left outer join dept d on (e.deptno=d.deptno); SQL:1999 连接语法右外连接:列出所有部门的员工信息,包括没有员工的部门。e.deptno(+)=d.deptno;right outer join dept d on (e.deptno=d.deptno);全外连接full outer join dept d on (e.deptno=d.deptno);注意:同一个外连接查询需求,使用左外连接或者右外连接都可以实现!左外连接是把左边表里数据显示完整,包括不符合连接条件的行;右外连接是把右边表里数据显示完整,包括不符合连接条件的行。如何区分左右:传统 oracle 连接语法看 where 子句中比较符,标准 SQL 看 from 子句中 join。

【OCP】051-7 join using/join on051-121 表连接 join using / natural join

目录

1.1 【使用子查询解决问题】

1.2 【子查询的基本语法】

1.3 【WHERE 子句中使用子查询】

1.4 【子查询要点】

1.5 【子查询的类型】

1.6 【执行单行子查询】

1.7 【在子查询中使用分组函数】

1.8 【在 HAVING 子句中使用子查询】

1.9 【单行子查询返回多行结果】

1.10 【多行子查询】

1.10.1【多行子查询使用 in】

1.10.2【多行子查询使用 not in/空值处理 】

1.10.3【多行子查询使用 any】

1.10.4【多行子查询使用 ALL】

1.11 【多列子查询】

1.12 【在 FROM 子句中使用子查询】

1.13 【关联子查询】

1.13.1【select 语句中子查询】

1.13.2【exists】

1.14 【结果集分页查询】

1.14.1【创建测试表】

1.14.2【union 并集:排序、去重】

1.14.3【union all 合并两个查询的结果,不排序、不去重】

1.14.4【intersect 交集:排序、去重】

1.14.5【minus 差集:排序、去重】

1.14.6【注意事项】

【OCP】

使用子查询

1.1 【使用子查询解决问题】

1.2 【子查询的基本语法】

在主查询执行之前,子查询首先执行一次。子查询的结果要在主查询中使用。

1.3 【WHERE 子句中使用子查询】

需求:查询谁的工资比 JONES(7566)高

SCOTT@orcl>select ename,sal from emp

2 where sal>(select sal from emp where ename=‘JONES’);

1.4 【子查询要点】

1.5 【子查询的类型】

1.6 【执行单行子查询】

单行子查询使用单行比较操作符

需求:查询工作和 7369 相同、工资比 7876 高的员工。

SCOTT@orcl>select ename,sal,job from emp

2 where job=(select job from emp where empno=7369)

3 and sal>(select sal from emp where empno=7876);

子查询返回一行结果

1.7 【在子查询中使用分组函数】

需求:查询谁的工资最少

子查询:求工资最小值

SCOTT@orcl>select empno,ename,job,sal from emp

2 where sal =(select min(sal) from emp);

错误查法使用排序

Rownum是查询结果的编号不参与order by排序

SCOTT@orcl>select ename,job,rownum,sal from emp where rownum=1 order by sal ;

SCOTT@orcl>select ename,job,rownum,sal from emp order by sal;

1.8 【在 HAVING 子句中使用子查询】

需求:查询哪些部门最低工资 比 20 号部门最低工资 高

子查询: 20 号部门最低工资

SCOTT@orcl>select deptno,min(sal) from emp

2 group by deptno

3 having min(sal)>(select min(sal) from emp where deptno=20);

需求:查询哪一种职位的平均工资最高

SCOTT@orcl>select job,avg(sal) from emp group by job

2 having avg(sal)=(select max(avg(sal)) from emp group by job);

1.9 【单行子查询返回多行结果】

需求:查询是经理的员工

SCOTT@orcl>select empno,ename from emp where empnol=(select mgr from emp);

ORA-01427: single-row subquery returns more than one row单行子查询返回多行结果

1.10 【多行子查询】

多行子查询使用多行比较操作符

1.10.1【多行子查询使用 in】

  • 需求:查询是经理的员工
  • SCOTT@orcl>select empno,ename from emp where empno in(select mgr from emp);

1.10.2【多行子查询使用 not in/空值处理 】


  • 需求:查询不是经理的员工
  • SCOTT@orcl>select employee.ename from emp employee
  • 2 where employee.empno not in
  • 3 (select manager.mgr from emp manager);
  • SCOTT@orcl>select employee.ename from emp employee
  • 2 where employee.empno not in (select manager.mgr from emp manager where manager.mgr is not nu
  • ll);

  • not in 要注意处理空值!

1.10.3【多行子查询使用 any】

需求:查询工资比职位是 clerk 的员工中任意(某)一个低 的员工信息

SCOTT@orcl>select sal from emp where job =‘CLERK’;

SCOTT@orcl>select empno,ename,sal from emp

2 where sal<any(select sal from emp where job =‘CLERK’)

3 and job!=‘CLERK’;

< any 小于最大值> any 大于最小值=any in

1.10.4【多行子查询使用 ALL】

需求:查询工资比各部门平均工资都高的员工

select empno,job,sal from emp

2 where sal > all ( select avg(sal) from emp group by deptno);

SCOTT@orcl>select empno,job,sal from emp

2 where sal> (select max(avg(sal)) from emp group by deptno);

>all 大于最大值<all 小于最小值!=all not in

有空值不能用

1.11 【多列子查询】

多列子查询存在子查询返回多行的情况,要使用多行比较操作符

SCOTT@PROD> select empno,ename,sal,job from emp

2 where (job,sal) in(select job,sal from emp where deptno=10)

3 and empno<>7788;

1.12 【在 FROM 子句中使用子查询】

需求:查询工资比本部门平均工资高的员工

SCOTT@PROD> select e.ename,e.sal,e.deptno,s.salavg from emp e,

2 (select deptno,avg(sal) salavg from emp group by deptno) s

3 where e.deptno=s.deptno and e.sal>s.salavg;

关联条件 where 在主查询中

子查询执行一次,子查询的结果集作为一个表

1.13 【关联子查询】

需求:查询工资比本部门平均工资高的员工

SCOTT@PROD> select e.ename,e.sal,e.deptno from emp e

2 where e.sal>(select avg(s.sal) from emp s where e.deptno=s.deptno);

子查询:主查询中员工所在部门的平均工资。对于每一名员工,均进行一次子查询。

普通子查询: 在主查询执行之前,子查询首先执行一次。子查询的结果要在主查询中使用。关联子查询: 需要重复执行子查询。

1.13.1【select 语句中子查询】

  • SCOTT@PROD> select e.empno,e.ename,e.deptno,d.dname
  • 2 from emp e,dept d
  • 3 where e.deptno=d.deptno;

  • SCOTT@PROD> select e.empno,e.ename,e.deptno,
  • 2 (select d.dname from dept d where e.deptno=d.deptno)
  • 3 from emp e;

1.13.2【exists】

  • 子查询返回至少一行时,结果为真
  • 查询是经理的员工
  • SCOTT@PROD> select m.empno,m.ename from emp m
  • 2 where exists (select 1 from emp e where e.mgr=m.empno);

  • 查询不是经理的员工
  • SCOTT@PROD> select m.empno,m.ename from emp m
  • 2 where not exists (select 1 from emp e where e.mgr=m.empno);

1.14 【结果集分页查询】

要求列出 emp 表第 5-第 10 名员工(按 sal 大–小排序)的信息

使用 rownum 筛选时必须显示或隐示的包含第一行,否则不会返回任何行

SCOTT@PROD> select * from

2 (select t1.*,rownum rn from

3 (select * from emp order by sal desc) t1

4 where rownum<=10) where rn>=5;

SCOTT@PROD> select * from (select t1.*,rownum rn from

2 (select * from emp order by sal desc) t1)

3 where rn between 5 and 10;

可以在 select、 from、 where、 having 子句中使用子查询

子查询要放在括号里面

子查询做好不要包含 order by

单行子查询使用单行比较操作符,多行子查询使用多行比较操作符(in,any,all)。

any: 小于最大,大于最小

all: 大于最大,小于最小

in 等价于 = any

not in 等价于 != all (注意处理空值,存在空值时 FALSE)

【集合运算符】

union、 intersect、 minus

1.14.1【创建测试表】

  • SCOTT@PROD> create table test1 as select * from emp where deptno in(10,20);
  • SCOTT@PROD> create table test2 as select * from emp where deptno in(20,30);

1.14.2【union 并集:排序、去重】

  • SCOTT@PROD> select empno,ename,deptno from test1
  • 2 union
  • 3 select empno,ename,deptno from test2;

  • 传统连接语法使用 union 实现全外连接
  • SCOTT@PROD> select e.ename,d.deptno,d.dname from emp e,dept d
  • 2 where e.deptno=d.deptno(+)
  • 3 union
  • 4 select e.ename,d.deptno,d.dname from emp e,dept d where e.deptno(+)=d.deptno;

1.14.3【union all 合并两个查询的结果,不排序、不去重】

  • SCOTT@PROD> select empno,ename,deptno from test1
  • 2 union all
  • 3 select empno,ename,deptno from test2;

1.14.4【intersect 交集:排序、去重】

  • SCOTT@PROD> select empno,ename,deptno from test1
  • 2 intersect
  • 3 select empno,ename,deptno from test2;

1.14.5【minus 差集:排序、去重】

  • SCOTT@PROD> select empno,ename,deptno from test1
  • 2 minus
  • 3 select empno,ename,deptno from test2;

  • 1.14.6【注意事项】


  • 【OCP】051-9 集合运算符 intersect
  • 目录
  • 1.1 【替代变量】
  • 1.1.1 【带有一个&的替换变量】
  • 1.1.2 【替换变量中的字符串和日期值】
  • 1.1.3 【运行时指定列名、表达式、文本】
  • 1.1.4 【带有两个&的替换变量】
  • 1.2 【定义用户变量 define/accept】
  • 1.2.1 【DEFINE 和 UNDEFINE】
  • 1.2.2 【ACCEPT 命令】
  • 1.2.2.1【accept + hide 输入密码】
  • 【set 定制 SQL*Plus 环境】
  • 1.3 【SET 设置命令变量】
  • 1.3.1 *【verify on 显示替换前后命令】
  • 1.3.2 *【linesize】 行的长度
  • 1.3.3 *【pagesize】 页面的尺寸
  • 1.3.4 *【time】 系统当前时间
  • 1.3.5 *【timing】 SQL 执行时间
  • 1.3.6 【set echo on】 执行 sql 脚本时 显示脚本内容
  • 1.3.7 【feedback on】显示查询结果行数
  • 1.3.8 【heading on】 显示头信息、列名
  • 1.3.9 【set colsep ‘|’ 】 设置列分隔符
  • 1.3.10【termout on】 显示脚本执行结果
  • 1.3.11【arraysize/long】
  • 1.4 【在 glogin.sql 文件中保存定制结果】
  • 1.5 【SQL*Plus 格式命令】
  • 1.5.1 【COLUMN 命令】
  • 1.5.2 【break on 合并 、 skip 空行】
  • 1.5.3 【使用 TTITLE 和 BTITLE 命令】
  • 1.6 【创建一个生成报表的脚本文件】
  • 1.6.1 【spool】 保存 sql 结果到文件中
  • 【总结】
  • 使用 sqlplus 创建友好输出
  • 定制 sqlplus 环境官方文档位置:SQL*Plus User’s Guide and Reference

1.1 【替代变量】

define 常用在 sqlplus 交互式环境中accept 常用在脚本中

1.1.1 【带有一个&的替换变量】

  • & 取地址符/ AND 符在条件语句中使用一个&, SQL 在执行的时候会提示输入员工号
  • SCOTT@PROD> select empno,ename,sal,deptno from emp
  • 2 where empno=&employee_num;

1.1.2 【替换变量中的字符串和日期值】

  • SCOTT@PROD> select ename,deptno,sal*12 from emp
  • 2 where job=job=&job_title;

  • SCOTT@PROD> select ename,deptno,sal*12 from emp
  • 2 where job=’&job_title’;

1.1.3 【运行时指定列名、表达式、文本】


  • SCOTT@PROD> select empno,ename,job,&column_name from emp
  • 2 where &condition order by & order_column;

1.1.4 【带有两个&的替换变量】


  • 需求:查询这个列的值,同时引用这个列做排序
  • SCOTT@PROD> select empno,ename,job,&col from emp order by &col;

  • SCOTT@PROD> select empno,ename,job,&&col from emp order by &col;

  • define 查看 sqlplus 当前定义的变量

1.2 【定义用户变量 define/accept】

define 默认定义字符串类型accept 可以在定义的时候指定变量数据类型、能够给出个性化提示信息

1.2.1 【DEFINE 和 UNDEFINE】


  • SCOTT@PROD> define deptname=sales
  • SCOTT@PROD> define deptname
  • SCOTT@PROD> select * from dept

  • SCOTT@PROD> undefine deptname=sales
  • SCOTT@PROD> define deptname

1.2.2 【ACCEPT 命令】


  • SCOTT@PROD> select * from dept
  • 2 where dname=upper(’&dept’);

  • SCOTT@PROD> accept dept prompt 'Provide the department name: ’
  • Provide the department name: sales
  • SCOTT@PROD> select * from dept
  • 2 where dname=upper(’&dept’);

  • 1.2.2.1【accept + hide 输入密码】
  • SCOTT@PROD> accept password prompt 'Please input your password: ’ hide
  • 设置为tiger,方便验证
  • SCOTT@PROD> conn scott/tiger
  • SCOTT@PROD> conn scott/&password

【set 定制 SQL*Plus 环境】

1.3 【SET 设置命令变量】

可以直接设置在 $ORACLE_HOME/sqlplus/admin/glogin.sql

1.3.1 *【verify on 显示替换前后命令】


  • SCOTT@PROD> show verify

  • 关闭与开启 old 和 new 的显示 set verify off/on

1.3.2 *【linesize】 行的长度

  • 显示长度还和屏幕分辨率有关
  • SCOTT@PROD> show linesize

  • SCOTT@PROD> set linesize 100

1.3.3 *【pagesize】 页面的尺寸

  • SCOTT@PROD> show pagesize

  • SCOTT@PROD> set pagesize 999
  • pagesize 0 不分页

1.3.4 *【time】 系统当前时间

  • SCOTT@PROD> set time on

  • 00:06:28 SCOTT@PROD> !date
  • 00:07:04 SCOTT@PROD> set time off

1.3.5 *【timing】 SQL 执行时间

  • SCOTT@PROD> set timing on
  • SCOTT@PROD> select * from dept;

  • SCOTT@PROD> set timing off
  • 时:分:秒.百分之一秒

1.3.6 【set echo on】 执行 sql 脚本时 显示脚本内容

  • SCOTT@PROD> select * from scott.emp where empno=7788;
  • SYS@PROD> save /home/oracle/scripts/select.sql
  • SYS@PROD> !cat /home/oracle/scripts/select.sql

  • 切一个会话查看目录是否存在
  • [oracle@enmoedu1 ~]$ cd /home/oracle
  • [oracle@enmoedu1 ~]$ ls

  • [oracle@enmoedu1 ~]$ mkdir scripts

  • SCOTT@PROD> select * from scott.emp where empno=7788;
  • SCOTT@PROD> save /home/oracle/scripts/select.sql
  • SCOTT@PROD> !cat /home/oracle/scripts/select.sql

  • SCOTT@PROD> set echo on
  • SCOTT@PROD> show echo

  • SCOTT@PROD> set echo on
  • SCOTT@PROD> @ /home/oracle/scripts/select.sql

  • SCOTT@PROD> set echo off
  • SCOTT@PROD> @ /home/oracle/scripts/select.sql

1.3.7 【feedback on】显示查询结果行数

  • SCOTT@PROD> show feedback #结果为6 行及以上显示

  • SCOTT@orcl>select * from emp ;

1.3.8 【heading on】 显示头信息、列名

  • SCOTT@PROD> show heading

  • SCOTT@PROD> select * from emp where empno=7788;
  • SCOTT@PROD> set heading off
  • SCOTT@PROD> select * from emp where empno=7788;

1.3.9 【set colsep ‘|’ 】 设置列分隔符

  • SCOTT@PROD> set colsep ‘|’
  • SCOTT@PROD> select * from emp;

  • SCOTT@PROD> set colsep ’ ’

1.3.10【termout on】 显示脚本执行结果

  • SCOTT@PROD> select * from emp;
  • SCOTT@PROD> save /home/oracle/scripts/test.sql
  • SCOTT@PROD> @ /home/oracle/scripts/test.sql

  • 在 sqlplus 中不显示脚本执行结果
  • SCOTT@PROD> set termout off
  • SCOTT@PROD> @ /home/oracle/scripts/test.sql

1.3.11【arraysize/long】

  • arraysize: sqlplus 与 oracle 交互时,每一次返回到客户端的行数

  • long:控制 lob 对象查询的时候显示多少字符数据类型中的 blob/clob 等 lob 对象, lob:large object 大对象。

1.4 【在 glogin.sql 文件中保存定制结果】

$ORACLE_HOME/sqlplus/admin/glogin.sql

define _editor=viset sqlprompt “_user’@’_connect_identifier>”

1.5 【SQL*Plus 格式命令】

1.5.1 【COLUMN 命令】



  • SCOTT@PROD> column ename heading ‘Employee |Name’ format a10
  • SCOTT@PROD> column sal justify left format $99,990.00
  • SCOTT@PROD> column mgr format 999999999 null ‘No manager’
  • SCOTT@PROD> select * from emp;

  • SCOTT@PROD> col ename clear
  • SCOTT@PROD> col sal clear
  • SCOTT@PROD> col mgr clear
  • SCOTT@PROD> clear columns

  • 清除所有 column 设置

1.5.2 【break on 合并 、 skip 空行】

  • SCOTT@PROD> break on deptno
  • SCOTT@PROD> select deptno,ename,sal from emp order by deptno;

  • SCOTT@PROD> break on deptno skip 1
  • SCOTT@PROD> select deptno,ename,sal from emp order by deptno;

  • clear breaks清除所有 break 配置
  • SCOTT@PROD> clear breaks

1.5.3 【使用 TTITLE 和 BTITLE 命令】


  • T:top B:bottom

1.6 【创建一个生成报表的脚本文件】

[oracle@enmoedu1 ~]$ vi /home/oracle/scripts/report.sql

[oracle@enmoedu1 ~]$ cat /home/oracle/scripts/report.sql

set linesize 45

set pagesize 25

ttitle ‘Employee|Report’

btitle ‘Confidential’

col job heading ‘Job|Category’ for a15

col ename heading ‘Employee’ for a15

col sal heading ‘Salary’ format ‘$99,999.99’

break on job

select job,ename,sal from emp order by job;

clear columns

clear breaks

set linesize 100

set pagesize 100

btitle off

ttitle off

SCOTT@PROD> @ /home/oracle/scripts/report.sql

1.6.1 【spool】 保存 sql 结果到文件中

  • SCOTT@PROD> spool /home/oracle/scripts/emp1.sql
  • SCOTT@PROD> select * from emp;
  • SCOTT@PROD> spool off;
  • SCOTT@PROD> set trimspool on #行显示结果紧凑


  • [oracle@enmoedu1 scripts]$ cat emp1.sql

  • [oracle@enmoedu1 scripts]$ vi /home/oracle/scripts/cr_sal.sql
  • [oracle@enmoedu1 scripts]$ cat cr_sal.sql
  • set heading on
  • set feedback off
  • set linesize 100
  • set pagesize 30
  • set colsep ’ | ’
  • set trimspool on
  • TTITLE ‘Salary|Report’
  • set underline ‘*’
  • BTITLE ‘Confidential’
  • col empno heading ‘Employee|Number’ for 99999
  • col ename heading ‘Employee|Name’ for a8
  • col sal heading ‘EMPLOYEE|Salary’ for $99,990.00
  • col mgr for 9999
  • col comm for 9999
  • set termout off
  • spool /home/oracle/scripts/emp_sal_rpt
  • select * from emp order by sal;
  • spool off
  • clear columns
  • clear breaks
  • set pagesize 100
  • btitle off
  • ttitle off
  • set feedback 6
  • set colsep ’ ’
  • set termout on
  • set underline ‘-’
  • set trimspool off

  • SCOTT@PROD> @ /home/oracle/scripts/cr_sal.sql

  • [oracle@enmoedu1 scripts]$ cat emp_sal_rpt.lst

  • 【总结】1.替换变量 & / &&2.定义用户变量: define / accept3.设置 sqlplus 环境set {verify / echo / feedback / heading / linesize / pagesize / time / timing / colsep /underline / termout /pause }4.sqlplus 格式命令 column / ttitle / btitle / break / spool
  • 目录
  • 1.1 【处理数据DML语句】
  • 1.2 【SQL 语句类型】
  • 1.2.4 【TCL:事务控制语言 Transaction Control Language】
  • 1.3 【DML 数据操纵语言】
  • 1.3.1 【insert 语句】
  • 1.3.1.1【向表中增加新行】
  • 1.3.1.2【插入带有空值的行】
  • 1.3.1.3【插入特殊的值 sysdate 函数】
  • 1.3.1.4*【从另一个表中拷贝行】
  • 1.3.1.5【补充扩展: insert all 多行插入多个表】
  • 1.3.2 【update 语句】
  • 1.3.2.1【用多列子查询进行修改】
  • 1.3.2.2*【基于另一个表进行修改】
  • 1.3.2.3【将值更新为空值】
  • 1.3.2.4【关联子查询更新】
  • 1.3.3 【delete 语句】
  • 1.3.3.1【从一个表中移去一行】
  • 1.4 【TCL 事务控制语言】
  • 1.4.1 【事务的 ACID 属性】
  • 1.4.2 【commit】
  • 1.4.2.1【commit 或 rollback之前的数据状态】
  • 1.4.2.2【commit 之后的数据状态】
  • 1.4.2.3【commit 提交数据】
  • 1.4.2.4【隐式提交】
  • 1.4.3 【rollback】
  • 1.4.3.1【回滚后的数据状态】
  • 1.4.3.2【隐式回滚】
  • 1.4.4 【savepoint】
  • 1.4.4.1【使用 savepoint 回滚到某个标记】
  • 1.4.4.2【savepoint 控制事务】
  • 1.5 【锁定】
  • 1.5.1 【select for update】
  • 1.5.2 【死锁】
  • 【总结】
  • 【OCP】
  • 处理数据-DML 语句

1.1 【处理数据DML语句】

1.2 【SQL 语句类型】

SELECT 数据查询语言1.2.1 【DML:数据操纵语言 Data Manipulation Language】DML 是指 SQL 语句中用于处理对象中的数据的语言。DML 语句用来增加、 修改和删除数据库对象中的数据,例如表中的数据。insert 向数据库的表中添加数据。update 更改表中已有的数据。delete 从表中删除已有的数据。mergeDML:增、删、改1.2.2 【DDL:数据定义语言 Data Definition Language】DDL 是指用于构建数据库对象的语言。create 创建数据库中的表、视图、索引、同义词和其他对象。alter 用于修改数据库中已经存在的对象结构、名称或其他属性。drop 删除数据库对象。truncate 截断表。名师传道,授业以专!2 / 29 www.enmoedu.com1.2.3 【DCL:数据控制语言 Data Control Language】grant 为用户赋予权限revoke 收回用户权限

savepoint 在会话中设置一个保存点,将来通过 rollback 语句可以回滚到该位置。

1.2.4 【TCL:事务控制语言 Transaction Control Language】

  • commit 提交事务
  • rollback 撤销事务
  • savepoint 在会话中设置一个保存点,将来通过 rollback 语句可以回滚到该位置。
  • 1.3 【DML 数据操纵语言】

1.3.1 【insert 语句】


  • 1.3.1.1【向表中增加新行】



  • SCOTT@PROD> insert into dept(deptno,dname,loc)
  • 2 values (50,‘DEVELOPMENT’,‘DETROIT’);

  • 1.3.1.2【插入带有空值的行】

  • SCOTT@PROD> insert into dept(deptno,dname)
  • 2 values(60,‘MIS’);
  • SCOTT@PROD> insert into dept
  • 2 values(70,‘FINANCE’,null);

  • 插入时经常出现错误:1.对 not null 约束的列没有插入值或插入 null 值2.违反唯一约束、 check 约束3.数据类型不匹配4.值过大,超出列的范围
  • 1.3.1.3【插入特殊的值 sysdate 函数】
  • sysdate 函数取出当前的日期时间。
  • SCOTT@PROD> insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
  • 2 values (7196,‘GREEN’,‘SALESMAN’,7782,sysdate,2000,null,10);

  • SCOTT@PROD> insert into emp
  • 2 values(2296,‘AROMANO’,‘SALESMAN’,7782,
  • 3 to_date(‘FEB 3,1997’,‘MON DD,YYYY’),1300,null,10);

  • 1.3.1.4*【从另一个表中拷贝行】
  • 创 建 表 emp1, 表 结 构与 emp相 同, 无数 据

  • SCOTT@PROD> create table emp1 as select * from emp where 1=2;
  • SCOTT@PROD> insert into emp1 select * from emp where job=‘MANAGER’;
  • SCOTT@PROD> select * from emp1;

  • 1.3.1.5【补充扩展: insert all 多行插入多个表】

  • 创建实验表 emp10,emp20,emp30,仅有表结构无数据
  • SCOTT@PROD> create table emp10 as select empno,ename,deptno from emp where 1=2;
  • SCOTT@PROD> create table emp20 as select empno,ename,deptno from emp where 1=2;
  • SCOTT@PROD> create table emp30 as select empno,ename,deptno from emp where 1=2;
  • SCOTT@PROD> insert all
  • 2 when deptno = 10 then
  • 3 into emp10 values (empno,ename,deptno)
  • 4 when deptno = 20 then
  • 5 into emp20 values (empno,ename,deptno)
  • 6 when deptno = 30 then
  • 7 into emp30 values (empno,ename,deptno)
  • 8 select empno,ename,deptno from emp;

1.3.2 【update 语句】



  • 需求: 7782 号员工从 10 号部门调到 20 号部门
  • SCOTT@PROD> select * from emp where empno=7782;
  • SCOTT@PROD> update emp set deptno=20 where empno=7782;
  • SCOTT@PROD> select * from emp where empno=7782;

  • 1.3.2.1【用多列子查询进行修改】

  • 需求:修改 7698 号员工的职位和部门号与 7499 一样
  • SCOTT@PROD> update emp
  • 2 set (job,deptno)=(select job,deptno from emp where empno=7499)
  • 3 where empno=7698;

  • 1.3.2.2*【基于另一个表进行修改】

  • SCOTT@PROD> update emp
  • 2 set deptno=(select deptno from emp where empno=7788)
  • 3 where job=(select job from emp where empno=7788);

  • 1.3.2.3【将值更新为空值】
  • update 时可以使用 =null
  • SCOTT@PROD> update emp set job=null;

  • 回滚数据
  • SCOTT@PROD> rollback;

  • 也可以使用 =’’
  • where 条件中不能使用=null =’’ <>’’ <>null 只能用is null和is not null
  • 1.3.2.4【关联子查询更新】
  • 创建测试表
  • SCOTT@PROD> drop table emp1 purge;
  • SCOTT@PROD> create table emp1 as select empno,ename,deptno from emp;
  • SCOTT@PROD> alter table emp1 add (loc varchar2(10));
  • SCOTT@PROD> select * from emp1;

  • 更新 emp1 表中 loc 列
  • SCOTT@PROD> update emp1 e
  • 2 set loc=(select loc from dept d where d.deptno=e.deptno);
  • SCOTT@PROD> select * from emp1;

1.3.3 【delete 语句】

  • delete 语句用来从数据库的表中删除行。被删除的行是由 where 子句所确定的。如果将 where 子句忽略,那么 delete 将从表中删除所有的行。

  • 1.3.3.1【从一个表中移去一行】


  • 创建一个表emp2
  • SCOTT@PROD> create table emp2 as select * from emp;
  • SCOTT@PROD> delete from emp2
  • 2 where deptno=10;

  • SCOTT@PROD> delete from emp2;
  • SCOTT@PROD> select * from emp2;

1.4 【TCL 事务控制语言】

sqlplus 异常中止时自动进行隐式回滚

1.4.1 【事务的 ACID 属性】

  • 任何关系型数据库都必须能够通过 ACID 测试:原子性: Atomicity 一致性: Consistency 隔离性: Isolation 持久性: Durability原子性:一个事务的所有部分必须都完成,或者都不完成。一致性:查询的结果必须与数据库在查询开始时的状态一致。隔离性:除了作出变更的会话,其他会话都无法看到未提交的数据。持久性:事务一旦完成,所有用户必须能够立刻看到所做的变更,同时数据库必须保证这些变更不会丢失。(数据库通过日志保持事务的持久性)

1.4.2 【commit】

  • commit 保存从会话中最后一次提交之后对数据库所做的修改。commit 对数据库作出永久的变动,一旦提交之后,这些变动就无法再通过 rollback 语句撤销。显示提交: commit 语句。隐式提交: DDL 或 DCL 语句。正常退出 Sql*Plus
  • 1.4.2.1【commit 或 rollback之前的数据状态】

  • SCOTT@PROD> update emp set ename=‘OCM’ where empno=7788;
  • SCOTT@PROD> select * from emp;

  • 新开一个 session

  • SCOTT@PROD> update emp set sal=9000 where empno=7788;

  • SCOTT@PROD> update emp set sal=9000 where empno=7782;
  • 事务等待,锁

  • SCOTT@PROD> rollback

  • 不回滚,退出时会发生隐形提交
  • 1.4.2.2【commit 之后的数据状态】
  • 1.4.2.3【commit 提交数据】
  • 1.4.2.4【隐式提交】

  • DDL、 DCL、正常退出DDL、 DCL 语句正确执行后会隐式 commit语法正确的 DDL、 DCL 语句执行前会隐式 commit
  • 隐式提交:
  • 非“自动提交”, Oracle 服务器只是按照指令进行操作。1.执行 DDL 语句时,实现这个 DDL 命令的源代码包含了一个完全正规的 commit 命令。2.SQL*Plus 退出命令 exit(quit),默认 exit commit,可以退出时 exit rollback;

1.4.3 【rollback】

  • rollback 撤销了执行该命令的用户在指定的会话中对数据库所做的修改。rollback 不会撤销已经提交过的修改。

  • 1.4.3.1【回滚后的数据状态】
  • 1.4.3.2【隐式回滚】
  • .异常退出:(点 X 关闭 sqlplus)2.系统崩溃
  • windows 平台 SQL*Plus 关闭窗口的代码中嵌入了一条 rollback 语句。

1.4.4 【savepoint】

  • savepoint 在一个事务内建立分隔点,使后续的 rollback 语句可以进一步细分能够撤销操作的位置。 实现未提交事务的部分回滚。
  • savepoint 语句需要有一个名称。在一个事务中不应重复 savepoint 名称,如果重复, 新的 savepoint 会覆盖旧的,从效果上是将之前的 savepoint 删除了。一旦提交事件发生(显示、隐式),所有现有的存储点都将从内存中被删除。
  • 1.4.4.1【使用 savepoint 回滚到某个标记】
  • 类比虚拟机的快照

  • 1.4.4.2【savepoint 控制事务】

  • 创建测试表create table test as select * from dept;select * from test;insert into test values (50,‘a’,null);savepoint spa;select * from test;
  • insert into test values (60,‘b’,null);savepoint spb;select * from test;
  • rollback to spb;select * from test;
  • rollback to spa;select * from emp;rollback;
  • Ctrl+C 终止
  • 若直接返回到spa点,则会自动清除spb点

1.5 【锁定】

【官方文档】Database Concepts9 Data Concurrency and Consistency

锁是一种保护机制,用于管理对共享资源的并发访问。oracle 数据库是多用户数据库,可以并发操作。锁避免了并发操作带来的数据不一致情况。

DML 操作用到的两种锁:受影响记录上的排它锁 exclusive受影响表上的共享锁 shared

排它锁:只有一份,我加锁后,其他人不能加锁,只能等待。 行级别, 保护这行的数据,防止其他会话修改这些行。只锁定修改的行。缩小加锁范围。如: session1 update 一行, session2 update 此行会等待。但可以 update 其他行。

共享锁:可以加多份。 表级别, 保护表的结构,防止其他会话使用 DDL 语句修改表的定义。如: session1 做 update 操作时, session2 不能把表删了,不能把字段删了。

1.5.1 【select for update】

  • session1:
  • SCOTT@PROD> select * from emp where empno=7788 for update;
  • session2:
  • SCOTT@PROD> delete from emp where empno=7788;
  • 等待,加不上

  • SCOTT@PROD> select * from emp where empno=7788 for update;
  • 等待,加不上

  • Ctrl+C 终止
  • session1 rollback 或 commit 后, session2 可以 select for update;
  • select for update 会锁定所有检索的行。除了发出命令的会话外,其他会话都不能改变这些行。 在这个会话 commit 或 rollback 之前, for update 设置的锁会一直存在。
  • for update nowait:如果已经加锁了,返回错误。
  • session1:
  • SCOTT@PROD> update emp set sal=sal*1.1 where empno=7788;

  • SCOTT@PROD> select * from emp where empno=7788 for update;

  • SCOTT@PROD> select * from emp where empno=7788 for update nowait;

1.5.2 【死锁】

  • session1
  • SCOTT@PROD> update emp set sal=9999 where empno=7788;

  • session2
  • SCOTT@PROD> update emp set sal=8888 where empno=7566;
  • SCOTT@PROD> update emp set sal=7777 where empno=7788;

  • session1
  • SCOTT@PROD> update emp set sal=5555 where empno=7566;

  • session2

  • 【总结】1.数据操纵语言 DML:增删改 insert,update,delete2.事务控制语言 TCL: commit,rollback,savepoint3.DML 会加锁:行排它锁、表共享锁

  • 【OCP】051-8 子查询、表连接
  • 目录
  • 1.1 【数据库对象】
  • 1.1.1 【对象命名习惯】
  • 1.2 【create table 基本语法】
  • 1.3 【创建表: create table】
  • 1.3.1 【default 默认值】
  • 1.4 【数据类型】
  • 1.4.1 【字符】
  • 1.4.3 【日期】
  • 1.5 【oracle 数据库中的表】
  • 1.6 【查询数据字典】
  • 1.7 【使用子查询来创建一个表】
  • 1.7.1 【创建一个空表】
  • 1.8 【修改表: alter table 命令】
  • 1.8.1 【add 增加一列】
  • 1.8.2 【modify 更改一个现存的列】
  • 1.8.3 【drop column 丢弃一个列】
  • 1.8.4 【set unused 标记列不可用】
  • 1.8.5 【rename column 重命名列】
  • 1.8.6 【read only 设置表只读】
  • 1.9 【改变一个对象的名称】
  • 1.10 【截断表:truncate table】
  • 1.11 【为一个表增加注释】
  • 1.12 【删除表: drop table】
  • 【总结】
  • 【补充扩展: truncate 和 delete 对比】
  • 【OCP】
  • 创建和管理表-DDL 语句

1.1 【数据库对象】

能够访问数据的对象。

表:列组成表的结构,行组成表的数据。视图:存储在数据字典中的一条 select 语句。序列:一种生成唯一数字的结构:有序的发出数字。索引:可以减少对表中行的访问次数、提高查询性能。同义词:别名。

掌握:创建、修改、删除

1.1.1 【对象命名习惯】


  • 1.小写字母会自动转换为大写字母2.通过将名称括在双引号内,除长度外其他规则可以打破。但后面使用对象时也一定要用双引号指定。(不推荐)3.保留字: Oracle 不建议使用 v$reserved_words, reserved=‘Y’ 完全禁止使用
  • create table “123” (name varchar2(10));desc 123;desc “123”;

1.2 【create table 基本语法】

用户要建表,需要有 create table 权限,有存储空间。一张表中最多有 1000 个列。

[GLOBAL TEMPORARY]临时表级别:事务级:事务结束,数据消失

会话级:会话断开,数据消失

[schema.] 方案名存储空间: 在表空间有使用权限逻辑存储结构:表空间、段、区、块物理存储结构:数据文件

1.3 【创建表: create table】

SCOTT@PROD> create table dept01

2 (deptno number(2),

3 dname varchar2(14),

4 loc varchar2(13));

SCOTT@PROD> desc dept01

1.3.1 【default 默认值】


  • 创建表, col1 col2 两列, col2 默认值 sysdate
  • SCOTT@PROD> create table test (col1 number,
  • 2 col2 date default sysdate);

  • SCOTT@PROD> insert into test(col1) values(1);
  • SCOTT@PROD> select * from test;

  • insert、 update 时也可以使用 default
  • SCOTT@PROD> insert into test values(2,default);
  • SCOTT@PROD> update test set col2=default;
  • SCOTT@PROD> select * from test;

1.4 【数据类型】

数据类型——不同的器皿,养鱼用鱼缸,放大米用米袋或米缸常见数据类型:字符、数字、日期、大对象

官方文档:SQL Language Reference -> 3 Basic Elements of Oracle SQL -> Oracle Built-in DataTypes重点掌握: varchar2(n)、 number、 date

1.4.1 【字符】char(n) 固定长度字符数据,读取效率快, 存储时使用空格填满空白内容。 n 默认值 1,范围 1~2000字节。varchar2(n) 可变长度字符数据,节省存储空间。 n 必须指定,范围 1~4000 字节。性别 char(2),一个汉字占两个字节。姓名 varchar2()1.4.2 【数字】number(p,s) 数值数据,包括零、负数、正数。p 是 precision 精度, 总有效数据位数,最大值是 38,默认是 38s 是 scale 刻度,小数点后位数。s=0 整数.s>0 小数点后保留 s 位,小数点左边最多 p-s 位.

s<0 小数点前第|s|位四舍五入,用 0 取代小数点前|s|位。最多 p-s 位 (p+|s|).

1.4.3 【日期】date 日期和时间值。包括世纪、年、月、日、小时、分、秒。必须指定年、月、日。timestamp(n):时间戳,表示日期和时间,比 date 更精准。 n 表示秒向下划分的精度范围, n 取值 0~9,默认 6.【时间段】interval year to month:表示时间段,记录两个 date 或 timestamp 之间以年和月为单位的时间间隔。年、月。interval day to second:表示时间段,记录两个 date 或 timestamp 之间以天和秒为单位的时间间隔。天、小时、分、秒。1.4.4 【大对象】BLOB,Binary Large Object (二进制大对象),例如图片、视频、音频。CLOB,Character Large Object(字符型大对象),例如文本BFILE,定位器,指向保存在数据库服务器的操作系统上的文件。文件大小限制为 4GB。子查询创建表的时候,long 类型不会被拷贝long 类型不能出现在 group by 和 order by 子句一个表只能有一个 long 类型字段long 类型字段不能有约束

1.5 【oracle 数据库中的表】

1.6 【查询数据字典】

数据字典:存储数据库自身的信息

官方文档位置:Reference -> Part II Static Data Dictionary Views

user_tables 查询属于用户自己的表

SCOTT@PROD> select table_name from user_tables;

user_objects : 对象的名字、 ID、类型

SCOTT@PROD> col object_name for a10

SCOTT@PROD> select object_name,object_id,object_type from user_objects;

user_catalog :查看用户拥有的表、视图、同义词、序列

SCOTT@PROD> select * from user_catalog;

cat 是 user_catlog 的同义词。

数据字典表在创建数据库时生成,只有 oracle 能够读写。oracle 提供了一组视图来查询数据字典,大致有 4 中类型的数据字典视图:USER_: 用户所有的对象的信息;ALL_:用户拥有的和有权限操作的对象的信息;

DBA_:只有具有 DBA 角色的用户可以访问,里面包含数据库所有对象的信息;V$_:动态性能视图(数据来自内存)、数据库服务器的性能和锁的相关信息。

哪个视图显示数据库中所有的表?是 DBA_TABLES 不是 ALL_TABLES

1.7 【使用子查询来创建一个表】

SCOTT@PROD> create table dept30 as

2 select empno,ename,sal*12 ANASAL,hiredate from emp

3 where deptno=30;

SCOTT@PROD> select * from dept30;

SCOTT@PROD> create table emp10 (id,name,salary) as select empno,ename,sal

2 from emp where deptno=10;

SCOTT@PROD> select * from emp10;

SCOTT@PROD> create table emp20

2 as select empno id,ename name,sal+nvl(comm,0) total_salary

3 from emp where deptno=20;

SCOTT@PROD> select * from emp20;

1.创建出来的表结构可以与原表不同。2.列上的 not null(非空)约束也将应用于新表,但 primary key(主键)、 unique(唯一)、 foreigne key(外键)约束 以及 隐式的 not null(主键列)约束都不会被继承。

1.7.1 【创建一个空表】

  • 只创建表结构
  • SCOTT@PROD> create table test as select * from emp where 1=2;
  • 2可以改为任意大于1的数字,不能是字符

1.8 【修改表: alter table 命令】

1.8.1 【add 增加一列】


  • 为 dept30 表新增加一列 job ,类型 varchar2(9)
  • SCOTT@PROD> alter table dept30 add (job varchar2(9));
  • SCOTT@PROD> select * from dept30;

1.8.2 【modify 更改一个现存的列】



  • SCOTT@PROD> alter table dept30 modify(ename varchar2(15));
  • SCOTT@PROD> desc dept30;

1.8.3 【drop column 丢弃一个列】


  • SCOTT@PROD> alter table dept30 drop column job;
  • SCOTT@PROD> select * from dept30;

1.8.4 【set unused 标记列不可用】

  • 表中数据量非常大时,如果在业务高峰时间直接执行 alter table drop column可能会收到如下错误 ORA-01562 - failed to extend rollback segment number stringOracle 推荐使用 SET UNUSED 选项标记一列(或多列),使该列不可用。然后,当业务量下降后再使用 DROP UNUSED column 选项删除被被标记为不可用的列。 SETUNUSED COLUMNS 用于 drop 多列时效率更高,SET UNUSED COLUMNS 方法系统开销比较小,速度较快,但效果等同于直接 drop column。就是说这两种方法都不可逆,无法再还原该字段及其内容了。
  • alter table xxx set unused (col1,col2);
  • alter table xxx drop unused columns;查看 unused 后的视图
  • SCOTT@PROD> alter table dept30 set unused(ename,hiredate);
  • SCOTT@PROD> select * from user_unused_col_tabs;

  • SCOTT@PROD> alter table dept30 drop unused columns;
  • SCOTT@PROD> select * from user_unused_col_tabs;

  • 官方文档Administrator’s Guide20 Managing Tables
  • 考点:1)如果 set unused 某列,该列上有索引,约束,并定义了视图,引用过序列:索引和约束自动删除,序列无关,视图保留定义。2)无法删除属于 SYS 的表中的列,会报 ORA-12988 错误,哪怕是 sys 用户都不可以。
  • SYS@PROD> create table test as select * from scott.emp;
  • SYS@PROD> alter table test set unused(ename,hiredate);

1.8.5 【rename column 重命名列】

  • SCOTT@PROD> alter table dept30 rename column empno to id;
  • SCOTT@PROD> select * from dept30;

1.8.6 【read only 设置表只读】

  • SCOTT@PROD> alter table dept30 read only;

  • SCOTT@PROD> update dept30 set id=1000;

  • SCOTT@PROD> truncate table dept30;

  • SCOTT@PROD> drop table dept30;

  • SCOTT@PROD> alter table dept30 read write;
  • 考点:只读表可以 drop,因为只需要在数据字典做标记。但是只读表不能做 DML, 也不能 truncate。 因为它们都在对只读表做写操作。

1.9 【改变一个对象的名称】

SCOTT@PROD> rename emp10 to enumber;

1.10 【截断表:truncate table】

SCOTT@PROD> select * from enumber;

SCOTT@PROD> truncate table enumber;

SCOTT@PROD> select * from enumber;

SCOTT@PROD> desc enumber

1.11 【为一个表增加注释】

给 emp 表添加注释:Employee Information给 emp 表的 deptno 列添加注释: Department Number

SCOTT@PROD> comment on table emp is ‘Employee Information’;

SCOTT@PROD> desc user_tab_comments;

SCOTT@PROD> select * from user_tab_comments;

SCOTT@PROD> comment on column emp.deptno is ‘Department Number’;

SCOTT@PROD> desc user_col_comments;

SCOTT@PROD> select * from user_col_comments where table_name=‘EMP’;

1.12 【删除表: drop table】

SCOTT@PROD> drop table emp20;

【总结】

常用数据类型: varchar2,number,date创建表 create table修改表 alter table (add,modify,drop column,rename column,read only/read write)删除表 drop table截断表 truncate table

【补充扩展: truncate 和 delete 对比】

1.delete 是 DML 语句,会产生很多 undo 数据,用于回滚(rollback),速度慢。delete 不会降低高水位线。 delete 可以删除表中部分数据。2.truncate 是 DDL 语句,几乎不产生 undo 数据,不能回滚。速度快。truncate 会降低高水位线。 truncate 会删除表中所有数据。


  • 速度
  • 语句类型
  • 是能回滚
  • 是否生成 undo 数据
  • 是否降低高水位线
  • 是否能加条件
  • delete
  • DML
  • 大量
  • 不降低
  • 可以
  • truncate
  • DDL
  • 不能
  • 几乎不
  • 降低
  • 不能

【OCP】051-19 数据类型051-20 数据类型051-21 数据类型051-22 数据类型051-23 数据类型051-24 数据类型051-25 数据类型/对象命名规则/字符串用单引号051-26 对象命名规则/保留字051-27 对象命名规则/创建表规则051-37 删除表(视图、索引、同义词)051-80 数据类型-日期

目录

1.1 【什么是约束】

1.2 【定义一个约束】

1.3 【not null 非空约束】

1.4 【unique 唯一键约束】

1.5 【primary key 主键约束】

1.6 【foreign key 外键约束】

1.6.1 【on delete cascade/set null】

1.6.2 【不指定 on delete cascade】

1.6.3 【指定 on delete cascade】

1.6.4 【指定 on delete set null】

1.7 【扩展:官方文档图示阅读方法】

1.8 【check 约束】

1.9 【增加一个约束】

1.9.1 【自引用外键约束】

1.9.2 【增加非空约束】

1.10 【删除一个约束】

1.10.1【cascade】

1.10.2【删除非空约束】

1.11 【级联约束 cascade constraints】

1.11.1【alter table drop column …cascade constraint】

1.11.2【drop table … cascade constraint】

1.12 【约束有关数据字典】

1.13 【约束的状态】

1.13.1【使约束失效】

1.13.2【使约束生效】

【总结】

【OCP】

数据库中的约束

1.1 【什么是约束】

强制性: 向表中插入数据的时候, oracle 对数据进行检查,满足条件才能插入。完整性: 比如身份证号码必须唯一,重复的不允许插入。5 种约束: 非空、唯一、主键、外键、检查not null,unique,primary key,foreign key,check

约束是一种限制,插入或修改数据时进行判断。在针对定义了约束的表执行 DML 操作时,如果违反约束,将自动回滚出现问题的整个语句,而不是语句中的单个操作,也不是整个事务。

创建时机: 1.创建表的时候,约束和表一起创建2.在表创建后给 alter table 语句给表添加约束

约束存储在数据字典中, USER_CONSTRAINTS

【官方文档】Database Concepts5 Data Integrity

1.2 【定义一个约束】

列级: 引用单个列,在列的内部定义,可以定义任何类型完整性约束。表级:可以引用多个列,与列的定义分开,可以定义除 not null 之外的约束。

1.3 【not null 非空约束】

not null 约束只能在列级定义,不能在表级定义。其他约束,如果定义在一列上,两种都可以。

SCOTT@PROD> create table emp01(

2 empno number (4),

3 ename varchar2(10) constraint nn_emp01_name not null,

4 job varchar2(9),

5 mgr number(4),

6 hiredate date,

7 sal number(7,2),

8 comm number(7,2),

9 deptno number(7,2) not null);

SCOTT@PROD> desc emp01;

SCOTT@PROD> insert into emp01 (empno,ename) values (1000,‘oracle’);

使用子查询创建表时,列上的 not null(非空)约束也将传递到新表(create as创建一样的表时不会传递),但 primary key(主键)、 unique(唯一)、 foreigne key(外键)约束 以及 隐式的 not null(主键列)约束都不会被子表继承。

1.4 【unique 唯一键约束】

oracle 在 unique 约束列上隐式建立 unique indexUINQUE 约束可以在表级或者列级定义。

表级 unique 约束:

SCOTT@PROD> create table dept01(

2 deptno number(2),

3 dname varchar2(14),

4 loc varchar2(13),

5 constraint uk_dept01_dname unique (dname));

SCOTT@PROD> insert into dept01 select * from dept;

SCOTT@PROD> insert into dept01 select * from dept;

有唯一键约束的列不可以插入重复的值,但可以插入一个或多个空值。

SCOTT@PROD> insert into dept01 values (50,null,null);

SCOTT@PROD> insert into dept01 values (60,null,null);

1.5 【primary key 主键约束】

主键是表中的一列或者多列的组合,能够唯一标识表中的一行。一个表只能有一个主键。可以有多个唯一或非空约束。主键约束可以在表级或者列级定义。

表级 PRIMARY KEY 约束 和 列级 UINQUE 约束:

SCOTT@PROD> create table dept02 (

2 deptno number(2),

3 dname varchar2(14) constraint uk_dept02_dname unique,

4 loc varchar2(13),

5 constraint pk_dept02_deptno primary key (deptno));

SCOTT@PROD> insert into dept02 select * from dept;

SCOTT@PROD> insert into dept02 values(20,‘MARKTING’,‘DALLAS’);

SCOTT@PROD> insert into dept02 values(null,‘FINANCE’,‘NEW YORK’);

主键约束和唯一键约束需要索引,如果不存在, oracle 会自动创建索引。

SCOTT@PROD> col index_name for a20

SCOTT@PROD> col index_type for a20

SCOTT@PROD> select index_name,index_type,table_name from user_indexes;

主键约束:非空+唯一

1.6 【foreign key 外键约束】

emp 表的 deptno 列值参考 dept 表的 deptno 列。emp 是子表, dept 是父表

emp 表中插入数据时, deptno 必须是存在于父表 deptno 列中的值.

外键约束可以在表级定义也可以在列级定义。

表级外键约束:

SCOTT@PROD> create table emp02(

2 empno number(4),

3 ename varchar2(10) not null,

4 job varchar2(9),

5 mgr number(4),

6 hiredate date,

7 sal number(7,2),

8 comm number(7,2),

9 deptno number(7,2) not null,

10 constraint fk_emp02_deptno foreign key (deptno)

  • 11 references dept02 (deptno));

SCOTT@PROD> insert into emp02

2 values(7571,‘FORD’,‘MANAGER’,null,sysdate,1000,200,9);

SCOTT@PROD> insert into emp02

2 values(7571,‘FORD’,‘MANAGER’,null,sysdate,1000,200,20);

SCOTT@PROD> commit;

外键约束定义在子表上,所参考的父表列需要有唯一约束或主键约束。

有外键约束的列允许插入空值

SCOTT@PROD> insert into emp02

2 values(7572,‘ALICE’,‘MANAGER’,null,sysdate,1000,200,null);

直接插入不允许,是因为 emp02 的 deptno 列有 not null 约束。

删除 not 约束:

SCOTT@PROD> alter table emp02 modify deptno null;

SCOTT@PROD> insert into emp02

2 values(7572,‘ALICE’,‘MANAGER’,null,sysdate,1000,200,null);

1.6.1 【on delete cascade/set null】

1.6.2 【不指定 on delete cascade】

  • 默认不指定 on delete cascade 时, 不能删除父表中已经被子表引用的行。
  • SCOTT@PROD> select * from emp02;
  • SCOTT@PROD> delete from dept02 where deptno=20;
  • SCOTT@PROD> delete from dept02 where deptno=10;

  • 违反了完整性约束,不允许删除 dept02 表中 deptno=20 的行。父表中没有被子表引用的行可以被删除。

1.6.3 【指定 on delete cascade】

  • 约束不能修改,只能创建、删除、添加
  • 删除约束、 重新添加约束
  • SCOTT@PROD> alter table emp02 drop constraint fk_emp02_deptno;
  • SCOTT@PROD> alter table emp02 add constraint fk_emp_deptno foreign key (deptno)
  • 2 references dept02 (deptno) on delete cascade;

  • SCOTT@PROD> delete from dept02 where deptno=20;
  • SCOTT@PROD> select * from emp02;
  • SCOTT@PROD> rollback;

  • 删除父表中行时,子表 emp02 中引用父表的行 deptno=20 也一起被删除。

1.6.4 【指定 on delete set null】

  • SCOTT@PROD> alter table emp02 drop constraint fk_emp02_deptno;
  • SCOTT@PROD> alter table emp02 add constraint fk_emp02_deptno foreign key (deptno)
  • 2 references dept02 (deptno) on delete set null;

  • SCOTT@PROD> select * from emp02;
  • SCOTT@PROD> delete from dept02 where deptno = 20;
  • SCOTT@PROD> select * from emp02;

  • 删除父表中的行,子表中引用父表行的外键列为 null。

1.7 【扩展:官方文档图示阅读方法】

约束 constraintSQL Language Reference -> 8 Common SQL DDL Clauses ->constraint

外键约束 on delete cascade官方文档位置:SQL Language Reference -> 16 -> CREATE TABLE方框:关键字椭圆:具体字或可以继续扩展relational_table(关系表) -> relational_properties::(关系属性) ->out_of_line_ref_constraint (表级外键约束) -> constraint::(约束) ->out_of_line_ref_constraint:: (表级外键约束) -> ON DELETE CASCADE ->references_clause::=

1.8 【check 约束】

check 约束通常是对某一列的值作出限制。一个列上可以定义多个 check 约束。

heck 约束可以定义在列级或表级。

rownum 等伪列可以出现在 select 语句中,但不是表中真正的列。

SCOTT@PROD> create table dept03(deptno number(2)

2 constraint ck_dept03_deptno check ( deptno between 10 and 99));

SCOTT@PROD> insert into dept03 values(5);

SCOTT@PROD> insert into dept03 values(10);

SCOTT@PROD> commit;

1.9 【增加一个约束】

1.9.1 【自引用外键约束】

  • 自引用外键约束是 外键约束的变体: 外键约束的父表和子表是同一个表。emp 表中,经理 mgr 本身也必须是一名员工 empno.
  • SCOTT@PROD> alter table emp02
  • 2 add constraint fk_emp02_mgr foreign key (mgr) references emp02(empno);

  • 外键约束引用的父表列,必须存在 unique 或 primary key 约束。
  • SCOTT@PROD> alter table emp02 add constraint pk_emp02_empno primary key (empno);

  • SCOTT@PROD> alter table emp02
  • 2 add constraint fk_emp02_mgr foreign key (mgr) references emp02(empno);

1.9.2 【增加非空约束】

  • 增加或删除非空约束要用 modify
  • SCOTT@PROD> alter table emp02 modify empno not null;
  • SCOTT@PROD> desc emp02

  • 在deptno上做,empno被做了check约束
  • SCOTT@PROD> alter table emp02 modify deptno not null;
  • SCOTT@PROD> desc emp02
  • SCOTT@PROD> alter table emp02 modify deptno null;

  • SCOTT@PROD> desc emp02

1.10 【删除一个约束】

SCOTT@PROD> alter table emp02 drop constraint fk_emp02_mgr;

1.10.1【cascade】

  • SCOTT@PROD> alter table dept02 frop constraint pk_dept02)_deptno;

  • SCOTT@PROD> alter table dept02 drop constraint pk_dept02_deptno cascade;

  • 删除父表上的主键约束时,级联把子表的外键约束先删除。

1.10.2【删除非空约束】

  • SCOTT@PROD> alter table emp02 modify empno null;

1.11 【级联约束 cascade constraints】

删除有约束的列(表)时报错,加 cascade constraints 可先把约束删除。

1.11.1【alter table drop column …cascade constraint】

  • SCOTT@PROD> alter table dept02
  • 2 add constraint pk_dept02_deptno primary key (deptno);
  • SCOTT@PROD> alter table emp02 add constraint fk_emp02_deptno foreign key (deptno)
  • 2 references dept02 (deptno);

  • SCOTT@PROD> alter table dept02 drop column deptno;

  • SCOTT@PROD> alter table dept02 drop column deptno cascade constraint;

1.11.2【drop table … cascade constraint】

  • SCOTT@PROD> drop table dept02;
  • SCOTT@PROD> create table dept02 as select * from dept;
  • SCOTT@PROD> alter table dept02 add constraint pk_dept02_deptno primary key (deptno);
  • SCOTT@PROD> alter table emp02 add constraint fk_emp02_deptno foreign key (deptno)
  • 2 references dept02 (deptno);

  • SCOTT@PROD> drop table dept02;
  • SCOTT@PROD> drop table dept02 cascade constraint;

1.12 【约束有关数据字典】

SCOTT@PROD> col table_name for a15

SCOTT@PROD> col CONSTRAINT_NAME for a30

SCOTT@PROD> col COLUMN_NAME for a30

SCOTT@PROD> col R_CONSTRAINT_NAME for a30

SCOTT@PROD> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME

2 from user_constraints;

SCOTT@PROD> col owner for a10

SCOTT@PROD> select * from user_cons_columns;

同时查看约束列名和表名

SCOTT@PROD> select a.constraint_type,a.table_name,a.constraint_name,

b.column_name

2 from user_constraints a,user_cons_columns b

3 where a.table_name=b.table_name and a.table_name=‘EMP02’;

1.13 【约束的状态】

1.13.1【使约束失效】


  • SCOTT@PROD> alter table dept03
  • 2 disable constraint ck_dept03_deptno;
  • SCOTT@PROD> alter table dept03
  • 2 modify constaint ck_dept03_deptno disable;
  • SCOTT@PROD> select constraint_name,table_name,status,validated
  • 2 from user_constraints
  • 3 where table_name=‘DEPT03’;

1.13.2【使约束生效】


  • SCOTT@PROD> alter table dept03
  • 2 enable constraint ck_dept03_deptno;
  • SCOTT@PROD> select constraint_name,table_name,status,validated
  • 2 from user_constraints
  • 3 where table_name=‘DEPT03’;

  • enable validate:新插入数据和表中已有数据都满足约束。disable novalidate:可以插入任何数据,表中可以存在不满足约束的数据。enable novalidate:新插入数据必须满足约束,但表中可以存在不满足约束的数据。(主键/唯一键约束如果设置为此状态,创建时需使用 deferrable)
  • enable:启用约束(默认)disable:禁用约束validate:表中已有数据验证(默认)novalidate:表中已有数据不验证
  • isable validate:这种情况最终结果是 DML 命令锁定表:表中所有数据都符合约束,新插入数据可以不满约束,逻辑上矛盾,此时 insert/update/delete 都不允许。
  • alter table dept03disable validate constraint ck_dept03_deptno;
  • 【总结】约束: not null,unique,primary key,foreign key,check列级: not null
  • 表级或列级: uinque,primary key,foreign key,check创建约束、删除约束、增加约束(增加、删除非空约束)
  • 【OCP】051-28 约束051-29 约束051-30 约束-check051-31 约束、序列051-32 约束051-33 约束051-34 约束-primary key051-35 约束051-36 约束
  • 【作业】1.创建一个结构与 EMP 表结构、数据都相同的 EMPLOYEE 表,使用 ALTER TABLE 语句为该表的 empno 列增加一个主键约束, pk_employee_empno。create table employee as select * from emp;alter table employee add constraint pk_employee_empno primary key(empno);2.创建一个结构与 DEPT 表相同结构、数据的 DEPARTMENT 表,为该表的 deptno 列增加一个主键约束: pk_department_deptno。create table department as select * from dept;alter table department add constraint pk_department_deptno primary key(deptno);3.为 EMPLOYEE 表增加一个外键约束,保证员工不能安排到一个不存在于 department 中的部门。alter table employee add constraint fk_employee_deptnoforeign key(deptno) references department(deptno);4.为 employee 表增加一个外键约束,保证经理号 mgr 都存在于此表中。alter table employee add constraint fk_employee_mgrforeign key(mgr) references employee(empno);5.从数据字典视图 USER_CONSTRAINTS 中查询出上面两个表所添加约束的名称、类型。desc user_constraints;select constraint_name,constraint_type from user_constraintswhere table_name in (‘EMPLOYEE’,‘DEPARTMENT’);
  • 目录
  • 1.1 【数据库对象】
  • 1.2 【为什么使用视图】
  • 1.3 【简单视图和复杂视图】
  • 1.4 【创建视图的基本语法】
  • 1.4.1【创建简单视图】
  • 1.5 【查询一个视图】
  • 1.6 【视图的数据更新】
  • 1.7 【创建复杂的视图】
  • 1.7.1 扩展:创建视图 查询表空间使用情况
  • 1.8 【对视图进行 DML 操作的规则】
  • 1.8.1 delete
  • 1.8.2 update
  • 1.8.3 insert
  • 1.9 【with check option 限定操作范围】
  • 1.10 【with read only 禁止 DML 操作】
  • 1.11 【删除视图】
  • 【总结】
  • 【OCP】
  • 1 创建视图

1.1 【数据库对象】

逻辑数据集:没有真正数据。

1.2 【为什么使用视图】

通过视图访问数据 与 基表数据隔开静态数据字典视图: DBA_ 、 ALL_、 USER_、 动态性能视图 V$

1.3 【简单视图和复杂视图】

简单视图从明细表中取得数据,不使用函数,不进行聚合。复杂视图可以联接明细表,使用函数或进行聚合。

简单视图通常能接受 DML 语句,复杂视图则不能。

1.4 【创建视图的基本语法】

or replace : 假如视图已经存在,可以覆盖。

建表的时候 create table 可以 or replace 吗?

force:即使子查询中明细表不存在,也创建视图。noforce:默认值,如果明细表不存在,则引发错误。with check option 加约束进行检查,对视图进行 dml 操作时,检查创建时的 where 条件。with read only 只能进行查询,不能通过视图修改基表。

1.4.1【创建简单视图】

  • sys 用户授予 scott 用户创建视图的权限(系统权限)
  • SCOTT@PROD> conn / as sysdba
  • SYS@PROD> grant create view to scott;

  • SYS@PROD> conn scott/tiger
  • SCOTT@PROD> create or replace view salvu30
  • 2 as select empno employee_number,ename name,sal salary from emp
  • 3 where deptno=30;

  • SCOTT@PROD> select * from salvu30;

1.5 【查询一个视图】

oracle 访问 user_views 数据字典,找到视图的子查询并执行,返回数据。访问视图,实际是访问基表。

SCOTT@PROD> col view_name for a20

SCOTT@PROD> col text for a70

SCOTT@PROD> select view_name,text from user_views;

视图是存放在数据字典中的一条子查询。

1.6 【视图的数据更新】

SCOTT@PROD> create or replace view empvu10

2 (employee_number,employee_name,job_title)

3 as select empno,ename,job

4 from emp

5 where deptno=10;

SCOTT@PROD> select * from empvu10;

SCOTT@PROD> update empvu10 set employee_name=‘test’ where employee_number=7782;

SCOTT@PROD> select * from emp;

1.7 【创建复杂的视图】

需求,创建复杂视图,查询各部门名称、 工资的最小值、最大值、平均值

SCOTT@PROD> create view dept_sum_vu

2 (name,minsal,maxsal,avgsal)

3 as select d.dname,min(e.sal),max(e.sal),avg(e.sal)

4 from emp e,dept d

5 where e.deptno=d.deptno group by d.dname;

SCOTT@PROD> select * from dept_sum_vu;

1.7.1 扩展:创建视图 查询表空间使用情况

  • create view tablesp_usage as
  • select a.tablespace_name as tablespace_name,
  • to_char(a.total/1024/1024,99999999) as total_mb,
  • to_char((a.total-b.free)/1024/1024,99999999) use_mb,
  • to_char(b.free/1024/1024,99999999) as free_mb,
  • to_char(((total-free)/total)*100,999.99) as “Used %”
  • from
  • (select tablespace_name,sum(bytes) as total from dba_data_files
  • group by tablespace_name) a,
  • (select tablespace_name,sum(bytes) as free from dba_free_space
  • group by tablespace_name) b
  • where a.tablespace_name=b.tablespace_name order by 5 desc;
  • SYS@PROD> select * from tablesp_usage;

1.8 【对视图进行 DML 操作的规则】

可以对简单视图进行 DML 操作。

1.8.1 delete

1.8.2 update

1.8.3 insert


  • 考点: 基表中 not null 约束的列没有在视图中出现

1.9 【with check option 限定操作范围】

1.10 【with read only 禁止 DML 操作】

在创建视图时使用 with read only 选项,禁止通过视图 DML 操作。

SCOTT@PROD> create or replace view empvu10

2 (employee_number,employee_name,job_title)

3 as select empno,ename,job from emp

4 where deptno=10 with read only;

SCOTT@PROD> delete from empvu10;

1.11 【删除视图】

SCOTT@PROD> drop view empvu10;

【总结】视图是从数据库表中或者其他视图中获取的数据的集合。1.视图是一个逻辑的结果集,没有自己的数据。2.视图是存放在数据字典中的一条子查询。

【OCP】

051-2 视图 DML 操作规则

051-38 视图

051-39

051-40

051-41

051-42

【作业】1.创建一个叫做 EMP_VU 的视图,它的列分别来自于 EMP 表的 empno,ename,deptno。该视图能够获取 EMP 所有行的信息。不允许从该视图更改数据。create view emp_vu as select empno,ename,deptno from emp with read only;2.查询出 EMP_VU 的所有数据。尝试更改 7788 的名字为“ORACLE”。select * from emp_vu;update emp_vu set ename=‘ORACLE’ where empno=7788;3.从数据字典 USER_VIEWS 中,获取视图名称(VIEW_NAME)和视图文本(TEXT)的信息。select view_name,text from user_views where view_name=‘EMP_VU’;4.创建一个视图 dept20_vu,该视图中包括第 20 号部门的员工编号、姓名和部门名称;分别命名为: EMPLOYEE_ID, EMPLOYEE, 和 DEPARTMENT。create or replace view dept20_vu(employee_id,employee,department) asselect e.empno,e.ename,d.deptno from emp e,dept dwhere e.deptno=d.deptno and e.deptno=20;

目录

1.1 【学习目标】

1.2 【序列】 sequence

1.2.1 【创建序列 create sequence】

1.2.2 【nextval 和 currval 伪列】

1.2.3 【序列的使用】

1.2.4 【user_sequences】

1.2.5 【修改一个序列】

1.2.6 【删除一个序列】

1.3 【同义词】 synonym

1.3.1 【创建和删除同义词】

1.4 【索引】 index

1.4.1 【创建索引】

1.4.2 【创建索引的语法】

1.4.3 【B-tree 索引的结构】

1.4.3.1【索引里内容】

1.4.3.2【rowid】

1.4.3.2.1【扩展: rowid 查 对象号-文件号-块号-行号】

1.4.3.2.2*【利用 rowid 查询/删除表中重复行】

1.4.4 【创建索引注意事项】

1.4.5 【索引相关数据字典视图】

1.4.6 【常见 B 树索引创建语法】

1.4.7 【基于函数的索引】

1.4.8 【重建索引】

1.4.9 【删除索引】

1.4.10【索引不可用 unusable 和 不可见 invisible】

1.4.10.1 索引不可用(unusable)

1.4.10.2 索引不可见(invisible)

1.4.11【补充扩展:监控索引的使用】

【OCP】

【扩展:分析函数查重去重】

【扩展: PLSQL 实现索引监控】

序列-同义词-索引

1.1 【学习目标】1.创建、维护、使用序列2.创建和维护索引3.创建同义词

1.2 【序列】 sequence

数据库对象:存放在数据库当中的。可共享:相同的用户,不用的会话登录,可以共同调用;不同用户可以使用。主键列,如果是 number 型的,按顺序产生,可以使用序列。应用程序自己产生唯一的数字也可以,需要额外的代码,维护不方便,使用效率不高。

1.2.1 【创建序列 create sequence】


  • increment by n :步长,每次增长多少,默认 1start with n :初始值,默认 1maxvalue 升序序列最大值,默认无最大值minvalue 降序序列最小值,默认无最小值cycle : 到最大值之后,是循环。 cycle 不能用在主键列。默认 nocycle. 循环后初始是从 1 开始的,不管原来的值是如何设的。cache :缓存多少,默认 20。为了提高性能, oracle 预先成批地发出序列值,并将它们缓存起来以便发给用户

  • SCOTT@PROD> create sequence dept_deptno
  • 2 increment by 1
  • 3 start with 91
  • 4 maxvalue 100
  • 5 nocache
  • 6 nocycle;

1.2.2 【nextval 和 currval 伪列】


  • 伪列:不存在的,虚拟的列。
  • 第一次访问时, currval 是没有值的。
  • SCOTT@PROD> select dept_deptno.currval from dual;

  • SCOTT@PROD> !oerr ora 8002

  • SCOTT@PROD> select dept_deptno.nextval from dual;

1.2.3 【序列的使用】

  • 利用序列产生的值向表中插入数据
  • SCOTT@PROD> insert into dept(deptno,dname,loc)
  • 2 values(dept_deptno.nextval,‘MARKETING’,‘BEIJING’);

  • rollback;
  • 新 session
  • SCOTT@PROD> insert into dept(deptno,dname,loc)
  • 2 values(dept_deptno.nextval,‘TRAINING’,‘SHANGHAI’);

  • SCOTT@PROD> select * from dept;

  • SCOTT@PROD> rollback;
  • SCOTT@PROD> select * from dept

  • SCOTT@PROD> insert into dept(deptno,dname,loc)
  • 2 values(dept_deptno.nextval,‘TEONTTP’,‘CHONGQING’);

1.2.4 【user_sequences】


  • 如果使用 cache,关闭数据库时,所有已生成并缓存,但还没有发出的数值会丢失。
  • SCOTT@PROD> desc user_sequences;

  • SCOTT@PROD> col SEQUENCE_NAME for a25
  • SCOTT@PROD> select * from user_sequences;

1.2.5 【修改一个序列】



  • SCOTT@PROD> select dept_deptno.nextval from dual;

  • SCOTT@PROD> alter sequence dept_deptno
  • 2 increment by 1
  • 3 maxvalue 999999
  • 4 nocache
  • 5 nocycle;

1.2.6 【删除一个序列】

1.3 【同义词】 synonym

oracle 中,如果一个表名字特别长,可以创建同义词,简化对对象的访问。

public:公有同义词私有同义词只能自己访问,公有同义词任何有权限的用户都可以访问。公有同义词不是模式对象,不能用模式名做前缀。

1.3.1 【创建和删除同义词】


  • SYS@PROD> grant create synonym to scott;
  • SYS@PROD> grant create public synonym to scott;
  • 解锁 hr 用户 并修改密码为hr
  • SYS@PROD> alter user hr account unlock identified by hr;

  • scott 用户为 emp 表创建公有同义词 scottemp
  • SCOTT@PROD> create public synonym scottemp for emp;

  • hr 用户尝试访问
  • SCOTT@PROD> conn hr/hr
  • HR@PROD> select * from scottemp;
  • sys 用户把公有同义词 scottemp 的访问权限给 hr
  • SYS@PROD> grant select on scottemp to hr;
  • SYS@PROD> conn hr/hr
  • HR@PROD> select * from scottemp;

  • 或者 scott 把访问 emp 基表的权限赋予 hr
  • HR@PROD> conn scott/tiger
  • SCOTT@PROD> grant select on emp to hr;
  • SCOTT@PROD> conn hr/hr
  • HR@PROD> select * from scottemp;

1.4 【索引】 index

索引跟调优非常密切。官方文档Database Concepts 3 Indexes and Index-Organized TablesPerformance Tuning Guide

  • 11 The Query Optimizer 11.2.3 Index Scans

1.4.1 【创建索引】

1.4.2 【创建索引的语法】


  • SCOTT@PROD> create index emp_ename_idx
  • 2 on emp(ename);

1.4.3 【B-tree 索引的结构】



  • 1.4.3.1【索引里内容】
  • 列的值(键值) + rowid
  • SCOTT@PROD> select ename,rowid from emp;

  • 1.4.3.2【rowid】
  • rowid 是伪列, oracle 专用的虚拟列,每个表的每一行都有 rowid。每一行的 rowid 是全局唯一的。(行的身份证号)
  • AAAR+RAAHAAAACTAAH 7788 SCOTT
  • rowid 18 位, 64 进制包括:所在表的对象号 数据文件号 块号 块行号63636 位对象号, 3 位文件号, 6 位块号, 3 位行号
  • 1.4.3.2.1【扩展: rowid 查 对象号-文件号-块号-行号】
  • 1.4.3.2.1.1【表对应的对象号】
  • HR@PROD> conn / as sysdba
  • SYS@PROD> col object_name for a20
  • SYS@PROD> select object_id,object_name,object_type
  • 2 from dba_objects where owner=‘SCOTT’ and object_name=‘EMP’;

  • 1.4.3.2.1.2【表空间对应数据文件号】
  • SYS@PROD> col owner for a10
  • SYS@PROD> col table_name for a10
  • SYS@PROD> select owner,table_name,tablespace_name from dba_tables
  • 2 where owner=‘SCOTT’ and table_name=‘EMP’;

  • SYS@PROD> col file_name for a50
  • SYS@PROD> select file_name,file_id,tablespace_name from dba_data_files;

  • 1.4.3.2.1.3【段中块号范围】
  • SYS@PROD> col segment_name for a20
  • SYS@PROD> select extent_id,segment_name,bytes/1024 k,blocks,file_id,block_id
  • 2 from dba_extents where owner=‘SCOTT’ and segment_name=‘EMP’;

  • BlOCK_ID 显示的是段中块的号的开始,范围 144-151
  • 1.4.3.2.1.4【Rowid 显示 对象号-数据文件号-块号-行号】
  • SCOTT@PROD> select rowid,
  • 2 dbms_rowid.rowid_object(rowid) object#,
  • 3 dbms_rowid.rowid_relative_fno(rowid) datafile#,
  • 4 dbms_rowid.rowid_block_number(rowid) block#,
  • 5 dbms_rowid.rowid_row_number(rowid) row#
  • 6 from emp where empno=7788;

  • emp表中7788 这行数据对应的对象 73580,7 号数据文件,147号块中的,第7 行
  • 1.4.3.2.2*【利用 rowid 查询/删除表中重复行】
  • 1.4.3.2.2.1创建测试表
  • SCOTT@PROD> drop table dept01;
  • SCOTT@PROD> create table dept01 as select * from dept;
  • SCOTT@PROD> insert into dept01 select * from dept where deptno=30;
  • SCOTT@PROD> insert into dept01 select * from dept where deptno=40;
  • SCOTT@PROD> insert into dept01 select * from dept where deptno=40;
  • SCOTT@PROD> commit;

  • SCOTT@PROD> select a.rowid,a.* from dept01 a;

  • 1.4.3.2.2.2查找重复行
  • SCOTT@PROD> select a.rowid,a.* from dept01 a order by rowid;

  • SCOTT@PROD> select a.rowid,a.* from dept01 a order by deptno,rowid;

  • 去重思路:重复数据行中,保留 rowid 最小的或者 rowid 最大的
  • SCOTT@PROD> select min(rowid),deptno,dname,loc from dept01
  • 2 group by deptno,dname,loc order by deptno;

  • 确认要删除的行
  • SCOTT@PROD> select a.rowid,a.* from dept01 a where rowid not in
  • 2 (select min(rowid) from dept01 group by deptno,dname,loc);

  • SCOTT@PROD> select * from dept01 where rowid not in
  • 2 (select min(rowid) from dept01 group by deptno,dname,loc);

  • 1.4.3.2.2.3删除重复行
  • SCOTT@PROD> delete from dept01 where rowid not in
  • 2 (select min(rowid) from dept01 group by deptno,dname,loc);

  • 1.4.3.2.2.4关联子查询写法
  • SCOTT@PROD> select a.rowid,a.* from dept01 a where a.rowid not in
  • 2 (select min(rowid) from dept01 b
  • 3 where a.deptno=b.deptno and a.dname=b.dname and a.loc=b.loc);

1.4.4 【创建索引注意事项】


  • 为了优化连接操作,可以在外键列上创建索引,加快匹配行的查找。
  • 索引能够提高 select 语句的效率, 但执行 DML 语句时,oracle 要维护索引,会做很多的递归操作,有维护成本。

1.4.5 【索引相关数据字典视图】



  • user_indexes 查看索引名字、类型、表名、是否唯一索引user_ind_columns 查看索引名、表名、列名
  • SCOTT@PROD> desc user_indexes

  • SCOTT@PROD> col index_name for a20
  • SCOTT@PROD> col table_name for a20
  • SCOTT@PROD> select index_name,index_type,table_name from user_indexes;

  • SCOTT@PROD> col column_name for a20
  • SCOTT@PROD> select index_name,table_name,column_name from user_ind_columns;

  • 连接两表查询表和列详细信息
  • SCOTT@PROD> select a.index_name,a.index_type,a.table_name,b.column_name
  • 2 from user_indexes a,user_ind_columns b
  • 3 where a.table_name=b.table_name and a.table_name=‘EMP’;

1.4.6 【常见 B 树索引创建语法】

  • SCOTT@PROD> create table emp1 as select * from emp;
  • 1)唯一索引,指键值不重复。
  • SCOTT@PROD> create unique index empno_idx on emp1(empno);

  • SCOTT@PROD> drop index empno_idx;
  • 2)非唯一索引
  • SCOTT@PROD> create index empno_idx on emp1(empno);
  • SCOTT@PROD> drop index empno_idx;

  • 3)组合索引(Composite):基于两个或多个列的索引
  • SCOTT@PROD> create index job_deptno_idx on emp1(job,deptno);
  • SCOTT@PROD> drop index job_deptno_idx;

  • 4)反向键索引(Reverse):将字节倒置后组织键值。当使用序列产生主键索引时,可以防止叶节点出现热块现象(考点)。缺点是无法提供索引范围扫描。
  • SCOTT@PROD> create index mgr_idx on emp1(mgr) reverse;
  • SCOTT@PROD> drop index mgr_idx;

  • 5)函数索引(Function base):以索引列值的函数值为键值去组织索引 SCOTT@PROD> create index fun_idx on emp1(lower(ename));
  • SCOTT@PROD> drop index fun_idx;

  • 6)压缩(Compress):重复键值只存储一次,就是说重复的键值在叶块中就存一次,后跟所有与之匹配的 rowid 字符串。
  • SCOTT@PROD> create index comp_idx on emp1(sal) compress;
  • SCOTT@PROD> drop index comp_idx;

  • 7)升序或降序(Ascending or descending):叶节点中的键值排列默认是升序的。
  • SCOTT@PROD> create index deptno_job_idx on emp1(deptno desc,job asc);
  • SCOTT@PROD> drop index deptno_job_idx;

1.4.7 【基于函数的索引】


  • 查看执行计划
  • SCOTT@PROD> explain plan for select * from emp where ename=upper(‘scott’);
  • SCOTT@PROD> @?/rdbms/admin/utlxplp.sql

  • SCOTT@PROD> explain plan for select * from emp where lower(ename)=‘scott’;
  • SCOTT@PROD> @?/rdbms/admin/utlxplp.sql

  • 创建基于函数的索引
  • SCOTT@PROD> create index lower_ename_idx on emp(lower(ename));
  • 再次查看执行计划
  • SCOTT@PROD> explain plan for select * from emp where lower(ename)=‘scott’;
  • SCOTT@PROD> @?/rdbms/admin/utlxplp.sql

1.4.8 【重建索引】


  • SCOTT@PROD> alter index lower_ename_idx rebuild online;

1.4.9 【删除索引】


  • 一个无用的索引会降低 DML 效率,还占用存储空间。
  • SCOTT@PROD> drop index lower_ename_idx;

1.4.10【索引不可用 unusable 和 不可见 invisible】

  • 1.4.10.1 索引不可用(unusable)
  • SCOTT@PROD> create index ind_test_id on emp1(job);

  • 仅仅保存索引定义,不删除索引,也不更新索引。
  • SCOTT@PROD> alter index ind_test_id unusable;
  • 索引被设定为 unusable 后,如再次使用需要做 rebuild。
  • SCOTT@PROD> alter index ind_test_id rebuild;

  • 1.4.10.2 索引不可见(invisible)
  • 在 11g 里, Oracle 提供了一个新特性(Index Invisible)来降低直接删除索引或禁用索引的风险。可以在创建索引时指定 invisible 属性或者用 alter 语句来修改索引为 invisible(visible)索引不可见其实是对优化器来说不可见,索引维护还是正常进行的。
  • SCOTT@PROD> alter index ind_test_id invisible;

  • SCOTT@PROD> select index_name,status,visibility from user_indexes
  • 2 where table_name=‘EMP1’;

  • SCOTT@PROD> alter index ind_test_id visible;
  • SCOTT@PROD> select index_name,status,visibility from user_indexes
  • 2 where table_name=‘EMP1’;

1.4.11【补充扩展:监控索引的使用】


  • desc user_indexes;desc v object_usage;select * from v objectu​sage;select∗fromvobject_usage;开启索引监控alter index pk_dept monitoring usage;执行查询select * from dept where deptno=10;查看索引是否被使用select * from v object_usage;关闭索引监控alter index pk_dept nomonitoring usage;select * from v objectu​sage;关闭索引监控alterindexpkd​eptnomonitoringusage;select∗fromvobject_usage;

  • 【OCP】051-43 序列051-44 序列051-45 序列051-46 索引051-47 索引051-48 索引051-49 同义词051-50 同义词051-51 同义词
  • 【扩展:分析函数查重去重】 SCOTT@PROD> insert into dept01 select * from dept where deptno=30;
  • SCOTT@PROD> insert into dept01 select * from dept where deptno=30;
  • SCOTT@PROD> insert into dept01 select * from dept where deptno=40;
  • SCOTT@PROD> insert into dept01 select * from dept where deptno=40;
  • SCOTT@PROD> insert into dept01 select * from dept where deptno=40;
  • select a.rowid,a.deptno,a.dname,a.loc,ROW_NUMBER () OVER (PARTITION BY a.deptno,a.dname,a.loc ORDER BY a.ROWID) rnfrom dept01 a;

  • select rowid from(select a.rowid,a.deptno,a.dname,a.loc,ROW_NUMBER () OVER (PARTITION BY a.deptno,a.dname,a.loc ORDER BY a.ROWID) rnfrom dept01 a) bwhere b.rn >1;
  • delete from dept01 where rowid in (select rowid from(select a.rowid,a.deptno,a.dname,a.loc,ROW_NUMBER () OVER (PARTITION BY a.deptno,a.dname,a.loc ORDER BY a.ROWID) rnfrom dept01 a) bwhere b.rn >1);


【扩展: PLSQL 实现索引监控】

declarecursor cu_index is select index_name from user_indexes;beginfor i in cu_index loop–dbms_output.put_line('alter index ‘|| i.index_name || ’ monitoring usage’);execute immediate 'alter index ‘|| i.index_name || ’ monitoring usage’;end loop;end;

关闭索引监控declarecursor cu_index is select index_name from user_indexes;beginfor i in cu_index loopexecute immediate 'alter index ‘|| i.index_name || ’ nomonitoring usage’;end loop;end;

【作业】1.创建一个序列来产生表 DEPARTMENT 的主键值。该序列名字为 DEPT_ID_SEQ,它的初始值为 60,最大值为 200,每次递增 10,并且不能循环使用这些值。create sequence dept_id_seq increment by 10 start with 60 maxvalue 200 nocycle;2.从 user_sequences 视图中查出序列名称、最大值、递增值、下一个值的信息。select SEQUENCE_NAME,MAX_VALUE ,INCREMENT_BY,LAST_NUMBER from user_sequences;3.向 DEPARTMENT 表中插入两行信息,要求使用上面创建的序列来产生部门号insert into department values(dept_id_seq.nextval,‘IT’,‘BEIJING’);insert into department values(dept_id_seq.nextval,‘HR’,‘SHANGHAI’);commit;select * from department;4.在 EMPLOYEE 表中的 deptno 列上创建一个索引 idx_depart_deptno。create index idx_depart_deptno on employee(deptno);

目录

1.1 【控制用户存取】

1.2 【创建用户】

1.2.1 【修改用户密码】

1.2.2 【解锁用户】

1.2.3 【使用新建用户登录】

1.3 【权限】

1.4 【系统权限】

1.4.1 【用户的系统权限】

1.5 【角色】

1.5.1 【创建角色并给角色授权】

1.5.2 【默认角色 connect/resource】

1.6 【对象权限】

1.6.1 【授予对象权限】

1.6.2 【with grant option 和 public】

1.7 【权限相关数据字典视图】

1.8 【收回权限】

【总结】

【补充扩展: select any table】

【补充扩展:设置密码不过期】

【扩展:查看执行计划 set autotrace】

1 控制用户访问

1.1 【控制用户存取】

访问数据库里的数据,要经过 oracle 用户名和密码的验证,通过验证后才能登陆到数据库对数据进行访问

sqlplus scott/tiger 使用用户名/密码 验证sqlplus / as sysdba 是操作系统认证方式,操作系统中 oracle 用户 属于 dba 组,有权限对数据库进行管理。

外部认证:把用户名和口令存储在目录服务器上

官方文档:Security Guide

1.2 【创建用户】

SYS@PROD> create user damon identified by damon;

1.2.1 【修改用户密码】


  • SYS@PROD> alter user damon identified by oracle;

  • SYS@PROD> password scott

1.2.2 【解锁用户】

  • SYS@PROD> alter user hr identified by hr account unlock;

1.2.3 【使用新建用户登录】

  • SYS@PROD> conn damon/oracle
  • @> conn / as sysdba

  • 缺少创建会话的权限 (create session)
  • SYS@PROD> !oerr ora 1045

1.3 【权限】

系统权限:能够在数据库中做什么操作(如创建表)对象权限:能够对指定的对象做什么操作(如对表的 DML 操作权限)【模式】: schema :某个用户下所有对象的集合。 模式与用户一一对应

select * from scott.emp;访问 scott 模式下 emp 这个对象。

1.4 【系统权限】

reate table:在自己的方案中创建表create any table:跨方案创建表

8i 80+, 9i 100+, 10g 11g 200+

权限分布细化,提高安全性。sysdba 权限最大 用 sys 用户登录时 要 as sysdbadba 是角色

官方文档:SQL Language Reference-> 18 SQL Statements: DROP SEQUENCE to ROLLBACK->GRANTTable18-1 系统权限: system privilegesTable18-2 对象权限: object privilegesOracle Database Security Guide:预定义的角色角色:权限的集合

1.4.1 【用户的系统权限】



1.5 【角色】

dba 是角色 sysdba 是系统权限

oracle 可以用角色来简化权限的管理。创建角色 -> 把所需的权限给角色 -> 把角色给用户。

1.5.1 【创建角色并给角色授权】

1.5.2 【默认角色 connect/resource】

  • CONNECT、 RESOURCE 角色:查看角色包含的权限
  • SYS@PROD> select * from role_sys_privs where role in (‘CONNECT’,‘RESOURCE’);

  • SYS@PROD> grant connect,resource to damon;

  • 给用户能够完成任务的最低标准。

1.6 【对象权限】

index 在表上创建索引的权限references 参考一个对象的权限

不同的对象有不同的权限。

SYS@PROD> conn damon/oracle

DAMON@PROD> select * from scott.emp;

1.6.1 【授予对象权限】


  • DAMON@PROD> conn scott/tiger
  • SCOTT@PROD> grant select on emp to damon;
  • SCOTT@PROD> conn damon/oracle
  • DAMON@PROD> select * from scott.emp;

  • 对象权限可以精确到列(insert/update)。
  • SCOTT@PROD> grant update (dname,loc) on dept to damon;
  • 12c需要加一个权限,select
  • 11g可以直接更新,但是不能查看

1.6.2 【with grant option 和 public】


  • with grant option:这个用户还可以把授权给他的权限给其他用户。A 把权限授予 B,B 还可以把这个权限授予其他用户。
  • SCOTT@PROD> grant select ,insert on dept to damon with grant option;

  • Damon 可以把相关权限授予给其他
  • DAMON@PROD> grant select on scott.dept to hr;

  • 【PUBLIC 角色】:数据库中任何一个用户都默认拥有 public 角色,授予给 public 权限(grant … to public),会使所有用户拥有这个权限

1.7 【权限相关数据字典视图】

session_privssession_roles

SCOTT@PROD> select * from session_privs;

SCOTT@PROD> select * from session_roles;

SCOTT@PROD> select * from role_sys_privs;

SCOTT@PROD> select * from user_sys_privs;

SCOTT@PROD> select * from user_tab_privs_recd;

SCOTT@PROD> select * from user_col_privs_recd;

DAMON@PROD> col owner for a10

DAMON@PROD> col table_name for a10

DAMON@PROD> col privilege for a10

DAMON@PROD> col grantor for a10

DAMON@PROD> select * from user_tab_privs_recd;

1.8 【收回权限】

cascade constraints收回外键约束权限时,先把外键约束删掉。

A -> B -> C -> D对象权限: with grant option对象权限的回收是级联: A 收回 B 的权限, CD 权限也被收回。回收原则谁授予谁回收: A 不能直接回收 C 的权限。scott -> damon -> hr

A -> B -> C -> D系统权限: with admin option系统权限的回收不是级联的: A 收回 B 的权限, CD 权限还在。回收原则:只要被授权时有 with admin option,就可以回收这个权限。

【总结】

系统权限和对象权限语法格式不同,不能混合使用。系统权限和角色语法相同,可以合并授予。一条语句可以给多个用户授权。可以通过授权语句来创建用户。

查看创建 scott 用户方案的脚本more $ORACLE_HOME/rdbms/admin/utlsampl.sq

【补充扩展: select any table】

使用 hr 查询 scott.bonus,没有权限

HR@PROD> select * from scott.bonus;

使用 sys 用户把 select any table 权限赋予给 hr

SYS@PROD> grant select any table to hr;

hr 用户再次查询,可以查看 scott.bonus

hr 用户查询 dba_数据字典(视图),如 dba_users,无法查询HR@PROD>select user_name,account_status from dba_users where user_name=‘HR’;

查询参数 O7_DICTIONARY_ACCESSIBILITY (字母 O 数字 7)SYS@PROD>show parameter O7

不建议使用 O7_参数普通用户查询数据字典视图建议给用户赋予权限 select any dictionary

或角色 SELECT_CATALOG_ROLE

【补充扩展:设置密码不过期】

*查看用户的 profile(一般是 default)

SYS@PROD> select username,profile from dba_users;

*查看 default 概要文件设置的密码有效期(默认 180 天)

SYS@PROD> select * from dba_profiles

where resource_name =‘PASSWORD_LIFE_TIME’ and profile=‘DEFAULT’;

将密码有效期设置为无限制

SYS@PROD> alter profile default limit password_life_time unlimited;

SYS@PROD> select * from dba_profiles

where resource_name =‘PASSWORD_LIFE_TIME’ and profile=‘DEFAULT’;

【扩展:查看执行计划 set autotrace】

plustrace 角色 public 角色

set autotrace on/off/traceonly

sys 用户:创建 plustrace 角色

SYS@PROD> @?/sqlplus/admin/plustrce.sql

将角色赋给 scott

SYS@PROD> grant plustrace to public;

SYS@PROD> conn scott/tiger

SCOTT@PROD> show autotrace

查看执行结果和执行计划

SCOTT@PROD> set autotrace on

SCOTT@PROD> select * from dept where deptno =10;

只查看执行计划

SCOTT@PROD> set autotrace traceonly

SCOTT@PROD> select * from dept where deptno=10;

SCOTT@PROD> set autotrace off

SCOTT@PROD> show autotrace

【作业】1.使用 DBA 账户创建一个账号为 temp,密码为 temp 用户,并授予会话创建权限.conn / as sysdbacreate user temp identified by temp;grant create session to temp;2.以 SCOTT 用户登录,将查询 emp 表的权限授权给 temp 用户.conn scott/tigergrant select on emp to temp;3.使用 temp 用户登录。尝试查询 emp 表中部门号为 20 的所有员工信息,是否成功?尝试删除 emp 表中部门号为 20 的所有员工信息,是否成功?conn temp/tempselect * from scott.emp where deptno = 20;delete from scott.emp where deptno =20;4.以 DBA 用户,创建用户 temp2,密码为 temp2,并授予会话创建权限。conn / as sysdbagrant create session to temp2 identified by temp2;5.以 DBA 用户,创建角色 temp_role.conn / as sysdbacreate role temp_role;6.以 scott 用户登录,将查询 scott.dept 表的权限授权给 temp_roleconn scott/tigergrant select on dept to temp_role;7.将角色 temp_role 授给用户 temp 和 temp2

conn / as sysdba

  • grant temp_role to temp,temp2;

8.用 temp2 用户登录,查询表 scott.dept 的所有信息conn temp2/temp2select * from dept;9.删除角色 temp_role。重复操作第 8 题conn / as sysdbadrop role temp_role;conn temp2/temp2select * from scott.dept;10.删除用户 temp 和 temp2.drop user temp;drop user temp2;