登录 ip mysql -h ip -P port -u user -p; SHOW VARIABLES WHERE Variable_name = 'port';
- mysql view
CREATE VIEW view_orderid_today
AS (select * from tb_order_id
where (cast(now() as date) = cast(tb_order_id
.MODTIME
as date)));
CREATE VIEW view_orderid_period
AS (select * from tb_order_id_period
where (tb_order_id_period
.FROMID
<> 3));
CREATE VIEW view_user_account
AS (select * from (tb_user_account
m
left join tb_capital_conf
s
on((m
.ACCOUNTID
= s
.ACCOUNTID
))));
CREATE VIEW view_orderid_tomorow
AS (select * from tb_order_id_period
where ((tb_order_id_period
.FROMID
= 3) and (tb_order_id_period
.MODTIME
> (now() - interval 24 hour))));
- mysql 存储过程
TRUNCATE TABLE tb_order_id; DELETE FROM tb_order_id_period WHERE PRDEND<NOW()-INTERVAL 2 DAY; INSERT INTO tb_order_id (SEQUENCE, ORDERID, USERID, PGROUPID, ACCOUNTID, TRADEID, POLICYID, PNAME, POLICYPARAM, DIRTYPE, STOCKSET, DEALSTOCK, STARTTIME, ENDTIME, ISTEST, BUYCOUNT, BUYAMOUNT, PERCENT, STATUS, FLAG_SYSTEM, FLAG_USER, ADDTIME, MODTIME, FROMID, LABLE, REMARK) SELECT SEQUENCE, ORDERID, USERID, PGROUPID, ACCOUNTID, TRADEID, POLICYID, PNAME, POLICYPARAM, DIRTYPE, STOCKSET, DEALSTOCK, STARTTIME, ENDTIME, ISTEST, BUYCOUNT, BUYAMOUNT, PERCENT, STATUS, FLAG_SYSTEM, FLAG_USER, NOW(), now(), FROMID, LABLE, REMARK FROM tb_order_id_period WHERE FLAG_SYSTEMFLAG_USERVISIBLE!=0 and DATE(NOW()) BETWEEN PRDSTART AND PRDEND;
SET @rescode = 10; select @rescode as rescode; END
- mysql 开权限
- mysql backup
Back up your MySQL Database with Compress $ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz] $ gunzip [backupfile.sql.gz]
Restoring your MySQL Database $ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql] e.g: $ mysql -u root -p Tutorials < tut_backup.sql
$ mysql -u root -p Enter password: mysql> CREATE DATABASE metastore; mysql> USE metastore; mysql> SOURCE /usr/lib/hive/scripts/metastore/upgrade/mysql/hive-schema-n.n.n.mysql.sql;
- //联合查询
show_time_segment, sum( cdn_size) cdn_size ,sum( p2p_pc_size) p2p_pc_size ,sum( p2p_tv_size) p2p_tv_size, sum( p2p_box_size) p2p_box_size ,sum( p2p_mo_size) p2p_mo_size,sum( cde_pc_size) cde_pc_size ,sum( cde_tv_size) cde_tv_size, sum( cde_box_size) cde_box_size ,sum( cde_mo_size) cde_mo_size, sum( lpsize) lpsize,sum(lcsize) lcsize,sum(lsize_cde) lsize_cde,sum(up_rtmfp) up_rtmfp,sum(up_cde) up_cde,sum( conn_node_times) conn_node_times ,sum( get_node_times) get_node_times ,sum( conn_cde_times) conn_cde_times ,sum( get_cde_times) get_cde_times ,sum( share_rep_count)share_rep_count, sum(act) act
from
(
select DATE_FORMAT(concat( (select date(time_stamp)),' ' , (select hour (time_stamp)) ) ,'%Y-%m-%d %H')show_time_segment , sum( cdn_size) cdn_size ,sum( p2p_pc_size) p2p_pc_size ,sum( p2p_tv_size) p2p_tv_size, sum( p2p_box_size) p2p_box_size ,sum( p2p_mo_size) p2p_mo_size,sum( cde_pc_size) cde_pc_size ,sum( cde_tv_size) cde_tv_size, sum( cde_box_size) cde_box_size ,sum( cde_mo_size) cde_mo_size, sum( lpsize) lpsize,sum(lcsize) lcsize,sum(lsize_cde) lsize_cde,sum(up_rtmfp) up_rtmfp,sum(up_cde) up_cde,sum( conn_node_times) conn_node_times ,sum( get_node_times) get_node_times ,sum( conn_cde_times) conn_cde_times ,sum( get_cde_times) get_cde_times ,sum( share_rep_count)share_rep_count, sum(act) act from share_rate_vod_unicom where ( time_stamp >= date_format(' 2017-9-3 0:00:00 ', '%Y-%m-%d %H:%i:%s') and time_stamp <= date_format('2017-9-4 23:59:59','%Y-%m-%d %H:%i:%s') ) and ( ((select hour('time_stamp') ) >= 0 and (select hour('time_stamp') ) < 24) ) and country=0 group by show_time_segment
union all
select DATE_FORMAT(concat( (select date(time_stamp)),' ' , (select hour (time_stamp)) ) ,'%Y-%m-%d %H')show_time_segment , sum( cdn_size) cdn_size ,sum( p2p_pc_size) p2p_pc_size ,sum( p2p_tv_size) p2p_tv_size, sum( p2p_box_size) p2p_box_size ,sum( p2p_mo_size) p2p_mo_size,sum( cde_pc_size) cde_pc_size ,sum( cde_tv_size) cde_tv_size, sum( cde_box_size) cde_box_size ,sum( cde_mo_size) cde_mo_size, sum( lpsize) lpsize,sum(lcsize) lcsize,sum(lsize_cde) lsize_cde,sum(up_rtmfp) up_rtmfp,sum(up_cde) up_cde,sum( conn_node_times) conn_node_times ,sum( get_node_times) get_node_times ,sum( conn_cde_times) conn_cde_times ,sum( get_cde_times) get_cde_times ,sum( share_rep_count)share_rep_count, sum(act) act from share_rate_vod_telecom where ( time_stamp >= date_format(' 2017-9-3 0:00:00 ', '%Y-%m-%d %H:%i:%s') and time_stamp <= date_format('2017-9-4 23:59:59','%Y-%m-%d %H:%i:%s') ) and ( ((select hour('time_stamp') ) >= 0 and (select hour('time_stamp') ) < 24) ) and country=0 group by show_time_segment
union all
select DATE_FORMAT(concat( (select date(time_stamp)),' ' , (select hour (time_stamp)) ) ,'%Y-%m-%d %H')show_time_segment , sum( cdn_size) cdn_size ,sum( p2p_pc_size) p2p_pc_size ,sum( p2p_tv_size) p2p_tv_size, sum( p2p_box_size) p2p_box_size ,sum( p2p_mo_size) p2p_mo_size,sum( cde_pc_size) cde_pc_size ,sum( cde_tv_size) cde_tv_size, sum( cde_box_size) cde_box_size ,sum( cde_mo_size) cde_mo_size, sum( lpsize) lpsize,sum(lcsize) lcsize,sum(lsize_cde) lsize_cde,sum(up_rtmfp) up_rtmfp,sum(up_cde) up_cde,sum( conn_node_times) conn_node_times ,sum( get_node_times) get_node_times ,sum( conn_cde_times) conn_cde_times ,sum( get_cde_times) get_cde_times ,sum( share_rep_count)share_rep_count, sum(act) act from share_rate_vod_other where ( time_stamp >= date_format(' 2017-9-3 0:00:00 ', '%Y-%m-%d %H:%i:%s') and time_stamp <= date_format('2017-9-4 23:59:59','%Y-%m-%d %H:%i:%s') ) and ( ((select hour('time_stamp') ) >= 0 and (select hour('time_stamp') ) < 24) ) and country=0 group by show_time_segment
union all
select DATE_FORMAT(concat( (select date(time_stamp)),' ' , (select hour (time_stamp)) ) ,'%Y-%m-%d %H')show_time_segment , sum( cdn_size) cdn_size ,sum( p2p_pc_size) p2p_pc_size ,sum( p2p_tv_size) p2p_tv_size, sum( p2p_box_size) p2p_box_size ,sum( p2p_mo_size) p2p_mo_size,sum( cde_pc_size) cde_pc_size ,sum( cde_tv_size) cde_tv_size, sum( cde_box_size) cde_box_size ,sum( cde_mo_size) cde_mo_size, sum( lpsize) lpsize,sum(lcsize) lcsize,sum(lsize_cde) lsize_cde,sum(up_rtmfp) up_rtmfp,sum(up_cde) up_cde,sum( conn_node_times) conn_node_times ,sum( get_node_times) get_node_times ,sum( conn_cde_times) conn_cde_times ,sum( get_cde_times) get_cde_times ,sum( share_rep_count)share_rep_count, sum(act) act from share_rate_liv_unicom where ( time_stamp >= date_format(' 2017-9-3 0:00:00 ', '%Y-%m-%d %H:%i:%s') and time_stamp <= date_format('2017-9-4 23:59:59','%Y-%m-%d %H:%i:%s') ) and ( ((select hour('time_stamp') ) >= 0 and (select hour('time_stamp') ) < 24) ) and country=0 group by show_time_segment
union all
select DATE_FORMAT(concat( (select date(time_stamp)),' ' , (select hour (time_stamp)) ) ,'%Y-%m-%d %H')show_time_segment , sum( cdn_size) cdn_size ,sum( p2p_pc_size) p2p_pc_size ,sum( p2p_tv_size) p2p_tv_size, sum( p2p_box_size) p2p_box_size ,sum( p2p_mo_size) p2p_mo_size,sum( cde_pc_size) cde_pc_size ,sum( cde_tv_size) cde_tv_size, sum( cde_box_size) cde_box_size ,sum( cde_mo_size) cde_mo_size, sum( lpsize) lpsize,sum(lcsize) lcsize,sum(lsize_cde) lsize_cde,sum(up_rtmfp) up_rtmfp,sum(up_cde) up_cde,sum( conn_node_times) conn_node_times ,sum( get_node_times) get_node_times ,sum( conn_cde_times) conn_cde_times ,sum( get_cde_times) get_cde_times ,sum( share_rep_count)share_rep_count, sum(act) act from share_rate_liv_telecom where ( time_stamp >= date_format(' 2017-9-3 0:00:00 ', '%Y-%m-%d %H:%i:%s') and time_stamp <= date_format('2017-9-4 23:59:59','%Y-%m-%d %H:%i:%s') ) and ( ((select hour('time_stamp') ) >= 0 and (select hour('time_stamp') ) < 24) ) and country=0 group by show_time_segment
union all
select DATE_FORMAT(concat( (select date(time_stamp)),' ' , (select hour (time_stamp)) ) ,'%Y-%m-%d %H')show_time_segment , sum( cdn_size) cdn_size ,sum( p2p_pc_size) p2p_pc_size ,sum( p2p_tv_size) p2p_tv_size, sum( p2p_box_size) p2p_box_size ,sum( p2p_mo_size) p2p_mo_size,sum( cde_pc_size) cde_pc_size ,sum( cde_tv_size) cde_tv_size, sum( cde_box_size) cde_box_size ,sum( cde_mo_size) cde_mo_size, sum( lpsize) lpsize,sum(lcsize) lcsize,sum(lsize_cde) lsize_cde,sum(up_rtmfp) up_rtmfp,sum(up_cde) up_cde,sum( conn_node_times) conn_node_times ,sum( get_node_times) get_node_times ,sum( conn_cde_times) conn_cde_times ,sum( get_cde_times) get_cde_times ,sum( share_rep_count)share_rep_count, sum(act) act from share_rate_liv_other where ( time_stamp >= date_format(' 2017-9-3 0:00:00 ', '%Y-%m-%d %H:%i:%s') and time_stamp <= date_format('2017-9-4 23:59:59','%Y-%m-%d %H:%i:%s') ) and ( ((select hour('time_stamp') ) >= 0 and (select hour('time_stamp') ) < 24) ) and country=0 group by show_time_segment
union all
select DATE_FORMAT(concat( (select date(time_stamp)),' ' , (select hour (time_stamp)) ) ,'%Y-%m-%d %H')show_time_segment , sum( cdn_size) cdn_size ,sum( p2p_pc_size) p2p_pc_size ,sum( p2p_tv_size) p2p_tv_size, sum( p2p_box_size) p2p_box_size ,sum( p2p_mo_size) p2p_mo_size,sum( cde_pc_size) cde_pc_size ,sum( cde_tv_size) cde_tv_size, sum( cde_box_size) cde_box_size ,sum( cde_mo_size) cde_mo_size, sum( lpsize) lpsize,sum(lcsize) lcsize,sum(lsize_cde) lsize_cde,sum(up_rtmfp) up_rtmfp,sum(up_cde) up_cde,sum( conn_node_times) conn_node_times ,sum( get_node_times) get_node_times ,sum( conn_cde_times) conn_cde_times ,sum( get_cde_times) get_cde_times ,sum( share_rep_count)share_rep_count, sum(act) act from share_rate_dl_unicom where ( time_stamp >= date_format(' 2017-9-3 0:00:00 ', '%Y-%m-%d %H:%i:%s') and time_stamp <= date_format('2017-9-4 23:59:59','%Y-%m-%d %H:%i:%s') ) and ( ((select hour('time_stamp') ) >= 0 and (select hour('time_stamp') ) < 24) ) and country=0 group by show_time_segment
union all
select DATE_FORMAT(concat( (select date(time_stamp)),' ' , (select hour (time_stamp)) ) ,'%Y-%m-%d %H')show_time_segment , sum( cdn_size) cdn_size ,sum( p2p_pc_size) p2p_pc_size ,sum( p2p_tv_size) p2p_tv_size, sum( p2p_box_size) p2p_box_size ,sum( p2p_mo_size) p2p_mo_size,sum( cde_pc_size) cde_pc_size ,sum( cde_tv_size) cde_tv_size, sum( cde_box_size) cde_box_size ,sum( cde_mo_size) cde_mo_size, sum( lpsize) lpsize < ,sum(lcsize) lcsize,sum(lsize_cde) lsize_cde,sum(up_rtmfp) up_rtmfp,sum(up_cde) up_cde,sum( conn_node_times) conn_node_times ,sum( get_node_times) get_node_times ,sum( conn_cde_times) conn_cde_times ,sum( get_cde_times) get_cde_times ,sum( share_rep_count)share_rep_count, sum(act) act from share_rate_dl_telecom where ( time_stamp >= date_format(' 2017-9-3 0:00:00 ', '%Y-%m-%d %H:%i:%s') and time_stamp <= date_format('2017-9-4 23:59:59','%Y-%m-%d %H:%i:%s') ) and ( ((select hour('time_stamp') ) >= 0 and (select hour('time_stamp') ) < 24) ) and country=0 group by show_time_segment
union all
select DATE_FORMAT(concat( (select date(time_stamp)),' ' , (select hour (time_stamp)) ) ,'%Y-%m-%d %H')show_time_segment , sum( cdn_size) cdn_size ,sum( p2p_pc_size) p2p_pc_size ,sum( p2p_tv_size) p2p_tv_size, sum( p2p_box_size) p2p_box_size ,sum( p2p_mo_size) p2p_mo_size,sum( cde_pc_size) cde_pc_size ,sum( cde_tv_size) cde_tv_size, sum( cde_box_size) cde_box_size ,sum( cde_mo_size) cde_mo_size, sum( lpsize) lpsize,sum(lcsize) lcsize,sum(lsize_cde) lsize_cde,sum(up_rtmfp) up_rtmfp,sum(up_cde) up_cde,sum( conn_node_times) conn_node_times ,sum( get_node_times) get_node_times ,sum( conn_cde_times) conn_cde_times ,sum( get_cde_times) get_cde_times ,sum( share_rep_count)share_rep_count, sum(act) act from share_rate_dl_other where ( time_stamp >= date_format(' 2017-9-3 0:00:00 ', '%Y-%m-%d %H:%i:%s') and time_stamp <= date_format('2017-9-4 23:59:59','%Y-%m-%d %H:%i:%s') ) and ( ((select hour('time_stamp') ) >= 0 and (select hour('time_stamp') ) < 24) ) and country=0 group by show_time_segment
) as T_m
group by show_time_segment ;