ORDER BY

(1) 可以使用多个关键字

示例

  SELECT * FROM my_contact ORDER BY name, number;

(2) 默认 升序ASC

SQL排序规则

NULL > 数字 > 大写字母 > 小写字母

升序ASC和降序DESC可以同时用在一条语句中

示例

  SELECT * FROM movie_table ORDER BY title ASC, purchased DESC;

GROUP BY 可以进行分组加总、求平均值等操作

示例

  SELECT SUM(sales), AVG(sales), MIN(sales), MAX(sales), COUNT(DISTINCT sales_date) GROUP BY first_name ORDER BY SUM(sales) DESC;

DINSTINCT 用于返回唯一出现的值，它是关键字不是函数

LIMIT

(1) LIMIT k ： 显示前k个结果

示例

  SELECT name, score FROM class_teacher ORDER BY score DESC LIMIT 5;

  选择排名前5的同学

(1) LIMIT s, k ： 显示从s开始的k个结果 (s最小为0)

示例

  SELECT name, score FROM class_teacher ORDER BY score DESC LIMIT 11, 10;

  选择排名从11名开始的10名同学

ALL

(1) 大于(大于等于)加上ALL可以找出任何大于(大于等于)集合中最大值的值

  SELECT name, rating FROM restaurant_ratings 
  WHERE rating > ALL (SELECT rating FROM restaurant_ratings WHERE rating BETWEEN 3 AND 9) ;

(2) 小于(小于等于)加上ALL可以找出任何小于(小于等于)集合中最小值的值

  SELECT name, rating FROM restaurant_ratings 
  WHERE rating <= ALL (SELECT rating FROM restaurant_ratings WHERE rating BETWEEN 3 AND 9) ;

ANY

(1) 大于(大于等于)加上ANY可以找出任何大于(大于等于)集合中最小值的值

  SELECT name, rating FROM restaurant_ratings 
  WHERE rating > ANY (SELECT rating FROM restaurant_ratings WHERE rating BETWEEN 3 AND 9) ;

(2) 小于(小于等于)加上ANY可以找出任何小于(小于等于)集合中最大值的值

  SELECT name, rating FROM restaurant_ratings 
  WHERE rating <= ANY (SELECT rating FROM restaurant_ratings WHERE rating BETWEEN 3 AND 9) ;