🏷️ The current chapter is [MySQL basics] 07: view 🤖

🏷️ More: MySQL basic tutorial , take you to start MySQL database on a zero basis! 💪

🏷️ Welcome to praise 👍 Collection 🌟 Attention ❤️ Leaving a message. ✍️

Concept of view

A virtual table composed of data queried by one or more tables in a database according to specific conditions

Role of view

• Simple: users using the view do not need to care about the structure, association conditions and filter conditions of the corresponding table. For users, it is already the result set of filtered composite conditions.
• Security: users using the view can only access the result set they are allowed to query. The permission management of the table cannot be limited to a row or a column, but it can be simply realized through the view.
• Data independence: once the view structure is determined, the impact of table structure changes on users can be shielded. Adding columns to the source table has no impact on the view; If the column name of the source table is modified, it can be solved by modifying the view without affecting visitors.

Use view

Create view

Syntax:

CREATE [OR REPLACE] [ALGORITHM = {UNDIFINED|MERGE|TEMPTABLE}] VIEW <view_name>[<column_list>]
AS select_statement
[WITH[CASCADED|LOCAL] CHECK OPTION]

create view <view_name>[<column_list>]
as <select_statement>
with check option;

• OR REPLACE: indicates that the existing view will be replaced when the view is created
• ALGORITHM: represents the view selection ALGORITHM
• select_statement: indicates a select statement
• [with [cached | local] check option]: indicates that the view is guaranteed to be within the permission range of the view when it is updated. Note: it is recommended to use the while [cached | local] check option to ensure data security, so it is recommended to add it.

-- sample sql
drop table if exists book;
create table book(
	id int primary key auto_increment,
    book_name varchar(64),
    author varchar(32),
    price decimal(10,2),
    create_time date
);

insert into book(book_name,author,price,create_time) values('<Listen to the wind sing','Haruki Murakami','36.66',sysdate()),('<White night walk','Keio Dongye','35.33',sysdate()),('<Border town','Shen Congwen','38.88',sysdate()),('<Alive','Yu Hua','37.77',sysdate()),('<Red and white roses','Zhang Ailing','35.55',sysdate()),('<Three bodies','Liu Cixin','39.33',sysdate()),('<Trisomy 2: dark forest','Liu Cixin','39.33',sysdate()),('<Trisomy 3: immortality of death','Liu Cixin','39.33',sysdate());

create view view_book
as select book_name,author,price from book
with check option;

select * from view_book;

View operation

view a chart

-- View view structure
desc <view_name>;
-- View view creation details
show create view <view_name>;

-- sample sql 1
desc view_book;
-- result
mysql> desc view_book;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| book_name | varchar(64)   | YES  |     | NULL    |       |
| author    | varchar(32)   | YES  |     | NULL    |       |
| price     | decimal(10,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

-- sample sql 2
show create view view_book;
-- result
mysql> show create view view_book;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View      | Create View                                                                                                                                                                                                                  | character_set_client | collation_connection |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_book | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_book` AS select `book`.`book_name` AS `book_name`,`book`.`author` AS `author`,`book`.`price` AS `price` from `book` WITH CASCADED CHECK OPTION | utf8mb4              | utf8mb4_0900_ai_ci   |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.04 sec)

Modify view

-- 1
create or replace view <view_name>
as <select_statement>;
-- 2
alter view <view_name>
as <select_statement>;

-- sample sql 1
create or replace view view_book
as select id,book_name,author,price from book;

desc view_book;

-- sample sql 2
alter view view_book
as select book_name,author,price,create_time from book;

desc view_book;

Delete view

drop view <view_name>;

drop view view_book;

Properties of view data

• The view is a virtual table, and the data of the query view comes from the data table. When the data in the original table is modified, the data of the view will also change.
• When DML operation is performed on the data in the view, if the view is a single table view, the original table data will be affected. When the view is a connected table view, DML operation on the view is not allowed.

Views can be used to simplify operations on complex queries without modifying the data.

-- sample sql
drop table if exists class;
create table class(
	id int primary key auto_increment,
    class_no varchar(32),
    grade tinyint
);
insert into class(class_no,grade) values('A01',1),('B01',1),('C01',1);
drop table if exists student;
create table student(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    gender tinyint,
    class_id int
);
insert into student(name,age,gender,class_id) values('Jonh',19,1,1),('Yrusp',25,1,3),('Orisk',22,0,2),('Pande',21,1,1),('Loice',19,0,1),('Alan',22,1,2);

-- sample view single table
drop view if exists view_class;
create view view_class
as select class_no,grade from class;

select * from view_class;

insert into view_class values('A02',2);

select * from view_class;
select * from class;

-- sample view double table
drop view if exists view_class_stu;
create view view_class_stu
as select c.class_no,s.name,s.age,s.gender from class c left join student s on c.id = s.class_id;

select * from view_class_stu;

insert into view_class_stu values(99,'Boli',23,1);
insert into view_class_stu(name,age,gender) values('Boli',23,1);
-- result
1471 - The target table view_class_stu of the INSERT is not insertable-into