Oracle 对象-如何操作

Oracle对象——增删查改
1、增删查改的简单操作介绍

--插入数据
Insert into 表名 (列名) values (值列表)；
Insert into students （sno，sname，sid）values （2，’tom’，22）；

Insert into 表名 values （值列表）；
insert into myuser values(1,'张三','123');

插入数据的注意事项：
1、 每插入一行数据，插入的列和插入的值的数量要一致 --– 完整性
2、 列名的类型和要插入值类型要对应 ---  准确性
3、 不能为标识列指定值，因为它的数字是自动增长的
4、 不许为空的列必须插入数据
5、 具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值

--更新数据
Update <表名> Set <列名 = 更新值>  [Where <更新条件>]

--删除数据
Delete From <表名> [Where <删除条件>]

Delete from表名     ----   删除表中的所有数据
Drop table 表名      ----    删除表

Truncate Table <表名>    清空数据
截断，效率更高，不记录在日志文件中，也将不可恢复。


--查询emp表
select * from emp;

select 列名 from 表

--查询部分行数据
Select 列名 from 表名  where 查询条件

--别名查询
select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno;


Commit  提交数据，将事务做的修改提交到数据库

Rollback 回滚事务过程中的任何修改


2、数据库对象

--赋权限
Grant 权限（select，insert，update）[ on 表名 ] to 用户；
--删除权限
Revoke 权限 [on 表名] from 数据库用户

-- 创建表
Create table user
(
Uid number(10)  not null ,
Uname varchar2(20) ,
Upwd varchar2(20)
)
--创建表空间 
Create tablespace USERS
Temporary  storage -- 临时存储空间
  (
initial 64K,      
next 64K ,
    minextents 1, 最小长度
    maxextents unlimited, 最大长度  没有限制
    pctincrease 0          增长百分比
  );


1.同义词：现有对象的一个别名
作用：
简化SQL语句
隐藏对象名称和所有者

私有同义词：只有建立该同义词的用户可以访问
Create synonym 别名 for 表名；
Creat synonym e for scott.emp ;

公有同义词：可被所有的数据库用户访问
Create public synonym 别名 for 表名；
Create public synonym e for scott.emp ;

创建和替换现有的同义词
Create or replace synonym e for scott.emp ; 

删除同义词
Drop synonym emp；
Drop public synonym emp；

2.序列 (一般做主键使用)
序列是用于生成唯一，连续序号的对象
有升序asc 和 降序 desc
创建序列（一般直接生成，不容易出错）
Create sequence seq_num ;  -- create sequence 序列名；
Start with 1   --start with 起始值
Increment by 20 --increment by 增量 （每次增长量为20）
Maxvalue 20  --maxvalue 最大值
Minvalue 1 --minvalue 最小值
Cache 10 --cache 缓存个数 （缓存10个，执行一次序列）
Nocycle ;  --nocycle (默认，不循环；)
（cycle 序列达到最大值，又从起始值开始执行)
通过序列的伪列来访问序列的值
Seq_num.NEXTVAL 返回序列的下一个值
Seq_num.CURRVAL 返回序列的当前值

--查询用户的数列
Select * from user_sequence；

Select * from user_sequence.currval ；

--使用数列 seq_num
create table seqtest(sid int,sname varchar2(10),age int);

insert into seqtest values(seq_auto.nextval,'ding',10)
或者
insert into seqtest(sid,sname) vlues(seq_auto.nextval,'ding');


--更改序列 (起始值不能修改)
Alter sequence seq_num
maxvalue
30 cycle ;  --更改序列的最大值，并设置循环

--删除序列
Drop sequence seq_num ;

3、视图
View 是一个虚拟表，只有表结构，没有数据，用户查询时通过表结构从原表中得到数据
视图一般用来查看数据，不是用来修改数据的
数据库开发时，有时视图和表是并行开发的。表还没开发好，创建一个错误的视图，在表创建后，错误视图自动变为正确视图

优点：
1. 提供了另外一种级别的表安全性，隐藏原有表名
2. 隐藏数据的复杂性
3. 简化用户的sql命令
4. 通过重命名列，给用户看到想给看的部分，将原有的表结构隐藏了

创建或代替原有视图
Create or replace view v_emp as select empno,ename,job from emp ;

-- 修改了列名
create or replace view vm_emp
as
select empno as salary,ename as name,job,
       (select dname from scott.dept where deptno=e.deptno) as dname
       from scott.emp e;

语法
Create [or replace] [from] view 视图名 as select 视图的列 from 表
[with check option] [with read only]

--查询视图
Select * from v_emp ;

--删除视图
Drop view v_emp ;

在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE
但是有如下限制：
1.只能修改一个底层的基表
2.不能违反基表的约束条件
3.如果视图中包含连接操作符、distinct关键字、集合操作符、聚合函数或者group by语句，则无法更新视图
4.视图中包含伪列或表达式，则无法更新视图

--键保留表
如果一个视图中包含一张表的主键列，那么这张表成为键保留表
创建错误视图：Create force view vv as select * from student ;
Student表中有主键：sid ；

4、索引 index 基于rowid 产生的
优点;提高sql查询语句执行的性能
减少磁盘I/O
在逻辑和物理上都独立于表的结构
Oracle自动维护索引

不足: 降低了增删改的效率

-- 创建索引
Create index 索引名 on表名（列名）
Tablespace 表空间名；
（一旦列中的数据发生改变，DBMS自动维护索引）
Create index item_index on item ;

--重建索引
Alter intdex item_index rebuild ;

--删除索引
Drop index item_index ;

1. 唯一索引：确保在定义索引的列中没有重复值
建立之后相当于该列多了一条约束性条件，没有重复值，但是可以为空，而唯一列的效率                比较低下（每次都要全部比较一下有无重复值），在实际开发中用的比较少

Create unique index item2 on item ;

2. 组合索引：常用在两列或多列上进行查询，比如 经常使用 empno，sal 作为查询条件

Create index ix_item on item（sid，sname）；

3方向索引 ：一般用在数值为主键的列上
一万条记录，前五十条被删除的比较多，可能造成索引树分布不均匀，反向索引是数值变成二进制，将二进制顺序颠倒，在对这个颠倒顺序的值进行索引构建
反向索引有可能解决索引树分布不均匀的问题

4、位图索引  适用于低基数列
Create bitmap index bit_index on student (sno);

不建立索引的情况：
1. 数据比较少，十万行以下
2. 不经常查询的列
3. 低基数列（低基数：重复值比较多，不同的值比表的行数少）