试图管理

-- 创建试图

CREATE VIEW view_sys AS

SELECT *

FROM schema_db.table_name

WHERE app_key = '6b1ea867-fe1b-4890-beef-1311809244e8'

COMMENT '试图名称';

-- 查看数据库中的所有表和视图

SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- 通过信息模式查看视图

SELECT *

FROM INFORMATION_SCHEMA.VIEWS

WHERE TABLE_SCHEMA = 'schema_db';

-- 查看视图的创建语句

SHOW CREATE VIEW view_sys;

-- 查看视图的列信息

DESCRIBE view_sys;

SHOW COLUMNS FROM view_sys;

-- 修改视图定义

ALTER VIEW view_sys AS

SELECT *

FROM schema_db.table_name

WHERE app_key = '6b1ea867-fe1b-4890-beef-1311809244e8'

COMMENT '试图名称';

-- 替换视图（如果不存在则创建）

CREATE OR REPLACE VIEW view_sys AS

SELECT *

FROM schema_db.table_name

WHERE app_key = '6b1ea867-fe1b-4890-beef-1311809244e8'

COMMENT '试图名称';

-- 删除视图

DROP VIEW view_sys;

-- 删除多个视图

DROP VIEW IF EXISTS view_sys, view_sys1;

-- 查看视图的元数据信息

SELECT

v.TABLE_NAME as view_name,

v.VIEW_DEFINITION,

v.IS_UPDATABLE,

v.CHECK_OPTION,

t.TABLE_ROWS as estimated_rows,

t.UPDATE_TIME as last_updated

FROM INFORMATION_SCHEMA.VIEWS v

LEFT JOIN INFORMATION_SCHEMA.TABLES t

ON v.TABLE_SCHEMA = t.TABLE_SCHEMA AND v.TABLE_NAME = t.TABLE_NAME

WHERE v.TABLE_SCHEMA = 'schema_db';