mysql常用语句

给某个字段添加另一个字段的值

UPDATE product_notice SET title = CONCAT(product_name) where (ISNULL(title) =1 or LENGTH(trim(title)) =0);

 

如果需要赋值拼接使用空格进行分隔

UPDATE product_notice SET title = CONCAT(product_name, ' ', content) where (ISNULL(title) =1 or LENGTH(trim(title)) =0);

这样就把两个值连接起来了

(ISNULL(title) =1 or LENGTH(trim(title)) =0) 表示条件为null或者控制符的更新

 

去重

 一个用户有多个订单 根据用户id去重

select * from sys_user_info as sui
where EXISTS( 
select 1 from sign_order as so where so.user_id = sui.id
)

或者
select distinct sui.id from sys_user_info as sui
 join sign_order as so on so.user_id = sui.id
 
或者
select  sui.id, count(so.id) from sys_user_info as sui
 join sign_order as so on so.user_id = sui.id
 GROUP BY sui.id 

 

空或者空字符查询处理

查询不为空或者控制符的数据(以job_id 为条件)

select * from sys_user_info as sui where 
ISNULL(job_id)=0 AND LENGTH(TRIM(job_id)) >0

 

查询为空或者控制符的数据(以job_id 为条件)

select * from sys_user_info as sui where 
(ISNULL(job_id) =1 or LENGTH(trim(job_id)) =0)

 

两个表之间赋值

UPDATE users
JOIN temp_users ON users.id = temp_users.id
SET users.email = temp_users.email

把temp_users表的email字段值 赋值到 users表的email字段中

 

HAVING的使用

SELECT num_no, num, MIN(id) as min_id
    FROM student
    GROUP BY num_no, num
    HAVING COUNT(*) > 1

上面语句是查询num_no和num组合相同的数据，having起到了组合相同的作用。如果不用having，只使用group by分组，会把所有num_no和num相同的数据都查出来。

 

删除

delete t1 from student as t1
inner join (
 SELECT num_no, num, MIN(id) as min_id
    FROM student
    GROUP BY num_no, num
    HAVING COUNT(*) > 1
        ) t2 on t1.num_no = t2.num_no and t1.num = t2.num and t1.id > t2.min_id

MIN(id) as min_id 和 t1.id > t2.min_id 取一条数据