SQL陳述句進階

sql陳述句三、技巧篇

1、1=1，1=2的使用，在SQL陳述句組合時用的較多

“where 1=1” 是表示選擇全部 “where 1=2”全部不選，
如：
if @strWhere !='' 
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere 
end
else 
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 
end



我們可以直接寫成

錯誤！未找到目錄項。
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定'+ @strWhere



2、收縮資料庫


--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收縮資料和日志
DBCC SHRINKDB
DBCC SHRINKFILE



3、壓縮資料庫

dbcc shrinkdatabase(dbname)



4、轉移資料庫給新用戶以已存在用戶權限

exec sp_change_users_login 'update_one','newname','oldname'
go



5、檢查備份集

RESTORE VERIFYONLY from disk='E:\dvbbs.bak'



6、修復資料庫

ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO



7、日志清除



SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT


USE tablename -- 要操作的資料庫名
SELECT @LogicalFileName = 'tablename_log', -- 日志檔案名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想設定的日志檔案的大小(M)

Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size 
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' + 
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)


DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) 
AND (@OriginalSize * 8 /1024) > @NewSize 
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog) 
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles 
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF



8、說明：更改某個表

exec sp_changeobjectowner 'tablename','dbo'



9、存盤更改全部表



CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS

DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)

DECLARE curObject CURSOR FOR 
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name

OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN 
if @Owner=@OldOwner 
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO @Name, @Owner
END

close curObject
deallocate curObject
GO


10、SQL SERVER中直接回圈寫入資料


declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end


案例：


有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:



Name score

Zhangshan 80

Lishi 59

Wangwu 50

Songquan 69

while((select min(score) from tb_table)<60)

begin

update tb_table set score =score*1.01

where score<60

if (select min(score) from tb_table)>60

break

else

continue

end



SQL陳述句資料開發篇



四、資料開發篇

1.按姓氏筆畫排序

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //從少到多



2.資料庫加密


select encrypt('原始密碼')
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密后密碼') = 1--相同；否則不相同 encrypt('原始密碼')
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


案例


例如1：一張表有一萬多條記錄，表的第一個欄位 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則在資料表中查找，這樣由于索引中的順序有可能和資料表中的不一致，這樣就導致查詢到的不是本來的欲得到的資料。



解決方案



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

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



例2：查詢表中的最后以條記錄，并不知道這個表共有多少資料,以及表結構。


set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)'

print @s exec sp_executesql @s



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 '



--連接遠程/局域網資料(openrowset/openquery/opendatasource)



--1、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

--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

--3、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.表名

select * from 本地表

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/97636.html

標籤：基礎類

上一篇：SQL陳述句

下一篇：python給一個特定區域打一個mask怎么做