第七章使用各种操作Union/UNION ALL/MINUS/INTERSECT union的使用:将查询的重复结果去除 select employee_id,job_id from employees union select employee_id,job_id from job_history; union all的使用:(包括所有的重复数据) select employee_id,job_id ,deparment_id from employees union all select employee_id,job_id,deparment_id from job_history order by employee_id; INTERSECT的使用:(返回公共的重复数据) select employee_id,job_id ,deparment_id from employees intersect select employee_id,job_id,deparment_id from job_history order by employee_id; minus的使用:(返回第一个查询中在第二查询没有的数据信息) select employee_id,job_id ,deparment_id from employees minus select employee_id,job_id,deparment_id from job_history order by employee_id; 备注:1.重复数据将被自动去除,除了union all.第八章:数据操作 insert ,delete,update,select,truncate 复制表中的数据 insert into test select * from test_1 where 1=1; 数据库事物(DCL) 第九章使用DDL语句创建和管理表各种约束: 主键约束 列级别: create table xxx (id number(8) constraint xxx_id_pk primary key, hire_date date default sysdate); 表级别: create table xxx (id number(8) , hire_date date default sysdate, constraint xxx_id_pk primary key(id)); 唯一约束: create table xxx (id number(8) , email varchar(30), hire_date date default sysdate, deptno number, salary number(2), constraint emp_salary_min check(salary>0), constraint xxx_id_fk foreign key(deptno) ---外键约束 references departments(deptno), constraint xxx_email_uk unique(email)); ---唯一约束 例如: CREATE TABLE employees ( employee_id NUMBER(6) CONSTRAINT emp_employee_id PRIMARY KEY , first_name VARCHAR2(20) , last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL , email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL CONSTRAINT emp_email_uk UNIQUE , phone_number VARCHAR2(20) , hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL , job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL , salary NUMBER(8,2) CONSTRAINT emp_salary_ck CHECK (salary>0) , commission_pct NUMBER(2,2) , manager_id NUMBER(6) , department_id NUMBER(4) CONSTRAINT emp_dept_fk REFERENCES departments (department_id));
?