chapter06_SELECT进阶

• 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) ;