数据库实验——数据查询

 数据查询   

一、实验目的

1、掌握select语句的基本语法和查询条件表示方法

2、掌握数据汇总方法

3、掌握group by子句的作用和使用方法

4、掌握having子句的作用和使用方法

5、掌握order by子句的作用和使用方法

6、掌握连接查询的表示方法

7、掌握嵌套查询的表示方法

二、实验内容与步骤

1、在GoodsOrder数据库中，利用SQL语句实现下面操作：

（要求以文本形式给出对应的SQL语句和查询结果截图）

查询语句（文本）：

查询结果（截图）：

（1）查询所有商品的详细信息。

（2）查询客户订单中的所有付款方式，并且要求显示的付款方式不重复。

（3）查询各种球类商品的编号、名称和价格等信息。

（4）查询“上海、江苏南京、河南郑州”所有的客户信息，并按客户所在省市降序排列。

（5）查询商品价格较高的前两名信息。

补充知识内容：

TOP子句可以提取记录集的前几条记录，格式为：

SELECT  TOP  整数|百分数  列名1[,列名2,…]  FROM 表名

（6）查询姓“张”且不为单名所有的客户姓名和性别。

（7）查询备注不为空的VIP客户信息。

（8）查询保质期在2020年下半年之间的食品类商品的编号、名称、生产商和库存量。

（9）统计各类商品的最低库存数，统计结果所在列标题显示为“最低库存量”。

（10）统计江苏的客户数量。

（11）按付款方式统计订单总数量大于等于5的付款方式及订单总数，并按付款方式进行排序。

2、在StuCourse数据库中，用SQL语句实现如下查询：

（1）查询每个学生的情况以及其选修课程的情况（要求结果集是自然连接）

（2）查询“电子信息工程”专业的学生姓名及其选课详情，无论该学生有没有选课

（3）用JOIN连接查询的方法查询选修了“程序设计基础”的学生姓名

（4）统计“计算机科学与技术”专业各学生的平均成绩

（5）统计各专业学生的平均成绩在80以上的学生人数

（6）将各学生的学号和姓名按平均成绩降序排列

（7）用JOIN连接查询的方法查找所有选修了“2001”或“1002”课程的学生学号和姓名

（8）用子查询查找所有选修了“2001”或“1002”课程的学生学号和姓名

（9）用子查询查询与“李进”在同一个专业学习的学生学号、姓名和专业

（10）查询所有课程的成绩都在80分以上的学生姓名、学号

（11）用子查询查询所有未选修1002课程的学生姓名

（12）用子查询查找“计算机科学与技术”专业“1001”课程成绩比所有“电子信息工程”专业“1001”课程成绩都高的学生的学号和姓名

三、实验心得

实验内容：

1、在GoodsOrder数据库中，利用SQL语句实现下面操作：

（1）查询所有商品的详细信息。

查询语句：

USE GoodsOrder
SELECT *
FROM Goodsinfo

查询结果：

（2）查询客户订单中的所有付款方式，并且要求显示的付款方式不重复。

查询语句：

USE GoodsOrder
SELECT DISTINCT 付款方式
FROM OrderList

查询结果：

（3）查询各种球类商品的编号、名称和价格等信息。

查询语句：

USE GoodsOrder
SELECT *
	FROM GoodsInfo
	WHERE 商品类型 = '体育用品'

查询结果：

（4）查询“上海、江苏南京、河南郑州”所有的客户信息，并按客户所在省市降序排列。

查询语句：

USE GoodsOrder
SELECT *
	FROM CustomerInfo
	WHERE 所在省市 = '上海市' OR 所在省市 = '江苏南京' OR 所在省市 = '河南郑州'
	ORDER BY 所在省市 DESC

查询结果：

（5）查询商品价格较高的前两名信息。

补充知识内容：

TOP子句可以提取记录集的前几条记录，格式为：

SELECT  TOP  整数|百分数  列名1[,列名2,…]  FROM 表名

查询语句：

USE GoodsOrder
SELECT TOP 2 *
	FROM GoodsInfo
	ORDER BY 单价 DESC

查询结果：

（6）查询姓“张”且不为单名所有的客户姓名和性别。

查询语句：

USE GoodsOrder
SELECT 客户姓名, 性别
	FROM CustomerInfo
	WHERE 客户姓名 LIKE '张__' AND 客户姓名 NOT LIKE '张_'

查询结果：

（7）查询备注不为空的VIP客户信息。

查询语句：

USE GoodsOrder
SELECT *
	FROM CustomerInfo
	WHERE 备注 NOT LIKE 'NULL' AND VIP = '1'

查询结果：

（8）查询保质期在2020年下半年之间的食品类商品的编号、名称、生产商和库存量。

查询语句：

USE GoodsOrder
SELECT 商品编号, 商品名称, 生产商 ,库存量
	FROM GoodsInfo
	WHERE 商品类型 = '食品' AND 保质期 BETWEEN '2020-7-01 00:00:00.000' AND '2020-12-31 23:59:59.000'

查询结果：

（9）统计各类商品的最低库存数，统计结果所在列标题显示为“最低库存量”。

查询语句：

USE GoodsOrder
SELECT 商品类型, MIN(库存量) AS '最低库存量'
	FROM GoodsInfo
	GROUP BY 商品类型

查询结果：

（10）统计江苏的客户数量。

查询语句：

USE GoodsOrder
SELECT COUNT(所在省市) AS '江苏客户数量'
	FROM CustomerInfo
	WHERE 所在省市 LIKE '江苏%'

查询结果：

（11）按付款方式统计订单总数量大于等于5的付款方式及订单总数，并按付款方式进行排序。

查询语句：

USE GoodsOrder
SELECT 付款方式, SUM(数量) AS '订单总数'
	FROM OrderList
	GROUP BY 付款方式
		HAVING SUM(数量) >= '5'
	ORDER BY 付款方式

查询结果：

2、在StuCourse数据库中，用SQL语句实现如下查询：

（1）查询每个学生的情况以及其选修课程的情况（要求结果集是自然连接）

查询语句：

USE StudentCourse
	SELECT Student.*, StuCourse.课程号, StuCourse.成绩
		FROM Student, StuCourse
		WHERE Student.学号  = StuCourse.学号

查询结果：

 

（2）查询“电子信息工程”专业的学生姓名及其选课详情，无论该学生有没有选课

查询语句：

USE StudentCourse
	SELECT Student.姓名, StuCourse.课程号
		FROM Student LEFT JOIN StuCourse ON Student.学号 = StuCourse.学号
		WHERE Student.专业名 = '电子信息工程'

查询结果：

 

（3）用JOIN连接查询的方法查询选修了“程序设计基础”的学生姓名

查询语句：

USE StudentCourse
	SELECT Student.姓名
		FROM Student JOIN StuCourse JOIN Course
		ON Course.课程号 = StuCourse.课程号
		ON Student.学号  = StuCourse.学号
		WHERE 课程名 = '程序设计基础'

查询结果：

 

（4）统计“计算机科学与技术”专业各学生的平均成绩

查询语句：

USE StudentCourse
	SELECT Student.姓名, AVG(StuCourse.成绩) AS '计算机科学与技术专业各学生平均成绩'
		FROM Student JOIN StuCourse
		ON Student.学号 = StuCourse.学号
		WHERE Student.专业名 = '计算机科学与技术'
		GROUP BY Student.姓名

查询结果：

 

（5）统计各专业学生的平均成绩在80以上的学生人数

查询语句：

USE StudentCourse
SELECT Student.专业名, COUNT(a.姓名) AS '人数'
FROM(
	SELECT  Student.姓名, AVG(StuCourse.成绩) AS '学生平均成绩'
		FROM Student JOIN StuCourse
		ON Student.学号 = StuCourse.学号
		GROUP BY Student.姓名 
			HAVING AVG(StuCourse.成绩) > 80
	)a JOIN Student
		ON Student.姓名  = a.姓名
GROUP BY 专业名

查询结果：

 

（6）将各学生的学号和姓名按平均成绩降序排列

查询语句：

USE StudentCourse
SELECT Student.学号, a.姓名
FROM(
	SELECT Student.姓名, AVG(StuCourse.成绩) AS '平均成绩'
		FROM Student JOIN StuCourse
		ON Student.学号 = StuCourse.学号
		GROUP BY Student.姓名
		)a JOIN Student
		ON Student.姓名 = a.姓名
ORDER BY a.平均成绩 DESC

查询结果：

 

（7）用JOIN连接查询的方法查找所有选修了“2001”或“1002”课程的学生学号和姓名

查询语句：

USE StudentCourse
	SELECT DISTINCT Student.姓名, Student.学号
		FROM Student JOIN StuCourse
		ON Student.学号 = StuCourse.学号
		WHERE 课程号 = '2001' OR 课程号 = '1002'

查询结果：

 

（8）用子查询查找所有选修了“2001”或“1002”课程的学生学号和姓名

查询语句：

USE StudentCourse
	SELECT 学号, Student.姓名
		FROM Student 
			WHERE 学号 IN 
				( SELECT DISTINCT 学号
					 FROM StuCourse
					 WHERE 课程号 = '2001' OR 课程号 = '1002'
				)

查询结果：

 

 

（9）用子查询查询与“李进”在同一个专业学习的学生学号、姓名和专业

查询语句：

USE StudentCourse
	SELECT 学号, 姓名, 专业名
		FROM Student
			WHERE 专业名 IN
				(SELECT 专业名 
					FROM Student
					WHERE 姓名 = '李进'
					)

查询结果：

 

 

（10）查询所有课程的成绩都在80分以上的学生姓名、学号

查询语句：

USE StudentCourse
SELECT Student.姓名, Student.学号
FROM (
	SELECT Student.学号
	FROM Student JOIN StuCourse 
	ON Student.学号 = StuCourse.学号
	GROUP BY Student.学号 
		HAVING MIN(StuCourse.成绩) > 80)a JOIN Student
	ON Student.学号 = a.学号

查询结果：

 

 

（11）用子查询查询所有未选修1002课程的学生姓名

查询语句：

USE StudentCourse
SELECT Student.姓名
FROM Student
WHERE Student.学号 NOT IN (
	SELECT StuCourse.学号
	FROM StuCourse
	WHERE StuCourse.课程号 = 1002
)

查询结果：

 

 

（12）用子查询查找“计算机科学与技术”专业“1001”课程成绩比所有“电子信息工程”专业“1001”课程成绩都高的学生的学号和姓名

查询语句：

USE StudentCourse
SELECT Student.姓名, Student.学号
FROM Student JOIN StuCourse ON Student.学号 = StuCourse.学号
WHERE Student.专业名 = '计算机科学与技术' AND StuCourse.课程号 = '1002' AND StuCourse.成绩 > (
		SELECT MAX(StuCourse.成绩)
		FROM StuCourse JOIN Student
		ON Student.学号 = StuCourse.学号
		WHERE StuCourse.课程号 = '1001' AND Student.专业名 = '电子信息工程')

查询结果：