头歌作业-数据库实验三 数据查询一

数据库实验三 数据查询一

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

use province;
 #代码开始
 #第一题
select * from jdxx where qxmc="开福区";
 #第二题
select * from jdxx where qxmc in("开福区","岳麓区");

 #第三题
select * from jdxx where cs="长沙市" and name="西湖街道";
  
 #代码结束

第二关：查询唯一值

use province
 #代码开始
 
select distinct qxmc from jdxx where sf="湖南省";
select distinct qxmc from jdxx where cs="长沙市";
 
 #代码结束

第三关：统计查询

use province;
#代码开始
#答案1
select count(name)  from jdxx where sf="湖南省";
#答案
select count(name)  from jdxx where cs="长沙市";

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

第四关：分组查询

use province;
SELECT sf, count(*) 
FROM jdxx  
GROUP BY sf;  
SELECT cs, count(*) 
FROM jdxx  
GROUP BY cs  
HAVING count(name) > 200;  
SELECT qxmc, count(*) 
FROM jdxx  
WHERE cs = '长沙市'  
GROUP BY qxmc;  

第五关：数据排序

 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;  
 #代码结束