oracle 中子查询的常用法

子查询：
1.使用子查询创建表
 通用格式
     CREATE TABLE tablename
            AS
            SELECT-query
    eg.  create  table student
         as
         select classname,classid from  class
2.使用子查询插入数据
   通用格式
       INSERT INTO TABLENAME[(column list)]
       select columnnames from tablename where condition
   eg.  insert into student(classname,classid)
        select classname,classid from class
3.向多张表中插入数据
  通用格式（无条件）eg.
      insert all
      into employee_salary
           values(Employeeid,lname,fname,salary,commission)
      into employ_dept
           values((Employeeid,lname,fname,deptid,supervision)
      select Employeeid,lname,fname,salary,commission,deptid,supervision
      from employee where salary>5000 or deptid<>40
通用格式（有条件）.eg
      insert all
      when salsry >5000 then
      into employee_salary
           values(Employeeid,lname,fname,salary,commission)
      when deptid<>40then
      into employ_dept
           values((Employeeid,lname,fname,deptid,supervision)
      select Employeeid,lname,fname,salary,commission,deptid,supervision
      from employee where salary>5000 or deptid<>40
4.有条件的insert first,
   同有条件的insert all 不同的是，如果一行同时满足2个条件salary>5000和deptid<40>,则只将这条

数据插入第一张表中
5.使用子查询更新语句
  通用格式
      update tablename
        set columnname = value or expression
        where columnname operator
        (select subquery)
     eg.略

     update tablename
      set (columnname) =( select subquery)
[where condition]
 eg.
   update employee
   set (positionid,supervisior,deptid)=
    (select positionid,supervisior,deptid
     from employee where employeeid =135
    )
where employeeid = 200

6.获取已建序列信息
 select * from user_sequences
获取序列当前值
 select SEQ_BUSINESS.Currval from dual
获取序列下一个值
 select SEQ_BUSINESS.NEXTVAL from dual
7.查询索引信息
  select * from user_indexes