Mysql 条件查询（like、between、and、in······）

落日映苍穹つ 2023-06-20 09:24 77阅读 0赞

**目录**

Mysql 条件查询

示例

一、比较运算和逻辑运算符符

二、范围运算符

三、列表运算符

四、模糊匹配符

五、空值判断符

--------------------

# Mysql 条件查询 #

*  **Mysql 条件查询即where子句后跟条件**

**运算符：**

![format_png][]

**格式：**

select 字段 from 表名 where 条件;

--------------------

# **示例** #

**使用下表为例：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTg4MDAz_size_16_color_FFFFFF_t_70][]

# #

## 一、比较运算和逻辑运算符符 ##

**示例1**：查询年龄大于20，并且小于30的信息（下述三种都可以，结果一样）

SELECT * FROM student WHERE age >= 20 && age <=30;
    SELECT * FROM student WHERE age >= 20 AND age <=30;
    SELECT * FROM student WHERE age BETWEEN 20 AND 30;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTg4MDAz_size_16_color_FFFFFF_t_70 1][]

**示例2**：查询年龄22岁，18岁 ，25岁的信息（下述两种方法结果相同）

SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
    SELECT * FROM student WHERE age IN (22,18,25) ;

![20191209220430125.png][]

## 二、范围运算符 ##

**示例3**：查询数学成绩在20到80之间的学生

## ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTg4MDAz_size_16_color_FFFFFF_t_70 2][] ##

## 三、列表运算符 ##

**示例4：**查询地址属于杭州或香港的学生

## ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTg4MDAz_size_16_color_FFFFFF_t_70 3][] ##

## 四、模糊匹配符 ##

**示例4**：like运算符模糊查询

配合like使用的占位符：

*  \_：单个任意字符
 *  %：多个任意字符

-- 查询姓马的有哪些? like
    SELECT * FROM student WHERE NAME LIKE '马%';
    
    -- 查询姓名第二个字是化的人
    SELECT*FROM student WHERE NAME LIKE '_化%';
    
    -- 查询姓名是3个字的人
    SELECT * FROM student WHERE NAME LIKE '___';
    
    -- 查询姓名中包含马的人
    SELECT * FROM student WHERE NAME LIKE '%马%';

## 五、空值判断符 ##

**示例5**：查询英语成绩为NULL的信息

SELECT * FROM student WHERE english = NULL; -- 不对的。null值不能使用= (!=) 判断
    SELECT * FROM student WHERE english IS NULL;

![20191209220826479.png][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zczEuYmFpZHUuY29tLzZPTlhzamlwMFFJWjh0eWhucS9pdC91PTE5NTA2NDM3NjIsNzQ0NDcwOTU1JmZtPTE3MyZhcHA9NDkmZj1KUEVH?x-oss-process=image/format,png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTg4MDAz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191209220634401.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTg4MDAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTg4MDAz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191209220228492.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTg4MDAz,size_16,color_FFFFFF,t_70
[20191209220430125.png]: https://img-blog.csdnimg.cn/20191209220430125.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTg4MDAz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201229105034813.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTg4MDAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTg4MDAz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201229105423870.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTg4MDAz,size_16,color_FFFFFF,t_70
[20191209220826479.png]: https://img-blog.csdnimg.cn/20191209220826479.png