4.约束&amp;&amp;视图

2024-07-21 02:53:05

字体：大中小

来源：转载

供稿：网友

什么是约束

约束是表级的强制规定

有以下五种约束: - 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.

上一篇：位图索引BitMap

下一篇：EXP-00011: XX.tab does not exist