创建视图

创建视图 - “定义新视图

句法

CREATE [OR REPLACE] VIEW [schema.]view [ ( column_name [ ,column_name ]... ) ] AS  选择  query

描述

创建视图 defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query. The column_name list is optional. However, if the column_name list is provided, the number of columns in the column_name list must be identical to the number of columns in the 选择 query. If a schema name is specified (for example, 创建视图 myschema.myview …​) then the view is created in the specified schema; otherwise it is created in the current schema. The view name must be distinct from the name of any other view or table in the schema that the view is being created in. Use 掉落视图 to drop views (See 掉落视图 )。

When the OR REPLACE clause is used for an existing view name, the effect is that of dropping the existing view and creating a new one with the given specification. OR REPLACE does not have any effect on the command if a view with the specified name does not already exist.

无法在临时表上创建视图。

使用创建或替换语句时,与两个不同的事务同时创建相同的数据库对象时,可能会返回以下错误:
duplicate value in unique index ...
如果通过基于表达式的索引调用用户定义的函数,则任何删除或修改函数的尝试都会使用错误警告,但不会重新创建索引。

The 创建视图 command used to create each view is stored in SYSTEM.TABLES in the VIEWDEFINITION column. Any tables used in view definitions are stored in SYSTEM.VIEW_TABLES. See 表系统表描述, View_Tables系统表说明 , 和 例1 below.

参数

schema

Name of the schema in which the view will be created. The default is the current schema as defined by the command. If schema is provided, and the schema does not exist, it will be created. Views cannot be created in the SYSTEM schema. This schema is reserved for database metadata.

view

要创建的视图的名称(可选地模式)。

column_name

An optional list of column names to be used as columns for the view. If specified, these names override the column names that would be used based on the 选择 query. Each column_name defined for a view must be unique.

query

A 选择 statement (see 选择 ) which provides the columns and rows of the view. If the optional column_name list is not provided when creating the view, the column names defined for the view are the same as the 选择 statement column_list names (see 例1 )。

If joining multiple tables, and the 选择 statement column_list includes a column that is common to multiple joined tables, the column in the 选择 statement column_list must be qualified by a table name or, if used, table name alias.

If the optional column_name list is not provided when creating the view, and the 选择 statement column_list includes duplicate column names, those duplicate names must be assigned alias column names, using AS. All column names defined in a view must be unique.

Using an ORDER BY in a VIEW definition is not recommended. To improve performance, the NuoDB optimizer will ignore ordering of the result set returned by the VIEW, unless a LIMIT or OFFSET is supplied in the VIEW definition, along with the ORDER BY (see 例2. )。让’s say you have defined a VIEW with an ORDER BY and now you want to 选择 from the VIEW and order the results differently. The execution plan for both of the ORDER BY statement would need to perform two sorts, one for the ORDER BY in the VIEW definition, and one for the ORDER BY for the 选择 statement. However, because the NuoDB optimizer eliminates the ORDER BY in the VIEW definition, the execution plan for this 选择 statement requires only one sort, which results in a performance improvement.

例子

示例1:创建没有列列表的视图
 选择  * FROM hockey.hockey where name like '%BERG%';
 ID  NUMBER        NAME        POSITION   TEAM
 --- ------- ----------------- --------- ------
  1    37    PATRICE BERGERON   Forward  Bruins
 21    44    DENNIS SEIDENBERG  Defense  Bruins

SELECT playerid, firstname, lastname, height, weight, position, birthcountry
  FROM hockey.players
 WHERE lastname LIKE 'Bergeron%';
 PLAYERID   FIRSTNAME  LASTNAME  HEIGHT  WEIGHT  POSITION  BIRTHCOUNTRY
 --------- ----------- --------- ------- ------- --------- -------------
 bergeje01 Jean-Claude Bergeron    74      192       G        Canada
 bergema02 Marc-Andre  Bergeron    70      197       D        Canada
 bergemi01 Michel      Bergeron    70      170       R        Canada
 bergepa01 Patrice     Bergeron    72      186       C        Canada
 bergeyv01 Yves        Bergeron    69      165       R        Canada


CREATE VIEW player_stats  AS
SELECT number, name
            , hockey.position
            , team, height, weight, birthcountry
  FROM hockey.hockey
    JOIN hockey.players ON name = UPPER(CONCAT(firstname, ' ', lastname));

SELECT * FROM player_stats WHERE name LIKE '%BERGE%';
 NUMBER        NAME       POSITION   TEAM  HEIGHT  WEIGHT  BIRTHCOUNTRY
 ------- ---------------- --------- ------ ------- ------- -------------
   37    PATRICE BERGERON  Forward  Bruins   72      186      Canada

SET OUTPUT VERTICAL;
SELECT * FROM system.tables WHERE tablename='PLAYER_STATS';
==================================== Row #1 ====================================
TABLENAME: PLAYER_STATS
SCHEMA: HOCKEY
EXTENDS: <null>
TYPE: VIEW
SUBTYPE: 0
TABLEID: 88
CURRENTVERSION: <null>
REMARKS: <null>
VIEWDEFINITION: VIEWDEF VER='2.0' SCHEMA="HOCKEY" 创建视图  player_stats  AS SELECT number, name             , hockey.position               ,
team, height, weight, birthcountry   FROM hockey.hockey
JOIN hockey.players ON name = UPPER(CONCAT(firstname, ' ', lastname));

CARDINALITY: <null>
RECORDSBATCHFACTOR: <null>
示例2:使用列名称列表创建视图
CREATE VIEW vw_team_rooster
    (team_name, year, conference, division, rank, position
   , player_name)
AS
 选择  t.name, t.year, t.conferenceid, t.divisionid, t.rank, s.position
   , p.lastname||','||p.firstname
  FROM teams t
    JOIN scoring s ON s.year = t.year and s.teamid = t.teamid
    JOIN players p ON p.playerid = s.playerid
 WHERE t.rank = 1
  ORDER BY year desc, conferenceid, divisionid, position;

 选择  * FROM vw_team_rooster
  WHERE year = '2011' AND conference = 'EC' AND division = 'NE';
   TEAM_NAME   YEAR  CONFERENCE  DIVISION  RANK  POSITION     PLAYER_NAME
 ------------- ----- ----------- --------- ----- --------- -----------------
 Boston Bruins 2011      EC         NE       1       C     Peverley,Rich
 Boston Bruins 2011      EC         NE       1       C     Sauve,Max
 Boston Bruins 2011      EC         NE       1       C     Rolston,Brian
 Boston Bruins 2011      EC         NE       1       C     Seguin,Tyler
 Boston Bruins 2011      EC         NE       1       C     Krejci,David
 Boston Bruins 2011      EC         NE       1       C     Kelly,Chris
 Boston Bruins 2011      EC         NE       1       C     Horton,Nathan
 Boston Bruins 2011      EC         NE       1       C     Marchand,Brad
示例3:订购结果的结果

For performance reasons, the NuoDB optimizer will ignore the ORDER BY in a view definition unless a LIMIT and / or OFFSET is also used.

DROP VIEW IF EXISTS vw_hockey;
CREATE VIEW vw_hockey
AS
 选择  id, number, name, position, team
  FROM hockey ORDER BY name;

 选择  * FROM vw_hockey;
 ID  NUMBER        NAME        POSITION   TEAM
 --- ------- ----------------- --------- ------

  1    37    PATRICE BERGERON   Forward  Bruins
  2    48    CHRIS BOURQUE      Forward  Bruins
  3    11    GREGORY CAMPBELL   Forward  Bruins
  4    18    NATHAN HORTON      Forward  Bruins
  5    23    CHRIS KELLY        Forward  Bruins
  6    46    DAVID KREJCI       Forward  Bruins
...

DROP VIEW IF EXISTS vw_hockey;
CREATE VIEW vw_hockey
AS
 选择  id, number, name, position, team
  FROM hockey
 ORDER BY name LIMIT 5;

 选择  * FROM vw_hockey;
 ID  NUMBER       NAME      POSITION   TEAM
 --- ------- -------------- --------- ------

 19    45    AARON JOHNSON   Defense  Bruins
 20    54    ADAM MCQUAID    Defense  Bruins
 17    21    ANDREW FERENCE  Defense  Bruins
 22    35    ANTON KHUDOBIN  Goalie   Bruins
  9    63    BRAD MARCHAND   Forward  Bruins
...