目录
1.视图
1.1 什么是视图
视图通过以定制的方式显示来自一个或多个表的数据;
视图是一种数据库对象,用户可以像查询普通表一样查询视图;
视图内其实没有存储任何数据,它只是对表的一个查询;
视图的定义保存在数据字典内,创建视图所基于对表称为“基表”。
1.2 为什么需要视图
例如经常要对emp和dept表进行连接查询,每次都要做表的连接,写同样的一串语句,同时由于工
资列队数据比较敏感,对外要求不可见。对这样的问题就可以通过视图来解决。
1.3 视图的作用和优点
作用:
-
控制安全
-
保存查询数据
优点:
-
提供了灵活一致级别安全性。
-
隐藏了数据的复杂性
-
简化了用户的SQL指令
-
通过重命名列,从另一个角度提供数据
1.4 创建视图
CREATE [OR REPLACE] VIEW 视图名
[(alias[ , alias]...)] -- 为视图字段指定别名
AS subquery
[WITH READ ONLY];
例如: 创建视图,EMP_V_10 , 包括10号部门的所有雇员信息。
CREATE VIEW emp_v_10
AS SELECT employee_id, name, salary
FROM employees
WHERE deptt_id = 10;
1.5 视图的使用规则
-
视图必须有唯一命名
-
在mysql中视图的数量没有限制
-
创建视图必须从管理员那里获得必要的权限
-
视图支持嵌套,也就是说可以利用其他视图检索出来的数据创建新的视图
-
在视图中可以使用OREDR BY,但是如果视图内已经使用该排序子句,则视图的ORDER BY将覆盖前面的 ORDER BY。
-
视图不能索引,也不能关联触发器或默认值
-
视图可以和表同时使用
1.6 修改视图
使用CREATE OR REPLACE VIEW 语句修改EMP_V_10 视图.,为每个列指定列名:
CREATE OR REPLACE VIEW emp_v_10
(id, name, sal, dept_id)
AS SELECT id,name,
salary, dept_id
FROM employees
WHERE dept_id = 10;
使用ALTER VIEW 语句修改EMP_V_10 视图.,为每个列指定列名:
ALTER VIEW emp_v_10
(id, name, sal, dept_id)
AS SELECT id,name,
salary, dept_id
FROM employees
WHERE dept_id = 10;
在CREATE VIEW 语句中字段与子查询中的字段必须一一对应,否则就别指定别名,或在子查询中指定别名
1.7 删除视图
删掉视图不会导致数据的丢失,因为视图是基于数据库的表之上的一个查询定义。
DROP VIEW view_name;
1.8 案例演示
1. 在数据库example下创建college表,College表内容如下所示:
字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
number | 学号 | int(10) | 是 | 否 | 是 | 是 | 否 |
name | 姓名 | varchar(20) | 否 | 否 | 是 | 否 | 否 |
major | 专业 | varchar(20) | 否 | 否 | 是 | 否 | 否 |
age | 年龄 | int(5) | 否 | 否 | 否 | 否 | 否 |
CREATE TABLE college(
number INT(10) NOT NULL UNIQUE PRIMARY KEY COMMENT '学号',
name VARCHAR(20) NOT NULL COMMENT '姓名',
major VARCHAR(20) NOT NULL COMMENT '专业',
age INT(5) COMMENT '年龄'
);
2. 在student表上创建视图college_view,视图的字段包括student_num、student_name、
student_age和department。ALGORITHM设置为MERGE类型,并且为视图加上WITH LOCAL CHECK OPTION条件:
CREATE ALGORITH=MERGE VIEW
college_view(student_num,student_name,student_age,department)
AS SELECT number,name,age,major FROM college
WITH LOCAL CHECK OPTION;
3. 查看视图college_view的详细结构:
SHOW CREATE VIEW college_view;
4. 更新视图,向视图中插入3条记录。记录内容如下表所示:
umer | name | major | age |
0901 | 张三 | 外语 | 20 |
0902 | 李四 | 计算机 | 22 |
0903 | 王五 | 计算机 | 19 |
INSERT INTO college_view VALUES(0901,'张三',20,'外语');
INSERT INTO college_view VALUES(0902,'李四',22,'计算机');
INSERT INTO college_view VALUES(0903,'王五',19,'计算机');
5 . 修改视图,使其显示专业为计算机的信息,其他条件不变:
方法一:
CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW
college_view(student_num,student_name,student_age,department)
AS SELECT number,name,age,major
FROM college WHERE major=’计算机’
WITH LOCAL CHECK OPTION;
方法二:
ALTER ALGORITHM=UNDEFINED VIEW
college_view(student_num,student_name,student_age,department)
AS SELECT number,name,age,major
FROM college WHERE major=’计算机’
WITH LOCAL CHECK OPTION;
6 . 删除视图college_view:
DROP VIEW college_view;