mysql常用sql

1.mysql导出数据库：

   运行cmd，进入到命令行，执行命令

  a. mysqldump -u root -p test_db> news20161204.sql

   mysqldump -u 用户名 -p 数据库名 >需要导出的sql文件路径

  b.sql文件将会生成在命令行的当前目录，如果进入的是用户目录，则生成在用户目录下

2.mysql创建视图

create or repace  view

as

select  a.selectlist ,b.selectlist from table a ,table bwhere

or repace 为可选，

a.selectlist ,b.selectlist 为查询参数

where 条件

seleclist是查出来的字段会作为视图的字段，因此很多都采用别名如 :

create or replace view cathectic_avice_view
as
select
workdate,
periods,
sum(totalmoney) as totalmoneys,
sum(ball1) as ball1, 
sum(ball2) as ball2, 
sum(ball3) as ball3, 
sum(ball4) as ball4, 
sum(ball5) as ball5, 
sum(ball6) as ball6, 
sum(ball7) as ball7, 
sum(ball8) as ball8, 
sum(ball9) as ball9, 
sum(ball10) as ball10,
sum(ball11) as ball11,
sum(ball12) as ball12,
sum(ball13) as ball13,
sum(ball14) as ball14,
sum(ball15) as ball15,
sum(ball16) as ball16,
sum(ball17) as ball17,
sum(ball18) as ball18,
sum(ball19) as ball19,
sum(ball20) as ball20,
sum(ball21) as ball21,
sum(ball22) as ball22,
sum(ball23) as ball23,
sum(ball24) as ball24,
sum(ball25) as ball25,
sum(ball26) as ball26,
sum(ball27) as ball27,
sum(ball28) as ball28,
sum(ball29) as ball29,
sum(ball30) as ball30,
sum(ball31) as ball31,
sum(ball32) as ball32,
sum(ball33) as ball33,
sum(ball34) as ball34,
sum(ball35) as ball35,
sum(ball36) as ball36,
sum(ball37) as ball37,
sum(ball38) as ball38,
sum(ball39) as ball39,
sum(ball40) as ball40,
sum(ball41) as ball41,
sum(ball42) as ball42,
sum(ball43) as ball43,
sum(ball44) as ball44,
sum(ball45) as ball45,
sum(ball46) as ball46,
sum(ball47) as ball47,
sum(ball48) as ball48,
sum(ball49) as ball49,
sum(ballmax) as ballmax,         
sum(ballmix) as  ballmix,                        
sum(ballgry) as  ballgry,         
sum(ballred) as ballred,         
sum(ballblue) as ballblue,        
sum(ballgreen) as ballgreen,       
sum(ballsingle) as ballsingle,      
sum(balldouble) as balldouble,      
sum(ballmouse) as ballmouse,       
sum(ballcow) as ballcow,         
sum(balltiger) as balltiger,       
sum(ballrabbit) as ballrabbit,      
sum(balldragon)  as balldragon,      
sum(ballsnake) as ballsnake,       
sum(ballhorse) as ballhorse,       
sum(ballsheep) as ballsheep,       
sum(ballmonkey) as ballmonkey,      
sum(ballcock) as ballcock,        
sum(balldog) as balldog,         
sum(ballpig) as ballpig,
sum(ballmousespe) as ballmousespe,
sum(ballcowspe) as ballcowspe,
sum(balltigerspe) as balltigerspe,
sum(ballrabbitspe) as ballrabbitspe,
sum(balldragonspe) as balldragonspe,
sum(ballsnakespe) as ballsnakespe,
sum(ballhorsespe) as ballhorsespe,
sum(ballsheepspe)as ballsheepspe,
sum(ballmonkeyspe) as ballmonkeyspe,
sum(ballcockspe)as ballcockspe,
sum(balldogspe)as balldogspe,
sum(ballpigspe) as  ballpigspe
from cathectic_ball group by workdate,periods