50622.htm

一本很基础的SQL讲解

<link href="./dzs_cs.css" rel="stylesheet" type="text/css" />
<table width="96%" border="0" align="center" cellpadding="0" cellspacing="0">
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td height="24" align="center" valign="bottom" class="d_font3">SQL Server 2008对T-SQL语言的增强</td>
      </tr>
      <tr>
        <td height="3" bgcolor="#E3E3E3"></td>
      </tr>
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td class="d_font4"><P>【引自<A href="http://blog.csdn.net/lee576/archive/2007/07/04/1679060.aspx">lee576</A>的博客】本文包含如下内容：</P>
<P>◆T-SQL行构造器<BR>◆FORCESEEK表提示<BR>◆GROUPING SETS<BR>◆兼容性级别<BR>◆用户自定义表数据类型<BR>◆表值参数<BR>◆MERGE语句</P>
<P>本文适用于：Microsoft SQL Server(TM) 2008 Developer Edition June 2007 CTP</P>
<P><STRONG>1、T-SQL行构造器</STRONG></P>
<P>T-SQL行构造器(Row Constructors)用来在INSERT语句中一次性插入多行数据。例如：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>CREATE TABLE #a<BR>(<BR>&nbsp;&nbsp;&nbsp; Column1 nvarchar(max),<BR>&nbsp;&nbsp;&nbsp; Column2 nvarchar(max)<BR>);<BR>GO</P><P>INSERT INTO #a<BR>VALUES (<BR>('1', '1'),<BR>('2', '2')<BR>);</P><P>SELECT * FROM #a;<BR>GO</P><P>DROP TABLE #a;<BR>GO</P></PRE></TD></TR></TBODY></TABLE></P>
<P>经过增强后的INSERT语句的语法结构如下。</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>[ WITH &lt;common_table_expression&gt; [ ,...n ] ]<BR>INSERT <BR>&nbsp;&nbsp;&nbsp; [ TOP ( expression ) [ PERCENT ] ] <BR>&nbsp;&nbsp;&nbsp; [ INTO ] <BR>&nbsp;&nbsp;&nbsp; { &lt;object&gt; | rowset_function_limited <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [ WITH ( &lt;Table_Hint_Limited&gt; [ ...n ] ) ]<BR>&nbsp;&nbsp;&nbsp; }<BR>{<BR>&nbsp;&nbsp;&nbsp; [ ( column_list ) ] <BR>&nbsp;&nbsp;&nbsp; [ &lt;OUTPUT Clause&gt; ]<BR>&nbsp;&nbsp;&nbsp; { VALUES ( ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ] )<BR>&nbsp;&nbsp;&nbsp; | derived_table <BR>&nbsp;&nbsp;&nbsp; | execute_statement <BR>&nbsp;&nbsp;&nbsp; | &lt;dml_table_source&gt;<BR>&nbsp;&nbsp;&nbsp; | DEFAULT VALUES <BR>&nbsp;&nbsp;&nbsp; } <BR>} <BR>[; ]</P><P>&lt;object&gt; ::=<BR>{ <BR>&nbsp;&nbsp;&nbsp; [ server_name . database_name . schema_name . <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | database_name .[ schema_name ] . <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | schema_name . <BR>&nbsp;&nbsp;&nbsp; ]<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; table_or_view_name<BR>}</P><P>&lt;dml_table_source&gt; ::=<BR>&nbsp;&nbsp;&nbsp; SELECT &lt;select_list&gt;<BR>&nbsp;&nbsp;&nbsp; FROM ( &lt;dml_statement_with_output_clause&gt; ) <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [AS] table_alias [ ( column_alias [ ,...n ] ) ]<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [ WHERE &lt;search_condition&gt; ]<BR>&nbsp;&nbsp;&nbsp; [ OPTION ( &lt;query_hint&gt; [ ,...n ] ) ]</P></PRE></TD></TR></TBODY></TABLE></P>
<P><STRONG>2. FORCESEEK提示</STRONG></P>
<P>FORCESEEK 是一个新的表提示(Table Hints)，它用来指定SQL Server查询优化程序如何更高效的执行查询。该提示指示优化程序对查询引用的表和视图通过索引检索来作为唯一的查询执行访问路径。也就是强制通过索引检索数据。例如：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>USE tempdb;<BR>GO<BR>DROP TABLE t;<BR>GO<BR>CREATE TABLE t(i int UNIQUE, j int, vc varchar(100));<BR>CREATE INDEX t_vc ON t(vc);<BR>GO<BR>DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int;<BR>SELECT * FROM t WHERE i IN (@p1, @p2, @p3, @p4, @p5);<BR>GO<BR>DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int;<BR>SELECT * FROM t WITH (FORCESEEK) WHERE i IN (@p1, @p2, @p3, @p4, @p5);<BR>GO<BR>SELECT * FROM t WHERE vc LIKE 'Test%';<BR>GO<BR>SELECT * FROM t WITH (FORCESEEK) WHERE vc LIKE 'Test%';<BR>GO<BR>DECLARE @vc varchar(100);<BR>SELECT * FROM t WHERE vc LIKE @vc;<BR>GO<BR>DECLARE @vc varchar(100);<BR>SELECT * FROM t WITH (FORCESEEK) where vc like @vc;<BR>GO</P></PRE></TD></TR></TBODY></TABLE></P>
<P>#p#</P>
<P><STRONG>3. GROUPING SETS</STRONG></P>
<P>新的T-SQL对GROUP BY子句增加了GROUPING SETS,ROLLUP和CUBE操作符。还有一个新的函数GROUPING_ID()，它相比GROUPING()函数返回更多分组级别的信息。WITH ROLLUP, WITH CUBE 和 ALL 等非 ISO 标准语法已经不再有效。</P>
<P><STRONG>4. 兼容性级别</STRONG></P>
<P>新的 ALTER DATABASE SET COMPATIBILITY_LEVEL 语法替换了 sp_dbcomplevel 存储过程。它用来设置特定数据库的兼容性级别。其语法形式为：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>ALTER DATABASE database_name <BR>SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }</PRE></TD></TR></TBODY></TABLE></P>
<P>您可以为 database_name 为名称的数据库设置特定兼容性级别。80、90 和 100 分别代表 SQL Server 2000、SQL Server 2005 和 SQL Server 2008。</P>
<P><STRONG>5. 用户自定义表数据类型</STRONG></P>
<P>现在，SQL Server 提供一种新的用户自定义数据类型——自定义表数据类型(User-defined Table Types)。它可以作为参数提供给语句、存储过程或者函数。您还可以为它创建唯一约束和主键。</P>
<P>请使用 CREATE TYPE 语句创建这种数据类型。如：</P>