各种日常操作
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>