Oracle 10g SQL Fundamentals II(学习笔记二第3-4章)

第三章 大数据集合    从一个表中拷贝数据insert into sales(id,name,salary,commission_pct)select employee_id,last_name,salaryfrom employeeswhere job_id like '%REP%';将子查询作为insert的目标INSERT INTO(SELECT employee_id, last_name,email, hire_date, job_id, salary, department_id FROM   empl3 WHERE  department_id = 50) VALUES (99999, 'Taylor', 'DTAYLOR',TO_DATE('07-JUN-99', 'DD-MON-RR'),'ST_CLERK', 5000, 50);            获取数据使用子查询作为数据源            SELECT  a.last_name, a.salary,                     a.department_id, b.salavgFROM    employees a, (SELECT   department_id,   AVG(salary) salavg  FROM     employees  GROUP BY department_id) bWHERE   a.department_id = b.department_idAND     a.salary > b.salavg;    update emp   set job_id=(select job_id from employees               where employee_id=205   )   ,salary=( select salary from employees               where employee_id=205   )  where  employee_id=114;      INSERT ALL插入多个表的数据               语法INSERT  ALL  INTO table_a VALUES(…,…,…)  INTO table_b VALUES(…,…,…)  INTO table_c VALUES(…,…,…)     SELECT …    FROM  sourcetab      WHERE …;              例如一：  INSERT  ALLINTO sal_history VALUES(EMPID,HIREDATE,SAL)   INTO mgr_history VALUES(EMPID,MGR,SAL)  SELECT employee_id EMPID, hire_date HIREDATE,           salary SAL, manager_id MGR   FROM  employees     WHERE employee_id > 200;            例如二：  INSERT ALLWHEN SAL > 10000 THEN   INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR > 200   THEN    INTO mgr_history VALUES(EMPID,MGR,SAL)    SELECT employee_id EMPID,hire_date HIREDATE,  salary SAL, manager_id MGR   FROM   employees     WHERE  employee_id > 200;             实例三： INSERT FIRSTWHEN SAL  > 25000          THEN    INTO special_sal VALUES(DEPTID, SAL)  WHEN HIREDATE like ('%00%') THEN    INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)  WHEN HIREDATE like ('%99%') THEN    INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)  ELSE  INTO hiredate_history VALUES(DEPTID, HIREDATE)SELECT department_id DEPTID, SUM(salary) SAL,         MAX(hire_date) HIREDATE  FROM   employeesGROUP BY department_id;实例四：INSERT ALLINTO sales_info VALUES(employee_id,week_id,sales_MON)INTO sales_info VALUES(employee_id,week_id,sales_TUE)INTO sales_info VALUES(employee_id,week_id,sales_WED)INTO sales_info VALUES(employee_id,week_id,sales_THUR)INTO sales_info VALUES(employee_id,week_id,sales_FRI)                     SELECT employe_id,week_id,sales_mon,sales_tue,sales_wed,sales_thur,sale_pri                     FROM sales_source_data;MERGE语句的使用：      语法：     MERGE INTO table_name table_alias  USING (table|view|sub_query) alias  ON (join condition)  WHEN MATCHED THENUPDATE SET col1 = col_val1,col2 = col2_val  WHEN NOT MATCHED THENINSERT (column_list)VALUES (column_values);                     例如：插入或者更新empl3表信息 MERGE INTO empl3  c  USING employees e  ON (c.employee_id = e.employee_id)WHEN MATCHED THEN  UPDATE SET c.first_name     = e.first_name, c.last_name      = e.last_name, ... c.department_id  = e.department_idWHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name,  e.email, e.phone_number, e.hire_date, e.job_id,  e.salary, e.commission_pct, e.manager_id,   e.department_id);             第四章分组相关的函数 语法： SELECT[column,] group_function(column)... FROMtable[WHEREcondition][GROUP BYgroup_by_expression][HAVING having_expression] [ORDER BYcolumn]; SELECT   department_id, job_id, SUM(salary),  COUNT(employee_id)FROM     employeesGROUP BY department_id, job_id ;SELECT   department_id, job_id, SUM(salary)FROM     employees  WHERE    department_id < 60GROUP BY ROLLUP(department_id, job_id);SELECT   department_id, job_id, SUM(salary)FROM     employees  WHERE    department_id < 60GROUP BY CUBE (department_id, job_id) ;SELECT   department_id DEPTID, job_id JOB,  SUM(salary), GROUPING(department_id) GRP_DEPT, GROUPING(job_id) GRP_JOBFROM     employeesWHERE    department_id < 50GROUP BY ROLLUP(department_id, job_id);SELECT   department_id, job_id,  manager_id,avg(salary)FROM     employeesGROUP BY GROUPING SETS((department_id,job_id), (job_id,manager_id));SELECT   department_id, job_id, manager_id,  SUM(salary)FROM     employees GROUP BY ROLLUP( department_id,(job_id, manager_id));SELECT   department_id, job_id, manager_id,  SUM(salary)FROM     employees  GROUP BY department_id, ROLLUP(job_id), CUBE(manager_id);

?