Oracle Remarks

1.  sqlplus sys/sys as sysdba

2.  unlock user：alter user scott account unlock;

3.  连接：conn scott/tiger

4. 清屏：clear scr

5.  描述表结构：desc emp(表名);

6.  系统时间：select sysdate from dual;

    SQL> select sysdate from dual;

    SYSDATE
    --------------
    15-9月 -09

7. 别名：
   ①：select ename,sal*12 anuual_sal from emp;

   ②：select ename,sal*12 "anuual sal" from emp;

8. 连接||：
   ①：select ename||sal from emp;

   ②：select ename||'abcedfg' from emp;

   ③：select ename||'abce''dfg' from emp;

9. 空值NULL：
   ①：任何含有空值的表达式的计算结果都为空值(NULL)

   ②：select ename,sal,comm from emp where comm is null;

   ③：select ename,sal,comm from emp where comm is not null;

10. like:
   ①：select ename from emp where ename like '%\%%';

   ②：select ename from emp where ename like '%$%%' escape '$';

11. dual：

   SQL> desc dual;
   名称                                      是否为空? 类型
    ----------------------------------------- -------- ----------------------------
   DUMMY                                              VARCHAR2(1)

   SQL> select * from dual;

   D
    -
   X

12. order by
  SQL> select ename,sal*12 annual_sal from emp
    2  where ename not like '_A%' and sal>800
    3  order by sal desc,ename asc;
  
  ENAME      ANNUAL_SAL
  ---------- ----------
  KING            60000
  FORD            36000
  SCOTT           36000
  JONES           35700
  BLAKE           34200
  CLARK           29400
  ALLEN           19200
  TURNER          18000
  MILLER          15600
  ADAMS           13200
  
  已选择10行。
  
     为了对返回的记录排序，需要用到SELECT语句的ORDER BY子句，ORDER BY 后可以跟列名、也可以跟指定列所处的位置。例如，下面两条语句的功能是一样的：

     SELECT empno,ename,sal FROM EMP ORDER BY ename;

     SELECT empno,ename,sal FROM EMP ORDER BY 2;

     缺省情况下ORDER BY是以升序（ASC）对行进行排序，可以指定用降序（DESC）排序。

     升序：SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL;

              SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY 3 ASC;

     降序：SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC;

     注意：

          ①. 当指定排序的列包含NULL值时
               如果是升序排序，ORACLE把NULL值所在的行放在最后面；
               如果是降序排序，ORACLE把NULL值所在的行放在最前面；
          ②. ORDER BY 后可以使用列的别名。
              SELECT EMPNO,ENAME,SAL ‘SALARY’ FROM EMP ORDER BY SALARY;

 

13.sql function:
   lower: elect lower(ename) from emp;
   upper:  select ename from emp where upper(ename) like '_A%';
   substr: select substr(ename,2,3) from emp;
   chr:
     SQL> select chr(65) from dual;
 
   C
   -
   A
   
  ascii:
    SQL> select ascii('A') from dual;
 
   ASCII('A')
   ----------
           65
          
  round:     
  1.SQL> select round(23.654) from dual;

    ROUND(23.654)
    -------------
              24
   
  2.SQL> select round(23.654,2) from dual;
   
    ROUND(23.654,2)
    ---------------
              23.65
   
  3.SQL> select round(23.654,-1) from dual;
   
    ROUND(23.654,-1)
    ----------------
                  20
                
  to_char：
 
  1.SQL> select to_char(sal,'$99,999.99') from emp where sal between 800 and 1500;
  
   TO_CHAR(SAL
   -----------
       $800.00
     $1,250.00
     $1,250.00
     $1,500.00
     $1,100.00
       $950.00
     $1,300.00
   
   已选择7行。
   
  2.SQL> select to_char(sal,'L99,999.99') from emp where sal between 800 and 1500;

   TO_CHAR(SAL,'L99,999
   --------------------
               ￥800.00
             ￥1,250.00
             ￥1,250.00
             ￥1,500.00
             ￥1,100.00
               ￥950.00
             ￥1,300.00
   
   已选择7行。
   
  3.SQL> select to_char(sal,'L00000.00') from emp where sal between 800 and 1500;

   TO_CHAR(SAL,'L00000
   -------------------
            ￥00800.00
            ￥01250.00
            ￥01250.00
            ￥01500.00
            ￥01100.00
            ￥00950.00
            ￥01300.00
   
   已选择7行。
   
  4.SQL> select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') from dual;

   TO_CHAR(SYSDATE,'YY
   -------------------
   2009-09-15 06:15:53
   
  5.SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
   
   TO_CHAR(SYSDATE,'YY
   -------------------
   2009-09-15 18:16:01
   
  to_date： 
   SQL> select ename,hiredate from emp where hiredate > to_date('1982-2-21 12:31:33
   ','YYYY-MM-DD HH24:MI:SS');
   
   ENAME      HIREDATE
   ---------- --------------
   SCOTT      19-4月 -87
   ADAMS      23-5月 -87
   
  to_number:
   SQL> select sal from emp where sal > to_number('$3,000.00','$9,999.00');

      SAL
      ----------
      5000
     
   nvl:
      SQL> select ename,sal*12+nvl(comm,0) from emp;

   ENAME      SAL*12+NVL(COMM,0)
   ---------- ------------------
   SMITH                    9600
   ALLEN                   19500
   WARD                    15500
   JONES                   35700
   MARTIN                  16400
   BLAKE                   34200
   CLARK                   29400
   SCOTT                   36000
   KING                    60000
   TURNER                  18000
   ADAMS                   13200
   
   ENAME      SAL*12+NVL(COMM,0)
   ---------- ------------------
   JAMES                   11400
   FORD                    36000
   MILLER                  15600
   
   已选择14行。

 

14. group function:
   max: select max(sal) from emp;
   min: select min(sal) from emp;  
   avg:
    1.SQL> select avg(sal) from emp;

     AVG(SAL)
     ----------
     2073.21429
 
  2.SQL> select to_char(avg(sal),'99999.99') from emp;
  
    TO_CHAR(A
    ---------
    2073.21
  
  3.SQL> select round(avg(sal),2) from emp;
 
    ROUND(AVG(SAL),2)
    -----------------
              2073.21
   
   sum: select sum(sal) from emp;
   count: select count(*) from emp;
            select count(deptno) from emp;
            select count(distinct deptno) from emp;

 

15.

   select table_name from  user_tables ;
   select view_name from  user_views ;
   select constraint_name from  user_constraints;

   select table_name from dictionary where table_name like 'USER%'

 

16.

    create index index_pro_pname on products (pname);

    drop index index_pro_pname;