Mysql Study Notes 4 (view), mysql Study Notes View

Mysql Study Notes 4 (view), mysql Study Notes View
View

Operations on views can simplify queries and improve security.

View:

Essentially, a virtual table is similar to a real table and contains a series of columns and row data with names. However, a view does not exist in the form of storing data values in the database. The row and column data are derived from the basic tables referenced by the query in the definition view and are dynamically generated when the view is referenced.

The view allows programmers to focus only on specific data of interest and specific tasks they are responsible. In this way, the programmer can only see the data defined by the view, rather than the data in the table referenced by the view. This improves the data shard security in the database.

Features:
1. View columns can come from different tables, which are the abstraction of tables and new relationships established in a logical sense.
2. View tables (Virtual tables) generated by basic tables (real tables)
3. Creating and deleting a view does not affect the basic table.
4. Updating (adding, deleting, and modifying) View content directly affects basic tables.
5. When a view comes from multiple tables, it is not allowed to add or delete data (this should be easy to understand, because multiple tables are involved. It is obviously inconvenient or unsafe to add a view after deletion)

Create

Create view viewname

Example:
Create table t_product (

Id int,

Name varchar (20 ),

Price float

)

// Create a view
Create view view_product as select id, product from t_product
Failed. It was originally defined as name. Modify the attribute.
Alter table t_product change name product varchar (20 );
Create a view again, OK
Select * from view_product
OK to hide the price field.

View family

Because the view is actually a query statement encapsulated (you can think of it), can any form of query statements be encapsulated into a view?

Query View
Constant query:

Create view view_const as select 3.1415926;

Aggregate functions (count min sum max, etc)

Create view view_fun as select count (name) from t_student

ORDER)

Create view view_order as select name from t_student order by id DESC

Internal Connection query statement

Create view view_join

As

Select s. name

From t_student as s, t_group as g

Where s. group_id = g. id and g. id = 2

Outer join (left join and right join)

Create view view_left

As

Select s. name

From t_student as s left join t_group as g on s. group_id = g. id

Where g. id = 2

Subquery
Create view view_child

As

Select s. name

From t_student as s

Where s. group_id in (select id from t_group)

Union of records (union and union all)

Create view view_union

As

Select id, name from t_student

Union all

Select id, name from t_group

View

Show tables, show table status (view is a special table, so you can view the table), show create view viewname
1. show table status like "view_product" \ G
The view_product created above is used as an example.
2. show create view view_product \ G
View view Definitions
3. describe | desc
Desc viewname

Delete View

Drop viewname [, viewname,...]
Multiple Views can be deleted at a time.

Modify View

1. create or replace view view_name

Take view_product as an Example

Now you want to hide the id field.

A. You can delete a view and recreate it.

B. create or replace view viewname

For example, create or replace view view_product as select id from t_product

In this way, you do not need to delete

2. alter Statement (guessed)

Alter view viewname

As query statement