数据库约束和视图问题

数据库约束和视图问题 [sql] --约束 --**************************************************************** --非空约束:(not null) * 确保字段值不允许为空 * 与其他约束相比是唯一只能在字段级定义 --在列级定义 create table EMPLOYEESNOTNULL ( EMPLOYEE_

数据库约束和视图问题

 

[sql] 

--约束  

--****************************************************************

--非空约束:(not null)  

   * 确保字段值不允许为空  

   * 与其他约束相比是唯一只能在字段级定义  

     

  --在列级定义   

  create table EMPLOYEESNOTNULL  

  (  

    EMPLOYEE_ID    NUMBER(6),  

    FIRST_NAME     VARCHAR2(20) not null,--在列级定义   

    LAST_NAME      VARCHAR2(25)  

  )  

    www.2cto.com  

  --在表的外部定义约束  

  create table EMPLOYEESNOTNULL_01  

  (  

    EMPLOYEE_ID    NUMBER(6),  

    FIRST_NAME     VARCHAR2(20),  

    LAST_NAME      VARCHAR2(25)  

  )  

    

  alter table EMPLOYEESNOTNULL_01  

  modify FIRST_NAME not null  

--******************************************************************************************  

--唯一性约束（UNIQUE）  

  * 唯一性约束条件确保所在的字段或者字段组合不出现重复值  

  * 唯一性约束条件的字段允许出现（1或多个）空值  

  * Oracle将为唯一性约束条件创建对应的唯一性索引  

       注:如果字段有值，要唯一，但空值可以出现多个  

  

--方法一 在列级定义  

  create table emp_un_01  

  (  

    EMPLOYEE_ID    NUMBER(6),  

    FIRST_NAME     VARCHAR2(20) unique,  --列级定义  

    LAST_NAME      VARCHAR2(25)  

  )  

    

--方法二  在表级定义约束  

   --在表级定义约束额语法格式  

      constraint  约束的名称    约束的类型(字段1,字段2)  

        * 约束的名称 自定义  

        * 约束的类型(unique,primary key)  

        * (字段1,字段2) 如果有多个字段，中间用,隔开  

          

 create table emp_un_02  

 (  

    EMPLOYEE_ID    NUMBER(6),  

    FIRST_NAME     VARCHAR2(20),  

    LAST_NAME      VARCHAR2(25),  

    constraint  un_emp_un_02  unique(first_name)  --在表级定义约束  

 )  

   www.2cto.com  

--方法三 在表的外部定义约束  

 --语法结构:  

   alter table table_name  

   add  constraint  约束的名称    约束的类型(字段1,字段2)  

        * 约束的名称 自定义  

        * 约束的类型(unique,primary key)  

        * (字段1,字段2) 如果有多个字段，中间用,隔开  

 create table emp_un_03  

 (  

    EMPLOYEE_ID    NUMBER(6),  

    FIRST_NAME     VARCHAR2(20),  

    LAST_NAME      VARCHAR2(25)  

 )  

   

 alter table emp_un_03  

 add constraint  un_emp_un_03  unique(first_name)  

  www.2cto.com  

 --方法四（在表级定义联合唯一）  

 create table emp_un_04  

 (  

    EMPLOYEE_ID    NUMBER(6),  

    FIRST_NAME     VARCHAR2(20),  

    LAST_NAME      VARCHAR2(25),  

    constraint  un_emp_un_04  unique(first_name,LAST_NAME)  --在表级定义约束  

 )  

   

--方法五(在表的外部定义)  

 create table emp_un_05  

 (  

    EMPLOYEE_ID    NUMBER(6),  

    FIRST_NAME     VARCHAR2(20),  

    LAST_NAME      VARCHAR2(25)  

 )  

    www.2cto.com  

 alter table  emp_un_05  

 add constraint  un_emp_un_05  unique(first_name,LAST_NAME)  

  

--******************************************************************************************  

--主键约束（ PRIMARY KEY）  

  * 主键从功能上看相当于非空且唯一  

  * 一个表中只允许一个主键  

  * 主键是表中能够唯一确定一个行数据的字段  

  * 主键字段可以是单字段或者是多字段的组合  

  * Oracle为主键创建对应的唯一性索引  

    

--方法一 在列级定义  

 create table emp_pk_01  

 (  

    EMPLOYEE_ID    NUMBER(6) primary key,  

    FIRST_NAME     VARCHAR2(20),  

    LAST_NAME      VARCHAR2(25)  

 )  

   

 --方法二 在表级定义  

 create table emp_pk_02  

 (  

    EMPLOYEE_ID    NUMBER(6),  

    FIRST_NAME     VARCHAR2(20),  

    LAST_NAME      VARCHAR2(25),  

    constraint pk_emp_pk_02 primary key(EMPLOYEE_ID)  

 )  

    www.2cto.com  

--方法三  在外部定义  

 create table emp_pk_03  

 (  

    EMPLOYEE_ID    NUMBER(6),  

    FIRST_NAME     VARCHAR2(20),  

    LAST_NAME      VARCHAR2(25)  

 )  

 alter table emp_pk_03  

 add constraint pk_emp_pk_03 primary key(EMPLOYEE_ID)  

  

--方法四(联合主键) 在表级定义  

--账号表  

create table account_01  

(  

  accounid varchar2(18) primary key,  --账号  

  balance  number(10,2)    --余额  

)  

  

--存款信息表  

create table inaccount_01  

(  

  accounid   varchar2(18),    --账号  

  inbalance  number(10,2),    --存入金额  

  indate     timestamp,        --存款时间  

  constraint pk_inaccount_01 primary key(accounid,indate)  

)  

  

insert into inaccount_01(accounid,inbalance,indate) values('1111',12,sysdate);  

insert into inaccount(accounid,inbalance,indate) values('1111',10,sysdate);  

   www.2cto.com  

--方法五 ，在外部定义  

create table account_02  

(  

  accounid varchar2(18) primary key,  --账号  

  balance  number(10,2)    --余额  

)  

  

--存款信息表  

create table inaccount_02  

(  

  accounid   varchar2(18),    --账号  

  inbalance  number(10,2),    --存入金额  

  indate     timestamp        --存款时间  

)  

  

alter table inaccount_02  

add constraint pk_inaccount_02 primary key(accounid,indate)  

--**********************************************************************************

--外键约束（ FOREIGN KEY）  

 * 外键是构建于一个表的两个字段或者两个表的两个字段之间的关系  

 * 外键确保了相关的两个字段的关系：  

 * 子表外键列的值必须在主表参照列值的范围内，或者为空  

 * 主表主键值被子表参照时，主表记录不允许被删除  

 * 外键约束条件参照的是主表的一个或者多个字段的值，通常被外键参照的 是
主表的主键或者唯一键  

  

 --在表级定义外键约束语法格式  

      constraint  约束的名称  foreign key(外键字段)  references 表(主键)  

        * 约束的名称 自定义  

        * 约束的类型(foreign key)  

        * references 表(主键)  参照表的字段 一般为主表的主键  

    www.2cto.com  

--方法一 在表级定义  

create table deptfk_01  

(  

  DEPARTMENT_ID   NUMBER(4) primary key,  

  DEPARTMENT_NAME VARCHAR2(30),  

  MANAGER_ID      NUMBER(6),  

  LOCATION_ID     NUMBER(4)  

)  

create table EMPFK_01  

(  

  EMPLOYEE_ID    NUMBER(6) ,  

  FIRST_NAME     VARCHAR2(20),  

  DEPARTMENT_ID  NUMBER(4),  

  constraint  fk_EMPFK_01  foreign key(DEPARTMENT_ID)  references deptfk_01
(DEPARTMENT_ID)  

)  

    www.2cto.com  

--方法二 在表的外部定义  

--在外部定义外键约束的语法格式  

   alter table table_name  

   add constraint  约束的名称  foreign key(外键字段)  references 表(主键)  

        * 约束的名称 自定义  

        * 约束的类型(foreign key)  

        * references 表(主键)  参照表的字段 一般为主表的主键  

          

create table deptfk_02  

(  

  DEPARTMENT_ID   NUMBER(4) primary key,  

  DEPARTMENT_NAME VARCHAR2(30),  

  MANAGER_ID      NUMBER(6),  

  LOCATION_ID     NUMBER(4)  

)  

  

create table EMPFK_02  

(  

  EMPLOYEE_ID    NUMBER(6) ,  

  FIRST_NAME     VARCHAR2(20),  

  DEPARTMENT_ID  NUMBER(4)  

)  

  

alter table EMPFK_02  

add constraint  fk_EMPFK_02  foreign key(DEPARTMENT_ID)  references deptfk_02
(DEPARTMENT_ID)  

  

--方法三(主外键作用于一个表的两个字段)  

create table emp_two  

(  

  EMPLOYEE_ID    NUMBER(6) primary key,  

  FIRST_NAME     VARCHAR2(20),  

  MANAGER_ID     NUMBER(6)    --外键  

)  

    www.2cto.com  

alter table emp_two  

add constraint  fk_emp_two  foreign key(MANAGER_ID)  references emp_two
(EMPLOYEE_ID)  

--************************************************************************************

--check约束  

 * Check约束条件是一种比较特殊的约束条件，通过check定义，  

 * 强制定义在字段上的每一记录都要满足check中定义的条件。  

 * 在check中定义检查的条件表达式，进入表中的数据必须符合check中设置的条件  

      

create table empck  

(  

  EMPLOYEE_ID    NUMBER(6) primary key,  

  FIRST_NAME     VARCHAR2(20),  

  SALARY         NUMBER(8,2)    -->6000  

)  

  

alter table empck  

add constraint  ck_empck  check(salary>6000)  

--************************************************************************************

    www.2cto.com  

--删除约束  

 * 删除约束条件对于表和数据不会产生影响  

  

 * 删除约束emp_manager_fk  

      ALTER TABLE  employees  

      DROP CONSTRAINT  emp_manager_fk;  

        

  --删除ck_empck     

  alter table empck  

  drop constraint ck_empck  

--  www.2cto.com  ******************************************************************

--约束的应用案例:  

create table  F_ADDRESS  

(  

   address_id       number(6) primary key,  

   province_name    varchar2(20),  

   city_name        varchar2(20),  

   district_name    varchar2(20),  

   street_name      varchar2(20),  

   street_nbr       varchar2(20),  

   detail           varchar2(20),  

   postcode         varchar2(10)  

)  

    www.2cto.com  

create table f_cust  

(  

  cust_id  number(6) primary key,  

  cust_name  varchar(50),  

  address_id  number(6),  

  state  varchar(10)  

)  

  

alter table f_cust  

add constraint fk_f_cust  foreign key(address_id) references F_ADDRESS
(address_id)  

  

alter table f_cust  

add constraint ck_f_cust  check(state in('在用','作废'))  

  

--定义约束f_cust中 cust_name唯一  

alter table f_cust  

add constraint un_f_cust   unique(cust_name)  

  

--删除约束  

alter table f_cust  

drop constraint un_f_cust  

    www.2cto.com  

--**************************************************************************************

--视图:     --为sql语句起的别名  给予表之上的一个查询语句  

--语法:  

--在CREATE VIEW语句后加入子查询.  

     CREATE [OR REPLACE] VIEW view_name  

     [(alias[, alias]...)]   

     AS subquery  

     [WITH READ ONLY];  

  

   create  or replace view v_emp  

   as  

   select * from employees  

     

   --查询视图  

   select * from v_emp;  

     

   --视图的作用  

      --* select 语句比较复杂  

      --* select语句在开发的程序中可能多次使用  

      --* 在程序中直接使用视图  select * from v_emp  

   create  or replace view v_emp  

   as  

   select "EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER",  

      "HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID",
"DEPARTMENT_ID" from employees  

       www.2cto.com  

   --描述视图的结构(命令行执行)  

     desc v_emp  

     describe v_emp  

         

    --创建复杂视图  

      create or replace view v_emp_dept  

      as  

      select  d.department_name,min(salary) mins,max(salary) mass,avg(salary) avgs,
sum(salary) sums,count(salary) counts  

      from employees e,departments d  

      where e.department_id=d.department_id  

      group by d.department_name  

        

      --查询视图    www.2cto.com  

      select * from v_emp_dept  

        

      --通过视图插入数据到表中  

      create or replace view  v_dept  

      as  

      select deptno,dname,loc from dept  

        

      --查询视图  

      select * from v_dept  

        

      --通过 v_dept视图插入数据到dept表中  

      insert into v_dept(deptno,dname,loc) values(89,'xxx','ss')  

        

        

      --通过设置WITH READ ONLY选项可以禁止对视图执行DML操作.  

      create or replace view  v_dept  

      as  

      select deptno,dname,loc from dept  

      with read only  

        

      --删除视图    www.2cto.com  

        --删掉视图不会导致数据的丢失，因为视图是基于数据库的表之上的一个查询定义.  

  

       DROP VIEW view_name;  

        --删除v_dept视图  

        drop view v_dept   

-******************************************************************************************