mysql中模糊查询使用like,支持两个特殊字符:"_":任意一个字符"%":任意数量的任意字符
假如有1个表
mysql> select * from stu;
+----+----------+------+-----+---------+
| id | name | age | sex | classid |
+----+----------+------+-----+---------+
| 1 | zhangsan | 18 | m | php01 |
| 2 | miss li | NULL | w | php02 |
| 5 | lisi | 20 | w | php01 |
| 6 | wangwu | 50 | m | php02 |
| 7 | zhaoliu | 30 | w | |
| 11 | beiye | 80 | m | php04 |
| 12 | diaosi | 120 | m | php01 |
| 13 | shit | 22 | w | php03 |
| 15 | bext | NULL | w | |
| 16 | qq | NULL | w | php04 |
+----+----------+------+-----+---------+
10 rows in set (0.00 sec)
要查询每个班的总人数我们使用group by:
mysql> select classid class_name,count(id) stu_num from stu group by classid;
+------------+---------+
| class_name | stu_num |
+------------+---------+
| | 2 |
| php01 | 3 |
| php02 | 2 |
| php03 | 1 |
| php04 | 2 |
+------------+---------+
5 rows in set (0.00 sec)
此时发现有一行是空格,要得到取出没有班级的一行的目的,要怎么办呢?
我们可以查询表的结构:
mysql> describe stu;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| sex | enum('w','m') | NO | | w | |
| classid | char(11) | NO | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)
得知:classid 为char(11) 并且not null ,所以就形成一行空格班级了。
在这种情况下要得到所有班级的总人数我们就可以用到_ %:
mysql> select classid class_name,count(id) stu_num from stu where classid like
'%_%' group by classid;
+------------+---------+
| class_name | stu_num |
+------------+---------+
| php01 | 3 |
| php02 | 2 |
| php03 | 1 |
| php04 | 2 |
+------------+---------+
4 rows in set (0.00 sec)