[MS SQL]高階SQL總結

--------------------建立檔案包
exec sp_configure'show advanced options',1
RECONFIGURE
exec sp_configure'xp_cmdshell',1
RECONFIGURE
exec xp_cmdshell 'md d:\project'
go
use master 
go
--建立bbsDB資料庫
if exists(select name from sysdatabases where name='bbsDB')
	drop database bbsDB
go
create database bbsDB
on
(
	name='bbsDB',
	filename='d:\project\bbsBD.mdf',
	size=10mb,
	filegrowth=15%
)
log on
(
	name='bbsDB_log',
	filename='d:\project\bbsBD_log.ldf',
	size=3mb,
	maxsize=20mb,
	filegrowth=15%
)
--建立 bbsUsers 表
use bbsDB
go
if exists(select name from sysobjects where name='bbsUsers')
	drop table bbsUsers
go
create table bbsUsers
(
	UID int identity(1,1) not null, --使用者編號
	Uname varchar(15) not null,		--使用者名稱
	Upassword varchar(10) not null,	--密碼
	Uemail varchar(20),				--郵箱
	Usex bit	not null,			--性別
	Uclass int ,					--使用者等級
	Uremark varchar(20),			--備註資訊
	UregDate datetime  not null,	--註冊日期
	Ustate int,						--狀態
	Upoint int						--使用者積分
)
go
--主鍵
use bbsDB
go
if exists(select name from sysobjects where name='PK_UID')
	alter table bbsUsers 
	drop constraint PK_UID
go
alter table bbsUsers
add constraint PK_UID primary key (UID)
go
--預設密碼值  888888
if exists(select name from sysobjects where name='DF_Upassword')
	alter table bbsUsers
	drop constraint DF_Upassword
go
alter table bbsUsers
add constraint DF_Upassword default (888888) for Upassword 
go
--密碼約束  必須大於6個字元
if exists(select name from sysobjects where name='CK_Upassword')
	alter table bbsUsers
	drop constraint CK_Upassword
go
alter table bbsUsers
add constraint CK_Upassword check(Len(Upassword)>=6)
GO

--預設日期  擷取當前時間 getdate() 方法
if exists(select name from sysobjects where name='DF_UregDate')
	alter table bbsUsers
	drop constraint DF_UregDate
go
alter table bbsUsers
add constraint DF_UregDate default (getdate()) for UregDate
go
--郵箱資料約束
if exists(select name from sysobjects where name='CK_Uemail')
	alter table bbsUsers 
	drop constraint CK_Uemail
go
alter table bbsUsers
add constraint CK_Uemail  check (Uemail like '%@%')
go
--版塊編號，外來鍵，引用bbsSection 表的主鍵 SID
use bbsDB
if exists (select name from sysobjects where name='FK_TsID')
	alter table bbsToic
	drop constraint FK_TsID
go
alter table bbsTopic
add constraint FK_TsID foreign key (TsID) references bbsSection(SID)
go
--唯一約束
if exists (select name from sysobjects where name='UQ_stuID')
	alter table bbsToic
	drop constraint UQ_stuID
go
alert table bbsTopic
add constraint UQ_stuID unique (stuID)


------------------------------------------------------------------------
第一曲：新增SQL登入使用者

	use master
	--go
	--if exists(select * from syslogins where name='zhouxuan')
	--	drop loin 'zhouxuan'
	--go
	exec  sp_addlogin 'zhouxuan' ,'密碼'
	go

第二曲:分配指定資料庫

	use 需要分配的資料庫
	go 
	exec sp_grantdbaccess '分配的使用者' [,'你的登入賬號（必須有許可權）'] 

第三曲：分配許可權
 
	grant 許可權 [on 表名] to 資料庫使用者
	許可權：insert ,delete, update, select, create table
-------------------------------------------------------------
區域性變數：
	宣告：
		declare @name varchar(8)
		declare @seat int 
		不可   declare @name ,@seat int
	賦值：
		set @name=value
		select @name = name from . where.
		不可 +=
	兩種賦值的區別：
		1.select 可以同時對多變數送至
		2.set 必須保證查詢語句返回單行單列
		  select當結果中返回多行時，自動將最後一行此欄位的知送給變數
		3.set當查詢語句一行記錄都沒有返回，為變數送空值。
		  select 當查詢語句一行記錄都沒有返回，保持原值
	declare @stuSeat int
	set @stuSeat =10
	select @stuSeat=stuseat from stuinfo where stuname='李文'
	print @stuSeat
	
全域性變數
	
	@@error 最後一個T-SQL語句的錯誤號
	@@identity 最後一次插入的標示值
	@@language  當前使用的語言的名稱
	@@max_connections 可以建立的同時連線的最大數目
	@@rowcount 受上一個SQL語句影響的行數
	@@servername 本地伺服器名稱
	@@servicename 該技術機上的sql服務的名稱
	@@timeticks 當前技術機上每刻度的微秒數
	@@version sqlserver的版本資訊
	set nocount [on\off ]不顯示/顯示  
	distinct 取消重複項  SELECT DISTINCT ProductID FROM Production.ProductInventory
	日期部分  	縮寫  
	年份 		yy、yyyy 
 	季度 		qq、q 
 	月份 		mm、m 
 	每年的某一日 	dy、y 
 	日期 		dd、d 
 	星期 		wk、ww
	工作日		dw
	小時 		hh 
	分鐘 		mi、n 
	秒 		ss、s 
 	毫秒 		ms 
	dateadd(dd,3,需要新增的時間)  新增時間   
	datediff(dd,當前時間,其他時間) 比較兩個時間的間隔
	datepat(ss,getdate())   返回表示指定日期的指定日期部分的整數
輸出語句：
	print 區域性變數或字串
	select 區域性變數 as 自定義列名
	
控制語句：
	if(@sumError>0)
  		begin
		 	print'交易失敗'
  		end
	else 
  		begin
    			print'交易成功'
 		end

	
	while(1=1)
	{
		begin
			if(@n>0)
				update
			else
				break
	}
	

	select 是否通過=case
			when writtenExam>=60 and LabExam>=60 then '通過'
			else '未通過'
			end
	from stuInfo where id=1
	
GO 關鍵字標誌著批處理的結果
----------------------------------------------------------------------------------
資料轉換
convert(varchar(6),@send)
convert(varchar(15),getdate(),111) 108 109 轉換時間格式

-----------------------------------------------------------------------------------
子查詢 in \not in
	select * from stuinfo where stuno in(select stuno stuMarks where writtenExam>60)

-----------------------------------------------------------------------------------
生成新表並儲存資料
if exists(select * from sysobjects where name='newTable')
	drop table newTable
go
select ....into newTable ...
-------------------------------------------------------------------------------------
事務：
	事務是作為單個邏輯工作單元執行的一系列操作，要麼都執行，要麼都不執行
	
	4個屬性：
		1.原子性：事務是一個完整的操作，事務的各種元素是不可分的。
		2.一致性：當事務完成時，資料處於一致狀態
		3.隔離性：對資料進行修改的所以併發事務是彼此隔離的
		4.永續性：事務完成後，它對系統的影響是永久性的，該修改即使出現系統故障，也
			保持一致
	具體操作例子

	use bankDB
	go
	    begin tran --開始事務
		declare @sumError int
		set @sumError=0
		update bank set currentMoney=currentMoney+500 where customerName='張三'
		set @sumError=@sumError+@@error
		declare @ka char(10)
		select @ka=cardID from bank where customerName='張三'
		insert into transInfo(cardID, transType, transMoney, transDate)values(@ka,'存入',500,Getdate())
		set @sumError=@sumError+@@error
		if(@sumError>0)
  			begin
   			   	rollback tran --回滾事務
   	 			print'存錢失敗，請確認輸入無異常'
 	 		end
		else
  			begin
   	 			commit tran  --提交事務
    				print '存錢成功，請查詢餘額'
  	     end
	 go
  
	自動提交事務：這是SQLServer的預設模式，它將每單條的SQL語句為一個事務，如果執行成功，
		則自動提交，如果錯誤，則自動回滾
	
	set implicit transactions on/off 隱式事務


-------------------------------------------------------------------------------------------
索引：
	索引：是SQLServer編排資料的內部方法，為SQLServer提供一個方法來編排查詢資料的路徑
	索引的作用：使資料庫程式無須對整個表進行是掃描，就可以在其中找到所需要的資料
	索引頁:資料庫中儲存索引的資料頁，索引頁存放檢索資料行的關鍵字頁以及該資料行的地址指標
	唯一索引：唯一索引不允許兩行具有相同的索引值
		提示：建立了唯一約束，將自動建立唯一索引，儘管唯一索引有助於找到資訊，但為了
			獲得聚佳效能，建議使用主鍵約束
	主鍵索引：主鍵，唯一標示
	索引分為：
		1、聚集索引：包括主鍵	
			表中各行的物理順序與鍵值的邏輯（索引）順序相同，表只能包含一個聚集索引
			例如：漢語字典預設按拼音排序，拼音字母a b c d e...z 就是索引的邏輯順序
			      而頁碼1.2.3.4 就是物理順序
		2、非聚集索引：
			表中各行的物理順序與鍵值的邏輯順序不匹配，聚集索引比非聚集索引有更快速
			的資料訪問速度
			例如：按筆畫排序的索引就是非聚集索引，"1"畫的字對應的頁碼可能比"3"畫的
			      字對應的頁碼大
	建立使用索引
	
	use stuDB
	go
	if exists(select name from sysindexes where name='IX_stuMarke_writtenExam')
		where name='IX_stuMarke_writtenExam'
		drop index stuMarks.IX_stuMarke_writtenExam   --刪除索引
	go
	---------建立非聚集索引，填充因子30%
	create nonclustered index IX_stuMarke_writtenExam on stuMarks(writtenExam)
		with fillfactor=30    ---填充因子%
	go
	
	---------建立時可選擇 是否唯一  是否為聚集非聚集
	[unique] [clustered | nonclustered]
	
	使用索引
	select * from stuMarks	with (index(IX_stuMarke_writtenExam)) 
		where writtenExam between 60 and 90 
----------------------------------------------------------------------------------------
SQL語句建立檢視
	use studb
	go
	if exists(select * from sysobjects where name='view_name')
		drop view view_name
	go
	create view view_name
		as 
		<select 語句>
	go
	select * from view_name

--------------------------------------------------------------------------------------
常用的系統儲存過程
	sp_databases	  列出伺服器上的所有資料庫
	sp_helpdb	  報告有關指定資料庫或所有資料庫的資訊
	sp_renamedb	  更改資料庫名稱
	sp_tables 	  返回當前環境下可查詢的物件的列表
	sp_defaultdb '使用者','資料庫'  登陸時顯示的預設資料庫
	sp_columns  	  返回某個表列的資訊
	sp_help		  查詢某個表的所有資訊
	sp_helpconstraint 檢視某個表的約束
	sp_helpindex   	  檢視某個表的索引
	sp_stored_procedures 列出當前環境中的所有儲存過程
	sp_password	  新增或修改登陸使用者的密碼
	sp_helptext	  顯示預設值，未加密的儲存過程，發器或檢視的實際檔案
	
	
	呼叫
	exec sp_databases	 ---列出伺服器上的所有資料庫
	exec sp_renamedb 'db1','db2' 	 ---把資料庫名db1改為db2
	use  stuDB
	go
	exec sp_tables --返回當前環境下可查詢的物件的列表
	exec sp_columns stuInfo  --檢視錶stuInfo中的列的資訊
	exec sp_help  stuInfo	--檢視錶 stuInfo的所有資訊
	exec sp_helpconstraint stuInfo  --檢視錶stuInfo的約束
	exec sp_helpindex stuMarks  ---檢視錶stuMarks的索引
	exec sp_helptext 'view_stuInfo_stuMarks'  --檢視檢視的語句文字
	exec sp_stored_procedures   --返回當前資料庫中的儲存過程列表
	呼叫DOS命令
	exec sp_configure'show advanced options',1
	RECONFIGURE
	exec sp_configure'xp_cmdshell',1
	RECONFIGURE
	exec xp_cmdshell 'md d:\project'
-----------------------------------------------------------------------------------------
儲存過程：
	use stuDB
	go
	if exists(select * from sysobjects where name='proc_stu')
		drop procedure proc_stu
	go
	
	create procedure proc_stu
		@writtenpass int, --輸入引數：考試及格線
		@labPass int,	--輸入
		@name varchar(50) output --- 設定外面接收的引數
	   as 
		print '-----------------------'
		
	go
	
	---呼叫儲存過程------------------------------
	declare @test varchar(50)
	exec proc_stu 60,55,@test output
	--或者
	exec proc_stu @labPass=55,@writtenpass=60，@name=@test output
	注意：
		1.初始化化引數為空就直接  ..=null
	          判斷是否為空就  if .. is null
	     	2.注意傳參
	引數
	
錯誤處理
	raiserror('及格線錯誤,請指定0-100之間的分數，統計退出',16,1)        1-16
	錯誤的嚴重級別大於10，將自動設定系統全域性變數 @error 為非零