My Oracle Career (2) basic content

Organize some sql&plsql statements

as Alias

Select X as Y from ...//use Y to display the X column name

DISTINCT to remove duplicate rows

Select DISTINCT x from ...//table to filter x rows of data that are not duplicated

in range filtering

Select X from Y where z in (A, B)//select Z==a | | X column data for z = = b

like fuzzy query

Select X from Y where z like '%_a_% '//z matches any number of arbitrary characters + single character +a+ single character + any number of characters

Order by sort

Select X from y order by Z//Sort by Z ascending order by z desc Descending

Group by group

Select SUM (x) from Z where....group by a, b//After grouping by A/b x and (for example, ask for a class all girls age and so on)

having Judgment after GROUP by

TRANSLATE keyword substitution

TRANSLATE (A,A1,A2)//A is the content, A1 is the keyword, A2 is the replacement content and the A1 in A is replaced by A2

SUBSTR truncation Field

SUBSTR (A,A1,A2)//A to intercept A2 length from A1

INSTR Find

INSTR (A1,A2,B1,B2)///A1 looking for A2,B1 is from the beginning of the search (negative numbers to the left, positive to the right), B2 is the first occurrence, return position

SEQUENCE sequence//To table data automatic arranging, serial number is not duplicated

CREATE SEQUENCE A (table name)

MINVALUE 1//min 1

MAXVALUE 100//MAX 100

Start with 1//sort starting from 1

INCREMENT by 1//increments by 1 each time

NOCACHE//No cache

Nocycle//Non-cyclic

index; a column created to increase the efficiency of database queries

CREATE INDEX X on table_name (col_1,col_2,...)

synonym synonyms//In order to facilitate the different tables, different columns and so on in the same file of the uniform address and pay a name

CREATE synonym xxx for YYY//At this time xxx and YYY are xxx

DROP synonym XXX//revoke synonym XXX

My Oracle Career (2) basic content