Introduction to MySQL views and operations (with code)

This article brings you an introduction to the introduction and operation of MySQL views (with code). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

1. Preparation

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

#外键为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 each into the customer table and balance table.

insert into customer values(0001,"xiaoming",'vip1','12566666','male','江宁区888号');
insert into customer values(0002,"xiaohong",'vip10','15209336760','male','建邺区888号');
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. Introduction to views

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 to 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: The view is virtual, and you can only grant the user the permissions of the view without granting the permissions of the real table, which plays a role in protecting the real table.
b. Customized display of data: Based on the same actual table, data can be customized to display data to users with different needs through different views.
c. Simplify data operations: It is suitable for scenarios where query statements are complex and frequently used, and can be implemented through views.
......

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

Create view syntax

create view <视图名称>  as <select语句>;

Modify view syntax

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

#更新视图结构
alter view <视图名称>  as <select语句>;
#更新视图数据相当于更新实际表,不适用基于多表创建的视图
update ....

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

a, select statement contains multiple tables
b, view Contains the having clause
c, and the view contains the distinct keyword
...

Delete view syntax

drop view <视图名称>

3. View operations

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 the creation is completed, 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 querying through the real table.

#查询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)
#查询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 draw the conclusion through the statement of creating 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 modify the balance of the 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.

Create 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)
#查看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 the 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）
#查看修改后视图结构。
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

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

[Related recommendations: MySQL Tutorial】

The above is the detailed content of Introduction to MySQL views and operations (with code). For more information, please follow other related articles on the PHP Chinese website!