Oracle操作及SQL语句总结

Oracle数据库

晴 星期一 2012.11.12

几种数据库比较：

【商用版】Oracle数据库：甲骨文公司；大型数据库：可容纳千万行级，上亿行级别；可靠性高、并发性好、安全性好

产品版本有：oracle8i/oracle9i/oracle10i/oracle11g 其中i与g分别表示： i:Internet 互联网（协议） g:Grid 网格（协议）

【商用版】MySql数据库：sun公司，现在已经被Oracle公司购买，中型数据库：可容纳千万行级

【免费版】SQL Sever数据库：MicroSoft(微软公司)，中型数据库：可容纳千万行级

下载网址：www.oracle.com www.sun.com

数据库范式：1NF/2NF/3NF/BCNF

第一范式（1NF）：数据库中的字段不可拆分

SQL语言：DQL/DDL/DML/DCL

DQL: Data Query Language : select * from student【查询】

DDL: Data Define Language : create table student

DML: Data Management Language: insert into【增加】 / update set【修改】 / delete from【删除】

DCL: Data Control Language : grant【授权】 select to scott / revoke【撤消权限】 select from scott

总结为：增、删、改、查

修改表名：alter table 原表名 rename to 新表名

修改字段：alter table 表名 rename column 原字段 to 新字段

添加字段：alter table 表名 add 新字段 字段类型

DBA 是 Database Administrator 的缩写

常用数据库及默认密码（如果安装时指定了新密码，则以指定为准）：

sys --〉 change_on_install

system --> manager

scott --> tiger

执行（cmd）命令操作：

sys用户登录：sqlplus sys/密码 as sysdba

在切换用户简单登录sys(密码也可省)：conn/as sysdba

显示用户：show user

查看有哪些表：select * from tab;

给scott用户加锁：alter user scott account lock;

给scott用户解锁：alter user scott account unlock;

连接scott用户：connect/conn scott/密码

退出：quit/exit

bank数据库中某些字段解析：

简写 全写 中文

dept department 部门表

emp employee 员工表

bonus 奖金表

salgrade 工资等级表

阴 星期二 2012.11.13

安全登录：sqlplus scott 回车

输入口令：密码

指定登陆某个数据库：sqlplus scott/密码@网络服务名 (可以和数据库名相同)

Oracle数据库中字段类型升级：varchar() --> varchar2()

显示表中数据时，控制字段以多少字符输出（格式化）：column 字段名 format a10

a: ascii (ascII 码) 10: 10个字符

消除格式化：column 字段名 clear

/ "/"：重新执行最后一次操作的SQL语句命令； 注：column 不是SQL语句命令

保存点(a,b 指某个点、位置):savepoint a;

回退，撤销：rollback; (回退到建表之后，即向表中插入数据之前)

回退到某个点：rollback to a;

指定显示字段对齐方式：column 字段名 justify left/center/right

给字段指定别名(可用as,也可不用)：

select id 编号,name 姓名,gender 性别 from student;

select id as 编号,name as 姓名,gender as 性别 from student;

别名中有空格则需加 双引号 " " 即："姓 名"

给查询内容补空格([lpad]左补右对齐，[rpad]右补左对齐，空间满则不补)：

select id 编号,lpad(name,8,'') 姓名,rpad(gender,2,'') 性别 from student;

向表中添加新字段：alter table 表名 add 字段 字段类型;

alter table student add salary number(9,2);

x/9:位宽 y/2:精确度 (注：插入数据时 x+y不能大于x 即：1234567.12可行，12345678.12不可行)

如果输入命令有错可以使用 edit/ed 打开记事本编辑器进行更改；创建库、表时，如果用系统关键字，则需加 双引号 " "

即：create table "table" ();

阴 星期二 2012.11.13

Oracle数据库中注释符：REM

日期数据类型 data

字母转大写：upper() 转小写：lower()

在知道具体字母但不知道大小写的情况下：select * from bank where upper(name)='USERNAME'

in查询：select * from 表名 where 字段 in(数据1,数据2,数据3)

模糊查询：‘%’匹配零个或多个字符 ‘_’只匹配一个字符

可以使用 escape 标识符 选择‘%’和 ‘_’ 符号

eg: select * from student where name like '%房祖\_名' escape '\'; ‘\’可换成其他符号，如：# * ? 等

阴 星期四 2012.11.15

将数据存到物理文件：commit;

测服务器当前时间：sysdate

dual Oracle数据库提供的伪表，供用户进行测试

initcap 将每个单词首字母转为大写

concat('ABC','abc') 字符串结合 ABCabc

substr('abcd',1,3) 字符串截取 x/1:从第几/1个字符开始 y/3:截取多少/3个字符

length('他是一个高官') 返回字符个数 6

lengthb('他是一个高官') 返回字符位宽 12

instr('他是abc一个高官','abc') 返回字符所在位置 3

trim(' abcd ') 去掉字符前后空格 (abc)

round('3.1415926',3) 四舍五入 3.142

左边补一位‘$’: select name,lpad(salary,length(salary)+1,'$') as dollar from salary

补符号‘￥’: select name,to_char('￥') as RMB from salary

eg: select last_name,lpad(rpad(salary,length(salary)+2,'元'),12,' ') as RMB from employees

阴 星期五 2012.11.16

trunc('3.1415926',3) 截取 3.141

replace() 替换

mod(5,2) 取余数 3

to_char() 转换为字符型

to_date() 转换为日期型

字符型与整型可相互转换；字符型与日期型也可相互转换

varchar2(),char() <--> number(); varchar2(),char() <--> date

Oracle数据库中默认日期格式：DD-MON-RR (16-may-92) 日-月-年

插入日期格式：insert into 表名 values('16-11月-1992')

日期格式转换：select 字段1,to_char(hire_date,'yyyy-mm-dd') from dual

或 select 字段1,to_char(hire_date,'yyyy"年"mm"月"dd"日"') from dual

查看人民币符：select name,aalary,to_char(salary,'L99,999,00') from dual

dual：Oracle提供供测试用的伪表

nvl2() 填充

nullif('','') 两值相同时，返回空

coalese()

阴 星期一 2012.11.19

①

左外连：select …… where 表1.id=表2.id(+)

右外连：select …… where 表1.id(+)=表2.id

②

左外连：select ……left jion …… on ……

右外连：select ……right jion …… on ……

全外连：

① select …… full join …… on

② select …… , …… where 表1.id=表2.id(+)

或 select …… , …… where 表1.id(+)=表2.id

cross jion 交叉集合，笛卡尔集

natural jion 自然联接（所以同名字段的数据必须完全一样才能返回值，字段不同，则数据全返回）

和全外联返回的②结果一样

阴 星期二 2012.11.20

stddev(salary) 标准方差

set pages 30 设置（题头）显示页面大小

count(字段名) 求行，该字段值为空但其他字段值不空，不返回值

rownum >= x 前几行

rownum 是不确定的值，随时可变动 rowid 固定不变

删除：…… where rownum<=x (x可为任意数)或 rownum=1 可行 ,rownum = y (y>1 不可行)

查询条件： …… salary<any(x,yx,z) (x>y>z 显示的值小于x)

>any(x,yx,z) (x>y>z 显示的值大于于z)

<all(x,yx,z) (x>y>z 显示的值小于z)

>all(x,yx,z) (x>y>z 显示的值大于x)

阴 星期三 2012.11.21

在创建表之后再设定字段插入值不能为空： alter table 表名 modify 字段名 not null

删除字段：alter table 表名 drop column 字段名

截断、删除： truncate table 表名 (删除表中所有行，表结构还在，和 delete …… from …… 效果相同，但速度比它快)

创建表时设插入数据默认值： 字段 类型 default '默认值'

阴 星期四 2012.11.22

drop table 表名 (没有真正删除表，只是将表存放在Oracle回收站中，而且表数量并没有少，但执行了删除命令的表的名字改变了)

Oracle回收站：recyclebin

显示回收站结构：desc recyclebin

显示回收站数据：show recyclebin

从回收站回复被删除的表：flashback table 原表名 to before drop

禁用回收站：alter session set "_recyclebin"=false

启用回收站：alter session set "_recyclebin"=ture

(注：禁、启用命令得根据Oracle版本号而定)

真正删除表(不经过回收站)：drop table 表名 purge

查询Oracle版本号：select * from v$version

查看视图：desc role_sys_privs

查询角色：select * from role_sys_privs

角色有：connect 、resource

回收、撤销某角色：revoke resource from scott

授权角色：grant resource to scott

(注：授权、撤销权限只能在 sys 中执行)

创建用户：create user 用户名 identified by 密码

把

某表的某些权限授予某用户： grant all/select/delete on 表名 to 用户名

创建用户之后得授权才能登录、连接：grant create session to 用户名

授予用户所有操作权限：grant resource to 用户名

授予用户某些操作权限，如：把创建表的权限授予某用户：grant create table to 用户名

创建表时，图像字段类型：clob/blob <----> char/binary large object (此字段类型不推荐用，推荐用 varchar2() )

eg: image clob

向表中插入数据时图像字段为空格式：empty_clob()

把某表创建在某空间(Oracle默认表空间 users / system)：create table 表名(id number,name varchar2(12)) tablespace users

切换表空间：alter 表名 default tablespace 表空间名

创建表空间：create tablespace mysystem logging datefile 'd:\mysystem.dbf' size 32m autoextend on next 32m maxsize 2048m extend management local

(注：mysystem 为 表空间名，mysystem.dbf 为 表空间文件)

对某用户不设置限定表空间权限：grant unlimited tablespace to 用户名

阴 星期五 2012.11.23

timestamp 时间撮 是对 (date 精确到秒) 的扩展，精确到秒后3位

to_timestamp() 格式转换

eg: …… to_timestamp('2012-11-23 10:31:33','yyyy-mm-dd hh-mi-ss') /* 注：分钟格式：mi */

插入精确格式(最大精确度为9位)：…… to_timestamp('2012-11-23 10:31:33.123456789','yyyy-mm-dd hh-mi-ss.ff')

修改日期显示格式：alter session set nls_timestamp_format='yyyy"年"mm"月"dd"日" hh"时"mi"分"ss"秒"'

快速建表：create table 表2 as select * from 表1 where x>y;

(注：前提是表1已经创建好了；where x>y 为假 (eg：1>2 )，目的：创建相同表结构，但无数据)

删除表字段的两种方法：

①直接删除：alter table 表名 drop column 字段名

②先标识字段不可用：alter table 表名 set unused (字段名); (可以同时标识多个字段(字段1,字段2,字段3))

再删除标识不可用的字段：alter table 表名 drop unused columns

(注：如果服务器忙，则推荐使用方法②，反之则反之；两种方法删除之后都不能恢复、还原，删除时请慎重 或 先备份)

注释：comment on table 表名 is '注释的文字'

注释表的名字：user_tab_comments

约束：unique 唯一 、default 默认 、not null 不空 、check 检查 、primary key 主 、foreign key 外键

数据完整性：参照完整性 (外键)

行完整性、域完整性

阴 星期一 2012.11.26

视图：view

创建视图：create view 视图名 as select * from 表名 (要求：表已经创建好)

创建视图时也可以给视图指定字段，指定则以视图字段为准，不指定则以表字段为准：create view 视图名(vid,vname) as ……

视图替换：create or replace view 已有视图名 as select * from 表名

创建只读视图：create view 视图名 as select * from 表

名 where read only

创建序列号：create sequence rmb_seq001 start whith 1 increment by 1 maxvalue 100 nocycle/cycle nocache

sequence:关键字 rmb_seq001:序列名(任意取) start whith x/1:从x开始 increment by y/1:每步以y递增 maxvalue z:最大值

nocycle:不循环 cycle:循环 nocache:不缓存

查数据时自动编号：编号字段值 --> rmb_seq.nextval (下一值)

查询当前值：select rmb_seq.currval ……

创建索引：create index ind001 on 表名(字段名) 目的：提高查询速度

index:关键字 ind001:索引名(任意取)

阴 星期二 2012.11.27

创建同义词：create synonym 同义词名 from 表名

创建角色：create role 角色名 (先创建角色，然后将需要的权限授予角色，再将角色授予用户)

用户密码忘记后，创建新密码：alter user 用户名 identified by 新密码 (只能通过sys更改，所以sys密码绝对不能忘)

一次性授权给所有用户：grant …… to public

用户接力授权(即：用户可以再给用户授权)：grant …… to 用户名 with admin option (admin 是授予所有权，如果指定了授什么权给用户，则将 admin 改为 grant)

集合：并集(去重：union 不去重：union all) 、交集：intersect 、补集：minus

eg: select 字段a,字段b from 表名 union select 字段x,字段y from 表名 (对应字段类型必须相同 如：a<-->x , b<-->y)

控制某字段不输出(打印)：column 字段名 noprint

分组集合：select …… group by rollup(字段) (rollup：返回n+1种集合，n代表字段个数)

cube() 2的n次方

grouping sets 相当于多个 union all 查询

阴 星期三 2012.11.28

PL/SQL

PL/SQL <--> Procedure(Programming) Language/SQL:是SQL的扩展

查看所有参数：show all

打开serveroutput参数：set serveroutput on

输出：dbms_output.put_line();

查看错误：show error

晴 星期四 2012.11.29

赋值： := eg: name varchar2(20) :='mike';

连接符：||

输出误代号：sqlcode

输出误信息：sqlerrm (包括错误代号)

eg: dbms_output.put_line('错误了：'||sqlerrm);

编写文档格式：

declare

/*在此声明变量*/

begin

/*执行操作：写命令，输出……*/

end;

定义/创建新类型：

declare

type newtype is recode

(

字段1 字段类型, --id int,

字段2 字段类型, --name varchar(20),

字段3 字段类型 --age int(3)

);

xy newtype;

/*注：字段 字段类型, <--可改写为自动获取字段类型--> 字段 表名.字段%type, (eg:id student.id%type,);*/

/*注：newtype 为新类型名，可任意取 xy 为自定义变量名，可任意取*/

begin

select 字段1,字段2,字段3 into xy from 表名 where name='mike'; -- 字段1,字段2,字段3 可换为 *

dbms_output.put_line('字段1为：'||xy.字段1);

--dbms_output.put_line('编号：'||xy.id);

dbms_output.put_line('字段2为：'||xy.字段2);

dbms_output.put_line('字段3为：'||xy.字段3);

end;

/*

type newtype is recode

(

字段1 字段类型, --id int,

字段2 字段类型, --name varchar(20),

字段3 字段类型 --age int(3)

);

xy newtype;

整体可换为：

xy 表名%rowtype;

--xy student%rowtype

*/

定义表：

declare

type mytable is table of student%rowtype index by binary_integer;

abc mytable;

--mytable 为新类型名，可任意取 、abc 为为自定义变量名，可任意取 、student 为某表名

begin

select * into abc(0) from 表名 where name='mike';

dbms_output.put_line('字段1为：'||abc(0).字段1);

--dbms_output.put_line('编号：'||abc(0).id);

dbms_output.put_line('字段2为：'||abc(0).字段2);

dbms_output.put_line('字段3为：'||abc(0).字段3);

--abc(0) 括号中 可任意改(0 表示取第1行数据，1 表示取第2行数据)，还可以为负数 abc(-8) 但负数不一定排在正数之前

end;

处理错误：

exception

when other then

dbms_output.put_line(sqlerrm);

/*end之前*/

关系运算符：不等于 <> 、!=、~= 、^=

晴 星期五 2012.11.30

循环：

while loop

/*循环条件*/

end loop;

--begin 之后

判断：

if …… then

dbms_output.put_line();

elsif …… then

dbms_output.put_line();

else

dbms_output.put_line();

end if;

--begin 之后

异常处理：

①系统预定义

②非预定义

③用户自定义

SQL%NOTFUND……：此处 SQL 为 隐式游标

阴 星期一 2012.12.03

触发器(trigger)

每张表可创建 12种 触发器，可创建 N个 触发器

备份触发器：

--当你从emp表删除行时，会激活 trigger007触发器,trigger007触发器将正在被删除的行备份到emp_history

create or replace trigger trigger007

before delete on emp

for each row

begin

insert into emp_history values(

:old.empno,

:old.ename,

:old.job,

:old.mgr,

:old.hiredate,

:old.sal,

:old.comm,

:old.deptno

);

dbms_output.put_line(:old.ename||'成功备份');

end;

备份复原：insert into 表名 select * from 备份表名

阴 星期二 2012.12.04

导出表：exp scott/pyh01085618@bank file=d:\student2.dmp tables=(student,emp)

导入表：imp scott/pyh01085618@bank file=d:\student.dmp tables=(student) --注：导出与导入都在dos命令下执行

阴 星期三 2012.12.05

查询Oracle进程：select pid,program from v$process

唤醒某进程：oradebug wakeup x --x 为 pid 号

①关闭数据库，②卸载数据库，③关闭例程：shutdown immediate

①装载数据库，②打开数据库，③启动例程：startup

阴 星期四 2012.12.06

修改系统内存大小：alter system set sag_max_size=200M scope=spfile

显示系统内存大小：show parameter sag

显示共享池信息：show parameter share

修改共享池大小：alter system set shared_pool_size=92M scope=spfile

/*以 4M 为单位添加 eg: 80M , 84M ,92M 即使给90M，系统也会自动给成92M*/