SQL经典短小代码收集
来源：互联网 发布：深圳盘古数据上市了没 编辑：程序博客网 时间：2024/05/05 08:17
http://topic.csdn.net/u/20080920/15/424c77bf-7610-4888-be85-9a43e70f55c6.html?4728
http://topic.csdn.net/u/20090310/17/0b8f60c5-9304-4633-84fa-d176f282cb7d.html
(1) 固定列数的行列转换
如
student subject grade
--------- ---------- --------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下：select student,
sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student;
(2) --SQL2005
CREATE TABLE #t (id int IDENTITY(1,1) PRIMARY KEY,nums int)
GO
INSERT INTO #t
SELECT abs(CHECKSUM(NEWID())%100000)
GO 10000 ----生成10000笔资料,可以是任意一数字
SELECT * FROM #t
GO
DROP TABLE #t
GO
(3) --日期转换参数,值得收藏
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
20040912
select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12
其它我不常用的日期格式转换方法:
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004
select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1
select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177
(4)
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
---------------------------------
/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')
drop table tb
------------------
------------------
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 748393
李四 748494
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''')+ ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/
select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb
(5)
--按某一字段分组取最大(小)值所在行的数据
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)
/*
数据如下：
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order bya.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/
--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order bya.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
*/
--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
*/
--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) orderby a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 5 b5b5b5b5b5
*/
--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order bya.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order bya.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val havingCount(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
b 2 b2b2b2b2
*/
--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order bya.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) orderby a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val havingCount(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
a 3 a3:a的第三个值
b 4 b4b4
b 5 b5b5b5b5b5
*/
--七，如果整行数据有重复，所有的列都相同。
/*
数据如下：
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 1 a1--a的第一个值
a 3 a3:a的第三个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
select * , px = identity(int,1,1) into tmp from tb
select m.name,m.val,m.memo from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)
drop table tb,tmp
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数，不需要使用临时表。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
select m.name,m.val,m.memo from
(
select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)
drop table tb
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
(2 行受影响)
*/
(6) 合并列值
原著：邹建
改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳
表结构，数据如下：
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果：
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即：group by id, 求 value 的和（字符串相加）
1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--1. 创建处理函数
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数
SELECt id, value = dbo.f_str(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_str
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
（所影响的行数为 2 行）
*/
--2、另外一种函数.
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as value from tb
drop table tb
drop function dbo.f_hb
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
（所影响的行数为 2 行）
*/
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb
/*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
*/
--SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id
/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc
(2 row(s) affected)
*/
drop table tb
(7)
分拆列值
原著：邹建
改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳
有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
DROP TABLE #
2. 新的解决方法(sql server 2005)
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)B
DROP TABLE tb
/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
(5 行受影响)
*/
(8)
/*标题：分解字符串并查询相关数据作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-18
地点：广东深圳说明：通过使用函数等方法分解字符串查询相关数据。 问题：通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。例如 @str = '1,2,3',查询下表得到记录1,4,5,6
ID TypeID1 1,2,3,4,5,6,7,8,9,10,11,122 2,33 3,7,8,94 2,65 4,56 6,7*/-----------------------------create table tb (ID int , TypeID varchar(30))
insert into tb values(1 , '1,2,3,4,5,6,7,8,9,10,11,12')
insert into tb values(2 , '2,3')
insert into tb values(3 , '3,7,8,9')
insert into tb values(4 , '2,6')
insert into tb values(5 , '4,5')
insert into tb values(6 , '6,7')
go-------------------------------如果仅仅是一个,如@str = '1'.
declare @str as varchar(30)
set @str = '1'
select * from tb where charindex(',' + @str + ',' , ',' + TypeID + ',') > 0
select * from tb where ',' + TypeID + ',' like '%,' + @str + ',%'
/*ID TypeID ----------- ------------------------------1 1,2,3,4,5,6,7,8,9,10,11,12（所影响的行数为 1 行）
*/-------------------------------如果包含两个,如@str = '1,2'.
declare @str as varchar(30)
set @str = '1,2'
select * from tb where charindex(',' + left(@str , charindex(',' , @str) - 1) + ',' , ',' + typeid + ',') > 0 or
charindex(',' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',' , ',' + typeid + ',') > 0
select * from tb where ',' + typeid + ',' like '%,' + left(@str , charindex(',' , @str) - 1) + ',%' or
',' + typeid + ',' like '%,' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',%'
/*ID TypeID ----------- ------------------------------1 1,2,3,4,5,6,7,8,9,10,11,122 2,34 2,6（所影响的行数为 3 行）
*/---------------------------------------------如果包含三个或四个，用PARSENAME函数来处理.
declare @str as varchar(30)
set @str = '1,2,3,4'
select * from tb where
charindex(',' + parsename(replace(@str , ',' , '.') , 4) + ',' , ',' + typeid + ',') > 0 or
charindex(',' + parsename(replace(@str , ',' , '.') , 3) + ',' , ',' + typeid + ',') > 0 or
charindex(',' + parsename(replace(@str , ',' , '.') , 2) + ',' , ',' + typeid + ',') > 0 or
charindex(',' + parsename(replace(@str , ',' , '.') , 1) + ',' , ',' + typeid + ',') > 0
select * from tb where
',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 4) + ',%' or
',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 3) + ',%' or
',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 2) + ',%' or
',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 1) + ',%'
/*ID TypeID ----------- ------------------------------1 1,2,3,4,5,6,7,8,9,10,11,122 2,33 3,7,8,94 2,65 4,5（所影响的行数为 5 行）
*/-----------------------------------------如果超过四个，则只能使用函数或动态SQL来分解并查询数据。
/*名称：fn_split函数.
功能：实现字符串分隔功能的函数*/create function dbo.fn_split(@inputstr varchar(8000), @seprator varchar(10))
returns @temp table (a varchar(200))
asbegindeclare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator , @inputstr)
while @i >= 1
begininsert @temp values(left(@inputstr , @i - 1))
set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
set @i = charindex(@seprator , @inputstr)
endif @inputstr <> '\'
insert @temp values(@inputstr)
returnendgo--调用
declare @str as varchar(30)
set @str = '1,2,3,4,5'
select distinct m.* from tb m,
(select * from dbo.fn_split(@str,',')) n
where charindex(',' + n.a + ',' , ',' + m.typeid + ',') > 0
drop table tb
drop function dbo.fn_split
/*ID TypeID ----------- ------------------------------1 1,2,3,4,5,6,7,8,9,10,11,122 2,33 3,7,8,94 2,65 4,5（所影响的行数为 5 行）
*/--------------------------------------------使用动态SQL的语句。
declare @str varchar(200)
declare @sql as varchar(1000)
set @str = '1,2,3,4,5'
set @sql = 'select ''' + replace(@str , ',' , ''' as id union all select ''')
set @sql = @sql + ''''
set @sql = 'select distinct a.* from tb a , (' + @sql + ') b where charindex(' + ''','' + b.id + ' + ''',''' + ' , ' +''','' + a.typeid + ' + ''',''' + ') > 0 '
exec (@sql)
/*ID TypeID ----------- ------------------------------1 1,2,3,4,5,6,7,8,9,10,11,122 2,33 3,7,8,94 2,65 4,5（所影响的行数为 5 行）
*/(9)
--返程问题,找出雇员从本地出发后直接返回的情况
create table trav(name nvarchar(10),date datetime,comefrom nvarchar(10),destin nvarchar(10),id int)
insert into trav select '张三','2007-01-01','上海','广州',1
insert into trav select '李四','2007-01-01','上海','广州',2
insert into trav select '李四','2007-02-01','上海','成都',3
insert into trav select '张三','2007-01-15','广州','上海',4
insert into trav select '张三','2007-02-06','上海','广州',5
insert into trav select '张三','2007-02-18','广州','上海',6
goselect a.name,a.date,a.comefrom,a.destin,b.date,b.comefrom,b.destinfrom trav a inner join trav b on a.name=b.name and a.comefrom=b.destin and a.destin=b.comefrom where a.id<b.id
and not exists(select 1 from trav where comefrom=b.comefrom and date<b.date and date>a.date)
godrop table trav
/*name date comefrom destin date comefrom destin---------- ----------------------- ---------- ---------- ----------------------- ---------- ----------张三 2007-01-01 00:00:00.000 上海 广州 2007-01-15 00:00:00.000 广州 上海
张三 2007-02-06 00:00:00.000 上海 广州 2007-02-18 00:00:00.000 广州 上海
(2 行受影响)
*/(10)
--库存先进先出简单例子：
create table t(
id int identity(1,1),
name varchar(50),--商品名称
j int, --入库数量
c int, --出库数量
jdate datetime --入库时间
)
insert into t(name,j,c,jdate) select 'A',100,0,'2007-12-01'
insert into t(name,j,c,jdate) select 'A',200,0,'2008-01-07'
insert into t(name,j,c,jdate) select 'B',320,0,'2007-12-21'
insert into t(name,j,c,jdate) select 'A',100,0,'2008-01-15'
insert into t(name,j,c,jdate) select 'B',90,0,'2008-02-03'
insert into t(name,j,c,jdate) select 'A',460,0,'2008-02-01'
insert into t(name,j,c,jdate) select 'A',510,0,'2008-03-01'
go
create proc wsp
@name varchar(50),--商品名称
@cost int --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where name=@name
if(@spare>=@cost)
begin
--根据入库日期采用先进先出原则对货物的库存进行处理
update t set c=
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0
then a.j
else
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 then 0
else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c)
end
end
from t a where name=@name and j!=c
end
else
raiserror('库存不足',16,1)
return
go
--测试：
exec wsp @name='A',@cost=180
select * from t
--drop table t
--drop proc wsp
（11）
1. 查看数据库的版本
select @@version
2. 查看数据库所在机器操作系统参数
exec master..xp_msver
3. 查看数据库启动的参数
sp_configure
4. 查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
查看数据库服务器名和实例名
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)
5. 查看所有数据库名称及大小
sp_helpdb
重命名数据库用的SQL
sp_renamedb 'old_dbname', 'new_dbname'
6. 查看所有数据库用户登录信息
sp_helplogins
查看所有数据库用户所属的角色信息
sp_helpsrvrolemember
修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程
更改某个数据对象的用户属主
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'
注意: 更改对象名的任一部分都可能破坏脚本和存储过程。
把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本
7. 查看链接服务器
sp_helplinkedsrvlogin
查看远端数据库用户登录信息
sp_helpremotelogin
8.查看某数据库下某个数据对象的大小
sp_spaceused @objname
还可以用sp_toptables过程看最大的N(默认为50)个表
查看某数据库下某个数据对象的索引信息
sp_helpindex @objname
还可以用SP_NChelpindex过程查看更详细的索引情况
SP_NChelpindex @objname
clustered索引是把记录按物理顺序排列的，索引占的空间比较少。
对键值DML操作十分频繁的表我建议用非clustered索引和约束，fillfactor参数都用默认值。
查看某数据库下某个数据对象的的约束信息
sp_helpconstraint @objname
9.查看数据库里所有的存储过程和函数
use @database_name
sp_stored_procedures
查看存储过程和函数的源代码
sp_helptext '@procedure_name'
查看包含某个字符串@str的数据对象名称
select distinct object_name(id) from syscomments where text like '%@str%'
创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数
解密加密过的存储过程和函数可以用sp_decrypt过程
10.查看数据库里用户和进程的信息
sp_who
查看SQL Server数据库里的活动用户和进程的信息
sp_who 'active'
查看SQL Server数据库里的锁的情况
sp_lock
进程号1--50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.
spid是进程编号,dbid是数据库编号,objid是数据对象编号
查看进程正在执行的SQL语句
dbcc inputbuffer ()
推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句
sp_who3
检查死锁用sp_who_lock过程
sp_who_lock
11.收缩数据库日志文件的方法
收缩简单恢复模式数据库日志，收缩后@database_name_log的大小单位为M
backup log @database_name with no_log
dbcc shrinkfile (@database_name_log, 5)
12.分析SQL Server SQL 语句的方法:
set statistics time {on | off}
set statistics io {on | off}
图形方式显示查询执行计划
在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形
文本方式显示查询执行计划
set showplan_all {on | off}
set showplan_text { on | off }
set statistics profile { on | off }
13.出现不一致错误时，NT事件查看器里出3624号错误，修复数据库的方法
先注释掉应用程序里引用的出现不一致性错误的表，然后在备份或其它机器上先恢复然后做修复操作
alter database [@error_database_name] set single_user
修复出现不一致错误的表
dbcc checktable('@error_table_name',repair_allow_data_loss)
或者可惜选择修复出现不一致错误的小型数据库名
dbcc checkdb('@error_database_name',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 有3个参数:
repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误，
以及删除已损坏的文本对象，这些修复可能会导致一些数据丢失。
修复操作可以在用户事务下完成以允许用户回滚所做的更改。
如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。
如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。
修复完成后，请备份数据库。
repair_fast 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。
这些修复可以很快完成，并且不会有丢失数据的危险。
repair_rebuild 执行由 repair_fast 完成的所有修复，包括需要较长时间的修复（如重建索引）。
执行这些修复时不会有丢失数据的危险。
以上脚本来源于: http://www.51windows.net/data/files/file_797.asp
（12）--bom结构，查找节点下所有子节点：
create table os(id int,parentid int,desn varchar(10))
insert into os select 1,0,'体育用品'
insert into os select 2,0,'户外运动'
insert into os select 3,1,'篮球'
insert into os select 4,1,'足球'
insert into os select 5,2,'帐篷'
insert into os select 6,2,'登山鞋'
insert into os select 7,0,'男士用品'
insert into os select 8,7,'刮胡刀'
insert into os select 9,3,'大号篮球'
--求个节点下所有子节点：
create function f_cid(@id int)
returns varchar(500)
asbegindeclare @t table(id int,parentid int,desn varchar(10),lev int)
declare @lev int
set @lev=1
insert into @t select *,@lev from os where id=@id
while(@@rowcount>0)
beginset @lev=@lev+1
insert into @t select a.*,@lev from os a,@t b
where a.parentid=b.id and b.lev=@lev-1
enddeclare @cids varchar(500)
select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
return @cids
endgo--调用函数
select *,ids=dbo.f_cid(id) from os
--得到每个节点路径：
create proc wsp2
@id int
asselect *,cast(' ' as varchar(10)) fullpath into #os from os
DECLARE @i int,@j int
set @i=0
set @j=1
select @i=max(parentid) from #os
update #os set fullpath=id
while @j<=@i
beginupdate #os set fullpath=a.fullpath+','+ltrim(#os.id)
from #os inner join #os a on #os.parentid=a.id
where #os.parentid=@j
set @j=@j+1
endselect * from #os
go--调用存储过程
exec wsp2 1
（13）
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER trigger [tri_updatesalary_Mem_Type]
on [dbo].[SalaryIncrease]
after insertasdeclare @i int
set @i=@@identity
update mset m.Mem_Type=case when s.SMark>500000 then '退休会员'--500000
when s.SMark>400000 then '钻石五级'--400000
when s.SMark>300000 then '钻石四级'--300000
when s.SMark>200000 then '钻石三级'--200000
when s.SMark>100000 then '钻石二级'--100000
when s.SMark>50000 then '钻石一级'--50000
when s.SMark>40000 then '五星级'--40000
when s.SMark>30000 then '四星级'--30000
when s.SMark>20000 then '三星级'--20000
when s.SMark>10000 then '二星级'--10000
when s.SMark>5000 then '一星级'--5000
else '一般VIP会员'
endfrom MemberInfo mjoin (select s.SCardName,sum(s.SMark) as SMark
from SalaryIncrease s join inserted i on s.SCardName=i.SCardName group by s.SCardName) s
on m.Mem_Num=s.SCardName
--if exists(select * from MemberInfo m join inserted i on m.Mem_Num=i.SCardName and m.Mem_Mark>100 )if exists (select m.SCardName,sum(m.ShopMark)as mark from(select m.SCardName,m.ShopMark from ShoppingMark m join inserted i on m.SCardName=i.SCardName
where year(m.SDate)=year(getdate()) and month(m.SDate)=month(getdate())) m
group by m.SCardName having sum(m.ShopMark)>100)
/**//* --最新的(我写的,上面是整合魅影的)select * from ShoppingMark SM join (select m.SCardName,sum(m.ShopMark) as Mark from ShoppingMark m join inserted i on m.SCardName=i.SCardName group by m.SCardName) s
on SM.SCardName=s.ScardName where month(SDate)=month(getdate()) and year(SDate)=year(getdate()) and s.Mark>100*/beginupdate sset s.SIncease=case
--积分大于1000就是星级.所以不用判断是否是星级
when m.Mem_Type<>'一般VIP会员' then s.SMark*0.3
else case
when s.SMark>4000 then
s.SMark*0.3
-- when s.SMark>4000 then -- 200*0.2+200*0.23+200*0.25+200*0.28+(s.SMark-800)*0.3when s.SMark>3000 then
1000*0.2+1000*0.23+1000*0.25+(s.SMark-600)*0.28
when s.SMark>2000 then
1000*0.2+100*0.23+(s.SMark-400)*0.25
when s.SMark>1000 then
(s.SMark-200)*.023+1000*0.2
else s.SMark*0.2
end endfrom SalaryIncrease as s
join inserted ion s.SCardName=i.SCardName
join MemberInfo mon (i.SCardName=m.Mem_Num and s.SID=@i) or (i.SCardName=m.Mem_Num and s.SIncease=0)
end--go（14）
create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go
--查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
declare @level int
set @level = 1
insert into @t_level select @id , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.id , @level
from tb a , @t_Level b
where a.pid = b.id and b.level = @level - 1
end
return
end
go
--调用函数查询001(广东省)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
002 001 广州市
003 001 深圳市
004 002 天河区
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇
（所影响的行数为 10 行）
*/
--调用函数查询002(广州市)及其所有子节点
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
002 001 广州市
004 002 天河区
（所影响的行数为 2 行）
*/
--调用函数查询003(深圳市)及其所有子节点
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
003 001 深圳市
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇
（所影响的行数为 7 行）
*/
drop table tb
drop function f_cid
（15）
/*
标题：日期分表查询(version 1.0)
网名：悬崖边的舞者
时间：2008-07-14
地点：天津
说明：以日期或月份分表进行多日、多月查询连表查询的有关写法
*/
Create Procedure Search
@date1 datetime, --起始时间
@date2 datetime --终止时间
as
declare @date1New datetime,
@rq varchar(6),
@i int , --记录循环次数
@s varchar(5000) --根据表多少 可以扩大它
set @rq=convert(varchar,@date1,12) --把时间格式化 比如2008-06-20 变成了080620
set @s='select * from sensor'+@rq --初始化 @s='select * from sensor080620' 这样第一个表就有了
set @i=datediff(d,@date1,@date2) --日期相减
while @i>0
begin
--如果是非常重要的系统可以在这里加上该物理表是否存在的语句
--if exists(select * from dbo.sysobjects where
--id = object_id(N'c') and OBJECTPROPERTY(id,N'IsUserTable')=1)
--begin
--SELECT '存在 '
--end
set @date1New= dateadd(day,@i,@date1)
set @rq=convert(varchar,@date1New,12)
--如果是按月进行查询 就修改上面
set @s= @s+' union all select * from sensor'+@rq
set @i=@i-1 --累加
end
select @s
--exec(@S)
go
exec Search '2008-06-20 00:11:11.000','2008-07-25 00:11:11.000'
drop Procedure Search
（16）
知识点：SQL函数主要分为两中类型 1.单行函数(也叫标量函数) 2.多行函数
标量函数：对单一值操作，返回单一值。只要表达式有效即可使用标量函数。
我们把标量函数的分类列出来：
配置函数 返回当前配置信息
游标函数 返回游标信息
日期和时间函数 对日期和时间输入值进行操作，返回一个字符串、数字或日期和时间值
数学函数 对作为函数参数提供的输入值进行计算，返回一个数字值
元数据函数 返回有关数据库和数据库对象的信息
安全函数 返回有关用户和角色的信息
字符串函数 对字符串(char或varchar)输入值执行操作,返回一个字符串或数字值
系统函数 执行操作并返回有关Microsoft SQL Server中的值、对象和设置的信息
系统统计函数 返回系统的统计信息
文本和图象函数 对文本或图象进行操作并返回有关信息
聚合函数 是对一组值进行操作，跟标量函数不同.
行集函数 是可以向SQL语句中表引用一样使用，具体看文档了....
下面具体看例子了
*/
select emp_id,UPPER(lname),job_id
from employee
where lname='chang'
/*大小写处理函数*/
select emp_id,LOWER(lname),job_id
from employee
where lname='chang'
select emp_id,fname+lname NAME, /*使用字符处理函数*/
job_id,LEN(lname) Length,
CHARINDEX('a',lname)"Contains 'a'?"
from employee
where SUBSTRING(emp_id,1,2)='MA'
/*
运算符 + :连接值在一起
substring :选取给定位置和长度的子字符串
len :以数字值显示一个字符串的长度
charIndex :找到一个给定字符的数字位置
Replicate :用给定的字符替换给定长度的字符串
Ltrim和Rtrim :从一个字符串中去除头或尾的字符
*/
select ROUND(2008.092023,2),/*数字函数ROUND四舍五入*/
ROUND(2008.092023,0),
ROUND(2008.092023,-1)
/*注意奇数偶数的进位不同 它也可以用语日期函数处理*/
select lname,job_lvl,job_lvl%50/*使用%(模)函数 经常用与确定一个值是奇数还是偶数*/
from employee
where job_lvl=5
select lname,hire_date/*日期的使用*/
from employee
where lname like 'A%'
select lname,hire_date+365 AS "One Year"/*用日期做算术运算 该例显示所有job_id=10的雇员的名字和其受雇满一年的日期*/
from employee
where job_id=10
select lname, /*CONVERT函数用于日期转换*/
CONVERT(varchar(10),hire_date,20)
AS HIREDATE
from employee
select SUBSTRING(title,1,30) AS Title,ytd_sales/*对数字使用CAST函数*/
from titles
where CAST(ytd_sales AS char(20)) LIKE '3%'
/*
检索书名：这些图书的截止当前销售额的第一数字为3，并将这些图书的ytd_sales转换为char(20)
CAST(number,数据类型)
*/
select CONVERT(varchar(10),price,0) PRICE/*对数字使用CONVERT函数*/
from titles
where title_id='BU1111'
select CAST('2008-09-20 23:57' AS datetime)
/*使用CAST或CONVERT函数转换字符串到日期*/
select CONVERT(datetime,'2008-09-20 23:59')
select title,ytd_sales/*使用带有LIKE子句的CAST*/
from titles
where
CAST(ytd_sales AS char(20)) LIKE '15%'
AND type='trad_cook'
select title,ISNULL(price,0) Price,/*使用ISNULL函数*/
ISNULL(price*12,0) 'Ten Price'
from titles
/*为了计算所有图书的价格，在进行算术运算之前必须转换空值为一个数，ISNULL函数用来转换空值为零*/
select /*使用CASE表达式*/
CASE
when price IS NULL THEN 'Not yet priced'
when price<10 THEN 'Very Reasonable Title'
when price>=10 and price<20 THEN 'Coffee Table Title'
else 'Expensive book!'
END AS 'Price Category', /*这个逗号不敢忘*/
CONVERT(varchar(20),title) AS "shorttened Title"
from pubs.dbo.titles
ORDER BY price
/*
使用CASE表达式使得if-then-else条件判断很容易实现
*/
select /*使用CASE处理条件数据*/
CASE type
when 'popular_comp' then 'Popular Computing'
when 'mod_cook' then 'Modern Coking'
when 'business' then 'Business'
when 'psychology' then 'Psychology'
when 'trad_cook' then 'Traditional Cooking'
else 'Not yet categorized'
END AS Category,
CONVERT(varchar(30),titles) AS "Shortened Title",
Price AS price
from titles
where price IS NOT NULL
ORDER BY 1
/*
小结下：掌握用函数对数据的计算
用函数修饰不同的数据项
用函数改变数据格式
用函数改变列数据类型
使用ISNULL函数
使用if-then-else逻辑
*/
/*最后一个例子有问题先不管了掌握语法就OK*/
/*08.09.20*/
select * from employee
use master /*显示表结构*/
exec sp_helpdb
GO
use pubs
exec sp_help
GO
use pubs
exec sp_help employee
GO
select lname,job_lvl
from employee
where job_lvl<=50
select lname,job_lvl/*使用BETWEEN条件*/
from employee
where job_lvl BETWEEN 100 AND 200
select lname,job_id,job_lvl/*使用IN成员条件测试在列表中的值*/
from employee
where job_lvl IN (100,170,200)
select emp_id,lname,job_id,job_lvl/*使用NOT操作*/
from employee
where job_lvl NOT IN(100,170,200)
select title_id,title,type,price
from titles
where price IS NULL
select lname,job_lvl,job_lvl/*优先规则AND在前*/
from employee
where job_lvl=5
OR job_lvl=7
AND job_lvl>160
select lname,job_lvl,job_lvl/*用圆括号强制优先权*/
from employee
where (job_lvl=5
OR job_lvl=7)
AND job_lvl>160
select lname,job_id,job_lvl,hire_date/*用ORDER BY子句进行排序(升序排列)*/
from employee
ORDER BY hire_date
select lname,job_id,job_lvl,hire_date/*用DESC降序排列*/
from employee
ORDER BY hire_date DESC
select emp_id,lname,job_lvl*2 "Double job_lvl"/*用列别名排序*/
from employee
ORDER BY "Double job_lvl"
select lname,job_id,job_lvl/*多列排序*/
from employee
ORDER BY job_id,job_lvl DESC
/*
小结下：主要掌握WHERE子句限制输出行
使用比较条件
使用BETWEEN，IN，AND,NOT,OR操作
最后掌握使用ORDER BY子句排序输出行
所有操作在数据库pubs里进行
*/
（17）
--改变用户的锁定状态 (可适合一切 bit 类型的字段)
UPDATE tb_Table SET IsLook=Abs(IsLook-1) WHERE Id=@id
（18）
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1 <>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
（19）
--功能概述：显示某一表的结构
DECLARE @tableName nvarchar(100)
SET @tableName ='brand'
--mssql2005
SELECT (
CASE WHEN a.colorder=1 THEN d.name ELSE '' END)表名,
a.colorder 字段序号,
a.name 字段名,
(CASE WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN '√' ELSE '' END) 标识,
(CASE WHEN (
SELECT COUNT(*)
FROM sysobjects
WHERE (name IN
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid IN
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid IN
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 THEN '√' ELSE '' END) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 长度,
ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS 小数位数,
(CASE WHEN a.isnullable=1 THEN '√' ELSE '' END) 允许空,
ISNULL(e.text,'') 默认值,
ISNULL(g.[value],'') AS 字段说明
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype=b.xusertype
INNER JOIN sysobjects d ON a.id=d.id AND d.xtype='U' AND d.name <>'dtproperties'
LEFT JOIN syscomments e ON a.cdefault=e.id
LEFT JOIN sys.extended_properties g ON a.id=g.major_id AND a.colid = g.major_id
WHERE d.name=@tableName
ORDER BY a.id,a.colorder
（20）
--功能概述:重新编译一个表上的所有索引
alter index all on order_master rebuild
（21）
*--比较两个数据库的表结构差异
--*/
/*--调用示例
exec p_comparestructure 'xzkh_model','xzkh_new'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_comparestructure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_comparestructure]
GO
create proc p_comparestructure
@dbname1 varchar(250), --要比较的数据库名1
@dbname2 varchar(250) --要比较的数据库名2
as
create table #tb1(表名1 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 varchar(500),字段说明 varchar(500))
create table #tb2(表名2 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 varchar(500),字段说明 varchar(500))
--得到数据库1的结构
exec('insert into #tb1 SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case when a.status=0x80 then 1 else 0 end,
主键=case when exists(SELECT 1 FROM '+@dbname1+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM '+@dbname1+'..sysindexes WHERE indid in(
SELECT indid FROM '+@dbname1+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
默认值=isnull(e.text,''''''),字段说明=isnull(g.[value],'''''')
FROM '+@dbname1+'..syscolumns a
left join '+@dbname1+'..systypes b on a.xtype=b.xusertype
inner join '+@dbname1+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name <>''dtproperties''
left join '+@dbname1+'..syscomments e on a.cdefault=e.id
left join '+@dbname1+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--得到数据库2的结构
exec('insert into #tb2 SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case when a.status=0x80 then 1 else 0 end,
主键=case when exists(SELECT 1 FROM '+@dbname2+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM '+@dbname2+'..sysindexes WHERE indid in(
SELECT indid FROM '+@dbname2+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
默认值=isnull(e.text,''''''),字段说明=isnull(g.[value],'''''')
FROM '+@dbname2+'..syscolumns a
left join '+@dbname2+'..systypes b on a.xtype=b.xusertype
inner join '+@dbname2+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name <>''dtproperties''
left join '+@dbname2+'..syscomments e on a.cdefault=e.id
left join '+@dbname2+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--and not exists(select 1 from #tb2 where 表名2=a.表名1)
select 比较结果=case when a.表名1 is null and b.序号=1 then '库1缺少表：'+b.表名2
when b.表名2 is null and a.序号=1 then '库2缺少表:'+a.表名1
when a.字段名 is null and exists(select 1 from #tb1 where 表名1=b.表名2) then '库1 ['+b.表名2+'] 缺少字段：'+b.字段名
when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) then '库2 ['+a.表名1+'] 缺少字段：'+a.字段名
when a.标识 <>b.标识 then '标识不同'
when a.主键 <>b.主键 then '主键设置不同'
when a.类型 <>b.类型 then '字段类型不同'
when a.占用字节数 <>b.占用字节数 then '占用字节数'
when a.长度 <>b.长度 then '长度不同'
when a.小数位数 <>b.小数位数 then '小数位数不同'
when a.允许空 <>b.允许空 then '是否允许空不同'
when a.默认值 <>b.默认值 then '默认值不同'
when a.字段说明 <>b.字段说明 then '字段说明不同'
else '' end,
*
from #tb1 a
full join #tb2 b on a.表名1=b.表名2 and a.字段名=b.字段名
where a.表名1 is null or a.字段名 is null or b.表名2 is null or b.字段名 is null
or a.标识 <>b.标识 or a.主键 <>b.主键 or a.类型 <>b.类型
or a.占用字节数 <>b.占用字节数 or a.长度 <>b.长度 or a.小数位数 <>b.小数位数
or a.允许空 <>b.允许空 or a.默认值 <>b.默认值 or a.字段说明 <>b.字段说明
order by isnull(a.表名1,b.表名2),isnull(a.序号,b.序号)--isnull(a.字段名,b.字段名)
go
（22）
求字串中所含某字符或某子串的个数
declare @a varchar(100),@b varchar(20)
select @a='abcdefbcmnbcde',@b='bc'
select (len(@a)-len(replace(@a,@b,'')))/len(@b)
（23）
USE MASTER--连接系统数据库
IF EXISTS(select 1 from master..sysdatabases where name='bankDB')
DROP DATABASE bankDB
GO-----------------------------------------------建库------------------
--打开外围服务器
EXEC sp_configure 'show advanced options', 1
GORECONFIGUREGOEXEC sp_configure 'xp_cmdshell', 1
GORECONFIGUREGO--新建文件夹
exec xp_cmdshell 'MD e:\数据库'
CREATE DATABASE bankDB
ON(
NAME ='bankDB',
FILENAME='e:\数据库\bankDB.mdf',
SIZE = 10,
MAXSIZE=500,
FILEGROWTH=15%
)
GO--------------------------------------------------建表-----------------
USE bankDB--用户信息表
IF EXISTS(select 1 from bankDB..sysobjects where name='userInfo')
DROP TABLE userInfo
GOCREATE TABLE userInfo
(
customerID int identity(1,1) PRIMARY KEY , --顾客编号(自动增长 主键)
customerName varchar(20) not null, --开户名
PID varchar(20)UNIQUE not null, --身份证(18-15位数 唯一约束)
telephone varchar(20) not null, --联系电话(****-********或手机号11位数)
[address] ntext --联系地址
)
--银行卡信息表
IF EXISTS(select 1 from bankDB..sysobjects where name='cardInfo')
DROP TABLE cardInfo
GOCREATE TABLE cardInfo
(
cardID varchar(20) primary key , --卡号 (格式为1010 3576 **** ***(*部分是随机产生))
curType varchar(10) default('RMB') not null,--货币种类 (默认为RMB)
savingType varchar(10), --存款类型 (活期/定活两便/定期)
openDate datetime default(getdate()) not null,--开户日期 (默认为当前时间)
openMoney money check(openMoney<1) not null, --开户金额 (不能低于1元)
dalance money check(dalance<1) not null, --余额 (不能低于1元 否则将销户)
pass varchar(20) default(888888) not null,--密码 (6位数 开户时默认为6个8)
IsReportLoss bit default(0) not null, --是否过失 (是/否 默认为否 1是 0否)
customerID int not null --顾客编号 (外键 该卡号对应的顾客编号 一个用户可办多张卡)
)
--交易信息表
IF EXISTS(select 1 from bankDB..sysobjects where name='transInfo')
DROP TABLE transInfo
GOCREATE TABLE transInfo
(
transDate datetime not null, --交易日期(默认为当前时间)
cardID varchar(20) not null, --卡号(外键 可重复索引)
transType varchar(10) not null, --交易类型(只能是存入/支取)
transMoney money check(transMoney>0) not null, --交易金额(大于0)
remark ntext --备注(其它说明)
)
------------------------------约束-------------------------------
--约束电话和手机号码
if(object_id('uq_pid') is not null)
beginalter table userinfo
drop constraint uq_pid
endif(object_id('ck_PID') is not null)
beginalter table userinfo
drop constraint ck_PID
endif(object_id('ck_telephone') is not null)
beginalter table userinfo
drop constraint ck_telephone
endalter table userInfo
add constraint ck_PID check(len(pid) in (15,18)),
constraint uq_PID unique(pid),
constraint ck_telephone check(telephone like '1[35][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'--约束手机号码
ortelephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'--010-12345678
ortelephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'--0719-12345678
ortelephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]')--0719-1234567
----------------------------------------------设置卡号为随机数(方法1)---------------------------
declare @r decimal(10,8)
declare @time varchar(25)
set @time=convert(varchar,getdate(),120)+convert(varchar,datepart(ms,getdate()))
set @time=replace(@time,' ','')
set @time=replace(@time,':','')
set @time=replace(@time,'-','')
set @time=substring(@time,8,len(@time)-1)
--select @time--查看获得的随机数
select @r=rand(convert(bigint,@time))
set @time=cast(@r as varchar)
--select @timeset @time=substring(@time,3,len(@time)-1)
print @time
print '1001'+convert(varchar(10),@time)
----------------------------------------------设置卡号为随机数(方法2用了 存储过程)---------------------------
create proc proc_randCardID
@cardid varchar(19) output
asdeclare @r numeric(8,8)
set @cardid='1010 3657 '
while(1=1)
beginset @r=rand(datepart(mm,getdate())*100000+datepart(ss,getdate())*1000+datepart(ms,getdate()))
declare @temp char(8)
set @temp=substring(convert(varchar,@r),3,8)
set @cardid=@cardid+substring(@temp,1,4)+' '+substring(@temp,5,4)
if not exists(select 1 from cardinfo where cardid=@cardid)
breakend--测试（调用存储过程）
declare @card varchar(19)
exec proc_randCardID @card output
print @card
*/（24）
我来一段.用友U870中,生产订单欠料查询,我自己写的,优化后的语句我就不贴了
SQL code
SELECT mom.Moid,mom.MoCode,mom.CreateDate,mom.MoDid,mom.Qty,
mom.QualifiedInQty, mom.InvCode,mom.momName,mom.momStd,mom.RelsDate,mom.CloseDate,
list.allocateId,list.listinvcode,Inventory.cInvName,cInvStd,list.listQty,list.IssQty,list.WhCode,rd.impoids,rd.NonTakeNum,rd.TakeNum,rd.NonReQNum,rd.ReQNum,rd.NonReMNum,rd.ReMNum,
CASE WHEN listQty-rd.TakeNum-rd.NonReQNum-rd.ReQNum-rd.NonReMNum-rd.ReMNum<=0 THEN0 ELSE listQty-rd.TakeNum-rd.NonReQNum-rd.ReQNum-rd.NonReMNum-rd.ReMNum END AS CurPlanNum,
pl.tinvcode,pl.tqty,pl.tIssqty,pl.tNonTakeNum,pl.tTakeNum,pl.tNonReQNum,pl.tReQNum,pl.tNonReMNum,pl.tReMNum,
CASE WHEN pl.tqty-pl.tTakeNum-pl.tNonReQNum-pl.tReQNum-pl.tNonReMNum-pl.tReMNum<=0THEN 0 ELSE pl.tqty-pl.tTakeNum-pl.tNonReQNum-pl.tReQNum-pl.tNonReMNum-pl.tReMNumEND AS TotPlanNum,
ISNULL(CurrentStock.iQuantity,0) AS iQuantity, ISNULL(CurrentStock.fInQuantity,0) AS fInQuantity,CurrentStock.cWhCode,
CASE WHEN list.listQty-rd.TakeNum-rd.NonReQNum-rd.ReQNum-rd.NonReMNum-rd.ReMNum<=0THEN ISNULL(CurrentStock.iQuantity,0)+ISNULL(CurrentStock.fInQuantity,0) ELSE ISNULL(CurrentStock.iQuantity,0)+ISNULL(CurrentStock.fInQuantity,0)-( list.listQty-rd.TakeNum-rd.NonReQNum-rd.ReQNum-rd.NonReMNum-rd.ReMNum) END AS CurAppNum,
CASE WHEN pl.tqty-pl.tTakeNum-pl.tNonReQNum-pl.tReQNum-pl.tNonReMNum-pl.tReMNum<=0THEN ISNULL(CurrentStock.iQuantity,0)+ISNULL(CurrentStock.fInQuantity,0) ELSE ISNULL(CurrentStock.iQuantity,0)+ISNULL(CurrentStock.fInQuantity,0)-( pl.tqty-pl.tTakeNum-pl.tNonReQNum-pl.tReQNum-pl.tNonReMNum-pl.tReMNum) END AS TotAppNum
FROM
(SELECT mom_order.Moid,mom_order.MoCode,mom_order.CreateDate,mom_orderdetail.MoDid,mom_orderdetail.Qty,mom_orderdetail.WhCode,
mom_orderdetail.QualifiedInQty,mom_orderdetail.BomId, mom_orderdetail.InvCode,inventory.cInvName AS momName,inventory.cInvStd as momStd,mom_orderdetail.RelsDate,mom_orderdetail.CloseDate
FROM mom_order,mom_orderdetail ,inventory WHERE mom_orderdetail.status='3' AND mom_order.MOID=mom_orderdetail.MOID AND mom_orderDetail.InvCode=inventory.cInvcode) mom
LEFT JOIN
(SELECT Allocateid,MODID,Qty AS listQty,IssQty,WhCode,InvCode AS listinvcode FROM mom_moallocate) list ON mom.MODID=list.MODID
LEFT JOIN Inventory ON list.listInvCode=Inventory.cInvCode
LEFT JOIN
(SELECT al.invcode as tinvcode,SUM(al.QTY) AS TQTY,SUM(al.IssQty) AS TIssQty ,SUM(NonTakeNum) AS tNonTakeNum,SUM(TakeNum) AS tTakeNum,
SUM(NonReQNum) AS tNonReQNum,SUM(ReQNum) AS tReQNum,SUM(NonReMNum) AS tNonReMNum,SUM(ReMNum) AS tReMNum
FROM mom_moallocate al
INNER JOIN mom_orderdetail od ON al.modid=od.modid AND od.status='3'
LEFT JOIN
(SELECT cinvcode,impoids,
SUM(CASE WHEN iQuantity>0 AND cHandler IS NULL THEN iQuantity ELSE 0 END ) AS NonTakeNum,
SUM(CASE WHEN iQuantity>0 AND cHandler IS NOT NULL THEN iQuantity ELSE 0 END ) AS TakeNum,
SUM(CASE WHEN iQuantity<0 AND cRdCode='212' AND cHandler IS NULL THEN iQuantity ELSE 0 END) AS NonReQNum,
SUM(CASE WHEN iQuantity<0 AND cRdCode='212' AND cHandler IS NOT NULL THEN iQuantityELSE 0 END) AS ReQNum,
SUM(CASE WHEN iQuantity<0 AND cRdCode='208' AND cHandler IS NULL THEN iQuantity ELSE 0 END) AS NonReMNum,
SUM(CASE WHEN iQuantity<0 AND cRdCode='208' AND cHandler IS NOT NULL THEN iQuantityELSE 0 END) AS ReMNum
FROM rdrecord,rdrecords where rdrecord.cBusType='领料' AND rdrecord.id=rdrecords.id AND rdrecords.impoids >0
GROUP BY cinvCode,impoids)rdt
ON al.allocateid=rdt.impoids AND al.invcode=rdt.cinvcode
GROUP BY al.Invcode ) pl
ON list.listinvcode=pl.tinvcode
LEFT JOIN
(SELECT cinvcode,impoids,
SUM(CASE WHEN iQuantity>0 AND cHandler IS NULL THEN iQuantity ELSE 0 END ) AS NonTakeNum,
SUM(CASE WHEN iQuantity>0 AND cHandler IS NOT NULL THEN iQuantity ELSE 0 END ) AS TakeNum,
SUM(CASE WHEN iQuantity<0 AND cRdCode='212' AND cHandler IS NULL THEN iQuantity ELSE 0 END) AS NonReQNum,
SUM(CASE WHEN iQuantity<0 AND cRdCode='212' AND cHandler IS NOT NULL THEN iQuantityELSE 0 END) AS ReQNum,
SUM(CASE WHEN iQuantity<0 AND cRdCode='208' AND cHandler IS NULL THEN iQuantity ELSE 0 END) AS NonReMNum,
SUM(CASE WHEN iQuantity<0 AND cRdCode='208' AND cHandler IS NOT NULL THEN iQuantityELSE 0 END) AS ReMNum
FROM rdrecord,rdrecords where rdrecord.cBusType='领料' AND rdrecord.id=rdrecords.id AND rdrecords.impoids >0
GROUP BY cinvCode,impoids) rd ON list.Allocateid=rd.impoids AND list.listinvcode=rd.cinvcode
LEFT JOIN CurrentStock ON list.listinvcode=CurrentStock.cInvCode AND CurrentStock.cWhcode in ('M01','F01','BCP') AND CurrentStock.iQuantity>0
（25）
说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
（26）
--字符串函数
--CHARINDEX
--返回字符串中指定表达式的起始位置。
--语法
--CHARINDEX ( expression1 , expression2 [ , start_location ] )
--参数
--expression1
--一个表达式，其中包含要寻找的字符的次序。expression1 是一个短字符数据类型分类的表达式。
--expr-ession2
--一个表达式，通常是一个用于搜索指定序列的列。expression2 属于字符串数据类型分类。
--start_location
--在 expression2 中搜索 expression1 时的起始字符位置。如果没有给定 start_location，而是一个负数或零，则将从 expression2 的起始位置开始搜索。
--返回类型
--int
--注释
--如果 expression1 或 expression2 之一属于 Unicode 数据类型（nvarchar 或 nchar）而另一个不属于，则将另一个转换为 Unicode 数据类型。
--如果 expression1 或 expression2 之一为 NULL 值，则当数据库兼容级别为 70 或更大时，CHARINDEX返回 NULL 值。当数据库兼容级别为 65 或更小时，CHARINDEX 仅在 expression1 和 expression2 都为 NULL 时返回 NULL 值。
--果在 expression2 内没有找到 expression1，则 CHARINDEX 返回 0。
select charindex('abc', 'fdfdfafgfgffabcfdfdfdf',0)
--len
select len('fdfdfd')
--6
select len('好阿')
--2
--lower
select lower('DFDFFF')
--upper
select upper ('fdfdfdf')
--ltrim
select ltrim(' dfdfd')
--rtrim
select rtrim('dfdfd ')
select ('ddff ')
--right
select right('abcdefg',5)
--cdefg
--replace
select replace ('北大青鸟市一所好学校','市','是')
--stuff
STUFF
--删除指定长度的字符并在指定的起始点插入另一组字符。
--语法
--STUFF ( character_expression , start , length , character_expression )
--参数
--character_expression
--由字符数据组成的表达式。character_expression 可以是常量、变量，也可以是字符或二进制数据的列。
--start
--是一个整形值，指定删除和插入的开始位置。如果 start 或 length 是负数，则返回空字符串。如果 start比第一个 character_expression 长，则返回空字符串。
--length
--是一个整数，指定要删除的字符数。如果 length 比第一个 character_expression 长，则最多删除到最后一个 character_expression 中的最后一个字符。
--返回类型
select stuff('abcdef',1,3,'okokokok')
-- 日期函数
select getdate()
-- 返回当前系统日期
--dateadd
-- 返回日相加后的日期 day
select dateadd (dd,5,'1999-9-9')
-- 返回月相加后的日期 month
select dateadd (month,5,'1999-9-9')
-- 返回年相加后的日期 year
select dateadd (yy,5,'1999-9-9')
--DateDiff
select datediff(mm,'2003-4-5','2007-6-7')
--dateName
--weekday (dw) 日期部分返回星期几（星期天、星期一等）。
--DATENAME
--返回代表指定日期的指定日期部分的字符串。
--语法
--DATENAME ( datepart , date )
--参数
--datepart
--是指定应返回的日期部分的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩写。
--日期部分 缩写
--year yy, yyyy
--quarter qq, q
--month mm, m
--dayofyear dy, y
--day dd, d
--week wk, ww
--weekday dw
--Hour hh
--minute mi, n
--second ss, s
--millisecond ms
select datename(dw,'1999-9-9')
--datepart
select datepart(day,'1999-9-11')
--数学函数
--绝对值 abs
select abs(-60)
--60
--ceiling 取出大于或等于指定表达式的最小整数
select ceiling(334.3)
--335
--floor 取出小于或等于指定表达式的最大整数
select floor (44.5)
--44
--power 返回幂值
select power(5,3)
--rounud 四舍五入
select round (44.543,1)
--sing 正数返回 1 负数 返回-1 零 返回 0
select sign(0)
--sqrt 取平方根
select sqrt(9)
--系统函数
--convert 返回指定的类型
select convert(varchar(5),22222)
--current_user
select current_user
--dbo
--datalength
select datalength('好人一生平安！！')
--16
--host_name 登陆的计算机名字
select host_name()
--System_user
select system_user
--user_name
select user_name(1)
--0 public
--1 dbo
--2 guest
select convert (varchar(4),datepart(yyyy,getdate()))
select convert(varchar(2),datepart(dd,getdate()))
select convert(varchar(2),datepart(mm,getdate()))
select convert(varchar(4),right(rand(datepart(ms,getdate())*1000),4))
（27）
收缩数据库日志
BACKUP LOG Hr_Cp_Sbu WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (Hr_Cp_Sbu, 0)
--Hr_Cp_Sbu 数据库名
我在使用sqlserver2000数据库时，使用一段时间后日志日益庞大。所以我就给Server添加了一个作业，让它每月运行一次。来完成一次我的数据库维护工作。（dmt就是我的多媒体数据库名）
backup log dmt with no_log
--这里只是截断日志，日志文件的物理大小并没有减少。
--而且建议此语句执行完后马上备份数据库（我这里没有备份）
exec sp_dboption 'dmt','autoshrink','true'
--确保数据库设置为自动收缩
dbcc shrinkdatabase(dmt,10)
--收缩数据库，保留10％的空闲空间
--注意，这个操作真真缩小数据库的物理文件。（我自己的日志文件从8G缩小到了7M,嘿嘿）
数据库文件将成为自动周期性收缩的候选文件。
USE master
EXEC sp_dboption 'pubs', 'autoshrink', 'true'
（28）
-- 获取M$ SQL Server用户表的字段信息
USE database1SELECT表名 = CASE a.colorder WHEN 1 THEN c.name ELSE '' END,
序 = a.colorder, 字段名 = a.name,标识 = CASE COLUMNPROPERTY(a.id,a.name,'IsIdentity') WHEN 1 THEN '√' ELSE '' END,
主键 = CASE
WHEN EXISTS (
SELECT *
FROM sysobjectsWHERE xtype='PK' AND name IN (
SELECT name FROM sysindexesWHERE id=a.id AND indid IN (
SELECT indid FROM sysindexkeysWHERE id=a.id AND colid IN (
SELECT colid FROM syscolumnsWHERE id=a.id AND name=a.name
)
)
)
)
THEN '√'
ELSE ''
END, 类型 = b.name, 字节数 = a.length,长度 = COLUMNPROPERTY(a.id,a.name,'Precision'),
小数 = CASE ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)
WHEN 0 THEN ''
ELSE CAST(COLUMNPROPERTY(a.id,a.name,'Scale') AS VARCHAR)
END,允许空 = CASE a.isnullable WHEN 1 THEN '√' ELSE '' END,
默认值 = ISNULL(d.[text],''),
说明 = ISNULL(e.[value],'')
FROM syscolumns aLEFT JOIN systypes b ON a.xtype=b.xusertype
INNER JOIN sysobjects c ON a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties'
LEFT JOIN syscomments d ON a.cdefault=d.id
LEFT JOIN sysproperties e ON a.id=e.id AND a.colid=e.smallid
ORDER BY c.name, a.colorder
（29）
免费视频：
浪曦ASP.NET企业实战系列 http://down1.langsin.com/001.rar
浪曦NUnit详解视频 http://down1.langsin.com/002.rar
浪曦Struts 2应用开发详解 http://www.verycd.com/topics/210454
VIP视频：
浪曦Java常见笔试、面试题目深度剖析 http://down1.langsin.com/003.rar
浪曦J2EE测试实用指南http://down1.langsin.com/005.rar
浪曦Lucene视频教程 http://down1.langsin.com/006.rar
浪曦电脑维护项目 http://down1.langsin.com/007.rar
培训视频：
浪曦ExtJS视频之Grid控件 http://down1.langsin.com/008.rar
业务QQ：1050429531
（30）
一年中所有为星期二的日期
select dateadd(day,x,col),'星期二' from
(
select cast('2006-1-1' as datetime) as col
)a cross join
(
SELECT top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x
FROM(SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN(SELECT 0 i UNION ALL SELECT 8) b3
CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4
CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5
CROSS JOIN(SELECT 0 i UNION ALL SELECT 64) b6
CROSS JOIN(SELECT 0 i UNION ALL SELECT 128) b7
CROSS JOIN(SELECT 0 i UNION ALL SELECT 256) b8
order by 1
)b
where datepart(dw,dateadd(day,x,col))=3
（31）
--排名子函数
select ROW_NUMBER() OVER (ORDER BY sum(amount)) ROW_NUMBER,
Rank () OVER (ORDER BY sum(amount)) Rank ,
DENSE_RANK() OVER (ORDER BY sum(amount)) DENSE_RANK
--说明
ROW_NUMBER() OVER (ORDER BY sum(amount)) ROW_NUMBER,--行号
Rank () OVER (ORDER BY sum(amount)) Rank ,--可并列排名，并列后，排名跳号
DENSE_RANK() OVER (ORDER BY sum(amount)) DENSE_RANK --可并列排名，并列后，排名不跳号
（32）
create table A(
student varchar(20),
subject varchar(20),
grade int
)
insert A select 'student1' ,'语文', 80
union all select 'student1', '数学', 70
union all select 'student1', '英语', 60
union all select 'student2' ,'语文' ,90
union all select 'student2', '数学', 80
union all select 'student2' ,'英语' ,100
declare @a varchar(1000)
set @a='select student '
select @a=@a+',sum(case when subject='''+subject+''' then grade end)'+' as '+ subject ----這裡sum改為min或max結果一樣
from (select distinct subject from A)T1
set @a=@a+' from A group by student'
exec(@a)
print (@a)
（33）
CREATE function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
/*--实现split功能 的函数 */
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
end
（34）
/*--解决一段sql文字符串超过8000字符的问题
应用行列转换的时候，如果科目过多的时候，会出现一个字符串>8000长度的问题
以下的例子中并不会出现sql文超长
假设有张学生成绩表(tb)如下:
姓名 课程 分数
tom yuwen 74
tom shuxue 83
tom wuli 93
jim yuwen 74
jim shuxue 84
jim wuli 94
想变成(得到如下结果)：
minzi yuwen shuxue wuli
---- ---- ---- ----
jim 74 84 94
tom 74 83 93
-------------------
在sql server2000下调试通过
*/
create table tb(minzi varchar(10) , kecheng varchar(10) , fen int)
insert into tb values('tom' , 'yuwen' , 74)
insert into tb values('tom' , 'shuxue' , 83)
insert into tb values('tom' , 'wuli' , 93)
insert into tb values('jim' , 'yuwen' , 74)
insert into tb values('jim' , 'shuxue' , 84)
insert into tb values('jim' , 'wuli' , 94)
go
select distinct kecheng into #tb from tb
declare @sqlhead varchar(8000),@sqlend varchar(8000)
,@sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000),@sql4 varchar(8000)
,@i int,@ic varchar(20)
--
select id=identity(int,0,1),gid=0
,a=',['+kecheng +']=max(case b.kecheng when '''
+kecheng+''' then b.fen end)'
into # from #tb a
--
select @i=max(len(a)) from #
print @i
set @i=8000/@i
--
update # set gid=id/@i
select @i=max(gid) from #
select @i= count(*) from #
--
select @sqlhead='''select b.minzi '''
,@sqlend=''' from tb b group by b.minzi'''
,@sql1='',@sql2='select ',@sql3='',@sql4=''
while @i>=0
select @ic=cast(@i as varchar),@i=@i-1
,@sql1='@'+@ic+' varchar(8000),'+@sql1
,@sql2=@sql2+'@'+@ic+'='''','
,@sql3='select @'+@ic+'=@'+@ic+'+a from # where id='+@ic
+char(13)+@sql3
,@sql4=@sql4+'+@'+@ic
select @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
,@sql2=left(@sql2,len(@sql2)-1)+char(13)
,@sql3=left(@sql3,len(@sql3)-1)
,@sql4=substring(@sql4,2,8000)
exec ( @sql1+@sql2+@sql3+' exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')')
（35）
declare @i int
set @i = convert(int,rand(checksum(newid()))*5)
select @i
------------随机数---------------
（36）
复制表结构，而不复制内容
select * into #table1
from admin
where 1<>1 --不会复制数据
select * from #table1
drop table #table1
这样#table1就有了admin 的所有字段了
（37）
/*功能：2000当中绘画日历
*/DECLARE @Year nvarchar(4)
DECLARE @YearMonth nvarchar(7) --月份
DECLARE @strTop nvarchar(200)
DECLARE @ForI INT,@ForYear INT ,@MaxDay INT
DECLARE @RowX INT --行位置
DECLARE @strWeekDayList nvarchar(20)
DECLARE @strPrint nvarchar(300)
-- ======================================SET @Year='2008' --请在这里输入年份
-- ======================================SET @strTop= '日'+char(9)+'一'+char(9)+'二' +char(9)+'三'++char(9)+'四'++char(9)+'五'++char(9)+'六' +char(13)+
'───────────────────────────'SET @strWeekDayList='日一二三四五六'
SET @ForYear=1
WHILE @ForYear<=12 --1月份至12月份
BEGINSET @YearMonth=@Year + '-' +CAST( @ForYear AS nvarchar(2))
SET @MaxDay=DAY(DATEADD(Day,-1,DATEADD(Month,1,@YearMonth+'-01')))
SET @RowX=CHARINDEX(RIGHT(DATENAME(WeekDay,@YearMonth+'-01'),1),@strWeekDayList)-1
SET @strPrint=''
SET @ForI=1
WHILE @ForI<=@RowX --构造1号的位置
BEGINSET @strPrint=@strPrint+CHAR(9)
SET @ForI=@ForI+1
ENDSET @ForI=1
WHILE @ForI<=@MaxDay --构造2号到月底的位置
BEGINSET @strPrint=@strPrint+CAST(@ForI AS nvarchar(2)) +Char(9)
SET @RowX=@RowX+1
SET @ForI=@ForI+1
IF (@RowX%7=0)
BEGINSET @RowX=0
SET @strPrint=@strPrint+CHAR(13)
END ENDSET @ForYear=@ForYear+1
-- 打印输出一个月的结果
PRINT '━━━━━━━━━━━━━━━━━━━━━━━━━━━'
PRINT +Char(9)++Char(9)+' '+@YearMonth+CHAR(10)
PRINT @strTop
PRINT @strPrint +CHAR(10)
END（38）
/*
SQL 2005当中绘画日历
*/
Set Nocount On
Declare
@Date datetime,
@StartDate datetime,
@EndDate datetime,
@FirstIndex int
Set @Date ='20080222' --输入一个日期，即可算出当月的日历
Select
@StartDate=Convert(char(6),@Date,112)+'01',
@EndDate=Dateadd(month,1,@StartDate)-1,
@FirstIndex=Datediff(day,-1,@StartDate)%7
;With t As
(
Select Date=Convert(int,1),Row=(@FirstIndex)/7,Col=@FirstIndex
Union All
Select Date=Date+1,Row=(@FirstIndex+Date)/7,Col=(Date+@FirstIndex)%7
From t
Where Date<=Datediff(day,@StartDate,@EndDate)
)
Select
[日]=Isnull(Convert(char(2),[0]),''),
[一]=Isnull(Convert(char(2),[1]),''),
[二]=Isnull(Convert(char(2),[2]),''),
[三]=Isnull(Convert(char(2),[3]),''),
[四]=Isnull(Convert(char(2),[4]),''),
[五]=Isnull(Convert(char(2),[5]),''),
[六]=Isnull(Convert(char(2),[6]),'')
From t
Pivot (Max(Date) For col In([0],[1],[2],[3],[4],[5],[6])) b
/*
2008年2月份
------------
日 一 二 三 四 五 六
---- ---- ---- ---- ---- ---- ----
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29
*/
（39）
轉發一個日期列轉行的，作者：pt1314917 背着灵魂漫步
http://topic.csdn.net/u/20080911/14/86211090-5f32-40ac-be61-8e7d3d8cab2a.html
[code=SQL][/code]create table t(t_pdno int,t_opno smallint,t_tdat datetime,t_pass int)
insert into t
select 100003,10,'2001-01-09',5090 union
select 100003,10,'2001-01-11',4410 union
select 100003,10,'2003-03-26',0 union
select 100003,20,'2001-01-09',0 union
select 100003,20,'2001-01-11',0 union
select 100003,20,'2001-01-16',2068 union
select 100003,20,'2001-01-17',748 union
select 100003,20,'2001-02-01',1826
--借助临时表
if object_id('tempdb..#') is null
select top 31 id=identity(int,1,1) into # from syscolumns
declare @sql varchar(8000)
set @sql='select t_pdno,t_opno,t_aaym=convert(varchar(7),t_tdat,120)'
select @sql=@sql+',['+ltrim(id)+']=max(case datepart(dd,t_tdat) when '''+ltrim(id)+''' then t_pass else 0 end)'
from #
set @sql=@sql+' from t group by t_pdno,t_opno,convert(varchar(7),t_tdat,120)'
exec(@sql)
（40）
[url=http://topic.csdn.net/u/20081106/11/1141518b-05af-4843-b85d-93db9b484fea.html]http://topic.csdn.net/u/20081106/11/1141518b-05af-4843-b85d-93db9b484fea.html
四舍五入和加千分位
原創：dobear_0
SQL code
create function GetFormatString(@dec decimal(28,8), @n int)
returns varchar(32) as
begin
declare @str varchar(32), @len int, @left varchar(32), @right varchar(32),@endvarchar(32)
if @n!='0'
BEGIN
set @str= round(@dec,@n)
select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2
while @len>1
begin
select @left=stuff(@left,@len,0,','), @len=@len-3
end
select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4
while @len <=len(@right)
begin
select @right=stuff(@right,@len,0,','), @len=@len+4
end
set @end= @left+'.'+@right
end
else
BEGIN
set @str= round(@dec,@n)
select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2
while @len>1
begin
select @left=stuff(@left,@len,0,','), @len=@len-3
end
select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4
while @len <=len(@right)
begin
select @right=stuff(@right,@len,0,','), @len=@len+4
end
set @end= @left
end
return @end
end
select dbo.GetFormatString(123645.7889, 2)+'±'+'MON'
union all select dbo.GetFormatString(123645.7889, 3)+'±'+'MON'
union all select dbo.GetFormatString(123645.7889, 4)+'±'+'MON'
（41）
1.给一条得到任意月份的是大天数
select day(dateadd(dd,-1,dateadd(mm,1,Dateadd(mm,datediff(mm,0,getdate()),0))))
2.自定义Sql查询分析中的一些快捷键
A. 如:按Ctrl+0 表示查询指定表的最近30条记录.
选 "工具"-->"自定义"-->"自定义"页面,快捷方式=Ctrl+0,存储过程=Sp_Select_T
B. 按Ctrl+F1 得到某个表的表结构
选 "工具"-->"自定义"-->"自定义"页面,快捷方式=Ctrl+F1 ,存储过程=Sp_HelpTable
存储过程如下
SQL code
use master
go
if exists(select 1 from dbo.sysobjects where name='SP_select_t' and xtype='P')
drop proc SP_select_t
go
CREATE proc SP_select_t(@Tablename sysname)
as
exec('Select top 30 * from '+@Tablename+' Order by 1 desc')
go
if exists(select 1 from dbo.sysobjects where name='SP_HelpTable' and xtype='P')
drop proc SP_HelpTable
go
create proc SP_HelpTable(@TableName varchar(20))
as
if not exists (select 1 from sysobjects where name=@tableName and Xtype='U')
begin
Select 'No Table Name is'+@TableName as Error
Return
end
SELECT 表名=case when a.colorder=1 then d.name else '' end,
字段名=a.name,
--标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
/*主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name
in ( SELECT name FROM sysindexes WHERE indid
in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid )))
then '√' else '' end,
*/
类型=b.name,
--占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
字段序号=a.colorder,
字段说明=isnull(g.[value],'')
FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id
and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id
and a.colid=g.smallid
where d.name=@TableName
order by a.id,a.colorder
Exec sp_helpIndex @tablename
go
（42）
下列语句部分是Mssql语句，不可以在access中使用。
SQL分类：
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
首先,简要介绍基础语句：
1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count * as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
其次，大家来看一些不错的sql语句
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
随机选择数据库记录的方法（使用Randomize函数，通过SQL语句实现）
对存储在数据库中的数据来说，随机数特性能给出上面的效果，但它们可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见的解决方案是建立如下所示的循环：
Randomize
RNumber = Int(Rnd*499) +1
While Not objRec.EOF
If objRec("ID") = RNumber THEN
... 这里是执行脚本 ...
end if
objRec.MoveNext
Wend
这很容易理解。首先，你取出1到500范围之内的一个随机数（假设500就是数据库内记录的总数）。然后，你遍历每一记录来测试ID的值、检查其是否匹配RNumber。满足条件的话就执行由THEN 关键字开始的那一块代码。假如你的RNumber 等于495，那么要循环一遍数据库花的时间可就长了。虽然500这个数字看起来大了些，但相比更为稳固的企业解决方案这还是个小型数据库了，后者通常在一个数据库内就包含了成千上万条记录。这时候不就死定了？
采用SQL，你就可以很快地找出准确的记录并且打开一个只包含该记录的recordset，如下所示：
Randomize
RNumber = Int(Rnd*499) + 1
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber
set objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email")
（43）
不必写出RNumber 和ID，你只需要检查匹配情况即可。只要你对以上代码的工作满意，你自可按需操作“随机”记录。Recordset没有包含其他内容，因此你很快就能找到你需要的记录这样就大大降低了处理时间。
再谈随机数
现在你下定决心要榨干Random 函数的最后一滴油，那么你可能会一次取出多条随机记录或者想采用一定随机范围内的记录。把上面的标准Random 示例扩展一下就可以用SQL应对上面两种情况了。
为了取出几条随机选择的记录并存放在同一recordset内，你可以存储三个随机数，然后查询数据库获得匹配这些数字的记录：
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3
假如你想选出10条记录（也许是每次页面装载时的10条链接的列表），你可以用BETWEEN 或者数学等式选出第一条记录和适当数量的递增记录。这一操作可以通过好几种方式来完成，但是 SELECT 语句只显示一种可能（这里的ID 是自动生成的号码）：
SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"
注意：以上代码的执行目的不是检查数据库内是否有9条并发记录。
随机读取若干条记录，测试过
Access语法：SELECT top 10 * From 表名 ORDER BY Rnd(id)
Sql server:select top n * from 表名 order by newid()
mysqlelect * From 表名 Order By rand() Limit n
Access左连接语法(最近开发要用左连接,Access帮助什么都没有,网上没有Access的SQL说明,只有自己测试, 现在记下以备后查)
语法elect table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where ...
使用SQL语句 用...代替过长的字符串显示
语法：
SQL数据库：select case when len(field)>10 then left(field,10)+'...' else field end as news_name,news_id from tablename
Access数据库：SELECT iif(len(field)>2,left(field,2)+'...',field) FROM tablename;
Conn.Execute说明
Execute方法
该方法用于执行SQL语句。根据SQL语句执行后是否返回记录集，该方法的使用格式分为以下两种：
1．执行SQL查询语句时，将返回查询得到的记录集。用法为：
Set 对象变量名=连接对象.Execute("SQL 查询语言")
Execute方法调用后，会自动创建记录集对象，并将查询结果存储在该记录对象中，通过Set方法，将记录集赋给指定的对象保存，以后对象变量就代表了该记录集对象。
2．执行SQL的操作性语言时，没有记录集的返回。此时用法为：
连接对象.Execute "SQL 操作性语句" [, RecordAffected][, Option]
·RecordAffected 为可选项，此出可放置一个变量，SQL语句执行后，所生效的记录数会自动保存到该变量中。通过访问该变量，就可知道SQL语句队多少条记录进行了操作。
·Option 可选项，该参数的取值通常为adCMDText，它用于告诉ADO，应该将Execute方法之后的第一个字符解释为命令文本。通过指定该参数，可使执行更高效。
·BeginTrans、RollbackTrans、CommitTrans方法
这三个方法是连接对象提供的用于事务处理的方法。BeginTrans用于开始一个事物；RollbackTrans用于回滚事务；CommitTrans用于提交所有的事务处理结果，即确认事务的处理。
事务处理可以将一组操作视为一个整体，只有全部语句都成功执行后，事务处理才算成功；若其中有一个语句执行失败，则整个处理就算失败，并恢复到处里前的状态。
BeginTrans和CommitTrans用于标记事务的开始和结束，在这两个之间的语句，就是作为事务处理的语句。判断事务处理是否成功，可通过连接对象的Error集合来实现，若Error集合的成员个数不为0，则说明有错误发生，事务处理失败。Error集合中的每一个Error对象，代表一个错误信息。
（44）
Create FUNCTION [dbo].[ID15TO18] (@id15 char(15))
/*功能：将身份证的15位号码升级为18位
--
-- --调用函数
-- update
-- 表
-- set
-- 身份证号 = dbo.ID15TO18(身份证号)
-- where
-- LEN(身份证号) = 15
--
--
*/
RETURNS CHAR(18)
AS
BEGIN
DECLARE @ID18 CHAR(18)
DECLARE @S1 AS INTEGER
DECLARE @S2 AS INTEGER
DECLARE @S3 AS INTEGER
DECLARE @S4 AS INTEGER
DECLARE @S5 AS INTEGER
DECLARE @S6 AS INTEGER
DECLARE @S7 AS INTEGER
DECLARE @S8 AS INTEGER
DECLARE @S9 AS INTEGER
DECLARE @S10 AS INTEGER
DECLARE @S11 AS INTEGER
DECLARE @S12 AS INTEGER
DECLARE @S13 AS INTEGER
DECLARE @S14 AS INTEGER
DECLARE @S15 AS INTEGER
DECLARE @S16 AS INTEGER
DECLARE @S17 AS INTEGER
DECLARE @S18 AS INTEGER
SET @S1=SUBSTRING(@ID15,1,1)
SET @S2=SUBSTRING(@ID15,2,1)
SET @S3=SUBSTRING(@ID15,3,1)
SET @S4=SUBSTRING(@ID15,4,1)
SET @S5=SUBSTRING(@ID15,5,1)
SET @S6=SUBSTRING(@ID15,6,1)
SET @S7=1
SET @S8=9
SET @S9=SUBSTRING(@ID15,7,1)
SET @S10=SUBSTRING(@ID15,8,1)
SET @S11=SUBSTRING(@ID15,9,1)
SET @S12=SUBSTRING(@ID15,10,1)
SET @S13=SUBSTRING(@ID15,11,1)
SET @S14=SUBSTRING(@ID15,12,1)
SET @S15=SUBSTRING(@ID15,13,1)
SET @S16=SUBSTRING(@ID15,14,1)
SET @S17=SUBSTRING(@ID15,15,1)
SET @S18=((@S1*7)+(@S2*9)+(@S3*10)+(@S4*5)+(@S5*8)+(@S6*4)+(@S7*2)+(@S8*1)
+(@S9*6)+(@S10*3)+(@S11*7)+(@S12*9)+(@S13*10)+(@S14*5)+(@S15*8)+(@S16
*4)+(@S17*2))%11
SET @ID18=SUBSTRING(@ID15,1,6)+'19'+SUBSTRING(@ID15,7,9)
+CASE WHEN @S18=0 THEN '1'
WHEN @S18=1 THEN '0'
WHEN @S18=2 THEN 'X'
WHEN @S18=3 THEN '9'
WHEN @S18=4 THEN '8'
WHEN @S18=5 THEN '7'
WHEN @S18=6 THEN '6'
WHEN @S18=7 THEN '5'
WHEN @S18=8 THEN '4'
WHEN @S18=9 THEN '3'
WHEN @S18=10 THEN '2'
END
RETURN @ID18
END
（45）
Create FUNCTION [dbo].[f_digit_chn](@num bigint)
RETURNS varchar(20)
AS
BEGIN
--将整型数字转换为大写汉字的自定义函数,如123084转换为'壹贰叁零捌肆'
-- 调用 select dbo.[f_digit_chn](12345678)
DECLARE @result varchar(20),@symbol varchar(2)
IF @num<0
SELECT @symbol='负',@result='',@num=ABS(@num)
ELSE
SELECT @symbol='',@result=''
WHILE @num<>0
SELECT @result=SUBSTRING('零壹贰叁肆伍陆柒捌玖拾',@num%10+1,1)+@result,@num=@num/10
RETURN @symbol+@result
END
（46）
CREATE PROCEDURE Mypage
(
@metabname varchar(200), --表名
@mepagesize int, --每页显示记录数
@mepageint int, --分页条显示个数
@thispage int, --当前页编号
@msrsall int output, --总记录数
@mspagecount int output --总页数
)
AS
declare @tabsql nvarchar(200)
declare @pagecount int --分页总数
declare @rsall int --总记录数
declare @top int --提当前段数据
declare @sql varchar(200)
------------------------------------------------------------------------------------
set @tabsql='select @rsall =count(*) from '+ @metabname
exec sp_executesql @tabsql,N'@rsall int output',@rsall output
------------------------------------------------------------------------------------
if @rsall >0 --有数据
begin
set @top=@mepagesize*(@thispage-1)
set @sql='select top ' + str(@mepagesize) + ' * from '+@metabname+' where id not in(select top '+ str(@top) +' id from '+@metabname+') order by id asc'
exec(@sql)
if @rsall % @mepagesize=0
begin
set @pagecount=@rsall / @mepagesize
end
else
begin
set @pagecount=@rsall / @mepagesize+1
end
end
else --没数据
begin
set @msrsall=0
set @mspagecount=0
end
set @msrsall=@rsall --总信息数
set @mspagecount=@pagecount --分页总数
GO
（47）
存储过程如下：
CREATE PROC sp_PageView
@tbname sysname, --要分页显示的表名
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序
@Where nvarchar(1000)='', --查询条件
@PageCount int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END
--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END
--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @PageCount=COUNT(*)'
+N' FROM '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END
--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize
--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@FieldShow
+N' FROM '+@Where
+N' '+@TopN
+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@tbname
+N' '+@FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql,
'+@FieldShow
+N' FROM '+@TopN
+N' '+@tbname
+N' WHERE '+@sql
+N') '+@FieldOrder)
END
END
（48）
--行列转换示例--列变行
--测试数据
create table 表(id int,proc1 decimal(20,1),proc2 decimal(20,1),proc3 decimal(20,1))
insert 表 select 12,3.4,6.7,1.1
union all select 13,5.6,10.3,5.6
union all select 14,7.9,9.0,9.9
go
--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''select id,[proc]='''''+name+''''',value=['+name+'] from 表'''
,@s3=@s3+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('表')=id and name<>'id'
select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s3=substring(@s3,16,8000)
exec('declare '+@s1+'
select '+@s2+'
exec(''select * from(''+'+@s3+'+'')a order by id'')')
go
/*--测试结果
id proc value
----------- ----- ----------------------
12 proc1 3.4
12 proc2 6.7
12 proc3 1.1
13 proc3 5.6
13 proc2 10.3
13 proc1 5.6
14 proc1 7.9
14 proc2 9.0
14 proc3 9.9
（所影响的行数为 9 行）
--*/
（49）
--查所有加入域的人的信息
SELECT
sAMAccountname AS AccountName,
GivenName AS [First Name],
SN AS [Last Name],
mail AS [E-mail Address],
TelephoneNumber AS [Telephone Number],
l AS Office,
PostalAddress AS [Postal Address],
PostalCode,
StreetAddress AS [Street Address],
wWWHomePage AS [Web Page],
distinguishedname AS DN
FROM
OPENQUERY(
ADSI,
'SELECT GivenName, SN, company, department, TelephoneNumber, mail, distinguishedname, title, l, manager, mobile, name, PhysicalDeliveryOfficeName, PostalAddress, PostalCode, sAMAccountname, StreetAddress, wWWHomePage FROM ''LDAP://itg.net/ OU=itg ,DC=itg,DC=net'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' ORDER BY Name'
) Rowset_1
--where sAMAccountname ='clw' --查个人信息
--查部门信息
SELECT
*
FROM
OPENQUERY(
ADSI,
'SELECT GivenName, SN, company, department, TelephoneNumber, mail, distinguishedname, title, l, manager, mobile, name, PhysicalDeliveryOfficeName, PostalAddress, PostalCode, sAMAccountname, StreetAddress, wWWHomePage FROM ''LDAP://itg.net/ OU=itg ,DC=itg,DC=net'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' ORDER BY Name'
) Rowset_1
where distinguishedname like '%OU=财务部,%' --查IT部门的域信息
（50）
我来加一个，用代码实现在SqlServer2005上布置一个job
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_create_job] Script Date: 02/10/2009 19:44:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create proc [dbo].[sp_create_job]
as
BEGIN TRANSACTION
DECLARE @jobLogpath VARCHAR(1000)
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'FiveDayOptOutList')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''FiveDayOptOutList'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'FiveDayOptOutList'
SELECT @JobID = NULL
END
BEGIN
/*****************************************************************************************************/
DEClARE @ServerRole Varchar(20)
DECLARE @UserName Varchar(20)
DECLARE @Logfile_name Varchar(100)
-------------------------------------------------------------------------------------------------------
if object_id('msdb.dbo.temppopedom') is not null drop table msdb.dbo.temppopedom
-------------------------------------------------------------------------------------------------------
/***select all users information in SqLServer **/
select SrvRole = g.name, UserName = u.name, MemberSID = u.sid
into msdb.dbo.temppopedom
from sys.server_principals u, sys.server_principals g, sys.server_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
order by 1, 2
-------------------------------------------------------------------------------------------------------
/***select the user's name who using database currently **/
select @UserName = loginame from master.dbo.sysprocesses where uid=user_id()
-------------------------------------------------------------------------------------------------------
/***select the user's role who using database currently base on @UserName **/
select @ServerRole=SrvRole
from msdb.dbo.temppopedom
where Username=@UserName
-------------------------------------------------------------------------------------------------------
/*****************************************************************************************************/
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID OUTPUT ,
@job_name = N'FiveDayOptOutList',
@owner_login_name = @UserName,
@description = N'Execute package: FiveDayOptOutList',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
------------------------------------------------------------------------
IF @ServerRole = N'sysadmin'
BEGIN
-- SysAdmin role Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID,
@step_name = N'CREATE TABLE ',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id=0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority=0,
@subsystem = N'CmdExec',
@command=N'input you commond ',
@output_file_name = @jobLogpath,
@server = N'',
@database_user_name = N'',
@flags = 4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'FiveDayOptOutList',
@enabled=1,
@freq_type=8,
@freq_interval=2,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=1,
@freq_recurrence_factor=1,
@active_start_date=20070319,
@active_end_date=99991231,
@active_start_time=230000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
--EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
--IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
ELSE
---non-SysAdmin Role add job
----------------------------------
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID,
@step_name = N'CREATE TABLE ',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id=0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority=0,
@subsystem = N'CmdExec',
@command=N'input you commond ',
@server = N'',
@flags = 16
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'FiveDayOptOutList',
@enabled=1,
@freq_type=8,
@freq_interval=2,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=1,
@freq_recurrence_factor=1,
@active_start_date=20070319,
@active_end_date=99991231,
@active_start_time=230000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
-----------------------
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
（51）
c++截取字符串前后指定字符的函数
C/C++ code
TCHAR *TrimLeft(TCHAR *str, TCHAR *sep = _T(" \t\n"));
TCHAR *Trim(TCHAR *str, TCHAR *sep = _T(" \t\n"));
TCHAR *TrimRight(TCHAR *str, TCHAR *sep = _T(" \t\n"));
TCHAR *Trim(TCHAR *str, TCHAR *sep)
{
return TrimLeft(TrimRight(str, sep), sep);
}
TCHAR *TrimLeft(TCHAR *str, TCHAR *sep)
{
TCHAR *pstr = str;
for (; *pstr != 0; pstr++)
{
TCHAR *p = sep;
while (*p != 0 && *p != *pstr) p++;
if (*p == 0) break;
}
if (pstr > str) _tcscpy(str, pstr);
return str;
}
TCHAR *TrimRight(TCHAR *str, TCHAR *sep)
{
TCHAR *pstr = str + _tcslen(str) - 1;
for (; pstr >= str; pstr--)
{
TCHAR *p = sep;
while (*p != 0 && *p != *pstr) p++;
if (*p == 0) break;
}
pstr[1] = 0;
return str;
}
（52）
sql中的循环:
declare @index int
select @index=1
while @index<=1000
begin
insert into st1 (学号,姓名,性别,出生时间,学分,备注)
values (1000+@index,'小王',1,'1989-02-22',70,'无')
set @index=@index+1
end
经典！！！！
（53）
随机查询语句，随机返回表中的n条记录
SQL Server：
Select TOP N * From TABLE Order By NewID()
Access：
Select TOP N * From TABLE Order By Rnd(ID)
Rnd(ID) 其中的ID是自动编号字段，可以利用其他任何数值来完成，比如用姓名字段(UserName)
Select TOP N * From TABLE Order BY Rnd(Len(UserName))
MySql：
Select * From TABLE Order By Rand() Limit 10
（54）
看一段 邹老大的实战代码——乘车模型
SQL code
CREATE TABLE T_Line(
ID nvarchar(10), -- 公交线路号
Station nvarchar(10), -- 站点名称
Orders int) -- 行车方向(通过它反应每个站的上一个、下一个站)
INSERT T_Line
SELECT N'8路' ,N'站A', 1 UNION ALL
SELECT N'8路' ,N'站B', 2 UNION ALL
SELECT N'8路' ,N'站C', 3 UNION ALL
SELECT N'8路' ,N'站D', 4 UNION ALL
SELECT N'8路' ,N'站J', 5 UNION ALL
SELECT N'8路' ,N'站L', 6 UNION ALL
SELECT N'8路' ,N'站M', 7 UNION ALL
SELECT N'20路' ,N'站G', 1 UNION ALL
SELECT N'20路' ,N'站H', 2 UNION ALL
SELECT N'20路' ,N'站I', 3 UNION ALL
SELECT N'20路' ,N'站J', 4 UNION ALL
SELECT N'20路' ,N'站L', 5 UNION ALL
SELECT N'20路' ,N'站M', 6 UNION ALL
SELECT N'255路',N'站N', 1 UNION ALL
SELECT N'255路',N'站O', 2 UNION ALL
SELECT N'255路',N'站P', 3 UNION ALL
SELECT N'255路',N'站Q', 4 UNION ALL
SELECT N'255路',N'站J', 5 UNION ALL
SELECT N'255路',N'站D', 6 UNION ALL
SELECT N'255路',N'站E', 7 UNION ALL
SELECT N'255路',N'站F', 8
GO
-- 乘车线路查询存储过程
CREATE PROC dbo.p_qry
@Station_Start nvarchar(10),
@Station_Stop nvarchar(10)
AS
SET NOCOUNT ON
DECLARE
@l int
SET @l = 0
-- a. 从开始站点查询可能的乘车线路
SELECT
ID, Station,
Line = CONVERT(nvarchar(4000), '(' + RTRIM(ID) + ': ' + RTRIM(Station)),
Orders = Orders,
[Level] = @l
INTO #
FROM T_Line
WHERE Station = @Station_Start
-- 循环查找所有可能线路, 直到找不到可用线路或者找到达到终止站点的乘车线路
WHILE @@ROWCOUNT > 0
AND NOT EXISTS(
SELECT * FROM #
WHERE Station = @Station_Stop)
BEGIN
SET @l = @l + 1
INSERT #(
Line, ID, Station, Orders, [Level])
SELECT
Line = A.Line
+ CASE
WHEN A.ID = B.ID THEN N'->' + RTRIM(B.Station)
ELSE N') ∝ (' + RTRIM(B.ID) + N': ' + RTRIM(B.Station)
END,
B.ID, B.Station, B.Orders, @l
FROM # A, T_Line B
WHERE A.[Level] = @l - 1
AND(
(A.Station = B.Station AND A.ID <> B.ID) -- 另一条统一口径
OR (
A.ID = B.ID -- 同一站点的前一站或者下一站
AND(
A.Orders = B.Orders + 1
OR
A.Orders = B.Orders - 1)
)
)
AND LEN(A.Line) < 4000
-- 避免查找已经找过的线路
AND PATINDEX('%[ >]' + B.Station + '[-)]%', A.Line) = 0
END
-- 显示结果
SELECT
N'起点站' = @Station_Start,
N'终点站' = @Station_Stop,
N'乘车线路' = Line+N')'
FROM #
WHERE [Level] = @l
AND Station = @Station_Stop
IF @@ROWCOUNT = 0 --如果未有可以到达的线路,则显示处理结果表备查
SELECT * FROM #
GO
（55）
保留名次：
Select *, Rank=(select count(distinct(销量)) from table1 where 销量>=A.销量) From table1 A
效果
1
2
2
2
3
4（记录数一共为6条）
不保留名次：
select *, rank=(select count(1) from table1 where 销量>A.销量)+1 from table1 A
效果：
1
2
2
2
5
6（记录数一共为6条）
/******************************************************/
select isnull((select sum(1) from t where 成绩>A.成绩),0)+1 as '名次',* from t A
order by 成绩 desc
1、
Select *,IsNull((Select sum(1) from t1 where 总分>a.总分),0)+1 as 名次
from t1 a
order by (Select sum(1) from t1 where 总分>=a.总分)
id name 总分 名次
----------- ---------- ----------- -----------
1 jksfff 270 1
5 sfjskj 270 1
3 skfjsk 249 3
4 sfjskj 249 3
2 loskfi 238 5
（所影响的行数为 5 行）
2、
Select *,IsNull((Select count(distinct 总分) from t1 where 总分>a.总分),0)+1 as 名次
from t1 a
order by (Select sum(1) from t1 where 总分>=a.总分)
id name 总分 名次
----------- ---------- ----------- -----------
1 jksfff 270 1
5 sfjskj 270 1
3 skfjsk 249 2
4 sfjskj 249 2
2 loskfi 238 3
（所影响的行数为 5 行）
* select *,名次=(select count(distinct (EstimateMoney-OutMoney)) from ##fff
where (EstimateMoney-OutMoney)>=(a.EstimateMoney-a.OutMoney) and Depart=a.Depart)
from ##fff a order by Depart,mc asc
（56）
SQL codeDECLARE @dt datetime
SET @dt=GETDATE()
DECLARE @number int
SET @number=3
--1．指定日期该年的第一天或最后一天
--A. 年的第一天
SELECT CONVERT(char(5),@dt,120)+'1-1'
--B. 年的最后一天
SELECT CONVERT(char(5),@dt,120)+'12-31'
--2．指定日期所在季度的第一天或最后一天
--A. 季度的第一天
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt)-2,
@dt),
120)+'1')
--B. 季度的最后一天（CASE判断法）
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)
+CASE WHEN DATEPART(Quarter,@dt) in(1,4)
THEN '31'ELSE '30' END)
--C. 季度的最后一天（直接推算法）
SELECT DATEADD(Day,-1,
CONVERT(char(8),
DATEADD(Month,
1+DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)+'1')
--3．指定日期所在月份的第一天或最后一天
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')
--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')
--C. 月的最后一天（容易使用的错误方法）
SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))
--4．指定日期所在周的任意一天
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
--5．指定日期所在周的任意星期几
--A. 星期天做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)
--B. 星期一做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
1.一个月第一天的
Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
2.本周的星期一
Select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
3.一年的第一天
Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
4.季度的第一天
Select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
5.当天的半夜
Select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
6.上个月的最后一天
Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
7.去年的最后一天
Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
8.本月的最后一天
Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
9.本年的最后一天
Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
10.本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
返回当前日期和时间
通过函数GETDATE（），你可以获得当前的日期和时间。函数GETDATE（）可以用来作为DATEDIME型字段的缺省值。这对插入记录时保存当时的时间是有用的。要建立一个表，其中的记录包含有当前的日期和时间，可以添加一个DATETIME型字段，指定其缺省值为函数GETDATE（）的返回值，就象这样：
Create TABLE site_log (
username VARCHAR(40)，
useractivity VARCHAR(100)，
entrydate DATETIME DEFAULT GETDATE())
转换日期和时间
函数GETDATE（）的返回值在显示时只显示到秒。实际上，SQL Sever内部时间可以精确到毫秒级（确切地说，可以精确到3.33毫秒）。
要得到不同格式的日期和时间，你需要使用函数CONVERT（）。例如，当下面的这个语句执行时，显示的时间将包括毫秒：
-----------------------------------------------------------------------------
1.显示本月第一天
SELECT DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
select convert(datetime,convert(varchar(8),getdate(),
120)+'01',120)
2.显示本月最后一天
select dateadd(day,-1,convert(datetime,convert
(varchar(8),dateadd(month,1,getdate()),120)+'01',120))
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
3.上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
4.本月的第一个星期一i
select DATEADD(wk,DATEDIFF(wk,0, dateadd(dd,6-datepart(
day,getdate()),getdate())),0)
5.本年的第一天
SELECT DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
6.本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))
7.去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
8.本季度的第一天
SELECT DATEADD(qq,DATEDIFF(qq,0,getdate()),0)
9.本周的星期一
SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0)
10.查询本月的记录
select * from tableName where DATEPART(mm, theDate)
=DATEPART(mm, GETDATE()) and DATEPART(yy, theDate)
= DATEPART(yy, GETDATE())
11.查询本周的记录
select * from tableName where DATEPART(wk, theDate) = DATEPART
(wk, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
12查询本季的记录 注：其中:GETDATE()是获得系统时间的函数。
select * from tableName where DATEPART(qq, theDate) = DATEPART
(qq, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
13.获取当月总天数:
select DATEDIFF(dd,getdate(),DATEADD
(mm, 1, getdate()))
select datediff(day,
dateadd(mm, datediff(mm,'',getdate()), ''),
dateadd(mm, datediff(mm,'',getdate()), '1900-02-01'))
-------------------------------------
Declare @dt datetime
Set @dt=GETDATE()
SELECT 32-day(@dt+(32-Day(@dt)))
SELECT 32-DAY(getdate()+32-DAY(getdate()))
14.获取当前为星期几
DATENAME(weekday, getdate())
Select CONVERT(VARCHAR(30)，GETDATE()，9)
----------------------------------------------------------------------------
sql语句获取本周、本月数据
本周：select * from table where datediff(week,C_CALLTIME,getdate())=0 --C_CALLTIME 为日期字段
本月：select * from table where datediff(Month,C_CALLTIME,getdate())=0 --C_CALLTIME 为日期字段
本季：select * from table where datediff(qq,C_CALLTIME,getdate())=0
前半年1-6，后半年7-12：select * from table where datepart(mm,C_CALLTIME)/7 = datepart(mm,getdate())/7
（57）
问题描述：
无论是在sql 2000， 还是在 sql 2005 中，都没有提供字符串的聚合函数， 所以， 当我们在处理下列要求时，会比较麻烦：
有表tb, 如下：
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果：
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即， group by id, 求 value 的和（字符串相加）
1. 旧的解决方法
-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value
FROM tb
WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数
SELECt id, values=dbo.f_str(id)
FROM tb
GROUP BY id
-- 2. 新的解决方法
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
-- 查询处理
SELECT *
FROM(
SELECT DISTINCT
id
FROM @t
)A
OUTER APPLY(
SELECT
[values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
/*--结果
id values
----------- ----------------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
--*/
（58）http://topic.csdn.net/u/20090310/17/0b8f60c5-9304-4633-84fa-d176f282cb7d.html
【SQL Server 数据库导入导出部分】
1、在查询分析器下查询Excel文档
SELECT * FROM
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\测试.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
2、从数据库中导出数据并存到文件中
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.xls -c -q -S"." -U"sa" -P""'
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.txt -c -q -S"." -U"sa" -P""'
3、从文件中导入数据到数据库对应表中
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN in c:\temp1.xls -c -q -S"." -U"sa" -P""'
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN in c:\temp1.txt -c -q -S"." -U"sa" -P""'
（59）
【SQL SERVER 数据库实用SQL语句】
1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.分页SQL语句
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from 表名 As tab) As t where rownum between 起始位置 And 结束位置
3.获取当前数据库中的所有用户表
select * from sysobjects where xtype='U' and category=0
4.获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')
5.查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
6.查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
7.查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
8.查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
9.使用事务
在使用一些对数据库表的临时的SQL语句操作时，可以采用SQL SERVER事务处理，防止对数据操作后发现误操作问题
开始事务
Begin tran
Insert Into TableName Values(…)
SQL语句操作不正常，则回滚事务。
回滚事务
Rollback tran
SQL语句操作正常，则提交事务，数据提交至数据库。
提交事务
Commit tran
10. 按全文匹配方式查询
字段名 LIKE N'%[^a-zA-Z0-9]China[^a-zA-Z0-9]%'
OR 字段名 LIKE N'%[^a-zA-Z0-9]China'
OR 字段名 LIKE N'China[^a-zA-Z0-9]%'
OR 字段名 LIKE N'China
11．计算执行SQL语句查询时间
declare @d datetime
set @d=getdate()
select * from SYS_ColumnProperties select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
12、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
（60）
【关闭SQL Server 数据库所有使用连接】
使用存储过程终止：
在查询分析器下创建终止数据库所有接连的存储过程，通过调用该存储过程可以关闭所有使用该数据库的连接操作。
--创建终止使用数据库下所有进程的存储过程，参数为数据库名称
use master
go
create proc KillSpByDbName(@dbname varchar(20))
as
begin
declare @sql nvarchar(500),@temp varchar(1000)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status<>-1
begin
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end
--举例使用，关闭数据库下的所有连接操作
Use master
Exec KillSpByDbName '数据库名称'
（61）
Transact-SQL语句进行导入导出：1.在SQL SERVER里查询access数据:-- ======================================================SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名-------------------------------------------------------------------------------------------------2.将access导入SQL server-- ======================================================在SQL SERVER 里运行:
SELECT *
INTO newtableFROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名-------------------------------------------------------------------------------------------------3.将SQL SERVER表里的数据插入到Access表中-- ======================================================在SQL SERVER 里运行：
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名(列名1,列名2)
select 列名1,列名2 from sql表
实例：
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\db.mdb';'admin';'', Test)
select id,name from Test
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', 表名)
SELECT *
FROM sqltablename-------------------------------------------------------------------------------------------------二、SQL SERVER 和EXCEL的数据导入导出
1、在SQL SERVER里查询Excel数据:-- ======================================================SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions-------------------------------------------------------------------------------------------------2、将Excel的数据导入SQL server :-- ======================================================SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions-------------------------------------------------------------------------------------------------3、将SQL SERVER中查询到的数据导成一个Excel文件-- ======================================================T-SQL代码：EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数：S 是SQL服务器名；U是用户；P是密码
说明：还可以导出文本文件等多种格式
实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中应用ADO导出EXCEL文件代码：
Dim cn As New ADODB.Connectioncn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
------------------------------------------------------------------------------------------------4、在SQL SERVER里往Excel插入数据:-- ======================================================insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)
T-SQL代码：INSERT INTO
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]
(bestand, produkt) VALUES (20, 'Test')
-------------------------------------------------------------------------------------------------总结：利用以上语句，我们可以方便地将SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换，为我们提供了极大方便！
EXEC master..xp_cmdshell 'bcp "select OrderID,ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Northwind.dbo.orders" queryout "d:\Oreders.txt" -t"|" -c -q -S"127.0.0.1" -U"sa" -P""'
SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="xxxx.xls";Extended Properties="Excel 8.0";IMEX=1;Persist Security Info=False')...[a1$]
（62）
1. 分页查询数据
--查询到行的结果
select * from(
select ID,DocClassName,DocClassDesc, ROW_NUMBER() OVER(order by ID) as row from Sys_DocClass
) a
where row between 20 and 30
select * from Sys_DocClass
2、增加异常处理 try ... catch
SET XACT_ABORT ON -- 打开try功能
BEGIN TRY
begin tran
insert into Sys_DocClass values(...) --数据表操作语句
commit tran --提交事务
print 'commited'
END TRY
BEGIN CATCH
rollback tran --回滚事务
print 'rolled back'
END CATCH
3、通用表达式CTE，可以简化嵌套SQL
--例：结合通用表达式进行分页
WITH DocClasses AS(
select ID,DocClassName,DocClassDesc, ROW_NUMBER() OVER(order by ID) as row from Sys_DocClass
)
select ID,DocClassName,DocClassDesc from DocClasses where row between 20 and 30
（63）
--设置 min server memory 配置项
EXEC sp_configure N'min server memory (MB)', 0
--设置 max server memory 配置项
EXEC sp_configure N'max server memory (MB)', 256
--使更新生效
RECONFIGURE WITH OVERRIDE
/*--功能说明
下面的代码是在SQL Server 2000上创建名为 MyDB 的数据库
该数据库包括1个主要数据文件、3个用户定义的文件组和1个日志文件
ALTER DATABASE语句将用户定义文件组指定为默认文件组。
之后，通过指默认的文件组来创建表，并且将图像数据和索引放到指定的文件组中。
最后，将文件组中的指定数据文件删除
--*/
--切换到 master 数据库
USE master
GO
--A. 创建数据库 MyDB
CREATE DATABASE MyDB
ON PRIMARY --主文件组和主要数据文件
( NAME='MyDB_Primary',
FILENAME= 'c:\MyDB_Prm.mdf'),
FILEGROUP MyDB_FG1 --用户定义文件组1
( NAME = 'MyDB_FG1_Dat1',
FILENAME = 'c:\MyDB_FG1_1.ndf'), --次要数据文件1
( NAME = 'MyDB_FG1_Dat2',
FILENAME = 'd:\MyDB_FG1_2.ndf'), --次要数据文件2
FILEGROUP MyDB_FG2 --用户定义文件组2
( NAME = 'MyDB_FG1_Dat',
FILENAME = 'e:\MyDB_FG2.ndf') --次要数据文件
LOG ON --日志文件
( NAME='MyDB_log',
FILENAME ='d:\MyDB.ldf')
GO
--B. 修改默认数据文件组
ALTER DATABASE MyDB MODIFY FILEGROUP MyDB_FG1 DEFAULT
GO
--切换到新建的数据库 MyDB
USE MyDB
--C. 在默认文件组MyDB_FG1创建表,并且指定图像数据保存在用户定义文件组MMyDB_FG2
CREATE TABLE MyTable
( cola int PRIMARY KEY ,
colb char(8) ,
colc image )
TEXTIMAGE_ON MyDB_FG2
--在用户定义文件组MyDB_FG2上创建索引
CREATE INDEX IX_MyTable ON MyTable(cola) ON MyDB_FG2
GO
--D. 将要删除数据文件MyDB_FG1_Dat1上的数据转移到其他数据文件中，并且清空数据文件MyDB_FG1_Dat1
DBCC SHRINKFILE(MyDB_FG1_Dat1,EMPTYFILE)
--删除数据文件MyDB_FG1_Dat1
ALTER DATABASE MyDB REMOVE FILE MyDB_FG1_Dat1
--A. 将tempdb数据库的主数据文件大小设置为10MB。
ALTER DATABASE tempdb
MODIFY FILE(
name=tempdev,
size=100 MB)
GO
--B. 将tempdb数据库的主数据文件移动到指定的磁盘分区上，并且为其添加一个数据文件。
--移动主数据文件
ALTER DATABASE tempdb MODIFY FILE
( NAME='tempdev',
FILENAME ='d:\tempdb.mdf')
--添加次要数据文件
ALTER DATABASE tempdb ADD FILE
( NAME='tempdata_1',
FILENAME ='d:\tempdb_data_1.ndf')
（64）
1.sql 导出到 access
select *
into goods
from [ODBC;Driver={SQL Server};Server=.;Database=kpsj;Uid=sa;Pwd=123456].goods
2.sql从access导入
insert into [ODBC;Driver={SQL Server};Server=.;Database=kpsj;Uid=sa;Pwd=123456].goods
values('kk')
需要在Access中执行。
（65）
【SQL Server 数据库常用函数使用】
总结Sql Server中提供的内置常用函数，方便在以后的数据库开发使用。
一、字符串函数
1、长度与分析用
--函数：datalength(Char_expr)
--功能：返回字符串长数
select datalength('China') --结果：5
--函数：substring(expression,start,length)
--功能：截取字符串
select substring('China',1,2) --结果：Ch
--函数：right(char_expr,int_expr)
--功能：返回字符串右边的第int_expr个字符
select right('China',2) --结果：na
--函数：upper(char_expr)
--功能：将字符串转化为大写
select upper('China') --结果：CHINA
--函数：lower(char_expr)
--功能：将字符串转化为小写
select lower('China') --结果：china
--函数：space(int_expr)
--功能：生成int_expr个空格
select space(5) --结果：
--函数：replicate(char_expr,int_expr)
--功能：复制字符串
select replicate('China',2) --结果： ChinaChina
--函数：reverse(char_expr)
--功能：反转字符串
select reverse('China') --结果：anihC
--函数：stuff(char_expr1,start,length,char_expr2)
--功能：替换字符串
select stuff('China',2,3,'A') --结果：CAa
--函数：ltrim(char_expr)
--功能：去除左空格
select ltrim(' China') --结果：China
--函数：rtrim(char_expr)
--功能：去除右空格
select rtrim('China ') --结果：China
--函数：ascii(char_expr)
--功能：获取字符对应Ascii码
select ascii('A') --结果：65
--函数：rtrim(char_expr)
--功能：获取Ascii码对应字符
select char(65) --结果：A
字符串查找
--函数：charindex(char_expr,expression)
--功能：返回char_expr的起始位置
注释：返回字符串char_expr在字符串expression中的起始位置，如不存在返回0.
select charindex('s','China') --结果： 0
select charindex('C','China',2) --结果：1
select charindex('hin','China') --结果：2
select charindex('a','China') --结果：5
--函数：patindex("%pattern%",expression)
--功能：返回指定模式的起始位置
select PATINDEX('App%','Application') --结果：1
判断一个字符串是否包含字母A和Z，还有任何数字
select PATINDEX('%[A,Z,0-9]%[A,Z,0-9]%[A,Z,0-9]%','XYZABC123')
--结果：3
二、数学函数
--函数：abs(numeric_expr)
--功能：求绝对值
select abs(-1) --结果：1
--函数：ceiling(numeric_expr)
--功能：取大于等于指定值的最小整数
select ceiling(-84.24) --结果：-84
select ceiling(84.24) --结果：85
--函数：floor(numeric_expr)
--功能：小于等于指定值得最大整数
select floor(84.24) --结果：84
select floor(-84.24) --结果：-85
--函数：pi()
--功能：返回圆周率
select pi() --结果：3.14159265358979
--函数：power(numeric_expr,power)
--功能：返回power次方
select power(2,4) --结果：16
--函数：round(numeric_expr,int_expr)
--功能：按int_expr规定的精度四舍五入
select round(6.78,1) --结果：6.80
--函数：sign(int_expr)
--功能：根据正数,0,负数,,返回+1,0,-1
select sign(1) --结果：1
--函数：sqrt(float_expr)
--功能：求平方根
select sqrt(2) --结果：1.4142135623731
三、日期函数
--函数：getdate()
--功能：返回当前日期
select getdate() --结果：2009-03-12 15:46:29.077
--函数：datediff(datepart,date_expr1.dateexpr2)
--功能：日期差
select datediff(day,'1980-03-05',getdate()) --结果：
--函数：dateadd(datepart,number,date_expr)
--功能：返回日期加上number
select dateadd(year,29,'1983-03-05') --结果：2009-03-05 00:00:00.000
四、系统函数
--函数：suser_name()
--功能：用户登录名
select suser_name()
--函数：user_name()
--功能：用户在数据库中的名字
select user_name() --结果：dbo
--函数：user
--功能：用户在数据库中的名字
select user --结果：dbo
--函数：db_name()
--功能：数据库名
select db_name() --结果：数据库名
（66）
查询数据库中所有的用户表的记录数以及其他
ALTER proc [dbo].[p_spaceused](@num int)
as
declare @temptable table(name varchar(200),rows varchar(20),reserved varchar(20),
data varchar(20),index_size varchar(20),unused varchar(20)
)
declare @tablename varchar(200)
declare @sqlStr nvarchar(200)
declare tablelist cursor for select name from sysobjects where xtype='u'
open tablelist
fetch next from tablelist into @tablename
while(@@fetch_status=0)
begin
set @sqlStr='sp_spaceused '+ @tablename
insert into @temptable exec sp_executesql @sqlStr
fetch next from tablelist into @tablename
end
close tablelist
deallocate tablelist
if exists(select 1 from sysobjects where id=object_id('XX'))
begin
insert into XX(creat_tm,name,rows,reserved,data,index_size,unused)
select getdate() 'creat_tm',a.* from @temptable a
end
else
select getdate() 'creat_tm ',* into XX from @temptable
select * from XX where rows >@num order by convert(int,rows) desc
drop table XX
（67）
sql 拼音查询
create function f_GetPy
(@str nvarchar(4000))
returns nvarchar(4000)
as
begin declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter) select '吖 ', 'A ' union all select '八 ', 'B ' union all select '嚓 ', 'C '
union all select '咑 ', 'D ' union all select '妸 ', 'E ' union all select '发 ', 'F '
union all select '旮 ', 'G ' union all select '铪 ', 'H ' union all select '丌 ', 'J '
union all select '咔 ', 'K ' union all select '垃 ', 'L ' union all select '嘸 ', 'M ' union all select '拏 ', 'N '
union all select '噢 ', 'O ' union all select '妑 ', 'P ' union all select '七 ', 'Q ' union all select '呥 ', 'R '
union all select '仨 ', 'S ' union all select '他 ', 'T ' union all select '屲 ', 'W ' union all select '夕 ', 'X '
union all select '丫 ', 'Y ' union all select '帀 ', 'Z '
select @strlen=len(@str),@re= ' '
while @strlen> 0 begin select top 1 @re=letter+@re,@strlen=@strlen-1 from @t a
where chr <=substring(@str,@strlen,1)
order by chr desc if @@rowcount=0 select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 end return(@re)
end
--样例
if object_id('[pactinfo]') is not null
drop table [pactinfo]
go
create table [pactinfo]([ID] int,[pactname] varchar(4))
insert [pactinfo] select 1,'正常' union all select 2,'中国' union all select 3,'做饭'
union all select 4,'加发'
---查询---
select * from [pactinfo] where left(dbo.f_GetPy(pactname),1)='Z'
select * from pactinfo where dbo.f_GetPy(pactname) like '%Z%'
--结果--- ID pactname -----------
- 1 正常 2 中国 3 做饭 （所影响的行数为 3 行）
drop table [pactinfo]
drop function f_GetPy
- SQL经典短小代码收集
- SQL经典短小代码收集
- SQL经典短小代码收集
- SQL经典短小代码收集1
- 经典短小代码收集（sql）
- 经典SQL短小代码收集汇总
- SQL经典短小代码收集 1
- SQL经典短小代码收集 2
- 数据库SQL经典短小代码收集
- 数据库SQL经典短小代码收集
- 数据库SQL经典短小代码收集
- 经典短小代码收集
- 经典短小代码收集
- 经典短小代码收集
- 经典短小代码收集
- 经典短小代码收集
- 经典MSSQL短小代码收集----第一季
- 经典sql短小语句
- perl 获取文件内容里第一个AAA和最后一个AAA
- 采集视频并写入文件
- 在命令行调用Junit测试类
- [Array]Rotate Array
- Codeforces Round #361 (Div. 2) C. Mike and Chocolate Thieves
- SQL经典短小代码收集
- 字符串匹配——Sunday算法
- Css3 - Animation Examples
- iOS-CoreGraphics学习（彩色图片转灰白图片）
- 经典电影
- Apache与Tomcat有什么关系和区别
- Universal-Image-Loader使用小记
- 本科毕业生活感悟
- 基于DotSpatial实现面积测量图层
