Oracle-SQL

这是对Oracle-SQL知识点详细介绍的文章系列，其他文章如下：

• Oracle-SQL系列知识点(一)
• Oracle-SQL系列知识点(二)
• PL/SQL基础知识详解

创建和维护表

数据定义语言： (Data Definition Language, DDL) 是SQL语言集中负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成。

数据库对象，是数据库的组成部分，有表、约束、索引、视图、序列、同义词、触发器、存储过程、函数等。

1477124936374.png

• Oracle中数据库对象命名原则

  • 必须由字母开始，长度在 1–30个字符之间。
  • 名字中只能包含 A–Z, a–z, 0–9, _ (下划线)，$ 和 #。
  • 同一个Oracle服务器用户所拥有的对象名字不能重复。
  • 名字不能为Oracle的保留字。
  • 名字是大小写不敏感

• 创建表

1477125022997.png

DEFAULT 选项

1、指定在插入或更新数据时，列的默认值
2、合法的值可以是字面值、表达式或SQL函数
3、非法的值是另一个列的名称或虚拟列
3、默认值的数据类型必须和列的类型匹配

1477125255314.png

用子查询语法创建表

1477125282945.png

• 修改表

添加列语法
不能指定新添加列的位置，新列会成为最后一列

1477125348376.png

修改列语法：

1477125355501.png

删除列语法：

1477125360917.png

• 数据类型

1477125128326.png

1477125144633.png

NUMBER(p,s)：数值型：总长度为p，小数位最大为s位 ，整数位最大为p-s位，p的范围从1到38，s的范围从-84到127。

S>0：只能表示小数，精确到小数点右边s位，并四舍五入，然后检验有效数位是否<=p；

S<0：只能表示整数，并且精确到小数点左边s位，进行四舍五入，然后检验有效数位是否<=p+|s|；

S=0：只能表示整数；

p<s：只能表示数字是绝对值小于1的数字，且从小数点右边开始的前s-p位必须是0，保留s位小数。 

数据类型(日期型)

1477125201654.png

数据类型（图片类型）

1477125232035.png

• 修改已存在的列

列的修改可以修改列名，数据类型，长度，及默认值。
修改数据类型：已有的行数据必须为空。

修改长度原则：

• 数值型修改长度：当长度向小改时，已有行的数该列必须为空；当长度向大改时，可以随意修改。

• 字符型修改长度：当长度向小改时，只要修改后的值能容纳下当前已有数据的最大值即可，当长度向大改时，可以随意修改。

• 修改列的默认值：

• 默认值的修改不会影响已经存在的行，只影响新增加的行。

• 8i版本之后，可以修改列名字
  ALTER TABLE 表名 RENAME COLUMN 原有列名 TO 新列名 。

• 删除列

可以用DROP子句从表中删除列，包括列的定义和数据。

删除列原则：
1、列可以有也可以没有数据。
2、表中至少保留一列。
3、列被删除后，不能再恢复。
4、被外键引用的列，不能被删除。

删除列语法一

1477125657372.png

删除列语法二

1477125650819.png

• 删除表

删除表语法：

1477125716469.png

只有表的创建者；
或具有DROP ANY TABLE权限的用户才能删除表删除表原则：
表中所有的数据和结构都被删除。
任何视图和同义词被保留但无效。
所有与其相关的约束和索引被删除。
任何未完成的事务被提交。

• 重命名表

1477125776634.png

• 截断表

1477125795814.png

TRUNCATE和DELETE区别

TRUNCATE是DDL，只能删除表中所有记录，释放存储空间，使用ROLLBACK不可以回滚。

DELETE是DML，可以删除指定记录，不释放存储空间，使用ROLLBACK可以回滚。

约束

约束： Constraint，是定义在表上的一种强制规则。

当为某个表定义约束后，对该表做的所有SQL操作都必须满足约束的规则要求，否则操作将失败；

• 约束类型

1477131247295.png

除了NOT NULL约束外，建议给其它约束起名字，命名规则为：表名列名约束类型。

• 定义约束语法

1477131308551.png

• 追加约束

1477131376651.png

追加FOREIGN KEY约束

1477131404495.png

追加 NOT NULL 约束

1477131432893.png

删除约束

1477131477131.png

DROP子句的CASCADE选项导致任何与其相依赖的约束也被删除；

约束禁用

如果有大批量数据导入时，我们可以采用禁用约束的方法，主要的好处，首先效率高，另外有主外键约束的表之间导入时，不用考虑导入的先后顺序。

禁用约束语法：

1477131534521.png

启用约束语法：

1477131550511.png

视图

视图是逻辑上来自一个或多个表的数据集合；

• 为什么使用视图

1、限制其它用户对数据库表的访问,因为视图可以有选择性的显示数据库表的一部分；
2、容易实现复杂的查询；
3、对于相同的数据可以产生不同的视图；

• 视图分类

视图分为简单视图和复杂视图，最基本差别在DML操作上

1477131805579.png

• 创建视图

1477132359837.png

**OR REPLACE**：如果所创建的视图已经存在，该选项表示修改原视图的定义；

**FORCE**：不管视图所基于的基表是否存在，都会创建该视图；

**NOFORCE**：只有视图所基于的基表都存在，才会创建该视图；

**viewname** ：视图的名称；

**column** ：列名，列名的数量必须和视图所对应查询语句的列数量相等；

**subquery**：一条完整的SELECT语句；

**WITH CHECK OPTION**：一个约束条件，通过视图所插入或修改的数据行必须满足视图所定义的查询； 

**constraintname**：约束名；

**WITH READ ONLY**：确保在该视图上不能进行任何DML操作；

如果要确保在视图上执行的DML操作仅限于一定的范围，便可使用WITH CHECK OPTION子句；

在视图定义时使用WITH READ ONLY选项，可以确保不能对视图执行DML操作；

• 删除视图

删除视图并不会删除数据，因为视图是基于数据库中的基表的虚表。

1477132532210.png

序列、索引、同义词

• 序列

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

通常可以使用序列自动地生成主键值。

创建序列：

1477132747986.png

sequencename:序列对象的名字

INCREMENT BY n：序列连续两个值之间的间隔n，默认为1。

START WITH n ：序列起始值n，该项省略，起始值为1

MAXVALUE n ：序列最大值；NOMAXVALUE ：指定序列无最大值

MINVALUE n ：序列最小值；NOMINVALUE ：指定序列无最小值

CYCLE|NOCYCLE：表示序列在达到最大值或最小值之后是否继续产生序列值，NOCYLE表示不再产生，NOCYLE是默认选项。

CACHE n|NOCACHE:表示序列值被服务器预先分配并存储在内存中，NOCACHE表示不预先分配并存储，CACHE 20是默认选项

序列属性：

CURRVAL：表示序列返回的当前值；
NEXTVAL：表示序列返回的下一个值；

通常应用在INSERT 语句的VALUES子句中

CURRVAL在被引用之前，必须先使用NEXTVAL来产生一个序列值；

可用语句 序列名.CURRVAL或 序列名.NEXTVAL来访问序列

• 修改序列

1477133111203.png

修改序列的语法没有START WITH子句![Alt text]

• 删除序列

1477133162658.png

• ROWID

是一个伪列，系统自动产生。

ROWID能唯一标示每一条数据库行记录的物理地址，通过 ROWID 能快速定位到一条行记录。

• 同义词

是指向数据库对象（如：表、视图、序列、存储过程等）的数据库指针

使用同义词的好处：

可以简化对数据库对象的访问;
方便对其他用户表的访问;
简化过长的对象名称;
节省大量的数据库空间，对不同用户的操作同一张表没有多少差别;
扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;
同义词可以创建在不同一个数据库服务器上，通过网络实现连接;

• 创建同义词

1477133337397.png

• 删除同义词

1477133367010.png

用户、权限与角色

用户是数据库的使用者。

【用户一般是由DBA来创建和维护的，创建用户后，用户不可以执行任何Oracle操作（包括建立会话），只有赋予用户相关的权限，用户才能执行权限允许范围内的操作】

• 创建用户
  对于新建用户，默认情况下没有创建对象的权限；

1477134515271.png

deafult tablespace:用户的默认表空间;
temporary tablespace: 用户的临时表空间;
quota on :表示允许该用户在表空间中使用的空间大小;
执行该语句的用户需要有“创建用户”的权限，一般为系统的DBA用户。

• 修改配额

1477134642117.png

• 修改密码

1477134689952.png

• 用户状态

OPEN：正常状态，为用户帐号初始创建后状态。
EXPIRED：密码过期状态，用户下次登录的时候需要修改密码；
LOCKED：锁定状态，不能执行任何Oracle相关操作

1477134743716.png

• 删除用户

1477134765402.png

CASCADE表示系统先自动删除该用户下的所有对象，然后再删除该用户的定义。

已经登录的用户是不允许被删除的

• 权限概述

数据库用户要想在数据库上执行任何操作，必须首先要拥有权限，包括建立会话

• 权限分类

系统权限：允许用户在数据库中执行指定的行为，一般可以理解成比较通用的一类权限

对象权限：允许用户访问和操作一个指定的对象，该对象是一个确切存储在数据库中的命名对象

• 系统特权

SYSOPER：启动停止数据库，恢复数据库等；
SYSDBA：所有SYSOPER功能的管理权限；创建数据库等权限；

• 授予系统权限

1477135021258.png

sys_priv_list：系统特权列表，由逗号分隔;
user_list: 用户列表，由逗号分隔;
WITH ADMIN OPTION：允许权限的接受者再把此特权授予其他用户

• 回收权限

1477135079038.png

注意：使用 WITH ADMIN OPTION 选项授予的权限，在回收时候的回收策略如下：

如果A授予权限给B，B又把该权限赋予给C ，如果此时A把权限从B处收回，那么B给予出去的权限是继续保留，即C继续拥有该权限。

• 授予对象权限

1477135132372.png

• 角色

角色是权限的集合；

1477135173998.png

• Oracle-SQL系列知识点(一)
• Oracle-SQL系列知识点(二)
• PL/SQL基础知识详解