Shulou(Shulou.com)06/01 Report--

one。 Oracle string operation

String type

1. CHAR and VARCHAR2 types

Represents a string data type that is used to store string information in a table

CHAR stores fixed-length characters, that is, if you are not satisfied with filling in spaces (waste space and save time); VARCHAR2 stores variable-length characters and takes up as much as you can (waste time and save space)

Sort by the natural order of characters.

2. Storage coding of CHAR and VARCHAR2

The default unit is bytes, which can be specified as characters

-CHAR (10), equivalent to CHAR (10 BYTE)

-specified unit is character: CHAR (10 CHAR), 20 bytes

-VARCHAR2 (10), equivalent to VARCHAR2 (10 BYTE)

-specified unit is character: VARCHAR2 (10 CHAR), 20 bytes

-ZHS16GBK:2 bytes

-UTF-8:2-4 bytes

3. Maximum length of CHAR and VARCHAR2

The maximum value of CHAR is 2000 bytes and a maximum of 2000 English characters are saved.

The maximum value of VARCHAR2 is 4000 bytes

CHAR may not specify a length. The default is 1. VARCHAR2 must specify a length.

4. LONG and CLOB types

LONG:VARCHAR2 extended version, which stores variable length strings and up to 2GB string data

LONG has many restrictions: each table can have only one column of type LONG; it cannot be used as a primary key; it cannot be indexed; it cannot appear in query conditions.

CLOB: stores string data of fixed or variable length, up to 4GB

ORACLE recommends using CLOB instead of LONG type in development

String function

1. CONCAT and "| |"

CONCAT (char1, char2)

-returns the result of concatenating two strings. The two parameters char1,char2 are the two strings to be concatenated.

Equivalent operation: concatenate operator "| |"

If either char1 or char2 is NULL, it is equivalent to concatenating a space.

"| |" is the connection string in the database, which is equivalent to the "+" in java. Note that it is different from java "| |".

Eg:

In java: "hello" + "world" = = > "helloworld"

In DB: 'hello' | |' world' = > 'hello world'

In oracle: CONCAT ('hello','world') = >' hello world'

2. FROM DUAL (virtual table)

DUAL: virtual table, there is no such table, just to meet the syntax requirements of SELECT.

-We often use virtual tables to test the results of expressions.

-when to use virtual tables: when no fields in the table participate in the SELECT statement

-eg: suppose the contents stored in the name and sal fields in the table emp are name and salary, respectively.

SELECT name | |':'| | sal FROM emp

The result is: boss:5000

3. LENGTH

LENGTH (char): used to return the length of the string

If the character type is VARCHAR2, the actual length of the character is returned, and if the character type is CHAR, the length also includes trailing spaces.

Eg: SELECT name,LENGTH (name) FROM emp

The result is: boss 4 (name is CHAR)

4. UPPER,LOWER and INITCAP

Case conversion function, used to convert the case of characters

UPPER (char) is used to convert characters to uppercase

LOWER (char) is used to convert characters to lowercase

INITCAP (char) is used to capitalize the first character of each word in a string, lowercase other characters, and separate words with spaces and non-alphabetic characters.

If the parameter of an acquaintance is null, kernel returns null

5. TRIM,LTRIM,RTRIM

Function: truncate the substring

Grammatical form:

-TRIM (c2 FROM C1) truncates c2 from the front and rear of C1

-LTRIM (C1 [, c2]) truncates c2 from the left side of C1 (Left)

Remove spaces if there is no c2

Eg:SELECT TRIM ('e 'from' eeeeliteeee') FROM DUAL

Only a single character can precede from in the parameter.

If there is no from and the preceding characters, the whitespace is removed.

Eg:SELECT LTRIM ('eeeeliteeee','e') FROM DUAL

SELECT RTRIM ('eeeliteee','e') FROM DUAL

Do not specify the second parameter. The default is to remove whitespace.

6. LPAD, RPAD

Complement function, which is used to complete n bits with char2 at the left or right end of the string char1. Char2 can be repeated many times.

-LPAD (char1, n, char2) left complement function

-RPAD (char1, n, char2) right complement function

Use the left complement in the emp table to complete 20 bits of sal with $

Eg:SELECT name, LPAD (sal, 20,'$') as "salary" FROM emp

Function: it is required to display 20 characters. If the value of sal is less than the length, add several'$'to reach 20 characters.

Eg:SELECT RPAD ('aaaaAAAAA') FROM DUAL

The result is aaaaA.

7. SUBSTR

SUBSTR (char, [m [, n]])

-used to get the substring of the string, returning the char to take n characters starting with m bit

If n is not set, or if the length of n exceeds the length of char, it is taken to the end of the string

The first bit count of a string starts at 1

8. INSTR

INSTR (char1, char2 [, n [, m]])

Returns the position of the substring char2 in the original string char1

Parameters:

-search from the position of n, no n specified, search from the first character

-m is used to specify the m-th occurrence of the substring, if you do not specify a value of 1

-if the substring char2 is not found in char1, 0 is returned.

two。 Oracle numerical operation

Numerical type

1. NUMBER (P) represents an integer

Complete syntax: NUMBER (precision, scale)

-if scale is not set, the default value is 0, that is, NUMBER (p) represents an integer

-P represents the total number of digits, with a value of 1-38

Used to store data recorded in integers in a table, such as coding, age, times, etc.

2. NUMBER (P, S) represents a floating point number.

NUMBER (precision, scale)

-the maximum length of digits that precision:NUMBER can store (excluding the zeros on the left and right sides)

-scale: the maximum number length to the right of the decimal point (including left 0)

If s is specified but p is not specified, p defaults to 38

It is often used to store decimal data such as amounts, scores, etc.

Variant data type of NUMBER: the internal implementation is NUMBER, which can be understood as an alias for NUMBER for the purpose of compatibility with multiple databases and programming languages

-NUMERIC (p, s): completely mapped to NUMBER (p, s)

-DECIMAL (p, s) or DEC (p, s): completely mapped to NUMBER (p, s) type

-INTEGER or INT: fully mapped to type NUMBER (38)

-SMALLINT: fully mapped to NUMBER (38) type

-FLOAT (b): mapping to NUMBER type

-DOUBLE PRECISION: mapping to NUMBER type

-REAL: mapping to NUMBER type

Numerical function

1. ROUND

ROUND (n [, m]): for rounding

-the n in the parameter can be any number, referring to the number to be processed

-m must be an integer

If-m takes a positive number, it is rounded to the m place after the decimal point

If-m takes a value of 0, it is rounded to integer bits.

If-m is negative, it is rounded to m before the decimal point

-m default, default is 0

Eg:SELECT ROUND (45.678,-1) FROM DUAL;-50

2. TRUNC

TRUNC (n [, m]): for interception

The definitions of-n and m are the same as those of ROUND (n [, m]), except that the number n is processed functionally as intercepted.

Eg:SELECT TRUNC (45.678,-1) FROM DUAL;-40

3. MOD

MOD (m, n): returns the remainder of m divided by n

If-n is 0, m is returned directly.

4. CEIL and FLOOR

CEIL (n), FLOOR (n), one takes the minimum integer value greater than or equal to n, and the other takes the maximum integer value less than or equal to n.

Eg:SELECT CEIL (45.678) FROM DUAL;-46

SELECT FLOOR (45.678) FROM DUAL;-45

three。 Oracle date operation

Date Typ

1. DATE

The most commonly used date type in ORACLE, which is used to save dates and times

The date indicated by DATE can range from January 1, 4712 BC to December 31, 9999 AD.

The storage of the DATE type in the database is fixed at 7 bytes in the format:

-first byte: century + 100

-second byte: year

-third byte: month

-fourth byte: days

-Fifth byte: hour + 1

-sixth byte: min + 1

-Seventh byte: seconds + 1

2. TIMESTAMP

Date types commonly used in ORACLE

The database is stored in 7 or 11 bytes with precision of 0 and 7 bytes, which has the same function as DATE. If the precision is greater than 0, 11 bytes are used.

The format is:

-Byte 1-byte 7: same as DATE

-8-11 bytes: nanosecond, using 4 bytes of storage, internal operation type is integer

Date keyword

1. SYSDATE

It is essentially an internal function of ORACLE that returns the current system time, accurate to seconds.

The default display format is DD-MON-RR

2. SYSTIMESTAMP

Internal function that returns the current system date and time, accurate to milliseconds

Date conversion function

1. TO_DATE

TO_DATE (char [, fmt [, nlsparams]]): converts a string to a date type in a custom format

-char: the string to be converted

-fmt: format

-nlsparams: specifies the date language

-commonly used date formats are shown in the table

YY2-digit year YYYY4-digit year MM2-digit month MON simplified month MONTH full-spelled month DD2-digit day DY day of the week abbreviated DAY days of the week full-spelled HH2424 hourly HH1212 hourly MI display minutes

SS

Display seconds

2. TO_CHAR

Convert other types of books to character types

TO_CHAR (date [, fmt [, nlsparams]]): outputs a string of date type data date in fmt format. Nlsparams is used to specify the date language

Two dates can be subtracted, and the difference is the number of days between them.

Common function of date

1. LAST_DAY

LAST_DAY (date): returns the last day of the month where the date date is located

It is useful to calculate some business logic according to the natural month, or to arrange periodic activities at the end of the month.

Eg:SELECT LAST_DAY (SYSDATE) FROM DUAL;-30-September-17

SELECT LAST_DAY ('20-February-09') FROM DUAL;-28-February-09

2. ADD_MONTHS

ADD_MONTHS (date, I): returns the date date plus the date value after I month

-if I is a decimal, the integer will be intercepted before participating in the operation

-if I is negative, you get the date value minus I months

Eg: the 20th Anniversary of the Computing staff

SELECT name, ADD_MONTHS (hiredate, 20 * 12) as' 20th Anniversary 'FROM emp

3. MONTHS_BETWEEN

MONTHS_BETWEEN (date1, date2): calculates the number of months between date1 and date2 date values

The actual operation is date1-date2. If the date2 time is later than date1, you will get a negative value.

Unless the interval between the two dates is an integer month, you will get a result with decimal places.

-you can use FLOOR to get the whole month at this time.

Eg:SELECT name, FLOOR (MONTHS_BETWEEN (SYSDATE, hiredate)) FROM emp

4. NEXT_DAY

In the Chinese environment, directly use the form of "Wednesday", in the English environment, you need to use the English day of the week of "WEDNESDAY". Bit to avoid trouble, you can directly use 1-7 to express Sunday-Saturday

NEXT_DAY is not tomorrow.

SELECT NEXT_DAY (SYSDATE, 3) FROM DUAL;-the nearest Tuesday (excluding today)

5. LEAST, GREATEST

GREAGEST (expr1 [, expr2 [, expr3]]...)

LEAST (expr1 [, expr2 [, expr3]]...)

Also known as a comparison function, it can have multiple parameter values, and the return result is the largest or smallest value in the parameter list

Parameter types must be consistent

Before comparison, the second subsequent parameter in the parameter list is implicitly converted to the data type of the first parameter, so if it can be converted, continue the comparison, if it cannot be converted, an error will be reported.

6. EXTRACT

Eg:SELECT EXTRACT (HOUR FROM TIMESTAMP '2008-01-01 10 FROM DUAL

four。 Null operation

The meaning of NULL

An important concept in the database: NULL, or null value

Sometimes the values of some fields in the table, the data is unknown or does not exist temporarily, take the value NULL.

Any data type can have a value of NULL

Operation of NULL

1. Null values are inserted and updated to NULL only when non-null constraints are available

Query criteria: WHERE name IS NULL/ WHERE name IS NOT NULL

Any data added to NULL is NULL.

two。 Non-empty constraint

Non-null (NOT NULL) constraints are used to ensure that field values are not empty

By default, null values are allowed for any column

When a field is set with a non-null constraint, there must be a valid value in the field.

Null value function

1. NVL

NVL (expr1, expr2): converts NULL to a non-null value

-if expr1 is NULL, the value expr2,expr2 is the actual value

-expr1 and expr2 can be any data type, but the data types of the two parameters must be the same

2. NVL2

NVL2 (expr1, expr2, expr3): similar to NULL function, it converts NULL to actual value.

NVL2 is used to determine whether expr1 is NULL. If it is not NULL, return expr2. If it is NULL, return expr3.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.