Basic knowledge of Oracle 1

SQL (Structured Query Language): Structured Query Language

The specifications for its use are:

Keywords to capitalize, other lowercase

When the WHERE clause is written, each condition occupies one line, and when the statement is started on another line, it starts with a reserved word or connector, joins the aligns, and uses the alias of the table to refer to the column when it joins.

Strings and dates are expanded with single quotes because the string and date are case-sensitive, and the default date format is ' Dd-mon-yy '

Classification of SQL language: (1) Data Query Language: SELECT (2) DML (date manipulation language): Data Manipulation Language: Insert,update,delete (3) DDL (date difinition Language): Data definition Language: Create,alter,drop,rename,truncate (4) TCl (Transaction Control Language): Transaction control Language: Commit,rollback, SavePoint (5) DCL (date control Language): Data controls Language: Grant,revoke.

Use of the SELECT command: SELECT * FROM table name where condition

or select list from table name where condition

That is: Select identifies what column, from what table, where is followed by the condition.

Arithmetic expressions: Can be expressed in subtraction, with the same precedence as in mathematics, with the use of:

Define the alias of the column: You can change the header header of the column, if you use special characters, or case sensitive, or if there are spaces, you must add double quotation marks;

For example: Select Ename as "Name", Sal "SALARY" from EMP Note when defining the alias of a column, add as can.

Column-to-column connection with double vertical bar (| | ), the resulting result is a string. Such as

DISTINCT: Can be used to delete duplicate rows. For example: Distinct is written in front of the duplicate line to be deleted.

Common comparison operators: have <, <=, =, >=, <>,between and (between two values or lower and higher), in (matches the listed value (can be a number, can be a character string, can be a value, Also a value greater than or equal to two) like (contains literal characters or numbers, the usage is usually like ' A% ' or not-like ") is null (null value) is NOT NULL: null

Common logical operators: and, OR, not

20 or 30 Expression: in (20,30) or keyword =20 or keyword =30, not ... not in ()

Precedence: parentheses First, then */+-finally not and OR

Order BY Usage: Use Orde Rby is to sort records, ASC: Ascending, default is ascending,

Desc: Descending

Oracle's single set of functions: Like Upper (' x '): Converts characters to uppercase, lower (' x '): Convert characters to lowercase. NVL (X,value): Indicates that if x is empty, the value of output value, if x is not NULL, output the value of X. Trunc: Intercept, not after the decimal point, only take the integer before the decimal point. Dual: is a virtual table.

Note that the final format to be converted is in single quotation marks.

To_char: Converts the content to a string, To_number: Converts the character to a number, To_date: Converts the character to a date.

Common date formats are: YYYY/MM/DD or YYYY-MM-DD

Data type conversions: To_char (date, ' FMT ') such as: To_char (HireDate, ' FMDD month yyyy ') must be in single

Enclose the date in quotation marks and use commas to separate the dates. To_number: Converts a character into a number. To-date: Converts a character to a date. such as: To_number (' 99f ', ' xxx ')

NVL (ARG1,ARG2): Determines the value of arg1, if empty, returns ARG2, otherwise returns ARG1

Basic knowledge of Oracle 1