Oracle创建和管理表

---

提示：以下是本篇文章正文内容，下面案例可供参考,来源于尚硅谷课件

Oracle中的数据类型

数据类型	描述
VARCHAR2(size)	可变长字符数据
CHAR(size)	定长字符数据
NUMBER(p,s)	可变长数值数据p是数值s表示保留的小数点
DATE	日期数据
LONG	可变长字符数据，最大可达2G
CLOG	字符数据，最大4G
RAW(LONG RAW)	原始二进制数据
BLOG	二进制数据，最大4G
BFILE	存储外部文件的二进制数据，最大4G
ROWID	行地址

表的创建Create

Create Table 语句

1. 必须具备：
   • CREATE TABLE 权限
   • 存储空间
2. 必须指定
   • 表名
   • 列名，数据类型，尺寸
3. 语法，示例

create table dept(
	deptno number(2),
	dname VARCHAR2(14),
	loc VARCHAR2(13)
)

使用子查询创建表

1. 复制表

     create table emp1 as select * from employees;

2. 只要结构不要内容

-- 子查询加一个永远不成立的条件
create table emp2 as select * from employees where 1=2;

修改表Alter Table

ALTER TABLE 语句追加, 修改, 或删除列的语法,对表结构进行修改

使用 ALTER TABLE 语句可以:

• 追加新的列

-- 追加一个列,列名为XX,类型是长度为10的VARCAHAR2
alter table emp1 add(
      XX VARCHAR2(10)
)

• 修改现有的列

-- 修改XX的数据类型长度为20,除了改类型长度也可以改类型
alter table emp1 modify(
      XX VARCHAR2(20)
)

• 为新追加的列定义默认值

-- 为列XX 添加默认值20
alter table emp1 modify(
      XX default '20'
)

• 删除一个列 ALTER TABLE TABLENAME(表名) DROP COLUMN COLUMNNAME(列名)

alter table emp1 drop column HH

• 重命名表的一个列名

-- 修改列名XX为ZZ
alter table emp1 rename column XX to ZZ

删除表

• 数据和结构都被删除
• 所有正在运行的相关事务被提交
• 所有相关索引被删除
• DROP TABLE 语句不能回滚

-- 删除之后不可以回滚,慎用
drop table emp1;

清空表

• TRUNCATE TABLE 语句:
  – 删除表中所有的数据,保留表的结构
  – 释放表的存储空间
  – TRUNCATE语句不能回滚
  – 如果需要可以回滚可以使用delete

-- truncate 
truncate table emp1;

-- delete
delete emp1;

--回滚
rollback;

约束

约束是表级的强制规定
有以下五种约束:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

注意事项

• 如果不指定约束名 ，Oracle server 自动按照 SYS_Cn 的格式指定约束名
• 创建和修改约束:
• 建表的同时
• 建表之后
• 可以在表级或列级定义约束
• 可以通过数据字典视图查看约束

表级约束

1. 作用范围
   列级约束只能作用在一个列上
   表级约束可以作用在多个列上（当然表级约束也
   可以作用在一个列上）
2. 定义方式
   列约束必须跟在列的定义后面，表约束不与列一起，而是单独定义

定义约束

1. 创建表时定义

create table emp2 (
id number(10) not null,
name varchar2(5)
)

• 查看表结构 desc

Name Type        Nullable Default Comments 
---- ----------- -------- ------- -------- 
ID   NUMBER(10)                            
NAME VARCHAR2(5) Y      

-- 可以看到Null able这个列的y已经没有了,表示不能为空,

为约束定义名称

• 这种方式约束的名称时系统命名的,如果想要自己命名可以使用 CONSTRAINT name

create table emp3 (
id number(10) CONSTRAINT emp_id_nn not null,
name varchar2(5)
)

约束的删除

-- 约束名如果定义时没起名字，会用系统默认的，以SYS_开头
Alter table t_sequence drop constraint 约束名;

UNIQUE 约束(唯一约束)

不允许一个列出现重复的字段

create table emp (
id number(10) unique,
name varchar2(5)
) 

PRIMARY KEY 约束(主键约束)

create table emp1 (
id number(10) CONSTRAINT pk_emp1 PRIMARY KEY ,
name varchar2(5)
) 

另外一种写法

create table emp1 (
id number(10) ,
name varchar2(5),
 CONSTRAINT pk_emp1 PRIMARY KEY(id)
) 

FOREIGN KEY (外键)约束

数据操纵

数据插入(INSERT INTO)

• 向表中插入单条数据,使用这种语法一次只能向表中插入一条数据

-- 后面不写列名,values的值必须和emp每一个列对应
insert into emp1
values(7369,'SMITH','CLERK',7902,to_date('1980/12/17','yyyy/MM/dd'),800,null,20)

-- 插入指定列,数据类型为varchar2或者char values的值要用单引号
insert into emp1(empno,job)
values(7499,'ALLEN')

• insert 插入多条数据

insert all 
into emp1(empno,ename,job) values(7369,'SMITH','CLERK')
into emp1(empno,ename,job) values(7499,'ALLEN','SALESMAN')
select * from dual

• 子查询插入数据

-- 不必书写 VALUES 子句。 子查询中的值列表应与 INSERT 子句中的列名对应
insert into emp1 select * from emp

数据修改(UPDATE)

• 使用UPDATE语句更新数据,可以一次更新多条数据
  语法: update 表名 set column = value where

-- 不加where 更新全表
update emp1 set sal=1600,job='ABC',comm=4000 where empno = 7369

• 更新使用子查询

update emp1 set job =(select job from emp where empno = 7499 ) where empno = 7499

数据删除(DELETE)

语法

delete from table where

示例:

-- 如果省略where所有的数据都被删除
delete from emp1 where empno = 7369

-- delete也可以使用子查询,和添加一样

数据库事务

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态

以第一个 DML 语句的执行作为开始

• 以下面的其中之一作为结束:
• COMMIT 或 ROLLBACK 语句
• DDL 语句（自动提交）
• 用户会话正常结束
• 系统异常终止

使用COMMIT 和 ROLLBACK语句,我们可以: 确保数据完整性。数据改变被提交之前预览。将逻辑上相关的操作分组

使用 SAVEPOINT 语句在当前事务中创建保存点
使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点

视图

概念：

从表中抽出的逻辑上相关的数据集合

1. 是一种虚表
2. 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
3. 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
4. 视图向用户提供基表数据的另一种表现形式

视图分为简单视图和复杂视图，他们的区别是

特性	简单视图	复杂视图
表的数量	一个	一个或者多个
函数	没有	有
分组	没有	有
DML操作	可以	有时不可以

改变视图也会改变表，所以当视图的某些字段是通过函数计算得出的结果，就不能修改视图

视图的创建

CREATE VIEW

-- 利用子查询创建视图
create view vw_empdept as 
(
select e.ename ename,e.job job ,e.mgr mgr , e.sal sal ,e.comm comm,d.dname 
from emp e 
join dept d 
on e.deptno = d.deptno
)

视图的查询和查询表一样，select * from vw_empdept

视图的修改

-- 视图的修改会把原先的视图丢弃
create or replace view vw_empdept as (
   select * from emp
)

视图中使用DML的规定

这几个操作和对表的操作时一样的

当视图定义中包含以下元素之一时不能使用delete:

1. 组函数
2. GROUP BY 子句
3. DISTINCT 关键字
4. ROWNUM 伪列

当视图定义中包含以下元素之一时不能使用update:

1. 组函数
2. GROUP BY子句
3. DISTINCT 关键字
4. ROWNUM 伪列
5. 列的定义为表达式

当视图定义中包含以下元素之一时不能使insert:

1. 组函数
2. GROUP BY 子句
3. DISTINCT 关键字
4. ROWNUM 伪列
5. 列的定义为表达式
6. 表中非空的列在视图定义中未包括

可以使用 WITH READ ONLY 选项屏蔽对视图的DML 操作 ，在创建或者修改视图时添加，视图就不可以

create or replace view vw_empdept as (select * from emp)
with read only

Top-n 分析(分页)

每一个表都有一个rownum字段

例子：查询工资在第5到第8的

select * from (

	-- 将排序好的字段当作一个表，把rownum作为新列
	select rownum ru,e.* from
	(
		-- 先排序
  		select * from emp order by sal desc
	)e
) where ru between 5 and 8

这样就可以对他们进行分页，计算，，原先的ROWNUM只能使用<或<=,使用其他的都不返回数据

序列

由于Oracle没有只增ID，一般使用序列来产生唯一值

什么是序列

1. 可供多个用户用来产生唯一数值的数据库对象
2. 自动提供唯一的数值
3. 共享对象
4. 主要用于提供主键值
5. 将序列值装入内存可以提高访问效率

序列的创建

公式：

CREATE SEQUENCE sequence -- 序列名称
       [INCREMENT BY n]  --每次增长的数值
       [START WITH n]    --从哪个值开始
       [{MAXVALUE n | NOMAXVALUE}] -- 最大值
       [{MINVALUE n | NOMINVALUE}] -- 最小值
       [{CYCLE | NOCYCLE}]     --是否需要循环
       [{CACHE n | NOCACHE}];  --是否缓存登录

例子：

CREATE SEQUENCE emp_empno_seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOMINVALUE
NOCYCLE
NOCACHE

使用：

如果使用insert all 批量插入，序列的值是同一个

insert into emp values(emp_empno_seq.nextval,'ZZ','PRESIDENT',null,sysdate,300,300,10);

序列中的NEXTVAL 和 CURRVAL 伪列

1. NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
2. CURRVAL 中存放序列的当前值
3. NEXTVAL 应在 CURRVAL 之前指定 ，否则会报CURRVAL 尚未在此会话中定义的错误。

序列的查询

使用下列语句可以查询序列定义的信息

SELECT  * FROM  user_sequences

序列的修改

修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存

例子：

ALTER SEQUENCE emp_empno_seq
               INCREMENT BY 20
               MAXVALUE 999999
               NOCACHE
               NOCYCLE;

如果需要改初始值，还是把当前序列删了重新创建一个吧

序列的删除

使用 DROP SEQUENCE 语句删除序列

DROP SEQUENCE dept_deptid_seq;

序列不连续（裂缝）

序列在下列情况下出现裂缝:

• 回滚
• 系统异常
• 多个表同时使用同一序列
• 如果不将序列的值装入内存(NOCACHE), 可使用表 USER_SEQUENCES 查看序列当前的有效值

索引

什么是索引

• . 一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
• . 索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
• 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引
• 在删除一个表时,所有基于该表的索引会自动被删除
• 通过指针加速 Oracle 服务器的查询速度
• 通过快速定位数据的方法，减少磁盘 I/O

如何创建索引

创建索引的方式

• 自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
• 手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询

手动创建

在一个或多个列上创建索引

公式：

例子:
在表 EMP 的列 SAL上创建索引

create index emp_sal_idx on emp(sal)

什么时候应该创建索引

以下情况可以创建索引:

• 列中数据值分布范围很广
• 列经常在 WHERE 子句或连接条件中出现
• 表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%

索引的查询

可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息

例子:

	SELECT	ic.index_name, ic.column_name,
		ic.column_position col_pos,ix.uniqueness
	FROM	user_indexes ix, user_ind_columns ic
	WHERE	ic.index_name = ix.index_name
	AND	ic.table_name = 'EMP'```

索引的删除

公式:

DROP INDEX index;

例子:

DROP INDEX emp_sal_idx