SQL-DML(12)数据操纵语言

数据操纵语言：
 插入：insert
 修改：update
 删除：delete

1.插入语句

1. 方式一插入的语法：（常用的插入方式）
  insert into 表名（列名1，列名2，...)values(值1，值2...);
  
  方式二插入的语法：（不常用）
  insert into 表名
  set 列名=值，列名=值...
  
2.注意：（1）插入的值的类型要和列的类型一致或兼容
        (2)不可以为null的列必须插入值。
        (3)列的顺序可以调换，但必须写出列名
        (4)列的个数和值的个数必须一致
        (5)可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致。

√1-1. 小试牛刀（嘻嘻，不要脸）

案例一：不省略列名，并且不全插

 mysql> insert into beauty (name,sex,borndate,phone,boyfriend_id) values('杨紫','女','1992-11-06',1884626857,6);
Query OK, 1 row affected (0.24 sec)

mysql> select * from beauty ;                                                                          +----+------------+------+---------------------+-------------+-------+--------------+
| id | name       | sex  | borndate            | phone       | photo | boyfriend_id |
+----+------------+------+---------------------+-------------+-------+--------------+
|  1 | 柳岩       | 女   | 1988-02-03 00:00:00 | 18209876577 | NULL  |            8 |
|  2 | 苍老师     | 女   | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 |
|  3 | Angelababy | 女   | 1989-02-03 00:00:00 | 18209876567 | NULL  |            3 |
|  4 | 关晓彤     | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            2 |
|  5 | 周冬雨     | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            9 |
|  6 | 周芷若     | 女   | 1988-02-03 00:00:00 | 18209876577 | NULL  |            1 |
|  7 | 岳灵珊     | 女   | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 |
|  8 | 小昭       | 女   | 1989-02-03 00:00:00 | 18209876567 | NULL  |            1 |
|  9 | 双儿       | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            9 |
| 10 | 王语嫣     | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            4 |
| 11 | 夏雪       | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            9 |
| 12 | 赵敏       | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            1 |
| 13 | 王菲       | 女   | 1969-08-08 00:00:00 | 18202318320 | NULL  |            5 |
| 14 | 张柏芝     | 女   | 1980-05-24 00:00:00 | 15920424850 | NULL  |            5 |
| 15 | 杨紫       | 女   | 1992-11-06 00:00:00 | 1884626857  | NULL  |            6 |
+----+------------+------+---------------------+-------------+-------+--------------+
15 rows in set (0.00 sec)

案例二：省略列名的插入。

mysql> insert into boys values(6,'张一山',666);
Query OK, 1 row affected (0.21 sec)

mysql> select * from boys;
+----+-----------+--------+
| id | boyName   | userCP |
+----+-----------+--------+
|  1 | 张无忌    |    100 |
|  2 | 鹿晗      |    800 |
|  3 | 黄晓明    |     50 |
|  4 | 段誉      |    300 |
|  5 | 谢霆锋    |   1000 |
|  6 | 张一山    |    666 |
+----+-----------+--------+
6 rows in set (0.00 sec)

案例三：使用语法二的方式插入

mysql> insert into boys set id = 7 ,boyname = '张翰',usercp = 500;
Query OK, 1 row affected (0.09 sec)

mysql> select * from boys;
+----+-----------+--------+
| id | boyName   | userCP |
+----+-----------+--------+
|  1 | 张无忌    |    100 |
|  2 | 鹿晗      |    800 |
|  3 | 黄晓明    |     50 |
|  4 | 段誉      |    300 |
|  5 | 谢霆锋    |   1000 |
|  6 | 张一山    |    666 |
|  7 | 张翰      |    500 |
+----+-----------+--------+
7 rows in set (0.00 sec)

√ 方式一 PK 方式2

 1.方式一支持多行插入，方式二只支持单行插入
   insert into beauty values（...），(...),(...)...;
 2.方式一 支持子查询，方式二不支持。
  insert into beauty（ID，name，phone）
  select ID  boyname ,'123456' from boys ;
  这样能实现将另一个表的数据插入当前表。

2. 修改语句

语法：
 1.修改单表的记录（√√√）
  update 表名
  set 列=新值，列=新值，...
  where 筛选条件；
 2.修改多表的记录【补充，了解】（SQL199语法）
  update 表1，别名
  inner/left/right join表2，别名
  on 连接条件
  set 列=值...
  where 筛选条件；     

√ 2-1 练习

案例一：单表修改练习

mysql> #将安迪的电话号码修改为‘13102609278’
mysql> update beauty set phone ='13104209278' where name='安迪';
Query OK, 1 row affected (0.20 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from beauty;
+----+------------+------+---------------------+-------------+-------+--------------+
| id | name       | sex  | borndate            | phone       | photo | boyfriend_id |
+----+------------+------+---------------------+-------------+-------+--------------+
|  1 | 柳岩       | 女   | 1988-02-03 00:00:00 | 18209876577 | NULL  |            8 |
|  2 | 苍老师     | 女   | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 |
|  3 | Angelababy | 女   | 1989-02-03 00:00:00 | 18209876567 | NULL  |            3 |
|  4 | 关晓彤     | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            2 |
|  5 | 周冬雨     | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            9 |
|  6 | 周芷若     | 女   | 1988-02-03 00:00:00 | 18209876577 | NULL  |            1 |
|  7 | 岳灵珊     | 女   | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 |
|  8 | 小昭       | 女   | 1989-02-03 00:00:00 | 18209876567 | NULL  |            1 |
|  9 | 双儿       | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            9 |
| 10 | 王语嫣     | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            4 |
| 11 | 夏雪       | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            9 |
| 12 | 赵敏       | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            1 |
| 13 | 王菲       | 女   | 1969-08-08 00:00:00 | 18202318320 | NULL  |            5 |
| 14 | 张柏芝     | 女   | 1980-05-24 00:00:00 | 15920424850 | NULL  |            5 |
| 15 | 杨紫       | 女   | 1992-11-06 00:00:00 | 1884626857  | NULL  |            6 |
| 16 | 安迪       | 女   | 1987-01-01 00:00:00 | 13104209278 | NULL  |         NULL |
+----+------------+------+---------------------+-------------+-------+--------------+
16 rows in set (0.00 sec)

案例二：多表修改练习

mysql> #修改没有男朋友资料的女神的男朋友编号到为6号，哈哈！
mysql> update beauty left join boys b on boyfriend_id =b.id set boyfriend_id = 7 where b.id is null;
Query OK, 6 rows affected (0.07 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from beauty;
+----+------------+------+---------------------+-------------+-------+--------------+
| id | name       | sex  | borndate            | phone       | photo | boyfriend_id |
+----+------------+------+---------------------+-------------+-------+--------------+
|  1 | 柳岩       | 女   | 1988-02-03 00:00:00 | 18209876577 | NULL  |            7 |
|  2 | 苍老师     | 女   | 1987-12-30 00:00:00 | 18219876577 | NULL  |            7 |
|  3 | Angelababy | 女   | 1989-02-03 00:00:00 | 18209876567 | NULL  |            3 |
|  4 | 关晓彤     | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            2 |
|  5 | 周冬雨     | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            7 |
|  6 | 周芷若     | 女   | 1988-02-03 00:00:00 | 18209876577 | NULL  |            1 |
|  7 | 岳灵珊     | 女   | 1987-12-30 00:00:00 | 18219876577 | NULL  |            7 |
|  8 | 小昭       | 女   | 1989-02-03 00:00:00 | 18209876567 | NULL  |            1 |
|  9 | 双儿       | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            7 |
| 10 | 王语嫣     | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            4 |
| 11 | 夏雪       | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            7 |
| 12 | 赵敏       | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            1 |
| 13 | 王菲       | 女   | 1969-08-08 00:00:00 | 18202318320 | NULL  |            5 |
| 14 | 张柏芝     | 女   | 1980-05-24 00:00:00 | 15920424850 | NULL  |            5 |
| 15 | 杨紫       | 女   | 1992-11-06 00:00:00 | 1884626857  | NULL  |            6 |
| 16 | 安迪       | 女   | 1987-01-01 00:00:00 | 13104209278 | NULL  |            7 |
+----+------------+------+---------------------+-------------+-------+--------------+
16 rows in set (0.00 sec)

3. 删除语句

 方式一：delete
 1.单表删除（√√√）
 语法  ：
 delete from 表名 where 筛选条件
 2.多表的删除【补充了解 以199语法为标准】
 语法  ：
 delete 表1别名，表2别名
 from 表1 别名
 inner/left/right join 表2 别名 on 连接条件
 where 筛选条件；
  
 方式二：truncate
 语法：truncate table 表名；

√ 3-1 练习

案例一：单表删除

#删除手机号以8结尾的女神信息(结果把安迪给删了）
mysql> delete from beauty where phone like '%8';
Query OK, 1 row affected (0.35 sec)

mysql> select * from beauty;
+----+------------+------+---------------------+-------------+-------+--------------+
| id | name       | sex  | borndate            | phone       | photo | boyfriend_id |
+----+------------+------+---------------------+-------------+-------+--------------+
|  1 | 柳岩       | 女   | 1988-02-03 00:00:00 | 18209876577 | NULL  |            7 |
|  2 | 苍老师     | 女   | 1987-12-30 00:00:00 | 18219876577 | NULL  |            7 |
|  3 | Angelababy | 女   | 1989-02-03 00:00:00 | 18209876567 | NULL  |            3 |
|  4 | 关晓彤     | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            2 |
|  5 | 周冬雨     | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            7 |
|  6 | 周芷若     | 女   | 1988-02-03 00:00:00 | 18209876577 | NULL  |            1 |
|  7 | 岳灵珊     | 女   | 1987-12-30 00:00:00 | 18219876577 | NULL  |            7 |
|  8 | 小昭       | 女   | 1989-02-03 00:00:00 | 18209876567 | NULL  |            1 |
|  9 | 双儿       | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            7 |
| 10 | 王语嫣     | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            4 |
| 11 | 夏雪       | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            7 |
| 12 | 赵敏       | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            1 |
| 13 | 王菲       | 女   | 1969-08-08 00:00:00 | 18202318320 | NULL  |            5 |
| 14 | 张柏芝     | 女   | 1980-05-24 00:00:00 | 15920424850 | NULL  |            5 |
| 15 | 杨紫       | 女   | 1992-11-06 00:00:00 | 1884626857  | NULL  |            6 |
+----+------------+------+---------------------+-------------+-------+--------------+
15 rows in set (0.00 sec)

案例二：多表删除

#删除张无忌以及他女盆友的信息
mysql> delete bf, gf from beauty gf join boys bf on bf.id = gf.boyfriend_id where bf.boyname = '张无忌';
Query OK, 4 rows affected (0.11 sec)

mysql> select * from beauty;
+----+------------+------+---------------------+-------------+-------+--------------+
| id | name       | sex  | borndate            | phone       | photo | boyfriend_id |
+----+------------+------+---------------------+-------------+-------+--------------+
|  1 | 柳岩       | 女   | 1988-02-03 00:00:00 | 18209876577 | NULL  |            7 |
|  2 | 苍老师     | 女   | 1987-12-30 00:00:00 | 18219876577 | NULL  |            7 |
|  3 | Angelababy | 女   | 1989-02-03 00:00:00 | 18209876567 | NULL  |            3 |
|  4 | 关晓彤     | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            2 |
|  5 | 周冬雨     | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            7 |
|  7 | 岳灵珊     | 女   | 1987-12-30 00:00:00 | 18219876577 | NULL  |            7 |
|  9 | 双儿       | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            7 |
| 10 | 王语嫣     | 女   | 1992-02-03 00:00:00 | 18209179577 | NULL  |            4 |
| 11 | 夏雪       | 女   | 1993-02-03 00:00:00 | 18209876579 | NULL  |            7 |
| 13 | 王菲       | 女   | 1969-08-08 00:00:00 | 18202318320 | NULL  |            5 |
| 14 | 张柏芝     | 女   | 1980-05-24 00:00:00 | 15920424850 | NULL  |            5 |
| 15 | 杨紫       | 女   | 1992-11-06 00:00:00 | 1884626857  | NULL  |            6 |
+----+------------+------+---------------------+-------------+-------+--------------+
12 rows in set (0.00 sec)

mysql> select * from boys;
+----+-----------+--------+
| id | boyName   | userCP |
+----+-----------+--------+
|  2 | 鹿晗      |    800 |
|  3 | 黄晓明    |     50 |
|  4 | 段誉      |    300 |
|  5 | 谢霆锋    |   1000 |
|  6 | 张一山    |    666 |
|  7 | 张翰      |    500 |
+----+-----------+--------+
6 rows in set (0.00 sec)

 

案例三：方式二：truncate(清除整个表的内容，慎用哦！嘻嘻，我复制了boys表并命名为boy_2再删除复制的表）

mysql> select * from boys_2;
+----+-----------+--------+
| id | boyName   | userCP |
+----+-----------+--------+
|  2 | 鹿晗      |    800 |
|  3 | 黄晓明    |     50 |
|  4 | 段誉      |    300 |
|  5 | 谢霆锋    |   1000 |
|  6 | 张一山    |    666 |
|  7 | 张翰      |    500 |
+----+-----------+--------+
6 rows in set (0.00 sec)

mysql> truncate boys_2;
Query OK, 0 rows affected (0.15 sec)

mysql> select * from boys_2;
Empty set (0.01 sec)

mysql> desc boys_2;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | 0       |       |
| boyName | varchar(20) | YES  |     | NULL    |       |
| userCP  | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

？？？我想知道竟然有delete from 表名了，为啥需要这种truncate 表名 的操作呢？他们都可以删除整个表的内容呀！！！

——> 欸，我好像发现一点不同了耶，一个删除有返回值，一个没有返回值.

mysql> truncate boys_2;
Query OK, 0 rows affected (0.15 sec)

mysql> delete from boys_2;
Query OK, 6 rows affected (0.15 sec)

√ delete PK truncate（√√√）

 1.delete 可以加where条件,truncate 不可以。
 2.truncate 删除，效率高那么一丢丢.
 3.假如要删除的表中有自增长列，用delete删除后，再插入数据，自增长列的值从断点开始。而用truncate 删除后，再插入数据，自增长列的值从‘1’开始。
 4.truncate删除没有返回值，delete删除有返回值。
 5.truncate 删除不能回滚，delete 删除可以回滚。(事务那块会说到）

突然觉得truncate删除有点神不知鬼不觉地删除你，更加彻底，不留痕迹，咳咳...

注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！