MySQL View Principles and Basic Operation Examples

This paper describes the principle and basic operation of MySQL view with examples. Share it for your reference, as follows:

Overview

A view is a virtual table whose contents are defined by a query. Like the real Table 1, the view contains 1 series of named column and row data. However, views do not exist in the database as stored data value sets. Row and column data comes from the table referenced by the query that defines the view and is generated dynamically when the view is referenced.

For the underlying table referenced in it, the view acts like a filter.

Basic operations

Create a view

CREATE VIEW view_test(qty,price,total) AS SELECT quantity,price,quantity*price FROM t;

Multi-table view

CREATE VIEW stu_class(id,name,glass) AS SELECT student.s_id,student.name,stu_info.glass FROM student,stu_info WHERE student.s_id = stu_info.s_id;

View view

DESCRIBE 视图名

DESC 视图名

View view basics

SHOW TABLE STATUS LIKE ' View name ';

View the table building information of the view

SHOWCREATE VIEW  View name 

View details in the VIEW table

All view definitions are stored in the views table under the information_schema database of mysql select View

select * from information_schema.views;

Modify View

REPLACE statement

CREATE OR REPLACE VIEW view_test AS SELECT * FROM t;

ALTER statement

ALTER VIEW view_test SELECT name FROM t;

Delete View

DROP VIEW IF EXISTS stu_glass

Example of table building statement

/* Get the system organization structure 
 * Including user name, user ID The name of the store to which it belongs and the store to which it belongs ID Creation time ...
 */
CREATE VIEW organizationTableView as select id,storename,regdate from v9_qd_account;

/* Gets the current day's Cpz Installation data (summarized by user group) 
 * Include users ID , Cpz Summary value ...
 */
CREATE VIEW TodayCpzTableView as select storeid,storename,count(id)as total,sum(tui_num)as tui_num from v9_qd_dev where days = DATE_FORMAT(NOW(),'%Y%m%d') group by storeid;

CREATE VIEW stu_class(id,name,glass) AS SELECT student.s_id,student.name,stu_info.glass FROM student,stu_info WHERE student.s_id = stu_info.s_id;

0

CREATE VIEW stu_class(id,name,glass) AS SELECT student.s_id,student.name,stu_info.glass FROM student,stu_info WHERE student.s_id = stu_info.s_id;

1

CREATE VIEW stu_class(id,name,glass) AS SELECT student.s_id,student.name,stu_info.glass FROM student,stu_info WHERE student.s_id = stu_info.s_id;

2

// Today 
DATE_FORMAT(NOW(),'%Y%m%d');

For more readers interested in MySQL related content, please check the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Related Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.