简单的mysql语句

SELECT CASE WHEN photo IS NULL THEN 'img/1.jpg' ELSE photo END FROM web_cus

判断图片是否为null， CASE WHEN photo IS NULL THEN 'img/1.jpg'   ELSE photo END

为null就给一个默认值photo 字段名，ELSE 若不为null用已有的

 

连表更新，一表的字段更新到另一表中去

update case_order s, sys_user c set s.user_order_name = c.name where s.user_order_id = c.id;

 

联表插入 表不能写缩写名称， 如comp_infos_statistics 写成comp

    INSERT INTO comp_infos_supplement (company_name, company_infos_id)

SELECT company_name,id AS company_infos_id  FROM comp_infos

 

清理数据库重复数据

1查询重复数据

 SELECT id,company_name,count(1) as c FROM comp_infos GROUP BY company_name having c > 1

 

2应该保留的数据

         SELECT MIN(id) min_id ,company_name FROM comp_infos GROUP BY company_name

 

3删除重复数据

    DELETE FROM comp_infos WHERE id NOT IN (SELECT temp.min_id FROM (

SELECT MIN(id) min_id FROM comp_infos_supplement GROUP BY company_name)AS temp);