How to Use views in online shop Databases

The essence of how to use a View in an online store database is to save the search results of a SELECT statement in the form of a table. Therefore, a View is also called: false table and pseudo table. The www.2cto.com view dynamically organizes data from multiple physical tables. Users can use the data as they do with physical tables. View: 1. You can publish specific rows or columns in a table. By limiting the user's SELECT Operation permission on the actual table, and only granting the user the SELECT Operation permission on the corresponding view, the user can only read specific rows or columns. 2. simplify complex SQL statements 3. limit the scope of pluggable/updatable SQL statements. Use with check point [SQL] CREATE VIEW v_order (oid, odate, memo, uid, zip, address, name, pid, pname, price, quantity) AS 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] CRES Ate 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; in the VIEW, search [SQL] SELECT * FROM v_order WHERE oid = 'd0000001' \ G UPDATE product SET pname = 'lampshade 'where pid = 'p0002'; COMMIT; view, instead of actually saving data, it is just a form of organizing data together. When you use a view to modify data (INSERT/UPDATE/DELETE), the following operations may fail: 1. columns in the view, including statistical functions 2. When the view is defined, when the group by/HAVING statement, DISTINCT statement, UNION Statement 3, and view definition are used, subquery is used. 4. when data is changed across multiple basic tables to create 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 <3000, the failure view will be inserted, which is a convenient function. However, 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!