【0032】SQL查询--简单查询--选择运算

WHERE 子句中常用的查询条件如下：

比较运算：>，>=，<，<=，=，<>（或！=）

范围运算：BETWEEN ... AND

集合查询：IN

空值查询：IS NULL

字符匹配查询：LIKE

逻辑查询：AND，OR，NOT

1、比较运算

SELECT classNo,className,institute
FROM class
WHERE grade=2007

SELECT studentNo,studentName,birthday
FROM student
WHERE YEAR(NOW())-YEAR(birthday)>=27

2、范围查询

SELECT studentNo,courseNo,score
FROM score
WHERE score BETWEEN 80 AND 90

SELECT studentNo,courseNo,score
FROM score
WHERE score NOT BETWEEN 80 AND 90

3、集合查询

SELECT studentNo,courseNo,score
FROM score
WHERE courseNo IN (002)

SELECT studentName,native,classNo
FROM student
WHERE native NOT IN ('南昌','上海')

4、空值查询

SELECT * 
FROM course
WHERE priorCourse IS NULL

SELECT *
FROM course
WHERE priorCourse IS NOT NULL

5、字符匹配查询

SELECT studentNo,studentName
FROM student
WHERE studentName LIKE '王__'

SELECT studentNo,studentName
FROM student
WHERE studentName NOT LIKE '%红%'

SELECT studentNo,studentName
FROM student
WHERE nation LIKE '蒙古族'

选取 name 以 "G"、"F" 或 "s" 开始的所有网站

SELECT * FROM Websites
WHERE name REGEXP '^[GFs]'

选取 name 以 A 到 H 字母开头的网站：

SELECT * FROM Websites
WHERE name REGEXP '^[A-H]'

name 不以 A 到 H 字母开头的网站：

SELECT * FROM Websites
WHERE name REGEXP '^[^A-H]';

如果匹配字符串不含%或_，则LIKE与=查询结果一样，如下：

SELECT studentNo,studentName
FROM student
WHERE nation='蒙古族'

SELECT className
FROM class
WHERE className LIKE '%08#_%' ESCAPE '#'

6、逻辑查询

SELECT studentNo,courseNo,score
FROM score
WHERE courseNo='001' OR courseNo='005' OR courseNo='003'

SELECT studentNo,studentName,birthday
FROM student
WHERE YEAR(birthday)=1991 AND nation='汉族'

在逻辑运算中，不可以对同一个属性进行逻辑“与”的等值运算。比如；

同时选修001和002的选课信息

SELECT *

FROM Score

WHERE courseNo='001' AND courseNo='002'

SELECT studentName,native,classNo
FROM student
WHERE native!='南昌' AND native!='上海'

SELECT studentNo,courseNo,score
FROM score
WHERE score>=80 AND score<=90

SELECT studentNo,courseNo,score
FROM score
WHERE score<80 OR score>90