数据库系统管理与应用.ppt

数据库系统管理与应用,大连民族学院计算机学院,何丽君,课程名称：数据库系统管理与应用 教材：Oracle 10g 数据库管理 应用与开发标准教程 马晓玉等 清华大学出版社 参考书： Oracle 10g 入门与提高 王彬 清华大学出版社 Oracle 10g DBA 宝典 路川 电子工业出版社 总学时：64学时（理论40学时+上机24学时） 任课教师：何丽君 办公地点：2-446（网络工程教研室） E-mail：,不旷课、不迟到、不早退（无故旷课1次扣平时分2分，迟到或早退1次扣平时分1分），累计旷课（迟到三次计一次旷课）达三次以上者，取消考试资格。 认真听课，认真复习，认真完成实验报告和作业（缺1次作业扣平时分2分，雷同作业或报告都按缺交计,缺交报告或实验检查缺席实验成绩按0分计） 尽量靠前坐 上课时将手机等通讯工具关闭,课程要求：,为什么选择学习Oracle,Oracle数据库环境已成为世界上最流行的数据库平台之一 1977年成立于美国加州，是一个小型的关系数据库公司 1979年推出ORACLE2是首批商用的RDBMS 1986年的ORACLE5是世界上第一个开放型分布式数据库产品 1988年的ORACLE6具有OLTP的能力 1992年ORACLE7适用于大型OLTP及C/S应用 1997年ORACLE8i支持数据仓库、Web应用 2001年在ORACLE8i的基础上推出ORACLE9i 2004年发布了ORACLE10g 2007年7月新发布ORACLE11g,第一章 Oracle关系数据库,1.1 Oracle 10g的安装（教材1.6） 1.2 创建数据库（教材1.7） 1.3 数据库的启动与关闭（教材1.8） 1.4 Oracle 默认用户（教材1.9） 1.5 在SQL*Plus中使用函数（教材3.5） 1.6 SQL*Plus命令（教材4.4）,1.1 Oracle 10g的安装（教材1.6）,安装过程 Oracle 数据库的网络服务配置,Oracle Net 服务器端 基本配置 目标,了解监听程序如何响应传入的连接 使用 Oracle Net Manager 配置监听程序 使用 “监听程序控制实用程序” (Listener Control Utility, lsnrctl) 控制监听程序,监听程序进程,listener.ora,监听程序,客户机,服务器,tnsnames.ora,sqlnet.ora,sqlnet.ora,连接方法,客户机向服务器发出连接请求时，监听程序执行下列操作之一： 衍生一个服务器进程并将连接传送给该进程或由其继承 将连接传送给 Oracle Shared Server 配置中的调度程序 将连接重定向到调度程序或服务器进程,衍生、继承和直接传送连接,重定向会话,端口,监听程序,客户机,服务器,端口,服务器或 调度程序 进程,1,2,3,4,5,6,服务配置和注册,可以通过两种方式配置监听程序： 静态服务配置 用于 Oracle8 及更早版本 需要配置 listener.ora 对 Oracle Enterprise Manager 和其它服务是必需的,静态服务注册： listener.ora 文件,安装 Oracle 软件时，会为初始数据库创建具有下列缺省设置的 listener.ora 文件： 监听程序名 LISTENER 端口 1521 协议 TCP/IP 和 IPC SID 名 缺省例程 主机名 缺省主机名,静态服务注册： listener.ora 文件,1. LISTENER = 2. (ADDRESS_LIST = 3. (ADDRESS= (PROTOCOL= TCP)(Host= dlnu- hlj02)(Port= 1521) 4. SID_LIST_LISTENER = 5. (SID_LIST = 6. (SID_DESC = 7. (ORACLE_HOME= /home/oracle) (GLOBAL_DBNAME = ) 9. (SID_NAME = ORCL),静态服务注册： 创建监听程序,配置服务,事件记录与跟踪,“监听程序控制实用程序” (Listener Control Utility, LSNRCTL),“监听程序控制实用程序” (Listener Control Utility) 命令可从命令行或 LSNRCTL 提示符下发出。 UNIX 命令行语法： 提示符语法： 控制非缺省监听程序,$ lsnrctl ,LSNRCTL ,LSNRCTL set current_listener listener02,LSNRCTL 命令,使用以下命令控制监听程序： START listener_name STOP listener_name,LSNRCTL SET 和 SHOW 修改符,使用 SET 命令更改监听程序参数： 使用 SHOW 命令显示参数的值：,LSNRCTL SET trc_level ADMIN,LSNRCTL SHOW trc_directory,命名方法配置 目标,描述主机命名与本地服务名称解析之间的区别 使用 Oracle Net Configuration Assistant 配置： 主机命名方法 本地命名方法 网络服务名 排除简单连接出现的故障,命名方法概览,客户机应用程序在试图连接数据库服务时，需要借助命名方法将连接标识符解析为连接描述符。 Oracle Net 提供了五种命名方法： 主机命名 本地命名 目录命名 Oracle Names 外部命名,主机命名,在下列情况下，客户机可使用主机名来连接服务器： 使用 Oracle Net Services Client 软件连接 Oracle 数据库服务 使用 TCP/IP 协议建立客户机与服务器的连接 主机名通过 DNS 或本地的 /etc/hosts 文件等 IP 地址转换机制来解析 不使用 “连接管理器” (Connection Manager) 或安全选项等高级功能,主机命名：客户端,sqlnet.ora, names.directory_path = (HOSTNAME),服务器,listener.ora,客户机,TCP/IP,主机命名：服务器端,客户机,sqlnet.ora,listener.ora,服务器,SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ) (ORACLE_HOME = /u03/ora9i/rel12) (SID_NAME = TEST),1521,TCP/IP,主机命名示例,listener.ora 文件 从客户机连接,SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ) (ORACLE_HOME = /u03/ora9i/rel12) (SID_NAME = TEST),sqlplus system/,命名方法配置,本地命名,listener.ora,服务器,客户机,tnsnames.ora,sqlnet.ora,生成的文件： tnsnames.ora,# TNSNAMES.ORA Network Configuration # File:/u03/ora9i/rel12/network/admin/tnsnames.ora # Generated by Oracle configuration tools. MY_SERVICE.US.ORACLE.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = ) ) ),生成的文件： sqlnet.ora,# SQLNET.ORA Network Configuration File: /u03/ora9i/rel12/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DEFAULT_DOMAIN = NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME) SQLNET.EXPIRE_TIME=0,sqlplus system/abc123MY_SERVICE SQL*Plus:Release .0-Production on Thu Nov 15 13:46:24 2001 (c) Copyright 2001 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release .0 - Production JServer Release .0 - Production SQL,排除客户端故障,下面是在客户端出现问题时显示的错误代码：,ORA-12154 “TNS：无法解析服务名” ORA-12198 “TNS：找不到指向目标的路径” ORA-12203 “TNS：无法连接目标” ORA-12533 “TNS：ADDRESS 参数非法” ORA-12541 “TNS：没有监听程序 ”,第一章 Oracle关系数据库,1.1 Oracle 10g的安装（教材1.6） 1.2 创建数据库（教材1.7） 1.3 数据库的启动与关闭（教材1.8） 1.4 Oracle 默认用户（教材1.9） 1.5 在SQL*Plus中使用函数（教材3.5） 1.6 SQL*Plus命令（教材4.4）,第一章 Oracle关系数据库,1.1 Oracle 10g的安装（教材1.6） 1.2 创建数据库（教材1.7） 1.3 数据库的启动与关闭（教材1.8） 1.4 Oracle 默认用户（教材1.9） 1.5 在SQL*Plus中使用函数（教材3.5） 1.6 SQL*Plus命令（教材4.4）,第一章 Oracle关系数据库,1.1 Oracle 10g的安装（教材1.6） 1.2 创建数据库（教材1.7） 1.3 数据库的启动与关闭（教材1.8） 1.4 Oracle 默认用户（教材1.9） 1.5 在SQL*Plus中使用函数（教材3.5） 1.6 SQL*Plus命令（教材4.4）,ORACLE中预定义的用户,SYS SYSTEM SCOTT DEMO,用户名,第一章 Oracle关系数据库,1.1 Oracle 10g的安装（教材1.6） 1.2 创建数据库（教材1.7） 1.3 数据库的启动与关闭（教材1.8） 1.4 Oracle 默认用户（教材1.9） 1.5 在SQL*Plus中使用函数（教材3.5） 1.6 SQL*Plus命令（教材4.4）,转换,字符,数值,日期,通用,函数,字符函数,字符函数,LOWER UPPER INITCAP,CONCAT SUBSTR LENGTH INSTR LPAD TRIM,大小写转换,字符处理,函数,结果,大小写转换函数,转化字符串的大小写,LOWER(SQL Course) UPPER(SQL Course) INITCAP(SQL Course),sql course SQL COURSE Sql Course,使用大小写转换函数,显示Blake的雇员编号、姓名和部门编号。,SQL SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = blake; no rows selected,EMPNO ENAME DEPTNO - - - 7698 BLAKE 30,SQL SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = UPPER(blake);,CONCAT(Good, String) SUBSTR(String,1,3) LENGTH(String) INSTR(String, r) LPAD(sal,10,*) TRIM(S FROM SSMITH),GoodString Str 6 3 *5000 MITH,函数,结果,字符处理函数,字符处理函数,使用字符处理函数,SQL SELECT ename, CONCAT (ename, job), LENGTH(ename), 2 INSTR(ename, A) 3 FROM emp 4 WHERE,SUBSTR(job,1,5) = SALES;,ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,A) - - - - MARTIN MARTINSALESMAN 6 2 ALLEN ALLENSALESMAN 5 1 TURNER TURNERSALESMAN 6 0 WARD WARDSALESMAN 4 2,数值函数,ROUND: 四舍五入到指定的小数位 ROUND(45.926, 2) 45.93 TRUNC: 截取到指定的小数位 TRUNC(45.926, 2) 45.92 MOD: 取余数 MOD(1600, 300) 100,使用 ROUND 函数,SQL SELECT ROUND(45.923,2), ROUND(45.923,0), 2 ROUND(45.923,-1) 3 FROM DUAL;,ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1) - - - 45.92 46 50,SQL SELECT TRUNC(45.923,2), TRUNC(45.923), 2 TRUNC(45.923,-1) 3 FROM DUAL;,TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1) - - - 45.92 45 40,使用 TRUNC函数,使用 MOD 函数,计算工作为salsman的雇员的薪水和佣金相除后的剩余。,SQL SELECT ename, sal, comm, MOD(sal, comm) 2 FROM emp 3 WHERE job = SALESMAN;,ENAME SAL COMM MOD(SAL,COMM) - - - - MARTIN 1250 1400 1250 ALLEN 1600 300 100 TURNER 1500 0 1500 WARD 1250 500 250,日期的处理,Oracle是以一种内部的数值形式存储日期的，即：世纪、年、月、日、小时、分、秒。 默认的日期形式是： DD-MON-RR. SYSDATE 是一个可以返回日期和时间的函数 DUAL是一个虚拟表用于查看SYSDATE.,日期的运算,对一个日期型数据加上或减去一个数可以得到一个新的日期型数据。 两个日期型数据相减得到这两个日期的间隔数。 如要以小时相加则必须用小时数除以24得到的数据进行相加。,对日期使用算术运算符,SQL SELECT ename, (SYSDATE-hiredate)/7 WEEKS 2 FROM emp 3 WHERE deptno = 10;,ENAME WEEKS - - KING 830.93709 CLARK 853.93709 MILLER 821.36566,RR 日期格式,当前年份 1995 1995 2001 2001,指定的日期 27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95,RR 格式 1995 2017 2017 1995,YY 格式 1995 1917 2017 2095,如果当前年份的两位数是：,049,049,5099,5099,返回的日期是当前世纪的日期,返回的日期是下一个世纪的日期,返回的日期是上一个世纪的日期,返回的日期是当前世纪的日期,如果指定两位数年份是：,日期函数,两个日期之间间隔多少个月,MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC,向一个日期数据加一定的月份,某个指定日期之后的某天,返回某月的最后一天,对日期进行四舍五入,对日期进行截取,函数,描述,MONTHS_BETWEEN (01-SEP-95,11-JAN-94),使用日期函数,ADD_MONTHS (11-JAN-94,6),NEXT_DAY (01-SEP-95,FRIDAY),LAST_DAY(01-SEP-95),19.6774194,11-JUL-94,08-SEP-95,30-SEP-95,使用日期函数,转换函数,隐式数据类型转换,显式数据类型转换,数据类型转换,隐式转换,赋值语句中，Oracle服务器自动完成以下转换：,VARCHAR2 or CHAR,从,到,VARCHAR2 or CHAR,NUMBER,DATE,NUMBER,DATE,VARCHAR2,VARCHAR2,隐式转换,在表达式中， Oracle服务器自动完成以下转换：,VARCHAR2 or CHAR,从,到,VARCHAR2 or CHAR,NUMBER,DATE,显式转换,NUMBER,CHARACTER,TO_CHAR,显式转换,NUMBER,CHARACTER,TO_CHAR,显式转换,NUMBER,CHARACTER,TO_CHAR,TO_CHAR 用于日期型,日期格式模型： 必须用单引号引起来并且是大小写敏感的 可以包含任何有效的日期元素 可以用fm来消除前导空格或零 使用逗号与日期型数据分隔开,TO_CHAR(date, fmt),YYYY,日期格式模型的元素,YEAR,MM,MONTH,DY,DAY,完整的年份数字表示,年份的英文表示,用两位数字来表示月份,用3个英文字符所写来表示星期几,星期几完整的英文表示,月份的英文表示,日期格式模型的元素,用时间元素格式化日期的时间部分 通过使用双引号可以添加字符串 使用数字下标可以拼写数字号码,TO_CHAR 用于日期型,SQL SELECT ename, 2 TO_CHAR(hiredate, fmDD Month YYYY) HIREDATE 3 FROM emp;,ENAME HIREDATE - - KING 17 November 1981 BLAKE 1 May 1981 CLARK 9 June 1981 JONES 2 April 1981 MARTIN 28 September 1981 ALLEN 20 February 1981 . 14 rows selected.,TO_CHAR 用于数值型,通过在TO_CHAR中使用以下形式可以把数值型数据转化成变长的字符串：,TO_CHAR(number, fmt),9,0,$,L,.,一位数字,显示前导零,显示美元符号,显示本地货币号,显示小数点,显示千位符,TO_CHAR 用于数值型,SQL SELECT TO_CHAR(sal,$99,999) SALARY 2 FROM emp 3 WHERE ename = SCOTT;,SALARY - $3,000,TO_NUMBER 和 TO_DATE 函数,使用TO_NUMBER将一个字符串转换成数值型数据。,TO_NUMBER(char, fmt),使用TO_DATE将一个字符串转换成日期型数据。,TO_DATE(char, fmt),NVL 函数,将NULL转换为一个实际的值 数据类型可以是date、 character、 number。 数据类型必须匹配 NVL(comm,0) NVL(hiredate,01-JAN-97) NVL(job,No Job Yet),SQL SELECT ename, sal, comm, (sal*12)+NVL(comm,0) 2 FROM emp;,使用 NVL 函数,ENAME SAL COMM (SAL*12)+NVL(COMM,0) - - - - KING 5000 60000 BLAKE 2850 34200 CLARK 2450 29400 JONES 2975 35700 MARTIN 1250 1400 16400 ALLEN 1600 300 19500 . 14 rows selected.,函数的嵌套,单行函数可以嵌套于任何层。 嵌套的函数是从最里层向最外层的顺序计算的。,F3(F2(F1(col,arg1),arg2),arg3),Step 1 = Result 1,Step 2 = Result 2,Step 3 = Result 3,函数的嵌套,SQL SELECT ename, 2 NVL(TO_CHAR(mgr),No Manager) 3 FROM emp 4 WHERE mgr IS NULL;,ENAME NVL(TO_CHAR(MGR),NOMANAGER) - - KING No Manager,第一章 Oracle关系数据库,1.1 Oracle 10g的安装（教材1.6） 1.2 创建数据库（教材1.7） 1.3 数据库的启动与关闭（教材1.8） 1.4 Oracle 默认用户（教材1.9） 1.5 在SQL*Plus中使用函数（教材3.5） 1.6 SQL*Plus命令（教材4.4）,定制 SQL*Plus 环境,使用 SET 命令设置当前环境变量 使用SHOW 命令显示当前环境变量,SQL SET ECHO ON,SQL SHOW ECHO echo ON,SET system_variable value,Login.sql文件保存了每次会话的标准环境设置及其他SQL*Plus命令,常用的SET命令变量列表,ARRAYSIZE 20 | n COLSEP _ | text FEEDBACK 6 | n |OFF | ON HEADING OFF | ON LINESIZE 80 | n LONG 80 | n PAGESIZE 24 | n PAUSE OFF | ON | text TERMOUT OFF | ON,将定制的环境保存在 login.sql文件中,login.sql 文件保存了标准的环境设置及其他 SQL*Plus命令，这些都在登录SQL*Plus时自动加载 可以修改 login.sql 保存其它额外的设置命令,SQL*Plus 格式化命令,COLUMN column option TTITLE text | OFF | ON BTITLE text | OFF | ON BREAK ON report_element,COLUMN 命令,控制列的显示 其中，option有以下可选项； CLEAR: 清除所有列的显示格式 FORMAT format: 使用格式模型改变列的显示 HEADING text: 设置列标题 JUSTIFY align: 调整列标题的位置居左、居中还是居右,COLUMN column|alias option,使用 COLUMN 命令,创建列标题,显示列ENAME的当前设置,清除列ENAME的当前设置,COLUMN ename HEADING Employee|Name FORMAT A15 COLUMN sal JUSTIFY LEFT FORMAT $99,990.00 COLUMN mgr FORMAT 999999999 NULL No manager,COLUMN ename,COLUMN ename CLEAR,COLUMN 格式模型,结果 N/A 1234 01234 $1234 L1234 1234.00 1,234,举例 N/A 999999 099999 $9999 L9999 9999.99 9,999,元素 An 9 0 $ L . ,描述 设置显示宽度为 n个字符 每个9表是一位数字，不显示前导零 表示前导零 美元符号 本地货币符号 小数点的位置 千位分割符,使用 BREAK 命令,命令格式为： BREAK on column|alias|row skip n|dup|page on on report 其中： page 表示遇到断点时产生新的一页 skip n 表示遇到断点时跳跃n行 duplicate 显示重复值 并且断点可以作用于：列 、行、页、报表。 清除所有BREAK设置使用CLEAR 命令： CLEAR BREAK,BREAK 命令用于去掉重复的行以及断点处跳过指定的行数。,使用 TTITLE 和 BTITLE 命令,显示标题和脚注,TTITLE text|OFF|ON,SQL TTITLE Salary|Report,设置报表标题,设置报表脚注,SQL BTITLE Confidential,交互式报表,我想立即运行一个视图,.工资 = ? 部门 = ? 姓名 = ? .,User,可以采用在命令文件或单一的SQL语句中嵌入替代变量的方式实现。,替代变量,使用SQL*Plus替代变量临时存储数据 & & DEFINE 命令：创建变量并声明一个值 ACCEPT 命令：读取用户输入的一行 并保存成变量。 在 SQL语句之间传递值 动态地改变标题和脚注 从文件而不是人为输入获得输入的值。,使用 &替代变量,当运行一个报表时，用户希望能动态地限制返回的数据，SQL*Plus通过用户变量的方式提供了这种灵活性。在SQL语句中使用&来识别每一个变量而不需要定义变量的值。,使用 &替代变量,使用变量前缀&提示用户需要输入一个值,SQL SELECT empno, ename, sal, deptno 2 FROM emp 3 WHERE empno = ,Enter value for employee_num: 7369 EMPNO ENAME SAL DEPTNO - - - - 7369 SMITH 800 20,SET VERIFY 命令,先后显示SQL*Plus用值替换替代变量前后的命令文本,SQL SET VERIFY ON SQL SELECT empno, ename, sal, deptno 2 FROM emp 3 WHERE empno = ,Enter value for employee_num: 7369 old 3: WHERE empno = &employee_num new 3: WHERE empno = 7369 .,字符和日期的替代变量,使用单引号将日期和字符型的替代变量扩起来,SQL SELECT ename, deptno, sal*12 2 FROM emp 3 WHERE job=,Enter value for job_title: ANALYST ENAME DEPTNO SAL*12 - - - SCOTT 20 36000 FORD 20 36000,在运行时指定列、表达式及文本,使用替代变量可以补充： WHERE条件 ORDER BY 子句 列表达式 表名 完整的 SELECT 语句,替代变量几乎可以使用在SQL语句的任何地方。,在运行时指定列、表达式及文本,SQL SELECT empno, ename, job, ,Enter value for column_name: sal Enter value for condition: sal=3000 Enter value for order_column: ename EMPNO ENAME JOB SAL - - - - 7902 FORD ANALYST 3000 7839 KING PRESIDENT 5000 7788 SCOTT ANALYST 3000,使用 &,如果您想再次使用同一变量而不再重新输入，则可以使用&,SQL SELECT empno, enam