Oracle/MySQL/SQL Server创建/删除视图

创建视图

• Oracle

  CREATE OR REPLACE VIEW V_USERASSELECT     ID,    NAME,    AGEFROM T_USER;COMMENT ON TABLE V_USER IS '用户视图';COMMENT ON COLUMN V_USER.ID IS 'ID';COMMENT ON COLUMN V_USER.NAME IS '名称';COMMENT ON COLUMN V_USER.AGE IS '年龄';

  
  
• MySQL

  CREATE OR REPLACE VIEW V_USERASSELECT     ID,    NAME,    AGEFROM T_USER;// 不支持写备注

  
  
• SQL Server

  CREATE VIEW V_USERASSELECT     ID,    NAME,    AGEFROM T_USERGOif exists (select 1 from  sys.extended_properties           where major_id = object_id('V_USER') and minor_id = 0)begin   declare @CurrentUser sysnameselect @CurrentUser = user_name()execute sp_dropextendedproperty 'MS_Description',    'user', @CurrentUser, 'view', 'V_USER'endselect @CurrentUser = user_name()execute sp_addextendedproperty 'MS_Description',    '用户视图',   'user', @CurrentUser, 'view', 'V_USER'GOif exists(select 1 from sys.extended_properties p where      p.major_id = object_id('V_USER')  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'ID'))begin   declare @CurrentUser sysnameselect @CurrentUser = user_name()execute sp_dropextendedproperty 'MS_Description',    'user', @CurrentUser, 'view', 'V_USER', 'column', 'ID'endselect @CurrentUser = user_name()execute sp_addextendedproperty 'MS_Description',    'ID',   'user', @CurrentUser, 'view', 'V_USER', 'column', 'ID'GOif exists(select 1 from sys.extended_properties p where      p.major_id = object_id('V_USER')  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'NAME'))begin   declare @CurrentUser sysnameselect @CurrentUser = user_name()execute sp_dropextendedproperty 'MS_Description',    'user', @CurrentUser, 'view', 'V_USER', 'column', 'NAME'endselect @CurrentUser = user_name()execute sp_addextendedproperty 'MS_Description',    '名称',   'user', @CurrentUser, 'view', 'V_USER', 'column', 'NAME'GOif exists(select 1 from sys.extended_properties p where      p.major_id = object_id('V_USER')  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'AGE'))begin   declare @CurrentUser sysnameselect @CurrentUser = user_name()execute sp_dropextendedproperty 'MS_Description',    'user', @CurrentUser, 'view', 'V_USER', 'column', 'AGE'endselect @CurrentUser = user_name()execute sp_addextendedproperty 'MS_Description',    '年龄',   'user', @CurrentUser, 'view', 'V_USER', 'column', 'AGE'GO

  
  

删除视图

   

DROP VIEW V_USER; // Oracle/MySQL/SQL Server均支持

DROP VIEW IF EXISTS V_USER; // 仅MySQL支持