Oracle day5 总结
删除表：
drop table 表名;
drop table 表名 cascade constraints; 级联删除
trucate table 表名;
(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表)
更改表名
rename 旧表名 to 新表名;
索引(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 最大值 nomaxvalue 定义的最大值
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 view 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只用于读入内存的数据。
找出工资前三名的员工
select first_name,salary
from (select first_name,salary from s_emp order by salary desc)
where rownum<=3;
列出每一个表的外键的定义，主表表名，主表字段名，子表表名，子表字段名 (画出E-R图)
select c.table_name,cc.column_name,
p.table_name,pc.column_name,
p.constraint_type
from user_constraints c,user_cons_columns cc,user_constraints p,user_cons_columns pc
where c.constraint_name=cc.constraint_name
and p.constraint_name=pc.constraint_name
and c.r_constraint_name=p.constraint_name
and c.table_name='S_EMP'
and c.constraint_type='R';
动态生成脚本
set head off
set feed off
set echo off
spool seletab.sql
select 'select * from '||table_name||';'
from user_tables;
spool off
12:57 浏览 (218) 评论 (0) 分类: oracle 2007-09-13
缩略显示oracle学习笔记(四)
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));
如果两个字段都为唯一且非空,这时可以定义成UK+NOT NULL
(PK或UK)一对多(FK)
(PK+UK)一对一(FK) 或 (PK)一对一(PK)
多对对多关系,一般都通过一张中间表来分解成两个一对多的表
非空约束是唯一一个可以用desc看到的约束
check 约束
create table test(c1 number primary key); 设置主键
create table test(c1 number constraints test_c1 primary key); 定义约束名,默认约束名为SYS_ 在列后面定义约束称为列级约束
create table test(c1 number primary key(c1)); 所有列定义完后再定义约束称为表级约束(能定义联合主键)
cretae table test(c1 number,c2 number,priary key(c1,c2)); 定义联合主键
create table test(c1 number primary key,c2 number unique); unique 唯一约束
create table test(c1 number primary key,c2 number not null unique); 一个字段上可以加多个约束
create table test(c1 number check(c1>100)); 约束c1的值大于100 ，列级约束
create table test(c1 number,check(c1>100)); 表级约束
create table child(c1 number primary key); 先要定义父表
create table child(c1 number primary key, c2 number references parent(c1)); 然后定义子表 references parent定义外键
create table child(c1 number primary key, c2 number references parent(c1) on delete cascade); on delete cascade为级联删除,删除父表时子表也被删除
create table child(c1 number primary key, c2 number references parent(c1) on delete set null); on delete set null删除后将外键置空
create table child (c1 number primary key, c2 number,foreignkey(c2) references parent(c1));
DML操作
online transaction procession OLTP 联机事务处理
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;
3、delete删除操作
delete from 表名 [where ...=...];
例：update shenfenzhen set num=99 where sid=2;
用delete 删除一张大表会花很长的时间。
用delete操作删除的记录可以通过 rollback命令回滚操作，会恢复delete操作删除的数据。
delete操作不会释放表所占用的空间，delete不适合删除记录多的大表。
delete操作会占用大量的系统资源。
事务
OLTP ( online transaction procession ) 联机事务处理
数据库中操作的应是事务，而不是DML语句
事务是有生命周期的，commit；事务结束
系统中充满了并发的transation,每个连接是一个session，每个操作都是一个transaction
DDL、DCL语句是自动提交的
sqlplus正常退出(exit)，系统自动提交
上个事务的结束就是下个事务的开始
事务保证数据的一致性，保证原子操作的安全
一个没有结束的事务，叫做活动的事务 (active transaction),活动的事务中修改的数据，只有本会话才能看见。
read committed，只可以读取已经做提交操作的数据，本会话可以看到自己的所作的没有提交的操作。
在活动事务中，当多个用户同时对同一张表进行操作时，会对表加上表级共享锁，当用户对操作该表某一条记录进行操作时会对该条记录加上行级排它锁，
只允许一个用户对该条记录进行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 test modify(c11 null);
增加约束：
alter table 表名 add constraint [约束名] 约束(字段);
只能够增加表级约束。
解除约束：(删除约束)
alter table 表名 drop 约束;
alter table 表名 drop primary key;
(对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错)
alter table father drop primary key cascade;
(如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了)
数据字典
数据字典是由系统维护的，包含的数据库的信息
数据字典示图
user_XXXXX 用户示图
all_XXXXX 所有示图
dba_XXXXX 数据库中所有示图
v$_XXXXX 动态性能示图
dict 表示数据字典的数据字典。
user_constraints 用户的表中约束的表
其中有constraint_name字段存放的是约束名，r_constraint_name字段表示外键引用pk或uk的名字
这两个字段之间有自连接的关系，也就是约束名和外键约束名之间的自连接。
user_cons_columns表，是用户的列级约束表
U 代表UK,R (references)代表FK,C 代表check和NOT NULL，P代表PK
找出s_emp表的主键约束
select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.constraint_name=cc.constraint_name and c.table_name=cc.table_name and c.table_name='S_EMP' and c.constraint_type='P';
练习： 列出每一个表的外键的定义，主表表名，主表字段名，子表表名，子表字段名 (画出E-R图)
alter table 表名 drop constraint 约束名;
(怎样取一个约束名：
a、人为的违反约束规定根据错误信息获取!
b、查询示图获取约束名!)
使约束失效或者生效
alter table 表名 disable from primary key; (相当于把一个表的主键禁用)
alter table 表名 enable primary key;
(enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable)
12:54 浏览 (203) 评论 (0) 分类: oracle 2007-09-13
缩略显示oracle学习笔记(三)
关键字: oracle
组函数
avg(..),求平均值 sum(..),求和 这两个函数的参数只能是number型的。
例：求所有人的提成的平均值
select avg(nvl(commission_pct,0)) from s_emp;
求有多少人有提成
select count(commission_pct) from s_emp;
求员工都分布在多少个不同的部门
select count(distinct(dept_id)) from s_emp;
以下所提到的函数可以使用任意类型做参数。
count(..)，用来统计记录数，可以使用排重命令。count(...)默认使用的是all。
max(..),min(..)求最大值和最小值，
注意：组函数可以处理一组数据，返回一个值。
所有的组函数会忽略空值。
count(*),统计表中记录数，不忽略空值。
group 组
group by 分组子句，按指定的分组规则分组 ，这个group by 子句可以跟在 select 语句后或是 having后面。
group by子句也会出发排序操作，会按分组字段排序。
select [组函数或分组的字段名] ，... from 表名 group by [字段名1],[字段名2],.....；
例：找出各个部门的平均工资
select dept_id,avg(salary)
from s_emp;
group by dept_id;
在没有group by时，select后不能把普通字段和组函数同时使用
求各个部门不同职位有多少人
select dept_id,title,count(*)
from s_emp
group by dept_id,title;
求平均工资大于2000的部门
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>2000;
求除了42部门以外的各个部门的平均工资
select dept_id,avg(salary)
from s_emp
where dept_id<>42
group by dept_id;
求各个部门的平均工资
select max(d.name),max(r.name),avg(salary)
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id
group by dept_id;
注意：只要写了group by子句，select后就只能用group by后的字段或者是组函数。
where子句只能够过滤记录，放单行函数。
having子句可以过滤组函数结果或是分组的信息，且写在group by子句后。
注意：要先过滤掉不需要的记录，然后再进行分组操作，提高效率。
四、子查询
子查询，就是可以嵌在任何的sql语句中的select语句,子查询也会触发排序
在select语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边。
注意：在使用子查询时，要注意这个运算符是单行的(也就是只能是单值)，还是多行运算符(范围，多值，in)。
配合使用子查询返回的结果必须符合运算符的用法。
例:
求谁的工资是最低的
select first_name,salary
from s_emp
where salary =(select min(salary)
from s_emp
);
求谁和Smith是做一样工作的
select first_name,title
from s_emp
where title in (select title
from s_emp
where last_name='Smith')
求哪些人是普通员工
select first_name from s_emp
where id not in (select manager_id from s_emp
where manager_id is not null)
求哪些人是经理
select first_name from s_emp
where id in (select manager_id from s_emp)
from后面也可以用子查询
例：找出哪个人的平均工资比本部门的平均工资高
select e.first_name,e.salary,a.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;
重点掌握的三种形式：
1、哪个部门没有员工
select e.deptno,d.deptno
from emp e,dept d
where e.deptno(+)=d.deptno and e.deptno is null;
2、求各部门的平均工资
select max(d.name),max(r.name),avg(salary)
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id
group by dept_id;
3、找出哪个人的平均工资比本部门的平均工资高
select e.first_name,e.salary,a.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;
将业务需求转换成可操作的表
一: 需求分析
二: 画E-R图(E entity 实体, R relation 关系)
三: 转换成表关系
四: 割接(新老系统交接)
五: 生产,试运行
E-R工具：Erwin
E-R图属性:
* 为强制的非空属性
o 可选属性(可以有值也可以没有)
#* 表示此属性唯一且非空
# 表示唯一的
实体关系:
mastbean maybean
数量关系: 一对一关系
一对多关系
多对一关系
多对多关系
第一范式，所有的属性都必须是单值，也就是属性只表示单一的意义。(记录可以重复，没有任何限制)
第二范式，属性要求唯一且非空，(记录不可重复，但是数据可能会出现冗余)。
第三范式，非主属性只能依赖于主属性，不能依赖于其他非主属性。(解决数据冗余问题)
六、约束
约束是针对表中的字段进行定义的。
primary key (主键约束 PK)保证实体的完整性，保证记录的唯一
主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键，只有两个字段放在一起唯一标识记录，叫做联合主键。
foreign key (外建约束 FK)保证引用的完整性，
外键约束，外键的取值是受另外一张表中的主键或唯一值得约束，不能够取其他值，只能够引用主键会唯一键的值，被引用的表，叫做parent table(父表)，引用方的表叫做child table(子表)，要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。
unuque key(唯一键，可以为空 UK)，值为唯一
index(索引)是数据库特有的一类对象，view(视图)
典型的一对多 class 对应多个学生。
student table class table
______________________________ _________________________
| id | name | address| class_id| | id |class_desc|class_num|
|(PK)|______|________|___(FK)__| |(pk)|__________|_________|
| | | | | | | | |
一对一
student tabel shenfenzheng table
____________________ _________________________________
| id | name | address| | s_id |shenfen_desc|shenfen_num|
|(PK)|______|________| |(PK，FK)|____________|___________|
| | | | | | | |
多对多
student tabel zhongjian table kecheng table
____________________ _________________________________ __________________
| id | name | address| | s_id |shenfen_desc|shenfen_num| | kid | kechengname|
|(PK)|______|________| |(FK，FK)|____________|___________| | (PK)|____________|
| | | | |联合主键| | | | | |
引用对方表的主键,当作本身的主键,所以这个表的主键,既是主键又是外建
建表和其他相关操作
DDL语句
创建表：
create table 表名 ( 字段名1 类型(数据长度)(default ...) 约束条件， 字段名2 类型(数据长度) 约束条件 );
Oracle数据库中的数据类型
varchar(长度)，可变长字符串，按照实际长度存储 char(长度) 定长字符串存储
number(..,..),number 表示浮点数，或者是整数
LOB(large object) 大对象 不能直接用insert插入
CLOB 字符的大对象，存大文本
BLOB 二进制的大对象，也是以相当于指针的形式存放的。
data block数据块
create table test(c1 number default 1,c2 number); 没有插入的时候会用默认值
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) ) ; 用表级约束可以实现联合主键
处理错误
(1)!oerr ora [错误号] ，系统可以显示错误的原因和如何修改。如果命令错误输入可以使用edit或ed来修改输入错误。
实际上是在编辑缓存文件中的最后一条sql语句。
也可以使用 (change) c /错误字段/正确字段，来进行替换操作进行修改。
只有在Linux平台使用
! 相当于 host ，没有断连接，只是切换了一下，执行shell命令
(2)edit命令来编辑最后一条sql语句。
replace：
update tableName set valueName=replace(valueName,'oldStr','newStr')
1、where子句(选择操作)
where子句使用在 select ... from ... 后面，用来选择所需(符合条件的)的记录
例：查询George的工资
select first_name,salary from s_emp where first_name='George';
where后面跟的是表达式 也就是 XXX=XXX， XXX between X and X ，XXX in(X，X，X)
like '...' 通配查询
between ... and ... ,表示结果在这之间，between and是一个闭区间，也就相当于... <= ... and ... >= ... 。
!=，<>，^=，这三个都标识不等于，<=，>=，=，这些运算符都可以使用。
... in (va1,val2,...) 判断结果是否在这个集合中存在，等于其中随变一个就行了，可以说in=any
in中集合的数据是有顺序的，概率高的数据放在前面可提高效率
not in<>all 没有顺序
空值会对not in造成影响，也就是不等于任何值，但是空值例外。
like '...' 字符串通配查询，'%'表示0或任意多个字符，'_'，表示任意一个字符。
注意：转义的用法：like ‘S\_%’ escape ‘\’
例：找出表名是以s_开头的表
select table_name from user_tables where table_name like 'S\_%' escape '\'; 把\定义为转义字符
... is null 使用来判断值是否为空。is not null 判断值不为空
例：查看哪些人没有提成
select first_name from s_emp where commission_pct is null;
... and ... 表示只有两个条件同时满足
... or ... 表示条件只要满足其中只一就可以
all ... 是要求都满足条件。
注意：Oracle中的字符串是严格区分大小写的。
(1)注意数据类型，数字类型直接写，字符用'......' ，缺省格式的Date可以用'......'，只有别名才用" "包含。
(2)选择合适的运算符
2、单行函数(一个输入，一个输出)
多行函数又叫组函数(多个输入，一个输出)例平均值
1.字符函数
字符是大小写敏感的
转小写 lower(字段名) --- 其中的参数可以是一个字符串常量或是一个字段名
转大写 upper(字段名)
首字母大写 initcap(字段名)
字符串拼接 concat(字段1, 字段2)
截取子串 substr(字段名, 起始位置，取字符个数)
返回字符串长度length(字符串)
dual表，是专门用于函数测试和运算的，他只有一条记录
可以使用"-"表示从右向左取，取的时候可以从左往右取。
例：select substr(first_name,-2,2) sub from s_emp;(取后两个)
select substr(first_name,2,2) sub from s_emp;(取前两个)
nvl是一般函数，支持任何数据类型
2.数值函数
四舍五入 round(数据,保留小数点后几位)
可以用负数表示小数点前，0，表示小数点后第一位，也就是保留个位，-1表示个位(保留到十位)。
例：select round(15.36,1) from dual;
截取数字函数 trunc(数据，保留的位数(小数点后位数)) 截取个位之后补0
例：select trunc(123.456,1) from dual;
3.日期函数
日期格式，
全日期格式 世纪信息，年月日，时分秒。
缺省日期格式，日-月-年 dd-mon-rr
修改当前会话的日期格式，会按照指定的格式输出日期
alter session set nls_date_format='yyyy mm dd hh24:mi:ss';
返回当前日期 sysdate
例：select sysdate from dual;
select sysdate+1 from dual; 获得明天的日期，加1，单位是天
例: 10分钟以后的时间 select sysdate,sysdate+1/144 from dual;
日期是格式敏感的
求两个日期间相隔了多少个月 months_between(date1,date2)
加减指定数量的月份 add_months(date,月数)，月数可以为负，负值就是减去相应的月数。
从date日期开始的第一个星期五 next_day(date,FriDay)
返回月末的日期 last_day(date)
round 不能使用缺省的日期格式
例：select round(to_date('02-JAN-07','DD-MM-RR'),'MONTH') from dual;
截取日期 trunc(date,'年或月或日或时分秒')
例：select next_day(sysdate,2) from dual;
例：select trunc(add_months(sysdate,1),'month') from dual;
练习：
返回下个月的第一天的日期
select round(last_day(sysdate),'MONTH') from dual;
select add_months(trunc(sysdate,'MONTH'),1) from dual;
select trunc(add_months(sysdate,1),'MONTH') from dual;
4.不同数据类型间转换函数
将日期转成字符 tochar(date,'日期格式')
日期格式要用有效格式，格式大小写敏感 'yyyy mm dd hh24:mi:ss'(完整的日期可格式)
year'(全拼的年),'mm'(2位数字表示的月) 'month'(全拼的月)，'dy'(3位字母表示的星期) ，'day'(星期的全拼)，'ddspth' (日期的序数词)
例：显示完整的系统日期
select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual;
查询3号入职的员工
select first_name,start_date from s_emp where to_char(start_date,'dd')='03';
select first_name,start_date from s_emp where to_char(start_date,'fmdd')='3'; 'fm'是去掉前导0
向表中插入日期(test是表名)
insert into test values('01-JAN-07');
insert into test values(to_date('2007 01 01 10:10:10','yyyy mm dd hh24:mi:ss'));
将字符转换成数字 to_number('...') ,缺省是按十进制来算，字符串只能是0---9的数字
select to_number('ab','xx') from dual; 把ab转成十六进制
将数字转字符to_char(number，'fmt') fmt是数字格式
select to_char(salary,'$99,999.99') from s_emp;
select to_char(salary,'$00,000.00') from s_emp; ##########表示越界，大于显示宽度
select to_char(salary,'L00,000.00') from s_emp;
将字符串转成日期 to_date('...','日期格式')
例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual；
3、多表查询
表连接(关联查寻)
如果多表查询时不加where子句，也就是过滤条件或者是使用了无效的条件，就会产生两表之间记录的相互逐条匹配(组合)，产生很多无效的结果(笛卡尔积)。
注意：在使用表连接时，要注意查询的表间的关系信息，表之间的字段所表示的信息的关系
1、等值连接
select [表别名1.字段名1]，[表别名2.字段名2],...
from 表1 表别名1 ，表2 表别名2
where 表别名1.字段名3=表别名2.字段名4;
表连接时，当表与表之间有同名字段时，可以加上表名或表的别名，加以区分，使用时要用
表名.字段名或表别名.字段名(列名)。当表的字段名是唯一时，可以不用加上表名或表的别名。
注意：当为表起了别名，就不能再使用表名.字段名。
例：列出所有Sales部门的员工
select e.first_name,d.name
from s_emp e,s_dept d
where e.dept_id=d.id and d.name='Sales';
列出每个员工的名称，他所在的部门名称，及地区名称
select e.first_name,d.name,r.name
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id;
执行脚本
cd $ORACLE_HOME
cd sqlplus
cd demo
sqlplus sd0703/sd0703 @demobld.sql
2、非等值连接
select [表别名1.字段名1]，[表别名2.字段名2],...
from 表1 表别名1 ，表2 表别名2
where 表别名1.字段名3 ..... 表别名2.字段名4
....可以使比较运算符，也可以使其他的除了'='的运算符
例：列出每个员工的工资和他的工资级别
select e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
3、自连接
用别名把一张表中的数据分成两部分，然后在使用条件过滤。
select [表别名1.字段名1]，[表别名2.字段名2],...
from 表1 表别名1 ，表1 表别名2
where 表别名1.字段名3=表别名2.字段名4;
例：列出每个员工的经理名
select a.first_name ename,b.first_name cname
from s_emp a,s_emp b
where a.manager_id=b.id;
以上所提到的表连接，都叫做内连接，严格匹配两表的记录。
4、外连接(先做连接再做过滤)
会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，数据库会模拟出记录去和那些不匹配的记录匹配。
例：select a.first_name enamei,a.id,b.first_name cname,b.id
from s_emp a,s_emp b
where a.manager_id=b.id(+);
即用a表中的数据去匹配b表的，若b表中有null，系统模拟纪录与其匹配
注意：要把那一方的记录全部都显示出来，还有注意条件(+)跟在要全部选出的对端。
外连接的应用：
员工都分布在哪些部门
select distinct deptno from emp;
列出哪个部门没有员工
select e.deptno,d.deptno
from emp e,dept d
where e.deptno(+)=d.deptno
and e.deptno is null;
12:50 浏览 (320) 评论 (0) 分类: oracle 2007-09-13
缩略显示SQL*PLUS命令的使用大全
关键字: Database oracle,database
SQL*PLUS命令的使用大全
Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中，可以运行sql*plus命令与sql*plus语句。我们通常所说的DML、DDL、DCL语句都是sql*plus语句，它们执行完后，都可以保存在一个被称为
sql buffer的内存区域中，并且只能保存一条最近执行的sql语句，我们可以对保存在sql buffer中的sql 语句进行修改，然后再次执行，sql*plus一般都与数据库打交道。
除了sql*plus语句，在sql*plus中执行的其它语句我们称之为sql*plus命令。它们执行完后，不保存在sql buffer的内存区域中，它们一般用来对输出的结果进行格式化显示，以便于制作报表。
下面就介绍一下一些常用的sql*plus命令：
1. 执行一个SQL脚本文件
SQL>start file_name
SQL>@ file_name
我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。
2. 对当前的输入进行编辑
SQL>edit
3. 重新运行上一次运行的sql语句
SQL>/
4. 将显示的内容输出到指定文件
SQL> SPOOL file_name
在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。
5. 关闭spool输出
SQL> SPOOL OFF
只有关闭spool输出，才会在输出文件中看到输出的内容。
6．显示一个表的结构
SQL> desc table_name
7. COL命令：
主要格式化列的显示形式。
该命令有许多选项，具体如下：
COL[UMN] [{ column|expr} [ option ...]]
Option选项可以是如下的子句:
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE { expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
1). 改变缺省的列标题
COLUMN column_name HEADING column_heading
For example:
Sql>select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- ---------
10 ACCOUNTING NEW YORK
sql>col LOC heading location
sql>select * from dept;
DEPTNO DNAME location
--------- ---------------------------- -----------
10 ACCOUNTING NEW YORK
2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上：
Sql>select * from emp
Department name Salary
---------- ---------- ----------
10 aaa 11
SQL> COLUMN ENAME HEADING ’Employee|Name’
Sql>select * from emp
Employee
Department name Salary
---------- ---------- ----------
10 aaa 11
note: the col heading turn into two lines from one line.
3). 改变列的显示长度：
FOR[MAT] format
Sql>select empno,ename,job from emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
Sql> col ename format a40
EMPNO ENAME JOB
---------- ---------------------------------------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
4). 设置列标题的对齐方式
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
SQL> col ename justify center
SQL> /
EMPNO ENAME JOB
---------- ---------------------------------------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
对于NUMBER型的列，列标题缺省在右边，其它类型的列标题缺省在左边
5). 不让一个列显示在屏幕上
NOPRI[NT]|PRI[NT]
SQL> col job noprint
SQL> /
EMPNO ENAME
---------- ----------------------------------------
7369 SMITH
7499 ALLEN
7521 WARD
6). 格式化NUMBER类型列的显示：
SQL> COLUMN SAL FORMAT $99,990
SQL> /
Employee
Department Name Salary Commission
---------- ---------- --------- ----------
30 ALLEN $1,600 300
7). 显示列值时，如果列值为NULL值，用text值代替NULL值
COMM NUL[L] text
SQL>COL COMM NUL[L] text
8). 设置一个列的回绕方式
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
COL1
--------------------
HOW ARE YOU?
SQL>COL COL1 FORMAT A5
SQL>COL COL1 WRAPPED
COL1
-----
HOW A
RE YO
U?
SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW
ARE
YOU?
SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW A
9). 显示列的当前的显示属性值
SQL> COLUMN column_name
10). 将所有列的显示属性设为缺省值
SQL> CLEAR COLUMNS
8. 屏蔽掉一个列中显示的相同的值
BREAK ON break_column
SQL> BREAK ON DEPTNO
SQL> SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE SAL < 2500
ORDER BY DEPTNO;
DEPTNO ENAME SAL
---------- ----------- ---------
10 CLARK 2450
MILLER 1300
20 SMITH 800
ADAMS 1100
9. 在上面屏蔽掉一个列中显示的相同的值的显示中，每当列值变化时在值变化之前插入n个空行。
BREAK ON break_column SKIP n
SQL> BREAK ON DEPTNO SKIP 1
SQL> /
DEPTNO ENAME SAL
---------- ----------- ---------
10 CLARK 2450
MILLER 1300
20 SMITH 800
ADAMS 1100
10. 显示对BREAK的设置
SQL> BREAK
11. 删除6、7的设置
SQL> CLEAR BREAKS
12. Set 命令：
该命令包含许多子命令：
SET system_variable value
system_variable value 可以是如下的子句之一：
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {15|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT] {ON|OFF}
AUTORECOVERY [ON|OFF]
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
BLO[CKTERMINATOR] {.|c}
CMDS[EP] {;|c|ON|OFF}
COLSEP {_|text}
COM[PATIBILITY]{V7|V8|NATIVE}
CON[CAT] {.|c|ON|OFF}
COPYC[OMMIT] {0|n}
COPYTYPECHECK {ON|OFF}
DEF[INE] {&|c|ON|OFF}
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
ECHO {ON|OFF}
EDITF[ILE] file_name[.ext]
EMB[EDDED] {ON|OFF}
ESC[APE] {\|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF}
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
FLU[SH] {ON|OFF}
HEA[DING] {ON|OFF}
HEADS[EP] {||c|ON|OFF}
INSTANCE [instance_path|LOCAL]
LIN[ESIZE] {80|n}
LOBOF[FSET] {n|1}
LOGSOURCE [pathname]
LONG {80|n}
LONGC[HUNKSIZE] {80|n}
MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL
{ON|OFF}] [PRE[FORMAT] {ON|OFF}]
NEWP[AGE] {1|n|NONE}
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {10|n}
PAGES[IZE] {24|n}
PAU[SE] {ON|OFF|text}
RECSEP {WR[APPED]|EA[CH]|OFF}
RECSEPCHAR {_|c}
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_
WRAPPED]|TRU[NCATED]}]
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
SHOW[MODE] {ON|OFF}
SQLBL[ANKLINES] {ON|OFF}
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {ON|OFF}
SQLPRE[FIX] {#|c}
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|ON|OFF}
SUF[FIX] {SQL|text}
TAB {ON|OFF}
TERM[OUT] {ON|OFF}
TI[ME] {ON|OFF}
TIMI[NG] {ON|OFF}
TRIM[OUT] {ON|OFF}
TRIMS[POOL] {ON|OFF}
UND[ERLINE] {-|c|ON|OFF}
VER[IFY] {ON|OFF}
WRA[P] {ON|OFF}
1). 设置当前session是否对修改的数据进行自动提交
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
2)．在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句
SQL> SET ECHO {ON|OFF}
3).是否显示当前sql语句查询或修改的行数
SQL> SET FEED[BACK] {6|n|ON|OFF}
默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数
4).是否显示列标题
SQL> SET HEA[DING] {ON|OFF}
当set heading off 时，在每页的上面不显示列标题，而是以空白行代替
5).设置一行可以容纳的字符数
SQL> SET LIN[ESIZE] {80|n}
如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。
6).设置页与页之间的分隔
SQL> SET NEWP[AGE] {1|n|NONE}
当set newpage 0 时，会在每页的开头有一个小的黑方框。
当set newpage n 时，会在页和页之间隔着n个空行。
当set newpage none 时，会在页和页之间没有任何间隔。
7).显示时，用text值代替NULL值
SQL> SET NULL text
8).设置一页有多少行数
SQL> SET PAGES[IZE] {24|n}
如果设为0，则所有的输出内容为一页并且不显示列标题
9).是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。
SQL> SET SERVEROUT[PUT] {ON|OFF}
在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。
10).当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句。
SQL> SET WRA[P] {ON|OFF}
当输出的行的长度大于设置的行的长度时(用set linesize n命令设置)，当set wrap on时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。
11).是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。
SQL> SET TERM[OUT] {ON|OFF}
在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。
12).将SPOOL输出中每行后面多余的空格去掉
SQL> SET TRIMS[OUT] {ON|OFF}
13)显示每个sql语句花费的执行时间
set TIMING {ON|OFF}
14．修改sql buffer中的当前行中，第一个出现的字符串
C[HANGE] /old_value/new_value
SQL> l
1* select * from dept
SQL> c/dept/emp
1* select * from emp
15．编辑sql buffer中的sql语句
EDI[T]
16．显示sql buffer中的sql语句，list n显示sql buffer中的第n行，并使第n行成为当前行
L[IST] [n]
17．在sql buffer的当前行下面加一行或多行
I[NPUT]
18．将指定的文本加到sql buffer的当前行后面
A[PPEND]
SQL> select deptno,
2 dname
3 from dept;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
SQL> L 2
2* dname
SQL> a ,loc
2* dname,loc
SQL> L
1 select deptno,
2 dname,loc
3* from dept
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
19．将sql buffer中的sql语句保存到一个文件中
SAVE file_name
20．将一个文件中的sql语句导入到sql buffer中
GET file_name
21．再次执行刚才已经执行的sql语句
RUN
or
/
22．执行一个存储过程
EXECUTE procedure_name
23．在sql*plus中连接到指定的数据库
CONNECT user_name/passwd@db_alias
24．设置每个报表的顶部标题
TTITLE
25．设置每个报表的尾部标题
BTITLE
26．写一个注释
REMARK [text]
27．将指定的信息或一个空行输出到屏幕上
PROMPT [text]
28．将执行的过程暂停，等待用户响应后继续执行
PAUSE [text]
Sql>PAUSE Adjust paper and press RETURN to continue.
29．将一个数据库中的一些数据拷贝到另外一个数据库(如将一个表的数据拷贝到另一个数据库)
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query
sql>COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST
create emp_temp
USING SELECT * FROM EMP
30．不退出sql*plus，在sql*plus中执行一个操作系统命令：
HOST
Sql> host hostname
该命令在windows下可能被支持。
31．在sql*plus中，切换到操作系统命令提示符下，运行操作系统命令后，可以再次切换回sql*plus：
!
sql>!
$hostname
$exit
sql>
该命令在windows下不被支持。
32．显示sql*plus命令的帮助
HELP
如何安装帮助文件：
Sql>@ ?\sqlplus\admin\help\hlpbld.sql ?\sqlplus\admin\help\helpus.sql
Sql>help index
33．显示sql*plus系统变量的值或sql*plus环境变量的值
Syntax
SHO[W] option
where option represents one of the following terms or clauses:
system_variable
ALL
BTI[TLE]
ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|
TRIGGER|VIEW|TYPE|TYPE BODY} [schema.]name]
LNO
PARAMETERS [parameter_name]
PNO
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
SQLCODE
TTI[TLE]
USER
1) . 显示当前环境变量的值：
Show all
2) . 显示当前在创建函数、存储过程、触发器、包等对象的错误信息
Show error
当创建一个函数、存储过程等出错时，变可以用该命令查看在那个地方出错及相应的出错信息，进行修改后再次进行编译。
3) . 显示初始化参数的值：
show PARAMETERS [parameter_name]
4) . 显示数据库的版本：
show REL[EASE]
5) . 显示SGA的大小
show SGA
6). 显示当前的用户名
show user
◆◆
评论读取中....
请登录后再发表评论!
◆◆
修改失败，请稍后尝试
3106
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
