Mysql tutorial-View concepts and basic usage

Quick start: use the goods table of ecshop to query the first three items of average price.

Traditional methods:

select cat_id,avg(shop_price) as aprice from goods group by cat_id order by aprice desc limit 3;

# Create view v1 as select cat_id, avg (shop_price) as aprice from goods; # view: Show tables; # view column: Desc view Name # view creation: show create view Name # Delete view: Drop view Name

New method: create a view

Benefits of a view:

1. simplified query.

2. permission control.

3. table sharding query.

4. good maintainability. ,

A. simplified query. the code implemented in the preceding example is as follows:

Create view v1 as Select gooods_id, cat_id, goods_name, avg (shop_price) as aprice from esc_goods group by cat_id; # Query view chart Select * from v1 order by aprice limit 3;

No.	Product Name	Market price	Cost	Unit	Brand
1	Gold	446	200	Yuan/gram	Chow Tai Fu
2	Platinum	375.94	192	Yuan/gram	Chow Tai Fu
3	Gold bars	456.87	185.35	Yuan/gram	Zhou Dasheng
4	Ornaments	461	203.58	Yuan/gram	Zhou Dasheng

B permission control, as shown in the table below, is maintained by Xiao Liu. He needs to develop and manage the sales software, but cannot see the cost price. what should he do?

# Demonstrate how to control permissions through views

# Create table jewelry (id int unsigned not null auto_increment primary key, name varchar (20) not null, price decimal (10, 2) not null, cost decimal (10, 2) not null, unit varchar (20) not null, brand varchar (30) not null );

Insert into jewelry (name, price, cost, unit, brand) values ('gold', 446,200, 'Yuan/k', 'chow fo'); insert into jewelry (name, price, cost, unit, brand) values ('Platinum ', 375.94, 192, 'Yuan/k', 'chow Tai fo'); insert into jewelry (name, price, cost, unit, brand) values ('golden bars ', 456.87, 185.35, 'Yuan/k', 'Zhou Dasheng'); insert into jewelry (name, price, cost, unit, brand) values ('ornaments ', 461,203.58, 'Yuan/ck', 'chow Dasheng ');

# Insert several Records

# Create a View

# Create a user and log on to MySQLmysql> insert into user (host, user, password, ssl_cipher, x509_issuer, x509_subject) values ('localhost' test', password ('123 '), '','', ''); # refresh the system permission table mysql> flush privileges; # use the root account for User authorization, // create permissions for a view (database name. view Name) grant select, insert, update, delete on wcp. vj to test @ localhost;

Table sharding: queries in the C-table sharding mode to accelerate the query speed. Suppose there is a massive data table, such as the QQ account table, which indicates that the logon time is too long. Is there a way to optimize it?

The qq user table can be divided into 10 tables by qq number remainder,

Qq1, qq2, qq3, qq4, qq5,

How to query?

10003% 10 = 3

Create view vq

Slect * from qq1 union select * from qq2

D. good maintainability. James works in website development and maintenance at the company. due to business changes, there are two tables a and B in the database. now we need to combine them into a c table, can you give him an idea?

Table

Table B

Table C

You can use views to combine Table A and Table B into A table C, and then create the views of the original table A and Table B respectively from Table c, in this way, the program only needs to be slightly changed, and the original query and other statements are still used.

# P # in-depth view # e #

In-depth understanding of view-View algorithms and their relationships with tables

1. what is the relationship between views and tables?

A view is a virtual table. it does not exist physically, but can be used as a table.

Using a view can save space.

2. how does a view work?

When using the view, reference the corresponding table and query the result.

3. Will operations between views and tables affect each other?

No.	Name	Price	Operating system	Brand
1	IPhone 5	5999	Ios	Apple
2	Iphone4s	4430	Ios	Apple
3	Htc 328 w	2930	Android	HTC
4	Htc G21	2250	Android	HTC
5	Htc one s	2500	Android	HTC
6	Lumia 920.	4590	Wp8	Nokia
7	Lumia 800.	2900	Wp7	Nokia
8	Nokia Lightning	2300	MeeGo	Nokia
9	Galaxy 3	3480	Android	Samsung

# Create a phone table

reate table phone(id int unsigned not null auto_increment primary key,name varchar(30) not null,price decimal(7,2) not null,os varchar(30) not null,brand varchar(30) not null);

insert into phone(name,price,os,brand) values('iphone5',5999,'ios','Apple');insert into phone(name,price,os,brand) values('iphone4s',4430,'ios','Apple');insert into phone(name,price,os,brand) values('htc328w',2930,'android','HTC');insert into phone(name,price,os,brand) values('htc G21',2250,'android','HTC');insert into phone(name,price,os,brand) values('htc one s',2500,'android','HTC');insert into phone(name,price,os,brand) values('lumia920',4590,'wp8','Nokia');insert into phone(name,price,os,brand) values('lumia800',2900,'wp7','Nokia');insert into phone(name,price,os,brand) values('nokia n9',2300,'meego','Nokia');

# Insert eight records

# Create a view vphone1

mysql> create view vp1 as select name,price,brand from phone;

Conclusion: table update directly affects the view. # Update a table to see its impact on the view

# View a View

# Update a view to see its impact on the table

Update vp1 set price = price-500;

Conclusion: changing the view can change the table. But it cannot always be changed. you can change the table through the view only when the view and the original table have one-to-one correspondence.

# Create another view, vphone2

mysql> create view vp2 as    -> select brand,avg(price) as aprice from phone    -> group by brand;

# Update vphone2 # View

  mysql> update vp2 set aprice = aprice + 500;ERROR 1288 (HY000): The target table vp2 of the UPDATE is not updatable

# Insert a record to vphone1. Conclusion: The record is not always changeable. you can change the table through the view only when the view and the original table are one-to-one.

mysql> insert into vp1 values('galaxy '3470','samsung','android'); ERROR 1423 (HY000): Field of view 'wcp.vp1' underlying table doesn't have a default value

# If you change the table structure for columns that do not appear in the view and have any default values in the table, insertion will fail.

Mysql> alter table phone modify OS varchar (30) not null default '';

# Insert a record to vphone1

Mysql> insert into P0 values ('Galaxy '000000', 'Samsung ', 'Android ');

4. View usage considerations

A. a view is also a type of table. it is a virtual table, or the table and view share the same namespace in the database. it cannot be duplicated with an existing table (view.

B. The View belongs to the database. By default, a new view is created for the current database.

View algorithm

1. how can I use a view to query the most expensive items in each topic?

Original practice:

mysql> select goods_id,goods_name,cat_id,shop_price-> from ( select goods_id,goods_name,cat_id,shop_price->from goodsorder by cat_id,shop_price desc) as temp-> group by cat_id;

mysql> create view vec as   -> select goods_id,goods_name,cat_id,shop_price    -> from goods  order by cat_id,shop_price desc;mysql> select * from vec group by cat_id;

Use views to achieve:

The result is incorrect. why?

Combine the statements used to create and query views.

select * from select goods_id,goods_name,cat_id,shop_price from goods group by cat_id order by cat_id,shop_price desc;

The reason is that the merge algorithm is used, which returns to our incorrect path.

Three view algorithms (algorithm)

Mode	Description	Remarks
Merge	Merge: when executing a View, merge the query View statement and create View statement, and then execute the statement.	Default
Temptable	For a temporary table, when you execute a view query statement, first execute the create View statement and use it as a temporary table, and then query the statements in the query view.
Undefined	Undefined, automatically selected,

Algorithm for changing views:

Create algorithm = temptable view name asmysql> create algorithm = temptable view vec as-> select goods_id, goods_name, cat_id, shop_price-> from goods order by cat_id, shop_price desc;