SQL開發與資料庫管理筆記

原創： 丶平凡世界

文章連結：

https://mp.weixin.qq.com/s/Y9TmoHOyh7To7jUrMulvEw

一、開發管理篇

1.按姓氏筆畫排序

Select * From TableName
Order By CustomerName
Collate Chinese_PRC_Stroke_ci_as           

2.資料庫加密:

select encrypt('原始密碼')
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密後密碼') = 1--相同；否則不相同
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密後密碼') = 1--相同；否則不相同           

3.取回表中字段:

declare @list varchar(1000),
@sql nvarchar(1000)
select @list=@list+','+b.name
from sysobjects a,syscolumns b
where a.id=b.id and a.name='表A'
set @sql='select '+right(@list,len(@list)-1)+'
     from 表A' 
exec (@sql)           

4.檢視硬碟分區:

EXEC master..xp_fixeddrives
           

5.比較A,B表是否相等:

if (select checksum_agg(binary_checksum(*)) from A)
     =
    (select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'           

6.殺掉所有的事件探察器程序:

DECLARE hcforeach CURSOR GLOBAL FOR 
SELECT 'kill '+RTRIM(spid)
FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'           

7.記錄搜尋:

--開頭到N條記錄
Select Top N * From 表
--N到M條記錄(要有主索引ID)
Select Top M-N * From 表
Where ID in (Select Top M ID From 表)
Order by ID   Desc
--N到結尾記錄
Select Top N * From 表 Order by ID Desc           

例如：一張表有一萬多條記錄，表的第一個字段 RecID 是自增長字段， 寫一個SQL語句， 找出表的第31到第40個記錄。

select top 10 recid from A
where recid not  in
(select top 30 recid from A)           

分析：

如果這樣寫會産生某些問題，如果recid在表中存在邏輯索引。

select top 10 recid from A where

是從索引中查找，而後面的

select top 30 recid from A

則在資料表中查找，這樣由于索引中的順序有可能和資料表中的不一緻，這樣就導緻查詢到的不是本來的欲得到的資料。

解決方案

a，用order by select top 30 recid from A order by ricid 如果該字段不是自增長，就會出現問題

b，在那個子查詢中也加條件：select top 30 recid from A where recid>-1

9：擷取目前資料庫中的所有使用者表

select Name 
from sysobjects
where xtype='u' and status>=0           

10：擷取某一個表的所有字段

--方法一：
select name from syscolumns
where id=object_id('表名')

--方法二：
select name from syscolumns
where id in (
select id from sysobjects
where type = 'u' and name = '表名'
)           

11：檢視與某一個表相關的視圖、存儲過程、函數

select a.*
from sysobjects a, syscomments b
where a.id = b.id and b.text like '%表名%'           

12：檢視目前資料庫中所有存儲過程

select name as 存儲過程名稱
from sysobjects
where xtype='P'           

13：查詢使用者建立的所有資料庫

--方法一
select * from master..sysdatabases D
where sid not in(
select sid from master..syslogins
where name='sa')
--方法二
select dbid, name AS DB_NAME
from master..sysdatabases
where sid <> 0x01           

14：查詢某一個表的字段和資料類型

select column_name,data_type
from information_schema.columns
where table_name = '表名'           

15：不同伺服器資料庫之間的資料操作

--建立連結伺服器

exec sp_addlinkedserver
'ITSV ',
' ',
'SQLOLEDB ',
'遠端伺服器名或ip位址 '

exec sp_addlinkedsrvlogin
'ITSV ',
'false ',
null,
'使用者名 ',
'密碼 '
           

--查詢示例

select * from ITSV.資料庫名.dbo.表名           

--導入示例

select * into 表 from ITSV.資料庫名.dbo.表名           

--以後不再使用時删除連結伺服器

exec sp_dropserver 'ITSV ', 'droplogins '           

16：連接配接遠端/區域網路資料

A、openrowset

select * from openrowset(
'SQLOLEDB ', 'sql伺服器名 '; '使用者名 '; '密碼 ',資料庫名.dbo.表名)
           

--生成本地表

select * into 表 from openrowset(
'SQLOLEDB ', 'sql伺服器名 ';'使用者名 '; '密碼 ',資料庫名.dbo.表名)           

--把本地表導入遠端表

insert openrowset( 'SQLOLEDB ', 'sql伺服器名 '; '使用者名 '; '密碼 ',資料庫名.dbo.表名)

select *from 本地表           

--更新本地表

update b
set b.列A=a.列A
from openrowset( 
'SQLOLEDB ', 'sql伺服器名 '; '使用者名 '; '密碼 ',資料庫名.dbo.表名)as a
inner join 本地表 b on a.column1=b.column1           

B、openquery

--首先建立一個連接配接建立連結伺服器

exec sp_addlinkedserver
'ITSV ',
' ',
'SQLOLEDB ',
'遠端伺服器名或ip位址 '           

--查詢

select *
FROM openquery(
ITSV, 'SELECT * FROM 資料庫.dbo.表名 '
)           

insert openquery(
ITSV, 'SELECT * FROM 資料庫.dbo.表名 '
)
select * from 本地表           

update b
set b.列B=a.列B
FROM openquery(
ITSV, 'SELECT * FROM 資料庫.dbo.表名 '
) as a
inner join 本地表 b on a.列A=b.列A           

C、opendatasource/openrowset

SELECT   *
FROM   opendatasource(
'SQLOLEDB ',
'Data Source=ip/ServerName;
User ID=登陸名;Password=密碼 ' 
).test.dbo.roy_ta           

insert opendatasource(
'SQLOLEDB ',
'Data Source=ip/ServerName;
User ID=登陸名;
Password=密碼 '
).資料庫.dbo.表名           

二、SQL Server基本函數

1.字元串函數

DATALENGTH(Char_expr)

傳回字元串包含字元數,但不包含後面的空格

SUBSTRING(expression,start,length)

取子串，字元串的下标是從“1”，start為起始位置，length為字元串長度，實際應用中以len(expression)取得其長度

RIGHT(char_expr,int_expr)

傳回字元串右邊第int_expr個字元，還用left于之相反

ISNULL( check_expression , replacement_value )

如果check_expression為空，則傳回replacement_value的值，不為空，就傳回check_expression字元操作類

SET NOCOUNT{ON|OFF}

使傳回的結果中不包含有關受 Transact-SQL 語句影響的行數的資訊。如果存儲過程中包含的一些語句并不傳回許多實際的資料，則該設定由于大量減少了網絡流量，是以可顯著提高性能。SET NOCOUNT 設定是在執行或運作時設定，而不是在分析時設定。

SET NOCOUNT 為 ON 時，不傳回計數（表示受 Transact-SQL 語句影響的行數）。

SET NOCOUNT為 OFF 時，傳回計數

三、小常識

在SQL查詢中：from後最多可以跟多少張表或視圖：256

一個字段的最大容量是8000，而對于nvarchar(4000),由于nvarchar是Unicode碼。