黑马程序员---mysql数据库---条件查询

柔情只为你懂 2022-11-27 10:28 152阅读 0赞

一：比较运算符：  
where语句支持的运算符:

比较运算符  
逻辑运算符  
模糊查询  
范围查询  
空判断  
1：查询大于18岁的信息

mysql> select * from students where age>18;
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | height | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | 11 | 金星         |   33 | 162.00 | 中性   |      3 |          |
    | 14 | 周杰         |   34 | 176.00 | 女     |      5 |           |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    +----+--------------+------+--------+--------+--------+-----------+
    11 rows in set (0.00 sec)

2：查询小于18岁的信息

mysql> select * from students where age<18;
    +----+--------+------+--------+--------+--------+-----------+
    | id | name   | age  | height | gender | cls_id | is_delete |
    +----+--------+------+--------+--------+--------+-----------+
    | 12 | 静香   |   12 | 180.00 | 女     |      4 |           |
    | 13 | 郭靖   |   12 | 170.00 | 男     |      4 |           |
    +----+--------+------+--------+--------+--------+-----------+
    2 rows in set (0.00 sec)

3：查询小于等于，大于等于18岁的信息：

mysql> select * from students where age<=18;
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | height | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
    |  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |
    |  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          |
    | 12 | 静香      |   12 | 180.00 | 女     |      4 |           |
    | 13 | 郭靖      |   12 | 170.00 | 男     |      4 |           |
    +----+-----------+------+--------+--------+--------+-----------+
    5 rows in set (0.00 sec)
    
    mysql> select * from students where age>=18;
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | height | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |
    |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | 11 | 金星         |   33 | 162.00 | 中性   |      3 |          |
    | 14 | 周杰         |   34 | 176.00 | 女     |      5 |           |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    +----+--------------+------+--------+--------+--------+-----------+
    14 rows in set (0.00 sec)

4：查询等于,不等于18岁的信息。

mysql> select * from students where age=18;
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | height | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
    |  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |
    |  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          |
    +----+-----------+------+--------+--------+--------+-----------+
    3 rows in set (0.00 sec)
    
    mysql> select * from students where age!=18;
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | height | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | 11 | 金星         |   33 | 162.00 | 中性   |      3 |          |
    | 12 | 静香         |   12 | 180.00 | 女     |      4 |           |
    | 13 | 郭靖         |   12 | 170.00 | 男     |      4 |           |
    | 14 | 周杰         |   34 | 176.00 | 女     |      5 |           |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    +----+--------------+------+--------+--------+--------+-----------+
    13 rows in set (0.00 sec)
    
    mysql> select * from students where age<>18;
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | height | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | 11 | 金星         |   33 | 162.00 | 中性   |      3 |          |
    | 12 | 静香         |   12 | 180.00 | 女     |      4 |           |
    | 13 | 郭靖         |   12 | 170.00 | 男     |      4 |           |
    | 14 | 周杰         |   34 | 176.00 | 女     |      5 |           |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    +----+--------------+------+--------+--------+--------+-----------+
    13 rows in set (0.00 sec)

二：逻辑运算符：  
and  
or  
not  
1：查询18到28岁之间的学生信息。  
2：18以上或者身高高过180(包含)以上  
3：不在 18岁以上的女性 这个范围内的信息

mysql> select * from student where age > 18 and age < 28;
    ERROR 1146 (42S02): Table 'python39.student' doesn't exist
    mysql> select * from students where age > 18 and age < 28;
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | height | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  9 | 程坤      |   27 | 181.00 | 男     |      2 |           |
    | 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           |
    +----+-----------+------+--------+--------+--------+-----------+
    2 rows in set (0.00 sec)
    
    mysql> select * from students where age>18 or height>180;
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | height | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | 11 | 金星         |   33 | 162.00 | 中性   |      3 |          |
    | 14 | 周杰         |   34 | 176.00 | 女     |      5 |           |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    +----+--------------+------+--------+--------+--------+-----------+
    11 rows in set (0.00 sec)
    
    mysql> select * from students where not age>18;
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | height | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
    |  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |
    |  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          |
    | 12 | 静香      |   12 | 180.00 | 女     |      4 |           |
    | 13 | 郭靖      |   12 | 170.00 | 男     |      4 |           |
    +----+-----------+------+--------+--------+--------+-----------+
    5 rows in set (0.00 sec)

三： 模糊查询  
like是模糊查询关键字  
%表示任意多个任意字符  
\_表示一个任意字符

1： 查询姓名中 以 “小” 开始的名字  
2： 查询姓名中 有 “小” 所有的名字  
3： 查询有2个字的名字  
4：查询有3个字的名字  
5：查询至少有2个字的名字

mysql> select * from students where name like "小%";
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | height | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
    |  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |
    +----+-----------+------+--------+--------+--------+-----------+
    2 rows in set (0.00 sec)

mysql> select * from students where name like "% 小%"
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | height | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
    |  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |
    | 15 | 凌小小    |   28 | 180.00 | 女     |      1 |           |
    +----+-----------+------+--------+--------+--------+-----------+
    3 rows in set (0.00 sec)

mysql> select * from students where name like "__";
    +----+--------+------+--------+--------+--------+-----------+
    | id | name   | age  | height | gender | cls_id | is_delete |
    +----+--------+------+--------+--------+--------+-----------+
    |  1 | 小明   |   18 | 180.00 | 女     |      1 |           |
    |  5 | 黄蓉   |   38 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐   |   28 | 150.00 | 保密   |      2 |          |
    |  9 | 程坤   |   27 | 181.00 | 男     |      2 |           |
    | 11 | 金星   |   33 | 162.00 | 中性   |      3 |          |
    | 12 | 静香   |   12 | 180.00 | 女     |      4 |           |
    | 13 | 郭靖   |   12 | 170.00 | 男     |      4 |           |
    | 14 | 周杰   |   34 | 176.00 | 女     |      5 |           |
    +----+--------+------+--------+--------+--------+-----------+
    8 rows in set (0.00 sec)
    
    mysql> select * from students where name like "___";
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | height | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |
    |  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          |
    |  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          |
    |  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           |
    | 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           |
    | 15 | 凌小小    |   28 | 180.00 | 女     |      1 |           |
    +----+-----------+------+--------+--------+--------+-----------+
    7 rows in set (0.00 sec)
    
    mysql> select * from students where name like "__%";
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | height | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |
    |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | 11 | 金星         |   33 | 162.00 | 中性   |      3 |          |
    | 12 | 静香         |   12 | 180.00 | 女     |      4 |           |
    | 13 | 郭靖         |   12 | 170.00 | 男     |      4 |           |
    | 14 | 周杰         |   34 | 176.00 | 女     |      5 |           |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    +----+--------------+------+--------+--------+--------+-----------+
    16 rows in set (0.00 sec)

四： 范围查询  
between … and … 表示在一个连续的范围内查询  
in 表示在一个非连续的范围内查询

1： 查询 年龄为18、34的姓名

mysql> select * from students where age between 18 and 34;
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | height | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |
    |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | 11 | 金星         |   33 | 162.00 | 中性   |      3 |          |
    | 14 | 周杰         |   34 | 176.00 | 女     |      5 |           |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    +----+--------------+------+--------+--------+--------+-----------+
    11 rows in set (0.01 sec)

2：not in 不非连续的范围之内  
年龄不是 18、34岁的信息

mysql> select * from students where age not in (18,34);
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | height | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | 11 | 金星         |   33 | 162.00 | 中性   |      3 |          |
    | 12 | 静香         |   12 | 180.00 | 女     |      4 |           |
    | 13 | 郭靖         |   12 | 170.00 | 男     |      4 |           |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    +----+--------------+------+--------+--------+--------+-----------+
    12 rows in set (0.00 sec)

3：between … and …表示在一个连续的范围内  
查询 年龄在18到34之间的的信息

not between … and …表示不在一个连续的范围内  
查询 年龄不在在18到34之间的的信息  
：注意不能加括号，否则报错。

mysql> select * from students where age not between 18 and 34;
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | height | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          |
    |  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           |
    |  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           |
    | 12 | 静香      |   12 | 180.00 | 女     |      4 |           |
    | 13 | 郭靖      |   12 | 170.00 | 男     |      4 |           |
    +----+-----------+------+--------+--------+--------+-----------+
    5 rows in set (0.00 sec)

五：空判断：  
判断为空使用: is null  
判断非空使用: is not null

1：查询身高为空的  
2：查询身高不为空的  
注意是is not null 不是 not is null

mysql> select * from students where height is null;
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | height | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           |
    +----+-----------+------+--------+--------+--------+-----------+
    1 row in set (0.00 sec)
    
    mysql> select * from students where height is not null;
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | height | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |
    |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | 11 | 金星         |   33 | 162.00 | 中性   |      3 |          |
    | 12 | 静香         |   12 | 180.00 | 女     |      4 |           |
    | 13 | 郭靖         |   12 | 170.00 | 男     |      4 |           |
    | 14 | 周杰         |   34 | 176.00 | 女     |      5 |           |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    +----+--------------+------+--------+--------+--------+-----------+
    15 rows in set (0.00 sec)