mysql 谓词_数据库谓词

谓词：属于函数的一种，但其返回值是真值(true/false/unknown)

判断是否存在满足某种条件的记录，存在返回TRUE、不存在返回FALSE。

比较多用到的几种谓词:

LIKE

BETWEEN

IS NULL/IS NOT NULL

IN

EXISTS

LIKE谓词——字符串的部分一直查询(模糊查询)

--MySQL

--DDL：创建表

CREATE TABLE SampleLike

( strcol VARCHAR(6) NOT NULL,

PRIMARY KEY (strcol));

--DML：插入数据

START TRANSACTION;

INSERT INTO SampleLike (strcol) VALUES ('abcddd');

INSERT INTO SampleLike (strcol) VALUES ('dddabc');

INSERT INTO SampleLike (strcol) VALUES ('abdddc');

INSERT INTO SampleLike (strcol) VALUES ('abcdd');

INSERT INTO SampleLike (strcol) VALUES ('ddabc');

INSERT INTO SampleLike (strcol) VALUES ('abddc');

COMMIT;

1.前方一致查询，查询对象字符串起始部分相同的记录值的查询方法。

-- 匹配以ddd开始的字符串

select * from SampleLike where strcol LIKE 'ddd%';

2.中间一致查询

-- 匹配包含ddd的字符串

select * from SampleLike where strcol LIKE '%ddd%';

3.后方一致查询

-- 匹配以ddd结尾的字符串

select * from SampleLike where strcol LIKE '%ddd';

4.使用下划线_代替%,它代表的是任意一个字符

-- 匹配以abc开始的后面要有2个字符的字符串

select * from SampleLike where strcol LIKE 'abc__';

BETWEEN谓词——范围查询

(注：会包含100和1000两个临界值，如果不需要，需使用

-- 包含临界值100和1000

SELECT product_name, sale_price

FROM Product

WHERE sale_price BETWEEN 100 AND 1000;

-- 不包含临界值

SELECT product_name, sale_price

FROM Product

WHERE sale_price > 100

AND sale_price < 1000;

is null, is not null判断是否为空

为选取某些值为null的数据列，不能使用=，只能由is null

SELECT product_name, purchase_price

FROM Product

WHERE purchase_price IS NULL;

IN谓词——OR的简单用法(使用in代替or)

1.通过OR指定多个进货单价查询(阅读性较差)

SELECT product_name, purchase_price

FROM Product

WHERE purchase_price = 320

OR purchase_price = 500

OR purchase_price = 5000;

2.通过in来指定多个进货单价查询(否定形式可以使用NOT IN来实现，注：IN和NOT IN无法选出NULL数据)

SELECT product_name, purchase_price

FROM Product

WHERE purchase_price IN (320, 500, 5000);

3.使用子查询作为IN谓词的参数

SELECT product_name, sale_price

FROM Product

WHERE product_id IN (SELECT product_id

FROM ShopProduct

WHERE shop_id = '000C');

EXIST谓词

注：实际上不使用EXIST基本上也都可以使用IN(或NOT IN)来替代

SELECT product_name, sale_price

FROM Product AS P

WHERE EXISTS (SELECT *

FROM ShopProduct AS SP

WHERE SP.shop_id = '000C'

AND SP.product_id = P.product_id);

-- 创建ShopProduct商店商品表

CREATE TABLE ShopProduct

(shop_id CHAR(4) NOT NULL,

shop_name VARCHAR(200) NOT NULL,

product_id CHAR(4) NOT NULL,

quantity INTEGER NOT NULL,

PRIMARY KEY (shop_id, product_id));

-- MySQL

START TRANSACTION;

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A','北京','0001',30);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A','北京','0002',50);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A','北京','0003',15);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B','上海','0002',30);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B','上海','0003',120);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B','上海','0004',20);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B','上海','0006',10);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B','上海','0007',40);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C','深圳','0003',20);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C','深圳','0004',50);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C','深圳','0006',90);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C','深圳','0007',70);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D','广州','0001',100);

COMMIT;