一、前言
未看过文章(一)的朋友，需要准备测试数据
测试数据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;
