第3章Transact-SQL語言(1)2．1Transact-SQL概論

SQL語言是一種用于存取和查詢數(shù)據(jù)，更新并管理關(guān)系數(shù)據(jù)庫系統(tǒng)的數(shù)據(jù)庫查詢和編程語言。Transact-SQL是對標(biāo)準(zhǔn)SQL程序語言的增強(qiáng)，是用于應(yīng)用程序和SQLServer之間通信的主要語言。Transact-SQL的語法結(jié)構(gòu)類似于英語，易學(xué)易用，書寫隨意。SQL語言是一種說明性的語言。SQL語言特別適合于Client/Server體系結(jié)構(gòu)。SQL語言是由數(shù)據(jù)庫定義語言（DDL）、數(shù)據(jù)操作語言（DML）、數(shù)據(jù)控制語言（DCL）組成。數(shù)據(jù)庫類型主數(shù)據(jù)文件：數(shù)據(jù)庫的起點，指向數(shù)據(jù)庫中的其他文件。*.mdf次數(shù)據(jù)文件:*.ndf日志文件:包含用于恢復(fù)數(shù)據(jù)的所有日志信息。*.ldf文件名與文件大小邏輯文件名：是在T-SQL中引用物理文件時使用的名稱。物理文件名：符合操作系統(tǒng)命名規(guī)則文件大小：一般由初始容量不斷增大，可以制定一個最大容量。數(shù)據(jù)庫對象表：由行和列組成，是具體組織和存儲數(shù)據(jù)的對象。記錄：表中每一行數(shù)據(jù)即為一條記錄主鍵：表中一列或多列的組合，能唯一確定一條記錄。外鍵：一列或多列的組合中，存在A表中，但不是A的主鍵，同時存在B表中，且為B表的主鍵。索引：表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識的數(shù)據(jù)頁的邏輯指針清單。約束：是SQL實施數(shù)據(jù)一致性和完整性的方法。數(shù)據(jù)庫對象默認(rèn)值：插入數(shù)據(jù)時，未指定具體值的字段，系統(tǒng)自動提供默認(rèn)的數(shù)據(jù)規(guī)則：用來限制表中字段的有限范圍，確保數(shù)據(jù)完整性的方式存儲過程：一組經(jīng)過編譯的而已重復(fù)使用的T-SQL代碼的組合觸發(fā)器：一組特殊的存儲過程，與表格或某些操作相關(guān)聯(lián)。系統(tǒng)數(shù)據(jù)庫Master:記錄SQL實例的所有系統(tǒng)級信息，同時還記錄其他數(shù)據(jù)庫是否存在，以及這些數(shù)據(jù)庫存放的位置。不能添加文件或文件組不能更改排序規(guī)則，更改數(shù)據(jù)庫所有者不能創(chuàng)建全文目錄或索引不能在系統(tǒng)表上創(chuàng)建觸發(fā)器不能刪除數(shù)據(jù)庫，上傳主文件組不能將數(shù)據(jù)庫設(shè)置為OFFLINE和ReadOnly系統(tǒng)數(shù)據(jù)庫Model:用于SQL實例上創(chuàng)建的所有數(shù)據(jù)庫的模板，對model數(shù)據(jù)庫進(jìn)行的修改將應(yīng)用于以后的所有數(shù)據(jù)庫Msdb:用于計劃警報和作業(yè)以及與備份和回復(fù)相關(guān)的信息，由SQLServer代理Tempdb:所有用戶都可用的全局資源，保存所有臨時表和臨時存儲過程顯示創(chuàng)建的臨時對象所有版本的更新記錄創(chuàng)建或重新生成索引時，臨時排序的結(jié)果系統(tǒng)數(shù)據(jù)庫Resource:是只讀數(shù)據(jù)庫，包含SQLServer2005中的所有系統(tǒng)對象。創(chuàng)建數(shù)據(jù)庫注意事項：只有系統(tǒng)管理員和數(shù)據(jù)庫擁有者才可創(chuàng)建創(chuàng)建過程就是為數(shù)據(jù)庫設(shè)計名稱、設(shè)計所占用的存儲空間和存儲文件位置的過程所有新數(shù)據(jù)庫都是系統(tǒng)數(shù)據(jù)庫model的副本單個數(shù)據(jù)庫可存儲在單個文件上，也可跨文件存儲數(shù)據(jù)庫的大小可增大或收縮最多可存儲32767個數(shù)據(jù)庫自動生成sysdatabases系統(tǒng)表CREATDATABASE命令建數(shù)據(jù)庫Creatdatabase_nameON[[<>[,…n]][,<>[,…n]]][LOGON{<>[,…n]}][COLLATEcollation_name]<>定義[PRIMARY]([NAME=logical_,]‘OS_’[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,=growth_increment])[,…n][]：表示可選語法項，省略時各參數(shù)取默認(rèn)值[,…n]：表示前面的內(nèi)容可重復(fù)多次{}：必選項<>：表示實際的語句要用相應(yīng)的內(nèi)容代替文字大寫：T-SQL的關(guān)鍵字文字小寫：用戶提供的T-SQL語法的參數(shù)Database_name:用戶創(chuàng)建的數(shù)據(jù)庫名ON：指定存放數(shù)據(jù)庫的數(shù)據(jù)文件信息LOGON：指定日志文件的明確定義COLLATE：指定數(shù)據(jù)庫默認(rèn)排序規(guī)則<>：指定文件屬性NAMElogical_:定義文件的邏輯名稱os_：定義文件的物理名稱SIZE:文件屬性的初始值MAXSIZE:定義文件可以增長到的最大值:定義文件的每次增長的大小：定義對文件組的控制注意事項1、創(chuàng)建用戶數(shù)據(jù)庫后，要備份master數(shù)據(jù)庫2、所有數(shù)據(jù)庫都至少包含一個主文件組，所有系統(tǒng)表都分配在主文件組中。3、每個數(shù)據(jù)庫都有一個所有者，可在數(shù)據(jù)庫中執(zhí)行某些特殊的操作。4、創(chuàng)建數(shù)據(jù)庫的權(quán)限默認(rèn)的授予sysadmin和dbcreator固定服務(wù)器角色的成員。舉例創(chuàng)建一個xsgl數(shù)據(jù)庫、其中主數(shù)據(jù)庫文件為10MB，最大容量不受限制，每次增長1MB，事務(wù)日志文件容量為1MB，最大容量不受限制，每次增長100KB。答案CREATEDATABASExsglONPRIMARY(NAME=‘xsgl’,‘C:\programFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\xsgl.mdf’,SIZE=10MB,MAXSIZE=UNLIMITED,)LOGON(NAME=‘xsgl_log’,‘C:\programFiles\MicrosoftSQL,Server\MSSQL.1\MSSQL\Data\xsgl.ldf’SIZE=1MB,MAXSIZE=UNLIMITED,)練習(xí):創(chuàng)建一個xsb數(shù)據(jù)庫、其中主數(shù)據(jù)庫文件為10MB，最大容量為50MB，每次增長0.1MB，事務(wù)日志文件容量為1MB，最大容量不受限制，每次增長5%事務(wù)日志事務(wù)日志主要是存放恢復(fù)數(shù)據(jù)庫所需的信息，是數(shù)據(jù)庫中已發(fā)生的所有修改和執(zhí)行每次修改的事務(wù)的一連串記錄，后綴名為“*.ldf”，日志文件最小為512KB日志文件有點1、可以單獨(dú)備份2、可從服務(wù)器失效的時間中將服務(wù)恢復(fù)到最近的狀態(tài)3、可以很容易監(jiān)測事務(wù)日志文件的空間，不會搶占數(shù)據(jù)庫空間4、向數(shù)據(jù)庫和事務(wù)日志寫入數(shù)據(jù)時會產(chǎn)生較少的沖突，有利于提高SQL的完整性。工作過程1、應(yīng)用程序發(fā)出一個修改數(shù)據(jù)庫命令2、事務(wù)開始時，事務(wù)日志會記錄事務(wù)開始標(biāo)志，并將可能影響的數(shù)據(jù)庫頁從磁盤讀入到緩沖區(qū)3、事務(wù)中每個更改語句都被記錄在日志文件中4、在緩沖去中修改相應(yīng)數(shù)據(jù)打開數(shù)據(jù)庫使用語句：USEdatabase_name查看數(shù)據(jù)庫1、使用SQLServerManagementStudio查看2、使用T-SQL語句查看[EXECUTE]sp_helpdb[數(shù)據(jù)庫名]修改數(shù)據(jù)庫1、使用SQLServerManagementStudio修改數(shù)據(jù)庫配置2、使用T-SQL語句修改數(shù)據(jù)庫配置ALTERDATABASEdatabase_name{ADDFILE<>[,…n][TO]|ADDLOGFILE<>[,…n]|REMOVE|ADD|REMOVEMODIFYFILE<>MODIFYNAME=new_database_nameMODIFY{}}例題用T-SQL命令將xsgl重命名為“學(xué)生管理系統(tǒng)”？ALTERDATABASExsglMODIFYNAME=學(xué)生管理系統(tǒng)練習(xí)為“學(xué)生管理系統(tǒng)”增加一個數(shù)據(jù)庫文件，文件名為“成績表.ndf”刪除數(shù)據(jù)庫1、使用SQLServerManagementStudio刪除數(shù)據(jù)庫2、使用T-SQL語句刪除數(shù)據(jù)庫語法：DROPDATABASEdatabase_name例題 刪除已創(chuàng)建的“學(xué)生管理系統(tǒng)”？DROPDATABASE學(xué)生管理系統(tǒng)GO2．2操作數(shù)據(jù)屬于數(shù)據(jù)操作語言（DML）的功能：SELECT、INSERT、UPDATE、DELETE共4個語句。最重要的是SELECT，希望大家熟練掌握。2．2．1打開一個數(shù)據(jù)庫

在對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行操作之前，必須先使用USE命令打開數(shù)據(jù)庫，并使用GO語句作為結(jié)束行。例如：USEXKGO2．2．2SELECT語句

SELECT語句主要用于檢索數(shù)據(jù)，也可以用來向局部變量賦值或者調(diào)用一個函數(shù)。常用的語法為：SELECT選擇列表FROM表名WHERE檢索條件GROUPBY分類列HAVING分類后的篩選條件ORDERBY輸出排序列COMPUTER[BY]在查詢結(jié)果后附加統(tǒng)計查詢。Computerby前必有orderby排序。下面詳細(xì)介紹SELECT語句的各種使用方法。一、在列名中使用*在列名中使用星號*，則從FROM子句中指定的表或視圖中檢索并返回所有列二、在列名之前使用DISTINCT消除重復(fù)行在列名之前使用DISTINCT，則會消除輸出列的值都相同的那些行。如有重復(fù)行，則只顯示第一行。與DISTINCT相反的是ALL，表示顯示全部滿足條件的那些行

三、在列名之前使用TOPn[PERCENT]返回前n[%]行使用TOP，可以從結(jié)果集中僅返回前n行。如果指定了PERCENT，則僅返回前n%行，此時n必須介于0-100之間。如果語句中有ORDERBY，則先執(zhí)行ORDERBY排序后返回前n[%]行。四、修改檢索結(jié)果中列的標(biāo)題方法一：新標(biāo)題=列名方法二：列名新標(biāo)題方法三：列名AS新標(biāo)題列名不能加引號，當(dāng)新標(biāo)題中含有空格時必須加引號。單引號和雙引號都可以。五在檢索結(jié)果中顯示字符串（必須加單引號）在SELECT的列名中，將要增加的字符串用單引號括起來，顯示結(jié)果的該列將全部顯示指定的字符串。六、在WHERE子句中給出檢索的條件條件結(jié)果為邏輯值，可以包含比較運(yùn)算符（等于=、不等于<>或!=、大于>、大于等于>=或!<、小于<、小于等于<=或!>）和邏輯運(yùn)算符（與AND、或OR、非NOT）。七、列為表達(dá)式在列名中可以使用表達(dá)式（列名、函數(shù)、常數(shù)、表達(dá)式等）。八、使用ORDERBY子句重新排序檢索結(jié)果可以使用ORDERBY子句對檢索結(jié)果重新進(jìn)行排序，可以規(guī)定升序或降序，方法是使用關(guān)鍵字ASC或DESC。如果省略ASC或DESC，系統(tǒng)則默認(rèn)為升序。可以在ORDERBY子句中指定多個列，檢索結(jié)果首先按第1列進(jìn)行排序，對第1列值相同的那些數(shù)據(jù)行，再按照第2列排序……依此類推。九、在WHERE中使用IN關(guān)鍵字使用IN關(guān)鍵字進(jìn)行檢索比使用多個OR運(yùn)算符進(jìn)行檢索更為簡單，并且易于閱讀和理解。也可以使用NOTIN，功能與IN相反。十、在WHERE中使用LIKE關(guān)鍵字LIKE關(guān)鍵字是一個匹配運(yùn)算符，它與字符串表達(dá)式相匹配，字符串表達(dá)式被定義為字符串和通配符。字符串和通配符必須括在單引號中。SQL的4個通配符為：%——百分號，匹配包含0個或多個字符的字符串。_——下劃線，匹配任何單個的字符。[]——排列通配符，匹配任何在范圍或集合中的單個字符，例如[m-p]匹配的是mnop單個字符。[^]——不在范圍之內(nèi)的字符，匹配任何不在范圍或集合之內(nèi)的單個字符。要查找通配符本身，需將它們用方括號括起來。十一、在WHERE中使用ISNULL關(guān)鍵字在WHERE中使用ISNULL關(guān)鍵字檢索列中沒有賦值的行。十二、在WHERE中使用比較運(yùn)算符和邏輯運(yùn)算符實現(xiàn)檢索某一范圍內(nèi)的信息在WHERE中使用比較運(yùn)算符和邏輯運(yùn)算符實現(xiàn)檢索某一范圍內(nèi)的信息。也可以單獨(dú)使用比較運(yùn)算符。十三、在WHERE中使用BETWEEN關(guān)鍵字實現(xiàn)檢索某一范圍內(nèi)的信息BETWEEN關(guān)鍵字總是和AND一起使用，用于檢索某一范圍內(nèi)的信息。也可以使用NOTBETWEEN。使用格式是：BETWEEN下限AND上限。相當(dāng)于>=下限AND<=上限十四、在WHERE之后使用COMPUTE子句COMPUTE用于計算總計或進(jìn)行分組小計，總計值或小計值將作為附加新行出現(xiàn)在檢索結(jié)果中。該子句用在WHERE子句之后。第3章Transact-SQL語言(2)十五、在WHERE之后使用COMPUTEBY子句使用COMPUTEBY子句對BY后面給出的列進(jìn)行分組顯示，并計算該列的分組小計。使用COMPUTEBY子句時必須使用ORDERBY對COMPUTEBY中BY指定的列進(jìn)行排序。使用格式是：COMPUTE函數(shù)BY分類列名。十六、在WHERE之后使用GROUPBY子句將檢索結(jié)果按照GROUPBY后面指定的列進(jìn)行分組該子句寫在WHERE子句的后面當(dāng)在SELECT子句中包含有聚合函數(shù)時，最適合使用GROUPBY子句。SELECT子句中的選項列表中出現(xiàn)的列包含在聚合函數(shù)中或者包含在GROUPBY子句中。十七、在GROUPBY之后使用HAVING子句HAVING子句用于限定組或聚合函數(shù)的檢索條件該子句常常用在GROUPBY之后十八、在WHERE中使用子查詢子查詢是在查詢中包含另一個查詢的查詢可以使用子查詢代替表達(dá)式子查詢只能返回一列數(shù)據(jù)，有時只有返回一行數(shù)據(jù)。子查詢要用圓括號括起來。十九、使用UNION運(yùn)算符UNION運(yùn)算符用于將兩個或多個檢索結(jié)果合并成一個結(jié)果，當(dāng)使用UNION時，需遵循兩個規(guī)則：1．所有查詢中的列數(shù)和列的順序必須相同。2．所有查詢中按順序?qū)?yīng)列的數(shù)據(jù)類型必須兼容。如果希望重新排序多個檢索結(jié)果的合并結(jié)果，則在最后的SELECT語句中使用ORDERBY子句。二十、對多個表或視圖進(jìn)行檢索

1．笛卡兒積使用格式：SELECT*FROM表1，表2，……。結(jié)果的行數(shù)是各表行數(shù)的積，結(jié)果的列數(shù)是各表列數(shù)的和。2．在WHERE中使用連接條件（1）連接條件為避免產(chǎn)生笛卡兒積，并且得到所需要的檢索結(jié)果，必須使用WHERE子句給出連接條件。一般來說，對N個表（或視圖）的檢索要有N-1個連接條件。多表檢索中的表或視圖可以在同一個數(shù)據(jù)庫中，也可以來自不同的數(shù)據(jù)庫。在FROM子句中最多可使用16個表或視圖。（2）連接類型連接的類型有內(nèi)連接、外連接和交叉連接。內(nèi)連接就是只包含滿足連接條件的數(shù)據(jù)行，它主要有自然連接和相等連接等形式。外連接是連接運(yùn)算的擴(kuò)展，可以處理缺失信息，它又分為左外連接、右外連接和全連接三種形式。交叉連接是一種很少使用的連接，兩個表的交叉連接的結(jié)果集的總行數(shù)等于所連接的兩個表的行數(shù)的積，交叉連接通常沒有實際的意義。3．相等連接（內(nèi)連接）相等連接是將要連接的列作相等比較后所作的連接，相等連接總會產(chǎn)生冗余，因為連接的列要顯示兩次。內(nèi)連接需在FROM子句中指明：FROM左表名INNERJOIN右表名ON連接條件4．自然連接自然連接是將要連接的列作相等比較后的連接，但是連接的列只顯示一次。自然連接消除了相等連接產(chǎn)生的冗余。5．帶有選擇條件的連接6．比較連接在進(jìn)行連接時不必使用等號進(jìn)行連接，而采用比較運(yùn)算符。7．自連接自連接就是一個表和它自身進(jìn)行連接。表名在FROM子句中出現(xiàn)兩次，可以對表定義一個別名，在SELECT子句中引用的列名也要使用表的別名進(jìn)行限定。左連接：左連接首先計算兩個表的自然連接，再取出左表中與右表任一數(shù)據(jù)行都不匹配的那些行，用空值填充所有來自右表的那些列，再把增加的這些行增加到自然連接的結(jié)果集中。左連接需在FROM子句中指明：FROM左表名LEFTJOIN右表名ON連接條件（2）右連接右連接與左連接相對稱：右連接首先計算兩個表的自然連接，再取出右表中與左表任一數(shù)據(jù)行都不匹配的那些行，用空值填充所有來自左表的那些列，再把增加的這些行增加到自然連接的結(jié)果集中。右連接需在FROM子句中指明：FROM左表名RIGHTJOIN右表名ON連接條件（3）完全連接為了包含兩個表中都不匹配的數(shù)據(jù)行，可以使用完全連接，它完成左連接和右連接的操作，包括了左表和右表中所有不滿足條件的行。完全連接需在FROM子句中指明：FROM左表名FULLJOIN右表名ON連接條件8．外連接

9．交叉連接交叉連接就是將連接的兩個表的所有行進(jìn)行組合，形成一個結(jié)果集，該結(jié)果集的列數(shù)等于兩個表的列數(shù)和，行數(shù)等于兩個表的行數(shù)積。結(jié)果與笛卡兒積相同。交叉連接需在FROM子句中指明：FROM表1CROSSJOIN表2二十一、使用表別名可以給表定義別名，以方便檢索時對列的引用和簡化連接條件的書寫。定義別名的方法：在FROM子句中將要定義別名的表名用空格間隔，然后緊隨所定義的別名。即表名在前別名在后，中間中空格隔開。二十二、在WHERE中使用EXISTS關(guān)鍵字EXISTS子句用于測試跟隨的子查詢中的行是否存在，如果存在，則返回TRUE（真）。2．2．3向表中輸入數(shù)據(jù)

一、使用INSERT語句INSERT語句用來向表中或視圖或輸入新的數(shù)據(jù)行。輸入數(shù)據(jù)的方法有兩種：使用INSERT語句和使用企業(yè)管理器。INSERT語句的簡單語法：INSERT[INTO]表名{[(列名表)]{VALUES({DEFAULT|NULL|表達(dá)式}[,……n])|執(zhí)行過程}列的個數(shù)必須與VALUES子句中給出的值的個數(shù)相同；數(shù)據(jù)類型必須和列的數(shù)據(jù)類型相對應(yīng)。當(dāng)省略列名表時，其值必須是表的所有列，并且順序一致。二、使用企業(yè)管理器輸入數(shù)據(jù)打開表返回所有行。輸入數(shù)據(jù)后關(guān)閉窗口即可。三、在INSERT語句中使用SELECT子句插入數(shù)據(jù)INSERT語句的特點是每一次只能輸入一行數(shù)據(jù)。在INSERT語句中使用SELECT子句可以將表中數(shù)據(jù)行插入到表中，并且可以一次輸入多行數(shù)據(jù)。使用SELECT子句插入數(shù)據(jù)的語法格式：INSERT表名[(列名表)]SELECT列表FROM源表WHERE條件注意：1．INSERT語句中和FROM子句中所使用的表可以相同也可以不相同，但必須是已存在的表。2．INSERT中指定的表和SELECT子句得到的結(jié)果集一定要兼容，即列的數(shù)量和順序必須相同，列的數(shù)據(jù)類型和長度要相同，或者進(jìn)行轉(zhuǎn)換。3．如果省略列名表，則SELECT中的列表必須與表名中的所有列保持一致。使用SELECTINTO語句允許用戶定義一個新表，并且把SELECT的數(shù)據(jù)插入到新表中。前面的3種方法的共同點就是在數(shù)據(jù)插入之前表已經(jīng)存在。而使用SELEC