随笔分类 -  数据库

sqlserver获取字段信息
摘要：WITH indexCTE AS ( SELECT ic.column_id, ic.index_column_id, ic.object_id FROM nopCom.sys.indexes idx INNER JOIN nopCom.sys.index_columns ic ON idx.index_id = ic.index_id AND idx.object_id = ic.object_id WHERE idx.object_id =OBJECT_ID('nopCom.dbo.BlogPost') AND idx.is_primary_key=1 ) selectco 阅读全文
posted @ 2012-12-15 18:03 zhengguoqing 阅读(340) 评论(0) 推荐(0)
SQL
摘要：1.分组前3条数据select * from (SELECT a.categoryid,a.infoid from information a where (select count(*) from information b where a.CategoryID =b.categoryid and a.infoid>b.infoid)<3order by a.categoryid,infoid) c where c.CategoryID in (28) ; 阅读全文
posted @ 2012-11-14 13:09 zhengguoqing 阅读(222) 评论(0) 推荐(0)
SQL Server 2005 集成sp2的 企业版安装后没发现 Management Studio管理工具
摘要：SQL Server 2005集成sp2的企业版安装后没发现 Management Studio管理工具http://www.microsoft.com/downloads/details.aspx?FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=zh-cnSQLServer2005_SSMSEE.msi 阅读全文
posted @ 2010-07-19 11:26 zhengguoqing 阅读(272) 评论(0) 推荐(0)
用Coalesce函数代替"when ...else"
摘要：在sql server 2000/sql server 2005中，当我们想查找一个非空的值时，我们可以利用Coalesce函数来取代常用的"select ...case when.. else"语句。 假设我们想查找到一个可用的"source“时，我们一般会这样写T-Sql 语句: SELECT TheSource = CASE WHEN localSource IS N... 阅读全文
posted @ 2008-04-10 20:56 zhengguoqing 阅读(377) 评论(0) 推荐(0)
T-sql
摘要：use master if exists(select * from sysdatabases where name='chenhangbing' ) begin raiserror('delete database chenhangbing please wait ......',0,1) drop database chenhangbing raiserror ('database ... 阅读全文
posted @ 2008-04-03 13:28 zhengguoqing 阅读(285) 评论(0) 推荐(0)
将SQL表中的数据导出成一批INSERT语句.
摘要：select 'insert into UserRight (ID, parentId, rightId, rightType, userGrade, isDefault, url, module, menuname) values ('+ convert(varchar,ID)+','+ convert(varchar,parentId)+','+ convert(varchar,righ... 阅读全文
posted @ 2008-04-03 13:17 zhengguoqing 阅读(312) 评论(0) 推荐(0)
动态sql语句基本语法
摘要：动态sql语句基本语法 1 :普通SQL语句可以用Exec执行 eg: Select * from tableName Exec('select * from tableName') Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名，表名，数据库名之类作为变量时，必须用动态SQL eg: de... 阅读全文
posted @ 2008-03-07 17:17 zhengguoqing 阅读(333) 评论(0) 推荐(0)
SQL 中拆分字符串
摘要：while patindex('%,%',@pid)>0 begin set @pid_after=substring(@pid,PATINDEX('%,%',@pid)+1,len(@pid)) set @p=substring(@pid,0,len(@pid)-len(@pid_after)) set @strSQL='update +' set +'=getdate(),'+@s... 阅读全文
posted @ 2008-03-07 17:08 zhengguoqing 阅读(343) 评论(0) 推荐(0)
可以选择的去掉重复数据
摘要：CREATE TABLE [test] ( [id] [int] IDENTITY (1, 1) NOT NULL , [a] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [b] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [c] [varchar] (50) COLLATE Chine... 阅读全文
posted @ 2008-03-07 16:44 zhengguoqing 阅读(256) 评论(0) 推荐(0)
四舍五入,保留两位
摘要：select round(convert(numeric(6,2),23.2346),2) select Convert(decimal(10,2),23.2346) 阅读全文
posted @ 2008-03-07 16:42 zhengguoqing 阅读(216) 评论(0) 推荐(0)
游标
摘要：DECLARE @DueDay varchar(20)--定义变量 DECLARE Cur_DueDay CURSOR FOR--定义游标 select * from tablename open Cur_DueDay--打开游标 ///////注意定义的游标变量必须与select *相一致，（位置，数量） FETCH NEXT FROM Cur_DueDay INTO @DueDay--第一次... 阅读全文
posted @ 2008-03-05 16:21 zhengguoqing 阅读(242) 评论(0) 推荐(0)
筛选功能的实现
摘要：用户表--- user uid username 1 user1 2 user2 3 user3 ......... 数据表1--- table1 id useranme a b c ... 阅读全文
posted @ 2008-01-23 20:29 zhengguoqing 阅读(407) 评论(0) 推荐(0)
查询最新修改的SP及远程登陆的用户,和断掉
摘要：select [name] from sys.all_objects where type = 'p' and modify_date > '2007-11-6' exec master.dbo.xp_cmdshell 'logoff id ' exec master.dbo.xp_cmdshell 'query user' 阅读全文
posted @ 2008-01-10 11:30 zhengguoqing 阅读(260) 评论(0) 推荐(0)
sql server2005,启用xp_cmdshell
摘要：默认情况下,sql server2005安装完后,xp_cmdshell是禁用的(可能是安全考虑),如果要使用它,可按以下步骤 -- 允许配置高级选项 EXEC sp_configure 'show advanced options', 1 ... 阅读全文
posted @ 2007-11-26 22:32 zhengguoqing 阅读(486) 评论(0) 推荐(0)