《数据库系统原理》​​​​​​​实验8：表数据的高级查询

一、实验目的

本实验的目的是使学生熟练掌握MySQL查询分析器的使用方法，加深SQL语言查询语句的理解。熟练掌握数据查询中的分组、统计、计算和集合的操作方法。

二、实验内容

在数据库world中完成以下操作。

1、 查询所有国家名称及相应的城市、语言。

输入以下代码并运行：

USE world;
SELECT country.Name,city.Name,countrylanguage.Language
FROM country,city,countrylanguage
WHERE country.Code= city.CountryCode
AND city.CountryCode = countrylanguage.CountryCode;

（注：已根据粉丝指正修改了代码，图片未修改）

2、 查询国家政体为共和国且国家人口在一千万以上的城市的名称和城市人口、所属国家。

输入以下代码并运行：

SELECT city.`Name`, city.Population, country.`Name`  
FROM city  
INNER JOIN country  
ON country.Code = city.CountryCode  
WHERE country.GovernmentForm = 'Republic' and country.Population > 10000000  

3、 统计country表中共和国政体的国家数。

输入以下代码并运行：

SELECT count(*)  
FROM country  
WHERE GovernmentForm = 'Republic'  

4、 统计country表中共和国政体国家的平均人口。

输入以下代码并运行：

SELECT AVG(Population)  
FROM country  
WHERE GovernmentForm = 'Republic'  

5、 统计countrylanguage表中官方语言数。

输入以下代码并运行：

SELECT count(*)  
FROM countrylanguage  
WHERE IsOfficial = 'T'  

6、 分组统计country表中各政体的国家个数。

输入以下代码并运行：

SELECT GovernmentForm,count(GovernmentForm)  
FROM country  
GROUP BY GovernmentForm  

7、 分组统计各大洲平均每国人口数。

输入以下代码并运行：

SELECT Continent,AVG(Population)  
FROM country  
GROUP BY Continent  

8、 查询有超过两条城市记录的国家的名称。

输入以下代码并运行：

SELECT country.`Name`,COUNT(city.`Name`)  
FROM country  
INNER JOIN city  
ON country.Code = city.CountryCode  
GROUP BY country.`Name`  
HAVING COUNT(city.`Name`) > 2  

9、 查询非共和政体的国家的名称和政体。

输入以下代码并运行：

SELECT Name,GovernmentForm  
FROM country  
WHERE GovernmentForm != 'Republic'  

10、查询独立年份未知的国家的缩写和名称

输入以下代码并运行：

SELECT `Code`,`Name`  
FROM country  
WHERE Indepyear IS NULL  

11、查询欧洲国民生产总值GNP排名前20的国家名称及GNP值

输入以下代码并运行：

SELECT `Name`,GNP  
FROM country  
WHERE Continent = 'Europe'  
ORDER BY GNP DESC  
LIMIT 0,20;  

11、查询平均每国人口数高于非洲的大洲名称及该平均数，以平均数的降序排列。

输入以下代码并运行：

SELECT Continent,AVG(Population) 
FROM country 
GROUP BY Continent 
HAVING AVG(Population) >  
(SELECT AVG(Population) FROM country WHERE Continent = 'Africa') ORDER BY AVG(Population) DESC; 

12、查询city表中多于3个城市记录且缩写以“A”开头的国家的名称和城市平均人口，以平均人口的升序排列。

输入以下代码并运行：

SELECT country.`Name`,AVG(city.Population)  
FROM city  
INNER JOIN country  
ON city.CountryCode = country.`Code`  
WHERE city.CountryCode LIKE 'A%'  
GROUP BY CountryCode  
HAVING count(CountryCode) > 3  
ORDER BY AVG(city.Population)  

三、课后练习题

1、建立名为SPJ的数据库。它包括S、P、J、SPJ 4个关系模式：

S(SNO,SNAME,STATUS,CITY);

P(PNO,PNAME,COLOR,WEIGHT);

J(JNO,JNAME,CITY);

SPJ(SNO,PNO,JNO,QTY)

供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成；

零件表P由零件代码（PNO）、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成；

工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成；

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。

今有若干数据如下：

S表

P表

J表

SPJ表

第2～8题在数据库SPJ中完成。

2、查询重量最轻的零件的零件代码。

输入以下代码并运行：

SELECT PNO  
FROM P  
ORDER BY WEIGHT  
LIMIT 0,1;  

3、查询由供应商S1提供零件的工程项目名。

输入以下代码并运行：

SELECT J.JNAME  
FROM j  
INNER JOIN spj  
ON SPJ.JNO = J.JNO  
WHERE SPJ.SNO = 'S1'  

4、查询同时为工程J1和J2提供零件的供应商代码。

输入以下代码并运行：

SELECT SNO  
FROM spj  
GROUP BY SNO  
HAVING COUNT(case when JNO ='J1' then '1' end) > 0 AND COUNT(case when JNO ='J2' then '1' end)>0  

5、查询为位于天津的工程提供零件的供应商代码。

输入以下代码并运行：

SELECT SNO
FROM spj
INNER JOIN J
ON J.JNO = spj.JNO
WHERE J.CITY ='天津'
GROUP BY SNO

 

6、查询同时为位于天津或北京的工程提供红色零件的供应商代码。

输入以下代码并运行：

SELECT SNO
FROM spj  
INNER JOIN J  
ON J.JNO = spj.JNO 
INNER JOIN P  
ON P.PNO = spj.PNO  
WHERE P.COLOR = 'Red'  
GROUP BY SNO  
HAVING COUNT(case when J.CITY ='Beijing' then '1' end) > 0 AND COUNT(case when J.CITY ='Tianjin' then '1' end)>0 

7、查询供应商和工程所在城市相同的供应商能提供的零件代码。

输入以下代码并运行：

SELECT PNO  
FROM spj  
INNER JOIN J  
ON J.JNO = spj.JNO  
INNER JOIN S  
ON S.SNO = spj.SNO  
WHERE S.CITY = J.CITY  
GROUP BY spj.PNO  

8、查询上海供应商不提供任何零件的工程代码。

输入以下代码并运行：

SELECT spj.JNO  
FROM spj  
INNER JOIN S  
ON S.SNO = spj.SNO  
GROUP BY spj.JNO  
HAVING COUNT(case when S.CITY ='Shanghai' then '1' end) = 0  

四、出现的问题及解决方案

问题：

ORDER BY关键字降序排序问题

SQL AND & OR 运算符与优先级问题

WHERE语句的特殊条件——例如is null、between and、like模糊查询等问题

Enum枚举类型的设定值问题

解决方案：

参考《数据库系统概论课本》、菜鸟教程（SQL 教程 | 菜鸟教程）和CSDN博客