How to use view_mysql in online shop databases

How to use the View bitsCN.com in the online shop Database

How to Use views in online shop databases

The essence of a View is to save the search results of a SELECT statement in the form of a table. Therefore, a View is also called a false table or a pseudo table.

View, which dynamically organizes data from multiple physical tables. you can use it as if you were using a physical table.

View function:

1. specific rows or columns in the table can be exposed.

By limiting the user's SELECT operation permission on the actual table, the user is only granted the SELECT operation permission on the corresponding view,

To restrict users from reading specific rows or columns.

2. simplify complex SQL statements

3. you can restrict the scope of insertion/update. Use WITH CHECK POINT

[SQL]

Create view v_order (

Oid, odate, memo, uid, zip, address, name, pid, pname, price, quantity)

SELECT

Ob. oid, ob. odate, ob. memo,

U. uid, u.zip, u. address, u. name,

P. pid, p. pname, p. price,

Od. quantity

FROM

(

(

(Order_basic AS ob inner join order_detail AS od ON ob. oid = od. oid)

Inner join product AS p ON od. pid = p. pid

)

Inner join user AS u ON ob. uid = u. uid

);

[SQL]

Create or replace view v_order

AS SELECT

Ob. oid AS oid,

Ob. odate AS odate,

Ob. memo AS memo,

U. uid AS uid,

U.zip AS zip,

U. address AS address,

U. name AS name,

P. pid AS pid,

P. pname AS pname,

P. price AS price,

Od. quantity AS quantity

FROM

(

(

(Order_basic AS ob inner join order_detail AS od ON ob. oid = od. oid)

Inner join product AS p ON od. pid = p. pid

)

Inner join user AS u ON ob. uid = u. uid

);

[SQL]

Drop view v_order;

[SQL]

Show tables;

Show tables like 'V/_ % ';

Show fields from v_order;

Search in the view

[SQL]

SELECT * FROM v_order WHERE oid = 'd0000001'/G

UPDATE product SET pname = 'lampshade 'Where pid = 'p0002 ';

COMMIT;

View is not a form of actually saving data, but organizing data together.

When using views to change data (INSERT/UPDATE/DELETE), the operation may fail in the following situations:

1. columns in the view contain statistical functions.

2. the group by/HAVING statement, DISTINCT statement, and UNION statement are used for view definition.

3. subquery is used for view definition.

4. data changes across multiple basic tables

When creating a view, use the with check option command

[SQL]

Create view v_product3000up as select * FROM product WHERE price> = 3000 with check option;

In this case, if the price of the record inserted to the view v_product3000up is <3000, the insertion will fail.

View is a convenient function, but performance is not the best choice.

It simplifies complex SELECT statements, but it does not mean that the internal processing is simplified.

Use it with caution!

BitsCN.com