---------Lession1 Selecting Rows------
* RELATIONSHIPS
Solid Line = Must Be
Dashed Line = May Be
Single Line = One and Only One
Crow's 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 left-justified within a column and numbers are right-justified. 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 92 standards.
* 关于别名的使用（重点）：
1. 一旦在WHERE中定义了表别名，则在整个SELECT语句中不能再使用表原名
2. where,group by子句中可以使用表别名，但不能使用列别名
2. order by子句中可以使用列别名，但不能有表名前缀，不能使用：表原名.列别名，但可以使用：表别名.列原名
3. 表别名可以与列别名相同
* 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. -- 8i中还没有改变，''仍然视为null处理
* You can use the NVL function to convert any datatype, but the return value is always the same as the datatype of expr1. -- 除非可以隐含自动转换的类型，比如数字－字符
* 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 : 0 text Inserts a line before line 1.
* 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. L9999 －与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.(Lession1 Page7 Wri
--------------Lession2 Limiting Selected Rows------------------
* ORDER BY clause is last in SELECT command. －（重点）
* If no ORDER BY clause, the Oracle7 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 Oracle7, null values are displayed last for ascending sequences and first for descending sequences. -- 8i依然是这个原则（重点）
* 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 name,expression,constant
- Comparison operator
- Literal
* ...WHERE expr operator value : the expr cannot be an alias. －参见Lession1的关于别名的限制说明
* SQL Operator Precedence 优先次序 －（重点）
1. 正负 + -
2. 乘除 * /
3. 加减 + - ||
4. All comparison operators 比较运算符：= = BETWEEN ...AND.../IN(list)/LIKE/is (not) null
5. NOT
6. AND 逻辑运算符
7. 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?
--
----------------Lession3 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 user-supplied 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|expression,m[,n]): m 不能缺省，n缺省为到end, n=0返回null
NVL(column|expression1,column|expression2)：exp1和exp2必须同类型，或者可以隐含转换
