MySQL view principles and basic operation examples

This article uses examples to illustrate the principles and basic operations of MySQL views. Share with you for your reference, 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 consists of a set of named columns and rows of data. However, views do not exist as sets of stored data values in the database. The row and column data comes from the tables referenced by the query that defines the view and is generated dynamically when the view is referenced.

A view acts like a filter on the underlying tables referenced in it.

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 視圖名

DESC 視圖名

View basic 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 the information_schema database of MySQL, which can be viewed with select

select * from information_schema.views;

Modify a view

REPLACE Statement

CREATE OR REPLACE VIEW view_test AS SELECT * FROM t;

ALTER Statement

ALTER VIEW view_test SELECT name FROM t;

Deleting a View

DROP VIEW IF EXISTS stu_glass

Example of creating a table

/*Get the system organizational structure* 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 (aggregated by user group)
 *Including user 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;

/*Get the Cpz installation data for each day of the month (aggregated by day and user group)
 *Includes date (day), user ID, Cpz summary value, CpzApp summary value, duplicate 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;

/*Get monthly Cpz installation data (summarized by month and user group)
 *Includes 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;

/*Get Cpz installation data according to the input IMEI()
 *Including IMEI, account ID, store ID, installation time, whether it is a repeated installation, number of installed apps, and phone model*/
CREATE VIEW QueryCpzDatumByIMEI as select storeid,storename,verify,tui_num,sn,idfa,imei,devms,installdate from v9_qd_dev;

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

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:

Detailed explanation of the usage and differences of MySQL views and indexes
A brief discussion on MySql views, triggers and stored procedures
Detailed explanation of creating a view (CREATE VIEW) and usage restrictions in MySQL
The difference between Update and select in MySQL for single and multiple tables, and views and temporary tables
mysql three tables connected to create a view
How to create a view on multiple tables in MySQL
Summary of MySQL view principles and usage examples
Using Python to solve the problem of MySQL view import and export dependency
Detailed analysis of the principles and usage of MySQL views

<<: Steps to set up Windows Server 2016 AD server (picture and text)

>>: How to implement simple data monitoring with JS