操作Petstore数据查询丨分类汇总和排序操作

MySQL常用命令

1.PetStore数据库和表结构及示例数据

2.操作Petstore数据查询丨分类汇总和排序操作 ⇦当前位置🪂

你需要根据提示先创建好PetStore数据库和表结构及示例数据才可以完成下方的操作
点击前往→

操作Petstore数据查询、分类汇总和排序操作

创建一个空数据库：petstore

1
CREATE DATABASE IF NOT EXISTS petstore;

选定petstore为当前的使用数据库

1
USE petstore;

导入准备好的数据库

1
SOURCE D:\petstore.sql;

select语句的基本使用

  1. 查询account表中客户的姓名（fullname）、地址（address）和电话（phone），显示的列标题要求显示“姓名”“地址”“电话”。
1
SELECT fullname AS 姓名, address AS 地址, phone AS 电话 FROM account;

  1. 查询lineitem表中商品号（itemid）和单价（unitprice），要求消除重复行。
1
SELECT DISTINCT itemid, unitprice FROM lineitem;

  1. 计算lineitem表中每条记录的商品金额。
1
SELECT orderid, itemid, quantity * unitprice AS 金额 FROM lineitem;

  1. 查询account表中客户的姓名（fullname）和性别（sex），要求性别为“男”显示为1，为“女”显示为0。
1
2
3
4
5
SELECT fullname, 
    CASE WHEN sex = '男' THEN '1'
         WHEN sex = '女' THEN '0'
    END AS sex
FROM account;

  1. 查询product表中的商品名（name）和档次。档次按价格（unitcost）划分，1000元以下显示为“低价商品”，1000元-2000元之间显示为“中档商品”，2000元及以上显示为“高档商品”。
1
2
3
4
5
6
SELECT name, 
    CASE WHEN unitcost < 1000 THEN '低价商品'
         WHEN unitcost >= 1000 AND unitcost < 2000 THEN '中档商品'
         ELSE '高档商品'
    END AS 档次
FROM product;

条件查询

  1. 显示orders表中单笔订单金额大于等于200元的用户号（userid）、订单总价（totalprice）和订单状态（status）。
1
SELECT userid, totalprice, status FROM orders WHERE totalprice >= 200;

  1. 查询orders表中2020年4月的所有订单。
1
SELECT * FROM orders WHERE orderdate BETWEEN '2020-04-01' AND '2020-04-30';

  1. 查询account表中女客户的姓名（fullname）、地址（address）和电话（phone），显示的列标题要求用中文“姓名”“地址”“电话”表示。
1
SELECT fullname AS 姓名, address AS 地址, phone AS 电话 FROM account WHERE sex='女';

  1. 查询account表中姓吴的客户信息。
1
SELECT * FROM account WHERE fullname LIKE '吴%';

  1. 查询orders表中订单总价在200-500元内的订单信息。
1
SELECT * FROM orders WHERE totalprice BETWEEN 200 AND 500;

  1. 查询product表中商品号（productid）倒数第4个标号为“W”的商品信息。注意这里是3个下划线。
1
SELECT * FROM product WHERE productid LIKE '%W___';

多表查询

  1. 查询lineitem表中的订单号、商品名称和购买数量。
1
2
SELECT orderid, name, quantity FROM lineitem
  JOIN product ON (itemid=productid);

  1. 显示orders表中单笔订单金额大于等于300元的用户名、订单总价。
1
2
3
SELECT fullname, totalprice FROM orders
  JOIN account ON (orders.userid=account.userid)
  WHERE totalprice >= 300;

  1. 查询“刘晓和”的基本情况和订单情况。
1
2
3
SELECT * FROM orders
  JOIN account ON (orders.userid=account.userid)
  WHERE fullname='刘晓和';

  1. 统计2020年5月以前订购了商品的女客户的姓名和订单总价。
1
2
3
SELECT fullname, totalprice FROM orders
  JOIN account ON (orders.userid=account.userid)
  WHERE orderdate <= '2020-05-01' AND sex='女';

  1. 查找购买了商品号为“FI-SW-02”的商品的订单号、用户号和订单日期。
1
2
SELECT orderid, userid, orderdate FROM orders
  WHERE orderid IN (SELECT orderid FROM lineitem WHERE itemid = 'FI-SW-02');

  1. 查找product表中价格不低于“波斯猫”商品的信息
1
2
SELECT * FROM product
  WHERE unitcost >= ANY (SELECT unitcost FROM product WHERE name = '波斯猫');

分类汇总与排序

  1. 统计客户总数。
1
SELECT COUNT(*) AS 总人数 FROM account;

  1. 计算orders表中每单的平均价。
1
SELECT orderid, AVG(totalprice) AS 每单平均价 FROM orders GROUP BY orderid;

  1. 计算orders表中的成交总额。
1
SELECT SUM(totalprice) AS 成交总额 FROM orders;

  1. 显示orders表中的单笔最高成交额和最低成交额
1
SELECT MAX(totalprice) AS 最高成交额, MIN(totalprice) AS 最低成交额 FROM orders;

  1. 按性别统计客户人数。
1
SELECT sex, COUNT(*) FROM account GROUP BY sex;

  1. 按商品类别统计各类商品总数、平均单价。
1
SELECT catid, SUM(qty), AVG(unitcost) FROM product GROUP BY catid;

  1. 将客户信息按电话号码从大到小排序。
1
SELECT * FROM account ORDER BY phone DESC;

  1. 将orders表按用户号从小到大排序，用户号相同的按订单日期从大到小排序。
1
SELECT * FROM orders ORDER BY userid, orderdate DESC;

  1. 显示lineitem表中商品的购买总数量超过2件的商品号和购买总数量，并按购买数量从小到大排序。
1
2
3
SELECT itemid, SUM(quantity) FROM lineitem
  GROUP BY itemid HAVING SUM(quantity) > 2
  ORDER BY SUM(quantity);