常用SQL语句

创建表

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 ,
)

游标

DECLARE @pid varchar(50)
DECLARE @photo varchar(50)
DECLARE myCusor CURSOR FOR
SELECT [pid],[photo] FROM [tbproduct]
OPEN myCusor
FETCH NEXT FROM myCusor INTO @pid,@photo
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO tbpropic(Photo,pid) VALUES(@photo,@pid)
   
        FETCH NEXT FROM myCusor INTO @pid,@photo
    END
    CLOSE myCusor
    DEALLOCATE myCusor

 

添加默认值
ALTER TABLE TBProduct add DEFAULT '商品名称' FOR Pname WITH VALUES

SELECT @@IDENTITY

添加字段时添加默认值
ALTER TABLE MyTable
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES

 替换字段中某一字符串

UPDATE TBAssociationNews SET NewsContent=REPLACE(convert(varchar(4000), [NewsContent]),'http://222.73.69.168:8124','http://www.bestgo360.com')  WHERE NewsContent LIKE '%http://222.73.69.168:8124%'