sql server 常用操作

--分页
SELECT o.* FROM dbo.journal o
ORDER BY o.id DESC OFFSET 10 ROWS FETCH NEXT 40 ROWS ONLY

--TOP (100) PERCENT百分比
SELECT TOP (100) PERCENT ISBN FROM book_isbn_1 ORDER BY len(ISBN)

--去除左右空格
update 表 set 字段=ltrim(rtrim(字段))

--查询中文
select * from tb where name like '%[吖-做]%'

--patindex
select * from tb where patindex('%[吖-做]%',name)>0

INSERT INTO DB_Meta.dbo.book
SELECT *
FROM common.dbo.need_result

INSERT INTO mongo_journal_common
SELECT B.docid,dataProvider,identifier,url
FROM mongo_20161009_common as A ,mongo_journal_add as B
WHERE B.docid = A.docid

DELETE FROM A WHERE REPLACE(filename,'.pdf','') = '123'

Alter Table  表名  ALTER COLUMN  列名 类型(长度)

--删除表中多余的重复记录,只留有rowid最小的记录
delete from A where Id in (select Id from A group by Id having count(Id) > 1)and rowid not in (select min(rowid) from   A group by Id having count(Id)>1)

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

UPDATE B SET docid = A.docid FROM A,B WHERE A.filename = B.filename

--创建索引
CREATE INDEX index_name
ON table_name (column_name)

DELETE FROM 表名称 WHERE 列名称 = 值

--查询大于1
Select id,Count(*) From A Group By id Having Count(*) > 1

--查询大于1所有数据
select * from A
where id in (select id from A group by id having count(id) > 1)
        
--删除大于1
delete from A
where id in (select id from A group by id having count(*) > 1)    

--SUBSTRING
SELECT DISTINCT SUBSTRING(docid, 1, 2)  as 类型, SUBSTRING(docid, 15, 16) as 语言  FROM PDF_fulltext_common

--随机查询10条
SELECT top 10 * FROM [dbo].[standard] WHERE [batchNo] is not null order by newid()

select _id from mongo_level_null group by _id having count (_id) > 1

--删除A里docid等于B里docid
DELETE A FROM A,B WHERE A.docid=B.docid

--删除A里docid没有匹配的记录B里docid
DELETE A FROM A LEFT JOIN B ON A.docid=B.docid WHERE B.docid IS NULL