MySQL学习（二）

-- 过滤数据
SELECT prod_name,prod_price
FROM Products
WHERE prod_price = 3.49;


-- WHERE子句操作符
SELECT prod_name,prod_price
FROM Products
WHERE prod_price < 10;

SELECT prod_name,prod_price
FROM Products
WHERE prod_price > 10;

SELECT prod_name,prod_price
FROM Products
WHERE prod_price <=10;


-- 不匹配检查
SELECT vend_id,prod_name
FROM Products
WHERE vend_id != 'DLL01';


-- 范围值检查
SELECT prod_name,prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;


-- 空值检查
SELECT cust_name
FROM Customers
WHERE cust_email IS NULL;


-- 高级数据过滤
-- AND操作符
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <=4
ORDER BY prod_name;


-- OR操作符
SELECT prod_name,prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';


-- 求值顺序
SELECT prod_name,prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01' )
AND prod_price >= 10;


-- IN操作符
SELECT prod_name,prod_price
FROM Products
WHERE vend_id IN ('DLL01','BRS01')
ORDER BY prod_name;

-- IN 操作符与 OR 操作符可以实现相同的功能
SELECT prod_name,prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
ORDER BY prod_name;


-- NOT 操作符
SELECT prod_name,vend_id
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;


SELECT prod_name,vend_id
FROM Products
WHERE vend_id != 'DLL01'
ORDER BY prod_name;


-- 用通配符进行过滤
-- LIKE 操作符

-- % 通配符,可以匹配0个，1个或者多个字符
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';
-- 查找Fish开头的名字

SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';
-- 查找包含 bean bag 的名字，无论前面和后面是什么

SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';
-- 查找以 F 开头 y 结尾的名字


--   _ 通配符,只能匹配单个字符

SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';


SELECT prod_id,prod_name
FROM Products 
WHERE prod_name LIKE '% inch teddy bear';


--  [] 通配符  mysql 不支持
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
-- mysql 不支持 [] 通配符,可以写成以下这种形式
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE 'J%' OR cust_contact LIKE 'M%'
ORDER BY cust_contact;