sql.htm

Sql Server的例子

                                </tr>
                                <tr>
                                  <td width="3%" rowspan="3" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%" height="70">
                                  　</td>
                                  <td width="18%" valign="top" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%" height="17">
                                  [@old =] &#39;old_password&#39;</td>
                                  <td width="76%" valign="top" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%" height="17">
                                  是旧密码。old_password 为 sysname 类型，其默认值为 NULL。</td>
                                </tr>
                                <tr>
                                  <td width="18%" valign="top" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%" height="1">
                                  [@new =] &#39;new_password&#39;</td>
                                  <td width="76%" valign="top" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%" height="1">
                                  是新密码。new_password 为 sysname 类型，无默认值。如果没有使用命名参数，就必须指定 
                                  old_password。</td>
                                </tr>
                                <tr>
                                  <td width="18%" valign="top" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%" height="0">
                                  [@loginame =] &#39;login&#39;</td>
                                  <td width="76%" valign="top" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%" height="0">
                                  是受密码更改影响的登录名。login 为 sysname 类型，其默认值为 NULL。login 
                                  必须已经存在，并且只能由 sysadmin 固定服务器角色的成员指定。</td>
                                </tr>
                              </table>
                            </td>
                            </tr>
                            </table>
                            <p>
                            <a href="#Microsoft SQL Server Transact-SQL   by 柯晓东" style="color: #205064; text-decoration: none">
                            [Back]</a><br />
                            </p>
                            <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#4678A4" width="100%" id="AutoNumber1">
                              <tr>
                                <td width="100%" height="17" bgcolor="#003366" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                <p align="center"><b>
                                <font color="#FFFFFF" size="3">
                                <a name="基本SQL语句" style="text-decoration: none">
                                基本SQL语句</a></font></b></p>
                                </td>
                              </tr>
                              <tr>
                                <td width="100%" height="35" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                <table border="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber6" height="20" cellpadding="2">
                                  <tr>
                                    <td width="100%" height="2" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                    1.<span lang="zh-cn">声明变量<br>
                                    declare @a varchar(20)</span></td>
                                  </tr>
                                  <tr>
                                    <td width="100%" height="1" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                    <u><b>1.</b></u><span lang="zh-cn"><u><b>新建数据库</b></u><br>
                                    </span><b>create database</b> Ke_Xiaodong <b>
                                    on</b> <b>primary</b> (name=Ke_Xiaodong,filename=&#39;c:\test\ke_xiaodong.mdf&#39;,size=6MB,maxsize=10MB,filegrowth=10%)<br>
                                    <b>log on</b> (name= user_name_log,filename=&#39;c:\test\ 
                                    ke_xiaodong_log.ldf&#39;,size=2MB,maxsize=3MB,filegrowth=1MB)</td>
                                  </tr>
                                  <tr>
                                    <td width="100%" height="2" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                    <u><b>2.新建表</b></u><br>
                                    <b>CREATE TABLE</b> MyTable <br>
                                    ( Code int identity (1, 1) PRIMARY KEY NOT 
                                    NULL,&nbsp;<br>
&nbsp; Name nvarchar 
                                    (10) NOT NULL,&nbsp;<br>
&nbsp; Male bit NULL,&nbsp;<br>
&nbsp; Birthday DateTime NOT NULL,&nbsp;<br>
&nbsp; School varChar(40) NOT NULL,&nbsp;<br>
&nbsp; Salary Money Null,&nbsp;<br>
&nbsp; Memo 
                                    Text)<br>
                                    <br>
                                    <b>CREATE TABLE</b> dbo.Insured <br>
                                    ( InsuredID int IDENTITY (1, 1) NOT NULL,<br>
&nbsp; PolicyID int NOT NULL,<br>
&nbsp; InsuredName char(30) NOT NULL,<br>
&nbsp; InsuredBirthDate datetime NOT NULL,<br>
&nbsp; CONSTRAINT PK_Insured PRIMARY KEY CLUSTERED( InsuredID ),&nbsp;&nbsp; --&nbsp;&nbsp; 
                                    &lt;--索引<br>
&nbsp; CONSTRAINT FK_Insured_Policy FOREIGN KEY ( PolicyID ) REFERENCES 
                                    dbo.Policy( PolicyID )&nbsp;&nbsp;&nbsp; --&nbsp; 
                                    &lt;--外键<br>
                                    )</td>
                                  </tr>
                                  <tr>
                                    <td width="100%" height="2" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                    <u><b>3.</b></u><span lang="zh-cn"><u><b>新建视图</b></u><br>
                                    <b>CREATE VIEW </b>MyView(Code,Name,Birthday,School)
                                    <b>AS SELECT</b> [Code],[Name],[Birthday],[School]
                                    <b>FROM</b> myTable</span></td>
                                  </tr>
                                  <tr>
                                    <td width="100%" height="2" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                    <u><b>4.选择语句</b></u><table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="95%" id="AutoNumber7">
                                      <tr>
                                        <td width="4%" valign="top">语法</td>
                                        <td width="96%" valign="top">
                                        <p class="a"><span lang="EN-US">SELECT [ 
                                        ALL | DISTINCT ] <br>
                                        [ TOP <i>n </i>[PERCENT] [ WITH TIES] ] 
                                        &lt;select_list&gt;<br>
                                        [ INTO <i>new_table</i> ]<br>
                                        [ FROM &lt;table_sources&gt; ]<br>
                                        [ WHERE &lt;search_conditions&gt; ]<br>
                                        [ [ GROUP BY [ALL] <i>
                                        group_by_expression </i>[,…<i>n</i>]]&nbsp; 
                                        [HAVING &lt;search_conditions&gt; ]&nbsp; [ 
                                        WITH { CUBE | ROLLUP } ] ]<br>
                                        [ ORDER BY { <i>column_name</i> [ ASC | 
                                        DESC ] } [,…<i>n</i>] ]<br>
                                        [ COMPUTE&nbsp; { { AVG | COUNT | MAX | 
                                        MIN | SUM } (<i>expression</i>) </span>
                                        <span lang="EN-US" style="font-family: 宋体">
                                        }</span><span lang="EN-US"> [,…<i>n</i>]&nbsp; 
                                        [ BY <i>expression </i>[,…<i>n</i>] ]</span></td>
                                      </tr>
                                      <tr>
                                        <td width="4%" valign="top">实例</td>
                                        <td width="96%" valign="top">
                                    <b>SELECT</b> person.Name, person.Salary, person.Address<br>
                                    <b>FROM</b> person <b><br>
                                    INNER JOIN</b>&nbsp; person_lover <b>ON</b> 
                                    person.Code = person_lover.Code<br>
                                    <b>WHERE</b> (person.Male = &#39;女&#39;) <b>AND</b> 
                                    (person.Salary &gt; person_lover.Salary) <b>OR</b> 
                                    (person.Male = &#39;男&#39;) <b>AND</b> (person.Salary 
                                    &lt; person_lover.Salary)<br>
                                        <br>
                                        <b>SELECT</b> SUM(quantity) <b>FROM</b> 
                                        [order details]<br>
                                    <br>
                                    <b>SELECT</b>&nbsp; 'Num 
                                    Employees'=COUNT(*), 'Avg Salary'=AVG(NULLIF(emp_salary, 
                                    0)), 'Low Salary'=MIN(NULLIF(emp_salary, 
                                    0)),'Avg Age'=AVG(NULLIF(emp_age, 
                                    0)),'Youngest'=MIN(NULLIF(emp_age, 0)) <b>
                                    FROM</b> employees<br>
　</td>
                                      </tr>
                                    </table>
                                    </td>
                                  </tr>
                                  <tr>
                                    <td width="100%" height="2" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                    <u><b>5.插入语句</b></u><br>
                                    <b>Insert</b> <b>into</b> Baby_count(Code,Baby_count)
                                    <b>Values</b> (12,1)<br>
                                    <b>Insert into</b> 目标数据表 select * from 源数据表</td>
                                  </tr>
                                  <tr>
                                    <td width="100%" height="2" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                    <u><b>6.更新语句</b></u><br>
                                    <b>Update</b> Baby_count <b>Set</b> Baby_count=Baby_count+1
                                    <b>where</b> code=12</td>
                                  </tr>
                                  <tr>
                                    <td width="100%" height="1" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                    <u><b>7.删除语句</b></u><br>
                                    <b>Delete</b> <b>from</b> person <b>Where</b> 
                                    Married=0</td>
                                  </tr>
                                  <tr>
                                    <td width="100%" height="1" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                    <u><b>8.</b></u><span lang="zh-cn"><u><b>比较</b></u><br>
                                    <b>SELECT</b> companyname <b>FROM</b> customers
                                    <b>WHERE</b> companyname <b>LIKE</b> &#39;%Restaurant%&#39;</span></td>
                                  </tr>
                                  <tr>
                                    <td width="100%" height="0" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                    <u><b>9.Rollup和Cube</b></u><br>
                                    初始语句<b><br>
                                    </b>SELECT orderid, productid, SUM(quantity) 
                                    AS total_quantity FROM [order details] WHERE 
                                    orderid &lt; 10250 GROUP BY orderid, productid 
                                    ORDER BY orderid, productid<b><br>
　</b><table border="1" cellspacing="0" cellpadding="0" style="width: 50%; border-collapse: collapse; border: medium none">
                                      <tr>
                                        <td width="31%" valign="top" style="width: 31.58%; border: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a" style="margin-left:10.5pt">
                                        <span lang="EN-US">Orderid</span></td>
                                        <td width="35%" valign="top" style="width: 35.1%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: 1.0pt solid windowtext; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a" style="margin-left:10.5pt">
                                        <span lang="EN-US">productid</span></td>
                                        <td valign="top" style="width: 33%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: 1.0pt solid windowtext; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a" style="margin-left:10.5pt">