MySQL view

View

Introduce ：

View (View） It's a virtual existence table . The data in the view does not actually exist in the database , Row and column data comes from tables used in queries that define views , And it's dynamically generated when using views .

Generally speaking , The view only saves the of the query 5QL Logic , Do not save query results . So when we create views , The main job is to create this SQL On the query statement .

establish ：

create [or replace] view  View name [( List of names )] as select  sentence  [with [cascaded|local] check option]

Inquire about ：

# View the create view statement ：
show create view  View name ;

#  View view data ：
select * from  View name ....;

test ：

mysql> create view s_v1 as select * from s;
Query OK, 0 rows affected (0.02 sec)

mysql> show create view s_v1;
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                                                                                                                                     | character_set_client | collation_connection |
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| s_v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `s_v1` AS select `s`.` Supplier name ` AS ` Supplier name `,`s`.` Supplier name ` AS ` Supplier name `,`s`.` Date of birth ` AS ` Date of birth `,`s`.` Gender ` AS ` Gender `,`s`.` city ` AS ` City   City `,`s`.` contact number ` AS ` contact number ` from `s` | gbk                  | gbk_chinese_ci       |
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.01 sec)

mysql> select * from s_v1;
+----------+----------+------------+------+----------+--------------+
|  Supplier name  |  Supplier name  |  Date of birth    |  Gender  |  city  |  contact number      |
+----------+----------+------------+------+----------+--------------+
| s12001   |  Zhang Qingan    | 1978-08-08 |  male    |  wuhan      | 123456789015 |
| s12002   |  Qian Daqing    | 1979-07-18 |  male    |  Jiujiang      | 123456789016 |
| s12003   |  Lipingping    | 1985-08-05 |  Woman    |  ningbo      | 12345607     |
| s12004   |  Wang jiangqiang    | 1985-08-06 |  Woman    |  Xi'an      | 12345608     |
| s12005   |  Shi Bixing    | 1985-08-07 |  male    |  Beijing      | 12345609     |
| s12006   |  Liu Dezhu    | 1985-08-08 |  male    |  ningbo      | 12345610     |
| s12007   |  Sun Qiquan    | 1985-08-09 |  Woman    |  ningbo      | 12345611     |
| s12008   |  Wu Tong      | 1985-08-10 |  Woman    |  Beijing      | 12345612     |
| s12009   |  Zhengmeimei    | 1985-08-11 |  Woman    |  Beijing      | 12345613     |
| s12010   |  Cao Cao      | 1985-08-12 |  male    |  Xi'an      | 12345614     |
+----------+----------+------------+------+----------+--------------+
10 rows in set (0.00 sec)

#  You can also add where Conditions 
mysql> select * from s_v1 where  Gender =' male ';
+----------+----------+------------+------+----------+--------------+
|  Supplier name  |  Supplier name  |  Date of birth    |  Gender  |  city  |  contact number      |
+----------+----------+------------+------+----------+--------------+
| s12001   |  Zhang Qingan    | 1978-08-08 |  male    |  wuhan      | 123456789015 |
| s12002   |  Qian Daqing    | 1979-07-18 |  male    |  Jiujiang      | 123456789016 |
| s12005   |  Shi Bixing    | 1985-08-07 |  male    |  Beijing      | 12345609     |
| s12006   |  Liu Dezhu    | 1985-08-08 |  male    |  ningbo      | 12345610     |
| s12010   |  Cao Cao      | 1985-08-12 |  male    |  Xi'an      | 12345614     |
+----------+----------+------------+------+----------+--------------+
5 rows in set (0.00 sec)

Modify the view ：

alter view  View name  as select  sentence ;

mysql> alter view s_v1 as select * from s where  city =' Beijing ';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from s_v1;
+----------+----------+------------+------+----------+----------+
|  Supplier name  |  Supplier name  |  Date of birth    |  Gender  |  city  |  contact number  |
+----------+----------+------------+------+----------+----------+
| s12005   |  Shi Bixing    | 1985-08-07 |  male    |  Beijing      | 12345609 |
| s12008   |  Wu Tong      | 1985-08-10 |  Woman    |  Beijing      | 12345612 |
| s12009   |  Zhengmeimei    | 1985-08-11 |  Woman    |  Beijing      | 12345613 |
+----------+----------+------------+------+----------+----------+
3 rows in set (0.00 sec)

Delete view ：

drop view [if exists]  View name ;

Check options ：

When using WITH CHECK OPTION Clause when creating a view ,MySQL Each row that is changing is checked through the view , For example, insert updates , Delete , To make it conform to the definition of the view .MySQL Allows you to create a view based on another view , It also checks the rules in the dependency view for consistency . In order to determine the scope of inspection ,mysql There are two options :CASCADED and LOCAL, The default value is CASCADED.

cascaded:

local:

Update of view ：

To make the view updatable , There must be a one-to-one relationship between the rows in the view and the rows in the underlying table . If the view contains any of the following , The view is not updatable :

1、 Aggregate function or window function （sum（）、min（）、max（）、count（） wait ）

2、distinct

3、group by

4、having

5、union perhaps union all

The function of view ：

• Simple

Views not only simplify the user's understanding of the data , They can also simplify their operations . Queries that are often used can be defined as views , Thus, the user does not have to specify all the conditions for each subsequent operation .

• Security

The database can authorize , However, it cannot be authorized to specific rows and columns in the database . Through view users can only query and modify the data they can see

• Data independence

View can help users shield the impact of changes in real table structure .

Case study ：

1. In order to ensure the security of database tables , Developers are operating tb_user Table time , You can only see the user's basic fields , Block mobile phone number and email
Field .

mysql> create view user_v_1 as select id,name,profession,age,gender,status,createtime from tb_user
;
Query OK, 0 rows affected

mysql> select * from user_v_1;
+----+------+------------+-----+--------+--------+---------------------+
| id | name | profession | age | gender | status | createtime          |
+----+------+------------+-----+--------+--------+---------------------+
|  1 |  Lyu3 bu4  |  Software Engineering    |  22 |      1 |      1 | 2022-05-23 10:51:55 |
+----+------+------------+-----+--------+--------+---------------------+
1 row in set

2． Check each student's elective courses （ Associated query of three tables ), This function is used in many businesses , To simplify the operation , Define a visual
chart .

mysql> create view c_v_1 as 
select s.sno,s.sname,s.ssex,s.saddress,s.sdept,s.speciality,course.cname from student s,sc,course where sc.sno = s.sno and course.cno = sc.cno;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from c_v_1;
+----------+-------+------+----------+--------------+------------+----------+
| sno      | sname | ssex | saddress | sdept        | speciality | cname    |
+----------+-------+------+----------+--------------+------------+----------+
| 20050101 |  Li Yong   |  male    |  Jinan, Shandong  |  Department of computer engineering  |  Computer application  |  database    |
| 20050101 |  Li Yong   |  male    |  Jinan, Shandong  |  Department of computer engineering  |  Computer application  |  mathematics      |
| 20050101 |  Li Yong   |  male    |  Jinan, Shandong  |  Department of computer engineering  |  Computer application  |  The information system  |
| 20050201 |  Liu Chen   |  Woman    |  Qingdao, Shandong  |  Department of information engineering    |  Electronic Commerce    |  mathematics      |
| 20050201 |  Liu Chen   |  Woman    |  Qingdao, Shandong  |  Department of information engineering    |  Electronic Commerce    |  The information system  |
+----------+-------+------+----------+--------------+------------+----------+
5 rows in set (0.00 sec)