第1页:第二十二讲 Oracle数据库
第二十二讲ORACLE数据库
第2页:本讲内容概览
本讲内容
- 介绍在ORACLE提供的工具SQL*PLUS中应用SQL语句的方法和示例。
第3页:主要内容
主要内容
- ORACLE中常用的DDL和DML是什么以及使用它们的实例。
- 怎样注册到SQL*Plus。
- 最常见的参数设置。
- 怎样从数据库中检索数据。
- 怎样使用SQL*Plus格式化数据。
- 怎样更新和删除数据库中的数据。
- 怎样创建表和将数据插入表中。
- 怎样变更表
第4页:常用DDL列表
常用 DDL 列表:
Oracle 常用 DDL 语句| SQL命令 | 功能 |
|---|
| Alter procedure | 重编译存储过程 |
|---|
| Alter table | 增加表列、重定义表列、更改存储分配 |
|---|
| analyze | 收集数据库对象的性能统计值并送入基于代价的优化器 |
|---|
| Alter table add constraint | 在已有的表上增加约束 |
|---|
| Create table | 创建表 |
|---|
| Create index | 创建索引 |
|---|
| Drop index | 删除索引 |
|---|
| Drop table | 删除表 |
|---|
| grant | 将权限或角色授予用户或其他角色 |
|---|
| truncate | 删除表中所有行 |
|---|
| revoke | 从用户或数据库角色回收权限 |
|---|
第5页:常用DML列表
常用 DML 列表:
Oracle 常用 DML 语句| SQL命令 | 功能 |
|---|
| insert | 增加数据行到表 |
|---|
| delete | 从表中删除数据行 |
|---|
| update | 更改表中数据 |
|---|
| select | 从表或视图中检索数据行 |
|---|
| Commit work | 把当前事务所做的更改永久化(写入磁盘) |
|---|
| rollback | 作废上次提交以来的所有更改 |
|---|
第6页:SQL*Plus入门
SQL*Plus入门
- 1)从[开始]菜单,单击programs|Oracle-Ora81|ApplicationDevelopment|SQLPlus。在connect对话框中按提示输入用户名和口令。
- 注意因为用Ora81作为ORACLE_HOME,所以开始菜单的选项为Oracle_Ora81;如果用户设置了不同的ORACLE_HOME,开始菜单的选项就有可能不同。
- 2)从DOS窗进入:
- 输入命令sqlplus polly/gone,Oracle8i将打开一个单独的DOS窗口。
第7页:登录SQL*Plus
登录SQL*Plus
- 如果在登录到SQL*Plus时只提供用户名,即使用命令sqlplus polly,那么将被提示输入口令。
- 如果在登录到SQL*Plus时什么也不提供,即使用命令sqlplus,那么将被提示输入用户名和口令。
- 在进入SQL*Plus后,就会看到SQL*Plus的提示符SQL>。
第8页:创建表语句
创建语句
- 由于创建数据库对象的工作是由DDL语句来完成的,所以建立数据库应用总是从DDL语句开始的。首先,创建四个表:Customer、state、X和Y。
第9页:输入语句的注意事项
示例注意在上面的清单中,SQL>提示符以下的数字无需用户输入。当用户按回车键进入下一行时,SQL*Plus会自动加入这些数字。需用一对圆括号括起表中各个列的定义。
第10页:Oracle数据类型
ORACLE 的数据类型:
Oracle 常用数据类型| 数据类型 | 说明 |
|---|
| char(size) | 存放定长字符数据,最长2000个字符 |
|---|
| nchar(size) | 与 char 型类似,最大长度由数据库所用的字符集决定 |
|---|
| varchar2(size) | 存放可变长字符数据,最大长度为4000字符 |
|---|
| nvarchar(size) | 类似于 varchar2 |
|---|
| Varchar | 目前等同于 char |
|---|
| number(l,d) | 存放数值型数据,l 代表总位数,d 代表小数点后位数 |
|---|
| Blob | 二进制大对象 |
|---|
第11页:数值类型定义提示
提示
- 用长度和小数点后的位数定义数值型表列时,长度定义十进制数字个数,其中包括整数部分和小数部分数字的位数。例如:number(4,2),定义的最大数值为99.99。
- 切记,Oracle数据库是由表的集合所组成的,而各表中的表列或字段定义了该表。这些表列或字段都有一个属性,属性表明可保存何种数据。数据库系统根据数据类型决定对其内容的操作,这些操作都有相应的函数,如“处理数字型数据的函数”、“处理字符型数据的函数”以及“处理日期型数据的函数”等等。例如:Oracle可对数值型数据进行加、减乘、除操作。后面将顺序介绍。
第12页:空值与非空值
空值与非空值
- 空值(Null value)是指不含数据的表列。可以将nullvalue理解为长度为0的字符串。很多时候若不知道某表列的数据类型或数据值则可以给它赋一空值。但人们最容易犯的一个错误就是将空值加载到一个数值型表列中,由于“1+null=null”!因此,如果用户偶然将空值加载至数值型域中,那么产生的统计报表肯定不正确。
- 提示:不要用null表示数值型域中的0,如果要对数值表列进行算术运算,那么该空值一定要用0来代替。
第13页:describe描述语句
描述语句
- Oracle对SQL最重要的改进之一就是增加了描述(describe)命令。使用describe命令,用户可以快速掌握表及其中所有表列的概要。在下一章中,将介绍存储的PL/SQL包和存储过程。
- describe命令可以列出存储的PL/SQL包中所有的过程和这些过程所使用的输入/输出参数。如命令describecustomer得出下列有关客户表输出:
- NameNull?Type
- ---------------------
- Last_namenotnullVARCHAR2(50)
- State_cdnotnullCHAR(2)
- SalesNUMBER
第14页:插入语句
插入语句:向 customer 表中插入两行数据。
Insert into customer values('Teplow','MA',23445.67); insert into customer values('Abbev','GA',6969.96);
每条 insert 语句执行后,Oracle 以“1 row created.”响应。
第15页:SQL函数
函数
- 函数在SQL语句中用于处理表列内容。在SQL语句中使用函数时,被函数作用的表列的值将发生改变,并将在结果中返回改变后的值。显示数值表列的绝对值是使用函数的一个很好的例子,值为-321的表列的绝对值为321。在SQL*Plus中,求绝对值的表达式由关键字“abs”后跟一对括号以及括号中的表列名称三部分组成,如abs(ytd_sales)。因此SQL语句
- select abs(ytd_sales) from customer;
- 将显示值321,而不论ytd_sales表列中的数据为-321或是+321。
第16页:数值函数
数值函数:函数在 SQL 语句中用于处理表列内容。
Oracle 常用数值函数| 函数 | 返回值 | 样例 |
|---|
| ceil(n) | 大于等于数值 n 的最小整数 | select ceil(10.6) from dual; → 11 |
|---|
| floor(n) | 小于等于数值 n 的最大整数 | select floor(10.6) from dual; → 10 |
|---|
| mod(m,n) | m 除以 n 的余数,若 n=0 则返回 m | select mod(7,5) from dual; → 2 |
|---|
| power(m,n) | m 的 n 次方 | select power(3,2) from dual; → 9 |
|---|
| round(n,m) | 将 n 四舍五入,保留小数点后 m 位 | select round(…) from dual; |
|---|
原课件 round 函数的样例语句在页面上显示不完整,此处仅保留函数说明。
第17页:字符函数
字符函数:
Oracle 常用字符函数| 函数 | 返回值 | 样例显示 |
|---|
| initcap(char) | 把每个字符串的第一个字符换成大写 | Select initcap('mr.teplow') from dual; → Mr.Teplow |
|---|
| lower(char) | 整个字符串换成小写 | Select lower('Mr. TterryBeaker ') from dual; → mr.terrybeaker |
|---|
| replace(char,str1,str2) | 字符串中所有 str1 换成 str2 | select replace('Scott','S','Boy') from dual; → Boycott |
|---|
第18页:并置运算符
并置运算符
- 在联结两个字符域时,它非常有用。虽然我们将其列为函数,但它实际上是个运算符。用两竖线“||”表示并置运算符。执行语句
- select 'ABC'||'DEF' from dual;
- 返回文本ABCDEF。若某行的last_name值为John,执行语句
- select 'Dear'||last_name||':' from customer;
- 将返回文本“DearJohn:”
第19页:日期型函数
日期型函数:Oracle 中日期型数据同时含有日期和时间,缺省格式为 DD-MON-YY。
Oracle 常用日期函数| 函数 | 返回值 | 样例 |
|---|
| Sysdate | 当前日期和时间 | Select sysdate from dual; → 28-FEB-02 |
|---|
| Last_day | 本月最后一天 | Select last_day(sysdate) from dual; |
|---|
课件示例输出 28-FEB-02 即 2002 年 2 月 28 日;原页 last_day 的返回值显示不完整,此处未补全。
第20页:特殊格式的日期型数据
特殊格式的日期型数据
第21页:日期格式符提示
续
- 提示:注意不要将MM格式用于分钟(分钟应该使用MI )。MM是用于月份的格式,将它用于分钟也能工作,但结果是错误的。
第22页:日期运算
日期运算
- 当用户开始使用日期型数据时,也许会提出这样的问题:“在多少天前刚加过油?”,日期型运算使用户能得到这个答案。当给日期表列数据加2时,Oracle知道用户的意思是增加两天。假定表列sale_date的值为03-MAR-98,在此情况下,SQL语句select sale_date+10from customer;将返回13-MAR-02。下面还要介绍两个更具典型意义的例子。
- 提示:日期运算在SQL*Plus中是最容易出错的,因此Oracle制定了严格的日期格式规则,以确定允许执行何种运算。Oracle特别注意月和年的界限问题。如语句select to_char(sysdate+14) from dual;将在原值23-DEC-2002上加14天,得出06-JAN-03。
第23页:数据类型转换函数
表列数据类型转换
- 用户常常会用遇到需要将数据表列从一种类型转换为另一种类型的情况(如数值型转换成
- 日期型,字符型转换成数值型)。Oracle提供三种主要的转换函数。
- To_char将任意类型的数据转换成字符类型。语句select to_char(8897) from dual;返回一个包含字符串8897的字符类型的数据。
- To_number将一组合法的数字字符串(如字符数据8897)转换成数值。语句selectto_number('8897') from dual;返回一个包括数值8897的数值类型的数据。
第24页:to_date转换注意事项
续
- To_date将适合格式的字符串数据转换成日期型数据,这是最容易产生错误的转换。因为12-DEC-02是合法的日期格式,语句select to_date('12-DEC-02') fromdual;能成功地进行数据转换。而语句selectto_date('bad date') from dual;在执行时会出问题。语句select to_date('20021227','YYYYMMDD') fromdual;将返回日期27-DEC-02,因为在to_date函数中的日期格式和被转化的数据是合法的。语句selectto_date ('20021236','YYYYMMDD') from dual;将失败,因为在12月中没有36天。
- 提示:用to_date转换机制时,如果被转换的数据格式不对,会出现各种各样的Oracle错误。
第25页:第二十二讲小结
小结
- 介绍了在SQL*Plus中使用SQL的一些示例,其中涉及了有关ORACLE特有的函数。