Mysql基础操作简单整理

1、基本命令
USE crm_db 
show databases；
show tables；
清空表 ：TRUNCATE TABLE '表名'
创建表：CREATE TABLE systree LIKE systreebak0515
新建表并导入记录： CREATE TABLE 新表 SELECT * FROM 旧表
删除表：DROP TABLE table_name；DROP TABLE IF EXISTS taskbakbak;
显示表：SHOW TABLES LIKE "%task%";  
显示表结构：SHOW CREATE TABLE task_1015bak;  DESC task_1015bak ; 

表重命名：ALTER TABLE systree RENAME TO systrexs;
表添加字段：ALTER TABLE tb_order_comment  ADD content VARCHAR(100)
表删除字段： ALTER TABLE task_1015bak  DROP COLUMN content 


2、聚集函数：
SUM()
AVG()
COUNT()
MAX()
MIN()
order by
group by （查询使用：having ）




3、联表查询 
SELECT ABS(sum1.value1-total.value1)  FROM (SELECT SUM(em_show)+SUM(pm_show)+SUM(bm_show) AS value1
 FROM  fengchao_process_account_day201306  WHERE DATE='20130617' )sum1
  ,(SELECT SUM(display) AS value1 FROM  fengchao_core_account_day201306 WHERE DATE='20130617' )total


4、查询结果中使用if判断
SELECT if(ABS(sumv.value1-total.value1),0,10)  FROM (SELECT SUM(em_csm)+SUM(pm_csm)+SUM(bm_csm) AS value1
 FROM  fengchao_process_account_month2013  WHERE DATE='201306' )sumv 
  ,(SELECT SUM(click_pay) AS value1 FROM  fengchao_core_account_month2013 WHERE DATE='201306' )total


SELECT  IF(SUM(click_pay + v_pay)=0,0,SUM(click_money + v_money)/SUM(click_pay + v_pay)) AS fc_r_val
FROM fengchao_core_account_day201305
WHERE account_id IN(5940051,6076287)
ORDER BY account_id,date


4’、另一种if判断 
      update custfund cf, (select cusid,sum(clickpay) clickpay from lastweekcusdaysttt where date<=curdate and date>sevendaysago group by cusid) t1 
   set cf.consumeratio=case cf.consumeavg when 0 then -20   else ROUND((t1.clickpay-cf.consumeavg*7)*100/(cf.consumeavg*7)) end
   , cf.consumedays=case t1.clickpay when 0 then -1   else cf.account*7/t1.clickpay end
   ,cf.consumeavg=t1.clickpay/7 where cf.custid=t1.cusid;



5、查询导出
mysql -ucrm -p123456  -Dtrend -e " SELECT  email FROM  member WHERE module_id =7">module_member


/home/mysql/mysql_compass/bin/mysql -uzhaodengqin -p5s1qQX6DiqZr -h10.42.125.22 -P3001 -Dcompass_realtime -e " ">xxxx

文件导入：
/home/mysql/mysql_weihu/bin/mysql -ucrm -p123456   -Dka_visit  -e "load data local infile 'tb_user.txt' into table tb_user  character set utf8 "

character set utf8  ：对中文的处理




6、mysqldump 
     mysqldump -u root -p密码 dbname  --no-create-info >d:\test.sql
    1、导出數據库為dbname的表结构（其中用戶名為root,密码為dbpasswd,生成的脚本名為db.sql）
             mysqldump -uroot -pdbpasswd -d dbname >db.sql;  
    2、导出數據库為dbname某张表(test)结构
    mysqldump -uroot -pdbpasswd -d dbname test>db.sql;
    3、导出數據库為dbname所有表结构及表數據（不加-d）
    mysqldump -uroot -pdbpasswd  dbname >db.sql;
    4、导出數據库為dbname某张表(test)结构及表數據（不加-d）
    mysqldump -uroot -pdbpasswd dbname test>db.sql;


7
INSERT INTO managerrightbak(managerid,funcgroupid) 
SELECT SUBSTRING(path,61,10)+0,157 FROM systree s,managerinfo m WHERE  m.managerid=SUBSTRING(path,31,10) AND SUBSTRING(path,61,10)!=0
 AND NOT EXISTS(SELECT 1 FROM managerright r WHERE r.managerid=SUBSTRING(path,61,10) AND funcgroupid=157)


8、添加索引
use crm_rights;
ALTER TABLE `tb_pos_sec_0` ADD INDEX tb_pos_sec_0_sec_id ( `sec_id` );
ALTER TABLE `tb_custacct_sec_0` ADD INDEX tb_custacct_sec_0_sec_id ( `sec_id` );
    ALTER TABLE `tb_custacct_sec_1` ADD INDEX tb_custacct_sec_1_sec_id ( `sec_id` );

USE compass_web;
DROP INDEX idx_status ON task;
DROP INDEX idx_level ON task;
CREATE INDEX idx_status_level_failtimes ON task (status,level,fail_times);
CREATE INDEX idx_userid ON task (user_id);


9、在一个机器上，操作特定机器的数据：
/home/mysql/mysql_compass/bin/mysql  -h10.48.29.50  -P8166  -ucrm -p123456 -Dorigin -e 'select * from tb_account   '>liyuan.test


10、查看数据表大小
 use information_schema;  
SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS DATA  FROM TABLES;
SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS DATA  FROM TABLES WHERE table_schema='compass_web'  ;
SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS DATA  FROM TABLES WHERE table_schema='compass_web'  AND table_name='task_1015bak';  


1、数据从线上导出，并导入到测试环境：
 /home/mysql/mysql_compass/bin/mysql -ubaoliyuan -peicumeagooshuoci -h10.42.125.22 -P3002 -Dcompass_web -e  'select * from task   '>task
/home/mysql/mysql_compass/bin/mysql  -h10.48.29.50  -P8166  -ucrm -p123456 -Dcompass_web  -e "load data local infile 'task' into table task "


2、修改时间
需要使用 root账号的密码   （重置root密码的链接 http://ocean.baidu.com/）
修改日期：
时间设定成2009年5月10日的命令如下：
#date -s 05/10/2009

修改时间：
将系统时间设定成上午10点18分0秒的命令如下。
#date -s 10:18:00 


3) select 中待参数


SELECT @ROW := @ROW + 1 AS ROW ,result.totalNum,result.url FROM 
(SELECT SUM(num) AS totalNum, url AS url FROM  tb_op_report_compass
WHERE  DATE BETWEEN '2013-11-24'  AND  '2013-11-30'
GROUP BY url ORDER BY totalNum ) result
,(SELECT @ROW := 0) r  


4）字符串分隔
SUBSTRING_INDEX(str,delim,count) 
 返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数，返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数，返回最后的分隔符到右边的所有字符(从右边数)。 
 mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2);
         -> 'www.mysql'
 mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2);
         -> 'mysql.com'


5）表去重
表 innocentbak  根据  ip 进行去重
ALTER IGNORE TABLE innocentbak ADD UNIQUE idx_ip (ip);


6）截取字符串
 /compass/realtime/indicator/searchRealTimeIndicatorByCondition.action


 SUBSTRING(SUBSTRING_INDEX(url,".action",1),9)


 /realtime/indicator/searchRealTimeIndicatorByCondition