Example Analysis of the principle of MySQL View

This article mainly introduces the example analysis of the principle of MySQL view, which is very detailed and has certain reference value. Friends who are interested must finish it!

The details are as follows:

Overview

A view is a virtual table whose contents are defined by a query. Like a real table, a view contains a series of named columns and rows of data. However, the view does not exist in the database as a set of stored data values. Row and column data to freely define the table referenced by the view's query and dynamically generate when the view is referenced.

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

Basic operation

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 View name

DESC View name

View basic view information

SHOW TABLE STATUS LIKE 'View name'

View the table creation information of the view

SHOWCREATE VIEW View name

View view details in the VIEW table

All view definitions are stored in the views table under mysql's information_schema database, which can be viewed with select

Select * from information_schema.views

Modify the 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

Examples of table-building statements

/ * get the organizational structure of the system * including user name, user ID, store name, store ID, creation time. * / CREATE VIEW organizationTableView as select id,storename,regdate from v9_qd_account / * get the Cpz installation data for the day (summarized by user group) * including the total value of user ID and Cpz. * / 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 / * obtain Cpz installation data for each day of the month (summarized by day, user grouping) * including date (day), user ID, Cpz summary value, CpzApp summary value, repeat Cpz summary value. * / CREATE VIEW HistoryCurrentMonthDayView as select storeid,storename,count (id) as total,sum (tui_num) as tui_num,days from v9_qd_dev where days > DATE_FORMAT (NOW (),'% Y% m') group by storeid,days / * obtain monthly Cpz installation data (by month, user grouping summary) * including date (day), user ID, Cpz summary value, CpzApp summary value, duplicate Cpz summary value. * / CREATE VIEW HistoryMonthTableView as select storeid,storename,count (id) as total,sum (tui_num) as tui_num, DATE_FORMAT (FROM_UNIXTIME (installdate),'% Y% m') as months from v9_qd_dev group by storeid,months / * obtain Cpz installation data according to the entered IMEI () * including IMEI, account ID, store ID, installation time, whether to install repeatedly, the number of app installed, mobile phone model * / CREATE VIEW QueryCpzDatumByIMEI as select storeid,storename,verify,tui_num,sn,idfa,imei,devms,installdate from v9 / CREATE VIEW QueryCpzDatumByIMEI as select storeid,storename,verify,tui_num,sn,idfa,imei,devms,installdate from v9 / DATE_FORMAT (NOW (),'% Y% m% d') The above is all the contents of the article "sample Analysis of the principles of MySQL View". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!