基本操作

建立視圖

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;

查看視圖

DESCRIBE 視圖名

DESC 視圖名

查看視圖基本信息

SHOW TABLE STATUS LIKE '視圖名';

查看視圖的建表信息

SHOW　CREATE VIEW 視圖名

在VIEW表中查看視圖詳細信息

在mysql的information_schema數(shù)據(jù)庫下的views表中存儲了所有的視圖定義，可以用select查看

select * from information_schema.views;

修改視圖

REPLACE語句

CREATE OR REPLACE VIEW view_test AS SELECT * FROM t;

ALTER語句

ALTER VIEW view_test SELECT name FROM t;

刪除視圖

DROP VIEW IF EXISTS stu_glass

建表語句示例

/*獲取系統(tǒng)組織結(jié)構(gòu)

*包括用戶名稱、用戶ID、所屬店面名稱、所屬店面ID、創(chuàng)建時間...

*/

CREATE VIEW organizationTableView as select id,storename,regdate from v9_qd_account;

/*獲取當天的Cpz安裝數(shù)據(jù)(按用戶分組匯總)

*包括用戶ID、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;

/*獲取當月每天的Cpz安裝數(shù)據(jù)(按天、用戶分組匯總)

*包括日期(天)、用戶ID、Cpz匯總值、CpzApp匯總值、重復Cpz匯總值...

*/

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;

/*獲取每月的Cpz安裝數(shù)據(jù)(按月、用戶分組匯總)

*包括日期(天)、用戶ID、Cpz匯總值、CpzApp匯總值、重復Cpz匯總值...

*/

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;

/*根據(jù)輸入的IMEI獲取Cpz安裝數(shù)據(jù)()

*包括IMEI、賬戶ID、店面ID、安裝時間、是否重復安裝、安裝app個數(shù)、手機型號

*/

CREATE VIEW QueryCpzDatumByIMEI as select storeid,storename,verify,tui_num,sn,idfa,imei,devms,installdate from v9_qd_dev;

//今天

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

總結(jié)

以上是生活随笔為你收集整理的手机展示mysql实例_mysql 视图示例的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。