日常操作"/>
各种日常操作
各种日常操作
2021-07-21 小杭整理
Mysql操作
动态行列
********mysql 动态行转列 ：********
-- 准备一个适用的测试方法 用字典的那种 ╮(╯_╰)╭ 方便以后修改为函数啥的使用
CREATE DEFINER=`zyuser`@`%` PROCEDURE `xiaohang_test`(
IN table_name VARCHAR (30), -- 统计表名
IN table_status VARCHAR(30), -- 字典映射字段
IN dict_type VARCHAR(30), -- 对应字典类型
IN sum_data VARCHAR(30), -- 分组统计求和内容
IN group_name VARCHAR(30))
BEGIN
SET @sql = NULL;
SELECTGROUP_CONCAT(DISTINCTCONCAT('sum(IF(u.',table_status,' = ''',c.dict_value,''', ',sum_data,', 0)) AS ''',c.dict_label, '''')) INTO @sql
FROM sys_dict_data c where c.dict_type = dict_type;select @sql;SET @sql = CONCAT('Select u.',table_status,',', @sql, ' From ',table_name,' uGroup by u.',table_status,'');
select @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END-- 调用栗子：
-- call xiaohang_test('biz_device_activity_info','status','biz_device_activity_status','1','policy_id')
-- 中文解释：对x1表的x2字段，依据字典类型x3,进行分组统计求和值x4, 分组统计依据x5
==============================================================================
-- 参考：
********mysql 动态行转列 ：********
BEGIN
SET @sql = NULL;
SELECTGROUP_CONCAT(DISTINCTCONCAT('MAX(IF(u.status = ''',c.status,''', u.status, 0)) AS ''',c.status, '''')) INTO @sql
FROM user c;
select @sql;
SET @sql = CONCAT('Select u.status,', @sql, ' From user uGroup by u.status');
select @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
锁表，解锁
-- mysql 锁表查询和解锁操作
-- 1.查看目前mysql数据库锁表的情况
SELECT * FROM information_schema.INNODB_TRX; -- 2.杀掉查询结果中已经锁表的trx_mysql_thread_id
kill trx_mysql_thread_id
时间获取
-- Mysql 获取当月和上个月第一天和最后一天的解决方案
#获取当前日期
select curdate();#获取当月最后一天
select last_day(curdate())；#获取本月的第一天
select date_add(curdate(),interval -day(curdate())+1 day) ;#获取下个月的第一天
select date_add(curdate() - day(curdate()) +1,interval 1 month );#获取当前月已过了几天
select day(curdate());#获取当前月的天数(先加一个月，再减今天是第几天，得到当前月的最后一天，最后求最后一天是几号)
select day(date_add( date_add(curdate(),interval 1 month),interval -day(curdate()) day ));#上个月的第一天
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(
day from now())-1 day),interval 1 month)#上个月的最后一天：
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(
day from now()) day),interval 0 month) as date#这个月的第一天：
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(
day from now())-1 day),interval 0 month)#获取当月最后一天
select last_day(curdate())；
解释分析对比
explaintype:ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）Extra:性能从好到坏:useing index>usinh where > using temporary | using filesort
查询逗号个数
select pids
,((length(pids)-length(replace(pids,',','')))/length(','))
from front_user
where id > 100
打开数据库定时功能
-- mysql数据库开启定时事件功能：
SHOW VARIABLES LIKE 'event_scheduler'
SET GLOBAL event_scheduler = ON;
查找表被使用的地方
--查找被使用的地方
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%rpt_merchant_trade_statistics%'
AND ROUTINE_TYPE='PROCEDURE'
and routine_schema = 'happy'
更新名称
-- 更新表名
rename table wechat_user to z_del_wechat_user;-- 更新存储过程名
UPDATE mysql.proc set name = 'z_del_pro_rpt_month_policy_trade_statistics',specific_name = 'z_del_pro_rpt_month_policy_trade_statistics' WHERE db = 'happypay' AND name = 'pro_rpt_month_policy_trade_statistics';
如果我想将“x”PROCEDURE重命名为“new_x”，则该语句将如下所示：
UPDATE`mysql` .proc`
SET name ='new_x'，
specific_name
='new_x'WHERE db ='db_mydb'AND
name ='x';
数组求和
-- '1,1,1,4,5,6,7',8,'1,2,3,4,5,6,7'CREATE DEFINER=`root`@`%` FUNCTION `测试数组求和`(`data` varchar(1000)) RETURNS int(11)
BEGIN#Routine body goes here...DECLARE i BIGINT(20) DEFAULT 0;DECLARE d_sum BIGINT(20) DEFAULT 0;DECLARE cc VARCHAR(10);DECLARE dd VARCHAR(1000);
-- set i = 1;while d_sum < 3 do set cc = SUBSTRING_INDEX(data, ',', 1);set d_sum = d_sum + CAST(IFNULL(cc,0) AS SIGNED) ;set dd = dd+cc;set data = replace( data,CONCAT( cc,','), '');set i = i +1;end while;RETURN i;
END
电话号码脱敏
CONCAT(LEFT(tel, 3), '****' , RIGHT(tel, 4))
Linux 的操作
同步时间
# 同步linux 时间信息：
ntpdate 0.asia.pool.ntp
Java 操作
本地解压缩jar包
# 压缩当前目录
jar -cfM0 happypay-open-new.jar ./
# 解压
jar xf "happy-rest-1.0 - 副本.jar"
Oracle操作
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- **闪回更新:逐条更新语句：select 'update bms_merchant set IS_DIRECT_MODE = '''||tx_data.IS_DIRECT_MODE || ''' where merchant_id = '''||a.merchant_id ||''';'as tx from bms_merchant a
left join (SELECT * FROM bms_merchantas of TIMESTAMP SYSTIMESTAMP - interval '200' minute where activation_date = '20170702' )tx_data on tx_data.id = a.idwhere a.activation_date = '20170702' and a.IS_DIRECT_MODE != tx_data.IS_DIRECT_MODE=========================================================================================-- **直接更新闪回数据的匹配字段：MERGE INTO bms_merchant a
USING (SELECT * FROM bms_merchant as of TIMESTAMP SYSTIMESTAMP - interval '20' minute ) b
ON ( a.id = b.id ) -- 条件是 A 相同
WHEN MATCHED THEN UPDATE SET a.merchant_name = b.merchant_name -- 匹配的时候，更新=======================================================================================-- 闪回查询特定时间节点的闪回表数据：select * from account as of timestamp to_timestamp('2014-04-22 08:00:00', 'yyyy-mm-dd hh24:mi:ss');=======================================================================================-- **闪回表：Flashback Table 直接闪回全表：可以恢复过去某一个时间的状态，包括表引索，触发器和约束。
【需要在表上启用行移动】alter table TEST_XIAOHANG enable row movement
flashback table TEST_XIAOHANG TO TIMESTAMP SYSTIMESTAMP - interval '10' minute ;=======================================================================================-- **备份闪回表数据：create table xxxxxxxx as SELECT * FROM xxxxxxxxxas of TIMESTAMP SYSTIMESTAMP - interval '200' minute ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
模糊 正则查询
select tx_data.* from (select a.identity_no as a,count(1) as b, wm_concat(to_char(a.create_date,'yyyymmdd' )) as c ,wm_concat(a.is_deposit_device) as d from bms_merchant a left join bms_id_white_list b on b.identity_no = a.identity_nowhere b.identity_no is null --and a.is_deposit_device in ('5','6')group by a.identity_no having count(1)>1 and to_char( max(a.create_date),'yyyymmdd' ) > '20170901'
) tx_data where regexp_like(tx_data.d,'.*[56].*');
分析函数等
-- 字符串正则匹配 ：
cast(regexp_substr(pids,'[^,]+',1,1) as int)-- 分析函数求占比 ：
ratio_to_report(capping_amount) over(partition by t1.type) -- 分组排序（写上标号123121234123） ：
row_number() over(partition by t2.report_generation_time,t2.type,t2.pid order by t2.capping_amount)
锁问题
-- 查看数据库中被锁的东东SELECT object_name,machine,s.sid,s.serial#
FROM gv$locked_object l,dba_objects o,gv$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid; select * from dba_jobs_running
-------------------------------------------
下面是网络的解决方案
-------------------------------------------1、查看数据库锁,诊断锁的来源及类型：
?
1
select object_id,session_id,locked_mode from v$locked_object;
ORACLE里锁有以下几种模式:
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive或者用以下命令： select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id SELECT lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC2、找出数据库的serial#,以备杀死： select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;3、杀死该session alter system kill session 'sid,serial#'0、查看是否开启归档模式
selectname,log_mode,open_mode from v$database;1.看看现在用了多少归档日志了（按百分比）：select * from v$flash_recovery_area_usage;
查看表空间
查看表空间：
select * from (
Select a.tablespace_name,
to_char(a.bytes/1024/1024,'99,999.999') total_bytes,
to_char(b.bytes/1024/1024,'99,999.999') free_bytes,
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes,
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%'use
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes/1024/1024,'99,999.999') total_bytes,
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
)
order by tablespace_name
Excel 操作
# =====================excel 函数用法=======================
* $ 区域的绝对应用 eg: $F$2:$F$28
匹配： =INDEX(B22:B33,MATCH(F2,C22:C33,0)) --match 先匹配行数 index 再取范围内 确定行数的值=VLOOKUP(F2,(B22:B33,C22:C33),1,FALSE) --匹配 条件2 的第一列为匹配项 条件3 为取值项
前端的
标签动画
<script>var title = document.title;var tit;function doTitle(){var num = parseInt(6*Math.random());var arr = new Array();arr[0] = '╭ (￣︶￣)ψ ';arr[1] = '(づ￣ 3￣)づ';arr[2] = '┌(￣◇￣)┘ ';arr[3] = 'ヘ(￣ω￣ヘ) ';arr[4] = '(┘￣ ￣)┘ ';arr[5] = '└(￣ ￣└) ';document.title = arr[num] +' 记得回来哦';}window.onblur = function() {doTitle();tit = setInterval(doTitle, 200);};window.onfocus = function() {document.title = title;clearInterval(tit);};
</script>
动画提示小人
<style type="text/css" id="wiz_custom_css">
.spig {display:block;width:150px;height:190px;position:absolute;top: -200px;left: 160px;z-index:9999;}
#message{font-family: 微软雅黑,"Microsoft YaHei", Georgia,Helvetica,Arial,sans-serif,宋体, PMingLiU,serif;font-size: 10.5pt;line-height: 1.5;color :#191919;border: 1px solid #c4c4c4;background:#ddd;-moz-border-radius:5px;-webkit-border-radius:5px;border-radius:5px;min-height:1em;padding:5px;top:-30px;position:absolute;text-align:center;width:auto !important;z-index:10000;-moz-box-shadow:0 0 15px #eeeeee;-webkit-box-shadow:0 0 15px #eeeeee;border-color:#eeeeee;box-shadow:0 0 15px #eeeeee;outline:none;
}
.mumu{width:150px;height:190px;cursor: move;background:url("/bms/static/images/spig.png") no-repeat;}
</style><script type="text/javascript" src="/bms/static/jquery/jquery-1.9.1.min.js"/><script type="text/javascript" >
console.log("您的到来是我的荣幸，谢谢你朋友");
console.log("呀!这么巧，你也在这里哈！交个朋友吧！我的QQ:1062767698");
</script> <script type="text/javascript" >$(document).ready(function ($) {//右键菜单$("#spig").mousedown(function (e) {if(e.which==3){showMessage("目前在线人数："+"只！",5000);}});$("#spig").bind("contextmenu", function(e) {return false;});//鼠标在消息上时$("#message").hover(function () {$("#message").fadeTo("100", 1);});//鼠标在上方时//$(".mumu").jrumble({rangeX: 2,rangeY: 2,rangeRot: 1}); //Jquery 抖动效果$(".mumu").mouseover(function () {$(".mumu").fadeTo("300", 0.3);msgs = ["经常来来看我，我就跟你玩~","本小助理可远观不可亵玩！", "我会隐身哦！嘿嘿！", "别动手动脚的，把手拿开！！", "再不把手拿开小心我横竖竖你！！", "主人，他摸我，呜呜呜呜~~~", "你把手拿开我就出来！"];var i = Math.floor(Math.random() * msgs.length);showMessage(msgs[i]);});$(".mumu").mouseout(function () {$(".mumu").fadeTo("300", 1)});//开始if (true) { //如果是主页var now = (new Date()).getHours();if (now > 0 && now <= 6) {showMessage(' 你是夜猫子呀？还不睡觉，明天起的来么你？', 6000);} else if (now > 6 && now <= 11) {showMessage(' 早上好，早起的鸟儿有虫吃噢！早起的虫儿被鸟吃，你是鸟儿还是虫儿？嘻嘻！', 6000);} else if (now > 11 && now <= 14) {showMessage(' 中午了，吃饭了么？不要饿着了，饿死了谁来挺我呀！', 6000);} else if (now > 14 && now <= 18) {showMessage(' 中午的时光真难熬！还好有你在！', 6000);} else {showMessage(' 快来逗我玩吧！我好无聊啊~~', 6000);}};$(".spig").animate({top: $(".spig").offset().top + 300,left: document.body.offsetWidth - 160},{queue: false,duration: 1000});//鼠标在某些元素上方时$("a").click(function () {//超链接被点击时showMessage('正在用吃奶的劲加载《<span style="color:#0099cc;">' + $(this).text() + '</span>》请稍候');});//无聊讲点什么window.setInterval(function () {msgs = ["好无聊哦，你都不陪我玩~", "…@……!………", "^%#&*!@*(&#)(!)(", "我是你的小小助理哦~_~", "我可爱吧！嘻嘻!~^_^!~~","谁淫荡呀?~谁淫荡?，你淫荡呀!~~你淫荡！~~","从前有座山，山上有座庙，庙里有个老和尚给小和尚讲故事，讲：“从前有座……”","喵。喵。。逗逗我吧。。"];var i = Math.floor(Math.random() * msgs.length);showMessage(msgs[i], 10000);}, 35000);//无聊动动window.setInterval(function () {msgs = ["北风飘丫飘~~", "乾坤大挪移！", "我飘过来了！~", "我飘过去了", "我得意地飘！~飘！~"];var i = Math.floor(Math.random() * msgs.length);s = [0.1, 0.2, 0.3, 0.4, 0.5, 0.6,0.7,0.75,-0.1, -0.2, -0.3, -0.4, -0.5, -0.6,-0.7,-0.75];var i1 = Math.floor(Math.random() * s.length);var i2 = Math.floor(Math.random() * s.length);$(".spig").animate({left: document.body.offsetWidth/2*(1+s[i1]),top: document.body.offsetHeight/2*(1+s[i1])},{duration: 2000,complete: showMessage(msgs[i])});}, 45000);//滚动条移动var spig_top = 50;var f = $(".spig").offset().top;$(window).scroll(function () {$(".spig").animate({top: $(window).scrollTop() + f +200},{queue: false,duration: 1000});});//鼠标点击时var stat_click = 0;$(".mumu").click(function () {if (!ismove) {stat_click++;if (stat_click > 4) {msgs = ["你有完没完呀？", "你已经摸我" + stat_click + "次了，人家脸都红色...","再摸，再摸我就叫我哥打你哟...", "非礼呀！救命！OH，My ladygaga"];var i = Math.floor(Math.random() * msgs.length);//showMessage(msgs[i]);} else {msgs = ["筋斗云！~我飞！", "我跑呀跑呀跑！~~", "别摸我了，再摸我就脸红了！", "惹不起你，我还躲不起你么？", "不要摸我了，我会告诉主人哥哥来打你的哦！", "干嘛动我呀！小心我咬你！"];var i = Math.floor(Math.random() * msgs.length);//showMessage(msgs[i]);}s = [0.1, 0.2, 0.3, 0.4, 0.5, 0.6,0.7,0.75,-0.1, -0.2, -0.3, -0.4, -0.5, -0.6,-0.7,-0.75];var i1 = Math.floor(Math.random() * s.length);var i2 = Math.floor(Math.random() * s.length);$(".spig").animate({left: document.body.offsetWidth/2*(1+s[i1]),top: document.body.offsetHeight/2*(1+s[i2])},{duration: 500,complete: showMessage(msgs[i])});} else {ismove = false;}});//显示消息函数 function showMessage(a, b) {if (b == null) b = 10000;$("#message").hide().stop();$("#message").html(a);$("#message").fadeIn();$("#message").fadeTo("1", 1);$("#message").fadeOut(b);};//拖动var _move = false;var ismove = false; //移动标记var _x, _y; //鼠标离控件左上角的相对位置$("#spig").mousedown(function (e) {_move = true;_x = e.pageX - parseInt($("#spig").css("left"));_y = e.pageY - parseInt($("#spig").css("top"));});$(document).mousemove(function (e) {if (_move) {var x = e.pageX - _x; var y = e.pageY - _y;var wx = $(window).width() - $('#spig').width();var dy = $(document).height() - $('#spig').height();if(x >= 0 && x <= wx && y > 0 && y <= dy) {$("#spig").css({top: y,left: x}); //控件新位置ismove = true;}}}).mouseup(function () {_move = false;});});</script><div id="spig" class="spig" style="top: 100px; left: 700 px;"><div id="message" style="display: block; opacity: 0.680405;"></div><div id="mumu" class="mumu" style="opacity: 0.680405;"></div></div>
更多推荐
各种日常操作
发布评论