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

Foreword:

What is a view?

A virtual table, just like the real one. The view contains a series of row and column data with names. Views are derived from one or more tables, and we can manipulate them through insert,update,delete. When the data seen through the view is modified, the data of the corresponding original table will also change. At the same time, if the original table changes, the change can be automatically reflected in the view.

Views have the following advantages:

Simplicity: what you see is what you need. Views can simplify not only the user's understanding of the data, but also the operation. Frequently used queries can be made into a view; security: through the view users can only query and modify the data that can be seen, and other data in the database can neither be seen nor accessed. Database authorization commands allow each user to limit the retrieval of the database to specific database objects, but not to specific rows and columns of the database; logical data independence: views can help users shield the impact of changes in the structure of real tables.

What is the difference and relationship between a view and a table?

The difference between the two:

The view is a compiled SQL statement, which is a visual table based on the result set of the SQL statement, but the table is not; the view has no actual physical record, but the table has; the table is the content, the view window; although both the table and the view occupy physical space, but the view only exists logical concepts, and the table can modify the data in time, but the view can only be modified with the creation statement. A view is a way to view a data table, which can query the data made up of certain fields in the data table, just a collection of SQL statements. From a security point of view, the view prevents users from touching the data table and therefore does not know the table structure; the table belongs to the table in the global schema and is a real table. On the other hand, the table in which the view belongs to the local schema is a virtual table; the establishment and deletion of the view only affects the view itself, but not the basic table of the corresponding table.

The connection between the two:

A view is a table built on top of the basic table, its structure and content come from the basic table, and it depends on the existence of the basic table. A view can correspond to either one base table or multiple base tables. A view is a relationship established in a basic abstract and logical sense.

1. Create view 1, create single table view mysql > create table t (quantity int,price int); Query OK, 0 rows affected (0.01 sec) mysql > insert into t values (3 sec 50); Query OK, 1 row affected (0.00 sec) mysql > create view view_t as select quantity,price,quantity*price as tottal from t politics query OK, 0 rows affected (0.01 sec) mysql > select * from view_t +-+ | quantity | price | total | +-+ | 3 | 50 | 150 | +-+ 1 row in set (0.00 sec) 2. Create a multi-table view mysql > create table student-> (- > s_id int (3) primary key -> s_name varchar (30),-> s_age int (3),-> s_sex varchar (8)->) Query OK, 0 rows affected (0. 01 sec) mysql > create table stu_info-> (- > s_id int (3),-> class varchar (50),-> addr varchar (100)->); Query OK, 0 rows affected (0. 01 sec) mysql > insert into stu_info values-> (1) Mysql > create view stu_class (id,name,class) as-> select student.s_id,student.s_name,stu_info.class-> from student,stu_info where student.s_id=stu_info.s_id;3, view view related information mysql > desc stu_class +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (3) | NO | | NULL | name | varchar (30) | YES | | NULL | | class | varchar (50) | YES | | NULL | | +-+-+ 3 rows in set (0.00 sec) mysql > show table status like 'stu_class'\ gateway * 1. Row * * Name: stu_class Engine: NULL Version: NULL Row_format: NULL Rows: NULL Avg_row_length: NULL Data_length: NULLMax_data_length: NULL Index_length: NULL Data_free: NULL Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: NULL Checksum: NULL Create_options: VIEW1 row in set (0.01sec) mysql > show create view stu_class\ gateway * 1. Row * * * View: stu_class Create View: CREATE ALGORITHM=UNDEFINED DEFINER= `root` @ `localhost`SQL SECURITY DEFINER VIEW `stu_ class`AS select `student`.`s _ id`AS `id` `student`.`s _ name`AS `name`, `student`.`class`class`AS `class`from (`student`join `stu_ into`) where (`student`.`s _ id` = `student`.`s _ id`) character_set_client: utf8collation_connection: utf8_general_ci1 row in set (0.00 sec) mysql > select * from information_schema.views where TABLE_SCHEMA='test02'\ G4, modify the view

Method 1:

Mysql > create or replace view view_t as select * from tactile query OK, 0 rows affected (0.00 sec) mysql > select * from view_t; +-+ | quantity | price | +-+-+ | 3 | 50 | +-+-+ 1 row in set (0.00 sec)

Method 2:

Mysql > alter view view_t (abc) as select quantity from t; Query OK, 0 rows affected (0.00 sec) mysql > desc view_t +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | abc | Int (11) | YES | | NULL | | +-+-+ 1 row in set (0.00 sec) 5. Update view 1) update directive updates mysql > select * from t +-+ | quantity | price | +-+-+ | 3 | 50 | +-+-+ 1 row in set (0.00 sec) mysql > select * from view_t;+-+ | abc | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql > update view_t set abc=5 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from view_t; +-+ | abc | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql > select * from t +-+-+ | quantity | price | +-+-+ | 5 | 50 | +-+-+ 1 row in set (0.00 sec) 2) insert instruction update mysql > select * from t +-+-+ | quantity | price | +-+-+ | 5 | 50 | +-+-+ 1 row in set (0.00 sec) mysql > select * from view_t +-+ | abc | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql > insert into t values (3Ling 5); Query OK, 1 row affected (0.00 sec) mysql > select * from view_t; +-+ | abc | +-+ | 5 | | 3 | +-+ 2 rows in set (0.00 sec) mysql > select * from t +-+-+ | quantity | price | +-+-+ | 5 | 50 | 3 | 5 | 5 | +-+ 2 rows in set (0.00 sec) 3) delete instruction deletes table data mysql > create view view_t2 (qty,price,total) as select quantity,price Quantity*price from t Mysql > select * from view_t2; +-+ | qty | price | total | +-+ | 5 | 50 | 3 | 5 | 15 | +-+ 2 rows in set (0.00 sec) mysql > delete from view_t2 where price=5 Query OK, 1 row affected (0.00 sec) mysql > select * from view_t2; +-+ | qty | price | total | +-+ | 5 | 50 | 50 | +-+ 1 row in set (0.00 sec) mysql > select * from t +-+-+ | quantity | price | +-+-+ | 5 | 50 | +-+-+ 1 row in set (0.00 sec) 6. Delete view mysql > drop view view_t

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.