02 SQLServer³£Óú¯Êý
/******************************
ϵͳº¯Êý
******************************/
--convert ÓÃÀ´×ª±äÊý¾ÝÀàÐÍ
--convert (data_type[(length)], expression [, style])
select convert(int,convert(varchar(5),12345)+'678')+1 --·µ»Ø12345679
--cast Ò²ÊÇÓÃÀ´×ª»»Êý¾ÝÀàÐÍ
--cast(expression as data_type[(length)])
select cast((cast(12345 as varchar(5))+'678') as int)+1 --·µ»Ø123456789
--current_user ·µ»Øµ±Ç°Óû§µÄÃû×Ö
select current_user
--datalength
--·µ»ØÓÃÓÚÖ¸¶¨±í´ïʽµÄ×Ö½ÚÊý
select datalength ('ÖйúaÃË') --ÖÐÎÄÕ¼¸ö×Ö½Ú
--host_name ·µ»Øµ±Ç°Óû§ËùµÇ¼µÄ¼ÆËã»úÃû×Ö
select host_name()
--system_user ·µ»Øµ±Ç°ËùµÇ¼µÄÓû§Ãû³Æ
select system_user
--user_name´Ó¸ø¶¨µÄÓû§id·µ»ØÓû§Ãû
select user_name(2) --·µ»Øguest
--¿ÕÖµº¯Êýisnull(col,replaceStr)
select isnull(pwd,'***') pwd from person
--×Ö·û´®Á¬½Ó
print 'hello,'+'sqlserver2005!'
--Èç¹ûÁ½¸ö²ÎÊýÏàͬ¾Í·µ»Ønull£¬·ñÔò·µ»ØµÚÒ»¸ö²ÎÊý£¬ºöÂÔ´óСд
if nullif('accp','ACCP2') is null
print 'Á½¸ö²ÎÊýÏàͬ'
else
begin
print nullif('accp','ACCP2')
print 'Á½¸ö²ÎÊý²»Ïàͬ'
end
--·µ»ØÆäÖеÚÒ»¸ö·Ç¿ÕµÄ±í´ïʽ
print coalesce(null,null,'accp')
print coalesce(null,'**','accp','case')
/****************************
Êýѧº¯Êý
****************************/
--abs
--È¡ÊýÖµ±í´ïʽµÄ¾ø¶ÔÖµ
select abs(-43)
--ceiling
--·µ»Ø´óÓÚ»òµÈÓÚËù¸øÊý×Ö±í´ïʽµÄ×îСÕûÊý
select ceiling(43.5)
select ceiling(-43.5)
--floor
--ȡСÓÚ»òµÈÓÚÖ¸¶¨±í´ïʽµÄ×î´óÕûÊý
select floor(43.5)
select floor(-43.5)
--power
--È¡ÊýÖµ±í´ïʽµÄÃÝÖµ
select power(5,2) --·µ»Ø
--round--½«ÊýÖµ±í´ïʽËÄÉáÎåÈëΪָ¶¨¾«¶È
select round(43.543,1) --·µ»Ø.500
--sqrt
--È¡±í´ïʽµÄƽ·½¸ù
select sqrt(9) --·µ»Ø.0
--rand
select rand() --²úÉúËæ»ú-1Ö®¼äµÄËæ»úÊý
--sign
--¶ÔÓÚÕýÊý·µ»Ø,¸ºÊý·µ»Ø-1,Áã·µ»Ø
select sign(9)
select sign(-9)
select sign(0)
--Ëæ»úº¯ÊýnewId()ºÍrand()
/*
newid()µÄ·µ»ØÖµ ÊÇuniqueidentifier
newid()ÔÚɨÃèÿÌõ¼Ç¼µÄʱºò¶¼Éú³ÉÒ»¸öÖµ, ¶øÉú³ÉµÄÖµÊÇËæ»úµÄ, ûÓдóСд˳Ðò.
rand() ²úÉúСÓÚ1µÄСÊý£¬Èç¹ûÖ¸¶¨ÖÖ×ÓÔò²úÉúµÄËæ»úÊý¹
Ïà¹ØÎĵµ£º
ÍÐÁ˼¸ÌìµÄÄÑÌ⣬ÖÕÓÚ½â¾öÁË¡£ÌØ·ÖÏíÒ»ÏÂ
ÒÔÏÂÊÇÒ»¸ö´æ´¢¹ý³Ì
CREATE PROCEDURE deleteAccountAll
@id int
AS
SET XACT_abort ON
BEGIN DISTRIBUTED TRAN
delete from AccountCapital where accountid = @id
delete from logininfo where username = @id
delete from CapitalRecord where accountid ......
¿Î³ÌÉè¼ÆµÄµÚÒ»²½£º
Óû§µÇ½ģ¿é:¾ÍÕâ¸öСģ¿é°ÑÎÒÕûËÀÁË£¬³öÏÖµÄÎÊÌâÒ»¸ö½Ó×ÅÒ»¸ö£¬×îÖ÷ÒªµÄ¾ÍÊÇÊý¾Ý¿âÁ¬½Ó.
question1.
java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
Òì³£ÔÒò£ºÃ»Óе¼Èëµ¼Çý¶¯°üsqljdbc.jar.
question2.
......
´ó²¿·ÖÈ˶¼ÖªµÀÓÃoledbÀ´¶ÁÈ¡Êý¾Ýµ½dataset£¬µ«ÊǶÁÈ¡Ö®ºóÔõô´¦Àídataset¾ÍǧÆæ°Ù¹ÖÁË¡£ºÜ¶àÈËͨ¹ýÑ»·À´Æ´½Ósql£¬ÕâÑù×ö²»µ«ÈÝÒ׳ö´í¶øÇÒЧÂʵÍÏ£¬System.Data.SqlClient.SqlBulkCopy ¶ÔÓÚÐÂÊÖÀ´Ëµ»¹ÊDZȽÏÄ°ÉúµÄ£¬Õâ¸ö¾ÍÊÇ´«ËµÖÐЧÂʼ«¸ßµÄbcp£¬6Íò¶àÊý¾Ý´Óexcelµ¼Èëµ½sqlÖ»ÐèÒª4.5Ãë¡£
using System;
......
**ÔÚsqlserverÖÐÈç¹ûҪʹÓÃÒ»¸ö³ÌÐò¼¯Ò»°ãÓÐÈçÏÂ×¢ÒâÊÂÏî
Ò»£º´ò¿ªsqlserver µÄCLRÖ§³Ö
&nb ......
µÚÒ»ÖÖ JavaScriptÁ¬½ÓÊý¾Ý¿â£º
<mce:script language="JavaScript"><!--
// ´´½¨Êý¾Ý¿â¶ÔÏó
var objdbConn = new ActiveXObject("ADODB.Connection");
// DSN×Ö·û´®
var strdsn = "Driver={SQL Server};SERVER=192.168.0.210;UID=xhb;PWD=xhb;DATABASE=books";
// ´ò¿ªÊý¾ÝÔ´
objdbConn.Open(strdsn);
// ......