学SQL(WHERE(2)、NOT、IN、通配符、正则表达式)

WHERE(2)——使用AND，OR，IN，NOT

使用AND

--检索由供应商1003制造的，价格在在10以下的所有产品的名称和价格
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;

求值顺序：AND > OR

--检索供应商为1002的所有产品，供应商为1003且价格超过10的产品
select prod_name,prod_price
from products
where vend_id = 1002 or vend_id = 1003 and prod_price >=10;
--检索供应商为1002或1003，且产品价格超过10的产品
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;

IN优于OR：

1.求值顺序更容易管理（因为它使用了比OR更少的运算符）。

2.执行更快。

3.IN运算符可以包含一条新的select语句。

使用NOT

select prod_name,prod_price
from products
where vend_id not in (1002,1003)
order by prod_name;

---

通配符

通配符（Wildcard）：用于匹配值的某些部分的特殊字符。

搜索模式（Search Pattern）：由字面意义的文本、通配符字符或者二者的任意组合构成的搜索条件。

%：“匹配出现任意次数的任意字符——可以是0次”

select prod_id,prod_name
from products
where prod_name like 'Jet%';
select prod_id,prod_name
from products
where prod_name like '%anvil%';

注意：①大小写敏感；②留心尾部空格（解决：搜索模式末尾加“%”；用函数剪切空格）；③留心null（“%”不匹配null值）。

_：“只匹配单个字符——不多也不少”

select prod_name,prod_price
from products
where prod_name like '_ ton anvil%';

注意：①不要过度的使用通配符；②尽量不要在开头使用通配符（速度变慢）；③注意通配符的位置。

---

正则表达式——简易（正则表达式后续单独写一篇~~）

正则表达式是什么？

正则表达式是一种用于匹配文本的特殊语言的一部分。例如：需要从文本中提取电话号码，可能会需要使用一个正则表达式。

Oracle PL/SQL 提供了四种可以访问正则表达式的函数：

regexp_like( )：用于搜索字符。

regexp_replace( )：用于替换字符串中的字符。

regexp_instr( )、regexp_substr( )：在字符串内搜索字串。

示例：

--基本的字符匹配
select prod_name
from products
where regexp_like(prod_name, '1000')
order by prod_name;
/*
对比：
1.
where子句需要给它传递一个列名称，一个值，以及一个运算符（例如=，like）、
regexp_like()是一个接受参数的函数，它会返回TRUE和FALSE，TRUE的话就匹配where子句，并且返回它。
2.
like匹配整个列，如果要匹配的文本位于列值的中间，like将不会找到它。
regexp_like()会在列值中寻找匹配，若要匹配的部分位于列值中间，会找到它并返回行。
*/
--“.”：“匹配任意单个字符”
select prod_name
from products
where regexp_like(prod_name, '.000')
order by prod_name;
--“|”：or匹配
select prod_name
from products
where regexp_like(prod_name, '1000|2000|3000')
order by prod_name;
例子：
select prod_name
from products
where regexp_like(prod_name, '1|2|3 ton')/*将匹配1或2或3 ton*/
--匹配多个字符之一“[]”
select prod_name
from products
where regexp_like(prod_name, '[123] ton')/*将匹配1 ton或2 ton或3 ton*/
order by prod_name;
--“^”：字符集取反[^123]
select prod_name
from products
where regexp_like(prod_name, '[^123] ton')
order by prod_name;
--匹配范围[0-9];[a-z]
select prod_name
from products
where regexp_like(prod_name, '[1-5] ton')
order by prod_name;
--匹配特殊字符：需要在前面加“\”
select vend_name
from vendors
where regexp_like(vend_name, '\.')
order by vend_name;
/*这个过程称为转义（Escape）*/

匹配字符类别

类别	描述
\d	任意数字（等同于[0-9]）
\D	任意非数字字符（等同于[^0-9]）
\w	任意字母或数字（等同于[a-zA-Z0-9]）
\W	任意非字母或数字（等同于[^a-zA-Z0-9]）
\s	任意空白字符
\S	任意非空白字符

匹配重复次数

元字符	描述
*	0个或多个匹配
+	1个或多个匹配（等价于{1,}）
?	0个或1个匹配（等价于{0,1}）
{n}	具体的匹配次数
{n,}	不少于指定的匹配次数
{n,m}	匹配的范围

示例：

select prod_name
from products
where regexp_like(prod_name, '\(\d sticks?\)')
order by prod_name;
/*
结果：
prod_name
TNT (1 stick)
TNT (5 sticks)

分析：
\(：匹配（
\d：匹配任意数字
sticks?：匹配stick或sticks
*/
select prod_name
from products
where regexp_like(prod_name, '\d{4}')/*匹配任意四个连续的数字*/
order by prod_name;
/*还可写为：'[0-9]{4}'*/

锚

锚用于匹配特定位置的文本（例如查找以某个数字开头的产品）

锚元字符	描述
^	文本的开头
$	文本的末尾

select prod_name
from products
where regexp_like(prod_name, '^[0-9\.]')
order by prod_name;

注：“^”在字符集[]内使用时，表示取反；否则表示指示字符串的开头。使用锚可以使得regexp()像like那样，只需在开头加^，在末尾加$。

 

参考文献：《Oracle PL\SQL 必知必会》[美] Ben Forta 著 傅强 译（第7-9章，64-99页）。