MySQL精讲(二) |DML数据操作语句全解析

ゞ浴缸里的玫瑰 2023-02-15 10:09 15阅读 0赞

> **MySQL精讲系列文章(更新完毕)**  
> [《MySQL入门必看知识》][MySQL]  
> [《MySQL精讲(一) | DQL数据查询语句全解析》][MySQL_ _ DQL]  
> [《MySQL精讲(二) | DML数据操作语句全解析》][MySQL_ _ DML]  
> [《MySQL精讲(三) | DDL数据定义语句全解析 |数据类型和约束》][MySQL_ _ DDL_]  
> [《MySQL精讲(四) | TCL事务控制语句全解析|视图》][MySQL_ _ TCL]  
> [《MySQL精讲(五) | 存储过程和函数》][MySQL_ _]  
> [《MySQL精讲(六) | 流程控制》][MySQL_ _ 1]  
>                                                                                                [– by 凡尘阿凉][by]

### 文章目录 ###

*  一、插入insert
 *  二、修改update
 *  三、删除delete/truncate
 *  四、DML语句练习题

--------------------

# 一、插入insert #

方式一：`insert into 表名(列名,...) values(值1,...);`  
方式二： `insert into 表名 set 列名=值,列名=值...`

<table> 
 <thead> 
  <tr> 
   <th>比较</th> 
   <th>是否支持插入多行</th> 
   <th>是否支持子查询</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>方式一</td> 
   <td>√</td> 
   <td>×</td> 
  </tr> 
  <tr> 
   <td>方式二</td> 
   <td>√</td> 
   <td>×</td> 
  </tr> 
 </tbody> 
</table>

【插入方式一】
    # 1.插入的值的类型要与列的类型一致或兼容
    INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
    VALUE(13,'双笙','女','1999-02-15','13868666666',NULL,2);
    
    # 2.列的顺序可以调换
    INSERT INTO beauty(NAME,id,sex,borndate,phone,photo,boyfriend_id)
    VALUE('徐婕',14,'女','1999-02-16','13868666661',NULL,2);
    
    # 3.可以省略列名,列的顺序和表中列的顺序一致
    INSERT INTO beauty
    VALUE(16,'金克斯','女','2000-02-16','13868666661',NULL,2);
    
    # 4.支持子查询
    INSERT INTO beauty(id,NAME,phone)
    SELECT id,boyname,'110'
    FROM boys WHERE id<3; # 本例需将beauty表的主键删除
    ---------------------------------------------------------------
    【插入方式二】
    INSERT INTO beauty SET id=19,NAME='花花',phone='666';

# 二、修改update #

1、修改单表的记录语法:

`update 表名`  
`set 列=新值,列=新值,...`  
`where 筛选条件;`

执行顺序:`update > where > set`

2、修改多表的记录语法:  
`update 表1 别名`  
`[inner|left|right] join 表2 别名`  
`on 连接条件`  
`set 列=新值,列=新值,...`  
`where 筛选条件;`

【修改单表的记录】
    # 修改beautty表中姓周的女生电话为15888888888
    UPDATE beauty
    SET phone='15888888888'
    WHERE NAME LIKE '%周%';
    
    【修改多表的记录】
    # 将张无忌的女朋友的电话号改为2333
    UPDATE boys b1
    JOIN beauty b2 
    ON b1.id=b2.boyfriend_id
    SET b2.phone='2333'
    WHERE b1.boyName='张无忌';

# 三、删除delete/truncate #

**delete**

单表删除:`delete from 表名 where 筛选条件`

多表删除:  
`delete 表1别名,表2别名`  
`from 表1 别名`  
`inner|left|right join 表2 别名 on 连接条件`  
`where 筛选条件;`

**truncate**

清空单表：`truncate table 表名;`

注意：`truncate删除没有返回值，delete删除有返回值。`

<table> 
 <thead> 
  <tr> 
   <th>比较</th> 
   <th>是否可以加where条件</th> 
   <th>是否可以回滚</th> 
   <th>是否有返回值</th> 
   <th>效率</th> 
   <th>表中有自增长列时</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>delete</td> 
   <td>√</td> 
   <td>√</td> 
   <td>√</td> 
   <td>一般</td> 
   <td>用delete删除，再插入数据，自增长的值从断点开始</td> 
  </tr> 
  <tr> 
   <td>truncate</td> 
   <td>×</td> 
   <td>×</td> 
   <td>×</td> 
   <td>略高于delete</td> 
   <td>用truncate删除，再插入数据，自增长的值从1开始</td> 
  </tr> 
 </tbody> 
</table>

【delete】
    # 1.单表删除
    # 删除以9结尾的女生信息
    DELETE FROM beauty WHERE phone LIKE '%9';
    
    # 2.多表删除
    # 删除黄晓明的信息以及他女朋友的信息
    DELETE b1,b2
    FROM beauty b1
    INNER JOIN boys b2
    ON b1.boyfriend_id=b2.id
    WHERE b2.boyName='黄晓明';
    
    【truncate】
    # 仅支持单表删除全部数据
    TRUNCATE TABLE boys;

# 四、DML语句练习题 #

> 学习了DML语言语句的增删改，尝试完成下面的习题：![这里是引用][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70]  
> 答案：  
> `1、执行下面的sql语句`  
> CREATE TABLE my\_employees(  
> Id INT(10),  
> First\_name VARCHAR(10),  
> Last\_name VARCHAR(10),  
> Userid VARCHAR(10),  
> Salary DOUBLE(10,2)  
> );  
> CREATE TABLE users(  
> id INT,  
> userid VARCHAR(10),  
> department\_id INT  
> );  
> `2.`  
> DESC my\_employees  
> `3.`  
> 方式一:  
> INSERT INTO my\_employees  
> VALUES(1,‘patel’,‘Ralph’,‘Rpatel’,895),  
> (2,‘Dancs’,‘Betty’,‘Bdancs’,860),  
> (3,‘Biri’,‘Ben’,‘Bbiri’,1100),  
> (4,‘Newman’,‘Chad’,‘Cnewman’,750),  
> (5,‘Ropeburn’,‘Audrey’,‘Aropebur’,1550);  
> TRUNCATE TABLE my\_employees;  
> 方式一:  
> INSERT INTO my\_employees  
> SELECT 1,‘patel’,‘Ralph’,‘Rpatel’,895 UNION  
> SELECT 2,‘Dancs’,‘Betty’,‘Bdancs’,860 UNION  
> SELECT 3,‘Biri’,‘Ben’,‘Bbiri’,1100 UNION  
> SELECT 4,‘Newman’,‘Chad’,‘Cnewman’,750 UNION  
> SELECT 5,‘Ropeburn’,‘Audrey’,‘Aropebur’,1550;  
> `4.`  
> INSERT INTO users  
> VALUE(1,‘Rpatel’,10),  
> (2,‘Bdancs’,10),  
> (3,‘Bbiri’,20),  
> (4,‘Cnewman’,30),  
> (5,‘Aropebur’,40);  
> `5.`  
> UPDATE my\_employees  
> SET Last\_name=‘drelxer’  
> WHERE id=3;  
> `6.`  
> UPDATE my\_employees  
> SET salary=1000  
> WHERE salary<900;  
> `7.`  
> DELETE u,e  
> FROM users u  
> JOIN my\_employees e ON u.userid=e.userid  
> WHERE u.userid=‘Bbiri’;  
> `8.`  
> DELETE FROM my\_employees;  
> DELETE FROM users;  
> `9.`  
> SELECT \* FROM my\_employees;  
> SELECT \* FROM users;  
> `10.`  
> TRUNCATE TABLE my\_employees;

[MySQL]: https://guoqianliang.blog.csdn.net/article/details/106447977
[MySQL_ _ DQL]: https://guoqianliang.blog.csdn.net/article/details/106456675
[MySQL_ _ DML]: https://guoqianliang.blog.csdn.net/article/details/106560523
[MySQL_ _ DDL_]: https://guoqianliang.blog.csdn.net/article/details/106571679
[MySQL_ _ TCL]: https://guoqianliang.blog.csdn.net/article/details/106589677
[MySQL_ _]: https://guoqianliang.blog.csdn.net/article/details/106604374
[MySQL_ _ 1]: https://guoqianliang.blog.csdn.net/article/details/106627351
[by]: https://guoqianliang.blog.csdn.net/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200605085554352.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70