SQL大全－－－－－－之oracle

delete 删除一张大表时空间不释放，非常慢是因为占用大量的系统资源，支持回退操作，空间还被这张表占用着。

truncate table 表名  (删除表中记录时释放表空间)

DML 语句：

表级共享锁： 对于操作一张表中的不同记录时，互不影响

行级排它锁：对于一行记录，oracle 会只允许只有一个用户对它在同一时间进行修改操作

wait()   等到行级锁被释放，才进行数据操作

drop一张表时也会对表加锁，DDL排它锁,所以在删除一张表时如果当前还有用户操作表时不能删除表

alter table 命令用于修改表的结构(这些命令不会经常用)：

增加约束：

alter table  表名 add   constraint 　约束名  primary key  (字段）;

解除约束：(删除约束)

alter  table 表名  drop  primary  key（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）

alter  tbale   father   drop  primary key    cascade ;  (如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）

alter table  表名 drop  constraint   约束名;

(怎样取一个约束名：1、人为的违反约束规定根据错误信息获取!

                                 2、查询示图获取约束名!)

alter  table   表名  disable    from   primary  key ;  (相当于把一个表的主键禁用)

alter  table   表名  enable    primary key ;（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable）

 

*******************************************************************

增加字段：

 alter  table 　　表名　　　add(字段字  字段类型)

删除字段：

 alter table 　　表名     drop(字段)

 alter tbale         表名    drop    column   字段 ; (8i 以后才支持）

给列改名:920才支持

 alter  table   表名   rename   column   旧字段名    to     新字段名;

修改字段

(此时应注意的问题，更改时要看具体值情况之间的转达换，　改为字符类型时，必须要为空)

 alter  table   　表名     modify( 字段，类型)

更改表中的字段：

 update 表名   set     字段     ＝      值     where       条件

更改表名

 rename       旧表名           to 　  　新表名           ；

删除表：

 trucate   table    表名:(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表）

 

关于oralce中产生序列(sequence):

create sequence   序列名alter system  flush   shared_pool;

(不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，序列会出现不连续的动作回退操作不会影响序列取值）

sequence 的参数：

 increment by  n 起始值,    start with  n 递增量, maxvalue  n 最大值,  minvalue n  最小值，cycle | no cycle 轮回，  cache n  绶存(第一次取时会一次取多少个id存起来）

查看   sequence 示图：

desc    user_sequences ;

select   sequence_name , cache_size , last_number  from  user_sequences   where   sequence_name  like 's_';

select  序列名.currval  from   dual    查看当前的序列数

select  序列名.nextval  from   dual    查看下一个序列数，它会自动给当前的序列加１

为列：nextval          currval

(开另一个session时取当前值不成功时，应该先取下一个值，再取当前值)

清空当前会话的内存：

alter system  flush   shared_pool;（执行此命令要有DBA权限，一般用户执行出错）

修改序列：(此命令不常用，只需了解就行不必深究）

alter  sequence  序列名  修改项；

删除序列sequence

drop  sequence 序列名;

创建示图： creating      views（属于了解知识）

desc  user_views;

select   text   from  user_views    where   view_name='TEST1_V1' ;

示图就相当于一条select 语句,定义了一个示图就是定义了一个sql语句,示图不占空间,使用view 不会提高性能，但是能简单化sql语句

（扩展知识： oracle  8i 以后的新示图）MV   物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时性，但是存在刷新问题， 主要应用在数据仓库中用要用于聚合表）

使用示图的好处：控制数据访问权限.

如何创建一个示图：

create   or replace   views   test_vi    as       select       *   from    test1   where c1=1;

此时往表test1（base   table  基表）中插入数据时：表中没能变化，示图中的数据发生改变

从示图中插数据时相对应的表会发生改变:

往示图中插数据时，会直接插进基表中，查看示图中的数据时，相当于就是执行创建时的select语句。

简单示图：能进行DML操作。

复杂示图：来源于多张表，不能执行DML操作。

关于rownum：

rownum  有个特点要么等于1 要么小于某个值， 不能直接等于某个值, 不能大于某个值。rownum常用于分页显示。

练习：查询出第5条数据和第10条数据之间：

 select   first_name , rnum    from   (  select   rownum   rnum    , first_name   from    s_emp  where rownum <=10 )     where rnum  between 5  and  10 ;

分面显示：

SELECT * FROM (SELECT a.*, rownum r FROM   S_EMP  a  WHERE r between 5  AND  10 )；

练习：哪些员工的工资比本部门的平均工资高？

select   first_name  , salary   , avgsal     from  s_emp   e , ( select   dept_id  , avg (salary )   avgsal  from   s_emp  group  by dept_id )  a   where   e.dept_id =a.dept_id and e.salary > a.avgsal;

 在示图上加一个 with  check   option 就相当于给示图加上了约束

create    view    test_v  as  select   *  from   test  where c =1  with check option ;

同义词:相当于别名的作用(***只需了解***）系统自建的同义词：    user_tables

create  synonym    asd_s_emp   for    asd_0607.s_emp ;

目的就是为了给asd_0607_s_emp表起另一个代替的名称asd.s_emp;注意这个同义词只能自己使用;

create  public     synonym  p_s_emp  fro asd_0607.s_emp; 创建公共的同义词，但是要权限.

删除同义词：

drop  synonym    同义词名称

创建索引：  Creating    indexes(概念很重要对系统的性能影响非常大）

建索引的目的就是为了加快查询速度。

索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相对应的索引也会删除。truncate 表时索引结构在，但是数据不存在。

full   table    scan  全表扫描

用索引就是为了快速定位数据：(理解时就以字典的目录为例）

查看表的rowid：

select     rowid  , first_name    from  s_emp;

rowid 定义的信息有：  object   block  table

每条记录都有自己的rowid

索引由谁创建：用户,建索引后会使DML操作效率慢，但是对用户查询会提高效率,这就是我们建索引的最终目的，

创建一个索引：

create  index     索引名     on   表名 (  字段名）;

create   insex testindex  on test(c1, c2);

哪些字段应该建索引：

经常要用where的子句的地方，所以要用索引.用不用索引，关键要看所查询的数据与所有数据的百分比，表越大，查询的记录越少，索引的效率最高.

替换变量：用&符号来定义替换变量支持交互性提示,对于字符性的数字，一定要写在单引号之间

set    verify on

set    verify off;

相当于开关变量，用于控制是否显示新旧的sql语句

select   id ,last_name  ,salary   from s_emp  where  title='&job_title';

更改交互的提示信息：

accept  p_dname prompt ' 提示信息';

定义变量：

define     p_dname='abc';

分页的实现语句：(可以正常运行)

  select   *   from  (  select   rownum   rnum  , a.*   from   (select * from s_emp) a  )     where rnum  between 5  and  10 ;