【05实操】模糊查询、条件查询、分组查询

小熊 MySQL评论3,830字数 3787阅读12分37秒阅读模式

本小节介绍如何使用关键字模糊查询符合要求的结果集,模糊查询在实际业务中主要用于搜索关键字查询需要的信息。

模糊查询

1.模糊查询表达式

% 表示指代任意内容,例如'%小%' 表示包含 小 的表达式,且 小 前后都有内容,'%小' 表示以 小 结尾的表达式,王 前面有内容,后面没有内容,'小%' 表示以 小 开头的表达式,小 前面没有内容,后面有内容。

2.使用LIKE模糊查询

以 teacher 表为例,需要查询出姓 王 的教师信息结果集:

SELECT * FROM teacher WHERE name LIKE '王%';

执行结果如下图:

【05实操】模糊查询、条件查询、分组查询

条件查询

符号 说明 举例
< 小于,< 左边的值如果小于右边的值,则结果为 TRUE,否则为 FALSE 如 : 满足年龄小于 18 的条件 age < 18
= 等于,= 左边的值如果等于右边的值,则结果为 TRUE,否则为 FALSE 如 : 姓名为 小明 的条件 name = '小明'
> 大于,> 左边的值如果大于右边的值,则结果为 TRUE,否则为 FALSE 如 : 时间戳大于 2020-03-30 00:00:00的条件 time > 1585497600
<> 不等于,<>还可写成 != ,左边的值如果不等于右边的值,则结果为 TRUE,否则为 FALSE 如 : 年份不等于2012的条件 year != 或 year <> 2012
<= 小于等于,<= 左边的值如果大于右边的值,则结果为 FALSE,否则为 TRUE 如 : 满足年龄小于等于 18 的条件 age <= 18
>= 大于等于,>= 左边的值如果小于右边的值,则结果为 FALSE,否则为 TRUE 如 : 满足年龄大于等于 18 的条件 age >= 18
LIKE 模糊条件,LIKE 右边的值如果包含左边的值,则结果返回TRUE,否则为 FALSE 如 : 满足身份证号为 420 开头的条件 id_number LIKE '420%',其中 % 表示任意值
NOT LIKE 不满足模糊条件,LIKE 右边的值如果不包含左边的值,则结果返回TRUE,否则为 FALSE 如 : 满足身份证号不是 X 结尾的条件 id_number NOT LIKE '%X',其中 % 表示任意值
BETWEEN AND 在两个值之间(包含两端值) 如 : 年龄满足 大于等于20 和 小于等于30 的条件 age BETWEEN 20 AND 30
NOT BETWEEN AND 不在在两个值之间(不包含两端值) 如 : 年龄满足 小于20 和 大于30 的条件 age NOT BETWEEN 20 AND 30
IS NULL 空,IS NULL 左边的值如果为空,则返回TRUE,否则为FALSE 如 : 年龄满足 邮箱为空 的条件 email IS NULL
IS NOT NULL 不是空,IS NOT NULL 左边的值如果不为空,则返回TRUE,否则为FALSE 如 : 年龄满足 邮箱不为空 的条件 email IS NOT NULL

1. 单条件查询

以 teacher 表为例,查询年龄大于 18 的教师信息:

SELECT * FROM teacher WHERE age > 18;

执行结果如下图:

【05实操】模糊查询、条件查询、分组查询

再比如使用 LIKE 模糊查询身份证号以 020X 结尾的教师信息:

SELECT * FROM teacher WHERE id_number LIKE '%020X';

【05实操】模糊查询、条件查询、分组查询

2. AND 多条件查询

以 teacher 表为例,查询年龄大于 18 和 教师姓氏 王 的教师信息:

SELECT * FROM teacher WHERE age > 18 AND name LIKE  '王%';

执行结果如下图:
【05实操】模糊查询、条件查询、分组查询

Tips:这里还想加更多的条件可以在后面继续跟 AND,LIKE 后面表达式中 % 表示指代任意内容。

3. OR 多条件查询

以 teacher 表为例,查询年龄大于 25 或 教师姓氏 王 的教师信息:

SELECT * FROM teacher WHERE age > 25 OR name LIKE  '王%';

执行结果如下图:
【05实操】模糊查询、条件查询、分组查询

Tips:这里还想加更多的条件可以在后面继续跟 OR,若 AND 和 OR 混合使用则需要把同一组表达式用 () 包起来,如 :(age > 25 AND name LIKE '王%') OR (age < 20 AND name LIKE '刘%')

4. BETWEEN AND 更新数据

以 teacher 表为例,将 age 在 20 和 30 之间的教师身份证设置为无:

UPDATE teacher SET id_number = '无' WHERE age BETWEEN 20 AND 30;

【05实操】模糊查询、条件查询、分组查询

Tips:注意 BETWEEN AND 包含两端的值。

5. IS NULL 查询

为了演示方便,我们先给 teacher 表增加一个字段 email:

ALTER TABLE `item_base`.`teacher`
ADD COLUMN `email` varchar(50) NULL AFTER `id_number`;

执行结果如下图:
【05实操】模糊查询、条件查询、分组查询

往 teacher 表插入几条测试数据:

INSERT INTO teacher
(name,age,id_number,email)
VALUES
('姓名甲',19,'42011720200604077X',NULL),
('姓名乙',18,'42011720200604099X','123@qq.com'),
('姓名丙',20,'42011720200604020X',NULL),
('姓名丁',21,'42011720200604022X','345@qq.com'),
('姓名戊',22,'42011720200604033X',NULL);

然后查询 email 为 NULL 的教师信息结果集:

SELECT * FROM teacher WHERE email IS NULL;

执行结果如下图:

【05实操】模糊查询、条件查询、分组查询

小结

本小节介绍了

1、模糊查询表达式、如何模糊查询,需要注意的是实际业务中如非必要尽量避免使用模糊查询,如果必须要用,尽量选择最左匹配原则,因为这样可以使用到索引,形如 '王%' 这种格式,否则一旦数据量很大,没有用到索引的模糊查询性能可能会很差。

2、如果在查询、更新语句后面加上 WHERE 条件约束,需要注意的是当使用 >、<、这样的操作符和空值比较的时候,NULL值与任何其它值的比较(即使是NULL)永远不会为 TRUE,例如要查询 name 为 NULL 的结果集要写成 name IS NULL,而不能使用 name=NULL。

进阶学习-GROUP BY分组

本小节介绍如何对查询结果使用 GROUP BY 分组,GROUP BY 分组是对指定一个或多个字段分组,使用分组可以较好地对数据结果分析和处理。

1. 单字段分组

先查看 course 表所有数据:

SELECT * FROM course;

查询结果如下图:

【05实操】模糊查询、条件查询、分组查询

以表 course 为例,需要查出所有课程教师相关信息,按照 teacher_id 字段分组可去除重复数据:

 SELECT teacher_id FROM course GROUP BY teacher_id;

查询结果如下图:

【05实操】模糊查询、条件查询、分组查询

根据上面分组结果还可以使用 LEFT JOIN 连接查询分组:

SELECT c.teacher_id,t.* FROM course c LEFT JOIN teacher t ON c.teacher_id=t.id GROUP BY c.teacher_id;

执行结果如下图:

【05实操】模糊查询、条件查询、分组查询

2. 多字段分组

以查询学生选课关联表为例,查询学生选课相关信息:

SELECT * FROM 
student_course a 
INNER JOIN 
student b 
ON a.student_id=b.id 
INNER JOIN 
course c 
ON a.course_id=c.id 
INNER JOIN teacher d 
ON c.teacher_id=d.id;
2345678910

查询结果如下图;

【05实操】模糊查询、条件查询、分组查询

上图是不同学生选课相关的信息数据,可以使用 GROUP BY 按照 teacher_id、course_id 分组来展示所有教师的所有课程信息:

SELECT c.teacher_id,a.course_id,c.course_name,d.name
FROM 
student_course a 
INNER JOIN 
student b 
ON a.student_id=b.id 
INNER JOIN course c 
ON a.course_id=c.id 
INNER JOIN teacher d
ON c.teacher_id=d.id 
GROUP BY c.teacher_id,a.course_id;

执行结果如下图:

【05实操】模糊查询、条件查询、分组查询

Tips:单字段分组和多字段分组的区别在于,单字段是以一个字段来判断数据是否重复分组出来的结果,多字段分组是以多个字段同时来判断是否重复分组出来的结果。

小结

本小节介绍了如何使用 GROUP BY 对查询结果集分组筛选出想要的内容,相对于前面小节的内容来说,本小节 GROUP BY 分组较为复杂,需要仔细的体会和思考,需要注意的是使用 GROUP BY 分组时,要将 MySQL 的 sql model 配置中 ONLY_FULL_GROUP_BY 的值去除掉,如果有该 sql_model 配置,在 SELECT 中的列,没有在 GROUP BY 中出现,那么这个 SQL 是不合法的,这样的严格模式对新手不友好,需要去除该配置,查看当前 sql_model 配置命令:

SELECT @@sql_mode;

执行结果如下图:

【05实操】模糊查询、条件查询、分组查询

上图中 sql_model 中并没有 ONLY_FULL_GROUP_BY 配置值,若想要配置 sql_mode 则可以在 MySQL 配置文件中 [mysqld] 下面增加 sql_model,设置好之后,重启 MySQL 即可,内容如下格式:

sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

weinxin
公众号
扫码订阅最新深度技术文,回复【资源】获取技术大礼包
小熊