专题二Oracle基本使用PPT课件

.,1,专题二：Oracle基本使用,1.SQL*Plus基础理解SQL*Plus的运行环境SQL*Plus的帮助命令SQL*Plus常用命令的使用（重点）2.SQL语言基础SQL语言概述、分类常用查询语句的使用（重点）常用系统函数及其应用（重点）分组语句、模糊查询、表的连接以及子查询（重点）,.,2,1.SQL*Plus基础,1.1理解SQL*Plus的运行环境在数据库系统中，可以使用两种方式执行命令，一种方式是通过图形化工具(例如：Oracle10g中提供基于Web方式的页面管理数据库)，另一种方式是直接使用各种命令。图形化工具的特点是直观、简单，但是图形工具灵活性比较差，不利于用户对命令的理解；而命令则非常灵活，有利于加深用户对复杂命令选项的理解，并且可以完成某些图形工具无法完成的任务。SQL*Plus则是用于执行SQL语句和PL/SQL程序的工具。,.,3,1.SQL*Plus基础,1.2HELP命令SQL*Plus有许多命令，而且每个命令都有大量的选项，要记住每一个命令的所有选项是很困难的。用户可以可以使用HELP命令查询相关的命令信息。SQL*Plus帮助系统可以向用户提供下面一些信息：命令标题。命令作用描述的文件。命令的缩写形式。命令中使用的强制参数和可选参数。,.,4,1.SQL*Plus基础,HELP命令的语法形式：helptopic说明：topic参数表示将要查询的命令名称。使用helpindex命令，可以查看SQL*Plus命令清单。例如：helpshutdown,.,5,.,6,1.SQL*Plus基础,1.3SQL*Plus常用命令的使用1.3.1SET命令1.3.2DESCRIBE命令1.3.3PROMPT命令1.3.4SPOOL命令1.3.5格式化查询结果COLUMN命令TTITLE和BTITLE命令1.3.6缓存区,.,7,1.SQL*Plus基础,1.3.1SET命令SET命令用来设置SQL*Plus的运行环境。语法格式为：SETsystem_optionvalue列举：,说明：更多参数选项可以利用help命令查询其使用方法。,.,8,1.3.1SET命令举例,显示SQL*Plus默认的一页的大小设置PAGESIZE为30后查询SCOTT.EMP表,注意：比较设置前后的效果。,练习：利用选项LINESIZE对每行打印的字符进行设置，比较设置前后的效果。,.,9,1.SQL*Plus基础,1.3.2DESCRIBE命令DESCRIBE命令可以返回数据库中所存储的对象的描述。对于表、视图等对象而言，DESCRIBE命令可以列出其各个列的名称以及各个列的属性。除此之外，DESCRIBE还会输出过程、函数和程序包的规范。,.,10,1.SQL*Plus基础,1.3.3PROMPT命令使用PROMPT命令可以在屏幕上输出一行数据，这种输出方式非常有助于在存储的脚本文件中向用户传递相应的信息。PROMPT命令的语法形式如下：Prompt_text表示用于指定要在屏幕上显示的提示信息。,PROMPTprompt_text;,.,11,例如：将命令存储在user_tablespace.sql文件中,在SQL*Plus中使用命令运行user_tablespace.sql文件，运行结果如下：,.,12,知识点：数据字典(Oracle数据库的核心组件),数据字典是由一系列对于用户而言是只读的基础表和视图组成，它保存了关于数据库本身以及其中存储的所有对象的基本信息。对数据字典的管理和维护由Oracle系统负责，任何数据库用户都无法对数据字典中的内容进行修改，但是用户可以查看数据字典中的内容。数据字典中的信息是通过表和视图的形式组织起来的，数据字典和视图都保存在SYSTEM表空间中。视图好比一本关于当前Oracle数据库的参考手册，可以通过SELECT语句查询。,.,13,知识点：数据字典(Oracle数据库的核心组件),Oracle中的数据字典有静态和动态之分。静态数据字典：在用户访问数据字典时不会发生改变，主要是由表和视图组成，数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。静态数据字典中的视图分为三类，它们分别由三个前缀够成：user_*、all_*、dba_*。动态数据字典：依赖数据库运行的性能的，反映数据库运行的一些内在信息，会不断进行更新。,.,14,知识点：数据字典(Oracle数据库的核心组件),静态数据字典3类视图：user_*该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象）all_*该试图存储了当前用户能够访问的对象的信息。（与user_*相比，all_*并不需要拥有该对象，只需要具有访问该对象的权限即可）dba_*该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限）,.,15,知识点：数据字典(Oracle数据库的核心组件),查看当前用户的缺省表空间SQLselectusername,defaulttablespacefromuser_users;查看当前用户的角色SQLselect*fromuser_role_privs;查看当前用户的系统权限和表级权限SQLselect*fromuser_sys_privs;SQLselect*fromuser_tab_privs;查看用户下所有的表SQLselect*fromuser_tables;显示指定用户所具有的系统权限SQLselect*fromdba_sys_privswheregrantee=SCOTT;,数据字典查询举例：,.,16,知识点：数据字典(Oracle数据库的核心组件),显示特权用户select*fromv$pwfile_users;显示全局存储区SGA的信息,SQLselectname,valuefromv$sga;NAMEALUE-FixedSize1333312VariableSize310380480DatabaseBuffers58720256RedoBuffers6201344,.,17,1.SQL*Plus基础,1.3.4SPOOL命令使用SPOOL命令可以把查询结果保存到文件中，或者把查询结果发送到打印机中。SPOOL命令的语法格式如下：,spoolfile_namecreate|replace|append|off;,说明：file_name参数用于指定文件的名称，默认的扩展名为.LSTcreate关键字表示创建一个新的文件replace关键字表示替代已经存在的文件append表示把内容附加到一个已经存在的文件中,.,18,1.3.4SPOOL命令,例如：使用SPOOL命令生成output_file.txt文件，并将查询SCOTT.EMP表的内容保存到该文件中，显示如下：,.,19,1.SQL*Plus基础,1.3.5格式化查询结果SQL*Plus提供了大量的命令用于格式化查询结果，使用这些命令可以对查询结果进行格式化，以产生用户需要的报表。使用这些命令可以实现重新设置列的标题，重新定义列的值的显示格式和显示宽度，为报表增加头标题和底标题，在报表中显示当前日期和页号，也可以为报表添加新的统计数据等。常用的格式化查询结果命令包括：COLUMN、BTITLE和TTITLE等。,.,20,1.SQL*Plus基础,COLUMN命令通过使用COLUMN命令，可以对查询结果进行格式。COLUMN命令的语法格式如下：,columncolumn_namealias|option,说明：column_name参数用于指定要控制的列名称。alias参数用于指定列的别名。option参数用于指定某个列的显示格式,.,21,COLUMN命令,Option选项的取值及意义,.,22,COLUMN命令举例,显示结果：,.,23,COLUMN命令举例,为查询的各列指定别名SQLcolumnempnoheading工作编号SQLcolumnenameheading姓名SQLcolumnsalheading工资SQLselectempno,ename,salfromemp;,.,24,COLUMN命令举例,使用命令查看特定列的显示属性：Column列名例如：columnsal通过ON/OFF设置某列的显示属性是否起作用。例如：columnsaloff禁用了列的显示属性。,.,25,1.SQL*Plus基础,TTITLE和BTITLE命令利用TTITLE和BTITLE命令设置打印时每页的顶部和底部标题。TTITLE命令的语法格式为：TTITLEprintspectext|variable|off|on说明：printspec用来设置格式化头部标题的字句。它可以用如下选项。LEFT|CENTER|RIGHT在当前行中左对齐|中间|右对齐打印BLOD以黑体打印数据COL指定在当前行的第几列打印头部标题。SKIP跳到从下一行开始的第几行，默认值为1off选项用于禁止打印头部标题，on用于打印头部标题,.,26,TTITLE和BTITLE命令举例,例如：SQLttitlecenter职工信息表SQLbtitleleft2010/03/25SQLselectempno,ename,salfromemp;,.,27,TTITLE和BTITLE命令举例,例如：SQLttitlecenter职工信息表skip-SQLleft页SQL.PNOskipSQLselectempno,ename,salfromemp;,.,28,1.SQL*Plus基础,1.3.6缓存区SQL*Plus可以在缓存区中存储用户最近执行的命令。通过在缓存区中存储这些命令，用户可以重新调用、编辑或运行那些最近输入的SQL语句。编辑缓存区最常用的方法是将缓存区中的内容传递到Windows记事本中进行编辑。,savefile_namecreate|replace|append|off;,例如：saveD:2010春季课件大型数据库技术oracle资料samp_filesemployee.sql,.,29,1.3.6缓存区,使用命令：EDIT可以对文本内容进行编辑例如：editD:2010春季课件大型数据库技术oracle资料samp_filesemployee.sql使用命令：GET命令把一个命令文件的内容放进缓冲区使用。语法形式：Getfile_name.extlist|nolist,注意：/执行存储在缓冲区中最近执行的SQL命名。,.,30,1.3.6缓存区,使用命令：CLEARBUFFER清除缓冲区中的内容。,说明：在查询语句中，允许使用变量替代查询条件语句中的常量值，形式为,使用命令：LIST查看缓冲区中最近执行的SQL命令,.,31,2.SQL语言基础,1.SQL语言概述、分类2.各种语言常见语句写法3.常用查询语句的使用4.常用系统函数及其应用5.分组语句6.模糊查询7.表的连接8.子查询,.,32,语言分类,1DDLCREATEALTERDROP2DCLGRANTREVOKE3DMLSELECTINSERTDELETEUPDATE,练习：1.在scott模式下创建一个表。2.改变该表结构(如，给表加一个字段)。3.给其他用户授权能对新建表进行操作（如，SELECT权限）。4.以被授权用户的身份连接数据库后检验是否能对scott模式下创建的新表进行操作。5.再次以scott模式连接数据库，将权限收回。6.在新建表中插入一条或多条数据。7.在新建表中对数据进行查询、修改或删除操作。,.,33,常用系统函数,1.字符Length,ltrim,replace,rtrim,substr,trim2.日期Sysdate,current_date,next_day3.转换To_char,to_date,to_number4.统计函数Sum,avg,max,min,count5.其他User,decode,nvl,.,34,Oracle事务管理,在Oracle系统中，在使用INSERT、UPDATE和DELETE语句操作数据时，数据库中的数据并不会立即改变，用户还可以通过控制事务确认是否提交或取消先前的操作。,.,35,Oracle事务管理,1事务的基本概念事务是数据库系统工作的一个逻辑单元，它由一个或多个SQL语句组成。对于数据库而言，事务是不可分割的工作单元，一个事务中的所有SQL语句要么全部执行，要么全部不执行。当事务被提交后，该事务的操作才直正被保存到数据库中。如果某个事务被回退了，那么该事务的所有操作都被取消。事务的回退和提交可以由用户显式执行（COMMIT语句提交事务），也可以隐含地执行。只有当事务被提交后，其他用户才能够看到对数据库的修改结果。,35,.,36,Oracle事务管理举例,（1）使用SCOTT身份连接数据库，并向EMP表中添加一条数据：SQLinsertintoemp(empno,ename,job,sal)2values(8000,zhanyan,TEACHER,2300);（2）在该会话中查看先前插入的数据。SQLselectempno,ename,job,sal2fromemp3whereempno=8000;,运行结果：EMPNOENAMEJOBSAL-8000zhanyanTEACHER2300,.,37,Oracle事务管理举例,（3）打开另一个SQL*PLUS，并保持第一个SQL*PLUS不关闭。在第二个SQL*PLUS中执行相同的SELECT语句查看先前插入的数据。,SQLselectempno,ename,job,sal2fromemp3whereempno=8000;未选定行,结论：由于第一个会话没有提交事务，所以在第二个会话中看不到第一个会话添加的数据。,.,38,Oracle事务管理举例,（4）在第一个SQL*PLUS中使用COMMIT语句提交事务。SQLcommit;（5）用户再次在第二个SQL*PLUS中运行SELECT语句，就会看到在第一个SQL*PLUS中所提交的数据。,.,39,Oracle事务管理,2事务控制Oracle中的事务是隐式自动开始，它不需要用户显式地使用语句开始事务处理。当发生如下情况时，Oracle认为一个事务结束：执行COMMIT语句提交事务。执行ROLLBACK语句撤销事务。执行一条数据定义语句（例如CREAE、DROP或ALTER语句等）。如果该语句执行成功，那么表示系统自动执行COMMIT命令；如果这种操作失败，那么表示系统自动执行