本文共 5464 字，大约阅读时间需要 18 分钟。

1.SQL基本查询语句

SELECT 列名，类名……

FROM 表名/视图名，表名/视图名……

WHERE 条件表达式

GROUP BY 列名1 HAVING 条件表达式

ORDER BY 列名2

       根据WHERE子句的条件表达式，从FROM子句指定的基本表或者视图中找出满足条件的元组，再按照SELECT子句中的目标列表达式，选出元组中属性值形成结果表。

      如果有GROUP BY子句，就按照列名1的值进行分组，该属性列值相等的元组为一组，通常会在每组中使用聚集函数。如果GROUP BY子句带有HAVING短语，则只有满足指定条件的组才能输出。

       如果有ORDER BY子句，结果还要按照列名2的值升序或者降序。

2.基本查询

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

3.高级查询

/*3.1订购“沈阳市新民高中压阀门厂”制造“竹炭黄土按摩皂”的顾客*/SELECT custkey,name /* 找出订购的顾客的编号和姓名*/FROM customerWHERE 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 customerWHERE 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 nameHAVING(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 customerWHERE 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 partWHERE 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 partWHERE 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 partWHERE 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='戴美竹'             )          )  );

转载地址：http://wxyni.baihongyu.com/