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连接到表dept的dept_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.