mysql8学习笔记6--SQL基础-insert update delete语句
• Insert语句用于插入数据到表中，其基本语法有以下三种：
其中insert…values和insert…set两种语句都是将指定的数据插入到现成的表中，而insert…select语句是将另外表中数据查出来并插入到现成的表中
• Partition子句代表可以将数据插入到指定的表分区中
• Tbl_name代表将数据插入到的目标表
• Col_name代表要插入指定数据的目标表列，如果是多列则用逗号隔开，如果目标表中的某些列没有在Insert语句中指定，则这些列会插入默认值，当然可以使用default显视指定插入默认值
• Values中除了可以指定确定的数值之外，还可以使用表达式expr
• INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2); ##正确
• INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15); ##错误
• Insert…values语句不光可以插入一条数据，也可以插入多条数据
• INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
• Insert into students values(7,’abc’),(8,’bcd’);
• Insert…values和insert…select语句的执行结果如下
• Records: 100 Duplicates: 0 Warnings: 0
• Records代表此语句操作了多少行数据，但不一定是多少行被插入的数据，因为如果存在相同的行数据且违反了某个唯一性，则duplicates会显示非0数值，warning代表语句执行过程中的一些警告信息
• low_priority关键词代表如果有其他链接正在读取目标表数据，则此insert语句需要等待读取完成
• low_priority和high_priority关键词仅在MyISAM, MEMORY, and MERGE三种存储引擎下才生效
• Ignore关键词代表insert语句如果违反主键和唯一键的约束条件，则不报错而只产生警告信息，违反的行被丢弃，而不是整个语句回退；在数据类型转换有问题时如果有ignore则只产生警告信息，而不是语句
mysql> create table orders3 as select * from orders2; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into orders3(order_num,cust_id,order_date) values(20010,10007,'2005-09-01 00:00:00');#insert第一种方式 Query OK, 1 row affected (0.00 sec) mysql> select * from orders3; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20010 | 2005-09-01 00:00:00 | 10007 | +-----------+---------------------+---------+ 1 row in set (0.00 sec) mysql> insert into orders3 values(20010,10007,'2005-09-01 00:00:00');#insert时可以不指定列，但是需要按照排列顺序传入values; ERROR 1292 (22007): Incorrect datetime value: '10007' for column 'order_date' at row 1 mysql> insert into orders3 values(20010,'2005-09-01 00:00:00',1007); Query OK, 1 row affected (0.01 sec) mysql> select * from orders3; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20010 | 2005-09-01 00:00:00 | 10007 | | 20010 | 2005-09-01 00:00:00 | 1007 | +-----------+---------------------+---------+ 2 rows in set (0.00 sec) mysql> insert into orders3(order_num,cust_id,order_date) values(20010,order_num*2,'2005-09-01 00:00:00'); #insert时，列和值一一对应就行。value可以放入表达式。 Query OK, 1 row affected (0.03 sec) mysql> select * from orders3; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20010 | 2005-09-01 00:00:00 | 10007 | | 20010 | 2005-09-01 00:00:00 | 1007 | | 20010 | 2005-09-01 00:00:00 | 40020 | +-----------+---------------------+---------+ 3 rows in set (0.00 sec) mysql> insert into orders3(order_num,cust_id,order_date) values(20010,order_num*2,'2005-09-01 00:00:00'),(20011,order_num*2,'2020-09-01 00:00:00');#可一次插入多条记录。 Query OK, 2 rows affected (0.08 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from orders3; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20010 | 2005-09-01 00:00:00 | 10007 | | 20010 | 2005-09-01 00:00:00 | 1007 | | 20010 | 2005-09-01 00:00:00 | 40020 | | 20010 | 2005-09-01 00:00:00 | 40020 | | 20011 | 2020-09-01 00:00:00 | 40022 | +-----------+---------------------+---------+ 5 rows in set (0.00 sec) mysql> insert into orders3 set order_num=21210,cust_id=order_num*2,order_date='2122-09-01 00:00:00';#insert第二种方式。 Query OK, 1 row affected (0.08 sec) mysql> select * from orders3; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20010 | 2005-09-01 00:00:00 | 10007 | | 20010 | 2005-09-01 00:00:00 | 1007 | | 20010 | 2005-09-01 00:00:00 | 40020 | | 20010 | 2005-09-01 00:00:00 | 40020 | | 20011 | 2020-09-01 00:00:00 | 40022 | | 21210 | 2122-09-01 00:00:00 | 42420 | +-----------+---------------------+---------+ 6 rows in set (0.00 sec) mysql> insert into orders3 select * from orders;#insert第三种方式 Query OK, 5 rows affected (0.09 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from orders3; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20010 | 2005-09-01 00:00:00 | 10007 | | 20010 | 2005-09-01 00:00:00 | 1007 | | 20010 | 2005-09-01 00:00:00 | 40020 | | 20010 | 2005-09-01 00:00:00 | 40020 | | 20011 | 2020-09-01 00:00:00 | 40022 | | 21210 | 2122-09-01 00:00:00 | 42420 | | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10001 | +-----------+---------------------+---------+ 11 rows in set (0.00 sec) mysql>
• 当insert语句中使用on duplicate key update子句时，如果碰到当前插入的数据违反主键或唯一键的唯一性约束，则Insert会转变成update语句修改对应的已经存在表中的这条数据。比如如果a字段有唯一性约束且已经含有1这条记录，则以下两条语句的执行结果相同。
• INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
• UPDATE table SET c=c+1 WHERE a=1;
• On duplicate key update子句后面可以跟多个修改，用逗号隔开
• 上述例子中如果b字段也有唯一性约束，则与此语句的执行结果相同，但一般应该避免出现对应多条的情况
• UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
mysql> select * from customers5; +---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+ | 110 | 小明 | 中国广东省 | 深圳 | 南山区 | NULL | NULL | NULL | NULL | +---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+ 1 row in set (0.00 sec) mysql> insert into customers5(cust_id,cust_address) values(190,"China"); Query OK, 1 row affected (0.01 sec) mysql> select * from customers5; +---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+ | 110 | 小明 | 中国广东省 | 深圳 | 南山区 | NULL | NULL | NULL | NULL | | 190 | NULL | China | 深圳 | 南山区 | NULL | NULL | NULL | NULL | +---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+ 2 rows in set (0.01 sec) mysql> insert into customers5(cust_id,cust_address) values(190,"China") on duplicate key update cust_id=200,cust_address='China'; Query OK, 2 rows affected (0.04 sec) mysql> select * from customers5; +---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+ | 110 | 小明 | 中国广东省 | 深圳 | 南山区 | NULL | NULL | NULL | NULL | | 200 | NULL | China | 深圳 | 南山区 | NULL | NULL | NULL | NULL | +---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+ 2 rows in set (0.00 sec) mysql>
• update语句用于修改表中已经存在的数据
• 单表修改语句结构
• 多表修改语句结构
update语句用于修改表中已经存在的数据
• 单表修改语句结构
mysql> update customers7 set cust_name='大班' where cust_id=110; Query OK, 1 row affected (0.17 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update customers7 set cust_address='中国台湾' -> ,cust_city='台湾' where cust_id=110; Query OK, 1 row affected (0.18 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from customers7 where cust_id=110; +---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+ | 110 | 大班 | 中国台湾 | 台湾 | 南山区 | NULL | NULL | NULL | NULL | +---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+ 1 row in set (0.00 sec) mysql> select * from customers5 where cust_id=110; +---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+ | 110 | 小明 | 中国广东省 | 深圳 | 南山区 | NULL | NULL | NULL | NULL | +---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+ 1 row in set (0.00 sec) mysql> update customers5,customers7 set customers5.cust_country='中国',customers7.cust_country='中国' where customers5.cust_id = customers7.cust_id; Query OK, 16 rows affected (0.14 sec) Rows matched: 16 Changed: 16 Warnings: 0 mysql> select * from customers5; +---------+----------------+-----------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+-----------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 110 | 小明 | 中国广东省 | 深圳 | 南山区 | NULL | 中国 | NULL | NULL | | 123 | | 中国深圳南山区 | NULL | NULL | NULL | 中国 | NULL | NULL | | 200 | 大同 | China | 深圳 | 南山区 | NULL | 中国 | NULL | NULL | | 10001 | Coyote Inc. | 中国深圳南山区 | Detroit | MI | 44444 | 中国 | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 中国深圳南山区 | Columbus | OH | 43333 | 中国 | Jerry Mouse | NULL | | 10003 | Wascals | 中国深圳南山区 | Muncie | IN | 42222 | 中国 | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 北京西城区 | Phoenix | AZ | 88888 | 中国 | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 北京西城区 | Chicago | IL | 54545 | 中国 | E Fudd | NULL | +---------+----------------+-----------------------+-----------+------------+----------+--------------+--------------+---------------------+ 8 rows in set (0.00 sec) mysql> select * from customers7; +---------+----------------+-----------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+-----------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 110 | 大班 | 中国台湾 | 台湾 | 南山区 | NULL | 中国 | NULL | NULL | | 123 | | 中国深圳南山区 | NULL | NULL | NULL | 中国 | NULL | NULL | | 200 | 大同 | China | 深圳 | 南山区 | NULL | 中国 | NULL | NULL | | 10001 | Coyote Inc. | 中国深圳南山区 | Detroit | MI | 44444 | 中国 | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 中国深圳南山区 | Columbus | OH | 43333 | 中国 | Jerry Mouse | NULL | | 10003 | Wascals | 中国深圳南山区 | Muncie | IN | 42222 | 中国 | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 北京西城区 | Phoenix | AZ | 88888 | 中国 | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 北京西城区 | Chicago | IL | 54545 | 中国 | E Fudd | NULL | +---------+----------------+-----------------------+-----------+------------+----------+--------------+--------------+---------------------+ 8 rows in set (0.00 sec) mysql>
mysql> select * from customers7; +---------+----------------+-----------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+-----------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 110 | 大班 | 中国台湾 | 台湾 | 南山区 | NULL | 中国 | NULL | NULL | | 123 | | 中国深圳南山区 | NULL | NULL | NULL | 中国 | NULL | NULL | | 200 | 大同 | China | 深圳 | 南山区 | NULL | 中国 | NULL | NULL | | 10001 | Coyote Inc. | 中国深圳南山区 | Detroit | MI | 44444 | 中国 | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 中国深圳南山区 | Columbus | OH | 43333 | 中国 | Jerry Mouse | NULL | | 10003 | Wascals | 中国深圳南山区 | Muncie | IN | 42222 | 中国 | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 北京西城区 | Phoenix | AZ | 88888 | 中国 | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 北京西城区 | Chicago | IL | 54545 | 中国 | E Fudd | NULL | +---------+----------------+-----------------------+-----------+------------+----------+--------------+--------------+---------------------+ 8 rows in set (0.00 sec) mysql> select * from customers7 order by cust_id desc limit 2; +---------+----------------+-----------------+-----------+------------+----------+--------------+--------------+------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+-----------------+-----------+------------+----------+--------------+--------------+------------------+ | 10005 | E Fudd | 北京西城区 | Chicago | IL | 54545 | 中国 | E Fudd | NULL | | 10004 | Yosemite Place | 北京西城区 | Phoenix | AZ | 88888 | 中国 | Y Sam | sam@yosemite.com | +---------+----------------+-----------------+-----------+------------+----------+--------------+--------------+------------------+ 2 rows in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> delete from customers7 order by cust_id desc limit 2; Query OK, 2 rows affected (0.00 sec) mysql> select * from customers7; +---------+-------------+-----------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+-------------+-----------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 110 | 大班 | 中国台湾 | 台湾 | 南山区 | NULL | 中国 | NULL | NULL | | 123 | | 中国深圳南山区 | NULL | NULL | NULL | 中国 | NULL | NULL | | 200 | 大同 | China | 深圳 | 南山区 | NULL | 中国 | NULL | NULL | | 10001 | Coyote Inc. | 中国深圳南山区 | Detroit | MI | 44444 | 中国 | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 中国深圳南山区 | Columbus | OH | 43333 | 中国 | Jerry Mouse | NULL | | 10003 | Wascals | 中国深圳南山区 | Muncie | IN | 42222 | 中国 | Jim Jones | rabbit@wascally.com | +---------+-------------+-----------------------+-----------+------------+----------+--------------+--------------+---------------------+ 6 rows in set (0.00 sec) mysql>
13.2.2 DELETE语句
DELETE 是DML语句，用于从表中删除行。
一条DELETE语句可以从一个WITH子句开始， 以定义可在内访问的公用表表达式 DELETE。请参见第13.2.15节“ WITH（公用表表达式）”。
单表语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
主要子句
可选WHERE子句中的条件标识要删除的行。没有no WHERE 子句，将删除所有行。
where_condition是一个表达式，对于要删除的每一行，其值为true。如第13.2.10节“ SELECT语句”中所述指定它。
如果ORDER BY指定了子句，则按指定的顺序删除行。该 LIMIT子句限制了可以删除的行数。这些子句适用于单表删除，但不适用于多表删除。
mysql> select * from customers7 where cust_id=110 or cust_id=123; +---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+ | 110 | 大班 | 中国台湾 | 台湾 | 南山区 | NULL | 中国 | NULL | NULL | | 123 | | 中国深圳南山区 | NULL | NULL | NULL | 中国 | NULL | NULL | +---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+ 2 rows in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> delete from customers7 where cust_id=110 or cust_id=123; Query OK, 2 rows affected (0.00 sec) mysql> select * from customers7 where cust_id=110 or cust_id=123; Empty set (0.00 sec)
mysql错误提示：不能先将select出表中的某些值，再duupdate这个表(在同一语句中)。
mysql> select cust_id from customers7 where cust_address='中国深圳南山区'; +---------+ | cust_id | +---------+ | 123 | | 10001 | | 10002 | | 10003 | +---------+ 4 rows in set (0.00 sec) mysql> select cust_id from customers7 where cust_address='china'; +---------+ | cust_id | +---------+ | 200 | +---------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> delete from customers7 where cust_id=(select cust_id from customers7 where cust_address='中国深圳南山区'); ERROR 1093 (HY000): You can't specify target table 'customers7' for update in FROM clause mysql>
替换方案：
方案一：
多嵌套一层子查询，再进行删除：
mysql> delete from customers7 where cust_id=(select cust_id from (select cust_id from customers7 where cust_address='中国深圳南山区')); ERROR 1248 (42000): Every derived table must have its own alias #意思是每个派生表都必须有自己的别名。 mysql> delete from customers7 where cust_id=(select id from (select cust_id as id from customers7 e where e.cust_address='中国深圳南山区')); ERROR 1248 (42000): Every derived table must have its own alias #在最里面的表加别名不行 mysql> delete from customers7 where cust_id=(select id from (select cust_id as id from customers7 e where e.cust_address='中国深圳南山区') a); ERROR 1242 (21000): Subquery returns more than 1 row #在最外层的派生表加别名就可以了，不过只能返回一个值。 mysql> mysql> delete from customers7 where cust_id=(select id from (select cust_id as id from customers7 where cust_address='中国深圳南山区') a); ERROR 1242 (21000): Subquery returns more than 1 row mysql> delete from customers7 where cust_id=(select id from (select cust_id as id from customers7 where cust_address='china') a); Query OK, 1 row affected (0.00 sec) mysql> select cust_id from customers7 where cust_address='china'; Empty set (0.00 sec)
方案二：
方案二：
mysql> rollback; Query OK, 0 rows affected (0.06 sec) mysql> select cust_id from customers7 where cust_address='china'; +---------+ | cust_id | +---------+ | 200 | +---------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> create table temp as select cust_id from customers7 where cust_address='china';1.创建一张临时表，将要删除的条件自动存入临时表中： Query OK, 1 row affected (0.16 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from temp; +---------+ | cust_id | +---------+ | 200 | +---------+ 1 row in set (0.00 sec) mysql> delete from customers7 where cust_id=(select cust_id from temp);2.再根据临时表，删除主表数据： Query OK, 1 row affected (0.03 sec) mysql> select cust_id from customers7 where cust_address='china'; Empty set (0.00 sec)
mysql> drop table temp;3.最后删除掉临时表：
Query OK, 0 rows affected (0.09 sec)
mysql>
多表语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition] DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition]
Privileges
You need the DELETE privilege on a table to delete rows from it. You need only the SELECT privilege for any columns that are only read, such as those named in the WHERE clause.
