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
------------------------------
页:
[1]