Oracle教程

PAGE16Oracle 教案 中职数学基础模块教案 下载北师大版¥1.2次方程的根与系数的关系的教案关于坚持的教案初中数学教案下载电子教案下载 第1章Oracle基础知识1.1Oracle简介oracle是殷墟（YinXu）出土的甲骨文（oracleboneinscriptions）的英文翻译的第一个单词，在英语里是“神谕”的意思。Oracle的四大创始人Oracle数据库中有个默认用户SCOTT，就是BruceScott，而SCOTT用户的默认密码tiger，是当时Scott养的一只猫的名字。2009年4月Oracle公司以74亿美元收购SUN公司。1.2Oracle的版本Oracle8i(internet):表示Oracle公司开始正式进入互联网。Oracle9i:与Oracle8i相关，性能更佳，管理更人性化。Oracle10g(grid) :网格技术Oracle11g(grid) :网格技术网格是利用互联网把地理上广泛分布的各种资源（包括计算资源、存储资源、带宽资源、软件资源、数据资源、信息资源、知识资源等）连成一个逻辑整体，就像一台超级计算机一样，为用户提供一体化信息和应用服务（计算、存储、访问等），彻底消除资源“孤岛”，最充分的实现信息共享。1.3Oracle的安装（演示）重要概念：1.全局数据库名与SID全局数据库名是数据库的名称，当数据库处于网络中时，为 标识 采样口标识规范化 下载危险废物标识 下载医疗器械外包装标识图下载科目一标识图大全免费下载产品包装标识下载 该数据库的网络位置，需要用数据库名和网络位置组成其全局数据库名，其命名 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 是：database_name.database_domain。例如：sales.atg.com，其中sales为数据库名，atg.com为数据库域。指定全局数据库名时，尽量为数据库选择能够反映其用途的名称，例如sales。数据库域用于将数据库与分布式环境中的其他数据库区分开来。例如在上海的数据库可以命名为sales.shanghai.com，北京的数据库可以命名为sales.beijing.com。即使数据库名都相同，但数据库域不同，所以也能区分开。SID（数据库实例名）用于对外相连时使用。Oracle实例（Instance）是用来访问数据库文件集的存储结构与后台程序的集合。Oracle数据库其实是磁盘上的一堆文件；为了启动数据库即访问这堆文件，需要在内存中创建它的一个实例，然后由实例加载并打开数据库。用户连接数据库时，实际上是连接到实例，然后由实例负责与数据库通信，再将处理结果返回给用户。Oracle中一个数据库至少有一个实例与之对应，但一个数据库也可以对应多个实例，被多个实例访问。因此SID主要用于区分同一台计算机上不同的实例。对于单实例数据库，其SID通常与数据库名相同。一个运行着的ORACLE数据库可以看成是一个ORACLESERVER，该SERVER由数据库(Database)和实例(Instance)组成，一般情况下一个ORACLESERVER包含一个实例和一个与之对应的数据库，但是在特殊情况下，如8i的OPS，9i的RAC，一个SERVER中一个数据库可以对应多个实例。当某一实例出现故障时，其他实例自动服务。2.Oracle的常用账户用户名角色默认密码说明sys超级管理员change_on_install所有oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。sys用户拥有dba，sysdba，sysoper等角色或权限，是oracle权限最高的用户。sywstem普通管理员Manager用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。system用户拥有普通dba角色权限。可用来创建其他用户。scott普通用户（练习常用）Tiger在默认情况下从Oracle10g开始，scott不能登陆。被禁用了。需要手工解锁。3.安装后的注意事项：Oracle安装完成后至少要启动两个服务：OracleOraDb11g_home1TNSListener:监听器，监听程序的服务进程。OracleServiceORCL:主服务，是Oracle数据库实例的服务进程。建议将启动类型改为手动。1.4Oracle的常用管理工具1.使用SQL*Plus在Oracle中，用户对数据库的操作主要是通过SQL*Plus工具来实现的。应用举例：(1).查看当前连接用户SQL>showuser(2).查看全局数据库名SQL>SELECT*FROMglobal_name;(4).清空屏幕SQL>clscr;2.使用OracleEnterpriseManager（OEM）OracleEnterpriseManager（OEM）提供了基于Web界面的、可管理单个数据库的工具。使用步骤：(1).启动OracleDBConsoleorcl服务(2).启动浏览器，输入OEM的URL地址（https://主机名:1158/em），或者直接在【开始】菜单的Oracle程序组中选择DatabaseControl–orcl命令即可。(3).注意事项：只能用sys和system用户登陆，sys必须用SYSDBA身份登陆。3.使用DBCA创建数据库如果在安装Oracle时选择仅安装数据库服务器软件，而不创建数据库，就需要安装后手动创建数据库。如果在系统中已经存在Oracle数据库，为了充分利用服务器的资源，建议不要再创建一个数据库。DBCA（DatabaseConfigurationAssistant）是一个图形化用户界面的工具，DBA通过它可以快速、直观地创建数据库。选择【开始】｜【程序】｜Oracle-OraDb11g_home1|【配置和移置工具】｜DatabaseConfigurationAssistant命令，打开DBCA界面。用户只需要根据DBCA的提示逐步进行设置，就可以根据相应配置创建数据库。第2章SQL*Plus命令2.1用户管理命令1.更改用户登录命令格式：conn用户名/密码[ASSYSDBA]注意：如果连接的是超级管理员（SYS），必须写上ASSYSDBA2.用户加锁和解锁加锁：SQL>alteruser用户名accountlock;解锁：SQL>alteruser用户名accountunlock;3.更改用户密码命令格式：SQL>alteruser用户名identifiedby密码;注意，如果忘记所有用户的密码，可启动SQL*Plus，输入以下命令：conn/assysdbaSQL>alteruser用户名identifiedby密码;2.2其他常用命令1.help命令SQL*Plus有许多命令，而且每个命令都有大量的选项，要记住每一个命令的所有选项是很困难的。SQL*Plus提供了内建的帮助系统，可以使用HELP命令查询相关的命令信息。命令格式：SQL>help命令名示例：查看conn命令的帮助信息SQL>helpconn;查看SQL*Plus的命令清单SQL>helpindex;查看SQL*Plus的关键字清单SQL>helpreservewords;2.describe命令describe命令可以缩写为desc，用来列出表或视图各个列的名称以及属性。命令格式：SQL>descobject_name;示例：查看scott用户的emp表的结构SQL>descscott.emp;3.setlinesize命令系统默认每行打印80个字符，当SQL*Plus输出linesize指定数量的字符后，随后的数据就会折叠到下一行显示。命令格式：SQL>setlinesizenumber示例：SQL>showlinesize;SQL>setlinesize800;4.setpagesize命令当SQL*Plus执行查询语句时，setpagesize命令可以设置一页显示的行数。命令格式：SQL>setpagesizenumber示例：SQL>showpagesize;SQL>setpagesize30;5.pause命令如果在SQL*Plus中运行的查询语句可以返回多行数据，以至于无法在窗口中一次显示完，输出窗口会快速滚动显示。可以设置环境变量pause为on来控制显示完一页后暂停显示，直到按回车键才继续显示下一页数据。pause选项还可以设置暂停后显示的字符串，以便提示用户。命令格式：SQL>setpauseon ;SQL>setpause‘按回车键继续’;6.@命令用于执行脚本文件。命令格式：SQL>@文件名;注意：需写文件路径；sql文件的后缀可以不写。7.继续使用上次命令命令格式：SQL>/2.3常用数据字典视图数据字典是Oracle数据库的核心组件，是数据库中的所有对象信息的知识库，提供了数据库结构、数据库对象空间分配和数据库用户等有关的信息。任何数据库用户都无法对数据字典中的内容进行修改，但可以查看数据字典中的内容。数据字典中的信息通过表和视图的形式组织。数据字典中的信息实际上保存在基础表中，只有Oracle系统才有权读取和写入基础表。基础表中存储的信息通常是经过加密处理的。而视图是一种虚拟表，它本身并不包含数据，用户可以通过数据字典视图来获取信息，而不需访问数据字典表。数据字典视图分类：视图类型说明USER视图USER视图的名称以user_为前缀，用来记录用户对象的信息。例如user_tables视图，它记录用户的表信息ALL视图ALL视图的名称以all_为前缀，是USER视图的扩展。用来记录用户对象的信息以及被授权访问的对象信息。例如all_synonyms视图，它记录用户可以存取的所有同义词信息DBA视图DBA视图的名称以dba_为前缀，用来记录数据库实例的所有对象的信息。例如dba_tables视图，通过它可以访问所有用户的表信息V$视图V$视图的名称以v$为前缀，用来记录与数据库活动相关的性能统计动态信息。例如v$datafile视图，它记录有关数据文件的统计信息GV$视图GV$视图的名称以gv$为前缀，用来记录分布式环境下所有实例的动态信息。例如gv$lock视图，它记录出现锁的数据库实例的信息基本数据字典视图字典名称说明dba_tables所有用户的所有表的信息dba_tab_columns所有用户的表的字段信息dba_views所有用户的所有视图信息dba_synonyms所有用户的同义词信息dba_sequences所有用户的序列信息dba_constraints所有用户的表的约束信息dba_indexes所有用户的表的索引简要信息dba_ind_columns所有用户的索引的字段信息dba_triggers所有用户的触发器信息dba_sources所有用户的存储过程信息dba_segments所有用户的段的使用空间信息dba_extents所有用户的段的扩展信息dba_objects所有用户对象的基本信息cat当前用户可以访问的所有基表tab当前用户创建的所有基表、视图和同义词等dict构成数据字典的所有表的信息与数据库组件相关的数据字典数据库组件数据字典中的表或视图说明数据库v$datafile记录系统的运行情况表空间dba_tablespaces记录系统表空间的基本信息dba_free_space记录系统表空间的空闲空间的信息控制文件v$controlfile记录系统控制文件的基本信息v$controlfile_record_section记录系统控制文件中记录文档段的信息v$parameter记录系统各参数的基本信息数据文件dba_data_files记录系统数据文件以及表空间的基本信息v$filestat记录来自控制文件的数据文件信息v$datafile_header记录数据文件头部分的基本信息段dba_segments记录段的基本信息数据区dba_extents记录数据区的基本信息日志v$thread记录日志线程的基本信息v$log记录日志文件的基本信息v$logfile记录日志文件的概要信息归档v$archived_log记录归档日志文件的基本信息v$archive_dest记录归档日志文件的路径信息数据库实例v$instance记录实例的基本信息v$system_parameter记录实例当前有效的参数信息内存结构v$sga记录SGA区的大小信息v$sgastat记录SGA的使用统计信息v$db_object_cache记录对象缓存的大小信息v$sql记录SQL语句的详细信息v$sqltext记录SQL语句的语句信息v$sqlarea记录SQL区的SQL基本信息后台进程v$bgprocess显示后台进程信息v$session显示当前会话信息常用动态性能视图视图名称说明v$fixed_table显示当前发行的固定对象的说明v$instance显示当前实例的信息v$latch显示锁存器的统计数据v$librarycache显示有关库缓存性能的统计数据v$rollstat显示联机的回滚段的名字v$rowcache显示活动数据字典的统计v$sga显示有关系统全局区的 总结 初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf 信息v$sgastat显示有关系统全局区的详细信息v$sort_usage显示临时段的大小及会话v$sqlarea显示SQL区的SQL信息v$sqltext显示在SGA中属于共享游标的SQL语句内容v$stsstat显示基本的实例统计数据v$system_event显示一个事件的总计等待时间v$waitstat显示块竞争统计数据应用举例：1.查看所有用户SQL>SELECTusername,account_statusFROMdba_users;*OPEN表示账户为解锁状态；EXPIRED表示账户为过期状态(需要设置口令才能解除此状态)；LOCKED表示账户为锁定状态。2.查看SIDSQL>SELECTinstance_nameFROMv$instance;3.查看当前用户所有的表信息SQL>SELECT*FROMtab;*table（表）view（视图）synonym（别名，与视图类似）4.查看所有用户的表信息SQL>SELECTtable_name,ownerFROMdba_tables;5.查看指定用户的表信息SQL>SELECTtable_name,ownerFROMdba_tableswhereowner=’SCOTT’;第3章SQL语句基础scott用户有4个案例表，可在日常练习中使用。3.1Oracle内置字段数据类型数据类型字符型数据型日期时间型LOB型ROWID型1.字符型CHAR型：定长字符串，短则用空格填充，长则出错。VARCHAR2型：变长字符串。字段长度根据实际字符串长度自动调整，不用空格填充。2.数值型NUMBER(PRECISION,SCALE)精度PRECISION指定所有数字位的个数，范围SCALE指定小数的位数，两个参数均是可选的。如果插入的字段数据超过指定位数，将自动四舍五入。3.日期时间数据类型DATE可以存储日期和时间的组合数据。ORACLE默认的日期格式是DD-MON-YY。4.LOB数据类型用于大型的、未被结构化的数据，如二进制文件、图片文件等。LOB数据类型又分为BLOB、CLOB和BFILE三种。BLOB类型：用于存储二进制对象。如图像、音频、视频。CLOB类型：用于存储字符格式的大型对象。Oracle将数据转换成Unicode格式。BFILE类型：将二进制文件作为操作系统文件存储在数据库外部，BFILE类型的字段仅保存二进制文件的指针。5.ROWID类型亦称伪列类型，用于保存表中每条记录的物理地址。每条记录都有唯一的rowid。ORACLE自动为每个表建立名称为ROWID的字段。可以对该字段进行查询。rowid确定了每条记录属于哪一个数据对象、数据文件、块、行。是基于64位编码的18个字符显示。其格式如下：  示例：ROWID是隐含的，检索表时不会看到，须显式指定名称。SQL>SELECTrowid,enameFROMemp;3.2SQL简介SQL(StructuredQueryLanguage)结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。SQL语言分为3类：1．数据定义语言（DDL）用来定义和管理数据库中的对象（如表、视图、存储过程、触发器）。由CREATE、ALTER、DROP命令构成。2．数据操纵语言（DML）针对数据库中存储的数据进行相关操作，主要包括增加（insert）、删除（delete）、更新（update）和查询（select）四种操作。在实际工作中增加、删除和更新所占使用DML比例为20%，而查询所占比例为80%。3．数据控制语言（DCL）用来管理用户对指定数据库对象的使用权限。常用操作有分配（grant）和回收（revoke）。3.3SELECT语句的用法在众多SQL语句中，使用频率最高的是SELECT语句，该语句主用于检索数据。虽然在前面已经使用了一些SELECT语句，但这些使用是零散的、不完整的。本节将对SELECT语句进行系统地、完整地介绍。SELECT语句的完整语法格式如下：SELECT[ALL|DISTINCT{*|expression|column1_name[,column2_name][,...]}FROM{table1_name|(subquery)}[alias][,{table2_name|(subquery)}[alias],...][WHEREcondition][CONNECTBYcondition[STARTWITHcondition]][GROUPBYexpression[,...]][HAVINGcondition[,...]][{UNION|INTERSECT|MINUS}][ORDERBYexpression[ASC|DESC][,...]][FORUPDATE[OF[schema.]table_name|view]column][NOWAIT];3.3.1检索单表数据检索单表数据是指从单个表中检索数据，检索的结果都是来自于同一个表中。在检索数据的过程中，既可以检索所有的列，也可以检索部分列。数据将按照SELECT子句后面指定的列的顺序显示。如果使用星号*，则表示检索所有的列，这时数据将按照定义表时指定的列的顺序显示。示例：SQL>SELECT*FROMemp;SQL>SELECTempno,ename,job,sal,deptnoFROMemp;SQL>SELECTenameAS"姓名",jobAS"职位",hiredateAS"工作日期",salAS"工资"FROMemp;SQL>SELECTename"姓名",job"职位",hiredate"工作日期",sal"工资"FROMemp;SQL>SELECT'编号是'||empno||'的雇员,姓名是'||ename||'的工作是'||jobFROMemp;SQL>SELECTDISTINCTjobFROMemp;3.3.2过滤数据在SELECT语句中可以使用WHERE子句过滤数据，只检索那些满足过滤条件的数据。通过过滤数据，可以从大量的数据中获取自己所需要的数据。1.比较运算符比较操作符说明=等于<>、!=不等于>=大于等于<=小于等于>大于<小于ANY与一个列表中的任何值进行比较ALL与一个列表中的所有值进行比较示例：SQL>SELECTename,job,hiredate,salFROMempWHEREempno=7521;SQL>SELECTename,job,hiredate,salFROMempWHEREjob=any('CLERK','ANALYST');2.SQL运算符SQL运算符说明BETWEEN指定条件在两个值之间，包括边界值LIKE匹配的字符样式，一般用于模糊查询IN匹配的一个列表值ISNULL匹配空值注意：表中SQL运算符可以与NOT运算符取反处理，例如，NOTLIKE，NOTBETWEEN和ISNOTNULL等。示例：SQL>SELECTempno,ename,job,salFROMempWHEREenameLIKE'S%';SQL>SELECTempno,ename,job,salFROMempWHEREempnoIN(7369,7521,7789);SQL>SELECTempno,ename,job,salFROMempWHEREempnoNOTIN(7369,7521,7789);SQL>SELECTempno,ename,job,salFROMempWHEREsalBETWEEN1500AND2000;SQL>SELECT*FROMempWHEREcommISNULL;3.逻辑运算符使用逻辑运算符可以将简单的条件组合起来。运算符说明NOT取反，当条件为真时，结果为假；当条件为假时，结果为真。AND与，当两个条件为真时，结果为真OR或，当两个条件中有一个为真时，结果为真示例：SQL>SELECTempno,ename,job,salFROMempWHEREsal>=1500ANDsal<=2000;SQL>SELECTempno,ename,job,salFROMempWHEREjob='CLERK'ORjob='SALESMAN';3.3.3排序数据在SELECT语句中，可以使用ORDERBY子句对检索的结果集进行排序。语法格式：SELECT字段列表FROM表名WHERE条件ORDERBY字段名[ASC|DESC]示例：SQL>SELECTename,job,salFROMempORDERBYsal;SQL>SELECTename,job,salFROMempORDERBYsal,enameDESC;SQL>SELECTename,job,salFROMempORDERBY3;3.3.4多表检索在实际应用中，经常会碰到需要检索的数据存在于两个或两个以上的表中。这时就需要使用SELECT语句执行多表检索。为了更好地理解多表检索操作，需要理解表的别名，笛卡尔积、内连接、外连接、自然连接和交叉连接等概念。1.表的别名在多表查询时，如果多个表之间存在同名的列，则必须使用表名限定列引用。但随着查询变得越来越复杂，语句会由于每次限定列时输入表名而变得冗长。因此SQL语言提供了一种机制——表的别名。可以在SELECT语句中为表定义临时性名称，简化对表的引用。示例：检索某一部门的职工信息(1)没有使用表的别名SQL>SELECTename姓名,job职位,sal工资,dname部门FROMemp,deptWHEREemp.deptno=dept.deptnoANDdept.dname='SALES';(2)使用表的别名SQL>SELECTe.ename姓名,e.job职位,e.sal工资,d.dname部门FROMempe,deptdWHEREe.deptno=d.deptnoANDd.dname='SALES';注意：一旦为表指定了别名，则必须在整个剩余语句中使用表的别名，不允许再使用表原来的名称。2.内连接内连接是指满足连接条件的连接操作。语法格式：SELECT字段列表FROM表名1[INNER]JOIN表名2ON连接表达式示例：SQL>SELECTe.ename,e.job,e.sal,d.deptno,d.dnameFROMempejoindeptdONe.deptno=d.deptno;SQL>SELECTe.ename,e.job,e.sal,d.deptno,d.dnameFROMempe,deptdWHEREe.deptno=d.deptno;3.外连接如果某个表中的数据不满足连接条件，而又要出现在检索结果中，可以使用外连接。外连接可以分为：左外连接：LEFT[OUTER]JOIN右外连接：RIGHT[OUTER]JOIN全外连接：FULL[OUTER]JOIN示例：(1)左外连接SQL>INSERTINTOemp(empno,ename,job,sal)VALUES(8000,'ATG','CLERK',950);SQL>SELECTe.ename,e.job,e.sal,d.deptno,d.dnameFROMempeLEFTJOINdeptdONe.deptno=d.deptno;(2)右外连接SQL>SELECTe.ename,e.job,e.sal,d.deptno,d.dnameFROMempeRIGHTJOINdeptdONe.deptno=d.deptno;(3)全外连接SQL>SELECTe.ename,e.job,e.sal,d.deptno,d.dnameFROMempeFULLJOINdeptdONe.deptno=d.deptno;4.自然连接使用自然连接检索多个表时，Oracle会将第一个表中的列与第二个表中具有相同名称的列进行连接。用户不需要明确指定进行连接的列，系统会自动完成这一任务。示例：SQL>SELECTempno,ename,job,sal,deptno,dnameFROMempNATURALJOINdeptWHEREdname='SALES';注意：自然连接的实际应用性较差，它需要连接的各个表之间必须具有相同名称的列，并且不能让表中其他的列具有相同的名称。假如emp表和dept表中都有一个address地址列，自然连接会尝试使用该列进行连接。5.交叉连接交叉连接是没有连接条件的连接，即笛卡尔积。语法形式：SELECT字段列表FROM表1CROSSJOIN表2示例：SQL>SELECTcount(*)FROMempCROSSJOINdept;3.4SQL函数数据库产品的主要区别是SQL函数库不同。3.4.1字符函数1.小写转大写函数upper范例：SQL>SELECTupper(‘smith’)FROMdual;SQL>SELECT*FROMempwhereename=upper(‘smith’);2.大写转小写函数lower范例：SQL>SELECTlower(‘HELLO,WORLD’)FROMdual;3.将单词第一个字母大写函数initcap范例：SQL>SELECTinitcap(ename)as姓名,jobas职位FROMemp;4.字符串连接函数concat范例：SQL>SELECTconcat(‘hello’,‘world’)FROMdual;注意：字符串连接可使用||运算符实现SQL>SELECT‘hello’||‘world’FROMdual;5.字符串截取函数substr范例：SQL>SELECTsubstr(‘hello’,1,3)FROMdual;SQL>SELECTsubstr(‘hello’,0,3)FROMdual;6.字符串长度函数length范例：SQL>SELECTlength(‘hello’)FROMdual;7.字符串内容替换函数replace范例：SQL>SELECTreplace(‘hello’,‘l’,‘x’)FROMdual;3.4.2数学函数1.四舍五入函数round范例：SQL>SELECTround(789.536,2)FROMdual;SQL>SELECTround(789.536)FROMdual;SQL>SELECTround(789.536,-2)FROMdual;2.截断小数位函数trunc范例：SQL>SELECTtrunc(789.536,2)FROMdual;SQL>SELECTtrunc(789.536)FROMdual;SQL>SELECTtrunc(789.536,-2)FROMdual;3.取模函数mod范例：SQL>SELECTmod(10,3)FROMdual;3.4.3日期函数1.获取当前日期函数sysdate范例：SQL>SELECTsysdateFROMdual;2.获取给定日期范围内的月数函数months_between范例：SQL>SELECTename,months_between(sysdate,hiredate)FROMemp;3.在指定日期上加上指定月数函数add_months范例：SQL>SELECTadd_months(sysdate,4)FROMdual;4.计算下一个今天是哪个日期next_day范例：SQL>SELECTnext_day(sysdate,‘星期一’)FROMdual;5.求出给定日期的当月最后一天日期last_day范例：SQL>SELECTlast_day(sysdate)FROMdual;3.4.4转换函数1.将指定表达式转换成字符串函数to_char范例1：SQL>SELECTempno,ename,to_char(hiredate,’yyyy’)year,to_char(hiredate,’mm’)months,to_char(hiredate,’dd’)dayFROMemp;范例2：SQL>SELECTempno,ename,to_char(hiredate,’yyyy-mm-dd’)FROMemp;注意：可以用fm去掉月份前面的前导0SQL>SELECTempno,ename,to_char(hiredate,’fmyyyy-mm-dd’)FROMemp;范例3：给工资加千位分隔符SQL>SELECTempno,ename,to_char(sal,’99,999’)FROMemp;范例4：给工资加货币符号，$是美元，L代表本地货币SQL>SELECTempno,ename,to_char(sal,’$99,999’)FROMemp;2.字符串转数字函数to_number范例：SQL>SELECTto_number(‘123’)+to_number(‘123’)FROMdual;3.字符串转日期函数to_date范例：SQL>SELECTto_date(‘2010-12-20’,’yyyy-mm-dd’)FROMdual;3.4.5通用函数1.将指定null值变为指定的内容函数nvl范例：计算职员年薪（月薪sal+奖金comm）*12，奖金可能为nullSQL>SELECTempno,ename,sal,nvl(comm,0),(sal+nvl(comm,0))*12incomeFROMemp;2.decode函数范例1：SQL>SELECTdecode(1,1,'内容是1',2,'内容是2',3,'内容是3')FROMdual;范例2：emp表中雇员的工作有：clerk(业务员)、salesman（销售员）、manager（经理）、analyst（ 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 员）、president（总裁）。SQL>SELECTempno雇员编号,ename雇员姓名,hiredate雇佣日期,decode(job,’clerk’,’业务员’,’salesman’,’销售人员’,’manager’,’经理’,’analyst’,’分析员’,’president’,’总裁’)职位FROMemp;3.4.6统计函数使用统计函数可以对一组数据进行计算。函数说明AVG平均值SUM汇总值MAX最大值MIN最小值COUNT统计值示例：SQL>SELECTMAX(sal),min(sal)FROMemp;3.5分组技术在SELECT语句中可以使用GROUPBY子句进行分组操作，并可以使用HAVING子句提供分组条件。示例：统计各部门的员工人数SQL>SELECTdeptno,count(*)as员工数量FROMempGROUPBYdeptno;使用HAVING子句对分组进行筛选SQL>SELECTdeptno,count(*)as员工数量FROMempGROUPBYdeptnoHAVINGCOUNT(*)>=5;3.6子查询如果某个SQL语句依赖于另外一个SELECT语句的检索结果，可以把SELECT语句嵌入到该语句中，就形成了一个子查询。示例：检索某一部门的员工信息SQL>SELECTempno,ename,job,salFROMempWHEREdeptno=(SELECTdeptnoFROMdeptWHEREdname='SALES');注意：外查询检索一行，子查询就要完整检索一遍。子查询最多可以嵌套255层，层数越多，效率越差。子查询可以分为3种类型：单行子查询、多行子查询和关联子查询3.6.1单行子查询子查询只返回单行单列值，可以作为一个常量使用。示例：查询工资最高和最低的员工信息SQL>SELECTempno,ename,job,salFROMempWHEREsal>=(SELECTMAX(sal)FROMemp)orsal<=(SELECTMIN(sal)FROMemp);3.6.2多行子查询多行子查询可以返回单列多行数据，必须使用多行运算符判断：IN：等于列表中的任何一值ANY：与子查询返回的任何一个值进行比较ALL：与子查询返回的所有值进行比较示例：(1)查询属于ACCOUNTING和RESEARCH部门的员工信息SQL>SELECTempno,ename,job,sal,deptnoFROMempWHEREdeptnoIN(SELECTdeptnoFROMdeptWHEREdname='ACCOUNTING'ORdname='RESEARCH');(2)查询大于MANAGER职位中最小工资的员工信息SQL>SELECTempno,ename,job,salFROMempWHEREsal>ANY(SELECTsalFROMempWHEREjob='MANAGER');(3)查询工资大于所有MANAGER职位的员工信息SQL>SELECTempno,ename,job,salFROMempWHEREsal>ALL(SELECTsalFROMempWHEREjob='MANAGER');3.6.3关联子查询在前面介绍的子查询中，内查询和外查询是分开执行的，内外查询没有关系。外查询仅仅使用内查询的最终结果。如果在子查询中，内查询的执行需要借助于外查询，而外查询的执行又离不开内查询，内外查询是相互关联的，称为关联子查询。示例：检索某个职位的员工工资是否超出了平均水平。SQL>SELECTename,job,salFROMemptWHEREsal>(SELECTAVG(sal)FROMempWHEREt.job=job);注意：表中数据量大时关联子查询效率低。3.7操作数据包括插入、更新和删除数据。3.7.1插入数据语法格式：INSERTINTO表名（字段列表）VALUES（值列表）示例：SQL>INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7995,'ATG','CLERK',7782,to_date('2010-10-01','yyyy-mm-dd'),1300,null,10);向表中所有列添加数据时，可以省略列清单SQL>INSERTINTOempVALUES(7995,'ATG','CLERK',7782,to_date('2010-10-01','yyyy-mm-dd'),1300,null,10);注意：省略列清单容易出错，不建议使用。可以只为部分列提供数据，被省略的列必须允许为空值、有默认值或可以自动生成值。不能违反主键约束。可以结合SELECT语句一次向表添加多行数据：SQL>CREATETABLEemployee(empnonumber(4),enamevarchar2(10),jobvarchar2(20),hiredatedate,salnumber(6,2));SQL>INSERTINTOemployeeSELECTempno,ename,job,hiredate,salFROMemp;3.7.2更新数据语法格式：UPDATE表名SET字段名=表达式WHERE条件式3.7.3删除数据语法格式：DELETEFROM表名WHERE条件式示例：SQL>DELETEFROMempWHEREename='ATG';3.8ROWID的应用示例1：删除表中重复记录（适用于有大量重复记录的情况）SQL>CREATETABLEempaASSELECT*FROMemp;SQL>INSERTINTOempaSELECT*FROMempWHEREempno=7934;SQL>DELETEFROMempaWHERErowidNOTIN(SELECTMIN(rowid)FROMempaGROUPBYempno);示例2：删除表中重复记录（适用于有少量重复记录的情况）SQL>DELETEFROMempaaWHERErowid<>(SELECTMAX(rowid)FROMempaWHEREempno=a.empno);3.9ROWNUM的使用Oracle没有提供TOPN语句，若希望按特定条件查询前N条记录，可以使用伪列ROWNUM。ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列(注意：先要有结果集)。rownum的值是oracle顺序分配的从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推。示例：建立student表，结构为：ID　char(6)　　　　　--学号name　VARCHAR2(10)　　　--姓名SQL>CREATETABLEstudent(IDCHAR(6),nameVARCHAR2(10));--添加测试记录SQL>insertintostudentvalues('200001','张一');SQL>insertintostudentvalues('200002','王二');SQL>insertintostudentvalues('200003','李三');SQL>insertintostudentvalues('200004','赵四');⑴rownum用于等于某值的查询查询表中第一条记录，可以使用rownum=1作为条件。SQL>SELECTrownum,id,nameFROMstudentWHERErownum=1;查询表中第n（n>1）条记录，使用rownum=n查不到。SQL>SELECTrownum,id,nameFROMstudentWHERErownum=2;说明：rownum是一个总是从1开始的伪列，是oracle从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类推。如果用>,>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除，接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。可以使用子查询解决：子查询中的rownum必须要有别名，否则还是查不出记录。因为rownum不是某个表的列，如果不起别名，就无法知道rownum是子查询的列还是主查询的列。起别名：SQL>SELECT*FROM(SELECTrownumno,id,nameFROMstudent)WHEREno=2;不起别名：SQL>SELECT*FROM(SELECTrownum,id,nameFROMstudent)WHERErownum=2;⑵rownum用于大于某值的查询Oracle中rownum>n（n>1）条件依旧不成立。SQL>SELECTrownum,id,nameFROMstudentWHERErownum>2;使用子查询解决：SQL>SELECT*FROM(SELECTrownumno,id,nameFROMstudent)WHEREno>2;⑶rownum对于小于某值的查询条件条件rownum1）是成立的。SQL>SELECTrownum,id,nameFROMstudentWHERErownum<3;可以使用子查询得到rownum在某区间的数据。SQL>SELECT*FROM(SELECTrownumno,id,nameFROMstudentWHERErownum<=3)WHEREno>=2;⑷rownum和排序Oracle中的rownum是在取数据的时候产生的序号。SQL>SELECTrownum,id,nameFROMstudentORDERBYname;ROWNUMIDNAME-------------------------------------------------------------------3200003李三2200002王二1200001张一4200004赵四必须使用子查询解决问题：SQL>SELECTrownum,id,nameFROM(SELECT*FROMstudentORDERBYname);ROWNUMIDNAME-------------------------------------------------------------------1200003李三2200002王二3200001张一4200004赵四3.10Oracle分页Oracle中的分页较之SQLServer更为复杂。示例：每页3条记录SQL>SELECTb.*FROM(SELECTrownumr,a.*FROM(SELECTe.*FROMempeORDERBYhiredateDESC)a)bWHEREb.r<=3;第4节Oracle事务处理4.1事务的基本概念在Oracle中使用INSERT、UPDATE和DELETE语句操作数据时，数据库中的数据并不会立即改变，用户可以通过控制事务确认是否提交或取消先前的操作。事务是数据库系统工作的一个逻辑单元，它由一个或多个SQL语句组成。事务是不可分割的，一个事务中的所有SQL语句要么全部执行，要么全部不执行。当事务被提交后，该事务的操作才真正被保存到数据库中。如果某个事务被回退了，那么该事务的所有操作都被取消。事务的回退和提交可以由用户显式执行，也可以隐含地执行。下面启动两个SQL*Plus会话演示事务处理特性1.建立表，向表中插入数据并查询验证SQL>CREATETABLEstudent(IDCHAR(6),nameVARCHAR2(10));SQL>insertintostudentvalues('200001','张一');SQL>SELECT*FROMstudent;2.启动另外一个SQL*Plus验证SQL>SELECT*FROMstudent;3.在第一个SQL*Plus中使用COMMIT语句提交事务SQL>commit;4.2事务控制事务通过COMMIT和ROLLBACK语句控制。Oracle中的事务隐式自动开始，不需要用户显式地使用语句开始事务处理。当发生如下情况时，Oracle认为一个事务结束：执行COMMIT语句提交事务。执行ROLLBACK语句撤销事务。执行一条数据定义语句（例如CREAE、DROP或ALTER语句等）。如果该语句执行成功，那么表示系统自动执行COMMIT命令；如果这种操作失败，那么表示系统自动执行ROLLBACK命令。执行一个数据控制命令（例如GRANT、REVOKE等），这种操作表示自动执行COMMIT命令。断开数据库的连接。如果使用EXIT命令正常退出SQL*Plus，则系统自动执行COMMIT命令；如果退出SQL*Plus出现异常，则系统自动执行ROLLBACK命令。1.提交事务提交事务意味着该事务对数据库进行的全部操作将永久地记录在数据库中。2.回退事务回退事务意味着在该事务中对数据库进行的全部操作将被撤消，Oracle利用回退段（或撤消表空间）存储修改前的数据。Oracle具有“保存点”机制，可以将一个事务分割为几部分。用户可以有选择地回退到某个保存点，该保存点之后的操作将被取消。示例：向student插入两条记录，并在两次插入操作之间建立保存点SQL>insertintostudentvalues('200002','王二');SQL>savepoints1;SQL>insertintostudentvalues('200003','李三');SQL>SELECT*FROMstudent;SQL>rollbacktosavepoints1;SQL>commitSQL>SELECT*FROMstudent;第5节SQL优化由于Oracle可以存储海量数据，数据的查询效率犹为重要，低效率的查询给实际应用带来的麻烦是不可估量的。对于DELETE语句、SELECT子句、FROM子句和WHERE子句等都可以进行优化。1.SELECT语句中避免使用“*”在SELECT子句中使用“*”替代所有的列，可以降低编写SQL语句的难度，减少SQL语句的复杂性，但是却降低了SQL语句执行的效率。【范例】：首先使用SETTIMINGON语句显示执行时间。然后检索emp表，使用“*”来替代所有的列名，执行语句和执行时间如下：SQL>SETTIMINGONSQL>SELECT*FROMemp;已用时间:00:00:00.04在SELECT子句中不使用“*”，而使用具体的列名，执行语句和执行时间如下：SQL>SELECTempno,ename,job,mgr,hiredate,sal,comm,deptno2FROMemp;已用时间:00:00:00.012.使用WHERE子句替代HAVING子句在SELECT语句中，使用WHERE子句过滤行，使用HAVING子句过滤分组，也就是在行分组之后才执行过滤。【范例】：将emp表根据deptno列进行分组，并使用HAVING子句过滤deptno列值大于10的记录信息。具体如下：SQL>SELECTdeptno,avg(sal)FROMscott.empGROUPBYdeptnoHAVINGdeptno>10;DEPTNOAVG(SAL)--------------------301566.66667202175已用时间:00:00:00.05用where替代having：SQL>SELECTdeptno,avg(sal)FROMscott.empWHEREdeptno>10GROUPBYdeptno;已用时间:00:00:00.013.使用TRUNCATE替代DELETE删除表中数据可以使用DELETE语句，也可以使用TRUNCATE语句。使用DELETE语句删除表中数据时，Oracle会对数据逐行删除，并使用回滚段来记录删除操作，用户在没有使用COMMIT提交之前使用ROLLBACK命令进行回滚操作，则会将表中的数据恢复到删除之前的状态。使用TRUNCATE语句删除表中的所有数据行时，Oracle不会在撤消表空间中记录删除操作，这就提高了语句的执行速度。而且这种删除是一次性的，执行一次TRUNCATE语句，所有的数据行是在同一时间被删除。如果确定要删除表中的所有行，建议使用TRUNCATE语句。【范例】：创建一个表test，向该表中添加10000行记录数据。然后使用DELETE语句执行删除操作，如下：SQL>DELETEFROMtest;已删除10000行。已用时间:00:00:03.71同样的条件下，使用TRUNCATE语句执行删除操作，如下：SQL>TRUNCATETABLEtest;已用时间:00:00:01.454.使用表连接而不是多个查询执行每条查询语句时，Oracle内部执行了许多工作——解析SQL语句、估算索引的利用率、绑定变量，以及读取数据块等。因此要尽量减少访问SQL语句的执行次数。可以使用一次查询获得的数据，尽量不要通过两次或更多次的查询获得。【范例】:对scott用户的emp表和dept表进行