十、数据的更新

表数据修改 update 表名 set 字段名 = 值…… where 条件;

update 表名 set 字段名 = 值…… where 条件;
-- 如果后面没有添加where子句的话 ， 会将整个字段的所有数据进行修改

-- 元数据里，凯莎和华烨性别没有
mysql> update t1 set sex = 'X' where name = '凯莎';
mysql> select * from t1;
+-----------+------+
| name      | sex  |
+-----------+------+
| 莫甘娜    | X    |
| 卡尔      | Y    |
| 凯莎      | X    |
| 华烨      | NULL |
+-----------+------+

mysql> update t1 set sex = 'Y' where name = '华烨';
mysql> select * from t1;
+-----------+------+
| name      | sex  |
+-----------+------+
| 莫甘娜    | X    |
| 卡尔      | Y    |
| 凯莎      | X    |
| 华烨      | Y    |
+-----------+------+

mysql> update t1 set sex = 'X' where name = '莎';
mysql> select * from t1;
+-----------+------+
| name      | sex  |
+-----------+------+
| 莫甘娜    | X    |
| 卡尔      | Y    |
| 凯莎      | X    |
| 华烨      | Y    |
+-----------+------+

-- 看不到结果的，只能看状态的，就不复制状态了，看不清楚
mysql> update t2 set sex = '女'; 
mysql> select *from t2;
+----+--------------+------+
| id | name         | sex  |
+----+--------------+------+
|  1 | 逆天而行     | 女   |
|  2 | NULL         | 女   |
+----+--------------+------+

mysql> update t2 set sex = '男' where name is NULL;
mysql> select *from t2;
+----+--------------+------+
| id | name         | sex  |
+----+--------------+------+
|  1 | 逆天而行     | 女   |
|  2 | NULL         | 男   |
+----+--------------+------+

mysql> drop table t6,t5,t4,t3,t2;
mysql> show tables;
+--------------+
| Tables_in_b1 |
+--------------+
| t1           |
+--------------+

表数据删除 delete from 表名 where 条件

delete from 表名 where 条件
-- 如果后面没有添加where子句的话 ， 会将表中的记录全部清空

mysql> select * from t1;
+-----------+------+
| name      | sex  |
+-----------+------+
| 莫甘娜    | X    |
| 卡尔      | Y    |
| 凯莎      | X    |
| 华烨      | Y    |
+-----------+------+

mysql> delete from t1 where sex='x';
mysql> select * from t1;
+--------+------+
| name   | sex  |
+--------+------+
| 卡尔   | Y    |
| 华烨   | Y    |
+--------+------+

mysql> delete from t1;
mysql> select * from t1; -- 全部删除，清空表数据
Empty set (0.00 sec)