数据导出

mysqldump -uroot -p [db] [table] -w "id=50[条件]" --no-create-info > [文件名].sql

查询

使用变量

set @cardNo = 'test0012'; -- 变量
SELECT * from mkt_bz_card where card_no = @cardNo;

Explain

explain select * from emp where name = 'Jefabc';

批量新增
https://dev.mysql.com/doc/refman/5.7/en/insert.html

1. replace into

-- 先删除，再新增
replace into test_tbl (id,name) values (1,'a'),(2,'b'),(3,'y');

2. insert into on duplicate key update

insert into test_tbl (id,name) values (1,'a'),(2,'b'),(x,'y') on duplicate key update name=values(name);

创建临时表，先更新临时表，然后从临时表中update

create temporary table tmp(id int(4) primary key, name varchar(50));
insert into tmp values (0,'gone'), (1,'xx'),(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

when case 条件更新

UPDATE t_user
SET 
age = CASE id 
    WHEN 1 THEN 23 
    WHEN 2 THEN 24 
    WHEN 3 THEN 25 
END,
name = CASE id 
    WHEN 1 THEN '张飞2' 
    WHEN 2 THEN '关羽2' 
    WHEN 3 THEN '刘备2' 
END
WHERE id IN (1,2,3)

INSERT ... SELECT Statement

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

显示进程

show full processlist;