本系列文章：
MySQL（一）SQL语法、数据类型、常用函数、事务
MySQL（二）MySQL SQL练习题
MySQL（三）视图、存储过程、索引
MySQL（四）存储引擎、锁
MySQL（五）MySQL架构、数据库优化、主从复制
MySQL（六）SQL语句优化
MySQL（七）MySQL和Oracle、PostgreSQL的区别
一、MySQL和Oracle
1.1 基本差别*
-
数据库类型
Oracle数据库是一个对象关系数据库管理系统（ORDBMS），MySQL是一个开源的关系数据库管理系统（RDBMS）。
对象关系数据库管理系统：基于对象模型，存储数据及其方法，数据存储在对象中。拥有对象类、对象标识、多态、封装和继承等特性。用于存储复杂的数据。
关系数据库管理系统：基于关系模型， 只存储数据，数据存储在实体里面，以包含特定信息的表格的形式存在。用于处理比较简单的数据。
-
客户规模
Oracle主要面向大企业级别的用户，而MySQL则更适合中小型企业和个人。 -
成本
Oracle付费，MySQL免费。 -
可移植性和兼容性
MySQL可以很容易地在各种平台上运行，并与其他许多开源软件集成。Oracle虽然也有跨平台支持，但更偏向于使用自己的技术堆栈和产品集成。 -
安全性
Oracle使用了许多安全功能，如用户名，密码，配置文件，本地身份验证，外部身份验证，高级安全增强功能等。MySQL只使用三个参数来验证用户，即用户名，密码和位置。 -
内存
Oracle占有内存空间大（因为面对对象，并且还存储数据的方法）；MySQL占有内存空间比较小。 -
性能和扩展性
由于MySQL的精简设计和管理方式，所以其性能通常比Oracle更高，尤其在读取和写入方面。MySQL的扩展性也相对较好，因为其社区活跃，有许多插件和工具可供选择和使用。 -
支持并发量
Oracle使用行级别的锁，不依赖索引来锁定某一行，锁资源的粒度小，所以并发性高，支持大并发访问量；
MySQL只有InnoDb支持行锁，而且必须依赖索引才能用到行锁，否则会使用表锁，所以并发性较低，面对大访问量可以做分表分库优化。 -
数据量
Oracle单表数据量，可以千万，甚至上亿的规模；Mysql单表数据量，最好控制在百万级别。 -
事务隔离级别
Oracle默认的是 read commited（读已提交）的隔离级别，会有不可重复读+幻读的问题。
MySQL默认是 repeatable read（可重复度）的隔离级别，只会有幻读的问题。 -
临时表
在MySQL中，临时表是仅对当前用户会话可见的数据库对象，并且一旦会话结束，这些表将自动删除。
Oracle中临时表的定义与MySQL略有不同，因为临时表一旦创建就会存在，直到它们被显式删除，并且对具有适当权限的所有会话都可见。但是，临时表中的数据仅对将数据插入表中的用户会话可见，并且数据可能在事务或用户会话期间持续存在。 -
sql语句的灵活性
MySQL对sql语句有很多非常实用而方便的扩展，比如limit功能(分页)、insert可以一次插入多行数据；Oracle在这方面感觉更加稳重传统一些，Oracle的分页是通过伪列和子查询完成的，插入数据只能一行行的插入数据。 -
对于事务的支持
Mysql对于事务默认是不支持的，只是有某些存储引擎中如：innodb可以支持；而Oracle对于事物是完全支持的。
适合Oracle的场景：
对数据库有高级需求：如果企业对数据库的高级需求较高，如存储复杂数据及其方法，要求高可用性、灾备恢复、安全性等，可以考虑用Oracle。
大型企业应用：Oracle在处理大规模、复杂的企业级应用方面表现出色。它能够处理海量的数据和高并发的访问请求，同时支持复杂的数据模型和关系。
项目并发量高：使用Oracle，它是是OLTP（联机事务处理）最好的工具。
安全性要求高：Oracle使用了许多安全功能，如用户名，密码，配置文件，本地身份验证，外部身份验证，高级安全增强功能等。像金融、银行等对安全性要求高的项目一般都选用Oracle作为数据库。
高可用性和容灾需求：Oracle提供了强大的高可用性和容灾解决方案，例如集群配置、数据复制和自动故障转移等，能够确保系统的连续性和数据的可靠性。MySQL付费版也支持，但可靠性不如Oracle。
1.2 使用区别
-
1、大小写是否敏感
Oracle：双引号下大小写敏感，大小写不敏感的前提条件是在没有使用双引号 “” 的前提下（表名、字段名）。
Oracle创建表默认大写，可以设置为小写。
// 如果创建表的时候是这样写的，那么就必须严格区分大小写
CREATE TABLE "TableName"("id" number);
// 不仅要区分大小写而且要加双引号，以便和上面的第三种查询方式区分开
SELECT * FROM "TableName";
MySQL创建的表名默认小写。
-
2、常用字段类型
Oracle常用字段类型：
数值：number。
字符串：CHAR，NCHAR，VARCHAR2和NVARCHAR2。
char：使用数据库字符集来存储数据，长度固定，如果存储的数据没有达到指定长度，自动补足空格。
nchar：使用国家字符集来存储数据，长度固定，如果存储的数据没有达到指定长度，数据库自动补足空格。
varchar2: 使用数据库字符集存储数据，长度可变，如果存储数据没有达到指定长度，不自动补足空格。
nvarchar2:使用国家字符集来存储数据，长度可变，如果存储的数据没有达到指定长度，不自动补足空格。
日期：date、timestamp。
oracle的varchar，一般不使用，那个是sql标准的类型，允许空串，oracle自己开发了个类型叫varchar2，不允许存空串，而且可以兼容以后的oracle版本。
MySQL常用字段类型：
数值：tinyint、smallint、mediumint、int、bigint、decimal。
字符串：char、varchar。
日期：date、time、datetime、timestamp。
-
3、时间日期
Oracle对于计算前7天，时间减7就行了；计算前1个小时，时间减1/24就行了，加法同理：
select TO_DATE('2021-05-30 15:51:20', 'yyyy-mm-dd hh24:mi:ss') -7 from dual;
-- 计算前7天，结果：2021-05-23 15:51:20
select TO_DATE('2021-05-30 15:51:20', 'yyyy-mm-dd hh24:mi:ss') - 1/24 from dual;
-- 计算前1个小时，结果：2021-05-30 14:51:20
MySQL计算前7天，或者前1个小时，需要用不同的关键字：
select date_sub('2021-05-30 21:00:40' ,interval 7 day)
-- 计算前7天，结果：2021-05-23 21:00:40
select date_sub('2021-05-30 21:00:40' ,interval 1 hour)
-- 计算前1个小时，结果：2021-05-30 20:00:40
Oracle中timestamp类型的字段，相减，结果是多少天、多少小时，多少分钟，多少秒：
SELECT MIN_T, MAX_T, MAX_T - MIN_T
FROM "T_TIMESTAMP"
-- 结果：2021-07-06 20:29:20.000000 2021-07-07 20:29:22.000000 +000000001 00:00:02.000000
MySQL中，2个timestamp类型的值相减，如果想要知道相差多少天、多少秒，需要借助函数：
SELECT min_t, max_t, TIMESTAMPDIFF(second,min_t,max_t) FROM `t_timestamp`
-- 结果：
2021-07-03 21:01:22 2021-07-04 21:01:22 86400
2021-07-04 21:02:01 2021-07-04 21:02:06 5
2021-07-04 21:03:58 2021-07-04 21:04:01 3
Oracle使用to_date()函数，将字符串转时间：
select TO_DATE('2021-05-30 15:51:20', 'yyyy-mm-dd hh24:mi:ss') from dual;
-- 24小时制的转换，结果：2021-05-30 15:51:20
select TO_DATE('2021-05-30 下午 11:51:20', 'yyyy-mm-dd am hh12:mi:ss') from dual;
-- 12小时制的转换，结果：2021-05-30 23:51:20
MySQL的字符串转时间，使用str_to_date()函数：
select STR_TO_DATE('2021-05-30 15:51:20','%Y-%m-%d %H:%i:%s');
-- 结果：2021-05-30 15:51:20
-- tip: myql的这个格式，也不难记，Y、m、d、H、i、s
Oracle使用to_char()函数，将时间转字符串：
select to_char(current_date, 'yyyy-mm-dd hh24:mi:ss') from dual;
-- 24小时制的转换，结果：2021-05-30 16:06:52
select to_char( TO_DATE('2021-05-30 15:51:20', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd am hh12:mi:ss') from dual;
-- 12小时制的转换，结果：2021-05-30 下午 03:51:20
MySQL使用date_format()函数：
select DATE_FORMAT(current_timestamp,'%Y-%m-%d %H:%i:%s');
-- 结果：2021-05-30 16:10:14
Oracle查询当前的日期+时间示例：
select CURRENT_TIMESTAMP from dual;
-- 结果：2021-05-30 16:19:10.640466 +08:00
select current_date from dual;
-- 结果：2021-05-30 16:16:54
select SYSDATE from dual;
-- 结果：2021-06-07 20:54:57
MySQL查询当前的日期+时间示例：
select CURRENT_TIMESTAMP;
-- 结果：2021-05-30 16:21:16
select now();
-- 结果：2021-05-30 16:17:41
-- 我一般用这个，比较简单，好记忆
select sysdate()
-- 结果：2021-06-07 20:55:38
- 4、建表和插入数据
-- Oracle
create table t_student(
sid int primary key ,
sname varchar2(10) not null ,
enterdate date,
gender char(2),
mail unique,
age number check (age>19 and age<30)
)
insert into t_student values(stuseq.nextval,'Test',to_date('1990-3-4','YYYY-MM-DD'),'男','1@outlook.com',20);
commit;
--MySQL
create table t_student(
sid int primary key auto_increment,
sname varchar(1) not null ,
enterdate date,
gender char(1),
age int,
mail varchar(10) UNIQUE
)
insert into t_student values(null,'Test','1990-3-4','男',30,'2@outlook.com')
-
5、事务
Oracle：完全支持事务，默认不自动提交，需要用户手动提交，提交可以通过以下几个命令实现：
BEGIN：事务块开始的标志。事务块里的SQL语句要么全部执行成功，要么全部失败回滚。
COMMIT：提交事务。执行成功时，事务将被提交，并且对数据库的修改是可见的。
ROLLBACK：用于取消尚未提交的事务，并将数据库恢复到事务开始之前的状态。当ROLLBACK语句执行成功时，事务中的所有修改都将被撤销。
SAVEPOINT：用于在事务中创建一个保存点，以便在事务执行过程中可以回滚到该保存点。它可以在事务中设置一个中间点，以便在需要时回滚到该点。
SET TRANSACTION：SET TRANSACTION用于设置事务的属性。通过该命令，可以设置事务的隔离级别、读写权限等属性。
示例：
BEGIN
SAVEPOINT sp;
-- 向学生表插入数据
INSERT INTO student_table (student_name, student_age) VALUES ('John', 18);
INSERT INTO student_table (student_name, student_age) VALUES ('Emma', 19);
-- 向班级表插入数据
INSERT INTO class_table (class_name, class_size) VALUES ('Class A', 30);
INSERT INTO class_table (class_name, class_size) VALUES ('Class B', 28);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO sp;
RAISE;
END;
MySQL：仅innoDB支持事务，默认自动提交。
-- 关闭事务提交
set AutoCommit = 0;
-- 手动提交事务
START TRANSACTION; -- 开始事务
INSERT INTO student (name,age) VALUES ('Tom',18); -- 执行一些数据操作
INSERT INTO score (student_id,score) VALUES (1,90);
COMMIT; -- 手动提交事务
-
6、分页
Oracle：需要用rownum和子查询来做。示例：
-- 查第10-20条记录。10、20个数字表示记录的行号
select * from (
SELECT emp.*, rownum as rowno from emp
)t_target
where rowno >= 10 and rowno < 20
MySQL：通过limit关键字分页。示例：
-- 查第10-20条记录。第一个10表示起始下标，第二个10表示取多少条记录
SELECT * FROM `tb_user`
limit 10, 10
-
7、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则自带自动自增的功能。
-
7、字符处理
Oracle的字符串只能用单引号包裹。
select concat('%', 'G1120') from dual
-- 结果：%G1120
MySQL的字符串可以用单引号，也可以用双引号包裹。
select concat("%", 'G1120')
-- 结果：%G1120
oracle中不允许有空字符串的存在，如果update某个字段为’'，则会发现这个字段值变成null了。mysql中允许空字符串。
-
8、多条件判断
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。
-
9、小数格式化
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
MySQL使用format()函数：
select FORMAT(211.125,2);
-- 需要四舍五入的情况，结果：211.13
select FORMAT(211.1,2);
-- 小数位不够2位的情况，结果：211.10
select FORMAT(0,2);
-- 特殊值0的情况，结果：0.00
-
10、查询语句
Oracle的查询必须带from：
select current_date from dual;
-- 结果：2021-05-30 16:16:54
MySQL的查询，就没强制要求带from关键字，但是查询dual表也支持：
select now();
-- 结果：2021-05-30 16:17:41
二、MySQL和PostgreSQL
PostgreSQL是一个企业级的关系数据库，允许关系和非关系查询，支持：Java、.net、Go、C、C++等。PostgreSQL通常用作网站、地理空间和分析应用程序的主要数据存储或仓库。文章来源：https://www.toymoban.com/news/detail-824074.html
2.1 基本差别*
-
PostgreSQL的优势
1、功能强大。PostgreSQL对正则表达式的支持强，内置函数也丰富。
2、性能优化工具与度量信息丰富。PostgreSQL数据库中有大量的性能视图，可以方便地定位问题。此外，PostgreSQL还设计了专门架构和进程用于收集性能数据视图，比如可以看打卡正在执行的SQL、哪条记录被锁定等。
3、在线操作功能好。PostgreSQL增加空值列时，本质上只是在系统表上把列定义上，无须对物理结构做更新。PostgreSQL还支持在线建索引的功能，在创建索引的过程中，可以不锁更新操作。
4、支持数据仓库。MySQL对SQL语法支持的功能较弱，基本上不适合做数据仓库。PostgreSQL不仅支持复杂的SQL，还有大量的分析函数，非常适合做数据仓库。
5、移动互联网特征。PostgreSQL中还有一些支持移动互联网的新功能，比如：空间索引。
综上，PostgreSQL是一个功能强大、又带有移动互联网的开源数据库。 -
MySQL的优势
1、MySQL的事务回滚机制较好。InnoDB的基于回滚实现的MVCC机制，相对于PG新老数据一起存放的基于XID的MVCC机制，是占优的。
2、MySQL采用索引组织表。这种存储方式非常适用于主键匹配的查询、删改操作，但是对表结构设计存在约束。
3、MySQL的优化器比较简单。系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作。
4、MySQL分区表的实现要优于PG。
5、MySQL的存储引擎插件化机制。使得它的应用场景更加广泛，比如除了InnoDB适合事务处理场景外，MyIsam适合静态数据的查询场景。 -
两者的选择
如果仅仅是想把数据库作为一个简单的存储软件，比如一些大型互联网公司就是这样，一些比较复杂的功能都放在应用中，那么MySQL是合适的。
如果应用的数据访问很简单，比如大多数的博客系统，那么后端使用MySQL也是合适的。
如果应用不像博客系统那么简单，又不像耗费太多的开发资源，那么使用PostgreSQL是合适的。
2.2 使用差别
-
1、格式区别
和Oracle一样，PostgreSQL也是严格区分大小写。 -
2、自增区别
MySQL中使用auto_increment ，在需要的列指定自增；而PostgreSQL中需要设置自增序列。
PostgreSQL的自增序列设置：
-- 创建表时
CREATE table infisa_template_config(id serial );
-- 表已存在
<--设置序列从1开始，自增1-->
CREATE SEQUENCE user_id_seq START WITH 1
INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
<--设置序列-->
ALTER table user ALTER column id SET DEFAULT nextval('user_id_seq');
-
3、时间转化
时间转字符串：
-- MySQL
date_format(a.tag_create_date,'%Y-%m-%d %H:%i:%s')
-- PostgreSQL
to_char(a.tag_create_date,'yyyy-mm-dd HH:MM:SS')
字符串转时间：文章来源地址https://www.toymoban.com/news/detail-824074.html
-- MySQL
STR_TO_DATE('2021-05-30 15:51:20','%Y-%m-%d %H:%i:%s')
-- PostgreSQL
to_date(a.tag_create_date,'yyyy-mm-dd HH:MM:SS')
- 4、字段为null时返回指定值
-- MySQL
IFNULL(a.idm,'')
-- PostgreSQL
COALESCE(a.id,'')
- 5、获取当前日期
-- MySQL
SELECT sysdate()
-- PostgreSQL
SELECT now()
- 6、limit
-- MySQL
select id,name from hospital.ods_user_basic limit 10,2;
-- PostgreSQL
select id,name from hospital.ods_user_basic limit 10 offset 2;
到了这里，关于MySQL（七）MySQL和Oracle、PostgreSQL的区别的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
