MYSQL高级技巧

不念不忘少年蓝@ 2022-10-27 01:40 173阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70][]

INSERT IGNORE INTO t_dept(deptno,dname,loc)
    VALUES (40,'企划部','北京'),
    (50,'培训部','上海'),
    (60,'后勤部','武汉'),
    (70,'技术部','广州'),
    (80,'市场部','香港');

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1][]

-- empno字段违反了唯一约束,会更新ip
    INSERT  INTO t_emp_ip(id,empno,ip) VALUES
    (2,8004,'192.168.1.100'),
    (3,8005,'192.168.1.101'),
    (4,8006,'192.168.1.102'),
    (5,8001,'192.168.1.103')
    ON DUPLICATE KEY UPDATE ip = VALUES(ip);

**查询工资比empno=7499高的数据**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 4][]

SELECT e.`empno`,e.`ename`,e.`deptno` FROM t_emp e
    LEFT JOIN t_dept d ON e.`deptno`=d.`deptno`
    AND d.`deptno`=10;
    
    SELECT e.`empno`,e.`ename`,e.`deptno` FROM t_emp e
    LEFT JOIN t_dept d ON e.`deptno`=d.`deptno`
    WHERE d.`deptno`=10;

**使用表连接修改能同时修改两张表.修改SALES部门员工薪水=10000,同时将dname改为销售部 **

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 5][]

**使用表连接删除能同时删除多张表的数据,适用于有关联关系、一对多关系等数据. 删除e表中部门是销售部的员工,同时删除d表中dname=销售部的部门**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 6][]

DELETE e,d FROM t_emp e JOIN t_dept d
    ON e.`deptno`=d.`deptno` AND d.`dname`='销售部';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 7][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 8][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70]: /images/20221024/50e94b09635e4073a5f54dd12309d66c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1]: /images/20221024/450c1ef7495442e295dd4c2cee7d8d30.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 2]: /images/20221024/76fe1b6eeef440c8840c903172c298af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 3]: /images/20221024/32c9385ce9de40519851d9bfd947bae1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 4]: /images/20221024/89459abd587b4562aa6945211edc9ecb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 5]: /images/20221024/127d01180ad24085ae1700602fae302c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 6]: /images/20221024/09013377c40d480a94d844825e370ec4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 7]: /images/20221024/dc33c5225c3a456fa0e9648e4e36275d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 8]: /images/20221024/a8f4d94fe042491e808b44d674c1ba86.png