View of MySQL

First, the definition of the view

A view is a virtual table or logical table that is defined as a SQL SELECT query statement with a connection. Because a database view is similar to a database table, it consists of rows and columns, so you can query data based on database tables. Its contents are defined by the query.
However, the view does not exist in the database as a stored set of data values, and the row and column data is derived from the table referenced by the query that defines the view, and is generated dynamically when the view is referenced. In a nutshell, a view is a table consisting of its defined results;

Second, the advantages and disadvantages of the view

Advantages:

1, the database view allows you to simplify complex queries, and with a database view, you only need to use simple SQL statements instead of complex SQL statements with multiple connections.

2, security. This is typically done by creating a view that defines the data that the view operates on. The user rights are then bound to the view. The way to do this is to use a feature: The GRANT statement can grant permissions on the view.

Disadvantages:　

  1. Performance: Querying data from a database view can be slow, especially if the view is created based on other views.

2. Table dependency: A view is created based on the underlying table of the database. Whenever you change the structure of a table associated with it, you must change the view.

Third, create a view

Grammar:

CREATE view name as  SQL statement

Ex

#两张有关系的表mysql>Select* fromcourse;+-----+--------+------------+| CID | CNAME | teacher_id |+-----+--------+------------+|1| Biology |1||2| Physics |2||3| Sports |3||4| Art |2|+-----+--------+------------+rowsinch Set(0.00sec) MySQL>Select* fromteacher;+-----+-----------------+| Tid | Tname |+-----+-----------------+|1| Mr. Zhang Lei | |2| Teacher Li Ping | |3| Liu Haiyan Teacher | |4| Zhu Yunhai Teacher | |5| Li Jie teacher |+-----+-----------------+rowsinch Set(0.00sec) #查询李平老师教授的课程名mysql>SelectCName fromCoursewhereteacher_id = (SelectTid fromTeacherwhereTname='Mr. Li Ping');+--------+| CNAME |+--------+| Physics | | Art |+--------+rowsinch Set(0.00sec) #子查询出临时表, as a basis for judging teacher_idSelectTid fromTeacherwhereTname='Mr. Li Ping'

Temp Table

 as Select  from where tname=' Mr. Li Ping '; #于是查询李平老师教授的课程名的sql可以改写为mysqlSelect  from where teacher_id = (select from teacher_view); +--------+| CNAME  |+--------+| Physics   | | Art   |+--------+inset (0.00 sec)

The staging table was created as an attempt toIv. Use of views

Mysql> CREATE VIEW TT as Select* fromCourse left JOIN teacher on teacher.tid =course.teacher_id; Query OK,0Rows Affected (0.02sec) MySQL>Select* fromtt;+-----+--------+------------+------+-----------------+| CID | CNAME | teacher_id | Tid | Tname |+-----+--------+------------+------+-----------------+|1| Harry |1|1| Mr. Zhang Lei | |2| Harry |2|2| Teacher Li Ping | |4| Harry |2|2| Teacher Li Ping | |5| Harry |2|2| Teacher Li Ping | |6| Harry |2|2| Teacher Li Ping | |3| Harry |3|3| Wang Haiyan Teacher |+-----+--------+------------+------+-----------------+rowsinch Set(0.01sec) cannot modify view data, verify for example: MySQL> INSERT into TT values (7,'haha',2,4,'Zhang Sanfeng teacher'); ERROR1471(HY000): The target table TT of the INSERT isNot Insertable-into

View CodeV. Modify the View

# Syntax: ALTER view view name as SQL statement MySQL> ALTER VIEW Teacher_view as Select* fromCoursewhereCid>3; Query OK,0Rows Affected (0.04sec) MySQL>Select* fromTeacher_view;+-----+-------+------------+| CID | CNAME | teacher_id |+-----+-------+------------+|4| xxx |2||5| yyy |2|+-----+-------+------------+rowsinch Set(0.00Sec

View CodeVii. Deleting views

# syntax: Drop view Name drop view Teacher_view

View of MySQL