數據庫技術與應用第4章關系數據庫標準語言（1）

第四章關系數據庫標準語言SQL4.1 SQL的基本知識和特點SQL語言集數據查詢、數據操縱、數據定義和數據控制功能于一體1．綜合統一2．高度非過程化3．用同一種語法結構提供兩種使用方式4．語言簡潔，易學易用

第四章關系數據庫標準語言SQL1．綜合統一SQL語言則集數據定義語言（DDL）、數據操縱語言（DML）、數據控制語言（DCL）的功能于一體，語言風格統一，可以獨立完成數據庫生命周期中的全部活動

第四章關系數據庫標準語言SQL2．高度非過程化非關系數據模型的數據操縱語言是面向過程的語言，要完成某項請求，必須指定存取路徑。而用SQL語言進行數據操作，用戶只需提出“做什么”，而不必指明“怎么做”。

第四章關系數據庫標準語言SQL3．用同一種語法結構提供兩種使用方式SQL語言既是自含式語言，又是嵌入式語言。作為自含式語言，它能夠獨立地用于聯機交互的使用方式，用戶可以在終端鍵盤上直接鍵入SQL命令對數據庫進行操作

第四章關系數據庫標準語言SQL4．語言簡潔，易學易用SQL語言功能極強，但由于設計巧妙，語言十分簡潔，完成數據定義、數據操縱、數據控制的核心功能只用了9個動詞

第四章關系數據庫標準語言SQLSQL功能動詞數據查詢SELECT數據定義CREATE，DROP，ALTER數據操縱INSERT，UPDATE，DELETE數據控制GRANT，REVOKE

使用SQLServer創建數據庫中的表時，要對表中的每一列定義一種數據類型，數據類型決定了表中的某一列可以存放什么數據。除了定義表需要指定數據類型外，使用視圖、存儲過程、變量、函數等都需要用到數據類型。

SQLServer提供了豐富的系統定義的數據類型，用戶還可以在此基礎上自己定義數據類型。

第四章關系數據庫標準語言SQL1整型數據類型1.bigint類型存儲大小:8個字節，取值范圍:-263～263-12.int類型存儲大小:4個字節，取值范圍:-231~231-13.smallint類型存儲大小:2個字節，取值范圍:-215～215-14.tinyint類型存儲大小:1個字節，取值范圍:0～2552定點數據類型

定點數據類型用于表示定點實數，包括numeric和decimal類型。Numeric等價于decimal。格式：

decimal[(p[,s])]numeric[(p[,s])]說明:

p:表示精度，指定小數點左邊和右邊十進制數字的最大位數，取值在1到38之間，缺省值為18；s:指定小數點右邊十進數的最大位數，取值在0到p之間，缺省值為0。表示范圍:-1038+1～1038-12定點數據類型

精度:decimal和numeric類型數據的存儲字節數與精度p有關。具體如下表:精度p存儲字節數1～9510～19920～281329～38173浮點數據類型

浮點數據類型采用科學計數法存儲十進制小數，包括real和float數據類型。尾數位數n精度存儲字節數1～247位425～53或省15位8

表示范圍:-1.79E+308～1.79E+3081.float類型格式:

float[(n)]說明:n:科學記數法尾數的位數，具體如下表：3浮點數據類型2.real類型存儲大小:4個字節取值范圍:-3.40E+38～3.40E+38

精度:7位在SQLServer中，real的同義詞為float(24)。4字符數據類型

分為Unicode字符數據類型和非Unicode字符數據類型.非Unicode字符數據類型:允許使用由特定字符集定義的字符。字符集在安裝SQLServer時選擇，不能更改。非Unicode字符數據類型:char、varchar和text。

Unicode字符數據類型:可存儲由Unicode標準定義的任何字符，包含由不同字符集定義的所有字符。需要相當于非Unicode數據類型兩倍的存儲空間。Unicode字符數據數據類型包括:nchar、nvarchar和ntext。對于存儲來源于多種字符集的字符的列，可采用這些數據類型。分為固定長度和可變長度字符數據類型。4字符數據類型1.char類型格式:

char[(n)]功能:

定義長度為n個字節的固定長度非Unicode字符數據，每個字符占一個字節。說明:n:1~8000

存儲大小:n個字節（n個字符）2.varchar類型格式:

varchar[(n)]功能:定義長度最多為n個字節的可變長度非Unicode字符數據,每個字符占一個字節。說明:n:1~8000。存儲大小:輸入字符的實際長度。長度可為零4字符數據類型3.nchar類型格式:

nchar[(n)]功能:定義包含n個字符的固定長度Unicode字符數據說明:n:1~4000

存儲大小:2n個字節4.text類型格式:

text功能:當存儲字符型數據非常大時，大于8000個字節，使用text類型說明:

最大長度可達字符型數據舉例例4.1Createtablechars-example(char_1char(5),Vchar_1varchar(5),Text_1text)5

日期和時間數據類型日期和時間數據類型用于存儲日期和時間的結合體。包括datetime和smalldatetime兩種類型。1.datetime類型存儲大小:8個字節表示范圍:1753年1月1日零時~9999年12月31日23時59分59秒。例:01/01/9823:59:592000-5-2912:30:482.smalldatetime

存儲大小:4個字節表示范圍:1900年1月1日~2079年6月6日。例:2000/05/0812:352000-05-2912:35

6

圖形（image）數據類型image數據類型用于存儲可變長度二進制數據，其長度界于0到231-1個字節之間。7

貨幣數據類型

貨幣數據類型包括money和smallmoney數據類型。貨幣數據存儲的精確度為四位小數。1.money類型存儲大小:8個字節表示范圍:-922,337,203,685,477.5808~+922,337,203,685,477.58072.smallmoney類型存儲大小:4個字節表示范圍:-214,748.3648~214,748.36478二進制數據類型

二進制數據類型又可以分為binary和varbinary類型。1.binary類型格式:

binary[(n)]

功能:

定義固定長度的n個字節二進制數據，當輸入的二進制數據長度小于n時，余下部分填充0。

說明:

n:1~8000

存儲大小:n+4個字節8二進制數據類型2.varbinary類型格式:

varbinary[(n)]功能:定義n個字節可變長度二進制數據。說明:

n:1~8000存儲大小:為實際輸入數據長度加4個字節，而不是n個字節。輸入的數據長度可能為0字節。

如果在數據定義或變量定義語句中使用時沒有指定n，則默認長度n為1。如果在CAST函數中使用時沒有指定n，則默認長度n為30。9其他數據類型1.timestamp類型(時間戳數據類型)

存儲大小:8個字節。

時間戳類型的數據用于提供數據庫范圍內的惟一值，反映數據庫中數據修改的相對順序，相當于一個單調上升的計數器。當表中的某列定義為timestamp類型時，在對表中某行進行修改或添加行時，相應timestamp類型列的值會自動被更新。9其他數據類型2.uniqueidentifier類型

用于存儲一個16字節長的二進制數據，它是SQLServer根據計算機網絡適配器和CPU時鐘產生的全局惟一標識符（GloballyUniqueIdentifier，GUID），該數字可以通過調用SQLServer的NEWID函數獲得。

GUID是一個唯一的二進制數字，世界上的任何兩臺計算機都不會生成重復的GUID值。GUID主要用于在擁有多個節點、多臺計算機的網絡中，分配必須具有唯一性的標識符。9其他數據類型3.sql_variant類型

用于存儲除text、ntext、image、timestamp和sql_variant外的其它任何合法的數據。4.table類型

用于存儲對表或者視圖處理后的結果集。這種新的數據類型使得用變量就可以存儲一個表，從而使函數或過程返回查詢結果更加方便、快捷。5.cursor類型

cursor類型是變量或存儲過程的OUTPUT參數的一種數據類型，這些參數包含對游標的引用。10用戶自定義數據類型用戶自定義數據類型并不是真正的數據類型，它只是提供了一種加強數據庫內部和基本數據類型之間一致性的機制?？梢允褂孟到y存儲過程sp_addtype來創建用戶自定義數據類型。語法是：sp_addtype[@type_name=]type,[@phystype=]system_dada_type10用戶自定義數據類型創建兩個用戶自定義數據類型UseliziExecsp_addtypephonenum,’varchar(24)’,’notnull’Execsp_addtypefax,’varchar(24)’,’null’刪除用戶自定義數據類型Sp_droptypefax

第四章關系數據庫標準語言SQL4.2.2數據定義語句介紹1.建立數據庫CREATEDATABASE<數據庫名>

Createdatabaselizi4.2.2數據定義1定義語句格式CREATETABLE<表名>

（<列名><數據類型>[<列級完整性約束條件>][，<列名><數據類型>[<列級完整性約束條件>]]…[，<表級完整性約束條件>]）；<表名>：所要定義的基本表的名字<列名>：組成該表的各個屬性（列）<列級完整性約束條件>：涉及相應屬性列的完整性約束條件<表級完整性約束條件>：涉及一個或多個屬性列的完整性約束條件例題[例4.3]建立一個“學生”表Student，它由學號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成。其中學號不能為空，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(5)NOTNULL，

SnameCHAR(20)UNIQUE，

SsexCHAR(1)，

SageINT，

SdeptCHAR(15))；例題[例4.4]建立課程表。

CREATETABLECourse(CnoCHAR(10)NOTNULL，

CnameCHAR(20)notnull，

CcreditTinyintCheck(ccredit>0)，

PcnoChar(10)，

PrimaryKey(Cno))；定義基本表（續）常用完整性約束主碼約束：PRIMARYKEY唯一性約束：UNIQUE非空值約束：NOTNULL參照完整性約束PRIMARYKEY與

UNIQUE的區別？例題（續）[例4.5]建立一個“學生選課”表SC，它由學號Sno、課程號Cno，修課成績Grade組成，其中(Sno,Cno)為主碼CREATETABLESC(SnoCHAR(5),CnoCHAR(3),GradeTinyintCheck(grade>=10andgrade<=100),Primarykey(Sno,Cno),Foreignkey(sno)referencesstudent(Sno),Foreignkey(cno)referencescourse(cno));三、刪除基本表 DROPTABLE<表名>;

基本表刪除時數據、表上的索引都刪除表上的視圖往往仍然保留，但無法引用刪除基本表時，系統會從數據字典中刪去有關該基本表及其索引的描述

例題[例4.6]刪除Student表

DROPTABLE

Student;修改基本表ALTERTABLE<表名>[ADD<新列名><數據類型>[完整性約束]][DROP<完整性約束名>][MODIFY<列名><數據類型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性約束條件DROP子句：刪除指定的完整性約束條件MODIFY子句：用于修改列名和數據類型例題[例4.7]向Student表增加“入學時間”列，其數據類型為日期型。ALTERTABLEStudentADDeDATE；不論基本表中原來是否已有數據，新增加的列一律為空值。

例題[例4.8]將姓名字段長度AlterTableStudentaltercolumnsnamechar(22);修改原有的列定義有可能會破壞已有數據。[例4.9]刪除入學時間AlterTableStudentDropColumne;查詢概述4.3.1概述4.3.2單表查詢4.3.3連接查詢4.3.4嵌套查詢4.3.5集合查詢4.3.6小結4.3.1概述語句格式SELECT[ALL|DISTINCT]<目標列表達式>[，<目標列表達式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]]；

語句格式SELECT子句：指定要顯示的屬性列FROM子句：指定查詢對象(基本表或視圖)WHERE子句：指定查詢條件

GROUPBY子句：對查詢結果按指定列的值分組，該屬性列值相等的元組為一個組。通常會在每組中作用集函數。HAVING短語：篩選出只有滿足指定條件的組ORDERBY子句：對查詢結果表按指定列值的升序或降序排序示例數據庫學生-課程數據庫學生表：Student(Sno，Sname，Ssex，Sage，Sdept)課程表：Course(Cno，Cname，Cpno，Ccredit)

學生選課表：SC(Sno，Cno，Grade)4.3.2單表查詢

查詢僅涉及一個表，是一種最簡單的查詢操作一、選擇表中的若干列二、選擇表中的若干元組三、對查詢結果排序四、使用集函數五、對查詢結果分組

查詢指定列[例4.10]查詢全體學生的學號與姓名。SELECTSno，SnameFROMStudent；

[例4.11]查詢全體學生的姓名、學號、所在系。SELECTSname，Sno，SdeptFROMStudent；查詢全部列[例4.12]查詢全體學生的詳細記錄。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；

或SELECT*FROMStudent；3.查詢經過計算的值SELECT子句的<目標列表達式>為表達式算術表達式字符串常量函數列別名等3.查詢經過計算的值[例4.13]查全體學生的姓名及其出生年份。SELECTSname，2008-SageFROMStudent；

輸出結果：

Sname2008-Sage----------------------

李勇1988

劉晨1989

王名1990

張立19893.選擇表中的若干元組[例4.14]查詢全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。SELECTSname，‘YearofBirth:’，2008-Sage’出生年份’，ISLOWER(Sdept)FROMStudent；例題（續）輸出結果：

Sname'YearofBirth:'2000-SageISLOWER(Sdept)----------------------------------------------

李勇YearofBirth:1988cs

劉晨YearofBirth:1989is

王敏YearofBirth:1990ma

張立YearofBirth:1989is[例]使用列別名改變查詢結果的列標題SELECTSnameNAME，'YearofBirth:’

BIRTH，

2000-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROM

Student；輸出結果：

NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------

李勇YearofBirth:1988cs

劉晨YearofBirth:1989is

王名YearofBirth:1990ma

張立YearofBirth:1989is二、選擇表中的若干元組消除取值重復的行查詢滿足條件的元組1.消除取值重復的行在SELECT子句中使用DISTINCT短語假設SC表中有下列數據

SnoCnoGrade---------------------9500119295001285950013889500229095002380ALL與

DISTINCT

[例4.15]查詢選修了課程的學生學號。(1)SELECTSnoFROMSC;

或(默認ALL)SELECTALLSnoFROMSC;

結果：Sno-------9500195001950019500295002例題（續）(2)SELECTDISTINCTSnoFROMSC;

結果：

Sno-------9500195002例題（續）注意DISTINCT短語的作用范圍是所有目標列例：查詢選修課程的各種成績錯誤的寫法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正確的寫法

SELECTDISTINCTCno，GradeFROMSC;

2.查詢滿足條件的元組WHERE子句常用的查詢條件表4.2常用的查詢條件查

詢

條

件謂

詞比

較=，>，<，>=，<=，!=，<>，!>，!<；NOT

+

上述比較運算符確定范圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空

值ISNULL，ISNOTNULL多重條件AND，OR(1)比較大小在WHERE子句的<比較條件>中使用比較運算符=，>，<，>=，<=，!=或<>，!>，!<，邏輯運算符NOT+比較運算符[例4.17]查詢所有年齡在20歲以下的學生姓名及其年齡。

SELECTSname，Sage

FROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；

(1)比較大小在WHERE子句的<比較條件>中使用比較運算符=，>，<，>=，<=，!=或<>，!>，!<，邏輯運算符NOT+比較運算符[例4.18]查詢考試成績有不及格歲的學生學號。

SELECTdistinctSno

FROMCourseWHEREgrade<60；(2)確定范圍使用謂詞BETWEEN…AND…NOTBETWEEN…AND…[例4.19]查詢年齡在20~23歲（包括20歲和23歲）之間的學生的姓名、系別和年齡。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

例題（續）[例4.20]查詢年齡不在20~23歲之間的學生姓名、系別和年齡。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；

(3)確定集合使用謂詞IN<值表>,NOTIN<值表>

<值表>：用逗號分隔的一組取值[例4.21]查詢信息系（IS）、數學系（MA）和計算機科學系（CS）學生的姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');(3)確定集合[例4.22]查詢既不是信息系、數學系，也不是計算機科學系的學生的姓名和性別。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串當匹配模板為固定字符串時，可以用=運算符取代LIKE謂詞用!=或<>運算符取代NOTLIKE謂詞通配符%(百分號)代表任意長度（長度可以為0）的字符串例：a%b表示以a開頭，以b結尾的任意長度的字符串。如acb，addgb，ab等都滿足該匹配串_(下橫線)代表任意單個字符例：a_b表示以a開頭，以b結尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串ESCAPE短語：當用戶要查詢的字符串本身就含有%或_時，要使用ESCAPE'<換碼字符>'短語對通配符進行轉義。例題1)匹配模板為固定字符串[補充例]查詢學號為95001的學生的詳細情況。

SELECT*FROMStudentWHERESnoLIKE'95001'；等價于：

SELECT*FROMStudentWHERESno='95001'；例題（續）2)匹配模板為含通配符的字符串[例4.23]查詢所有姓劉學生的姓名、學號和性別。

SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘劉%’；例題（續）匹配模板為含通配符的字符串（續）[補充例]查詢姓"歐陽"且全名為三個漢字的學生的姓名。

SELECTSnameFROMStudentWHERESnameLIKE'歐陽__'；例題（續）匹配模板為含通配符的字符串（續）[例4.24]查詢名字中第2個字為“大”或”小"字的學生的姓名和學號。

SELECTSname，SnoFROMStudentWHERESnameLIKE‘__[大小]%'；例題（續）匹配模板為含通配符的字符串（續）[例4.25]查詢所有不姓劉的學生姓名。

SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'劉%'；例題（續）3)使用換碼字符將通配符轉義為普通字符

[補充例]查詢DB_Design課程的課程號和學分。

SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'

ESCAPE'\'例題（續）使用換碼字符將通配符轉義為普通字符(續)[補充例]查詢以"DB_"開頭，且倒數第3個字符為i的課程的詳細情況。

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；(5)涉及空值的查詢

使用謂詞ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例4.27]某些學生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢缺少成績的學生的學號和相應的課程號。

SELECTSno，CnoFROMSCWHEREGradeISNULL；例題(續)[例4.28]查所有有成績的學生學號和課程號。

SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；(6)多重條件查詢用邏輯運算符AND和OR來聯結多個查詢條件

AND的優先級高于OR

可以用括號改變優先級可用來實現多種其他謂詞

[NOT]IN[NOT]BETWEEN…AND…例題[例4.29]查詢計算機系年齡在20歲以下的學生姓名。

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；改寫[補充例]查詢信息系（IS）、數學系（MA）和計算機科學系（CS）學生的姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改寫為：SELECTSname，SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS'；三、對查詢結果排序

使用ORDERBY子句可以按一個或多個屬性列排序升序：ASC；降序：DESC；缺省值為升序當排序列含空值時ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示對查詢結果排序（續）[例4.30]查詢選修了3號課程的學生的學號及其成績，查詢結果按分數降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；對查詢結果排序（續）[例4.31]查詢全體學生情況，查詢結果按所在系的系號升序排列，同一