Oracle 10g SQL Fundamentals I(学习笔记二第7-8章)

第七章使用各种操作Union/UNION ALL/MINUS/INTERSECTunion的使用:将查询的重复结果去除   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));

?