1 数据准备
DROP TABLE IF EXISTS ` t_stu` ;
CREATE TABLE ` t_stu` (
` id` int NOT NULL AUTO_INCREMENT ,
` name` varchar ( 255 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ,
` age` int NULL DEFAULT NULL ,
` grade` int NULL DEFAULT NULL ,
` sex` varchar ( 255 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ,
` addr` varchar ( 255 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ,
PRIMARY KEY ( ` id` ) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1010 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO ` t_stu` VALUES ( 1 , '王大锤' , 100 , 88 , '男' , NULL ) ;
INSERT INTO ` t_stu` VALUES ( 1006 , '令狐冲' , 19 , 80 , '男' , '北京市海淀区宝盛北里西区28号' ) ;
INSERT INTO ` t_stu` VALUES ( 1007 , '任盈盈' , 100 , 88 , '男' , '北京市海淀区宝盛北里西区28号' ) ;
INSERT INTO ` t_stu` VALUES ( 1008 , '东方' , 16 , 83 , '男' , '北京市海淀区宝盛北里西区28号' ) ;
INSERT INTO ` t_stu` VALUES ( 1009 , '张三丰' , 18 , 90 , '男' , '北京市海淀区宝盛北里西区28号' ) ;
INSERT INTO ` t_stu` VALUES ( 1010 , '郭靖' , 16 , 86 , '男' , '北京市海淀区宝盛北里西区28号' ) ;
INSERT INTO ` t_stu` VALUES ( 1011 , '张三丰' , 20 , 90 , '女' , '北京市海淀区宝盛北里西区28号' ) ;
INSERT INTO ` t_stu` VALUES ( 1012 , 'ABC' , 20 , 96 , '女' , '北京市海淀区宝盛北里西区28号' ) ;
SET FOREIGN_KEY_CHECKS = 1 ;
2 mysql-查询出上表中,name列重复的所有行
select stu. * from t_stu stu where stu. name = ( select name from t_stu GROUP BY name having count ( name) > 1 )
3 where和having的区别
3.1 where
3.2 having