当前位置:操作系统 > Unix/Linux >>

[每日一题] OCP1z0-047 :2013-07-28多表插入――pivoting insert(旋转插入)

[每日一题] OCP1z0-047 :2013-07-28多表插入――pivoting insert(旋转插入)
 
 
这道题目的知识点是要了解Oracle 中的Insert用法
A、pivoting insert(旋转插入)
1、创建表marks_details
[html] 
gyj@OCM> create table marks_details (  
  2  student_id number(4) not null,  
  3  subject_id1 number(2),  
  4  marks_english number(3),  
  5  subject_id2 number(2),  
  6  marks_math number(3),  
  7  subject_id3 number(2),  
  8  marks_physics number(3),  
  9  subject_id4 number(2),  
 10  marks_chemistry number(3),  
 11  subject_id5 number(2),  
 12  marks_biology number(3)  
 13   );  
  
Table created.  
 
2、向表marks_details中插入一行数据
[html] 
gyj@OCM> insert into marks_details values (1001,01,90,02,80,03,85,04,95,05,75);   
   
1 row created.  
  
gyj@OCM> commit;  
  
Commit complete.  
  
gyj@OCM> select * from  marks_details;  
  
STUDENT_ID SUBJECT_ID1 MARKS_ENGLISH SUBJECT_ID2 MARKS_MATH SUBJECT_ID3 MARKS_PHYSICS SUBJECT_ID4 MARKS_CHEMISTRY SUBJECT_ID5 MARKS_BIOLOGY  
---------- ----------- ------------- ----------- ---------- ----------- ------------- ----------- --------------- ----------- -------------  
      1001           1            90           2         80           3            85           4              95    5             75  
 
3、创建表marks
 
[html] 
gyj@OCM> create table marks (  
  2   studnet_id number(4) not null,  
  3   subject_id number(2),  
  4   marks number(3)  
  5   );  
  
Table created.  
 
4、现在要将marks_details表的数据插入到marks表中
[html] 
gyj@OCM> insert all  
  2   into marks values(student_id,subject_id1,marks_english)  
  3   into marks values(student_id,subject_id2,marks_math)  
  4   into marks values(student_id,subject_id3,marks_physics)  
  5   into marks values(student_id,subject_id4,marks_chemistry)  
  6   into marks values(student_id,subject_id5,marks_biology)  
  7   select student_id,subject_id1,marks_english,subject_id2,marks_math,subject_id3,  
  8   marks_physics,subject_id4,marks_chemistry,subject_id5,marks_biology  
  9   from marks_details;   
  
5 rows created.  
  
gyj@OCM> commit;  
  
Commit complete.  
  
gyj@OCM> select * from marks;  
  
STUDNET_ID SUBJECT_ID      MARKS  
---------- ---------- ----------  
      1001          1         90  
      1001          2         80  
      1001          3         85  
      1001          4         95  
          1001          5         75  
 
这道题目就是考什么是pivoting insert?
 
B、Unconditional INSERT(无条件Insert all多表多行插入)
接着上面的题,继续创建表
[html] 
gyj@OCM> create table marks_english (  
  2   studnet_id number(4) not null,  
  3   subject_id number(2),  
  4   marks number(3)  
  5   );  
  
Table created.  
  
gyj@OCM> create table marks_math (  
  2   studnet_id number(4) not null,  
  3   subject_id number(2),  
  4   marks number(3)  
  5   );  
  
  
create table marks_physics (  
Table created.  
  
gyj@OCM> gyj@OCM>   2   studnet_id number(4) not null,  
  3   subject_id number(2),  
  4   marks number(3)  
  5   );  
  
  
Table created.  
  
gyj@OCM> gyj@OCM> create table marks_chemistry (  
  2   studnet_id number(4) not null,  
  3   subject_id number(2),  
  4   marks number(3)  
  5   );  
  
  
Table created.  
  
gyj@OCM> gyj@OCM> create table marks_biology (  
  2   studnet_id number(4) not null,  
  3   subject_id number(2),  
  4   marks number(3)  
  5   );  
  
Table created.  
  
gyj@OCM> insert all  
  2  into marks_english values(student_id,subject_id1,MARKS_ENGLISH)  
  3  into marks_math values(student_id,subject_id2,MARKS_MATH)  
  4  into marks_physics values(student_id,subject_id3,MARKS_PHYSICS)  
  5  into marks_chemistry values(student_id,subject_id4,MARKS_CHEMISTRY)  
  6  into marks_biology values(student_id,subject_id5,MARKS_BIOLOGY)  
  7  select STUDENT_ID,SUBJECT_ID1,MARKS_ENGLISH,SUBJECT_ID2,MARKS_MATH,SUBJECT_ID3,MARKS_PHYSICS,SUBJECT_ID4,MARKS_CHEMISTRY,SUBJECT_ID5,MARKS_BIOLOGY  
  8  from marks_details;  
  
5 rows created.  
  
gyj@OCM> commit;  
  
Commit complete.  
  
gyj@OCM> select * from marks_english;  
  
STUDNET_ID SUBJECT_ID      MARKS  
---------- ---------- ----------  
      1001          1         90  
  
gyj@OCM> select * from marks_math;  
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,