表结构:
CREATE TABLE `white_user_new` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(1000) NOT NULL COMMENT '姓名', `sex` int(11) DEFAULT NULL COMMENT '性别,0是女生,1是男生', `created_time` datetime DEFAULT NULL COMMENT '创建时间', `updated_time` datetime DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 COMMENT='新表-白名单表'
1. 如何一次插入多行?
insert into table_name(c1,c2,c3)
values(v11,v12,v13),(v21,v22,v23),(v31,v32,33)......;
mysql> insert into white_user_new(name,sex,created_time,updated_time) values ('张三',1,now(),now()),('李四',1,now(),now()),('张三',1,now(),now()),('李四',1,now(),now()),('王五',0,now(),now()),('赵六',0,now(),now());Query OK, 6 rows affected (0.00 sec)Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from white_user_new ; +----+--------+------+---------------------+---------------------+| id | name | sex | created_time | updated_time |+----+--------+------+---------------------+---------------------+| 6 | 张三 | 1 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 || 7 | 李四 | 1 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 || 8 | 张三 | 1 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 || 9 | 李四 | 1 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 || 10 | 王五 | 0 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 || 11 | 赵六 | 0 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 |+----+--------+------+---------------------+---------------------+6 rows in set (0.00 sec)
2. 如何删除最大id的记录?
如果你回答:
delete from white_user_new where id = select max(id) from white_user_new ;-- ERROR 1064 (42000): You have an error in your SQL syntax;
以上语句是错误的!
正确如下:
delete a from white_user_new a ,(select max(id) as max_id from white_user_new) bwhere a.id=b.max_id;-- Query OK, 1 row affected (0.00 sec) mysql> select * from white_user_new ;+----+--------+------+---------------------+---------------------+| id | name | sex | created_time | updated_time |+----+--------+------+---------------------+---------------------+| 6 | 张三 | 1 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 || 7 | 李四 | 1 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 || 8 | 张三 | 1 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 || 9 | 李四 | 1 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 || 10 | 王五 | 0 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 |+----+--------+------+---------------------+---------------------+5 rows in set (0.00 sec
3. 如何查询出重复的字段值?
比如查询white_user_new表的男生的重复的name
mysql> select name ,count(*) as c1 from white_user_new where sex = 1 group by name having c1 > 1 ;-- 带有重复次数 mysql> select name ,count(*) from white_user_new where sex = 1 group by name having count(*) > 1 ;-- 带有重复次数 mysql> select name from white_user_new where sex = 1 group by name having count(*) > 1 order by name ;-- 不带重复次数,并按照name升序排列(默认是升序,不必写asc)+--------+| name |+--------+| 张三 || 李四 |+--------+2 rows in set (0.00 sec)
4. 如何查询white_user_new表的男生的name重复所有记录?
mysql> select * from white_user_new where name in (select name from white_user_new where sex = 1 group by name having count(*) > 1 ) +----+--------+------+---------------------+---------------------+| id | name | sex | created_time | updated_time |+----+--------+------+---------------------+---------------------+| 6 | 张三 | 1 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 || 7 | 李四 | 1 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 || 8 | 张三 | 1 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 || 9 | 李四 | 1 | 2020-03-05 17:55:55 | 2020-03-05 17:55:55 |+----+--------+------+---------------------+---------------------+4 rows in set (0.00 sec)
5. 男生的name重复记录仅保留1条,其他的多余重复记录删除?
mysql> select max(id) from white_user_new where sex = 1 group by name having count(*) > 1;+---------+| max(id) |+---------+| 8 || 9 |+---------+2 rows in set (0.00 sec)
使用子表, delete语句里的'where sex = 1'别忘了写!
mysql> delete from white_user_new where sex =1 and id not in (select max_id from (select max(id) as max_id from white_user_new where sex = 1 group by name having count(*) >1) b);Query OK, 2 rows affected (0.00 sec) mysql> select * from white_user_new ; +----+--------+------+---------------------+---------------------+| id | name | sex | created_time | updated_time |+----+--------+------+---------------------+---------------------+| 8 | 张三 | 1 | 2020-03-05 18:34:13 | 2020-03-05 18:34:13 || 9 | 李四 | 1 | 2020-03-05 18:34:13 | 2020-03-05 18:34:13 || 10 | 王五 | 0 | 2020-03-05 18:34:13 | 2020-03-05 18:34:13 |+----+--------+------+---------------------+---------------------+3 rows in set (0.00 sec)
上面的语句是正确的。
如果写成:
mysql> delete a from white_user_new a ,(select max(id) as max_id from white_user_new where sex = 1 group by name having count(*) > 1) bwhere a.id<>b.max_id;Query OK, 5 rows affected (0.00 sec) mysql> select * from white_user_new ; Empty set (0.00 sec) mysql>
会把所有的记录都删除掉!
如果写成:
mysql> delete from white_user_new where id not in (select max(id) from white_user_new where sex = 1 group by name having count(*) > 1);
则报错 ERROR 1093 (HY000): You can't specify target table 'white_user_new' for update in FROM clause
6. Mysql事物未COMMIT可以回滚吗?COMMIT之后还可以回滚吗?
start TRANSACTION;delete from article where aid= 3;COMMIT; ROLLBACK; -- 此时回滚无效,COMMIT后无法回滚---------------------------------start TRANSACTION;delete from article where aid= 3;ROLLBACK; -- 此时回滚有效,未COMMIT可以回滚
历史文章:
Mysql 不走索引的情况
Mysql查询年的第多少周
Mysql,replace into,存在则更新,不存在则插入
Mysql修改字段名、字段类型、指定位置添加字段、删除字段、修改表名、表注释
「欢迎关注,一起学习,一起进步」