专题二-Oracle基本使用.ppt

1、专题二：Oracle基本使用,1.SQL*Plus基础 理解SQL*Plus的运行环境 SQL*Plus的帮助命令 SQL*Plus常用命令的使用（重点） 2.SQL语言基础 SQL语言概述、分类 常用查询语句的使用（重点） 常用系统函数及其应用（重点） 分组语句、模糊查询、表的连接以及子查询（重点）,1.SQL*Plus基础,1.1理解SQL*Plus的运行环境 在数据库系统中，可以使用两种方式执行命令，一种方式是通过图形化工具(例如：Oracle10g中提供基于Web方式的页面管理数据库)，另一种方式是直接使用各种命令。图形化工具的特点是直观、简单，但是图形工具灵活性比较差，不利于用户对命

2、令的理解；而命令则非常灵活，有利于加深用户对复杂命令选项的理解，并且可以完成某些图形工具无法完成的任务。 SQL*Plus则是用于执行SQL语句和PL/SQL程序的工具。,1.SQL*Plus基础,1.2 HELP命令 SQL*Plus有许多命令，而且每个命令都有大量的选项，要记住每一个命令的所有选项是很困难的。用户可以可以使用HELP命令查询相关的命令信息。SQL*Plus帮助系统可以向用户提供下面一些信息： 命令标题。 命令作用描述的文件。 命令的缩写形式。 命令中使用的强制参数和可选参数。,1.SQL*Plus基础,HELP命令的语法形式： help topic 说明：topic参数表示

3、将要查询的命令名称。 使用help index命令，可以查看SQL*Plus命令清单。 例如：help shutdown,1.SQL*Plus基础,1.3 SQL*Plus常用命令的使用 1.3.1 SET命令 1.3.2 DESCRIBE命令 1.3.3 PROMPT命令 1.3.4 SPOOL命令 1.3.5格式化查询结果 COLUMN命令 TTITLE和BTITLE命令 1.3.6缓存区,1.SQL*Plus基础,1.3.1 SET命令 SET命令用来设置SQL*Plus的运行环境。语法格式为： SET system_option value 列举：,说明： 更多参数选项可以利用help

4、命令查询其使用方法。,1.3.1 SET命令举例,显示SQL*Plus默认的一页的大小 设置PAGESIZE为30后查询SCOTT.EMP表,注意：比较设置前后的效果。,练习：利用选项LINESIZE对每行打印的字符进行设置，比较设置前后的效果。,1.SQL*Plus基础,1.3.2 DESCRIBE命令 DESCRIBE命令可以返回数据库中所存储的对象的描述。对于表、视图等对象而言，DESCRIBE命令可以列出其各个列的名称以及各个列的属性。除此之外，DESCRIBE还会输出过程、函数和程序包的规范。,1.SQL*Plus基础,1.3.3 PROMPT命令 使用PROMPT命令可以在屏幕上输

5、出一行数据，这种输出方式非常有助于在存储的脚本文件中向用户传递相应的信息。 PROMPT命令的语法形式如下： Prompt_text表示用于指定要在屏幕上显示的提示信息。,PROMPT prompt_text;,例如：将命令存储在user_tablespace.sql文件中,在SQL*Plus中使用命令运行user_tablespace.sql文件，运行结果如下：,知识点：数据字典(Oracle数据库的核心组件),数据字典是由一系列对于用户而言是只读的基础表和视图组成，它保存了关于数据库本身以及其中存储的所有对象的基本信息。 对数据字典的管理和维护由Oracle系统负责，任何数据库用户都无法对

6、数据字典中的内容进行修改，但是用户可以查看数据字典中的内容。 数据字典中的信息是通过表和视图的形式组织起来的，数据字典和视图都保存在SYSTEM表空间中。视图好比一本关于当前Oracle数据库的参考手册，可以通过SELECT语句查询。,知识点：数据字典(Oracle数据库的核心组件),Oracle中的数据字典有静态和动态之分。 静态数据字典：在用户访问数据字典时不会发生改变，主要是由表和视图组成，数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。静态数据字典中的视图分为三类，它们分别由三个前缀够成：user_*、 all_*、 dba_*。 动态数据字典：依赖数据库运行的性能的，

7、反映数据库运行的一些内在信息 ，会不断进行更新 。,知识点：数据字典(Oracle数据库的核心组件),静态数据字典3类视图： user_* 该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象） all_*该试图存储了当前用户能够访问的对象的信息。（与user_*相比，all_* 并不需要拥有该对象，只需要具有访问该对象的权限即可） dba_*该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限）,知识点：数据字典(Oracle数据库的核心组件),查看当前用户的缺省表空间 SQL select username,defau

8、lt tablespacefrom user_users; 查看当前用户的角色 SQLselect*fromuser_role_privs; 查看当前用户的系统权限和表级权限 SQLselect*fromuser_sys_privs; SQLselect*fromuser_tab_privs; 查看用户下所有的表 SQLselect*fromuser_tables; 显示指定用户所具有的系统权限 SQLselect*fromdba_sys_privswheregrantee=SCOTT;,数据字典查询举例：,知识点：数据字典(Oracle数据库的核心组件),显示特权用户 select*from

9、v$pwfile_users; 显示全局存储区SGA的信息,SQL select name,value from v$sga; NAME ALUE - - Fixed Size 1333312 Variable Size 310380480 Database Buffers 58720256 Redo Buffers 6201344,1.SQL*Plus基础,1.3.4 SPOOL命令 使用SPOOL命令可以把查询结果保存到文件中，或者把查询结果发送到打印机中。SPOOL命令的语法格式如下：,spool file_name create | replace | append | off;,说明

10、： file_name参数用于指定文件的名称，默认的扩展名为.LST create关键字表示创建一个新的文件 replace关键字表示替代已经存在的文件 append表示把内容附加到一个已经存在的文件中,1.3.4 SPOOL命令,例如：使用SPOOL命令生成output_file.txt文件，并将查询SCOTT.EMP表的内容保存到该文件中，显示如下：,1.SQL*Plus基础,1.3.5格式化查询结果 SQL*Plus提供了大量的命令用于格式化查询结果，使用这些命令可以对查询结果进行格式化，以产生用户需要的报表。使用这些命令可以实现重新设置列的标题，重新定义列的值的显示格式和显示宽度，为报

11、表增加头标题和底标题，在报表中显示当前日期和页号，也可以为报表添加新的统计数据等。常用的格式化查询结果命令包括：COLUMN、BTITLE和TTITLE等。,1.SQL*Plus基础,COLUMN命令 通过使用COLUMN命令，可以对查询结果进行格式。COLUMN命令的语法格式如下：,column column_name alias | option ,说明： column_name参数用于指定要控制的列名称。 alias参数用于指定列的别名。 option参数用于指定某个列的显示格式,COLUMN命令,Option选项的取值及意义,COLUMN命令举例,显示结果：,COLUMN命令举例,为查

12、询的各列指定别名 SQL column empno heading 工作编号 SQL column ename heading 姓名 SQL column sal heading 工资 SQL select empno,ename,sal from emp;,COLUMN命令举例,使用命令查看特定列的显示属性：Column 列名 例如：column sal 通过ON/OFF设置某列的显示属性是否起作用。 例如：column sal off 禁用了列的显示属性。,1.SQL*Plus基础,TTITLE和BTITLE命令 利用TTITLE和BTITLE 命令设置打印时每页的顶部和底部标题。 TTI

13、TLE命令的语法格式为： TTITLE printspec text| variable |off|on 说明： printspec用来设置格式化头部标题的字句。它可以用如下选项。 LEFT|CENTER|RIGHT 在当前行中左对齐|中间|右对齐打印 BLOD 以黑体打印数据 COL 指定在当前行的第几列打印头部标题。 SKIP 跳到从下一行开始的第几行，默认值为1 off 选项用于禁止打印头部标题，on用于打印头部标题,TTITLE和BTITLE命令举例,例如： SQL ttitle center 职工信息表 SQL btitle left 2010/03/25 SQL select em

14、pno,ename,sal from emp;,TTITLE和BTITLE命令举例,例如： SQL ttitle center 职工信息表 skip - SQL left 页 SQL.PNO skip SQL select empno,ename,sal from emp;,1.SQL*Plus基础,1.3.6缓存区 SQL*Plus可以在缓存区中存储用户最近执行的命令。通过在缓存区中存储这些命令，用户可以重新调用、编辑或运行那些最近输入的SQL语句。编辑缓存区最常用的方法是将缓存区中的内容传递到Windows记事本中进行编辑。,save file_name create | replace

15、| append | off;,例如： save D:2010春季课件大型数据库技术oracle资料samp_filesemployee.sql,1.3.6缓存区,使用命令：EDIT 可以对文本内容进行编辑 例如：edit D:2010春季课件大型数据库技术oracle资料samp_filesemployee.sql 使用命令：GET命令把一个命令文件的内容放进缓冲 区使用。语法形式： Get file_name .ext list|nolist,注意：/执行存储在缓冲区中最近执行的SQL命名。,1.3.6缓存区,使用命令：CLEAR BUFFER清除缓冲区中的内容。,说明：在查询语句中，允许

16、使用变量替代查询条件语句中的常量值，形式为,使用命令：LIST查看缓冲区中最近执行的SQL命令,2. SQL语言基础,1.SQL语言概述、分类 2.各种语言常见语句写法 3.常用查询语句的使用 4.常用系统函数及其应用 5.分组语句 6.模糊查询 7.表的连接 8.子查询,语言分类,1 DDL CREATE ALTER DROP 2 DCL GRANT REVOKE 3 DML SELECT INSERT DELETE UPDATE,练习： 1.在scott模式下创建一个表。 2.改变该表结构(如，给表加一个字段)。 3.给其他用户授权能对新建表进行操作（如，SELECT权限）。 4.以被授权

17、用户的身份连接数据库后检验是否能对scott模式下创建的新表进行操作。 5.再次以scott模式连接数据库，将权限收回。 6.在新建表中插入一条或多条数据。 7.在新建表中对数据进行查询、修改或删除操作。,常用系统函数,1.字符 Length,ltrim,replace,rtrim,substr,trim 2.日期 Sysdate,current_date,next_day 3.转换 To_char,to_date,to_number 4.统计函数 Sum,avg,max,min,count 5.其他 User,decode,nvl,Oracle事务管理,在Oracle系统中，在使用INSER

18、T、UPDATE和DELETE语句操作数据时，数据库中的数据并不会立即改变，用户还可以通过控制事务确认是否提交或取消先前的操作。,Oracle事务管理,1 事务的基本概念 事务是数据库系统工作的一个逻辑单元，它由一个或多个SQL语句组成。 对于数据库而言，事务是不可分割的工作单元，一个事务中的所有SQL语句要么全部执行，要么全部不执行。 当事务被提交后，该事务的操作才直正被保存到数据库中。如果某个事务被回退了，那么该事务的所有操作都被取消。 事务的回退和提交可以由用户显式执行（COMMIT语句提交事务），也可以隐含地执行。只有当事务被提交后，其他用户才能够看到对数据库的修改结果。,35,Ora

19、cle事务管理举例,（1）使用SCOTT身份连接数据库，并向EMP表中添加一条数据： SQL insert into emp(empno,ename,job,sal) 2 values(8000,zhanyan,TEACHER,2300); （2）在该会话中查看先前插入的数据。 SQL select empno,ename,job,sal 2 from emp 3 where empno=8000;,运行结果： EMPNO ENAME JOB SAL - - - - 8000 zhanyan TEACHER 2300,Oracle事务管理举例,（3）打开另一个SQL*PLUS，并保持第一个SQ

20、L*PLUS不关闭。在第二个SQL*PLUS中执行相同的SELECT语句查看先前插入的数据。,SQL select empno,ename,job,sal 2 from emp 3 where empno=8000; 未选定行,结论：由于第一个会话没有提交事务，所以在第二个会话中看不到第一个会话添加的数据。,Oracle事务管理举例,（4）在第一个SQL*PLUS中使用COMMIT语句提交事务。 SQL commit; （5）用户再次在第二个SQL*PLUS中运行SELECT语句，就会看到在第一个SQL*PLUS中所提交的数据。,Oracle事务管理,2 事务控制 Oracle中的事务是隐式自动开始，它不需要用户显式地使用语句开始事务处理。当发生如下情况时，Oracle认为一个事务结束： 执行COMMIT语句提交事务。 执行ROLLBACK语句撤销事务。 执行一条数据定义语句（例如CREAE、DROP或ALTER语句等）。如果该语句执行成功，那么表示系统自动执行COMMIT命令；如果这种操作失败，那么表示系统自动执行R