Oracle入门到精通.docx

Oracle入门到精通.docx

《Oracle入门到精通.docx》由会员分享，可在线阅读，更多相关《Oracle入门到精通.docx（137页珍藏版）》请在冰点文库上搜索。

Oracle入门到精通

Oracle安装使用编程精华文档

一、oralce安装

基本位置：

Oracle安装在哪一个目录（注意不同版本的区别）

%driver_name%\app\username

目标驱动器：

一般Oracle自动选择剩余空间最多的驱动器

Oracle主目录：

用于设置环境变量OracleHome，一般指向Oracle

程序文件所在的目录（db_1）

类型：

版本区别与操作系统无关

个人版

标准版

企业版

选择Oracle产品时，是与OS有关的。

win32，unix/linux、MacOS、visita

附加启动数据库

如果不选，只安装软件，没有数据库

如果选，需要设置数据库名

全局数据库名：

在网络上唯一标识数据库的名字

格式：

dbname.domain：

SID.Domain

数据库的SID：

数据库在本地计算机上的唯一标识

域名，是此数据库在网络上的后缀

：

在整个Internet是唯一的，称为全局数据库名

全局数据库名可以没有后缀（域名），一般用于内部网络

数据库口令：

不是数据库本身的口令，数据库用户的口令

sys（数据库中最高权限用户），

system（数据库中DBA用户），sysman、dbsnmp用户

此处设置的口令同时应用于以上用户

数据库配置：

1、一般用途（OLTP）

OLTP：

联机事务处理：

主要应用于Web，管理系统，电子商务等

事务在整个应用中数量很多，但是执行时间都很短

2、数据仓库（OLAP）

OLAP：

联机分析处理：

主要应用于数据分析、数据仓库和数据挖掘等

事务的数量是有限的，但是，每一个事务可能开销很大

启用自动备份：

归档或非归档模式

归档模式：

开销大，但可恢复，适用于高可靠性和高可用性环境

非归档模式：

开销小，用于普通环境，数据的恢复依赖于备份

用户的密码：

对于9i及老版本，数据库用户使用默认密码

sys：

change_on_install

system：

manager

对于10g及以上的新版本，有用户自己设置密码（password）

安装完成后的准备

因为Oracle对系统资源占用很大，一般需要把Oracle服务修改为手动

需要使用时才启动，如何启动Oracle服务

Oracle要正常使用至少启动两个服务：

1、OracleOraDb11g_home1TNSListener：

监听服务

用于处理网络中对Oracle访问的请求

2、OracleServiceHY：

数据库服务

数据库服务（Oracle实例）

在机房使用Oracle时：

修改两个文件

Oracle11g：

D:

\app\administrator\product\11.1.0\db_1\network\ADMIN

Oracle10g：

D:

\oracle\product\10.2.0\db_1\network\ADMIN

1、listener.ora

2、tnsnames.ora

修改HOST的取值：

（ADDRESS=（PROTOCOL=TCP）（HOST=YourHostName）（PORT=1521））

只能用主机名，不能用IP、localhost和127.0.0.1

修改完成后，再启动服务

可选的服务：

OracleDBConsolehy，可以使用也可以不使用

启动Oralce的web管理界面的。

管理Oracle的方法：

1、SQL*Plus（重点，以后主要的工具）

2、Web的管理界面

3、第三方工具（以后开发时常用）

启用scott帐户：

scott（密码是tiger）

是Oracle中比较小的，范例方案，经常被用于教学。

Oracle10g及以上的数据库中scott用户是被锁定的，需要开启此帐户

1、在运行中sqlplussystem/manager（数据库已经启动）

2、在提示符下，执行命令

1、alteruserscottaccountunlock;--解锁

2、alteruserscottidentifiedbytiger;--设置密码

3、grantconnect,resource,createviewtoscott;--授权

3、利用scott身份登录：

运行：

sqlplusscott/tiger

1、查询当前用户的表

selecttable_namefromuser_tables;

2、查询表的结构

desctable_name

4、在sqlplus中如何提交语句

1、在语句后加“；”提交语句到Oracle

2、如果换行行了，“；”或者“/”

Web管理：

1、httpserver开启：

http:

//127.0.0.1/isqlplus（只适合于Oracle9i）

2、OracleDBConsolehy开启：

第三方工具（以后开发时常用）

1、SQLDevepler

2、PL/SQLDevepler

3、DataStudio

Oracle卸载

1、执行卸载程序

2、删除注册表项

HLM\software\oracle

HLM\system\currentcontrolset\service

3、删除目录

driver:

\oracle（app）

C:

\programfile\oracle

二、SQL*PLUS

SQL*PLUS：

是SQL加强版，具有通用性，所有的Oracle产品中都有此工具

这个工具不需要图形界面

在SQL*PLus中可以执行以下操作

1、SQL语句

2、SQL*Plus命令

3、PL/SQL语句（T-SQL）（编程）

SQL*Plus的启动方法：

在Oracle10g及以前的老版本中有两个SQL*Plus命令：

sqlplus：

在命令行（shell、DOS提示符）中运行，也可以在图形界面

sqlplusw：

在图形界面中运行的（Windows）

在11g以后的新版本中，sqlplusw不再支持。

1、在菜单中启动——“应用程序开发”

2、在“运行”直接输入：

SQLPLUS或者sqlplusw

3、打开CMD，运行SQLPLUS或者sqlplusw

SQL*Plus的登录（重点）

提示符界面SQLPLUS：

sqlplususername/password@localservicename

sqlplus用户名/密码@本地服务名

图形界面

用户名：

Oracle用户名

口令：

密码

主机字符串：

=本地服务名，如果留空默认连接本机

本地服务名：

主机字符串==网络服务名==本地服务名

=主机名+端口号+协议+数据库名

=IP+端口号+协议+数据库名

本地服务名是用来说明客户端连接的数据库服务器的相关信息

保存的是服务器的相关信息

什么是服务器的信息？

？

？

？

哪一个服务器：

IP、服务器的主机名

哪一个端口：

端口决定了所提供的服务的类型

比如：

80代表是web应用，21代表FTP应用，1433代表SQLServer数据库

Oracle的默认端口号是1521

用什么语言？

：

协议（计算机间通信的语言就是协议）

使用什么数据库呢？

：

数据库名（SID）

在客户端创建一个本地服务名，用于方便的连接服务器

scott/tiger@hygj

交互模式：

先输入命令然后在根据提示输入信息

sqlplus

1、用户名：

scott@ora，然后输入密码

2、用户名：

scott/tiger@ora

非交互模式：

直接在命令的后面跟用户/密码@本地服务名

SQL*PLUS的退出：

quitexit都可以

直接点击“关闭”按钮不是正常的退出！

！

！

！

没有提交的事务会自动回退，所以不推荐使用

当在SQL*plus中输入错误命令，可以使用ed命令

编辑错误的语句（edit），只能编辑刚刚执行过的指令

SQL*Plus调用外部脚本

命令1：

startdriver\path\filename

命令2：

@driver\path\filename

命令@和start区别：

@可以在脚本中使用，而start不行

sqlplusscott/tiger@ora@c:

\mysql.txt

把上面命令放到一个以bat或cmd为扩展名的文件中，就可以执行了

用户的切换：

可以在不退出SQL*Plus的情况下，可以切换用户

connect=conn

如果不想退出SQL*Plus，只是想注销用户

disconnect=disc

SQL*Plus会自动为查询结果进行分页，设置行宽

默认行宽是

showlinesizepagesize

setlinesize100

setpagesize16

设置列格式

1、对字符列设置格式（列宽）

在默认情况下，列宽是根据列的定义显示的

columncol_nameformatanumber

columnenameformata20

2、对数字列设置格式（数字的表现形式）

定义数字的小数点位置、千分位位置、货币类型等

columncol_nameformatexp_format

0：

强制显示前导0，不够位数的，用0补齐

9：

不显示前导0，原来多少位就显示多少位

L：

本地货币符号

$：

代表美元

：

千分位分隔符

.：

小数点

SQL*Plus不是自动提交的

setautocommiton

设置脱机：

开始脱机：

设定将运行结果脱机到那个文件

spooldriver:

\path\filename

结束脱机：

结束脱机，并将结果保存到目标文件

spooloff

一旦结束脱机，SQL*Plus中的数据就自动存储在上面设置的文件中

showall：

显示SQL*Plus所有的设定

三、DML

SQL语言

SQL支持关系数据库的三级模式结构

1、外模式：

开发人员所面对的一层，视图和部分基本表（安全性低）

2、模式层：

描述商业需求，实体与实体间的关系，基本表，数据库设计主要完成的内容

3、内模式：

解决数据库底层的实现，表空间和数据文件

一般来讲，应用程序的开发，主要操作的外模式的对象

SQL特点：

综合统一

SQL语言风格一致，可以实现数据库生命周期中的全部活动

高度非过程化

典型的第4代语言，不能进行过程化控制

面向集合的操作

数据操作一次可以针对若干记录的集合操作

提供两种使用方法

自含式语言：

直接在SQL*PLUS或其他客户端输入即能运行

嵌入式语言：

SQL语句可以作为高级语言的一个字符串，提交给数据库

简洁易用

英语式语法，无须编程经验

SQL语言的分类：

（仅对于当前的学习，简单分类）

DML（数据操纵语言）：

Inert、Update、delete（Commit、Rollback、Savepoint、select）

DDL（数据定义语言）：

Create、Drop、Alter

DCL（数据控制语言）：

Grant、Revoke

DML语言：

insert

1、字面值如何提供

insertintoemp（empno,ename,hiredate）

values（20,'hygj','01-1月-2009'）

数字型：

直接提供数字，不需要单引号，由于

数据库支持自动的隐式数据类型转换，所以即使加上单引号也不会报错

字符串型：

需要单引号引起，如果提供的字面值包含单引号，需要处理

'Tom''sMohter'

日期型：

日期默认提供的值字符串，此字符串需要遵循Oracle默认的日期格式

如果不想遵循默认格式需要to_date转换

2、满足约束：

notnull、unique、primarykey、Check、foreignkey

3、提供的数据顺序和列顺序一致

子查询插入数据：

（Oracle=SQLServer）

insertintotab_name（col_list）selectcol_listfromtab_name

注意：

列的个数、顺序和数据类型

多表插入：

（Oracle）

Insert[all|first]insert_into_clause[value_clause]subQuery;

范例：

Insertall【first】

whendeptno=10thenintodept10

whendeptno=20thenintodept20

whenjob=‘clerk’thenintoclerk

elseintoother

select*fromemp;

dept10、dept20、clerk、other：

表名

deptno=10deptno=20job=‘clerk’匹配的条件

all和first区别

如果一行部门是10，且岗位是clerk

all：

分别向dept10、clerk插入，同一行插入两次

first：

只匹配第一次，只向第一个满足条件的表插入

update：

（MSSQL相同）

Update

set=[,=]

[where];

delete：

（MSSQL相同）

Deletefrom[where];

在事务内部，即可以回退，创建日志

Truncatetabletable_name

DDL语句，不能回退也不做日志，因此，数据不能恢复

deletefromtable_name

truncatetabletable_name

结果一样，表的数据全部删除

delete：

在删除大量数据，速度慢

truncate：

在删除大量数据，速度快

四、事务

必须完整地完成或取消的逻辑操作单位；没有可以接受的中间状态。

"begin"

1、updateasetx=x+100wherey=111;111=1000——>1100

2、updatebsetx=x-100wherez=222;222=2000——>1900

3、commit;

"end"

事务的性质

原子性：

事务不可分割

一致性：

保持数据文件中的数据和日志中记录的数据一致

数据的修改前，需要在日志中记录

当提交（commit）了数据，则数据就变成永久的数据

当数据提交后，修改的数据，还是在内存中的，磁盘中的数据还是没有修改的。

而日志数据却一定会在磁盘中

日志记录的是：

111=1100222=1900

磁盘中的数据：

111=1000222=2000（修改的数据只是在内存中）

数据文件中的数据和日志中记录的数据不一致

如果两个语句在一个事务中则：

数据库在下次重启时，自动检查日志数据和数据文件数据

发现不一致，则按照日志中的数据，重新执行数据修改。

最终磁盘中数据：

111=1100222=1900

隔离性：

不受其他事务的干扰

两个事务之间，在没有结束事务之前，数据的修改不会影响对方的数据操作

持续性：

事务一旦被提交，数据的修改就是永久性的

提交：

commit，Oracle中事务默认不是自动提交的（MSSQLServer是自动提交的）

回退：

rollback，把没有提交的事务取消

保留点：

savepoint：

savepointsp_name

在事务的内部添加标记，用于部分的回退相应的操作。

事务：

手工提交：

DML语句是事务语句，需要手工提交，才能将数据变成永久的

自动提交：

DDL、DCL语句，不需要提交，自动将数据变成永久。

五、基本查询

1、单表执行Select：

select列列表或表达式from表名或视图或其他where条件orderby排序

关系型数据库中查询操作：

1、选择（where）

2、投影（列列表）

3、连接（join）

1、检索所有列：

列列表或*

select*fromemp

selectdeptno,dname,locfromdept

推荐使用列列表，不推荐使用*

因为*会降低程序的可读性，增加系统开销

2、理解行标识符：

rowid

selectrowid,enamefromemp

Oracle中ROWID伪列是用于表示每一行的物理地址的

3、执行算术运算：

+-*/

selectsal+commfromemp

对空值的处理：

空值与任何表达式进行算术运算都是空

4、日期运算：

selecthiredate+3fromemp——日期

selectto_date（'12-2月-09'）-hiredatefromemp--天数

5、列运算（3和4属于列运算）

6、使用列别名：

空格或AS，

需要双引号引起：

特殊符号、大小写区分等

selectename"employee'sname"fromemps

7、串联操作符：

“||”（SQLServer“+”）

select'姓名：

'||ename||'岗位：

'||jobfromemp

8、理解空值：

表示该列的值未知，判断使用isnull或isnotnull，处理函数

select*fromempwherecommisnull

selectsal+commfromemp;

selectsal+nvl（comm,0）fromemp;

9、禁止显示重复的行：

distinct

distinct：

消除重复：

selectcount（distinctjob）fromemp

--确定列基数

selectcount（distinctjob）/count（*）fromemp

--确定数据的重复几率

2、使用Where子句过滤数据

1、使用比较运算符

=><<=>=<>!

=anyallin

select*fromempwheredeptno=20;

select*fromempwheredeptno<>20;

select*fromempwheresal>3000;

select*fromempwheredeptnoin（10,20）

select*fromempwheresal>ANY（2000,3000,4000）--大于最小的（小于最大的）

select*fromempwheresal>ALL（2000,3000,4000）--大于最大的（小于最小的）

2、SQL操作符

like、in、between、isnull

1、使用like操作符：

字符串匹配，"_"匹配一个字符，"%"匹配任意多个字符

2、IN操作符：

匹配值列表

3、Between操作符：

匹配范围==大于等于小的而且小于等于大的

3、逻辑操作符

andornot：

优先级notandor

4、使用Orderby执行排序ASCDESC

五、rownum

rownum：

Oracle中的“伪列”在执行desc命令显示的列名中不存在的列

rownum为查询出的数据添加顺序的编号

可以基于编号的数据过滤

获得emp表的前10行（MSSQLServer“TOP”）

selectrownum,enamefromempwhererownum<=10

获得emp表的后4行

selectrownum,enamefromempwhererownum>10

以上语句不能得到相应的结果

rownum是在提取一行后就添加rownum，并且比较查询条件

如果提取第一行，rownum=1，rownum>10对于1来讲，是不符合条件的

第一行就是不满足条件的，如果再取一行，因为刚才的顺序编号1没有被使用

再次使用1，再次匹配条件也是不符合条件的，以此类推，就没有一行是，满足条件的了

1、selectrownumid,enamefromemp

createorreplaceviewa

as

selectrownumid,enamefromemp

通过创建视图，实现先给要提取的每一行添加编号

2、select*fromawhereid>10

视图a是先被执行的，然后在执行以上语句

3、上面两步合并

select*fromawhereid>10===>

select*from（selectrownumid,enamefromemp）awherea.id>10;

其中a称为内嵌视图，SQL解析器，首先解释执行a对应的SQL语句

获得工资收入最高的前5个人

1、先对根据工资进行数据排序

createviewb

as

select*fromemporderbysaldesc;

2、再对排序后的数据加上编号

createviewc

as

selectrownumid,b.*fromb;

3、根据编号获得前5位

select*fromcwhereid<=5;

4、合并成一条SQL语句

select*from（selectrownumid,t.*from

（select*fromemporderbysaldesc）t）ttwherett.id<=5;

获得工资第五到第八位的人员信息

select*from（selectrownumid,b.*from

（select*fromemporderbysaldesc）b）cwherec.idbetween5and8;

数据库分页程序需要用到Rownum伪列

==========聚合函数和分组查询==========

聚合函数：

同时对一组数据进行操作，并每组返回一行输出，也称之为分组函数

聚合函数主要是数字处理函数（忽略空值）

avg（X）：

平均值

count（X）：

计数（返回查询的行数）

max（X）：

最大值

min（X）：

最小值

sum（X）：

求和

variance（X）：

方差（用于测算离散趋势）

stddev（X）：

标准差（离均差，用于测算离散趋势，推荐使用）

1、avg（）

列出员工的平均工资

selectavg（sal）fromemp

列出