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