Mastering Oracle SQL学习笔记.pdfVIP

Mastering Oracle SQL 学习笔记 一、第 2 章：Where子句 1 二、第 3 章：连接 2 三、第 4 章：分组操作： 9 四、第5 章：子查询(Subquery) 12 五、第 6 章：处理时间数据 20 六、第 7 章：集合运算(Set Operation): 30 七、第 8 章：Hierachical Querys(级联查询) 37 八、第 9 章 Decode和Case 45 九、第 13 章Advanced Analytic SQL(高级分析函数) ： 50 十、第 14 章 SQL Best Practise(SQL最佳实践) ： 55 一、第 2 章：Where 子句 *************************************************************************************** 1．Between…And 子句： When using BETWEEN, make sure the first value is the lowest of the two values provided. While BETWEEN 1 AND 10 and BETWEEN 10 AND 1 might seem logically equivalent, specifying the higher value first guarantees that your condition will always evaluate to FALSE 2．NULL 关键字： When working with NULL, the concept of equality does not apply; a column may be NULL, but it will never equal NULL. Oracle doesnt complain if you mistakenly use the equality operator when evaluating for NULL,The following query will parse and execute but will never return rows: SELECT order_nbr, cust_nbr, sale_price, order_dt FROM cust_order WHERE ship_dt = NULL; NVL substitutes a specified value for columns that are NULL, as in: SELECT fname, lname, manager_emp_id FROM employee WHERE NVL(manager_emp_id, -999) != 11; In this example, the value -999 is substituted for all NULL values, which, since -999 is never equal to 11, guarantees that all rows whose manager_emp_id column is NULL will be included in the result set. Thus, all employees whose manager_emp_id column is NULL or is not NULL and has a value other than 11 will be retrieved by the query. 3.如何充分利用 WHERE子句： here are a few tips to help you make the most of your WHERE clauses: A．Check your join conditions carefully. Make sure that each data set in the FROM clause is properly joined. Keep in mind that some joins require multiple conditions. See Chapter 3 for more information.