关系型数据库Mysql和Oracle的区别

一、基本区别：

是否开源：mysql是开源免费的，而oracle则是收费的，并且价格非常高

管理工具：mysql的管理工具较少，在Linux下的管理工具的安装有时需要安装额外的包（phpmyadmin，etc），有一定复杂性。而oracle有多重成熟命令行、图形界面、web管理工具，还有很多第三方的管理工具，管理极其方便高效。oracle支持大并发，大访问量，是OLTP最好的工具。

事务提交：mysql默认是自动提交，可以修改为手动提交。而oracle默认不自动提交，需要手动提交，需要在写commit指令或点击commit按钮

并发性：mysql以表级锁为主，对资源锁定的粒度很大，如果一个session对一个表加锁时间过长，会让其他session无法更新此表中的数据。而oracle使用行级锁，对资源锁定的粒度要小很多，只是锁定sql需要的资源，并且加锁是在数据库中的数据行上，不依赖于索引，所以oracle对并发性的支持要好很多。

主从复制：mysql复制服务器配置很简单，但主库出问题时，从库可能丢失一定的数据，且需要手工切换从库到主库；而oracle既有堆或拉式的传统数据复制，也有dataguard的双机或多机容灾机制，主库出问题时，可以自动切换备库到主库，但配置管理较复杂。

端口号：mysql是3306，oracle是1521

管理用户：mysql是root用户，oralce为system和sys用户

大小写：mysql不区分，oracle区分

二、库表操作

1.库操作

创建删除数据库

mysql：create/drop database 库名称 charset='utf8'

oracle：create/drop tablespace 表空间名称

2.表操作

表中数据类型

整形：int

浮点型：float, numeric(m,n) / decimal(m,n)

字符型：char(m)不可变长，varchar(m)可变长-mysql，varchar2(m)可变长-oracle版本2

其他类型： date日期，time时间，datetime日期时间，timestamp年-月-日 时:分:秒，set集合类型mysql有oracle没有（但可通过检查约束实现）

表的约束

主键约束（PRIMARY KEY）主键是一行数据的唯一标识，要求非空且唯一

外键约束（FOREIGN KEY）用来让两张表的数据之间建立连接，保证数据的一致性和完整性

唯一性约束（UNIQUE）保证该字段的所有数据都是唯一、不重复的，可以有多个

默认值约束（DEFAULT）保存数据时，如果未指定该字段的值，则采用默认值 

非空约束（NOT NULL）限制该字段的数据不能为null  

自增长约束（AUTO_INCREMENT）为每条记录生成唯一的标识号，oracle中没有，但可通过create sequence 序列名来实现自增序列

检查约束（CHECK）保证字段值满足某一个条件(Mysql8.0.16版本之后增加) 

增加表

create table 表名称（字段名 类型 约束...），mysql后可加charset='utf8'，oracle不可以

删除表

drop table 表名称

查看表

mysql：show tables；

oracle：select * from tab，tab表里放了所有的表信息

增加数据

insert into 表名称 values（值1，值2），但mysql支持非标准插入不加values的s，不加into

删除数据

delete from 表名称，truncate table 表名称，而truncate和delete的区别在于四个方面：

1. 条件删除：因为delete是可以带where的，所以支持条件删除;而truncate只能删除整个表

2. 事务回滚：由于delete是数据操作语言(DML - Data Manipulation Language)，操作时原数据会被放到 rollback segment中，可以被回滚;而truncate是数据定义语言(DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚

3. 清理速度：在数据量比较小的情况下，delete和truncate的清理速度差别不是很大。但是数据量很大的时候就能看出区别。由于第二项中说的，truncate不需要支持回滚，所以使用的系统和事务日志资源少。delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项，固然会慢，但是相对来说也较安全

4. 高水位重置：随着不断地进行表记录的DML操作，会不断提高表的高水位线(HWM)，delete操作之后虽然表的数据删除了，但是并没有降低表的高水位，随着DML操作数据库容量也只会上升，不会下降。所以如果使用delete，就算将表中的数据减少了很多，在查询时还是很和delete操作前速度一样。而truncate操作会重置高水位线，数据库容量也会被重置，之后再进行DML操作速度也会有提升

修改数据

update 表 set 列 = 值 where 条件

二、查询操作

1.单表查询

标准语句

select * from 表

查日期

mysql：select now(),可以不跟from

oracle：select sysdate from dual，需要有from，给dual伪表查出一行，给实际的表查出多行结果

查询显示列

select 列1，列2 from 表

查询去重

select distinct 列1，列2 from 表；select 列1，列2 from 表 group by 列1，列2；

如果去重的字段有索引，那么 group by 和 distinct 都可以使用索引，此情况它们的性能是相同的；而当去重的字段没有索引时，distinct 的性能就会高于 group by，因为在 MySQL 8.0 之前，group by 有一个隐藏的功能会进行默认的排序，这样就会触发 filesort 从而导致查询性能降低。

查询表达式

select 薪水 * 12 as 年收入 from 表

查询空值处理

mysql：select 薪水 * 12+ifnull(comm,0) as 年收入 from 表

oracle：select 薪水 * 12+NVL(comm,0) as 年收入 from 表

排序取top3

mysql：select * from 表 order by 字段 limit 3

oracle：select * from 表 order by 字段 where rownum <= 3，使用隐藏虚拟列rownum，但不能大于3或者等于3，采用select * from (select rownum rn, 表* from 表) a where rn > 3方式，将序列实化后进行过滤处理

日期相减

mysql：select datediff(日期1，日期2)默认前减后；select timestampdiff(起始日期，结束日期)默认后减前

oracle：select 日期1-日期2 from 表

2.多表查询

等值连接

select * from a,b where a.id = b.id

内连接

select * from a join b on a.id = b.id，等价于上面的等值链接

不等值连接

select * from a join b on a.id > b.id

左右连接

select * from a left join b on a.id = b.id

oracle：select * from a,b where a.id = b.id(+)，可以用这种等值连接方式实现左连接功能

3.子查询

子查询在from子句中，看成一张表

子查询在where子句中，看成一个数值：where 字段 = （子查询）

子查询在where子句中，看成一组数值：where 字段 in（子查询）

子查询在where子句中，看成多组数值：where （字段1，字段2） in（子查询）

子查询在select列中，可以替代多表join且具有分组功能：select a.*，(select count(*) from b where a.id = b.aid) from a