Giter VIP home page Giter VIP logo

mysql's Introduction

mysql

登录 ip mysql -h ip -P port -u user -p; SHOW VARIABLES WHERE Variable_name = 'port';

    mysql view
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER 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_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 存储过程
BEGIN INSERT INTO tb_order_id_history (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, ADDTIME, MODTIME, FROMID, LABLE, REMARK from tb_order_id;

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> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root"; mysql> flush privileges; /etc/init.d/mysql stop mysqld_safe --skip-grant-tables & mysql -u root use mysql; update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root'; flush privileges; quit;

    mysql backup
$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql] [uname] Your database username [pass] The password for your database (note there is no space between -p and the password) [dbname] The name of your database [backupfile.sql] The filename for your database backup [--opt] The mysqldump option e.g: one database $ mysqldump -u root -p Tutorials > tut_backup.sql more database $ mysqldump -u root -p --databases Tutorials Articles Comments > content_backup.sql all database $ mysqldump -u root -p --all-databases > alldb_backup.sql --add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump. --no-data: Dumps only the database structure, not the contents. --add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

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;

    //联合查询
select

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 ;

mysql's People

Watchers

James Cloos avatar mazhou avatar

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.