文章目录* *一、MySQL和Oracle * * 1.1 基本差别* * 1.2 使用区别 * 二、MySQL和PostgreSQL * * 2.1 基本差别* * 2.2 使用差别 本系列文章 MySQL一SQL语法、数据类型、常用函数、事务 MySQL二MySQL SQL练习题 MySQL三视图、存储过程、索引 MySQL四存储引擎、锁 MySQL五MySQL架构、数据库优化、主从复制 MySQL六SQL语句优化 MySQL七MySQL和Oracle、PostgreSQL的区别### 一、MySQL和Oracle#### 1.1 基本差别**数据库类型Oracle数据库是一个对象关系数据库管理系统ORDBMSMySQL是一个开源的关系数据库管理系统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。 字符串CHARNCHARVARCHAR2和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) FROMt_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 (age19 and age30) ) insert into t_student values(stuseq.nextval,‘Test’,to_date(‘1990-3-4’,‘YYYY-MM-DD’),‘男’,‘1outlook.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,‘2outlook.com’) *5、事务Oracle完全支持事务默认不自动提交需要用户手动提交提交可以通过以下几个命令实现BEGIN事务块开始的标志。事务块里的SQL语句要么全部执行成功要么全部失败回滚。 COMMIT提交事务。执行成功时事务将被提交并且对数据库的修改是可见的。 ROLLBACK用于取消尚未提交的事务并将数据库恢复到事务开始之前的状态。当ROLLBACK语句执行成功时事务中的所有修改都将被撤销。 SAVEPOINT用于在事务中创建一个保存点以便在事务执行过程中可以回滚到该保存点。它可以在事务中设置一个中间点以便在需要时回滚到该点。 SET TRANSACTIONSET 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 * FROMtb_userlimit 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和PostgreSQLPostgreSQL是一个企业级的关系数据库允许关系和非关系查询支持Java、.net、Go、C、C等。PostgreSQL通常用作网站、地理空间和分析应用程序的主要数据存储或仓库。#### 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’) 字符串转时间 – 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; 
长河君 · 2026/8/28 15:53:55 · 阅读 1.2k MySQL（七）MySQL和Oracle、PostgreSQL的区别
文章目录* *一、MySQL和Oracle * * 1.1 基本差别* * 1.2 使用区别 * 二、MySQL和PostgreSQL * * 2.1 基本差别* * 2.2 使用差别 本系列文章 MySQL一SQL语法、数据类型、常用函数、事务 MySQL二MySQL SQL练习题 MySQL三视图、存储过程、索引 MySQL四存储引擎、锁 MySQL五MySQL架构、数据库优化、主从复制 MySQL六SQL语句优化 MySQL七MySQL和Oracle、PostgreSQL的区别### 一、MySQL和Oracle#### 1.1 基本差别**数据库类型Oracle数据库是一个对象关系数据库管理系统ORDBMSMySQL是一个开源的关系数据库管理系统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。 字符串CHARNCHARVARCHAR2和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) FROMt_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 (age19 and age30) ) insert into t_student values(stuseq.nextval,‘Test’,to_date(‘1990-3-4’,‘YYYY-MM-DD’),‘男’,‘1outlook.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,‘2outlook.com’) *5、事务Oracle完全支持事务默认不自动提交需要用户手动提交提交可以通过以下几个命令实现BEGIN事务块开始的标志。事务块里的SQL语句要么全部执行成功要么全部失败回滚。 COMMIT提交事务。执行成功时事务将被提交并且对数据库的修改是可见的。 ROLLBACK用于取消尚未提交的事务并将数据库恢复到事务开始之前的状态。当ROLLBACK语句执行成功时事务中的所有修改都将被撤销。 SAVEPOINT用于在事务中创建一个保存点以便在事务执行过程中可以回滚到该保存点。它可以在事务中设置一个中间点以便在需要时回滚到该点。 SET TRANSACTIONSET 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 * FROMtb_userlimit 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和PostgreSQLPostgreSQL是一个企业级的关系数据库允许关系和非关系查询支持Java、.net、Go、C、C等。PostgreSQL通常用作网站、地理空间和分析应用程序的主要数据存储或仓库。#### 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’) 字符串转时间 – 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;