sqlС±Ê¼Ç£¨5.26£©
sql server 2005 ¼òµ¥ÔËÓú¯Êý
1.null º¯Êý
Ó÷¨ÓëoracleÖÐnvl()ÀàËÆ£¬´¦Àíº¯ÊýΪisnull()£¬
ÀýÈ磺
select ename,sal+isnull(comm,0)
from emp
go
isnull(comm,0)µÄÓ÷¨ÊÇ£º commΪnull Ôò·µ»Ø0 ·ñÔòΪ commµÄÖµ¡£
2.Varchar ¶Ôÿ¸öÓ¢ÎÄ(ASCII)×Ö·û¶¼Õ¼ÓÃ2¸ö×Ö½Ú£¬¶ÔÒ»¸öºº×ÖÒ²Ö»Õ¼ÓÃÁ½¸ö×Ö½Ú
char ¶ÔÓ¢ÎÄ(ASCII)×Ö·ûÕ¼ÓÃ1¸ö×Ö½Ú£¬¶ÔÒ»¸öºº×ÖÕ¼ÓÃ2¸ö×Ö½Ú
Varchar µÄÀàÐͲ»ÒÔ¿Õ¸ñÌîÂú£¬±ÈÈçvarchar(100)£¬µ«ËüµÄÖµÖ»ÊÇ"qian",ÔòËüµÄÖµ¾ÍÊÇ"qian"
¶øchar ²»Ò»Ñù£¬±ÈÈçchar(100),ËüµÄÖµÊÇ"qian"£¬¶øÊµ¼ÊÉÏËüÔÚÊý¾Ý¿âÖÐÊÇ"qian "(qianºó¹²ÓÐ96¸ö¿Õ¸ñ£¬¾ÍÊǰÑËüÌîÂúΪ100¸ö×Ö½Ú)¡£
ÓÉÓÚcharÊÇÒԹ̶¨³¤¶ÈµÄ£¬ËùÒÔËüµÄËÙ¶È»á±Èvarchar¿ìµÃ¶à!µ«³ÌÐò´¦ÀíÆðÀ´ÒªÂé·³Ò»µã£¬ÒªÓÃtrimÖ®ÀàµÄº¯Êý°ÑÁ½±ßµÄ¿Õ¸ñÈ¥µô!
3.Ð޸ıí½á¹¹ alter table dept add phone_number char(12)
ɾ³ýÒÔÉϵÄalter table dept drop colume phone_number
Ð޸ıíÃû sp_rename 'old_name' 'new_name' 'type'
typeÒ»°ãΪ database(¿â) object£¨±í£©column(ÁÐ) index£¨Ë÷Òý£©
ÀýÈ磺sp_rename 'sch.dept.loc' 'location' 'column' °ÑÁÐÃûloc¸ÄΪlocation
Ïà¹ØÎĵµ£º
ÐÞ¸ÄÄÚÈÝ£ºÉ¾³ýÔÀ´µÄTIMEά¶È£¬Ð½¨TIMEά¶È£¬ÐÞ¸ÄSALESÁ¢·½¡£
Êý¾Ý²åÈ룺
1£¬ÊÂʵÊý¾Ý²åÈ룺ͨ¹ýDTS¼Óµ÷¶ÈʵÏÖ½«sales_fact_1997µÄÊý¾Ý½øÐи´ÖÆ¡£ÆµÂÊΪÿ·ÖÖÓ10000Ìõ¡£ &nb ......
create table [±íÃû]
(
[×Ô¶¯±àºÅ×Ö¶Î] int IDENTITY (1,1) PRIMARY KEY ,
[×Ö¶Î1] nVarChar(50) default 'ĬÈÏÖµ' null ,
[×Ö¶Î2] ntext null ,
[×Ö¶Î3] datetime,
[×Ö¶Î4] money null ,
[×Ö¶Î5] int default 0,
[×Ö¶Î6] Decimal (12,4) default 0,
[×Ö¶Î7] image null ,
)
ɾ³ý±í£º
Drop table [ ......
ÐÞ¸ÄÁÐÊôÐÔ
alter table tablename modify(name ,varchar2(10)) Áпí¶È²»µÃСÓÚÐÞ¸ÄǰµÄ¿í¶È
ÈÕÖ¾¹ÜÀí
ÊÓͼ v$logfile
alter database add logfile 'd:\orcl01.log' size 10m;//Ìí¼Ó×é
alter database add logfile member 'd:\orcl02.log' to group 4;//Ïò4×éÖÐÌí¼Ó³ÉÔ±
alter database drop logfile member 'd: ......
--´´½¨Á´½Ó·þÎñÆ÷
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', 'Ô¶³Ì·þÎñÆ÷Ãû»òipµØÖ· '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, 'Óû§Ãû ', 'ÃÜÂë '
--²éѯʾÀý
select * from ITSV.Êý¾Ý¿âÃû.dbo.±íÃû
--µ¼ÈëʾÀý
select * into ±í from ITSV.Êý¾Ý¿âÃû.dbo.±íÃû
--ÒÔºó²»Ô ......
1¡¢ ´æ´¢¹ý³Ì
1£© ¾¡Á¿½«´úÂë¶Î·Åµ½TRY…CATCH…¡£
µ«·²Ê¹ÓÃC#д¹ý´úÂëµÄÈË£¬¶¼ÖªµÀC#ÖÐTRY…CATCH…µÄÔËÐкͳö´íÌø×ªÂß¼£¬¶øSQL Server2005ÖУ¬ÆäÔËÐкͳö´íÌø×ªÂß¼ÓëÔÚC#ÖÐÊÇÒ»Öµġ£TRY…CATCH…ÊÇSQL Server2005ÖÐÐÂÔöµÄ£¬¹¦ÄÜÇ¿´ó£¬ÇҺܺÃÓá£
2£©&nb ......