MySQL Learning notes View

A view is an abstraction of table data that is saved on disk, that is, extracting data from a table or a subset of rows or columns of tables that are presented to the consumer.

Let's start by listing the simplest syntax for a view operation in MySQL:

1. Create a view:

Create View  as      SELECT statement for example:createview    as Select  from t_fruit          where weight<=;

As far as I know, the statements after "as" must always start with "select", and it can be understood that the result of the creation of the view is a new table, which is the result of the SELECT statement, and the new "table" field corresponds to the name after "select". In the example above is name,place,weight, but can also use as or omit as alias form, change the new "table" field name, for example:

Create View  as    Select  as  from t_fruit          where weight<=;

Thus, all the field names contained in the "new table" are Myname,myplace,weight

The view above is created on top of a single table, or you can join queries based on multiple tables, or create them based on other views.

2. Use the view:

Once the view is created, the operation is the same as the table, love how to play the game, at least you go to the editor to lose statements, certainly will not error. The actual execution phase is different, for the select from query operation, there is no problem, but for the increment, delete, change, because the view is actually stored only the definition, these three operations will change the actual table below, and you see the view, the data may be from several tables, It can also be calculated by the and, average, and so on, once the insert, delete, or update, the changes will be particularly complex. For the sake of insurance, for some cases, adding and deleting changes directly by the MySQL system to be judged as misoperation, unable to succeed. There are a lot of changes here, only a few, such as changes involving two or more than two tables will fail, the modified NOT NULL column No value will fail, the value of the column values of the view by sum, count, etc. will fail.

3. Modify the View:

There are two methods, one is the regular alter and the other can be shared with the CREATE view

①alter method, simply change the creation of create to alter on the line, for example:

Alter View  as    Select  as  from t_fruit          where weight<=;

② 1th CREATE view it's missing. There is actually a way to create and modify views that can be shared: creating or replace, just replace create with these 3 words, others do not change

Create or Replace View  as    Select  as  from t_fruit          where weight<=;

4. Delete the view:

Drop View The view name, or Drop  View View 1, views 2,...

5. View the definition of each field in the view:

describe view name, or desc view name;

Deeper analysis to be continued ...

MySQL Learning notes View