第 2 章 SQL語言編程基礎,本章學習目標,l了解Transact-SQL語言起源、組成 l掌握Transact-SQL編程基礎知識 l 綜合運用變量、表達式、函數(shù)及流控語句等編寫應用程序代碼,2.1 Transact-SQL語言概論 2.1.1 Transact-SQL語言簡介 SQL的含義為結(jié)構化查詢語言，即Structured Query Language，是在關系型數(shù)據(jù)庫系統(tǒng)中被廣泛采用的一種語言形式。SQL語言能夠針對數(shù)據(jù)庫完成定義、查詢、操縱和控制功能，是關系型數(shù)據(jù)庫領域中的標準化查詢語言。微軟公司在SQL語言的基礎上對其進行了大幅度的擴充，并將其應用于SQL Server服務器技術中，從而將SQL Server所采用的SQL語言稱為Transact-SQL語言。目前SQL語言的最新標準為SQL-92，由美國國家標準局制定，包含了語法標準以及對SQL關鍵字的定義。 在使用SQL語言的過程中，用戶不需要知道數(shù)據(jù)庫中的數(shù)據(jù)是如何定義和怎樣存儲的，只需要知道表和列的名字，即可從表中查詢出需要的信息。 SQL語言特別適合于Client/Server體系結(jié)構，客戶用SQL語句發(fā)出請求，服務器處理用戶發(fā)出的請求，客戶與服務器之間任務劃分明確。但SQL語言本身不是獨立的程序設計語言，不能進行屏幕界面設計和控制打印等，因此通常將SQL語言嵌入到程序設計語言（如Visual Basic、C語言、Delphi等）中使用。,SQL語言由三部分組成：數(shù)據(jù)定義語言（Data Definition Language，簡稱DDL）、 數(shù)據(jù)操縱語言（Data Manipularion Language，簡稱DML）、 數(shù)據(jù)控制語言（Data Control Language，簡稱DCL）。對上述三種語言的描述如表2-1所示。 表2-1 SQL語言的組成,2.1.2 Transact-SQL語法規(guī)則 1Transact-SQL語法規(guī)則 為方便用戶更好地掌握與使用Transact-SQL語言，需要首先向讀者介紹Transact-SQL中涉及到的語法規(guī)則，如表2-2所示：,表2-2 Transact-SQL的語法規(guī)則,2數(shù)據(jù)庫對象的引用規(guī)則 一般情況下，數(shù)據(jù)庫對象的引用都由以下四部分所組成: l server_name 用于指定所連結(jié)的本地服務器或遠程服務器的名稱。 l database_name 用于確定在服務器中當前狀態(tài)下所操作的數(shù)據(jù)庫名稱 l object_name 在數(shù)據(jù)庫中被引用的數(shù)據(jù)庫對象名稱 l owner_name 表示數(shù)據(jù)庫對象的所有者,所以一個完整的數(shù)據(jù)對象引用的表示方法應該為：server_name.database_name.owner_name.object_name，其中，服務器名稱、數(shù)據(jù)庫名稱以及所有者都可以省略，所以下列對象的表示方法都是合法的：server_name.database_name.owner_name.object_nameserver_name.database_name.object_nameserver_name. owner_name.object_nameserver_name.object_namedatabase_name.owner_name.object_namedatabase_name.object_nameowner_name.object_nameobject_name例如：shutupandcode.xscj.dbo.班級表、shutupandcode.xscj.dbo.課程信息表。2.1.3 Transact-SQL語法元素在大部分Transact-SQL語句中，都包含諸如標識符、數(shù)據(jù)類型、函數(shù)、表達式、運算符、注釋以及保留關鍵字等語法元素。,1 識符 用于標識數(shù)據(jù)庫對象的名稱，這些對象包括服務器、數(shù)據(jù)庫及相關對象（如表、視圖、列、索引、觸發(fā)器、過程、約束、規(guī)則等）。標識符在定義對象時進行命名，當需要使用某個對象時可以通過引用該對象的名稱來完成。 標識符可劃分為常規(guī)標識符與分隔標識符兩類，其中常規(guī)標識符的命名規(guī)則如下： l 第一個字符必須由字母a-z 、A-Z，以及來自其他語言的字母字符或者下劃線_、#構成，其中表示局部變量或參數(shù)，以#開頭的標識符表示全局臨時對象，以開始的標識符表示全局變量，也稱為配置函數(shù)。 l 在定義標識符時，不能占用Transact-SQL 的保留字，例如不能將Table、View、Index等定義為一個標識符。 l 在標識符中不能含有空格，并且標識符中的字符數(shù)量不能超過128個。 如果定義的標識符不符合上述規(guī)則時，即被稱為分隔標識符，需要使用雙引號”或方括號對其進行分割。例如：SELECT * FROM my table,1 數(shù)據(jù)類型 Transact-SQL中的基本數(shù)據(jù)類型如表2-3表所示： 表2-3 Transact-SQL基本數(shù)據(jù)類型,（1）整型數(shù)據(jù)整數(shù)數(shù)據(jù)類型是最常用的數(shù)據(jù)類型之一，由正整數(shù)和負整數(shù)所組成，使用 bigint、int、smallint和tinyint數(shù)據(jù)類型進行存儲。bigint 數(shù)據(jù)類型可存儲的數(shù)字范圍比 int 數(shù)據(jù)類型廣。int 數(shù)據(jù)類型比 smallint 數(shù)據(jù)類型的存儲范圍大，而 smallint 的數(shù)值范圍又比 tinyint 類型大。,l bigint：可以存儲-263到263-1之間的數(shù)字，占據(jù)8個字節(jié)存儲空間。 l int：可以存儲從-231到231-1范圍之間的所有整數(shù)，占據(jù)4字節(jié)存儲空間。 l smallint：可以存儲從-215到215-1范圍之間的所有整數(shù)，占據(jù)2字節(jié)存儲空間。 l tinyint：可以存儲從0到255范圍之間的所有正整數(shù)。,（2）浮點數(shù)據(jù)類型主要包括Real、Float、Decimal和numeric四種類型。,l Real：用于存儲7位小數(shù)的十進制數(shù)據(jù)，所能夠表示的范圍為-3.40E+38到1.79E+38。 l Float：可以精確到第15位小數(shù)，數(shù)據(jù)范圍為-1.79E-308到1.79E+308。 l Decimal：提供小數(shù)所需要的實際存儲空間，可以存儲2到17個字節(jié)的從-1038-1到1038-1之間的數(shù)值。 l numeric：與Decimal數(shù)據(jù)類型幾乎完全相同，區(qū)別是在表格中，只有numeric型的數(shù)據(jù)可以帶有identity關鍵字的列。,（3）字符數(shù)據(jù)類型SQL Server提供了三種字符數(shù)據(jù)類型，分別是Char、Varchar和Text。,l Char：最長可以容納8000個字符，并且每個字符占用一個字節(jié)的存儲空間。使用Char數(shù)據(jù)類型定義變量時，需要指定數(shù)據(jù)的最大長度。如果實際數(shù)據(jù)的字符長度小于指定長度時，剩余的字節(jié)用空格來填充。如果實際數(shù)據(jù)的長度超過了指定的長度，則超出部分將會被刪除。在表示字符串常量時，需要使用一對單引號將其括起來。 l varchar：該數(shù)據(jù)類型的使用方式與Char數(shù)據(jù)類型類似。Char 數(shù)據(jù)類型不同的是，Varchar數(shù)據(jù)類型所占用的存儲空間由字符數(shù)據(jù)所占據(jù)的實際長度來確定。 l text：該數(shù)據(jù)類型所能表示的最大長度為 231 - 1 即2,147,483,647個字符，當需要表示的數(shù)據(jù)類型長度超過了8000時，可以采用text來處理可變長度的字符數(shù)據(jù)。,（4）日期/時間數(shù)據(jù)類型日期/時間數(shù)據(jù)類型可以分為datetime和smalldatetime兩類。,l Datetime：范圍從1753年1月1日到9999年12月31日，可以精確到千分之一秒，此類型的數(shù)據(jù)占用8個字節(jié)的存儲空間。 l Smalldatetime：數(shù)據(jù)范圍從1900年1月1日到2079年6月6日，可以精確到分，此類型的數(shù)據(jù)占4個字節(jié)的存儲空間。,（5）貨幣數(shù)據(jù)類型SQL Server提供了Money和Smallmoney兩種貨幣數(shù)據(jù)類型。,l Money：占據(jù)8字節(jié)存儲空間。每4字節(jié)分別用于表示貨幣值的整數(shù)部分及小數(shù)部分。Money的取值的范圍為-263到263-1，并且可以精確到萬分之一貨幣單位。 l Smallmoney：占據(jù)4字節(jié)存儲空間。每2字節(jié)分別用于表示貨幣值的整數(shù)部分以及小數(shù)部分。smallmoney的取值范圍為-214,748.3648到+214,748.3647，可以精確到萬分之一貨幣單位。,（6）二進制數(shù)據(jù)類型用于存儲二進制數(shù)據(jù)，有binary、varbinary兩種。,l Binary:用于存儲固定長度的二進制數(shù)據(jù)，表示數(shù)據(jù)的長度取值為1到8000個字節(jié)。在輸入數(shù)據(jù)時必須在數(shù)據(jù)前加上字符0X作為二進制標識。如要輸入abc則應輸入0 xabc。若輸入的數(shù)據(jù)過長將會截掉其超出部分，若輸入的數(shù)據(jù)位數(shù)為奇數(shù)則會在起始符號0X后添加一個0。如上述的0 xabc會被系統(tǒng)自動變?yōu)? x0abc。 l Varbinary: 具有可變長度的特性，表示數(shù)據(jù)的長度也為1到8000個字節(jié)，若輸入的數(shù)據(jù)過長將會截掉其超出部分。當binary數(shù)據(jù)類型允許NULL值時將被視為varbinary數(shù)據(jù)類型。,（7）邏輯數(shù)據(jù)類型 bit數(shù)據(jù)類型占用1個字節(jié)的存儲空間，其值為0或1，如果輸入0或1以外的值將被視為1。bit類型不能定義為NULL值（所謂NULL值是指空值或無意義的值）。（8）文本和圖形數(shù)據(jù)類型這類數(shù)據(jù)類型用于存儲大量的字符或二進制數(shù)據(jù)。,l Text：用于存儲大量文本數(shù)據(jù)，其容量理論上為1-231-1（2,147, 483, 647）個字節(jié)，在實際應用時需要視硬盤的存儲空間而定。 l ntext：與text類型相似，不同的是ntext類型采用Unicode標準字符集(Character Set), 因此其理論容量為230-1(1,073,741,823)個字節(jié)。 l image:用于存儲大量的二進制數(shù)據(jù)。其理論容量為231-1(2,147,483,647)個字節(jié)。通常用來存儲圖形等（OLE Object Linking and Embedding，對象連接和嵌入）對象。在輸入數(shù)據(jù)時同binary數(shù)據(jù)類型一樣，必須在數(shù)據(jù)前加上字符“0X”作為二進制標識。,（9）特定數(shù)據(jù)類型SQLServer提供的特殊數(shù)據(jù)類型有Timestamp、 Uniqueidentifier2種。,l Timestamp：用于表示SQL Server 活動的先后順序，以二進投影的格式表示。Timestamp 數(shù)據(jù)與插入數(shù)據(jù)或者日期和時間沒有關系。 l Uniqueidentifier：由 16 字節(jié)的十六進制數(shù)字組成，此數(shù)字稱為GUID（GloballyUniqueIdentifier即全球惟一鑒別號）。此數(shù)字由SQLServer的NEWID函數(shù)產(chǎn)生全球惟一的編碼。在全球各地的計算機經(jīng)由此函數(shù)產(chǎn)生的數(shù)字不會相同。,（10）用戶定義的數(shù)據(jù)類型 用戶定義的數(shù)據(jù)類型基于在 Microsoft SQL Server 中提供的數(shù)據(jù)類型。當幾個表中必須存儲同一種數(shù)據(jù)類型時，并且為保證這些列有相同的數(shù)據(jù)類型、長度和可空性時，可以使用用戶定義的數(shù)據(jù)類型。例如，可定義一種稱為postal_code 的數(shù)據(jù)類型，它基于 Char 數(shù)據(jù)類型。當創(chuàng)建用戶定義的數(shù)據(jù)類型時，必須提供三個數(shù)：數(shù)據(jù)類型的名稱、所基于的系統(tǒng)數(shù)據(jù)類型和數(shù)據(jù)類型的可空性。（11）新數(shù)據(jù)類型 SQLServer2000中增加了3種數(shù)據(jù)類型bigint、SQL_variant和table。其中bigint數(shù)據(jù)類型已在整數(shù)類型中介紹。,l SQL_variant：可以存儲除文本圖形數(shù)據(jù)text、ntext、image、timestamp類型數(shù)據(jù)外其它任何合法的SQLServer數(shù)據(jù)。此數(shù)據(jù)類型大大方便了SQLServer的開發(fā)工作。 l Table:用于存儲對表或視圖處理后的結(jié)果集。這一新類型使得變量可以存儲一個表，從而使函數(shù)或過程返回查詢結(jié)果更加方便快。,3、函數(shù)在SQL Server中包含了大量具有執(zhí)行特定運算功能的內(nèi)置函數(shù)。函數(shù)的使用可以包含在SELECT查詢語句的選擇列表中、WHERE子句搜索條件中、視圖的搜索條件中、約束或觸發(fā)器中。一般情況下，無論函數(shù)是否有參數(shù)，在調(diào)用函數(shù)時，函數(shù)名稱后都需要帶有圓括號()。有時，用來指定數(shù)據(jù)庫、計算機、登錄或數(shù)據(jù)庫用戶的參數(shù)是可選的，如果未指定這些參數(shù)，就默認地將這些參數(shù)賦值為當前的數(shù)據(jù)庫、主機、登錄或數(shù)據(jù)庫用戶。同時，SQL Server中的函數(shù)可以支持嵌套。SQL Server中函數(shù)的種類如表2-4所示：,表2-4 SQL Serverr的函數(shù)類型,2.2 函數(shù)、變量與表達式2.2.1 常量 常量可以用來表示特定的數(shù)值，根據(jù)不同的數(shù)據(jù)類型，常量的格式也會發(fā)生相應的變化。常量可以分為：字符串常量、二進制常量、bit常量、datetime常量、integer常量、decimal常量、float和real常量、uniqueidentifier常量等。,1字符串常量 字符串常量需要括在一對單引號中，其中包含字母數(shù)字（a-z、A-Z、0-9）以及其他特殊字符，如!、#。如果在字符串中含有一個嵌入的引號時，為了避免發(fā)生混淆，用兩個單引號代替嵌入的單引號。 以下字符串均為合法的常量：XJNZY、abc、efg。,2數(shù)值常量,l 二進制常量 其前綴為OX，并且由16進制數(shù)字所組成。例如，0 xAE、0 x12Ef。 l bit常量 該常量的取值為0或1，如果bit常量的值大于1則自動被轉(zhuǎn)換為1。 l integer常量 用于表示 l decimal常量 浮點數(shù)類型的常量，可以包含小數(shù)點。 lfloat和real常量 采用科學計數(shù)法來表示浮點數(shù)。例如101.5E5、0.5E-2等。 l 指定正數(shù)和負數(shù) 在數(shù)字前面添加+或-，指明一個數(shù)是正數(shù)或是負數(shù)。如+14562、-￥87.56、+324E-5等。money常量 貨幣常量，以$作為前綴，可以包含小數(shù)點。如$16.34、$134.5等。,3uniqueidentifier 常量 該類常量用于產(chǎn)生一個全局唯一標識符 (GUID) 值的字符串。可用于數(shù)據(jù)庫中的某一個關鍵字字段。其格式可以指定為字符或二進制字符串。例如，6F9619FF-8B86-D011-B42D-00C04FC964FF。4datetime常量 用于表示特定的日期和時間。在SQL Server中能夠識別的日期格式包括，字母日期格式、數(shù)字日期格式以及未分隔的字符串格式，例如：April 15,2005、01/15/2006、20051220。時間常量可以參照如下示例，如：14:30:24、04:24 PM。 2.2.2 變量變量對應內(nèi)存中的一個存儲空間，它與常量不同，變量的值在程序運行過程中可以隨時改變。有局部變量與全局變量兩種。變量采用先申明后使用的原則。1局部變量局部變量是用戶在程序中定義的變量，一次只能保存一個值，它僅在定義的程序范圍內(nèi)有效。局部變量可以臨時存儲數(shù)值。局部變量名以符號開始，最長為128個字符。,l 申明局部變量 可以通過DECLARE語句聲明局部變量，需要確定變量的名字、數(shù)據(jù)類型和長度。例如，定義一個整數(shù)類型的變量score以及字符串類型的變量name，可以表示為： DECLARE score INT DECLARE name CHAR(12) l 對局部變量賦值 局部變量的初值為NULL（空），可以使用SELECT語句或SET語句對變量進行賦值。SET語句一次只能給一個局部變量賦值，SELECT語句則可以同時給一個或多個變量賦值。,局部變量通常用于以下幾種情況： 作為計數(shù)器計算循環(huán)執(zhí)行的次數(shù)或控制循環(huán)執(zhí)行的次數(shù)。 保存數(shù)據(jù)值以供流控語句測試。 保存由存儲過程返回代碼的數(shù)據(jù)值。 在使用變量之前應該考慮到變量的作用域，只有在變量的作用范圍以內(nèi)才能夠正確的對變量進行操作。變量的作用域指從申明變量的開始位置到含有該變量的批處理或存儲過程的結(jié)束位置。 【例2-1】在數(shù)據(jù)庫XSCJ中定義一個長度為12的字符串類型變量student，對該變量進行賦值，并且查詢出“學生基本信息表“中該變量所指定姓名的學生信息。 在查詢分析器中運行如下命令： USE XSCJ GO DECLARE student CHAR(12) SET student = 王倩倩 SELECT * FROM 學生基本信息表 WHERE 姓名 = student 運行結(jié)果如圖2-1所示。,圖2-1定義一個長度為12的字符串類型變量,【例2-2】在數(shù)據(jù)庫XSCJ中定義兩個日期時間類型的變量max_csrq、min_csrq，分別用于查詢“學生基本信息表”中“出生日期”的最大值、最小值。 可在查詢分析器中運行如下命令： USE XSCJ GO DECLARE max_csrq DATETIME , min_csrq DATETIME SELECT max_csrq=MAX(出生日期),min_csrq=MIN(出生日期) FROM 學生基本信息表 PRINT max_csrq PRINT min_csrq 運行結(jié)果如圖2-2所示：,圖2-2定義日期時間類型變量運行結(jié)果,2 全局變量 全局變量以開頭，實際上它是SQL Server的配置函數(shù)。 2.2.3 表達式 在Transact-SQL語言中，表達式由標識賦、數(shù)值、運算符組成?？梢詫Ρ磉_式進行計算并且得到結(jié)果。表達式可以是一個常量、變量、字段名、函數(shù)或子查詢?？梢酝ㄟ^運算符將兩個或更多的簡單表達式聯(lián)接起來組成復雜的表達式。 表達式中的運算符可以執(zhí)行算術、比較、連接或賦值操作。運算符大致可以劃分為：算術運算符、位運算符、邏輯運算符、比較運算符、字符串連接運算符等。 常用的算術運算符有+（加）、（減）、*（乘）、/（除）、%（求余）等；常用的比較運算符有=（等于）、=（大于等于）、（不等于）或!=（不等于）等；常用的位運行符有&（按位與）、|（按位或）、（按位異或）等；常用的邏輯運算符有AND（邏輯與）、NOT（邏輯非）、OR（邏輯或）等。 2.2.4 常用函數(shù) 在SQL Server 2000中提供了大量的系統(tǒng)函數(shù)，通過使用這些函數(shù)，用戶可以根據(jù)需要完成特定的操作。常用的系統(tǒng)函數(shù)有：聚合函數(shù)、數(shù)學函數(shù)、字符串函數(shù)、日期時間函數(shù)、數(shù)據(jù)類型轉(zhuǎn)換函數(shù)等。,1聚合函數(shù) 聚合函數(shù)也稱為統(tǒng)計函數(shù)，它對一組值進行計算并返回一個數(shù)值。聚合函數(shù)經(jīng)常與SELECT語句一起使用。常用聚合函數(shù)如表2-5所示。 表2-5 SQL Server聚合函數(shù)及其功能,【例2-3】計算XSCJ數(shù)據(jù)庫中“學生基本信息表”的總行數(shù)。 在查詢分析器中運行如下命令： USE XSCJ GO SELECT COUNT(*) AS 學生總?cè)藬?shù) FROM 學生基本信息表 GO 運行結(jié)果如圖2-3所示。,表2-3 計算學生基本信息表的總?cè)藬?shù),【例2-4】計算XSCJ數(shù)據(jù)庫中課程編號為003的課程總成績和平均成績。 在查詢分析器中運行如下命令： USE XSCJ GO SELECT SUM(成績) AS 課程總分,AVG(成績) AS 課程平均分 FROM 成績表 WHERE 課程編號=003 GO 運行結(jié)果如圖2-4所示。,圖2-4 計算課程編號為003的課程總分和平均分,【例2-5】在成績表中查詢課程編號為003的課程的最高分和最低分。在查詢分析器中運行如下命令：USE XSCJGOSELECT MAX(成績) AS 最高分,MIN(成績) AS 最低分FROM 成績表WHERE 課程編號=003GO運行結(jié)果如圖2-5所示。,圖2-5 查詢課程編號為003的課程最高分和最低分,2數(shù)學函數(shù) 數(shù)學函數(shù)用來對數(shù)值型數(shù)據(jù)進行數(shù)學運算。常用數(shù)學函數(shù)如表2-6所示。 表2-6 常用數(shù)學函數(shù),【例2-6】分別輸出2的3次冪、-1的絕對值、2的平方、3.14的整數(shù)部分。 在查詢分析器中運行如下命令： PRINT POWER(2,3) PRINT ABS(-1) PRINT SQUARE(2) PRINT FLOOR(3.14) GO 運行結(jié)果為：8、1、4、3。 3字符串函數(shù) 字符串函數(shù)可以對char、nchar、varchar、nvchar等類型的參數(shù)執(zhí)行操作，并返回相應的結(jié)果，返回值一般為字符串或數(shù)字。SQL Server 2000中所包含的字符串函數(shù)如表2-7所示。 表2-7 常用字符串函數(shù),【例2-7】使用LEN函數(shù)顯示字符串常量以及字符串變量的長度。 提示：LEN函數(shù)用于計算字符串中所包含的字符個數(shù)，如果字符串尾部含有空格則會被忽略。 在查詢分析器中運行如下命令： PRINT LEN(computer department),DECLARE s1 char(10) DECLARE s2 char(10) SET s1 = welcome SET s2 = hellow PRINT LEN(s1) PRINT LEN(s2) 運行結(jié)果為：19，7，6。 【例2-8】給定一個字符串have a good time，判斷字符g在整個字符串中的位置。 提示：CHARINDEX函數(shù)用于在規(guī)定字符串中對子字符串進行查詢。當返回值大于零時表示子字符串的起始位置，返回值為0時表明沒有查詢結(jié)果。 在查詢分析器中運行如下命令： DECLARE s CHAR(20) SET s =have a good time PRINT CHARINDEX(g,s) 運行結(jié)果為：8,4日期時間函數(shù) 日期時間函數(shù)可以對日期時間類型的參數(shù)進行運算、處理，并返回一個字符串、數(shù)字或日期和時間類型的值。SQL Server 2000中提供的日期時間函數(shù)如表2-7所示。 表2-7常用日期時間函數(shù),【例2-9】獲取系統(tǒng)時間信息，在查詢分析器中分別顯示系統(tǒng)時間中的年份、月份以及日期。 提示：GETDATE函數(shù)用于返回當前的系統(tǒng)時間，YEAR，MONTH，DAY函數(shù)可以取得時間中的年、月、日的數(shù)值。,在查詢分析器中運行如下命令： DECLARE xtsj DATETIME SET xtsj = GETDATE() SELECT YEAR(xtsj) SELECT MONTH(xtsj) SELECT DAY(xtsj) 運行結(jié)果如圖2-6所示。,圖2-6 計算時期時間信息,【例2-10】通過對“學生基本信息表”中的“出生日期”字段進行計算，查詢每一位學生的年齡。 提示：利用DATEDIFF函數(shù)可以計算出兩個日期之間的距離，該函數(shù)含有三個參數(shù)，第一個參數(shù)通常可以為yy（年）或mm（月）或dd（日），若第一個參數(shù)為yy時，該函數(shù)返回值為后兩個日期參數(shù)之間年份的差距。在本例中，當前的日期由GETDATE函數(shù)獲得后，計算與每一位學生的出生日期之間年份的差距，從而獲得學生的年齡并在查詢結(jié)果中顯示。 在查詢分析器中運行如下命令： SELECT 學號,姓名,DATEDIFF(yy,出生日期,GETDATE() from 學生基本信息表 運行結(jié)果如圖2-7所示。,圖2-7查詢每一位學生的年齡,5數(shù)據(jù)類型轉(zhuǎn)換函數(shù) 在不同的數(shù)據(jù)類型之間進行運算時，需要將其轉(zhuǎn)換為相同的數(shù)據(jù)類型。在SQL Server中，某些數(shù)據(jù)類型可以由系統(tǒng)自動完成轉(zhuǎn)換，當系統(tǒng)不能夠自動執(zhí)行不同類型表達式的轉(zhuǎn)換時，可以通過CAST和CONVERT函數(shù)對數(shù)據(jù)進行轉(zhuǎn)換。CAST和CONVERT函數(shù)的語法格式為：CAST (表達式 AS 數(shù)據(jù)類型及長度)CONVERT (數(shù)據(jù)類型及長度 , 表達式 , 日期/字符串格式樣式 , )其中，日期/字符串格式樣式為可選，用于確定將參數(shù)轉(zhuǎn)化為字符串之后后的樣式。包括將 datetime 或 smalldatetime 數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)（nchar、nvarchar、char、varchar、nchar 或 nvarchar 數(shù)據(jù)類型）；或者將 float、real、money 或 smallmoney 數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)（nchar、nvarchar、charvarchar、nchar 或 nvarchar 數(shù)據(jù)類型）。,【例2-11】查詢“學生基本信息表”中的學號、姓名、年齡，并且將這三個字段通過“+”運算符進行連結(jié)顯示在查詢結(jié)果中。 提示：由于計算學生年齡的結(jié)果為整數(shù)，而學號、姓名均為字符串類型的值，因而在運算之前,需要將年齡的計算結(jié)果轉(zhuǎn)化為字符串，即CAST(DATEDIFF(yy , 出生日期 , GETDATE() AS CHAR(2)。 在查詢分析器中運行如下命令： SELECT 學號+姓名+年齡：+CAST(DATEDIFF(yy,出生日期,GETDATE() AS CHAR(2) FROM 學生基本信息表 運行結(jié)果如圖2-8所示。,圖2-8 查詢“學生基本信息表”中的學號、姓名、年齡,【例2-12】將常量3.14分別轉(zhuǎn)換為整數(shù)類型以及字符串類型并且輸出結(jié)果。在查詢分析器中運行如下命令：PRINT CONVERT(INTEGER,3.14)PRINT CONVERT(CHAR(4),3.14)結(jié)果顯示為：3、3.14?！纠?-13】取得系統(tǒng)當前時間，并且將其轉(zhuǎn)化為mm/dd/yyyy格式的字符串并且顯示結(jié)果。提示；由于mm/dd/yyyy格式所對應的參數(shù)為101，所以在使用CONVERT函數(shù)時，日期格式樣式參數(shù)設定為101。在查詢分析器中運行如下命令：DECLARE xtsj DATETIMESET xtsj = GETDATE()PRINT CONVERT(CHAR(50),xtsj,101)運行結(jié)果如圖2-9所示。,圖2-9 取得系統(tǒng)當前時間，并將其轉(zhuǎn)化,6系統(tǒng)函數(shù) 系統(tǒng)函數(shù)可以使得用戶在不直接訪問系統(tǒng)表的情況下就可獲得系統(tǒng)表的信息。 針對于數(shù)據(jù)庫、主機、對象、登錄以及用戶的系統(tǒng)函數(shù)包括：,l DB_ID 、DB_NAME 分別返回當前數(shù)據(jù)庫的編號以及名稱 l HOST_ID 、HOST_NAME 分別返回主機編號以及名稱 l OBJECT_ID 、OBJECT_NAME 分別返回對象的編號以及名稱 l SUSER_ID 、SUSER_NAME 分別返回SQL Server 身份驗證模式下，當前登錄用戶的編號以及名稱 l USER_ID 、USER_NAME 返回系統(tǒng)用戶編號、名稱，通常該用戶為dbo 例如：SELECT DB_ID()表示調(diào)用DB_ID 函數(shù)來獲得當前數(shù)據(jù)庫在系統(tǒng)中的編號。 與字段有關的系統(tǒng)函數(shù)包括： l COL_NAME 返回列名。 l COL_LENGTH 返回列長度。 l INDEX_COL 返回索引列名稱。 例如：SELECT COL_LENGTH(課程信息表,課程名稱)可以取得“課程信息表” 中“課程名稱”字段的長度。,2.3 系統(tǒng)存儲過程 2.3.1 系統(tǒng)存儲過程概述 存儲過程是一組編譯在單個執(zhí)行計劃中的Transact-SQL語句，分為系統(tǒng)存儲過程和用戶自定義存儲過程兩種。SQL Server包含了多種多樣的系統(tǒng)存儲過程，用戶可以通過調(diào)用這些存儲過程對系統(tǒng)進行管理以及對數(shù)據(jù)信息進行操作。系統(tǒng)存儲過程以“sp_”開頭，并且保存在Master數(shù)據(jù)庫中，部分存儲過程只能由系統(tǒng)管理員進行調(diào)用，普通用戶可以經(jīng)過授權后使用其它的系統(tǒng)存儲過程。 2.3.2 常用系統(tǒng)存儲過程 在SQL Server中，常用的系統(tǒng)存儲過程有sp_helpdb、sp_renamedb、sp_helpuser、sp_addlogin等。 1sp_helpdb 該存儲過程可以查看數(shù)據(jù)庫信息，語法格式為： sp_helpdb 數(shù)據(jù)庫名稱 查詢的結(jié)果包括：數(shù)據(jù)庫的邏輯名稱、容量、所有者、創(chuàng)建時間、狀態(tài)，數(shù)據(jù)文件的邏輯名稱、標識符、物理名稱、文件所屬組、文件的大小、文件最大值以及增量等。如果不指定數(shù)據(jù)庫名稱，則返回master.dbo.sysdatabases 中的所有數(shù)據(jù)庫信息。,【例2-14】顯示XSCJ數(shù)據(jù)庫信息。 在查詢分析器中運行如下命令： sp_helpdb XSCJ 運行結(jié)果如圖2-10所示。,圖2-10 顯示XSCJ數(shù)據(jù)庫信息,2sp_renamedb 該存儲過程用于修改數(shù)據(jù)庫名稱。語法格式為： sp_renamedb 原名稱,新名稱 修改后的名稱也需要遵循標識賦的規(guī)則。該存儲過程只能由系統(tǒng)管理員以及數(shù)據(jù)庫創(chuàng)建者調(diào)用。當返回值為0表示修改成功，為1表示操作失敗。 【例2-15】創(chuàng)建一個名稱為somename的數(shù)據(jù)庫，利用sp_renamedb存儲過程將該數(shù)據(jù)庫重命名為newname。 在查詢分析器中運行如下命令： CREATE DATABASE somename GO sp_renamedb somename,newname GO 運行結(jié)果如圖2-11所示。,圖2-11 利用sp_renamedb存儲過程將數(shù)據(jù)庫重命名,3sp_helpuser 用于顯示系統(tǒng)內(nèi)部的用戶信息，語法格式為： sp_helpuser 用戶名稱 不指定用戶名稱時顯示所有用戶。 【例2-16】顯示超級用戶dbo的用戶信息。 在查詢分析器中運行如下命令： sp_helpuser dbo 運行結(jié)果如圖2-12所示。,圖2-12 顯示超級用戶dbo的用戶信息,4sp_addlogin 用于在系統(tǒng)中增加新的登錄，語法格式為： sp_addlogin 登錄名 ,密碼 ,登錄的默認數(shù)據(jù)庫 ,登錄時的默認語言 ,標識號 ,是否加密。 【例2-17】在系統(tǒng)中添加一個名為test、密碼為1234的登錄帳號。 在查詢分析器中運行如下命令： sp_addlogin test,1234 運行結(jié)果如圖2-13所示。,圖2-13 在系統(tǒng)中添加一個名為test、密碼為1234的登錄帳號,5sp_adduser 可以在當前數(shù)據(jù)庫中添加用戶，語法格式為： sp_adduser 登錄名 用戶名稱 組 登錄名必須是已經(jīng)存在的登錄，如果不指定用戶名稱，則默認為登錄名。組為當前數(shù)據(jù)庫中有效的組或角色。注意：在添加用戶之前首先需要添加相應的登錄。 【例2-18】在SQL Server中添加一個名稱為test的用戶。 在查詢分析器中運行如下命令： sp_addlogin test GO sp_adduser test GO 運行結(jié)果如圖2-14所示。,圖2-14 在SQL Server中添加一個名稱為test的用戶,6sp_dropuser 可以從當前數(shù)據(jù)庫中刪除SQL-Server中已經(jīng)存在的用戶。其語法格式為： sp_dropuser 用戶名 用戶名的數(shù)據(jù)類型為sysname。可以利用sp_helpuser顯示用戶列表，并刪除該列表包含的用戶名。刪除某一個用戶的條件為該用戶不擁有任何對象，因此在刪除用戶之前需要使用sp_changeobjectowner將對象的所屬關系進行更改。只有 sysadmin 固定服務器角色、db_owner 或 db_accessadmin 固定數(shù)據(jù)庫角色的成員才能執(zhí)行該存儲過程。 【例2-19】刪除名稱為test的用戶。 在查詢分析器中運行如下命令： sp_dropuser test 運行結(jié)果如圖2-15所示。,圖2-15 刪除名稱為test的用戶,7sp_addrole 用于在當前數(shù)據(jù)庫中建立新的角色，語法格式為： sp_addrole 角色名稱 角色的所有者 其中：角色名稱必須符合有效的標識符命名規(guī)則，而且不能是數(shù)據(jù)庫中已有的角色。增加新的角色之后，可以使用sp_addrolemember在角色中添加成員。sysadmin 固定服務器角色及 db_securityadmin 和 db_owner 固定數(shù)據(jù)庫角色的成員才能執(zhí)行該存儲過程。,【例2-20】添加一個名為myrole的角色，并將test用戶加入該角色中。 在查詢分析器中運行如下命令： sp_addrole myrole GO sp_addmember myrole , test GO 運行結(jié)果如圖2-16所示。,圖2-16 添加一個名為myrole的角色，并將test用戶加入該角色中,8、sp_droprole 對于不需要的角色，可以使用該存儲過程將其從數(shù)據(jù)庫中刪除。語法格式為： sp_droprole 角色名稱。 【例2-21】刪除已有的角色myrole。 在查詢分析器中運行如下命令： sp_droprole myrole 運行結(jié)果如圖2-17所示。,圖2-17 刪除已有的角色myrole,2.4 批處理與流控語句 2.4.1 批處理 批處理是多條Transact-SQL語句構成的集合。SQL Server將批處理語句進行編譯形成一個可執(zhí)行單元，稱為執(zhí)行計劃。執(zhí)行計劃中的語句一次執(zhí)行每一條語句，如果在編譯過程中出現(xiàn)語法錯誤，那么批處理中所有的語句均無法正常執(zhí)行。如果在運行階段出現(xiàn)錯誤時，一般都會中斷當前以及其后語句的執(zhí)行，只有在少數(shù)情況下，如違反約束時，僅中斷當前出錯的語句而繼續(xù)執(zhí)行其他語句。 如果在事務中含有批處理語句，那么在運行階段一旦出現(xiàn)錯誤時，都將會對已經(jīng)執(zhí)行的操作結(jié)果進行回滾。假定在事務中包含10條語句，那么如果第五條語句出現(xiàn)錯誤，則不會執(zhí)行后續(xù)的語句，并且取消之前完成的操作。,在建立批處理時，應該遵循以下規(guī)則： l 不能在批處理中引用其他批處理中所定義的變量。 l CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER 和 CREATE VIEW 語句不能在批處理中與其它語句組合使用。 l 不允許在一個批處理中更改表結(jié)構、并引用新的字段。 l 如果 EXECUTE 語句是批處理中的第一句，則不需要 EXECUTE 關鍵字。如果 EXECUTE 語句不是批處理中的第一條語句，則需要 EXECUTE 關鍵字。 l 一個完整的批處理需要使用GO語句作為結(jié)束標記。,【例2-22】執(zhí)行批處理程序，依次查詢系部表、系部總數(shù)、班級表、班級總數(shù)。 USE XSCJ GO SELECT * FROM 系部表 SELECT COUNT(*) FROM 系部表 SELECT * FROM 班級表 SELECT COUNT(*) FROM 班級表 GO 運行結(jié)果如圖2-18所示。,圖2-18 執(zhí)行批處理程序結(jié)果,2.4.2 流控語句 流控制語句采用了與程序設計語言相似的機制，使其能夠產(chǎn)生控制程序執(zhí)行及流程分支的作用。通過使用流程控制語句，用戶可以完成功能較為復雜的操作，并且使得程序獲得更好的邏輯性和結(jié)構性。 1BEGIN.END語句 用于將一系列的SQL語句合并為一組語句，當需要同時執(zhí)行兩條以上的語句時，可以使用BEGINEND語句將這些語句包含在內(nèi)形成一個語句塊，作為一個整體來執(zhí)行。通常該語句可以嵌套在其他語句中，如條件分支語句、循環(huán)語句中。 BEGINEND語法格式為： BEGIN 語句1 語句2 語句n END,2IFELSE語句 該語句用于設計條件分支流程，根據(jù)給定的條件，程序可以執(zhí)行不同的操作和運算，從而使程序的功能更加完善。該語句的格式為： IF 條件表達式 語句塊1 ELSE 語句塊2 其中：條件表達式為一個布爾表達式，結(jié)果為真或假。當條件成立時，執(zhí)行語句塊1，否則執(zhí)行語句塊2。當需要判斷多個條件時，可以對IF.ELSE進行嵌套。 【例2-23】在“學生基本信息表”中查詢班級編號為20041001的班級中是否有黨員。要求：如果有黨員則顯示黨員的人數(shù)，否則，提示該班級沒有黨員。,在查詢分析器中運行如下命令： USE XSCJ GO IF (SELECT COUNT(*) FROM 學生基本信息表 WHERE 班級編號 = 20041001 AND 政治面貌 = 黨員) 0 BEGIN DECLARE dy INTEGER SELECT dy = COUNT(*) FROM 學生基本信息表 WHERE 班級編號 = 20041001 AND 政治面貌 = 黨員 PRINT 黨員的人數(shù)為： PRINT dy END ELSE PRINT 該班級沒有黨員,上述程序中利用COUNT函數(shù)計算出滿足條件的學生人數(shù)，并且根據(jù)人數(shù)進行判斷，當人數(shù)大于零時，將人數(shù)賦值給dy變量并輸出結(jié)果，程序運行結(jié)果如圖2-19所示。,圖2-19 在“學生基本信息表”中查詢班級編號為20041001的班級中是否有黨員,3CASE語句 CASE語句用于計算多個條件并為每個條件返回單個值，以簡化SQL語句格式。CASE語句不同于其他SQL語句，不能作為獨立的語句來執(zhí)行，而是需要作為其他語句的一部分來執(zhí)行。 CASE語句有兩種格式：簡單CASE表達式以及搜索CASE表達式。 （1）簡單CASE表達式 語法格式為： CASE 表達式 WHEN 條件1 THEN 結(jié)果表達式1 WHEN 條件2 THEN 結(jié)果表達式2 ELSE 結(jié)果表達式n END 在簡單表達式中，將表達式與每一個條件依次進行比較，如果遇到表達式與條件相匹配時，停止比較，并且返回滿足條件的WHEN子句所對應的結(jié)果表達式。如果表達式與所有的條件都不匹配時，則返回ELSE子句中的結(jié)果表達式，如果不存在ELSE子句，則返回NULL值。 如果表達式與多個條件匹配時，CASE函數(shù)返回第一次滿足條件時的WHEN子句所對應的結(jié)果表達式。,【例2-24】根據(jù)系統(tǒng)時間判斷當前日期所對應的星期值并且輸出結(jié)果。 在查詢分析器中運行如下命令： DECLARE dt DATETIME SET dt = DATEPART(w,GETDATE() SELECT CASE dt WHEN 1 THEN 星期天 WHEN 2 THEN 星期一 WHEN 3 THEN 星期二 WHEN 4 THEN 星期三 WHEN 5 THEN 星期四 WHEN 6 THEN 星期五 WHEN 7 THEN 星期六 END 該實例中，首先通過DATEPART函數(shù)獲得當前時間所對應的星期數(shù)，范圍為1至7，其中1代表星期天，7代表星期六，構造一個條件分支。然后將系統(tǒng)的星期數(shù)值通過CASE函數(shù)轉(zhuǎn)換為相應的字符串信息并顯示結(jié)果。運行結(jié)果如圖2-20所示。,圖2-20 根據(jù)系統(tǒng)時間判斷當前日期所對應的星期值并且輸出結(jié)果,（2）搜索CASE表達式 搜索CASE表達式與簡單CASE表達式的功能類似，但能夠?qū)崿F(xiàn)的條件分支要比簡單CASE表達式更為復雜。其語法格式為： CASE WHEN 布爾表達式 THEN 結(jié)果表達式 WHEN 布爾表達式2 THEN 結(jié)果表達式2 ELSE 結(jié)果表達式n END 【例2-25】取得系統(tǒng)時間，并且判斷當前時間在一天中所處的時間段，并且在查詢分析器中輸出提示信息。 在查詢分析器中運行如下命令： DECLARE sj DATETIME SET sj = DATEPART(hh,GETDATE(),SELECT CASE WHEN sj=20 and sj=14 and sj=12 and sj=10 and sj=0 and sj<10 THEN 臨晨 END,本例中首先通過DATEPART函數(shù)取得當前時間中的小時數(shù)，并保存在sj變量中，然后構造CASE函數(shù)，用于確定不同的條件分支。將變量sj分別與CASE函數(shù)中的條件依次進行比較，直到滿足條件時輸出結(jié)果。運行結(jié)果如圖2-21所示：,圖2-21取得系統(tǒng)時間并判斷當前時間所處的時間段,4WHILE語句 WHILE語句可以在滿足條件的情況下重復執(zhí)行在循環(huán)體內(nèi)的語句。在循環(huán)體內(nèi)部可以使用CONTINUE、BREAK語句對循環(huán)進行控制。 WHILE語句的語法格式為： WHILE 條件表達式 BEGIN 語句塊1 BREAK 語句塊2 CONTINUE 語句塊3 END 當條件表達式的值為真時，循環(huán)將重復執(zhí)行，直到條件表達式的值為假時，退出循環(huán)。CONTINUE命令可以忽略其后的語句而繼續(xù)進入下一次循環(huán)。而BREAK命令可以提前終止整個循環(huán)過程。,【例2-26】利用循環(huán)計算1+2+3+99+100的值。 在查詢分析器中運行如下命令： DECLARE sum int , i int SET sum = 0 SET i = 1 WHILE i<=100 BEGIN SET sum = sum + i SET i = i + 1 END PRINT sum 運行結(jié)果為：5050。 【例2-27】利用WHILE循環(huán)計算當前時間距離2007年1月1日之間的天數(shù)。 在查詢分析器中運行如下命令： DECLARE num int , sj DATETIME SET sj = GETDATE() PRINT 當前時間為： + CAST( sj AS CHAR(20) SET num=0 WHILE 1=1 BEGIN,SET sj = DATEADD(day,1,sj) SET num = num + 1 IF DATEPART( yy , sj ) = 2007 BREAK END PRINT 距離2007年1月1日還有 + CAST(num AS VARCHAR(10) + 天 本實例中，首先由sj變量獲取系統(tǒng)時間，其后在while的每次循環(huán)中，都對sj對應的日期加1。當日期增長至2006年時，使用BREAK語句退出循環(huán)，用num變量累計循環(huán)的次數(shù)，退出循環(huán)時num值為最終的結(jié)果。運行結(jié)果如圖2-22所示。,圖2-12 利用WHILE循環(huán)計算當前時間距離2007年1月1日之間的天數(shù),本章小結(jié) 本章內(nèi)容主要學習了Transact-SQL語句中編程的有關知識，包括變量、表達式、函數(shù)及流控語句，同學們應重點掌握如何能夠通過這些知識設計一些較為復雜的SQL語句。,練習與上機 一、填空題 1、 SQL語言主要由哪三部分組成_，_，_。 2、 數(shù)據(jù)對象的引用都由哪四部分所組成_，_，_，_。 3、標識符可劃分為_，_。 4、系統(tǒng)存儲過程通常以_開頭。 5、TranceSQL語言提供了幾種類型的系統(tǒng)數(shù)據(jù)類型，分別為：_、_、_、_、_等。 6、字符型數(shù)據(jù)類型包括_、_、_、_等。 7、日期型數(shù)據(jù)類型為_ 和_ 。 8、貨幣型數(shù)據(jù)類型為_ 和_ 。,二、選擇題 1、在SQL Server 2000中，使用哪種語句定義變量（ ）？ A、CREATE TABLE B、SET C、GO D、DECLARE 2、TransactSQL語句中的賦值運算符為（ ）。 A、= = B、= C、：= D、：= 3、下列哪種函數(shù)用于判斷兩個日期相隔的時間差（ ）？ A、DATEADD B、DATEDIFF C、DAGENAME D、GETDATE 4、下列哪種函數(shù)用于求得不大于某個數(shù)最小整數(shù)（ ）？ A、FLOOR B、SIN C、SQUARE D、POWER 5、如果數(shù)據(jù)表中的某列值是從0從到255的整形數(shù)，最好使用哪種數(shù)據(jù)類型（ ） ？ A、int B、tinyint C、bigint D、decimal,三、簡答題 1、簡述SQL Server2000中標識符的格式規(guī)則。； 2、簡述 Transact-SQL語言的各種流程控制語句的語法格式。 項目實訓 1、查詢出每一個系部學生中團員的數(shù)量。 2、編寫Transact-SQL語句獲取當前的系統(tǒng)日期時間，并取得當前日期的年、月和日，在查詢分析器中執(zhí)行Transact-SQL語句，測試語句結(jié)果。,