Mysql学习笔记（2）——过滤数据（行）

SELECT *
FROM `house-prices`
WHERE Price > 110000
ORDER BY Price;

选择房屋价格>110000的所有信息

SELECT *
FROM `house-prices`
WHERE Neighborhood = 'East'
ORDER BY Price;

选择Neighborhood全为East的信息

SELECT *
FROM `house-prices`
WHERE Neighborhood > 'East'
ORDER BY Price;

出现North和West原因：字符串比较大小按照字典序

SELECT *
FROM `house-prices`
WHERE Price BETWEEN 100000 AND 120000
ORDER BY Price;

SELECT *
FROM `house-prices`
WHERE Price>=100000 AND Price<=120000
ORDER BY Price;

MYSQL中，BETWEEN 可以包含两个端点值

SELECT *
FROM `house-prices`
WHERE Neighborhood = '';
找空字符的信息

SELECT *
FROM `house-prices`
WHERE Neighborhood IS NULL;
找NULL的信息

空字符串！= 空值

SELECT *
FROM `house-prices`
ORDER BY Neighborhood

空值默认最小，接下来是空字符串

SELECT *
FROM `house-prices`
WHERE Neighborhood <> 'East';  不等于（也可！=，但推荐<>）

结果包含空字符，但是少了null

SELECT *
FROM `house-prices`
WHERE Neighborhood = 'East'; 

也不包含null

SELECT *
FROM `house-prices`
WHERE Neighborhood = 'East'
OR Neighborhood <> 'East';

依然没有null，这种情况叫unknown

SELECT *
FROM `house-prices`
WHERE Neighborhood <> 'East'
OR Neighborhood IS NULL;

可以找到了

如果需要对null的设置，我们可以从源头解决，也就是设计表这里，选择“不是null”

SELECT *
FROM `house-prices`
WHERE Bedrooms = 3
AND Bathrooms = 3
OR Bathrooms = 2;

想要找bedroom=3，bathroom=2或3

原因：and 优先级大于 or，所以执行的命令是：Bedroom为3并且Bathroom为2，或者Bathroom为2

SELECT *
FROM `house-prices`
WHERE Bedrooms = 3
AND Bathrooms = 3
OR Bedrooms = 3
AND Bathrooms = 2;

SELECT *
FROM `house-prices`
WHERE Bedrooms = 3
AND (Bathrooms = 3
OR Bathrooms = 2);

可选用括号来改变优先级

SELECT *
FROM `house-prices`
WHERE Bedrooms = 3
AND NOT Bathrooms = 2;

not的优先级高于and，先找bathroom不等于2的信息，再找bedroom等于3