ORACLE/MYSQL数据库比较应用教学法综述

2017-12-13 19:41俞海
电脑知识与技术 2017年33期
关键词:比较法数据库

摘要:当前,数据库教学要紧跟时代发展需要,要选择市场使用比较广泛的二种主流数据库厂商,进行数据库的各内容或类型比较、函数比较、SQL 语法的使用差别,创建使用存储过程的差别等。通过比较法进行教学讲解,不仅使学生在比较学习法中进行体会,同时在相应的实验环境中进行试验,这样就能做到实践,心中有数。该文重点介绍数据库教学中的比较用法,通过比较,对数据库的基本概念和SQL操作,有更深的理解和印象,对不同的数据库(oracle和mysql)SQL的基本操作语法以及存储过程的创建中进行比较,对数据库教学中融入比较概念,面向应用和开发的角度,对数据库教学及应用开发的有较好的引导作用。

关键词:数据库; 比较法;SQl;Stored Procedure;Oracle;Mysql

中图分类号:TP393 文献标识码:A 文章编号:1009-3044(2017)33-0001-03

当前,高校数据库应用课程的教学要紧跟时代发展需要,可选择oracle,mysql或SQLSERVER数据库进行教学,要选择市场使用比较广泛的两种数据库厂商进行教学类型的比较,本课程在进行数据库的教学类型比较,函数比较,SQL 语法的使用差别,创建存储过程的差别等。通过比较法进行教学的讲解,不仅使学生在比较学习法中进行体会,同时在相应的实验环境中进行试验,这样就能做到实践,心中有数,本文给出一些具体的比较及案例,并介绍主流的ORALCE 的数据库和目前互联网应用广泛的Mysql数据库,对加深主流数据库的基本功能及应用开发有较好的参考或帮助作用。

Oracle 数据库是一款面向关键(Critical business)行业的主流数据库软件产品。而 Mysql数据库则是目前世界上使用最为广泛的数据库管理系统,目前Mysql数据库已被Oracle数据库收购,二者作为一个通用的数据库系统,它们都具有完整的数据管理功能的关系型数据库,支持通用的SQL 语言和语法。

SQL语言不仅简单易学,而且灵活自如,可在sql * plus 下或mysql终端下使用,也可在c/s(客户/服务器) 进行编程语言使用,可嵌入在主流的C/C++,或Dephi,Powerbuilder等窗口设计较多的场合(scenarios),也可嵌入B/s(浏览器/服务器)模式开发的PHP,Python,C#,Java等互联网应用编程等主流语言中。在这些应用场合中通过ODBC,JDBC 等方法连接后台的ORACLE数据库或Mysql数据库,进行数据的更新(update)修改,查询(select),新增(insert)删除(delete)操作,以及数据的定义,如创建表(create table)等数据库对象的创建,从而实现数据查询(Data Query)、數据操纵(Data Manipulation)、数据定义(Data Definition)和数据控制(Data Control)功能。

所以当今高校数据库选用主流的Oracle、MYSQL这些大中型的数据库管理系统,同时选择一门编程语言,如像Dephi、PowerBuilder、C/C++、PHP、Python,C#等常用的开发语言,利用SQL语言的灵活性来处理面向对象的过程,如数据记录的逐行修改或利用SQL语言的面向集合的功能,从数据库获取查询,统计功能的工具,作为数据库查询接口返回查询结果进行数据的高效处理的语言,通过这样可进一步加深对SQL的灵活使用和数据库应用的开发,具有非常重要的应用和实践作用。

SQL是高级的非过程化编程语言,允许用户在高层数据结构上工作。它不要求用户指定对数据的存储方法,也不需要用户了解具体的数据存储方式,所以对具有完全不同底层结构的不同数据库系统,可以使用相同的SQL语言作为数据输入与管理的接口。它以记录集合作为操作对象,所有SQL语句接受集合作为输入,返回集合作为输出,这种集合特性允许一条SQL语句的输出作为另一条SQL语句的输入,所以SQL语句可以嵌套,这使他具有极大的灵活性和强大的功能,在多数情况下,在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的,这也意味着用SQL语言可以写出非常复杂的语句。

实现SQL在各场景(scenarios)下的应用,灵活利用SQL的create、 Insert、 delete、update、alter、drop、grant、Restore, Recover 等功能,熟悉数据操纵(Data Manipulation)、数据定义(Data Definition)和数据控制(Data Control)功能,另外也要考虑到Oracle 数据库和Mysql数据库之间的一些微小差异,包括支持的数据类型、函数使用的比较以及存储过程创建的比较等内容,以及Oracle数据库和Mysql数据库在其他系统性能上的差异比较,使学生通过对比学习加深对Oracle数据库和Mysql数据库主流数据库的认识,通过数据库比较教学法,将有利于学生加深印象和理解。

1 ORACLE 和MYSQL数据类型差别

类型就是数据格式,就是编码,ORACLE数据库支持的数据类型众多,包括内建(built-in)的数据类型主要有:

CHAR ( 字符串类型),NCHAR(包含UNICODE格式数据的定长字符串)

NVARCHAR2 (包含UNICODE格式数据的变长字符串,最长可存储4000字节的信息)

VARCHAR2 (包含UNICODE格式数据的变长字符串,最长可存储4000字节的信息)

VARCHAR (同VARCHAR2类型)

NUMBER(p,s) (数字类型数据)

INTEGER (数字类型整数数据)

DATE (日期类型数据)endprint

TIMESTAMP(时间戳型数据)

RAW and LONG RAW Datatypes

LOB (large object)是一种用于存储大对象的数据类型,如医学记录(如X-射线)、视频、图像等。

LOB有三种类型:BLOB:、CLOB:、DBCLOB:Double-byte Character Large Object。每个LOB最多可以有4GB。

这样oracle 数据库就可以处理复杂的数据类型,内置的LOB数据类型包括BLOB、CLOB、NCLOB、BFILE(外部存储)的大型化和非结构化数据,如文本、图像、视屏、空间数据存储。

BLOB、CLOB、NCLOB类型

BFILE (二进制文件外部存储数据类型 ,存储在数据库外的系统文件,文件属性只读,数据库会将该文件当二进制文件处理)

BLOB (Binary Large Object,即二进制大对象数据,一般是图像、声音、视频等文件类型)

CLOB (Character Large Object,即字符大对象数据,如一段长的文本可定义该类型,它存储单字节和多字节字符数据。支持固定宽度和可变宽度的字符集)

NCLOB(存储UNICODE类型的CLOB数据,支持固定宽度和可变宽度的字符集)

UROWID 数据类型,在数据库中的每一行都有一个地址。然而,一些表行的地址不是物理或永久的,或者不是ORACLE数据库生成的。

例如,外部表的ROWID(如通过网关访问DB2表)不是??标准的ORACLE的Rowid。

LOB 代表大对象数据,包括 BLOB 和 CLOB 两种类型,前者用于存储大块的二进制数据,如图片数据,视频数据等,而后者用于存储长文本数据,如论坛的帖子内容,产品的详细描述等。值得注意的是:在不同的数据库中,大对象对应的字段类型是不尽相同的,如 DB2 对应 BLOB/CLOB,MySQL 对应 BLOB/LONGTEXT,SqlServer 对应 IMAGE/TEXT。需要指出的是,有些数据库的大对象类型可以像简单类型一样访问,如 mysql 的 LONGTEXT 的操作方式和 VARCHAR 类型一样。在一般情况下, LOB 类型数据的访问方式不同于其他简单类型的数据,我们经常会以流的方式操作 LOB 类型的数据。此外,LOB 类型数据的访问不是线程安全的,需要为其单独分配相应的数据库资源,并在操作完成后釋放资源。最后,Oracle 9i 非常有个性地采用非 JDBC 标准的 API 操作 LOB 数据。

而MYSQL 支持所有标准SQL中的数值类型,如CHAR,VARCHAR,BINARY,BLOB,TEXT,ENUM,DATE, TIME 等类型。

2 分组函数使用的一些差别

分组子语句(group by) 在统计分析或报表生成过程中,作用非常重要。但oracle和Mysql数据库中分组使用有些注意事项,如:

Mysql中组函数在select语句中可以随意使用,但在oracle中如果查询语句中有组函数,那其他列名必须是组函数指定过的,或者是group by子句中的列,否则报错

例如:

select name,count(money) from user;这个放在mysql中没有问题,而在oracle中就有问题了。

应为:selectname,count(money) from user group by name;

3 Oracle 和MYSQL 中的单引号‘和双引号“”的区别

MYSQL里可以用双引号包起字符串,ORACLE里只可以用单引号包起字符串,所以在ORACLE数据库在插入和修改字符串前必须做单引号的替换:把所有出现的一个单引号替换成两个单引号。

4 自动增长的数据类型处理

MYSQL 中包含了 auto_increment 类型的列,而ORACLE 中没有auto_increment这种类型的列, 相应的使用序列号来自动递增序列号,MYSQL有自动增长的数据类型,插入记录时不用操作此字段,会自动获得数据值。ORACLE没有自动增长的数据类型,需要建立一个自动增长的序列号,插入记录时要把序列号的下一个值赋于此字段。

CREATE SEQUENCE序列号的名称(最好是表名+序列号标记)INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;

其中最大的值按字段的长度来定,如果定义的自动增长的序列号NUMBER(6),最大值为999999。

INSERT语句插入这个字段值为:序列号的名称.NEXTVAL。

5 行rownum限制输出表中内容

MYSQL处理表中行输出的SQL语句比较简单,用LIMIT开始位置,记录个数;ORACLE处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置,并且只能用ROWNUM。

6 日期字段的处理

日期变量类型是所有应用型数据记录中通常考虑定义的类型,如数据的修改(删除或更新)时间,银行 存款贷款还款都涉及日期,超市流水的生成等也涉及日期字段的定义和处理。MYSQL日期字段分DATE和TIME两种,ORACLE日期字段只有DATE,包含年月日时分秒信息,用当前数据库的系统时间为SYSDATE,精确到秒,或者用字符串转换成日期型函数TO_DATE(‘2017-08-01,YYYY-MM-DD),年-月-日24小时:分钟:秒的格式为YYYY-MM-DD HH24:MI:SS, TO_DATE()还有很多种日期格式。日期型字段转换成字符串函数为TO_CHAR,如TO_CHAR (‘2001-08-01,YYYY-MM-DD HH24:MI:SS)endprint

日期字段的数学运算公式有很大的不同。MYSQL找到离当前时间7天可用DATE_FIELD_NAME > SUBDATE(NOW(),INTERVAL 7 DAY)表达式。

ORACLE找到离当前时间7天可用 DATE_FIELD_NAME >SYSDATE — 7表达式完成;

MYSQL中插入当前时间的几个函数是:NOW()函数以`'YYYY-MM-DD HH:MM:SS'返回当前的日期时间,可以直接存到DATETIME字段中。

另外CURDATE()以YYYY-MM-DD的格式返回今天的日期,可以直接存到DATE字段中。CURTIME()以HH:MM:SS的格式返回当前的时间,可以直接存到TIME字段中。例:insert into sale (id,name,price,id_time) values (‘2017091801,U disk,100,now())

而oracle中当前时间是sysdate。[1]

7 空字符的处理

MYSQL的非空字段也有空的内容,ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构,导数据的时候会产生错误。因此导数据时要对空字符进行判断,如果为NULL或空字符,需要把它改成一个空格的字符串。

8 字符串的模糊比较

MYSQL里用字段名like%‘字符串%,ORACLE里也可以用字段名like%‘字符串%但这种方法不能使用索引,速度不快,用字符串比较函数instr(字段名,‘字符串)>0会得到更精确的查找结果。

9 存储过程的创建和执行的比较

(1) 在创建存储过程时如果存在同名的存储过程,会删除老的存储过程.

oracle使用create or replace.

mysql使用先删除老的存储过程,然后再创建新的存储过程.

(2) oracle 存储过程可以定义在package中,也可以定义在Procedures中. 如果定义在包中,一个包中可以包含多个存储过程和方法.如果定义在Procedures中,存储过程中不可以定义多个存储过程.

Mysql 存储过程中不可以定义多个存储过程.

(3) oracle中字符串类型可以使用varchar2.

Mysql 需要使用varchar

(4) Oracle中参数varchar长度不是必须的, Mysql中参数varchar长度是必须的, 比如varchar(100)

(5) 在创建函数时如果存在同名的函数,会删除老的函数.

oracle使用create or replace.

mysql使用先删除老的函数,然后再创建新的函数.

(6) oracle 函数可以定义在package中,也可以定义在Functions中. 如果定义在包中,一个包中可以包含多个存储过程和函数.如果定义在Functions中,每个函数只能定义一个函数.Mysql Functions不可以定义多个函数.

(7) oracle返回值用return.

Mysql返回值用returns.

(8) 存储过程异常处理不一样

Oracle异常处理

EXCEPTION

WHEN OTHERS THEN

ROLLBACK ;

error_msg := c_sp_name||'('|| li_debug_pos ||'):'||

TO_CHAR(SQLCODE)||': '||SUBSTR(SQLERRM,1,100);

Msql异常处理

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK ;

seterror_msg=concat(c_sp_name,'(', li_debug_pos ,'):',

TO_CHAR(SQLCODE),': ',SUBSTR(SQLERRM,1,100));

END;

10 結论

通过比较二种最流行的数据库ORACLE及MYSQL的差异方面,来加深二者之间的差别,加深学生了解这两种数据库的异同,对于今后使用其中之一或进行二者之间的转换也有指导帮助作用,对于数据库的教学能和市场数据库应用开发密切联系起一定的作用。

参考文献:

[1] http://blog.csdn.net/io_field/article/details/52669811.

[2] 俞海 .数据库基本原理及应用开发教程[J].南京:南京大学出版社, 2017.endprint

猜你喜欢
比较法数据库
比较法:立法的视角
管窥“浮沉比较法”在脉诊中的应用
比较法在教学中的运用