select length(trim(' zxy ')),length(' zxy ') from dual  ---trim双向取空格
SQL> /

LENGTH(TRIM('ZXY')) LENGTH('ZXY')
------------------- -------------
                  3             5

SQL> select trim('m' from 'm love you') from dual;---trim另一种引用方式

TRIM('M'F
---------
love you

SQL> select trim('m' from 'm love youm') from dual;

TRIM('M'F
---------
love you

SQL> select trim('m' from 'm love m youm') from dual;

TRIM('M'FRO
-----------
love m you

SQL> select ltrim('love you m','m') from dual;---rtrim同理

LTRIM('LOV
----------
love you m

c/被替换的字符/替换为的字符  ---sqlplus

SQL> select substr('tom',-1,2) from dual;--- -1表明为从后端所处位置,从右向左算

S
-
m

SQL> select substr('toms',-1,2) from dual;

S
-
s

SQL> select substr('toms',-1,1) from dual;

S
-
s

SQL> select substr('toms',-2,1) from dual;

S
-
m

SQL> select substr('toms',-4,1) from dual;

S
-
t

SQL> select trunc(192.168,2),round(192.686,2) from dual;

TRUNC(192.168,2) ROUND(192.686,2)
---------------- ----------------
          192.16           192.69

SQL> select round(192.168,-2) from dual;

ROUND(192.168,-2)
-----------------
              200

SQL> select (sysdate-(sysdate-7))/7 from dual;---计算几个星期,两个日期

(SYSDATE-(SYSDATE-7))/7
-----------------------
                      1

add_months(sysdate,1) ---对指定日期加指定月份,对应的具体日期
months_between(date1,date2) ---两个指定日期相差的月份数
last_day(date1) ---指定日期对应本月最后一天的具体日期

SQL> select sysdate,round(sysdate,'month') from dual;

SYSDATE   ROUND(SYS
--------- ---------
23-JUN-08 01-JUL-08

SQL> select trunc(sysdate,'month') from dual;

TRUNC(SYS
---------
01-JUN-08

SQL> select round(sysdate,'year'),trunc(sysdate,'year') from dual;

ROUND(SYS TRUNC(SYS
--------- ---------
01-JAN-08 01-JAN-08

SQL> select user_t from user_tables;
select user_t from user_tables
       *
ERROR at line 1:
ORA-00904: "USER_T": invalid identifier


SQL> c/user_t/table_name
  1* select table_name from user_tables
SQL> /

TABLE_NAME
------------------------------
SEX1
WHILETEST
SEX_BAK

oracle会发生隐式数据转换和显式数据转换.

隐式转换:是兼容数据类型转换,
from                   to

varchar or char        number  ---字符到数值
varchar or char        date    ---字符到日期
number                 varchar ---数值到字符
date                   varchar ---日期到字符


1* select to_char(sysdate,'year') from dual
SQL> /

TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
two thousand eight

SQL> select to_char(sysdate,'month') from dual;

TO_CHAR(S
---------
june

SQL> select to_char(sysdate,'mm') from dual;---取指定日期月份

SQL> /

TO_CHAR(S
---------
june

SQL>  select to_char(sysdate,'mm') from dual;---取指定日期月份简写

TO
--
06
1* select to_char(sysdate,'dd-month-yyyy') from dual
SQL> /

TO_CHAR(SYSDATE,'
-----------------
23-june     -2008

SQL> c/yyyy/year
  1* select to_char(sysdate,'dd-month-year') from dual
SQL> /

TO_CHAR(SYSDATE,'DD-MONTH-YEAR')
-------------------------------------------------------
23-june     -two thousand eight

SQL> select to_char(1234.00,'$9,999.00') from dual;

TO_CHAR(12
----------
$1,234.00

to_char ---第二部分格式串中
           $ 表示美元符
           L 表示本地符
           . 表示小数点
           , 表示千位符

SQL> select nullif('sex','it is not null') from dual;---nullif(compress1,compress2),表示如compress1为null,输出compress2,否则显示compress1

NUL
---
sex

SQL> select nullif(' ','it is null') from dual;

N
-

SQL> select nvl('sex','it is null') from dual;---nvl(compress1,compress2),若compress1为空,显示compress2,不为空,显示compress1

NVL('','IT
----------
it is null

SQL> select nvl(' ','it is null') from dual;

N
-

SQL> select nvl2('make love','have value','have no value') from dual;

NVL2('MAKE
----------
have value
select nvl2('','have value','have no value') from dual
SQL> /

NVL2('','HAVE
-------------
have no value

set pagesize --配置不让过多的列标题多次出现,也讲为配置页行长度
set linesize 1000

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> select decode(job,'CLERK','normal', ---decode(str1,条件1,条件1显示结果,条件2,条件2显示结果,其它情况结果)--类似于if then else
  2                    'SALESMAN','top',
  3                    'MANAGER','god',
  4                    'it') from emp;

DECODE
------
normal
top
top
god
top
god
god
it
it
top
normal
normal
it
normal

14 rows selected.

007 第四个看到这儿了.
equal join ---用于主外键,连接n个表,最少n-1个条件
non-equaljoin ---select e.name,e.salary,j.grade_level from enployees e,job_grades j where e.salary between j.lowest sal and j.highest sal;
outer join
---是join特殊情况,把不完全匹配join条件的信息也显示出来,
    select t1.col,t2.col from t1,t2 where t1.col(+)=t2.col;---右连接,左全显示,右为某些null值
    +在哪边,哪边的不匹配以null值显示
select e.empno,e.ename,e.sal,d.deptno,d.dname
  2  from emp e,dept d
  3  where e.deptno(+)=d.deptno;

     EMPNO ENAME             SAL     DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
      7782 CLARK            2450         10 ACCOUNTING
      7839 KING             5000         10 ACCOUNTING
      7934 MILLER           1300         10 ACCOUNTING
      7566 JONES            2975         20 RESEARCH
      7902 FORD             3000         20 RESEARCH
      7876 ADAMS            1100         20 RESEARCH
      7369 SMITH             800         20 RESEARCH
      7788 SCOTT            3000         20 RESEARCH
      7521 WARD             1250         30 SALES
      7844 TURNER           1500         30 SALES
      7499 ALLEN            1600         30 SALES

     EMPNO ENAME             SAL     DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
      7900 JAMES             950         30 SALES
      7698 BLAKE            2850         30 SALES
      7654 MARTIN           1250         30 SALES
                                         40 OPERATIONS

15 rows selected.



self join---一个join key是另一人join key的子集,只发生在一个表,表名连接要写上别名,取掉自己和自己匹配 的结果,及一半重复内容(>)
natural join-----用于多表同名列的连接select
SQL> select e.ename,e.job,d.dname
  2  from emp e natural join dept d;

ENAME      JOB       DNAME
---------- --------- --------------
SMITH      CLERK     RESEARCH
ALLEN      SALESMAN  SALES
WARD       SALESMAN  SALES
JONES      MANAGER   RESEARCH
MARTIN     SALESMAN  SALES
BLAKE      MANAGER   SALES
CLARK      MANAGER   ACCOUNTING
SCOTT      ANALYST   RESEARCH
KING       PRESIDENT ACCOUNTING
TURNER     SALESMAN  SALES
ADAMS      CLERK     RESEARCH

ENAME      JOB       DNAME
---------- --------- --------------
JAMES      CLERK     SALES
FORD       ANALYST   RESEARCH
MILLER     CLERK     ACCOUNTING

14 rows selected.



cross join ---同于无条件连接,同于笛卡尔积m*n
select e.empno,d.dname
  2  from emp e cross join dept d;


using clause
1  select e.ename,d.dname
  2* from emp e join dept d using (deptno) ---using用于指定连接列


full or two sided outer join  ---from x left outer join y on x.id=y.id,full outer join为 left outer join和right outer join之和
arbitrary join conditions for outer joins


1* select e.ename,e.sal,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno
SQL> /

ENAME             SAL     DEPTNO DNAME
---------- ---------- ---------- --------------
SMITH             800         20 RESEARCH
ALLEN            1600         30 SALES
WARD             1250         30 SALES
JONES            2975         20 RESEARCH
MARTIN           1250         30 SALES
BLAKE            2850         30 SALES
CLARK            2450         10 ACCOUNTING
SCOTT            3000         20 RESEARCH
KING             5000         10 ACCOUNTING
TURNER           1500         30 SALES
ADAMS            1100         20 RESEARCH

ENAME             SAL     DEPTNO DNAME
---------- ---------- ---------- --------------
JAMES             950         30 SALES
FORD             3000         20 RESEARCH
MILLER           1300         10 ACCOUNTING

14 rows selected.

SQL> input and e.ename='SMITH'
1  select e.ename,e.sal,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno ---利用input命令对多表连接添加where条件
  2* and e.ename='SMITH'
SQL> /

ENAME             SAL     DEPTNO DNAME
---------- ---------- ---------- --------------
SMITH             800         20 RESEARCH



  
run ---同于/,再次运行sqlplus
l   ---显示以上脚本
lx  ---显示第x行脚本
del n ---用于删除第几行脚本

select e.name,d.dname from emp e join dept d on d.x=e.y;---用on指定不同的连接列

count ---会ignore此列空值

1  select deptno,avg(nvl(sal,0)) from emp
  2* group by deptno

    DEPTNO AVG(NVL(SAL,0))
---------- ---------------
        30      1566.66667
        20            2175
        10      2916.66667
select deptno,avg(nvl(sal,0)) from emp
  2  group by deptno
  3* order by deptno asc
DEPTNO AVG(NVL(SAL,0))
---------- ---------------
        10      2916.66667
        20            2175
        30      1566.66667
select sum(sal) from emp group by deptno ---分组列也可以不出现在select中

  SUM(SAL)
----------
      9400
     10875
      8750
where 条件中不能有sum,avg等function

1  select deptno,sum(sal) ---加了having的子句,在select中有要分组列
  2  from emp
  3  group by deptno
  4* having sum(sal)>9400
SQL> /

    DEPTNO   SUM(SAL)
---------- ----------
        20      10875

SQL> select max(sum(sal)) from emp group by deptno;---分组funtion可以nest,但在select中不能有分组列出现

MAX(SUM(SAL))
-------------
        10875