3.表记录操作-修改

1.插入记录

INSERT into tableName(value1,value2,value3) values(value1,value2,value3);
example:
mysql> INSERT into newuser(uid,uname,upassword) values(null,'zhang','111');
Query OK, 1 row affected (0.29 sec)
mysql> select * from newuser;
+-----+----------+-----------+
| uid | uname    | upassword |
+-----+----------+-----------+
|   1 | zhang | 111       |
+-----+----------+-----------+
1 row in set (0.00 sec)

OR

mysql> INSERT into newuser values(null,'wang','111');
Query OK, 1 row affected (0.28 sec)
mysql> select * from newuser;
+-----+----------+-----------+
| uid | uname    | upassword |
+-----+----------+-----------+
|   1 | zhang| 111      |
|   2 | wang| 111       |
+-----+----------+-----------+
2 rows in set (0.00 sec)

2.更新记录

UPDATE tableName set title=value,title=value,...;
example:
mysql> UPDATE newuser set upassword=222;
Query OK, 2 rows affected (0.29 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * from newuser;
+-----+----------+-----------+
| uid | uname    | upassword |
+-----+----------+-----------+
|   1 | wangchao | 222       |
|   2 | zhangsan | 222       |
+-----+----------+-----------+
2 rows in set (0.00 sec)

OR


UPDATE tableName set title=value,title=value ... WHERE condition;
example:
mysql> UPDATE newuser set upassword='333' WHERE uid=1;
Query OK, 1 row affected (0.28 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM newuser;
+-----+----------+-----------+
| uid | uname    | upassword |
+-----+----------+-----------+
|   1 | wangchao | 333       |
|   2 | zhangsan | 222       |
+-----+----------+-----------+
2 rows in set (0.00 sec)

3.删除记录

DELETE FROM tableName WHERE uid=1;
example:
mysql> DELETE FROM newuser WHERE uid=1;
Query OK, 1 row affected (0.33 sec)

mysql> SELECT *FROM newuser;
+-----+----------+-----------+
| uid | uname    | upassword |
+-----+----------+-----------+
|   2 | zhangsan | 222       |
+-----+----------+-----------+
1 row in set (0.00 sec)
* 删除后，uid不会重置！！！

删除表记录，DELETE是一条条的删除，配合事务，可以恢复删除记录。
mysql> DELETE FROM newuser;
Query OK, 8 rows affected (0.29 sec)

mysql> SELECT * FROM newuser;
Empty set (0.00 sec)
mysql> INSERT into newuser values(null,'niu','444');
Query OK, 1 row affected (0.28 sec)
重新插入数据，删除表，配合事务回滚，恢复数据。
mysql> INSERT into newuser values(null,'niu','444');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT into newuser values(null,'niu','444');
Query OK, 1 row affected (0.02 sec)

mysql> select * from newuser;
+-----+-------+-----------+
| uid | uname | upassword |
+-----+-------+-----------+
|  16 | niu   | 444       |
|  17 | niu   | 444       |
+-----+-------+-----------+
6 rows in set (0.00 sec)
//1.开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
//2.删除表
mysql> DELETE FROM newuser;
Query OK, 6 rows affected (0.00 sec)
//3.查询表
mysql> SELECT * FROM newuser;
Empty set (0.00 sec)
//4.事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.09 sec)
//5.再次查询表，数据恢复
mysql> SELECT * FROM newuser;
+-----+-------+-----------+
| uid | uname | upassword |
+-----+-------+-----------+
|  16 | niu   | 444       |
|  17 | niu   | 444       |
|  18 | niu   | 444       |
|  19 | niu   | 444       |
|  20 | niu   | 444       |
|  21 | niu   | 444       |
+-----+-------+-----------+
6 rows in set (0.00 sec)

删除表记录，TRUNCATE是将整个表销毁，再重新创建一张一样的新表，删除的数据无法恢复。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate newuser;
Query OK, 0 rows affected (0.51 sec)

mysql> select * from newuser;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from newuser;
Empty set (0.00 sec)
* truncate删除表，uid会重置！！！