Oracle基础学习之二—表

一、SQL语句分类

• 查询语句：select
• 数据操纵语句DML：面向数据库中的数据，INSERT、UPDATE、DELETE
• 数据定义语句DDL：面向对象的操纵，CREATE、ALTER、DROP、RENAME、TRUNCATE
• 事务控制数据：用于撤销或提交某些操作，COMMIT、ROLLBACK、SAVEPOINT
• 数据控制语句：用于控制数据的访问权限，GRANT,REVOKE

二、数据库的主要对象

表(TABLE)：数据库存储的基本单元,由行和列组成.
约束条件(CONSTRAINT)：用来确保数据库中数据的完整性
视图(VIEW)：一个或多个表的逻辑表示或虚拟表，主要用于简化查询操作
索引(INDEX)：用来加速数据访问的数据库对象。
序列(SEQUENCE)：用于生成唯一数字值的数据库对象。
同义词(SYNONYM)：对象的别名。
另外数据库中还有存储过程、函数、 触发器、包等对象。

三、创建和管理表

3.1常用数据类型

3.2表的建立及相关操作

简单表的建立时，有时会直接指定约束，当然也可以在表之后建立约束。简单版：

CREATE TABLE 表名称(
字段名称 1 字段类型 [constraint],
字段名称 2 字段类型 [constraint],
....
字段名称 n 字段类型 [constraint]
)

利用子查询建表：

CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE 1=1;  —-会复制表结构+数据
CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE 1=2;  —-仅仅复制表结构

表的重命名：
RENAME后不需要跟什么对象类型，应为不需要，不关你是什么数据库对象，直接改名就好了，不需要提供对象类型。

RENAME TABLE_OLD_NAME TO NEW_NAME

截断表： DELETE删除表数据往往可以进行回滚，如果希望删除表中所有的数据行且不能恢复，但保留表的结构，且不触发（运行） 表的删除触发器，则使用截断表：

TRUNCATE TABLE 表名

DELETE和TURNCATE
表的删除：

drop table 表名称

Oracle 10g 版本以后有了闪回技术，回收drop后的东西，即对象，而不针对于数据的delete和turncate。用户所删除的表默认情况下在一个回收站之中保存，而用户也可以通过回收站进行表的恢复，所以此技术称为闪回（FLASHBACK）。

查看回收站： SHOW RECYCLEBIN
恢复表的语法：
FLASHBACK TABLE 表名称 TO BEFORE DROP

可以直接删除掉回收站中的一些数据表，语法如下：

PURGE TABLE 表的名称

当然可以直接清除回收站：

PURGE RECYCLEBIN

如果现在希望删除一张表，而又不希望其进入到回收站之中，则可以在删除的时候增加PURGE:

DROP table 表名 PURGE

3.3表的结构修改

• 在一个表中加入一个新的列：

 ALTER TABLE 表名 ADD(列名数据类型 [DEFAULT 默认值]，
列名数据类型 [DEFAULT 默认值]....

表一般不建议扩充列，因为可能会带来未知错误，一般独立建立新表并进行主外键关联.

• 修改已存在的列：

ALTER TABLE 表名 MODIFY(列名数据类型 [constraint]，
列名数据类型 [constraint]， ……

• 在一个表中删除一列：

ALTER TABLE 表名 DROP COLUMN 列名

• 主键添加：

#建表后添加
alter table <表名> add [constraint <主键名称>] primary key(主键列名)

• 外键添加：

alter table <表名> add [constraint <外键名称>] foreign key(字段名) references <表名>（字段名）

注：constraint是用来指定约束名的，可以不写，此时约束名有系统默认匹配。因此它可以在任何添加约束的地方进行约束名添加。如：字段约束前，表约束前，modify后的字段约束中。

3.4表数据结构修改

• 增加记录：

INSERT INTO tablename [(column, column, ….)]VALUES (value, value, ….)

该命令每次只能增加一条记录，多记录参考，字符串和日期必须加单引号。

INSERT INTO EMP （EMPNO,NAM,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO）
VALUES (7568,’MASON’,’ANALYST’,7566,
TO_DATE(‘24/06/2084 9:30’,’DD/MM/YYYY HH:MI’),
3000,NULL,20)
#增加子查询数据
INSERT INTO table[(column, column, ….)]
SELECT select_list
FROM table_name

注：除表中数据严格区分大小写外，表名，字段名，关键字一般不需要双引号，自动全部定为大写。当有大小也区分要求时，可用双引号进行大小写区别。单引号表示字符串；双引号之中的表示字段或者表名；参考

• 更新记录

UPDATE table[alias]
SET (column1,column2,...) = {expression, subquery}
[WHERE condition]
#例子：
UPDATE EMP
SET JOB =’SALESMAN’,
HIREDATE = SYSDATE,
SAL = SAL*1.1WHERE ENAME = ‘SCOTT’;

一般都要写where条件，否则进行全表更新，也就是不满足的记录会直接在对应字段赋空值。

• 删除某条记录：

DELETE FROM table_name [WHERE condition]

3.5表约束的创建和管理

在数据库表的开发中，是必不可少的支持。使用约束可以更好的保证数据库中的数据完整性。
约束一般可以分为表约束和字段约束。

• 主键约束 primary key：唯一且不为空

#建表字段中字段约束
CREATE TABLE MY_TABLE(
CODE INT [CONSTRAINT 约束名] PRIMARY KEY,...)
#字段之后添加表约束
CREATE TABLE MY_TABLE(
字段名称 1 字段类型 [constraint],
字段名称 2 字段类型 [constraint],
....
字段名称 n 字段类型 [constraint],
[CONSTRAINT 主键名称] primary key(字段名))
#建表后修改：
alter table <表名> add constraint <主键名称> primary key(主键列名)

键在理论上可以不存在，但实际中规范中必须至少有一个主键，主键多个时，其余为副键。

• **外键约束foreign key：**外键起到约束的作用，插入非空值时，如果主键表中没有这个值，则不能插入；更新时，不能改为主键表中没有的值；删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除；更新主键记录时，同样有级联更新和拒绝执行的选择。

#建表后添加约束：
alter table <表名> add constraint <外键名称> foreign key(外键列名) references <主表名>（主键列名）

alter table <表明> modify(字段 [constraint 约束名] references 表名(字段名))

#建表时表约束：
CREATE TABLE MY_TABLE(
字段名称 1 字段类型 [constraint],
字段名称 2 字段类型 [constraint],
....
字段名称 n 字段类型 [constraint],
[CONSTRAINT 外键名称] foreign key(字段名) references table_name(字段名))

#建表时字段约束：
CREATE TABLE MY_TABLE(
字段名称 1 字段类型 [CONSTRAINT 约束名]references <表名>(字段名),
.....)

当没有指定参照字段时，默认参照字段是父表的主键

• 唯一约束unique：在一个表中只允许建立一个主键约束，而其他列如果不希望出现重复值的话，则可以使用唯一约束。指定一个字段或者字段的集合为唯一键。在表中没有两行具有相同的值，例如 email。
  如果唯一键是基于单条记录的， NULL 是允许的。

#建表时的字段约束：
CREATE TABLE TABLE_NAME(
字段名 类型 [CONSTRAINT 约束名] UNIQUE，
...);
#建表时的表约束：
CREATE TABLE TABLE_NAME(
字段名 类型，
...，
[CONSTRAINT 约束名] UNIQUE (Column1, Column2, …));
#建表后：
ALTER TABLE TABLE_name MODIFY(字段名 [CONSTRAINT 约束名] UNIQUE) 

• NOT NULL 约束：只有字段约束，NOT NULL 约束保证字段值不能为 NULL。没有 NOT NULL 约束的字段，值可以为 NULL。

#建表时的字段约束
CREATE TABLE TABLE_NAME(
字段名 类型 [CONSTRAINT 约束名] NOT NULL，...)
#修改：
ALTER TABLE TABLE_NAME MODIFY(字段名 [CONSTRAINT 约束名] NOT NULL)

• **检查约束：**CHECK 约束定义了每条记录必须满足的条件， 是为表中数据增加一些过滤条件。

#建表字段约束：
CREATE TABLE table_name ( column_name datatype [CONSTRAINT 约束名] CHECK(expressions)) ;
#建表时表级约束
CREATE TABLE table_name(
column_name 类型,
[CONSTRAINT 约束名] CHECK(expressions) );
#建表后
ALTER TABLE 表名 ADD CONSTRAINT constraint_name CHECK(expressions) 

ALTER TABLE 表名 MODIFY(字段 [CONSTRAINT 约束名] CHECK(expressions))

3.6级联删除和级联事件

参考

3.7约束查询查询约束

参考

select constraint_name,constraint_type,search_condition
 from user_constraints
 where table_name = '注意大小写问题'

在 Oracle 之中所有的对象都会在数据字典之中保存，而约束也是一样的， 所以如果要想知道有哪些约束，可以直接查询”user_constraints”数据字典。但是这个查询出来的约束只是告诉你名字，而并没有告诉在哪个字段上有此约束，所以此时可以查看另一张数据字典表”user_cons_columns”.

3.8约束的禁用、启用、删除

#禁用约束
ALTER TABLE 表名 DISABLE CONSTRAINT 约束名
#启用约束
ALTER TABLE 表名 ENABLE CONSTRAINT 约束名
#删除约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名
#增加约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型（字段）

可以发现，如果要维护约束，肯定需要一个正确的名字才可以，可是在这五种约束之中，非空约束作为一个特殊的约束无法操作。注意：跟表结构一样，约束最好也不要修改，而且要记住，表建立的同时一定要将约束定义好，以后的使用之中建议就不要去改变了。

注：禁用了外键依然不能级联删除。