Oracle-SQL系列知识点(三)

2016-10-22 本文已影响251人 GuaKin_Huang

Oracle-SQL

这是对Oracle-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来访问序列