mysql复杂查询练习题_数据库练习题(DDL&DML&复杂查询部分) -----mysql数据库实现（商品查询练习）...-CSDN博客

本文链接：https://blog.csdn.net/weixin_29185493/article/details/113223184

本文提供了一系列MySQL数据库的练习题，包括查询购买了供应商'宝洁'产品的所有顾客、查找购买了全部商品的顾客、找出牙膏销售最多的供应商、调整牙膏商品价格以及删除未被购买的商品记录等操作。通过这些练习，读者可以提升在MySQL中的复杂查询和数据管理技能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

A.求购买了供应商"宝洁"产品的所有顾客

B.求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客姓名

C.求牙膏卖出数量最多的供应商

D.将所有的牙膏商品单价增加10%

E.删除从未被购买的商品记录

CREATE TABLE PRODUCT(

PRODUCTID VARCHAR(4) PRIMARY KEY,

PRODUCTNAME VARCHAR(20) NOT NULL,

UNITPRICE INT(7),

CATEGORY VARCHAR(10),

PROVIDER VARCHAR(30),

CONSTRAINT FK_UNITPRICE CHECK (UNITPRICR > 0)

);

CREATE TABLE CUSTOMER(

CUSTOMERID VARCHAR(4) PRIMARY KEY,

CNAME VARCHAR(20) NOT NULL,

LOCATION VARCHAR(60)

);

CREATE TABLE PURCASE(

CUSTOMERID VARCHAR(4),

PRODUCTID VARCHAR(4),

QUANTITY INT(22),

CONSTRAINT FK_QUANTITY CHECK (QUANTITY BETWEEN 0 AND 20),

CONSTRAINT FK_CUSTOMERID FOREIGN KEY(CUSTOMERID) REFERENCES CUSTOMER(CUSTOMERID),

CONSTRAINT FK_PRODUCTID FOREIGN KEY(PRODUCTID) REFERENCES PRODUCT(PRODUCTID)

)

INSERT INTO PRODUCT VALUES

('M01','佳洁士',8.00,'牙膏','宝洁'),

('M02','高露洁',6.50,'牙膏','高露洁'),

('M03','洁诺',5.00,'牙膏','联合利华'),

('M04','舒肤佳',3.00,'香皂','宝洁'),

('M05','夏士莲',5.00,'香皂','联合利华'),

('M06','雕牌',2.50,'洗衣粉','纳爱斯'),

('M07','中华',3.50,'牙膏','联合利华'),

('M08','汰渍',3.00,'洗衣粉','宝洁'),

('M09','碧浪',4.00,'洗衣粉','宝洁')

INSERT INTO PURCASE VALUES

('C01','M01',3),

('C01','M05',2),

('C01','M08',2),

('C02','M02',5),

('C02','M06',4),

('C03','M01',1),

('C03','M05',1),

('C03','M06',3),

('C03','M08',1),

('C04','M03',7),

('C04','M04',3),

('C04','M09',1),

('C05','M06',2),

('C05','M07',8)

SELECT CNAME,LOCATION FROM CUSTOMER WHERE CUSTOMERID IN

(SELECT CUSTOMERID FROM PURCASE WHERE PRODUCTID IN

(SELECT PRODUCTID FROM PRODUCT WHERE PROVIDER = "宝洁"))

SELECT * FROM CUSTOMER WHERE CUSTOMERID IN

(SELECT CUSTOMERID FROM PURCASE WHERE PRODUCTID IN

(SELECT PRODUCTID FROM PURCASE WHERE CUSTOMERID =

(SELECT CUSTOMERID FROM CUSTOMER WHERE CNAME = 'Dennies')))

**每个派生出来的表都必须有一个自己的别名,

**进行嵌套查询的时候子查询出来的的结果是作为一个派生表来进行上一级的查询的，

**所以子查询的结果必须要有一个别名

SELECT PROVIDER FROM

(SELECT PRO.PROVIDER , SUM(QUANTITY) FROM PRODUCT PRO , PURCASE P

WHERE PRO.PRODUCTID = P.PRODUCTID

AND PRO.CATEGORY = '牙膏' GROUP BY PRO.PROVIDER ORDER BY SUM(QUANTITY) DESC) M

limit 1

UPDATE PRODUCT SET UNITPRICE = UNITPRICE * 1.1 WHERE CATEGORY = '牙膏'

DELETE FROM PRODUCT

WHERE PRODUCTID NOT IN

(SELECT PRODUCTID FROM PURCASE)