4.约束&&视图

什么是约束

约束是表级的强制规定

有以下五种约束:
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHECK

表级约束和列级约束

  • 作用范围:

    • ① 列级约束只能作用在一个列上
    • ② 表级约束可以作用在多个列上(当然表级约束也可以作用在一个列上)
  • 定义方式:
    列约束必须跟在列的定义后面,表约束不与列一起,而是单独定义。

非空(not null) 约束只能定义在列上

1.添加约束

alter table [表名] add constraint [约束别名] [约束类别(字段名)]

向表emp2的id列中添加PRIMARY KEY约束(my_emp_id_pk)

ALTER table emp2
ADD constraint my_emp_id_pk primary key(id);

2. 修改约束

alter table modify [字段名] [约束]

或者

alter table modify [字段名] [constraint] [别名] [约束]
– 例如:把 name 字段修改为 not null约束

alter table emp2 modify name not null;

  • 修改的时候增加别名

alter table emp2 modify salary constraint my_emp2_salary_n not null;

3.check约束

alter table [表名] add constraint [约束别名] check([约束条件])

例子:给表中添加约束,使得salary大于0

alter table emp2 add constraint emp2_salary_min check(salary>0);

4.删除约束

alter table [表名] drop constraint [约束别名]

5. 约束要注意的地方

  • 非空约束(not null)只能定义在列级
  • 唯一约束(unique)的列值可以为空
  • 外键(foreign key)引用的列起码要有一个唯一的约束

6.级联操作

当一个表进行操作的时候会影响和他有外检约束的另外一张表

  • ON DELETE CASCADE(级联删除): 当父表中的列被删除时,子表中相对应的列也被删除
  • ON DELETE SET NULL(级联置空): 子表中相应的列置空

假如表emp有一个外键dept_id连接到表deptdept_id

constraint dept_fk foreign key(dept_id) 
references dept(dept_id) 
on delete cascade;

在创建表的时候就设置

on delete cascade

on delete set null

视图

视图和子表类似,不同的是,对视图的操作会影响原表的内容

  • 视图是一种虚表。
  • 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
  • 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
  • 视图向用户提供基表数据的另一种表现形式

简单视图和复杂视图区别:

特性 简单视图 复杂视图
表的数量 一个 一个或多个
函数 没有
分组 没有
DML操作 可以 有时候可以

简单视图

创建视图

注意:
使用scott用户登陆的时候默认是没有创建视图权限的
解决方式:
1. 打开cmd
2. 使用system用户登录:sqlplus system/root@orcl
3. 赋权限:grant create view to scott;
4. 此时scott用户就获得了创建view的权限

实例:创建视图empview,来自于对employees的查询

create view empview as 
select employee_id,last_name,salary 
from employees 
where department_id=80

desc empview查看一下

SQL> desc empview
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------

 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 SALARY                                             NUMBER(8,2)

此时看到创建的视图符合我们的意图

修改视图

添加上or replace即刻

create or replace view empview as 
select employee_id,last_name,salary 
from employees 
where department_id=80

复杂视图

使用组合函数的查询创建的视图为复杂视图

实例:查询所有部门的平均工资,赋值给empview2

SQL> create view empview2 as 
select department_id,avg(salary) avg_salary 
from employees 
group by department_id;

使用权限

在创建一个view的时候后面加上with read only表示只读;
例如:

create view empview3 as 
select employee_id,last_name,salary 
from employees 
where department_id=80 
with read only;

此时进行uodate操作会提示错误

SQL> update empview3 set salary=8000 where last_name='Jhonson';
update empview3 set salary=8000 where last_name='Jhonson'
                    *
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view

删除视图

很简单
drop view [视图图名]

TOP-N分析

选取前n行的值,或者第n到m行之间的值

实例说明:
现在想选取出来工资前十名的人员的信息
创建一个empview4

SQL> create view empview4 as 
select employee_id,last_name,salary 
from employees
order by salary desc;

此时要想选择前十个,是无从下手的,这个时候就要借助rownum这个伪列
例如:

select rownum,employee_id,last_name,salary 
from employees
order by salary desc;

但是此时还不能够使用rownum来作为查询的条件,因为伪列是虚拟的

然后把上面的查询结果,作为一个新的表再次查询

select  rownum,employee_id,salary 
from (select employee_id,salary 
        from employees
        order by salary desc
      )
where rownum < 11;

输出:

    ROWNUM EMPLOYEE_ID     SALARY
---------- ----------- ----------
         1         100      24000
         2         101      17000
         3         102      17000
         4         145      14000
         5         146      13500
         6         201      13000
         7         205      12000
         8         147      12000
         9         108      12000
        10         168      11500

10 rows selected.

注意 :
对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据。
想要查询第40–第50的员工信息,只能再次嵌套一次,将rawnum作为一个真实的一列查询。

select * from(
 select  rownum rn,employee_id,salary 
from (select employee_id,salary 
        from employees
        order by salary desc
      ) 
) where rn>40 and rn<50;

输出结果:

        RN EMPLOYEE_ID     SALARY
---------- ----------- ----------
        41         154       7500
        42         171       7400
        43         172       7300
        44         164       7200
        45         179       7000
        46         161       7000
        47         178       7000
        48         155       7000
        49         113       6900

9 rows selected.
IT文库 » 4.约束&&视图
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址