Oracle常用命令

1.查看当前用户下的表
select * from tab;

以下命令需要在sqlplusw中进行
------------------------------------------------------------------------------
2.在命令行中打开记事本
ed a;
3.执行笔记本中的sql语句
@a;
4.切换用户
conn sys/change_on_install as SYSDBA;
5.查看当前用户
show user;
6.读取硬盘上的文件如果后缀是.sql则：
@D:\demo或者D:demo
如果不是后缀不是.sql，需要加上后缀名
@D:demo.txt
7.设置sqlplusw的列大小
set LINESIZE 300;
8.设置每页显示的大小
set PAGESIZE 30;
9.访问不同用户的表需要加上“用户名”当用户是sys是访问scott的表:
select * from scott.emp;
10.当使用超级管理员是可以手工的控制数据库的实例的打开和关闭
（1）关闭数据库实例：SHUTDOWN IMMEDIATE
一旦关闭之后用户无法直接连接sqlplusw命令。此时可以先使用“/nolog”登录，之后使用管理员登录
(2)气功数据库实例：STARTUP;
另外，sqlplusw命令窗口也可以调用本机的操作系统命令，使用“host”作为前缀。
host copy e:\a.txt e:\hello.txt;
11.查看表的结构
DESC 表名称
-------------------------------------------------------------------------------------------------------------------------
12.表查询中使用“||”连接查询，其中“雇员信息是表头”即别名内容不要用单引号括起来
select '雇员编号:'||empno||'雇员职务：'||job 雇员信息 from emp;

 

 

13.oracle分页核心代码
  13.1 数据库伪列
       含义：用户不需要处理的的列，而是由oracle自行维护的的列，在oracle中有两个数据库伪列
      rownum  rowid
 rownum表示的是行号，这个是重点，是动态显示的
  13.2  显示表的6-10记录
      select rownum,empno,sal,comm from emp where rownum between 6 and 10;显示不出数据是因      为 rownum  不是真实列，实现这种查询的思路：先查询出前10条记录，然后再显示后五条
解决方案1：
分页代码：
select rownum,empno,sal,comm from (select rownum rn,empno,sal,comm from emp where rownum<=10) e
where e.rn>5;
解决方案二：
 select rownum ,empno,sal,comm from (select rownum rn,empno,sal,comm from emp  ) b where b.rn  between 5 and 10;

 

14.表的创建：
create table member(
 name varchar2(50) default '无名氏',
 age number(3),
 birthday date  default sysdate,
 content clob
);
insert into member(age,content)values(22,'想你，想你');

15.表的复制：
create table emp20 as select * from emp where deptno=20;
复制表结构：没有数据 即：写一个永远不能满足条件的表达式：
create table empnull as select * from emp where 1=2;

16.为表重命名：
rename member to person;

oracle的数据字典三种如下：
tab, dba,user_

select * from tab;
select * from user_tables;

17.截断表
删除表中的数据的方法有delete,truncate,
它们都是删除表中的数据,而不能删除表结构,delete 可以删除整个表的数据也可以删除表中某一条或N条满足条件的数据,而truncate只能删除整个表的数据,一般我们把delete 操作收作删除表,而truncate操作叫作截断表.

truncate table person;
18.表的删除
drop table person;
19.闪回技术
查看回收站：
show recyclebin
表恢复：
flashback table myemp to before drop;
删除回收站中的表
purge table empnull;
清除回收站中的所有记录
purge recyclebin;
20.彻底删除表
drop table myemp purge;
21.修改表的结构
添加字段：
alter table 表名称 add(字段名称 字段类型 default[默认值])
alter table member add(age number(3),birthday date default sysdate);
修改表字段：alter table 表名称 modify(字段名称 字段类型 default[默认值])
alter table member modify(name varchar2(50) default '无名氏')

面试题：
SQL> select n.name,na.name
    from nation n,nation na
    where n.name!=na.name;

NAME                 NAME
-------------------- --------
中国                 美国
中国                 荷兰
中国                 巴西
美国                 中国
美国                 荷兰
美国                 巴西
荷兰                 中国
荷兰                 美国
荷兰                 巴西
巴西                 中国
巴西                 美国
巴西                 荷兰
22.约束
not null unique 唯一约束
指定约束名称,表达式：‘约束类型_字段’
constraint UK_email unique(email);

drop table member purge;
create table member(
mid number ,
name varchar2(20) not null,
email varchar2(50),
constraint UK_email unique(email)
);
23.主键约束
primary key

create table member(
mid number ,
name varchar2(20) not null,
email varchar2(50),
constraint UK_email unique(email),
constraint PK_mid primary key(mid)

);
24.检查约束（check）
drop table member purge;
create table member(
mid number primary key,
name varchar2(20) not null,
email varchar2(50),
sex varchar2(20),
age number(3),
constraint UK_email unique(email),
constraint ck_sex check(sex in('男','女','中'));
contstraint ck_age check(age between 0 and 250)
);
25.创建外键关联关系
drop table member purge;
create table member (
mid number primary key,
name varchar2(50) not null
);

create table book(
bid number primary key,
title varchar2(100) not null,
mid number,
constraint fk_mid foreign key(mid) references member(mid)
);

26.级联删除（on delete cascade）

drop table member purge;
create table member (
mid number primary key,
name varchar2(50) not null
);

create table book(
bid number primary key,
title varchar2(100) not null,
mid number,
constraint fk_mid foreign key(mid) references member(mid) on delete cascade
);
27.级联设置成空（on delete set null）

28.强制删除表，不管级联（cascade constraint）

29.修改表约束
alter table 表名称 add constraint 约束名称 约束类型（字段）;
alter table 表名称 delete constraint 约束名称 约束类型（字段）;
非空约束作为一种特殊的约束不能修改

alter table member add constraint pk_mid primary key(mid);
alter table member add constraint ck_age check(age between 0 and 250);

30.oracle数据库的备份与还原
备份
exp [user]/[passwd]@[servername] file=文件路径 log=日志路径
exp developer/mysteelsoft@orcl owner=developer file=2012.07.23_bsSes.dmp rows=y  log=log-.log

还原
imp developer/mysteelsoft@orcl fromuser=developer touser=developer file=07.23_bsSes.dmp log=huaiyuan.log ignore=y

 

   

二、oracle常用函数

1.CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
赵 A

2.CONCAT
连接两个字符串;
SQL> select concat(’010-’,’88888888’)||’转23’ 高乾竞电话 from dual;

3.LOWER
返回字符串,并将所有的字符小写
SQL> select lower(’AaBbCcDd’)AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd

4.UPPER
返回字符串,并将所有的字符大写
SQL> select upper(’AaBbCcDd’) upper from dual;
UPPER
--------
AABBCCDD

5.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr(’13088888888’,3,8) from dual;
SUBSTR(’
--------
08888888

6.REPLACE(’string’,’s1’,’s2’)
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace(’he love you’,’he’,’i’) from dual;
REPLACE(’H
----------
i love you

7.ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)
--------- ---------
100 100

8.TO_CHAR(date,’format’)  将日期格式转换成字符串格式

 select  to_char(sysdate,'yyyy-MM-dd') from  dual;

9.SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,’dd-mm-yyyy day’) from dual;

10.ROUND和TRUNC    trunc 不对后边的小数做处理，直接截取掉
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55

11.to_date（string_value , date_format）
select to_date('09-11-2012','MM-dd-yyyy') from dual;

三、pl/sql编程
1.oracle 存储过程的基本语法  学习地址：
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN

END 存储过程名字

有单个数据值返回的存储过程

create or replace procedure xs_proc(temp_name in varchar2,                                  temp_num  out number)
is 
 num_1 number; 
 num_2 number;
begin 
 select yu_wen, shu_xue     into num_1, num_2    from xuesheng   where xing_ming = temp_name;    dbms_output.put_line(num_1 + num_2);  temp_num := num_1 + num_2;
end;

 

12.触发器

13.函数

14.java 调用存储过程

为什么要使用过程化编程
1.执行速度（需要
编译）
2.安全性问题
3.占带宽

oracle创建自动增长列

create sequence studentPkSequence start with 1 increment by 1

instr 函数用法
 语法如下：

　　instr( string1, string2 [, start_position [, nth_appearance ] ] )

　　参数分析：

　　string1

　　源字符串，要在此字符串中查找。

　　string2

　　要在string1中查找的字符串.

　　start_position

　　代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。

　　nth_appearance

　　代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。

　　注意：

　　如果String2在String1中没有找到，instr函数返回0.

 

下面最两个例子：
sys@ORCL> select instr('asdfasdf','s',1,1) from dual;

INSTR('ASDFASDF','S',1,1)
-------------------------
                        2

sys@ORCL> select instr('asdfasdf','s',-1,1) from dual;

INSTR('ASDFASDF','S',-1,1)
--------------------------
                         6

可以看出不管从左数还是从右数，string2在string1中的位置是已经定好的。

当然instr函数也能判断表中某列的值是否包含某个字符串，比如：

scott@ORCL> select empno,ename
  2  from emp
  3  where instr(ename,'SCO')>0;

     EMPNO ENAME
---------- ----------
      7788 SCOTT

 

oracle 截取时间值函数

//oracle中extract()函数从oracle 9i中引入,用于从一个date或者interval类型中截取到特定的部分  
//语法如下：  
EXTRACT ( 
        { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } 
        | { TIMEZONE_HOUR | TIMEZONE_MINUTE } 
        | { TIMEZONE_REGION | TIMEZONE_ABBR } 
FROM { date_value | interval_value } ) 
//我们只可以从一个date类型中截取 year,month,day(date日期的格式为yyyy-mm-dd);  
//我们只可以从一个 timestamp with time zone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE;  
select extract(year from date'2011-05-17') year from dual; 
      YEAR 
---------- 
      2011 
select extract(month from date'2011-05-17') month from dual; 
     MONTH 
---------- 
         5 
select extract(day from date'2011-05-17') day from dual; 
       DAY 
---------- 
        17 
//获取两个日期之间的具体时间间隔，extract函数是最好的选择  
select extract(day from dt2-dt1) day 
      ,extract(hour from dt2-dt1) hour 
      ,extract(minute from dt2-dt1) minute 
      ,extract(second from dt2-dt1) second 
from ( 
     select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1 
           ,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2 
     from dual) 
/ 
       DAY       HOUR     MINUTE     SECOND 
---------- ---------- ---------- ---------- 
       102          4          1         46 
-- 
select extract(year from systimestamp) year 
      ,extract(month from systimestamp) month 
      ,extract(day from systimestamp) day 
      ,extract(minute from systimestamp) minute 
      ,extract(second from systimestamp) second 
      ,extract(timezone_hour from systimestamp) th 
      ,extract(timezone_minute from systimestamp) tm 
      ,extract(timezone_region from systimestamp) tr 
      ,extract(timezone_abbr from systimestamp) ta 
from dual 
/ 
     YEAR      MONTH        DAY     MINUTE     SECOND         TH         TM TR         TA 
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ---------- 
      2011          5         17          7     14.843          8          0 UNKNOWN   UNK 
//