2021-07-22 SQL Day5

一.数据过滤

1.WHERE

根据指定条件，过滤结果数据

SELECT m.* FROM milk_tea AS m WHERE m.prod_id = 2;

SELECT m.* FROM milk_tea AS m WHERE m.prod_name = '奶糖';

SELECT m.* FROM milk_tea AS m WHERE m.net_w = '150g';

image.png

SELECT m.* FROM milk_tea AS m WHERE m.in_price < 5;

SELECT m.* FROM milk_tea AS m WHERE m.sale_price = 15;

SELECT m.prod_name FROM milk_tea AS m WHERE m.sale_price = 15;

SELECT m.* FROM milk_tea AS m WHERE m.sale_price <> 15;

image.png

2.IFNULL(expr1,expr2)

image.png

SELECT m.* FROM milk_tea AS m WHERE IFNULL(sale_price, 15) <> 15;

SELECT m.* FROM milk_tea AS m WHERE IFNULL(sale_price, 15) = 15;

image.png

SELECT m.*, m.sale_price * 0.9 AS new_sale FROM milk_tea AS m WHERE m.sale_price * 0.9 < 10;

SELECT m.* FROM milk_tea AS m WHERE m.sale_price * 0.9 < 10;

SELECT m.* FROM milk_tea AS m WHERE IFNULL(m.sale_price * 0.9, 0)  < 10;

image.png

SELECT m.*, m.sale_price - m.in_price AS profit FROM milk_tea AS m WHERE m.sale_price - m.in_price > 5;

SELECT m.* FROM milk_tea AS m WHERE m.sale_price - m.in_price > 5;

image.png

3.BETWEEN...AND...

SELECT m.* FROM milk_tea AS m WHERE m.in_price BETWEEN 5 AND 10;

image.png

4.IS NULL \ IS NOT NULL

SELECT m.* FROM milk_tea AS m WHERE m.sale_price IS NULL;

image.png

SELECT m.* FROM milk_tea AS m WHERE m.sale_price IS NOT NULL;

image.png

二.今日代码

SELECT m.* FROM milk_tea AS m;

SELECT m.* FROM milk_tea AS m WHERE m.prod_id = 2;

SELECT m.* FROM milk_tea AS m WHERE m.prod_name = '奶糖';

SELECT m.* FROM milk_tea AS m WHERE m.net_w = '150g';

SELECT m.* FROM milk_tea AS m WHERE m.in_price < 5;

SELECT m.* FROM milk_tea AS m WHERE m.sale_price = 15;

SELECT m.prod_name FROM milk_tea AS m WHERE m.sale_price = 15;

SELECT m.* FROM milk_tea AS m WHERE m.sale_price <> 15;

IFNULL(expr1,expr2)

SELECT m.* FROM milk_tea AS m WHERE IFNULL(sale_price, 15) <> 15;

SELECT m.* FROM milk_tea AS m WHERE IFNULL(sale_price, 15) = 15;



SELECT m.*, m.sale_price * 0.9 AS new_sale FROM milk_tea AS m WHERE m.sale_price * 0.9 < 10;

SELECT m.* FROM milk_tea AS m WHERE m.sale_price * 0.9 < 10;

SELECT m.* FROM milk_tea AS m WHERE IFNULL(m.sale_price * 0.9, 0)  < 10;


SELECT m.*, m.sale_price - m.in_price AS profit FROM milk_tea AS m WHERE m.sale_price - m.in_price > 5;

SELECT m.* FROM milk_tea AS m WHERE m.sale_price - m.in_price > 5;


SELECT m.* FROM milk_tea AS m WHERE m.in_price BETWEEN 5 AND 10;

SELECT m.* FROM milk_tea AS m WHERE m.sale_price IS NULL;

SELECT m.* FROM milk_tea AS m WHERE m.sale_price IS NOT NULL;

SELECT m.* FROM milk_tea AS m WHERE IFNULL(m.sale_price, 11) = 11;

三.课后作业

Q1：写出过滤子句的结构（包括关键字和操作对象）以及该子句在整个语句中的位置。
答：
结构：关键字 - WHERE
操作对象 - 过滤条件
位置：在 SELECT...FROM...子句之后，ORDER BY...之前。

Q2：总结一下过滤子句（WHERE …）和排序子句（ORDER BY …）的异同。

image.png

Q3：分别写出以下语句。

SELECT m.* FROM milk_tea AS m;

SELECT m.* FROM milk_tea AS m WHERE m.sale_price IS NOT NULL;

SELECT m.* FROM milk_tea AS m WHERE IFNULL(m.sale_price, 10) ORDER BY m.sale_price ASC;

SELECT * FROM milk_tea ORDER BY IFNULL(sale_price, 10);

SELECT * FROM milk_tea WHERE IFNULL(sale_price, 20) * 1.1 > 16;

SELECT * FROM milk_tea WHERE (IFNULL(sale_price, 20) - in_price) > 5;

SELECT * FROM milk_tea WHERE (IFNULL(sale_price, 20) - in_price) > 5 ORDER BY IFNULL(sale_price, 20) DESC;

1、选出 milk_tea 表的所有列。