mysql now()查询后带着.0_MySQL常用面试题（一）

表结构：

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修改字段名、字段类型、指定位置添加字段、删除字段、修改表名、表注释

「欢迎关注，一起学习，一起进步」