mysql知识点汇集

1、将两个表字段类型一致的数据合并到一个新表的命令。

INSERT into new_table(user_name,password,age) SELECT user_name,password,age FROM old_table1;

INSERT into new_table(user_name,password,age) SELECT user_name,password,age FROM old_table2;

 

2、对每一天的数据进行统计，并输出到一个excel文件中。

select * from (select 
    DATE_FORMAT(e.create_Time,'%Y年%m月%d日') as time,
    sum( e.actionorder_number) as '激活数',
    sum( e.neworder_number) as '新增数',
    sum( e.unsub_number ) as '退订数'
    
from extract_order_info e 
WHERE  
     e.create_Time BETWEEN '2018-10-01' and '2018-10-25'
GROUP BY time
ORDER BY time) f INTO OUTFILE "D:\1.xls" 

 3、连接数查看和优化

#查看实时连接数
show status like 'Threads%';  

#查看最大连接数
show variables like '%max_connections%';  

##查看连接超时时间
show variables like  '%timeout%';

###当前所有的连接数
show processlist;

show binary log

show binlog events in 'mysql-bin.000001'