自常用sql整理备份

按日期分组

select trunc(CREATEDON ) 日期,count(1) 数据量  
from td_buyer  
where CREATEDON <  to_date('2020-06-01','YYYY-MM-DD') and CREATEDON>=  to_date('2020-06-11','YYYY-MM-DD')
group by trunc(CREATEDON );

删表

DROP TABLE `FBADS_FACEBOOK_PAGE`;

查询被包括字符串

SELECT * FROM fbads_keyword_filter WHERE 'ssssdhgates255' LIKE  CONCAT('%',keyword,'%') 

条件修改

UPDATE TD_FEEDBACK_SCORE_WEEK_S ss set BUYERLEVEL='1' where ss.FEEDBACKSCOREWEEKSID in 
(select s.FEEDBACKSCOREWEEKSID from TD_FEEDBACK_SCORE_WEEK_S s , TD_USER_DIM d where  s.buyerid=d.buyerid
AND d.EXP_LEVEL_ID in('d1','d2','d3','d4') and s.BUYERLEVEL='3')

计算

select  CAST(col_1_0_ /(case when col_0_0_ = 0 then 1 else col_0_0_ end) AS decimal(9,1)) from(
select count(*) as col_0_0_, nvl(sum(tdperforms0_.RESPONSE), 0) as col_1_0_
  from HGGJV2.TD_PERFORM_SERVISE_WEEK_S tdperforms0_
where tdperforms0_.SUPPLIERID = :1
   and tdperforms0_.RESPONSE <> '0.0'
   and (tdperforms0_.SYSWEEKINFOID in
       (select tcweekinfo1_.SYSWEEKINFOID
           from HGGJV2.TC_WEEK_INFO tcweekinfo1_
          where tcweekinfo1_.STARTDATE > add_months(sysdate, -12)
            and tcweekinfo1_.STARTDATE <= sysdate)));

大量数据 分页数据优化整理

源sql

select * from account where age >= 18;

优化后sql

select * from （select * from account where age >= 18）where limit 100,10;

子查询分页

SELECT * FROM account WHERE  id >=  
(SELECT id FROM account WHERE age = 18 ORDER BY id LIMIT 10000, 1) LIMIT 10 

join分页

SELECT * FROM account a JOIN (select id from account limit 10000, 10) b ON a.ID = b.id

分页count优化

mybatis

增加手写 count 查询支持 增加 countSuffix count 查询后缀配置参数，该参数是针对 PageInterceptor 配置的，默认值为 _COUNT。
分页插件会优先通过当前查询的 msId + countSuffix 查找手写的分页查询。
如果存在就使用手写的 count 查询，如果不存在，仍然使用之前的方式自动创建 count 查询。
例如，如果存在下面两个查询：

<select id="selectLeftjoin" resultType="com.github.pagehelper.model.User">
    select a.id,b.name,a.py from user a
    left join user b on a.id = b.id
    order by a.id
</select>
<select id="selectLeftjoin_COUNT" resultType="Long">
    select count(distinct a.id) from user a
    left join user b on a.id = b.id
</select>

上面的 countSuffix 使用的默认值 _COUNT，分页插件会自动获取到 selectLeftjoin_COUNT 查询，这个查询需要自己保证结果数正确。

返回值的类型必须是resultType=“Long”，入参使用的和 selectLeftjoin 查询相同的参数，所以在 SQL 中要按照 selectLeftjoin 的入参来使用。

因为 selectLeftjoin_COUNT 方法是自动调用的，所以不需要在接口提供相应的方法，如果需要单独调用，也可以提供。

参考资料：

pagehelper/Mybatis-PageHelper

mybatis 查询返回主键

<selectKey keyProperty="id" resultType="long" order="AFTER">
  SELECT LAST_INSERT_ID()
</selectKey>

数据库锁表查询

第一种：
1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

show processlist

3.杀死进程id（就是上面命令的id列）

kill id

第二种：
1.查看下在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2.杀死进程id（就是上面命令的trx_mysql_thread_id列）

kill 线程ID

例子：
查出死锁进程：SHOW PROCESSLIST
杀掉进程 KILL 420821;
其它关于查看死锁的命令：
1：查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;