【Day 12】MySQL Basics

Update（09/28）: 已完成 section 4,5

晚上刚回台北ＱＱ
这个笔记还蛮个人的，对别人来说没太大参考价值
先看到 Section 3，明天先补上 4 5
照原定计画看把 MySQL Basics 看过去，
虽然本来就会一些，但还是有收获。

本篇包含 5 sections：

1. Querying data
2. Sorting data
3. Filtering data
4. Joining tables
5. Grouping data

evalute order

1. Quering data

select

• 不一定要接 FROM clause
  select now()
select 1+1
• The dual table (dummy)

具体什麽时候会用到呢

2. Sorting data

order by

• 可以依照多个 column 排，要升降幂可以分别 specify
  order by col1 desc, col2 asc
• 配合 field function，可以指定一个 list，并让结果照着此 list 的顺序排
  field(要找的string, str1, str2, str3...): 例如 field('a', 'a', 'b') 回 1，field('b', 'a', 'b') 回 2

SELECT 
    orderNumber, status
FROM
    orders
ORDER BY FIELD(status,
        'In Process',
        'On Hold',
        'Cancelled',
        'Resolved',
        'Disputed',
        'Shipped');

• null < non-null
  asc 时会出现在最前面

3. Filtering data

where

• 过滤用，配合 and or, like, in, between, comparison operators(<>=) 等使用

select distinct

• 去除重复的 rows

and、or、in、not in

• mysql 中 null 不是 0
• 注意 与 null 的比较

为什麽要能够与 null 比较？背後的语意是什麽？

between

• 日期比较时要 cast

SELECT 
   orderNumber,
   requiredDate,
   status
FROM 
   orders
WHERE 
   requireddate BETWEEN 
     CAST('2003-01-01' AS DATE) AND 
     CAST('2003-01-31' AS DATE);

like

• %0或多，_1
• ESCAPE clause 来指定逃脱字元

选 含有 _20 的 records
SELECT 
    productCode, 
    productName
FROM
    products
WHERE
    productCode LIKE '%$_20%' ESCAPE '$';

limit: 限制返回的结果数

• LIMIT [offset,] row_count; offset 由 0 开始，包含。
• 找到前n大 / 前n小 / 第n大 的结果（配合 ORDER BY）
• pagination

IS NULL

• 有一些特别的 features:

1. If a DATE or DATETIME column has a NOT NULL constraint and contains a special date '0000-00-00', you can use the IS NULL operator to find such rows.
2. Influence of @@sql_auto_is_null variable

4. Joining tables

从不同表拿 Column，通常两张表之间有 foreign keys 关联

table / column alias

table aliases: from table as t

• 当使用 join 时，如果两张表有相同的 column，就需要 table alias 然後指定 t1.column 和 t2.column，否则会有 ambiguous error

column aliases: select column as c from table

• where clause 不能用 column aliases
  依据前面提到的 evaluation order 可以知道 where 比 select 早执行。

inner join

• 交集
• on t1.column = t2.column 相等於 using (column)，括号不能省略

不懂括号的意义？

left / right join

• 一定有左 / 右表所有的 records，如果在右 / 左表找不到 on 条件的 record 则填 Null
• 注意 WHERE clause 和 ON clause
  例如
  WHERE ordernum=123
  where 是 join 完的结果再过滤留下 ordernum = 123 的结果
  FROM orders o LEFT JOIN orderDetails d ON o.ordernum = d.ordernum and o.ordernum=123
  on 则是在 join 时，如果发现 o.ordernum不是123，就不做 leftjoin，也就是右表的栏位会被填上 NULL

cross join

• Cartesian product
• 没有 on 或 using clause

实际应用还不是很有感觉

self join

• join 同张表时，要注意 join condition 要有 c1.column > c2.column 之类的东西，以除去顺序不同但一样的 record。

5. Grouping data

group by

• 把很多 records 合并成一条
• 通常与 aggregate functions 使用
  aggregate functions 就是从一堆 rows 算出一个值
• 群组排序：GROUP BY cloumn DESC/ASC

having

• having 是过滤群组，where则是对个别records(紧接在table之後）

having 或是 where 可以达到类似的效果的时候，用哪个比较快呢？

rollup

• 除了得到每个分组的结果（subtotals），还想要的总和的时候（grand totals)

SELECT 
    select_list
FROM 
    table_name
GROUP BY
    c1, c2, c3 WITH ROLLUP;

• group by 多个 column 并 rollup 时，hierarchy 是 c1 > c2
  例如 group by c1, c2 with rollup grouping sets如下
  (c1, c2) ：这些subgroups（下图无框部分）
  (c1) ：对於c1的加总（下图红框）
  () 只会有一个就是全部的总和（下图橘框）

• grouping() function
  如果总和没有栏位表示，就会显示 null
  透过 if() + grouping() 可以把 null 值换成有意义的名字

先知道，以後有需要再细读

SELECT 
    IF(GROUPING(orderYear),
        'All Years',
        orderYear) orderYear,
    IF(GROUPING(productLine),
        'All Product Lines',
        productLine) productLine,
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    orderYear , 
    productline 
WITH ROLLUP;