操作简单化
增加数据的安全性
提高表的逻辑独立性
语法:
CREATE
[
OR REPLACE] [
ALGORITHM
= {
UNDEFINED
|
MERGE
|
TEMPTABLE}]
查看创建视图的权限
SELECT
Select_priv,Create_view_priv
FROM
mysql.user
WHERE USER='root';
+-------------+------------------+
| Select_priv | Create_view_priv |
+-------------+------------------+
| Y
| Y
|
| Y
| Y
|
| Y
| Y
|
| Y
| Y
|
| Y
| Y
|
| Y
| Y
|
+-------------+------------------+
6 rows in set (0.00 sec)
练习表(自己创建)
mysql> desc department;
+----------+-------------+------+-----+---------+-------+
| Field
| Type
| Null
| Key
| Default
| Extra |
+----------+-------------+------+-----+---------+-------+
| d_id
| int(4)
| NO
| PRI
| NULL
|
|
| d_name
| varchar(20) | NO
| UNI
| NULL
|
|
| function
| varchar(50) | YES
|
| NULL
|
|
| address
| varchar(50) | YES
|
| NULL
|
|
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> desc worker;
+-------------+-------------+------+-----+---------+-------+
| Field
| Type
| Null
| Key
| Default
| Extra
|
+-------------+-------------+------+-----+---------+-------+
| num
| int(10)
| NO
| PRI
| NULL
|
|
| d_id
| int(4)
| YES
| MUL | NULL
|
|
| NAME
| varchar(20)
| NO
|
| NULL
|
|
| sex
| varchar(4)
| NO
|
| NULL
|
|
| birthday
| datetime
| YES
|
| NULL
|
|
| homeaddress | varchar(50)
| YES
|
| NULL
|
|
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
单表创建
CREATE VIEW
department_view1
AS SELECT
*
FROM
department;
mysql> desc department_view1;
+----------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id
| int(4)
| NO
|
| NULL
|
|
| d_name
| varchar(20) | NO
|
| NULL
|
|
| function | varchar(50) | YES
|
| NULL
|
|
| address
| varchar(50) | YES
|
| NULL
|
|
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
CREATE VIEW
department_view2(NAME,funciton,location)
AS SELECT
d_name,FUNCTION,address
FROM
department;
desc department_view2;
+----------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name
| varchar(20) | NO
|
| NULL
|
|
| funciton | varchar(50) | YES
|
| NULL
|
|
| location | varchar(50) | YES
|
| NULL
|
|
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
多表创建视图
mysql>
CREATE ALGORITHM=
MERGE
VIEW
Query OK, 0 rows affected (0.01 sec)
mysql> desc worker_view1;
+------------+--------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| NAME
| varchar(20)
| NO
|
| NULL
|
|
| department | varchar(20)
| NO
|
| NULL
|
|
| sex
| varchar(4)
| NO
|
| NULL
|
|
| age
| double(23,6) | YES
|
| NULL
|
|
| address
| varchar(50)
| YES
|
| NULL
|
|
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
查看视图
mysql>
SHOW TABLE STATUS LIKE
'worker_view1' \G
*************************** 1. row ***************************
Max_data_length: NULL
1 row in set (0.00 sec)
mysql>
SHOW CREATE VIEW
worker_view1 \G
*************************** 1. row ***************************
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
在views表中查看视图详细信息
视图存在放information_schema数据库下的views表中
mysql>
SELECT
*
FROM
information_schema.VIEWS \G
*************************** 1. row ***************************
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
*************************** 2. row ***************************
COLLATION_CONNECTION: utf8_general_ci
.............................................
CREATE OR REPLACE VIEW 语句修改视图
存在修改,不存在创建
mysql>
CREATE OR REPLACE ALGORITHM=
TEMPTABLE
Query OK, 0 rows affected (0.01 sec)
ALTER语句修改视图
mysql>
ALTER VIEW
department_view2(department,NAME,sex,location)
Query OK, 0 rows affected (0.01 sec)
更新视图
mysql>
SELECT
*
FROM
department;
+------+-----------+--------------+-------------+
| d_id
| d_name
| function
| address
|
+------+-----------+--------------+-------------+
| 1001
| 人事部
| 人事变动
| 2号楼3层
|
| 1002
| 生产部
| 生产管理
| 5号楼1层
|
+------+-----------+--------------+-------------+
2 rows in set (0.00 sec)
CREATE VIEW
department_view3(NAME,FUNCTION,address)
AS SELECT
d_name,FUNCTION,address
FROM
department
WHERE
d_id=1001;
mysql>
SELECT
*
FROM
department_view3;
+-----------+--------------+-------------+
| NAME
| FUNCTION
| address
|
+-----------+--------------+-------------+
| 人事部
| 人事变动
| 2号楼3层
|
+-----------+--------------+-------------+
1 row in set (0.00 sec)
mysql>
UPDATE
department_view3
SET
NAME='科研部',FUNCTION='新产品研发',address='3号楼五层';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1
Changed: 1
Warnings: 0
mysql>
SELECT *
FROM department_view3;
+-----------+-----------------+---------------+
| NAME
| FUNCTION
| address
|
+-----------+-----------------+---------------+
| 科研部
| 新产品研发
| 3号楼五层
|
+-----------+-----------------+---------------+
1 row in set (0.00 sec)
不能更新视图:
视图中含SUM(),MAX(),MIN()等函数
CREATE VIEW
worker_view4(NAME,sex,total)
AS SELECT
NAME,sex,
COUNT(NAME)
FROM
worker;
Query OK, 0 rows affected (0.06 sec)
mysql>
UPDATE
worker_view4
SET
NAME='abc';
ERROR
1288 (HY000): The target table worker_view4 of the UPDATE is not updatable
视图中含UNION、UNION ALL、DISTINCT、GROUP BY、HAVING等关键字
CREATE VIEW
worker_view5(NAME,sex,address)
AS SELECT
NAME,sex,homeaddress
FROM
worker
GROUP BY
d_id;
Query OK, 0 rows affected (0.00 sec)
UPDATE
worker_view5
SET
NAME='abc';
ERROR
1288 (HY000): The target table worker_view5 of the UPDATE is not updatable
常量视图
CREATE VIEW
worker_view6
AS SELECT
'Aric'
AS
NAME;
Query OK, 0 rows affected (0.00 sec)
UPDATE
worker_view6
SET
NAME='abc';
ERROR
1288 (HY000): The target table worker_view6 of the UPDATE is not updatable
视图中的SELECT中包含子查询
CREATE VIEW
worker_view7(NAME)
AS SELECT
(
SELECT
NAME
FROM
worker);
Query OK, 0 rows affected (0.00 sec)
UPDATE
worker_view7
SET
NAME='abc';
ERROR
1288 (HY000): The target table worker_view7 of the UPDATE is not updatable
由不可更新的视图导出的视图
CREATE VIEW
worker_view8
AS SELECT
*
FROM
worker_view7;
Query OK, 0 rows affected (0.00 sec)
UPDATE
worker_view8
SET
NAME='abc';
ERROR
1288 (HY000): The target table worker_view8 of the UPDATE is not updatable
创建视图是,ALGORITHM为TEMPTABLE类型
CREATE ALGORITHM=
TEMPTABLE VIEW
worker_view9
AS SELECT
*
FROM
worker;
Query OK, 0 rows affected (0.01 sec)
UPDATE
worker_view9
SET
NAME='abc';
ERROR 1288 (HY000): The target table worker_view9 of the UPDATE is not updatable
视图的表上存在没有默认值得列,而且该列没有包含在视图里。
例如:表中包含name字段没有默认值,但是视图中不包括该字段。那么这个视图是不能更新的。
因为在更新时,这个没有默认值得记录将没有值插入,也没有NULL值插入。
删除视图
只能删视图的定义,不能删除数据,必须有drop权限
DROP VIEW [IF EXISTS] view_name [RESTRICT|CASCADE]
查看权限
mysql>
SELECT
Drop_priv
FROM
mysql.user
WHERE
USER='root';
+-----------+
| Drop_priv |
+-----------+
| Y
|
| Y
|
| Y
|
| Y
|
| Y
|
| Y
|
+-----------+
6 rows in set (0.00 sec)
删除视图
DROP VIEW IF EXISTS
worker_view1;
Query OK, 0 rows affected (0.00 sec)
删多个
DROP VIEW IF EXISTS
department_view1,department_view2;
Query OK, 0 rows affected (0.00 sec)
实例
创建work_info表
插入数据
创建视图info_view
查看视图info_view的基本机构和详细结构
查看视图info_view的所有记录
修改视图info_view
更新视图
删除视图
work_info表内容
字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
id | 编号 | INT(10) | 是 | 否 | 是 | 是 | 否 |
name | 姓名 | VARCHAR(20) | 否 | 否 | 是 | 否 | 否 |
sex | 性别 | VARCHAR(4) | 否 | 否 | 是 | 否 | 否 |
age | 年龄 | INT(5) | 否 | 否 | 否 | 否 | 否 |
address | 家庭住址 | VARCHAR(50) | 否 | 否 | 否 | 否 | 否 |
tel | 电话号码 | VARCHAR(20) | 否 | 否 | 否 | 否 | 否 |
work_info表中的信息
id | name | sex | age | address | tel |
1 | 张三 | M | 18 | 海淀 | 1234567 |
2 | 李四 | M | 22 | 昌平 | 2345678 |
3 | 王五 | F | 17 | 平谷 | 3456789 |
4 | 赵六 | F | 25 | 顺义 | 4567890 |
mysql>
CREATE TABLE
work_info(id
INT(10)
NOT NULL UNIQUE PRIMARY KEY,
Query OK, 0 rows affected (0.02 sec)
INSERT INTO
work_info
VALUES(1,'张三','M',18,'海淀','1234567');
INSERT INTO
work_info
VALUES(2,'李四','M',22,'昌平','2345678');
INSERT INTO
work_info
VALUES(3,'王五','F',17,'平谷','3456789');
INSERT INTO
work_info
VALUES(4,'赵六','F',25,'顺义','4567890');
创建视图info_view,从work_info表中选出age>20的记录来创建视图。视图的字段包含id、name、sex和address.ALGORITHM设置为MERGE类型加上WITH LOCAL CHECK OPTION条件
mysql>
CREATE ALGORITHM=MERGE VIEW
info_view(
Query OK, 0 rows affected (0.00 sec)
mysql>
SHOW CREATE VIEW
info_view \G
*************************** 1. row ***************************
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
mysql> SELECT * FROM info_view;
+----+--------+-----+---------+
| id
| NAME
| sex
| address |
+----+--------+-----+---------+
|
2
| 李四
| M
| 昌平
|
|
4
| 赵六
| F
| 顺义
|
+----+--------+-----+---------+
2 rows in set (0.00 sec)
修改视图,使其显示age<20的信息,其他条件不变。
ALTER ALGORITHM=MERGE VIEW
info_view(id,NAME,sex,address)
AS SELECT
id,NAME,sex,address
FROM
work_info WHERE age<20
WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql>
SELECT
*
FROM
info_view;
+----+--------+-----+---------+
| id | NAME
| sex | address |
+----+--------+-----+---------+
|
1 | 张三
| M
| 海淀
|
|
3 | 王五
| F
| 平谷
|
+----+--------+-----+---------+
2 rows in set (0.00 sec)
mysql>
UPDATE
info_view
SET
sex='M'
WHERE
id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1
Changed: 1
Warnings: 0
mysql>
SELECT
*
FROM
info_view;
+----+--------+-----+---------+
| id | NAME
| sex | address |
+----+--------+-----+---------+
|
1 | 张三
| M
| 海淀
|
|
3 | 王五
|
M | 平谷
|
+----+--------+-----+---------+
2 rows in set (0.01 sec)
mysql>
SELECT
*
FROM
work_info;
+----+--------+-----+------+---------+---------+
| id | NAME
| sex | age
| address | tel
|
+----+--------+-----+------+---------+---------+
|
1 | 张三
| M
|
18 | 海淀
| 1234567 |
|
2 | 李四
| M
|
22 | 昌平
| 2345678 |
|
3 | 王五
|
M
|
17 | 平谷
| 3456789 |
|
4 | 赵六
| F
|
25 | 顺义
| 4567890 |
+----+--------+-----+------+---------+---------+
4 rows in set (0.00 sec)
DROP VIEW IF EXISTS
info_view;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
SELECT
*
FROM
info_view;
ERROR 1146 (42S02): Table 'tianfan.info_view'
doesn't exist
实践
创建college表
在student表上创建视图college_view。视图的字段包括student_num、student_name、student_age和department。ALGORITHM设置为UNDEFINED类型。加入WITH LOCAL CHECK OPTION条件
查看视图college_view的详细结构
更新视图。向视图中插入三条记录。
修改视图,使其显示专业为‘计算机’的信息,其他条件不变
删除视图college_view
college表内容
字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | | 自增 |
number | 学好 | INT(10) | 是 | 否 | 是 | 是 | 否 |
name | 姓名 | VARCHAR(10) | 否 | 否 | 是 | 否 | 否 |
mejor | 专业 | VARCHAR(10) | 否 | 否 | 是 | 是 | 否 |
age | 年龄 | INT(5) | 否 | 否 | 否 | 否 | 否 |
collegeview表信息
number | name | major | age |
0901 | 张三 | 外语 | 20 |
0902 | 李四 | 计算机 | 22 |
0903 | 王五 | 计算机 | 19 |
CREATE TABLE college(number INT(10) NOT NULL UNIQUE PRIMARY KEY,
NAME VARCHAR(20) NOT NULL,
major VARCHAR(20) NOT NULL ,
age
INT(5)
);
CREATE ALGORITHM=UNDEFINED VIEW
college_view (student_num,student_name,student_age,department)
AS SELECT number,NAME,age,major FROM college
WITH LOCAL CHECK OPTION;
mysql> SHOW CREATE VIEW college_view \G
*************************** 1. row ***************************
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
INSERT INTO college_view VALUES(0901,'张三',20,'外语');
INSERT INTO
college_view VALUES(0902,'李四',22,'计算机');
INSERT INTO
college_view VALUES(0903,'王五',19,'计算机');
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;
DROP VIEW IF EXISTS college_view;