一：比较运算符：
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)
浙公网安备 33010602011771号