Frankwangyifang

  :: 首页 :: 新随笔 :: 联系 :: 订阅 :: 管理 ::

随笔分类 -  SQL

摘要:--1. 启用 SQL Server 2005 邮件功能。use mastergoexec sp_configure 'show advanced options',1goreconfiguregoexec sp_configure 'Database mail XPs',1goreconfigurego--2. 在 SQL Server 2005 中添加邮件帐户(account)exec msd... 阅读全文
posted @ 2009-12-15 18:01 Frankwangyifang

摘要:SQL Server:定时作业的设置方法 --------------------------------------------------------------------------------如果在SQL Server 里需要定时或者每隔一段时间执行某个存储过程或3200字符以内的SQL语句时,可以用管理->SQL Server代理->作业来实现。 1、管理->SQL ... 阅读全文
posted @ 2009-12-15 17:02 Frankwangyifang

摘要:--1, 定义游标:DECLARE Cursor_UserInfo CURSOR scroll dynamic -- scroll表示可随意移动游标指针(否则只能向前),dynamic表示可以读写游标(否则游标只读)FOR SELECT UserID--,UserName,UserAddress FROM UserInfo WHERE UserName='aaa'; --2, 打开游标:open ... 阅读全文
posted @ 2009-12-15 16:45 Frankwangyifang

摘要:SQL 关键字:1,CAST:SELECT CAST(productID as varchar(30)+':'+productName) as Name from Prouduct --把 int 转为 varchar 类型去处理. (相当于string.Formate()方法. )2, CASE:SELECT studentName,case Score  when Score<60 th... 阅读全文
posted @ 2009-11-21 16:39 Frankwangyifang

摘要:IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserInfo]') AND type in (N'U'))BEGINCREATE TABLE [dbo].[UserInfo]([UserID] [bigint] IDENTITY(999999999999999999,10000000) ... 阅读全文
posted @ 2009-11-21 14:17 Frankwangyifang

摘要:select*from[AAA]..TableAa innerjoin[BBB]..TableBbona.AcountID=b.ClientID/*方式一:*/select * from [JinRiChinaHotel].dbo.tblVisitor/* 方式二:*/select * from [JinRiChinaHotel]..tblVisitor注意:1,最好数据库用"[]".2, 表名前... 阅读全文
posted @ 2009-11-18 11:31 Frankwangyifang

摘要:create function GetUserNames(@userDptID int)returns nvarchar(500)asbegindeclare @UserID intdeclare @UserNames nvarchar(300)select @UserID = min( UserID ) from UserInfo where UserDpt=@userDptIDset @Use... 阅读全文
posted @ 2009-11-11 17:49 Frankwangyifang

摘要:DateTime baseTime = DateTime.Parse("1900-01-01"); rpf.BriefDate = baseTime.AddDays(1000); example: DateTime baseTime = DateTime.Parse("1900-01-01"); Response.Write(baseTime.AddDays(39868-2).ToString("... 阅读全文
posted @ 2009-11-10 13:37 Frankwangyifang

摘要:insert into a values('aaaaa') print @@identity; //打印刚生成的主键值.**update a set c='AAAAAAAAAAAA' where b=15print @@identity; 阅读全文
posted @ 2009-11-10 13:19 Frankwangyifang

摘要:DECLARE@pagenum INT,@pagesize INTSET @pagenum = 2SET @pagesize = 3SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY b DESC) AS rownum,c FROM a) AS DWHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pa... 阅读全文
posted @ 2009-11-10 13:18 Frankwangyifang