单表查询
前言:做这个实验之前建议看我上一篇博客实验1-数据库的定义,里面有表的创建和数据的添加!
一、实验目的及要求
(1)操作基于单表的SQL的数据定义、数据查询;
(2)操作基于单表的GROUP BY子句、HAVING子句、ORDER BY子句的用法;
(3)操作基于单表的(NOT)IN等谓词的用法;掌握SUM、AVG、COUNT、MAX、MIN等集合函数的用法;
二、实验环境
操作系统:Windows 11
应用软件:MySQL Navicat
三、实验内容
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的商品号和最高数量。
四、实验步骤和运行结果
1.查询products表中p_price(商品价格)在800以上的商品详细信息;
SELECT * FROM products WHERE p_price>800;
运行结果:
2.查询products表中p_quantity(商品数量)在20和50之间的商品编号、商品名称和商品数量;
SELECT P_no,P_name,P_quantity FROM products WHERE 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 FROM members WHERE m_address like '湖南%';
运行结果:
5.查询members表中年龄大于50且性别为“男”的会员详细信息;
SELECT * from members where m_sex= '男' and (YEAR(CURRENT_DATE)-year(m_birth))>50;
运行结果:
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 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_name,M_account,M_address FROM members ORDER BY M_address ASC,(YEAR(CURRENT_DATE)-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(*) as total_people 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 ;
运行结果: