MySQL中Where子句操作符,组合以及通配符
操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 在指定的两个值之间 |
Where子句操作符:
- 只返回prod_price列中值为2.50的行:
select prod_name, prod_price from products where prod_price = 2.50;
- 检查单个值:
select prod_name, prod_price from products where prod_name = 'fuses';
- 列出价格小于10美元的所有产品:
select prod_name, prod_price from products where prod_price < 10;
- 检索价格在5美元和10美元之间的所有产品:
select prod_name, prod_price from products where prod_price between 5 and 10;
- 空值检查:
select prod_name from products where prod_price is null;
Where子句组合:
- and 操作符:
select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <= 10;
- or 操作符:
select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003;
- 计算次序:
select prod_name, prod_price from products where (vend_id = 1002 or vend_id =1003) and prod_price >= 10;
- IN 操作符:
select prod_name, prod_price from products where vend_id in (1002, 1003) order by prod_name;
- NOT 操作符:
select prod_name, prod_price from products where vend_id not in (1002,1003) order by prod_name;
通配符:
- 找出所有以词jet起头的产品:
select prod_id, prod_name from products where prod_name like 'jet%';
- 两端通配符:
select prod_id, prod_name from products where prod_name like '%anvil%';
- 找出以s起头以e结尾的所有产品:
select prod_name from products where prod_name like 's%e';
(% 代表搜索模式中给定位置的0个、1个或多个字符,但 ‘%’ 不能匹配null); - 下划线通配符:
select prod_id, prod_name from products where prod_name like '_ ton anvil';
(下划线的用途与 % 一样,但下划线只匹配单个字符而不是多个字符)。