sql语句

聚合函数

• 分组，要和聚合函数一起用

 select sum(money) from people group by job order by sum(money) desc
 //查询出people表里每种job 所对应的money总和 并以总和的money做降序排列

• 常用聚合函数

min(column) max(column) 最小值和最大值
avg(column) 平均值
sum(column) 求和
count(column) 总个数

• group by xxx having xxx order by xxx desc

select job,sum(money) from people where age > 20 group by job having sum(money) > 1000 order by sum(money) desc

//查询出people表中年龄大于20岁的人的各类职业所得工资的总和 
//并将其以职业分组 只取工资总和大于1000的职业分组 
//并将其按工资总和降序排序

多表查询

• xx join xxx on xxx where xxx

select cs.name csname,ca.name caname  from category ca  left join cars cs on ca.cid = cs.id where cs.name != '宝马'

执行顺序

(8)SELECT (9)DISTINCT  (11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>