oracle基础培训课程.ppt

Oracle培训PPT,目录,数据库基础Oracle介绍Oracle使用SQL语言Oracle日常管理,目录,数据库基础Oracle介绍Oracle使用SQL语言Oracle日常管理,1.1 基本概念,数据所谓数据(Data)，就是描述事物的符号，在我们的日常生活中数据无所不在，数字、文字、图表、图像、声音等都是数据。人们通过数据来认识世界，交流信息数据库数据库(DB 即 Database)，顾名思义，就是数据存放的地方。在计算机中，数据库是数据和数据库对象的集合。所谓数据库对象是指表(Table)、视图(View)、存储过程(Stored Procedure)、触发器(Trigger)等。数据库管理系统数据库管理系统(DBMS即Database Management System)，是用于管理数据的一套软件。用户通过DBMS查询和操作数据库，DBMS维护数据的安全性和完整性，以及进行多用户下的并发控制和恢复数据库。,1.2 关系型数据库,关系模型关系模型(Relational Model)把世界看作是由实体Entity 和联系Relationship构成的。实体:现实世界中具有区分于其它事物的特征或属性集合。实体通常是以表的形式来表现的。表的每一行描述实体的一个实例，表的每一列描述实体的一个特征或属性。如:部门，汽车，员工，通讯录联系:实体之间的关系a)一对一 b)一对多 如班级和学生，一个 班级，有多个学生c)多对一 相反的，学生对班级，就是多对一的关系通过联系，就可以通过一个实体，查找相关实体,使系统形成一个有机的整体。,1.2 关系型数据库,关系型数据库既基于关系模型的数据库候选键：由一个或多个字段组成，能唯一标识表中的一行，而又不含多余的字段。主键(Primary Key):用来唯一标识表中一行的候选键，一个表只能有一个主键。外键(Foreign Key):一个表的字段，是另外一个表的主键。,候选键,外键,主键,目录,数据库基础Oracle介绍Oracle使用SQL语言Oracle日常管理,2.1 oracle体系结构,逻辑结构Oracle数据库按照一定的逻辑结构进行组织和管理表空间(tablespace)最高一级的逻辑存储单元，是连接逻辑存储结构和物理存储结构的桥梁段(segment)包括数据段，索引段，临时段区(extent)由一定数量连续的块组成的逻辑存储结构，是Oracle进行存储分配的单元块(block)Oracle读写数据文件的最小单位多个数据块组成区，多个区组成段，多个段组成表空间，多个表空间组成逻辑数据库。,2.1 oracle体系结构,创建表空间-数据文件用表空间create tablespace um_dat datafile D:oracleproduct10.2.0oradatadevum_dat01.dbf size 10M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED extent management local uniform size 128k online;-索引用表空间create tablespace um_idx datafile D:oracleproduct10.2.0oradatadevum_idx01.dbf size 10M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED extent management local uniform size 128k online;-临时表空间create temporary tablespace um_tmp tempfile D:oracleproduct10.2.0oradatadevum_tmp01.dbf size 10m AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED extent management local uniform size 128k;,2.1 oracle体系结构,物理结构物理存储结构主要是指在操作系统中，Oracle数据的存储和管理方式。它的组成包括：数据文件(data file)存储表、索引等实际数据的文件.一个表空间，可以有多个数据文件，一个数据文件，只能属于一个表空间控制文件(control file)存储数据库的物理结构等信息的文件。重做日志文件(redo file)记录数据库的修改操作和事务操作的文件其他文件,2.1 oracle体系结构,实例实例都是指后台进程与内存的组合,实例是一组计算机程序。启动数据库时，先创建实例，然后通过实例加载(mount)、打开(open)物理数据库,然后由这个实例来访问和控制数据库的各个物理结构。,2.1 oracle体系结构,内存结构系统全局区(Sytem Global Area),每个实例都只有一个SGA区。当多个用户连接到同一实例时，这些用户进程、服务进程共享SGA区。包括:a)数据高速缓存区b)字典缓存区c)重做日志缓存区d)SQL共享池程序全局区PGA（PROCESS GLOBAL AREA）是一个内存区，包含单个进程的数据和控制信息，所以又称为进程全局区。,2.1 oracle体系结构,数据字典由一些只读的数据字典表和数据字典视图组成。数据字典表保存的主要信息有：各种方案对象的定义信息存储空间的分配信息安全信息实例运行的性能和统计信息其他关于数据库的基本信息,2.2 Oracle数据库安装,1.选择高级安装,2.单击下一步,2.2 Oracle数据库安装,2.2 Oracle数据库安装,1.指定主目录的名称(用于区别安装的多个oracle)2.指定oracle的安装路径,2.2 Oracle数据库安装,这一项未执行，不用管它,检查结果为通过,2.1 Oracle数据库安装,选择是,2.2 Oracle数据库安装,2.2 Oracle数据库安装,2.2 Oracle数据库安装,数据库名和SID,连接数据库时会用到,2.2 Oracle数据库安装,2.2 Oracle数据库安装,2.2 Oracle数据库安装,2.2 Oracle数据库安装,设置几个系统用户的密码,2.2 Oracle数据库安装,2.2 Oracle数据库安装,2.2 Oracle数据库安装,2.5 使用PL/SQL Developer,客户端配置好网络服务名后，会在这个下拉框显示出来,2.5 使用PL/SQL Developer,这个窗口列出了所有的数据库对象，可以可视化的创建和管理,2.5 使用PL/SQL Developer,执行,2.6 用户和方案,oracle中，用户名和方案名相同，可把用户和方案理解为同一个概念。创建用户，同时也创建了一个方案。方案，又叫模式(Schema),是数据库对象的集合。-创建用户 um,密码oracle create user um identified by oracle default tablespace um_dat temporary tablespace um_tmp;-赋予权限grant connect to um;grant resource to um;grant unlimited tablespace to um;,2.7 常用数据类型,char:固定长度字符串，不足自动以空格补齐长度,最多2000个字节，如char(10)varchar2,可变长度字符串，最多4000个字节,如varchar2(100)number,数值型,最高精度28为，如number(24),最多24位，number(24,4),最多24位，其中小数最多4位date,存储日期和时间，精确到秒timestamp,存储时间，秒值精确到小数点后6位,目录,数据库基础Oracle介绍Oracle使用SQL语言Oracle日常管理,3.1 表,表由行和列组成，也称为二维表例：员工信息表记录：表中一行，称为一条记录字段：构成记录的各数据项，比如姓名、性别,3.1 表,创建表create table EMP(EMP_ID number(24)not null,EMP_CODE varchar2(10)not null,EMP_NAME varchar2(20)not null,E_MAIL varchar2(100),DEPT_ID number(24)not null)tablespace UM_DAT;,3.2 约束,主键约束-添加主键alter table EMP add constraint pk_emp_id primary key(EMP_ID);唯一约束alter table EMP add constraint uq_emp_code unique(EMP_CODE);外键约束 alter table EMP add constraint fk_dept_id foreign key(DEPT_ID)references dept(DEPT_ID);oracle自动为主键和唯一约束创建索引。,3.3 索引,索引作用类似书的目录，用于快速查找数据索引还可用户数据完整性限制，比如唯一索引，可以保证字段值的唯一性包含以下的类型：标准索引(B树)数据量非常大的情况下，查找依然很快惟一索引(Unique Index)比如员工编号,唯一索引查找最快位图索引(Bitmap)适合基数小的字段，比如性别,节约空间基于函数的索引(FBI),3.3 索引,创建标准索引create index IDX_DEPT_NAME on DEPT(dept_name);创建唯一索引create unique index IDX_DEPT_CODE on DEPT(dept_code);创建位图索引create bitmap index IDX_EMP_SEX on EMP(sex);创建函数索引create index IDX_EMP_BDATE on EMP(TO_CHAR(B_DATE,YYYY-MM-DD);,3.3 索引,哪些字段建议建立索引呢？select emp.e_mail,count(*)ct from empjoin dept on emp.dept_id=dept.dept_idwhere dept.dept_name=ITgroup by emp.e_mailorder by emp.e_mail,1.表间关联字段(外键)2.查询的字段3.group by的字段4.order by的字段,3.3 索引,身份证这类唯一属性，应建唯一索引 性别,只有男、女、未定等少数几种状态值，应创建位图索引，位图索引更节约空间对字段使用函数，会停用索引，可创建函数索引,3.3 索引,索引的优缺点优点:某些情况下，数据查找快缺点:a)在某些条件下，全表扫描比索引查找更快b)索引占用空间惊人，甚至超过表数据所占空间,不利于管理。c)创建索引后，会降低插入，修改，删除等操作的效率。,3.4 分区,分区就是把表和索引分成几大块，每一块存放到一个表空间上，性能调优的重要手段。有三种分区方式 1.散列分区 均匀分布数据，i/o设备负担均衡。2.范围分区按数据值的范围进行分区，比如将员工信息表，按入职时间分区，06年一个区，07年一个区，08年一个区,现在我要找一个06年入职的员工，只需要扫描06年那个分区，时间会快很多，磁盘i/o也会减少,3.4 分区,3.复合分区 范围分区和散列分区结合起来使用，先把数据按范围分区，然后在每个分区内再使用散列分区，把数据均匀分布注意：索引，分区这些调优技术，虽然在数据查询上效率得到了提高，但是，数据在插入，修改操作会更慢，因为在插入的时候，还需要做索引数据，分区数据的额外工作。数据库调优，需要考虑一个度的问题,3.4 分区,范围分区例子create table EMPS(SALARY NUMBER(24,4)not null,EMP_ID NUMBER(24)not null)partition by range(SALARY)(partition P_SALARY_2000 values less than(2000),partition P_SALARY_3000 values less than(3000);,目录,数据库基础知识Oracle基础表、索引、约束和分区SQL语句自定义函数、存储过程、触发器备份和恢复,4.1 oracle常用函数,字符函数 upper(str),转为大写 lower(str),转为小写 substr(str,n,m),从n位开始，截取m个字符 substr(str,n),从n位开始，截取后面字符 length(str),得到字符串的长度 ltrim(str),去掉左边空格 rtrim(str),去掉右边空格 instr(str,c),得到字符c在str的位置 lpad(str,n,c),将str补足为n位长度,不足左边用字符c代替 rpad(str,n,c),将str补足为n位长度,不足右边以字符c代替,4.1 oracle常用函数,字符函数例子-不区分大小写查询select emp_code,emp_namefrom empwhere upper(emp_name)=upper(Tom)-去掉空格select rtrim(ltrim(emp_code)from emp,4.1 oracle常用函数,数值函数 round(col,n)四舍五入round(457.628,2),小数点后2位四舍五入 结果 457.63round(457.628,-1),小数点前1位四舍五入 结果460trunc(col,n)截断数值trunc(457.628,2)结果457.62trunc(457.628,-1)结果450,4.1 oracle常用函数,日期函数 months_between(date1,date2),两个日期间的月数，结果为实数 add_months(date,m),增加m个月，m可以为负数，结果为减少m个月round,日期四舍五入trunc,截断日期last_day,当月最后一天,4.1 oracle常用函数,日期函数例子当前日期增加1个月select add_months(sysdate,1)from dual;去年同月select add_months(sysdate,-12)from dual;得到年初select trunc(sysdate,YYYY)from dual;得到月初select trunc(sysdate,MM)from dual;精确到天，截断小时分秒select trunc(sysdate)from dual;当月最后一天select last_day(sysdate)from dual;,4.1 oracle常用函数,转换函数日期转为字符:to_char(date1,format_model)format_model:转换后的显示格式YYYY 年,MM 月,DD 日,HH24 小时,MI 分,SS 秒例子:select to_char(sysdate,YYYY-MM-DD HH24:MI:SS)rqfrom dual;,4.1 oracle常用函数,转换函数字符转为日期to_date(2007-11-11,YYYY-MM-DD)数值转为字符select to_char(55676,fm99,999.00)from dualfm表示去掉前面的空格和0结果:55,676.00,4.2 SQL,SQL 结构化查询语言,是访问和处理数据库的标准语言.分为:DDL:数据定义语言 用于定义数据库对象 如:定义表，视图，存储过程等DML:数据操作语言 用于访问和处理数据 如：insert,update,delete,select,4.2 插入,插入单条insert into 表1(字段1,字段2,.)values(值1，值2,.)批量插入insert into 表1(字段1，字段2，.)select 字段1，字段2,.from 表2.这样可以把后面这个select查询的结果，批量插入表1中,4.2 插入,插入例子insert into emp(emp_id,emp_code,emp_name)values(1,231,luodaijun);,4.3 修改,update 表1set 字段1=值1，字段2=值2where 条件例：update empset emp_name=xiaoluowhere emp_id=1,4.4 删除,delete from 表1 where 条件例：delete from empwhere emp_id=1;-删除emp_id等于1的记录,4.5 select查询,1.用SELECT 子句来指定查询所需的列，多个列之间用逗号分开。例如：select p_id,p_name,cost from products2.可以使用符号*来选取表的全部列。例如：select*from products 3.在查询结果中添加列。例如：select p_id,p_name,qty,cost,cost*qty as sum_cost from products 4.使用WHERE子句。例如：select e_name from emp where salary between 2000 and 3000-salary的值=2000,=3000,4.5 select查询,5.使用DISTINCT 关键字,去掉重复结果，例如：select distinct dept_id from emp where salary 70006.使用IN关键字。例如：select e_name from employee where dept_id in(1001,1002)7.使用通配符,模糊查询。例如：select emp_name from emp where emp_name like luo%-查询所有emp_name以luo开头的数据8.使用ORDER子句,对结果排序。例如：select p_id,p_name,cost,qty from products where dept_id=1003 order by cost desc,quantity-默认是升序ASC,4.3 多表关联查询,内连接(结果为两表都包含的dept_id的行),4.3 多表关联查询,内连接ISO标准:(oracle 9i开始支持ISO标准写法)select e.emp_id,e.emp_name,d.dept_namefrom emp einner join dept d on e.dept_id=d.dept_idOracle:select e.emp_id,e.emp_name,d.dept_namefrom emp e,dept dwhere e.dept_id=d.dept_id,4.3 多表关联查询,左连接(以左表(emp)为准，右表没有的，为空值null),D05,左表有，右表无,4.3 多表关联查询,左连接ISO标准:select e.emp_id,e.emp_name,d.dept_namefrom emp eleft join dept d on e.dept_id=d.dept_idOracle:select e.emp_id,e.emp_name,d.dept_namefrom emp e,dept dwhere e.dept_id=d.dept_id(+),4.3 多表关联查询,右连接跟左连接相反，以右表为准ISO:select e.emp_id,e.emp_name,d.dept_namefrom emp eright join dept d on e.dept_id=d.dept_idOracle:select e.emp_id,e.emp_name,d.dept_namefrom emp e,dept dwhere e.dept_id(+)=d.dept_id,4.3 多表关联查询,全外连接(包含两表的数据),D05,左表有，右表无,D04,右表有，左表无,4.3 多表关联查询,全外连接Oracle 9i以上版本支持select e.emp_id,e.emp_name,d.dept_namefrom emp efull outer join dept d on e.dept_id=d.dept_id,4.4 条件表达式,Case 表达式 语法:case 表达式 when 值1 then 结果1 when 值2 then 结果2 else 默认结果 end,4.4 条件表达式,第二种方式 case when 条件1 then 结果1 when 条件2 then 结果2 else 默认结果 end,4.4 条件表达式,例子，交叉报表,4.4 条件表达式,例子，交叉报表select name,sum(yuwen)yuwen,sum(shuxue)shuxue,sum(huaxue)huaxue from(select name,case kechen when 语文 then fengshu end yuwen,case kechen when 数学 then fengshu end shuxue,case kechen when 化学 then fengshu end huaxue from table)tgroup by name,4.4 条件表达式,Decode函数 Decode(表达式,条件1,结果1,条件2,结果2,默认结果)注：默认值可以省略,4.4 条件表达式,Decode例子select swjg_dm,decode(swjg_bz,B,税务部门,J,税务机关)from dm_swjg,目录,数据库基础知识Oracle基础表、索引、约束和分区SQL语句视图，自定义函数、存储过程、触发器备份和恢复,5.1 视图,视图(View),虚拟表，命名的查询语句视图内部是一条select语句，用户可以像查询表一样查询视图如:create or replace view v_001asselect e.empno,e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;然后查询表一样查询视图 select*from v_001,5.2 存储过程,存储在数据库中，一段由PL/SQL语言编写的程序块。参数有三种类型a)in 输入参数b)out 输出参数c)in out 输入输出参数存储过程没有返回值，但可以通过out参数的方式返回结果。用于比较复杂的查询统计以及业务逻辑处理.,5.2 存储过程,for隐式游标例子:Create or replace procedure p_test(ls_emp_code varchar2)is begin for x in(select emp_id,salary from emp where emp_code=ls_emp_code)loop processData(x.emp_id,x.salary);-从游标取出记录,然后处理 end loop;end;,5.2 存储过程,显示游标例子create or replace procedure p_cursor_test is cursor my_cur is select*from scott.emp;cur_recode my_cur%rowtype;-记录类型begin open my_cur;loop fetch my_cur into cur_recode;-抓取数据，并赋值 EXIT WHEN my_cur%NOTFOUND;-游标没有数据，退出循环 dbms_output.put_line(cur_recode.empno);end loop;close my_cur;end p_cursor_test;,5.2 存储过程,存储过程返回结果集create or replace procedure p_get_emp(p_cur out sys_refcursor)asbegin open p_cur for select*from emp;end;这里通过oracle 9i开始提供引用游标，返回结果集在pl/sql developer里,选择存储过程，右键选择测试，可以测试及调试存储过程,5.3 函数,存储过程类似，存储在数据库中，一段由PL/SQL语言编写的程序块，可以有输入参数和返回值与存储过程的区别在于:1.在select语句中，只能使用函数2.函数可以索引(FBI),存储过程不能,5.3 函数,Create or replace function raise_salary(ls_money number)RETURN numberis ln_num number;begin ln_num:=ls_money+10000;RETURN ln_num;end;,5.4 JDBC调用存储过程、函数,创建用户登录存储过程create or replace procedure p_student_login(ln_stu_no varchar2,ln_pass varchar2,rtn_code out number)isbegin select count(*)into rtn_code from student where stu_no=ln_stu_no and pass=ln_pass;end;,5.4 JDBC调用存储过程、函数,CallableStatemment cst=conn.prepareCall(call p_student_login(?,?,?)cst.setString(1,001);/设置输入参数,?号位置1cst.SetString(2,123);/设置输入参数,?号位置2cst.registerOutParameter(3,java.sql.Types.Integer);/注册输出参数，?位置3cst.excute();/执行存储过程int a=cst.getInt(3);/取得存储过程返回输出参数的值，?位置3if(a=1)/通过认证,5.5 序列,序列，oracle提供的数字生成器create sequence SEQ_STU_IDminvalue 1maxvalue 99999999999999999999start with 1increment by 1;从序列得到数字:select seq_stu_id.nextval from dual;在insert语句使用序列insert into emp(emp_id)values(seq_stu_id.nextval);,5.4 触发器,存储在数据库里的带名的PL/SQL块，由事件触发。比如表插入修改数据时触发主要用于1.维护复杂的完整性约束 比如删除一条员工信息，在删除前，需要验证一些业务逻辑，比如是否有借款等，在删除前自动调用触发器2.审计修改3.表被修改时，给其他运行的程序发送信号,5.4 触发器,触发器种类语句级触发器行级触发器（for each row）:old:newinstead-of触发器,5.4 触发器,行触发器create or replace trigger t_empafter inserton empfor each rowbegin insert into emp_back(emp_code,emp_name)values(:old.emp_code,:old.emp_name);end;,5.4 触发器,语句级Create trigger emp_checkbefore/after/instead of insert or update or delete on empbeginif to_char(sysdate,DD)=01 then raise_application_error(-20000,月初);end if;end;,5.5 包,包是一个可以将相关对象存储在一起的PL/SQL结构。包提供了PL/SQL的全局变量包中过程可重载不同会话使用包的不同拷贝 简单的说，就是把相关的函数，存储过程组织在一起,目录,数据库基础知识Oracle基础表、索引、约束和分区SQL语句视图、自定义函数、存储过程、触发器备份和恢复,6 备份和恢复,备份一个ORACLE数据库有三种标准方式a)EXPORT(导出)b)脱机备份c)联机备份。导出方式是数据库的逻辑备份，其他两种备份方式都是物理文件备份。,6.1 逻辑备份,ORACLE 的实用程序exp 用来把数据库导出到一个文件中。exp导出备份有三种方式:full方式:导出整个数据库user方式:导出某个用户的所有对象table方式:导出部分表，不包括视图、存储过程,6.1 逻辑备份,exp程序用来导出数据库例：将scott用户的所有对象导出备份在cmd下输入下面命令:exp scott/oracledev file=d:scott.dmp log=d:log.txt导出过程写入日志d:log.txt用imp恢复数据库imp scott/oracledev file=d:scott.dmp log=d:log2.txt,6.2 脱机备份(冷备份),冷备份是数据库文件的物理备份。步骤如下:a)命令行输入sqlplus system/passwordtns_name as sysdbab)关闭数据库shutdown immediatec)将oradata下的数据库目录全部拷贝走比如我这里是D:oracleproduct10.2.0oradatadev目录恢复，把文件还原原来位置，启动数据库即可,