MYSQL:查看死锁日志
SHOW ENGINE INNODB STATUS
ORACLE 分析SQL执行性能1
/查询SQLID/
SELECT *
FROM GV$SQL S
WHERE S.SQL_TEXT LIKE ‘%关键字%’;
/获取SQL分析报告:包括获取正在运行的SQL参数/
select dbms_sqltune.report_sql_monitor(sql_id=>‘grrkbky39wguq’) from dual;
2:
explain plan for
select * from table(dbms_xplan.display())
1、序列
自增主键改法:
SELECT LAST_INSERT_ID()
</selectKey>
自定义主键雪花算法
SELECT ${@com.com.dfs.config.UniqueIdWorker@nextSeqId()}
JAVA代码获取单个或多个序列号
-
业务实现类注入雪花算法单例
@Autowired
private UniqueIdWorker uniqueIdWorker;
2.调用
uniqueIdWorker.nextIdString()―――返回字符串类型ID
或uniqueIdWorker.nextId()—返回long类型的ID
List uniqueIdList =
uniqueIdWorker.nextIdsForBatch(10)―――获取10个ID
2、日期格式化
oracle Mysql
TRUNC(SYSDATE) - 3 DATE_SUB(DATE_FORMAT(SYSDATE(),’%Y-%m-%d’), INTERVAL 3 DAY)
TRUNC(SYSDATE)+4 DATE_ADD(DATE_FORMAT(SYSDATE(),’%Y-%m-%d’), INTERVAL 4 DAY)
TRUNC(SYSDATE) DATE_FORMAT(SYSDATE(),’%Y-%m-%d’)
3、获取一行
oracle mysql
ROWNUM=1 limit 1
4、子查询需要使用别名
转换前ORACLE语句
select
DC_SUPPLY_MAPPING_JOB_ID, REGION_CODE_LIST, FULFIL_ORG_CODE_LIST, OBN_NODE_CODE_LIST,
PRODUCT_SERIES_CODE_LIST, OFFERING_CODE_LIST, SKU_NUMBER_LIST, PARAMETER, SUPPLY_MAPPING_JOB_NAME_CN,
SUPPLY_MAPPING_JOB_NAME_EN, SUPPLY_MAPPING_JOB_CODE, TYPE, REPO_DFS_DOWNLOAD_FLAG,
DC_ATP_DOWNLOAD_FLAG, START_TIME, END_TIME, STATUS, RESULT, CREATED_BY_ID, CREATED_BY_ACCOUNT,
CREATION_DATE, LAST_UPDATED_BY_ID, LAST_UPDATED_BY_ACCOUNT, LAST_UPDATE_DATE, LAST_UPDATE_VERSION,
DESCRIPTION, DELETE_FLAG, ERROR_MSG
from (
select t2.*
from DFS_DC_HEAD_T t1,
DFS_DC_SUPPLY_MAPPING_JOB_T t2
where t1.SUPPLY_MAPPING_JOB_CODE = t2.SUPPLY_MAPPING_JOB_CODE
and t1.FULFIL_ORG_CODE in (
select FULFIL_ORG_CODE from DFS_DC_HEAD_T where SUPPLY_MAPPING_JOB_CODE = '6666697077998563328' )
and t1.SUPPLY_MAPPING_JOB_TYPE = '10'
order by t2.CREATION_DATE desc
)
where ROWNUM=1
转换之后的mysql语句
select
DC_SUPPLY_MAPPING_JOB_ID, REGION_CODE_LIST, FULFIL_ORG_CODE_LIST, OBN_NODE_CODE_LIST,
PRODUCT_SERIES_CODE_LIST, OFFERING_CODE_LIST, SKU_NUMBER_LIST, PARAMETER, SUPPLY_MAPPING_JOB_NAME_CN,
SUPPLY_MAPPING_JOB_NAME_EN, SUPPLY_MAPPING_JOB_CODE, TYPE, REPO_DFS_DOWNLOAD_FLAG,
DC_ATP_DOWNLOAD_FLAG, START_TIME, END_TIME, STATUS, RESULT, CREATED_BY_ID, CREATED_BY_ACCOUNT,
CREATION_DATE, LAST_UPDATED_BY_ID, LAST_UPDATED_BY_ACCOUNT, LAST_UPDATE_DATE, LAST_UPDATE_VERSION,
DESCRIPTION, DELETE_FLAG, ERROR_MSG
from (
select t2.*
from DFS_DC_HEAD_T t1,
DFS_DC_SUPPLY_MAPPING_JOB_T t2
where t1.SUPPLY_MAPPING_JOB_CODE = t2.SUPPLY_MAPPING_JOB_CODE
and t1.FULFIL_ORG_CODE in (
select FULFIL_ORG_CODE from DFS_DC_HEAD_T where SUPPLY_MAPPING_JOB_CODE = '6666697077998563328' )
and t1.SUPPLY_MAPPING_JOB_TYPE = '10'
order by t2.CREATION_DATE desc
) AS TEMP
limit 1
5、Mysql分页
删除如下标签:
6、获取本周周一
ORACLE MYSQL
trunc(sysdate,‘iw’) SUBDATE(curdate(), weekday(curdate()))
7、MYSQL语句当行和多行注释
MySQL 单行注释
- 单行注释可以使用#注释符,#注释符后直接加注释内容。格式如下:
#注释内容
单行注释使用注释符#的示例如下:
#从结果中删除重复行
SELECT DISTINCT product_id, purchase_price FROM Product;
- 单行注释可以使用–注释符,–注释符后需要加一个空格,注释才能生效。格式如下:
– 注释内容
单行注释使用注释符–的示例如下:
– 从结果中删除重复行
SELECT DISTINCT product_id, purchase_price FROM Product;
MySQL 多行注释
多行注释使用/* /注释符。/用于注释内容的开头,/用于注释内容的结尾。多行注释格式如下:
/
第一行注释内容
第二行注释内容
*/
注释内容写在/和/之间,可以跨多行。
多行注释的使用示例如下
/这条SELECT语句,
会从结果中删除重复行/
SELECT DISTINCT product_id, purchase_price FROM Product;
任何注释(单行注释和多行注释)都可以插在 SQL 语句中,且注释可以放在 SQL 语句中的任意位置。
8、MYSQL语句删除别名
场景一:删除语句子查询为不同表
ORACLE删除语句
DELETE
FROM
DFS_DC_LINE_T L
WHERE
L.DC_HEAD_ID = (
SELECT
H.DC_HEAD_ID
FROM
DFS_DC_HEAD_T H
WHERE
H.SUPPLY_MAPPING_JOB_CODE = '6724173602931773440'
)
AND L.SUPPLY_DEMAND_TYPE = ‘3240’
AND L.SKU_NUMBER IN (‘51050HEP’)
MYSQL删除语句:方式一
DELETE
FROM
DFS_DC_LINE_T
WHERE
DFS_DC_LINE_T.DC_HEAD_ID = (
SELECT
H.DC_HEAD_ID
FROM
DFS_DC_HEAD_T H
WHERE
H.SUPPLY_MAPPING_JOB_CODE = '6724173602931773440'
)
AND DFS_DC_LINE_T.SUPPLY_DEMAND_TYPE = ‘3240’
AND DFS_DC_LINE_T.SKU_NUMBER IN (‘51050HEP’)
修改方式二:
DELETE L
FROM
DFS_DC_LINE_T L
WHERE
L.DC_HEAD_ID = (
SELECT
H.DC_HEAD_ID
FROM
DFS_DC_HEAD_T H
WHERE
H.SUPPLY_MAPPING_JOB_CODE = '6724173602931773440'
)
AND L.SUPPLY_DEMAND_TYPE = ‘3240’
AND L.SKU_NUMBER IN (‘51050HEP’)
场景二:删除语句子查询为同一张表
ORACLE删除语句:
DELETE FROM DPS_MPART_PRE_SCHEDULE_TI WHERE TRACKING_ID < (SELECT MAX(TRACKING_ID) FROM DPS_MPART_PRE_SCHEDULE_TI)
mysql删除语句:需要借助临时表先查询出来待删除的条件
DELETE L
FROM
DPS_MPART_PRE_SCHEDULE_TI L
WHERE
L.TRACKING_ID < (
SELECT
*
FROM
(
SELECT
MAX(TI.TRACKING_ID)
FROM
DPS_MPART_PRE_SCHEDULE_TI TI
) AS TEMP
)
9、批量插入获取序列号
备注:该接口不能有多余的参数,只能有一个list类型的参数。
insert into DFS_DC_SNAPSHOT_PARAM_T (REPOFFICE_CODE,
CREATED_BY_ID, CREATED_BY_ACCOUNT, CREATION_DATE,
LAST_UPDATED_BY_ID, LAST_UPDATED_BY_ACCOUNT,
LAST_UPDATE_DATE, LAST_UPDATE_VERSION, DESCRIPTION,
DELETE_FLAG, REGION_CODE, SUPPLY_MAPPING_JOB_CODE,
PARAMETER_KEY, PARAMETER_STRING_VAL, PARAMETER_INTEGER_VAL,
PARAMETER_DECIMAL_VAL, FULFIL_ORG_CODE, SKU_NUMBER,
PSPART_NUMBER)
values
(
#{item.repofficeCode,jdbcType=VARCHAR},
#{item.createdById,jdbcType=DECIMAL},
#{item.createdByAccount,jdbcType=VARCHAR},
#{item.creationDate,jdbcType=TIMESTAMP},
#{item.lastUpdatedById,jdbcType=DECIMAL},
#{item.lastUpdatedByAccount,jdbcType=VARCHAR},
#{item.lastUpdateDate,jdbcType=TIMESTAMP},
#{item.lastUpdateVersion,jdbcType=DECIMAL},
#{item.description,jdbcType=VARCHAR},
#{item.deleteFlag,jdbcType=VARCHAR},
#{item.regionCode,jdbcType=VARCHAR},
#{item.supplyMappingJobCode,jdbcType=VARCHAR},
#{item.parameterKey,jdbcType=VARCHAR},
#{item.parameterStringVal,jdbcType=VARCHAR},
#{item.parameterIntegerVal,jdbcType=DECIMAL},
#{item.parameterDecimalVal,jdbcType=DECIMAL},
#{item.fulfilOrgCode,jdbcType=VARCHAR},
#{item.skuNumber,jdbcType=VARC