day48数据库视图和约束

二、视图

什么是视图：数据库中的表和视图都是其内部的对象，视图可以理解成一张虚拟的表，视图本质就是取代了一条SQL查询语句。
为什么使用视图：因为有些数据的查询需要书写大量的SQL语句，每次书写效率太低，使用视图可以起到SQL重用的作用，视图可以隐藏敏感信息

创建视图的格式：

create view 视图名 as 子查询; 
create table 表名 as 子查询

创建一个10号部门员工的视图

create view v_emp_10 as 
(select * from emp where deptno=10); 

-- 从视图中查询数据 
select * from v_emp_10;

创建一个没有工资的员工表视图

create view v_emp_nosal as 
(select empno,ename,comm,deptno from emp); 

-- 查询 
select * from v_emp_nosal

视图的分类

简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询。可以对视图中的数据进行增删改查操作
复杂视图：和简单视图相反，只能对视图中的数据进行查询操作

创建一个复杂视图

create view v_emp_info as 
(select avg(sal),max(sal),min(sal) from emp); 

-- 查询 
select * from v_emp_info;

对简单视图进行增删改查，操作方式和table一样

插入数据

insert into v_emp_10 (empno,ename,deptno) values
(10011,'刘备',10); 

select * from v_emp_10; 

select * from emp;

如果插入一条在视图中不可见，但是原表中却可见的数据称为数据污染。

insert into v_emp_10 (empno,ename,deptno) values
(10012,'关羽',20); 

select * from v_emp_10; 
select * from emp;

通过 with check option 解决数据污染问题

create view v_emp_20 as 
(select * from emp where deptno=20) 
with check option; 

insert into v_emp_20 (empno,ename,deptno) values
(10013,'赵云',20); //成功 

insert into v_emp_20 (empno,ename,deptno) values
(10014,'黄忠',30); //失败

修改和删除视图中的数据（只能修改删除视图中有的数据）

 update v_emp_20 set ename='赵云2' where ename='赵云'; update v_emp_20 set ename='刘备2' where ename='刘备';//修改失败 delete from v_emp_20 where deptno=10;//没有数据被删除

创建或替换视图

create or replace view v_emp_10 as 
(select * from emp where deptno=10 and sal>2000);

删除视图 drop view 视图名;
```
drop view v_emp_10;

show tables;
```

如果创建视图的子查询中使用了别名则对视图操作时只能使用别名

create view v_emp_10 as 
(select ename name from emp where deptno=10); 

select name from v_emp_10;//成功 
select ename from v_emp_10;//失败

视图总结：

视图是数据库中的对象，代表一段SQL语句，可以理解成一张虚拟的表
作用：重用SQL，隐藏敏感信息
分类：简单视图（创建视图时不使用去重、函数、分组、关联查询，可以对数据进行增删改查）和复杂视图（和简单视图相反，只能对数据进行查询操作）
插入数据时有可能出现数据污染，可以通过with check option解决
删除和修改只能操作视图中存在的数据
起了别名后只能用别名

三、约束

什么是约束：约束就是给表字段添加的限制条件

主键约束+自增 primary key auto_increment

作用：唯一且非空

非空约束 not null

作用：该字段的值不能为null

create table t_null(id int,age int not null
); insert into t_null values(1,18); //成功 

insert into t_null values(2,null);//失败

唯一约束 unique

作用：该字段的值不能重复

create table t_unique(id int,age int unique
); 

insert into t_unique values(1,28);//成功 
insert into t_unique values(2,28);//失败 不能重复

默认约束 default

作用：给字段设置默认值

create table t_default(id int,age int default 20
); 

insert into t_default (id) values(1); //默认值会生效 
insert into t_default values(2,null);//默认值不会生效 
insert into t_default values(3,30);//可以赋值其它值

外键约束

外键：用来建立关系的字段称为外键
外键约束：添加外键约束的字段，值可以为null，可以重复，但是值不能是关联表中不存在的数据，外键指向的数据不能先删除，外键指向的表也不能先删除
如何使用外键约束

创建部门表

create table dept(id int primary key auto_increment,name varchar(10)
);

创建员工表

create table emp(id int primary key auto_increment,name varchar(10),dept_id int,constraint fk_dept foreign key(dept_id) references dept(id)); -- 格式介绍：
constraint 约束名称 foreign key(外键字段名) references 依赖的表名(依赖的字段名)

-- 测试插入数据 
insert into dept values
(null,'神仙'),
(null,'妖怪'); 

insert into emp values
(null,'悟空',1); 

insert into emp values
(null,'赛亚人',3);//失败 

delete from dept where id=1;//失败 

drop table dept; //失败