头歌(数据库实验三)数据查询

第1关：按条件查询单表的所有字段

use province;

 #代码开始

 #第一题

select * from jdxx where qxmc="开福区";

 #第二题

select * from jdxx where qxmc="开福区" or qxmc="岳麓区";

 #第三题

 select * from jdxx where cs="长沙市" and name="西湖街道";

 #代码结束

第2关：查询唯一值

use province

 #代码开始

 select distinct qxmc from jdxx where sf="湖南省";

 select distinct qxmc from jdxx where cs="长沙市"; 

 #代码结束

第3关：统计查询

use province;

#代码开始

#答案1

select count(name) from jdxx where sf="湖南省";

#答案2

select count(name) from jdxx where cs="长沙市";

#答案3

select count(distinct qxmc) from jdxx where sf="湖南省";

#答案4

select count(distinct qxmc) from jdxx where cs="长沙市";

 #代码结束

第4关：分组查询

use province;

#代码开始

#题目1

select sf,count(*) from jdxx group by sf;

#题目2

select cs,count(*) from jdxx group by cs having count(name)>200;

#题目3

select qxmc,count(*) from jdxx where cs="长沙市" group by qxmc;

 #代码结束

第5关：数据排序

use province;

 #代码开始

 #第一题

select * from jdtj order by jdgs desc limit 10;

 #第二题

select * from jdtj order by jdgs asc limit 10;

 #第三题

select * from jdtj where jdgs>35 order by jdgs desc,sf asc;

 #代码结束