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

preface

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

cognitive view

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 rows of data, but the view is not a table of data that the database actually stores.

A view is a table derived from one or more tables or views and contains a series of named columns and rows of data.

Views are different from data tables in the following ways:

A view is not a real table in a database, but a virtual table whose structure and data are based on queries against real tables in the data. Query operations stored in the database SQL statements define the contents of the view, column data and row data from the actual tables referenced by the view query, dynamically generated when referencing the view. A view has no actual physical record, 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 to data, and tables are content. A table is the unit of storage for actual data, while a view simply displays data in different ways, and its data source is the actual table. A view is a way to view a data table. You can query the data made up of certain fields in the data table. It is just a collection of SQL statements. From a security perspective, views have higher data security, and users who use views do not touch the data tables and do not know the table structure. The creation and deletion of views only affect the views themselves, not the corresponding base tables.

Although views and tables are different in nature, after views are defined, they have the same structure as tables, and can be queried, modified, updated, and deleted.

1. Preparation

Create two tables balance and customer 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;#Foreign key is customerIdcreate 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',' 1256666','male',' No. 888, Jiangning District ');insert into customer values (0002,"xiaohong",'vip 10',' 15209336760','male',' No. 888, Jianye District ');insert into customer values (0003,"xiaocui",'vip 11',' 15309336760','female',' Xinjiekou No.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 profile

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

a. Improve the security of real tables: Views are virtual, and users can only be granted permissions for views without granting permissions for real tables, playing a role in protecting real tables.

b. Customized display data: Based on the same actual table, customized display data can be displayed to users with different needs through different views.

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

......

One caveat: View-related actions require user permissions. The following operations use root user, the default user has operation permissions.

Create View Syntax

create view as ;

Modify View Syntax

View names can be deleted and then created with the same statement.

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

Note: Some view data cannot be updated, that is, it cannot be updated using update, insert and other statements, such as:

A. Select statement contains multiple tables

b. The view contains the having clause

c. Try to include the distinct keyword

......

Delete View Syntax

drop view

3. Operation of view

Create a view from a single table

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

Once created, view the structure and records of bal_view. You can see that the data you get from a view query is exactly the same as the data you get from a real table query.

#Query bal_view structure 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 ||+---------------| 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, the data in the view changes as well. So when the data in the view changes, does the data in the real table change? To experiment, change the customer balance for 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 visual chart changes, the data in the real table will also change.

Creating Views from 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 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 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 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 from 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)

summary

The above is all the content of this article, I hope the content of this article for everyone's study or work has a certain reference learning value, thank you for your support.

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.