本文已参与「新人创作礼」活动，一起开启掘金创作之路。

一、前言

未看过文章(一)的朋友，需要准备测试数据 测试数据sql如下：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `id` int(11) NOT NULL,
  `class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `created` datetime(6) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '一班', '2021-07-17 13:40:30.000000');
INSERT INTO `class` VALUES (2, '二班', '2021-07-18 13:40:48.000000');
INSERT INTO `class` VALUES (3, '三班', '2021-07-19 13:40:48.000000');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL,
  `created` datetime(6) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `class_id` int(11) NOT NULL,
  `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `height` int(3) NOT NULL,
  `weight` int(3) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '2021-07-19 13:42:35.000000', '张顺', 1, 'male', 170, 65);
INSERT INTO `student` VALUES (2, '2021-07-19 13:42:35.000000', '张玲', 1, 'female', 170, 65);
INSERT INTO `student` VALUES (3, '2021-07-19 13:42:35.000000', '李广', 2, 'male', 180, 68);
INSERT INTO `student` VALUES (4, '2021-07-19 13:42:35.000000', '李三四', 2, 'female', 170, 65);
INSERT INTO `student` VALUES (5, '2021-07-19 13:42:35.000000', '赵云', 3, 'male', 199, 100);
INSERT INTO `student` VALUES (6, '2021-07-19 13:42:35.000000', '马超', 3, 'female', 171, 66);
INSERT INTO `student` VALUES (7, '2021-07-19 13:42:35.000000', '诸葛亮', 3, 'male', 170, 65);
INSERT INTO `student` VALUES (8, '2021-07-19 13:42:35.000000', '刘备', 3, 'male', 202, 105);
INSERT INTO `student` VALUES (9, '2021-07-19 13:42:35.000000', '曹操', 3, 'male', 181, 80);
INSERT INTO `student` VALUES (10, '2021-07-19 13:42:35.000000', '黄忠', 2, 'female', 166, 50);

SET FOREIGN_KEY_CHECKS = 1;

class表数据

student表数据

二、具体讲解**

1.BETWEEN

查询两个值之间的数据 语法：select * from 表名 where 字段名 between 值 and 值; 查询体重在65-70的学生: select * FROM student WHERE weight BETWEEN 65 AND 70; 2.AS

为字段名指定别名 语法：select 字段名1 AS 别名,字段名2 AS 别名,.... from 表名 将字段名name改为别名学生名称显示: select name AS '学生名称' FROM student 3.Distinct

查询时忽略重复值 语法:SELECT DISTINCT 字段名 FROM 表名 查询学生所在的班级有哪些: select DISTINCT class_id FROM student 4.SUM

求和 语法:select SUM(字段名) from 表名; 查询学生总身高: select SUM(height) AS '学生总身高' FROM student; 5.COUNT

返回匹配指定条件的行数。 语法:select COUNT(字段名) from 表名; 查询有多少学生: select count(*) FROM student;

6..AVG 计算平均值 语法:select AVG(字段名) from 表名; 计算学生平均身高: select AVG(height) AS '学生平均身高' FROM student; 7.GROUP BY

根据一或多个字段对查询结果集进行分组 语法: select 字段名 关键字(字段名) from 表名 group by 字段名; 查询男生、女生各有多少: SELECT gender,count(id) FROM student GROUP BY gender 8.ORDER BY

对结果集进行排序 语法:select 字段名 from 表名 order by 字段名 升/降序关键字; 对学生身高降序排列(由高到矮): SELECT * FROM student ORDER BY height desc; 对学生体重升序排列(由轻到重): SELECT * FROM student ORDER BY weight asc; 9.limit

返回查询结果的数目 语法:select 字段名 from 表名 limit 开始条数,结尾条数; 返回前三条学生信息: SELECT * FROM student LIMIT 1,3; 10.having

当我们用到聚合函数(sum、count等)，又需要筛选条件时，having就派上用场了，因为WHERE是在聚合前筛选记录的，having和group by是组合一起再聚合后使用 语法:没有固定语法 查班级人数大于等于3的班级: SELECT count(*) FROM student GROUP BY class_id HAVING COUNT(id)>=3;