MySQL 我自己常用的语句汇总

1，更新，根据一个表更新另一个表，比如批量同步外键

　　方法一：

　　update 更新表 set 字段 = (select 参考数据 from 参考表 where  参考表.id = 更新表.id);

　　update table_2 m  set m.column = (select column from table_1 mp where mp.id= m.id);

　　方法二：

　　update table_1 t1,table_2 t2 set t1.column = t2.column where t1.id = t2.pid;

2，视图，把相同字段的表合起来　　

　　SELECT `b`.`Id` AS `Id`,`b`.`状态` AS `状态` FROM `培训体系之部门培训` `b` UNION 
　　SELECT `c`.`Id` AS `Id`, `c`.`状态` AS `状态` FROM `培训体系之安全培训` `c` UNION 
　　SELECT `d`.`Id` AS `Id`, `d`.`状态` AS `状态` FROM `培训体系之理念培训` `d`;

3，触发器，更新订单表的状态时，同时根据订单号更新订单明细表的状态

　　条件【触发：After；插入[ ]，更新[√]，删除[ ]】　　

　　BEGIN

　　　　if(new.状态='新') then
　　　　　　update 销售订单明细 set 状态='新' where 订单号=new.订单号 and 状态<>'删除';
　　　　end if;

　　END

4，触发器，更新一个时间戳

　　条件【触发：Before；插入[ ]，更新[√]，删除[ ]】

　　BEGIN

　　　　if(new.仓库（箱）!=old.仓库（箱） or new.车间（箱）!=old.车间（箱） ) then
　　　　　　set NEW.更新时间= set new.签到时间=DATE_FORMAT(NOW(),'%x-%m-%d %H:%i:%s');;
　　　　end if; 

　　END

5，触发器，获取刚刚添加记录的自增id

　　条件【触发：After；插入[√ ]，更新[]，删除[ ]】

　　new.id

 6，查询，分组查询，关键字用逗号隔开

　　select class_id, group_concat(name) frome student group by class_id;

7，查询，查重

　　SELECT 某列A,COUNT(*) FROM 表名 GROUP BY 某列A HAVING COUNT(*) > 1//查看重复数据的条数

8，去重，从口袋助理的app里导入自己的系统一些客户资料，但是客户名重复了，需要删除。　　

UPDATE `销售客户资料`, 
(
    SELECT
        count(客户名称) AS c,
        客户名称,
        是否口袋导入
    FROM
        `销售客户资料`
    GROUP BY
        客户名称
    HAVING
        c > 1
) tmp

SET `销售客户资料`.状态 = '删除' 

WHERE 
     `销售客户资料`.客户名称 = tmp.客户名称 
    AND `销售客户资料`.是否口袋导入 = '是' 
    AND `销售客户资料`.客户编码 != '';

 9，mysql筛选条件为限定长度的字符串

select * from a where length(user_id) = 8 ;

 10，触发器注意事项

Before与After区别：before：(insert、update)可以对new进行修改。
after不能对new进行修改。 两者都不能修改old数据。

11，查重

SELECT * FROM `销售客户资料` WHERE `销售客户资料`.`客户名称` IN 
(
select t.`客户名称` from `销售客户资料` t group by t.`客户名称` having count(*)>1
) 
ORDER BY `销售客户资料`.`客户名称` DESC LIMIT 2;

 12，binlog恢复SQL

C:\Users\Administrator>mysqlbinlog --no-defaults  --base64-output=decode-rows -v E:\backuplog.000003>mysql-bin000003.sql

 13，触发器定义变量注意一定要定义在最前面，比如↓

#更新欠款
#说明：销售订单中未付金额列发生更新时，将相应用户的未付金额之和更新到销售客户资料表中对应的欠款列。

BEGIN

declare m DECIMAL(10,2); 

declare n DECIMAL(10,2); 




if(new.未付金额 <> old.未付金额) then

  select 销售客户资料.欠款 into m from 销售客户资料 where 销售客户资料.客户编码=old.客户编码;
    
  select SUM(销售订单.未付金额) into n from 销售订单 where 销售订单.客户编码=old.客户编码;

  UPDATE 销售客户资料 SET 销售客户资料.欠款 = n WHERE 销售客户资料.客户编码 = old.客户编码;
    
  #将以上操作记录如下
    INSERT INTO 触发器操作记录  
     (触发器,              触发源表,  触发源Id,   触发源索引列, 触发源索引值,  触发源列,   触发目标表,     触发目标索引列, 触发目标索引值,  触发目标列,  触发行为描述,            触发时间) VALUES
     ('GengXinQianKuan', '销售订单', old.Id,   '客户编码',   old.客户编码, '未付金额', '销售客户资料', '客户编码',     old.客户编码,   '欠款',     CONCAT(m, ' → ', n),   DATE_FORMAT(NOW(),'%Y年%c月%e日 %H时%i分%s秒') );
     
end if;



END

 14，mysql更新语句（根据a表和b表的关联关系，将b表中的计算结果更新到a表中）

UPDATE tbl_users a
INNER JOIN ( 
    SELECT user_id, sum( pay_amount ) AS amount 
    FROM tbl_users_pay_details 
    GROUP BY user_id ) b ON a.id = b.user_id 
SET a.pay_total = b.amount;

或者

update tbl_users a,(
select 
 user_id,sum(pay_amount) as amount
from tbl_users_pay_details
group by user_id
)b
set a.pay_total=b.amount
where a.id=b.user_id

或者

UPDATE tbl_users a 
SET a.pay_total = ( SELECT sum( b.pay_amount ) FROM tbl_users_pay_details b WHERE a.id = b.user_id );

 15，mysql查询某一个字段是否包含中文汉字

在使用mysql时候，某些字段会存储中文字符，或是包含中文字符的串，查询出来的方法是：

　　SELECT column FROM table WHERE length(column)!=char_length(column)
原理其实很简单，当字符集为UTF-8，并且字符为中文时，length() 和 char_length() 两个方法返回的结果是不相同的。

 16，触发器，Before Update

# 用友存货编码
# 逻辑：当[申购采购]表更改[用友存货编码]列时，其列[名称，规格，材质要求]，重新从[存货档案]表提取相应列[存货大类名称，描述，材质]进行同步，并且以[项目号]为关联字段同步[预算表]的相应列[用友存货编码，项目名称，规格型号，材质要求]
BEGIN

#name 存货档案.存货大类名称
declare n VARCHAR(100);

#description 存货档案.描述
declare d VARCHAR(255);

#texture 存货档案.材质
declare t VARCHAR(255);

#id 预算表.Id
declare i int(11);


if(old.用友存货编码<>'' and new.用友存货编码<>old.用友存货编码) then 

  select 存货档案.存货大类名称 into n from 存货档案 where 存货档案.存货大类编码 = new.用友存货编码;
    select 存货档案.描述         into d from 存货档案 where 存货档案.存货大类编码 = new.用友存货编码;
    select 存货档案.材质         into t from 存货档案 where 存货档案.存货大类编码 = new.用友存货编码;
    
    select 预算表.Id             into i from 预算表   where 预算表.项目号         = old.项目号;
    
    update 预算表 set 
      预算表.用友存货编码 = new.用友存货编码, 
        预算表.项目名称 = n,
        预算表.规格型号 = d,
        预算表.材质要求 = t 
    where 预算表.项目号 = old.项目号;
    
    set new.名称 = n;
    set new.规格 = d;
    set new.材质要求 = t;    
    
    #增加修改记录    
    insert into 申购采购记录 (序号, 用户, 用户操作) VALUES (old.Id, '触发器YongYouCunHuoBianMa@申购采购', CONCAT('名称 → ', n, ' | 规格 → ', d, ' | 材质要求 → ', t, ' | ', DATE_FORMAT(NOW(),'%Y年%c月%e日 %H时%i分%s秒')));
    insert into 预算表记录 (序号, 用户, 用户操作) VALUES (i, '触发器YongYouCunHuoBianMa@申购采购', CONCAT('用友存货编码 → ', new.用友存货编码, ' | 名称 → ', n, ' | 规格 → ', d, ' | 材质要求 → ', t, ' | ', DATE_FORMAT(NOW(),'%Y年%c月%e日 %H时%i分%s秒')));
end if;


END

 

17，触发器，After Update，定义变量怎么判空赋值

#同步 机加工件
BEGIN

declare s int(11); 

if(new.状态 <> old.状态) then

  select sum(数量) into s from 部门物资管理 where 机加工件物资管理Id=old.机加工件物资管理Id and 状态='出库';
    
    if (s is null) then 
        select 0 into s; 
    end if;
    
  UPDATE 机加工件物资管理 SET 领用数量 = s WHERE Id = old.机加工件物资管理Id;
    
    
    ###########################################################################################################
    
    select sum(数量) into s from 部门物资管理 where 机加工件物资管理Id=old.机加工件物资管理Id and 状态='退库';
    
    if (s is null) then 
        select 0 into s; 
    end if;
  
    UPDATE 机加工件物资管理 SET 退回数量 = s WHERE Id = old.机加工件物资管理Id;
    

     
end if;



END