SQL 必知必会--notes

1.按列位置排序
   

SELECT prod_id, prod_price,prod_name FROM products ORDER BY 2, 3;
    --2 refer to prod_price--
    --3 refer to prod_name--

2.BETWEEN 包含边界值
   

SELECT prod_price, prod_name FROM products WHERE prod_price BETWEEN 5 AND 10;
    --包含5和10--

3.AND 优先于 or （不要使用默认优先级，始终使用括号）
   

SELECT prod_name, prod_price, vend_id FROM products WHERE vend_id = 'DLL01' or vend_id = 'BRS01' AND prod_price > 10
    --相当于--
SELECT prod_name, prod_price, vend_id FROM products WHERE vend_id = 'DLL01' or (vend_id = 'BRS01' AND prod_price > 10)

4.通配符'%'匹配0个,1个和多个字符， '_'匹配1个字符

5.TRIM 去除两边的空格， LTRIM 去除左边的空格， RTRIM 去除右边的空格--TODO:补充所有的文本操作函数，见8.2.1

6.AVG(columA) , MAX(columnA), MIN(columnA), SUM(columnA), COUNT(columnA) 忽略 NULL 值， COUNT(*)不忽略

7.LEFT OUTER JOIN 选择左边表所有的行 *=

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

------------------------------
--cust_id      order_num
--"1000000001";20009
--"1000000001";20005
--"1000000002";
--"1000000003";20006
--"1000000004";20007
--"1000000005";20008--
------------------------------
  RIGHT OUTER JOIN 选择右边表所有的行 =*

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

------------------------------
--cust_id      order_num
--"1000000001";20005
--"1000000003";20006
--"1000000004";20007
--"1000000005";20008
--"1000000001";20009
------------------------------