续上.

007-202
subquery:single subquery and multiple subquery
子查询把传递结果参数给父查询

标准子查询和外查询没有关联关系,子查询只执行一次
关联子查询和外查询有关联,子查询要执行多次

select deptno,min(sal)---having也可以用子查询
  2  from emp
  3  group by deptno
  4  having min(sal)>(select min(sal) from emp where ename='JOB');

no rows selected

1  select * from
  2  emp
  3* where sal>(select min(sal) from emp)
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

any  ---任何一个
all  ---每一个都要
in -----列表任何一个

join好于子查询(多表连接)

isqlplus学习
进入isqlplus:http://localhost/isqlplus
isqlplus从9i引入,三层体系架构
substitution value替换变量:
&    select ename from emp where job=&x;---可以用于where,from,order by,column,select   ,也可以select * from &b where &z;--一个sql中多个替换变量

&&   select &&sal from emp order by &sal
define  ---先给替换变理定义一个值,以后不用提示了.define z='zxy',取消define定义,用undefine,或退出isqlplus
           define下,可查看相关define定义变量情况

select &&sal from emp order by &sal;
Enter value for sal: sal
old   1:  select &&sal from emp order by &sal
new   1:  select sal from emp order by sal

       SAL
----------
       800
       950
      1100
      1250
      1250
      1300
      1500
      1600
      2450
      2850
      2975

       SAL
----------
      3000
      3000
      5000

14 rows selected.


set verify on|off ---是否对系统进行验证,也就是显示替换变量old,new

show all;----查询set一系列sqlplus中环境变量的配置情况

set 变量名  变理值
help 要帮助的主题


007-203 之25分钟

column job clear ---清除复位到原型
col last_name heading 'zxy'
column sal justify left  format $99,999.00
column manager format 999999999 null 'no manager'
break on   ---压缩重复值
ttitle [text|on|off]
btitle 'test report'  ---尾
ttitle 'salary report header'---头

SQL> ttitle 'report title'
SQL> btitle 'report footer'
SQL> /

Mon Jun 23                                                                                                                          page    1
                                                                                                                                   report title

     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
                                                                                                                                  report footer

Mon Jun 23                                                                                                                          page    2
                                                                                                                                   report title

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      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
                                                                                                                                  report footer

14 rows selected.


SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> col deptno justify left----表明justify left|right只是针对列标题左右排列
SQL> /

DEPTNO     DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> col deptno justify right
SQL> /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
col comm justify right null 'not comm' ---对comm列空值进行显示,以not comm显示
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800 not comm           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 not comm           20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

学习了

update emp_bak set comm=decode(comm,null,0,comm);---以不同条件结合decode对表进行更新

SQL> save /home/oracle/x.sql
Created file /home/oracle/x.sql
update emp
set job=(select job from emp where ename='zxy')
where deptno=58;---表明update中可以有子查询(set 和where中)


update emp set job='anaylist' where deptno in(select deptno from dept);---update可包含in子句

delete也同上可能用子查询

insert into (select * from emp where ) values();---insert中也可以用子查询,置于into中.

SQL> create table te(a int,b int default 0);---default constriant

Table created.

SQL> insert into te values(1,default);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from te;

         A          B
---------- ----------
         1          0

SQL> insert into te(a) values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from te;

         A          B
---------- ----------
         1          0
         2          0



merge学习
merge into copy_emp c
using employee e  ---引用另外一个表
on (c.employee_id=e.employee_id)  ---merge into表和using表连接条件
when matched then  ---二表匹配,更新,以using表更新merge into 表
  update set
    c.first_name=e.first_name
when not matched then  ----二表不匹配,则插入using表的值到merge into表
  insert values(e.employee_id)


oracle事务:
显式和隐式事务
ddl dcl隐式自动提交,dml要手工提交

语句级rollback,就是如果你插入多条记录,在最后一下插入发生错误,前面的插入还在,但要是rollback,前几个插入没有了


dirty read:没有rollback or commit的语句,它是保存在undo中的.另外oracle也锁定这条记录,其它用户不能更改它

oracle通过undo来实现读一致性:其它用户读取undo中的data pi
                             读不阻写,写不阻读


lock:显式和隐式
exclusive:排它
share

dml:table share,row exclusive
query:no  lock required
ddlrotect object definitions ---

lock会在commit or rollback release it

SQL> create table table-1 (a int); ---  -这个符号不能用于表名
create table table-1 (a int)
             *
ERROR at line 1:
ORA-00903: invalid table name
SQL> create table "table-l"(a int); ---加上双引号就可以,单引号也不行

Table created.
SQL> drop table "table-l";

Table dropped.
SQL> create table 23a(x int);---表明表名只能以字母开头

SQL> create table 23a(x int);
create table 23a(x int)
             *
ERROR at line 1:
ORA-00903: invalid table name


SQL> create table a23(x int);

Table created.

SQL> create table =x(a int);---reserved work不能用于表名
create table =x(a int)
             *
ERROR at line 1:
ORA-00903: invalid table name


SQL> create table a(x date);---测试在insert可以用function作value insert

Table created.

SQL> insert into a values(sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from a;

X
---------
23-JUN-08


  1* select to_char(x,'yyyymmdd') from a
SQL> /

TO_CHAR(
--------
20080623

long  ---2g ,不建议使用它
clob  ---4g
raw and long raw  ---raw binary data
blob   ---binary data up to 4g
bfile ---binary data stored in an external file;up to 4 g
rowid --64 base
number(p,s) ---精度和刻度
timestamp  ---用得少,有用再学


alter table drop|add|modify  ---drop column时,如果在线系统,此动作有一定延时,所以先让此列失效,然后在系统不太busy再delete it


SQL> desc a;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
X                                                  DATE

SQL> alter table a modify x char;---不同的类型转换要先把数据清除了.才可以转换
alter table a modify x char
                     *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype


SQL> truncate table a;

Table truncated.

SQL> alter table a modify x char;

Table altered.

SQL> alter table a modify x varchar2(10);

Table altered.

SQL> alter table a modify x number;--在表中没数据时,列数据类型转换没有什么限制

Table altered.

SQL> alter table a add y int;

Table altered.

SQL> desc a;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
X                                                  NUMBER
Y                                                  NUMBER(38)


SQL> alter table a drop column y;

Table altered.

SQL> alter table a add y int;

Table altered.

SQL> desc a;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
X                                                  NUMBER
Y                                                  NUMBER(38)

SQL> select * from a;

no rows selected

SQL> insert into a values(1,1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from a;

         X          Y
---------- ----------
         1          1
         1          1
         1          1
         1          1

SQL> alter table a drop column y;---在有数据时,删除一个列,数据仍在

Table altered.

SQL> desc a;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
X                                                  NUMBER

SQL> select * from a;

         X
----------
         1
         1
         1
         1

SQL> alter table a modify x default 5;

Table altered.

SQL> select * from a;

no rows selected

SQL> insert into a values(default);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from a;

         X
----------
         5
         5

SQL>
SQL> select * from tx;

         A          B
---------- ----------
         1          2
         2          3

SQL> alter table tx set unused column b;---利用此命令可以先把列变为不可用,在删除它在不busy时

Table altered.

SQL> desc tx;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
A                                                  NUMBER(38)

SQL> alter table tx drop unused columns;

Table altered.

SQL> desc tx;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
A                                                  NUMBER(38)

SQL> select * from tx;

         A
----------
         1
         2

SQL>