Oracle Learning Summary

Set Linesize 120

Set PageSize 20

Column file_name format A8

V$nls_parameters a table in the data dictionary

Note about NULL:

1: Empty expression with null result

2: Determine whether a value equals null is used is does not equal with IS. Remember (null!=null)

3: If the collection contains null values, do not use not in but can use in

SQL Optimizations:

After Oracle9, * and column name efficiency, before, with * column name efficiency is higher, because Oracle to resolve *, so the efficiency is relatively slow

The use of like, a _ represents matching all, multiple _ represents matching multiple characters, the ' s% ' beginning with S, '%s ' ending with S, '%s% ' containing s

Transfer character: Escape from the top with an escape character

The name of the query containing _ this

1 SELECT *
2 from EMP
* * where ename like '%\_% ' escape ' \ '

When using and, should try to be false value on the left, when using or should try to be true truth on the left

Sort:

If the value to be sorted contains a null value, you should make it in the following select * from emp ORDER BY comm Desc nulls last;

Modify the format of the system date

SELECT * from V$bls_parameters;

Alter session set nls_date_format= ' Yyyy-mm-dd '; the original system date format is ' DD-MON-RR '

DISTINCT remove duplicate records (effect all columns behind it)

Select DISTINCT deptno from EMP;

Oracle Learning Summary