聚合函数 | 含义 |
---|---|
max () | 返回某列的最大值 |
min () | 返回某列的最小值 |
count() | 返回某列的行数 |
avg() | 返回某列的平均值 |
sum() | 返回某列值之和 |
select max(___) from _____ where ____
min(___)
count(__)
排序去重
--order by 默认升序
select * from ____ order by _____ desc --desc倒序
--asc 升序
--去重
select distinct(___) from _____
--限制
select * from ____ limit 3 --从1——3
limit 2,5 --2后5个 3——7
分组和分组过滤
--可与聚合函数搭配使用
select , , max(__)from ____ group by ____
select , , max(__) as maxv from ____ group by ____ having maxv>100
-- name as rename 实现重命名 简化代码 having实现过滤 要maxv>100的数据
sql语句的执行顺序
from ->where ->group by->having ->select ->order by
多表连接
多表的连接又分为以下几种类型:
1)左连接(left join),连接结果保留左表的全部数据
2)右连接(right join),连接结果保留右表的全部数据
3)内连接(inner join),取两表的公共数据
题来!: 组合两个表.
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
FirstName, LastName, City, State
思路:考虑到有的人可能没有地址信息,要是查询结构要查所有人,需要保留表1(Person)里的全部数据,所以用左联结(left join),两个表连接条件:两个表通过personId产生联结。
select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId=Address.PersonId;
嵌套查询
多个select语句进行嵌套,嵌套位置可以在select后,from后,where后
select ____ from ____ where ____ in (select~~~~~)
select (select~~~~~),____ from _____
union:可以将多个select语句的结果合并为一个,合并后会删除重复数据
union all:可以将多个select语句的结果合并为一个,合并后不会删除重复数据
举例:
统计每个地区订单总金额占全国订单总金额的比例,
按照倒序排列,取占比最高前三位
select region,sum(total_price) as region_price
from morder group by region;--统计每个地区订单总金额
select sum(total_price) as all_price from morder --全国订单总金额
select region,sum(total_price) as region_price ,(select sum(total_price) as all_price from morder) as all_price
from morder group by region;-
select tmo.region,tmp.region.price/tmp.all_price*100 as radio from (select region,sum(total_price) as region_price ,
(select sum(total_price) as all_price from morder)
as all_price from morder group by region)
as tmp order by radio desc limit 3;-