Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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µÄСÊý£¬Èç¹ûÖ¸¶¨ÖÖ×ÓÔò²úÉúµÄËæ»úÊý¹


Ïà¹ØÎĵµ£º

sqlserver ÉÏ»úÁ·Ï°Ìâ

 create database db
use db
go
create table course
(
 sno varchar(20),
 cno int ,
 Gmark int
)
insert into course values('20071513115',1,80)
insert into course values('20071513114',2,80)
insert into course values('20071513113',3,80)
insert into course values('20071513 ......

ÈçºÎ¸ßЧµÄ½«excelµ¼Èësqlserver£¿

´ó²¿·ÖÈ˶¼ÖªµÀÓÃoledbÀ´¶ÁÈ¡Êý¾Ýµ½dataset£¬µ«ÊǶÁȡ֮ºóÔõô´¦Àídataset¾ÍÇ§Ææ°Ù¹ÖÁË¡£ºÜ¶àÈËͨ¹ýÑ­»·À´Æ´½Ósql£¬ÕâÑù×ö²»µ«ÈÝÒ׳ö´í¶øÇÒЧÂʵÍÏ£¬System.Data.SqlClient.SqlBulkCopy ¶ÔÓÚÐÂÊÖÀ´Ëµ»¹ÊDZȽÏİÉúµÄ£¬Õâ¸ö¾ÍÊÇ´«ËµÖÐЧÂʼ«¸ßµÄbcp£¬6Íò¶àÊý¾Ý´Óexcelµ¼Èëµ½sqlÖ»ÐèÒª4.5Ãë¡£
using System;  
......

SQLServer ´æ´¢¹ý³Ì¼ò½éÓëʹÓ÷½·¨

 Sql ServerµÄ´æ´¢¹ý³ÌÊÇÒ»¸ö±»ÃüÃûµÄ´æ´¢ÔÚ·þÎñÆ÷ÉϵÄTransacation-SqlÓï¾ä¼¯ºÏ,ÊÇ·â×°ÖØ¸´ÐÔ¹¤×÷µÄÒ»ÖÖ·½·¨,ËüÖ§³ÖÓû§ÉùÃ÷µÄ±äÁ¿¡¢Ìõ¼þÖ´ÐÐºÍÆäËûÇ¿´óµÄ±à³Ì¹¦ÄÜ¡£
      ´æ´¢¹ý³ÌÏà¶ÔÓÚÆäËûµÄÊý¾Ý¿â·ÃÎÊ·½·¨ÓÐÒÔϵÄÓŵ㣺
        &nbs ......

Sqlserver£º´ÓXMLÎļþÖжÁÈ¡ÅäÖÃÐÅÏ¢

ÈçºÎÔÚSqlserverÖдÓÍⲿXMLÎļþÖжÁÈ¡ÅäÖÃÐÅÏ¢ÄØ£¿¸ÃÎÊÌâÔ´×ÔÒ»¼ÒÆóÒµµÄ±ÊÊÔÐÅÏ¢ÓиС£
Ò»xmlÎļþÄÚÈÝ£º
<?xml version="1.0" encoding="utf-8"?>
<root>
  <db name="ClientDB1" datasize="512MB" datagrowth="100MB" logsize="100MB" loggrowth ="50MB">
  </db>
  <db ......

PythonÁ¬½ÓSqlServerÁ·Ï°¼Ç¼


import pymssql

#connect database
conn=pymssql.connect(host="192.168.1.28",user="boomink",password="boomink",
database="boomink")

cur=conn.cursor()

print '========================================'

cur.execute("exec Orders_GetTest1 @Valu ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ