4.约束&&视图

来源：互联网发布：淘宝长连接转换短连接编辑：程序博客网时间：2024/05/16 14:35

什么是约束

约束是表级的强制规定

有以下五种约束:
- 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 emp2ADD 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 employeesorder by salary desc;

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

select rownum,employee_id,last_name,salary from employeesorder 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      1150010 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       69009 rows selected.

0 0