📄 sql_view.asp
字号:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en-US" xml:lang="en-US" xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>SQL CREATE VIEW</title>
<link rel="shortcut icon" href="../favicon.ico" type="image/x-icon" />
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<meta name="Keywords" content="xml,tutorial,html,dhtml,css,xsl,xhtml,javascript,asp,ado,vbscript,dom,sql,colors,soap,php,authoring,programming,training,learning,beginner's guide,primer,lessons,school,howto,reference,examples,samples,source code,tags,demos,tips,links,FAQ,tag list,forms,frames,color table,w3c,cascading style sheets,active server pages,dynamic html,internet,database,development,Web building,Webmaster,html guide" />
<meta name="Description" content="Well organized easy to understand SQL tutorial with lots of examples. Including PHP, ASP, ADO, Oracle, Access, SQL Server. Related: HTML, JavaScript, XML, XQuery" />
<meta http-equiv="pragma" content="no-cache" />
<meta http-equiv="cache-control" content="no-cache" />
<link rel="stylesheet" type="text/css" href="../stdtheme.css" />
<script type="text/javascript">
var gaJsHost = (("https:" == document.location.protocol) ? "../../https@ssl./default.htm" : "../../www./default.htm");
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
</script>
<script type="text/javascript">
var pageTracker = _gat._getTracker("UA-3855518-1");
pageTracker._initData();
pageTracker._trackPageview();
</script>
</head>
<body>
<a name="top"></a>
<table cellpadding="0" cellspacing="0" width="100%" bgcolor="#808080">
<tr>
<td width="234" valign="top">
<a href="../default.htm"><img src="../images/w3default80.jpg" border="0" alt="W3Schools" /></a>
</td>
<th valign="middle" align="left" class="right">
<iframe style="background-color:#808080" src="../banners/bannerframe.asp@adpartner=mosso" height="90" width="728"
marginwidth="0" marginheight="0" frameborder="0" scrolling="no">
Your browser does not support inline frames or is currently configured not to display inline frames.
</iframe>
<br /></th>
</tr>
</table>
<table style="margin-left:1px" width="800px" border="0" cellpadding="0" cellspacing="2">
<tr>
<td width="137" class="content" valign="top"><br />
<a class="left" href="../default.asp" target="_top">
<img src="../images/homeicon.gif" border="0" alt="home" /></a>
<a class="left" href="../default.asp" target="_top">
<b>HOME</b></a>
<br /><br />
<b>SQL Basic</b>
<br />
<a class="left" target="_top" href="default.asp" >SQL HOME</a><br />
<a class="left" target="_top" href="sql_intro.asp" >SQL Intro</a><br />
<a class="left" target="_top" href="sql_select.asp" >SQL Select</a><br />
<a class="left" target="_top" href="sql_where.asp" >SQL Where</a><br />
<a class="left" target="_top" href="sql_insert.asp" >SQL Insert</a><br />
<a class="left" target="_top" href="sql_update.asp" >SQL Update</a><br />
<a class="left" target="_top" href="sql_delete.asp" >SQL Delete</a><br />
<br />
<b>SQL Demo</b><br />
<a class="left" target="_top" href="sql_tryit.asp" >SQL Try It</a><br />
<br />
<b>SQL Advanced</b><br />
<a class="left" target="_top" href="sql_orderby.asp" >SQL Order By</a><br />
<a class="left" target="_top" href="sql_and_or.asp" >SQL and & or</a><br />
<a class="left" target="_top" href="sql_in.asp" >SQL In</a><br />
<a class="left" target="_top" href="sql_between.asp" >SQL Between</a><br />
<a class="left" target="_top" href="sql_alias.asp" >SQL Aliases</a><br />
<a class="left" target="_top" href="sql_join.asp" >SQL Join</a><br />
<a class="left" target="_top" href="sql_union.asp" >SQL Union</a><br />
<a class="left" target="_top" href="sql_create.asp" >SQL Create</a><br />
<a class="left" target="_top" href="sql_drop.asp" >SQL Drop</a><br />
<a class="left" target="_top" href="sql_alter.asp" >SQL Alter</a><br />
<a class="left" target="_top" href="sql_functions.asp" >SQL Functions</a><br />
<a class="left" target="_top" href="sql_groupby.asp" >SQL Group By</a><br />
<a class="left" target="_top" href="sql_select_into.asp" >SQL Select Into</a><br />
<a class="left" target="_top" href="sql_view.asp" style='font-weight:bold;'>SQL Create View</a><br />
<a class="left" target="_top" href="sql_server.asp" >SQL Server</a><br />
<br />
<a class="left" target="_top" href="sql_quickref.asp" >SQL Quick Ref</a><br />
<a class="left" target="_top" href="sql_summary.asp" >SQL Summary</a><br />
<br />
<b>SQL Quiz</b><br />
<a class="left" target="_top" href="sql_quiz.asp" >SQL Quiz</a><br />
<br />
<b>Selected Reading</b><br />
<a class="left" target="_top" href="../browsers/browsers_stats.asp">Web Statistics</a><br />
<a class="left" target="_top" href="../site/site_glossary.asp">Web Glossary</a><br />
<a class="left" target="_top" href="../hosting/default.asp">Web Hosting</a><br />
<a class="left" target="_top" href="../quality/default.asp">Web Quality</a><br /><br />
<a class="left" target="_top" href="../forum/default.asp">W3Schools Forum</a><br /><br />
<a class="left" target="_top" href="../about/about_helping.asp">Helping W3Schools</a><br /><br />
<script type="text/javascript"><!--
google_ad_client = "pub-3440800076797949";
/*LeftLinkUnit*/
google_ad_slot = "4854527104";
google_ad_width = 120;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="../../pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</td>
<td valign="top">
<table style="border: 1px solid gray" width="100%" bgcolor="#FFFFFF" border="0" cellpadding="5" cellspacing="0">
<tr>
<td>
<h1>SQL CREATE VIEW Statement</h1>
<a href="sql_select_into.asp"><img border="0" alt="previous" src="../images/btn_previous.gif" width="100" height="20" /></a>
<a href="sql_server.asp"><img border="0" alt="next" src="../images/btn_next.gif" width="100" height="20" /></a>
<hr />
<p class="intro">A view is a virtual table based on the result-set of a SELECT
statement.</p>
<hr />
<h2>What is a View?</h2>
<p>In SQL, a VIEW is a virtual table based on the result-set of a SELECT
statement.</p>
<p>A view contains rows and columns, just like a real table. The fields in a view
are fields from one or more real tables in the database. You can add SQL
functions, WHERE, and JOIN statements to a view and present the data as if the data were
coming from a single table.</p>
<p><b>Note:</b> The database design and structure will NOT be affected by the
functions, where, or join statements in a view.</p>
<h3>Syntax</h3>
<table class="ex" cellspacing="0" border="1" width="100%" id="table5">
<tr>
<td>
<pre>CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition</pre>
</td>
</tr>
</table>
<p><b>Note:</b> The database does not store the view data! The database engine
recreates the data, using the view's SELECT statement, every time a user queries
a view. </p>
<hr />
<h2>Using Views</h2>
<p>A view could be used from inside a query, a stored
procedure, or from inside another view. By adding functions, joins, etc., to a view, it allows you to present
exactly the data you want to the user.</p>
<p>The sample database Northwind has some views installed by default. The
view "Current Product List" lists all active products (products that are not
discontinued) from the Products table. The view is created with the following
SQL:</p>
<table class="ex" cellspacing="0" border="1" width="100%" id="table13">
<tr>
<td>
<pre>CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No</pre>
</td>
</tr>
</table>
<p>We can query the view above as follows:</p>
<table class="ex" cellspacing="0" border="1" width="100%" id="table14">
<tr>
<td>
<pre>SELECT * FROM [Current Product List]</pre>
</td>
</tr>
</table>
<p>Another view from the Northwind sample database selects every product in the Products table that has a unit price
that is higher than the average unit price:</p>
<table class="ex" cellspacing="0" border="1" width="100%" id="table6">
<tr>
<td>
<pre>CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)</pre>
</td>
</tr>
</table>
<p>We can query the view above as follows:</p>
<table class="ex" cellspacing="0" border="1" width="100%" id="table8">
<tr>
<td>
<pre>SELECT * FROM [Products Above Average Price]</pre>
</td>
</tr>
</table>
<p>Another example view from the Northwind database calculates
the total sale for each category in 1997. Note that this view selects its data
from another view called "Product Sales for 1997":</p>
<table class="ex" cellspacing="0" border="1" width="100%" id="table7">
<tr>
<td>
<pre>CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName</pre>
</td>
</tr>
</table>
<p>We can query the view above as follows:</p>
<table class="ex" cellspacing="0" border="1" width="100%" id="table9">
<tr>
<td>
<pre>SELECT * FROM [Category Sales For 1997]</pre>
</td>
</tr>
</table>
<p>We can also add a condition to the query. Now we want to see the total sale
only for the category "Beverages":</p>
<table class="ex" cellspacing="0" border="1" width="100%" id="table10">
<tr>
<td>
<pre>SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'</pre>
</td>
</tr>
</table>
<br />
<hr />
<a href="sql_select_into.asp"><img border="0" alt="previous" src="../images/btn_previous.gif" width="100" height="20" /></a>
<a href="sql_server.asp"><img border="0" alt="next" src="../images/btn_next.gif" width="100" height="20" /></a>
<br />
<hr />
<!-- **** SPOTLIGHTS 1 **** -->
<iframe src="../banners/aspallframe.asp" height="110" width="485"
marginwidth="0" marginheight="0" frameborder="0" scrolling="no">
Your browser does not support inline frames or is currently configured not to display inline frames.
</iframe>
<hr />
<!-- **** SPOTLIGHTS 2 **** -->
<h2><a target="_blank" href="../../www.altova.com/ref/@s=w3s_spotlight&q=xmlspy">
Altova⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -