查看信息desc v$controlfile查看文件路径select status,name from v$controlfile查看数据文件desc v$datafile相看数据文件信息select file#,status,name from v$datafile
desc v$logfileselect member from v$logfile
dos下登录sqlplus "system/manager"
sqlplus/nologconnect system/managerstartup mount shutdown transactionalshutdown abortshutdown immediate
help index ? setset sqlblanklines onselect * from count where num1=&tt;list 简写l
写错查询命令处理select * fron count使用c/fron/from使用/执行缓冲查询保存缓冲 save c:\text.txt@c:\text.txt查看get c:\text.txtedit
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,999select bytes from v$datafile;
SET LINESIZE 2TTILE CENTER "我的标题" skip 1-left "测试报表" right "页" -format 999 SQL.PNO SKIP 2select * from count;
不使用时ttile off;
comp count label "计数" of result on num2
输出查询数据spool onspool c:\spool.txtselect * from countspool 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,trimselect length('sss') from abc; ===3select lengthb('林sss') from abc; ====5select ltrim(length(' sss') from abc; ==3//删除左边的空格2、日期 sysdate,current_date,next_dayselect 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_numberselect 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,countselect max(c) from abc;//最大值select sum(c) from abc;//总数select avg(c) from abc;//平均数select count(c) from abc;5、其它 user,decode,nvlselect 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);//通过另一表创建一个新表
相关资源：Oracle学习笔记.pdf