Oracle SQL笔记三
原文：http://samwong.iteye.com
一、复习
? 子查询里面不需要做order by
1.查询和smith处于同一工种的人
select first_name,title
from s_emp
where title in (select title from s_emp
???? where last_name = 'Smith')
and lash_name <> 'Smith';
或
select first_name,title
from s_emp
where title = any (select title from s_emp
???? where last_name = 'Smith')
and lash_name <> 'Smith';
where 后面如果返回为多值那么必需用=any 或in???
where后面如果是"="那么表示只能返回单值
2.查询哪些人是领导
select first_name form s_emp
where id in(select manager_id from s_emp);
3.查询哪些人不是领导
select first_name form s_emp
where id not in(select manager_id from s_emp
????????????? where manager_id is not null);?????????????? //如不加这一句,那么返回值为0;
用in能写出来的就能用表联接,用not in写出来的就能用外联接
4.哪个部门的工资是最低的
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary) = (select min(avg(salary))
??? from s_emp
?? group by dept_id)
能用连接就不用子查询???????????????????????????????????
http://samwong.iteye.com
二、约束
1、非空约束（not null）
? 这是一个列级约束
? 在建表时,在数据类型的后面加上 not null ，也就是在插入时不允许插入空值。
? 例：create table student(id number primary key,name varchar2(32) not null,address varchar2(32));
?
2、unique 唯一约束
??? 唯一约束，是会忽略空值的，唯一约束，要求插入的记录中的值是为一的。
??? 例：create table student(id number，name varchar2(32),address varchar2(32),primary key (id),unique (address));?
??? 如果创建一个uk，系统自动建一个唯一索引
?
3、 pk、uk
??? Oralce支持级联删除，不支持级联更新
4、check约束
??? 检查约束，可以按照指定条件，检查记录的插入。check中不能使用尾列，不能使用函数，不能引用其他字段。
例：create table sal (a1 number , check(a1>1000));
http://samwong.iteye.com
三、脚本
以.sql结尾的文件
运行脚本：? SQL> @脚本名
???????? shell> sqlplus 用户名/密码 @脚本名
???????
四、DML操作
1、insert操作，插入记录?
????
insert into 表名 values(值1，值2，......);
注意这种方法插入记录时，要对所有字段进行插入，没有非空约束时，又不想插入值时，要用空值替代，并且要按照字段的顺序插值（要清楚表结构），且要注意数据类型一致。
insert into 表名(字段名1，字段名2，.....) values(值1，值2，......);
这种方法可以对指定的字段进行插入，不想插值的就可以不写，前提是该字段没有非空约束。
例：insert into student value(1,'xxx','xxx');
?? insert into student(id,name,address) value(1,'xxx','xxx');
?
注意：有空值的话:
???? 隐式插入
???????? INSERT INTO s_dept (id, name) valueS(12, 'MIS');
???????? 不往想为空的字段中插数据，系统默认为NULL
???? 显示插入
???????? INSERT INTO s_dept valueS(13, 'Administration', NULL);
?
? select * from s_emp where 1=2;? 这样选不出纪录，方便察看表结构
?
2、 update修改操作
update table 表名 set? 字段名1=数据1或表达式1， 字段名2=数据2或表达式2
[where ....=....];
例：update shenfenzhen set num=99 where sid=2;
数据字典
数据字典是由系统维护的，包含的数据库的信息
数据字典示图
user_XXXXX 用户示图
all_XXXXX? 所有示图
dba_XXXXX? 数据库中所有示图
v$_XXXXX?? 动态性能示图
dist或 distionary 表示数据字典的数据字典。
user_constraints 用户的表中约束的表
其中有constraints_name字段存放的是约束名，r_constraints_name字段表示外键引用pk或uk的名字
这两个字段之间有自连接的关系，也就是约束名和外键约束名之间的自连接。
user_cons_column表，是用户的列级约束表
3、delete删除操作
delete from 表名 [where ...=...];
例：update shenfenzhen set num=99 where sid=2;
用delete操作删除的记录可以通过 rollback命令回滚操作，会恢复delete操作删除的数据。
delete 操作不会释放表所占用的空间，delete不适合删除记录多的大表。
delete操作会占用大量的系统资源。
五、事务
OLTP （ on line transaction procession ） 联机事务处理
数据库中操作的应是事务，而不是DML语句
事务是有生命周期的，commit；事务结束
系统中充满了并发的transation,每个连接是一个session，每个操作都是一个transaction
DDL、 DCL语句是自动提交的
sqlplus正常退出（exit），系统自动提交
上个事务的结束就是下个事务的开始
事务保证数据的一致性，保证原子操作的安全
一个没有结束的事务，叫做活动的事务 (active transaction),活动的事务中修改的数据，只有本会话才能看见。
read commited，只可以读取已经作提交操作的数据，本会话可以看到自己的所作的没有提交的操作。
??? 在活动事务中，当多个用户同时对同一张表进行操作时，会对表加上表级共享锁，当用户对操作该表某一条记录进行操作时会对该条记录加上行级排它锁，
??? 只允许一个用户对该条记录进行DML操作，只有提交操作commit;或回滚操作rollback;时，才可让其他用户操作对该记录进行DML操作，也就是释放了该条
??? 记录的行级排它锁。如果没有提交操作或回滚操作，那么该用户就不能对该条记录加锁，该用户的DML操作就会进入等待状态，但是在对表作drop操作（DDL操作）时，如果还有用户在操作该表，也就是没有释放表级共享锁，就会直接报错。
??? 事务越大，就会消耗更多的资源，并长时间持有事务会造成无法进行其他的操作，事物提交太频繁的话，会对I/O造成很大的负担，所以要合理确定事务的大小。
commit;? 提交操作，事物的结束
rollback;回滚操作，会将先前的活动事务中的操作（DML操作）的结果进行回滚，撤销全部操作，恢复成事务开始时的数据，也就是恢复成事务开始时的状态。
六、alter table命令
alter table 命令用于修改表的结构(这些命令不会经常用)：
增加字段：
alter? table 表名 add(字段字，字段类型)
删除字段：
alter tbale 表名 drop column 字段; (8i 以后才支持)
给列改名:9.2.0才支持
alter table 表名 rename column 旧字段名 to 新字段名;
修改字段
alter table 表名 modify( 字段，类型)
(此时应注意的问题，更改时要看具体值情况之间的转达换， 改为字符类型时，必须要为空)
not null约束是使用alter table .. modify (..,not null)，来加上的。
增加约束：
alter table 表名 add constraint [约束名] 约束(字段);
只能够增加表级约束。
解除约束：(删除约束)
alter table 表名 drop 约束;
（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）
alter table father drop primary key cascade;
(如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）
alter table 表名 drop constraint 约束名;
(怎样取一个约束名：
a、人为的违反约束规定根据错误信息获取!
b、查询示图获取约束名!)
使约束失效或者生效
alter table? 表名? disable from primary key;? (相当于把一个表的主键禁用)
alter table? 表名? enable primary key;
（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable）
更改表名
rename 旧表名 to 新表名;
删除表：
trucate table 表名;
(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表)
七、索引（index）
创建索引：Creating indexes(概念很重要对系统的性能影响非常大）
建索引的目的就是为了加快查询速度。
索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相对应的索引也会删除。索引是会进行排序。
truncate 表时索引结构在，但是数据不存在。
FTS? --- full table scan 全表扫描
用索引就是为了快速定位数据：(理解时就以字典的目录为例)
??? 创建索引就是创建key和记录的物理位置（rowid）组成的键值对。索引是有独立的存储空间，但是和表是逻辑关联的，索引和表的关系是依附关系，表被删除了，索引也没有存在的意义也就被删除了
在建表时会根据表中的PK或UK自动的建立唯一性索引。
查看表的rowid：
select rowid,first_name from s_emp;
rowid 定义的信息有：object block table
每条记录都有自己的rowid
索引由谁创建：用户,建索引后会使DML操作效率慢，但是对用户查询会提高效率,这就是我们建索引的最终目的。
创建一个索引：
create index 索引名 on 表名 (字段名);
create index 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';
八、关于oralce中产生序列(sequence)
create sequence? 序列名;
(不带参数时默认为从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 视图名;
视图就相当于一条select 语句,定义了一个视图就是定义了一个sql语句,视图不占空间,使用view 不会提高性能，但是能简单化sql语句
（扩展知识： oracle? 8i 以后的新视图）
MV? 物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时性，但是存在刷新问题，物化视图中的数据存在延迟问题，主要应用在数据仓库中用要用于聚合表)
使用视图的好处：控制数据访问权限。
如何创建一个视图的例子：
create or replace views test_vi as select * from test1 where c1=1;
or replace的意义，如果view存在就覆盖，不存在才创建。
force|no force ，基表存在是使用，不存在是则创建该表。
此时往表test1（base table 基表）中插入数据时：表中没能变化，视图中的数据发生改变
从视图中插数据时相对应的表会发生改变:
往视图中插数据时，会直接插进基表中，查看视图中的数据时，相当于就是执行创建时的select语句。
限制对数据库的访问，简化查询。
简单视图：来自于单表，且select语句中不能包括函数，能进行DML 操作。
复杂视图：来源于多张表，不能执行DML操作。
试图的约束
with read only 视图只读约束(O)
with check option 不允许插入与where条件不符的记录，类似于check约束的功能(V)
在select from 后也可以使用子查寻，这个写法也叫做内嵌视图
例：
select first_name,salary,avgsal from s_emp e,(select dept_id,avg(salary) avgsal from s_emp group by dept_id) s where e.dept_id=s.dept_id and e.salary>s.avgsal;
删除视图 drop views 视图名;
行号（rownum）
关于rownum：
rownum? 有个特点要么等于1 要么小于某个值， 不能直接等于某个值, 不能大于某个值。
rownum常用于分页显示。
rownum只用于读入内存的数据。
关于同义词：
同义词:相当于别名的作用(***只需了解***）系统自建的同义词:
user_tables
create synonym asd_s_emp for asd_0606.s_emp ;
目的就是为了给asd_0606_s_emp表起另一个代替的名称asd.s_emp;注意这个同义词只能自己使用;
create public synonym? p_s_emp? fro asd_0606.s_emp; 创建公共的同义词，但是要权限.
删除同义词：
drop? synonym??? 同义词名称
?
参考：
