|
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必须同类型或者可以隐含转换 |
