各种日常操作
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;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'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,' u
Group 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
==============================================================================
-- 参考：https://blog.csdn.net/wqc19920906/article/details/79791322
********mysql 动态行转列 ：********
BEGIN
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'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 u
Group 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())；
解释分析对比
explain
type:
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.org
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_merchant
as of TIMESTAMP SYSTIMESTAMP - interval '200' minute
where activation_date = '20170702'
)tx_data on tx_data.id = a.id
where 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 xxxxxxxxx
as 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_no
where 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 DESC
2、找出数据库的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>
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
