MySQL中Where子句操作符，组合以及通配符

MySQL中Where子句操作符，组合以及通配符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

Where子句操作符：

1. 只返回prod_price列中值为2.50的行：select prod_name, prod_price from products where prod_price = 2.50;
2. 检查单个值：select prod_name, prod_price from products where prod_name = 'fuses';
3. 列出价格小于10美元的所有产品：select prod_name, prod_price from products where prod_price < 10;
4. 检索价格在5美元和10美元之间的所有产品：select prod_name, prod_price from products where prod_price between 5 and 10;
5. 空值检查：select prod_name from products where prod_price is null;

Where子句组合：

1. and 操作符：select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <= 10;
2. or 操作符：select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003;
3. 计算次序：select prod_name, prod_price from products where (vend_id = 1002 or vend_id =1003) and prod_price >= 10;
4. IN 操作符：select prod_name, prod_price from products where vend_id in (1002, 1003) order by prod_name;
5. NOT 操作符：select prod_name, prod_price from products where vend_id not in (1002,1003) order by prod_name;

通配符：

1. 找出所有以词jet起头的产品：select prod_id, prod_name from products where prod_name like 'jet%';
2. 两端通配符：select prod_id, prod_name from products where prod_name like '%anvil%';
3. 找出以s起头以e结尾的所有产品：select prod_name from products where prod_name like 's%e'; （% 代表搜索模式中给定位置的0个、1个或多个字符，但 ‘%’ 不能匹配null）；
4. 下划线通配符：select prod_id, prod_name from products where prod_name like '_ ton anvil'; （下划线的用途与 % 一样，但下划线只匹配单个字符而不是多个字符）。