Oracle入门到精通.docx

1、Oracle入门到精通Oracle 安装使用编程精华文档一、oralce安装基本位置：Oracle安装在哪一个目录（注意不同版本的区别） %driver_name%appusername 目标驱动器：一般Oracle自动选择剩余空间最多的驱动器Oracle主目录：用于设置环境变量OracleHome，一般指向Oracle 程序文件所在的目录（db_1）类型：版本区别与操作系统无关 个人版 标准版 企业版选择Oracle产品时，是与OS有关的。win32，unix/linux、Mac OS、visita附加启动数据库 如果不选，只安装软件，没有数据库 如果选，需要设置数据库名 全局数据库名：在网

2、络上唯一标识数据库的名字 格式：dbname.domain ： SID.Domain 数据库的SID：数据库在本地计算机上的唯一标识 ,域名，是此数据库在网络上的后缀 ：在整个Internet是唯一的，称为全局数据库名 全局数据库名可以没有后缀（域名），一般用于内部网络 数据库口令： 不是数据库本身的口令，数据库用户的口令 sys（数据库中最高权限用户）， system（数据库中DBA用户），sysman、dbsnmp用户 此处设置的口令同时应用于以上用户数据库配置： 1、一般用途（OLTP） OLTP：联机事务处理：主要应用于Web，管理系统，电子商务等 事务在整个应用中数量很多，但是执行时

3、间都很短 2、数据仓库（OLAP） OLAP：联机分析处理：主要应用于数据分析、数据仓库和数据挖掘等 事务的数量是有限的，但是，每一个事务可能开销很大启用自动备份： 归档或非归档模式 归档模式：开销大，但可恢复，适用于高可靠性和高可用性环境 非归档模式：开销小，用于普通环境，数据的恢复依赖于备份用户的密码： 对于9i及老版本，数据库用户使用默认密码 sys：change_on_install system：manager 对于10g及以上的新版本，有用户自己设置密码(password)安装完成后的准备 因为Oracle对系统资源占用很大，一般需要把Oracle服务修改为手动 需要使用时才启动，

4、如何启动Oracle服务 Oracle要正常使用至少启动两个服务： 1、OracleOraDb11g_home1TNSListener：监听服务 用于处理网络中对Oracle访问的请求 2、OracleServiceHY：数据库服务 数据库服务（Oracle实例） 在机房使用Oracle时：修改两个文件 Oracle11g： D:appadministratorproduct11.1.0db_1networkADMIN Oracle10g： D:oracleproduct10.2.0db_1networkADMIN 1、listener.ora 2、tnsnames.ora 修改HOST的取值

5、： (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及以上的数据库中scot

6、t用户是被锁定的，需要开启此帐户1、在运行中sqlplus system/manager（数据库已经启动）2、在提示符下，执行命令 1、alter user scott account unlock; -解锁 2、alter user scott identified by tiger; -设置密码 3、grant connect,resource,create view to scott; -授权3、利用scott身份登录：运行：sqlplus scott/tiger 1、查询当前用户的表 select table_name from user_tables; 2、查询表的结构 desc t

7、able_name4、在sqlplus中如何提交语句 1、在语句后加“；”提交语句到Oracle 2、如果换行行了，“；”或者“/”Web管理： 1、httpserver开启：http:/127.0.0.1/isqlplus（只适合于Oracle 9i） 2、OracleDBConsolehy开启：第三方工具（以后开发时常用） 1、SQL Devepler 2、PL/SQL Devepler 3、Data Studio Oracle卸载1、执行卸载程序2、删除注册表项 HLMsoftwareoracle HLMsystemcurrentcontrolsetservice3、删除目录 drive

8、r:oracle(app) C:program fileoracle二、 SQL*PLUSSQL*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、

9、在菜单中启动“应用程序开发” 2、在“运行”直接输入：SQLPLUS 或者 sqlplusw 3、打开CMD，运行SQLPLUS 或者 sqlpluswSQL*Plus的登录（重点） 提示符界面SQLPLUS： sqlplus username/passwordlocalservicename sqlplus 用户名/密码本地服务名 图形界面 用户名：Oracle用户名 口令：密码 主机字符串：= 本地服务名，如果留空默认连接本机 本地服务名： 主机字符串=网络服务名=本地服务名 =主机名+端口号+协议+数据库名 =IP+端口号+协议+数据库名 本地服务名是用来说明客户端连接的数据库服务器的相

10、关信息 保存的是服务器的相关信息什么是服务器的信息？ 哪一个服务器：IP、服务器的主机名 哪一个端口：端口决定了所提供的服务的类型 比如：80代表是web应用，21代表FTP应用，1433代表SQL Server数据库 Oracle的默认端口号是1521 用什么语言？：协议（计算机间通信的语言就是协议） 使用什么数据库呢？：数据库名（SID） 在客户端创建一个本地服务名，用于方便的连接服务器 scott/tigerhygj 交互模式：先输入命令然后在根据提示输入信息 sqlplus 1、用户名：scottora，然后输入密码 2、用户名：scott/tigerora 非交互模式：直接在命令的后

11、面跟用户/密码本地服务名SQL*PLUS的退出：quit exit都可以直接点击“关闭”按钮不是正常的退出！没有提交的事务会自动回退，所以不推荐使用当在SQL*plus中输入错误命令，可以使用ed命令编辑错误的语句（edit），只能编辑刚刚执行过的指令 SQL*Plus调用外部脚本命令1：start driverpathfilename命令2： driverpathfilename命令和start区别：可以在脚本中使用，而start不行sqlplus scott/tigerora c:mysql.txt把上面命令放到一个以bat或cmd为扩展名的文件中，就可以执行了用户的切换：可以在不退出SQ

12、L*Plus的情况下，可以切换用户connect=conn如果不想退出SQL*Plus，只是想注销用户disconnect=discSQL*Plus会自动为查询结果进行分页，设置行宽默认行宽是show linesize pagesizeset linesize 100set pagesize 16设置列格式1、对字符列设置格式（列宽） 在默认情况下，列宽是根据列的定义显示的 column col_name format anumber column ename format a202、对数字列设置格式（数字的表现形式） 定义数字的小数点位置、千分位位置、货币类型等 column col_nam

13、e format exp_format 0：强制显示前导0，不够位数的，用0补齐 9：不显示前导0，原来多少位就显示多少位 L：本地货币符号 $：代表美元 ,：千分位分隔符 .：小数点SQL*Plus不是自动提交的set autocommit on设置脱机：开始脱机： 设定将运行结果脱机到那个文件 spool driver:pathfilename 结束脱机： 结束脱机，并将结果保存到目标文件 spool off一旦结束脱机，SQL*Plus中的数据就自动存储在上面设置的文件中show all：显示SQL*Plus所有的设定三、 DMLSQL语言SQL支持关系数据库的三级模式结构1、外模式：开

14、发人员所面对的一层，视图和部分基本表（安全性低）2、模式层：描述商业需求，实体与实体间的关系，基本表，数据库设计主要完成的内容3、内模式：解决数据库底层的实现，表空间和数据文件一般来讲，应用程序的开发，主要操作的外模式的对象SQL特点： 综合统一 SQL语言风格一致，可以实现数据库生命周期中的全部活动 高度非过程化 典型的第4代语言，不能进行过程化控制 面向集合的操作 数据操作一次可以针对若干记录的集合操作 提供两种使用方法 自含式语言：直接在SQL*PLUS或其他客户端输入即能运行 嵌入式语言：SQL语句可以作为高级语言的一个字符串，提交给数据库 简洁易用 英语式语法，无须编程经验SQL语言

15、的分类：（仅对于当前的学习，简单分类） DML（数据操纵语言）： Inert、Update、delete（Commit、Rollback、Savepoint、select） DDL（数据定义语言）： Create、Drop、Alter DCL（数据控制语言）： Grant、RevokeDML语言：insert 1、字面值如何提供 insert into emp(empno,ename,hiredate) values(20,hygj,01-1月-2009) 数字型： 直接提供数字，不需要单引号，由于 数据库支持自动的隐式数据类型转换，所以即使加上单引号也不会报错 字符串型：需要单引号引起，如果

16、提供的字面值包含单引号，需要处理 Toms Mohter 日期型： 日期默认提供的值字符串，此字符串需要遵循Oracle默认的日期格式 如果不想遵循默认格式需要to_date转换 2、满足约束： not null、unique、primary key、Check、foreign key 3、提供的数据顺序和列顺序一致子查询插入数据：（Oracle=SQL Server） insert into tab_name(col_list) select col_list from tab_name 注意：列的个数、顺序和数据类型多表插入：（Oracle） Insert all | first inse

17、rt_into_clause value_clause subQuery; 范例： Insert all 【first】 when deptno=10 then into dept10 when deptno=20 then into dept20 when job=clerk then into clerk else into other select * from emp; dept10、dept20、clerk、other：表名 deptno=10 deptno=20 job=clerk 匹配的条件 all和first区别 如果一行部门是10，且岗位是clerk all：分别向dept1

18、0、clerk插入，同一行插入两次 first：只匹配第一次，只向第一个满足条件的表插入update：（MSSQL相同） Update set =, = where ;delete：（MSSQL相同） Delete from where ; 在事务内部，即可以回退，创建日志 Truncate table table_name DDL语句，不能回退也不做日志，因此，数据不能恢复 delete from table_name truncate table table_name 结果一样，表的数据全部删除 delete：在删除大量数据，速度慢 truncate：在删除大量数据，速度快 四、 事务必须

19、完整地完成或取消的逻辑操作单位；没有可以接受的中间状态。begin 1、update a set x=x+100 where y=111; 111=1000 11002、update b set x=x-100 where z=222; 222=2000 19003、commit;end 事务的性质 原子性：事务不可分割 一致性：保持数据文件中的数据和日志中记录的数据一致 数据的修改前，需要在日志中记录 当提交（commit）了数据，则数据就变成永久的数据 当数据提交后，修改的数据，还是在内存中的，磁盘中的数据还是没有修改的。 而日志数据却一定会在磁盘中 日志记录的是：111=1100 222

20、=1900 磁盘中的数据：111=1000 222=2000（修改的数据只是在内存中） 数据文件中的数据和日志中记录的数据不一致 如果两个语句在一个事务中则： 数据库在下次重启时，自动检查日志数据和数据文件数据 发现不一致，则按照日志中的数据，重新执行数据修改。 最终磁盘中数据：111=1100 222=1900 隔离性：不受其他事务的干扰 两个事务之间，在没有结束事务之前，数据的修改不会影响对方的数据操作 持续性：事务一旦被提交，数据的修改就是永久性的 提交：commit，Oracle中事务默认不是自动提交的（MS SQL Server是自动提交的）回退：rollback，把没有提交的事务取

21、消保留点：savepoint：savepoint sp_name 在事务的内部添加标记，用于部分的回退相应的操作。事务：手工提交： DML语句是事务语句，需要手工提交，才能将数据变成永久的自动提交： DDL、DCL语句，不需要提交，自动将数据变成永久。五、基本查询1、单表执行Select：select 列列表或表达式 from 表名或视图或其他 where 条件 order by 排序 关系型数据库中查询操作： 1、选择（where） 2、投影（列列表） 3、连接（join） 1、检索所有列：列列表 或 * select * from emp select deptno,dname,loc f

22、rom dept 推荐使用列列表，不推荐使用* 因为*会降低程序的可读性，增加系统开销 2、理解行标识符：rowid select rowid,ename from emp Oracle中ROWID伪列是用于表示每一行的物理地址的 3、执行算术运算：+ - * / select sal+comm from emp 对空值的处理：空值与任何表达式进行算术运算都是空 4、日期运算： select hiredate+3 from emp 日期 select to_date(12-2月-09)-hiredate from emp -天数 5、列运算（3和4属于列运算） 6、使用列别名：空格或AS， 需

23、要双引号引起：特殊符号、大小写区分等 select ename employees name from emps 7、串联操作符：“|”（SQL Server “+”） select 姓名：|ename| 岗位：|job from emp 8、理解空值：表示该列的值未知，判断使用is null或is not null，处理函数 select * from emp where comm is null select sal+comm from emp; select sal+nvl(comm,0) from emp; 9、禁止显示重复的行：distinct distinct：消除重复： sele

24、ct count(distinct job) from emp -确定列基数 select count(distinct job)/count(*) from emp -确定数据的重复几率 2、使用Where子句过滤数据 1、使用比较运算符 = = != any all in select * from emp where deptno=20; select * from emp where deptno20; select * from emp where sal3000; select * from emp where deptno in (10,20) select * from emp

25、 where sal ANY(2000,3000,4000) -大于最小的（小于最大的） select * from emp where sal ALL(2000,3000,4000) -大于最大的（小于最小的） 2、SQL操作符 like、in、between、is null 1、使用like操作符：字符串匹配，_匹配一个字符，%匹配任意多个字符 2、IN操作符：匹配值列表 3、Between操作符：匹配范围 = 大于等于小的 而且小于等于大的 3、逻辑操作符 and or not：优先级 not and or 4、使用Order by执行排序 ASC DESC 五、 rownumrownu

26、m：Oracle中的“伪列”在执行desc命令显示的列名中不存在的列rownum为查询出的数据添加顺序的编号可以基于编号的数据过滤获得emp表的前10行（MS SQL Server “TOP”） select rownum,ename from emp where rownum10 以上语句不能得到相应的结果 rownum是在提取一行后就添加rownum，并且比较查询条件 如果提取第一行，rownum=1，rownum10对于1来讲，是不符合条件的 第一行就是不满足条件的，如果再取一行，因为刚才的顺序编号1没有被使用 再次使用1，再次匹配条件也是不符合条件的，以此类推，就没有一行是，满足条件的

27、了 1、select rownum id,ename from emp create or replace view a as select rownum id,ename from emp 通过创建视图，实现先给要提取的每一行添加编号 2、select * from a where id10 视图a是先被执行的，然后在执行以上语句 3、上面两步合并 select * from a where id10 = select * from (select rownum id,ename from emp) a where a.id10; 其中a称为内嵌视图，SQL解析器，首先解释执行a对应的SQL

28、语句 获得工资收入最高的前5个人 1、先对根据工资进行数据排序 create view b as select * from emp order by sal desc; 2、再对排序后的数据加上编号 create view c as select rownum id,b.* from b; 3、根据编号获得前5位 select * from c where id=5; 4、合并成一条SQL语句 select * from (select rownum id,t.* from (select * from emp order by sal desc) t) tt where tt.id=5;

29、获得工资第五到第八位的人员信息 select * from (select rownum id,b.* from (select * from emp order by sal desc) b) c where c.id between 5 and 8;数据库分页程序需要用到Rownum伪列 =聚合函数和分组查询=聚合函数：同时对一组数据进行操作，并每组返回一行输出，也称之为分组函数聚合函数主要是数字处理函数（忽略空值） avg(X) ：平均值 count(X) ：计数（返回查询的行数） max(X) ：最大值 min(X) ：最小值 sum(X) ：求和 variance(X) ：方差（用于测算离散趋势） stddev(X) ：标准差（离均差，用于测算离散趋势，推荐使用）1、avg() 列出员工的平均工资 select avg(sal) from emp 列出