数据表的其他操作
order:
select * from <table name> order by <field name>
e.g select * from StuInfo order by birthday;
group by (通常跟count一起用)
select gender,count(*) from StuInfo group by gender;
select city,count(StuId) from StuInfo group by city;
null/not null
select * from StuInfo where city is null;
max,min,avg
select max(math),min(math),avg(math) as avg_math from StuScore;
表的链接
select stuinfo.stuid,stuinfo.stuname,stuscore.gender,stuscore.math,stuscore.english
from stuinfo,stuscore
where stuinfo.stuid = stuscore.stuid;
1.内连接
select stuinfo.stuid,stuinfo.stuname,stuscore.gender,stuscore.math,stuscore.english
from stuinfo inner join stuscore
on stuinfo.stuid = stuscore.stuid;
2.左连接
select stuinfo.stuid,stuinfo.stuname,stuscore.gender,stuscore.math,stuscore.english
from stuinfo left join stuscore
on stuinfo.stuid = stuscore.stuid;
3.右连接
select stuinfo.stuid,stuinfo.stuname,stuscore.gender,stuscore.math,stuscore.english
from stuinfo right join stuscore
on stuinfo.stuid = stuscore.stuid;
like
select * from stuinfo where city like 'Bei%';
in,not in
select * from stuinfo where city in ('Beijing', 'Shanghai');
between
select * from StuInfo where birthDate between '1990-1-1' and '1990-12-1';
having
select class,avg(math) from StuScore2 group by class having avg(math) > 90 ;
distinct
select count(distinct city) from StuInfo;
date_format
select stuId,date_format(birthDate,'%Y') from StuInfo;
select stuId,date_format(birthDate,'%Y/%m') from StuInfo;
union
select StuName from StuInfo
union
select StuName from StuScore2;
case
select case city
when city = 'Beijing' then '华北'
when city = 'Guangzhou' then '华南'
when city = 'Shanghai' then '华东'
else '其他' end
as district,count(stuId) from StuInfo
group by district;