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])
