第三章 大数据集合 从一个表中拷贝数据 insert into sales(id,name,salary,commission_pct) select employee_id,last_name,salary from employees where 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.salavg FROM employees a, (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b WHERE a.department_id = b.department_id AND 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 ALL INTO 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 ALL WHEN 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 FIRST WHEN 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 employees GROUP BY department_id; 实例四： INSERT ALL INTO 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 THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED THEN INSERT (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_id WHEN 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)... FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING having_expression] [ORDER BY column]; SELECT department_id, job_id, SUM(salary), COUNT(employee_id) FROM employees GROUP BY department_id, job_id ; SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY ROLLUP(department_id, job_id); SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY CUBE (department_id, job_id) ; SELECT department_id DEPTID, job_id JOB, SUM(salary), GROUPING(department_id) GRP_DEPT, GROUPING(job_id) GRP_JOB FROM employees WHERE department_id < 50 GROUP BY ROLLUP(department_id, job_id); SELECT department_id, job_id, manager_id,avg(salary) FROM employees GROUP 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);?