1、新建数据库表DROP TABLE 诔罨租磊IF EXISTS `study_1`;CREATE TABLE `study_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` varchar(5) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;数据库表中插入测试数据INSERT INTO study_1 VALUES ('1', '张三', '20', '男');INSERT INTO study_1 VALUES ('2', '李四', '21', '女');INSERT INTO study_1 VALUES ('3', '王五', '22', '男');INSERT INTO study_1 VALUES ('4', '赵六', '23', '女');INSERT INTO study_1 VALUES ('5', '吴七', '24', '女');INSERT INTO study_1 VALUES ('6', '钱八', '25', '男');INSERT INTO study_1 VALUES ('7', '孙九', '26', '男');
7、使用LIKE进行字符串匹配查询（示例结果如图按顺序）通配符 % 匹配示例一，向后匹配SELECT * FROM study_1 WHERE name LIKE '张%';示例二，向前匹配SELECT * FROM study_1 WHERE name LIKE '%四';示例三，前后匹配SELECT * FROM study_1 WHERE name LIKE '%五%';下划线匹配SELECT * FROM study_1 WHERE name LIKE '张_';
8、查询空数据（示例结果如图按顺序）往表中再插入一条sex值为控制的数据INSERT INTO `study_1` VALUES ('8', '周十', '27', null);示例SELECT * FROM study_1 WHERE sex IS NULL;示例2SELECT * FROM study_1 WHERE sex IS NOT NULL;
10、使用DISTINCT查询不重复的数据（示例结果如图按顺序）插入一条与id=1数据内容一致的数据I鲍伊酷雪NSERT INTO `study_1` VALUES (&#泌驾台佐39;9', '张三', '20', '男');普通查询能查询到两条相同的数据示例SELECT name FROM study_1 WHERE name='张三';使用DISTINCT剔除相同的数据示例SELECT DISTINCT name FROM study_1 WHERE name='张三';
12、使用 [GROUP BY <字段>] [HAVING <查询表达式>] 旅邯佤践进行分组查询（示例结果如掬研疔缎图按顺序）新增分组字段ALTER TABLE study_1 ADD study_group int(11);为原有的数据分组UPDATE study_1 set study_group=1 where id<3;UPDATE study_1 set study_group=2 where id>=3 AND id<7;UPDATE study_1 set study_group=3 where id>=7;示例1.把各分组的姓名按分组查询并组合起来。SELECT GROUP_CONCAT(name) AS names,study_group FROM study_1 GROUP BY study_group;示例2.把各分组的姓名按分组查询并组合起来,并使用HAVING限制查询分组人数在2人以上的数据。SELECT GROUP_CONCAT(name) AS names,study_group,sex FROM study_1 GROUP BY study_group HAVING COUNT(name)>2;示例3.把各分组的姓名按分组查询并组合起来,WITH ROLLUP在最后一条显示所有记录。SELECT GROUP_CONCAT(name) AS names,study_group FROM study_1 GROUP BY study_group WITH ROLLUP;示例4.多字段分组SELECT GROUP_CONCAT(name) AS names,study_group,sex FROM study_1 GROUP BY study_group,sex;
