让我们高举程序主义,软件工程思想的伟大旗帜,不分昼夜地编程,把建设有中国特色的软件事业全面推向21世纪....
*************************************
** oracle 学习笔记第二天 **
** author Ice Xu (XuBin) **
** date 2006-11-01 **
*************************************
不给约束起名字时，系统给约束起名时的规律为：数据库用户名_数字(约束名也不能重名）
定义一个约束的两种形式：
列级约束 表级约束
非空约束：
not null (利用desc可能看到）primary key 自动具有非空约束的特点
primary key约束：
主键约束的定义：
第一种定义形式：
create table test(c number primary key ); 列级约束
第二种定义形式：
create table test(c number , primary key(c) ) ; 表级约束
create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
foregin key (fk) 外键约束:
(先定义父表，再定义子表）
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
check 约束：
create table test(c1 number check(c1>1000));
此表中要求c1的值必须要大于1000 才为有效值 .
怎么创建一个角本文件： xxx.sql结尾
执行角本的方法：
在sqlplus环境中执行：@filename.sql
在shell环境中执行： sqlplus nanjing/nanjing @filename.sql
创建表的语法：
create table 表名 ( 字段名 字段类型 约束类型（可选））；
利用已知表建一张新表：注会把非空约束带过来，其它约束要自己添加
create table s_emp_42 as select * from s_emp where dept_id = 42;
只取要表结构，不想要表中数据的建表方式：
create table s_emp_copy as select * from s_emp where 1=2;
(这是一个小技巧,在JDBC的学习中会用到 where 1=1 的形式,注意体会）
查看一张表的约束：( 查数据字典示图)
desc user_constraints;(这个数据字典中会查到相应的信息）
select constraint_name, constraint_type from user_constraints where table_name='S_EMP';
P pk
R fk
C check
U UK
V 这种只定义在示图中(with check option 相当于组示图加了一个约束）
O 也是出现在示图中
非空约束和CHECK都是用C来表示
查看字段约束的方法：
desc user_cons_columns;
select column_name, position from user_cons_columns where constraint_name='S_EMP_ID_PK' ;
position 的含义：联合主键，约束名一样。
user_constraints user_cons_columns 两张表的约束名相等，表名相等，两张表一关联就可以查出所需的信息。
select constraint_name , r_constraint_name from user_constraints where constraint_type='R' and table_name='S_EMP' ;
数据库建立时，数据字典就会建好。
user_constraints; 自己拥有的
all_constraints; 你自己拥有的加上你可以访问的
dba_constraints 所有的
查看当前数据库数据字典的字典（这个示图很重要）
desc dict;
select table_name form dict where table_name like '%cons%;
示图：
user_objects; user_tables;
select distinct object_type from user_objects;
介绍事务的概念：
commit 提交，此时说明前面所有语句都成功执行
rollback 回退操作，此时会恢复至上一次提交时的状态。
savepoint 设置保存点
注意 insert into 后面可以跟子查询
insert into s_emp_42 select * from s_emp where dept_id =42;
UPDATE 修改字段值：
update s_emp set dept_id =10 where id =2 ;
update s_emp set commission_pct =10 ; 没有where条件时说明是改表中所有的值.
注意：如有外键引用时常会出现外键引用值没有找到等错误？
delete 删除记录命令语法：
delete from s_emp where dept_id=42;
delete form s_emp ; 没有where条件时说明删除表中所有的值
注意：如有外键引用时，删除一张表时常会出现不能删除的情况，
原因一 是因为此时正在有人操作表中记录
原因二 此表有其他的表引用，没能设及联删除：
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 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';