MySQL基础二

数据表排序

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;