数据库查询，单表查询

实验目的及要求

1．基本要求
（1）操作基于单表的SQL的数据定义、数据查询；
（2）操作基于单表的GROUP BY子句、HAVING子句、ORDER BY子句的用法；
（3）操作基于单表的（NOT）IN等谓词的用法；掌握SUM、AVG、COUNT、MAX、MIN等集合函数的用法；
（4）选择运用数据基本查询相关理论和方法，对计算机复杂工程问题的解决方案进行评价，对复杂数据库应用系统开展方案设计与优化。
（5）课前准备：仔细研读实验指导书，详细规划实验过程和步骤，设计实验数据记录表，对相关知识做好预习和准备；
（6）实验过程：按照实验指导书要求，独立完成实验内容，记录好实验过程、关键数据和实验结果；
（7）实验报告：根据实验情况及时撰写实验报告，实验报告应该包括实验目的、实验内容、实验方法及步骤、实验数据及分析，以及实验总结等内容。
2．支撑的课程目标
本实验项目可以支撑课程目标1、课程目标2。
本实验通过启发式方式引导学生依据所掌握的相关知识点，根据业务需求设计SQL语句，促进课程目标的达成。

相关理论

基于单表的SQL数据查询如GROUP BY子句、HAVING子句、ORDER BY子句、（NOT）IN等谓词的用法；SUM、AVG、COUNT、MAX、MIN等集合函数的用法；

实验内容

1．查询products表中p_price（商品价格）在800以上的商品详细信息；
2．查询products表中p_quantity（商品数量）在20和50之间的商品编号、商品名称和商品数量；
3．查询orders表中各会员购买商品的总量，并以汉字列标题形式输出会员帐号，商品总量；
4．查询members表中家庭地址为“湖南”的会员详细信息；
5．查询members表中年龄大于50且性别为“男”的会员详细信息；
6．查询orders表各商品销售总量前3名的商品编号和销售总量；
7．查询orders表中购买过商品的会员帐号，要求去掉重复行；
8．查询orders表已确认、已支付和已配送的订单详细信息；
9．查询性别为“男”的会员详细信息，查询结果按月薪降序排列；
10．查询全体会员的会员帐号，姓名和年龄并按家庭地址升序排列，同一地址中的会员按年龄降序排列；
11．查询会员帐号为‘liuzc’的会员所购买的商品号和订购日期，并按订购日期升序排列；
12．查询购买商品号为‘0910810004’总人数；
13．查询2005年8月6日前，所有商品的订购总量，要求输出商品号和订购总量；
14．查询所有会员的平均月薪，最高月薪和最低月薪；
15．查询所有会员购买商品的种类和，要求输出会员号和商品种类和；
16．查询各类商品的最高购买数量，要求输出最高数量大于10的商品号和最高数量。

实验步骤

查询products表中p_price（商品价格）在800以上的商品详细信息

SELECT P_no
from products
where P_price>800

2．查询products表中p_quantity（商品数量）在20和50之间的商品编号、商品名称和商品数量；

SELECT P_no,p_name,P_quantity
from products
HAVING P_quantity
BETWEEN 20 and 50

3．查询orders表中各会员购买商品的总量，并以汉字列标题形式输出会员帐号，商品总量；
代码：

SELECT M_account 会员账号,sum(O_quantity) 商品总额
from orders
group BY M_account

4．查询members表中家庭地址为“湖南”的会员详细信息；
代码：

select M_address,M_account,M_name 
from members 
where M_address like '%湖南%'

5．查询members表中年龄大于50且性别为“男”的会员详细信息；
代码：

select * 
from members
where (2023-YEAR(M_birth))>50 and m_sex='男'

6．查询orders表各商品销售总量前3名的商品编号和销售总量；
代码：

select P_no,sum(O_quantity)
from orders
GROUP BY(P_no)
ORDER BY sum(O_quantity)DESC limit 3

7．查询orders表中购买过商品的会员帐号，要求去掉重复行；
代码：

select DISTINCT M_account,p_no 
from orders
GROUP BY(P_no)

8．查询orders表已确认、已支付和已配送的订单详细信息；
代码：

select * 
from orders
where O_confirm_state=1 AND O_pay_state=1 and O_send_state=1;

9．查询性别为“男”的会员详细信息，查询结果按月薪降序排列；
代码：

select *
from members
where M_sex='男'
ORDER BY M_salary DESC

10．查询全体会员的会员帐号，姓名和年龄并按家庭地址升序排列，同一地址中的会员按年龄降序排列；
代码：

select M_account,M_name,M_address
from members
ORDER BY M_address asc, (2023-YEAR(M_birth)) desc

11．查询会员帐号为‘liuzc’的会员所购买的商品号和订购日期，并按订购日期升序排列；
代码：

select O_date,P_no
from orders
where M_account = 'liuzc'
ORDER BY O_date

12．查询购买商品号为‘0910810004’总人数；

select count(distinct M)
from orders
where P_no='0910810004'

13．查询2005年8月6日前，所有商品的订购总量，要求输出商品号和订购总量；
代码：

SELECT P_no ‘商品号’,SUM(O_quantity) ‘订购总量’
FROM orders
WHERE O_date < '2005-08-06' GROUP BY P_no

14．查询所有会员的平均月薪，最高月薪和最低月薪；
代码：

select avg(M_salary),max(M_salary),min(M_salary)
from members

15．查询所有会员购买商品的种类和，要求输出会员号和商品种类和；
代码：

SELECT M_account ‘会员号’,COUNT(DISTINCT P_no) ‘商品种类和’
FROM orders
 GROUP BY M_account

16．查询各类商品的最高购买数量，要求输出最高数量大于10的商品号和最高数量。
代码：

SELECT  P_no ‘商品号’,SUM(O_quantity) ‘最高数量’
FROM orders
GROUP BY P_no HAVING SUM(O_quantity)>5
ORDER BY SUM(O_quantity) DESC