MySQL必知必会 -- 更新和删除数据

更新数据

MariaDB [test]> select * from hour;
+--------+--------+------+------+------+----------+-------+
| user   | passwd | sex  | age  | num  | math_num | class |
+--------+--------+------+------+------+----------+-------+
| user11 | NULL   | NULL | NULL | NULL | NULL     |  NULL |
| user12 | 222    | boy  | 27   | NULL | NULL     |  NULL |
+--------+--------+------+------+------+----------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> update hour set passwd = '111',sex = 'girl' where user='user11'；
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [test]> select * from hour;
+--------+--------+------+------+------+----------+-------+
| user   | passwd | sex  | age  | num  | math_num | class |
+--------+--------+------+------+------+----------+-------+
| user11 | 111    | girl | NULL | NULL | NULL     |  NULL |
| user12 | 222    | boy  | 27   | NULL | NULL     |  NULL |
+--------+--------+------+------+------+----------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> update hour set class = 1; 
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [test]> select * from hour;
+--------+--------+------+------+------+----------+-------+
| user   | passwd | sex  | age  | num  | math_num | class |
+--------+--------+------+------+------+----------+-------+
| user11 | 111    | girl | NULL | NULL | NULL     |     1 |
| user12 | 222    | boy  | 27   | NULL | NULL     |     1 |
+--------+--------+------+------+------+----------+-------+
2 rows in set (0.00 sec)

当不加where子句时候更新全部的行。

使用子查询：

MariaDB [test]> update hour set class = (select class from linux where user = 'user4'); 
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [test]> select * from hour;
+--------+--------+------+------+------+----------+-------+
| user   | passwd | sex  | age  | num  | math_num | class |
+--------+--------+------+------+------+----------+-------+
| user11 | 111    | girl | NULL | NULL | NULL     |     2 |
| user12 | 222    | boy  | 27   | NULL | NULL     |     2 |
+--------+--------+------+------+------+----------+-------+
2 rows in set (0.00 sec)

如果更新的行较多，我们想要在中途发即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：
UPDATE IGNORE customers

可以用NULL用来去除l列中的值：

MariaDB [test]> update hour set class = null; 
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [test]> select * from hour;
+--------+--------+------+------+------+----------+-------+
| user   | passwd | sex  | age  | num  | math_num | class |
+--------+--------+------+------+------+----------+-------+
| user11 | 111    | girl | NULL | NULL | NULL     |  NULL |
| user12 | 222    | boy  | 27   | NULL | NULL     |  NULL |
+--------+--------+------+------+------+----------+-------+
2 rows in set (0.00 sec)

删除数据

删除行：

MariaDB [test]> select * from ss;
+--------+-------+
| state  | user  |
+--------+-------+
| good   | user1 |
| secret | user2 |
| secret | user3 |
| bad    | user4 |
| good   | user5 |
| good   | user6 |
| bad    | user7 |
+--------+-------+
7 rows in set (0.00 sec)

MariaDB [test]> delete from ss where user = 'user7'
    -> ;
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> delete from ss where user = 'user7'
    -> \c
MariaDB [test]> select * from ss;
+--------+-------+
| state  | user  |
+--------+-------+
| good   | user1 |
| secret | user2 |
| secret | user3 |
| bad    | user4 |
| good   | user5 |
| good   | user6 |
+--------+-------+
6 rows in set (0.00 sec)

DELETE 后不接 WHERE 子句会删除所有的行，他只是删除表内容，而不是删除表本身。

删除列：

MariaDB [test]> alter table ss drop state;
Query OK, 6 rows affected (0.00 sec)               
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from ss;
+-------+
| user  |
+-------+
| user1 |
| user2 |
| user3 |
| user4 |
| user5 |
| user6 |
+-------+
6 rows in set (0.00 sec)