1、算术表达式
例如:SELECT Sname,2016-Sage '出生年份' FROM student
2、DISTINCT ,去掉结果中的重复行
例如:SELECT DISTINCT Sdept FROM student
3、NOT BETWEEN 低值 AND 高值,确定范围(包括低值和高值)
例如:SELECT * FROM student WHERE Sage NOT BETWEEN 18 AND 19
4、确定集合
例如:SELECT * FROM student WHERE Sdept NOT IN ('CS','IS')
5、字符匹配,匹配串可以是一个完整的字符串,也可以包含有通配符百分号(%)和下划线(_),下划线代表任意单个字符
例如:SELECT * FROM student WHERE Sname LIKE '李%'
6、涉及空值查询
例如:SELECT * FROM student WHERE Sno IS NOT NULL
7、多条件查询(AND、OR)
例如:SELECT * FROM student WHERE Ssex = '男' OR Sage > 18
8、ORDER BY子句,只能对最终查询结果排序,ASC代表升序,ASC为默认值,DESC代表降序
例如:SELECT * FROM student ORDER BY Sno ASC,Sage DESC
9、聚集函数,在聚集函数遇到空值时,除COUNT(*)外都跳过空值而只处理非空值
例如:SELECT SUM(Sage),COUNT(Sage),AVG(Sage),MAX(Sage),MIN(Sage) FROM student
10、GROUP BY子句,将查询结果按某一列或多列的值分组,值相等的为一组。HAVING短语作用于组,而WHERE子句作用于基本表或试图
例如:SELECT Sdept,COUNT(Sno) FROM student GROUP BY Sdept
SELECT Sdept,COUNT(Sno) FROM student GROUP BY Sdept HAVING COUNT(Sno) > 1
11、外连接,如果把舍弃的元组也保存在结果关系中,而在其它属性列上填空值,叫外连接。左外链接列出左边关系中所有的元组,右外连接列出右边关系中所有的元组。
例如:SELECT student.Sno,sc.Grade FROM student LEFT JOIN sc ON (student.Sno = sc.Sno)
SELECT student.Sno,sc.Grade FROM student RIGHT JOIN sc ON (student.Sno = sc.Sno)
12、带有IN谓词的子查询,子查询的结果是一个集合
例如:SELECT * FROM student WHERE Sdept IN ('CS','IS')
13、带有比较运算符的子查询,应用于子查询返回单值
例如:SELECT * FROM student WHERE Sage > (SELECT AVG(Sage) FROM student)
14、带有ANY或ALL谓词的子查询,应用于子查询返回多值
例如:SELECT * FROM student WHERE Sage > ANY (SELECT Sage FROM student WHERE Ssex = '男')
15、带有EXISTS谓词的子查询,子查询只产生逻辑真或逻辑假。使用EXISTS后,若内层查询结果非空,则外层的WHERE子句返回真值,否则返回假值。
例如:SELECT * FROM student WHERE EXISTS (SELECT * FROM student WHERE Sage > 10)