首页 > 数据库 >Oracle与Mysql 的区别（对比学习）

Oracle与Mysql 的区别（对比学习）

时间：2023-03-31 23:58:45浏览次数：43  
标签：03 -- 2023 28 mysql Mysql Oracle 对比 select

Oracle与Mysql 的区别（对比学习）

使用范围

  • Oracle是大型的数据库。Oracle是收费的，且价格昂贵
  • Mysql是中小型数据库。Mysql是开源的

安装部署

  • Oracle有3G左右，且使用的时候Oracle占用特别大的内存空间和其他机器性能
  • Mysql安装完后才152M

端口用户

  • Oracle默认端口1521，默认用户是system
  • Mysql默认端口3306，默认用户是root

登陆方式

  • Oracle登录：sqlplus user/password@ip:port/instance_name
  • Mysql登录：mysql -hip -u用户 -p密码 -P端口号 -D数据库名

并发性

  • Oracle使用行级锁，对资源锁定的粒度要小很多，只是锁定sql需要的资源，并且加锁是在数据库的数据行上，不依赖于索引，所以Oracle对并发性的支持要好很多
  • Mysql是以表级锁为主，对资源锁定的粒度较大，如果一个会话对表加锁时间过长，会让其他会话无法更新此表的数据。虽然innodb引擎表可以使用行级锁，但这个行级锁机制依赖于表的索引，如果表没有索引，或者说sql语句没有使用到索引，那么仍然使用表级锁

持久性

  • Oracle有在线联机日志，可以恢复因为意外宕机导致的数据丢失
  • Mysql如果意外宕机，假设数据还没来得及从缓存写入到磁盘，则会出现数据丢失

主从复制

  • Oracle支持多机容灾，在主库出现问题的时候，可以自动切换为主库
  • Mysql的主从复制比较简单，主库出现问题的时候，需要重新去手动配置哪个是主库

事务隔离级别

  • Oracle默认的是 read commited（读已提交）的隔离级别，会有不可重复读+幻读的问题
  • Mysql默认是 repeatable read（可重复度）的隔离级别，只会有幻读的问题

权限

  • Oracle的权限，有用户组的概念
  • Mysql的权限，没有用户组的概念

数据库结构层次

  • Oracle的表，是创建在各个用户下面的

  • mysql的表，是创建在各个数据库下面的

字段类型

常用的字段类型不一样

拓展说明：

oracle的varchar，一般不使用，那个是sql标准的类型，允许空串，oracle自己开发了个类型叫varchar2，不允许存空串，而且可以兼容以后的oracle版本

oracle虽然有float、binary_float、binary_double，但是和number比起来，有区别：

  1. 首先是这3种类型，都不允许指定scale，也就是小数的位数，我尝试了一下这几个字段，scale修改为2，navicat中一点击保存，还是变回了0；
  2. 其次binary_float、binary_double，这2个，存储空间更小（number最多需要22个字节存储，而他们，分别需要5个、9个字节来存储）
  • oracle常用的字段类型：数字常用Number、字符常用varchar(2)
  • mysql常用的字段类型：数字常用int、double、字符常用varchar

时间的字段类型不一样

  • oracle的时间类型，是date，或者timestamp，包含年月日时分秒
  • mysql的时间类型，不但有datetime、timestamp，还有仅仅包含年月日的date，仅仅包含时分秒的time

查询语句

  • oracle的查询必须带from示例：

    select current_date from dual;
-- 结果：2023-03-28 16:42:41

  • mysql的查询，没强制要求带from关键字示例：

    select now();
-- 结果：2023-03-28 16:43:22

id递增的处理

  • oracle需要通过手动新建1个序列+触发器来实现;

    -- 第一步：创建序列（ps: 也可以通过navicat创建）
create sequence SEQ_T_LOCALOBTMIND
-- 验证序列是否能成功取到值
select SEQ_T_LOCALOBTMIND.nextval from dual

-- 第二步：设置触发器（ps: 请确保表名、字段都是大写的，否则触发器执行会失败）
create or replace trigger T_LOCALOBTMIND_INSERT_ID
before insert on T_LOCALOBTMIND for each row
begin
	select SEQ_T_LOCALOBTMIND.nextval into:NEW.ID from dual;
end;

-- 第三步：测试
INSERT INTO T_LOCALOBTMIND(DDATETIME, OBTID, WDIDF) VALUES (TO_DATE('2021-06-03 08:22:04', 'SYYYY-MM-DD HH24:MI:SS'), 'G1121', '11.34');

  • mysql则自带自动自增的功能

分页

  • oracle需要用rownum 和 子查询来做;

    -- 查第10-20条记录。10、20个数字表示记录的行号
SELECT *
FROM (SELECT ROWNUM AS rowno,r.*
           FROM(SELECT * FROM DONORINFO t
                    WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')
                    AND TO_DATE ('20060731', 'yyyymmdd')
                    ORDER BY t.BIRTHDAY desc
                   ) r
           where ROWNUM <= page*size 
          ) table_alias
WHERE table_alias.rowno > (page-1)*size;

  • mysql直接通过limit就行

    -- 查第10-20条记录。第一个10表示起始下标，第二个10表示取多少条记录
SELECT * FROM `tb_user`
limit 10, 10

时间处理

时间相减

  • oracle对于计算前7天，时间减7就行了，计算前1个小时，时间减1/24就行了，加法同理：

    select TO_DATE('2023-03-28 15:51:20', 'yyyy-mm-dd hh24:mi:ss') -7 from dual;
-- 计算前7天，结果：2023-03-21 15:51:20

select TO_DATE('2023-03-28 15:51:20', 'yyyy-mm-dd hh24:mi:ss') -1/24 from dual;
-- 计算前1个小时，结果：2023-03-28 14:51:20

  • mysql计算前7天，或者前1个小时，需要用不同的关键字：

    select date_sub('2023-03-28 15:51:20' ,interval 7 day);
-- 计算前7天，结果：2023-03-21 15:51:20

select date_sub('2023-03-28 15:51:20' ,interval 1 hour);
-- 计算前1个小时，结果：2023-03-28 14:51:20

timestamp类型的时间相减

  • oracle中timestamp类型的字段，相减，结果是多少天、多少小时，多少分钟，多少秒

    SELECT MIN_T, MAX_T, MAX_T - MIN_T FROM "T_TIMESTAMP";
-- 结果：2023-03-28 15:51:20.000000	2023-03-28 15:51:22.000000	+000000001 00:00:02.000000

  • mysql中，2个timestamp类型的值相减，如果想要知道相差多少天、多少秒，需要借助函数

    SELECT min_t, max_t, TIMESTAMPDIFF(second,min_t,max_t) FROM `t_timestamp`;
-- 结果：
2023-03-28 15:01:22	2023-03-28 15:01:22	86400
2023-03-28 15:02:01	2023-03-28 15:02:06	5
2023-03-28 15:03:58	2023-03-28 15:04:01	3

字符串转时间

  • oracle使用to_date()函数，示例

    select TO_DATE('2023-03-28 15:51:20', 'yyyy-mm-dd hh24:mi:ss') from dual;
-- 24小时制的转换，结果：2023-03-28 15:51:20

select TO_DATE('2023-03-28 pm 11:51:20', 'yyyy-mm-dd am hh12:mi:ss') from dual;
-- 12小时制的转换，结果：2023-03-28 23:51:20

  • mysql的字符串转时间，使用str_to_date()函数，示例：

    select STR_TO_DATE('2023-03-28 15:51:20','%Y-%m-%d %H:%i:%s');
-- 结果：22023-03-28 15:51:20
-- tip: mysql的这个格式，也不难记，Y、m、d、H、i、s，只要记住有2个字母是大写的就好了

时间转字符串

  • oracle使用to_char()函数，这是一个功能比较强大的函数，能格式化时间、数字

    select to_char(current_date, 'yyyy-mm-dd hh24:mi:ss') from dual;
-- 24小时制的转换，结果：2023-03-28 16:31:10

select to_char( TO_DATE('2023-03-28 15:51:20', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd am hh12:mi:ss') from dual;
-- 12小时制的转换，结果：2023-03-28 pm 03:51:20

  • mysql使用date_format()函数

    select DATE_FORMAT(current_timestamp,'%Y-%m-%d %H:%i:%s');
-- 结果：2023-03-28 16:31:59

查询当前的日期+时间

  • oracle示例：

    select CURRENT_TIMESTAMP from dual;
-- 结果：2023-03-28 16:32:21.510843 +08:00

select current_date from dual;
-- 结果：2023-03-28 16:32:35

select SYSDATE from dual;
-- 结果：2023-03-28 08:32:45

  • mysql的话，也有current_timestamp这个查询，但是格式很友好，示例：

    select CURRENT_TIMESTAMP;
-- 结果：2023-03-28 16:32:57

select now();
-- 结果：2023-03-28 16:33:07

select sysdate();
-- 结果：2023-03-28 16:33:23

字符处理

引号的用法

  • oracle的字符串只能用单引号包裹

    select concat('%', 'G1120') from dual;
-- 结果：%G1120

  • mysql的字符串可以用单引号，也可以用双引号包裹

    select concat("%", 'G1120');
-- 结果：%G1120

空字符串的情况

  • oracle中不允许有空字符串的存在，如果update某个字段为’ ’，则会发现这个字段值变成null了
  • mysql中，允许空字符串

大小写区分

  • oracle创建表默认大写，可以设置为小写

    -- 如果表名用引号包裹，则区分大小写
CREATE TABLE "t_localobtmind2" (
	"ID" NUMBER,
	"DDATETIME" DATE,
	"OBTID" VARCHAR2 ( 20 ),
	"WDIDF" NUMBER ( 10, 2 )
);
-- 结果：生成了一张 t_localobtmind2 的表

-- 默认生成的表名是大写的
CREATE TABLE t_localobtmind3 (
"ID" NUMBER,
"DDATETIME" DATE,
"OBTID" VARCHAR2 ( 20 ),
"WDIDF" NUMBER ( 10, 2 )
);
-- 结果：生成了一张T_LOCALOBTMIND3的表

  • mysql创建表默认小写

    -- 默认是小写的，无论你是否用引号包裹，生成的表名都是小写的
CREATE TABLE T_TYPE3 (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_date` datetime DEFAULT NULL,
  `c_time` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- 结果：生成了一张 t_type3 的表

CREATE TABLE `T_TYPE4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_date` datetime DEFAULT NULL,
  `c_time` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- 结果：生成了一张t_type4的表

多条件判断

tip: oracle也有if的用法只适用于存储过程存储函数，不适用于单条sql执行

  • oracle，惯用decode()函数，另外支持case when的用法：

    -- decode()函数
select DECODE(2, 
1, '风速',
2, '雨量',
3, '浪高',
null)
from dual;
-- 结果：
雨量

-- case when的写法1：公式匹配

select sal, CASE 
	when sal >= 5000 then '高薪'
	when sal >= 2000 then '中薪'
	WHEN sal > 800 THEN '低薪'
	ELSE '其它'
END as cn
from emp;
-- 结果：800	其它
1600	低薪
1250	低薪
2975	中薪
...

-- case when的写法2：值匹配
select sal, CASE sal
	when 5000 then '高薪'
	when 1600 then '中薪'
	WHEN 800 THEN '低薪'
	ELSE '其它'
END as cn
from emp;
-- 结果：
800	低薪
1600	中薪
1250	其它
2975	其它
...

  • mysql的条件判断，只有case when这一种比较合适的选择

    -- mysql也有decode()函数，不过是用来加密字符串的
select @savePwd:= ENCODE('admin123','diy_key');
select DECODE(@savePwd,'diy_key');
-- 结果：(BLOB) 8 bytes
-- 如果在navicat里面，选择查看-文本，能看到是 admin123

-- case when的写法1：公式匹配：
set @var = 2001;
select CASE
	WHEN @var >= 5000 THEN '高薪'
	when @var >= 2000 then '中薪'
	when @var >= 800 then '低薪'
	ELSE '其它'
END as cn
-- 结果：中薪

-- case when的写法2：值匹配
-- select @var := 2;
set @var = 2;
select CASE @var
	WHEN 1 THEN
		'一'
	when 2 then
		'二'
	ELSE
		'其它'
END as cn
-- 结果：二

-- mysql的简单条件判断
set @factory=1;
select IF(@factory=1,'风速','其它要素')
-- 结果：
风速

行号的显示

  • oracle可以使用rownum来显示

    select rownum, username from all_users;
-- 结果：
1	SCOTT
2	OWBSYS_AUDIT
3	OWBSYS
4	APEX_030200
5	APEX_PUBLIC_USER
6	FLOWS_FILES
7	MGMT_VIEW
8	SYSMAN
9	SPATIAL_CSW_ADMIN_USR
10	SPATIAL_WFS_ADMIN_USR
11	MDDATA
12	MDSYS
13	SI_INFORMTN_SCHEMA
14	ORDPLUGINS
15	ORDDATA
16	ORDSYS
17	OLAPSYS
18	ANONYMOUS
19	XDB
20	CTXSYS
21	EXFSYS
22	XS$NULL
23	WMSYS
24	APPQOSSYS
25	DBSNMP
26	ORACLE_OCM
27	DIP
28	OUTLN
29	SYSTEM
30	SYS

  • mysql可以使用变量来实现

      SELECT 
      @rowno:=@rowno+1 as rowno,
      t_data.USER_NO
  from 
      mt_user t_data ,
      (select @rowno:=0) t_init;
  -- 结果：
1	8279310508960
2	8553248608551
3	8355351146508
4	8325940576515
5	8304452773553
6	8876948349113
7	8750848063319
8	8583907468573
9	8671262981999
10	8298506557219

小数格式化

  • oracle使用to_char()函数：

    select to_char(211.125456,'99999999999990.99') from dual;
-- 需要四舍五入的情况，结果：211.13
select to_char(211.1,'99999999999990.99') from dual;
-- 小数位不够2位的情况，结果：211.10
select to_char(0,'99999999999990.99') from dual;
-- 特殊值0的情况，结果：0.00

针对数字的常用格式化符

参数 示例 说明
9 999 存在数字显示数字，不存在显示空格
0 000 存在数字显示数字，不存在显示0
. 99.99 指定位置返回小数点
， 999,999,999 指定位置返回逗号
FM FM999,999,99.00 如果是因为9带来的空格则删除
$ FM$999,999,99.00 数字开头返回一个美元符号

TO_CAHR格式化数字小数点前面只有0，格式化之后0会丢失 .5to_char(a,'FM990.00') 0这个占位符就可以

  • mysql使用format()函数：

    select FORMAT(211.125,2);
-- 需要四舍五入的情况，结果：211.13
select FORMAT(211.1,2);
-- 小数位不够2位的情况，结果：211.10
select FORMAT(0,2);
-- 特殊值0的情况，结果：0.00

行转列的实现

  • oracle行转列，需要借助connect by语法和regexp_substr()函数

    select REGEXP_SUBSTR(codes, '[^,]+', 1, rownum) from(
      select 'G1120,G1121,G1122,G1123' as codes from dual
  ) t_base
connect by rownum <= LENGTH(codes) - LENGTH(REPLACE(codes, ',', '')) + 1;
  -- 结果：
  G1120
  G1121
  G1122
  G1123
  
    -- 分析：regexp_substr() 这个函数，可以通过正则去截取某个字符串里面的内容。第一个参数是源字符串，第二个参数是正则表达式，也就是截取规则，第三个参数表示从第几个字符开始匹配正则表达式，第四个参数表示第几个匹配内容
  -- 结果：
  G1120
  G1121
  G1122
  G1123

  -- 字符串正则分割
SELECT 
  REGEXP_SUBSTR ('G1120,G1121,G1122,G1123', '[^,]+', 1,2) code
from dual; 
  -- 结果：
  G1121
  -- 返回第2次匹配到的内容

  -- 生成数字序列结果集
  select rownum from dual
  connect by rownum <= 3;
  -- 结果：
  1
  2
  3

  • mysql的行转列，需要借助 join中on<的特殊用法和substring_index()函数

    select SUBSTRING_INDEX(subStr,',', -1) code from(
    select 
    codes, help_topic_id, SUBSTRING_INDEX(codes,',',help_topic_id + 1) subStr
    from
    (
        select 'G1120,G1121,G1122,G1123' as codes
    ) t_base
    inner join mysql.help_topic t_help
    on t_help.help_topic_id <= LENGTH(t_base.codes) - LENGTH(REPLACE(t_base.codes,',',''))
) t_with_id;


-- help_topic本身是Mysql一个帮助解释注释表，用于解释Mysql各种专有名词，由于这张表数据ID是从0顺序增加的，方便我们用于计数，但是8.0.17版本的只有686条数据，超过这个数字，我们就需要己自定义一张表。
-- 思路：
-- 通过mysql的一张行转列的辅助表来做
-- LENGTH(REPLACE(t_code.codes,',','')表示，除去逗号，还有多少个字符
-- LENGTH(t_code.codes) - LENGTH(REPLACE(t_code.codes,',','')) 表示有多少个逗号
-- 第一个SUBSTRING_INDEX()函数，得到的内容，分别是：
G1120
G1120,G1121
G1120,G1121,G1122,
G1120,G1121,G1122,G1123
-- 第二个SUBSTRING_INDEX()函数，得到的内容，分别是：
G1120
G1121
G1122
G1123

null值处理

  • oracle使用ISNULL或Nvl函数

    ISNULL ( check_expression , replacement_value )
-- replacement_value 必须与 check_expresssion 具有相同的类型。

NVL(eExpression1, eExpression2)
-- 如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2  
-- 返回值类型：字符型、日期型、日期时间型、数值型、货币型、逻辑型或 null 值

COALESCE (expression_1, expression_2, ...,expression_n)
-- 遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值  
-- 所有表达式必须是相同类型
-- 空值加任何值都是空值，空值 乘任何值也都是空值
SELECT COALESCE(NULL,NULL,3,4,5) FROM dual;

  • mysql使用date_format()函数

    IFNULL(expression, alt_value)
-- 如果第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值

去重函数distinct

  • oracle多列去重是根据写在distinct后面的各个字段来去重的（当这几个字段所有的列的值都相同时才会被认为是重复数据
  • mysql 同上

标签：03,--,2023,28,mysql,Mysql,Oracle,对比,select
From： https://www.cnblogs.com/fuqian/p/17277882.html

相关文章

  • lag与lead函数 oracle_11g
    lag与lead函数oracle_11glag与lead函数都可以实现跨行引用，语法如下：lag(col[,n1][,n2])over([分区子句]排序子句)lead(col[,n1][,n2])over([分区子句]排序子句)语法注解：参数： col,n1,n2 lag可以返回按排序子句排序后指定列的前n1行的值(如果不指定n1，则默......
  • m基于强化学习的PID控制器simulink仿真,对比PI控制器和变结构PI控制器
    1.算法描述       PID控制器，即控制器的控制方式为P比例调整，I积分调整以及D微分调整三个部分构成，PID控制器是目前为止应用最为广泛的控制方式。PID控制器具有结构简单，性能稳定，参数设置简单等优势。PID控制器适用于各种控制对象无法进行测量获得系统参数的情况，其根据控制对......
  • ORACLE数据库基础知识
    ORACLE数据库基础知识Oracle简介Oracle数据库系统是美国Oracle公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器（client/server）或B/S体系结构的数据库之一。Oracle数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具......
  • 窗口函数 oracle_11g
    窗口函数oracle_11g数据库中的窗口函数也叫分析函数，顾名思义，窗口函数可用于一些复杂的统计分析计算，另外，窗口函数还具有优越的性能表现，可以节约时间和资源，因此窗口函数经常用于数据仓库和大型报表应用中。窗口函数的结构窗口函数由四部分组成，分别是分析函数名、分区子句、排......
  • Lixnu：如何安装、启用、访问云服务器中的MySQL数据库？
    Linux系统：CentOS7MySQL版本：5.7.41安装方法：rpm安装（需要自己解决依赖问题）云服务器：阿里云MySQL客户端：Navicat  1、准备工作：软件配置本机是否有mysql软件：rpm-qa|grepmysql是否有冲突软件mariadb：rpm-qa|grepmariadb（如果有）卸载......
  • oracle静默安装
    ##################       如何确定安装的oracle是否有问题？              #####################......
  • 开心档之MySQL WHERE 子句
    MySQLWHERE子句我们知道从MySQL表中使用SQLSELECT语句来读取数据。如需有条件地从表中选取数据，可将WHERE子句添加到SELECT语句中。语法以下是SQLSELECT语句使用WHERE子句从数据表中读取数据的通用语法：SELECTfield1,field2,...fieldNFROMtable_name1,ta......
  • 开心档之MySQL 序列使用
    MySQL序列使用MySQL序列是一组整数：1,2,3,...，由于一张数据表只能有一个字段自增主键，如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。本章我们将介绍如何使用MySQL的序列。使用AUTO_INCREMENTMySQL中最简单使用序列的方法就是使用MySQLAUTO_INCREMEN......
  • 使用EFCore的Code First和MySql数据库迁移（转载 ）
    下辈子还当程序员 博客园首页新随笔联系订阅管理随笔-4  文章-0  评论-9  阅读- 44432使用EFCore的CodeFirst和MySql数据库迁移 1.感慨一下    随着.netcore的持续更新和升级，至少对于从事.net开发的人员和即将踏入这个领域......
  • mysql——null值和空值
    参考：https://blog.csdn.net/xp178171640/article/details/103065271?spm=1001.2101.3001.6650.2&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-2-103065271-blog-107128984.235%5Ev27%5Epc_relevant_3mothn_strategy_and_data_recovery......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99