MSSQL 新手學習要點

 sql分享

快捷鍵	功能	快捷鍵	功能
CTRL+A	全選	F3	重複查找
CTRL+C	複制	CTRL+H	替換
CTRL+V	粘貼	CTRL+SHIFT+L	使標明内容為小寫
CTRL+X	剪切	CTRL+SHIFT+U	使標明内容為大寫
CTRL+Z	撤消	CTRL+F5	分析查詢并檢查文法
TAB	增大縮進	F5	執行查詢
SHIFT+TAB	減小縮進	ALT+BREAK	取消查詢
CTRL+SHIFT+C	注釋代碼	F1	查詢分析器幫助
CTRL+SHIFT+R	删除注釋	SHIFT+F1	對所選SQL語句的幫助
CTRL+F	查找	F8	顯示/隐藏對象浏覽器

資料類型	名稱	取值範圍
整數	bigint、 int、 smallint、 tinyint	(±922億億)    8 (±21億)       4 (±32768)       2 (0-255)         1
位型	Bit	由0和1表示真、假。
貨币型	money、smallmoney	(±922萬億) (±21萬)，精确到萬分之一。
十進制	Decimal、Numeric	±1038-1，最大位數38位
浮點數	float、real	(±1.79E+308) (±3.40E+38)
日期時間	Datetime、smalldatetime	1753.1.1-9999.12.31,精确到3.33毫秒 1900.1.1-2079.12.31，精确到分鐘
單位元組字元	char/varchar/text	定/變長單位元組字元,最長8000；
Unicod字元	nchar / nvarchar / ntext	定/變長雙位元組字元，最長4000；
二進制資料	Binary/varbinary/image	定/變長二進制資料，最長8000；變長二進制資料
特殊類型	timestamp	SQL 活動的先後順序。
uniqueidentifier	全局唯一辨別

數學函數	名稱
ROUND(數字表達式, 小數位數)	四舍五入
FLOOR(數字表達式)	整數函數
CEILING(數字表達式)	整數函數
RAND()	随機函數
字元串、時間函數	名稱
ASCII碼	ASCII(字元表達式)
字元	CHAR(數字表達式)
統一代碼	UNICODE(字元表達式)
字元	NCHAR(數字表達式)
小寫	LOWER(字元表達式)
大寫	UPPER(字元表達式)
長度	LEN(字元表達式)
空格	SPACE(數字表達式)
截取左字串	LEFT(字元表達式,整數)
截取右字串	RIGHT(字元表達式,整數)
截取中間字串	SUBSTRING(字元表達式,起始點,n)
求子串位置	CHARINDEX(字元表達式1,字元表達式2,[開始位置])
剪去左空格	LTRIM(字元表達式)
剪去右空格	RTRIM(字元表達式)
重複字串	REPLICATE(字元表達式,n)
倒置字串	REVERSE(字元表達式)
轉換成字元串	convert(char(1),數值)
數值轉字串	STR(數字表達式)
現在日期時間	GETDATE()
年	YEAR(日期型表達式)
月	MONTH(日期型表達式)
日	DAY(日期型表達式)
日期 部分	DATEPART(格式串, 日期型表達式)
日期 加	DATEADD(格式串,數值,日期)
日期 差	DATEDIFF(格式串,日期1,日期2)
系統函數	說明
APP_NAME	目前會話的應用程式名稱
CURRENT_USER	目前的資料庫使用者。
USER_NAME	使用者資料庫使用者名。
SESSION_USER	會話使用者名
SYSTEM_USER	系統使用者名
HOST_ID	工作站辨別号。
HOST_NAME	工作站名稱。
ISDATE	是否為有效的日期。
ISNULL	是否為NULL。
ISNUMERIC	是否為一個有效的數字類型。
全局變量	說明
@@ERROR	最後執行的 T-SQL 語句的錯誤代碼。
@@TRANCOUNT	目前連接配接的活動事務數。
系統統計函數	說明
@@CONNECTIONS	連接配接次數。
@@CPU_BUSY	CPU 的工作時間
@@IDLE	閑置的時間
@@IO_BUSY	輸入和輸出的時間
@@TIMETICKS	傳回一刻度的微秒
@@PACK_SENT	發送的資料包數
@@PACK_RECEIVED	接受的資料包數
@@PACKET_ERRORS	發生的資料包錯誤
@@TOTAL_WRITE	寫入磁盤的次數
@@TOTAL_READ	讀取磁盤的次數
@@TOTAL_ERRORS	磁盤讀/寫錯誤次數

運算符	含義	BETWEEN	在…之間。	LIKE	比對(像)
AND	而且	EXISTS	存在	SOME	有些
OR	或者	ALL	所有(都)	IN	在…之内
NOT	否定	ANY	任一

 更多:http://www.52mvc.com/showtopic-1187.aspx

流程控制語句

1．RETURN

RETURN的作用是無條件傳回所在的批、存儲過程和觸發器。退出時，可以傳回狀态資訊。在RETURN語句後面的任何語句不被執行。

RETURN語句的文法形式：RETURN [整型表達式]

2．PRINT和RAISERROR

PRINT語句的作用是在螢幕上顯示使用者資訊。其文法形式為：

PRINT{’字元串’ | 局部變量| 全局變量}

RAISERROR語句的作用是将錯誤資訊顯示在螢幕上，同時也可以記錄在NT日志中。其文法形式為：RAISERROR(錯誤号|錯誤資訊, 錯誤的嚴重級别, 錯誤時的狀态資訊)。

3．複合語句(BEGIN...END)

其文法形式為： BEGIN

                  執行的SQL語句

END

4．CASE表達式

CASE [測試表達式]

{WHEN簡單表達式0 THEN 結果表達式0}[,...n]

[ELSE結果表達式n]

END

5．判斷語句(IF...ELSE)

文法：

IF 條件表達式

  SQL語句1

[ELSE

  SQL語句2]

6．循環語句(WHILE)

WHILE 條件表達式

  SQL語句|複合語句

7．注釋

多行注釋： 

單行 注釋：--

限制：限制名=限制類型_限制字段

ALTERTABLE stuInfo 添加主鍵限制（stuNo作為主鍵）

     ADD CONSTRAINT PK_stuNo PRIMARY KEY(stuNo)

ALTERTABLE stuInfo 添加唯一限制（因為每人的身份證号全國唯一）

    ADD CONSTRAINT UQ_stuID UNIQUE (stuID)

ALTERTABLE stuInfo 添加預設限制（如果位址不填，預設為“位址不詳”）

    ADD CONSTRAINT DF_stuAddress

          DEFAULT ('位址不詳') FOR stuAddress

ALTERTABLE stuInfo 添加檢查限制，要求年齡隻能在15－40歲之間

    ADD CONSTRAINT CK_stuAge

        CHECK(stuAge BETWEEN 15 AND 40)

ALTERTABLE stuMarks 添加外鍵限制（主表stuInfo和從表stuMarks建立關系，關聯字段為stuNo）

   ADD CONSTRAINT FK_stuNo         

       FOREIGN KEY(stuNo) REFERENCESstuInfo(stuNo)

系統存儲過程

1.      由系統定義，存放在master資料庫中

2.      類似C語言中的系統函數

3.      系統存儲過程的名稱都以“sp_”開頭或”xp_”開頭

4.      傳回0(成功),1(失敗)

系統存儲過程	說明
sp_databases	列出伺服器上所有資料庫
sp_helpdb	報告有關資料庫或者所有資料庫的資訊
sp_renamedb	更改資料庫的名稱
sp_tables	傳回目前環境下可查詢的對象清單
sp_columns	傳回某個表列的資訊
sp_help	傳回某個表的所有資訊
sp_helpconstraint	傳回某個表的限制
sp_helpindex	傳回某個表的所有索引
sp_stored_procedures	列出目前環境中的所有存儲過程
sp_password	修改登入帳戶的密碼
xp_cmdshell	運作DOS指令

語句	文法格式
建立資料庫*	CREATE DATABASE 資料庫名 [ ON (NAME = '邏輯檔案名',   FILENAME = '實體檔案名.mdf') ] [ LOG ON (NAME = '邏輯檔案名_log',   FILENAME = '實體檔案名_log.ldf') ]
删除資料庫*	DROP DATABASE 資料庫名
建立表*	CREATE TABLE 資料表名 ( 列名 資料類型 |列名 AS 計算清單達式   [ ,...n ] )
修改表	添加列	ALTER TABLE 表名 ADD 列名 列的描述
修改列	ALTER TABLE 表名    ALTER COLUMN 列名 列的描述
删除列	ALTER TABLE 表名    DROP COLUMN 列名,…
删除表*	DROP TABLE 表名
插入資料*	INSERT [INTO] 表名 [(列名1,…) ] Values (表達式1,…)
修改資料*	UPDATE 表名 SET 列名= 表達式 [WHERE 條件]
删除資料*	DELETE 表名 [WHERE 條件]

1、設定[教學成績管理資料庫]自動收縮。

alterdatabase 教學成績管理資料庫set auto_shrink on

2、用sp_dboption設定[教學成績管理資料庫]自動收縮。

Sp_dboption'教學成績管理資料庫', 'autoshrink', 'true'

SELECT語句文法格式

SELECT 字段清單   [INTO 目标資料表] FROM 源資料表或視圖，…   [WHERE 條件表達式]   [GROUP BY 分組表達式 [HAVING 搜尋表達式]]   [ORDER BY 排序表達式 [ASC]|[DESC]]   [COMPUTE 行聚合函數名1(表達式1)[,...n]      [BY 表達式 [,...n ]]]

子句	文法格式
SELECT	SELECT [ALL|DISTINCT][TOP n [PERCENT]] 列1 [,...n] 1. *   所有列 2. [{表名 | 視圖名 | 表别名}.]列名     指定列 3. 清單達式 [as] 别名 | 計算字段名=表達式 列别名 4. [ALL | DISTINC]     所有結果或去掉重複的結果 5. [TOP n [PERCENT]]   前n條(n%)的結果
FROM	1.FROM 表1 [[AS] 表别名1] | 視圖1 [[AS] 視圖别名1] [，...n] 2.FROM 表1 [inner] JION 表2 ON 條件表達式 3.FROM 表1 LEFT [OUTER] JOIN 表2 ON 條件表達式 4.FROM 表1 RIGHT [OUTER] JOIN 表2 ON 條件表達式 5.FROM 表1 FULL [OUTER] JOIN 表2 ON 條件表達式 6.FROM 表1 CROSS JOIN 表2  或  FROM 表1 ，表2
WHERE	WHERE 條件表達式 1. 表達式 比較運算符 表達式 2. 表達式 AND|OR 表達式      或：NOT 表達式 3. 表達式 [NOT] BETWEEN 表達式1 AND 表達式2 4. 表達式 [NOT] IN (表達式1, [,…表達式n]) 5. 表達式 [NOT] LIKE 格式串  通配符：% _ [ ] [^]
ORDER BY	ORDER BY 表達式1 [ ASC| DESC] [,…n]]
INTO	INTO 目标資料表
GROUP BY	[GROUP BY 分組表達式 [,...n ] [HAVING 搜尋表達式]]
COMPUTE	COMPUTE 行聚合函數名1(統計表達式1)[ ,...n]   [BY 分類表達式 [,...n ]]
UNION	查詢語句1 UNION  [ALL] 查詢語句2

視圖	定義	建立	CREATE VIEW 視圖名[(列名1 [,…n])] AS 查詢語句
修改	ALTER VIEW 視圖名[(列名1 [,…n])] AS 查詢語句
删除	DROP VIEW 視圖名[,…n]
資料操作	插入	INSERT [INTO] 表名\視圖名 [(列名1,…) ] Values (表達式1,…)
修改	UPDATE 表名\視圖名 SET 列名= 表達式 [WHERE 條件]
删除	DELETE 表名\視圖名[WHERE 條件]
查詢	SELECT 字段清單 FROM 資料表\視圖，…

建立索引：

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] 

    INDEX   index_name

     ONtable_name (column_name…)

      [WITHFILLFACTOR=x]

l       UNIQUE表示唯一索引，可選

l       CLUSTERED、NONCLUSTERED表示聚集索引還是非聚集索引，可選

l       FILLFACTOR表示填充因子，指定一個0到100之間的值，

l       該值訓示索引頁填滿的空間所占的百分比

功能	文法格式
自定義函數	建立	1.标量函數 CREATE FUNCTION [所有者].自定義函數名1 ([參數[…n])  RETURNS 傳回參數的類型   AS  BEGIN   函數體   RETURN 函數傳回的标量值 END 2.内嵌表值函數 CREATE FUNCTION [所有者].自定義函數名2([參數[…n]]  RETURNS TABLE AS  RETURN(SELECT查詢語句)
删除	DROP FUNCTION [所有者].自定義函數名
執行	1.标量函數：函數名自定義函數名1出現在表達式中； 2.内嵌表值函數：select 列名[,...] from自定義函數名2。

存儲過程	建立	CREATE PROCDURE 存儲過程名 [@參數 參數的資料類型] [OUTPUT] [,...n] AS  任意數量的T-SQL語句
删除	DROP PROCDURE 存儲過程名
執行	[EXECUTE] 存儲過程名 [參數1，…，參數n ]
觸發器	建立	CREATE TRIGGER 觸發器名 ON 表名或視圖名 { [FOR | AFTER] | INSTEAD OF } { [INSERT] [,] [UPDATE] [,] [DELETE]} AS  [ IF UPDATE(列名1) [{AND|OR} UPDATE(列名2)] [ ...n ] ] 任意數量的T-SQL語句
删除	DROP TRIGGER 觸發器名

遊标的使用方法

(1) 聲明遊标：declare 遊标名 cursor for select語句； (2) 打開遊标：open 遊标名； (3) 處理資料： l      移動目前行并讀取資料：fetch 遊标名 [into @變量名,…] l      删除目前行資料：delete from 表或視圖名 where current of 遊标名 l      修改目前行資料：update from 表或視圖名 set 列名=表達式,…                     where current of 遊标名 (4) 關閉遊标：close 遊标名； (5) 釋放遊标：deallocate 遊标名；

事務控制語句的使用方法

begin transaction   --  事務開始    ……          --  A組語句序列 save transaction 儲存點1    --定義儲存點    ……          --  B組語句序列 if @@error <> 0    rollback transaction 儲存點1   --復原到儲存點1 else    commit transaction  --送出A組語句，同時如果未復原B組語句則送出B組語句。

聲明遊标：

declare 遊标名 cursor

  [local |global]

 [forward_only | scroll]

  [static |keyset | dynamic | fast_forward]

  [read_only |scroll_locks | optimistic]

for select語句

  [for update[of 列名 [,...n]]]

l       遊标名：遊标命名必須符合辨別符規則，不能超過30 個字元。

l       select語句：定義結果集的标準select語句，且不許用compute、compute by、for browse和into子句。

l       local：遊标的作用域是局部的[建立它的批處理、存儲過程或觸發器]。

l       global：遊标的作用域是全局的。在由連接配接執行的任何存儲過程或批進行中，都可以引用該遊标名稱。該遊标僅在連接配接斷開時自動釋放。

l       forward_only 隻進。僅支援next。

l       Scroll 滾動支援：next、prior、first、last、absolute、relative

l       static 靜态 open時在tempdb建立臨時表複本儲存結果集。隻提取，不許修改

l       dynamic 動态記錄值、順序等在每次提取時都可能因其他使用者的更改而變動。不支援 absolute 提取選項。

l       keyset 鍵集 open時在tempdb建立keyset表，記錄結果集中每條記錄的關鍵字段值和順序。

l       fast_forward 快速向前 優化的 forward_only、read_only.與scroll、for_update、forward_only互斥

l       read_only 隻讀 在 update 或 delete 語句的 where current of 子句中不能引用遊标

l       scroll_locks 滾動鎖定 當滾動記錄指針提取目前記錄時，系統将會鎖定該行，確定遊标更新或删除的成功

l       Optimistic 樂觀 行自從被讀入遊标以來，如果已修改該行，嘗試進行的更新或删除将失敗

l       for update [of 列,...]]更新 指定可更新的列。預設所有列

打開遊标

文法格式：OPEN [GLOBAL] 遊标名

當遊标被打開時，行指針會指在第一行之前

u     打開遊标後,如果 @@error=0表示打開操作成功

u     打開遊标後,可用 @@cursor_rows傳回遊标記錄數

n       -m 遊标被異步填充.-m是鍵集中目前的行數

n       -1 遊标為動态。符合條件記錄的行數不斷變化

n       0 沒有符合的記錄、遊标沒打開、關閉或釋放

n       n 遊标已完全填充。n是在遊标中的總行數

資料處理文法格式：

fetch [[next|prior|first|last| absolute n |relative n]

  from] [global] 遊标名

  [into @變量名[, ...n ]]

功能：在查詢記錄集中移動指針并提取一行資料。

l       First  第一行；

l       Next   下一行；

l       Prior  上一行；

l       Last   最後一行；

l       absolute n n>0, 第一行開始正數的第n行；

n<0, 最後一行開始倒數的第n行；

l       relative n n>0, 從目前行開始正數的第n行；

n<0, 從目前行開始倒數的第n行。

注意：

l       查詢結果用into子句寫入局部變量，須先聲明局部變量的類型和寬度，且與select 語句中相應列的順序、類型和寬度一緻。

l       第一次執行fetch next，則将擷取遊标中的第一行資料。

l       打開遊标後第一次執行fetch prior，則得不到任何資料。

l       用@@fetch_status傳回執行FETCH操作之後，目前遊标指針的狀态。狀态值如下：

n       0 表示行已成功地讀取。

n       -1 表示讀取操作已超出了結果集。

n       -2 表示行在表中不存在。

文法格式：

update from 表或視圖

  set 列名=表達式,…

  wherecurrent of 遊标名

功能：修改目前行指定字段的值

delete from 表或視圖

  wherecurrent of 遊标名

功能：删除遊标名指定的目前行資料。

關閉遊标：

打開遊标時鎖定與其關聯的查詢結果集。

關閉完遊标後釋放與遊标關聯的查詢結果集。

文法格式：CLOSE [GLOBAL] 遊标名

釋放遊标：

釋放遊标，即釋放其占用系統資源。

文法格式：DEALLOCATE [GLOBAL] 遊标名

事務屬性：

原子性：對資料的修改，要麼都完成，要麼都取消。

一緻性：事務完成時，保持資料的一緻性、完整性。

隔離性：并行事務之間互相隔離。

持久性：事務完成後，對資料所做的所有修改就儲存到資料庫中。

(1)設定隐性事務開始模式：

l       set implicit_transactions on 啟動隐性事務模式；

l       set implicit_transactions off 關閉隐性事務模式。

顯式事務：是明确地用begin transaction 語句定義事務開始、用commit或 rollback 語句定義事務結束的事務。

隐式事務：是用set implicit_transactions on 不明顯地定義事務開始，用 commit 或 rollback 語句明顯地定義事務結束的事務。

(2)設定自動復原模式：

l       set xact_abort on 當事務中任一條語句産生運作時錯誤，整個事務将終止并整體復原；

l       set xact_abort off 當事務中語句産生運作時錯誤，将終止本條語句且隻復原本條語句

事務控制語句：

(1) begin transaction [事務名] 定義事務開始

(2) commit transaction [事務名] 送出事務使事務對資料庫的修改有效。

(3) rollback transaction [事務名] 復原事務使得事務對資料庫的修改無效。

資料庫備份：

BACKUP DATABASE teach

TO disk='c:\bak\teach_bak\teach.bak'

資料庫還原：

RESTORE DATABASE teach

 FROM disk='c:\bak\teach_bak\teach.bak'