mysql复习记录

select count(1) from counted_people_in_china; #统计表行数

select * from counted_people_in_china limit 2,340; #显示从第三行到第340行

select * from counted_people_in_china #显示所有安徽数据
where field1 like ‘%安徽%’;

select ‘安徽省’ ,min(field3) from #找到安徽人口最少的城市
counted_people_in_china
group by ‘安徽省’;

select count(1) as countanhui from #计数安徽的省份数量
(select * from counted_people_in_china
where field1 like ‘%安徽%’)as anhui ;

select sum(field3) as countanhui from
(select * from counted_people_in_china
where field1 like ‘%安徽%’)as anhui ; #求和安徽的人口数据

select * FROM
(select * from counted_people_in_china
where field1 like ‘%安徽%’)as anhui
order by field3 asc; #安徽省从小到大排

select field2,field3 from
(select * from counted_people_in_china
where field1 like ‘%安徽%’)as anhui
order by field3 DESC ; #安徽省从大到小排

select field1 ,count(field2) counted_of_provincial , #安徽省简单分组统计
sum(field3) as sum_of_provincial from
counted_people_in_china
GROUP BY field1;

select field1 , #分组频率统计
count(field2) as counted_of_provincial,
convert(count(field2)/(340-2)
,decimal(15,2)) as p_provincial,
sum(field3) as sum_of_provincial ,
convert(sum(field3)/(select sum(field3)
from counted_people_in_china )
,decimal(15,2)) * 2 as sum_ppl
from counted_people_in_china
GROUP BY field1;

#insert into
insert into counted_people_in_china #插入一行数据
(field1 , field2 , field3)
VALUES
(“未知” , “未知” , 10000 );
select * from counted_people_in_china
limit 340,343; #检查数据

#delete
delete from counted_people_in_china
where field3 = ‘10000’