Oracle基本使用（课堂PPT）

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

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

3、语法形式：lhelp topicl说明：topic参数表示将要查询的命令名称。l使用help index命令，可以查看SQL*Plus命令清单。l例如：help shutdown2021/5/852021/5/861.SQL*Plus基础l1.3 SQL*Plus常用命令的使用1.3.1 SET命令1.3.2 DESCRIBE命令1.3.3 PROMPT命令1.3.4 SPOOL命令1.3.5格式化查询结果lCOLUMN命令lTTITLE和BTITLE命令1.3.6缓存区2021/5/871.SQL*Plus基础l1.3.1 SET命令 SET命令用来设置SQL*Plus的运行环境。语法格式为

4、： SET system_option value列举： 选项说明set linesize 80|n设置SQL*Plus在一行中能够显示的总字符数，默认值为80。可以的取值为任意正整数。s e t n e w p a g e 1|n|none设置每页打印标题前的空行数，默认值为1。set pagesize 14|n 设置每页打印的行数，该值包括NEWPAGE设置的空行数。set pauseoff|on|text设置SQL*Plus输出结果时是否滚动显示。当取值为NO时表示输出结果的每一页都暂停，用户按下回车键后继续显示；取为字符串时，每次暂停都将显示该字符串。说明： 更多参数选项可以利用hel

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

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

7、信息。l对数据字典的管理和维护由Oracle系统负责，任何数据库用户都无法对数据字典中的内容进行修改，但是用户可以查看数据字典中的内容。l数据字典中的信息是通过表和视图的形式组织起来的，数据字典和视图都保存在SYSTEM表空间中。视图好比一本关于当前Oracle数据库的参考手册，可以通过SELECT语句查询。2021/5/813知识点：数据字典(Oracle数据库的核心组件)lOracle中的数据字典有静态和动态之分。 静态数据字典：在用户访问数据字典时不会发生改变，主要是由表和视图组成，数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。静态数据字典中的视图分为三类，它们分别由三

8、个前缀够成：user_*、 all_*、 dba_*。 动态数据字典：依赖数据库运行的性能的，反映数据库运行的一些内在信息 ，会不断进行更新 。2021/5/814知识点：数据字典(Oracle数据库的核心组件)l静态数据字典3类视图：user_* 该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象）all_*该试图存储了当前用户能够访问的对象的信息。（与user_*相比，all_* 并不需要拥有该对象，只需要具有访问该对象的权限即可）dba_*该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限）2021/5/815知

9、识点：数据字典(Oracle数据库的核心组件)l查看当前用户的缺省表空间 SQL select username,default tablespace from user_users; l查看当前用户的角色 SQLselect * from user_role_privs; l查看当前用户的系统权限和表级权限 SQLselect * from user_sys_privs; SQLselect * from user_tab_privs; l查看用户下所有的表 SQLselect * from user_tables; l显示指定用户所具有的系统权限 SQLselect * from dba_

10、sys_privs where grantee=SCOTT; 数据字典查询举例：2021/5/816知识点：数据字典(Oracle数据库的核心组件)l显示特权用户 select * from v$pwfile_users; l显示全局存储区SGA的信息SQL select name,value from v$sga;NAME ALUE- -Fixed Size 1333312Variable Size 310380480Database Buffers 58720256Redo Buffers 62013442021/5/8171.SQL*Plus基础l1.3.4 SPOOL命令 使用SPOO

11、L命令可以把查询结果保存到文件中，或者把查询结果发送到打印机中。SPOOL命令的语法格式如下：spool file_name create | replace | append | off; 说明：file_name参数用于指定文件的名称，默认的扩展名为.LSTcreate关键字表示创建一个新的文件replace关键字表示替代已经存在的文件append表示把内容附加到一个已经存在的文件中2021/5/8181.3.4 SPOOL命令l例如：使用SPOOL命令生成output_file.txt文件，并将查询SCOTT.EMP表的内容保存到该文件中，显示如下：2021/5/8191.SQL*Plu

12、s基础l1.3.5格式化查询结果SQL*Plus提供了大量的命令用于格式化查询结果，使用这些命令可以对查询结果进行格式化，以产生用户需要的报表。使用这些命令可以实现重新设置列的标题，重新定义列的值的显示格式和显示宽度，为报表增加头标题和底标题，在报表中显示当前日期和页号，也可以为报表添加新的统计数据等。常用的格式化查询结果命令包括：COLUMN、BTITLE和TTITLE等。2021/5/8201.SQL*Plus基础lCOLUMN命令命令通过使用COLUMN命令，可以对查询结果进行格式。COLUMN命令的语法格式如下：column column_name alias | option 说明：

13、column_name参数用于指定要控制的列名称。alias参数用于指定列的别名。option参数用于指定某个列的显示格式2021/5/821COLUMN命令命令lOption选项的取值及意义选项说明FORMAT为列指定显示格式HEADING定义列的标题JUSTIFY调整列标题的对齐方式。默认情况是数字列为右对齐，其他列为左对齐。可以设置的标题位置值为：LEFT、CENTER、RIGHTON|OFF控制显示属性的状态，OFF表示定义的显示属性不起作用WRAPPED当字符串的长度超过显示宽度时，将字符串的超出部分折叠到下一行显示。2021/5/822COLUMN命令举例命令举例l显示结果：202

14、1/5/823COLUMN命令举例命令举例l为查询的各列指定别名SQL column empno heading 工作编号SQL column ename heading 姓名SQL column sal heading 工资SQL select empno,ename,sal from emp;2021/5/824COLUMN命令举例命令举例l使用命令查看特定列的显示属性：Column 列名 例如：column sall通过ON/OFF设置某列的显示属性是否起作用。例如：column sal off 禁用了列的显示属性。2021/5/8251.SQL*Plus基础lTTITLE和和BTITL

15、E命令命令利用TTITLE和和BTITLE 命令设置打印时每页的顶部和底部标题。TTITLE命令的语法格式为： TTITLE printspec text| variable |off|on 说明：说明： printspec用来设置格式化头部标题的字句。它可以用如下选项。 LEFT|CENTER|RIGHT 在当前行中左对齐|中间|右对齐打印 BLOD 以黑体打印数据 COL 指定在当前行的第几列打印头部标题。 SKIP 跳到从下一行开始的第几行，默认值为1off 选项用于禁止打印头部标题，on用于打印头部标题2021/5/826TTITLE和和BTITLE命令举例命令举例l例如：lSQL t

16、title center 职工信息表lSQL btitle left 2010/03/25lSQL select empno,ename,sal from emp;2021/5/827TTITLE和和BTITLE命令举例命令举例l例如：lSQL ttitle center 职工信息表 skip -lSQL left 页 SQL.PNO skiplSQL select empno,ename,sal from emp;2021/5/8281.SQL*Plus基础l1.3.6缓存区l SQL*Plus可以在缓存区中存储用户最近执行的命令。通过在缓存区中存储这些命令，用户可以重新调用、编辑或运行那些

17、最近输入的SQL语句。编辑缓存区最常用的方法是将缓存区中的内容传递到Windows记事本中进行编辑。save file_name create | replace | append | off;例如： save D:2010春季课件大型数据库技术oracle资料samp_filesemployee.sql2021/5/8291.3.6缓存区使用命令：EDIT 可以对文本内容进行编辑例如：edit D:2010春季课件大型数据库技术oracle资料samp_filesemployee.sql使用命令：GET命令把一个命令文件的内容放进缓冲区使用。语法形式： Get file_name .ext

18、list|nolist注意：/执行存储在缓冲区中最近执行的SQL命名。2021/5/8301.3.6缓存区使用命令：CLEAR BUFFER清除缓冲区中的内容。说明：在查询语句中，允许使用变量替代查询条件语句中的常量值，形式为&变量名。例如： select empno,ename,sal from emp where sal &s;使用命令：LIST查看缓冲区中最近执行的SQL命令2021/5/8312. SQL语言基础l1.SQL语言概述、分类l2.各种语言常见语句写法l3.常用查询语句的使用l4.常用系统函数及其应用l5.分组语句l6.模糊查询l7.表的连接l8.子查询20

19、21/5/832语言分类l1 DDLCREATEALTERDROPl2 DCLGRANTREVOKEl3 DMLSELECTINSERTDELETEUPDATE练习：1.在scott模式下创建一个表。2.改变该表结构(如，给表加一个字段)。3.给其他用户授权能对新建表进行操作（如，SELECT权限）。4.以被授权用户的身份连接数据库后检验是否能对scott模式下创建的新表进行操作。5.再次以scott模式连接数据库，将权限收回。6.在新建表中插入一条或多条数据。7.在新建表中对数据进行查询、修改或删除操作。2021/5/833常用系统函数l1.字符lLength,ltrim,replace,r

20、trim,substr,triml2.日期lSysdate,current_date,next_dayl3.转换lTo_char,to_date,to_numberl4.统计函数lSum,avg,max,min,countl5.其他lUser,decode,nvl2021/5/834Oracle事务管理l在Oracle系统中，在使用INSERT、UPDATE和DELETE语句操作数据时，数据库中的数据并不会立即改变，用户还可以通过控制事务确认是否提交或取消先前的操作。2021/5/835Oracle事务管理1 事务的基本概念事务的基本概念l 事务是数据库系统工作的一个逻辑单元，它由一个或多个S

21、QL语句组成。l 对于数据库而言，事务是不可分割的工作单元，一个事务中的所有SQL语句要么全部执行，要么全部不执行。l 当事务被提交后，该事务的操作才直正被保存到数据库中。如果某个事务被回退了，那么该事务的所有操作都被取消。l 事务的回退和提交可以由用户显式执行（COMMIT语句提交事务语句提交事务），也可以隐含地执行。只有当事务被提交后，其他用户才能够看到对数据库的修改结果。352021/5/836Oracle事务管理举例l（1）使用SCOTT身份连接数据库，并向EMP表中添加一条数据：lSQL insert into emp(empno,ename,job,sal)l 2 values(8

22、000,zhanyan,TEACHER,2300);l（2）在该会话中查看先前插入的数据。lSQL select empno,ename,job,sall 2 from empl 3 where empno=8000;运行结果： EMPNO ENAME JOB SAL- - - - 8000 zhanyan TEACHER 23002021/5/837Oracle事务管理举例l（3）打开另一个SQL*PLUS，并保持第一个SQL*PLUS不关闭。在第二个SQL*PLUS中执行相同的SELECT语句查看先前插入的数据。SQL select empno,ename,job,sal 2 from e

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