oracle笔记.docx

oracle笔记.docx

《oracle笔记.docx》由会员分享，可在线阅读，更多相关《oracle笔记.docx（27页珍藏版）》请在冰豆网上搜索。

oracle笔记

2012-09-04

起源：

Oracle（甲骨文）公司

最早做数据库的厂商之一：

SYSBASEPowerBuilder/DelphiIBM微软的产品一般在oracle公司的后面上市

Oracle被号称为世界上最安全的数据库之一

ORACLE和其他数据库不同的地方：

用户--角色--权限之间的关系

一个用户可以有多个角色；

一个角色可以有多个权限；

用户分为各个级别：

超级管理员（有且只有一个）sys

普通管理员：

sysytem

普通用户：

scott

ORACLE给程序员或者说DBA提供了三个工具：

sqlplusdeveloperPL/SQL

常用的命令：

创建用户：

createuser用户名identifiedby密码

赋予登录权限：

grantcreatesessionto某个用户

查询权限：

grantselectonscott.empto某个用户

修改权限：

grantupdateonscott.empto某个用户

删除权限：

grantdeleteonscott.empto某个用户

增加权限：

grantinsertonscott.empto某个用户

解锁：

alteruser用户名accountunlock;先登录再解锁

加锁：

alteruser用户名accountlock给某个用户加锁

连接：

conn用户名（sys）/密码；assysdba

断开连接：

disconn/disconnect

修改密码：

alteruser用户名identifiedby新密码修改别人的密码

权限的问题：

简单的常规命令：

@加载一段脚本,与start的作用一样

@@

/run的简化写法

accept当前允许定义哪些类型的变量

append不换行在缓冲区中一条语句之后追加一些语句

archivelog查看日志的配置信息

attribute查看list缓冲区中的语句

btitle

break

clear清屏简写为：

cle

change

column

compute

connect连接

copy

define

del

describe查看当前表的结构

disconnection断开连接

edit编辑一个脚本editd:

/emp.sql;

execute执行存储过程

exit退出

help帮助helpindex查看所有的oracle命令

input换行去追加一条sql语句

list缓存当前的SQL语句,方便我们去调用用run调用

password更改当前用户自己的密码

pause

print

prompt

quit退出

recover

remark

repfooter

repheader

reserved

run执行缓存中的语句

save

setsetpagesize设定每页的大小

setlinesize设定当前显示几个位置

showuser查看是哪个用户在登录

shutdown关闭我们oracle数据库DBMS

spool将控制台中的所有运行的脚本及结果输入到文件中去

spoolon路径中间就是刷的内容spool就可以了

sqlplus调用sqlplus.exe可执行程序

start执行一段外部脚本

startup打开DBMS实例的

timing

store

ttitle

TNSORACLE监听服务：

1.配置监听器：

找到配置和移植工具--->NETconfigrationassisant打开

2.配置一个连接别人数据库的服务：

别人数据库的iporacle端口号1521

2012-09-05

oracle内置了三个用户：

syssystemscott

oracle是以用户来组织资源的，当用户不存在时，用户下的所有资源都会被级联删除

好处：

加强资源的利用提供资源的优化访问

缺点：

使资源的关系变得很复杂

1.oracle中的用户管理

创建用户：

createuser用户名identifiedby密码

注意：

创建的新用户没有任何权限

修改用户密码：

alteruser用户名identifiedby密码

删除用户：

dropuser用户名syssystem

2.oracle中的角色管理

角色是多个权限的组合体角色可以重用

我们可以将多个权限给一个角色，然后将一个角色给予一个用户，那么该用户就拥有了该角色的权限

createrole角色名

grant权限to角色名

droprole角色

grant角色to用户（用户名不能和角色同名）

3.oracle中的权限管理withgrantoption/withadminoption

用户和角色是高一级的范围，权限是最小的单位

grant某个权限to角色/用户

oracle中的回收权限：

revoke某个权限from用户/角色

4.oracle中的对象资源

5.连接远程数据库

找到配置和移植工具——>NETconfigrationassisant打开配置网络服务

创建一个连接的服务一个客户端可以允许有多个服务

6.如何在oracle中创建多个数据库

一般我们安装了oracle数据库会给我们安装一个数据实例

一般来说只需要打开两个服务就可以了

OracleServiceORCL有关于ORCL的数据库服务

OracleOraDb11g_home3TNSListenerOracle的监听服务

oracle数据库配置工具新建数据库的

DBMS和数据库文件的存放的地方是分开

Oracle中的三种类型的数据：

数据文件：

数据内模式的方式存放数据DBF

日志文件：

记录你当前用户的操作信息LOG

控制文件：

记录数据的信息什么时候创建的有多大角色权限CT关系

一般用途--普通的文件系统和DBMS　SID：

数据库的唯一标识

定制数据库

数据仓库--多了一个数据分析和数据挖掘

7.oracle中的监听器

监听某个端口是否有请求COBOL

　Oracle有两种模式：

单机模式：

共享模式：

8.建表（oracle中的数据类型：

用户类型和内部类型）

内部类型：

NUMBER（3,1）表示有效位数是3位，其中一位是小数

VARCHAR（20）

DATE日期

BLOB大文本类型TEXT

CLOB二进制数据类型

用户类型：

CHAR

BOOLEAN

　　　　　INT

FLOAT

DOUBLE

序列：

dual虚拟表序列可以被任意表使用用一次数字就会加1

这个是不是存在你用的时候才会存在

创建序列语法：

createsequence序列名

incrementby1--每次增长一个

startwith1--从1开始

maxvalue99999999999--最大值

minvalue1--最小值

cache10--允许的最大缓存10M

nocycle--不需要缓存

order

删除序列：

dropsequence序列名

查询序列：

selectidentity1.nextvalfromdual

eg:

创建序列：

createsequenceident

incrementby1

startwith1

maxvalue99999999999

minvalue1

cache10

nocycle

order

使用序列：

insertintostudentvalues（ident.nextval,'张三','123'...）

创建一个表

createtableStudent（

stu_idintprimarykey,

stu_namevarchar2（20）,

stu_passwordvarchar2（20）,

stu_sexchar（4）,

stu_ageint,

stu_addressvarchar2（100）

）

删除表中的字段

altertablestudentdropcolumnstu_age;

修改表中的字段

altertablestudentmodifystu_sexchar（8）

增加表中的字段

altertablestudentaddstu_ageint

9.建立索引

索引的种类：

非聚簇索引聚簇索引唯一索引（就是聚簇索引）

一张表中聚簇索引有且只能有一个非聚簇索引可以有多个

索引的优点：

是可以增加数据的查询效率的

如果当前表中有主键，系统会默认根据主键去建立一个聚簇索引，而当前我们查询数据都是根据这个聚簇索引去一个个查询的；索引是查询的根本

索引的语法：

createorreplaceindex索引名

on

表（字段名）

修改索引：

删了重建dropindex索引名

10.建立视图

视图就是一张虚拟表

ORACLE中的三种文件：

数据文件dbf控制文件ctl日志文件log

视图本身不会在硬盘中占据任何的空间，但是我们在使用这个视图的时候才会在内存中显示出来

视图的优点：

如果说语句很长可以使用视图来封装

视图的语法：

orreplace

ifexists（select*fromsys.objectswherename=''）

dropdatabase''

createorreplaceview视图名

as

sql语句

分页视图：

createorreplaceviewfindbyindex

as

select*from（selects.*,rownumrnfromscott.students）where

修改视图：

createorreplaceviewfindbyindex

as

select*fromscott.student

删除视图：

dropviewfindbyindex

视图的原则：

1.如果出现多表查询最好用视图来封装

2.如果查询数据亮比较大，最好不要使用视图

3.在做项目时，最好多使用分页查询

11.oracle中事务的概念

-JMSjava的事务处理

--transactiont=connection.getTransaction;

--t.setAutoCommit（false）;

--关键点:

oracle中你做增加,删除,修改一定要提交事务

否则操作不成功操作无效

insertintostudentvalues（100002,'李四','女'）

保存点；

select*fromstudent

1.设置保存点savepointonepo

2.事务提交commit

3.事务回滚rollbacktoonepo

12.DQL查询

12.1基本查询

分为两种：

查询所有select*from表名

查询几个字段select字段from表名

条件查询select*/字段from表名where条件

条件运算符：

><>=<=!

=<>=

谓词运算符：

in/notin/like/notlike/isnull/isnotnull

like运算：

%（匹配任意多个）_（匹配一个）

关系运算符：

andornot

域运算符：

between...and...

去掉重复项：

distinct适用于去掉单个

排序：

orderby字段desc（默认为升序asc）

unionall如果两个查询的数据一样可以通过unionall将数据联合

12.2分组查询

分组：

根据表中的数据进行分类

select分组条件，聚合函数（*）根据分组的结果来进行计算from表where条件groupby分组条件having子句（--对分组结果进行筛选）orderby排序字段

12.3连接查询（内连接外连接笛卡尔连接）

12.4集合查询

12.5子查询

12.6分页

Oracle中没有top语句

分页语句rownum伪列每一张表中都有一个伪列rownum用来标识当前是第几条数据是

1.select*from（selectt.*,rownumrnfromstudenttwhererownum<=5）wherern>=0

起别名as在oracle中可以不用写

最简单的分页：

selectrownumrn,s.*fromstudnetswhererownumbetween1and5

selectrownumrn,s.*fromstudnetswhererownum>=1andrownum<=5

分页的优化版本：

select*from（selectt.*,rownumrnfromstudenttwhererownum<=15）wherern>=10

13.DAL

数据的增加:

insertinto表名（字段...）values（...）

数据的删除:

deletefrom表名where条件

truncatetable表名---清空表

数据的修改：

update表名set字段=某个值...where条件

数据的访问：

约束primarykey/foreignkey/unique/default/check/notnull/null

14.表空间以及oracle的内部原理PGASGA

teblespace表空间：

oracle存放各种对象资源的地方

区、段、块

语法:

createtablespace表空间的名字

datafile'存放的地方'

size100M

segmentspacemanagementauto使用段的管理方式

createtablespacejava1210

datafile'd:

/java1210.dbf'

size100MREUSE--自动增长

segmentspacemanagementauto

15.性能视图（数据字典）

数据字典：

用来描述我们当前用户下的各种对象资源的数据状态

字段名数据类型是否为空是否为主键说明

idintnotnullprimarykey学号

user_tables查询当前用户下的所有表

user_users查询当前用户的状态

user_views查询当前用户下的视图

user_indexes查询当前用户的索引

user_procedures查询当前用户下的存储过程

user_tablespaces查询当前用户下使用哪些表空间

user_synonyms查询当前用户下的同义词

user_sequences查询当前用户下的序列

user_role_privs查询当前用户下的角色

v$process查询当前oracle使用了多少个进程存在权限问题systemsys

v$session查看当前有多少用户登录了我的oracle存在权限问题systemsys

user_packages查看当前用户下的所有包

user_types查看当前用户下的所有的自定义数据类型

user_triggers查看当前用户下的所有的触发器

2012-09-07

1.同义词

概念：

给oracle的对象资源取一个别名

eg:

select*fromscott.emp

createorreplacesynonymtb_tempforscott.emp

使用该同义词：

select*fromtb_emp

语法：

createorreplacesynonym别名for对象资源

eg:

给索引取一个别名:

createorreplacesynonymz_indexforscott.emp

2.rownum的知识

3.contraints

约束：

内部建立

外部建立：

主表要先有才能有从表的关联字段

4.Oracle中的内置函数

5.P_SQL

PL/SQL是oracle的内置的编程语言

--T-SQL是sqlserver的内置的编程语言

--变量常量运算符表达式语句

--PL/SQL中的语句块

6.语句块

1.语句块是PL/SQL编程语言的基本组成部分

--小例子

--数据数据结构数据元素

数据项

--数据结构-->数据项--->数据元素

--begin.......end;

--强调一下

--1.end后面一定要加;

--ORACLE的语法规则很严格oracle自成一个体系

--2.每一条语句写完之后也要加上一个;

一个最简单的语句块的例子

begin

dbms_output.put_line（'helloworld'）;

end;

--package的概念

--system.out.print（'helloworld'）

--system是一个类..类中有个常量叫out..out的返回值是printwriter...print方法

--dbms_output就是一个包

--在该包下面有很多的函数和存储过程

--put_line就是一个存储过程

--我们现在去使用put_line就是被组织在dbms_output这个包下面

--在命令窗口我们要使用setserveroutputon这个指令打开我们的输出的

--2.扩展的语句块

语法:

declare.....begin......end;

--ORALCE中的数据类型（定义变量）

--1.所有的变量都必须以v_开头visiable

--oracle中的内部类型

--number,char,varchar2,date,boolean,long,blob,clob,%type

--oracle中给变量赋予初值:

=

--字符串使用||和其他的数据类型相连接

--日---数字月-年

eg:

declare

v_idnumber:

=18;

v_namevarchar2（20）:

='张三';

v_sexchar（4）:

='男';

v_agenumber:

=22;

v_birdate:

='25-12月-1990';

begin

dbms_output.put_line（'我的学号'||v_id||'我的姓名'||v_name）;

end;

从表中将数据拿出来赋予给变量

eg:

declare

v_idscott.emp.empno%type;--%type表示我的数据类型和soctt用户下的emp表中的empno这个字段的类型是一样的

v_namescott.emp.ename%type;

begin

selectempno,v_nameintov_id,v_namefromscott.empwhereempno=7777;

--into表示将empno赋予给变量，只适用于返回单个值;若想返回多个值，请使用游标

dbms_output.put_line（v_id）;

end;

oracle中的常量不可变的量

declare

v_helloworldconstantvarchar2（40）:

='世界你好！

';--constant放在变量后面

begin

dbms_output.put_line（v_helloworld）;

end;

%rowtype

在Oracle中，数据类型不只是单一的数据类型，也有复合的数据类型

%rowtype表示当前的变量就是一个复合的数据类型

eg:

declare

v_empscott.emp%rowtype;

begin

v_emp.empno:

=11;

v_emp.ename:

='世界你好';

dbms_output.put_line（v_emp.empno||v_emp.ename）;

end;

--%type

oracle中的语句：

1.if...then...else...endif;

eg:

declare

v_speakvarchar2（50）:

='今天放假么？

不放';

v_speak2varchar2（50）:

='今天放假么？

放';

v_yesornoboolean:

=false;

begin

if（v_yesorno=true）

thendbms_output.put_line（v_speak2）;

else

dbms_output.put_line（v_speak）;

endif;

end;

2.多分支语句：

if...then...elsif...then...elsif...then...else...endif;

declare

v_scorenumber（4,1）:

=88;

begin

if（v_score>=0andv_score<=60）

thendbms_output.put_line（'留级'）;

elsif（v_score>60andv_score<=70）

thendbms_output.put_line（'及格'）;

elsif（v_score>70andv_score<=90）

thendbms_output.put_line（'良好'）;

else

dbms_output.put_line（'优秀'）;

endif;

end;

3.循环语句

**1.do...while循环

语法：

declare...begin...loop...循环的内容...end

eg:

declare

v_countnumber:

=1;

begin

loop

dbms_output.put_line（v_count）;

v_count:

=v_count+1;

exitwhen（v_count>=100）;

endloop;

end;

eg:

创建一张表并向其中添加100条数据

createtablestu（

stu_idintprimarykey,

stu_namevarchar2（20）,

stu_sexchar（4）

）

declare

v_countnumber:

=1;

begin

loop

dbms