MYSQL 与 ORACLE 用法区别

borunning

已于 2022-02-15 11:12:06 修改

阅读量775

点赞数

文章标签： mysql oracle 数据库

于 2021-12-13 21:01:07 首次发布

本文链接：https://blog.csdn.net/weixin_44067290/article/details/120163973

版权

MYSQL 与 ORACLE 用法区别

日期格式

MYSQL


%Y：	年，4 位
%y：	年，2 位
%M：	月名
%m：	月，数值（00-12）
%D：	带有英文前缀的月中的天
%d：	月的天，数值（00-31）
%e：	月的天，数值（0-31）

%H：	小时（00-23）
%h：	小时（01-12）
%I：	小时（01-12）
%i：	分钟，数值(00-59)
%S：	秒(00-59)
%s：	秒(00-59)

%W：	星期名
%a：	缩写星期名
%b：	缩写月名
%c：	月，数值
%f：	微秒
%j：	年的天 (001-366)
%k：	小时 (0-23)
%l：	小时 (1-12)

%p：	AM 或 PM
%r：	时间，12-小时（hh:mm:ss AM 或 PM）
%T：	时间, 24-小时 (hh:mm:ss)

%U：	周 (00-53) 星期日是一周的第一天
%u：	周 (00-53) 星期一是一周的第一天
%V：	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v：	周 (01-53) 星期一是一周的第一天，与 %x 使用
%w：	周的天（0=星期日, 6=星期六）

%X：	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x：	年，其中的星期一是周的第一天，4 位，与 %v 使用

ORACLE

	格式：		显示值:
Year	yy	two digits 两位年	21
Year	yyy	three digits 三位年	021
Year	yyyy	four digits 四位年	2021

Month	mm	number 两位月	11
Month	mon	abbreviated 字符集表示	11月,若是英文版,显示nov
Month	month	spelled out 字符集表示	11月,若是英文版,显示november

Day	dd	number 当月第几天	02
Day	ddd	number 当年第几天	02
Day	dy	abbreviated 当周第几天简写	星期五,若是英文版,显示fri
Day	day	spelled out 当周第几天全写	星期五,若是英文版,显示friday
Day	ddspth	spelled out, ordinal twelfth

Hour	hh	two digits 12小时进制	01
Hour	hh24	two digits 24小时进制	13

Minute	mi	two digits 60进制	45

Second	ss	two digits 60进制	25

其它	Q	digit 季度	4
其它	WW	digit 当年第几周	44
其它	W	digit 当月第几周	1

connect by start with

1、mysql没有层级查询方法而 oracle通过connect by start with语法可以实现层级查询
2、mysql实现层级查询的方式很多，有使用存储过程函数嵌套调用亦有使用临时表进行层级查询

要求：
一个字段里存放多个客户信息，多个客户信息中间用‘|#END#|’分割，每个客户信息按照名称，个人/机构标志，证件类型，证件号码，证件有效期，电话号码，电子邮箱存放客户数据并用‘|’符号分割。将所有客户的所有基本信息查询出来。

MYSQL写法：
SELECT
	SUBSTRING_INDEX(T.IN_STR,IN_INDEX2,1) AS BENE_NAME,
	SUBSTRING_INDEX(SUBSTRING_INDEX(T.IN_STR,IN_INDEX2,2),IN_INDEX2,-1) AS BENE_CUST_TYPE,
	SUBSTRING_INDEX(SUBSTRING_INDEX(T.IN_STR,IN_INDEX2,3),IN_INDEX2,-1) AS BENE_CERT_TYPE,
	SUBSTRING_INDEX(SUBSTRING_INDEX(T.IN_STR,IN_INDEX2,4),IN_INDEX2,-1) AS BENE_CERT_NO,
	SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(T.IN_STR,IN_INDEX2,5),IN_INDEX2,-1),1,POSITION(' ' IN SUBSTRING_INDEX(SUBSTRING_INDEX(T.IN_STR,IN_INDEX2,5),IN_INDEX2,-1))-1) AS BENE_VALID_DT,
	SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(T.IN_STR,IN_INDEX2,5),IN_INDEX2,-1),POSITION(' ' IN SUBSTRING_INDEX(SUBSTRING_INDEX(T.IN_STR,IN_INDEX2,5),IN_INDEX2,-1))+1) AS BENE_INVALID_DT,
	SUBSTRING_INDEX(SUBSTRING_INDEX(T.IN_STR,IN_INDEX2,6),IN_INDEX2,-1) AS BENE_TEL,
	SUBSTRING_INDEX(T.IN_STR,IN_INDEX2,-1) AS BENE_EMAIL
FROM (
	SELECT 
		SUBSTRING_INDEX(SUBSTRING_INDEX(@TO_STR,@TO_INDEX,HELP_TOPIC_ID+1),@TO_INDEX,-1) AS IN_STR, 
		@TO_INDEX AS IN_INDEX, 
		@TO_INDEX2 AS IN_INDEX2 , 
		(HELP_TOPIC_ID + 1) AS HELP_TOPIC_ID, 
		@TO_STR 
	FROM 
		MYSQL.HELP_TOPIC, 
		(SELECT @TO_STR := '名称1|个人/机构标志1|证件类型1|证件号码1|证件生效1 证件失效1|电话号码1|电子邮箱1|#END#|名称2|个人/机构标志2|证件类型2|证件号码2|证件生效2 证件失效2|电话号码2|电子邮箱2|#END#|名称3|个人/机构标志3|证件类型3|证件号码3|证件生效3 证件失效3|电话号码3|电子邮箱3|#END#|名称4|个人/机构标志4|证件类型4|证件号码4|证件生效4 证件失效4|电话号码4|电子邮箱4|#END#|', @TO_INDEX := '|#END#|', @TO_INDEX2 := '|') STR 
	WHERE 
		HELP_TOPIC_ID < (LENGTH(@TO_STR) - LENGTH(REPLACE(@TO_STR,@TO_INDEX,'')))/LENGTH(@TO_INDEX) 
	ORDER BY HELP_TOPIC_ID
) T

ORACLE 写法：
SELECT
	REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(IN_STR,'|#END#|',','),'[^,]+',1,ROWNUM),'[^,]+',1,1) AS BENE_NAME,
	REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(IN_STR,'|#END#|',','),'[^,]+',1,ROWNUM),'[^,]+',1,2) AS BENE_CUST_TYPE,
	REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(IN_STR,'|#END#|',','),'[^,]+',1,ROWNUM),'[^,]+',1,3) AS BENE_CERT_TYPE,
	REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(IN_STR,'|#END#|',','),'[^,]+',1,ROWNUM),'[^,]+',1,4) AS BENE_CERT_NO,
	SUBSTRING(REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(IN_STR,'|#END#|',','),'[^,]+',1,ROWNUM),'[^,]+',1,5),1,POSITION(' ' IN REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(IN_STR,'|#END#|',','),'[^,]+',1,ROWNUM),'[^,]+',1,5))-1) AS BENE_VALID_DT,
	SUBSTRING(REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(IN_STR,'|#END#|',','),'[^,]+',1,ROWNUM),'[^,]+',1,5),POSITION(' ' IN REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(IN_STR,'|#END#|',','),'[^,]+',1,ROWNUM),'[^,]+',1,5))+1) AS BENE_INVALID_DT,
	REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(IN_STR,'|#END#|',','),'[^,]+',1,ROWNUM),'[^,]+',1,6) AS BENE_TEL,
	REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(IN_STR,'|#END#|',','),'[^,]+',1,ROWNUM),'[^,]+',1,7) AS BENE_EMAIL
FROM (
	SELECT '名称1|个人/机构标志1|证件类型1|证件号码1|证件生效1 证件失效1|电话号码1|电子邮箱1|#END#|名称2|个人/机构标志2|证件类型2|证件号码2|证件生效2 证件失效2|电话号码2|电子邮箱2|#END#|名称3|个人/机构标志3|证件类型3|证件号码3|证件生效3 证件失效3|电话号码3|电子邮箱3|#END#|名称4|个人/机构标志4|证件类型4|证件号码4|证件生效4 证件失效4|电话号码4|电子邮箱4|#END#|' AS IN_STR, '|#END#|' AS IN_INDEX, '|' AS IN_INDEX2
)
CONNECT BY ROWNUM <= LENGTH(REPLACE(IN_STR,IN_INDEX,',')) - LENGTH(REPLACE(REPLACE(IN_STR,IN_INDEX,','),',',''))

执行结果：

INSERT

INSERT 忽略重复

mysql

	INSERT IGNORE INTO
	必须是主键重复才能忽略
	只是唯一索引不生效

oracle

1. errlog 重复后会在MY_ERROR_INFO插入一条记录

create table MY_ERROR_INFO
(
    ora_err_number$ NUMBER,
    ora_err_mesg$   VARCHAR2(2000),
    ora_err_rowid$  ROWID,
    ora_err_optyp$  VARCHAR2(2),
    ora_err_tag$    VARCHAR2(2000),
    myid            NUMBER
);
		
INSERT INTO MY_TABLE (COLUMN1, COLUMN2, COLUMN3, COLUMN4, CREATE_TM, CREATOR, MODIFY_TM, MODIFIER)
VALUES ('100001', '幺零零零零幺', 'COM.100001', '1', NULL, NULL, NULL, NULL)
LOG ERRORS INTO MY_ERROR_INFO ( 'MY_TABLE ' ) REJECT LIMIT UNLIMITED;

2. IGNORE_ROW_ON_DUPKEY_INDEX 这个注释有用 主键或者唯一索引都生效

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(MY_TABLE (COLUMN1, COLUMN2)) */ INTO MY_TABLE
(COLUMN1, COLUMN2, COLUMN3, COLUMN4, CREATE_TM, CREATOR, MODIFY_TM, MODIFIER)
VALUES ('100001', '幺零零零零幺', 'COM.100001', '1', NULL, NULL, NULL, NULL);

INSERT 替换重复行

mysql

	REPLACE INTO
	必须是主键重复才能忽略
	只是唯一索引不生效

oracle

INSERT 更新重复列

mysql

	INSERT INTO ON DUPLICATE KEY UPDATE
	必须是主键重复才能忽略
	只是唯一索引不生效

oracle

索引

索引失效

ORACLE

-- 失效查询监控语句：

-- 普通索引：
SELECT owner, index_name, tablespace_name FROM dba_indexes WHERE status = 'UNUSABLE';
-- 分区索引：
SELECT index_owner, index_name, partition_name, tablespace_name FROM dba_ind_PARTITIONS WHERE status = 'UNUSABLE';
-- 子分区索引：
SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name FROM dba_ind_SUBPARTITIONS WHERE status = 'UNUSABLE';

-- 对应的批量生成修复脚本：

SELECT 'alter index ' || owner || '.' || index_name ||
       ' rebuild tablespace ' || tablespace_name || ';'
  FROM dba_indexes
 WHERE status = 'UNUSABLE';

SELECT 'alter index ' || index_owner || '.' || index_name ||
       ' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' ||
       tablespace_name || ';'
  FROM dba_ind_partitions
 WHERE status = 'UNUSABLE';

SELECT 'alter index ' || index_owner || '.' || index_name ||
       ' rebuild subpartition ' || SUBPARTITION_NAME || ' TABLESPACE ' ||
       tablespace_name || ';'
  FROM dba_ind_subpartitions
 WHERE status = 'UNUSABLE';

-- 或者PL/SQL

set serveroutput on size unlimited

BEGIN
  FOR x IN
  (
    SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE PARALLEL' comm
    FROM    dba_indexes
    WHERE   status = 'UNUSABLE'
    UNION ALL
    SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||partition_name||' ONLINE PARALLEL'
    FROM    dba_ind_PARTITIONS
    WHERE   status = 'UNUSABLE'
    UNION ALL
    SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD SUBPARTITION '||subpartition_name||' ONLINE PARALLEL'
    FROM    dba_ind_SUBPARTITIONS
    WHERE   status = 'UNUSABLE'
  )
  LOOP
    dbms_output.put_line(x.comm);
    EXECUTE immediate x.comm;
  END LOOP;
END;

-- 原文链接：https://blog.csdn.net/kobe24shou/article/details/105428555

查进程锁

MYSQL

查询正在执行的SQL

select * from information_schema.`PROCESSLIST` where info is not null;
show processlist;

-- 1、查询进程
show processlist;
kill id;
-- 2.查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 3.查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

ORACLE

查询正在执行的SQL

-- 1、查询Oracle正在执行的sql语句及执行该语句的用户
SELECT b.sid oracleID,  
       b.username Oracle用户,  
       b.serial#,  
       spid 操作系统ID,  
       paddr,  
       sql_text 正在执行的SQL,  
       b.machine 计算机名  
FROM v$process a, v$session b, v$sqlarea c  
WHERE a.addr = b.paddr  
   AND b.sql_hash_value = c.hash_value;

-- 2、查看正在执行sql的发起者的发放程序
SELECT OSUSER 电脑登录身份,
       PROGRAM 发起请求的程序,
       USERNAME 登录系统的用户名,
       SCHEMANAME,
       B.Cpu_Time 花费cpu的时间,
       STATUS,
       B.SQL_TEXT 执行的sql
FROM V$SESSION A
LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
                   AND A.SQL_HASH_VALUE = B.HASH_VALUE
ORDER BY b.cpu_time DESC

-- 3、查出oracle当前的被锁对象
SELECT l.session_id sid,
       s.serial#,
       l.locked_mode 锁模式,
       l.oracle_username 登录用户,
       l.os_user_name 登录机器用户名,
       s.machine 机器名,
       s.terminal 终端用户名,
       o.object_name 被锁对象名,
       s.logon_time 登录数据库时间
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
ORDER BY sid, s.serial#;

-- 4、kill掉当前的锁对象可以为
alter system kill session 'sid， s.serial#‘;

-- 5、查看当前oracle中正在执行的sql语句——即查询oracle进程的sql
select a.program, b.spid, c.sql_text,c.SQL_ID
from v$session a, v$process b, v$sqlarea c
where a.paddr = b.addr
and a.sql_hash_value = c.hash_value
and a.username is not null;

查看正在执行的语句

查看锁

-- 1、查看锁是否存在
select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode 
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid; 
-- 2、释放锁				   SID，SERIAL
alter system kill session '1743,3445';