数据表排序
select * from stuinfo order by birthdate; #按照birthdate升序排列
select * from stuinfo order by birthdate desc; #按照birthdate降序排列
数据表分组
#分组 group by
select count(*) from stuinfo;
#根据性别gender进行分组
select gender, count(*) from stuinfo group by gender;
#根据城市进行分组city
select city, count(*) from stuinfo group by city;
数据表处理空值
#将city组中为非空值的数据筛选出
select * from stuinfo where city is not null;
#将city组中为空值的数据筛选出
select * from stuinfo where city is null;
数据表中常用的统计函数
根据stuscore数据表进行示范操作
#统计最高分max(数据名),最低分min(数据名),平均值avg(数据名)
select max(math), min(math), avg(math) from stuscore;
#可以通过as来给新的数据取一个别名
select max(math) as max_Math, min(math) as min_Math, avg(math) as avg_Math from stuscore;
max()和min()函数对日期也可以使用
#sum()进行求和,count()返回某列的行数
select sum(math)/count(stuid) as avg_Math from stuscore;
数据表的连接
stuinfo
stuscore
#表的连接
select stuinfo.stuid, stuinfo.stuname, gender, math, english
from stuinfo, stuscore
where stuinfo.stuId = stuscore.stuId;
一、内连接
获取两个表中字段匹配关系的记录
inner join
select stuinfo.stuid, stuinfo.stuname, gemder, math, english
from stuinfo inner join stuscore
on stuinfo.stuId = stuscore.stuId;
二、左连接
获取左表所有记录,右表中没有对应匹配记录的地方为空
left join
select stuinfo.stuid, stuinfo.stuname, gender, math, english
from stuinfo left join stuscore
on stuinfo.stuId = stuscore.stuId;
三、右连接
获取右表所有记录,左表中没有对应匹配记录的地方为空
right join
select stuinfo.stuid, stuinfo.stuname, gender, math, english
from stuinfo right join stuscore
on stuinfo.stuId = stuscore.stuId;
其他常用操作符
like
select * from stuinfo where city like 'Bei%';
注意:'Bei%'代表以Bei开头的字符
in,not in
#查询city为北京或者上海的数据
select * from stuinfo where city in('Beijing','Shanghai');
##查询city不为北京或者上海的数据
select * from stuinfo where city not in('Beijing','Shanghai');
dat_format
select stuname,date_format(birthdate,'%Y') as birth_y from stuinfo;
select stuname,date_format(birthdate,'%Y-%m') as birth_ym from stuinfo;
注意:%M代表分钟,%m代表月份
distinct
去除重复项
select count(city) from stuinfo;
select count(distinct city) from stuinfo;
between
select * from stuinfo where birthdate
between '1989-1-1' and '1990-6-1';
having
select class, avg(math) from stuscore group by class;
select class, avg(math) from stuscore group by class
having avg(math)>90;
注意:having和where的区别是,如果出现统计函数,则使用having,一般情况下用where
union
将多个查询结果联合
select stuname from stuinfo
union
select stuname from stuscore;
case表达式
用来表达条件分支
#方法一
select case city
when 'Beijing' then '华北'
when 'Tianjin' then '华北'
when 'Shanghai' then '华东'
when 'Guangzhou' then '华南'
else '其他' end as district, count(stuid)
from stuinfo
group by case city
when 'Beijing' then '华北'
when 'Tianjin' then '华北'
when 'Shanghai' then '华东'
when 'Guangzhou' then '华南'
else '其他' end;
#方法二
select class, case when math<80 then '一般'
when math>=80 and math<90 then '良好'
else '优秀' end as math_class, count(*)
from stuscore
group by class, case when math<80 then '一般'
when math>=80 and math<90 then '良好'
else '优秀' end;