oracle 中子查询的常用法

比眉伴天荒 2022-08-24 04:07 212阅读 0赞

子查询：  
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