SQL syntax

sql_note

1. GROUP_CONCAT() 
   SELECT GROUP_CONCAT( <cols> [ SEPARATOR <"char"> ] ) FROM <table>; 
   SELECT GROUP_CONCAT( <cols> [ SEPARATOR <"char"> ] [ ORDER BY <cols>] ) FROM <table>; 


2. GROUP BY vs ORDER BY 
   **ORDER BY** alters the order in which items are returned. 
   **GROUP BY** is used to organize results according to the structure of the data returned. 
   The results are indexed alphabtically. 
   ```GROUP BY``` is defined as a way of aggregating records by the specified columns which allow you to perform aggregation functions on non-grouped columns
   In other words, the GROUP BY clause’s purpose is to summarize unique combinations of columns values
   That means SQL first makes groups from the same values of certain column and returns one row representing the group.
   ```GROUP BY``` is the SQL way to analyze similar data. There is no real use of ```GROUP BY``` without aggregation functions.
   e.g. 
   ```
      $ SELECT department, SUM(salary) FROM employee GROUP BY department
   ``` 
   [REFERNCE](https://www.tutorialspoint.com/sql/sql-group-by.htm) 
   
   
3. WHERE vs HAVING 
   **WHERE**敘述是將資料表先執行WHERE篩選後在進行SELECT. 
   **HAVEING**敘述是將SELECT出的欄位進行篩選, 
   因此WHERE會比HAVING更有效率, 
   也因此HAVING的條件所使用的欄位必須是要出現在SELECT中的欄位, 
   若使用Aggregation function則必須取別名才能使用該欄位經函數的結果 
    
  
4. JOIN <br> 
   * [LEFT|RIGHT|FULL] JOIN == [LEFT|RIGHT|FULL] OUTER JOIN == union in Math. 
   * INNER JOIN == intersection in Math. 
   * NATURAL JOIN has very close concept with INNER JOIN. 
   * NATURAL LEFT JOIN == LEFT JOIN 
   * NATURAL RIGHT JOIN == RIGHT JOIN 


5. wildcard ( used with LIKE )
  ```%```(百分比符號)：代表零個、一個、或數個字母。```_```(底線)：代表剛好一個字母。 <br> 
  * "%x" : a values that end with letter x 
  * "x%" : a values that begin with letter x 
  * "x%z%": a values that begin with letter x and contains at least one instance of the letter l 
  * "x_z%" : a values that begin with letter x and whose third letter is z 
  * "_____" : a five letter value 
  * "__%" : a values with least two characters 
  * [REFERENCE](https://www.1keydata.com/tw/sql/sql-wildcard.html) 

6. DATE and TIME data 
   * DATE, TIME, DATETIME, TIMESTAMP, YEAR,
   * TIMESTAMP用在自動生成, 會將資料以UTC時間存入, 取出時會以Server時區顯示
   * DATETIME主要是單純存入時間格式
   * DATE, DATETIME都有: 合併(年四位or兩位), 分開(年四位or兩位), 與使用相同的分隔符號等格式輸入 
     DATE: YYYYMMDD, YYMMDD, YYYY-MM-DD, YY-MM-DD, YYYY@MM@DD, YY@MM@DD
     DATETIME: YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYY-MM-DD HH:MM:SS, YY-MM-DD HH:MM:SS, YYYY@MM@DD HH@MM@SS, YY@MM@DD HH@MM@SS
     
   * YEAR(2)兩位年, YEAR(4)可接受兩位年與四位年
   
7. STRING and NUMERIC 
  * CONCAT( str1, str2 ) 
  * SELECT SUBSTRING( value, position, length ); == SELECT MID( value, position, length );
    The value that is a type of string and position where starts at 1 not 0 are required and length is optional.
  * SELECT [SUBSTRING|MID]( value, -position ); // backward 
  * TRIM(); 
  * SELECT REPALCE( <base value>, <value to be replaced>, <replacement value> ); 
  * UPPER(), LOWER(): 
  * ROUND( <base value>, <numbers of position> ); // 
  * SELECT ROUND( 10000/3, 5 ); // --> 3333.33333, 四捨五入至第五位小數 
  * SELECT ROUND( 992, -3 ); // --> 1000, 
  * SELECT FORMAT( 10000/3, 5 ); // --> 3,333.33, 總共取五位數
  * SELECT INSTR( <base string>, <value to be found> ); 
  * SELECT LOCATE( <value to be found>, <base string> ); 
  * SELECT LOCATE( <value to be found>, <base string>, <specified position> ); // e.g. $ SELECT LOCATE( "C", "ABC#$@CDE", 4 ); --> 7 

8. AND, OR, IN, LIKE, ANY, ALL 
  * AND: the intersecation 
  * OR: the union 
  * IN: IN ( ... ), traverse in () 
  * LIKE: used by wildcard. 
  * ANY: returns TRUE if any of the subquery values meet the condition.
         e.g. SELECT ProductName 
                  FROM Products 
              WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
         The SQL statement returns TRUE and lists the product names if it finds ANY records in the OrderDetails table that quantity = 10. 
  * ALL: returns true if all of the subquery values meet the condition. 
         e.g. SELECT ProductName 
                  FROM Products 
              WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10); 
         The SQL statement returns TRUE and lists the product names if ALL the records in the OrderDetails table has quantity = 10 
         (so, this example will return FALSE, because not ALL records in the OrderDetails table has quantity = 10):
  
  
9. basic syntax <br> 
  * SELECT <COLs> FROM <TABLE> [WHERE clause] [GROUP BY clause] [ ORDER BY clause ]; 
  * SELECT <COLs> FROM <TABLE> [HAVING clause]; 
  * SELECT <COLs> FROM <TABEL> LIMIT arg1, arg2;  // 從arg1+1開始選取arg2個資料, e.g. LIMIT 2, 3 means 從第三個開始選取三個資料
  * INSERT INTO <TABLE>( <COLs> ) VALUES( <VALUEs> )...; 
  * INSERT INTO <TABLEa>( <COLs> ) SELECT <TABLEb>.<COLs> FROM <TABLEb> [Condition];
  * ALTER TABLE <TABLE> MODIFY <COL to be modified>; 
  * ALTER TABLE <TABLE> ADD COLUMN <COL spec>; 
  * UPDATE <TABLE> SET <COL>=<VALUE> WHERE clause; 
  * DELETE FROM <TABLE> WHERE clause; 
  * SELECT <TABLEa>.<COLs> FROM <TABLEa> <LEFT|RIGHT|FULL> [OUTER] JOIN <TABLEb> ON <TABLEa>.<COL>=<TABLEb>.<COL>; 
  * SELECT <TABLEa>.<COLs> FROM <TABLEa> INNER JOIN <TABLEb> ON <TABLEa>.<COL>=<TABLEb>.<COL>; 
  * SELECT <TABLEa>.<COLs>, <TABLEb>.<COLs> FROM <TABLEa> CROSS JOIN <TABLEb>; 
  * SELECT <TABLEa>.<COLs> FROM <TABLEa> WHERE <COL> = ( SELECT <TABLEb>.<COL> FROM <TABLEb> ... ); 
 

SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY subject IN ('Physics', 'Chemistry'), subject, winner;
-- subject IN ('Physics', 'Chemistry')可以被当作一个值为0或1的数字

questions: https://sqlzoo.net/wiki/SELECT_basics
answer: https://zhuanlan.zhihu.com/p/65607960

todo: https://sqlzoo.net/wiki/Nobel_Quiz