oracle基础培训课程.ppt

1、Oracle培训PPT,目录,数据库基础 Oracle介绍 Oracle使用 SQL语言 Oracle日常管理,目录,数据库基础 Oracle介绍 Oracle使用 SQL语言 Oracle日常管理,1.1 基本概念,数据 所谓数据(Data)，就是描述事物的符号，在我们的日常生活中数据无所不在，数字、文字、图表、图像、声音等都是数据。人们通过数据来认识世界，交流信息 数据库 数据库(DB 即 Database) ，顾名思义，就是数据存放的地方。在计算机中，数据库是数据和数据库对象的集合。所谓数据库对象是指表(Table)、视图(View)、存储过程(Stored Procedure)、触发器

2、(Trigger)等。 数据库管理系统 数据库管理系统(DBMS即Database Management System)，是用于管理数据的一套软件。用户通过DBMS查询和操作数据库，DBMS维护数据的安全性和完整性，以及进行多用户下的并发控制和恢复数据库。,1.2 关系型数据库,关系模型 关系模型(Relational Model)把世界看作是由实体Entity 和联系Relationship构成的。 实体:现实世界中具有区分于其它事物的特征或属性集合。实体通常是以表的形式来表现的。表的每一行描述实体的一个实例，表的每一列描述实体的一个特征或属性。 如:部门，汽车，员工，通讯录 联系:实体之间

3、的关系 a) 一对一 b) 一对多 如班级和学生，一个 班级，有多个学生 c) 多对一 相反的，学生对班级，就是多对一的关系 通过联系，就可以通过一个实体，查找相关实体,使系统形成一个有机的整体。,1.2 关系型数据库,关系型数据库 既基于关系模型的数据库 候选键：由一个或多个字段组成，能唯一标识表中的一行，而又不含多余的字段。 主键(Primary Key):用来唯一标识表中一行的候选键，一个表只能有一个主键。 外键(Foreign Key):一个表的字段，是另外一个表的主键。,候选键,外键,主键,目录,数据库基础 Oracle介绍 Oracle使用 SQL语言 Oracle日常管理,2.1

4、 oracle体系结构,逻辑结构 Oracle数据库按照一定的逻辑结构进行组织和管理 表空间(tablespace) 最高一级的逻辑存储单元，是连接逻辑存储结构和物理存储结构的桥梁 段(segment) 包括数据段，索引段，临时段 区(extent) 由一定数量连续的块组成的逻辑存储结构，是Oracle进行存储分配的单元 块(block) Oracle读写数据文件的最小单位 多个数据块组成区，多个区组成段，多个段组成表空间，多个表空间组成逻辑数据库。,2.1 oracle体系结构,创建表空间 -数据文件用表空间 create tablespace um_dat datafile D:oracl

5、eproduct10.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 loc

6、al 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) 存储

7、表、索引等实际数据的文件.一个表空间，可以有多个数据文件，一个数据文件，只能属于一个表空间 控制文件(control file) 存储数据库的物理结构等信息的文件。 重做日志文件(redo file) 记录数据库的修改操作和事务操作的文件 其他文件,2.1 oracle体系结构,实例 实例都是指后台进程与内存的组合,实例是一组计算机程序。 启动数据库时，先创建实例，然后通过实例加载(mount) 、打开(open)物理数据库,然后由这个实例来访问和控制数据库的各个物理结构。,2.1 oracle体系结构,内存结构 系统全局区(Sytem Global Area),每个实例都只有一个SGA区。当

8、多个用户连接到同一实例时，这些用户进程、服务进程共享SGA区。包括: a)数据高速缓存区 b)字典缓存区 c)重做日志缓存区 d)SQL共享池 程序全局区PGA（PROCESS GLOBAL AREA）是一个内存区，包含单个进程的数据和控制信息，所以又称为进程全局区。,2.1 oracle体系结构,数据字典 由一些只读的数据字典表和数据字典视图组成。 数据字典表保存的主要信息有： 各种方案对象的定义信息 存储空间的分配信息 安全信息 实例运行的性能和统计信息 其他关于数据库的基本信息,2.2 Oracle数据库安装,1. 选择高级安装,2. 单击下一步,2.2 Oracle数据库安装,2.2

9、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数

10、据库安装,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 t

11、emporary 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,存储日期和时间，精确到秒 timestam

12、p,存储时间，秒值精确到小数点后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 )

13、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 索引,索引作用类似书的

14、目录，用于快速查找数据 索引还可用户数据完整性限制，比如唯一索引，可以保证字段值的唯一性 包含以下的类型： 标准索引(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 b

15、itmap 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 emp join dept on emp.dept_id=dept.dept_id where dept.dept_name = IT group by emp.e_mail order by emp.e_mail,1.表间关联字段(外键) 2.查询的字段 3.group

16、by的字段 4.order by的字段,3.3 索引,身份证这类唯一属性，应建唯一索引 性别,只有男、女、未定等少数几种状态值，应创建位图索引，位图索引更节约空间 对字段使用函数，会停用索引，可创建函数索引,3.3 索引,索引的优缺点 优点:某些情况下，数据查找快 缺点: a)在某些条件下，全表扫描比索引查找更快 b)索引占用空间惊人，甚至超过表数据所占空间,不利于管理。 c)创建索引后，会降低插入，修改，删除等操作的效率。,3.4 分区,分区就是把表和索引分成几大块，每一块存放到一个表空间上，性能调优的重要手段。 有三种分区方式 1.散列分区 均匀分布数据，i/o设备负担均衡。 2.范围分区

17、 按数据值的范围进行分区，比如将员工信息表，按入职时间分区，06年一个区，07年一个区，08年一个区,现在我要找一个06年入职的员工，只需要扫描06年那个分区，时间会快很多，磁盘i/o也会减少,3.4 分区,3.复合分区 范围分区和散列分区结合起来使用，先把数据按范围分区，然后在每个分区内再使用散列分区，把数据均匀分布 注意：索引，分区这些调优技术，虽然在数据查询上效率得到了提高，但是，数据在插入，修改操作会更慢，因为在插入的时候，还需要做索引数据，分区数据的额外工作。 数据库调优，需要考虑一个度的问题,3.4 分区,范围分区例子 create table EMPS ( SALARY NUMB

18、ER(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,

19、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_name from emp where upper(emp_name) = upper(T

20、om) -去掉空格 select rtrim(ltrim(emp_code) from emp,4.1 oracle常用函数,数值函数 round(col,n) 四舍五入 round(457.628,2),小数点后2位四舍五入 结果 457.63 round(457.628,-1),小数点前1位四舍五入 结果460 trunc(col,n) 截断数值 trunc(457.628,2) 结果457.62 trunc(457.628,-1) 结果450,4.1 oracle常用函数,日期函数 months_between(date1,date2),两个日期间的月数，结果为实数 add_months

21、(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(

22、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) rq from dual;,4.1 oracle常用函数,转换函数 字符转为日期 to_date(2007-11-11,YYYY-MM-DD) 数值

23、转为字符 select to_char(55676,fm99,999.00) from dual fm表示去掉前面的空格和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，字

24、段2,. from 表2 . 这样可以把后面这个select查询的结果，批量插入表1中,4.2 插入,插入例子 insert into emp(emp_id,emp_code,emp_name) values(1,231,luodaijun);,4.3 修改,update 表1 set 字段1=值1， 字段2=值2 where 条件 例： update emp set emp_name=xiaoluo where emp_id=1,4.4 删除,delete from 表1 where 条件 例： delete from emp where emp_id=1; -删除emp_id等于1的记录,

25、4.5 select查询,1.用SELECT 子句来指定查询所需的列，多个列之间用逗号分开。例如： select p_id, p_name, cost from products 2.可以使用符号* 来选取表的全部列。例如： 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

26、的值 =2000 , =3000,4.5 select查询,5.使用DISTINCT 关键字,去掉重复结果，例如： select distinct dept_id from emp where salary 7000 6.使用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

27、_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_name from emp e inner join dept d on e.dept_id=d.dept_id Oracle : select e.emp_id,e.emp_n

28、ame,d.dept_name from emp e,dept d where e.dept_id=d.dept_id,4.3 多表关联查询,左连接 (以左表(emp)为准， 右表没有的，为空值null),D05,左表有，右表无,4.3 多表关联查询,左连接 ISO标准: select e.emp_id,e.emp_name,d.dept_name from emp e left join dept d on e.dept_id=d.dept_id Oracle : select e.emp_id,e.emp_name,d.dept_name from emp e,dept d where e

29、.dept_id =d.dept_id(+),4.3 多表关联查询,右连接 跟左连接相反，以右表为准 ISO : select e.emp_id,e.emp_name,d.dept_name from emp e right join dept d on e.dept_id=d.dept_id Oracle: select e.emp_id,e.emp_name,d.dept_name from emp e,dept d where e.dept_id(+)=d.dept_id,4.3 多表关联查询,全外连接(包含两表的数据),D05,左表有，右表无,D04,右表有，左表无,4.3 多表关联查

30、询,全外连接 Oracle 9i以上版本支持 select e.emp_id,e.emp_name,d.dept_name from emp e full 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 条件表达式,例子

31、，交叉报表 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,

32、 ,默认结果) 注：默认值可以省略,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_001 as select e.empno, e.ename, d.dname from emp e j

33、oin 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 (sel

34、ect 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 c

35、ur_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) as begin open p_cur for select * from emp; end; 这里通过oracle 9i开始提供引用游标，返回结果

36、集 在pl/sql developer里,选择存储过程，右键选择测试，可以测试及调试存储过程,5.3 函数,存储过程类似，存储在数据库中，一段由PL/SQL语言编写的程序块，可以有输入参数和返回值 与存储过程的区别在于: 1.在select语句中，只能使用函数 2.函数可以索引(FBI),存储过程不能,5.3 函数,Create or replace function raise_salary (ls_money number) RETURN number is ln_num number; begin ln_num := ls_money+10000; RETURN ln_num ; end

37、 ;,5.4 JDBC调用存储过程、函数,创建用户登录存储过程 create or replace procedure p_student_login( ln_stu_no varchar2, ln_pass varchar2, rtn_code out number) is begin 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_s

38、tudent_login(?,?,?) cst.setString(1,001); /设置输入参数,?号位置1 cst.SetString(2,123); /设置输入参数,?号位置2 cst.registerOutParameter(3,java.sql.Types.Integer); /注册输出参数，?位置3 cst.excute(); /执行存储过程 int a=cst.getInt(3); /取得存储过程返回输出参数的值，?位置3 if(a=1) /通过认证 ,5.5 序列,序列，oracle提供的数字生成器 create sequence SEQ_STU_ID minvalue 1 maxvalue 99999999999999999999 start with 1 increment by 1; 从序列得到数字: select seq_stu_id.nextval from dual; 在insert语句使用序列 insert into emp(emp_id) values(seq_stu_id.nextval);,5.4 触发器,存储在数据库里的