Tutorial-concepts and basic usage of views

Quick start: use the goods table of ecshop to query the top three topics with the average price. Traditional method: selectcat_id, avg (shop_price) asapricefromgoodsgroupbycat_idorderbyapricedesclimit3;... "> <LINKhre

 

Quick start: use the goods table of ecshop to query the top three topics with the average price.

Traditional methods:

Select cat_id, avg (shop_price) as aprice from goods

Group by cat_id

Order by aprice desc limit 3;

New method: create a view

# Create a view:

Create view v1

Select cat_id, avg (shop_price) as aprice from goods;

# View view:

Show tables;

# View columns:

Desc View Name

# View view creation:

Show create view Name

# Delete a view:

Drop view Name

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 a visual chart

Create view v1

Select gooods_id, cat_id, goods_name, avg (shop_price) as aprice from esc_goods group by cat_id;

# Querying visual charts

Select * from v1 order by aprice limit 3;

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?

Product Name market price cost price unit brand

1 gold 446200 yuan/gram Chow Dafu

2 Platinum 375.94192 yuan/gram Chow Dafu

3 gold bars 456.87185.35 yuan/gram Zhou Dasheng

4 ornaments 461203.58 yuan/gram Zhou Dasheng

# Demonstrate how to control permissions through views

# Creating a jewelry table

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 several Records

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 fo ');

Insert into jewelry (name, price, cost, unit, brand) values ('golden bars ', 456.87, 185.35, 'Yuan/kn', 'chow dashen ');

Insert into jewelry (name, price, cost, unit, brand) values ('ornaments ', 461,203.58, 'Yuan/k', 'chow dashen ');

# Create a View

# Create a user and log on to MySQL

Mysql> insert into user (host, user, password, ssl_cipher, x509_issuer, x509_subject) values ('localhost'

Test', password ('000000 '),'','','');

# Refresh the system permission list

Mysql> flush privileges;

# For user authorization, you must use the root account to create permissions for a view (database name. View name)

Grant select, insert, update, delete on wcp. vj to test @ localhost;

# Deleting a user

C-table queries 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?

Table sharding:

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.

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

1iphone55999iosApple

2iphone4s4430iosApple

3htc 328w2930androidHTC

4htc G212250androidHTC

5htc one s2500androidHTC

6 lumia 9204590wp8Nokia

7 lumia 8002900wp7Nokia

8 nokia platinum 2300 MeeGoNokia

9 Galaxy 33480 androidsamsung

# Create a phone table

Create 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 eight records

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 outputs', 2300, 'meego ', 'Nokia ');

# Create a view vphone1

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

# Update a table to see its impact on the view

Conclusion: table update directly affects 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.

-> Select brand, avg (price) as aprice from phone

-> Group by brand;

# View a View

# Update vphone2

Mysql> update CIDR set aprice = aprice + 500;

ERROR 1288 (HY000): The target table VP2. the UPDATE is not updatable

Conclusion: However, it is not always changeable. you can change the table through the view only when the view and the original table are one-to-one.

# Insert records to vphone1,

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

ERROR 1423 (HY000): Field of view 'wcp. csv1' underlying table doesn' t have a default value

If there are no columns in the view and there are no default values in the table, insertion will fail.

# Changing the table structure

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 goods

-> Order by cat_id, shop_price desc) as temp

-> Group by cat_id;

Use views to achieve:

Mysql> create view vec

-> 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;

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;

In this way, we will return to our incorrect old path.

The reason is that the merge algorithm is used,

Three view algorithms (algorithm)

Mode Description Remarks

Merge. when executing a View, merge the View query statement and the view creation statement, and then execute the statement. Default

When executing a view query statement, you must first execute the create View statement and use it as a temporary table. then, you can query the View query statement.

Undefined is not defined. it is automatically selected,

Algorithm for changing views:

Create algorithm = temptable view name

Mysql> create algorithm = temptable view vec

-> Select goods_id, goods_name, cat_id, shop_price from goods

-> Order by cat_id, shop_price desc;