ORACLE Database SQL basic data type knowledge, oraclesql

ORACLE Database SQL basic data type knowledge, oraclesql

Basic knowledge

-Several tables that come with ORACLE

-EMP: SELECT * from emp;

-DEPT: SELECT * from dept;

-SALGRADE: SELECT * from salgrade;

-BONUS: SELECT * from bonus;

Basic Data Type

The following are all copied from PLSQL, and the code can be directly copied to PLSQL to run. More practices are required for learning. Run SQL directly.

-- 1. string type -- fixed length type: indicates that the input field value is smaller than the maximum length of the field. However, when data is stored, spaces are automatically filled to the right. Advantages: high Storage/query efficiency -- CHAR (size [BYTE | CHAR]), which is stored by BYTE by default. If it is specified to be stored by character, it is stored by character. -- Example: CHAR_OLD default, CHAR_NEW is specified as the character storage create table CHAR_TEST (CHAR_OLD CHAR (10), CHAR_NEW CHAR (10 CHAR); -- insert into CHAR_TEST (CHAR_OLD, CHAR_NEW) SELECT 'abcdefghj ', 'I want five words I want five words' FROM DUAL; COMMIT; SELECT * FROM CHAR_TEST; -- try again in turn? Insert into CHAR_TEST (CHAR_OLD, CHAR_NEW) SELECT 'I want five words I want five words', 'abcdefgij' FROM DUAL; -- the value of CHAR_OLD is too large (actual value: 31, the maximum value is 10), because in oracle, if the NLS_CHARACTERSET is AL32UTF8, a Chinese character occupies three bytes-variable length type: indicates that the input field value is smaller than the maximum length of this field, do not automatically fill in spaces to the right to the maximum length-advantage: save data block space-Representative: VARCHAR and VARCHAR2, there is no big difference between the two, VARCHAR2 is the unique data type of ORACLE, it is generally recommended to use VARCHAR2 -- Example: CHAR_OLD default, CHAR_NEW is specified as character storage -- 2, NUMBER type -- NUMBER (P, S): P precision, that is, length, S is the NUMBER of decimal places -- INTEGER: The subtype of NUMBER, which is equivalent to NUMBER (). It is used to store integers. If a decimal point is inserted, It is rounded to the nearest integer. For example, create table integer_test (integer_col INTEGER, number_col number (11.11); insert into integer_test (integer_col, number_col) select 11,11 from dual; insert into integer_test (integer_col, number_col) select 11.11, from dual; insert into integer_test (integer_col, number_col) select 11.51, 11.51 from dual; commit; select * from integer_test;

Result:

-- FLOAT: subtype of NUMBER, FLOAT type -- float (n): n indicates precision, but its length is not well controlled. We recommend using NUMBER or DOUBLEalter table integer_test add float_col FLOAT (7 ); insert into integer_test (integer_col, number_col, float_col) select 11.11, 11.11, 11.11 from dual; insert into integer_test (integer_col, number_col, float_col) select 11.51, 11.51, 11.51 from dual; select * from integer_test;

Result:

-- 3. DATE: The DATE data type can store DATE and time information -- TIMESTAMP: The TIMESTAMP can contain decimal seconds, the timestamp with decimal seconds can retain a maximum of 9 digits on the right of the decimal point -- a number can be added for the DATE type, which indicates the number of days, indicates the DATE of a certain number of days. For example, select sysdate + 30 as "DATE after 30 days" from dual; -- the DATE type can be reduced to indicate two time differences. For example, select trunc (sysdate-hiredate) /365, 2) as "Working Life" from emp; select to_char (sysdate-hiredate)/365, '2017. 99 ') as "Working Life" from emp; -- PS: trunc function, retain a few decimal places, but if it is 0 at last, it is omitted. If the format is required to be neat, to_char (char, '99. 99 ')

Result

-- 4. LOB type -- CLOB: used to store single-byte and multi-byte character data -- BLOB: used to store binary data -- 5. RAW and long raw types -- 1, LONG: it stores variable-length strings, up to 2 GB of character data (2 GB refers to 2 Gigabit bytes rather than 2 Gigabit characters)-same as VARCHAR2 or CHAR type, character Set conversion is required for text stored in the LONG type. We recommend that you use CLOB to replace the LONG type in ORACLE development. LONG columns are supported only to ensure backward compatibility. The CLOB type is much less restrictive than the LONG type. -- The specific restrictions officially given are as follows -- only one column in a table can be of the LONG type. -- A LONG column cannot be defined as a primary key or a unique constraint. -- an index cannot be created. -- a regular expression cannot be specified for LONG data. -- Parameters of the LONG data type cannot be accepted in a function or stored procedure. -- LONG Columns cannot appear in the WHERE clause or integrity constraints (except for NULL and not null constraints) -- 2. RAW: used to store binary or character-type data, variable-length binary data type-6. ROWID and UROWID-this is not very understandable