⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 sqlserver实例-3.htm

📁 微软数据库开发梦工场多媒体教学-sql server篇.rar,是多媒体教学的
💻 HTM
📖 第 1 页 / 共 3 页
字号:
🔍
123 
<P class=boe_text><font color="#FFFFFF">以上的建议绝对经典，以下我们就用实例来说明：</font></P>
<P class=boe_text><font color="#FFFFFF">在SQL Server中要得到详细销售情况的SQL－Select语句如下（详细的知识见本人撰写的《SELECT-SQL 
  周周谈》一文）：</font></P>
<P class=boe_code><font color="#FFFFFF">SELECT ORDERS.SHIPNAME,ORDERS.SHIPADDRESS,ORDERS.SHIPCITY,ORDERS.SHIPREGION,ORDERS.SHIPPOSTALCODE,<BR>
  ORDERS.SHIPCOUNTRY,ORDERS.CUSTOMERID,CUSTOMERS.COMPANYNAME AS CUSTOMERNAM,<BR>
  EMPLOYEES.FIRSTNAME+' '+EMPLOYEES.LASTNAME AS SALESPERSON,<BR>
  ORDERS.ORDERID,ORDERS.ORDERDATE,ORDERS.REQUIREDDATE,<BR>
  ORDERS.SHIPPEDDATE,SHIPPERS.COMPANYNAME AS SHIPPERNAME,<BR>
  [ORDER DETAILS].PRODUCTID,PRODUCTS.PRODUCTNAME,[ORDER DETAILS].UNITPRICE,[ORDER 
  DETAILS].QUANTITY,[ORDER DETAILS].DISCOUNT,<BR>
  [ORDER DETAILS].UNITPRICE*[ORDER DETAILS].QUANTITY*(1-[ORDER DETAILS].DISCOUNT) 
  AS EXTENDEDPRICE, ORDERS.FREIGHT<BR>
  FROM ((((EMPLOYEES INNER JOIN ORDERS ON EMPLOYEES.EMPLOYEEID=ORDERS.EMPLOYEEID) 
  <BR>
  INNER JOIN CUSTOMERS ON ORDERS.CUSTOMERID=CUSTOMERS.CUSTOMERID)<BR>
  INNER JOIN SHIPPERS ON ORDERS.SHIPVIA=SHIPPERS.SHIPPERID)<BR>
  INNER JOIN [ORDER DETAILS] ON ORDERS.ORDERID=[ORDER DETAILS].ORDERID)<BR>
  INNER JOIN PRODUCTS ON [ORDER DETAILS].PRODUCTID=PRODUCTS.PRODUCTID</font></P>
<P class=boe_text><font color="#FFFFFF">这是一句12行，不计空格864个字符的大型语句，现在要通过Visual FoxPro的SQLEXEC()函数传送：</font></P>
<P class=boe_code><font color="#FFFFFF">local csq<BR>
  &amp;&amp;配置待传送的语句<BR>
  csql=csql+"SELECT ORDERS.SHIPNAME,ORDERS.SHIPADDRESS,ORDERS.SHIPCITY,ORDERS.SHIPREGION,"<BR>
  csql=csql+"ORDERS.SHIPPOSTALCODE,ORDERS.SHIPCOUNTRY,ORDERS.CUSTOMERID,"<BR>
  csql=csql+"CUSTOMERS.COMPANYNAME AS CUSTOMERNAM,"<BR>
  csql=csql+"EMPLOYEES.FIRSTNAME+' '+EMPLOYEES.LASTNAME AS SALESPERSON,ORDERS.ORDERID,ORDERS.ORDERDATE,"<BR>
  csql=csql+"ORDERS.REQUIREDDATE,ORDERS.SHIPPEDDATE,SHIPPERS.COMPANYNAME AS SHIPPERNAME,"<BR>
  csql=csql+"[ORDER DETAILS].PRODUCTID,PRODUCTS.PRODUCTNAME,[ORDER DETAILS].UNITPRICE,"<BR>
  csql=csql+"[ORDER DETAILS].QUANTITY,[ORDER DETAILS].DISCOUNT,"<BR>
  csql=csql+"[ORDER DETAILS].UNITPRICE*[ORDER DETAILS].QUANTITY*(1-[ORDER DETAILS].DISCOUNT) 
  AS EXTENDEDPRICE,"<BR>
  &nbsp;csql=csql+"ORDERS.FREIGHT"<BR>
  csql=csql+" FROM ((((EMPLOYEES INNER JOIN ORDERS ON EMPLOYEES.EMPLOYEEID=ORDERS.EMPLOYEEID)"<BR>
  csql=csql+" INNER JOIN CUSTOMERS ON ORDERS.CUSTOMERID=CUSTOMERS.CUSTOMERID)"<BR>
  csql=csql+" INNER JOIN SHIPPERS ON ORDERS.SHIPVIA=SHIPPERS.SHIPPERID)"<BR>
  csql=csql+" INNER JOIN [ORDER DETAILS] ON ORDERS.ORDERID=[ORDER DETAILS].ORDERID)"<BR>
  csql=csql+" INNER JOIN PRODUCTS ON [ORDER DETAILS].PRODUCTID=PRODUCTS.PRODUCTID"<BR>
  &amp;&amp;传送,得到的光标名称为默认的SqlResult<BR>
  SQLEXEC(hconn,csql)</font></P>
<P 
class=boe_text><font color="#FFFFFF">解释：由于语句很长，我花费了多条赋值语句才完成配置这个字符串。大家注意到了我选用双引号作为字符串分隔符，这里面是有大学问的。在SQL 
  Server中有待空格的表名如：Order Details，当它用于SQL语句时，就必须要用方括号标示，所以如果在Visual FoxPro中如果用方括号分割字符串的话，就会有冲突！；单引号是SQL 
  Server中分隔字符串、日期值的符号，在Visual FoxPro中如果使用单引号分隔字符串的话，也会造成冲突，当然在以上语句中我们没有看到单引号的身影，不过我们做一个变动，大家就会感到问题的存在了！</font></P>
<P class=boe_code><font color="#FFFFFF">local csq<BR>
  &amp;&amp;配置待传送的语句<BR>
  csql=csql+"SELECT ORDERS.SHIPNAME,ORDERS.SHIPADDRESS,ORDERS.SHIPCITY,ORDERS.SHIPREGION,"<BR>
  csql=csql+"ORDERS.SHIPPOSTALCODE,ORDERS.SHIPCOUNTRY,ORDERS.CUSTOMERID,"<BR>
  csql=csql+"CUSTOMERS.COMPANYNAME AS CUSTOMERNAM,"<BR>
  csql=csql+"EMPLOYEES.FIRSTNAME+' '+EMPLOYEES.LASTNAME AS SALESPERSON,ORDERS.ORDERID,ORDERS.ORDERDATE,"<BR>
  csql=csql+"ORDERS.REQUIREDDATE,ORDERS.SHIPPEDDATE,SHIPPERS.COMPANYNAME AS SHIPPERNAME,"<BR>
  csql=csql+"[ORDER DETAILS].PRODUCTID,PRODUCTS.PRODUCTNAME,[ORDER DETAILS].UNITPRICE,"<BR>
  csql=csql+"[ORDER DETAILS].QUANTITY,[ORDER DETAILS].DISCOUNT,"<BR>
  csql=csql+"[ORDER DETAILS].UNITPRICE*[ORDER DETAILS].QUANTITY*(1-[ORDER DETAILS].DISCOUNT) 
  AS EXTENDEDPRICE,"<BR>
  csql=csql+" ORDERS.FREIGHT"<BR>
  csql=csql+" FROM ((((EMPLOYEES INNER JOIN ORDERS ON EMPLOYEES.EMPLOYEEID=ORDERS.EMPLOYEEID)"<BR>
  csql=csql+" INNER JOIN CUSTOMERS ON ORDERS.CUSTOMERID=CUSTOMERS.CUSTOMERID)"<BR>
  csql=csql+" INNER JOIN SHIPPERS ON ORDERS.SHIPVIA=SHIPPERS.SHIPPERID)"<BR>
  csql=csql+" INNER JOIN [ORDER DETAILS] ON ORDERS.ORDERID=[ORDER DETAILS].ORDERID)"<BR>
  csql=csql+" INNER JOIN PRODUCTS ON [ORDER DETAILS].PRODUCTID=PRODUCTS.PRODUCTID"<BR>
  csql=csql+" WHERE ORDERS.ORDERDATE BETWEEN '1996-10-01' AND '1997-09-30'"<BR>
  csql=csql+" AND ORDERS.CUSTOMERID LIKE '%C%'"<BR>
  csql=csql+" AND [ORDER DETAILS].QUANTITY&gt;50"<BR>
  &amp;&amp;传送,得到的光标名称为默认的SqlResult<BR>
  SQLEXEC(hconn,csql)</font></P>
<P class=boe_head3><font color="#FFFFFF">传递变量</font></P>
<P class=boe_text><font color="#FFFFFF">上面一个例子充分证明了我关于字符串分隔符的经验，接着我又为大家提出了一个新课题：怎样传递变量到SQL 
  Server中。上例中，我们为语句加入了Where字句，下面我们把其中的条件值变为变量，这样这句SQL传递才变得真正的有意义！</font></P>
<P class=boe_text><font color="#FFFFFF">我以为关于变量的传递有两种方法，下面逐一介绍。</font></P>
<P 
class=boe_text><font color="#FFFFFF">第一种是利用：问号+变量来传递的，这是一种常用的方法，用起来也很简单，不用担心变量的类型，ODBC会自动的转化。下面的例子中就涉及到了日期型、数值型、字符型变量，我们根本不用担心变量类型的转化，只要在前面加上问号填入语句中就行了：</font></P>
<P class=boe_code><font color="#FFFFFF">local csq<BR>
  &amp;&amp;要传送的变量<BR>
  dDate1={^1996-10-01}<BR>
  dDate2={^1997-09-30}<BR>
  cCustomerID="%C%"<BR>
  nQty=50<BR>
  &amp;&amp;配置待传送的语句<BR>
  csql=csql+"SELECT ORDERS.SHIPNAME,ORDERS.SHIPADDRESS,ORDERS.SHIPCITY,ORDERS.SHIPREGION,"<BR>
  csql=csql+"ORDERS.SHIPPOSTALCODE,ORDERS.SHIPCOUNTRY,ORDERS.CUSTOMERID,"<BR>
  csql=csql+"CUSTOMERS.COMPANYNAME AS CUSTOMERNAM,"<BR>
  csql=csql+"EMPLOYEES.FIRSTNAME+' '+EMPLOYEES.LASTNAME AS SALESPERSON,ORDERS.ORDERID,ORDERS.ORDERDATE,"<BR>
  csql=csql+"ORDERS.REQUIREDDATE,ORDERS.SHIPPEDDATE,SHIPPERS.COMPANYNAME AS SHIPPERNAME,"<BR>
  csql=csql+"[ORDER DETAILS].PRODUCTID,PRODUCTS.PRODUCTNAME,[ORDER DETAILS].UNITPRICE,"<BR>
  csql=csql+"[ORDER DETAILS].QUANTITY,[ORDER DETAILS].DISCOUNT,"<BR>
  csql=csql+"[ORDER DETAILS].UNITPRICE*[ORDER DETAILS].QUANTITY*(1-[ORDER DETAILS].DISCOUNT) 
  AS EXTENDEDPRICE,"<BR>
  csql=csql+" ORDERS.FREIGHT"<BR>
  csql=csql+" FROM ((((EMPLOYEES INNER JOIN ORDERS ON EMPLOYEES.EMPLOYEEID=ORDERS.EMPLOYEEID)"<BR>
  csql=csql+" INNER JOIN CUSTOMERS ON ORDERS.CUSTOMERID=CUSTOMERS.CUSTOMERID)"<BR>
  csql=csql+" INNER JOIN SHIPPERS ON ORDERS.SHIPVIA=SHIPPERS.SHIPPERID)"<BR>
  csql=csql+" INNER JOIN [ORDER DETAILS] ON ORDERS.ORDERID=[ORDER DETAILS].ORDERID)"<BR>
  csql=csql+" INNER JOIN PRODUCTS ON [ORDER DETAILS].PRODUCTID=PRODUCTS.PRODUCTID"<BR>
  csql=csql+" WHERE ORDERS.ORDERDATE BETWEEN ?dDate1 AND ?dDate2"<BR>
  csql=csql+" AND ORDERS.CUSTOMERID LIKE ?cCustomerID"<BR>
  csql=csql+" AND [ORDER DETAILS].QUANTITY&gt;?nQty"<BR>
  &amp;&amp;传送,得到的光标名称为默认的SqlResult<BR>
  SQLEXEC(hconn,csql)</font></P>
<P class=boe_text><font color="#FFFFFF">分析：我们先来看一看书写的规范，请看：</font></P>
<P class=boe_code><font color="#FFFFFF">csql=csql+" WHERE ORDERS.ORDERDATE BETWEEN 
  ?dDate1 AND ?dDate2"<BR>
  csql=csql+" AND ORDERS.CUSTOMERID LIKE ?cCustomerID"<BR>
  csql=csql+" AND [ORDER DETAILS].QUANTITY&gt;?nQty"</font></P>
<P class=boe_text><font color="#FFFFFF">问号＋变量作为参数放置在SQL语句中，你也许会奇怪：问号＋变量怎么还要放在Visual 
  FoxPro的双引号里面，这不是作为一个字符串了吗？我们首先要清楚Visual FoxPro的SQLEXEC()传递的只能是一个字符串，所以问号＋变量应当作为字符串的一部分！SQLEXEC()会将这个字符串给ODBC，ODBC分析一下这个字符串，他会发现问号＋变量的特殊写法，于是它传递给SQL 
  Server的语句是（整句太长，我就选择最后一段）：</font></P>
<P class=boe_code><font color="#FFFFFF">WHERE ORDERS.ORDERDATE BETWEEN @P1 AND 
  @P2 AND ORDERS.CUSTOMERID LIKE @P3 AND [ORDER DETAILS].QUANTITY&gt;@P4 ', N'@P1 
  datetime,@P2 datetime,@P3 varchar(3),@P4 float', 'Oct 1 1996 12:00AM', 'Sep 
  30 1997 12:00AM', '%C%', 5.000000000000000e+001</font></P>
<P class=boe_text><font color="#FFFFFF">看到没有，所有变量都被重新命名，并用SQL Server的写法规范！</font></P>
<P class=boe_text><font color="#FFFFFF">第二种方法是利用Visual FoxPro把变量合并到语句中去，产生一个字符串，这样的话传递给SQL 
  Server的语句就是一个完整的字符串，其中不带变量，这样做的好处是更有效率，因为ODBC不用处理变量的传递，SQL Server也不用处理变量信息了。</font></P>
<P class=boe_code><font color="#FFFFFF">local csq<BR>
  &amp;&amp;要传送的变量<BR>
  dDate1={^1996-10-01}<BR>
  dDate2={^1997-09-30}<BR>
  cCustomerID="%C%"<BR>
  nQty=50<BR>
  &amp;&amp;配置待传送的语句<BR>
  csql=csql+"SELECT ORDERS.SHIPNAME,ORDERS.SHIPADDRESS,ORDERS.SHIPCITY,ORDERS.SHIPREGION,"<BR>
  csql=csql+"ORDERS.SHIPPOSTALCODE,ORDERS.SHIPCOUNTRY,ORDERS.CUSTOMERID,"<BR>
  csql=csql+"CUSTOMERS.COMPANYNAME AS CUSTOMERNAM,"<BR>
  csql=csql+"EMPLOYEES.FIRSTNAME+' '+EMPLOYEES.LASTNAME AS SALESPERSON,ORDERS.ORDERID,ORDERS.ORDERDATE,"<BR>
  csql=csql+"ORDERS.REQUIREDDATE,ORDERS.SHIPPEDDATE,SHIPPERS.COMPANYNAME AS SHIPPERNAME,"<BR>
  csql=csql+"[ORDER DETAILS].PRODUCTID,PRODUCTS.PRODUCTNAME,[ORDER DETAILS].UNITPRICE,"<BR>
  csql=csql+"[ORDER DETAILS].QUANTITY,[ORDER DETAILS].DISCOUNT,"<BR>
  csql=csql+"[ORDER DETAILS].UNITPRICE*[ORDER DETAILS].QUANTITY*(1-[ORDER DETAILS].DISCOUNT) 
  AS EXTENDEDPRICE,"<BR>
  csql=csql+" ORDERS.FREIGHT"<BR>
  csql=csql+" FROM ((((EMPLOYEES INNER JOIN ORDERS ON EMPLOYEES.EMPLOYEEID=ORDERS.EMPLOYEEID)"<BR>
  csql=csql+" INNER JOIN CUSTOMERS ON ORDERS.CUSTOMERID=CUSTOMERS.CUSTOMERID)"<BR>
  csql=csql+" INNER JOIN SHIPPERS ON ORDERS.SHIPVIA=SHIPPERS.SHIPPERID)"<BR>
  csql=csql+" INNER JOIN [ORDER DETAILS] ON ORDERS.ORDERID=[ORDER DETAILS].ORDERID)"<BR>
  csql=csql+" INNER JOIN PRODUCTS ON [ORDER DETAILS].PRODUCTID=PRODUCTS.PRODUCTID"<BR>
  ccsql=csql+" WHERE ORDERS.ORDERDATE BETWEEN '"+TRANSFORM(dDate1)+"' AND '"+TRANSFORM(dDate2)+"'"<BR>
  csql=csql+" AND ORDERS.CUSTOMERID LIKE '%"+cCustomerID+"%'"<BR>
  csql=csql+" AND [ORDER DETAILS].QUANTITY&gt;"+TRANSFORM(nQty)<BR>
  &amp;&amp;传送,得到的光标名称为默认的SqlResult<BR>
  SQLEXEC(hconn,csql)</font></P>
<P class=boe_text><font color="#FFFFFF">这里的写法是由一些技巧的，就是努力做到模仿SQL Server的规范塑造字符串：</font></P>
<P class=boe_code><font color="#FFFFFF">ccsql=csql+" WHERE ORDERS.ORDERDATE BETWEEN 
  '"+TRANSFORM(dDate1)+"' AND '"+TRANSFORM(dDate2)+"'"<BR>
  &nbsp;csql=csql+" AND ORDERS.CUSTOMERID LIKE '%"+cCustomerID+"%'"<BR>
  &nbsp;csql=csql+" AND [ORDER DETAILS].QUANTITY&gt;"+TRANSFORM(nQty)</font></P>
<P class=boe_text><font color="#FFFFFF">如果将变量计算后代入，大家会发现如下的字符串：</font></P>
<P class=boe_code><font color="#FFFFFF">"WHERE ORDERS.ORDERDATE BETWEEN '1996-10-01' 
  AND '1997-09-30' AND ORDERS.CUSTOMERID LIKE '%C%' AND [ORDER DETAILS].QUANTITY&gt;50"</font></P>
<P class=boe_text><font color="#FFFFFF">看这就是前文我们没有涉及到变量传递的那句SQL语句，很简单吧——合并变量到SQL语句中，塑造纯正的SQL 
  语句！完全是Visual FoxPro在字符串计算方面的技巧，这里值得一提的是TransForm()函数，这是个很有用的函数，它能够将各种类型的值改造成为标准的字符串，特别像日期型这样做在多种写法的数据，他也能轻易规范！</font></P>
<P class=boe_head2><font color="#FFFFFF">&nbsp;把数据集设为可更新</font></P>
<P class=boe_head3><font color="#FFFFFF">Visual FoxPro的光标类型</font></P>
<P class=boe_text><font color="#FFFFFF">光标的英文称呼是Cursor，在Visual FoxPro中习惯的提法是临时表（Temp 
  Table），不过我想应该根据惯例叫它光标。因为Visual FoxPro的光标绝对强劲，如果称呼Temp Table恐造成误会，好像Visual FoxPro不支持光标一样。</font></P>
<P class=boe_text><font color="#FFFFFF">Visual FoxPro的光标有三种：只读光标、可读写光标、可更新光标。</font></P>
<P class=boe_head4><font color="#FFFFFF">只读光标是那种不能被修改的光标，在Visual FoxPro中使用SQL-Select语句产生的光标就是典型的只读光标：</font></P>
123

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -