MySQL语言总结--2

第12章：汇总数据

• 12.1 聚集函数

SQL 聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列的值之和

12.1.1 AVG()函数: SELECT AVG(prod_price) AS avg_price FROM products;

12.1.2 COUNT()函数: SELECT COUNT(*) AS num_cust FROM customers;

12.1.3 MAX()函数： SELECT MAX(prod_price) AS max_price FROM products;

12.1.4 MIN() 函数： SELECT MIN(prod_price) AS min_price FROM products;

12.1.5 SUM() 函数： SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

• 12.2 聚集不同值。DISTINCT / 只可用于COUNT() 不能用于COUNT(*)

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;

• 12.3 组合聚集函数

  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;

第13章：分组数字

• 13.2 创建分组 GROUP BY 子句创立

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

• 13.3 过滤分组 HAVING /HAVING 支持所有的WHERE操作符

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

• 13.4 分组和排序

  SELECT order_num, SUM(quantityitem_price) AS ordertotal
  FROM orderitems
  GROUP BY order_num
  HAVING SUM(quantityitem_price) >= 50
  ORDER BY ordertotal;

• 13.5 SELECT子句排序
  SELECT子句及其顺序

子句	说明	是否必须使用
SELECT	要返回的列和表达式	是
FROM	从中检索数据表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

第14章: 使用子查询

• 14.2 利用子查询进行过滤

  SELECT order_num FROM orderitems WHERE prod_id = ‘TNT2’;

  SELECT cust_id FROM orders WHERE order_num IN (20005,20007);

  – 创建子查询
  SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = ‘TNT2’);
  SELECT order_num FROM orderitems WHERE prod_id = ‘TNT2’;

  SELECT cust_id FROM orders WHERE order_num IN(20005,20007);

  SELECT cust_name, cust_concat FROM customers WHERE cust_id IN(10001,10004);

  SELECT cust_name, cust_concat FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE
  order_num IN (SELECT order_num FROM orderitems WHERE prod_id = ‘TNT2’));

• 14.3 作为计算字段使用子查询

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

第15章:连接表

• 15.1 联结

  • 15.1.1 关系表
    外键 : 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

• 15.2 创建联结(等值联结）

  SELECT vend_name, prod_name, prod_price
  FROM vendors, products
  WHERE vendors.vend_id = products.vend_id
  ORDER BY vend_name, prod_name;

  • 15.2.2 内部联结

  SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

  • 15.2.3 联结多个表

  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 = 20005;

  SELECT cust_name, cust_contact
  FROM customers, orders, orderitems
  WHERE customers.cust_id = orders.cust_id
  AND orderitems.order_num = orders.order_num
  AND prod_id = ‘TNT2’;

第16章:创建高级联结

• 使用表别名

  • SELECT Concat(RTrim(vend_name), ’ (’, RTrim(vend_country), ‘)’) AS vend_title FROM vendors ORDER BY vend_name;

  • SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS oi
    WHERE c.cust_id = o.cust_id
    AND oi.order_num = o.order_num
    AND prod_id = ‘TNT2’;

• 16.2 使用不同类型的联结

  • 自联结

    SELECT prod_id, prod_name FROM products
    WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = ‘DTNTR’);

    SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2
    WHERE p1.vend_id = p2.vend_id
    AND p2.prod_id = ‘DTNTR’;

  • 自然联结

    SELECT c.*, o.order_num, o.order_date,
    oi.prod_id, oi.quantity, oi.item_price
    FROM customers As c, orders AS o, orderitems AS oi
    WHERE c.cust_id = o.cust_id
    AND oi.order_num = o.order_num
    AND prod_id = ‘FB’;

  • 外部联结

    SELECT customers.cust_id, orders.order_num
    FROM customers LEFT OUTER JOIN orders
    ON customers.cust_id = orders.cust_id;

    SELECT customers.cust_id, orders.order_num
    FROM customers RIGHT OUTER JOIN orders
    ON orders.cust_id = customers.cust_id;

• 16.3 使用带聚集函数的联结

  SELECT customers.cust_name,
  customers.cust_id,
  COUNT(orders.order_num) AS num_ord
  FROM customers INNER JOIN orders
  ON customers.cust_id = orders.cust_id
  GROUP BY customers.cust_id;

  SELECT customers.cust_name,
  customers.cust_id,
  COUNT(orders.order_num) AS num_ord
  FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id
  GROUP BY customers.cust_id;

第17章：组合查询

• 17.2 创建组合查询，利用UNION.

  • 17.2.1 使用UNION

  组合以下两个查询语句。

  SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5;

  SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002);

  利用UNION语句组合以上两个查询语句

  SELECT vend_id, prod_id, prod_price
  FROM products
  WHERE prod_price <= 5
  UNION
  SELECT vend_id, price_id, prod_price
  FROM products
  WHERE vend_id IN (1001,1002);

  多个WHERE的实现方式

  SELECT vend_id, prod_id, prod_price
  FROM products
  WHERE prod_price <= 5
  OR vend_id in (1001,1002);

  • 17.2.3 包含或取消重复的行

  SELECT vend_id, prod_id, prod_price
  FROM products
  WHERE prod_price <= 5
  UNION ALL
  SELECT vend_id, prod_id, prod_price
  FROM products
  WHERE vend_id IN (1001, 1002);

  • 17.2.4 对组合查询的结果排序

  SELECT vend_id, prod_id, prod_price
  FROM products
  WHERE prod_price <= 5
  UNION ALL
  SELECT vend_id, prod_id, prod_price
  FROM products
  WHERE vend_id IN (1001, 1002)
  ORDER BY vend_id, prod_price;

第18章：全文本搜索

• 18.2 使用全文本搜索-- FULLTEXT子句

  • 18.2.1 启用全文本搜索支持

  CREATE TABLE productnotes(
  note_id note NOT NULL AUTO_INCREMENT,
  prod_id char(10) NOT NULL,
  note_date datetime NOT NULL,
  note_text text NULL,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
  )ENGINE = MyISAM;

  • 18.2.2 进行全文本搜索

  使用Match() 和 Against() 执行全文本搜索，Match()指定被搜索的列，Against() 指定要搜索表达式。
  SELECT note_text
  FROM productnotes
  WHERE Match(note_text) Against(‘rabbit’);

  使用LIKE子句完成以上功能。
  SELECT note_text FROM productnotes WHERE note_text LIKE ‘%rabbit%’;

  rank 返回一个等级值
  SELECT note_text,
  Match(note_text) Against(‘rabbit’) AS rank
  FROM productnotes;

  • 18.2.3 使用查询拓展
    – 简单的全文本搜索

  SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘anvils’);

  – 查询拓展

  SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘anvils’ WITH QUERY EXPANSION);

  • 18.2.4 布尔文本搜索

  匹配包含heavy但不包含以rope开始的行：
  SELECT note_text FROM productnotes Match(note_text) Against(‘heavy -rope*’ IN BOOLEAN MODE);