# 数据更新 ## INSERT ``` INSERT INTO ( co1, co3, ...) VALUES (v1, v3,... ); # 省略列清单 INSERT INTO VALUES (v1,v2,v3,...); # 原来 mysql> SELECT * FROM Product -> ; +------------+--------------+--------------+------------+----------------+-------------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | +------------+--------------+--------------+------------+----------------+-------------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | | 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL | | 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 | | 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 | | 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 | | 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 | | 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 | +------------+--------------+--------------+------------+----------------+-------------+ 8 rows in set (0.00 sec) # 插入 mysql> INSERT INTO Product (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0009','T恤','衣服',1200,600,'2009-09-20'); Query OK, 1 row affected (0.01 sec) # 新的表 mysql> SELECT * FROM Product -> ; +------------+--------------+--------------+------------+----------------+-------------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | +------------+--------------+--------------+------------+----------------+-------------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | | 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL | | 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 | | 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 | | 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 | | 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 | | 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 | | 0009 | T恤 | 衣服 | 1200 | 600 | 2009-09-20 | +------------+--------------+--------------+------------+----------------+-------------+ 9 rows in set (0.00 sec) ``` ### 从其它表插入数据 ``` # 创建新复制表 mysql> CREATE TABLE ProductCopy (product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER, purchase_price INTEGER , regist_date DATE , PRIMARY KEY (product_id)); Query OK, 0 rows affected (0.03 sec) # 2张表 mysql> SHOW TABLES; +----------------+ | Tables_in_shop | +----------------+ | Product | | ProductCopy | +----------------+ # 从Product复制到Product mysql> INSERT INTO ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date) SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date FROM Product; Query OK, 9 rows affected (0.00 sec) # 结果 mysql> SELECT * FROM ProductCopy; +------------+--------------+--------------+------------+----------------+-------------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | +------------+--------------+--------------+------------+----------------+-------------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | | 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL | | 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 | | 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 | | 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 | | 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 | | 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 | | 0009 | T恤 | 衣服 | 1200 | 600 | 2009-09-20 | +------------+--------------+--------------+------------+----------------+-------------+ 9 rows in set (0.00 sec) # 其它 INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price) SELECT product_type, SUM(sale_price), SUM(purchase_price) FROM Product GROUP BY product_type; ``` ## DELETE ``` # 删除数据，保留表 DELETE FROM <表名>; # 删除所有 DROP TABLE <表名>; ``` ### 按条件删除 ``` DELETE FROM <表名> WHERE <条件>; # 原来 mysql> SELECT * FROM ProductCopy; +------------+--------------+--------------+------------+----------------+-------------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | +------------+--------------+--------------+------------+----------------+-------------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | | 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL | | 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 | | 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 | | 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 | | 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 | | 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 | | 0009 | T恤 | 衣服 | 1200 | 600 | 2009-09-20 | +------------+--------------+--------------+------------+----------------+-------------+ 9 rows in set (0.00 sec) # 删除sale_price >= 4000的 mysql> DELETE FROM ProductCopy -> WHERE sale_price >= 4000; Query OK, 2 rows affected (0.00 sec) # 新数据 mysql> SELECT * FROM ProductCopy; +------------+--------------+--------------+------------+----------------+-------------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | +------------+--------------+--------------+------------+----------------+-------------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | | 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 | | 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 | | 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 | | 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 | | 0009 | T恤 | 衣服 | 1200 | 600 | 2009-09-20 | +------------+--------------+--------------+------------+----------------+-------------+ 7 rows in set (0.00 sec) ``` ## UPDATE ``` UPDATE <表名> SET <列名> = <内容>; UPDATE Product SET regist_date = '2009-10-10'; ``` ### 按条件更新 ``` UPDATE <表名> SET <列> = <内容> WHERE <条件>; UPDATE Product SET sale_price = sale_price * 10 WHERE product_type = ''; # 多列一起更新 UPDATE Product SET sale_price = sale_price * 10, purchase_price = purchase_price / 2 WHERE product_type = ''; UPDATE Product SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2) WHERE product_type = ''; ``` ## 事物 ``` 开始事务: START TRANSACTION/ BEGIN TRANSACTION ... ... 结束事务： COMMIT / ROLLBACK # 原子性、一致性、隔离性、持久性 ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://im-qianuxn.gitbook.io/pytorch/ji-suan-ji/shu-ju-ku/mysql/data-update.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.