MySQL view introduction and basic operation tutorial

Preface

View is a very useful database object in the database system. MySQL 5.0 and later versions added support for views.

Understanding Views

A view is a virtual table whose contents are defined by a query. Like a real table, a view contains a series of named columns and row data, but a view is not a data table that is actually stored in the database.

A view is a table derived from one or more tables or views, containing a series of named columns and a number of rows.

Views are different from tables in the following ways:

• A view is not a real table in the database, but a virtual table whose structure and data are based on queries on real tables in the database.
• The query operation SQL statement stored in the database defines the content of the view. The column data and row data come from the actual table referenced by the view query and are generated dynamically when the view is referenced.
• A view has no actual physical records and is not stored in the database in the form of a data set. The data it corresponds to is actually stored in the real table referenced by the view.
• Views are windows to data, and tables are the content. A table is a storage unit for actual data, while a view only displays data in different ways. Its data source is still the actual table.
• A view is a method of viewing a data table. It can query the data composed of certain fields in the data table. It is just a collection of SQL statements. From a security perspective, views have higher data security because users who use views do not touch the data tables and do not know the table structure.
• The creation and deletion of a view only affects the view itself, not the corresponding basic table.

Although views and tables are essentially different, after a view is defined, its structure is the same as that of a table and operations such as query, modify, update, and delete can be performed on it.

1. Preparation

Create two tables, balance and customer, in the MySQL database and insert data.

create table customer(
 id int(10) primary key,
 name char(20) not null,
 role char(20) not null,
 phone char(20) not null,
 sex char(10) not null,
 address char(50) not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#Foreign key is customerId
create table balance(
 id int(10) primary key,
 customerId int(10) not null,
 balance DECIMAL(10,2),
 foreign key(customerId) references customer(id) 

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert 3 records into the customer table and the balance table respectively.

insert into customer values(0001,"xiaoming",'vip1','12566666','male','No. 888, Jiangning District');
insert into customer values(0002,"xiaohong",'vip10','15209336760','male','No. 888, Jianye District');
insert into customer values(0003,"xiaocui",'vip11','15309336760','female','新街口888号');

insert into balance values(1,0001,900.55);
insert into balance values(2,0002,900.55);
insert into balance values(3,0003,10000);

2. View Introduction

A view can be simply understood as a virtual table. It is different from the real data table in the database. The data in the view is obtained based on the query of the real table. Views have a similar structure as real tables. Views also support operations such as updating, querying, and deleting real tables. So why do we need views?

a. Improve the security of the real table: Views are virtual. Users can be granted only view permissions without granting permissions to the real table, thereby protecting the real table.

b. Customized display of data: Based on the same actual table, different views can be used to customize the display of data to users with different needs.

c. Simplify data operations: This is suitable for scenarios where query statements are complex and frequently used, and can be achieved through views.

......

It should be noted that view-related operations require users to have corresponding permissions. The following operations are performed using the root user, and the default user has operation permissions.

Create View Syntax

create view <view name> as <select statement>;

Modify view syntax

To modify the view name, you can first delete it and then create it using the same statement.

#Update the view structure alter view <view name> as <select statement>;
#Updating view data is equivalent to updating the actual table, and is not applicable to views created based on multiple tables.

Note: The data in some views cannot be updated, that is, they cannot be updated using statements such as update and insert, for example:

a. The select statement contains multiple tables

b. The view contains a having clause

c. The attempt contains the distinct keyword

......

Remove View Syntax

drop view <view name>

3. View Operation

Creating a view based on a single table

mysql> create view bal_view 
 -> as
 -> select * from balance;
Query OK, 0 rows affected (0.22 sec)

After the creation is complete, view the structure and records of bal_view. It can be found that the results obtained by querying data through the view are exactly the same as those obtained by querying the real table.

#Query the structure of bal_viewmysql> desc bal_view;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id | int(10) | NO | | NULL | |
| customerId | int(10) | NO | | NULL | |
| balance | decimal(10,2) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
3 rows in set (0.07 sec)
#Query the records in bal_viewmysql> select * from bal_view;
+----+------------+----------+
| id | customerId | balance |
+----+------------+----------+
| 1 | 1 | 900.55 |
| 2 | 2 | 900.55 |
| 3 | 3 | 10000.00 |
+----+------------+----------+
3 rows in set (0.01 sec)

It is not difficult to conclude from the statement that creates the view: when the data in the real table changes, the data in the view will also change accordingly. So when the data in the view changes, will the data in the real table change? Let's experiment and change the balance of customer with id=1 to 2000.

mysql> update bal_view set balance=2000 where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Let's take a look at the data in the real table balance.

mysql> select * from bal_view where id=1;
+----+------------+---------+
| id | customerId | balance |
+----+------------+---------+
| 1 | 1 | 2000.00 |
+----+------------+---------+
1 row in set (0.03 sec)

Conclusion: When the data in the view table changes, the data in the real table will also change accordingly.

Creating a view based on multiple tables

Create a view cus_bal with two fields: customer name and balance.

mysql> create view cus_bal
 -> (cname,bal)
 -> as
 -> select customer.name,balance.balance from customer ,balance
 -> where customer.id=balance.customerId;
Query OK, 0 rows affected (0.05 sec)
#View the data in cus_balmysql> select * from cus_bal;
+----------+----------+
| cname | bal |
+----------+----------+
| xiaoming | 2000.00 |
| xiaohong | 900.55 |
| xiaocui | 10000.00 |
+----------+----------+
3 rows in set (0.28 sec)

Modify a view

Change cname to cusname in the cus_bal view.

mysql> alter view cus_bal
 -> (cusname,bal)
 -> as
 -> select customer.name,balance.balance from customer ,balance
 -> where customer.id=balance.customerId;
Query OK, 0 rows affected (0.06 sec）
#View the modified view structure.
mysql> desc cus_bal;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| cusname | char(20) | NO | | NULL | |
| bal | decimal(10,2) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Modify a view created based on multiple tables

mysql> insert into cus_bal(cusname,bal) values ​​("ee",11);
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'rms.cus_bal'

Deleting a View

Delete the view cus_bal

drop view cus_bal;
mysql> drop view cus_bal;
Query OK, 0 rows affected (0.00 sec)

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.