创建计算字段

拼接
  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 BYWHERE语句之后,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';
01-07 12:16