python：mysql之LIKE操作符

接下来。装逼开始....

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

语法&＃xff1a;

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

pattern这里就是放指定模板的地方&＃xff0c;而这里就要用到“ % ”&＃xff0c;也叫做通配符

%如果是放在条件前面&＃xff0c;那就是查以...结尾的数据&＃xff1b;例如&＃xff1a;%李

%如果是放在条件后面&＃xff0c;那就是查以...开头的数据&＃xff1b;例如&＃xff1a;李%

%如果是在条件前后都存在&＃xff0c;那就是查包含的数据&＃xff1b;例如&＃xff1a;%李%

小知识点&＃xff1a;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &＃39;%z&＃39; at line 1


1064的错误就是LIKE查询时(语法错误)&＃xff0c;通配符处没加引号&＃xff0c;所以才会报错...

正确展示例如&＃xff1a;"%李%"

示例1&＃xff1a;终端运行sql且WHERE子句中使用LIKE

查询地址以Hang开头的人员信息

root&＃64;7c6316b19d80:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 140
Server version: 5.6.51 MySQL Community Server (GPL)mysql> mysql> select * from test_user where address like &＃39;Hang%&＃39;;
&＃43;----&＃43;--------&＃43;-------------&＃43;----------&＃43;
| id | name | mobile | address |
&＃43;----&＃43;--------&＃43;-------------&＃43;----------&＃43;
| 3 | python | 18856565858 | Hangzhou |
| 4 | java | 17756565858 | Hangzhou |
| 5 | php | 15556565858 | Hangzhou |
| 6 | c# | 17748484142 | Hangzhou |
&＃43;----&＃43;--------&＃43;-------------&＃43;----------&＃43;
4 rows in set (0.00 sec)
mysql>

查询地址以u结尾的人员信息

mysql> select * from test_user where address like &＃39;%u&＃39;;
&＃43;----&＃43;--------&＃43;-------------&＃43;----------&＃43;
| id | name | mobile | address |
&＃43;----&＃43;--------&＃43;-------------&＃43;----------&＃43;
| 3 | python | 18856565858 | Hangzhou |
| 4 | java | 17756565858 | Hangzhou |
| 5 | php | 15556565858 | Hangzhou |
| 6 | c# | 17748484142 | Hangzhou |
&＃43;----&＃43;--------&＃43;-------------&＃43;----------&＃43;
4 rows in set (0.00 sec)
mysql>

示例2&＃xff1a;使用python脚本执行含LIKE的sql语句

查询地址包含z字符的人员信息

import pymysql# 连接数据库
connection &＃61; pymysql.connect(host&＃61;"localhost", user&＃61;"root", password&＃61;"123456",database&＃61;"testing", port&＃61;3306, charset&＃61;&＃39;utf8&＃39;,cursorclass&＃61;pymysql.cursors.DictCursor)try:with connection:with connection.cursor() as cursor:sql &＃61; """SELECT*FROMtest_userWHEREaddress LIKE &＃39;%z%&＃39;;"""cursor.execute(sql)result &＃61; cursor.fetchall()for i in result:print(i)except pymysql.err.MySQLError as _error:raise _error

{&＃39;id&＃39;: 3, &＃39;name&＃39;: &＃39;python&＃39;, &＃39;mobile&＃39;: &＃39;18856565858&＃39;, &＃39;address&＃39;: &＃39;Hangzhou&＃39;}
{&＃39;id&＃39;: 4, &＃39;name&＃39;: &＃39;java&＃39;, &＃39;mobile&＃39;: &＃39;17756565858&＃39;, &＃39;address&＃39;: &＃39;Hangzhou&＃39;}
{&＃39;id&＃39;: 5, &＃39;name&＃39;: &＃39;php&＃39;, &＃39;mobile&＃39;: &＃39;15556565858&＃39;, &＃39;address&＃39;: &＃39;Hangzhou&＃39;}
{&＃39;id&＃39;: 6, &＃39;name&＃39;: &＃39;c#&＃39;, &＃39;mobile&＃39;: &＃39;17748484142&＃39;, &＃39;address&＃39;: &＃39;Hangzhou&＃39;}Process finished with exit code 0

查询地址不包含z字符的人员信息

try:with connection:with connection.cursor() as cursor:sql &＃61; """SELECT*FROMtest_userWHEREaddress NOT LIKE &＃39;%z%&＃39;;"""cursor.execute(sql)result &＃61; cursor.fetchall()for i in result:print(i)except pymysql.err.MySQLError as _error:raise _error

{&＃39;id&＃39;: 1, &＃39;name&＃39;: &＃39;张三三&＃39;, &＃39;mobile&＃39;: &＃39;17748484141&＃39;, &＃39;address&＃39;: &＃39;浙江杭州&＃39;}
{&＃39;id&＃39;: 9, &＃39;name&＃39;: &＃39;111&＃39;, &＃39;mobile&＃39;: &＃39;18847474549&＃39;, &＃39;address&＃39;: &＃39;浙江杭州&＃39;}Process finished with exit code 0

至此&＃xff0c;使用LIKE操作符查询完毕...

以上总结或许能帮助到你&＃xff0c;或许帮助不到你&＃xff0c;但还是希望能帮助到你&＃xff0c;如有疑问、歧义&＃xff0c;直接私信会及时修正发布&＃xff1b;如果觉得还不错的话&＃xff0c;记得点赞哟&＃xff0c;谢谢&＃xff01;

未完&＃xff0c;待续…

一直都在努力&＃xff0c;希望您也是&＃xff01;

微信搜索公众号&＃xff1a;就用python