MySQL View introduction

Data preparation

Create a database, import data

[Email protected] ~]# mysql-uroot-p-E "CREATE database viewdb;"

Enter Password:

[Email protected] ~]# mysql-uroot-p viewdb< viewdb.sql

Enter Password:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| DB2 |

| liujing |

| MySQL |

| Performance_schema |

| SYS |

| viewdb |

| WORLDDB |

| Xiaowei |

+--------------------+

9 rows in Set (0.13 sec)

Mysql> Show tables;

+------------------+

| tables_in_viewdb |

+------------------+

| user |

| UserInfo |

+------------------+

2 rows in Set (0.00 sec)

View Overview

A view is a virtual table that is a table from one or more tables in a database. Views can also be defined on the basis of a view that already exists.

The role of the view

1. Simplifying work

2. Improve safety

3. Logical Independence

Syntax for creating views

CREATE [OR REPLACE] [algorithm] ={undefined| merge| TempTable}]

VIEW view_name [(column_list)]

As Select_statement

[with[cascaded| LOCAL] CHECK OPTION]

Algorithm algorithm for setting the view

cascaded| LOCAL is the qualifying condition.

cascaded: The default value, which must be updated to meet the conditions of all related views and tables.

LOCAL: Only the conditions defined by the view itself are met when updating.

Create a single table view

Mysql> CREATE View View_user as selectname,age from user;

Query OK, 0 rows affected (0.01 sec)

View

Mysql> select name,age from user;

+---------+------+

| name | Age |

+---------+------+

|   Tubeliu | 10 |

|   Kevin | 20 |

|   Mark | 30 |

|   July | 40 |

+---------+------+

4 rows in Set (0.00 sec)

Mysql> select * from View_user;

+---------+------+

| name | Age |

+---------+------+

|   Tubeliu | 10 |

|   Kevin | 20 |

|   Mark | 30 |

|   July | 40 |

+---------+------+

4 rows in Set (0.00 sec)

Updated the data in the view

mysql> Update View_user Set age = 20where name= ' Tubeliu ';

Query OK, 1 row affected (0.10 sec)

Rows matched:1 changed:1 warnings:0

View

Mysql> select * from View_user;

+---------+------+

| name | Age |

+---------+------+

|   Tubeliu | 20 |

|   Kevin | 20 |

|   Mark | 30 |

|   July | 40 |

+---------+------+

4 rows in Set (0.00 sec)

Mysql> select name,age from user;

+---------+------+

| name | Age |

+---------+------+

|   Tubeliu | 20 |

|   Kevin | 20 |

|   Mark | 30 |

|   July | 40 |

+---------+------+

4 rows in Set (0.00 sec)

The operation of the view also affects the contents of the real table.

Create a multi-table view

Mysql> Create Viewview_userinfo (name1,phone1) as select User.name,userinfo.phone fromuser,userinfo where Id=fid;

Query OK, 0 rows Affected (0.00 sec)

View

Mysql> select * from View_userinfo;

+---------+--------+

| name1 | Phone1 |

+---------+--------+

| Tubeliu | 12345 |

|  Kevin | 54321 |

|  Mark | 32145 |

|  July | 34521 |

+---------+--------+

4 rows in Set (0.00 sec)

Mysql> Select User.name,userinfo.phonefrom user,userinfo where Id=fid;

+---------+-------+

| name | Phone |

+---------+-------+

| Tubeliu | 12345 |

| Kevin | 54321 |

| Mark | 32145 |

| July | 34521 |

+---------+-------+

4 rows in Set (0.00 sec)

This article from "The Sun Snail" blog, declined reprint!

MySQL View introduction