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

How are views used in MYSQL? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

1. What is a view

Execute a SQL to save the result set in a virtual table

(recommended: MySQL tutorial)

2. Why use views

Reuse SQL statement

Simplify SQL query and fetch data quickly

You only need to know the partial structure of the table.

Protect data according to specific authorization

Change the data format and presentation, and the view can return data that is different from the representation and format of the underlying table.

Matters needing attention

After the view is created, it can be used in much the same way as a table (query, filter, sort data, join or join other views, (add, update))

A view is just a facility for viewing data stored elsewhere, does not contain data itself, and the returned data is retrieved from other tables.

Because the view itself does not contain data, there may be performance problems in indexing multiple table joins or nesting, which need to be tested

3. Rules and restrictions

Table names must be unique (with other views and tables)

There are no restrictions on creating views

Sufficient authority

Views can be nested and a view can be constructed by querying from other views

If both the view and the query from the view have order by, the order by in the view will be overwritten

Views cannot be indexed

Views can be used with tables

4. Use the view

Create a view create view

View the statement that creates the view. Show create view viewname

Delete View drop view viewname

Update the view, 1 '️first drop then create 2' ️directly use create or replace view

Using views to simplify complex join queries

5. Use views to simplify complex join queries

Create a view

CREATE VIEW productcustomers ASSELECT cust_name, cust_contact, prod_idFROM customers, orders, orderitemsWHERE customers.cust_id = orders.cust_idAND orderitems.order_num = orders.order_num

Working with View

SELECT cust_name, cust_contactFROM productcustomersWHERE prod_id = 'TNT2'

6. Reformat the retrieved data with the view

Select concat (rtrim (vend_name),'(', rtrim (vend_country),') as vend_title from vendors order by vend_name

If you use this format frequently, you can create a view

CREATE VIEW vendorlocations ASSELECT concat (rtrim (vend_name),'(', rtrim (vend_country),') AS vend_title FROM vendors ORDER BY vend_name

The result can be obtained directly through the view query.

SELECT * FROM vendorlocations

7. Use views to filter unwanted data

Create view custmeremaillist ASSELECT cust_id, cust_name,cust_emailfrom customerswhere cust_email is not NULL

Use the view directly

SELECT * from custmeremaillist

8. Use views and calculated fields

Mysql query

SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price FROM orderitemsWHERE order_num=20005

Create a view

CREATE VIEW orderitemsexpanded ASSELECT order_num, prod_id, quantity, item_price, quantity * item_price AS expanded_price FROM orderitemsWHERE order_num=20005

Working with View

SELECT * FROM orderitemsexpandedWHERE order_num=20005

9. Update the view

Typically, views are updatable (insert, update, delete). Updating the view updates the base table. If there are the following definitions in the view, they cannot be updated.

1. Grouping (group by and having) 2. Link 3. Self-query 4. And 5. Aggregate functions (min (), count (), sum (), etc.) 6. Distinct7. Export (calculated) column

So it's best to use the select query directly for the view.

Thank you for reading! After reading the above, do you have a general idea of how to use views in MYSQL? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.