《SQL_Server數(shù)據(jù)庫(kù)原理及應(yīng)用教程》第八章：Transact-SQL語(yǔ)言結(jié)構(gòu)

1、 SQL Server 數(shù)據(jù)數(shù)據(jù)庫(kù)原理及應(yīng)用庫(kù)原理及應(yīng)用Transact-SQL語(yǔ)言結(jié)構(gòu)語(yǔ)言結(jié)構(gòu)第八章第八章Transact-SQL語(yǔ)言結(jié)構(gòu)語(yǔ)言結(jié)構(gòu) 8.1 Transact-SQL語(yǔ)言概述語(yǔ)言概述 8.2 Transact-SQL語(yǔ)言基礎(chǔ)語(yǔ)言基礎(chǔ) 8.3 Transact-SQL語(yǔ)言運(yùn)算符語(yǔ)言運(yùn)算符 8.4數(shù)據(jù)表的創(chuàng)建與維護(hù)數(shù)據(jù)表的創(chuàng)建與維護(hù) 8.5對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)表的操作對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)表的操作 8.6數(shù)據(jù)庫(kù)索引數(shù)據(jù)庫(kù)索引 8.7數(shù)據(jù)庫(kù)視圖數(shù)據(jù)庫(kù)視圖 8.8函數(shù)函數(shù) 8.9 SQL Server2000數(shù)據(jù)庫(kù)復(fù)制數(shù)據(jù)庫(kù)復(fù)制 8.1 Transact-SQL語(yǔ)言概述語(yǔ)言概述 Transact-S

2、QL語(yǔ)言特點(diǎn) ：(1)一體化的特點(diǎn)。 Transact-SQL語(yǔ)言集數(shù)據(jù)定義語(yǔ)言、數(shù)據(jù)操縱語(yǔ)言、數(shù)據(jù)控制語(yǔ)言和附加語(yǔ)言元素為一體。其中附加語(yǔ)言元素不是標(biāo)準(zhǔn)SQL語(yǔ)言的內(nèi)容，但是它增強(qiáng)了用戶對(duì)數(shù)據(jù)庫(kù)操作的靈活性和簡(jiǎn)便性，從而增強(qiáng)了程序的功能。(2) 兩種使用方式，統(tǒng)一的語(yǔ)法結(jié)構(gòu)。 兩種使用方式即聯(lián)機(jī)交互式和嵌入高級(jí)語(yǔ)言的使用方式。統(tǒng)一的語(yǔ)法結(jié)構(gòu)使得Transact-SQL語(yǔ)言可用于所有用戶的數(shù)據(jù)庫(kù)活動(dòng)模型，包括系統(tǒng)管理員、數(shù)據(jù)庫(kù)管理員、應(yīng)用程序員、決策支持系統(tǒng)管理人員以及許多其他類型的終端用戶。(3) 高度非過(guò)程化。 Transact-SQL語(yǔ)言一次處理一個(gè)記錄，對(duì)數(shù)據(jù)提供自動(dòng)導(dǎo)航；允許用戶在高

3、層的數(shù)據(jù)結(jié)構(gòu)上工作，可操作記錄集，而不是對(duì)單個(gè)記錄進(jìn)行操作；所有的SQL語(yǔ)句接受集合作為輸入，返回集合作為輸出，并允許一條SQL語(yǔ)句的結(jié)果作為另一條SQL語(yǔ)句的輸入。 另外，Transact-SQL語(yǔ)言不要求用戶指定對(duì)數(shù)據(jù)的存放方法，所有的Transact-SQL語(yǔ)句使用查詢優(yōu)化器，用以指定數(shù)據(jù)以最快速度存取的手段。(4) 類似于人的思維習(xí)慣，容易理解和掌握。Transact-SQL附加語(yǔ)言 Transact-SQL附加語(yǔ)言元素不是SQL的標(biāo)準(zhǔn)內(nèi)容，而是Transact-SQL語(yǔ)言為了編程方便而增加的語(yǔ)言元素。這些語(yǔ)言元素包括變量、運(yùn)算符、函數(shù)、流程控制語(yǔ)言和注釋等內(nèi)容。 本節(jié)主要介紹變量、流

4、程控制語(yǔ)言和注釋。1.變量 Transact-SQL語(yǔ)言允許使用兩種變量： 一種是用戶自己定一的局部變量，另一種是系統(tǒng)提供的全局變量。(1)局部變量 DECLARE 變量名 變量類型，變量名 變量類型(2)全局變量 全局變量通常存儲(chǔ)一些SQL Server的配置設(shè)定值和效能統(tǒng)計(jì)數(shù)據(jù)。用戶可在程序中用全局變量來(lái)測(cè)試系統(tǒng)的設(shè)定值或者Transact_SQL命令執(zhí)行后的狀態(tài)值。全局變量的名字前面要有兩個(gè)標(biāo)記符“” 。2.流程控制語(yǔ)句 (1)IFELSE語(yǔ)句語(yǔ)法格式：IF ELSE 條件表達(dá)式 流程控制語(yǔ)句(2)BEGINEND語(yǔ)句語(yǔ)法格式：BEGINEND流程控制語(yǔ)句(3)CASE語(yǔ)句CASE語(yǔ)句有

5、兩種格式：第一種：CASEWHENTHENWHENTHENELSEEND第二種：CASEWHENTHENWHENTHENELSEEND流程控制語(yǔ)句(4)WHILECONTINUEBREAK語(yǔ)句語(yǔ)法格式：WHILEBEGINBREAKCONTINUE命令行或程序塊END3. 注釋 在Transact-SQL語(yǔ)言中可使用兩種注釋符：行注釋和塊注釋。 行注釋符為“-”，這是ANSI標(biāo)準(zhǔn)的注釋符，用于單行注釋。 塊注釋符為“/*/”，“/*”用于注釋文字的開(kāi)頭，“*/”用于注釋文字的末尾。塊注釋符可在程序中標(biāo)識(shí)多行文字為注釋。SQL Server的查詢分析器 SQL Server的查詢分析器是一個(gè)圖形

6、用戶界面，用以交互地設(shè)計(jì)和測(cè)試 Transact-SQL 語(yǔ)句、批處理和腳本。 osql實(shí)用程序 SQL Server提供了一種在命令行中執(zhí)行查詢語(yǔ)句的實(shí)用程序，即osql實(shí)用程序。 osql 實(shí)用工具使用戶得以輸入 Transact-SQL 語(yǔ)句、系統(tǒng)過(guò)程和腳本文件。該實(shí)用工具通過(guò) ODBC 與服務(wù)器通訊。 使用osql實(shí)用程序時(shí)，直到輸入了執(zhí)行命令GO為止，才執(zhí)行相應(yīng)的Transact-SQL語(yǔ)句。 8.2 Transact-SQL語(yǔ)言基礎(chǔ)數(shù)據(jù)類型 1. 整數(shù)數(shù)據(jù)類型 整數(shù)型數(shù)據(jù)包括 bigint型 int型 smallint型 tinyint型。數(shù)據(jù)類型 2. 浮點(diǎn)數(shù)據(jù)類型 浮點(diǎn)數(shù)據(jù)類型

7、包括 real型 float型 decimal型 numeric型。 數(shù)據(jù)類型 3. 二進(jìn)制數(shù)據(jù)類型 二進(jìn)制數(shù)據(jù)類型用于存儲(chǔ)二進(jìn)制數(shù)據(jù)，包括 binary型 varbinary型 image型 數(shù)據(jù)類型 4. 邏輯數(shù)據(jù)類型 邏輯數(shù)據(jù)類型只有一種bit型。 bit數(shù)據(jù)類型只占用1個(gè)字節(jié)的存儲(chǔ)空間，其值為0和1。 數(shù)據(jù)類型 5. 字符數(shù)據(jù)類型 字符數(shù)據(jù)類型是使用最多的數(shù)據(jù)類型，它可以用來(lái)存儲(chǔ)各種字母、數(shù)字符號(hào)、特殊符號(hào)等。一般情況下，使用字符類型數(shù)據(jù)時(shí)，須在數(shù)據(jù)的前后加上單引號(hào)或雙引號(hào)。字符數(shù)據(jù)類型包括char型、nchar型、varchar型和nvarchar型。 數(shù)據(jù)類型6. 文本和圖形數(shù)據(jù)類

8、型 文本和圖形數(shù)據(jù)類型是用于存儲(chǔ)大量的非Unicode和Unicode字符以及二進(jìn)制數(shù)據(jù)的固定長(zhǎng)度和可變長(zhǎng)度數(shù)據(jù)類型，包括 text型 ntext型 image型。數(shù)據(jù)類型7. 日期和時(shí)間數(shù)據(jù)類型 日期和時(shí)間數(shù)據(jù)類型代表日期和一天內(nèi)的時(shí)間，包括 datetime型 smalldatetime型。 數(shù)據(jù)類型8. 貨幣數(shù)據(jù)類型 貨幣數(shù)據(jù)類型用于存儲(chǔ)貨幣或現(xiàn)金值，包括 money型 smallmoney型 在使用貨幣數(shù)據(jù)類型時(shí)，應(yīng)在數(shù)據(jù)前加上貨幣符號(hào). 數(shù)據(jù)類型9. 特定數(shù)據(jù)類型 SQL Server中包含了一些用于數(shù)據(jù)存儲(chǔ)的特殊數(shù)據(jù)類型，包括 timestamp型 uniqueidentifier

9、型。數(shù)據(jù)類型10. 用戶自定義數(shù)據(jù)類型 sysname數(shù)據(jù)類型是系統(tǒng)提供給用戶數(shù)據(jù)類型是系統(tǒng)提供給用戶的，便于用戶自定義的數(shù)據(jù)類型。該數(shù)的，便于用戶自定義的數(shù)據(jù)類型。該數(shù)據(jù)類型被定義為據(jù)類型被定義為NVARCHAR(128)，即，即它可以存儲(chǔ)它可以存儲(chǔ)128個(gè)個(gè)Unicode字符或字符或256個(gè)一個(gè)一般字符。般字符。 數(shù)據(jù)類型11. 新增數(shù)據(jù)類型 前面講到過(guò)SQL Server 2000新增了3種數(shù)據(jù)類型： bigint型 sql_variant型 table型 8.3 Transact-SQL語(yǔ)言運(yùn)算符1.算術(shù)運(yùn)算符2.賦值運(yùn)算符 3.按位運(yùn)算符 4.比較運(yùn)算 5. 邏輯運(yùn)算符 6. 一元運(yùn)

10、算符 7.運(yùn)算符的優(yōu)先順序 8.4 數(shù)據(jù)表的創(chuàng)建與維護(hù)1.創(chuàng)建數(shù)據(jù)表 CREATE TABLE database_name. owner .| owner. table_name ( | column_name AS computed_column_expression | ,.n ) ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT := column_name data_type DEFAULT constant_expression | IDENTITY ( seed , increment ) NOT FOR REPLICA

11、TION ROWGUIDCOL .n 數(shù)據(jù)表的創(chuàng)建與維護(hù)2.刪除數(shù)據(jù)表 刪除數(shù)據(jù)表指的是刪除表定義及該表的所有數(shù)據(jù)、索引、觸發(fā)器、約束和權(quán)限規(guī)范。語(yǔ)法格式如下： DROP TABLE table_name 其中table_name就是要?jiǎng)h除的表名。 數(shù)據(jù)表的創(chuàng)建與維護(hù)3.向數(shù)據(jù)表中插入記錄語(yǔ)法格式如下： INSERT INTO table_name WITH ( .n ) | view_name | rowset_function_limited ( column_list ) VALUES ( DEFAULT | NULL | expression ,.n ) | derived_table

12、 | execute_statement 數(shù)據(jù)表的創(chuàng)建與維護(hù)4.從數(shù)據(jù)表中刪除記錄 語(yǔ)法格式如下：DELETE FROM table_name WITH ( .n ) | view_name | rowset_function_limited FROM ,.n WHERE 數(shù)據(jù)表的創(chuàng)建與維護(hù)5 .修改和更新記錄 語(yǔ)法格式如下： UPDATEtable_name WITH ( .n ) SET column_name = expression | DEFAULT | NULL WHERE 8.5 對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)表的操作1.按條件查詢數(shù)據(jù) 2.使用通配符和子查詢 3.數(shù)據(jù)連接 4.數(shù)據(jù)排序 5.數(shù)

13、據(jù)統(tǒng)計(jì) 8.6 數(shù)據(jù)庫(kù)索引1.索引的概念 (1)通過(guò)創(chuàng)建唯一性索引，可以保證每一行數(shù)據(jù)的唯一性；(2)可以大大加快數(shù)據(jù)的檢索速度；(3)可以加速表和表之間的連接，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特有意義；(4)在使用ORDER BY和GROUP BY子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間；(5)通過(guò)使用索引，可以在查詢的過(guò)程中，使用優(yōu)化隱藏器，提高系統(tǒng)性能。 但是過(guò)多的增加索引就會(huì)耗費(fèi)資源。主要表現(xiàn)有：(1)創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間；(2)索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果建立聚簇索引，則需要的空間就更大了；(3)當(dāng)對(duì)表中

14、的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。數(shù)據(jù)庫(kù)索引2.索引類型 可以把索引分成兩種類型。一種是聚簇索引，這種類型的索引順序與數(shù)據(jù)表的物理順序相同；另一種是非聚簇索引，這種類型的索引順序與數(shù)據(jù)表的物理順序不同。 數(shù)據(jù)庫(kù)索引3.索引的創(chuàng)建與刪除 創(chuàng)建索引的語(yǔ)法格式如下：CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC ,.n ) WITH PAD_INDEX | FILLFACTOR = fillfactor | IGNOR

15、E_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB 刪除索引的語(yǔ)法格式如下： DROP INDEX table.index | view.index ,.n 數(shù)據(jù)庫(kù)索引4.索引的設(shè)計(jì) (1) 索引可以給查詢帶來(lái)多方面的好處。 SQL Server可以在同一個(gè)查詢中為一個(gè)表使用多個(gè)索引，并可以合并多個(gè)索引(使用聯(lián)接算法)，以便搜索關(guān)鍵字共同覆蓋一個(gè)查詢。 （2）SQL Server會(huì)自動(dòng)確定利用哪些索引進(jìn)行查詢，并且能夠在表被改動(dòng)時(shí)確保該表的所有索引都得到維護(hù)。 （3）設(shè)計(jì)索引時(shí)要考慮的其他準(zhǔn)則。 8.7 數(shù)據(jù)庫(kù)

16、視圖1.視圖的創(chuàng)建與刪除 創(chuàng)建語(yǔ)法格式如下：CREATE VIEW view_name(column,n)WITH ENCRYPTIONAS Select_statementWITH CHECK OPTION刪除法格式如下 DROP VIEW view ,.n 數(shù)據(jù)庫(kù)視圖2.視圖與數(shù)據(jù)修改 視圖本身并沒(méi)有數(shù)據(jù)，因?yàn)橐晥D是一張?zhí)摂M表，修改視圖的數(shù)據(jù)實(shí)際上就是修改導(dǎo)出視圖的基表數(shù)據(jù)。 如果滿足一些限制條件，可以通過(guò)視圖自由地插入、刪除和修改數(shù)據(jù)。 數(shù)據(jù)庫(kù)視圖3.查詢和視圖的比較 (1)存儲(chǔ)。視圖存儲(chǔ)為數(shù)據(jù)庫(kù)設(shè)計(jì)的一部分，而查詢則不是。當(dāng)設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)，可以出于下列原因?qū)⒁晥D包括在設(shè)計(jì)中：第一，一些數(shù)

17、據(jù)子集關(guān)系到許多用戶。由于每個(gè)視圖都存儲(chǔ)在數(shù)據(jù)庫(kù)內(nèi)，所以視圖建立特定的數(shù)據(jù)子集以供任何數(shù)據(jù)庫(kù)用戶使用。第二，視圖可以隱藏基表?？梢越顾杏脩粼L問(wèn)數(shù)據(jù)庫(kù)表，而要求用戶只能通過(guò)視圖操作數(shù)據(jù)。這種方法可以保護(hù)用戶和應(yīng)用程序不受某些數(shù)據(jù)庫(kù)修改的影響。(2)更新結(jié)果。對(duì)視圖和查詢的結(jié)果集更新限制是不同的。(3)排序結(jié)果。可以排序任何查詢結(jié)果，但是只有當(dāng)視圖包括TOP子句時(shí)才能排序視圖。(4)生成查詢計(jì)劃。查詢計(jì)劃是內(nèi)部策略，通過(guò)它數(shù)據(jù)庫(kù)服務(wù)器嘗試快速創(chuàng)建結(jié)果集。數(shù)據(jù)庫(kù)服務(wù)器可以在保存視圖后立即為視圖建立查詢計(jì)劃。但是對(duì)于查詢，數(shù)據(jù)庫(kù)服務(wù)器直到查詢實(shí)際運(yùn)行時(shí)才能建立查詢計(jì)劃。(5)參數(shù)設(shè)置?？梢詾椴樵儎?chuàng)

18、建參數(shù)，但不能為視圖創(chuàng)建參數(shù)。(6)加密?？梢约用芤晥D，但不能加密查詢。 8.8 函 數(shù) SQL Server提供了多種功能強(qiáng)大的函數(shù)，包括 算術(shù)函數(shù) 字符串函數(shù) 系統(tǒng)函數(shù) 日期時(shí)間函數(shù) 文本圖像函數(shù)等。 聚合函數(shù)1.聚合函數(shù) AVG函數(shù)(2)MAX函數(shù)(3) MIN函數(shù)(4)SUM函數(shù)(5)COUNT函數(shù) (6)STDEV函數(shù)(7)STDEVP函數(shù)(8)VAR函數(shù)(9)VARP函數(shù)算術(shù)函數(shù)2.算術(shù)函數(shù) 常用算術(shù)函數(shù) 字符串函數(shù) 3. 字符串函數(shù) 字符串函數(shù)對(duì)二進(jìn)制數(shù)據(jù)、字符串和表達(dá)式執(zhí)行不同的運(yùn)算。 1. 字符轉(zhuǎn)換函數(shù) (1)ASCII函數(shù) (2)CHAR函數(shù) (3)LOWER函數(shù)和UPPE

19、R函數(shù) (4)STR函數(shù) 2. 去空格函數(shù) LTRIM ( character_expression ) RTRIM ( character_expression )3. 取子串函數(shù) (1) LEFT函數(shù)和RIGHT函數(shù) (2) SUBSTRING函數(shù)4. 字符串比較函數(shù) (1) CHARINDEX函數(shù) (2) PATINDEX函數(shù)5. 字符串操作函數(shù) (1) QUOTENAME函 (2) REVERSE函數(shù) (3) REPLACE函數(shù) (4) SPACE函數(shù)系統(tǒng)函數(shù) 4.系統(tǒng)函數(shù) 系統(tǒng)函數(shù)用于獲取有關(guān)計(jì)算機(jī)系統(tǒng)、用戶、數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象的信息。系統(tǒng)函數(shù)可以讓用戶在得到信息后，使用條件語(yǔ)句，根據(jù)返回的信息進(jìn)行不同的操作。 日期和時(shí)間函數(shù) 5.日期和時(shí)間函數(shù) 日期和時(shí)間函數(shù)對(duì)日期和時(shí)間輸入值執(zhí)行操作，并返回一個(gè)字符串、數(shù)字值或日期和時(shí)間值。 注意：在使用日期和時(shí)間函數(shù)時(shí)，其日期值應(yīng)在1753年到9999年之間，這是SQL Server系統(tǒng)所能識(shí)別的日期范圍，否則會(huì)出現(xiàn)錯(cuò)誤。 文本和圖像函數(shù) 6.文本和圖像函數(shù) (1)PATINDEX函數(shù)(2) TEXTPTR函數(shù)(3) TEXTVALID函數(shù)8.9 S