1. NULL use details
Often someone will ask, what is NULL? As the name suggests, NULL is empty, Oracle, and other databases, the length of the column containing null values is zero. Oracle allows any field of data types to be empty, except for both cases:
1. Define this column as the primary keyfield (PRIMARY Key);
2. Define the fields of this column have explicitly add NOT of the NULL limit condition.
1.1. Specific description:
1. Isometrical no value, is unknown;
2, NULL and 0, empty strings, spaces are different;
3, add, minus, multiply, divided operations for null values, and the result is still empty;
4, NULL processing uses NVL functions;
5, inquiry, use keywords with "is null" and "is not null";
6, null value cannot be indexed, so some eligible data may not be found in queries, and more than one in count (*), then use NVL (column name, 0) to check;
7. Sort is larger than other data (index default is descending order, small → big), so NULL value is always in the end.
1.2. Usage method example:
SQL> SELECT 1 from dual where null = NULL;
No record
SQL> SELECT 1 from dual where null = '';
No record
SQL> SELECT 1 from dual where '' = '';
No record
SQL> SELECT 1 from dual where null is null;
1
---------
1
SQL> SELECT 1 from Dual WHERE NVL (NULL, 0) = NVL (NULL, 0);
1
---------
1
- Do plus, minus, multiply, divided by empty value, and the result is still empty.
SQL> SELECT 1 NULL from DUAL;
SQL> SELECT 1-NULL from DUAL
SQL> SELECT 1 * NULL from DUAL;
SQL> SELECT 1 / NULL from DUAL
Query a record.
1.3. Set some listed as null value
Update table1 set col1 = null where col1 is not null;
Skilled using Oracle's null usage, familiar with its conventions to ensure that the results Isolated are OK.
2. DUAL pseudo column
Meaning explanation:
Dual is an actual existing table in Oracle, any user can read, often in the SELECT statement that does not have a target table.
2.1. Instructions:
- View current connection users
SQL> SELECT User from Dual;
User
------------------------------
SYSTEM
- View the current date, time
SQL> SELECT SYSDATE from DUAL;
Sysdate
------------
18-April -03
SQL> SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24: MI: SS') from Dual;
TO_CHAR (sysdate, 'yy
-------------------
2003-04-18 22:37:56
- as a calculator
SQL> SELECT 1 2 from Dual; 1 2
------------
3
- View the serial value
SQL> CREATE SEQUENCE AAA INCREMENT BY 1 Start with 1;
SQL> SELECT AAA.NEXTVAL from DUAL
NextVal
------------
1
SQL> SELECT AAA.CURRVAL from DUAL
CURRVAL
------------
1
