oracle 10学习笔记

查看信息
desc v$controlfile
查看文件路径
select status,name from v$controlfile
查看数据文件
desc v$datafile
相看数据文件信息
select file#,status,name from v$datafile

desc v$logfile
select member from v$logfile

dos下登录
sqlplus "system/manager"

sqlplus/nolog
connect system/manager
startup mount
shutdown transactional
shutdown abort
shutdown immediate

help index
? set
set sqlblanklines on
select * from count where num1=&tt;
list 简写l

写错查询命令处理
select * fron count
使用c/fron/from
使用/执行缓冲查询
保存缓冲 save c:\text.txt
@c:\text.txt
查看get c:\text.txt
edit

column 简写 col
查看帮助 ? col
如：col num1 heading "编号"
select * from count;

表的描述describe  简写 desc

格式化显示效果
connect sys/manager as sysdba;
select bytes from v$datafile;
col bytes format 999,999,999
select bytes from v$datafile;

SET LINESIZE 2
TTILE CENTER "我的标题" skip 1-
left "测试报表" right "页" -
format 999 SQL.PNO SKIP 2
select * from count;

不使用时ttile off;

comp count label "计数" of result on num2

输出查询数据
spool on
spool c:\spool.txt
select * from count
spool off

表格的建立
create table abc(a varchar2(10),b char(10));
alter table abc add c number;
alter table abc drop column c;
授权访问
grant select on abc to sys;
删除受权
revoke select on abc from sys;

常用系统函数
1、字符 length,ltrim,replace,rtrim,substr,trim
select length('sss') from abc;  ===3
select lengthb('林sss') from abc;  ====5
select ltrim(length('  sss') from abc; ==3//删除左边的空格
2、日期 sysdate,current_date,next_day
select sysdate from abc; 当前时间
alter session set nls_date_format='dd-mon-yyy hh:mi:ss';
select current_date from abc;
select next_day(sysdate,'星期三') from abc;
3、转换 To_char,to_date,to_number
select to_char(sysdate,'yyyy-mm-dd') from abc;
select to_char(sysdate,'yyyy-mm-dd hh:mi;ss') from abc;
select to_char(sysdate,'yyyy-mm-dd hh24:mi;ss') from abc;
select to_date('12-3月-03') from abc;
select to_number('00333') from abc;
4、聚集函数 sum,avg,max,min,count
select max(c) from abc;//最大值
select sum(c) from abc;//总数
select avg(c) from abc;//平均数
select count(c) from abc;
5、其它 user,decode,nvl
select user from abc;//查询当前用户
select sum(decode(sex,'男',1,0)) 男人数，sum(decode(sex,'女',1,0)) 女人数

from e;//分析当前男女人数
select a,nvl(c,'未输入') a2 from abc;//凡是空的，就显示未输入
select * from abc where c is null;//查询是空值的数据
select * from abc where c is not null;//查询是不为空值的数据

查询重复的数据(分组查询)
select a,count(a) from abc group by a having count(a)>1

模糊查询
select a from abc where a like  'a__';
select a from abc where a like  'a%';
select a from abc where a like  '%a';

 

新建表后要加上commit命令确认

 

create table e(
   eid number(10),
   ename varchar2(10),
   sex char(10),
   id number(10)
)

create table d(
   id number(10),
   name char(10)
)

insert into e values('001','赵1','男','01');
insert into e values('002','赵2','男','02');
insert into d values('01','主人');
insert into d values('02','客户');

 查询两个表
select e.eid,e.ename,e.sex,d.name from e,d where e.id=d.id;
select e.eid,e.ename,e.sex,d.name from e,d where e.id=d.id(+);
select e.eid,e.ename,e.sex,d.name from e,d where e.id(+)=d.id;
相关子查询
select * from e where id in (select id from d where id=e.id and id='2')
select * from e where id not in (select id from d where id=e.id and id='2')
select * from e where exists (select id from d where id=e.id);
select * from e where not exists (select id from d where id=e.id);
无关子查询
select * from e where exists (select id from d);

select eid,ename from e union select id,name from d;

select id from e intersect select id from d;
insert into e(eid,name) select id,name from d;//通过另一表数据添加到一个表里
create table ttt as (select * from e);//通过另一表创建一个新表