MySQL基础复习与整理---DQL数据查询语言---排序查询、分组查询

排序查询order by

语法

select 查找列表、字段、常量、函数、表达式 
from 表名 
where 筛选条件
ORDER BY 排序列表 asc/desc;

asc升序
desc降序
不写默认升序

使用

• 支持表达式排序

SELECT title,length/2 FROM film WHERE rating IN ('G') ORDER BY length/2 desc;

• 支持别名排序

SELECT title 题目 FROM film WHERE rating IN ('G') ORDER BY 题目 desc;

• 支持函数排序

SELECT title 题目 FROM film WHERE rating IN ('G') ORDER BY LENGTH(题目);

• 支持多个排序，如先按a

SELECT title 题目 FROM film WHERE rating IN ('G') ORDER BY LENGTH(题目),length desc;

一般写在最后，limit除外

分组查询group by

分组前筛选where

语法

SELECT 分组函数,列(要求出现在group by 后面)
FROM 表
WHERE ...
GROUP BY 分组的列表
ORDER BY ...

查询列表必须特殊，要求是分组函数和group by 后出现的字段

SELECT COUNT(customer_id),staff_id
FROM payment
GROUP BY staff_id;

SELECT COUNT(title),rating
FROM film
GROUP BY rating;

SELECT MAX(length),rental_rate
FROM film 
WHERE rating = 'G'
GROUP BY rental_rate;

分组后筛选having

语法

SELECT 分组函数,列(要求出现在group by 后面)
FROM 表
WHERE ...
GROUP BY 分组的列表
HAVING...
ORDER BY ...

哪个国家城市数大于20

SELECT COUNT(city),country_id
FROM city
GROUP BY country_id 
HAVING COUNT(city)>20

每个rating，rental_rate = 2.99的，最大replace_cost>20的电影,根据rating降序

SELECT MAX(replacement_cost),rating
FROM film
WHERE rental_rate = 2.99
GROUP BY rating
HAVING MAX(replacement_cost)>20
ORDER BY rating desc

.	数据源	位置	关键字
分组前	原始表	GROUP BY前	WHERE
分组后	分组后的结果集	GROUP BY后	HAVING

• 以分组函数为条件肯定是having
• 能用分组前筛选的用分组前筛选
• 支持表达式分组

SELECT count(*),LENGTH(last_name)
FROM actor
GROUP BY LENGTH(last_name)

• 支持多字段分组,逗号隔开，无顺序要求

SELECT count(*),rental_rate,rating
FROM film
GROUP BY rental_rate,rating
ORDER BY rental_rate desc