Write Baseic SQL Select Statements

   #Oracle9i SQL语句的类型 
    —数据查询语句(SELECT 语句)
    —数据定义语句（DDL 语句）包括数据对象的建立、修改、删除等。
    —数据操作语句（DML）包括数据的insert、update、delete等。
    —数据控制语句（DCL语句）用户的权限授予、实现数据安全性。
    —事务控制语句（PCL语句）commit、rollback等。
  #基本select语句的书写
                       Objectives
  #After completing this lesson,you should be able to do the following:
     -List the capabilities of SQL select statements
     -Execute a basic select statement
     -Differentiate between SQL statments and iSQL*Plus commands
  #Capabilities of SQL select statements

     -select 语句可以基于某一个数据对象的如只列出某个表的某些列或某些行我们也能通
      过join连接访问多个表中的数据。
    例:比如用户scott（密码tiger）登陆有emp表(可以用select * from user_tables得到
       当前用户所包含的表)
        ·查询emp表某些列(column)
          select empno,ename from emp; 得到了emp表中empno,ename两列的信息
         ·查询emp表中的某些行（row）
          select * from emp where empno>7850;得到了emp表中所有empno>7850行的信息
         ·查询多表中的信息
          select t1.empno,t1.ename,t2.fname,t2.lname
          from emp t1,store t2
          where t1.ename=t2.lname;得到两表中 t1.ename=t2.lname的信息

#Basic select statement
    -select *|{[distinct] column|expression [alias],...}
     from table;
     ·select identifies what columns;
     ·from identifies which table;
    -Selecting All Columns
     ·select *
      from emp;
      '*'号表示当前数据对象的所有列（Column）,意思就是说'*'是在这是简写那么它对
      应到的就是当前数据对象的所有Column;
    -Selecting Specific Columns
     ·select empno,ename
       from emp;
       在这条语句中我们能看到指定栏位名（列名，Columns）的方式限定我们需要查   找的这些栏位信息;
     注意：栏位名之间有(,)号分隔(;)号作为语句的结束符;
                  Writing SQl Statements
     ·SQL statements are not case sensitive.不敏感大小写
     ·SQL statements can be on one of more lines.可在一行或多行写
     ·Keywords cannots be abbreviated or split across lines.关键字不能跨行或缩写
     ·Clause are usually placed on separate lines.子句(where等)通常另开一行写
     ·Indents are used to enhance readability.缩行可提高语句的可读性
   
   #Column Heading Dafaults
     ·iSQL*Plus:
          -Default heading justification:Center 缺省栏位居中
          -Default heading display:Uppercase 缺省显示栏位大写
     ·SQL*Plus:
          -Character and Date column headings are left-justified 字符和日期类型的栏位头居左
          -Number column heading are right-justified 数字类型栏位头居右
          -Default heading display:Uppercase 缺省显示栏位大写
   #Arithmetic Expressions 算术运算符
     ·Create expressions with number and date data by using arithmetic operators
        --------|------------
   Operator| Description
        --------|------------
            +    | Add
        --------|------------
            -     | Subtract
        --------|------------
            *     | Multiply
        --------|------------
            /      | Divide
        --------|------------
      -Using Arithmetic Expressions               

   例:SQL> Select empno,ename,sal+300
       2  from emp;         
     
          EMPNO ENAME         SAL+300
     ---------- ---------- ----------
           7369 SMITH            1100
           7499 ALLEN            1900
           7521 WARD             1550
           7566 JONES            3275
           7654 MARTIN           1550
           7698 BLAKE            3150
           7782 CLARK            2750
           7788 SCOTT            3300
           7839 KING             5300
           7844 TURNER           1800
           7876 ADAMS            1400
     
          EMPNO ENAME         SAL+300
     ---------- ---------- ----------
           7900 JAMES            1250
           7902 FORD             3300
           7934 MILLER           1600
     
     已选择14行。
     
     SQL>
 　　·Operator Precedence  运算符的优先级　
        ----------------------
   　　    *   /    +    -
        ----------------------
　　　  -Multiplication and division take priority over
        addition and subtraction .
        -Opertors of the same priority are evaluated from left to right.
        -Parentheses are used to force prioritized
         evaluated and to clarify statements.
        与我们日常的优先级一样,也可以用括弧'()'改变优先级
        -Opertor Precedence
例:SQL> run
  1  Select empno,ename,sal+300*3
  2  from emp

     EMPNO ENAME       SAL+300*3
---------- ---------- ----------
      7369 SMITH            1700
      7499 ALLEN            2500
      7521 WARD             2150
      7566 JONES            3875
      7654 MARTIN           2150
      7698 BLAKE            3750
      7782 CLARK            3350
      7788 SCOTT            3900
      7839 KING             5900
      7844 TURNER           2400
      7876 ADAMS            2000

     EMPNO ENAME       SAL+300*3
---------- ---------- ----------
      7900 JAMES            1850
      7902 FORD             3900
      7934 MILLER           2200

已选择14行。
例:用括弧'()'改变优先级
SQL> Select empno,ename,(sal+300)*3
  2  from emp;

     EMPNO ENAME      (SAL+300)*3
---------- ---------- -----------
      7369 SMITH             3300
      7499 ALLEN             5700
      7521 WARD              4650
      7566 JONES             9825
      7654 MARTIN            4650
      7698 BLAKE             9450
      7782 CLARK             8250
      7788 SCOTT             9900
      7839 KING             15900
      7844 TURNER            5400
      7876 ADAMS             4200

     EMPNO ENAME      (SAL+300)*3
---------- ---------- -----------
      7900 JAMES             3750
      7902 FORD              9900
      7934 MILLER            4800

已选择14行。
     #Defining a Null Value   Null值的定义
      -A null is a value that is unavailable,unassigned,  Null是一个无效的、不被指定的、未
      unknown,or inapplicable.知的或是不确定的状态
      -A null is not the same as zero or a blank space.及不等于零也不是一个空格  
      在数据库环境里面null只是说一种状态、没有给它指定一个确切的值，是一种不确定的状态。
      -Null Vlaues in Arithmetic Expresstions
       Arithmetic expresstions containing a null value evaluate to null
       算术表达式中如果包含任何一个null值,它的结果也为null
 例:
SQL> select  empno,ename,null*sal+99 from emp
  2  where empno=7369;

     EMPNO ENAME      NULL*SAL+99
---------- ---------- -----------
      7369 SMITH

     #Defining a Column Alias 栏位别名的定义
      -A column Alias
       ·Renames a column heading
       ·Is useful with calculations
       ·Immediately follows the column name-there can also be the optional as keyword between
         the column name and alias  一种是在栏位名后紧跟着定义别名一种是栏位名后加as再加别名
       ·Requires double quotation marks if it contains spaces or special character or is case
         sensitive.保持别名的大小写时用双引号鉴定
例:
SQL> select ename name,empno as id from emp
  2  where empno>7900;

NAME               ID
---------- ----------
FORD             7902
MILLER           7934
SQL>   select ename name,empno as "id"
  2   from emp
  3   where empno>7900;

NAME               id
---------- ----------
FORD             7902
MILLER           7934
       #Concatenation Operator连接处理
       -A Concatenation Operator:
       ·Concatenates columns or character strings to other columns
       ·Is repressented by two vertical bars(||)
       ·Creates a resultant column that is a character experssion
       oracle在实现字符连接时使用的(||)不同与SQL Server用(+).
例:
SQL>  select empno||'  is  '||ename from emp
  2  where empno<7400;

EMPNO||'IS'||ENAME
--------------------------------------------
7369  is  SMITH
       #Literal Character Strings
       - A Literal is a charcter,a number,or a date included in the selece list
       - Date and character literal values must be enclosed within single quotation marks
       - Each character string is output once for each row returned
       Literal用的单引号进行鉴定,如果Literal中包含一个单引号,要在加一个单引号进行转定义.
例:
SQL> select empno,'''is name ',ename from emp
  2  where empno>7900;

     EMPNO '''ISNAME ENAME
---------- --------- ----------
      7902 'is name  FORD
      7934 'is name  MILLER
     

     #Duplicate Rows 重复行
      -The default display of queries is all rowsm,including duplicate rows.
     Oracle在默认显示所有的行,包括重复行.
      #Eliminate Duplicate Rows除去重复行
      -Eliminate duplicate rows by using the DISTINCT keyword in the select clause
例:
SQL> select distinct deptno from emp;

    DEPTNO
----------
        10
        20
        30

 # SQL and iSQL*Plus Interaction
  - SQL statements Versus iSQL*Plus Commands
     SQL                           iSQL*Plus
       ·A language              |  ·An enviroment
       ·ANSI standard        |  ·Oracle proprietary
      ·Keyword cannot be |  ·Keywords can be
        abbreviated             |    addreviated
     ·Statements manipu-|  ·Commands do not allow
        late data and tab-    |    manipution of values in
        le definitions in        |    the database
        the database           |  ·Runs on a browser
                                           |  ·Centrally loaded,does not
                                           |    have to be implemented
                                                on each machine

  -Logging in to iSQL*Plus
    ·open a browser
    ·URL 'http://localhost/isqlplus'
     注意:如果键入URL后 发现地址未找到 应查看OracleOraHome92HTTPServer服务是否启用.
  #Display Table Structure 显示表的结构
  -Use the iSQL*Plus DESCRIDE command to display the structure of a table
  例:
SQL> desc emp;
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- --------------
 EMPNO                                                 NOT NULL NUMBER(4)
 ENAME                                                          VARCHAR2(10)
 JOB                                                            VARCHAR2(9)
 MGR                                                            NUMBER(4)
 HIREDATE                                                       DATE
 SAL                                                            NUMBER(7,2)
 COMM                                                           NUMBER(7,2)
 DEPTNO                                                         NUMBER(2)        
iSQL*Plus 可以保存脚本和加载运行脚本

                              Summary
 in this lession ,you should have learned how to :
 -Write a select statement that:
    ·Returns all rows and columns from a table
    ·Returns specified columns from a table
    ·Uses column aliases to give desciptive column heading
 -Use the iSQL*Plus enviroment to write,save,and
  execute SQL statements and iSQL*Plus commands.