mysql常用命令备份

#关闭外键关联

SET FOREIGN_KEY_CHECKS = 0

##导出多个表,需要注意的是: 表名两边不能有多个空格

mysqldump -uroot -p dbname table1 table2 table3 >mmm.sql

##条件导出sql
 

mysqldump -uroot -proot --no-create-info --databases movie --tables mv --where="id > '10193'"  >mmm.sql

##导出sql避免单引号
 

mysqldump -u root -p --no-create-db --no-create-info --complete-insert --compatible=mssql --default-character-set=utf8 --skip-opt --compact --extended-insert=false database table|sed "s/\\\'/''/g">table.sql

#mysql 授权root远程访问 IDENTIFIED BY '654321' 修改密码

GRANT ALL PRIVILEGES ON *.* TO 'back'@'192.168.1.1' IDENTIFIED BY 'back' WITH GRANT OPTION;
FLUSH PRIVILEGES;

#root 密码忘记

  1,停止服务

  2,找到mysqld.cnf: /etc/mysql/mysql.conf.d/mysqld.cnf

  3,到[mysqld]下面添加一行 skip-grant-table

  4,直接mysql -uroot登录之后修改密码

#后台执行MySQL导入

nohup mysql -uroot -proot table< /home/sql/table.sql

#查看正在进行的sql语句

show processlist;
show full processlist;

#查看索引

show index from tablename

#server.cnf位置(centos)
/etc/my.cnf.d/server.cnf
#慢查询查询
#状态

show variables  like '%slow_query_log%';

#时间

show variables like 'long_query_time%';

#开启慢查询

##命令行：
set global slow_query_log_file='/var/lib/mysql/test-10-226-slow.log';
set global long_query_time=1;
##修改文件：
找到配置文件，在[mysqld]下面添加:
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

#查看表引擎

show create table tablename;

#批量改变表引擎--得到修改sql,然后分别执行

SELECT CONCAT( 'ALTER TABLE ', table_schema, '.', table_name, ' ENGINE=InnoDB; #ENGINE=', ENGINE ) AS '# 待执行引擎转换的 TABLES_LIST' FROM information_schema.TABLES WHERE table_schema = 'your_database' and ENGINE != "InnoDB";

##大小写敏感查询--binary

where binary `voucher`='{$voucher}'

##数据表去重:song去重,如果singerId和songI都一样,删除最小id之外的)

delete from song where id not in (
    select t.min_id from 
	(select min(id) as min_id,`singerId`,songId from song group by `singerId`,songId having count(*) > 1) as t             
    );

##条件查出重复记录(查出来下面四个字段相同,再加上一些限制条件的数据)

select * from table a where (a.invoice_number,a.shipment_date,a.recipient_address,a.shipper_address) in (select invoice_number,shipment_date,recipient_address,shipper_address from table where (mtw_charge = 0 or mtw_charge is null) and service_type = 'Ground' group by invoice_number, shipment_date,recipient_address,shipper_address having count(*) > 1)

##导入数据库报错: ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NULL'
设置以下参数后再导入
set global max_allowed_packet=10000000000;##查找字段为:1,2,3,4,5这样格式的数据包含21的方法:

SELECT * FROM v_mv WHERE CONCAT(',',tags,',') REGEXP ',21,'

##随机查询几条数据

SELECT
    *
FROM
    `album` AS t1
    JOIN (
    SELECT
        ROUND(
            RAND( ) * ( ( SELECT MAX( id ) FROM `album` ) - ( SELECT MIN( id ) FROM `album` ) ) + ( SELECT MIN( id ) FROM `album` )
        ) AS id
    ) AS t2
WHERE
    t1.id >= t2.id
ORDER BY
    t1.id
    LIMIT 3;

##删除B表中A表不存在的记录

DELETE FROM B WHERE NOT EXISTS ( SELECT 1 FROM A WHERE B.id = A.id )

##查看A表存在B表没有的记录

select * from  A where (select count(1) from B where A.ID=B.ID) = 0;

B.id = A.id 就是A， B表主外键关系的字段##去重统计--统计经过ip去重之后的记录

SELECT COUNT(DISTINCT `ip`) AS 'count' FROM aff_openlog WHERE `aff` = '84509'

##update的时候拼接字符串

update table set profile_name = concat("pound_",id) where ISNULL(profile_name);

##分组求和

select user_id, sum(`cost`) as total from table where number = '004666390' group by user_id;

##子查询不支持limit,报错: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

不执行的语句:

select * from `table1` where `id` in (select table1_id from `table2` where `created_at` > '2010-05-01' limit 2,2);

可以执行的语句:

select * from `table1` where `id` in (select t.table1_id from(select table1_id from `table2` where `created_at` > '2010-05-01' limit 2,2) t);

但是没找到mysql为什么会如此,硬要加一层子查询才行 

##TRIM去除数据两边的制定字符

#去除两边的逗号

update bills set shipper_address = TRIM(BOTH ',' FROM shipper_address);

#去除左边的逗号

update bills set shipper_address = TRIM(LEADING ',' FROM shipper_address);

#去除右边的逗号

update bills set shipper_address = TRIM(TRAILING ',' FROM shipper_address);  

#根据一个表更新另一个表的字段, 如果这个表该字段不为空的话

update ups_adjustbills a set base_charge_desc = (select base_charge_desc from upsbills where id = a.carrierbill_id) where id in (select t.id from(select aa.id from ups_adjustbills aa left join upsbills bb on aa.carrierbill_id=bb.id where bb.base_charge_desc != '') t);

#查找某一些字段有重复数据的数据

SELECT
	payer_account,
	count( payer_account ),
	shipment_date,
	count( shipment_date ),
	recipient_address,
	count( recipient_address ),
	shipper_address,
	count( shipper_address ) 
FROM
	our_fedexbills { $ WHERE } 
GROUP BY
	payer_account,
	shipment_date,
	recipient_address,
	shipper_address 
HAVING
	( count( payer_account ) > 1 ) 
	AND ( count( shipment_date ) > 1 ) 
	AND ( count( recipient_address ) > 1 ) 
	AND ( count( shipper_address ) > 1 )

#select  SUM的时候出现一些.00001或者.999999之类的小数, 处理成decimal的小数

select CAST(SUM(quote_total) AS DECIMAL (12, 2)) as total_quote from shipments where order_id = '3246';

下面链接也有很实用的sql

MySQL之——查询重复记录、删除重复记录方法大全_冰 河的博客-CSDN博客_mysql 查询重复数据