Introduction to the usage of views in MYSQL (code example)

This article brings you an introduction to the usage of views in MYSQL (code examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. help.

1. What is a view

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

(Related recommendations: MySQL Tutorial)

2. Why use views

1. Reuse SQL statements

2. Simplify SQL query, quickly retrieve data

3. Only need to know part of the structure of the table

4. Protect data according to specific authorization

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

Notes
• After the view is created, it can be used in basically the same way as a table (query, filter, sort data, compare with other views or Link, (add, update))
• View is just a facility used to view data stored elsewhere. It does not contain data itself, and the returned data is also retrieved from other tables.
• Because the view itself does not contain data, indexing multiple table joins or nesting may have performance issues, which need to be tested

3, rules and restrictions

1. Table name must be unique (with other views and tables)

2. No restrictions on creating views

3. Sufficient permissions

4. Views can be nested, and you can query from other views to construct a view

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

6. Views cannot be indexed

7. Views can be used with tables

4. Use view

1. to create a view create view

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

3. Delete view drop view viewname

4. Update view, 1⃣️ drop first and then create 2⃣️ Directly use create or replace view

5. Use views to simplify complex join queries

5. Use views to simplify complex join queries

Create view

CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND   orderitems.order_num = orders.order_num

Use view

SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

6. Use the view to reformat the retrieved data

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

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

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

You can query the results directly through the view

SELECT * FROM vendorlocations;

7. Use views to filter unwanted data

create view custmeremaillist AS
SELECT cust_id ,cust_name,cust_email
from customers
where cust_email is not NULL;

Use views 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
    orderitems
WHERE order_num=20005;

Create views

CREATE VIEW orderitemsexpanded AS
SELECT
    order_num,
    prod_id,
    quantity,
    item_price,
    quantity * item_price AS expanded_price 
FROM
    orderitems
WHERE order_num=20005;

Using views

SELECT
    *
FROM
    orderitemsexpanded
WHERE order_num=20005;

9. Update views

Usually, views can be updated (insert, update, delete). Updating the view will update the base table. The view cannot be updated if it has the following definitions.

1. 分组（group by 和 having）
2. 联结
3. 自查询
4. 并
5. 聚合函数（min（）、count（）、sum（）等）
6. Distinct
7. 导出（计算）列

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

The above is the detailed content of Introduction to the usage of views in MYSQL (code example). For more information, please follow other related articles on the PHP Chinese website!