23.生产环境中海量数据的管理

1.子查询操作数据

--使用嵌入式视图查询数据
hr@ORCLPDB01 2023-02-26 17:23:39> select deparmetn_name,city
  2  from departments
  3  natural join ( select l.location_id,l.city, l.country_id
  4                 from loc l
  5                 join countries c
  6                 on ( l.country_id = c.country_id )
  7                 join regions using ( region_id)
  8                 where rgion_name = 'Europe');

--从其他表赋值数据
INSERT INTO  (SELECT 1.location_id, l.city, l.country_id
              FROM locations 1
              JOIN countries c
              ON(l.country_id = c.country_id) JOIN regions USING(region id)
			  WHERE region name = 'Europe') 
VALUES (3300，'Cardiff','uK') ;
----建议在value中使用默认值
----用户能灵活得空值默认值在那出现何时出现
----显示默认值能使用在insert，update
insert into deptm3 (department_id,department_name,manager_id) values(300,'Engineering',DEFAULT);
update deptm3 set manager_id = DEFAULT where department_id = 10;
----使用insert子查询复制行
insert into sales_reps(id,name,salary,commission_pct) select employee_id,last_name,salary,commission_pct from employees where job_id like '%REP%'; 

----insert all
----insert first
----insert 旋转

--使用来自某个表值得结果去更新一个表中的数据
----merge语法
--使用来自某个表值得结果去删除一个表得行记录

2.闪回版本查询