SQL2005ºÍ(Excel |access)Ö®¼äµÄÊý¾Ýµ¼Èëµ¼³ö
Sql´úÂë
--²ÉÓÃSQLÓï¾äʵÏÖsql2005ºÍExcel Êý¾ÝÖ®¼äµÄÊý¾Ýµ¼Èëµ¼³ö£¬ÔÚÍøÉÏÕÒÀ´Ò»--Ï£¬ÊµÏÖ·½·¨ÊÇÕâÑùµÄ£º
--Excel---->SQL2005 µ¼È룺
select * into useinfo from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 5.0;HDR=YES;DATABASE=d:\db.xls',sheet1$);
--ÆäÖУ¬useinfo ÊÇÊý¾Ý¿âÖеÄÒ»¸ö±í£¬d:\db.xls ΪÊý¾ÝÔ´£¬ÖµµÃÌá³öµÄÊÇ£º--sheet1$£¬¼ÇµÃ¼ÓÉÏ$¡£
---SQL2005--->Excel µ¼³ö£º
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 5.0;HDR=YES;DATABASE=d:\test.xls',sheet1$) select * from useinfo;
--н¨Ò»¸ötest.xml Îļþ£¬ÆäÖÐtest.xmlµÄsheet1 µÄ±íÍ·±ØÐëÒªºÍuseinfoµÄÁÐ----ÃûÏàÒ»ÖÂ.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----¸½ÉÏaccessµÄ
insert into OpenRowSet('microsoft.jet.oledb.4.0',';database=d:\test.mdb','select * from useinfo')
select * from useinfo;
----------------------------------------------------------------------------
select * into hi from
OpenRowSet('microsoft.jet.oledb.4.0',';database=d:\test.mdb','select * from useinfo')
``````````````````````````````````````````
Ïà¹ØÎĵµ£º
selectÓï¾äÖÐÖ»ÄÜʹÓÃsqlº¯Êý¶Ô×ֶνøÐвÙ×÷£¨Á´½Ósql server£©£¬
select ×Ö¶Î1 from ±í1 where ×Ö¶Î1.IndexOf("ÔÆ")=1;
ÕâÌõÓï¾ä²»¶ÔµÄÔÒòÊÇindexof£¨£©º¯Êý²»ÊÇsqlº¯Êý£¬¸Ä³Ésql¶ÔÓ¦µÄº¯Êý¾Í¿ÉÒÔÁË¡£
left£¨£©ÊÇsqlº¯Êý¡£
select ×Ö¶Î1 from ±í1 where charindex£¨'ÔÆ',×Ö¶Î1£©=1;
×Ö·û´®º¯Êý¶Ô¶þ½øÖÆÊý¾Ý¡¢×Ö·û´®º ......
ÎÊÌâÃèÊö£ºÓÐÒÔÏÂ2ÕÅ±í£¬
±íA
& ......
1¡¢Óëgroup by´îÅäʹÓõĺ¯Êý
ÔÚÊý¾Ý¿âÖУ¬ÎÒÃÇ¿ÉÒÔʹÓÃGROUP BYº¯Êý°ÑÊý¾Ý×éºÏÔÚÒ»Æ𣬴Ӷø»ñµÃ×ܼÆÐÅÏ¢¡£¿ÉÒ԰Ѵ˹¦ÄÜ¿´³ÉÊÇÒ»ÖÖµ±Êý¾Ý´ÓÊý¾Ý¿âÖзµ»Øʱ°ÑÏàͬÀàÐ͵ÄÐÅÏ¢¼¯Öе½Ò»ÆðµÄÄÜÁ¦¡£ÏÂÃæ¸ø³öÁËÍêÕûÁÐ±í¡£
avg([distinct]column_name)
ÇóËùÓйÍԱнˮµÄƽ¾ùÖµ¡£
select AVG(emp_salary)
from employee;
ÇóÈ¡c ......
ID int identity(1,1) primary key ×Ô¶¯Ôö³¤,Ö÷¼ü
EXEC sp_rename 'login_info','PDI_login_info' Ö´Ðд洢¹ý³Ì sp_rename , ½«login_info±íÃû ¸ü¸ÄΪ PDI_login_info
SET XACT_ABORT {ON|OFF} Èç¹ûÊÂÎñÖз¢Éú´íÎó£¬on Ôò»áÖÕÖ¹Õû¸öÊÂÎñµÄÖ´ÐУ¬Èç¹ûOFF£¬¼ÌÐø´íÎóµÄÏÂÃæÒ»¾ä
SET &nbs ......
index.jsp
<%@ page language="java" import="java.sql.*" import="java.lang.*" import="java.util.*" pageEncoding="GB2312"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%!
& ......