数据库约束和视图问题(6)
来源：未知 责任编辑：责任编辑 发表时间:2014-05-26 11:02 点击:次
)
alter table f_cust
add constraint fk_f_cust foreign key(address_id) references F_ADDRESS
(address_id)
(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
"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
sum(salary) sums,count(salary) counts
from employees e,departments d
where e.department_id=d.department_id
group by d.department_name
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>
