Oracle10g快速入门[课堂笔记]

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;

 

表类型(静态 动态)