001考試筆記 Part I

　　Lession Selecting Rows
　　
　　* RELATIONSHIPS
　　 Solid Line = Must Be
　　 Dashed Line = May Be
　　 Single Line = One and Only One
　　 Crows foot = One or More
　　 UID = #
　　 Secondary UID = ( # )
　　 Mandatory attribute = ( * )
　　 Optional Attribute = ( o )
　　* An SQL command is entered at the SQL prompt and subsequent lines are numbered This is called the SQL buffer －可有多個buffer存貯plus語句
　　* Only one statement can be current at any time within the buffer and the statement can be executed in a number of ways: (重點)
　　 Place a semicolon ( at the end of last clause
　　 Place a semicolon or slash on the last line in the buffer
　　 Place a slash at the SQL prompt
　　 Issue a SQL*Plus RUN command at the SQL prompt
　　* Character and date column headings and data are leftjustified within a column and numbers are rightjustified Character and date column headings may be truncated but number headings may not be truncated The column labels appear in uppercase by default You can override the column label display with an alias （重點注意僅數字是右對齊的而且保留完整的heading)
　　* SQL*Plus ignores blank spaces before and after the arithmetic operator
　　* By default alias headings will be forced to uppercase and cannot contain blank spaces and special characters (such as # or $) unless the alias is enclosed in double quotation marks ( )
　　* You can include the AS keyword before the alias name to comply with ANSI SQL standards
　　* 關於別名的使用（重點）
　　 一旦在WHERE中定義了表別名則在整個SELECT語句中不能再使用表原名
　　 wheregroup by子句中可以使用表別名但不能使用列別名
　　 order by子句中可以使用列別名但不能有表名前綴不能使用表原名列別名但可以使用表別名列原名
　　 表別名可以與列別名相同
　　* A literal is any character expression or number included in the SELECT list that is not a column name or a column alias 可以理解為除了字段名（包括別名）以外的常量（字符數字表達式）
　　* Currently Oracle treats a character value with length of zero as null However this may not continue to be true in future versions of Oracle i中還沒有改變仍然視為null處理
　　* You can use the NVL function to convert any datatype but the return value is always the same as the datatype of expr 除非可以隱含自動轉換的類型比如數字－字符
　　* In SQL*Plus you can display the structure of a table using the DESCRIBE command The result of the command is to see the column names datatypes and whether a column must contain data(null or not null) （重點）
　　* You terminate the SQL buffer by either entering one of the terminator characters (semicolon or slash) or pressing [RETURN] twice You will now see the SQL prompt (重點)
　　* SQLPLUS Command : text Inserts a line before line
　　* SPO[OL] [filename[ext]|OFF|OUT] Stores query results in a file OFF closes the spool file OUT closes the spool file and sends the file results to the system printer (重點)
　　* COLUMN 命令
　　 JUS[TIFY] {align} Justifies the column heading (not the data) to be left center or right
　　 NOPRI[NT] Hides the column
　　 NUL[L] text Specifies text to be displayed for null values
　　 PRI[NT] Shows the column
　　 TRU[NCATED] Truncates the string at the end of the first line of display －與wrap對應根據列寬截斷
　　 WRA[PPED] Wraps the end of the string to the next line
　　 WOR[D_WRAPPED] Same as WRAPPED but ensures that words do not split －在中文字符集下的處理也是以空格區分
　　 L Represents local currency L －與nls_currency的設定相關
　　*
　　 COL[UMN] column Displays the current settings for the specified column
　　 COL[UMN] Displays the current settings for all columns
　　 COL[UMN] column CLE[AR] Clears the settings for the specified column
　　 CLE[AR] COL[UMN] Clears the settings for all columns
　　* The selection capability in SQL allows you to choose rows in a table to be returned by a query You can restrict the rows returned by selection using various criteria The projection capability in SQL allows you to choose columns in a table to be returned by a query You can control the number of columns returned by a query Selection is often considered horizontal partitioning and projection is often considered vertical partitioning
　　? SQL commands are not case sensitive unless indicated(Lession Page Wri
　　
　　
　　Lession Limiting Selected Rows
　　
　　* ORDER BY clause is last in SELECT command －（重點）
　　* If no ORDER BY clause the Oracle Server may not fetch rows in the same order for the same query twice 與數據存貯的變化有關
　　* ASC orders the rows in ascending order This is the default order
　　* In Oracle null values are displayed last for ascending sequences and first for descending sequences i依然是這個原則（重點）
　　* You can order by position to save time 沒有測試過按位置order by是否可以節省運行時間？
　　* Sort by position is especially useful when sorting by a long expression Rather than typing the expression again －別名也可以解決問題
　　* You can order by columns that are not in the SELECT list 注意例外情況是在SELECT中使用了DISTINCT和組函數 （重點）
　　* Conditions consist of the following:
　　 Column nameexpressionconstant
　　 Comparison operator
　　 Literal
　　* WHERE expr operator value : the expr cannot be an alias －參見Lession的關於別名的限制說明
　　* SQL Operator Precedence 優先次序 －（重點）
　　 正負 +
　　 乘除 * /
　　 加減 + ||
　　 All comparison operators 比較運算符= < <= > >= <> BETWEEN AND/IN(list)/LIKE/is (not) null
　　 NOT
　　 AND 邏輯運算符
　　 OR
　　* = NULL that an error is not raised the result is simply always FALSE
　　* It may be faster and easier to eliminate rather than include －?排除比包括速度快?需要測試
　　* Search conditions can contain either literal characters or numbers
　　* The % and _ symbols may be used in any combination with literal characters
　　* When you need to have an exact match for the actual % and _ characters use the ESCAPE option This option specifies what the ESCAPE character is
　　
　　? 無ORDER BY時ORACLE缺省用什麼樣的順序顯示數據？ROWID?
　　
　　
　　
　　Lession Single Row Functions
　　
　　* Functions are a very powerful feature of SQL and can be used to
　　 Perform calculations on data
　　 Modify individual data items
　　 Manipulate output for groups of rows
　　 Alter date formats for display
　　 Convert column datatypes
　　* An argument may be one of the following:
　　 A usersupplied constant
　　 A variable value
　　 A column name
　　 An expression
　　* Features of Single Row Functions
　　 They may expect one or more user arguments
　　 You can nest them
　　 You can use them in SELECT WHERE and ORDER BY clauses －－ 為什麼不寫group by?
　　* Lower()Upper(): Converts alpha character
　　INITCAP(): Converts alpha character values to uppercase for the first letter of each word all other letters in lowercase
　　CONCAT(): 同 ||
　　SUBSTR(column|expressionm[n]): m 不能缺省n缺省為到end n=返回null
　　NVL(column|expressioncolumn|expression)exp和exp必須同類型或者可以隱含轉換

From:http://tw.wingwit.com/Article/program/Oracle/201311/16798.html