[转载]精妙SQL语句介绍----(5)

By | 04月21日
Advertisement

查询第xxx行数据 T*stQB
假设id是主键: :x}!q Ko
select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id) Dz^;;i o
如果使用游标也是可以的 oQ&FpuwJ|
fetch absolute [number] from [cursor_name] W-.Ynk~
行数为绝对行数 OKy L 58
SQL Server日期计算 ]z?3~S_
a. 一个月的第一天 M$c0[1$^
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) 9,XUrH
b. 本周的星期一 #Qmm)M
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) CtO{IJG
c. 一年的第一天 :& K3B
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) "#]6O$j\
d. 季度的第一天 .yc&Y 5F
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) SN(&Xrqo![
e. 上个月的最后一天 EHP3B!}Z
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) =b 9Y&Aa
f. 去年的最后一天 LrP)o]
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) Mb`p]>8v
g. 本月的最后一天 G%dE@F]Xa
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) ejn\*6dgX
h. 本月的第一个星期一 VHI> gK3
select DATEADD(wk, DATEDIFF(wk,0, h*sUhs}px
dateadd(dd,6-datepart(day,getdate()),getdate()) Z>D}Y9@`
), 0) rZlmg7rQ\
i. 本年的最后一天 f8ZO8O 69$
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。 ZfCVJH1
获取表结构[把 'sysobjects' 替换 成 'tablename' 即可] R+U1 jP
SELECT CASE IsNull(I.name, '') c$h=gQ
When '' Then '' QhkNnB @6
Else '*' Q U(hE%D&
End as IsPK, #DX.i00}U
Object_Name(A.id) as t_name, Q`+l[BcP}
A.name as c_name, ZW #_
IsNull(SubString(M.text, 1, 254), '') as pbc_init, 6"z`_PJO
T.name as F_DataType, jJ1?[mBn?
CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '') tFrV e S
WHEN '' Then Cast(A.prec as varchar) | r *R"N
ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar) V>H8yVI
END as F_Scale, E 'dhnF
A.isnullable as F_isNullAble j28R\Z( `2
FROM Syscolumns as A Ob"v`)w
JOIN Systypes as T Pa]+ D
ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') ) . MOY7&7
LEFT JOIN ( SysIndexes as I Bl(5*^)Bk
JOIN Syscolumns as A1 :9LY^}Sn3
ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid m:O%
ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) ) C86A9X{"
LEFT JOIN SysComments as M vA2asO~:&@
ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 ) rm=[+Hm
ORDER BY A.Colid ASC %u`R' C
提取数据库内所有表的字段详细说明的SQL语句 yX= 3
SELECT %wjd>] r
(case when a.colorder=1 then d.name else '' end) N'表名', R87#=e=
a.colorder N'字段序号', K8h2u8AC
a.name N'字段名', skZak"m
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' zTj85pS#6E
end) N'标识', tReM{(sXZ
(case when (SELECT count(*) A,]*%^;
FROM sysobjects x0ND
WHERE (name in 7'EHB*)@
(SELECT name Rz-dG==
FROM sysindexes
WHERE (id = a.id) AND (indid in et],RoQ.^
(SELECT indid &[[5=W
FROM sysindexkeys 97?.bi/O
WHERE (id = a.id) AND (colid in %$Lk$2[1r
(SELECT colid %(U?3&a~.
FROM syscolumns GK L~QhS5
WHERE (id = a.id) AND (name = a.name))))))) AND #G&Jy/BSg
(xtype = 'PK'))>0 then '√' else '' end) N'主键', Y*d`-f1
b.name N'类型', C$2 )qQh
a.length N'占用字节数', sDFg^Ka
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', ]-)!nW
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', +5, l{U
(case when a.isnullable=1 then '√'else '' end) N'允许空', AsBtn-X>^
isnull(e.text,'') N'默认值', 9lWpDCWPr
isnull(g.[value],'') AS N'字段说明' }F-g#~#q0i
FROM syscolumns a (@UJh $a
left join systypes b I`C n E/V
on a.xtype=b.xusertype 4ile(D`
inner join sysobjects d "d&fPACI
on a.id=d.id and d.xtype='U' and d.name'dtproperties'
left join syscomments e "=~jBww
on a.cdefault=e.id JB1$/
left join sysproperties g vS(z'nV
on a.id=g.id AND a.colid = g.smallid $](t
order by object_name(a.id),a.colorder Oak:OIbF{
快速获取表test的记录总数[对大容量表非常有效] lBq.c
快速获取表test的记录总数: D4J(c3KrQ
select rows from sysindexes where id = object_id('test') and indid in (0,1) @2qn+t+y]
update 2 set KHXH=(ID+1)\2 2行递增编号 hu#%].8
update [23] set id1 = 'No.'+right('00000000'+id,6) where id not like 'No%' //递增 `,8 Fkd\
update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6) //补位递增 B 6Hx7 N
delete from [1] where (id%2)=1 奇数 }BAhkub
替换表名字段 6 %e)G{
update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/') where domurl like '%Upload/Imgswf/%' |lAJJTm~
截位 gPQq$8Ym5
SELECT LEFT(表名, 5) B
r^_o9g\;I
经过对SQLServer2000系统表的分析,写出了以下两个SQL语句。可以把这两个语句分别建为两个“视图”,方便查看用户数据表和字段的信息。 &XjK1sW
1、列出所有的用户数据表: 0zo]W4 J
SELECT TOP 100 PERCENT o.name AS 表名 M=\#~ab"
FROM dbo.syscolumns c INNER JOIN D l83
dbo.sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND F :a."F
o.name 'dtproperties' LEFT OUTER JOIN )Y]3[@_
dbo.sysproperties m ON m.id = o.id AND m.smallid = c.colorder SY2+*h&"W
WHERE (c.colid = 1) >z~j=t2
ORDER BY o.name, c.colid Yq[)Y;1Ko
2、列出所有的用户数据表及其字段信息: c!Q\(+
SELECT TOP 100 PERCENT c.colid AS 序号, o.name AS 表名, c.name AS 列名, "+tY3u3Q|
t.name AS 类型, c.length AS 长度, c.isnullable AS 允许空, zk6bJ~ZZ{
CAST(m.[value] AS Varchar(100)) AS 说明 Euf#:!lwb
FROM dbo.syscolumns c INNER JOIN T&ubYF_
dbo.sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND m|S 8OPc(
o.name 'dtproperties' INNER JOIN bl/szk}sO
dbo.systypes t ON t.xusertype = c.xusertype LEFT OUTER JOIN T6!$MJi^(
dbo.sysproperties m ON m.id = o.id AND m.smallid = c.colorder W6'`.bS
ORDER BY o.name, c.colid e/':2.

Similar Posts:

  • [转载]精妙SQL语句介绍----(2)

    说明: 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源) 3TN7{d) , SQL: ~QUOBI{ 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'),

  • MySQL高手篇:精妙SQL语句介绍

    如何从一位菜鸟蜕变成为高手,灵活使用的sql语句是必不可少的.本文收集了部分比较经典,常用的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.

  • SQL高手技能--精妙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 ta

  • 精妙SQL语句-希望对你有用

    下面的内容是多年前下载下来了,忘记原作者是谁了,在此贴出希望资源可以共享给每个人,不参与任何商业用途. 精妙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

  • 转载 精妙SQL

    本文转载,原文地址:http://www.iteye.com/topic/73854 下列语句部分是Mssql语句,不可以在access中使用. SQL分类: DDL-数据定义语言(Create,Alter,Drop,DECLARE) DML-数据操纵语言(Select,Delete,Update,Insert) DCL-数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK) 首先,简要介绍基础语句: 1.说明:创建数据库 Create DATABASE database-na

  • 精妙SQL语句学习

    在前人的基础上,我对比较优秀的sql语句进行了重新的编辑和整理,力求精短易学.希望大家可以举一反三,更好学习sql语句,如果有问题,还请翻阅相关书籍.下列语句部分是Mssql语句,不可以在access中使用. 注:搜集来自网络,为了方便查询,我将此文发在我的网站里http://www.ekuaiji.com/articleDetail.asp?id=529,随时更新. 欢迎大家发帖,接龙! SQL分类: DDL-数据定义语言(CREATE,ALTER,DROP,DECLARE) DML-数据操纵

  • 精妙Sql语句大回顾

    在前人的基础上,我对比较优秀的sql语句进行了重新的编辑和整理,力求精短易学.希望大家可以举一反三,更好学习sql语句,如果有问题,还请翻阅相关书籍.下列语句部分是Mssql语句,不可以在access中使用. 注:搜集来自网络,为了方便查询,我将此文发在这里,随时更新. SQL分类: DDL-数据定义语言(CREATE,ALTER,DROP,DECLARE) DML-数据操纵语言(SELECT,DELETE,UPDATE,INSERT) DCL-数据控制语言(GRANT,REVOKE,COMMI

  • [MySQL] 精妙SQL语句,基础,提升,技巧

    一.基础 1.说明:创建数据库 Create DATABASE database-name 2.说明:删除数据库 drop database dbname 3.说明:备份sql server - 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat' - 开始 备份 BACKUP DATABASE pubs TO testBack 4.说明:创建新表 cre

  • 精妙SQL语句整理(1)

    一.基础 1.说明:创建数据库 CREATE DATABASE database-name 2.说明:删除数据库 drop database dbname 3.说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat' --- 开始 备份 BACKUP DATABASE pubs TO testBack 4.说明:创建新表

  • 转载-用 SQL 语句还原数据库(with move 选项用法)

    学习sqlserver的数据备份与恢复 用 SQL 语句还原数据库(with move 选项用法) 1. 查看 SQL Server 2000 中 Northwind 数据库文件的逻辑文件名(logical file name)和物理文件路径(operation system file name): use Nothwindgo select name, filename, * from dbo.sysfiles name            filename--------------  -

Tags: