SQL基础：操纵海量数据集

操纵海量数据集目标通过本章学习，您将可以学会以下内容:使用子查询操纵数据描述多表插入的特点使用不同类型的多表插入无条件的INSERT旋转INSERT有条件的ALLINSERT有条件的

FIRSTINSERT将表中数据和并跟踪在一段时间内更改的数据使用子查询操纵数据可以在DML语句中使用以下子查询:从其它表中拷贝数据从内敛视图中检索数据在UPDATE

中使用子查询，使更新基于另一个表中的数据在DELETE中使用子查询，使删除基于另一个表中的数据从其它表中拷贝数据在INSERT语句中加入子查询.

不必书写VALUES子句.子查询中的值列表应于INSERT

子句中的列名对应.INSERTINTOsales_reps(id,name,salary,commission_pct)SELECTemployee_id,last_name,salary,commission_pctFROMemployeesWHEREjob_idLIKE'%REP%';4rowscreated.在INSERT语句中使用子查询INSERTINTO(SELECTemployee_id,last_name,email,hire_date,job_id,salary,department_idFROMempl3WHEREdepartment_id=50)VALUES(99999,'Taylor','DTAYLOR',TO_DATE('07-JUN-99','DD-MON-RR'),'ST_CLERK',5000,50);1rowcreated.在INSERT语句中使用子查询验证结果：SELECTemployee_id,last_name,email,hire_date,job_id,salary,department_idFROMemployeesWHEREdepartment_id=50;…SELECTa.last_name,a.salary,a.department_id,b.salavgFROMemployeesa,(SELECTdepartment_id,AVG(salary)salavgFROMemployeesGROUPBYdepartment_id)bWHEREa.department_id=b.department_idANDa.salary>b.salavg;使用子查询检索数据…UPDATEempl3SETjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=205),salary=(SELECTsalaryFROMemployeesWHEREemployee_id=205)WHEREemployee_id=114;1rowupdated.在UPDATE语句中使用子查询更新114号员工的工作和工资使其与205号员工相同.UPDATEempl3SETdepartment_id=(SELECTdepartment_idFROMemployeesWHEREemployee_id=100)WHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=200);1rowupdated.基于另一个表更新数据在UPDATE

中使用子查询，使更新基于另一个表中的数据.DELETEFROMempl3WHEREdepartment_id=(SELECTdepartment_idFROMdepartmentsWHEREdepartment_nameLIKE'%Public%');1rowdeleted.基于另个一表删除数据在DELETE中使用子查询，使删除基于另一个表中的数据。在DML语句中使用WITHCHECKOPTION

关键字使用子查询表示DML语句中使用的表.WITHCHECKOPTION

关键字避免修改子查询范围外的数据.INSERTINTO(SELECTemployee_id,last_name,email,hire_date,job_id,salaryFROMempl3WHEREdepartment_id=50

WITHCHECKOPTION)VALUES(99998,'Smith','JSMITH',TO_DATE('07-JUN-99','DD-MON-RR'),'ST_CLERK',5000);INSERTINTO*ERRORatline1:ORA-01402:viewWITHCHECKOPTIONwhere-clauseviolation显式默认值使用DEFAULT

关键字表示默认值显式默认值符合SQL1999标准.可以使用显示默认值控制默认值的使用显示默认值可以在INSERT

和UPDATE

语句中使用显示使用默认值在插入操作中使用默认值:在更新操作中使用默认值:INSERTINTOdeptm3(department_id,department_name,manager_id)VALUES(300,'Engineering',DEFAULT);UPDATEdeptm3SETmanager_id=DEFAULT

WHEREdepartment_id=10;多表INSERT

语句INSERTALL

INTOtable_aVALUES(…,…,…)

INTOtable_bVALUES(…,…,…)

INTOtable_cVALUES(…,…,…)

SELECT…FROMsourcetabWHERE…;Table_aTable_bTable_c多表INSERT

语句INSERT...SELECT

是使用一个DML语句向多个表中插入数据的一部分.多表INSERT

语句可作为数据仓库应用中向目标数据库传送数据的一种方法.它具有更高的效率:使用一个DML进行多表插入INSERT…SELECT

语句使用一个DML完成IF...THEN

的逻辑处理多表INSERT

语句的类型多表INSERT语句类型:无条件的INSERT有条件的ALLINSERT有条件的FIRSTINSERT旋转INSERT多表INSERT

语句语法conditional_insert_clauseINSERT[ALL][conditional_insert_clause][insert_into_clausevalues_clause](subquery)[ALL][FIRST][WHENconditionTHEN][insert_into_clausevalues_clause][ELSE][insert_into_clausevalues_clause]无条件的INSERTALL从EMPLOYEES表中选择出EMPLOYEE_ID大于200的，EMPLOYEE_ID,HIRE_DATE,SALARY,和MANAGER_ID的值.使用多表插入将选择出来的值插入到SAL_HISTORY

和MGR_HISTORY表中.INSERTALL

INTOsal_historyVALUES(EMPID,HIREDATE,SAL)

INTOmgr_historyVALUES(EMPID,MGR,SAL)SELECTemployee_idEMPID,hire_dateHIREDATE,

salarySAL,manager_idMGRFROMemployees

WHEREemployee_id>200;8rowscreated.

有条件的INSERTALL从EMPLOYEES表中选择出EMPLOYEE_ID大于200的，EMPLOYEE_ID,HIRE_DATE,SALARY,和MANAGER_ID的值.使用有条件的多表插入语句将工资SALARY大于$10,000的员工信息插入到SAL_HISTORY表中.使用有条件的多表插入语句将MANAGER_ID大于200的员工信息插入到MGR_HISTORY

表中.有条件的INSERTALLINSERTALL

WHENSAL>10000THEN

INTOsal_historyVALUES(EMPID,HIREDATE,SAL)

WHENMGR>200THEN

INTOmgr_historyVALUES(EMPID,MGR,SAL)

SELECTemployee_idEMPID,hire_dateHIREDATE,salarySAL,manager_idMGRFROMemployees

WHEREemployee_id>200;4rowscreated.

有条件的FIRSTINSERT从EMPLOYEES表中选择出

DEPARTMENT_ID,SUM(SALARY),andMAX(HIRE_DATE)信息.如果SUM(SALARY)大于$25,000,使用有条件的

FIRST

INSERT条件将值插入到SPECIAL_SAL表中.如果第一个

WHEN

子句是真的,那么后面的

WHEN子句跳过不执行.如果第一个

WHEN

不满足条件，使用多表插入将基于

HIRE_DATE的值插入到HIREDATE_HISTORY_00,HIREDATE_HISTORY_99,或者

HIREDATE_HISTORY

表中.有条件的INSERTFIRST

INSERTFIRST

WHENSAL>25000THEN

INTOspecial_salVALUES(DEPTID,SAL)

WHENHIREDATElike('%00%')THEN

INTOhiredate_history_00VALUES(DEPTID,HIREDATE)

WHENHIREDATElike('%99%')THEN

INTO

hiredate_history_99

VALUES(DEPTID,

HIREDATE)

ELSE

INTOhiredate_historyVALUES(DEPTID,HIREDATE)SELECTdepartment_idDEPTID,SUM(salary)SAL,

MAX(hire_date)HIREDATE

FROMemployeesGROUPBYdepartment_id;8rowscreated.旋转INSERTSupposeyoureceiveasetofsalesrecordsfromanonrelationaldatabasetable,SALES_SOURCE_DATA,inthefollowingformat: EMPLOYEE_ID,WEEK_ID,SALES_MON,SALES_TUE, SALES_WED,SALES_THUR,SALES_FRIYouwanttostoretheserecordsintheSALES_INFOtableinamoretypicalrelationalformat:EMPLOYEE_ID,WEEK,SALESUsingapivotingINSERT,convertthesetofsalesrecordsfromthenonrelationaldatabasetabletorelationalformat.旋转INSERT

INSERTALL

INTOsales_infoVALUES(employee_id,week_id,sales_MON)

INTOsales_infoVALUES(employee_id,week_id,sales_TUE)

INTOsales_infoVALUES(employee_id,week_id,sales_WED)

INTOsales_infoVALUES(employee_id,week_id,sales_THUR)

INTOsales_infoVALUES(employee_id,week_id,sales_FRI)

SELECTEMPLOYEE_ID,week_id,sales_MON,sales_TUE,

sales_WED,sales_THUR,sales_FRI

FROMsales_source_data;5rowscreated.

合并语句按照指定的条件执行插入或更新操作如果满足条件的行存在，执行更新操作；否则执行插入操作:避免多次重复执行插入和删除操作提高效率而且使用方便在数据仓库应用中经常使用合并语句的语法可以使用merge语句，根据指定的条件进行插入或更新操作MERGEINTOtable_name

table_aliasUSING(table|view|sub_query)aliasON(joincondition)WHENMATCHEDTHENUPDATESET

col1=col_val1,col2=col2_valWHENNOTMATCHEDTHENINSERT(column_list)VALUES(column_values);MERGEINTOempl3cUSINGemployeeseON(c.employee_id=e.employee_id)WHENMATCHEDTHENUPDATESETc.first_name=e.first_name,c.last_name=e.last_name,...c.department_id=e.department_idWHENNOTMATCHEDTHENINSERTVALUES(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.hire_date,e.job_id,e.salary,mission_pct,e.manager_id,e.department_id);合并行对表EMPL3使用merge语句，根据指定的条件从表

EMPLOYEES中插入或更新数据.合并语句举例MERGEINTOempl3cUSINGemployeeseON(c.employee_id=e.employee_id)WHENMATCHEDTHENUPDATESET...WHENN