1、查询以汉字开头的记录
select * from tb_product where proname like N'[啊-座]%'
2、跨数据库查询(数据库和表名之间加两个点)
select * from [AAA]..TableA a
inner join [BBB]..TableB b on a.AcountID = b.ClientID
3、重置表的自增字段
--删除原表数据
truncate table tb_producttype
-- 重置表的自增字段
DBCC CHECKIDENT (tb_producttype,reseed,0)
要注意的就是重置之前得先删除原表的数据,如果是用truncate删除原表数据的话那么该表不能有外键关系。
4、按日期查询记录
数据库中存储日期的字段类型为datetime类型的时候,在按照日期来查询的时候就有问题,不能是
where riqi like '%2008-09%'
之类的来查询。。。必须得先把riqi列转化成字符串后方可查询,下面是按日期查询的例子:
select * from orders
where replace(CONVERT(char(10),OrderDate,111),'/','-') like '2008-09%'
其中“111”参数是把日期转成形如“2008/09/12” 的样子,所以得用replace函数把/转成-
5、统计数据库中每张表的记录数(sql2005)
select b.[name] 表名,max(a.rowcnt) 记录数
from sysindexes a
join sys.objects b on b.object_id=a.id
where b.type='U'
group by b.[name]
6、去除数字中多余的0
-- =============================================
-- Author: 牛腩
-- Create date: 2011年11月16日14时9分
-- Description: 格式化浮点数,去掉多余的0
-- =============================================
CREATE FUNCTION [dbo].[formatfloat]
(
-- Add the parameters for the function here
@numstr nvarchar(50)
)
RETURNS nvarchar(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result nvarchar(50)
-- Add the T-SQL statements to compute the return value here
select @Result=case when charindex('.',@numstr)>0 then
left(@numstr,charindex('.',@numstr)-1)+
case when convert(int,substring(@numstr,charindex('.',@numstr)+1,len(@numstr)))=0 then ''
else '.'+reverse(convert(nvarchar(20),convert(int,reverse(substring(@numstr,charindex('.',@numstr)+1,len(@numstr))))))
end
else @numstr end
-- Return the result of the function
RETURN @Result
END
7、查找表中的重复行
select * from tb_producttype where protname in(
select protname from tb_producttype group by protname having count(*)>1
)
8、删除表中重复数据
alter table tpPC add Col_Id int identity(1,1)--修改表增加一个自增列
delete from tppc where Col_Id not in(select max(Col_Id) from tppc group by Ip)--删除根据group by后的字段判断是否重复的数据
alter table tppc drop column Col_Id--删除新增的自增列
9、随机取出表中的10条记录
select top 10 * from tpPC order by newid()
10、取出不重复的记录
select distinct * from tppc
11、列出数据库中的所有表
select name from sysobjects where type='U'
12、数据库完整性检测
dbcc checkdb(lec_main)
dbcc checktable('表名',REPAIR_ALLOW_DATA_LOSS) --将数据库置于单用户状态,执行检查表
13、备份数据
use master
exec sp_addumpdevice 'disk','testBack','c:\mssqlBackup\MyNwind.dat' --创建备份文件
backup database pubs to testbck --开始备份
14、创建新表
create table tabname
(
Id int idetity(1,1) not null primary key,
[name] varchar(10)
)
15、分离、附加数据库
exec sp_detach_db lec_main --分离数据库
exec sp_attach_db 路径 --附加数据库
16、修改数据库名称
sp_renamedb 'old_name','new_name'
17、显示文章、提交人和最后回复时间
select a.title,a.username,b.addtime from table a,(select max(adddate) adddate from table where table.title=a.title) b
18、数据库分页语句
select top 10 b.* from(select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段=a.主键字段 order by a.排序字段
19、清空数据库日志
DUMP TRANSACTION 库名 WITH NO_LOG --清空日志
BACKUP LOG 库名 WITH NO_LOG --截断事务日志
DBCC SHRINKFILE(库名_log,1) --(1是文件号,可以通过这个语句查询到:select fileid,groupid,name from sysfiles where groupid=0 )
dlter database 数据库 modify file(name=数据库_log,maxsize=20) --修改日志文件增长
DBCC SHRINKDATABASE(库名)--收缩数据库文件
20、删除表数据
truncate table 表名
delete from 表名