Shulou(Shulou.com)06/01 Report--

Introduction to the View VIEW command:

The VIEW view is the stored SELECT statement data

1. Create view command format:

CREATE

[OR REPLACE]

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = {user | CURRENT_USER}]

[SQL SECURITY {DEFINER | INVOKER}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

Format: CREATE VIEW view_name AS select_statement creates a file named view_name

The view, the content of the view, is the content queried by the select statement.

Usage: CREATE VIEW sct AS SELECT Name,Cname,Tname FROM students,courses,teachers WHERE

Students.CID1=courses.CID AND courses.CID=teachers.TID

Store the CID equivalent data in the query students,courses,teachers table in the sct view.

2. Command function:

The SELECT data of the database is stored as a view, and the view is also equivalent to a virtual table. The table in the SELECT statement stored in the view is called the base table. The view can not be modified, but can only be modified based on the base table. Therefore, it is generally not recommended that MySQL create views.

3. Command parameters:

DISTING unique, non-repetitive

SELECT [DISTINCT] * FROM tb_name WHERE qualification

Select a unique, non-repeating entry that meets the criteria from the tb_ name table.

4. Example

1. See how the created table is created

Mysql > SHOW CREATE TABLE class\ G

* * 1. Row *

Table: class

Create Table: CREATE TABLE `class` (

`ID`int (11) NOT NULL AUTO_INCREMENT

`Name` char (20) NOT NULL

`Age` tinyint (4) NOT NULL

`Gender` varchar (10) NOT NULL

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

ERROR:

No query specified

5. Directly do not log in to the mysql client to modify the data in the mysql database

[root@lamp] # mysql-e 'CREATE DATABASE edb;' directly set up database edb

[root@lamp] # mysql-e 'SHOW DATABASES;'

+-+

| | Database |

+-+

| | information_schema |

| | edb |

| | hellodb |

| | mydb |

| | mysql |

| | performance_schema |

| | students |

| | test |

| | testdb |

+-+

[root@lamp ~] # mysql-e 'DROP DATABASE edb;' directly delete edb database

[root@lamp] # mysql-e 'SHOW DATABASES;'

+-+

| | Database |

+-+

| | information_schema |

| | hellodb |

| | mydb |

| | mysql |

| | performance_schema |

| | students |

| | test |

| | testdb |

+-+

[root@lamp ~] # mysql-e 'SELECT * FROM students.class;'# does not log in to the mysql client, but directly queries the data in the class table in the students database.

+-- +

| | ID | Name | Age | Gender | |

+-- +

| | 1 | Yang Guo | 22 |

| | 2 | Guo Jing | 46 |

| | 3 | Xiao Longnv | 18 |

| | 4 | Huang Rong | 40 |

+-- +

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.