mysql的select语句和count、max/min、sum、avg、group by、order by、limit等等应用

SELECT语句

select 若干属性值(按列筛选)
from 某表
where 满足若干条件的表

(别名，写不写as都行)    
select Name as 姓名，Salary  as 工资  
from ***

函数的聚集查询

count

select count(*) from **** 
#获取结果集行数

select count(*) from ***
where Age>25  

max/min

select max(Salary),min(Salary) from ***

select max(Salary),min(Salary)
from ***
where Age>25

sum/avg

select sum(Salary) from ***
where age>25

select avg(Salary) from ***
where age>25

group by

#将数据分为多个逻辑组
*group by 分段字符

#如图
select age from ****
group by age

#每一组中的，平均值，个数，最大值，最小值
select age,avg(Salary),count(*),max(Salary),min(Salary) from ****
group by age

having

#(having 限制)   WHERE与sum合计函数无法一起使用
#查找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额。
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

order by

#位于select末尾，允许指定按照一个或多个列排序
#升序：asc ， 降序：desc

#按照年龄升/降序排列
select * from ****
order by Age ASC/desc

#先按照年龄降序排列，若年龄相同则按照工资升序
select * from ****
where Age>25
order by Age DESC，Salary ASC

通配符过滤

#单字符匹配"_"
#任意字符开头，剩余部分为erry
select * from ****
where name like '_erry'

#零个/多字符匹配"%"
#检索包含字母n的信息
select * from ****
where name like '%n%'

limit，网站分页用

#限制返回结果集
#要返回结果集的最大数目（行号从0开始）

#从第二行开始的最多五条数据
select * from ****
where age>25
order by Salary desc
limit 2,5

join

#left join 被连接的表 on 连接的条件

select s.name,o.name
from Students s
left join Teachers t
on s.age=t.age

#添加where语句的
select s.name,o.name
from Students s
left join Teachers t
on s.age=t.age
where s.age>15

#连接多张表
select s.name,o.name
from Students s
LEFT join Teachers t ON s.age=t.age
LEFT join Customers c ON s.age=c.age
where s.age>15