################
##谓词
#like /between /is null/is not null/in/exists
################
show databases;
use xscj;
CREATE TABLE SampleLike (
strcol VARCHAR(6) NOT NULL, PRIMARY KEY (strcol));
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;
##like (not ilike为否定形式)
-- %代表0个及以上的任意字符串
-- _代表任意一个字符
-- 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';
## between的结果包含临界值
select product_name, sale_price from product
where sale_price between 100 and 1000;
## is null/is not null 选取某些值为Null的数据
select product_name, sale_price from product
where purchase_price is null;
## in (or的简便用法)/not in
#笨拙用法
select product_name,purchase_price
from product
where purchase_price = 320
or purchase_price = 500
or purchase_price = 5000;
#简单用法
select product_name,purchase_price
from product
where purchase_price in (320,500,5000);
-- 1.在使用in/not in的时候是无法取出null数据的,null数据还是需要is null/is not null
-- 2.in(not in)有其他谓词所没有的用法,就是可以使用子查询作为参数
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));
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;
select product_name,sale_price from product
where product_id in (select product_id from shopproduct
where shop_name = "大阪");
## exist/not exist 通常指定关联子查询作为exist的参数
select product_name,sale_price
from product as p
where exists (select * from shopproduct as sp
where sp.shop_name = '大阪'
and sp.product_id = p.product_id);
-- 大家可以吧exist的子查询中select *当做一种习惯。
-- 因为exist值关心记录是否存在,因此返回哪些列都没有关系。
-- exist只会判断是否满足子查询中where子句指定的条件( sp.shop_name = '大阪'
-- and sp.product_id = p.product_id),只有存在这样的记录才返回真
-- 其实exist后面就是一张记录表啦