MySQL:数据表的其他操作

数据表的其他操作

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;