【原】测试需要掌握的数据库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.条件查询
查询符合设定条件的数据
语法：select * from 表名 where 条件;

1.1比较运算符

等于=
小于<
大于>
大于等于>=
小于等于<=
不等于!=或者<>

查询身高大于170的学生：SELECT * from student WHERE height>170;
在这里插入图片描述
查询体重小于等于70的学生：SELECT * from student WHERE weight<=70;

查询不在“三班”的学生：SELECT * from student WHERE class_id!=3;

1.2逻辑运算符

and
or
not
查询性别为女且身高大于170的女生：SELECT * from student WHERE gender='female' and height>170;

在这里插入图片描述

查询性别为男或者体重小于70的学生：SELECT * from student WHERE gender='male' and weight<70;
在这里插入图片描述

查询不在三班的学生：SELECT * from student WHERE not class_id=3;
在这里插入图片描述
1.3模糊查询

like
_代表一个任意字符
代表多个任意字符

查询姓李的学生:SELECT * from student WHERE name like '李%';
在这里插入图片描述
查询姓李且名字有三个字的学生:SELECT * from student WHERE name like '李__';

1.4范围查询
4. in，代表是否在范围内

查询身高在165-170的学生：SELECT * from student WHERE height in (165,170);
在这里插入图片描述
查询身高不在165-170的学生：SELECT * from student WHERE height not in (165,170);

1.5优先级
1.小括号>not>比较运算符>逻辑运算符
2.and>or