mysql语句批量插入、查询插入、查询更新、统计并分页

批量 insert

1. 查询后插入 insert into select

insert into A(col1, col2) select col1, col2 from B;

2. 批量插入更新 insert into on duplicate key update

INSERT INTO t  
   (id, age)  
VALUES  
   (3, 28),
   (4, 29)  
ON DUPLICATE KEY UPDATE
   id = VALUES(id),
   age = VALUES(age);

Union & Union all用法

• Union 合并并去重

• Union all 合并不去重

update select语句

UPDATE bkb_seller bs 
INNER JOIN (
  SELECT new_user,seller_id,create_time from lc_seller_change_history  
  where  id = 11
) a 
SET bs.user_id =a.new_user,bs.create_time = a.create_time 
where bs.id = a.seller_id

经纬度距离计算

Distance = fmt.Sprintf("select ROUND(6378.138*2*ASIN(SQRT(POW(SIN((%s*PI()/180-latitude*PI()/180)/2),2)+COS(%s*PI()/180)*COS(latitude*PI()/180)*POW(SIN((%s*PI()/180-longitude*PI()/180)/2),2))),1) AS 'distance' from table1", t.Latitude, t.Latitude, t.Longitude)

统计全部，输出limit

SELECT SQL_CALC_FOUND_ROWS name FROM table1 where name like '%name%' limit 10,0;
SELECT FOUND_ROWS() 'nums';