MySQL view principles and basic operation examples

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

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;

SHOW TABLE STATUS LIKE 'view name';

SHOWCREATE VIEW view name

select * from information_schema.views;

CREATE OR REPLACE VIEW view_test AS SELECT * FROM t;

ALTER VIEW view_test SELECT name FROM t;

DROP VIEW IF EXISTS stu_glass

/*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');