【原】测试需要掌握的数据库sql知识(三)：十个必须掌握的关键字查询

曲鸟 2022-01-10

展开全文

一、前言
未看过文章(一)的朋友，需要准备测试数据
测试数据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;
在这里插入图片描述