一、前言
未看过文章(一)的朋友,需要准备测试数据
测试数据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;
测试交流、答疑Q群:814078962