SQL——1、查询执行顺序

这才是完整的SELECT查询

SELECT DISTINCT column， AGG_FUNC(column_or_expression)， …       #SELECT 选择；DISTINCT 关键字来指定某个或某些属性列唯一返回；AGG_FUNC统计函数

FROM mytable

JOIN another_table                                                                                           #JOIN ON 连接表 

    ON mytable.column = another_table.column

WHERE constraint_expression

GROUP BY column                                                                                           #GROUP BY进行分组，HAVING对分组结果进行筛选

HAVING constraint_expression

ORDER BY column ASC/DESC                                                                       #ORDER BY对结果进行排序，ASC升序，DESC降序，默认升序

LIMIT count OFFSET count;                                                                             #LIMIT  OFFSET，对排序结果进行截取

 

查询执行顺序

1、 FROM 和 JOIN

FROM或JOIN会第一个执行，确定一个整体的数据范围。如果要JOIN不同表，可能会生成一个临时Table来用于下面的过程。

2、 WHERE

我们确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选col属性 只能来自FROM圈定的表。 AS别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式。

3、 GROUP BY

如果你用了 GROUP BY 分组，那GROUP BY 将对之前的数据进行分组，统计等，并将是结果集缩小为分组数。这意味着 其他的数据在分组后丢弃。

4、 HAVING

如果你用了 GROUP BY 分组， HAVING 会在分组完成后对结果集再次筛选。AS别名也不能在这个阶段使用。

5、 SELECT

确定结果之后，SELECT用来对结果col简单筛选或计算，决定输出什么数据。

6、 DISTINCT

如果数据行有重复DISTINCT 将负责排重。

7、 ORDER BY

在结果集确定的情况下，ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了。此时可以用AS别名。

8、 LIMIT / OFFSET

最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据。

 

————————————————

版权声明：本文为CSDN博主「风中凌乱的小精灵」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/shujian_tianya/article/details/81067206

1、基本查询

/*2.1供应商名称、地址、联系电话*/
SELECT name,address,phone
FROM supplier;
 
/*2.2最近一周内提交的总价大于1000元的订单编号、顾客编号等订单的所有信息*/
SELECT *
FROM orders
WHERE Datediff(dd,orderdate,GETDATE())<=7
      AND totalprice>1000;
      
/*2.3每个顾客的订单金额*/
SELECT orders.custkey,name,SUM(totalprice) sumprice
FROM customer,orders
WHERE customer.custkey=orders.custkey
GROUP BY orders.custkey,name 
ORDER BY SUM(totalprice) DESC;
 
/*2.4订单平均金额超过元的顾客编号以及姓名*/
SELECT orders.custkey,name,AVG(totalprice) avgerage
FROM customer,orders
WHERE customer.custkey=orders.custkey
GROUP BY orders.custkey,name
HAVING AVG(totalprice)>1000
ORDER BY orders.custkey DESC;
 
/*2.5 查询与“上海黎顺服装经营部”在同一个国家的供应商编号、名称和地址信息*/
SELECT suppkey,name,address
FROM supplier
WHERE nationkey=
               (
                 SELECT nationkey
                 FROM supplier
                 WHERE name='上海黎顺服装经营部'
               );
               
SELECT a.suppkey,a.name,a.address
FROM supplier a , supplier b
WHERE b.nationkey=b.nationkey
     AND b.name ='上海黎顺服装经营部';
     
/*2.6 供应商价格大于零售价格的零件名、制造商名、零售价格和供应商价格*/
SELECT  name,mfgr,retailprice,supplycost
FROM part,partsupp
WHERE supplycost >retailprice
    AND part.partkey=partsupp.partkey
    
/*2.8顾客“阿波罗"订单的订单编号、总价、订购的零件编号、数量和明细价格*/
SELECT orders.orderkey,totalprice,partkey,quantity,extendedprice
FROM orders,lineitem                                                                                                 
WHERE orders.orderkey=lineitem.orderkey
  AND custkey =
             (
                SELECT custkey
                FROM customer
                WHERE name='阿波罗'
             );

2、高级查询

/*3.1订购“沈阳市新民高中压阀门厂”制造“竹炭黄土按摩皂”的顾客*/
SELECT custkey,name /* 找出订购的顾客的编号和姓名*/
FROM customer
WHERE custkey IN
(
   SELECT custkey/*含有该订单编号的顾客编号*/
   FROM orders
   WHERE orderkey IN
              (
              SELECT orderkey /*含有该零件编号的的订单编号*/
              FROM lineitem
              WHERE partkey=
                           (
                            SELECT partkey/* 找出零件编号*/
                            FROM part
                            WHERE name='竹炭黄土按摩皂'
                             AND  mfgr='沈阳市新民高中压阀门厂'
                           )         
              )
   );
   
/*3.2 没有购买过“沈阳市新民高中压阀门厂”制造的“竹炭黄土按摩皂”的顾客*/
SELECT custkey,name /* 找出订购的顾客的编号和姓名*/
FROM customer
WHERE custkey NOT IN
(
   SELECT custkey/*含有该订单编号的顾客编号*/
   FROM orders
   WHERE orderkey IN
              (
              SELECT orderkey /*含有该零件编号的的订单编号*/
              FROM lineitem
              WHERE partkey=
                           (
                            SELECT partkey/* 找出零件编号*/
                            FROM part
                            WHERE name='竹炭黄土按摩皂'
                             AND  mfgr='沈阳市新民高中压阀门厂'
                           )         
              )
   );
 
/*3.3 至少购买过顾客“董浩川”购买过的全部零件的顾客姓名*/
 
SELECT name FROM customer WHERE custkey IN 
(
  SELECT custkey FROM orders WHERE orderkey IN
  (
    SELECT orderkey FROM lineitem WHERE partkey IN
      ( 
        SELECT partkey  FROM lineitem WHERE orderkey IN
         (
            SELECT orderkey FROM orders WHERE custkey=
             (
                SELECT custkey FROM customer WHERE name='董浩川'
             )
          )
       )
    )
 )；
 
GROUP BY name
HAVING(COUNT(*)=
(
   SELECT count ( DISTINCT partkey) FROM lineitem WHERE orderkey IN
   (
     SELECT orderkey FROM orders  WHERE custkey=
     (
        SELECT custkey FROM customer WHERE name='董浩川'
     )
   )
  )
)；
 
/*3.4查询订单平均金额超过万元的顾客的中国国籍顾客信息*/
SELECT *
FROM customer
WHERE customer.custkey IN
             (
               SELECT orders.custkey
               FROM customer,orders
               WHERE customer.custkey=orders.custkey
               GROUP BY orders.custkey
               HAVING AVG(totalprice)>10000
                         )
AND nationkey=
              (
                SELECT nationkey
                FROM nation
                WHERE name='中国'
              );
 
/*3.5“董浩川”和“戴美竹”都订购过的全部零件信息*/
SELECT *
FROM part
WHERE partkey IN
         (
            SELECT partkey  FROM lineitem WHERE orderkey IN
                      (
                        SELECT orderkey FROM orders WHERE custkey=
                                    (
                                     SELECT custkey FROM customer WHERE name='董浩川'
             )
          )；
   

  INTERSECT
  SELECT partkey  FROM lineitem WHERE orderkey IN
         (
            SELECT orderkey FROM orders WHERE custkey=
             (
                SELECT custkey FROM customer WHERE name='戴美竹'
             )
          )
  
);
 
/*3.6“董浩川”和“戴美竹”订购过的全部零件信息*/
SELECT *
FROM part
WHERE partkey IN
(
  SELECT partkey  FROM lineitem WHERE orderkey IN
         (
            SELECT orderkey FROM orders WHERE custkey=
             (
                SELECT custkey FROM customer WHERE name='董浩川'
             )
          )
   

  UNION
  SELECT partkey  FROM lineitem WHERE orderkey IN
         (
            SELECT orderkey FROM orders WHERE custkey=
             (
                SELECT custkey FROM customer WHERE name='戴美竹'
             )
          )
  
);

/*3.8“董浩川”订购过而且“戴美竹”没有订购过的全部零件信息*/
SELECT *
FROM part
WHERE partkey IN
(
  SELECT partkey  FROM lineitem WHERE orderkey IN
         (
            SELECT orderkey FROM orders WHERE custkey=
             (
                SELECT custkey FROM customer WHERE name='董浩川'
             )
          )
   
  EXCEPT
  SELECT partkey  FROM lineitem WHERE orderkey IN
         (
            SELECT orderkey FROM orders WHERE custkey=
             (
                SELECT custkey FROM customer WHERE name='戴美竹'
             )
          )
  
);