MYSQL Getting started: Basic View operations _ MySQL

MYSQL Getting started: Basic View operations bitsCN.com

MYSQL Getting started: Basic View operations

Related links:

MYSQL: Basic operations

Http: // database/201212/173868 .html

MYSQL 2: use regular expressions to search

Http: // database/201212/173869 .html

MYSQL 3: full text search

Http: // database/201212/173873 .html

MYSQL entry 4: MYSQL data types

Http: // database/201212/175536 .html

MYSQL entry 5: MYSQL character set

Http: // database/201212/175541 .html

MYSQL getting started 6: MYSQL operators

Http: // database/201212/175862 .html

MYSQL entry 7: MYSQL common functions

Http: // database/201212/175864 .html

Step 8: basic database and table operations

Http: // database/201212/175867 .html

MYSQL entry 9: simple indexing operations

Http: // database/201212/176772 .html

I. basic introduction to views

A view is a virtual table. 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:

Reuse SQL statements;

Simplify complex SQL operations;

Use the table components instead of the entire table;

Protect data;

Change the data format and representation;

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.

II. View rules and restrictions

Like a table, a view must have a unique name (the view cannot be named the same as another view or table );

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;

Views cannot be indexed or associated triggers or default values;

Views can be used with tables;

III. use view

1. create a view

Create view view_name

AS

Select statement

Example:

Mysql> create or replace view v_pic_url

->

-> Select

-> Id, url

-> From v9_picture

-> Where catid = 17;

2. view the statement for creating a View

Show create view viewname;

Example:

Mysql> show create view v_pic_url;

+ ----------- + ------------------------------------------------------------------------------------ + ---------------------- + ------------------------ +

| View | Create View | character_set_client | collation_connection |

+ ----------- + ------------------------------------------------------------------------------------ + ---------------------- + ------------------------ +

| 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. update the View structure

You can DROP the view before using the CREATE statement to CREATE it;

You can also directly use the create or replace view statement;

4. Update view data

Generally, 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 (use group by and HAVING );

Join;

Subquery;

And;

Focus function;

DISTINCT;

Export (calculate) columns;

Generally, views should be used for retrieval instead of update.

Reference: MySQL required knowledge

BitsCN.com