基础篇-数据过滤 Filtering Data

Checking for Matches 检查匹配项

SELECT Name, Population
FROM country
WHERE Continent = 'Asia'

Checking for Nonmatches 检查不匹配项

SELECT Name, Population
FROM country
WHERE Continent <> 'Europe'

Checking for a Range of Values 检查值的范围

SELECT Name, Population
FROM country
WHERE Population BETWEEN 100000000 AND 1000000000

Checking fro No Value 无值检查

/* Check the IndepYear field for an empty value. */
SELECT Name
FROM country
WHERE IndepYear IS NULL

/* Select the records for which the 
IndepYear field contains the value. */
SELECT Name
FROM country
WHERE IndepYear IS NOT NULL

Using the AND Operator 使用AND运算符

SELECT Name, Population
FROM country
WHERE
    Population > 100000000 AND
    Continent <> 'Asia'

Usring the OR Operator 使用OR运算符

SELECT Name, Population
FROM country
WHERE
    Population > 100000000 OR
    Population BETWEEN 1 AND 2000

AND and OR Operator AND和OR运算符

SELECT Name, Population
FROM country
WHERE
    (Population > 100000000 OR Population < 2000) AND
    Continent <> 'Asia' AND
    Population > 0

Using the IN Operator 使用IN运算符

SELECT Code, Name
FROM country
WHERE Code IN ('RUS', 'USA')

Using the NOT Operator 使用NOT运算符

SELECT Code, Name
FROM country
WHERE NOT Code in ('RUS', 'USA')

Using the LIKE Operator 使用LIKE运算符

SELECT Code, Name
FROM country
WHERE Name LIKE '%State%'

SELECT Code, Name
FROM country
WHERE Code LIKE 'B_R'

SELECT Code, Name
FROM country
WHERE Code LIKE 'B[GH]R'


SELECT Code, Name
FROM country
WHERE Code LIKE 'B[^GH]R'

正则匹配

SELECT device_id, age, university
FROM user_profile
WHERE university REGEXP "北京"

操作符优先级(使用括号)

SELECT  device_id,gender,age,university,gpa
FROM user_profile
where (gpa > 3.5 and university = '山东大学') OR (gpa > 3.8 and university = '复旦大学') ;

The WHERE Clause Operator WHERE子句运算符

list of operators: