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
Ïà¹ØÎĵµ£º
SQL Default Ô¼ÊøµÄ³õ²½ÈÏʶºÍÀí½â£¡
Ê×ÏÈ´´½¨Ò»Õűíhello
CREATE TABLE hello
(
Id_P int PRIMARY KEY,
Firstname varchar(50),
Lastname varchar(50),
Address varchar(50),
City varchar(50)
)
´´½¨Ô¼ÊøÌõ¼þ
CREATE DEFAULT beijing_const AS 'beijing'
°ó¶¨Ô¼ÊøÌõ¼þµ½ÁÐÉÏ
sp_bindefault beijing_ ......
Ò»¡¢ÎÊÌâµÄÌá³ö
ÔÚÓ¦ÓÃϵͳ¿ª·¢³õÆÚ£¬ÓÉÓÚ¿ª·¢Êý¾Ý¿âÊý¾Ý±È½ÏÉÙ£¬¶ÔÓÚ²éѯSQLÓï¾ä£¬¸´ÔÓÊÓͼµÄµÄ±àдµÈÌå»á²»³öSQLÓï¾ä¸÷ÖÖд·¨µÄÐÔÄÜÓÅÁÓ£¬µ«ÊÇÈç¹û½«Ó¦ÓÃϵͳÌύʵ¼ÊÓ¦Óúó£¬Ëæ×ÅÊý¾Ý¿âÖÐÊý¾ÝµÄÔö¼Ó£¬ÏµÍ³µÄÏìÓ¦ËٶȾͳÉΪĿǰϵͳÐèÒª½â¾öµÄ×îÖ÷ÒªµÄÎÊÌâÖ®Ò»¡£ÏµÍ³ÓÅ»¯ÖÐÒ»¸öºÜÖØÒªµÄ·½ ......
ʹÓÃÁËÒ»¶Îʱ¼äºó£¬SQL Server µÄ LDFÎļþÌå»ý¾Þ´ó.
ÈçºÎ´¦ÀíàÏ, ¶ÔÓÚ SQL Server 2005 ¼°Ö®Ç°µÄ°æ±¾£¬¿ÉÒÔʹÓÃÈçÏ SQL£º
declare @name varchar(50)
set @name='dbname
'
backup
log @name
with truncate_only
dbcc shrinkdatabase (@name,20)
¿ÉÊÇÔÚ SQL Server 2008 ¿ªÊ¼£¬Ö´ÐÐÉÏÃæµÄÓï¾ä»á±¨´í:
'truncate ......
/*
SQL SERVER 2008 ѹËõ±¸·Ý
SQL SERVER 2008 ÔÚÆóÒµ°æºÍ¿ª·¢°æÖÐÒýÈëÁ˱¸·ÝѹËõ.ʹÓÃÕ߸ö¹¦ÄÜ¿ÉÒÔ¸ü¿ìËٵı¸·ÝÊý¾Ý¿â²¢ÇÒ
ÏûºÄ¸üÉٵĴÅÅ̿ռä.ѹËõÁ¿ÒÀÀµÓÚÊý¾Ý¿âÖд洢µÄÊý¾Ý.ÀýÈç,º¬ÓÐÖØ¸´Öµ×Ö·ûÊý¾ÝµÄÊý¾Ý¿â¿ÉÒÔÓÐ
  ......
USE StudentInfo
--=====================================================
--Author £ºyangjuncheng
--Create Date:2010.5.26
--Decription :¸ø±íÌí¼ÓÔ¼Êø£¨¿ÉÒÔÔÚ´´½¨±íʾֱ½ÓÌí¼Ó
-- Ò²¿ÉÒÔʹÓÃalter¹Ø¼ ......