MYSQL Getting Started: basic view operations

MYSQL getting started 10: view basic operations link: MYSQL getting started 1: basic operations http://www.bkjia.com/database/201212/173868.htmlMYSQL Entry 2: Search Using Regular Expressions http://www.bkjia.com/database/201212/173869.htmlMYSQL Entry 3: Full Text Search http://www.bkjia.com/database/201212/173873.htmlMYSQL Entry 4: MYSQL Data Types http://www.bkjia.com/database/201212/175536.htmlMYSQL Entry 5: MYSQL Character Set http://www.bkjia.com/database/201212/175541.htmlMYSQL Getting started 6: MYSQL Operators http://www.bkjia.com/database/201212/175862.htmlMYSQL Entry 7: MYSQL common functions http://www.bkjia.com/database/201212/175864.htmlMYSQL Step 8: basic database and table operations http://www.bkjia.com/database/201212/175867.htmlMYSQL 9: simple indexing operations http://www.bkjia.com/database/201212/176772.html I. Basic Introduction to views www.2cto.com views are virtual tables. Unlike tables that contain data, a view only contains queries that Dynamically Retrieve data when used. To use a view, MySQL5 and later versions are required. The following are some common applications of views: reusing SQL statements; simplifying complex SQL operations; using table components instead of the entire table; protecting data; changing data formats and representations; after creating a view, you can use it in the same way as the table. However, for a large number of complex or nested views, the performance may be greatly reduced. Therefore, full testing should be carried out before the corresponding applications are deployed. 2. The rules and restrictions for using a view are the same as those for a table. The view must be uniquely named (the view cannot have the same name as other views or tables). There is no limit on the number of views that can be created; to create a view, you must have sufficient access permissions; the view can be nested; order by can be used in the view; the view cannot be indexed or have associated triggers or default values; views can be used with tables. 3. view 1. create view view_name AS select statement example: mysql> create or replace view v_pic_url-> as-> select-> id, url-> from v9_picture-> where catid = 17; 2. VIEW the statement "show create view viewname" for creating a VIEW. Example: mysql> show create view v_pic_url; + ----------- + ------------ Please refer + ---------------------- + -------------------- + | View | Create View | character_set_client | collation_connection | + ----------- + response ------------------------------------------------------------------------- Certificate --------- + certificate + -------------------- + | v_pic_url | create algorithm = undefined definer = 'root' @ 'localhost' SQL SECURITY DEFINER VIEW 'v _ pic_url 'AS select 'v9 _ picture '. 'id' AS 'id', 'v9 _ picture '. 'url' AS 'url' from 'v9 _ picture 'where ('v9 _ picture '. 'catid' = 17) | latin1 | latin1_swedish_ci | + ----------- + ------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- + -------------------- + ------------------------ + 3. delete a view drop view viewname. Example: mysql> drop VIEW v_pic_url; 4. to update the view structure, you can first DROP the view and CREATE it using the CREATE statement. You can directly use the create or replace view statement. 4. update view data normally. Views are updatable (that is, you can use INSERT, UPDATE, and DELETE for them ). Updating a view updates its base table. Adding or deleting rows to or from a view is actually adding or deleting rows to or from its base table. However, not all views are updatable. If the view definition has the following operations, you cannot update the view: GROUP (using group by and HAVING); join; subquery; and; Focus function; DISTINCT; Export (Computing) columns; Generally, views should be used for retrieval instead of update. Reference: MySQL required knowledge