分类： Oracle
2011-07-28 11:28:29
oracle 甲骨文。注意：oracle安装路径不能出现中文
安装完Oracle10g后会在计算机上注册一些服务。
OracleService(数据库服务)
TNListener(客户连接监听服务)
OracleDBConsole(管理控制台服务)
oracle 逻辑结构 数据库--->表空间--->段--->区---->数据块
表空间对应就是一个物理文件(大小)
表空间数据的关系
一个数据库可以包多个表空间，但至少要有一个表空间
一个表空间只能属于一个数据
如果没有给数据创建表空间，那么数据库会自动创建一个表空间system
表空间与用户的关系
一个表空间可以有多个用户，一个用只能属于一个表空间
表空间是用来存放该用户所创建任何数据库的对象和数据
oracle服务
OracleOraDb11g_home1TNSListener OracleServiceORCL
oracle数据库与别名的关系
客户端别名设置Net Manager
别名实际是客户端识别远端服务器上数据库的另一个名称
别名只是针对客户机与服务机不为同一台机器
oracle系统自带的三个用户及三个角色
system 系统管理员(服务器端的命令语句处理)
sys 系统操作员(修改服务器端的配置文档)
scott 一般测试用户(不涉及到服务器端的设置)
默认密码为 tiger
角色
normal 一般角色
sysoper 系统操作角色
sysdba 系统管理
授权给scott grant dba to scott;
登陆scott
表空间创建：create tablespace tabspace2 datafile 'D:\oracle\tab.ora' size 10m autoextend on
删除表空间：drop tablespace tabspace2
解锁用户命令：alter user scott account unlock
加锁命令：alter user scott account lock
创建用户：create user user1 identified by test default tablespace tabspaces; //用户是属于表空间tabspaces
create user user2 identified by test //用户是属于系统system表空间
oracle新创建的用户不能连接数据首先需授权
oracle用户授权连接数据的权限
grant connect to user1
grant connect to user2
使用表空间资源的权限：grant resource to user1
常见的权限：
grant create any table,create view ,create procedure,select on table,update on table,delete on table,insert on table,all on table,DBA,create user to username
回收权限：revoke connect from user4
修改用户：alter user user4 identified by testtest
删除用户：
删除用户的同时，会把改用户的所有的对象级数据一起删除
drop user user4 //删除一个空用户
drop user user5 cascade //删除已经创建数据库对象的用户
roacle关键字
oracle虚表（dual），主要使用来获取系统的相关信息或测试函数
select sysdate from dual //获取服务器的时间
select upper('aaaaa') from dual
伪列：不是真正属于某个表的字段，但是任何一个表可以访问下面两个伪列，不能修改
记录在存储文件中物理地址rowid
伪列记录的编号处理(1+1.......)rownum
伪列
select emp.*,rowid,rownum from emp
rownum伪列注意
1.只能用在<=条件中，不能用在>=条件中
select * from emp where rownum<=3
select * from (select emp.*,rownum sid from emp) where sid>=5 and sid<=7
oracle数据类型
数字：number(m,n)m:数字长度,不包括小数点，n:小数位数
1234.567 m=7 n=3
int integer float
字符：varchar2(n) varchar(n)
日期时间：date
二进制：blob
oracle语句
DDL :create drop alter
DML :select insert update delete
DCL :grant revoke
TCL :transaction commit rollback
连接查询：
select * from emp,dept where emp.deptno=dept.deptno
select * from emp,dept where emp.deptno=dept.deptno(+)
select * from emp,dept where emp.deptno(+)=dept.deptno
select distinct deptno from emp
select ename 姓名 from emp
select ename as 姓名 from emp
select a.* from emp a
数据类型转换
将非字符类型转换成字符类型to_char：select to_char(sysdate,'yyyy/mm/dd hh:mi:ss') from dual
将非日期类型转换成日期类型to_date：select to_date('20110727141012','yyyy-mm-dd hh24:mi:ss') from dual
将非数字类型转换成数字类型to_number：select to_number('34') from dual
add_months：select add_months(sysdate,-1) from dual
select add_months(to_date('20110918','yyyy-mm-dd'),2) from dual
select * from emp where hiredate=sysdate
months_between：select months_between(sysdate,to_date('20110127','yyyy-mm-dd')) from dual
last_day：select last_day(sysdate) from dual
round：select round(sysdate) from dual
trunc：select trunc(sysdate) from dual
next_day：select next_day(sysdate,'星期四') from dual
extract：
select extract(year from sysdate) from dual
select extract(month from sysdate) from dual
select extract(day from sysdate) from dual
initcap：select initcap('aa bb cc') from dual
lower/upper：
select lower('AAAAA') from dual
select upper('aaaaa') from dual
ltrim/rtirm/trim：
select ltrim(' aaaa') from dual
select rtrim('aaaaa ') from dual
select trim(' aaa ') from dual
replace：select replace('abcdefghcd','cd','1111') from dual
instr：select instr('abcdefg','c') from dual
substr：select substr('abcdefg',2,3) from dual
concat：select ename||'aaa' from emp
select concat(ename,'aaa') from emp
chr/ascii：
select chr(97) from dual
select ascii('a') from dual
lpad/rpad：
select lpad('aaa',10,'*') from dual
select rpad('aaa',10,'*') from dual
lengths：select length('aaa') from dual
decode/case when then end select decode(1,1,3,4) from dual
select case when 1=2 then 3 else 4 end from dual
select floor(123.456) from dual
select ceil(123.456) from dual
select round(1234,-2) from dual
select trunc(1234,-2) from dual
nvl：select sal,comm,sal+nvl(comm,0) from emp
select empno,sal,deptno,row_number() over(partition by deptno order by sal desc) id from emp
select empno,sal,deptno,dense_rank()over(partition by deptno order by sal desc) id from emp
select empno,sal,deptno,rank()over(partition by deptno order by sal desc) id from emp
PL/SQL
procedure language struct squery language过程化语言/结构化查询语言
PL/SQL语句结构
declare
//定义变量或数据类型
begin
//业务语句
exception
//异常
end
变量声明
变量标识符 数据类型
age number(3);
declare
age number(3);
begin
age:=10; //给变量赋值
dbms_output.put_line(age);//利用输出函数输出变量值
end;
变量赋值
1. ：= age:=20;
2. select fieldname into varname from tablename
3. default age number(3) default 20;
declare
--age number(3) default 30;
age number(3) :=40;
begin
--age:=10;
dbms_output.put_line(age);
end;
declare
vsal number(7,2);
begin
select sal into vsal from emp where empno=7521;
dbms_output.put_line(vsal);
end;
declare
vsal number(7,2);
vempno number(4);
begin
select empno,sal into vempno,vsal
from emp
where empno=7521;
dbms_output.put_line(vsal||' '||vempno);
end;
声明常量
declare
vage constant number(3) default 10;
begin
dbms_output.put_line(vage);
end;
常量必须在声明时赋初始值，而且在程序中不能修改
PL/SQL除数函数
dbms_output.put_line();
declare
vage number(10);
begin
vage:=&1;//输入数字
dbms_output.put_line(vage);
end;
declare
vage varchar2(10);
begin
vage:='&1';//字符类型加''
dbms_output.put_line(vage);
end;
PL/SQL数据类型
数字：number(m,n) int integer
字符：varchar2(n)
日期：date
二进制：blob
字段引用类型
vsal emp.sal%type;
declare
vsal emp.sal%type;
vempno number(4);
begin
select empno,sal into vempno,vsal
from emp
where empno=7521;
dbms_output.put_line(vsal||' '||vempno);
end;
不需要明确指定变量的数据类型
表引用类型
vrow emp%rowtype;
//一般写法
declare
vdeptno dept.deptno%type;
vdname dept.dname%type;
vlog dept.loc%type;
begin
select * into vdeptno,vdname,vlog from dept where deptno=10;
dbms_output.put_line(vdeptno||vdname||vlog);
end;
declare
vrow dept%rowtype;//表引用类型
begin
select * into vrow from dept where deptno=10;
dbms_output.put_line(vrow.deptno||vrow.dname||vrow.loc);
end;
自定义数据类型
记录类型(类似于c的结构体)
type typename is record
(.......
)
declare
type emp_rec is record
(vempno emp.empno%type,
vename emp.ename%type,
vsal emp.sal%type,
vdeptno emp.deptno%type); //声明一种数据类型
vrow emp_rec; //用新的类型定义一个变量
begin
select empno,ename,sal,deptno into vrow from emp where empno=7521;
dbms_output.put_line(vrow.vempno||vrow.vename||vrow.vsal||vrow.vdeptno);
end;
表类型(静态 动态)
