sql oracle mysql 数据库 基础 知识 窗口函数 lag与lead函数 LISTAGG函数 connect by 结构化查询 备份和恢复 mysql5.7 mysql8.0 oracle_11g docker
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比起来，有区别：
首先是这3种类型，都不允许指定scale，也就是小数的位数，我尝试了一下这几个字段，scale修改为2，navicat中一点击保存，还是变回了0；
其次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 同上