MySQL database view

MySQL Database view

The view in the database is a virtual table. As with real tables, row and column data, rows and columns, are used to customize the tables referenced by the view query and are generated dynamically when the view is referenced. You can isolate users from data and keep them safe, and quickly query data.

The Mysql5.0 version supports views and can use SELECT, insert INTO, UPDATE, delete, and so on. Changes to the base table or the view, regardless of whether they are modified.

The role of the view:

Compared to reading directly from the data table:

1, simplified: see is needed.

2, security: Through the View users can only query and modify the data they can see. No other data can be seen or taken.

3. Logical data Independence: Views can help users block the impact of changes in the real-world table structure.

To create a view:

The view contains the results of a select query, so the view is created based on a SELECT statement and an existing data table, which can be built on a single table or on multiple tables.

Grammar:

create [or replace] [algorithm = {undefined | merge | temptable}]view view_name [(column_list)]as Select_statment[with [C ascaded | Local] Check option]create # CREATE VIEW replace # replacement view algorithm # View algorithm: Undefined system chooses its own algorithm; Merge indicates that the view statement is merged with the view definition so that the view definition partially overrides the statement counterpart TempTable means that the view results are stored in a temporary table, and a temporary table is used to execute the statement. View_name # View Name column_list # Property Column Select_statment # SELECT statement [with [cascaded | local] Check OPTION] # indicates permission range: CAs Caded indicates that all conditions are met. Local indicates that the condition of the view itself is met when updating.

Single table Creating a view:

Mysql> CREATE DATABASE view;mysql> use view;mysql> CREATE TABLE t (Quantity int,price INT);mysql> INSERT INTO T values (3,50);mysql> CREATE View view_t as Select Quantity,price,quantity * Price from t;mysql> select * from View_ T

# The view created by default is the same as the field for the base table, or you can create a view by specifying the name of the View field.

mysql> CREATE View View_t2 (qty,price,total) as Select Quantity,price,quantity * Price from t;mysql> select * FROM VI Ew_t2;

# view_t and View_t2 have a different field name in two views, but the data is the same. Therefore, when using the view, it is possible that the user does not need to understand the table's organization, and has no access to the data in the actual table, thus ensuring the security of the database.

To create a view on multiple tables:

Mysql> CREATE DATABASE stud;mysql> use stud;mysql> CREATE TABLE student (ID int,name char);mysql> create T Able Stu_info (id int,name char (), addr char);mysql> show tables;mysql> insert INTO student values (1, ' Zhouyi ') , (2, ' zhaoer '), (3, ' Zhangsan ');mysql> insert into student values (1, ' Zhouyi ', ' Beijing '), (2, ' zhaoer ', ' Shanghai '), (3 , ' Zhangsan ', ' Chengdu ');mysql> select * FROM stu_info;mysql> SELECT * from student;mysql> CREATE VIEW Stu_glass (i D,name,glass) as select Student.id,student.name,stu_info.addr from Student,stu_info where student.id=stu_info.id; Mysql> select * from Stu_glass;

# you can see that the view ID is student.id

# name is Student.name

# Glass is stu_info.addr

To view the view:

Mysql> Show Table status like ' Stu_glass ' \g;    # All is Null,comment:view represents the virtual table mysql> desc stu_glass;     # View basic Information mysql> show create View stu_glass\g;    # View creation Information mysql> select * from Information_schema.views\g; # View more information

To modify a view:

mysql> use view;mysql> desc view_t;mysql> alter view view_t as select quantity from T; or:mysql> Create or REPL Ace View view_t as SELECT * FROM t;mysql> desc view_t;

To update the view:

Mysql> ALTER view view_t as select quantity from T; # Modify view and meta data different mysql> select * FROM view_t;mysql> SELECT * from t;mysql> update view_t set quantity=5;mysql> Sele CT * FROM view_t;mysql> SELECT * FROM view_t2;mysql> SELECT * from t;# all updates

Insert data:

Mysql> INSERT into T values (3,5);        # Insert Data mysql> SELECT * FROM view_t;mysql> SELECT * FROM view_t2;mysql> select * from T; # Insert mysql> Delete from view_t2 where price=5;# delete price=5 data in View_t2 view;mysql> select * from View_t2;mysql> Selec The action of removing price=5 from t;# in View_t2 is ultimately achieved through related records in the base table. Note: When the view contains the following content, the update operation for the view will not be performed: 1. The view does not contain columns that are defined as non-empty in the base table, 2, and a mathematical expression is used in the field list after the SELECT statement that defines the view; 3. An aggregate function is used in the field list after the SELECT statement for the definition view, 4, distinct, union, top, group, having clauses are used in the SELECT statement that defines the view.

To delete a view:

Mysql> drop view if exists view_t2;mysql> show create View view_t2; ERROR 1146 (42S02): Table ' view.view_t2 ' doesn ' t exist

MySQL database view