玩转Oracle-10g实战教程.doc

玩转Oracle 10g实战教程Oracle第一讲0、 引言1、 为什么要学习oracle2、 Oracle的安装、启动及卸载3、 介绍oracle及oracle公司的背景4、 Oracle开发工具介绍5、 Sql*plus的常用命令6、 Oracle用户的管理学习目标1、 学会安装、启动、卸载oracle2、 使用Sql*plus工具3、 掌握oracle用户管理4、 学会在oracle中编写简单的select语句引言 一个问题淘宝网，天涯网，校友网.都有各自的功能，那么当我们关闭系统的时候，下次再访问这些网站时，问什么它们各自的信息还存在？再比如C/S的软件，比如网游、QQ，它们又是怎样保存数据的？A. 游戏积分 B.QQ聊天记录.引言 解决之道n 解决之道-文件、数据库我们刚学习过文件，大家可能回答用文件就可以保存数据嘛！没有错，可以如果用文件保存数据存在几个缺点：（1） 文件的安全性问题（2） 文件不利于查询和对数据的管理（3） 文件不利于存放海量数据（4） 文件在程序中控制不方便为了解决上述问题，专家们设计出更加利于管理数据的东西-数据库（本质就是一个软件），他能更有效的管理数据。数据库是衡量一个程序员水平的重要指标。举一个生活化的案例说明如果说图书馆是保存书籍的，那么数据库就是保存数据的。数据库服务器、数据库和表的关系l 所谓数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。l 为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。l 数据库服务器、数据库和表的关系如图所示：Client(专门用于访问数据库)Oracledbms(database management system)DB实例数据库对象(表、视图、序列、函数、包、过程、触发器)数据库对象(表、视图、序列、函数、包、过程、触发器)数据库对象(表、视图、序列、函数、包、过程、触发器)数据库的三层结构DB实例DB实例为什么选择oracle-性能优越概述：目前主流数据库包括l 微软： sql server 和accessl 瑞典MySQL： AB公司MySQLl IBM公司： db2【处理海量数据】l 美国Sybase公司： Sybasel IBM公司： informixl 美国oracle公司： oracle小型数据库中型数据库大型数据库海量数据库accessSql Server 2008oracleDb2foxbaseMy sqlsysbaseFoxproinformixSql Server 2008My sql应当如何选择数据库（项目中）（1） 标的（2） 功能要求（3） 多少人用（并发问题)（4） 安全、稳定性要求（5） 操作系统（UNIXsolaris,freebsd,hp unix,aix/Linux/Windows)Oracle 数据库的认证考试简单介绍ocm(高级认证)masterocp(中级认证)professionaloca(初级认证)Oracle certified associateOracle的安装系统要求n 操作系统最好为windows servern 内存最好在256M以上n 硬盘空间需要2G以上Oracle公司介绍-oracle的含义神喻 代神说话的人 甲骨文 暗示公司预作霸主的决心地位：全球第一大数据库厂商，全球第二大独立软件供应商oracle 10g比oracle 9i增加了什么？简单说：10g支持网格（Grid），支持自动管理（Automatic Management）。详细说：10g的g是“Grid”缩写，支持网格计算，即：多台结点服务器利用高速网络组成一个虚拟的高性能服务器，负载在整个网格中均衡（LoadBalance），按需增点，避免单点故障（Single Point of Faliure）。安装容易，安装工作量比9i减少了一半。新增给予浏览器的企业管理器（EnterpriseManager）。如果在安装的时候，忘记对某个用户解锁，比如scott，可以通过system来对此用户解锁，步骤：（1） 先使用system登录（2） 然后输入：alter user scott account unlockoracle公司介绍-产品线v 数据库服务器：2007年最新版本11Gv 应用服务器：Oracle Application Serverv 开发工具：OracleJDeveloper，Oracle Designer，Oracle Developer，等等v 应用软件（主要竞争对手：德国SAP公司。）v 企业资源计划（ERP）软件v 客户关系管理（CRM）软件v 人力资源管理软件（HCM）oracle管理工具的介绍u sql*plus是oracle自带的工具软件，主要用于执行sql语句，pl/sql块。如何使用：1） 在开始程序oracle oradb_home10gapplication developmentsql*plus2） 在运行栏中输入：sqlplusw即可。u sqlplus是DOS下操作oracle的工具，其功能和sql*plus相似。1） 在运行栏中输入sqlplus2） 找到该可执行文件sqlplus.exe，在oracle主目录ora10gbinsqlplus.exe，鼠标双击即可。u pl/sql developer属于第三方软件，主要用于开发、测试、优化oracle pl/sql的存储过程，比如：触发器，此软件oracle不带，需要单独安装。u Enterprise manager console(企业管理器)oracle 10g是通过web管理的，一般默认端口是5500，也有1158的。访问url（请一定保证oracle服务启动了）：OracleDBConsole数据库实例名http:/ip:1158(也可能是5500)/emhttp:/机器名:端口/emsql*plus常用命令n 连接命令（1） connect用法：conn 用户名/密码网络服务器名 as sysdba/sysoper当用特权用户身份连接时，必须带上as sysdba或是as sysoper该命令常用于切换当前用户。建议在登录的时候使用普通用户，比如scott，如果确实需要system用户，则可以使用该命令切换为高级用户。（2） disconnect 说明：该命令用来断开与当前数据库的连接，但是不规程sqlplus窗口（3） password 说明：该命令用于修改用户的密码，如果要想修改其它用户的密码，需要用sys/system登录。基本用法：password 用户名如果给自己修改密码，则可以不带用户名，如果给别人修改密码（前提：是system用户或者sys用户来修改），则需要带用户名。（4） show user 说明：显示当前用户名（5） exit 说明：该命令会断开与数据库的连接，同时会退出sql*plusn 交互命令（1） & 说明：可以替代变量，而该变量在执行时，需要用户输入。 sqlselect * from emp where job=&job;（2） edit 说明：该命令可以编辑指定的sql脚本，例子：sqledit d:a.sql;（3） spool 说明：该命令可以将sqlplus屏幕上显示的内容输出到指定文件中取。例子：sqlspool d:b.sql； 并输入sqlspool off; 基本用法：spool on； spool d:/bak.sql select * fromemp； spool offn 显示和设置环境变量概述：可以用来控制输出的各种格式。（1） linesize 用于控制每行显示多少个字符，默认80个字符。基本用法：set linesize 字符数aqlshow linesizesqlset linesize 120（2） pagesize 用于控制每页显示多少行数目，默认14行。sqlset pagesize 40oracle用户管理n 创建用户（简单版）概述：在oracle中要创建一个新的用户使用create user语句，一般是具有dba（数据库管理员）的权限才能使用。基本语法：create user 用户名 identified by 密码n 给用户修改密码概述：如果给自己修改密码可以直接使用sqlpassword 用户名如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限，也可以使用password 用户名sqlalter user 用户名 identified by 新密码u 表空间什么是表空间：表存在的空间，一个表空间是指向具体的数据文件表1表2表3表空间USER表空间TEMP文件1文件2文件3磁盘创建用户（细节）例子：sqlcreate user shunping identified by m123 default tablespace users temporary tablespace temp quota 3m on users;identified by 表明用户shunping将用数据库方式验证default tablespace users /用户的表空间在users上tempporary tablespace temp /用户shunping的临时表建在temp空间quota 3m on users /表明用户顺平建立的数据对象（表、索引、视图、pl/sql块.）最大只能是3m，刚刚创建的用户是没有任何权限的，因此，需要dba给该用户授权sqlgrant connect to shunping如果你希望该用户建表没有空间的限制sqlgrant resource to shunping 如果你希望该用户成为dba权限系统权限对象权限系统权限是和数据库管理相关的权限：create sessioncreate tablecreate index create viewcreate sequencecreate trriger.对象权限是和用户操作数据对象相关的权限：updateinsertdeleteselect.xiaomingsystem分配权限角色预定义角色把常用的权限集中起来，形成角色dbaconnectresource自定义角色分配角色sqlgrant dba to shunping 分配权限n 用户管理的综合案例概述：创建的新用户是没有任何权限的，甚至连登陆的数据库的权限都没有，需要为其制定相应的权限。给一个用户赋权限使用的命令grant，回收权限使用命令revoke。基本语法：grant 权限/角色 to 用户 revoke 权限/角色 from 用户创建xiaoming，并赋予connect和resource回收权限综合案例：创建一个用户xiaohong，然后给他分配两个角色，可以让xiaohong登录、创建表，可以操作（CRUD）自己创建表，然后回收角色，最后删除用户。(1) 使用system创建xiaohongcreate user xiaohong identified by m123;(2) 给xiaohong分配几个常用角色grant connect to xiaohong;grant resource to xiaohong;(3) 让xiaohong登录connect xiaohong/m123;(4) xiaohong修改密码password xiaohong(5) xiaohong创建一张简单的表create table .(6) 使用system登录，然后回收角色revoke connect from xiaohong;revoke connect from xiaohong;(7) 删除用户drop user xiaohong cascade;当我们删除一个用户的时候，如果这个用户自己已经创建过数据对象，那么我们在删除用户的时候，需要加选项cascade，表示把这个用户删除的同时，把该用户创建的数据对象一并删除。u 方案（schema）理解：当一个用户创建好后，如果该用户创建了任意一个数据对象，我们的dbms就会创建一个对应的方案与该用户对应，并且该方案的名字和用户名一致scottoracle dbmsdb实例（ORC1）scott方案对象systemEMP表xiaohong方案对象表对象xiaohong小技巧：如果希望看到某个用户的方案究竟有什么数据对象，我们可以用pl/sql developer方案的实际应用，要求：让xiaohong可以查看scott的emp表步骤1：先用scott登录，conn scott/tiger;步骤2：赋权限grant select update delete insert allon emp to xiaohong;步骤3：xiaohong查询scott的emp表时，需要用户创建练习1、 创建用户tea、stu，并给这两个用户resource，connect角色conn system/123456;create user tea identified by tea;grant resource to tea;grant connect to tea;create user stu identified by stu;grant resource to stu;grant connect to stu;2、 使用scott用户把对emp表的select权限交给teaconn scott/tiger;grant select on emp to tea;3、 使用tea查询scott的emp表conn tea/tea;select * from scott.emp;4、 使用scott用户把对emp表的所有权限赋给teaconn scott/tiger;grant all on emp to tea;5、 使用tea更新/删除/插入scott的emp表conn tea/tea;update scott.emp set job=”Teacher where job=&job;delete from scott.emp where job=&job;insert into scott.emp values(8888,FORD,Teache,769808-9月-81,1500,300,20);6、 使用scott收回权限conn scott/tiger;revoke select on scott.emp from tea;revoke all on scott.emp from tea;7、 想办法让tea把自己拥有的对scott.emp的权限转给stu；scott-tea-stu权限转移.conn scott/tiger;grant all on scott.emp to tea with grant option;conn tea/tea;grant select on scott.emp to stu;/with grant option 表示得到对象权限的用户，可以把权限继续分配。/with admin option 得到系统权限，可以继续分配8、 使用stu查询scott用户的emp表conn stu/stu;select * from scott.emp;9、 使用tea收回stu的权限conn tea/tea;revoke select on scott.emp from stu;n 使用profile管理用户口令概述：profile是口令限制、资源限制的命令集合，当建立数据时，oracle会自动建立名称为default的profile，当建立用户没有指定profile选项，那么oracle就会将default分配给用户。（1） 账户锁定概述：指定该账户（用户）登录时最多可以输入密码的次数，也可以指定用户锁定的时间（天）一般用dba的身份去执行该命令例子：指定scott这个用户最多只能尝试3次登录，锁定时间为2天，看看怎么实现：创建profile文件sqlcreate profile lock_account limit failed_login_attempts 3 password lock_time 2;分配文件sqlalter user scott profile lock_account;给账户（用户）解锁sqlalter user 用户名 account unlock;（2） 终止口令为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba身份来操作。例子：给前面创建的用户tea创建一个profile文件，要求该用户每隔10天要修改自家的登录密码，宽限期为2天，看看怎么操作。sqlcreate profile myprofile limit password_life_time 10 password_grace_time 2;sqlalter user tea profile myprofile;（3） 口令历史概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史这样oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样是，就提示用户重新输入密码。例子：（1） 建立profilesqlcreate profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 1;password_reuse_time /指定口令可重用时间即10天后就需要修改（2） 分配给某个用户sqlalter user tea profile myprofile;（4） 删除profile概述：当不需要某个profile文件时，可以删除该文件sqldrop profile profile文件名；oracle数据库启动流程oracle也可以通过命令行的方式启动，我们看看具体怎样操作。oracle启动流程-windows下1） lsnrctl start (启动监听)2） oradim -startup -sid 数据库实例名oracle启动流程-linux下1） lsnctl start （启动监听）2） sqlplus sys/change_on_install as sysdba(以sysdba身份登录，在oracle10g后可以这样写)sqlplus /nologconn sys/change_on_install as sysdba3） startupOracle第2讲0.数据库的一些基本概念1.表的管理2.基本查询3.复杂查询4.创建数据库实例基本概念-数据在数据库中存储方式User表列（column)IDnameage行(row)1shunping202lisi233wang24java程序class Users private int d; private String name; private int age;Users user1=new Users();user1.setId(1);user1.setName(“shunping”);user1.setAge(20);类Usersl 表的一行称之为一条记录l 表中一条记录对应一个java对象的数据id=3name=”wang”age=24User对象在java程序和数据表对应的时候类Users表usersUsers实例表的一条对应CREATE TABLE table_namefield1 datatype,field2 datatype,field3 datatype,field:指定列明 datatype:指定列类型基本管理-创建表（基本语句）注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user对象idnamepasswordbirthdayid int name stringpassword stringbirthday dateSQL create table users( 2 id number, 3 name varchar2(32), 4 password varchar2(32), 5 birthday date);Table created基本管理-oracle常用数据类型分类数据类型说明文本、二进制类型CHAR（size) char(20)VARCHAR2(size) vahchar2(32)nchar(n)nvchar2(n)clob(character large object)blod(binary image object)定长 最大2000字符变长 最大4000字符Unicode数据类型，定长 最大2000字符Unicode数据类型，变长 最大4000字符字符型大对象 最大8TB 1T=1024G二进制数据 可以存放图片/声音 8TB数值类型number(p,s)p为整数，s为小数位 范围：1=p=38,-84=s=127保存数据范围：-1.0e-130=number value=1.0e+126保存在机器内部的范围：122bytes时间日期dateTIMESTAMPn)包含年月日，时分秒。默认格式：DD-MON-YYYY。从公元前4712年1月1日到公元4712年12月31日的所有合法日期n的取值为09表示指定TIMESTEAMP中秒的小数位数。N为可选。如果n为0，timestamp与date等价（不推荐）u number可以理解成是一个可变的数值类型，比如number(12),你可以放一个小整数，它占用的字节数就少；你放一个大整数，它占用的字节数就多，很好！varchar2(size)变长，最大可以存放4000个字符举例：create table test2(name varchar2(16);比如存放了abc字符串，实际上在数据库中，就只存放abc，其余字符空间就回收。如果我们的数据的长度是固定的，比如商品编号（8位），则应当使用char来存放，因为这样存取速度快，如果存放数据的长度是变化的，则使用varchar2。nchar（size）1. 定长2. 编码方式：unicode3. 举例：create table test3(name nchar(2);create table test3(name char(2);说明：一个汉字，占用nchar的一个字符空间， 一个汉字，占用char的两个字符空间nvchar2(size）1. 变长2. 编码方式：unicode3. 最大字符数40000clob字符型大对象1. 变长 2.最大8tbblob 二进制大对象1. 变长 2.最大8tb说明：我们在实际开发中很少把文件存放数据库（效率问题），实际上我们一般记录文件的路径，然后通过IO/网络来操作。如果我们的要求对文件安全性较高，可以考虑放入数据库。numberNUMBERprecision,scale) NUMBER(p,s)1. number可以存放整数，也可以存放小数2. 变长3. number(p,s) p为整数位，s为小数位，范围：1=p=38，-84=s=127 保存数据范围：-1.0e-130=number value0精确到小数点右边s位，并四舍五入。然后检验有效位是否=p。s0精确到小数点左边s位，并四舍五入。然后检验有效位是否desc 表名; 查询所有列select * from 表名; 查询指定列select 列1，列2，. . . from 表名; 如何取消重复行select distinct deptno,job from emp order by deptno; 什么是重复行，返回的数据完全一样，才是重复行。？查询SMITH的薪水、工作、所在部门select sal,joB,dempno from emP wheRe EName=smith;特别说明：oracle的sql语句不区分大小写，但是内容是区分大小写的。oracle查询中使用的算术表达式select enamel，sal*13+nvl（comm，0）*13 as 年薪from emp；别名可以加双引号，可以不用双引号，可以用汉字，可以加as，但oracle不允许别名加单引号。说明：nvl函数是oracle提供的，用于处理数据null的问题，基本用法：nvl（comm，0）：如果comm为空null，则返回0；如果不为空，则返回本身的值。|的使用在查询的时候，如果希望吧多列拼接起来，作为一列返回，可以使用|，具体用法：select ename |是一个|job from emp;面试题：我们希望删除用户，同时保留该用户的数据对象，怎么处理？（1） 锁定用户alter user scott account lock；（2） 这时该用户已经不能登录到数据库，但是system用户仍然可以使用它的数据对象。（3） 解锁用户alter user scott account unlock;n where字句的用法? 如何显示工资高于3000的员工 select * from emp where sal3000;? 如何查找1982.1.1后入职的员工select * from emp where to_char(hiredate,yyyy-mm-dd)1982-01-01;? 查询1980年入职的员工 select * from emp where to_char(hiredate,yyyy)=1980;? 如何显示工资在2000到2500的员工情况select * from emp where sal=2000 and sal select ename,sal from emp where ename like S%;? 如何显示第三个字符为大写O的员工的姓名和工资SQL select ename,sal from emp where ename like _O%;n 在where条件中使用in? 如何显示empno为123,345,800.的雇员情况select * from emp where empno=123 or empno=345 or empno=800;select * from emp where empno in(123,345,800);n 使用is null的操作符? 如何显示没有上级的雇员的情况select * from emp where mgr is null;n 使用逻辑操作符合? 查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的Jselect * from emp where (sal500 or job=MANAGER) and ename like J%;n 使用order by字句 (对查询结果进行排序显示 asc升序 desc降序 默认asc)? 如何按照工资从低到高的顺序显示雇员的信息select * from emp order by sal;? 按照部门号升序雇员的入职时间降序排列select * from emp order by deptno,hiredate desc; order by 可以按照列的序号来排序select eanme,sal from emp order by 2; /不能使无中生有的列号，否则出错n 使用列的别名排序 oracle支持使用别名排序select ename,sal*13+nvl(comm,0)*13 “年薪” from emp order by “年薪” asc;一个单词的别名可以不用双引号，但如果是两个或多个单词作为一个别名就需要使用双引号圈中oracle表复杂查询n 说明在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的sel