「 数据查询（基础篇）」

--基础篇
CREATE DATABASE sp3;
USE sp3;
CREATE TABLE SHOP(
  ShopNo CHAR(3) PRIMARY KEY,
  ShopName CHAR(10),
  ShopAddress CHAR(20)
);

CREATE TABLE PRODUCT(
  ProNo CHAR(3) PRIMARY KEY,
  ProName CHAR(10),
  ProPrice DECIMAL
);

CREATE TABLE SALE(
  ShopNo CHAR(3),
  ProNo CHAR(3),
  Amount INT,
  PRIMARY KEY (ShopNo,ProNo),
  FOREIGN KEY(ShopNo)REFERENCES SHOP(ShopNo),
  FOREIGN KEY(ProNo)REFERENCES PRODUCT(ProNo)
);

INSERT INTO SHOP VALUES ('S01','乐购','上海'),
                        ('S02','红星','北京'),
                        ('S03','苏果','南京'),
                        ('S04','联华','北京');
                        

INSERT INTO PRODUCT VALUES ('P01','海尔冰箱','2400'),
                           ('P02','春兰空调','2000'),
                           ('P03','海尔电视','1200'),
                           ('P04','长虹电视','1200');

INSERT INTO SALE VALUES ('S01','P01','100 '),
                        ('S01','P02','200 '),
                        ('S01','P03','150 '),
                        ('S02','P01','120 '),
                        ('S02','P02','80  '),
                        ('S03','P01','100 '),
                        ('S03','P03','200 '),
                        ('S03','P04',NULL);          

--1.单表查询
--（1）选择表中的若干列
--①查询所有商店的商店号、商店名。
SELECT ShopNo,ShopName FROM SHOP;
--②查询所有商品的详细信息。
SELECT * FROM PRODUCT;
--（2）选择表中不重复的元组
--①查询销售了商品的商店号。
SELECT DISTINCT ShopNo FROM SALE;

--（3）选择表中满足条件的元组
--①查询销售了p01商品的商店编号。
SELECT ShopNo FROM SALE WHERE ShopNo = 'p01';
--②查询价格在2000~3000的商品号、商品名。
SELECT ProNo,ProName FROM PRODUCT WHERE ProPrice BETWEEN 2000 AND 3000;

--③查询销售了p01或p02商品的商店号。
SELECT ShopNo FROM SALE WHERE ProNo IN('p01','p02');
SELECT ShopNo FROM SALE WHERE ProNo = 'p01' OR ProNo = 'p02';

--④查询所有电视商品的品牌、价格。
SELECT ProName,ProPrice FROM Product WHERE ProName LIKE '%电视';

--⑤查询销售表中无销售数量的销售记录。

SELECT * FROM SALE WHERE Amount is null;

--⑥查询价格在2000元以上的海尔品牌商品。
SELECT ProNo,ProName FROM PRODUCT WHERE ProPrice > 2000 AND ProName LIKE '海尔%';

--（4）使用ORDER BY子句对查询结果进行排序
--①查询所有商品的信息，结果按价格降序排列，价格相同时按商品名升序排列。
SELECT * FROM PRODUCT ORDER BY ProPrice DESC,ProName;
--（5）使用聚集函数查询
--①查询销售了商品p01的商店数以及p01商品的销售总量、平均销售量、最大销售量和最小销售量。
SELECT COUNT( DISTINCT ShopNo) AS 商店数,SUM(Amount) AS 销售总量,AVG(Amount) AS 平均销售量,MAX(Amount) AS 最大销售量,MIN(Amount) AS 最小销售量 FROM SALE WHERE ProNo = 'P01';
--（6）使用GROUP BY子句进行分组查询
--①查询各商品的销售总量，只显示销售总量在300以上的商品及销售总量。
SELECT ProNo,SUM(Amount) AS 销售总量 FROM SALE GROUP BY ProNo HAVING SUM(Amount) > 300;
--2.多表查询
--（1）等值连接查询
--①查询每个商店及其销售情况。
SELECT SHOP.*,SALE.*FROM SHOP,SALE WHERE SHOP.ShopNo = SALE.ShopNo;

--（2）自然连接查询
--①对上例用自然连接完成。
SELECT SHOP.ShopNo,ShopName,ShopAddress, ProNo,Amount FROM SHOP,SALE WHERE SHOP.ShopNo = SALE.ShopNo;

--（3）外连接查询
--①查询每个商店及其销售情况，无任何销售记录的商店也要显示其基本信息。
SELECT SHOP.*,SALE.* FROM SHOP  FULL OUTER JOIN SALE ON SHOP.ShopNo = SALE.ShopNo;

--3.嵌套查询
--（1）不相关子查询
--①查询与红星商店在同一地区的商店信息。
SELECT SHOP.* FROM SHOP WHERE ShopAddress IN (SELECT ShopAddress FROM SHOP WHERE ShopName = '红星');
--（2）相关子查询
--①查询至少销售了商店s02所销售的全部商品的商店号。
SELECT DISTINCT ShopNo FROM Sale X WHERE NOT EXISTS
    (SELECT * FROM Sale Y WHERE Y.ShopNo= 's02' AND NOT EXISTS 
        (SELECT * FROM sale Z WHERE Z.ShopNo=x.ShopNo AND Z.ProNo=Y.ProNo));

--4.使用UNION的集合查询
--①查询上海及北京地区的商店信息。
SELECT * FROM SHOP WHERE ShopAddress = '上海' 
UNION
SELECT * FROM SHOP WHERE ShopAddress = '北京';