oracle 数据库——知识点总结（加示例）
新入oracle数据库，把目前学到的知识点记录下来，可能都比较基础，但还是比较全的，里面的示例都是自己在PL/SQL中跑过的，如果有错误，还望各位大侠指出哈。
创建用户
1.创建用户（使用管理员身份创建，即用system进行登录）
create user rick identified by ric account unlock;
--创建一个用户名是rick的用户，密码是ric，用户状态未锁定2.上诉创建后，如果直接用设置的用户名和密码进行登录，则会提示下面的错误
3.可以理解为用户没有会话权限，无法进行数据库登录。则，接下来给予用户rick基本的connect权限
grant connect to rick; --将connect权限给予rick4.这次rick可以作为normal用户登录（如果选择dba身份，则需要更高的权限）
5.重新使用system登录数据库，更改rick的信息
alter user rick account lock; --将rick的状态设置为锁定6.再次以rick来登录，则会提示下面错误（一开始在创建用户时如果不设置状态为unlock，则系统会默认为lock状态）
创建表及约束
create table infos
(
stuid varchar2(7) not null,
age number(2) not null
)
alter table infos add constraint infos_check --给infos表加一个约束，限制age在20到60之间
check(age>20 and age<60)根据结果集创建表
create table infos2 as select * from infos; --会把数据一起复制到新表中create table infos3 as select * from infos where 1=2;
--后面加了一个where条件，这个条件不成立，则只会复制表结构，不包括数据插入数据时，如果表中字段是date类型，需要先使用to_date('2012-12-29','YYYY-MM-DD')将数据转换成date类型
当两个表的结构全部相同时，可以直接插入结果集
insert into infos3 select * from infos;清空表中的数据（不可恢复）
turncate table infos;字符串拼接
select (ename||' is a '||job) as "employee detail" from emp;
--括号内的内容会在一列中显示出来消除重复项
select distinct deptno from emp;in操作查询指定列表的值
select ename,job from emp where job in ('salesman','president','analyst');
--只要job是salesman、president、analyst中任意一个即满足条件between……and操作查询范围内的数据
select ename,job from emp where sal between 1000 and 2000; --包括边界like模糊查询
- % 表示零个或多个任意字符
- _ 表示一个任意字符
- 如果查询条件中有特殊字符，则使用[ ]括起来
集合运算
- intersect （交集） 返回两个查询共有的记录
- union all（并集） 返回各个查询的所有记录，包括重复记录
- union（并集） 返回各个查询的所有记录，不包括重复记录
- minus（补集） 返回第一个查询的记录减去第二个查询的记录后剩余的记录
使用union实现多条数据插入
insert into dept
select 50,'公关部','武汉' from dual
union
select 60,'研发部','重庆' from dual
union
select 70,'培训部','四川' from dual其中dual是一个oracle自带的特殊的表，使用只是为了符合select的查询结构，它只会返回一行记录
连接查询
- 内连接：join……on 两个表中只有满足条件的才会被查询出来
- 左外连接：left join ……on 左边的表不受限制，右边的表需要满足条件
- 右外连接：right join……on 右边的表不受限制，左边的表需要满足条件
- 全外连接：full join……on 左右两张表都不受限制
ANY子查询（只要满足ANY中的一个即可）
select ename,job from emp where sal<any(select sal from emp where job='salesman');
--查询出比任意一个销售的工资低的员工姓名和工作ALL子查询（需要满足all中的所有）
select ename,job from emp where sal>all(select sal from emp where job='salesman');
--查询出比所有销售工资都高的员工的姓名和工作伪列
- ROWID：表中每一行在数据文件中都有一个物理地址，ROWID就是返回这个物理地址
- ROWNUM：表中每行数据前面的序号
select t.* from (select ename,job sal from emp order by sal desc) t where rownum<5; --查出工资排行前四的员工信息select t.* from (select ename,job,rownum r from emp where rownum<10) t where r>5; --查询第6到第9条数据
日期函数
- ADD_MONTHS(d,n) 在日期d的基础上加上n个月，返回新日期
- LAST_DAY(d) 返回指定日期当月的最后一天
- ROUND(d,fmt) 返回一个以fmt为格式的四舍五入的日期值
- EXTRACT(fmt FROM d) 从日期d中提取fmt部分（YEAR、MONTH……）
- TO_CHAR(d,'YYYY-MM-DD HH24:MI:SS') 按照右边的格式转换日期成字符串，HH24表示是采用24小时制
- TO_DATE(x, 'YYYY-MM-DD') 按照右边的格式把字符串x转换成对应的date类型
其他
- NVL(x,value) 如果x为空，则返回value，否则正常返回x
- NVL2(x,value1,value2) 如果x非空，返回value1，否则返回value2
- SUM()统计某一列中的数据的和，COUNT()统计有多少条数据（即行）
同义词
create synonym myemp for scott.emp; --给scott用户的emp表创建别名myemporacle可以为表、视图、序列、过程、函数、程序包等指定别名，比如上面的，当当前用户拥有权限时，访问emp表，可以直接访问myemp。而不需要使用scott.emp
创建视图
create view view_name as select ……; --将查询到的结果保存成视图create view empdetail
as
select empno,ename,emp.deptnp,dname
from emp join dept on emp.deptno=dept.deptno;上图是将emp表与dept表联合查询后的结果保存成视图，视图相当于一张新的表（虚表），可以直接来用
创建索引
create index ename_index on emp(ename); --为emp表的ename创建索引创建表空间 (目前不是很懂表空间)
create tablespace myspace
datafile 'd:/a.ora' size 10M;
'd:/b.ora' size 5M;
extent management local --指出表空间类型是：本地管理表空间
uniform size 1M; --指定每个分区的统一大小扩充表空间
alter tablespace myspace
add datafile 'd:/c.ora' size 10M;为表和索引指定表空间，只需要在其创建语句后面加上tablespace space_name即可，这种情况的表空间是不能修改的
PL/SQL块
declare
--声明部分，如常量和变量
begin
--执行部分，这部分不能少
exception
--异常处理部分
end;如下例子
declare
sname varchar2(20) default 'jerry'; --声明变量sname并设置初始值
begin
select ename into sname from emp where empno=9527; --将查询到的值赋给sname变量
dbms_output.put_line(sname); --输出sname
end;还可以声明宿主变量，该变量属于全局变量，类似于web中的session，只要用户没退出，这个变量都可以使用
var emp_name varchar(30);
begin
select ename into :emp_name from emp where empno=9527;
end;!! 注意在使用这种宿主变量时，需要在变量名前加：
属性数据类型
- %ROWTYPE 引用表中的一行作为数据类型
declare myemp emp%rowtype; --创建一个以emp表中一行作为数据类型的变量 begin select * into myemp from emp where empno=9527; --赋值 dbms_output.put_line(myemp.ename); --ename相当于一个对象,可以使用.来调用它里面的属性值 end; - %TYPE 引用表中的某列的属性作为数据类型
declare cursal emp.sal%TYPE; --引用emp表中的sal列的类型作为cursal的数据类型 mysal number(4):=3000; --声明一个变量 totalsal mysal%TYPE; --引用变量mysal的数据类型作为当前变量的数据类型 begin select sal into cursal from emp where empno=9527; total:=cursal+mysal; dbms_output.put_line(totalsal); end;
IF ELSE语句
declare
newsal emp.sal%TYPE; --
begin
select sal into newsal from emp where ename='JAMES';
if newsal>2000 then
update emp set comm=800 where ename='JAMES';
else
update emp set comm=500 where ename='JAMES';
end if;
end;IF THEN ……ELSIF THEN
declare
newsal emp.sal%TYPE;
begin
select sal into new sal from emp where ename='JAMES';
if newsal>1500 then
update emp set comm=1000 where ename='JAMES';
elsif newsal>1000 then
update emp set comm=800 where ename='JAMES';
else
update emp set comm=500 where ename='JAMES';
end if;
end;!!! 注意是elsif ， 不是elseif
CASE语句
declare
v_grade char(1):=upper('&p_grade'); --&表示弹出输入框，接收用户的输入值
begin
case v_grade
when 'A' then
dbms_out.put_line('Excellent');
when 'B' then
dbms_out.put_line('Very Good');
when 'C' then
dbms_out.put_line('Good');
else
dbms_out.put_line('no such grade');
end case;
end;还可以把case的值赋给变量，如下
declare
v_grade char(1):=upper('&grade');
p_grade varchar(20);
begin
p_grade:=
case v_grade
when 'A' then
'Excellent'
when 'B' then
'Very Good'
when 'C' then
'Good'
else
'no such grade'
end case;
dbms_output.put_line(v_grade);
end;LOOP循环
declare
counter number(3):=0;
sumResult number:=0;
begin
loop
counter:=counter+1;
sumResult:=sumResult+counter;
if counter>=100 then
exit; --退出loop循环
end if;
end loop;
end;WHILE循环
declare
counter number(3):=0;
sunResult number:=0;
begin
while counter<100 loop
counter:=counter+1;
sunResult:=sumResult+counter;
end loop;
end;FOR循环
declare
counter number(3):=0;
sumResult number:=0;
begin
for counter in 1..100 loop -- 1..100表示1到100这个范围
sumResult:=sumResult+counter;
end loop;
end;GOTO语句
declare
sumsal emp.sal%TYPE;
begin
select sum(sal) into sumsal from emp;
if sumsal>20000 then
goto first_label; --前往第一个标签处
else
goto second_label; --前往第二个标签处
end if;
<<first_label>> --<<>>是goto可识别的标志
dbms_output.put_line(sumsal);
<<second_label>>
null; --无操作,语句接着往下执行
end;动态SQL
declare
sql_stmt varchar2(200); --存放SQL语句
emp_id number(4):=7566; --实参
dept_id number(2):=90; --实参
dept_name varchar2(14):='PERSOLLNNEL'; --实参
location varchar2(13):='DALLAS'; --实参
emp_rec emp%ROWTYPE; --一行数据
begin
sql_stmt:='insert into dept values(:1,:2,:3)'; --里面的:1,:2,:3相当于形参
execute immediate sql_stmt using dept_id,dept_name,location; --using后面的相当于实参
sql_stmt:='select * from emp where empno=:id'; --:id相当于形参
execute immediate sql_stmt into emp_rec using emp_id; --把执行sql_stmt的结果赋给emp_rec,using后面的相当于实参
end;异常处理
declare
newsal emp.sal%TYPE;
begin
select sal into newsal from emp;
exception
when TOO_MANY_ROWS then --TOO_MANY_ROWS是常用异常名
dbms_output.put_line('返回的记录太多');
when OTHERS then
dbms_output.put_line('未知异常');
end;自定义异常
declare
cursal emp.sal%TYPE;
myexp exception; --定义一个名为myexp的异常
begin
select sal into cursal from emp where ename='JAMES';
if cursal<5000 then
raise myexp; --raise用于引发myexp这个异常
end if;
exception
when NO_DATA_FOUND then
dbms_output.put_line('no resource found');
when MYEXP then
dbms_output.put_line('sal is less 5000');
end;创建存储过程
create procedure my_procedure(eno in number,salary out number)
--in表示是传入的参数，out表示输出的参数，这里声明的参数不写范围
as
begin
select sal into salary from emp where empno=eno;
end;执行上述存储过程
declare
my_salary number; --声明一个变量用于接收输出参数
my_eno number:=7369; --作为传入的参数
begin
my_procedure(7369,my_salary); --执行存储过程
dbms_output.put_line(my_salary);
end;创建触发器
create trigger my_trigger
after insert or update on dept for each row
--after表示在操作之后触发，on dept表示这个触发器是绑定在dept表上的
declare --不需要变量的话，可以不要declare
begin
if inserting then --如果执行的是插入操作，则执行下面语句
dbms_output.put_line('已经插入');
elsif updating then --如果执行的是更新操作，则执行下面语句
dbms_output.put_line('已经更新');
end if;
end;创建游标（游标可以对结果集进行一行行处理）
- 隐式游标（不用创建，由系统自动控制开启和关闭）
begin update emp set sal=sal+100 where empno=7876; --增删改查时系统会自动打开隐式游标 if sql%found then --如果有游标存在，就说明之前的操作成功 dbms_output.put_line('修改成功'); else dbms_output.put_line('修改失败'); end if; end; - 显示游标
declare cursor emp_cursor is select * from emp; --声明一个游标，这个游标时指向emp表的返回集(多行),可以理解为这个地方的游标是一个游标工厂 my_cursor emp_cursor%rowtype; --声明一个自己的游标，它指向的是一行数据 begin for my_cursor in emp_cursor loop --循环，只要在游标工厂中还有自己的位置就继续 dbms_output.put_line('job is '||my_cursor.job||' and salary is '||salary); end loop; ennd;
imp jwba/ffffff@yfcsdb file=e:\jwba.dmp ignore=y fromuser=jwba touser=jwba
imp jwbanap/ffffff@yfcsdb file=e:\jwbanap.dmp ignore=y fromuser=jwbanap touser=jwbanap
imp jwba/ffffff@zwkf2 file=e:\jwba.dmp log=e:\jwba.log ignore=y fromuser=jwba touser=jwba
imp jwbanap/ffffff@zwkf2 file=e:\jwbanap.dmp log=e:\jwbanap.log ignore=y fromuser=jwbanap touser=jwbanap
导出：
exp jwba/ffffff@yfcsdb file=e:\jwba.dmp log=e:\jwba.log owner=jwba
exp jwbanap/ffffff@yfcsdb file=e:\jwbanap.dmp log=e:\jwbanap.log owner=jwbanap
exp jwba/ffffff@zwkf2 file=e:\jwba1.dmp log=e:\jwba1.log owner=jwba
exp jwbanap/ffffff@zwkf2 file=e:\jwbanap1.dmp log=e:\jwbanap1.log owner=jwbanap
jwba/ffffff 表空间：JWBA
jwbanap/ffffff 表空间：NEWKJ_NAP
角色： CONNECT DBA RESOURCE
private_key_info视图
系统授权
对象：
创建用户及表空间：
/*分为四步 */
/*第1步：创建临时表空间 */
create temporary tablespace user_temp
tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第2步：创建数据表空间 */
create tablespace user_data
logging
datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第3步：创建用户并指定表空间 */
create user username identified by password
default tablespace user_data
temporary tablespace user_temp;
/*第4步：给用户授予权限 */
grant connect,resource,dba to username;
在以SYSDBA身份登陆时可以修改其他用户的密码，比如：
SQL> alter user user01 identified by user10;
用户已更改。
这个是把USER01用户密码修改为USER10
Oracle 使用时间长了， 新增了许多user 和tablespace. 需要清理一下
对于单个user和tablespace 来说， 可以使用如下命令来完成。
步骤一： 删除user
drop user ×× cascade
说明： 删除了user，只是删除了该user下的schema objects，是不会删除相应的tablespace的。
步骤二： 删除tablespace
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
oracle的用户授权问题：如何把一个用户的所有表的读写权限授予另一个用户
select 'Grant all on '||table_name||'to user2 ;' from user_objects
where owner = upper(user1);
然后执行该语句的结果就可以了
查看主外键关联
select a.constraint_name, a.table_name, b.constraint_name,b.table_name
from user_constraints a, user_constraints b
where a.constraint_type = 'R'
and b.constraint_type = 'P'
and a.r_constraint_name = b.constraint_name
