当前位置：首页 > 范文大全 > 实用文>MSSQL SERVER数据库视图

MSSQL SERVER数据库视图

2022-06-25 08:08:18 收藏本文下载本文

“心理学家”通过精心收集，向本站投稿了8篇MSSQL SERVER数据库视图，下面是小编收集整理后的MSSQL SERVER数据库视图，供大家参考借鉴，欢迎大家分享。

MSSQL SERVER数据库视图

篇1：MSSQL SERVER数据库视图

MSSQL SERVER数据库-视图

“视图是由基于一个或多个表的一个查询所定义的虚拟表，它将查询的具体定义保存起来，视图不包含任何数据，” 前面这句是我在网上找的视图的定义，对于初接触MSSQLSERVER视图的人，一眼读过去，可能也不解其意，不过没有关系，大概记住就行了，在实际使用过后再来看这句，你就知道他在讲什么了。

视图在操作上和数据表没有什么区别，但两者的差异是在其本质上的。数据表是实际存储记录的地方，然而视图并不是保存任何记录，它存储的实际上是查询语句。相同的数据表，根据不同用户的不同需求，可以创建不同的视图。

视图的作用主要是用来查询，但也可以对视图进行增删改的操作，对视图的增删改操作实际是通过视图的变化引起基表数据的变化。这篇文章不会写到对视图的增删改操作，因为我认为没有必要对视图进行这方面的操作，我现在的理解仅是将视图作为查询操作，在实际应用上也是如此。

可们首先来看一下系统视图

（图1）

从图1我们可以看，MSSQL数据里内置了如此之多的系统视图。那么这些视图里面存放的是什么东西

1、我们输入SQL语句：

select * from sys.tables

结果显示：

name object_id principal_id schema_id parent_object_id type type_desc create_date

student 133575514 NULL 1 0 U USER_TABLE -08-04 16:03:00.613

Employee 597577167 NULL 1 0 U USER_TABLE 2012-08-04 16:53:40.557

comment 709577566 NULL 1 0 U USER_TABLE 2012-08-05 21:25:56.637

article 741577680 NULL 1 0 U USER_TABLE 2012-08-05 21:27:46.980

teacher 2121058592 NULL 1 0 U USER_TABLE 2012-08-04 14:41:58.470

从name字段可以看过，我们把当前数据库的全部表名都查找出来了。

2、我们输入SQL语句：

select * from sys.databases

从name字段可以看过，我们把当前数据库的全部数据库名都查找出来了。

3、我们输入SQL语句

select * from

INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='student'

结果显示

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION

MySchool dbo student sid 1

MySchool dbo student sname 2

MySchool dbo student sage 3

MySchool dbo student ssex 4

MySchool dbo student tid 5

我们可以从 COLUMN_NAME获得student表的全部字段，

那么你可能会问得到这些系统视图有什么用我实际用过一次，可以根据这些系统视图所获得的内容来做某种类型的代码生成器。

SQL视图的其他应用举列：

--如果存在该数据库那么先把他删除掉再创建

if exists(select * from sys.database where name='test')

drop database test

create database test

--如果存在该表那么先把他删除掉再创建

if exists(select * from sys.objects where name = 'test')

drop table test

create table test

(

tid int primary key identity(1,1),

tName nvarchar(50) null,

tAge int

)

--为该表创建一个年龄检测约束，约束范围在18-70岁之间，如果存在该约束则删除后创建

if exist(select * from sys.objects where name='UQ_TEST_TNAME'

alter table test

drop constraint UQ_TEST_TNAME

alter table test

add constraint UQ_TEST_TNAME CHECK(tAge>=18 and tAge<=70)

怎么创建视图

create view v_Student

select sname,sage from student

select * from v_Student;

创建好后就可当作一个表来使用 select * from v_Student

怎么删除视图

if exists(select * from sys.objects where name='v_Student')

drop view v_Student

怎么修改视图

alter view v_Student

select sname,sage from student where sid=1

视图的注意事项

视图不包括结合

视图不包括Group By子句

视图不包括Union语句

视图不包含对伪字段RowNum的任何引用

视图不包含任何组函数

不能使用Distinct子句

Where子句包含的嵌套的表表达式不能与From子句引用同一个表

作者春晓

篇2：如何创建MySQL5的视图数据库

基本语法： CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [( column_list )] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] This statement creates a new view, or replaces an existing one if the

基本语法：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    VIEWview_name[(column_list)]    ASselect_statement[WITH [CASCADED | LOCAL] CHECK OPTION]

This statement creates a new view, or replaces an existing one if theOR REPLACEclause is given. Theselect_statementis aSELECTstatement that provides the definition of the view. The statement can select from base tables or other views.

This statement requires theCREATE VIEWprivilege for the view, and some privilege for each column selected by theSELECTstatement. For columns used elsewhere in theSELECTstatement you must have theSELECTprivilege. If theOR REPLACEclause is present, you must also have theDELETEprivilege for the view.

A view belongs to a database. By default, a new view is created in the current database. To create the view explicitly in a given database, specify the name asdb_name.view_namewhen you create it.

mysql>CREATE VIEW test.v AS SELECT * FROM t;

Tables and views share the same namespace within a database, so a database cannot contain a table and a view that have the same name.

Views must have unique column names with no duplicates, just like base tables. By default, the names of the columns retrieved by theSELECTstatement are used for the view column names. To define explicit names for the view columns, the optionalcolumn_listclause can be given as a list of comma-separated identifiers. The number of names incolumn_listmust be the same as the number of columns retrieved by theSELECTstatement.

Columns retrieved by theSELECTstatement can be simple references to table columns. They can also be expressions that use functions, constant values, operators, and so forth.

Unqualified table or view names in theSELECTstatement are interpreted with respect to the default database. A view can refer to tables or views in other databases by qualifying the table or view name with the proper database name.

A view can be created from many kinds ofSELECTstatements. It can refer to base tables or other views. It can use joins,UNION, and subqueries. TheSELECTneed not even refer to any tables. The following example defines a view that selects two columns from another table, as well as an expression calculated from those columns:

mysql>CREATE TABLE t (qty INT, price INT);mysql>INSERT INTO t VALUES(3, 50);mysql>CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;mysql>SELECT * FROM v;+------+-------+-------+| qty | price | value |+------+-------+-------+| 3 | 50 | 150 |+------+-------+-------+

A view definition is subject to the following restrictions:

TheSELECTstatement cannot contain a subquery in theFROMclause.

TheSELECTstatement cannot refer to system or user variables.

TheSELECTstatement cannot refer to prepared statement parameters.

Within a stored routine, the definition cannot refer to routine parameters or local variables.

Any table or view referred to in the definition must exist. However, after a view has been created, it is possible to drop a table or view that the definition refers to. To check a view definition for problems of this kind, use theCHECK TABLEstatement.

The definition cannot refer to aTEMPORARYtable, and you cannot create aTEMPORARYview.

The tables named in the view definition must already exist.

You cannot associate a trigger with a view.

ORDER BYis allowed in a view definition, but it is ignored if you select from a view using a statement that has its ownORDER BY.

For other options or clauses in the definition, they are added to the options or clauses of the statement that references the view, but the effect is undefined. For example, if a view definition includes aLIMITclause, and you select from the view using a statement that has its ownLIMITclause, it is undefined which limit applies. This same principle applies to options such asALL,DISTINCT, orSQL_SMALL_RESULTthat follow theSELECTkeyword, and to clauses such asINTO,FOR UPDATE,LOCK IN SHARE MODE, andPROCEDURE.

If you create a view and then change the query processing environment by changing system variables, that may affect the results you get from the view:

mysql>CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));Query OK, 0 rows affected (0.00 sec)mysql>SET NAMES 'latin1';Query OK, 0 rows affected (0.00 sec)mysql>SELECT * FROM v;+-------------------+---------------------+| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |+-------------------+---------------------+| latin1| latin1_swedish_ci |+-------------------+---------------------+1 row in set (0.00 sec)mysql>SET NAMES 'utf8';Query OK, 0 rows affected (0.00 sec)mysql>SELECT * FROM v;+-------------------+---------------------+| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |+-------------------+---------------------+| utf8 | utf8_general_ci |+-------------------+---------------------+1 row in set (0.00 sec)

The optionalALGORITHMclause is a MySQL extension to standard SQL.ALGORITHMtakes three values:MERGE,TEMPTABLE, orUNDEFINED. The default algorithm isUNDEFINEDif noALGORITHMclause is present. The algorithm affects how MySQL processes the view.

ForMERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.

ForTEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement.

ForUNDEFINED, MySQL chooses which algorithm to use. It prefersMERGEoverTEMPTABLEif possible, becauseMERGEis usually more efficient and because a view cannot be updatable if a temporary table is used.

A reason to chooseTEMPTABLEexplicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than theMERGEalgorithm so that other clients that use the view are not blocked as long.

A view algorithm can beUNDEFINEDthree ways:

NoALGORITHMclause is present in theCREATE VIEWstatement.

TheCREATE VIEWstatement has an explicitALGORITHM = UNDEFINEDclause.

ALGORITHM = MERGEis specified for a view that can be processed only with a temporary table. In this case, MySQL generates a warning and sets the algorithm toUNDEFINED.

As mentioned earlier,MERGEis handled by merging corresponding parts of a view definition into the statement that refers to the view. The following examples briefly illustrate how theMERGEalgorithm works. The examples assume that there is a viewv_mergethat has this definition:

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) ASSELECT c1, c2 FROM t WHERE c3 > 100;

Example 1: Suppose that we issue this statement:

SELECT * FROM v_merge;

MySQL handles the statement as follows:

v_mergebecomest

*becomesvc1, vc2, which corresponds toc1, c2

The viewWHEREclause is added

The resulting statement to be executed becomes:

SELECT c1, c2 FROM t WHERE c3 > 100;

Example 2: Suppose that we issue this statement:

SELECT * FROM v_merge WHERE vc1 < 100;

This statement is handled similarly to the previous one, except thatvc1 < 100becomesc1 < 100and the viewWHEREclause is added to the statementWHEREclause using anANDconnective (and parentheses are added to make sure the parts of the clause are executed with correct precedence). The resulting statement to be executed becomes:

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

Effectively, the statement to be executed has aWHEREclause of this form.:

WHERE (select WHERE) AND (view WHERE)

TheMERGEalgorithm requires a one-to relationship between the rows in the view and the rows in the underlying table. If this relationship does not hold, a temporary table must be used instead. Lack of a one-to-one relationship oclearcase/“ target=”_blank“ >ccurs if the view contains any of a number of constructs:

Aggregate functions (SUM,MIN(),MAX(),COUNT(), and so forth)

DISTINCT

GROUP BY

HAVING

UNIONorUNION ALL

Refers only to literal values (in this case, there is no underlying table)

Some views are updatable. That is, you can use them in statements such asUPDATE,DELETE, orINSERTto update the contents of the underlying table. For a view to be updatable, there must be a one-to relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view non-updatable. To be more specific, a view is not updatable if it contains any of the following:

Aggregate functions (SUM(),MIN(),MAX(),COUNT(), and so forth)

DISTINCT

GROUP BY

HAVING

UNIONorUNION ALL

Subquery in the select list

Join

Non-updatable view in theFROMclause

A subquery in theWHEREclause that refers to a table in theFROMclause

Refers only to literal values (in this case, there is no underlying table to update)

ALGORITHM = TEMPTABLE(use of a temporary table always makes a view non-updatable)

With respect to insertability (being updatable withINSERTstatements), an updatable view is insertable if it also satisfies these additional requirements for the view columns:

There must be no duplicate view column names.

The view must contain all columns in the base table that do not have a default value.

The view columns must be simple column references and not derived columns. A derived column is one that is not a simple column reference but is derived from an expression. These are examples of derived columns:

3.14159col1 + 3UPPER(col2)col3 / col4(subquery)

A view that has a mix of simple column references and derived columns is not insertable, but it can be updatable if you update only those columns that are not derived. Consider this view:

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

This view is not insertable becausecol2is derived from an expression. But it is updatable if the update does not try to updatecol2. This update is allowable:

UPDATE v SET col1 = 0;

This update is not allowable because it attempts to update a derived column:

UPDATE v SET col2 = 0;

It is sometimes possible for a multiple-table view to be updatable, assuming that it can be processed with theMERGEalgorithm. For this to work, the view must use an inner join (not an outer join or aUNION). Also, only a single table in the view definition can be updated, so theSETclause must name only columns from one of the tables in the view. Views that useUNION ALLare disallowed even though they might be theoretically updatable, because the implementation uses temporary tables to process them.

For a multiple-table updatable view,INSERTcan work if it inserts into a single table.DELETEis not supported.

TheWITH CHECK OPTIONclause can be given for an updatable view to prevent inserts or updates to rows except those for which theWHEREclause in theselect_statementis true.

In aWITH CHECK OPTIONclause for an updatable view, theLOCALandCASCADEDkeywords determine the scope of check testing when the view is defined in terms of another view.LOCALkeyword restricts theCHECK OPTIONonly to the view being defined.CASCADEDcauses the checks for underlying views to be evaluated as well. When neither keyword is given, the default isCASCADED. Consider the definitions for the following table and set of views:

mysql>CREATE TABLE t1 (a INT);mysql>CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2->WITH CHECK OPTION;mysql>CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0->WITH LOCAL CHECK OPTION;mysql>CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0->WITH CASCADED CHECK OPTION;

Here thev2andv3views are defined in terms of another view,v1.v2has aLOCALcheck option, so inserts are tested only against thev2check.v3has aCASCADEDcheck option, so inserts are tested not only against its own check, but against those of underlying views. The following statements illustrate these differences:

ql> INSERT INTO v2 VALUES (2);Query OK, 1 row affected (0.00 sec)mysql>INSERT INTO v3 VALUES (2);ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'

The updatability of views may be affected by the value of theupdatable_views_with_limitsystem variable. (完)

原文转自：www.ltesting.net

篇3：通过视图管理数据数据库教程

视图与表具有相似的结构，当向视图中插入或更新数据时，实际上对视图所引用的表执行数据的插入和更新，

通过视图管理数据数据库教程

。但是通过视图插入、更新数据和表相比有一些限制，下面通过具体的例子来讲述通过视图插入、更新数据以及其使用的限制。

使用SELECT 语句，可以在视图和表中查到该条记录。但是如果执行下面的语句，虽然仍可以成功执行，但只可以在表而不是视图中查到该条数据。

注意：由于向视图插入数据实质是向其所引用的基本表中插入数据，所以必须确认那些来包括在视图列但属于表的列允许NULL值或有缺省值。

若要执行INSERT 语句，则在同一个语句只能对属于同一个表的列执行操作，

所以，若向视图au_title 中插入一行数据，只能分别执行以下语句：

insert into au_title (author_au_id, au_lname, au_fname, contract)

values ('234-34-4611','John','Smith', 1)

insert into au_title (title_au_id, title_id, au_ord, royaltyper)

values ('234-34-4611','BU1111',1,50)

通过视图对数据进行更新与删除时需要注意到两个问题：

执行UPDATE DELETE 时，所删除与更新的数据，必须包含在视图结果集中；

如果视图引用多个表时，无法用DELETE 命令删除数据，若使用UPDATE 则应与INSERT 操作一样，被更新的列必须属于同一个表。

篇4：在视图中用order by数据库教程

视图

大家知道

1：如下查询语句没问题

select * from sysobjects order by name

2：如果把该查询语句建成视图

create view v_test

select * from sysobjects order by name

会提示出错:

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

3: 既然提示除非在语句中使用top 才能用order by，那就好说了

create view v_test

select top 100 percent * from sysobjects order by name

一切正常

再用select * from v_test查一下，确实已经正确排序，

在视图中用order by数据库教程

，

篇5：一个查看MSSQLServer数据库空间使用情况的存储过程 SpaceUsed

-04-04mssql和sqlite中关于if not exists 的写法

-06-06sqlserver 存储过程分页(按多条件排序)

-10-10sqlserver数据库迁移后,孤立账号解决办法

2010-03-03数据结构简明备忘录线性表

-03-03海量数据库的查询优化及分页算法方案

2011-09-09简单触发器的使用献给SQL初学者

2010-07-07分发服务器系统抛出18483错误，未能连接服务器，因为''distribu

2010-09-09sql根据表名获取字段及对应说明

2010-04-04sqlserver 此数据库没有有效所有者错误的解决方法

-09-09SQL Server各种日期计算方法(收藏)

篇6：一个查看MSSQLServer数据库空间使用情况的存储过程 SpaceUsed

热点排行

SQL Server 图文安装教程

SQL Server 安装图解教程(附

sqlserver中distinct的用法(不重

SQL Server导入、导出、备份数据

SQL语句去掉重复记录，获取重复记

SQL Server数据库入门学习总结

SQL Server错误代码大全及解释（

sql convert函数使用小结

sql 时间函数整理的比较全了

用SQL语句添加删除修改字段、一些

篇7：视图在数据库中的应用分析数据库教程

视图|数据|数据库

视图技术现在几乎所有的关系型数据库都支持，其应用也十分广泛，

采用视图的优点

1、让数据库结构实现逻辑上的可扩充性：当数据库系统的物理信息（主要指表结构、表数据）已经完成后，如果由于新的业务要求导致表数据的不够，这时就可以采用视图技术进行扩充，通过视图中强大的SQL来完成功能。

2、软件开发及数据查询处理方便：采用视图可以简化数据库系统的内部结构及其关系图，因为物理表很多时候是以数据库范式原则分析建立的，尤其在数据冗余处理方面与实际业务会产生较大的分歧，采用视图可以将多个表通过SQL联合起来，产生与实际业务相适应的数据结果，这极大方便了软件开发及数据查询，利用视图的SQL写起来也比较简单。

视图按功能的分类分析

1、业务视图

业务视图在数据库应用中比较多，往往是实现返回一个具体的业务结果功能，里面的复杂程度随着业务的复杂而提高，这种视图往往只用在软件中的某个固定模块，所以在建立这种视图应考虑产生的业务数据信息是否有用和完整，不用的数据尽量不返回，如果须从其它表产生的数据应考虑在视图中产生，而不要在实现软件开发时的SQL中通过表链接产生，

2、基本视图

基本视图是指一般是指在软件开发中多个模块都可能用到的视图，这种视图一般比较简单，不会超过四个表的链接，处理基本视图时应做详细的分析，使它的重用性达到最大，切不能因为当前的SQL处理就随便建立一个基本视图。应用了基本视图内的表不应该在外面的SQL中重复出现，否则可能导致ORACLE重复处理的现象。

视图建立备注：

视图中如果出现UNION操作的话在数据不重复的情况下应改为UNION ALL以提高速度。

功能大致相同的视图应在不影响性能及数据的前提下分析是否可以合并成一个视图，这有助于软件模块化开发及调试。

非软件用和不常用的视图应使用完后可以保存SQL成文件再进行删除，以免给其它人员产生影响。

篇8：创建实体化视图的几个注意点数据库教程

创建|视图

1，如果要创建基表是其它用户表的实体化视图，那么需要给实体化视图的owner赋予以下权限：

grant CREATE ANY MATERIALIZED VIEW to username;

grant SELECT ANY TABLE to username;

如果要创建refresh on commit的视图，那么还需要下面这个权限：

grant ON COMMIT REFRESH to username;

2。创建refresh on commit的语法如下，此类实体化视图在基表的事务commit之后，就会立刻刷新

CREATE MATERIALIZED VIEW MV_T1

REFRESH FAST ON COMMIT WITH PRIMARY KEY AS SELECT * FROM kamus.t1;

3。如果不指定on commit，那么默认是on demand，只有手工调用DBMS_MVIEW包中的刷新过程，实体化视图才会被刷新

4，

指定了start with ... next ...选项之后，第一次创建会有作一次完整刷新，然后在指定的时间间隔之后会定时刷新，本例中刷新间隔为1分钟。

语法如下：

CREATE MATERIALIZED VIEW MV_T1

REFRESH FAST START WITH SYSDATE NEXT sysdate+1/24/60 WITH PRIMARY KEY AS SELECT * FROM kamus.t1;

检查USER_REFRESH视图和USER_JOBS视图，我们可以发现start with... next ...语法也就是Oracle自动创建了一个刷新组，这个刷新组的名称跟实体化视图名称相同，并且IMPLICIT_DESTROY属性为Y，表示只要该组中的实体化视图删除该组也自动被删除。同时，创建了一个JOB，JOB中的waht属性是dbms_refresh.refresh('”SCOTT“.”MV_T1"');

自然，由于自动刷新是通过JOB完成的，那么初始化参数job_queue_processes必须大于0，这样JOB才会正常运行。

5。可以自己创建刷新组来定时刷新，我以前的这篇文章中有创建刷新组的方法：

blog.csdn.net/kamus/archive/2004/09/18/108496.aspx

【MSSQL SERVER数据库视图】相关文章：

1.一个查看MSSQLServer数据库空间使用情况的存储过程 SpaceUsed