Oracle 学习笔记

╰+哭是因爲堅強的太久メ 2022-03-17 15:30 1539阅读 0赞

**目录**

第一章 基本概念

数据库选择

概念结构设计

第二章 Oracle体系结构

数据库创建

启动数据库

查看当前账号

账号加锁、解锁

断开数据库

查询当前账号下的所有表

查看表结构

设置页面宽度（防止列数太多，因为行宽有限而串行）

设置表格每页显示的行数（不是记录数）

查询语句的基本格式

在缓冲区里进行修改操作（使用缓冲区可以让修改命令更简便）

修改密码

密码失效

文件操作

将屏幕内容保存到文件中

清屏（把屏幕清空）清除屏幕内容及屏幕缓冲区（不同于内存缓冲区）

查询

1.查询所有信息

2.算术运算符

3.空值：无效的，未定义的

4.列的别名

5.连接符||

6.去掉重复行 DISTINCT

7.排序

8.过滤查询

9.比较运算符

10.逻辑运算符

函数

字符函数：

1.大小写控制函数:

2.字符控制函数:

3.数值函数

4.日期函数

转换函数

通用函数

多表查询

PL/SQL 基础

SQL：关型数据库

代码块实现形式：

SQL语句分类

PL/SQL代码块结构

标识符：

注释：

数据类型

定义变量的语法：

插入

1.插入单值

3.向多个表中插入数据

修改：

删除：

截断表

1、格式化

2.压缩列（做分组操作）

设置标题和页脚

层次查询

八、表操作

数据类型

建表

删除表

修改表

表的重命名

约束：对表的强制规定（根据约束的类型）

根据约束约束的位置分类

追加约束

删除约束

禁用和启动约束

查看约束

九、视图

9.1 视图

10 、同义词

创建/删除同义词

11、索引

创建序列

查看序列

更改系列

删除序列

ROWID

索引

用户与权限授理

权限管理

分类

系统权限

系统权限语法格式

系统授权的传递

系统权限的回收，不是级联回收

对象权限，级联回收

1）对象权限的授予

2）对象权限的回收

查询权限信息，这些表中有的表要权限才能看

角色管理

1）创建角色

2）角色权限的授予与回收

3）修改角色

4）角色的生效与失效

5)删除角色

利用角色进行权限管理

1）给用户授予角色

2) 从用户或角色回收角色权限

3）用户角色的激活或屏蔽

4)查询角色信息

概要文件

1）创建概要文件

2) 将概要文件分配给用户

3)删除概要文件

--------------------

笔试+平时分（考勤+上机+作业）30分

# 第一章 基本概念 #

数据库设计过程  
需求分析（重点）：调查情况  
\+概念结构设计+逻辑结构设计+物理结构设计+运行+维护

需求分析

## 数据库选择 ##

SQL server 安全性不是很好，登录的时候密码是星号，不能防星号探测器  
安全：QRACLE，输入密码的时候不显示，对系统的依赖比较强，速度比较慢，适合大型项目，推荐12c,10j版本，最新版是17c版，此版针对云端存储，下载和上传比较耗时  
MySQL，适合小型项目，被Qracle收购了，5.6版本之后，偏向于服务器和云端。

需求  
隐性需求：在文档中没有明确表示，但是可以分析出来  
显性需求：在文档中有明确表示

确定系统边界

需求分析的重点  
重点是调查、收集与分析用户在数据管理中的信息要求、处理要求、安全性与完整性要求  
分析需求的方法：自顶向下的结构化分析方法（SA），从最上层的系统组织结构入手，采用逐层分解的方式分析系统，用数据流图（只要看懂就可以，不要求会画）和数据字典描述系统。

如何画数据流图  
“由外向里”即先确定系统的边界或范围，再细化。

## 概念结构设计 ##

方法：  
自顶向下  
自底向上  
逐步扩张  
混合策略：自顶向下和自底向上的结合，是四个方法中最难的。在一些情况下，需要把子需求加入整个系统，此时就是混合策略。

视图的集成  
冲突的种类：属性冲突（数据库迁移的时候，属性的不一致，如字段大小不一致）、命名冲突、结构冲突  
冗余：冗余的数据是指可由基本数据导出的数据，冗余的联系指可由其他联系导出的联系，冗余会导致搜索变慢，但并不是所有冗余都需要消除。  
消除冗余的方法：加约束（如外键、主键）  
优化：常常用范式

物理设计

Oracle不要使用Win 8，Win 8 家庭版系统不全，如果是Win 10 最好装12c。Win 7 XP可以装10j，除了安全性不是特别好，其他的功能都不错

安装Oracle最好电脑有两个盘，每个盘留2G空白

数据库的实施  
包括：  
用DDL定义数据库结构  
组织数据入库  
编制与调试应用程序  
运行与维护

# 第二章 Oracle体系结构 #

Oracle服务器由Oracle实例和Oracle数据库组成  
实例名（SID）最多可以包含8个字符，在所处的服务器上必须唯一

内存：  
系统全局区：块缓冲区、重做缓冲区（即重做日志缓冲区，对日志的刷新重做）、共享池（数据的公用区域）、固定SGA，大池（即stream池，数据库流操作），Java池（java的代码数据）  
进程全局区：用户进程、服务器进程（连接Oracle实例），后台进程，其他进程（10g中，新增了ARB、ASMB、CTWR……了解即可）  
用户全局区

Oracle监听器：运行于Oracle数据库服务器上的进程，其职责是监听来自于客户应用的连接请求。

# 数据库创建 #

名字要全部用1字母。  
账号：  
超级管理员：sys            默认密码：change\_on\_install  
普通管理员：system     默认密码：manager  
普通用户：sysman        默认密码：tiger  
11j之后的版本起的口令必须包括：小写字母、大写字母、数字  
快速恢复区大小：  
示例方案最好不要勾上  
如果防火墙没关，创建数据库时肯会卡住  
如果弹出，EM无法打开的窗口，则直接关掉忽略即可，因为如果是克隆出来的系统就会报错

## 启动数据库 ##

1.DOS命令行  
Sqlplus 用户名/密码    (如果是超级管理员要加上)AS SYSDBA  
如果输入AQLPIUS 用户名/密码以后报错，则要去找环境变量，或者改成SQLPLUS.exe 用户名/密码   
连接库：Sqlplus 用户名/密码@（库名）【库是用来干什么的？】  
2.图形化界面方式（SQLpuls）  
3.dos命令与图形化操作的转换  
  sqlplusw 用户名/密码    (如果是超级管理员要加上)AS SYSDBA

### 查看当前账号 ###

Show user

连接数据库

Conn 用户名/密码 \[AS SYSTEM\]

### 账号加锁、解锁 ###

Alter user 用户名 ACCOUNT lock|unlock  
锁定账号以后账号就不能用了  
普通管理员不能进行加解锁

### 断开数据库 ###

disconnect（或disc）

### 查询当前账号下的所有表 ###

Select \* from tab

### 查看表结构 ###

Desc（或discribe） 表名  
Emp 表 雇员表  
EMPNO  雇员编号  
ENAME 雇员姓名  
JOB  雇员岗位  
MGRHIREDATE 受雇日期  
SAL 基本工资  
COMM 补助  
DEPTNP 部门编号

### 设置页面宽度（防止列数太多，因为行宽有限而串行） ###

Set linesize 宽度  
DOS命令下运行时，需要先通过属性调整黑框大小  
图形化界面就不会有问题

### 设置表格每页显示的行数（不是记录数） ###

Set pagesize 行数

### 查询语句的基本格式 ###

Select 列名1，列名2，列名3……….from 表名  
如 select ename,job from emp

### 在缓冲区里进行修改操作（使用缓冲区可以让修改命令更简便） ###

1.编辑缓冲区：ed（或edit）  
                       append   
                       del 删除缓冲区内容  
2.执行缓冲区：R（或run）会显示缓冲区的内容  
                       / 不会显示缓冲区的内容  
3.查看缓冲区：l（或list)  
4.清空缓冲区 clear buffer

### 修改密码 ###

Alter user 用户名 identified by 新密码  
Password 用户名  (输入此命令后，会弹出修改的窗口）

### 密码失效 ###

Alter user 用户名 password expired

### 文件操作 ###

1.  1.创建脚本文件

Save 文件地址（默认为create，但是可以不写）  
Save 文件地址 replace   （更换脚本文件）  
Save 文件地址 append  （内容追加）

1.  2.脚本文件的装载

Get 脚本文件  
Get 文件 nolist （装载的时候，不显示缓冲区内容）  
如果文件是.sql的后缀，则文件的后缀可以省略  
如果是其他类型的文件，则必须加后缀，如ab.txt

1.  3.脚本文件的执行（装载并执行）

Start 文件  
或    
@ 文件（用@方式更好，只能在sqlpuls内部执行）

1.  4.注释

a)单行注释：  
//  
b)多行注释：  
/\*注释内容  
注释内容\*/  
c)remark：单行注释，放在一行语句的头部

### 将屏幕内容保存到文件中 ###

Spool 文件名（保存到的文件中）  
……（指令）  
Spool off

### 清屏（把屏幕清空）清除屏幕内容及屏幕缓冲区（不同于内存缓冲区） ###

1.  1.通过指令Clear screen（可以简写cle scr）
2.  2.shift+delete快捷键

*  帮助命令

Help 命令（会把一个指令的的用法显示出来）

*  环境变量的设置与显示

Show 命令（显示环境变量）  
如：show linesize  
Set 命令（设置环境变量）  
如 set autocommit on

*  基本环境变量

1.autocommit：是否自动提交DML语句  
2.colsep：设置列之间的间隔符  
如 set colsep |   （列与列之间用|间隔）  
3.feedback：显示反馈信息的最低行数，默认值是6，只有当表超过6行，才会显示“已选择X行”，否则不显示  
4.heading：是否显示列标题  
如：set heading off （不显示列标题） 或on（显示列标题）  
5.time：命令提示符之前显示时间  
如果设置了时间，则会变成  
10:15:11 SQL>  
6.timing：设置是否显示执行SQL语句所用的时间  
7.环境变量的备份：store set 文件  
8.arravsize：设置从数据库中提取的行数。默认值是15

# 查询 #

Select 列名1，列名2…… from 表名

### 1.查询所有信息 ###

Select 所有列名 from 表名（执行速度比较快，尽量用这个）  
Select \* from 表名  
虽然SQL不区分大小写，但规范来说，SELECT，FROM等关键字要大写  
规范来说，要分行，如：  
SELECT 列名  
FROM 表名

###    2.算术运算符 ###

尽量用12\*sal，把常量写在前面，效率较高。Sal\*12效率会较低。  
时间数据也可以进行加减

###    3.空值：无效的，未定义的 ###

空值不能进行算数运算操作  
对空值的操作函数：Nvl(可能产生空值的列，指定值)将空值变为指定值，之后就可以进行运算操作  
如：sal+nvl（comm，0）

###    4.列的别名 ###

Select 列名1【as（可省略）】列别名1， 列名2 列别名2  
关键字不能用关键字，除非加上双引号  如 SELECT 列名 "FROM" FROM  表名  
如果写成 列名 AS（没有写别名）  则列名变为 列名AS  
如果写成 列名 AS " "(双引号内必须有空格） 则列名变为空  
标准写法是要把别名加双引号

###    5.连接符|| ###

a.列与列之间的合并  
如 SELECT empno||ename FROM emp；  
则会出现类似7369SMI的结果  
b.列与字符\[串\]的合并（用单引号表示字符串）  
如SELECT 'id:'||empno||'，name is'||ename FROM emp

###    6.去掉重复行 DISTINCT ###

注意：  
只能放在第一列的前面  
如 SELECT distinct job   
FROM emp  
但是SELECT ename distinct job   
FROM emp 会报错

###    7.排序 ###

SELECT 列名1  
FROM 表名  
PRDER BY 排序列名1  \[ASC\](升序，可以不写，因为是默认的)或DESC(降序)， 排序列名2 ASC或DESC

###    8.过滤查询 ###

SELECT 列名  
FROM 表名  
WHERE 过滤条件(如：sal>1800)  
ORDER BY 排序列名   
查询语句不区分大小写，但是数据库里的数据是区分大小写的

###    9.比较运算符 ###

=   
>   
<   
>=   
<=   
<> 或 !=

###    10.逻辑运算符 ###

AND  
OR  
NOT  
BETWEEN   AND  
IN(写成IN\[5,20\]在此区间范围内)  
LIKE：%：任意个数的任意字符  
\_:表示一个字符  
IS NULL

AND：如：查询工资大于200，并且小于2000的人的信息  
SELECT \*   
FROM emp  
WHERE sal>800 AND sal<2000

BETWEEN AND：如：查询工资在\[800,2000\]的人的信息  
SELECT \*   
FROM emp   
WHERE sal BETWEEN 800 AND 2000

IS NULL：如：查询没有补助的人的所有信息  
SELECT \*  
FROM emp  
WHERE comm IS NULL

LIKE：如：  
SELECT \*  
FROM emp  
WHERE ename LIKE "\_A%"

NOT：取反操作:  
查询有补助的人的信息：  
Select \* from emp where comm is not null

工具：PLSQL Developer  
可以方便地修改指令，但是最匹配的版本是Oracle 10j

### 函数 ###

1.单行函数：字符、数值、日期、转换、通用  
2.多行函数：

### 字符函数： ###

### 1.大小写控制函数: ###

lower【所有字母转换为小写】  
upper【所有字母转换为大写】如：select upper("hello，world") from dual  
initcap【每个单词第一个字母为大写，之后的为小写】

### 2.字符控制函数: ###

concat（列名1，列名2）【连接两个列，如果多个列连接，需要嵌套concat（x，concat（x)）】【不能嵌套超过三次】  
substr（'字符串'，截取开始处的位置，截取结束处）  
Length('字符串')【求长度】  
Inster（'母串'，'需查找的子串'）【查找字符串的位置】  
Lpad(列名，数据的长度，填充符号)从左边开始填充  
rpad(列名，数据的长度，填充符号)从右边开始填充

### 3.数值函数 ###

Round四舍五入  
Trunc  
mod（10,3）得数为1，求余数

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

Sysdate 系统当前日期  
日期的算数运算  
日期+-数字=日期  
日期+-日期=天数（得出来的天数不是整数，因为还要考虑时分秒）  
天数/7=周数  
Months\_between（日期一，日期二）两个日期之间相差的月份  
Add\_months(日期，月份） 在指定日期上加上月份  
Next\_day  
Last\_day  
Extract

### 转换函数 ###

To\_date  
To\_char  
To\_number

### 通用函数 ###

Nvl  
Nvl2  
Decode

### 多表查询 ###

# PL/SQL 基础 #

PL/SQL 基础（两类语句，一种是SQL，一种是PL（过程化语言，循环判断等））

### SQL：关型数据库 ###

NOSQL：弥补SQL的缺陷，处理非关型数据库，可以用多台数据库处理  
SQL和NOSQL互相补充

PL/SQL效率更高，网络传输的数据量少很多。可以写段代码放到数据库里，是预编译的  
可以写五种代码块：匿名块、过程、函数、触发器（满足触发条件会自动执行）、包（后四种为命名块）  
就算所在的操作系统不一样，程序也不用改

### 代码块实现形式： ###

1.只执行一次永不存储（匿名块）  
2.存储在数据库中以备后用的块（命名块）

### SQL语句分类 ###

1.查询  
2.DDL：数据库模式定义语言，如建表，删除表  
3.DML：以INSERT、UPDATE、DELETE三种指令为核心，数据操纵语言  
4.事物：提交回滚  
5.DCL：授权，回收权限  
其中，查询、DML、事物可以写在PL/SQL中，因为这些语句是静态的。而PL/SQL是预编译的，所以动态的语句不可以写在PL/SQL中（编译的时候值确定不了）

### PL/SQL代码块结构 ###

BEGIN  
    NULL;  
END;

*  声明部分：

DECLARE  
    v\_date DATE；

*  执行部分：（下节课会有例子）

异常处理部分：  
EXCEPTION  
    WHEN OTHERS  
    THEN  
        DBMS\_OUTPUT.

###    标识符： ###

长度只能30  
只能字母开头  
只能包含以下三个特殊符号\_ $ \#  
不能有Oracle的标识符

### 注释： ###

单行注释：--  
双行注释/\*\*/

### 数据类型 ###

*  标量类型：（不可以拆分，用得最多）

字符/字符串类型

数字  
布尔  
日期/时间

*  复合类型：

记录  
嵌套表  
Index-by表  
Varrays  
对象类型

### 定义变量的语法： ###

1.变量名 \[constant(如果写了这个，表示是常量)\]  type  \[not null（如果写了这个，则一定要赋初值\] \[:=value（用于赋初值)\]

2.变量名  其他变量名%type  （这样可以保证此变量名和其他的变量名的类型一样）（多数是这样命名）

# 插入 #

###    1.插入单值 ###

（1）标准写法（推荐）  
INSERT INTO 表名（列名，列名……） VALUE是（值1，值2）  
（2）省略写法（不推荐）  
INSERT INTO 表名 VALUE  
（3）利用select语句  
INSERT INTO(SELECT empno,ename FROM myemp1)VALUES(1，TOM’）  
（4）利用临时表  
INSERT INTO myemp1（empno，ename）SELECT 2，‘allen’FROM dual；  
（5）利用子查询插入数据（批量）  
INSERT INTO myemp1 SELECT \* FROM emp；

针对大量的数据，最好采用的不写入日志文件中，因为这样可以提高执行效率，想要实现此操作，要通过/\*+APPEND\*/  
如：INSERT /\*+APPEND\*/ INTO myemp(empno,ename,sel,job)SELECT empno,ename,sal,jobFROM emp;

## 3.向多个表中插入数据 ##

（1）无条件多表插入（数据备份最笨的做法）  
INSERT ALL  
INTO 表1 VALUES（列1，列2,……）  
INTO 表1 VALUES（列1，列2,……）  
……  
子查询

（2）有条件多表插入（常用来建立分区表，以提高查询效率）  
INSERT ALL或者FIRST（FIRST用于当有重复的数据时，只会插入到较前面的语句的表里）  
WHEN 条件1 THEN INTO 表1（列1，列2，……）  
WHEN 条件2 THEN INTO 表1（列1，列2，……）  
……  
ELSE INTO 表n（列1，列2，……）  
子查询  
例子：  
INSERT FIRST  
WHEN deptno=10 THEN INTO emp10  
WHEN deptno=20 THEN INTO emp20  
WHEN deptno=30 THEN INTO emp30  
ELSE INTO emp\_other  
SELECT \* FROM emp;

（3）多表插入的应用

非关型数据库转换为关型数据库  
列转化为行  
INSERT ALL  
INTO sale\_into VALUES（emp\_id，week\_id，sale\_MON）  
INTO sale\_into VALUES（emp\_id，week\_id，sale\_TUE）  
INTO sale\_into VALUES（emp\_id，week\_id，sale\_WED）

### 修改： ###

全部修改：UPDATE 表名 SET 列=值，列=值，……  
局部修改：UPDATE 表名 SET 列=值，列=值，…… WHERE 条件：

### 删除： ###

全部删除：DELETE FROM 表名  
局部删除：DELECT FROM 表名 WHERE 条件

增加、修改、删除，最后必须要加上事务处理操作

### 截断表 ###

Truncate table 表名  
在别的数据库中 ，table可以省略，但是在关系型数据库中，不可以省略

Rownum：表示行号，但它是一个伪列  
一般用来做分页的操作  
范例：

###    1、格式化 ###

Col【umn】命令  
Heading 设置列标题  
Justify   
Clear  
Format 设置格式 （L9999.99，L代表人名币的符号，9999.99代表数字的格式，为保留两个小数）

Column ename heading 员工名 justify center format a7（a表示显示宽度，7代表几个字符）

### 2.压缩列（做分组操作） ###

break on 列名【SKIP 值】  skip指分组之后的间距

break on deptno skip 1;
    select * from emp group by deptno;

为了实现组函数的分组统计操作，可以与compute命令结合使用

compute  组函数 label  标签内容  of  \[expression\]/(列 on  分组条件）

break on deptno 
    compute AVG label 平均工资 of sal on deptno
    select * from emp order by deptno;

### 设置标题和页脚 ###

*  TTITLE 标题
 *  BTITLE  页脚
 *  什么时候设置都行
 *  后面加上off删除设置

TTITLE CENTER 'EMP'
    BTITLE RIGHT 'MADE IN DYQ'
    select * from emp;
    //显示之前设置的格式样式
    ttitle
    btitle
    //删除设置
    ttitle off
    btitle off

### 层次查询 ###

select Ipad("",5*level-1)||empno
    from emp
    [where]
    start with empno=7839
    connect by prior empno=mgr;

--------------------

# 八、表操作 #

###  数据类型 ###

<table style="width:531px;"> 
 <tbody> 
  <tr> 
   <td>Char</td> 
   <td style="width:115px;">字符串</td> 
  </tr> 
  <tr> 
   <td>Varchar2</td> 
   <td style="width:115px;">可变字符串</td> 
  </tr> 
  <tr> 
   <td>Date</td> 
   <td style="width:115px;">日期</td> 
  </tr> 
  <tr> 
   <td>Number</td> 
   <td style="width:115px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td> <p>Clob/Blob&nbsp;</p> </td> 
   <td style="width:115px;">&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

##   建表 ##

表和表之间必须要有关联

drop table 表名
    create table 表名（
        列名 数值类型  【default 默认值】
        列名 数值类型  【default 默认值】
         ...
        列名 数值类型  【default 默认值】
    ）

##  删除表 ##

*    把表放在回收站里,查不到数据但是还存在于表列表中

drop table 表名；

*  从回收站恢复表，恢复最近的那个表，不能全部恢复

flashback table 表名 to before drop;
    
    //例子
    flashback table A to before drop;

*  从回收站把表彻底删除

purge table 表名；

*  查看回收站里的表

//object_name删完之后的名字 original_name原始名字
    select object_name,original_name，operation_type from recyclebin

*  清空回收站

purge reryclebin;

*   彻底删除,不经过回收站

drop table 表名  purge；

## 修改表 ##

*  增加列

alter table 表名 add(列名 数据类型 【default 默认值】,
                          ....
                        列名 数据类型 【default 默认值】);

*  修改列（数据类型和长度）

alter table 表名  modify(列名 数据类型 【default 默认值】,
                              ....
                            列名 数据类型 【default 默认值】);

*  修改列的名字

alter table 表名 rename column 现列名  to 新列名；

*  删除列

alter table 表名 drop column 列明；

*  修改列的无效状态，等同于删除

alter table 表名 set UNUSED COLUMN 列名；

*  删除无效的列

alter table 表名 drop unused columns;

## 表的重命名 ##

rename 旧的表名 to 新的表名；

## 约束：对表的强制规定（根据约束的类型） ##

create table person(
        Pid varchar(18),
        Name varchar(20),
        Sex int NOT NULL,  //列约束
        Age number(3) check(Age between 0 and 150),   //1,和2等价
    //表约束
        constraint person_pid PRIMARY KEY(Pid),
        constraint person_name UNIQUE(Name),
        constraint person_age check(Age between 0 and 150)   //2
    
       
    )
    create table employee( 
        id varchar(18) REFRENCES person(Pid),  //1,和2等价。这时候，如果父子表顺序错误的话，会报//错
        CONSTRAINT employe_deptno_fk foreign key(id) REFRENCES person(Pid) //2
        
    )

<table> 
 <tbody> 
  <tr> 
   <td style="width:277px;"> <p>主键约束。不能重复、不能为空&nbsp; 。主键可以是多个。</p> </td> 
   <td style="width:433px;"> 
    <ul> 
     <li>primary key 提示信息是系统默认的。列级别</li> 
     <li>constraint ...primary key(列名）在最后加上。表级别</li> 
    </ul></td> 
  </tr> 
  <tr> 
   <td style="width:277px;">唯一约束。不可以重复，可以为空</td> 
   <td style="width:433px;">UNIQUE</td> 
  </tr> 
  <tr> 
   <td style="width:277px;">非空约束。不能为空。没有表级别的</td> 
   <td style="width:433px;">not null</td> 
  </tr> 
  <tr> 
   <td style="width:277px;">检查约束。检查一个列的内容是否合法</td> 
   <td style="width:433px;">check(条件)</td> 
  </tr> 
  <tr> 
   <td style="width:277px;">外键约束。</td> 
   <td style="width:433px;">&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

## 根据约束约束的位置分类 ##

*  列级别约束。直接在列的后面
 *  表级别约束。在所有列的后面

## 追加约束 ##

> 后来想起来要加约束，想要补充

alter table add primary key(pid);//非空约束不行

## 删除约束 ##

alter table person drop primary key;  //删除列级约束
    alter table person drop  约束名字 //删除表级约束

## 禁用和启动约束 ##

//禁用
    alter table 表名disable 约束
    
    alter table person disable primary key;
    alter table person disable constrait 约束名
    //启动
    alter table 表名enable 约束
    
    alter table person enable primary key;
    alter table person enable constrait 约束名

## 查看约束 ##

select * from user_constraints where table_name='person';

# 九、视图 #

## 9.1 视图 ##

*  为了向用户隐藏部分信息
 *  https://blog.csdn.net/kanglovejava/article/details/52961244
 *  SQL允许通过查询来定义“虚关系”。它在概念上包含查询的结果。虚关系并不预先计算并存储，而是在使用虚关系的时候才通过查询被计算出来。任何不是逻辑模型的一部分，但作为虚关系对用户可见的关系称为视图。
 *  视图关系在概念上包含查询结果中的元组，但并不预先计算和存储。相反，数据库系统存储与视图关系相关联的查询表达式。当视图关系被访问时，其中的元组是计算查询结果而被创建出来的。
 *  一旦创建了视图，我们就可以使用视图名指代该视图生成的虚关系，视图名可以出现在任何关系名出现的地方。视图的属性名可以限定。

mysql> create view f as
        -> select * from list;
    Query OK, 0 rows affected (0.13 sec)
    
    mysql> create view w(id) as
        -> select id from list;
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> select * from w；
    mysql> select * from f；
    
    mysql> create view g as
        -> select *
        -> from list
        -> where no<2000
        -> with check option;
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> insert into g(id,no) values('211022199906030548',5000);
    
    
    drop view 视图名；//删除视图
    
    select * from user_views;//查看所有视图

[or replace]如果建过这个视图，覆盖
    force 没有表也建立
    create [or replace][force unforce] view ....
    with check option  //把约束关系搞过来
    with read only //只读视图

# 10 、同义词 #

> 表的另外一个名字。只有管理员才有权力创建和删除同义词

##  创建/删除同义词 ##

create synonym 同义词名 for 表名
    
    create synonym s_emp for scott.emp;
    select * from s_emp;
    
    drop synonym 同义词名；

# 11、索引 #

> 序列：按照一定的规则能够自动增加减少数字的一种数据库对象。

## 创建序列 ##

> create sequence 序列名  
> \[increment by N\] N为1表示每次递增1  
> \[start with N\]从几开始  
> \[maxvalue N\]最大值  
> \[minvalue N\]最小值  
> \[cycle/nocycle\] cycle：达到最大值之后继续产生数字  
> \[cache N /nocache\] cache:序列值被提前存储在分配好的内存之中。nocache:不预先分配内存。默认N=20

create sequence test
    startwith 10
    increment by 2
    maxvalue 100
    minvalue 9
    cycle     //回头从9开始
    cache 10

## 查看序列 ##

> **伪列：**
> 
> CURRVAL：序列返回的当前值
> 
> NEXTVAL：序列返回的下一个值
> 
> **注意：**在被引用之前，必须先使用next\_val来产生一个序列值

select test.CURRVAL from dual；
    //序列实现自动编号
    insert into person values(test.NEXVAL,'scott');

## 更改系列 ##

alter sequence test
    increment by 2
    maxvalue 100
    minvalue 9
    cycle     //回头从9开始
    cache 10
    
    //start with不能更改

## 删除序列 ##

drop sequence test_sql

## ROWID ##

> 伪列，快速定位到行记录.
> 
> **组成**：数据对象编号、相关文件编号、块编号、行编号
> 
>                 6                         3                    6            3

select * from emp where ROWID='AAAAAAAAAAAAAAAAAA';

## 索引 ##

> **分类：**
> 
> 单列索引:索引建立在一列上
> 
> 复合索引：索引建立在某几列上
> 
> **创建索引：**
> 
> 自动创建：建表时，使用PRIMARY KEY或UNIQUE时，数据库自动创建一个索引
> 
> 手工创建：
> 
>     //单列索引
>     create index idx_emp_ename
>     on emp(ename);
>     
>     //复合索引
>     create index idx_emp_ename
>     on emp(ename，sex);
> 
> **注意**：索引的命名规范，idx\_表名\_列名
> 
> **测试索引：**
> 
>     CREATE TABLE e1 AS SELECT * FROM emp;  //复制表结构，不复制约束
>     INSERT INTO e1 SELECT * FROM e1;   //批量插入
>     UPDATE e1 SET empno=ROWNUM;  
>     
>     SELECT ename,sal FROM e1 WHERE empno=210000;  //时间0.076s
>     create index e1_id on e1(empno);          //加上索引
>     SELECT ename,sal FROM e1 WHERE empno=210000;    //时间0.016s
> 
> **索引缺点**：
> 
> 降低DML速度
> 
> 占据资源多
> 
> 频繁更新表，不建议使用。
> 
> 有单行函数，有无索引执行速度差不多
> 
> **删除索引：**
> 
>     drop index 索引名
> 
>

# 用户与权限授理 #

> 用设置用户的安全参数来控制用户的访问和操作。
> 
> **Oracle数据库的初始用户：**
> 
>  *  SYS ：超级管理员，权限最大的。可以启动，修改，关闭数据库
>  *  SYSTEM：一般管理员，不可以启动和关闭数据库，主要管理操作：创建用户、删除用户
>  *  SCOTT：用来测试网络连接
>  *  PUBLIC：本质上是一个用户组，数据库中的任意一个用户都属于该组。因为要为每个用户授权，可以直接授权public 组
> 
> **用户属性（安全参数）：**
> 
>  *  身份认证：数据库身份认证、外部身份认证、全局身份认证
>  *  默认表空间
>  *  临时表空间
>  *  表空间配额
>  *  **概要文件**
>  *  账号状态：加密，解密，密码失效
> 
> **创建用户（不能执行任何操作）：**
> 
>     create user 用户名  identified 
>     [by 密码(数据库身份认证）/externally（外部身份认证）/glocally as 'external_name'(全局）]
>     [default tablespace tablespace_name]  //默认表空间
>     [temporary tablespace temp_tablespace_name]  //临时表空间
>     [QUOTA n K M UNILMITED On tablespace_name]  //表空间配额 
>     [PROFILE profile_name] //概要文件
>     [password EXPIRE]  
>     [ACCOUNT LOCK/UNLOCK]   //锁定
>     
>     例子：创建一个用户user3,口令为user3,默认表空间为USERS，配额为10MB,初始状态为锁定
>     create user user3
>     identified by user3
>     default tablespace USERS
>     QUOTE 10M on USERS account lock;
> 
> **修改账号：**
> 
>     create user 用户名 【identified】
>     [ by 密码(数据库身份认证）/externally（外部身份认证）/glocally as 'external_name'(全局）]
>     [default tablespace tablespace_name]  //默认表空间
>     [temporary tablespace temp_tablespace_name]  //临时表空间
>     [QUOTA n K M UNILMITED On tablespace_name]  //表空间配额 
>     [PROFILE profile_name] //概要文件
>     [DEFAULT ROLE role_list]  //角色
>     [password EXPIRE]  
>     [ACCOUNT LOCK/UNLOCK]   //锁定
> 
>  **删除用户：**
> 
>     drop user 用户名[cascade]; //cascede不删除账户下的数据库对象
> 
> **查询用户：**
> 
>     select * from ……
> 
>  *  ALL\_USERS:包含数据库所有用户的用户名、用户ID和创建时间
>  *  DBA\_USERS:包含数据库所有用户的详细信息
>  *  USER\_USERS:包含当前用户的详细信息
>  *  DBA\_TS\_QUOTAS:包含所有用户的表空间配额信息
>  *  USER\_TS\_QUOTAS:包含当前用户的表空间配额信息
>  *  V$SESSION:包含用户会话信息。数据很少，但是因为会话没有结束，会一直循环的打印，屏幕啪啪跑
>  *  V$OPEN\_CURSOR:包含用户执行的SQL语句信息。

## 权限管理 ##

### **分类** ###

**系统权限**：建表、修改表、删除表、创建视图、删除视图

**注意事项**：

只有DBA才能拥有ALTER DATABASE系统权限

应用程序开发者一般需要拥有create table、create view和create index等系统权限

普通用户一般只具有create session（登陆，连接） 系统权限。

只有授权时带有with admin option子句时，用户才可以将获得的系统权限再授予给其它用户。即系统权限的传递性。

**对象权限**：增删改查

## 系统权限 ##

### 系统权限语法格式 ###

grant 系统权限 to 用户/角色/public [with admin option]

**注意**：如果授予多个系统权限，中间用逗号分隔。如果授予多个用户，中间用逗号分隔。

grant create session,create table,create view to user3;  //授权连接

### 系统授权的传递 ###

grant create session,create table,create view to user1 with admin option;  //授权连接
    grant user1 to user2;

### 系统权限的回收，不是级联回收 ###

revoke 系统权限 from 用户列表

## 对象权限，级联回收 ##

### **1）对象权限的授予** ###

GRANT 对象权限列表 ALL ON 对象 to 对象列表/角色列表  【with grant option】
    
    //例子
    将emp表的select,update,insert 权限授予user1用户。
    
    GRANT select,update,insert on scott.emp to user1;
    
    //例子
    将emp表的select,update,insert 权限授予user2用户。user2用户将emp表的select,update,insert 权限授予user3.
    
    GRANT select,update,insert on emp to user2 with grant option;
    连接到user2用户
    GRANT select,update,insert on emp to user3;
    
    //例子
    GRANT select,update,insert on emp to user2 with grant option;
    GRANT delete on emp to user2 with grant option;
    连接到user2用户
    GRANT delete on emp to user3;  //这样是错误的

### 2）对象权限的回收 ###

> REVOKE 对象权限列表 ALL ON 对象 to 对象列表/角色列表

## 查询权限信息，这些表中有的表要权限才能看 ##

*  DBA\_TAB\_PRIVS  //授权信息
 *  ALL\_TAB\_PRIVS  //对象授权信息
 *  USER\_TAB\_PRIVS  //当前对象授权信息
 *  DBA\_COL\_PRIVS //所有字段已授权信息
 *  USER\_COL\_PRIVS //所有当前用户已授权信息
 *  DBA\_SYS\_PRIVS   //包含授权用户或角色的系统权限信息
 *  USER\_SYS\_PRIVS //包含当前用户

## 角色管理 ##

> 一系列相关权限的集合。
> 
> **角色分类：**
> 
>  *  系统预定义角色
> 
>             一般不会使用
> 
>             select \* from DBA\_USERS;
> 
>  *  用户自定义角色
> 
>

**以下都是用户自定义角色**

### 1）创建角色 ###

角色失效          设置角色密码
    create role 角色名称 [not identified][identified by 密码]
    
    //例子
    create role high;
    create role h identified by 123;
    create role identified by 23;

### 2）角色权限的授予与回收 ###

> 就是之前权限授予一样的。就是对象和系统权限一样的。
> 
> **注意：给角色授权时，一个角色可以被授予另外一个角色，但是不能授予其本身。不能产生循环授权。**

### 3）修改角色 ###

> 让角色有无密码

alter role 角色名称 【not identified】【identified by 密码】
    
    //例子
    为high 角色添加口令，取消low的角色口令
    alter role high identified by 123456;
    alter role low no identified

**注意：**修改角色必须具有alter any role这个系统权限，以及with admin option权限。如果是角色的创建者，自动具有该角色的修改权限。

### 4）角色的生效与失效 ###

角色失效
    set role 【角色名称【identified by密码】】【all(except 角色名称）】【none】
    
    //例子：设置当前所有角色失效
    set role none;
    
    //例子：设置某一个角色生效
    set role high identified by 123;
    
    //例子：同时设置多个角色生效
    set role high，low identified by 123;
    
    //例子：所有角色都生效除了某个角色
    set role all except low;

### 5)删除角色 ###

drop role 角色名称

## 利用角色进行权限管理 ##

### 1）给用户授予角色 ###

grant 角色列表 to 用户列表/角色列表
    
    //例子：将connnect，high角色授予用户user1
    
    grant connect，high to user1;

### 2) 从用户或角色回收角色权限 ###

revoke 角色列表 from 用户列表/角色列表
    
    revoke connect from high;

### 3）用户角色的激活或屏蔽 ###

alter user 用户名 default role 【角色名称】|【ALL(except 角色名称)】|【NONE】
    
    //例子：屏蔽用户的所有角色
    alter user user1 default role none;

### 4)查询角色信息 ###

*  DBA\_ROLES  所有角色及其描述
 *  DBA\_ROLE\_PRIVS  包含被数据库中所有用户和角色授权的角色信息
 *  USER\_ROLE\_PRIVS     包含被当前用户授权的角色信息
 *  ROLE\_ROLE\_PRIVS   被角色授权的角色信息
 *  ROLE\_SYS\_PRIVS   角色授权的系统权限
 *  ROLE\_TAB\_PRIVS    角色授权的对象权限
 *  SESSION\_ PRIVS   当前会话的系统权限
 *  SESSION\_ROLES   当前会话的对象权限

## 概要文件 ##

> 概要文件是数据库和系统资源的集合，是Oracle数据库安全策略的重要组成部分，利用概要文件，可以限制用户对数据库和系统资源的额使用，同时还可以对用户口令进行管理。
> 
> 每个数据库用户都必须有一个概要文件，通常DBA将用户分为几种类型，每种类型的用户创建一个概要文件。
> 
> **系统资源的限制：**
> 
>  *  CPU使用时间
>  *  用户连接数据库的时间
>  *  用户连接数据库的空闲时间
>  *  私有sql区的使用
>  *  每个用户的并发访问数

### 1）创建概要文件 ###

create profile 概要文件名 limit 系统资源限制参数|口令管理参数
    
    //例子：创建一个名为pwd的概要文件，如果用户连续4次登陆失败，则该账号被锁定。十天后，该账号自动解锁
    create profile pwd limit FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 10;
    
    //例子：创建一个res概要文件，每个用户可以创建四个并发会话，每个会话连接时间最长为60min,如果用户20min内无操作，结束会话，每个会话的私有SQL区大小为100KB，每个SQL语句语句的占用总量不超过10s。
    
    create profile res limit 
    SESSION_PER_USER 4 CONNECT_TIME 60 IDLE_TIME 20 PRIVATE_SGA 100k
    CPU_PER_CALL 1000;  //单位1/100s

### 2) 将概要文件分配给用户 ###

> 如果给两个概要文件，后面的替换前面的。

//1 在创建用户的时候指定概要文件
    create user5 identified by user5 profile res;
    
    //2 将默认的概要文件修改为指定的。为用户指定概要文件
    alter user user5 profile res;

### 3)删除概要文件 ###

drop profile 概要文件名称 cascade //级联关系