参考MICK的SQL基础教程,进行SQL的基础学习。
什么是谓词
对通常的函数来说,返回值有可能是数字、字符串或者日期等,但是谓词的返回值全都是真值(TRUE、FALS、EUNKNOWN)。这也是谓词和函数的最大区别。
SampleLike表
strcol |
---|
abcddd |
dddabc |
abdddc |
abcdd |
ddabc |
abddc |
字符串匹配(LIKE
)
SELECT
*
FROM
SampleLike
WHERE
strcol LIKE '%ddd%';
范围查询(BETWEEN
)
SELECT
*
FROM
product
WHERE
sale_price BETWEEN 100 AND 1000;
注:BETWEEN
的特点就是结果中会包含 100 和 1000 这两个临界值。
判断是否为NULL(IS NULL
、IS NOT NULL
)
SELECT
*
FROM
product
WHERE
purchase_price IS NULL;
OR的简便用法(IN)
SELECT
*
FROM
product
WHERE
purchase_price in (320, 500, 5000)
SELECT
*
FROM
product
WHERE
product_id IN (
SELECT
product_id
FROM
shopProduct
WHERE
shop_id = '000C';