MySQL学习足迹记录04--数据过滤--WHERE_MySQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL学习足迹记录04--数据过滤--WHERE_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4488字，纯文字阅读大概需要7分钟。
内容图文
bitsCN.com
MySQL学习足迹记录04--数据过滤--WHERE
1.使用WHERE子句
eg: mysql> SELECT prod_name,prod_price FROM products WHERE prod_price=2.50;+---------------+------------+| prod_name | prod_price |+---------------+------------+| Carrots | 2.50 || TNT (1 stick) | 2.50 |+---------------+------------+
TIPs:
*在同时使用ORDER BY和WHERE子句时,ORDER BY位于WHERE之后，否则出错。
2.WHERE子句的操作符
等于：=
不等于：<>或！=
小于：<
小于等于：<=
大于：>
大于等于：>=
在指定的两个值之间 BETWEEN
3.检测单个值
eg: mysql> SELECT prod_name,prod_price FROM products WHERE prod_name='fuses';+-----------------+---------------+ #和字符串比较需用单引号限定| prod_name | prod_price |+-----------------+---------------+| Fuses | 3.42 |+-----------------+----------------+
4.小于限定
eg: mysql> SELECT prod_name,prod_price FROM products WHERE prod_price<10;+---------------+------------+| prod_name | prod_price |+---------------+------------+| .5 ton anvil | 5.99 || 1 ton anvil | 9.99 || Carrots | 2.50 || Fuses | 3.42 || Oil can | 8.99 || Sling | 4.49 || TNT (1 stick) | 2.50 |+---------------+------------+7 rows in set (0.00 sec)
5.小于等于限定
eg: mysql> SELECT prod_name,prod_price FROM products WHERE prod_price<=10;+----------------+------------+| prod_name | prod_price |+----------------+------------+| .5 ton anvil | 5.99 || 1 ton anvil | 9.99 || Bird seed | 10.00 || Carrots | 2.50 || Fuses | 3.42 || Oil can | 8.99 || Sling | 4.49 || TNT (1 stick) | 2.50 || TNT (5 sticks) | 10.00 |+----------------+------------+9 rows in set (0.00 sec)
6.不匹配检查
eg: mysql> SELECT vend_id,prod_name FROM products WHERE vend_id <>1003; #等效于SELECT vend_id,prod_name FROM products # WHERE vend_id ！= 1003;+---------+--------------+ | vend_id | prod_name |+---------+--------------+| 1001 | .5 ton anvil || 1001 | 1 ton anvil || 1001 | 2 ton anvil || 1002 | Fuses || 1005 | JetPack 1000 || 1005 | JetPack 2000 || 1002 | Oil can |+---------+--------------+7 rows in set (0.00 sec)
7.范围值检查（BETWEEN）
eg: mysql> SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5.99 AND 10.00;+----------------+------------+ #注意，BETWEEN两边的取值为闭区间| prod_name | prod_price |+----------------+------------+| .5 ton anvil | 5.99 || 1 ton anvil | 9.99 || Bird seed | 10.00 || Oil can | 8.99 || TNT (5 sticks) | 10.00 |+----------------+------------+5 rows in set (0.00 sec)
8.空值检查（IS NULL)
*NULL：无值（no value),并不等于0，空字符串或仅仅包含空
eg: 先列出包含空值的表：customers; mysql> SELECT * FROM customers;+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com || 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse |NULL || 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com || 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com || 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd |NULL |+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+5 rows in set (0.00 sec)mysql> SELECT cust_id FROM customers WHERE cust_email IS NULL;+---------+| cust_id |+---------+| 10002 || 10005 |+---------+2 rows in set (0.00 sec)
bitsCN.com
内容总结
以上是互联网集市为您收集整理的MySQL学习足迹记录04--数据过滤--WHERE_MySQL全部内容，希望文章能够帮你解决MySQL学习足迹记录04--数据过滤--WHERE_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。