测试需要掌握的数据库sql知识(二)：条件查询详解

本文主要是介绍测试需要掌握的数据库sql知识(二)：条件查询详解，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、前言
未看过文章一的朋友，需要准备测试数据
测试数据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比较运算符

1. 等于=
2. 小于<
3. 大于>
4. 大于等于>=
5. 小于等于<=
6. 不等于!=或者<>

查询身高大于170的学生：SELECT * from student WHERE height>170;

查询体重小于等于70的学生：SELECT * from student WHERE weight<=70;

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

1.2逻辑运算符

1. and
2. or
3. 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模糊查询

1. like
2. _代表一个任意字符
3. 代表多个任意字符

查询姓李的学生: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

测试交流、答疑Q群：814078962

这篇关于测试需要掌握的数据库sql知识(二)：条件查询详解的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！