注:(1)内连接可以不使用ON或者WHERE;外连接必须使用ON子句,否则会报错!!!

       (2)UPDATE确定数据源头(或者说,UPDATE子句确定“待操作数据集”),WHERE子句:对“待操作数据集”做二次筛选。

       (3)案例4:这个例子说明,UPDATE语句可以同时修改多张表。。。

       (4)子查询不能在UPDATE语句中使用的原因:首先,在UPDATE中使用子查询,这会是相关子查询,效率很低;然后,MySQL不允许对要更改或要删除记录的数据表做子查询(很显然,动脑子想一想也明白了);

目录

一:UPDATE语句简介

二:案例:不涉及表连接  的案例(这部分不难)

三:案例:涉及表连接


一:UPDATE语句简介

● UPDATE子句,选择要修改的表;UPDATE子句只执行一次;

● IGNORE关键字可写可不写,作用是在写入数据的时候,如果遇到冲突就直接忽略。

● WHERE子句:筛选要修改的记录;

● ORDER BY子句的应用场合:把筛选出的记录进行排序。比如,当修改记录的主键的时候,给主键值都加1的时候,就需要把数据降序排列,这样才不会在修改主键的时候发生唯一性冲突;(自然如果给主键值都减1,就需要把数据升序排列了);

LIMIT子句:这儿的LIMIT子句只能写一个参数;

下面的执行顺序:UPDATE子句→WHERE子句→ORDER BY子句→LIMIT子句→SET子句;


二:案例:不涉及表连接  的案例(这部分不难)

案例1:把每个员工的编号和上司的编号+1,用ORDER BY子句完成;

这个例子很简单,不涉及多表查询;

UPDATE t_emp SET empno=empno+1,mgr=mgr+1
ORDER BY empno DESC;

……………………………………………………

案例2:把月收入排在前三名的员工,底薪减100元,用LIMIT子句完成

注意,LIMIT子句的用法。

这个例子很简单,不涉及多表查询;

UPDATE t_emp SET sal=sal-100
ORDER BY sal+IFNULL(comm,0) DESC
LIMIT 3;

……………………………………………………

案例3:把10部门中,工龄超过20年的员工,底薪增加200元,WHERE子句

这个例子很简单,不涉及多表查询;

UPDATE t_emp SET sal=sal+200
WHERE deptno=10 AND FLOOR(DATEDIFF(NOW(),hiredate)/365)>=20;


三:案例:涉及表连接

案例4:把ALLEN调往RESEARCH部门,职务调整为ANALYST;

这个案例中,涉及到了多张表,RESEARCH部门的编号不知道;

最直接的思路:使用子查询。

UPDATE t_emp e SET job="ANALYST",deptno=(SELECT d.deptno FROM t_dept d WHERE d.dname="RESEARCH")
WHERE e.ename="ALLEN";

但是上面的做法中,子查询是相关子查询,如果需要修改的不止ALLEN一人,那么【SELECT d.deptno FROM t_dept d WHERE d.dname="RESEARCH"】将会执行很多次。。。

涉及多张表的时候,需要引入表连接:

下面的两种表连接都是内连接的语法:内连接的,其中包括使用ON子句,和使用ON子句。

利用表连接的方式实现:这种骚操作。。。(这儿非常重要!!!!!,是核心!!!)

UPDATE t_emp e JOIN t_dept d
SET e.deptno=d.deptno,e.job="ANALYST",d.loc="成都"
WHERE e.ename="ALLEN" AND d.dname="RESEARCH";

分析:执行过程是,现将俩表做笛卡尔积(因为没有使用ON子句啦)从t_emp表中筛选出e.name="ALLEN"这条记录,从t_dept表中筛选出d.dname="RESEARCH"这条记录,其实,这所谓的两条记录在【笛卡尔积】中是在一条记录上的;此时,已经分别在两个表中各获得了一条记录,然后分别在获取的这两条记录上执行执行【SET e.deptno=d.deptno,e.job="ANALYST",d.loc="成都"】。然后,对此条记录的操作就能影响到原表上。

这个表连接的例子其实并不难,很简单,多想一下表连接的那个结果集,,,,,

同时,这个例子说明,UPDATE语句可以同时修改多张表。。。

……………………………………………………

案例5:把底薪低于公司平均底薪的员工,底薪增加150元;

【公司的平均底薪】和【谁的底薪低于公司的平均底薪】不知道;

我的解决方案:还是这种很骚的操作,,,

UPDATE t_emp e1 JOIN (SELECT AVG(sal) aa FROM t_emp) e2
SET e1.sal=e1.sal+150
WHERE e1.sal<e2.aa;

注:这个例子和【案例4】结合后,可以发现,这种表连接的骚操作,可以应用于多条记录的情况。

即,重复下【案例4】中的话,这些在笛卡尔积上挑选出的记录,对这些记录的更改可以影响到原表上。

通过这个例子,更加说明,UPDATE子句是指执行一次的。

标准做法:和我的做法类似,只是这儿将条件放在了ON子句中,我的做法是将条件放在了WHERE子句中。

UPDATE t_emp e1 JOIN (SELECT AVG(sal) aa FROM t_emp) e2 ON e1.sal<e2.aa
SET e1.sal=e1.sal+150;

……………………………………………………

UPDATE语句的表连接的第三种方式:外连接的语法,外连接的时候,条件只能写在ON子句中,,,而且ON子句不能少!!!!!

案例6:把没有部门的员工,或者SALES部门低于2000底薪的员工,都调往20部门。

涉及到多张表,需要表连接;张三没有部门,连接时需要保留,所以需要外连接;

第一种错误方案:

UPDATE t_emp e LEFT JOIN (SELECT deptno FROM t_dept WHERE dname="SALES") dd 
ON e.deptno=NULL OR (e.sal<=2000 AND e.deptno=dd.deptno)
SET e.deptno=20;

可以发现,实际效果出错了如BLACK的部门也没改成20了。首先,判断字段是不是空,需要IS NULL 而不是=NULL;

动动脑子:在左连接的时候,左边的记录都会被保留下来!!!!!!!!!!所以,这儿的结果是错误的,deptno全变成20了。。。

第二种错误方案:错误原因,经过多次测试,发现左连接和右连接必须要有ON子句,否则会报错。这个需要后续确定,并在外连接的那篇博客中标记一下。

UPDATE t_emp e LEFT JOIN (SELECT deptno FROM t_dept WHERE dname="SALES") dd 
SET e.deptno=20
WHERE e.deptno IS NULL OR (e.sal<=2000 AND e.deptno=dd.deptno);

标准做法:这个例子很典型,调理清晰,好好想想,,,

这儿,UPDATE确定数据源头(或者说,UPDATE子句确定“待操作数据集”),WHERE子句:对“待操作数据集”做二次筛选。

UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
SET e.deptno=20
WHERE e.deptno IS NULL OR (d.dname="SALES" AND e.sal<2000);

 

 

 

 

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐