Shulou(Shulou.com)06/01 Report--

This article mainly introduces what is the MySQL view and how to operate, the content of the article is carefully selected and edited by the author, with a certain pertinence, the reference significance for you is still relatively great, the following with the author to understand what is the MySQL view and how to operate it.

1. Preparatory work

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;# foreign keys are customerIdcreate table balance (id int (10) primary key, customerId int (10) not null, balance DECIMAL (10L2), foreign key (customerId) references customer (id) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert 3 pieces of data each into the customer table and the balance table.

Insert into customer values (0001, "xiaoming", 'vip1','12566666','male',' Jiangning District 888'); insert into customer values (0002, "xiaohong", 'vip10','15209336760','male',' Jianyuan District 888'); insert into customer values (0003, "xiaocui", 'vip11','15309336760','female',' Xinjiekou No. 888'); insert into balance values (1jie 0001900.55); insert into balance values (2Query 0002900.55); insert into balance values (3Mae 0003Jing 10000); 2, View introduction

The view can be simply understood as a virtual table, which is different from the real data table in the database, and the data in the view is based on the real table query. Views have the same structure as real tables. Real table update, query, delete and other operations, the view also supports. So why do you need a view?

A, improve the security of the real table: the view is virtual, which can only grant the user the permission of the view but not the real table, which plays the role of protecting the real table.

B, customized presentation data: based on the same actual table, you can customize the presentation of data to users with different needs through different views.

C, simplify data operation: it is suitable for scenarios where query statements are complex and frequently used, and can be realized through views.

.

It is important to note that view-related operations require the user to have the appropriate permissions. The following actions use the root user, who has operation permissions by default.

Create view syntax create view as; modify view syntax

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

# updating the view structure alter view as; # updating the view data is equivalent to updating the actual table, which is not applicable to the view update created based on multiple tables.

Note: the data of some views cannot be updated, that is, they cannot be updated using statements such as update,insert, such as:

A, select statements contain multiple tables

B. The view contains the having clause

C. Attempt to include the distinct keyword

.

Delete view syntax drop view 3, view operations create views 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 the bal_view. You can find that the result of querying the data through the view is exactly the same as that obtained through the real table query.

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

It is not difficult to conclude from the statement to create a view that when the data in the real table changes, so does the data in the view. So when the data in the view changes, does the data in the real table change? To experiment, change the customer balance of id=1 to 2000.

Mysql > update bal_view set balance=2000 where id=1;Query OK, 1 row affected (0.05sec) 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 visual chart changes, the data in the real table will also change.

Create views 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.05sec) # View data mysql > select * from cus_bal in cus_bal +-+-+ | cname | bal | +-+-+ | xiaoming | 2000.00 | xiaohong | 900.55 | xiaocui | 10000.00 | +-+-+ 3 rows in set (0.28sec) 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) # View the modified view structure. Mysql > desc cus_bal +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | cusname | char (20) | NO | | NULL | | bal | decimal (10L2) | 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)

After reading the above about what the MySQL view is and how to operate it, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.