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(客户名称) ASc,
客户名称,
是否口袋导入FROM`销售客户资料`GROUP BY客户名称HAVINGc> 1) tmpSET `销售客户资料`.状态 = '删除'
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表中)
UPDATEtbl_users aINNER JOIN(SELECT user_id, sum( pay_amount ) ASamountFROMtbl_users_pay_detailsGROUP BY user_id ) b ON a.id = b.user_id
SET a.pay_total =b.amount;
或者updatetbl_users a,(select
user_id,sum(pay_amount) asamountfromtbl_users_pay_detailsgroup by user_id)bset a.pay_total=b.amountwhere a.id=b.user_id或者UPDATEtbl_users aSET 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 预算表.Iddeclare 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,
预算表.材质要求=twhere 预算表.项目号 =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 intos;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 intos;end if;UPDATE 机加工件物资管理 SET 退回数量 = s WHERE Id =old.机加工件物资管理Id;end if;END