<<SQL必知必会>> -- 笔记3

创建计算字段

拼接

  SELECT Concat(vend_name, ' (', vend_country, ')')
          AS ven_title
  FROM Vendors
  ORDER BY vend_name;

计算

  SELECT prod_id, quantity, item_price,
         quantity*item_price AS expanded_price
  FROM OrderItems
  WHERE order_num = 20008;

测试计算

SELECT可以用来检验语句是否正确或者测试，例如：

    SELECT 2 * 3; -- (will return 6)
    SELECT TRIM('   abc    '); -- (will return 'abc')
    SELECT Now(); -- (will return current time)

数据处理函数

函数

文本处理函数

  SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
  FROM Vendors
  ORDER BY vend_name;

SOUNDEX()函数，是一个将任何文本串转换为描述其音节表示的字母数字模式的算法，可以用于模糊音查找

    SELECT cust_name, cust_contact
    FROM Customers
    WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

日期时间处理函数

   SELECT order_num 
   FROM Orders 
   WHERE strftime('%Y', order_date) = 2012

数值处理函数

一般数学函数ABS() COS() EXP() PI() SIN() SQRT() TAN()
分别是绝对值，余弦，指数，圆周率，正弦，开方，正切

汇总数据

聚集函数

AVG

  SELECT AVG(prod_price) AS avg_price
  FROM Products 
  WHERE vend_id = 'DLL01';

COUNT

  SELECT COUNT(*) AS num_cust
  FROM Customers;

MAX

   SELECT MAX(prod_price) AS max_price
   FROM Products;

MIN

   SELECT MIN(prod_price) AS min_price
   FROM Products;

SUM

   SELECT SUM(quantity) AS items_ordered
   FROM OrderItems
   WHERE order_num = 20005;

聚集不同值

DISTINCT

   SELECT AVG(DISTINCT prod_price) AS avg_price
   FROM Products
   WHERE vend_id = 'DLL01';

组合聚集函数

   SELECT COUNT(*) AS num_items,
          MIN(prod_price) AS price_min,
          MAX(prod_price) AS price_max,
          AVG(prod_price) AS price_avg
   FROM Products;

分组数据

GROUP BY

创建分组

   SELECT vend_id, COUNT(*) AS num_prods
   FROM Products 
   GROUP BY vend_id;

GROUP BY在WHERE语句之后，ORDER BY语句之前

过滤分组

WHERE过滤行，HAVING过滤分组

   SELECT cust_id, COUNT(*) AS orders
   FROM Orders
   GROUP BY cust_id 
   HAVING COUNT(*) >= 2;

SELECT子句顺序

SELECT -> FROM -> WHERE -> GROUP BY -> HAVING ->ORDER BY

   SELECT vend_id, COUNT(*) AS num_prods
   FROM Products
   WHERE prod_price >= 4
   GROUP BY vend_id 
   HAVING COUNT(*) >= 2;
   ORDER BY vend_id

使用子查询

子查询

子查询过滤

从内向外处理

   SELECT cust_name, cust_contact
   FROM Customers
   WHERE cust_id IN (SELECT cust_id 
                     FROM Orders 
                     WHERE order_num IN (SELECT order_num 
                                         FROM ORDERItems 
                                         WHERE prod_id = 'RGAN01'));
   

作为计算字段

    SELECT cust_name, cust_state,
           (SELECT COUNT(*)
            FROM Orders 
            WHERE Orders.cust_id = Customers.cust_id) AS orders 
    FROM Customers 
    ORDER BY cust_name;

联结表

联结

创建联结

    SELECT vend_name, prod_name, prod_price
    FROM Vendors, Products
    WHERE Vendors.vend_id = Products.vend_id;

笛卡尔积又叫叉联结，没有联结条件，直接将两个表相乘

内联结

    SELECT vend_name, prod_name, prod_price 
    FROM Vendors INNER JOIN Products 
    ON Vendors.vend_id = Products.vend_id;

内联结又叫等值联结，基于两个表的相等测试

联结多个表

    SELECT prod_name, vend_name, prod_price, quantity
    FROM OrderItems, Products, Vendors 
    WHERE Products.vend_id = Vendors.vend_id 
    AND OrderItems.prod_id = Products.prod_id 
    AND order_num = 20007;

高级联结

表别名

好处是可以缩短语句，允许在一个SQL语句使用多次同一张表

    SELECT cust_name, cust_contact 
    FROM Customers AS C, Orders AS O, OrderItems AS OI 
    WHERE C.cust_id = O.cust_id 
    AND O.order_num = OI.order_num 
    AND OI.prod_id = 'RGAN01';

自联结

    SELECT C2.cust_id, C2.cust_name, C2.cust_contact 
    FROM Customers AS C1, Customers AS C2
    WHERE C2.cust_name = C1.cust_name 
    AND C1.cust_contact = 'Jim Jones';

DBMS处理联结远远比子查询快得多

自然联结

排除多次出现，每一列只返回一次

外联结

    SELECT Customers.cust_id, Orders.order_num 
    FROM Customers LEFT OUTER JOIN Orders 
    ON Customers.cust_id = Orders.cust_id;

带聚集函数的联结

    SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_order
    FROM Customers INNER JOIN Orders 
    ON Customers.cust_id = Orders.cust_id 
    GROUP BY Customers.cust_id;

组合查询

UNION

使用UNION

给出每条SELECT语句，之间加上UNION

    SELECT cust_name, cust_contact, cust_email
    FROM Customers 
    WHERE cust_state IN ('IL', 'IN', 'MI')
    UNION 
    SELECT cust_name, cust_contact, cust_email 
    FROM Customers 
    WHERE cust_name = 'Fun4All';

规则

每个查询必须包含相同列，表达式或聚集函数，数据类型需兼容

包含重复行

    SELECT cust_name, cust_contact, cust_email
    FROM Customers 
    WHERE cust_state IN ('IL', 'IN', 'MI')
    UNION ALL
    SELECT cust_name, cust_contact, cust_email 
    FROM Customers 
    WHERE cust_name = 'Fun4All';