MySQL 视图的基础操作
来源:互联网 发布:雅思口语怎么提高 知乎 编辑:程序博客网 时间:2024/06/05 15:29
1.为什么使用视图:
为了提高复杂SQL语句的复用性和表操作的安全性(例如:工资字段不想展示给所有能查看该查询结果的人),MySQL提供了视图特性。所谓视图,本质上是一种虚拟表,其内容与真实的表相似,包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储数据值的形式存在。行和列的数据来自定义视图的查询所引用基本表,并且在具体使用视图时动态生成。
视图有如下特点;
1. 视图的列可以来自不同的表,是表的抽象和逻辑意义上建立的新关系。
2. 视图是由基本表(实表)产生的表(虚表)。
3. 视图的建立和删除不影响基本表。
4. 对视图内容的更新(添加、删除和修改)直接影响基本表。
5. 当视图来自多个基本表时,不允许添加和删除数据。
2.创建视图:
在创建视图时,首先要确保拥有CREATE VIEW的权限,并且同时确保对创建视图所引用的表也具有相应的权限。
2.1 创建视图的语法形式:
虽然视图可以被看成是一种虚拟表,但是其在物理上是不存在的,即数据库管理系统没有专门的位置为视图存储数据。根据视图的概念发现其数据来源于查询语句,因此创建视图的语法为:
CREATE VIEW view_name AS 查询语句
//说明:和创建表一样,视图名不能和表名、也不能和其他视图名重名。视图的功能实际就是封装了复杂的查询语句。
示例:
use zhaojd_test; //选择一个自己创建的库
create
table
t_product( //创建表
id
int
primary
key
,
pname
varchar
(20),
price
decimal
(8,2)
);
insert
into
t_product
values
(1,
'apple'
,6.5); //向表中插入数据
insert
into
t_product
values
(2,
'orange'
,3); //向表中插入数据
create
view
view_product
as
select
id,
name
from
t_product; //创建视图
select
*
from
view_product;
结果为:
+------+--------+
| id | name |
+------+--------+
| 1 | apple |
| 2 | orange |
+------+--------+
//其实在创建视图时实际代码里写的是一个表查询语句,只不过把这个查询语句封装起来重新起了一个名字,方便可以重复使用。
//再者,安全性方面可以隐藏一些不希望看到的字段,比如这里的价格字段。
//注意:在SQL语句的命名规范中,视图一般以view_xxx或者v_xxx的样式来命名。视图的查询语句和表的查询语句相同。
2.2 创建各种视图:
由于视图的功能实际上时封装查询语句,那么是不是任何形式的查询语句都可以封装在视图中呢?
2.2.1 封装实现查询常量语句的视图(常量视图):
示例:
mysql>
create
view
view_test1
as
select
3.1415926;
Query OK, 0
rows
affected (0.07 sec)
mysql>
select
*
from
view_test1;
+
-----------+
| 3.1415926 |
+
-----------+
| 3.1415926 |
+
-----------+
1 row
in
set
(0.00 sec)
2.2.2 封装使用聚合函数(SUM、MIN、MAX、COUNT等)查询语句的视图:
示例:
首先准备需要用到的两张表及其初始化数据;
CREATE
TABLE
t_group(
id
INT
PRIMARY
KEY
AUTO_INCREMENT,
NAME
VARCHAR
(20)
);
CREATE
TABLE
t_student(
id
INT
PRIMARY
KEY
AUTO_INCREMENT,
NAME
VARCHAR
(20),
sex
CHAR
(1),
group_id
INT
,
FOREIGN
KEY
(group_id)
REFERENCES
t_group (id)
);
//t_group表中插入数据
INSERT
INTO
t_group (
NAME
)
VALUES
(
'group_1'
);
INSERT
INTO
t_group (
NAME
)
VALUES
(
'group_2'
);
INSERT
INTO
t_group (
NAME
)
VALUES
(
'group_3'
);
INSERT
INTO
t_group (
NAME
)
VALUES
(
'group_4'
);
INSERT
INTO
t_group (
NAME
)
VALUES
(
'group_5'
);
//t_student表中插入数据
INSERT
INTO
t_student (
NAME
,sex,group_id)
VALUES
(
'zjd_1'
,
'M'
,1);
INSERT
INTO
t_student (
NAME
,sex,group_id)
VALUES
(
'zjd_2'
,
'M'
,1);
INSERT
INTO
t_student (
NAME
,sex,group_id)
VALUES
(
'zjd_3'
,
'M'
,2);
INSERT
INTO
t_student (
NAME
,sex,group_id)
VALUES
(
'zjd_4'
,
'W'
,2);
INSERT
INTO
t_student (
NAME
,sex,group_id)
VALUES
(
'zjd_5'
,
'W'
,2);
INSERT
INTO
t_student (
NAME
,sex,group_id)
VALUES
(
'zjd_6'
,
'W'
,2);
INSERT
INTO
t_student (
NAME
,sex,group_id)
VALUES
(
'zjd_7'
,
'M'
,3);
INSERT
INTO
t_student (
NAME
,sex,group_id)
VALUES
(
'zjd_8'
,
'W'
,4);
INSERT
INTO
t_student (
NAME
,sex,group_id)
VALUES
(
'zjd_9'
,
'W'
,4);
================================================================
mysql>
create
view
view_test2
as
select
count
(
name
)
from
t_student;
Query OK, 0
rows
affected (0.71 sec)
mysql>
select
*
from
view_test2;
+
-------------+
|
count
(
name
) |
+
-------------+
| 9 |
+
-------------+
1 row
in
set
(0.01 sec)
2.2.3 封装了实现排序功能(ORDER BY)查询语句的视图:
示例:
mysql>
create
view
view_test3
as
select
name
from
t_student
order
by
id
desc
;
Query OK, 0
rows
affected (0.06 sec)
mysql>
select
*
from
view_test3;
+
-------+
|
name
|
+
-------+
| zjd_9 |
| zjd_8 |
| zjd_7 |
| zjd_6 |
| zjd_5 |
| zjd_4 |
| zjd_3 |
| zjd_2 |
| zjd_1 |
+
-------+
9
rows
in
set
(0.00 sec)
2.2.4 封装了实现表内连接查询语句的视图:
示例:(第二组学生的姓名)
mysql>
create
view
view_test5
as
select
s.
name
from
t_student s,t_group g
where
s.group_id=g.id
and
g.id=2;
Query OK, 0
rows
affected (0.07 sec)
mysql>
select
*
from
view_test5;
+
-------+
|
name
|
+
-------+
| zjd_3 |
| zjd_4 |
| zjd_5 |
| zjd_6 |
+
-------+
4
rows
in
set
(0.00 sec)
2.2.5 封装了实现表外连接(LEFT JOIN和RIGHT JOIN)查询语句的视图:
示例:(第二组学生姓名)
mysql>
create
view
view_test6
as
select
s.
name
from
t_student s
left
join
t_group g
on
s.group_id=g.id
where
g.id=2;
Query OK, 0
rows
affected (0.09 sec)
mysql>
select
*
from
view_test6;
+
-------+
|
name
|
+
-------+
| zjd_3 |
| zjd_4 |
| zjd_5 |
| zjd_6 |
+
-------+
4
rows
in
set
(0.01 sec)
2.2.6 封装了实现子查询相关查询语句的视图:
示例:
mysql>
create
view
view_test7
as
select
s.
name
from
t_student s
where
s.id
in
(
select
id
from
t_group);
Query OK, 0
rows
affected (0.08 sec)
mysql>
select
*
from
view_test7;
+
-------+
|
name
|
+
-------+
| zjd_1 |
| zjd_2 |
| zjd_3 |
| zjd_4 |
| zjd_5 |
+
-------+
5
rows
in
set
(0.00 sec)
2.2.7 封装了实现记录联合(UNION和UNION ALL)查询语句的视图:
mysql>
create
view
view_test8
as
select
id,
name
from
t_student
union
all
select
id,
name
from
t_group;
Query OK, 0
rows
affected (0.08 sec)
mysql>
select
*
from
view_test8;
+
----+---------+
| id |
name
|
+
----+---------+
| 1 | zjd_1 |
| 2 | zjd_2 |
| 3 | zjd_3 |
| 4 | zjd_4 |
| 5 | zjd_5 |
| 6 | zjd_6 |
| 7 | zjd_7 |
| 8 | zjd_8 |
| 9 | zjd_9 |
| 1 | group_1 |
| 2 | group_2 |
| 3 | group_3 |
| 4 | group_4 |
| 5 | group_5 |
+
----+---------+
14
rows
in
set
(0.01 sec)
3.查看视图:
3.1 SHOW TABLES语句查看视图名:
执行SHOW TABLES 语句时不仅可以显示表的名字,同时也是显示出视图的名字。
示例:
mysql> show tables;
+
------------------+
| Tables_in_zhaojd |
+
------------------+
| t_group |
| t_product |
| t_student |
| v_product |
| view_test1 |
| view_test2 |
| view_test3 |
| view_test4 |
| view_test5 |
| view_test6 |
| view_test8 |
+
------------------+
11
rows
in
set
(0.00 sec)
3.2 SHOW TABLE STATUS语句查看视图详细信息:
和SHOW TABLES语句一样,SHOW TABLE STATUS语句不仅会显示表的详细信息,同时也会显示视图的详细信息。
语法如下:
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
//参数db_name用来设置数据库,SHOW TABLES STATUS表示将显示所设置库的表和视图的详细信息。
//设置LIKE关键字,可以查看某一个具体表或视图的详细信息。例如: SHOW TABLE STATUS FROM zhaojd LIKE 't_group' \G
示例:
mysql> show
table
status
from
zhaojd \G
*************************** 1. row ***************************
Name
: t_group
Engine: InnoDB
Version: 10
Row_format: Compact
Rows
: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 7340032
Auto_increment: 6
Create_time: 2016-08-19 16:26:06
Update_time:
NULL
Check_time:
NULL
Collation: utf8_general_ci
Checksum:
NULL
Create_options:
Comment:
=============================================================
Name
: view_test8
Engine:
NULL
Version:
NULL
Row_format:
NULL
Rows
:
NULL
Avg_row_length:
NULL
Data_length:
NULL
Max_data_length:
NULL
Index_length:
NULL
Data_free:
NULL
Auto_increment:
NULL
Create_time:
NULL
Update_time:
NULL
Check_time:
NULL
Collation:
NULL
Checksum:
NULL
Create_options:
NULL
Comment:
VIEW
3.3 SHOW CREATE VIEW语句查看视图定义信息:
语法为:
SHOW CREATE VIEW viewname;
示例:
mysql> show
create
view
view_test8 \G
*************************** 1. row ***************************
View
: view_test8
Create
View
:
CREATE
ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW
`view_test8`
AS
select
`t_student`.`id`
AS
`id`,
`t_student`.`
NAME
`
AS
`
name
`
from
`t_student`
union
all
select
`t_group`.`id`
AS
`id`,`t_group`.`
NAME
`
AS
`
name
`
from
`t_group`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row
in
set
(0.00 sec)
//根据执行结果可以发现,SHOW
CREATE
VIEW
语句返回两个字段,分别为表示视图名的
View
字段和关于视图定义的
Create
view
字段。
3.4 DESCRIBE | DESC 语句查看视图定义信息:
语法为:
DESCRIBE | DESC viewname;
示例:
mysql>
desc
view_test8;
+
-------+-------------+------+-----+---------+-------+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
-------+-------------+------+-----+---------+-------+
| id |
int
(11) |
NO
| | 0 | |
|
name
|
varchar
(20) | YES | |
NULL
| |
+
-------+-------------+------+-----+---------+-------+
2
rows
in
set
(0.02 sec)
3.5 通过系统表查看视图信息:
当MySQL安装成功后,会自动创建系统数据库infomation_schema。在该数据库中存在一个包含视图信息的表格,可以通过查看表格views来查看所有视图的相关信息。
示例:
mysql> use information_schema;
Database
changed
mysql>
select
*
from
views
where
table_name=
'view_test8'
\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: zhaojd
TABLE_NAME: view_test8
VIEW_DEFINITION:
select
`zhaojd`.`t_student`.`id`
AS
`id`,`zhaojd`.`t_student`.`
NAME
`
AS
`
name
`
from
`zhaojd`.`t_student`
union
all
select
`zhaojd`.`t_group`.`id`
AS
`id`,`zhaojd`.`t_group`.`
NAME
`
AS
`
name
`
from
`zhaojd`.`t_group`
CHECK_OPTION: NONE
IS_UPDATABLE:
NO
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row
in
set
(0.01 sec)
4.删除视图:
在删除视图时首先要确保拥有删除视图的权限。
语法为:
DROP VIEW view_name [,view_name] ......
//从语法可以看出,DROP VIEW一次可以删除多个视图
示例:
mysql> use zhaojd;
Database
changed
mysql> show tables;
+
------------------+
| Tables_in_zhaojd |
+
------------------+
| t_group |
| t_product |
| t_student |
| v_product |
| view_test1 |
| view_test2 |
| view_test3 |
| view_test4 |
| view_test5 |
| view_test6 |
| view_test8 |
+
------------------+
11
rows
in
set
(0.00 sec)
mysql>
drop
view
view_test1, view_test2;
Query OK, 0
rows
affected (0.01 sec)
mysql> show tables;
+
------------------+
| Tables_in_zhaojd |
+
------------------+
| t_group |
| t_product |
| t_student |
| v_product |
| view_test3 |
| view_test4 |
| view_test5 |
| view_test6 |
| view_test8 |
+
------------------+
9
rows
in
set
(0.00 sec)
5.修改视图:
5.1 CREATE OR REPLACE VIEW语句修改视图:
对于已经创建好的表,尤其是已经有大量数据的表,通过先删除,然后再按照新的表定义重新建表的方式来修改表,需要做很多额外的工作,例如数据的重载等。可是对于视图来说,由于是“虚表”,并没有存储数据,所以完全可以通过该方式来修改视图。
实现思路就是:先删除同名的视图,然后再根据新的需求创建新的视图即可。
DROP
VIEW
view_name;
CREATE
VIEW
view_name
as
查询语句;
但是如果每次修改视图,都是先删除视图,然后再次创建一个同名的视图,则显得非常麻烦。于是MySQL提供了更方便的实现替换的创建视图的语法,完整语法为:
CREATE OR REPLACE VIEW view_name as 查询语句;
5.2 ALTER语句修改视图:
语法为:
ALTER VIEW view_name as 查询语句;
6.利用视图操作基本表:
再MySQL中可以通常视图检索基本表数据,这是视图最基本的应用,除此之后还可以通过视图修改基本表中的数据。
6.1检索(查询)数据:
通过视图查询数据,与通过表进行查询完全相同,只不过通过视图查询表更安全,更简单实用。只需要把表名换成视图名即可。
6.2利用视图操作基本表数据:
由于视图是“虚表”,所以对视图数据进行的更新操作,实际上是对其基本表数据进行的更新操作。在具体更新视图数据时,需要注意以下两点;
1. 对视图数据进行添加、删除直接影响基本表。
2. 视图来自于多个基本表时,不允许添加、删除数据。
视图中的添加数据操作、删除数据操作、更新数据操作的语法同表完全相同。只是将表名换成视图名即可。
- MySQL 视图的基础操作
- mysql视图的操作
- mysql-表、ER图、索引与视图的基础操作
- MySQL数据库的视图操作
- 03-MySQL-视图的操作
- MYSQL 中视图的操作
- MySQL的基础操作
- MySQL的基础操作
- SQL基础(二)视图的操作
- MySQL基础之表操作 索引 约束 视图等介绍
- MySQL的CRUD操作+使用视图
- MySQL(五)视图的操作
- Mysql的视图简介与操作
- mysql-视图基础学习
- 【MySQL基础】视图
- Hibernate操作MySQL视图
- MYSQL 视图操作
- mysql 视图基本操作
- Prime Path(广搜)
- React Native 实现注销登录功能
- STL空间配置器——第一级配置器__malloc_alloc_template剖析
- Eclipse ,ctrl+h,搜索,拼接正则表达式
- 财务类程序使用的表(我的第一篇)
- MySQL 视图的基础操作
- Android设置十六进制颜色不同透明度对应的值
- Entity Framework Code First属性映射约定(Data Annotation基础篇)
- 层次聚类简介
- 滑雪_KEY
- Glide图片框架使用详细介绍(二)之缓存策略
- 二分查找
- h5学习培训课程目录
- Mac调试Qt For iOS程序,真机与模拟器