数据库约束和视图问题

数据库约束和视图问题 [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

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