mysql数据库经典sql

1.查询重复数据

select food_id,count() as count from t_food_nutrient group by food_id having count>1;

2.将查询结果当做过滤条件

select t. from (select b,c from A) as t where t.a>0;

3.将查询结果进行编辑

update user u1,(select id,mobile from user where id=1)as u2
set u1.mobile=u2.mobile
where u1.id=u2.id
或者
UPDATE cell_customer c set c.depart_id=
(SELECT d.new_id from t_s_depart d WHERE d.id=c.depart_id)

4.将查询结果进行新增

insert into user(name,mobile)
select name,mobile from user
或者
insert into coms.sys_user(id,password,realname,citizen_no,citizen_file)
SELECT b.id,b.password,b.realname,u.citizen_no,u.citizen_file from coms.t_s_base_user b LEFT JOIN coms.t_s_user u on b.id=u.id

5.group_concat 修改默认符号‘,’改为自定义符号’：’

SELECT t.name,t.code,(SELECT GROUP_CONCAT(n.meal_number,’:’) from t_classify_meal_number n where n.code=t.code)
from t_classify t GROUP BY t.id;

6.表新增字段，修改表字段为另一张表字段

update leyin_im.room r INNER JOIN leyin_member.user u ON r.user_id=u.id set r.room_cover=u.avatar