🥩 Update (更新/修改) 🦖
该语法主要针对于对数据库的表内数据进行修改;
一般该操作需要配合条件筛选,否则操作将影响整张表;
语法:
UPDATE table_name SET column = expr[,column = expr ... ] [WHERE ...] [ORDER BY ...] [LIMIT ...]
存在一张表:
+----+---------+---------+------+---------+ | id | name | chinese | math | english | +----+---------+---------+------+---------+ | 1 | Lihua | 67 | 88 | 90 | | 2 | Liming | 57 | 58 | 70 | | 3 | Zhaolao | 66 | 80 | 47 | | 4 | Wu | 76 | 70 | 47 | | 5 | Wuqi | 88 | 43 | 80 | | 6 | Liqiang | 89 | 92 | 90 | | 7 | Qinsu | 90 | 74 | 67 | | 8 | Zhaoli | 54 | 74 | 100 | +----+---------+---------+------+---------+
🥚 修改单行数据的某个字段内的数据 🦕
示例:将name字段为Lihua的chinese字段数据修改为100
mysql> select name,chinese from Point where name ='Lihua';# 打印数据进行观察 +-------+---------+ | name | chinese | +-------+---------+ | Lihua | 67 | +-------+---------+ 1 row in set (0.00 sec) mysql> update Point set chinese=100 where name='Lihua';# 修改数据 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select name,chinese from Point where name ='Lihua';# 打印数据进行观察 +-------+---------+ | name | chinese | +-------+---------+ | Lihua | 100 | +-------+---------+ 1 row in set (0.00 sec)
也可对单行数据的多个字段数据进行修改,前提是这个数据具有唯一属性(体现了主键约束的重要性);
🥚 配合LIMIT分页与ORDER BY 对符合条件的多条数据进行修改 🦕
示例:将字段条件chinese+math+english前3大的数据中的math字段数据+30
# 打印数据进行查看 -- 修改前 mysql> select name,math from Point order by math+chinese+english desc limit 3; +---------+------+ | name | math | +---------+------+ | Lihua | 88 | | Liqiang | 92 | | Qinsu | 74 | +---------+------+ 3 rows in set (0.00 sec) mysql> select name,math+chinese+english from Point order by math+chinese+english desc limit 3; +---------+----------------------+ | name | math+chinese+english | +---------+----------------------+ | Lihua | 278 | | Liqiang | 271 | | Qinsu | 231 | +---------+----------------------+ 3 rows in set (0.00 sec) # 修改数据 mysql> update Point set math=math+30 order by math+chinese+english desc limit 3; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 # 打印数据进行查看 -- 修改后 mysql> select name,math+chinese+english from Point order by math+chinese+english desc limit 3; +---------+----------------------+ | name | math+chinese+english | +---------+----------------------+ | Lihua | 308 | | Liqiang | 301 | | Qinsu | 261 | +---------+----------------------+ 3 rows in set (0.00 sec) mysql> select name,math from Point order by math+chinese+english desc limit 3; +---------+------+ | name | math | +---------+------+ | Lihua | 118 | | Liqiang | 122 | | Qinsu | 104 | +---------+------+
当需要修改的数据条件繁琐的前提可以先使用SELECT FROM语句先对需要修改的数据进行查看筛选,再对该SELECT FROM语句根据筛选条件修改为对应的UPDATE SET语句对数据进行修改!
🥚 对整表的某个数据字段进行修改 🦕
在UPDATE SET语句中若是没有用WHERE语句进行筛选则更新整表!
在SQL中对整表的操作需要慎重;
示例:将整表中的english字段数据修改为 *=2 ;
# 使用SELECT FROM语句对数据进行查看 -- 修改前 mysql> select id,name,english from Point; +----+---------+---------+ | id | name | english | +----+---------+---------+ | 1 | Lihua | 90 | | 2 | Liming | 70 | | 3 | Zhaolao | 47 | | 4 | Wu | 47 | | 5 | Wuqi | 80 | | 6 | Liqiang | 90 | | 7 | Qinsu | 67 | | 8 | Zhaoli | 100 | +----+---------+---------+ 8 rows in set (0.00 sec) # 对数据进行修改 mysql> update Point set english*=2 ; #SQL语句中不支持*=,+=等语法! ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*=2' at line 1# error mysql> update Point set english=english*2 ;# 正确 Query OK, 8 rows affected (0.01 sec) Rows matched: 8 Changed: 8 Warnings: 0 # 使用SELECT FROM语句对数据进行查看 -- 修改后 mysql> select id,name,english from Point; +----+---------+---------+ | id | name | english | +----+---------+---------+ | 1 | Lihua | 180 | | 2 | Liming | 140 | | 3 | Zhaolao | 94 | | 4 | Wu | 94 | | 5 | Wuqi | 160 | | 6 | Liqiang | 180 | | 7 | Qinsu | 134 | | 8 | Zhaoli | 200 | +----+---------+---------+ 8 rows in set (0.00 sec)
🥩 Delete (删除数据) 🦖
该语句一般用于对某条数据的删除,也可以删除整表的数据;
语法:
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
存在一张表(to_del):
+-----+-----------+ | id | name | +-----+-----------+ | 001 | Lihua | | 002 | Liming | | 003 | Zhangchao | | 004 | Zhaoli | | 005 | Lili | +-----+-----------+
🥚 删除单条数据 🦕
示例:删除id字段为2的数据
mysql> select * from to_del -> ; +-----+-----------+ | id | name | +-----+-----------+ | 001 | Lihua | | 002 | Liming | | 003 | Zhangchao | | 004 | Zhaoli | | 005 | Lili | +-----+-----------+ 5 rows in set (0.00 sec) mysql> delete from to_del where id=2; Query OK, 1 row affected (0.00 sec) mysql> select * from to_del; +-----+-----------+ | id | name | +-----+-----------+ | 001 | Lihua | | 003 | Zhangchao | | 004 | Zhaoli | | 005 | Lili | +-----+-----------+ 4 rows in set (0.00 sec)
🥚 删除整表数据 🦕
与UPDATE SET语句相同,DELETE FROM语句也可以删除整表的数据;
-- 查看表内数据 删除前 mysql> select * from to_del; +-----+-----------+ | id | name | +-----+-----------+ | 001 | Lihua | | 003 | Zhangchao | | 004 | Zhaoli | | 005 | Lili | +-----+-----------+ 4 rows in set (0.00 sec) -- 删除整表数据 mysql> delete from to_del; Query OK, 4 rows affected (0.00 sec) -- 查看表内数据 删除后 mysql> select * from to_del; Empty set (0.00 sec)
🥚 截断表 (TRUNCATE) 🦕
语法:
TRUNCATE [TABLE] table_name;
截断表的操作与删除整表的作用类似也为清除整表内容(不作示例);
但存在不同;
同样以该表进行测试;
+-----+-----------+ | id | name | +-----+-----------+ | 001 | Lihua | | 002 | Liming | | 003 | Zhangchao | | 004 | Zhaoli | | 005 | Lili | +-----+-----------+
- 使用
SHOW CREATE TABLE table_name \G对表的详细信息进行查看;
mysql> show create table to_del\G *************************** 1. row *************************** Table: to_del Create Table: CREATE TABLE `to_del` ( `id` int(3) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT 'id增加主键约束', `name` varchar(20) NOT NULL COMMENT 'name增加唯一键约束', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
- 对该表进行查看时自增
AUTO_INCREMENT关键字为6; - 使用
DELETE FROM删除整表;
mysql> delete from to_del; Query OK, 5 rows affected (0.01 sec) mysql> show create table to_del\G *************************** 1. row *************************** Table: to_del Create Table: CREATE TABLE `to_del` ( `id` int(3) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT 'id增加主键约束', `name` varchar(20) NOT NULL COMMENT 'name增加唯一键约束', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
- 使用
DELETE FROM对整表进行删除时其AUTO_INCREMENT关键字并没有改变; - 使用
TRUNCATE对整表进行截断操作;
mysql> truncate to_del; Query OK, 0 rows affected (0.00 sec) mysql> show create table to_del\G *************************** 1. row *************************** Table: to_del Create Table: CREATE TABLE `to_del` ( `id` int(3) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT 'id增加主键约束', `name` varchar(20) NOT NULL COMMENT 'name增加唯一键约束', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
- 而使用
TRUNCATE对表进行截断操作时此时该表的AUTO_INCREMENT关键字不见了;
综上所述可以确定:
1. TRUNCATE只能对整表进行操作,且不能像DELETE对部分数据进行操作;
2. 若是表中存在AUTO_INCREMENT自增约束时,TRUNCATE的截断表操作将会重置AUTO_INCREMENT;
除此之外,对于DELETE来说,TRUNCATE的删除操作会较快,因为TRUNCATE在操作删除数据时并不经过真正的事务,所以无法回滚;
🥩 总结 🦖🦖🦖🦖🦖
对于对表内数据的操作来说,无论是UPDATE还是DELETE与TRUNCATE对整表进行操作都应该注意;
尽量在对表内数据修改或删除前对需要修改\删除的数据使用 SELETC进行查看,避免误操作;
