sqlserver中⼀些常⽤的查询语句
sqlserver 实现分页查询
select top ("+pageSize+") word from(select distinct(d_word)as word,row_number() over(order by id asc) as rownumber from dbo.zdbz_dict_all) as tb where rownumber>("+(i*pageSize)+")
将查询出来的数据导⼊到另外⼀个表中
insert into test_cs(word)
select distinct(d_word) as word from dbo.zdbz_dict_all
创建表
use yike_test
create table zdbz_nky_phrase_nlp(
id int PRIMARY KEY IDENTITY(1,1),
sid nvarchar(4000),
title nvarchar(4000),
keyword nvarchar(4000),
abstract nvarchar(4000),
phrase nvarchar(4000),
startpos int,
endpos int
)
mysql中的去重语句
delete disease as a from disease as a,
(
常用的sql查询语句有哪些select * from disease group by name having count(1)>1) as b
where a.name=b.name and a.id > b.id;
）
sqlserver中查询字符串中含有%
select * st_cs where word like '%%c%%' escape 'c'
select * from dbo.zdbz_dict_all where t_word like '%[%]%'
表⽰字符串中含有%就⾏
select * st_cs where word like '%c%' escape 'c'
查询的结果为所有以%结尾的数据
select * st_cs where word like 'e%%' escape 'e'
查询的结果为所有以%开始的数据
sql语句实现数据库中的两个表数据之间的更新
update dbo.zdbz_dict_all set origin=b.word from dbo.zdbz_dict_all as a
,igin as b where a.id=b.uri
基于数据库表建⽴索引
CREATE NONCLUSTERED INDEX IX_origin --创建⼀个⾮聚集索引
ON dbo.zdbz_dict_all(origin)  --为dbo.zdbz_dict_all表的origin字段创建索引
WITH FILLFACTOR = 30 --填充因⼦为30%
如果将set statistics io 设置为on，则在执⾏SQL语句时将会显⽰磁盘活动的统计信息。如果将set statistics
io设置为off，则不显⽰统计信息。
set statistics io on
select * from
stuinfo--执⾏语句
set statistics
io off
基于SQLServer性能测试
create table table1
(
ID int identity(1,1),
UserID int,
OtherCol1 varchar(300),
OtherCol2 varchar(300)
)
create clustered index index_id on table1(id)
-
-清理⼀下表
truncate table table1
declare @i int
set @i=1
while (@i<2000000)
begin
insert into table1 values(substring(cast( RAND() as varchar),3,3),CAST(@i as varchar),CAST(@i as varchar));    set @i=@i+1
end
select count(1)from dbo.table1
select GETDATE()
select * from table1 where UserID='666'
create  index index_userid on table1(userid)
select GETDATE()
drop index index_userid on table1

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。