Introduction to MySQL View and Basic Operation Tutorial

Preface

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

Recognition view

A view is a virtual table whose contents are defined by a query. Like Real Table 1, the view contains 1 series of named column and row data, but the view is not the data table that the database actually stores.

A view is a table that is exported from one, more than one table, or view, and contains a series of named data columns and several rows of data.

Views are different from data tables in the following ways:

The view is not a real table in the database, but a virtual table. Its structure and data are based on the query of the real table in the data. The query operation SQL statement stored in the database defines the contents of the view. The column data and row data come from the actual table referenced by the view query, and these data are generated dynamically when the view is referenced. A view has no actual physical records and is not stored in a database as a dataset. The data it corresponds to is actually stored in the real table referenced by the view. Views are windows of data, while tables are contents. The table is the storage unit of the actual data, while the view only displays the data in different ways, and its data source is still the actual table. Views are a way to view a datasheet, and you can query the data composed of some fields in the datasheet, just a collection of SQL statements. From the security point of view, the data security of the view is higher, and the users who use the view do not touch the data table and do not know the table structure. The creation and deletion of views only affect the view itself, not the corresponding basic table.

Although the view and table are different in essence, after the view is defined, the structure form is the same as Table 1, and operations such as query, modification, update and deletion can be carried out.

1. Preparations

Create two tables, balance (balance table) and customer (customer table), in the MySQL database and insert the 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 pieces of data into the customer table and 3 pieces of data into the balance table.

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

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

2. Introduction to Views

Views can be simply understood as virtual tables, which are different from real data tables in databases, and the data in views are queried based on real tables. View and Real Table 1 have similar structure. Real table updates, queries, deletions and other operations, views also support. So why do you need views?

a, improve the security of real table: The view is virtual, which can only grant users the permission of view but not the permission of real table, thus protecting the real table.

b, Customized Display Data: Based on the same actual table, you can customize the display data to users with different needs through different views.

c, simplifying data operation: It is suitable for scenes where query statements are complex and frequently used, and can be realized through views.

......

One point to note is that view-related operations require users to have corresponding permissions. The following operations use the root user, and the default user has operation rights.

Create view syntax

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

Modify view syntax

You can modify the view name by deleting it first and then creating it with the same statement.

# Update the view structure 
alter view < View name > as <select Statement >;
# Updating the view data is equivalent to updating the actual table , Views created based on multiple tables are not applicable 
update ....

Note: The data of some views cannot be updated, that is, they cannot be updated by update, insert and other statements, such as:

a, select statements contain multiple tables

b, the view contains the having clause

c, attempt to include distinct keyword

......

Delete view syntax

drop view < View name >

3. Operation of the view

Create 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 creation, look at the structure and records of bal_view. It can be found that the data queried through views and the results queried through real tables are completely 1.

# Query bal_view Structure of 
mysql> 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 bal_view Records in 
mysql> 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)

From the statement that creates the view, it is not difficult to conclude that when the data in the real table changes, so does the data in the view. So when the data in the view changes, will the data in the real table change? In Experiment 1, modify id=1 for customer balance 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

Look at the data in the real table balance under 1.

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 visual chart changes, the data in the real table will also change.

Create views based on multiple tables

Create the 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 cus_bal Data in 
mysql> select * from cus_bal;
+----------+----------+
| cname | bal |
+----------+----------+
| xiaoming | 2000.00 |
| xiaohong | 900.55 |
| xiaocui | 10000.00 |
+----------+----------+
3 rows in set (0.28 sec)

Modify 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 views 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'

Delete View

Delete view cus_bal

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

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

0

Summarize