Oracle 常用語句檔案（一）

Basic Select Statement

SELECT *|{[DISTINCT] column|expression [alias],….} FROM table;

Using Arithmetic Operators

SELECT 12* (column +10)+200  FROM table;

Using the Concatenation Operator

SELECT columnA||columnB FROM table;

Using Literal Character Strings

SELECT columnA|| ‘plus’ || columnB  as “ACombineB” FROM table;

Eliminating Duplicate Rows

SELECT DISTINCT column_name FROM table;

Displaying Table Structure

DESC[RIBE] tablename;

 

Restricting and Sorting Data

Limiting the rows selected

SELECT *|{[DISTINCT] column|expression [alias],….} FROM table [WHERE condition(s)] [ORDER BY {column,expr,alias} [ASC|DESC] ];

Using comparison conditions

SELECT * FROM table WHERE columnA <> ’sample’;

Comparison Conditions: =,>,>=,<,<=,<>,BETWEEN…AND…,IN(set),LIKE,IS NULL

Using the LIKE condition

using the LINK condition to perform wildcard searches of valid search string values.

Search conditions can contain either literal characters or numbers:

%denotes zero or many characters.

_denotes one character.

You can use the ESCAPE identifier to search for the actual % and _ symbols.

SELECT column_name from table where column_name LIKE ‘%S\_%’  ESCAPE ‘\';

Using the NULL conditions

SELECT * FROM table WHERE column_name  IS NULL;

Order by Clause

Sort rows  with order by clause  [ASC:ascending order,default; DESC:descending order]

The ORDERY BY clause comes last in the select statement,you can sort by a column that is not in the select list and can sort by multiple columns.

 

Single-Row Functions

Character functions

Character-manipulation functions 

CONCAT,SUBSTR,LENGTH,INSTR,LPAD|RPAD,TRIM,REPLACE

Case-manipulation functions

LOWER,UPPER,INITCAP

Note:

INSERT(column|expression, ‘string”,[,m],[n])

Returns the numberic position of a named string.Optionally,you can provide a position m to start searching,and the occurrence n of the string.m and n default to 1,meaning start the search at the beginning of the seach and report the first occurrence.

LPAD(column|expression,n,’string’)   RPAD(collum|expression,n,’string’)

Pads the character value right-justified/left-justified to a total width of n character positions.

SUBSTR(column|expression,m,[,n])

Returns specified characters from character values starting at character position m, n characters long(If m is negative,the count starts from the end of the character value. If n is omitted,all characters to the the end of the string are returned.)

Number Functions

ROUND: Rounds value to specified decimal        ROUND(32.987,2)    print 32.99      ROUND(32.987,-1)     print  30     ROUND(32.987,-1)     print  33    

TRUNC: Truncates value to specified decimal     TRUNC(32.987,2)     print  32.98    

MOD:     Returns remainder of devision             MOD(1500,400)       print 300

Note: ROUND(column|expression,n) Rounds the column,expression, or values to n decimal places,or,if n is omitted,no decimal places.(If n is negative,numbers to left of the decimal point are rounded.)

Oracle Date Format

Oracle database stores dates in an internal numeric format,representing the century,year,month,day,hours,minutes,and seconds.The default display and input format for any date is DD-MON-RR.However,dates are not stored in the database in this format.All components of the date and time are stored.

SYSDATE Function

SYSDATE is a date function that returns the current database server date and time.You can use SYSDATE just as you would use any other column name.It is customary to select SYSDATE from a dummy table called DUAL.

Elements of the Date Format Model

YYYY    full year in numbers

YERA    Year spelled out

MM       Two-digit value for month

MONTH  Full name of the month

MON     Three-letter abbreviation of the month

DY        Three_letter abbreviation of the day of the week

DAY      Full name of the day of the week

DD        Numeric day of the month

demo: SELECT to_char(sysdate, 'YYYY/YEAR/MM/MONTH/MON/DY/DAY/DD,HH24:MI:SS AM') FROM DUAL;

Date Function

MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC

Conversion Functions

Implict data type conversion

Explicit data type conversion      TO_CHAR,TO_NUMBER,TO_DATE

Nesting Functions

Single-row functions can be nested to any level.

Nested functions are evaluated from deepest level to the least deep level.

General Functions

NVL(expr1,expr2)              convernts a null value to an actual value.

NVL2(exprl,expr2,expr3)   if  exprl is not null,NVL2 returns expr2,else returns expr3.

NULLIF(expr1,expr2)         compares two expressions and returns null if they are equal,or the first expression if they are not equal

COALESCE(exprl,expr2,…,exprn)   return the first non-null expression in the expressions list

Conditional Expressions

CASE  ( compiles with ANSI SQL)

CASE expr WHEN comparsion_expr1 then return_expr1

               [WHEN comparsion_expr2 then return_expr2

                 ……

                ELSE else_expr]

END

DECODE( specific to Oracle syntax)

DECODE( col|expression, search1,resut1

                                    [,search2,result2,…,]

                                    [,default])