Readme

Basic:
SELECT FROM /   WHERE / LIKE / * % / IN / OR / AND / NOT/ LIMIT
BETWEEN AND

DATA JOIN:
JOIN ON /LEFT JOIN ON
FULL JOIN
Self JOINs
UNION /UNION ALL  UNION will drop the duplicate rows but UNION can append all the rows UNION ALL is used more often


Operator	Example	                                                                    Result
+	        date '2001-09-28' + integer '7'	                                            date '2001-10-05'
+	        date '2001-09-28' + interval '1 hour'	                                      timestamp '2001-09-28 01:00:00'
+	        date '2001-09-28' + time '03:00'	                                          timestamp '2001-09-28 03:00:00'
+	        interval '1 day' + interval '1 hour'	                                      interval '1 day 01:00:00'
+	        timestamp '2001-09-28 01:00' + interval '23 hours'	                        timestamp '2001-09-29 00:00:00'
+	        time '01:00' + interval '3 hours'                                         	time '04:00:00'




DATA AGGREGATION:
NULL / ORDER BY / MAX / MIN / SUM / AVG / GROUP BY / HAVING / COUNT/ DISTINCT
DATE_TRUNC
DATE_PART
CASE WHEN THEN ELSE END AS


SUBQUERY: 
记得可以用1，2等数字来代替 
SELECT order_id, total_spending  FROM xxx ORDER BY 1
WITH table AS


DATA CLEANING:
LEFT,RIGHT,SUBTRING(text,begin,length),LENGTH,STRPOS(text,arg) / POSITION(arg IN text)
CONCAT 与 ||相同
CAST 与 :: 相同，定义数据类型
COALESCE() 补全空白值


WINDOW FUNCTION:
OVER,ALIASE,LAG/LEAD,NTILE,RANK(),DENSE_RANK(),ROW_NUMBER(),PARTITION BY

LIMIT cannot speed up when there is an aggregation. 

%%%%%%%%%%read the columns info%%%%%%%%%%%%%%
-- Pull column_name & data_type from the columns table
SELECT 
	column_name,
  data_type
FROM information_schema.columns
-- Filter for the table 'country_stats'
WHERE table_name = 'table';


%%%% String functions %%%%%%%%%%%%%%%%%

The LOWER(fieldName) function changes the case of all characters in fieldName to lower case.
The INITCAP(fieldName) function changes the case of all characters in fieldName to proper case.
The LEFT(fieldName,N) function returns the left N characters of the string fieldName.
The SUBSTRING(fieldName from S for N) returns N characters starting from position S of the string fieldName. Note that both from S and for N are optional.
REPLACE(fieldName, 'searchFor', 'replaceWith')
TRIM([characters FROM ]string) 
"characters FROM "  Optional,  Specific characters to remove.  
"string" Required, The string to remove spaces or characters from