MySQL 뷰의 기본 동작(5)_MySQL
- WBOY원래의
- 2016-09-09 08:13:421132검색
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;
결과는 다음과 같습니다.
+------+---------+
| 아이디 | 이름 |
+------+---------+
|1 |사과 |
|2 | 오렌지 |
+------+---------+
//실제로 뷰를 생성할 때 실제로 코드에 작성하는 것은 테이블 쿼리문인데, 재사용이 가능하도록 쿼리문을 캡슐화하고 새로운 이름을 부여해 줍니다.
//또한 보안 측면에서 여기에서는 가격 필드와 같이 보고 싶지 않은 일부 필드를 숨길 수 있습니다.
//참고: 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 문은 테이블에 대한 세부 정보뿐만 아니라 뷰에 대한 세부 정보도 표시합니다.
구문은 다음과 같습니다.
테이블 상태 표시 [FROM db_name] [LIKE '패턴']
//db_name 매개변수는 데이터베이스를 설정하는 데 사용됩니다. SHOW TABLES STATUS는 설정된 라이브러리의 테이블 및 뷰에 대한 세부 정보가 표시된다는 의미입니다.
//특정 테이블이나 뷰에 대한 자세한 정보를 보려면 LIKE 키워드를 설정합니다. 예: 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. 视图来自于多个基本表时，不允许添加、删除数据。
视图中的添加数据操作、删除数据操作、更新数据操作的语法同表完全相同。只是将表名换成视图名即可。
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。