| python：mysql之LIKE操作符 | 您所在的位置：网站首页 › coach酒神新款 › python：mysql之LIKE操作符 |
python：mysql之LIKE操作符
|
接下来。装逼开始.... LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。 语法： SELECT column_name(s) FROM table_name WHERE column_name LIKE patternpattern这里就是放指定模板的地方，而这里就要用到“ % ”，也叫做通配符 %如果是放在条件前面，那就是查以...结尾的数据；例如：%李 %如果是放在条件后面，那就是查以...开头的数据；例如：李% %如果是在条件前后都存在，那就是查包含的数据；例如：%李% 小知识点： 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 '%z' at line 11064的错误就是LIKE查询时(语法错误)，通配符处没加引号，所以才会报错... 正确展示例如："%李%" 示例1：终端运行sql且WHERE子句中使用LIKE 查询地址以Hang开头的人员信息 root@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 'Hang%'; +----+--------+-------------+----------+ | id | name | mobile | address | +----+--------+-------------+----------+ | 3 | python | 18856565858 | Hangzhou | | 4 | java | 17756565858 | Hangzhou | | 5 | php | 15556565858 | Hangzhou | | 6 | c# | 17748484142 | Hangzhou | +----+--------+-------------+----------+ 4 rows in set (0.00 sec) mysql>查询地址以u结尾的人员信息 mysql> select * from test_user where address like '%u'; +----+--------+-------------+----------+ | id | name | mobile | address | +----+--------+-------------+----------+ | 3 | python | 18856565858 | Hangzhou | | 4 | java | 17756565858 | Hangzhou | | 5 | php | 15556565858 | Hangzhou | | 6 | c# | 17748484142 | Hangzhou | +----+--------+-------------+----------+ 4 rows in set (0.00 sec) mysql>示例2：使用python脚本执行含LIKE的sql语句 查询地址包含z字符的人员信息 import pymysql # 连接数据库 connection = pymysql.connect(host="localhost", user="root", password="123456", database="testing", port=3306, charset='utf8', cursorclass=pymysql.cursors.DictCursor) try: with connection: with connection.cursor() as cursor: sql = """ SELECT * FROM test_user WHERE address LIKE '%z%'; """ cursor.execute(sql) result = cursor.fetchall() for i in result: print(i) except pymysql.err.MySQLError as _error: raise _error {'id': 3, 'name': 'python', 'mobile': '18856565858', 'address': 'Hangzhou'} {'id': 4, 'name': 'java', 'mobile': '17756565858', 'address': 'Hangzhou'} {'id': 5, 'name': 'php', 'mobile': '15556565858', 'address': 'Hangzhou'} {'id': 6, 'name': 'c#', 'mobile': '17748484142', 'address': 'Hangzhou'} Process finished with exit code 0查询地址不包含z字符的人员信息 try: with connection: with connection.cursor() as cursor: sql = """ SELECT * FROM test_user WHERE address NOT LIKE '%z%'; """ cursor.execute(sql) result = cursor.fetchall() for i in result: print(i) except pymysql.err.MySQLError as _error: raise _error {'id': 1, 'name': '张三三', 'mobile': '17748484141', 'address': '浙江杭州'} {'id': 9, 'name': '111', 'mobile': '18847474549', 'address': '浙江杭州'} Process finished with exit code 0至此，使用LIKE操作符查询完毕...
以上总结或许能帮助到你，或许帮助不到你，但还是希望能帮助到你，如有疑问、歧义，直接私信会及时修正发布；如果觉得还不错的话，记得点赞哟，谢谢！ 未完，待续… 一直都在努力，希望您也是！ 微信搜索公众号：就用python 
更多内容欢迎关注公众号
|
【本文地址】
公司简介
联系我们