Mysql Study Notes 4 (view)
View
Operations on views not only simplify queries, but also improve the security view. In essence, a virtual table is similar to a real table, 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_joinasselect s.namefrom t_student as s,t_group as gwhere s.group_id=g.id and g.id=2
Outer join (left join and right join)
create view view_leftasselect s.namefrom t_student as s left join t_group as g on s.group_id=g.idwhere g.id=2
Subquery
create view view_childasselect s.namefrom t_student as swhere s.group_id in (select id from t_group)
Union of records (union and union all)
create view view_unionasselect id,name from t_studentunion allselect 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