1:
SQL code create table tz2008_1_1(id int,name varchar(50))
insert into tz2008_1_1 select 1,'a'
create table tz2008_1_2(id int,name varchar(50))
insert into tz2008_1_2 select 2,'b'
create table tz2008_1_3(id int,name varchar(50))
insert into tz2008_1_3 select 3,'c'
declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select * from ['+name+']'
from sysobjects where xtype='u' and name like 'tz2008%'
exec(@sql)
模糊表名的联合查询..
2:
SQL code --查询表的默认值
if object_id('tb') is not null
drop table tb
go
create table tb(id int,name varchar(50) default 'abc',num int default 5)
insert into tb(id) select 1
insert into tb select 1,'oo',100
insert into tb(id,name) select 1,'oo'
go
declare @tbname varchar(50)
set @tbname='tb'--表名
select @tbname as tbname,c.name as colname,replace(replace(replace(replace(b.[text],'(''',''),''')',''),'((',''),'))','') as defaultvalue
from sysconstraints a join syscomments b on a.constid=b.id
join syscolumns c on a.id=c.id and a.colid=c.colid
where a.id=object_id(@tbname) and object_name(a.constid) like '%df%'3:
SQL code --存储过程语句查询
if object_id('proc_ttt') is not null
drop proc proc_ttt
go
create proc proc_ttt
as
select 1 union select 2
go
select [text] from syscomments
where id=object_id('proc_ttt')
EXEC SP_HELPTEXT 'proc_ttt'4:
SQL code --通过身份证获得户籍
create function f_getcityfromcid (@cid varchar(18))
returns varchar(50)
as
begin
declare @acity varchar(1000)
set @acity = '____,____,____,____,____,____,____,____,____,____,____,北京__,天津__,河北__,山西__,内蒙古_,____,____,____,____,____,辽宁__,吉林__,黑龙江_,____,____,____,____,____,____,____,上海__,江苏__,浙江__,安微__,福建__,江西__,山东__,____,____,____,河南__,湖北__,湖南__,广东__,广西__,海南__,____,____,____,重庆__,四川__,贵州__,云南__,**__,____,____,____,____,____,____,陕西__,甘肃__,青海__,宁夏__,新疆__,____,____,____,____,____,台湾__,____,____,____,____,____,____,____,____,____,香港__,澳门__,____,____,____,____,____,____,____,____,国外__,'
set @cid = upper(@cid)
IF (len(@cid) <> 18 OR patindex('%[^0-9X]%',@cid) > 0)
RETURN '你小子骗我，这不是合法的身份证'
IF substring(@acity,cast(left(@cid,2) as int)* 5+1,4) = ''
RETURN '你小子骗我，这身份证的地区码不存在'
RETURN '这小子是：'+replace(substring(@acity,cast(left(@cid,2) as int)* 5+1,4),'_','')
end
go
select dbo.f_getcityfromcid('32108519760502ttt9')
/*
--------------------------------------------------
你小子骗我，这不是合法的身份证
（所影响的行数为 1 行）
*/
select dbo.f_getcityfromcid('32108519****026**9')
/*
--------------------------------------------------
这小子是：江苏
（所影响的行数为 1 行）
*/
drop function f_getcityfromcid
5:
SQL code --随机选择一个小于等于500的组合
declare @tb table(id int,num int)
insert into @tb select 1,1000
insert into @tb select 2,100
insert into @tb select 3,500
insert into @tb select 4,200
insert into @tb select 5,200
insert into @tb select 6,50
insert into @tb select 7,150
insert into @tb select 8,80
insert into @tb select 9,70
declare @idtb table(id int)
declare @num int,@id int,@sum int
set @sum=0
while @sum<>500
begin
select top 1 @id=id,@num=num from @tb where num<=500 order by newid()
if @num=500
insert into @idtb select @id
else
if not exists(select 1 from @idtb where id=@id)
insert into @idtb select @id
select @sum=sum(num) from @tb where id in(select id from @idtb)
if(@sum>500)
begin
delete @idtb
end
end
select * from @tb where id in(
select id from @idtb)6:
SQL code --随机选择一个小于等于500的组合
declare @tb table(id int,num int)
insert into @tb select 1,1000
insert into @tb select 2,100
insert into @tb select 3,500
insert into @tb select 4,200
insert into @tb select 5,200
insert into @tb select 6,50
insert into @tb select 7,150
insert into @tb select 8,80
insert into @tb select 9,70
declare @idtb table(id int)
declare @num int,@id int,@sum int
set @sum=0
while @sum<>500
begin
select top 1 @id=id,@num=num from @tb where num<=500 order by newid()
if @num=500
insert into @idtb select @id
else
if not exists(select 1 from @idtb where id=@id)
insert into @idtb select @id
select @sum=sum(num) from @tb where id in(select id from @idtb)
if(@sum>500)
begin
delete @idtb
end
end
select * from @tb where id in(
select id from @idtb)7:
SQL code /*
标题：普通行列转换(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
------------------
------------------
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 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 tb8:
SQL code --按某一字段分组取最大(小)值所在行的数据
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 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 by a.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 by a.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()) order by 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 by a.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 by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 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 by a.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) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 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 行受影响)
*/9:
合并列值
原著：邹建
改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 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
10:
分拆列值
原著：邹建
改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 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 行受影响)
*/
11:
SQL code 日期的推算:(转邹老大的代码)
DECLARE @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)
12:
SQL code
/*
标题：分解字符串并查询相关数据
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-18
地点：广东深圳
说明：通过使用函数等方法分解字符串查询相关数据。
问题：通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。
例如 @str = '1,2,3',查询下表得到记录1,4,5,6
ID TypeID
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
6 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,12
2 2,3
4 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,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
（所影响的行数为 5 行）
*/
---------------------------------------
--如果超过四个，则只能使用函数或动态SQL来分解并查询数据。
/*
名称：fn_split函数.
功能：实现字符串分隔功能的函数
*/
create function dbo.fn_split(@inputstr varchar(8000), @seprator varchar(10))
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator , @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr , @i - 1))
set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
set @i = charindex(@seprator , @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
--调用
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,12
2 2,3
3 3,7,8,9
4 2,6
5 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,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
13:
SQL code
/* 本文由微软新闻组摘录下来的。一段非常有用的脚本。
如果碰到日志文件过大的问题，用SHIRNK DATABASE， TRUNCATE LOG FILE不是很有效时，可以考虑试下下面的脚本。把代码COPY到查询分析器里，然后修改其中的3个参数(数据库名，日志文件名，和目标日志文件的大小)，运行即可
*/
----------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @LogicalFileName sysname, --日志文件名
@MaxMinutes INT, --允许此脚本执行的最长时间
@NewSize INT --目标日志文件的大小
USE CRM -- 要操作的数据库名
SELECT @LogicalFileName = 'CRM_LOG', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 想要收缩到的目标大小(单位M),此处标记收缩到1M
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =
@LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
14:
SQL code
--递归删除父节点及所有子节点
create table tb(Id int, ParentId int, Name varchar(5))
insert into tb select 1, 0, 'a1'
union all select 2,2, 'a2'
union all select 14, 1, 'b11'
union all select 15, 1, 'b12'
union all select 16, 14, 'c13'
union all select 17, 14, 'c14'
union all select 104,17,'d15'
go
WITH temptab(id, parentid, name) AS
( SELECT root.id, root.parentid, root.name
FROM tb root
WHERE id=1
UNION ALL
SELECT sub.id, sub.parentid, sub.name
FROM tb sub, temptab super
WHERE sub.parentid = super.id
)
delete from tb where id in(
select id from temptab
)
select * from tb
go
drop table tb
/*
Id ParentId Name
----------- ----------- -----
15:
精妙SQL语句
明：复制表(只复制结构,源表名：a 新表名：b)
SQL: select * into b from a where 1 <>1
说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
SQL: insert into b(a, b, c) select d,e,f from b;
说明：显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
说明：外连接查询(表名1：a 表名2：b)
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
说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff( <|>minute <|>,f开始时间,getdate())>5
说明：两张关联表，删除主表中已经在副表中没有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
说明：--
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE, <|>YYYY/MM <|>) = TO_CHAR(SYSDATE, <|>YYYY/MM <|>)) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE, <|>YYYY/MM <|>) =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, <|>YYYY/MM <|>) ||
<|>/01 <|>, <|>YYYY/MM/DD <|>) - 1,
<|>YYYY/MM <|>) ) Y,
WHERE X.NUM = Y.NUM （+）
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
WHERE A.NUM = B.NUM
说明：--
SQL:
select * from studentinfo where not exists(select * from student where
studentinfo.id=student.id) and 系名称=
<|>"&strdepartmentname&" <|> and 专业名称=
<|>"&strprofessionname&" <|> order by 性别,生源地,高考总成绩
说明：
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, <|>yyyy <|>) AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>01 <|>, a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>02 <|>, a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>03 <|>, a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>04 <|>, a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>05 <|>, a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>06 <|>, a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>07 <|>, a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>08 <|>, a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>09 <|>, a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>10 <|>, a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>11 <|>, a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>12 <|>, a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, <|>yyyy <|>)
说明：四表联查问题：
SQL: 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 .....
说明：得到表中最小的未使用的ID号
SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
FROM Handle
WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
2 2 a2
SET NOCOUNT OFF
----------------------------------------------------------------------------
顺便标注
（所影响的行数为 5 行）
*/
16:
不足5位数补零的方法
public string getNumberString(int n)
{
if(n.ToString().Length <5)
{
return (1000000+n).ToString().SubString(1);
}
return n.ToString();
}
17:
ip各段内容提取，类似有3或4段字串通过一特殊字符连接的字串提取
-----------------------------------
declare @a varchar(50)
set @a='192.168.1.123'
SELECT PARSENAME(@a,1),PARSENAME(@a,2),PARSENAME(@a,3),PARSENAME(@a,4)
set @a='100,200,300'
SELECT PARSENAME(replace(@a,',','.'),1)高,PARSENAME(replace(@a,',','.'),2)宽
,PARSENAME(replace(@a,',','.'),3)长
18:
--备份
declare @sql varchar(8000)
set @sql='backup database mis to disk=''d:\databack\mis\mis'
+rtrim(convert(varchar,getdate(),112))+'.bak'''
exec(@sql)
--删除15天前备份文件
set @sql='del d:\databack\mis\mis'
+rtrim(convert(varchar,getdate()-15,112))+'.bak'''
exec master..xp_cmdshell @sql
19:
SQL code
精妙SQL语句
明：复制表(只复制结构,源表名：a 新表名：b)
SQL: select * into b from a where 1 <>1
说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
SQL: insert into b(a, b, c) select d,e,f from b;
说明：显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
说明：外连接查询(表名1：a 表名2：b)
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
说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff( <|>minute <|>,f开始时间,getdate())>5
说明：两张关联表，删除主表中已经在副表中没有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
说明：--
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE, <|>YYYY/MM <|>) = TO_CHAR(SYSDATE, <|>YYYY/MM <|>)) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE, <|>YYYY/MM <|>) =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, <|>YYYY/MM <|>) || <|>/01 <|>, <|>YYYY/MM/DD <|>) - 1, <|>YYYY/MM <|>) ) Y,
WHERE X.NUM = Y.NUM （+）
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
WHERE A.NUM = B.NUM
说明：--
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称= <|>"&strdepartmentname&" <|> and 专业名称= <|>"&strprofessionname&" <|> order by 性别,生源地,高考总成绩
说明：
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, <|>yyyy <|>) AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>01 <|>, a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>02 <|>, a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>03 <|>, a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>04 <|>, a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>05 <|>, a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>06 <|>, a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>07 <|>, a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>08 <|>, a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>09 <|>, a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>10 <|>, a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>11 <|>, a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>12 <|>, a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, <|>yyyy <|>)
说明：四表联查问题：
SQL: 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 .....
说明：得到表中最小的未使用的ID号
SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
FROM Handle
WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
20:
判断sql执行所花的时间（精度为毫秒）
DECLARE @begin datetime
DECLARE @chaju bigint
DECLARE @end datetime
SET @begin=getdate()
要执行的sql语句......
SET @end=getdate()
SELECT @chaju = datediff(Millisecond, @begin, @end)
PRINT @chaju
SQL code
Dump Transaction databasename With No_log
backup log databasename with no_log
--清除日志 22:
create table tt(iname varchar(10))
insert into tt
select 'sc001' union
select 'sc002' union
select 'sc003' union
select 'sc004'
declare @a varchar(8000)
set @a=''
select @a=@a+','+iname from tt
select stuff(@a,1,1,'')23:print convert(char(8),getdate(), 112)24:SELECT @ZORDER = ISNULL (MAX(ZORDER), 0) + 1 FROM TD_TRADE25:right(10001 + Cast(Right(@MaxID, 4) as Int), 4)26:SQL code
declare @kw_test_sumup table (a int identity(1,1) primary key not null, b int)
insert into @kw_test_sumup(b)
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 5 union all
select 16 union all
select 16 union all
select 16
select * from @kw_test_sumup
select a.a,sum(b.b) [sum up above]
from @kw_test_sumup a,@kw_test_sumup b where a.a >= b.a group by a.a order by a.a27:USE MASTER--连接系统数据库
IF EXISTS(select 1 from master..sysdatabases where name='bankDB')
DROP DATABASE bankDB
GO
-----------------------------------------------建库------------------
--打开外围服务器
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
--新建文件夹
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
GO
CREATE 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
GO
CREATE 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
GO
CREATE 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)
begin
alter table userinfo
drop constraint uq_pid
end
if(object_id('ck_PID') is not null)
begin
alter table userinfo
drop constraint ck_PID
end
if(object_id('ck_telephone') is not null)
begin
alter table userinfo
drop constraint ck_telephone
end
alter 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]'--约束手机号码
or
telephone 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
or
telephone 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
or
telephone 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 @time
set @time=substring(@time,3,len(@time)-1)
print @time
print '1001'+convert(varchar(10),@time)
----------------------------------------------设置卡号为随机数(方法2用了 存储过程)---------------------------
create proc proc_randCardID
@cardid varchar(19) output
as
declare @r numeric(8,8)
set @cardid='1010 3657 '
while(1=1)
begin
set @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)
break
end
--测试（调用存储过程）
declare @card varchar(19)
exec proc_randCardID @card output
print @card
*/28:已经短小到不能再短了希望大家理解呀 ！
/*通用分页存储过程*/
USE HotelManagementSystem
GO
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='up_GetPageOfRecords')
DROP PROCEDURE up_GetPageOfRecords
GO
--创建存储过程
CREATE PROCEDURE up_GetPageOfRecords
@pageSize int = 20, --分页大小
@currentPage int , --第几页
@columns varchar(1000) = '*', --需要得到的字段
@tableName varchar(100), --需要查询的表
@condition varchar(1000) = '', --查询条件, 不用加where关键字
@ascColumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)
@pkColumn varchar(50) = '' --主键名称
AS
BEGIN --存储过程开始
DECLARE @strTemp varchar(300)
DECLARE @strSql varchar(5000) --该存储过程最后执行的语句
DECLARE @strOrderType varchar(1000) --排序类型语句 (order by column asc或者order by column desc)
BEGIN
IF @bitOrderType = 1 --降序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = ' <(SELECT min'
END
ELSE --升序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END
IF @currentPage = 1 --第一页
BEGIN
IF @condition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderType
END
ELSE -- 其他页
BEGIN
IF @condition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+
' '+@pkColumn+' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+
' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType
END
END
EXEC (@strSql)
END29:双色球和值选球
Create proc up_getSumball
@XmlString varchar(2000),
@Max int
as
begin
declare @idtb table(id int)
create table #tb (id int,num int)
declare @num int,@id int,@sum int,@times int
declare @idHandle int
set @sum=0
set @times=0
EXEC sp_xml_preparedocument @idHandle OUTPUT, @XmlString
insert into #tb(id,num)
select * from openxml(@idHandle,N'/root/tb_ball')
with #tb
while (@sum <>@max or @times <>6)
begin
select top 1 @id=id,@num=num from #tb where num <=33 order by newid()
if @num <>@max begin
if not exists(select 1 from @idtb where id=@id)
insert into @idtb select @id
end
select @sum=sum(num) from #tb where id in(select id from @idtb)
select @times=count(1) from @idtb
if (@times>6 ) begin
delete @idtb
end
if ((@Max=@num) and (@times <6)) begin
delete @idtb
end
select @times=count(1) from @idtb
end
select * from #tb where id in(select id from @idtb)
drop table #tb
end
go
declare @tb table(id int,num int)
insert into @tb select 1,1
insert into @tb select 2,2
insert into @tb select 3,3
insert into @tb select 4,4
insert into @tb select 5,5
insert into @tb select 6,6
insert into @tb select 7,7
insert into @tb select 8,8
insert into @tb select 9,9
insert into @tb select 11,11
insert into @tb select 12,12
insert into @tb select 13,13
insert into @tb select 14,14
insert into @tb select 15,15
insert into @tb select 16,16
insert into @tb select 17,17
insert into @tb select 18,18
insert into @tb select 19,19
insert into @tb select 20,20
insert into @tb select 21,21
insert into @tb select 22,22
insert into @tb select 23,23
insert into @tb select 24,24
insert into @tb select 25,25
insert into @tb select 26,26
insert into @tb select 27,27
insert into @tb select 28,28
insert into @tb select 29,29
insert into @tb select 30,30
insert into @tb select 31,31
insert into @tb select 32,32
insert into @tb select 33,33
declare @dataxml xml
declare @strXml varchar(2000)
set @dataxml=(select * from @tb as tb_ball for xml auto,root('root'))
set @strXml=convert(varchar(2000),@dataxml)
exec up_getSumball @strXml,50
30
根据身份证计算生日函数
SQL code
ALTER FUNCTION [dbo].[Birthday]
(
@IDCardNo NVARCHAR(50)
)
RETURNS DATETIME
AS
BEGIN
DECLARE @Birthday DATETIME
if (LEN(@IDCardNo)=15 or LEN(@IDCardNo)=16) and SUBSTRING(@IDCardNo,9,2) between 1 and 12 and SUBSTRING(@IDCardNo,11,2) between 1 and 31
SET @Birthday= CONVERT(VARCHAR(10),'19'+SUBSTRING(@IDCardNo,7,2)+'-'+SUBSTRING(@IDCardNo,9,2)+'-'+SUBSTRING(@IDCardNo,11,2),120)
else if LEN(@IDCardNo)=18 and SUBSTRING(@IDCardNo,7,2)>=19 and SUBSTRING(@IDCardNo,11,2) between 1 and 12 and SUBSTRING(@IDCardNo,13,2) between 1 and 31
SET @Birthday= CONVERT(VARCHAR(10),SUBSTRING(@IDCardNo,7,4)+'-'+SUBSTRING(@IDCardNo,11,2)+'-'+SUBSTRING(@IDCardNo,13,2),120)
else
SET @Birthday=null
RETURN(@Birthday)
END31:根据身份证计算性别函数（原创）
SQL code
CREATE FUNCTION [dbo].[sex]
(
@IDCardNo NVARCHAR(50)
)
RETURNS int
AS
BEGIN
DECLARE @sex int
if (LEN(@IDCardNo)=18 and ISNUMERIC(SUBSTRING(@IDCardNo,17,1))=1 )
SET @sex= (
case
when SUBSTRING(@IDCardNo,17,1) in(1,3,5,7,9) then 1
when SUBSTRING(@IDCardNo,17,1) in(2,4,6,7,0) then 2
else
0
end
)
else if (LEN(@IDCardNo)=15 and ISNUMERIC(SUBSTRING(@IDCardNo,15,1))=1 )
SET @sex= (
case
when SUBSTRING(@IDCardNo,15,1) in(1,3,5,7,9) then 1
when SUBSTRING(@IDCardNo,15,1) in(2,4,6,7,0) then 2
else
0
end
)
else
SET @sex=0
RETURN(@sex)
END
32:根据汉字查询汉字的首字母，比如 “中国人” 显示“ZGR”
SQL code
ALTER FUNCTION [dbo].[fun_getPY] (@str nvarchar(4000))
returns nvarchar(4000)
AS
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
set @PY = @PY+ (case when unicode(@word) between 19968 and 19968+20901
then (
select top 1 PY
from
(
select 'A' as PY, N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC
)
else @word
end)
set @str=right(@str,len(@str)-1)
end
return @PY
end
33:SQL code
1.向表中插入字段
alter TABLE staffer(表名) add stafferID(要插入的字段名) varchar(字段类型)
2.修改表中的数据
update Onscenedata(表名) set stafferID='YS002'(设定新值) where stafferID is null(条件，找到要修改的数据)
3.在查询结果后连接另一个表
表名 left join 表名 as 别名 on(条件关键字) a.cardreaderid=b.cardreaderid(条件)
4.向表中插入数据
insert recog(表名) values(8716,'KGE39','8716','1组','','',getdate(),'矿工',getdate())
5.删除表中数据
delete recog(表名) where cardid=8715(删除符合条件的数据)
6.创建表
create table qqqtq (姓名 varchar(30) primary key,性别 varchar(2),出生日期 datetime,喜欢 char(20),你好 char(12) not null)
7.得到表中所有字段
Getfield 表名
8.修改列名,如修改表staffer中occupations改为occupation
exec sp_rename 'staffer.occupations','occupation','column'
9
--A. 重命名表,下例将表 customers 重命名为 custs。
EXEC sp_rename 'customers', 'custs'
--B. 重命名列,下例将表 customers 中的列 contact title 重命名为 title。
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'
1.修改表字段
--加
ALTER TABLE table2 ADD row_id bigint
--删
ALTER TABLE table2 DROP COLUMN row_id
--改
ALTER TABLE 你的表 ALTER COLUMN 列名 你的类型 null 34:SQL code
USE [MES]
GO
/****** Object: StoredProcedure [dbo].[H_SqlToExcel] Script Date: 11/24/2008 13:22:07 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROC [dbo].[H_SqlToExcel]
(
@Path varchar(100),--文件存放路径
@Fname varchar(100),--文件名字
@SheetName varchar(80),---工作表名字
@SqlStr varchar(8000)--查询语句,如果查询语句中使用了order by ,请加上top 100 percent,注意,如果导出表/视图,用上面的存储过程
)
AS
SET NOCOUNT ON
declare @sql varchar(8000)
declare @obj int--OLE对象
declare @constr varchar(8000)
declare @err int
declare @out int
declare @fdlist varchar(8000)
declare @tbname sysname--临时表
declare @Src nvarchar(200)
declare @Desc nvarchar(200)
set @tbname='##tmp_'+convert(varchar(38),newid())
exec('select * into ['+@tbname +'] from '+'('+@sqlStr+') A')
select @fdlist = ''
set @sql= @path+@fname
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'+@sql+'";DBQ='+@sql
--生成Excel的列
set @sql = ''
select @sql = @sql+','+'['+a.name+'] '+ case when b.name like '%char' then case when a.length >255 then 'memo' else 'text('+cast(a.length as varchar)+')' end
when b.name like '%int' or b.name='bit' then 'int'
when b.name like '%datetime' then 'datetime'
when b.name like '%money' then 'money'
when b.name like '%text' then 'memo'
else b.name
end,
@fdlist = @fdlist+','+'['+a.name+']'
from tempdb..syscolumns a join tempdb..systypes b on a.xtype = b.xusertype
where b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
and id in(select id from tempdb..sysobjects where name = @tbname) order by colorder
if @@rowcount=0 return
set @fdlist = substring(@fdlist,2,8000)
--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err <> 0 goto lberror
exec @err=sp_oamethod @obj,'open',null,@constr
if @err <> 0 goto lberror
--创建工作薄
select @sql='create table ['+@sheetname
+']('+substring(@sql,2,8000)+')'
exec @err=sp_oamethod @obj,'execute',@out out,@sql--@sql为excute方法提供参数
if @err <> 0 goto lberror
exec @err=sp_oadestroy @obj
--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@sheetname+'$])'
--print @sql
exec ('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')
exec('drop table ['+@tbname+']')
return
lberror:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist35:USE [MES]
GO
/****** Object: StoredProcedure [dbo].[sp_jmail_send] Script Date: 11/24/2008 13:22:46 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER Procedure [dbo].[sp_jmail_send]
@sender varchar(100),--發送人的郵件地址
@sendername varchar(100)='',--發送人
@serveraddress varchar(255)='192.168.203.9',--郵件服務器地址
@MailServerUserName varchar(255)=null,--服務器的用戶名
@MailServerPassword varchar(255)=null,--密碼
@recipient varchar(255),--收件人
@recipientBCC varchar(200)=null,--
@recipientBCCName varchar(200)=null,
@recipientCC varchar(200)=null,
@recipientCCName varchar(100)=null,
@attachment varchar(100) =null,
@subject varchar(255),--主旨
@mailbody text--內容
As
/*
该存储过程使用办公自动化脚本调用Dimac w3 JMail AxtiveX组件来代替Sql Mail发送邮件
该方法支持“服务器端身份验证”
*/
--声明w3 JMail使用的常规变量及错误信息变量
Declare @object int,@hr int,@rc int,@output varchar(400),@description varchar (400),@source varchar(400)
--创建JMail.Message对象
Exec @hr = sp_OACreate 'jmail.message', @object OUTPUT
--设置邮件编码
Exec @hr = sp_OASetProperty @object, 'Charset', 'base64'
--身份验证
If Not @MailServerUserName is null
Exec @hr = sp_OASetProperty @object, 'MailServerUserName',@MailServerUserName
If Not @MailServerPassword is null
Exec @hr = sp_OASetProperty @object, 'MailServerPassword',@MailServerPassword
--设置邮件基本参数
Exec @hr = sp_OASetProperty @object, 'From', @sender
Exec @hr = sp_OAMethod @object, 'AddRecipient', NULL , @recipient
Exec @hr = sp_OASetProperty @object, 'Subject', @subject
Exec @hr = sp_OASetProperty @object, 'Body', @mailbody
--设置其它参数
if not @attachment is null
exec @hr = sp_OAMethod @object, 'Addattachment', NULL , @attachment,'false'
print @attachment
If (Not @recipientBCC is null) And (Not @recipientBCCName is null)
Exec @hr = sp_OAMethod @object, 'AddRecipientBCC', NULL , @recipientBCC,@recipientBCCName
Else If Not @recipientBCC is null
Exec @hr = sp_OAMethod @object, 'AddRecipientBCC', NULL , @recipientBCC
If (Not @recipientCC is null) And (Not @recipientCCName is null)
Exec @hr = sp_OAMethod @object, 'AddRecipientCC', NULL , @recipientCC,@recipientCCName
Else If Not @recipientCC is null
Exec @hr = sp_OAMethod @object, 'AddRecipientCC', NULL , @recipientCC
If Not @sendername is null
Exec @hr = sp_OASetProperty @object, 'FromName', @sendername
--调用Send方法发送邮件
Exec @hr = sp_OAMethod @object, 'Send', null,@serveraddress
--捕获JMail.Message异常
Exec @hr = sp_OAGetErrorInfo @object, @source OUTPUT, @description OUTPUT
if (@hr = 0)
Begin
Set @output='错误源: '+@source
Print @output
Select @output = '错误描述: ' + @description
Print @output
End
Else
Begin
Print '获取错误信息失败！'
Return
End
--释放JMail.Message对象
Exec @hr = sp_OADestroy @object36:SQL code
USE [MES]
GO
/****** Object: StoredProcedure [dbo].[p_backupdb] Script Date: 11/24/2008 13:24:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[p_backupdb]
@dbname sysname='', --要备份的数据库名称,不指定则备份当前数据库
@bkpath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录
@bkfname nvarchar(260)='', --备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间
@bktype nvarchar(10)='DB', --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
@appendfile bit=1 --追加/覆盖备份文件
as
declare @sql varchar(8000)
if isnull(@dbname,'')='' set @dbname=db_name()
if isnull(@bkpath,'')='' set @bkpath=dbo.f_getdbpath(null)
if isnull(@bkfname,'')='' set @bkfname='\DBNAME\_\DATE\_\TIME\.BAK'
set @bkfname=replace(replace(replace(@bkfname,'\DBNAME\',@dbname)
,'\DATE\',convert(varchar,getdate(),112))
,'\TIME\',replace(convert(varchar,getdate(),108),':',''))
set @sql='backup '+case @bktype when 'LOG' then 'log ' else 'database ' end +@dbname
+' to disk='''+@bkpath+@bkfname
+''' with '+case @bktype when 'DF' then 'DIFFERENTIAL,' else '' end
+case @appendfile when 1 then 'NOINIT' else 'INIT' end
print @sql
exec(@sql)
37:
留名，查询
我也发个，更改数据库中表的所有者
SQL code
--下面的SQL语句可以直接更改一个表的所有者，前提是要testuser用户存在
--EXEC sp_changeobjectowner 'dob.product', 'testuser'
----下面是通过修改系统表的相应值来达到修改所有者的目的。在sql2000下可用。在2005下还没有测试。
----创建存储过程
--更改单个表的所有者
if exists ( select name from sysobjects where name = 'ChangeTableOwner' and type= 'P')
drop procedure ChangeTableOwner
go
create procedure ChangeTableOwner
@TableName varchar(50),
@newUserName varchar(50)
as
Begin
exec sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
declare @newId int
if exists (select uid from sysusers where name = @newUserName)
BEGIN
select @newId = uid from sysusers where name = @newUserName
update sysobjects set uid = @newId where name=@TableName and type='u'
END
else
BEGIN
print N'the @newUserName does not exist,please check it and try again!'
END
exec sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
End
go
--更改所有表的所有者（不会更改系统表）
if exists ( select name from sysobjects where name = 'ChangeUser' and type= 'P')
drop procedure ChangeUser
go
create procedure ChangeUser
@newUserName varchar(50)
as
Begin
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
declare @newId int
if exists (select uid from sysusers where name = @newUserName)
BEGIN
select @newId = uid from sysusers where name = @newUserName
update sysobjects set uid = @newId where type='u'
END
else
BEGIN
print N'the @newUserName does not exist,please check it and try again!'
END
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
End
38:--语 句 功 能
--数据操作
SELECT --从数据库表中检索数据行和列
INSERT --向数据库表添加新数据行
DELETE --从数据库表中删除数据行
UPDATE --更新数据库表中的数据
--数据定义
CREATE TABLE --创建一个数据库表
DROP TABLE --从数据库中删除表
ALTER TABLE --修改数据库表结构
CREATE VIEW --创建一个视图
DROP VIEW --从数据库中删除视图
CREATE INDEX --为数据库表创建一个索引
DROP INDEX --从数据库中删除索引
CREATE PROCEDURE --创建一个存储过程
DROP PROCEDURE --从数据库中删除存储过程
CREATE TRIGGER --创建一个触发器
DROP TRIGGER --从数据库中删除触发器
CREATE SCHEMA --向数据库添加一个新模式
DROP SCHEMA --从数据库中删除一个模式
CREATE DOMAIN --创建一个数据值域
ALTER DOMAIN --改变域定义
DROP DOMAIN --从数据库中删除一个域
--数据控制
GRANT --授予用户访问权限
DENY --拒绝用户访问
REVOKE --解除用户访问权限
--事务控制
COMMIT --结束当前事务
ROLLBACK --中止当前事务
SET TRANSACTION --定义当前事务数据访问特征
--程序化SQL
DECLARE --为查询设定游标
EXPLAN --为查询描述数据访问计划
OPEN --检索查询结果打开一个游标
FETCH --检索一行查询结果
CLOSE --关闭游标
PREPARE --为动态执行准备SQL 语句
EXECUTE --动态地执行SQL 语句
DESCRIBE --描述准备好的查询
---局部变量
declare @id char(10)
--set @id = '10010001'
select @id = '10010001'
---全局变量
---必须以@@开头
--IF ELSE
declare @x int @y int @z int
select @x = 1 @y = 2 @z=3
if @x > @y
print 'x > y' --打印字符串'x > y'
else if @y > @z
print 'y > z'
else print 'z > y'
--CASE
use pangu
update employee
set e_wage =
case
when job_level = '1' then e_wage*1.08
when job_level = '2' then e_wage*1.07
when job_level = '3' then e_wage*1.06
else e_wage*1.05
end
--WHILE CONTINUE BREAK
declare @x int @y int @c int
select @x = 1 @y=1
while @x < 3
begin
print @x --打印变量x 的值
while @y < 3
begin
select @c = 100*@x + @y
print @c --打印变量c 的值
select @y = @y + 1
end
select @x = @x + 1
select @y = 1
end
--WAITFOR
--例 等待1 小时2 分零3 秒后才执行SELECT 语句
waitfor delay '01:02:03'
select * from employee
--例 等到晚上11 点零8 分后才执行SELECT 语句
waitfor time '23:08:00'
select * from employee
***SELECT***
select *(列名) from table_name(表名) where column_name operator value
ex:(宿主)
select * from stock_information where stockid = str(nid)
stockname = 'str_name'
stockname like '% find this %'
stockname like '[a-zA-Z]%' --------- ([]指定值的范围)
stockname like '[^F-M]%' --------- (^排除指定范围)
--------- 只能在使用like关键字的where子句中使用通配符)
or stockpath = 'stock_path'
or stocknumber < 1000
and stockindex = 24
not stocksex = 'man'
stocknumber between 20 and 100
stocknumber in(10,20,30)
order by stockid desc(asc) --------- 排序，desc-降序，asc-升序
order by 1,2 --------- by列号
stockname = (select stockname from stock_information where stockid = 4)
--------- 子查询
--------- 除非能确保内层select只返回一个行的值，
--------- 否则应在外层where子句中用一个in限定符
select distinct column_name form table_name --------- distinct指定检索独有的列值，不重复
select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
select stockname , "stocknumber" = count(*) from table_name group by stockname
--------- group by 将表按行分组,指定列中有相同的值
having count(*) = 2 --------- having选定指定的组
select *
from table1, table2
where table1.id *= table2.id -------- 左外部连接，table1中有的而table2中没有得以null表示
table1.id =* table2.id -------- 右外部连接
select stockname from table1
union [all] ----- union合并查询结果集，all-保留重复行
select stockname from table2
***insert***
insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
value (select Stockname , Stocknumber from Stock_table2)---value为select语句
***update***
update table_name set Stockname = "xxx" [where Stockid = 3]
Stockname = default
Stockname = null
Stocknumber = Stockname + 4
***delete***
delete from table_name where Stockid = 3
truncate table_name ----------- 删除表中所有行，仍保持表的完整性
drop table table_name --------------- 完全删除表
***alter table*** --- 修改数据库表结构
alter table database.owner.table_name add column_name char(2) null .....
sp_help table_name ---- 显示表已有特征
create table table_name (name char(20), age smallint, lname varchar(30))
insert into table_name select ......... ----- 实现删除列的方法（创建新表）
alter table table_name drop constraint Stockname_default ---- 删除Stockname的default约束
***function(/*常用函数*/)***
----统计函数----
AVG --求平均值
COUNT --统计数目
MAX --求最大值
MIN --求最小值
SUM --求和
--AVG
use pangu
select avg(e_wage) as dept_avgWage
from employee
group by dept_id
--MAX
--求工资最高的员工姓名
use pangu
select e_name
from employee
where e_wage =
(select max(e_wage)
from employee)
--STDEV()
--STDEV()函数返回表达式中所有数据的标准差
--STDEVP()
--STDEVP()函数返回总体标准差
--VAR()
--VAR()函数返回表达式中所有值的统计变异数
--VARP()
--VARP()函数返回总体变异数
----算术函数----
/***三角函数***/
SIN(float_expression) --返回以弧度表示的角的正弦
COS(float_expression) --返回以弧度表示的角的余弦
TAN(float_expression) --返回以弧度表示的角的正切
COT(float_expression) --返回以弧度表示的角的余切
/***反三角函数***/
ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角
ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角
ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
--返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)
--把弧度转换为角度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
EXP(float_expression) --返回表达式的指数值
LOG(float_expression) --返回表达式的自然对数值
LOG10(float_expression)--返回表达式的以10 为底的对数值
SQRT(float_expression) --返回表达式的平方根
/***取近似值函数***/
CEILING(numeric_expression) --返回>=表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
FLOOR(numeric_expression) --返回 <=表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
ROUND(numeric_expression) --返回以integer_expression 为精度的四舍五入值返回的数据
--类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
ABS(numeric_expression) --返回表达式的绝对值返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
SIGN(numeric_expression) --测试参数的正负号返回0 零值1 正数或-1 负数返回的数据类型
--与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
PI() --返回值为π 即3.1415926535897936
RAND([integer_expression]) --用任选的[integer_expression]做种子值得出0-1 间的随机浮点数
----字符串函数----
ASCII() --函数返回字符表达式最左端字符的ASCII 码值
CHAR() --函数用于将ASCII 码转换为字符
--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值
LOWER() --函数把字符串全部转换为小写
UPPER() --函数把字符串全部转换为大写
STR() --函数把数值型数据转换为字符型数据
LTRIM() --函数把字符串头部的空格去掉
RTRIM() --函数把字符串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING() --函数返回部分字符串
CHARINDEX(),PATINDEX() --函数返回字符串中某个指定的子串出现的开始位置
SOUNDEX() --函数返回一个四位字符码
--SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值
DIFFERENCE() --函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异
--0 两个SOUNDEX 函数返回值的第一个字符不同
--1 两个SOUNDEX 函数返回值的第一个字符相同
--2 两个SOUNDEX 函数返回值的第一二个字符相同
--3 两个SOUNDEX 函数返回值的第一二三个字符相同
--4 两个SOUNDEX 函数返回值完全相同
39:QUOTENAME() --函数返回被特定字符括起来的字符串
/*select quotename('abc', '{') quotename('abc')
运行结果如下
----------------------------------{
{abc} [abc]*/
REPLICATE() --函数返回一个重复character_expression 指定次数的字符串
/*select replicate('abc', 3) replicate( 'abc', -2)
运行结果如下
----------- -----------
abcabcabc NULL*/
REVERSE() --函数将指定的字符串的字符排列顺序颠倒
REPLACE() --函数返回被替换了指定子串的字符串
/*select replace('abc123g', '123', 'def')
运行结果如下
----------- -----------
abcdefg*/
SPACE() --函数返回一个有指定长度的空白字符串
STUFF() --函数用另一子串替换字符串指定位置长度的子串
----数据类型转换函数----
CAST() 函数语法如下
CAST() ( <expression> AS <data_ type>[ length ])
CONVERT() 函数语法如下
CONVERT() ( <data_ type>[ length ], <expression> [, style])
select cast(100+99 as char) convert(varchar(12), getdate())
运行结果如下
------------------------------ ------------
----日期函数----
DAY() --函数返回date_expression 中的日期值
MONTH() --函数返回date_expression 中的月份值
YEAR() --函数返回date_expression 中的年份值
DATEADD( <datepart> , <number> , <date>)
--函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期
DATEDIFF( <datepart> , <number> , <date>)
--函数返回两个指定日期在datepart 方面的不同之处
DATENAME( <datepart> , <date>) --函数以字符串的形式返回日期的指定部分
DATEPART( <datepart> , <date>) --函数以整数值的形式返回日期的指定部分
GETDATE() --函数以DATETIME 的缺省格式返回系统当前的日期和时间
----系统函数----
APP_NAME() --函数返回当前执行的应用程序的名称
COALESCE() --函数返回众多表达式中第一个非NULL 表达式的值
COL_LENGTH( <'table_name'>, <'column_name'>) --函数返回表中指定字段的长度值
COL_NAME( <table_id>, <column_id>) --函数返回表中指定字段的名称即列名
DATALENGTH() --函数返回数据表达式的数据的实际长度
DB_ID(['database_name']) --函数返回数据库的编号
DB_NAME(database_id) --函数返回数据库的名称
HOST_ID() --函数返回服务器端计算机的名称
HOST_NAME() --函数返回服务器端计算机的名称
IDENTITY( <data_type>[, seed increment]) [AS column_name])
--IDENTITY() 函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中
/*select identity(int, 1, 1) as column_name
into newtable
from oldtable*/
ISDATE() --函数判断所给定的表达式是否为合理日期
ISNULL( <check_expression>, <replacement_value>) --函数将表达式中的NULL 值用指定值替换
ISNUMERIC() --函数判断所给定的表达式是否为合理的数值
NEWID() --函数返回一个UNIQUEIDENTIFIER 类型的数值
NULLIF( <expression1>, <expression2>)
--NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值 40:
use master
go
if exists(select * from sysdatabases where name = 'stuDB')
drop database stuDB
create database stuDB
on
(
name = 'stuDB_data', --主数据文件名
filename = 'E:\stuDB_data.mdf', --主数据文件的物理名称
size = 5mb, --主数据文件的初始大小
maxsize = 10mb, --主数据文件的最大值
filegrowth = 15% --主数据文件的增长值
)
log on
(
name = 'stuDB_log', --日志文件名
filename = 'E:\stuDB_log.ldf', --日志文件的物理名称
size = 2mb, --日志文件的初始大小
filegrowth = 15% --日志文件的增长率
)
go
/*--创建学生信息表stuInfo--*/
use stuDB --设置当前数据库为stuDB数据库，以便在stuDB中创建表
go
create table stuInfo /*--创建学员信息表--*/
(
stuName nvarchar(20) not null, --学生姓名，非空
stuNo nchar(6) not null, --学生学号，非空
stuSex nchar(1) not null, --学生性别
stuAge int not null, --学生年龄
stuSeat int identity(1,1), --座位编号
stuAddress text --学生地址
)
go
/*--为stuInfo表添加约束条件--*/
--添加主键约束，学号作为主键
alter table stuInfo
add constraint pk_stuNo primary key(stuNo)
--添加检查约束，要求学号必须是"s253**"
alter table stuInfo
add constraint ck_stuNo check(stuNo like 's253__')
--添加检查约束，要求性别必须是男或女
alter table stuInfo
add constraint ck_stuSex check(stuSex = '男' or stuSex = '女')
--添加检查约束，要求年龄必须在15~40岁之间
alter table stuInfo
add constraint ck_stuAge check(stuAge between 15 and 40)
--添加检查约束，要求座位号必须在1~30之间
alter table stuInfo
add constraint ck_stuSeat check(stuSeat <= 30)
--添加默认约束，如果地址不填就默认为"地址不详"
alter table stuInfo
add constraint df_stuAddress default('地址不详') for stuAddress
go
/*--向学生信息表中(stuInfo)插入数据--*/
insert into stuInfo values('张秋丽','s25301','男',18,default)
insert into stuInfo
select '李斯文','s25303','女',22,'荷兰洛阳' union
select '李文才','s25302','男',31,'北京海淀' union
select '欧阳俊雄','s25304','男',28,'新建威武哈' union
select '果冻','s25305','男',25,'成都锦江区'
/*--创建学员成绩表(stuMarks)--*/
create table stuMarks
(
examNO nchar(7) not null, --考号
stuNo nchar(6) not null, --学号
writtenExam int not null, --笔试成绩
labExam int not null --机试成绩
)
go
/*--向学员成绩表(stuMarks)中插入信息--*/
insert stuMarks
select 's2007','s25301',80,58 union
select 's2008','s25303',50,90 union
select 's2009','s25302',65,0 union
select 's2010','s25304',77,82
select * from stuMarks
select * from stuInfo
set nocount on
/*--创建储存过程--*/
if exists(select * from sysobjects where name = 'proc_stu')
drop proc proc_stu
go
create proc proc_stu
@writtenExam int = 60,
@labExam int = 60
as
select stuInfo.stuName as 姓名,stuInfo.stuNo as 学号
,[笔试成绩] = case
when stuMarks.writtenExam is null then '缺考'
else convert(nvarchar(3),writtenExam)
end
,[机试成绩] = case
when stuMarks.labExam is null then '缺考'
else convert(nvarchar(3),labExam)
end
,[是否通过] = case
when stuMarks.labExam >= @labExam and stuMarks.writtenExam >= @writtenExam then '是'
else '否'
end
from stuInfo left join stuMarks on stuInfo.stuNo = stuMarks.stuNo
go
exec proc_stu
/*--创建带三个参数的储存过程--*/
if exists(select * from sysobjects where name = 'proc_stu2')
drop proc proc_stu2
go
create proc proc_stu2
@noPass int output,
@writtenExam int = 60,
@labExam int = 60
as
select @noPass = count(*)
from(select stuInfo.stuName as 姓名,stuInfo.stuNo as 学号
,[笔试成绩] = case
when stuMarks.writtenExam is null then '缺考'
else convert(nvarchar(3),writtenExam)
end
,[机试成绩] = case
when stuMarks.labExam is null then '缺考'
else convert(nvarchar(3),labExam)
end
,[是否通过] = case
when stuMarks.labExam >= @labExam and stuMarks.writtenExam >= @writtenExam then '是'
else '否'
end
from stuInfo left join stuMarks on stuInfo.stuNo = stuMarks.stuNo) as temp where temp.[是否通过] = '否'
go
declare @noPass int
exec proc_stu2 @noPass output,60,60
print '没有通过的人数为' + convert(nvarchar(2),@noPass) + '人'
/*--创建统计没有通过的学员信息的储存过程--*/
if exists(select * from sysobjects where name = 'proc_stu3')
drop proc proc_stu3
go
create proc proc_stu3
@writtenExam int = 60,
@labExam int = 60
as
declare @IsPass int
declare @sum int
declare @noPass int
declare @noPass1 int
select 笔试及格线 = @writtenExam,机试及格线 = @labExam
--查询没有通过考试的学员信息
select *
from(select stuInfo.stuName as 姓名,stuInfo.stuNo as 学号
,[笔试成绩] = case
when stuMarks.writtenExam is null then '缺考'
else convert(nvarchar(3),writtenExam)
end
,[机试成绩] = case
when stuMarks.labExam is null then '缺考'
else convert(nvarchar(3),labExam)
end
,[是否通过] = case
when stuMarks.labExam >= @labExam and stuMarks.writtenExam >= @writtenExam then '是'
else '否'
end
from stuInfo left join stuMarks on stuInfo.stuNo = stuMarks.stuNo) as temp where temp.[是否通过] = '否'
--统计通过的人数
select @IsPass = count(*)
from(select stuInfo.stuName as 姓名,stuInfo.stuNo as 学号
,[笔试成绩] = case
when stuMarks.writtenExam is null then '缺考'
else convert(nvarchar(3),writtenExam)
end
,[机试成绩] = case
when stuMarks.labExam is null then '缺考'
else convert(nvarchar(3),labExam)
end
,[是否通过] = case
when stuMarks.labExam >= @labExam and stuMarks.writtenExam >= @writtenExam then '是'
else '否'
end
from stuInfo left join stuMarks on stuInfo.stuNo = stuMarks.stuNo) as temp where temp.[是否通过] = '是'
select @sum = count(*) from stuInfo
set @noPass = @sum - @IsPass
set @noPass1 = @noPass * 100/@sum
if @noPass1 > 60
select 未通过人数 = @noPass,结论 = '超过60%及格分数还应下调'
else
select 未通过人数 = @noPass
go
exec proc_stu3 50,50
41:
在前人基础上修改过的分页存储过程，单表多表都行，性能经过千万级数据验证
CREATE procedure [dbo].[userpage]
(
@SqlWhere varchar(1000)='', --查询条件, 可为空
@pagenum int=20, --每页的记录数
@beginline int=1, --第几页,默认第一页
@SqlTable varchar(1000), --要查询的表或视图,也可以一句sql语句
@SqlColumn varchar(1000), --查询的字段
@SqlPK varchar(50), --主键 必须填写，自动编号字段
@SqlOrder varchar(200) , --排序，可为空，则默认为以主键倒序排列
@GetCount bit=0, --0为取查询结果，1为取查询总数
@totalCount int --0为不限制结果总数，大于0即为取@totalCount条数据，其他忽视
)
as
set nocount on
declare @PageLowerBound int
declare @PageUpperBound int
declare @sqlstr nvarchar(2000)
declare @d datetime
if @SqlWhere=''
begin
set @SqlWhere=' where 1=1 '
end
else
begin
set @SqlWhere=' where 1=1 and '+@SqlWhere+' '
end
if @SqlOrder=''
begin
set @SqlOrder=' order by '+@SqlPK+' desc'
end
else
begin
set @SqlOrder=' order by '+@SqlOrder
end
if @SqlColumn='' set @SqlColumn=' * '
if @beginline=0 set @beginline=1
if @totalcount>0
begin
declare @totalpage int
set @totalpage=@totalcount/@pagenum
if @totalcount%@pagenum >0 set @totalpage=@totalpage+1
if @beginline>@totalpage set @beginline=@totalpage
end
declare @mytop nvarchar(20)
set @mytop=''
if @totalcount>0 set @mytop=' top '+convert(nvarchar(10),@totalcount)+' '
IF @GetCount=1
BEGIN
declare @Count int
declare @sCount int
if @totalCount=0
begin
set @sqlstr=N'select @sCount=count(1) FROM '+@SqlTable+@SqlWhere
end
else
begin
set @sqlstr=N'select @sCount=count(1) FROM '+@SqlTable+' where
'+@SqlPK+' in (select top '+convert(nvarchar(10),@totalcount)+'
'+@SqlPK+' from '+@SqlTable+' '+@Sqlwhere+')'
end
Exec sp_executesql @sqlstr,N'@sCount int outPut',@Count output
select @count as totalCount
END
else
begin
if @beginline=1
begin
set @sqlstr='select '+@SqlColumn+' from '+@SqlTable+' where '+@sqlPK+'
in (select top '+str(@pagenum)+' '+@SqlPK+' from
'+@SqlTable+@SqlWhere+@SqlOrder+')'+@SqlOrder
Exec sp_executesql @sqlstr
set nocount off
end
else
begin
set @PageLowerBound=(@beginline-1)*@pagenum
set @PageUpperBound=@PageLowerBound+@pagenum
create table #pageindex(temporary_id int identity(1,1) not null,temporary_nid int)
create unique clustered index index_nid_pageindex on #pageindex(temporary_id)
set rowcount @PageUpperBound
set @sqlstr=N'insert into #pageindex(temporary_nid) select '+@mytop+@SqlPK+' from '+@SqlTable+@SqlWhere+@SqlOrder
Exec sp_executesql @sqlstr
set @sqlstr='select '+@SqlColumn+' FROM '+ @SqlTable +' inner join
#pageindex p on '+@SqlPK+'=p.temporary_nid and
(p.temporary_id>'+STR(@PageLowerBound)+') and (p.temporary_id
<='+STR(@PageUpperBound)+')' +@SqlOrder
Exec sp_executesql @sqlstr
set nocount off
drop table #pageindex
end
end
GO
42:--MS SQLSERVER 清空所有表的数据
CREATE PROC P_DelAllUserTableData
as
Begin
declare @name varchar(20)
Declare Cur Cursor For
select Name from sysobjects where xtype='u' and status>=0 and Name like '%WQ_%'
declare @SQL Varchar(20)
Open Cur
Fetch Cur Into @name
While @@FETCH_STATUS=0
BEGIN
Set @sql='DELETE '+@name+''
Exec(@sql)
Fetch Cur Into @name
End
Close Cur
Deallocate cur
End
GO
exec P_DelAllUserTableData
我也写几句
--建库
create database booksAdm
on
(
name = booksAdm,
filename = 'F:\bookAdm_mdf.mdf',
filegrowth = 5,
size = 20,
maxsize = 52
)
log on
(
name = booksldf,
filename = 'F:\bookAdm_ldf.ldf',
filegrowth = 5,
size = 10,
maxsize = 22
)
--建表
/**
*员工信息表 对员
工信息的基本描
述****/
use booksAdm
create table Tblemplyee
(
PK_empId char(16) not null , --存放员工编号，切不能为空
empName char(16) not null, --存放员工姓名，不为空
FK_deptId int not null, --存放部门(department)编号
empBirthday datetime, --员工生日
empAge int ,--员工年龄
empSex int not null, --员工性别（0代表男，1代表女）
workdate datetime
) 43:----部门信息表，包括部门编号(deptId)，部门名称(deptName)
create table Tbldepartment
(
PK_deptId int not null,
deptName char(20) not null
)
----图书信息
create table Tblbooks
(
PK_bookId int not null, --图书编号
FK_bKid int not null ,--图书种类编号
FK_pubId int not null, --出版社编号
bookName varchar(20), --图书名称
bkPrice money,
pubDate datetime ,--出版日期
FK_bklocaId int not null,--图书位置信息
WithCD char(1) , --图书是否带光盘
sumbooks int not null --库存数量
)
----图书类型信息表
create table TblbookKinds
(
PK_bKid int not null, -- 图书种类编号
bKname varchar(16) not null -- 图书种类名称
)
---图书位置信息表
create table Tblbklocality
(
PK_bklocaId int not null, --图书位置编号
bklocaDisc varchar(20) --图书位置描述
)
---出版社信息表
create table Tblbook_Concern
(
PK_bkcnId int not null , -- 出版社编号
bkcnName varchar(20) --出版社名称
)
---图书借阅信息表
create table Tblbook_loan
(
PK_bkLnId varchar(20) not null, --图书借阅单编号
FK_bookId int not null, --图书编号
FK_empId char(16) not null ,--员工编号
loan_date datetime not null, --借出时间
FK_bKname varchar(20) not null --图书名称
)
-- 图书借阅归还 信息(图书)
--drop table TblReturnbook
create table TblReturnbook
(
PK_retId varchar(10)not null primary key,
FK_bookId int not null,
FK_empId char(16)not null,
FK_loan_date datetime not null,
Returntime datetime not null,
WhetherReturn int not null check(WhetherReturn between 0 and 1)
)
---------------------------------
--修改表 添加约束
alter table Tblemplyee
add constraint PK_empId primary key(PK_empId)
alter table Tblemplyee
add constraint UK_empName unique(empName)
alter table Tblemplyee
add constraint CK_empSex check(empSex between 0 and 1)
alter table Tblemplyee
add constraint check_empAge check(empAge between 1 and 150)
alter table Tbldepartment
add constraint PK_deptId primary key(PK_deptId)
alter table Tblemplyee
add constraint FK_deptId foreign key (FK_deptId) references Tbldepartment(PK_deptId)
alter table Tblbooks
add constraint PK_bookId primary key (PK_bookId)
alter table TblbookKinds
add constraint PK_bKid primary key (PK_bKid)
alter table TblbookKinds
add constraint UK_bKname unique (bKname)
alter table Tblbooks
add constraint FK_bKid foreign key (FK_bKid) references TblbookKinds (PK_bKid)
alter table Tblbook_Concern
add constraint PK_bkcnId primary key (PK_bkcnId)
alter table Tblbook_concern
add constraint UK_bkcnName unique(bkcnName)
alter table Tblbooks
add constraint FK_pubId foreign key (FK_pubId) references Tblbook_Concern(PK_bkcnId)
--select * from sysobjects
alter table Tblbook_loan
add constraint PK_bkLnId primary key (PK_bkLnId)
alter table Tblbook_loan
add constraint FK_bookId foreign key (FK_bookId) references Tblbooks(PK_bookId)
alter table Tblbook_loan
add constraint FK_empId foreign key (FK_empId) references Tblemplyee(PK_empId)
alter table Tblbklocality
add constraint PK_bklocaId primary key (PK_bklocaId)
alter table Tblbooks
add constraint FK_bklocaId foreign key (FK_bklocaId)references Tblbklocality (PK_bklocaId)
alter table TblReturnbook
add constraint FK_FK_bookId foreign key(FK_bookId) references Tblbooks(PK_bookId)
alter table TblReturnbook
add constraint FK_FK_empId foreign key(FK_empId) references Tblemplyee (PK_empId)
-----------------/*存储过程*/-----------------------
--*** 创建部门信息存储过程--(PK_deptId 90**)
--drop proc proc_insert_dept
create proc proc_insert_dept
@_PK_deptId int ,
@_deptName char (20)
AS
insert into Tbldepartment (PK_deptId,deptName) values(@_PK_deptId,@_deptName)
go
---执行存储过程
exec proc_insert_dept 109002,'人事部'
select * from Tbldepartment
--*** 创建员工存储过程 ---- proc_insert_emp (@_PK_empId 9100**)---- **
--drop proc proc_insert
create procedure proc_insert_emp
@_PK_empId char(16),
@_empName char(16),
@_FK_deptId int,
@_empBirthday datetime,
@_empAge int,
@_empSex int,
@_workdate datetime
AS
insert into Tblemplyee(PK_empId,empName,FK_deptId,empBirthday,empAge,empSex,workdate)
values(@_PK_empId,@_empName,@_FK_deptId,@_empBirthday,@_empAge,@_empSex,@_workdate)
go
--执行员工存储过程
exec proc_insert_emp 910001,'郭跃',109002,'08-08-2008',24,1,'09-09-2009'
select * from Tblemplyee
---创建 图书种类 存储过程
create proc proc_insert_bookkinds
@_PK_bKid int,
@_bKname varchar(16)
as
insert into TblbookKinds(PK_bKid,bKname)values (@_PK_bKid,@_bKname)
go
--执行 图书种类 存储过程
exec proc_insert_bookkinds 201001,'计算机及应用'
select * from TblbookKinds
--创建 出版社存储过程
create proc proc_insert_book_Concern
@_PK_bkcnId int,
@_bkcnName varchar(20)
as
insert into Tblbook_Concern (PK_bkcnId,bkcnName) values (@_PK_bkcnId,@_bkcnName)
go
--调用出版社存储过程
exec proc_insert_book_Concern 100001 ,'新华出版社'
select * from Tblbook_Concern
--创建 图书位置信息存储过程
--drop proc proc_insert_bklocality
create proc proc_insert_bklocality
@_PK_bklocaId int,
@_bklocaDisc varchar(20)
as
insert into Tblbklocality(PK_bklocaId,bklocaDisc)values (@_PK_bklocaId,@_bklocaDisc)
go
--执行 图书位置信息存储
exec proc_insert_bklocality 10901,'一书架顶层1号'
exec proc_insert_bklocality 10902,'一书架顶层2号'
44:--创建 图书 存储过程
--drop proc proc_insert_books
create proc proc_insert_books
@_PK_bookId int,
@_FK_bKid int,
@_FK_pubId int,
@_bookName varchar(20),
@_bkPrice money,
@_pubDate datetime,
@_FK_bklocaId int,
@_WithCD int,
@_sumbooks int
as
insert into Tblbooks(PK_bookId,FK_bKid,FK_pubId,bookName,bkPrice,pubDate,FK_bklocaId,WithCD,sumbooks)
values (@_PK_bookId,@_FK_bKid,@_FK_pubId,@_bookName,@_bkPrice,@_pubDate,@_FK_bklocaId,@_WithCD,@_sumbooks)
go
--调用 图书 存储过程
exec proc_insert_books 101,201001,100001,'Java 核心技术第八版',78,'08-09-2008',10901,0,10
exec proc_insert_books 102,201001,100001,'Java 核心技术第七版',70,'08-09-2008',10902,0,10
---创建 图书借阅信息 存储过程
create proc proc_Tblbook_loan
@_PK_bkLnId varchar(20),
@_FK_bookId int,
@_FK_empId char(16),
@_loan_date datetime,
@_FK_bKname varchar(20)
as
insert into Tblbook_loan (PK_bkLnId,FK_bookId,FK_empId,loan_date,FK_bKname)
values(@_PK_bkLnId,@_FK_bookId,@_FK_empId,@_loan_date,@_FK_bKname)
go
--调用 图书借阅信息 存储过程
exec proc_Tblbook_loan loan001,101,910001,'2009-2-24 14:36:19','Java 核心技术第八版'
select * from dbo.Tblbook_loan
--
select * from sysObjects where name =proc_insert_books,
--*-*-*-*-*--*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
--drop trigger trig_insert
-- 书籍借阅 触发器
create trigger trig_bookloan
on Tblbook_loan
WITH ENCRYPTION /*加密触发器*/
for insert
as
declare @PK_retId varchar(10),@FK_bookId int,@FK_empId
char(16),@FK_loan_date datetime,@Returntime datetime,@WhetherReturn int
select @Returntime = getdate()
select @WhetherReturn =0
select @PK_retId = PK_bkLnId,@FK_bookId = FK_bookId,@FK_empId=FK_empId,@FK_loan_date = loan_date from Tblbook_loan
insert into TblReturnbook
(PK_retId,FK_bookId,FK_empId,FK_loan_date,Returntime,WhetherReturn)
values
(@PK_retId,@FK_bookId,@FK_empId,@FK_loan_date,@Returntime,@WhetherReturn)
update Tblbooks set sumbooks = sumbooks -1 where PK_bookId = @FK_bookId
go
-----------------
--书籍 归还触发器
create trigger trig_bookReturn
on TblReturnbook
with ENCRYPTION
for update
as
declare @FK_bookId int
if update(PK_retId)
begin
print'此项不允许更改操作，Returntime，WhetherReturn，可以更改'
raiserror('更改被终止，操作失败',16,1)
rollback transaction
return
end
if update(FK_bookId)
begin
print'此项不允许更改操作，Returntime，WhetherReturn，可以更改'
raiserror('更改被终止，操作失败',16,1)
rollback transaction
return
end
if update(FK_loan_date)
begin
print'此项不允许更改操作，Returntime，WhetherReturn，可以更改'
raiserror('更改被终止，操作失败',16,1)
rollback transaction
return
end
if update(Returntime)
begin
if(Returntime > getdate()+1)
begin
print'应小于当前时间，操作失败'
raiserror('应小于当前时间，操作失败,时间操作异常，被终止',16,1)
rollback transaction
return
end
end
if update (WhetherReturn)
begin
if(WhetherReturn != 1)
begin
print'归还时 WhetherReturn 应为 1（是） '
raiserror('WhetherReturn 值 更改错误，操作终止',16,1)
rollback transaction
return
end
end
select @FK_bookId =Fk_bookId from inserted
update Tblbooks set sumbooks = sumbooks +1 where PK_bookId = @FK_bookId
go45:create table #tmp(学生 varchar(10),成绩 money )
insert into #tmp values('爱因斯坦',5)
insert into #tmp values('老布什',4)
insert into #tmp values('小小犬',0)
insert into #tmp values('perneer',3)
select a.学生,成绩排名次从高到低=count(*) from #tmp a,#tmp b where a.成绩 <=b.成绩
group by a.学生
order by 2
select a.学生,成绩排名次从低到高=count(*) from #tmp a,#tmp b where a.成绩 >=b.成绩
group by a.学生
order by 2
46:用Oracle發送mail
CREATE OR REPLACE PROCEDURE SENDSMTP2(sender IN VARCHAR2,
recipient IN STRINGLIST,
message IN STRINGLIST)
IS
mailhost VARCHAR2(30) := '192.168.125.25';
smtp_error EXCEPTION;
mail_conn utl_tcp.connection;
response varchar2(1000);
len pls_integer;
i integer;
j integer;
R1 list;
PROCEDURE smtp_command(command IN VARCHAR2,
ok IN VARCHAR2 DEFAULT '250')
IS
BEGIN
len:=utl_tcp.write_line(mail_conn, command);
response := utl_tcp.get_line(mail_conn);
-- luckyman dbms_output.put_line(ok||' '||response||' '||COMMAND);
IF (SUBSTR(response,1,3) <> ok) THEN
RAISE smtp_error;
END IF;
END;
BEGIN
mail_conn := utl_tcp.open_connection(mailhost, 25);
response := substr(utl_tcp.get_line(mail_conn), 1, 3);
-- luckyman dbms_output.put_line(response);
-- smtp_command('EHLO ' || mailhost,'250');
len:=utl_tcp.write_line(mail_conn, 'EHLO BNT29');
WHILE TRUE LOOP
response := utl_tcp.get_line(mail_conn);
-- luckyman dbms_output.put_line(response);
EXIT WHEN SUBSTR(RESPONSE,1,4) <>'250-';
END LOOP;
smtp_command('MAIL FROM: ' || sender,'250');
r1 := recipient.r1;
for i in r1.first..r1.last loop
smtp_command('RCPT TO: ' || r1(i),'250');
end loop;
smtp_command('DATA', '354');
r1 := message.r1;
for i in r1.first..r1.last loop
len:=utl_tcp.write_line(mail_conn,r1(i));
end loop;
len:=utl_tcp.write_line(mail_conn, '.');
response := utl_tcp.get_line(mail_conn);
smtp_command('QUIT', '221');
utl_tcp.close_connection(mail_conn);
END;
47:SQL code
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[product] varchar(1),[indate] datetime,[recorder] varchar(9))
insert [tb]
select 1,'a','2009-5-30','recorder1' union all
select 2,'b','2009-5-30','recorder2' union all
select 3,'c','2009-5-31','recorder1' union all
select 4,'d','2009-5-31','recorder1' union all
select 5,'e','2009-5-29','recorder3' union all
select 6,'f','2009-5-29','recorder4' union all
select 7,'g','2009-5-29','recorder5'
/*---创建字符连接函数---
create function F_Str(@col1 datetime)
returns nvarchar(1000)
as
begin
declare @S nvarchar(1000)
select
@S=isnull(@S+',','')+recorder
from
tb
where
datediff(dd,indate,@col1)=0
return @S
end */
---查询---
select
convert(varchar(10),indate,120) as indate,
count(1) as cnt,
dbo.f_str(indate) as recorder
from
tb
group by
indate
48:
create table tz2008_1_1(id int,name varchar(50))
insert into tz2008_1_1 select 1,'a'
create table tz2008_1_2(id int,name varchar(50))
insert into tz2008_1_2 select 2,'b'
create table tz2008_1_3(id int,name varchar(50))
insert into tz2008_1_3 select 3,'c'
declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select * from ['+name+']'
from sysobjects where xtype='u' and name like 'tz2008%'
exec(@sql)49:SQL code
日期的推算:(转邹老大的代码)
DECLARE @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)0本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
