1. Preparations

Create two tables balance (balance table) and customer (customer table) in 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;

#The 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 three pieces of data into the customer table and the balance table.

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 Xinjiekou');

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 the real data tables in the database. The data in the view is obtained by querying the real tables. Views have similar structures as real tables. Real table updates, queries, deletions and other operations, views also support. So why do you need views?

a. Enhance the security of real tables: Views are virtual, which can only grant the user's view rights and not the real tables, thus protecting the real tables.
b. Customized display data: Based on the same actual table, different views can be used to customize display data to users with different needs.
c. Simplify data operation: It is suitable for the scene where query statements are more complex and frequently used, and can be realized by view.
......

One thing to note is that view-related operations require users to have appropriate privileges. The following operations use root user, the default user has operation rights.

Create View Syntax

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

Modifying View Syntax

Modifying the view name can be deleted first and then created with the same statement.

#Update view structure
alter view <Name of view>  as <select Sentence>;
#Updating view data is equivalent to updating actual tables, not multi-table-based views
update ....

Note: The data of some views can not be updated, that is, it can not be updated with update, insert and other statements, such as:

a, select statements contain multiple tables
b. The view contains a having clause
c. Attempt to include distinct keywords
......

Delete view grammar

Drop view < view name >

3. Operations of Views

Creating Views Based on Forms

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

When the creation is complete, look at the structure and records of bal_view. It can be found that the results of querying data through views and querying data through real tables are exactly the same.

#Structure of querying bal_view
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 records in bal_view
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)

It is not difficult to conclude from the statement of creation a view that when the data in the real table changes, the data in the view will also change. So when the data in the view changes, will the data in the real table change? Let's try it out. Modify the customer balance 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

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.

Creating 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 the data in cus_bal
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 the cname in the cus_bal view to cusname.

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)

Modifying views based on multi-table creation

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

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