asp精妙的SQL语句例子(5)
时间:2008-03-04 17:42:00
10.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))。
11.1.获取表结构[把 'sysobjects' 替换 成 'tablename' 即可]
SELECT CASE IsNull(I.name, '')
When '' Then ''
Else '*'
End as IsPK,
Object_Name(A.id) as t_name,
A.name as c_name,
IsNull(SubString(M.text, 1, 254), '') as pbc_init,
T.name as F_DataType,
CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '')
WHEN '' Then Cast(A.prec as varchar)
ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar)
END as F_Scale,
A.isnullable as F_isNullAble
FROM Syscolumns as A
JOIN Systypes as T
ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') )
LEFT JOIN ( SysIndexes as I
JOIN Syscolumns as A1
ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) )
ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) )
LEFT JOIN SysComments as M
ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 )
ORDER BY A.Colid ASC
11.2..提取数据库内所有表的字段详细说明的SQL语句
SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''
end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
FROM syscolumns a
left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder
11.3.快速获取表test的记录总数[对大容量表非常有效]
快速获取表test的记录总数:
select rows from sysindexes where id = object_id(‘test’) and indid in (0,1)
update 2 set KHXH=(ID+1)\2 2行递增编号
update [23] set id1 = 'No.'+right('00000000'+id,6) where id not like 'No%' //递增
update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6) //补位递增
delete from [1] where (id%2)=1
奇数
替换表名字段
update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/') where domurl like '%Upload/Imgswf/%'
截位
SELECT LEFT(表名, 5)
标签:sql,邮局,selsect,表
0
投稿
猜你喜欢
python中dump与dumps实现序列化
2023-01-13 22:46:16
Python实现将SQLite中的数据直接输出为CVS的方法示例
2022-10-01 00:05:32
python使用wxpy实现微信消息防撤回脚本
2023-08-22 21:21:58
详解Python基础random模块随机数的生成
2021-07-29 08:12:01
Django框架基础模板标签与filter使用方法详解
2022-10-25 18:14:43
SQL Server基础之行数据转换为列数据
2024-01-19 22:16:00
JavaScript状态模式及适配器模式使用讲解
2024-04-19 10:14:05
制作一个更漂亮一些的多选列表框
2007-10-16 13:04:00
微软建议的ASP性能优化28条守则(2)
2008-02-22 17:02:00
在SQL Server2000中恢复Master数据库
2008-01-05 14:05:00
numpy之多维数组的创建全过程
2023-06-22 03:58:03
Sqlserver2005日志文件太大如何减小
2024-01-25 10:48:53
详解Python中string模块除去Str还剩下什么
2021-08-25 12:48:19
MySQL查询出现1055错误的原因及解决方法
2024-01-13 04:05:54
mysql中文排序注意事项与实现方法
2024-01-15 17:18:55
pytorch 使用单个GPU与多个GPU进行训练与测试的方法
2022-04-04 10:39:07
Django+zTree构建组织架构树的方法
2023-08-13 06:17:54
Python中输出ASCII大文字、艺术字、字符字小技巧
2021-03-03 00:06:49
JS中的public和private对象,即static修饰符
2023-08-29 21:53:13
MySQL性能全面优化方法参考,从CPU,文件系统选择到mysql.cnf参数优化
2024-01-22 16:37:20