做过项目的都知道,楼主去做做项目就知道,为什么要用视图了。用法 1. create view view_name As SQL语句2, use master goif(Exists(select * from sysobjects where name="view_stu")) drop view view_stu go create view view_stu as select * from titles go3.查看VIEW select * from view_stu这下你懂了没
以下情况用视图最好CREATE VIEW all_supplier_view AS SELECT * FROM SUPPLY1 UNION ALL SELECT * FROM SUPPLY2 UNION ALL SELECT * FROM SUPPLY3 UNION ALL SELECT * FROM SUPPLY4;select * from all_supplier_view
A. 使用简单的 CREATE VIEW 下例创建具有简单 SELECT 语句的视图。当需要频繁地查询列的某种组合时,简单视图非常有用。USE pubs IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'titles_view') DROP VIEW titles_view GO CREATE VIEW titles_view AS SELECT title, type, price, pubdate FROM titles GOB. 使用 WITH ENCRYPTION 下例使用 WITH ENCRYPTION 选项并显示计算列、重命名列以及多列。USE pubs IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'accounts') DROP VIEW accounts GO CREATE VIEW accounts (title, advance, amt_due) WITH ENCRYPTION AS SELECT title, advance, price * royalty * ytd_sales FROM titles WHERE price > $5 GO下面是用来检索加密存储过程的标识号和文本的查询:USE pubs GO SELECT c.id, c.text FROM syscomments c, sysobjects o WHERE c.id = o.id and o.name = 'accounts' GO下面是结果集:说明 text 列的输出显示在单独一行中。执行该存储过程时,下列信息将与 id 列信息出现在同一行中。 id text ----------- ------------------------------------------------------------ 661577395 ???????????????????????????????????????????????????????????????????????...(1 row(s) affected)C. 使用 WITH CHECK OPTION 下例显示名为 CAonly 的视图,该视图使得只对加利福尼亚州的作者应用数据修改。USE pubs IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'CAonly') DROP VIEW CAonly GO CREATE VIEW CAonly AS SELECT au_lname, au_fname, city, state FROM authors WHERE state = 'CA' WITH CHECK OPTION GOD. 在视图中使用内置函数 下例显示包含内置函数的视图定义。使用函数时,必须在 CREATE VIEW 语句中为派生列指定列名。USE pubs IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'categories') DROP VIEW categories GO CREATE VIEW categories (category, average_price) AS SELECT type, AVG(price) FROM titles GROUP BY type GOE. 在视图中使用 @@ROWCOUNT 函数 下例使用 @@ROWCOUNT 函数作为视图定义的一部分。USE pubs IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'myview') DROP VIEW myview GO CREATE VIEW myview AS SELECT au_lname, au_fname, @@ROWCOUNT AS bar FROM authors WHERE state = 'UT' GO SELECT * FROM myviewF. 使用分区数据 下例使用名为 SUPPLY1、SUPPLY2、SUPPLY3 和 SUPPLY4 的表,这些表对应于位于不同国家的四个办事处的供应商表。--create the tables and insert the values CREATE TABLE SUPPLY1 ( supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150), supplier CHAR(50) ) CREATE TABLE SUPPLY2 ( supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300), supplier CHAR(50) ) CREATE TABLE SUPPLY3 ( supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450), supplier CHAR(50) ) CREATE TABLE SUPPLY4 ( supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600), supplier CHAR(50) ) INSERT SUPPLY1 VALUES ('1', 'CaliforniaCorp') INSERT SUPPLY1 VALUES ('5', 'BraziliaLtd') INSERT SUPPLY2 VALUES ('231', 'FarEast') INSERT SUPPLY2 VALUES ('280', 'NZ') INSERT SUPPLY3 VALUES ('321', 'EuroGroup') INSERT SUPPLY3 VALUES ('442', 'UKArchip') INSERT SUPPLY4 VALUES ('475', 'India') INSERT SUPPLY4 VALUES ('521', 'Afrique')--create the view that combines all supplier tables CREATE VIEW all_supplier_view AS SELECT * FROM SUPPLY1 UNION ALL SELECT * FROM SUPPLY2 UNION ALL SELECT * FROM SUPPLY3 UNION ALL SELECT * FROM SUPPLY4
1.
create view view_name
As
SQL语句2,
use master
goif(Exists(select * from sysobjects where name="view_stu"))
drop view view_stu
go
create view view_stu
as
select * from titles
go3.查看VIEW
select * from view_stu这下你懂了没
AS
SELECT * FROM SUPPLY1
UNION ALL
SELECT * FROM SUPPLY2
UNION ALL
SELECT * FROM SUPPLY3
UNION ALL
SELECT * FROM SUPPLY4;select * from all_supplier_view
下例创建具有简单 SELECT 语句的视图。当需要频繁地查询列的某种组合时,简单视图非常有用。USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'titles_view')
DROP VIEW titles_view
GO
CREATE VIEW titles_view
AS
SELECT title, type, price, pubdate
FROM titles
GOB. 使用 WITH ENCRYPTION
下例使用 WITH ENCRYPTION 选项并显示计算列、重命名列以及多列。USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'accounts')
DROP VIEW accounts
GO
CREATE VIEW accounts (title, advance, amt_due)
WITH ENCRYPTION
AS
SELECT title, advance, price * royalty * ytd_sales
FROM titles
WHERE price > $5
GO下面是用来检索加密存储过程的标识号和文本的查询:USE pubs
GO
SELECT c.id, c.text
FROM syscomments c, sysobjects o
WHERE c.id = o.id and o.name = 'accounts'
GO下面是结果集:说明 text 列的输出显示在单独一行中。执行该存储过程时,下列信息将与 id 列信息出现在同一行中。
id text
----------- ------------------------------------------------------------
661577395
???????????????????????????????????????????????????????????????????????...(1 row(s) affected)C. 使用 WITH CHECK OPTION
下例显示名为 CAonly 的视图,该视图使得只对加利福尼亚州的作者应用数据修改。USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'CAonly')
DROP VIEW CAonly
GO
CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA'
WITH CHECK OPTION
GOD. 在视图中使用内置函数
下例显示包含内置函数的视图定义。使用函数时,必须在 CREATE VIEW 语句中为派生列指定列名。USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'categories')
DROP VIEW categories
GO
CREATE VIEW categories (category, average_price)
AS
SELECT type, AVG(price)
FROM titles
GROUP BY type
GOE. 在视图中使用 @@ROWCOUNT 函数
下例使用 @@ROWCOUNT 函数作为视图定义的一部分。USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'myview')
DROP VIEW myview
GO
CREATE VIEW myview
AS
SELECT au_lname, au_fname, @@ROWCOUNT AS bar
FROM authors
WHERE state = 'UT'
GO
SELECT *
FROM myviewF. 使用分区数据
下例使用名为 SUPPLY1、SUPPLY2、SUPPLY3 和 SUPPLY4 的表,这些表对应于位于不同国家的四个办事处的供应商表。--create the tables and insert the values
CREATE TABLE SUPPLY1 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),
supplier CHAR(50)
)
CREATE TABLE SUPPLY2 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),
supplier CHAR(50)
)
CREATE TABLE SUPPLY3 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),
supplier CHAR(50)
)
CREATE TABLE SUPPLY4 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),
supplier CHAR(50)
)
INSERT SUPPLY1 VALUES ('1', 'CaliforniaCorp')
INSERT SUPPLY1 VALUES ('5', 'BraziliaLtd')
INSERT SUPPLY2 VALUES ('231', 'FarEast')
INSERT SUPPLY2 VALUES ('280', 'NZ')
INSERT SUPPLY3 VALUES ('321', 'EuroGroup')
INSERT SUPPLY3 VALUES ('442', 'UKArchip')
INSERT SUPPLY4 VALUES ('475', 'India')
INSERT SUPPLY4 VALUES ('521', 'Afrique')--create the view that combines all supplier tables
CREATE VIEW all_supplier_view
AS
SELECT *
FROM SUPPLY1
UNION ALL
SELECT *
FROM SUPPLY2
UNION ALL
SELECT *
FROM SUPPLY3
UNION ALL
SELECT *
FROM SUPPLY4