oracle基础培训课程

Oracle培训PPT,目录,数据库基础Oracle介绍Oracle使用SQL语言Oracle日常管理,目录,数据库基础Oracle介绍Oracle使用SQL语言Oracle日常管理,1.1基本概念,数据所谓数据(Data)，就是描述事物的符号，在我们的日常生活中数据无所不在，数字、文字、图表、图像、声音等都是数据。人们通过数据来认识世界，交流信息数据库数据库(DB即Database)，顾名思义，就是数据存放的地方。在计算机中，数据库是数据和数据库对象的集合。所谓数据库对象是指表(Table)、视图(View)、存储过程(StoredProcedure)、触发器(Trigger)等。数据库管理系统数据库管理系统(DBMS即DatabaseManagementSystem)，是用于管理数据的一套软件。用户通过DBMS查询和操作数据库，DBMS维护数据的安全性和完整性，以及进行多用户下的并发控制和恢复数据库。,1.2关系型数据库,关系模型关系模型(RelationalModel)把世界看作是由实体Entity和联系Relationship构成的。实体:现实世界中具有区分于其它事物的特征或属性集合。实体通常是以表的形式来表现的。表的每一行描述实体的一个实例，表的每一列描述实体的一个特征或属性。如:部门，汽车，员工，通讯录联系:实体之间的关系a)一对一b)一对多如班级和学生，一个班级，有多个学生c)多对一相反的，学生对班级，就是多对一的关系通过联系，就可以通过一个实体，查找相关实体,使系统形成一个有机的整体。,1.2关系型数据库,关系型数据库既基于关系模型的数据库候选键：由一个或多个字段组成，能唯一标识表中的一行，而又不含多余的字段。主键(PrimaryKey):用来唯一标识表中一行的候选键，一个表只能有一个主键。外键(ForeignKey):一个表的字段，是另外一个表的主键。,候选键,外键,主键,目录,数据库基础Oracle介绍Oracle使用SQL语言Oracle日常管理,2.1oracle体系结构,逻辑结构Oracle数据库按照一定的逻辑结构进行组织和管理表空间(tablespace)最高一级的逻辑存储单元，是连接逻辑存储结构和物理存储结构的桥梁段(segment)包括数据段，索引段，临时段区(extent)由一定数量连续的块组成的逻辑存储结构，是Oracle进行存储分配的单元块(block)Oracle读写数据文件的最小单位多个数据块组成区，多个区组成段，多个段组成表空间，多个表空间组成逻辑数据库。,2.1oracle体系结构,创建表空间-数据文件用表空间createtablespaceum_datdatafileD:oracleproduct10.2.0oradatadevum_dat01.dbfsize10MAUTOEXTENDONNEXT10240KMAXSIZEUNLIMITEDextentmanagementlocaluniformsize128konline;-索引用表空间createtablespaceum_idxdatafileD:oracleproduct10.2.0oradatadevum_idx01.dbfsize10MAUTOEXTENDONNEXT10240KMAXSIZEUNLIMITEDextentmanagementlocaluniformsize128konline;-临时表空间createtemporarytablespaceum_tmptempfileD:oracleproduct10.2.0oradatadevum_tmp01.dbfsize10mAUTOEXTENDONNEXT10240KMAXSIZEUNLIMITEDextentmanagementlocaluniformsize128k;,2.1oracle体系结构,物理结构物理存储结构主要是指在操作系统中，Oracle数据的存储和管理方式。它的组成包括：数据文件(datafile)存储表、索引等实际数据的文件.一个表空间，可以有多个数据文件，一个数据文件，只能属于一个表空间控制文件(controlfile)存储数据库的物理结构等信息的文件。重做日志文件(redofile)记录数据库的修改操作和事务操作的文件其他文件,2.1oracle体系结构,实例实例都是指后台进程与内存的组合,实例是一组计算机程序。启动数据库时，先创建实例，然后通过实例加载(mount)、打开(open)物理数据库,然后由这个实例来访问和控制数据库的各个物理结构。,2.1oracle体系结构,内存结构系统全局区(SytemGlobalArea),每个实例都只有一个SGA区。当多个用户连接到同一实例时，这些用户进程、服务进程共享SGA区。包括:a)数据高速缓存区b)字典缓存区c)重做日志缓存区d)SQL共享池程序全局区PGA（PROCESSGLOBALAREA）是一个内存区，包含单个进程的数据和控制信息，所以又称为进程全局区。,2.1oracle体系结构,数据字典由一些只读的数据字典表和数据字典视图组成。数据字典表保存的主要信息有：各种方案对象的定义信息存储空间的分配信息安全信息实例运行的性能和统计信息其他关于数据库的基本信息,2.2Oracle数据库安装,1.选择高级安装,2.单击下一步,2.2Oracle数据库安装,2.2Oracle数据库安装,1.指定主目录的名称(用于区别安装的多个oracle)2.指定oracle的安装路径,2.2Oracle数据库安装,这一项未执行，不用管它,检查结果为通过,2.1Oracle数据库安装,选择是,2.2Oracle数据库安装,2.2Oracle数据库安装,2.2Oracle数据库安装,数据库名和SID,连接数据库时会用到,2.2Oracle数据库安装,2.2Oracle数据库安装,2.2Oracle数据库安装,2.2Oracle数据库安装,设置几个系统用户的密码,2.2Oracle数据库安装,2.2Oracle数据库安装,2.2Oracle数据库安装,2.5使用PL/SQLDeveloper,客户端配置好网络服务名后，会在这个下拉框显示出来,2.5使用PL/SQLDeveloper,这个窗口列出了所有的数据库对象，可以可视化的创建和管理,2.5使用PL/SQLDeveloper,执行,2.6用户和方案,oracle中，用户名和方案名相同，可把用户和方案理解为同一个概念。创建用户，同时也创建了一个方案。方案，又叫模式(Schema),是数据库对象的集合。-创建用户um,密码oraclecreateuserumidentifiedbyoracledefaulttablespaceum_dattemporarytablespaceum_tmp;-赋予权限grantconnecttoum;grantresourcetoum;grantunlimitedtablespacetoum;,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表,创建表createtableEMP(EMP_IDnumber(24)notnull,EMP_CODEvarchar2(10)notnull,EMP_NAMEvarchar2(20)notnull,E_MAILvarchar2(100),DEPT_IDnumber(24)notnull)tablespaceUM_DAT;,3.2约束,主键约束-添加主键altertableEMPaddconstraintpk_emp_idprimarykey(EMP_ID);唯一约束altertableEMPaddconstraintuq_emp_codeunique(EMP_CODE);外键约束altertableEMPaddconstraintfk_dept_idforeignkey(DEPT_ID)referencesdept(DEPT_ID);oracle自动为主键和唯一约束创建索引。,3.3索引,索引作用类似书的目录，用于快速查找数据索引还可用户数据完整性限制，比如唯一索引，可以保证字段值的唯一性包含以下的类型：标准索引(B树)数据量非常大的情况下，查找依然很快惟一索引(UniqueIndex)比如员工编号,唯一索引查找最快位图索引(Bitmap)适合基数小的字段，比如性别,节约空间基于函数的索引(FBI),3.3索引,创建标准索引createindexIDX_DEPT_NAMEonDEPT(dept_name);创建唯一索引createuniqueindexIDX_DEPT_CODEonDEPT(dept_code);创建位图索引createbitmapindexIDX_EMP_SEXonEMP(sex);创建函数索引createindexIDX_EMP_BDATEonEMP(TO_CHAR(B_DATE,YYYY-MM-DD);,3.3索引,哪些字段建议建立索引呢？selectemp.e_mail,count(*)ctfromempjoindeptonemp.dept_id=dept.dept_idwheredept.dept_name=ITgroupbyemp.e_mailorderbyemp.e_mail,1.表间关联字段(外键)2.查询的字段3.groupby的字段4.orderby的字段,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分区,范围分区例子createtableEMPS(SALARYNUMBER(24,4)notnull,EMP_IDNUMBER(24)notnull)partitionbyrange(SALARY)(partitionP_SALARY_2000valueslessthan(2000),partitionP_SALARY_3000valueslessthan(3000);,目录,数据库基础知识Oracle基础表、索引、约束和分区SQL语句自定义函数、存储过程、触发器备份和恢复,4.1oracle常用函数,字符函数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.1oracle常用函数,字符函数例子-不区分大小写查询selectemp_code,emp_namefromempwhereupper(emp_name)=upper(Tom)-去掉空格selectrtrim(ltrim(emp_code)fromemp,4.1oracle常用函数,数值函数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.1oracle常用函数,日期函数months_between(date1,date2),两个日期间的月数，结果为实数add_months(date,m),增加m个月，m可以为负数，结果为减少m个月round,日期四舍五入trunc,截断日期last_day,当月最后一天,4.1oracle常用函数,日期函数例子当前日期增加1个月selectadd_months(sysdate,1)fromdual;去年同月selectadd_months(sysdate,-12)fromdual;得到年初selecttrunc(sysdate,YYYY)fromdual;得到月初selecttrunc(sysdate,MM)fromdual;精确到天，截断小时分秒selecttrunc(sysdate)fromdual;当月最后一天selectlast_day(sysdate)fromdual;,4.1oracle常用函数,转换函数日期转为字符:to_char(date1,format_model)format_model:转换后的显示格式YYYY年,MM月,DD日,HH24小时,MI分,SS秒例子:selectto_char(sysdate,YYYY-MM-DDHH24:MI:SS)rqfromdual;,4.1oracle常用函数,转换函数字符转为日期to_date(2007-11-11,YYYY-MM-DD)数值转为字符selectto_char(55676,fm99,999.00)fromdualfm表示去掉前面的空格和0结果:55,676.00,4.2SQL,SQL结构化查询语言,是访问和处理数据库的标准语言.分为:DDL:数据定义语言用于定义数据库对象如:定义表，视图，存储过程等DML:数据操作语言用于访问和处理数据如：insert,update,delete,select,4.2插入,插入单条insertinto表1(字段1,字段2,.)values(值1，值2,.)批量插入insertinto表1(字段1，字段2，.)select字段1，字段2,.from表2.这样可以把后面这个select查询的结果，批量插入表1中,4.2插入,插入例子insertintoemp(emp_id,emp_code,emp_name)values(1,231,luodaijun);,4.3修改,update表1set字段1=值1，字段2=值2where条件例：updateempsetemp_name=xiaoluowhereemp_id=1,4.4删除,deletefrom表1where条件例：deletefromempwhereemp_id=1;-删除emp_id等于1的记录,4.5select查询,1.用SELECT子句来指定查询所需的列，多个列之间用逗号分开。例如：selectp_id,p_name,costfromproducts2.可以使用符号*来选取表的全部列。例如：select*fromproducts3.在查询结果中添加列。例如：selectp_id,p_name,qty,cost,cost*qtyassum_costfromproducts4.使用WHERE子句。例如：selecte_namefromempwheresalarybetween2000and3000-salary的值=2000,70006.使用IN关键字。例如：selecte_namefromemployeewheredept_idin(1001,1002)7.使用通配符,模糊查询。例如：selectemp_namefromempwhereemp_namelikeluo%-查询所有emp_name以luo开头的数据8.使用ORDER子句,对结果排序。例如：selectp_id,p_name,cost,qtyfromproductswheredept_id=1003orderbycostdesc,quantity-默认是升序ASC,4.3多表关联查询,内连接(结果为两表都包含的dept_id的行),4.3多表关联查询,内连接ISO标准:(oracle9i开始支持ISO标准写法)selecte.emp_id,e.emp_name,d.dept_namefromempeinnerjoindeptdone.dept_id=d.dept_idOracle:selecte.emp_id,e.emp_name,d.dept_namefromempe,deptdwheree.dept_id=d.dept_id,4.3多表关联查询,左连接(以左表(emp)为准，右表没有的，为空值null),D05,左表有，右表无,4.3多表关联查询,左连接ISO标准:selecte.emp_id,e.emp_name,d.dept_namefromempeleftjoindeptdone.dept_id=d.dept_idOracle:selecte.emp_id,e.emp_name,d.dept_namefromempe,deptdwheree.dept_id=d.dept_id(+),4.3多表关联查询,右连接跟左连接相反，以右表为准ISO:selecte.emp_id,e.emp_name,d.dept_namefromemperightjoindeptdone.dept_id=d.dept_idOracle:selecte.emp_id,e.emp_name,d.dept_namefromempe,deptdwheree.dept_id(+)=d.dept_id,4.3多表关联查询,全外连接(包含两表的数据),D05,左表有，右表无,D04,右表有，左表无,4.3多表关联查询,全外连接Oracle9i以上版本支持selecte.emp_id,e.emp_name,d.dept_namefromempefullouterjoindeptdone.dept_id=d.dept_id,4.4条件表达式,Case表达式语法:case表达式when值1then结果1when值2then结果2else默认结果end,4.4条件表达式,第二种方式casewhen条件1then结果1when条件2then结果2else默认结果end,4.4条件表达式,例子，交叉报表,4.4条件表达式,例子，交叉报表selectname,sum(yuwen)yuwen,sum(shuxue)shuxue,sum(huaxue)huaxuefrom(selectname,casekechenwhen语文thenfengshuendyuwen,casekechenwhen数学thenfengshuendshuxue,casekechenwhen化学thenfengshuendhuaxuefromtable)tgroupbyname,4.4条件表达式,Decode函数Decode(表达式,条件1,结果1,条件2,结果2,默认结果)注：默认值可以省略,4.4条件表达式,Decode例子selectswjg_dm,decode(swjg_bz,B,税务部门,J,税务机关)fromdm_swjg,目录,数据库基础知识Oracle基础表、索引、约束和分区SQL语句视图，自定义函数、存储过程、触发器备份和恢复,5.1视图,视图(View),虚拟表，命名的查询语句视图内部是一条select语句，用户可以像查询表一样查询视图如:createorreplaceviewv_001asselecte.empno,e.ename,d.dnamefromempejoindeptdone.deptno=d.deptno;然后查询表一样查询视图select*fromv_001,5.2存储过程,存储在数据库中，一段由PL/SQL语言编写的程序块。参数有三种类型a)in输入参数b)out输出参数c)inout输入输出参数存储过程没有返回值，但可以通过out参数的方式返回结果。用于比较复杂的查询统计以及业务逻辑处理.,5.2存储过程,for隐式游标例子:Createorreplaceprocedurep_test(ls_emp_codevarchar2)isbeginforxin(selectemp_id,salaryfromempwhereemp_code=ls_emp_code)loopprocessData(x.emp_id,x.salary);-从游标取出记录,然后处理endloop;end;,5.2存储过程,显示游标例子createorreplaceprocedurep_cursor_testiscursormy_curisselect*fromscott.emp;cur_recodemy_cur%rowtype;-记录类型beginopenmy_cur;loopfetchmy_curintocur_recode;-抓取数据，并赋值EXITWHENmy_cur%NOTFOUND;-游标没有数据，退出循环dbms_output.put_line(cur_recode.empno);endloop;closemy_cur;endp_cursor_test;,5.2存储过程,存储过程返回结果集createorreplaceprocedurep_get_emp(p_curoutsys_refcursor)asbeginopenp_curforselect*fromemp;end;这里通过oracle9i开始提供引用游标，返回结果集在pl/sqldeveloper里,选择存储过程，右键选择测试，可以测试及调试存储过程,5.3函数,存储过程类似，存储在数据库中，一段由PL/SQL语言编写的程序块，可以有输入参数和返回值与存储过程的区别在于:1.在select语句中，只能使用函数2.函数可以索引(FBI),存储过程不能,5.3函数,Createorreplacefunctionraise_salary(ls_moneynumber)RETURNnumberisln_numnumber;beginln_num:=ls_money+10000;RETURNln_num;end;,5.4JDBC调用存储过程、函数,创建用户登录存储过程createorreplaceprocedurep_student_login(ln_stu_novarchar2,ln_passvarchar2,rtn_codeoutnumber)isbeginselectcount(*)intortn_codefromstudentwherestu_no=ln_stu_noandpass=ln_pass;end;,5.4JDBC调用存储过程、函数,CallableStatemmentcst=conn.prepareCall(callp_student_login(?,?,?)cst.setString(1,001);/设置输入参数,?号位置1cst.SetString(2,123);/设置输入参数,?号位置2cst.registerOutParameter(3,java.sql.Types.Integer);/注册输出参数，?位置3cst.excute();/执行存储过程inta=cst.getInt(3);/取得存储过程返回输出参数的值，?位置3if(a=1)/通过认证,5.5序列,序列，oracle提供的数字生成器createsequenceSEQ_STU_IDminvalue1maxvalue99999999999999999999startwith1incrementby1;从序列得到数字:selectseq_stu_id.nextvalfromdual;在insert语句使用序列insertintoemp(emp_id)values(seq_stu_id.nextval);,5.4触发器,存储在数据库里的带名的PL/SQL块，由事件触