集合操作

集合操作
union 并集 去除重复值
union all 也是并集 不去除重复值
例：
select employee_id, job_id from employees
union all
select employee_id, job_id from job_history;

select employee_id, job_id from employees
union
select employee_id, job_id from job_history;

intersect 交集
select employee_id, job_id from employees
intersect
select employee_id, job_id from job_history;

minus 差集
select employee_id from employees
minus
select employee_id from job_history;

select employee_id, job_id, salary from employees
union all
select employee_id, job_id, null from job_history;
// 如果不能提供这个值 就用空值代替

集合排序：
select employee_id, job_id, salary from employees
union all
select employee_id, job_id, null from job_history
order by salary;
//order by只写一个 写在最后一个后面
select employee_id, job_id, null from job_history
union all
select employee_id, job_id, salary from employees
order by 3;
如果没有名字就用第几列或者用别名

DML
insert:
SQL> create table t1(x int, y char(1), z date);
SQL> insert into t1(x, y, z) values (1, 'a', sysdate);
SQL> insert into t1(x, z, y) values (2, sysdate+1, 'b');
SQL> insert into t1(x, y, z) values (1, null, sysdate);
SQL> insert into t1(x, z) values (2, sysdate+1);
SQL> insert into t1 values (1, null, sysdate)
//insert 定义列 列的声明 values 后面加列的值

create table my_emp as select * from employees; //复制表 只复制了数据和基本结构 没有附加结构
SQL> create table my_emp as select * from employees where 1=0;// 一张空的表 复制表结构 后面where 1=0 不可能实现所以查询的是空的

 

update 更新
SQL> update my_emp set salary=salary*1.1;
SQL> update my_emp set salary=salary*1.1 where department_id=50;
SQL> update my_emp set salary=salary*1.1, commission_pct=0.5 where employee_id=197;
//定位行 更新列

在new_dept表中删除没有员工的部门
SQL> create table my_dept as select * from departments;
delete from my_dept outer
where not exists
(select 1 from my_emp
where department_id=outer.department_id);

 

delete和truncate： delete truncate
语句类型 dml ddl
undo数据 产生大量undo数据 不产生undo数据
空间管理 不释放 释放
语法 where 删除全部数据