第講數(shù)據(jù)定義

會計學1第講數(shù)據(jù)定義3.1SQL概述SQL的產(chǎn)生和發(fā)展1974年Boyce和Chamberlin提出1975～1979年在IBM的SystemR上實現(xiàn)1986年ANSI的數(shù)據(jù)庫委員會批準了SQL作為關系數(shù)據(jù)庫語言的美國標準1987年ISO也通過該標準第1頁/共46頁3.1SQL概述SQL的特點1.綜合統(tǒng)一2.高度非過程化3.面向集合的操作方式4.以同一種語法結構提供兩種使用方法5.語言簡潔，易學易用第2頁/共46頁第3頁/共46頁SQL的基本概念支持SQL的RDBMS支持關系數(shù)據(jù)庫的三級模式結構。第4頁/共46頁3.2學生－課程數(shù)據(jù)庫包括以下三個表：學生表：Student(Sno,Sname,Ssex,Sage,Sdept)課程表：Course(Cno,Cname,Cpno,Ccredit)選課表：SC(Sno,Cno,Grade)各個表的數(shù)據(jù)示例參見課本P82第5頁/共46頁3.3數(shù)據(jù)定義

SQL的數(shù)據(jù)定義功能包括對基本表(table)、視圖(view)、和索引(index)的定義。第6頁/共46頁

<列名><數(shù)據(jù)類型>[列級完整性約束條件][，<列名><數(shù)據(jù)類型>[列級完整性約束條件]]

…[，<表級完整性約束條件>]<表名>：所要定義的基本表的名字<列名>：組成該表的各個屬性（列）<列級完整性約束條件>：涉及相應屬性列的完整性約束條件<表級完整性約束條件>：涉及一個或多個屬性列的完整性約束條件1定義基本表的一般格式為：一、基本表的定義、修改與刪除CREATETABLE

<表名>（）；第7頁/共46頁1.整數(shù)數(shù)據(jù)類型：依整數(shù)數(shù)值的范圍大小，有BIT,INT,SMALLINT。2.精確數(shù)值類型：用來定義可帶小數(shù)部分的數(shù)字，有NUMERIC和DECIMAL，如：123.0、8000.563.近似浮點數(shù)值數(shù)據(jù)類型：當數(shù)值的位數(shù)太多時，可用此數(shù)據(jù)類型取其近似值，用FLOAT和REAL兩種。如：1.23E+10SQL支持的數(shù)據(jù)類型

不同的DBMS支持的數(shù)據(jù)類型不完全相同，其中使用最多的是字符型數(shù)據(jù)和數(shù)值型數(shù)據(jù)。例如，SQLSERVER中常用的數(shù)據(jù)類型有：第8頁/共46頁SQL支持的數(shù)據(jù)類型4.字符串數(shù)據(jù)類型：用來表示字符串的字段。包括：CHAR,VARCHAR,TEXT三種5.日期時間數(shù)據(jù)類型：用來表示日期與時間，依時間范圍與精確程度可分為DATETIME與SMALLDATETIME兩種。如：1998-06-0815:30:00第9頁/共46頁常用完整性約束實體完整性：PRIMARYKEY參照完整性：FOREIGNKEY…

REFERENCES…用戶定義完整性：NOTNULL:列值非空UNIQE：列值唯一CHECK：檢查列值是否滿足一個布爾表達式第10頁/共46頁例1

建立一個“學生”表Student，Student(Sno,Sname,Ssex,Sage,Sdept)其中學號不能為空，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(10)UNIQUE，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20))；第11頁/共46頁CREATETABLESC(SNOCHAR(5)，CNOCHAR(5)，SCORESMALLINT,PRIMARYKEY(SNO,CNO)，F(xiàn)OREIGNKEYSNOREFERENCESS(SNO),FOREIGNKEYCNOREFERENCESC(CNO));練習：解釋如下基本表的定義.第12頁/共46頁例2建立一個課程表Course,并標明主碼和外碼。Course(Cno,Cname,Cpno,Ccredit)CREATETABLECourse(CnoCHAR(5)primarykey,CnameCHAR(20),CpnoCHAR(5),CcreditSMALLINT,ForeignkeyCpnoreferencesCourse(Cno));第13頁/共46頁2修改基本表ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束名>][MODIFY<列名><數(shù)據(jù)類型>]；<表名>：要修改的基本表ADD子句：增加新列DROP子句：刪除指定的完整性約束條件MODIFY子句：用于修改列的數(shù)據(jù)類型第14頁/共46頁例3向Student表增加“入學時間”列，其數(shù)據(jù)類型為日期型。

ALTER

TABLEStudent

ADDScomeDATE；例4將Student表年齡的數(shù)據(jù)類型改為短整數(shù)。ALTERTABLEStudentMODIFYSageSMALLINT；第15頁/共46頁DROPTABLE<表名>[RESTRICT\CASCADE];RESTRICT(缺省):該表的刪除有限制條件。如，該表不能被其他表的約束所引用，不能有視圖、觸發(fā)器、存儲過程。CASCADE:該表的刪除沒有限制條件。刪除基本表時，相關依賴對象一起被刪除。

3刪除基本表例5刪除Student表

DROPTABLEStudent;不同數(shù)據(jù)庫產(chǎn)品在遵循SQL標準的基礎上具體實現(xiàn)細節(jié)和處理策略會與標準有差別。例如，P89第16頁/共46頁第17頁/共46頁二、索引的建立與刪除索引是基本表的目錄，建立索引是加快查詢速度的有效手段建立索引DBA或表的屬主（即建立表的人）根據(jù)需要建立有些DBMS自動建立以下列上的索引

PRIMARYKEYUNIQUE維護索引

DBMS自動完成

使用索引

DBMS自動選擇是否使用索引以及使用哪些索引第18頁/共46頁語句格式CREATE[UNIQUE][CLUSTER]

INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)；用<表名>指定要建索引的基本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號分隔用<次序>指定索引表中索引值的排列次序，升序：ASC，降序：DESC。缺省值為ASC。UNIQUE:唯一性索引。不允許索引列有同值；若已有同值，則不允許建立此索引。CLUSTER：聚簇索引。表中元組按索引項的值排序并物理地聚集在一起。1.建立索引第19頁/共46頁[例6]為學生-課程數(shù)據(jù)庫中的Student，Course，SC三個表建立索引。其中Student表按學號升序建唯一索引，Course表按課程號升序建唯一索引，SC表按學號升序和課程號降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；第20頁/共46頁練習：解釋如下索引的含義：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一個聚簇索引，而且Student表中的記錄將按照Sname值的升序存放第21頁/共46頁2.刪除索引DROPINDEX<索引名>；刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關該索引的描述。[例7]刪除Student表的Stusname索引。

DROPINDEXStusname；第22頁/共46頁索引的有關說明可以動態(tài)地定義索引，即可以隨時建立和刪除索引；一個表上可建多個索引；不允許用戶在數(shù)據(jù)操作中引用索引，索引如何使用完全由系統(tǒng)決定；應在使用頻率高、經(jīng)常進行連接的列上建立索引；索引可提高查詢效率，但索引過多耗費空間，且降低了插入、刪除、更新的效率。第23頁/共46頁3.4數(shù)據(jù)查詢

3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5小結第24頁/共46頁示例數(shù)據(jù)庫

學生-課程數(shù)據(jù)庫e.g.P82學生表：Student(Sno，Sname，Ssex，Sage，Sdept)課程表：Course(Cno，Cname，Cpno，Ccredit)

學生選課表：SC(Sno，Cno，Grade)第25頁/共46頁概述

一般格式SELECT[ALL|DISTINCT]<目標列表達式>[，<目標列表達式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]]；

第26頁/共46頁3.4.1單表查詢

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

第27頁/共46頁1.查詢指定列[例1]查詢全體學生的學號與姓名。

[例2]查詢全體學生的姓名、學號、所在系。SELECTSno，SnameFROMStudent；SELECTSname，Sno，SdeptFROMStudent；——考慮語句的執(zhí)行過程一、選擇表中的若干列第28頁/共46頁2.查詢全部列[例3]查詢全體學生的詳細記錄。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；

或SELECT*FROMStudent；第29頁/共46頁3.查詢經(jīng)過計算的值[例4]查全體學生的姓名及其出生年份。SELECTSname，2010-SageFROMStudent；

輸出結果：

Sname2010-Sage----------------------

李勇1985劉晨1986王名1987張立1986SELECT子句的<目標列表達式>可以是屬性列，也可以表達式，如算術表達式、字符串常量、函數(shù)等第30頁/共46頁[例5]查詢全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。SELECTSname，'YearofBirth:'，2010-Sage,ISLOWER(Sdept)FROMStudent；輸出結果：

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

李勇YearofBirth:1985cs

劉晨YearofBirth:1986is

王名YearofBirth:1987ma

張立YearofBirth:1986is第31頁/共46頁使用列別名改變查詢結果的列標題SELECTSnameNAME，'YearofBirth:’BIRTH，2010-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent輸出結果：

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

李勇YearofBirth:1985cs

劉晨YearofBirth:1986is

王名YearofBirth:1987ma

張立YearofBirth:1986is第32頁/共46頁假設SC表中有下列數(shù)據(jù)

SnoCnoGrade---------------------9500119295001285950013889500229095002380二、選擇表中的若干元組

[例6]查詢選修了課程的學生學號。結果：Sno-------9500195001950019500295002

SELECTSnoFROMSC;第33頁/共46頁SELECTDISTINCTSnoFROMSC

結果：

Sno----------95001950021.消除取值重復的行第34頁/共46頁2.查詢滿足條件的元組WHERE子句常用的查詢條件第35頁/共46頁(1)比較大小在WHERE子句的<比較條件>中使用比較運算符=，>，<，>=，<=，!=或<>邏輯運算符NOT+比較運算符[例8]查詢所有年齡在20歲以下的學生姓名及其年齡。SELECTSname，SageFROMStudentWHERESage<20；第36頁/共46頁(2)確定范圍使用謂詞BETWEEN…AND…NOTBETWEEN…AND…[例10]查詢年齡在20~23歲（包括20歲和23歲）之間的學生的姓名、系別和年齡。[例11]查詢年齡不在20~23歲之間的學生姓名、系別和年齡。第37頁/共46頁(3)確定集合：謂詞IN用來查找屬性值屬于指定集合的元組。使用謂詞IN<值表>

NOTIN<值表>其中，<值表>：用逗號分隔的一組取值[例12]查詢信息系（IS）、數(shù)學系（MA）和計算機科學系（CS）學生的姓名和性別。[例13]查詢既不是信息系、數(shù)學系，也不是計算機科學系的學生的姓名和性別。第38頁/共46頁(4)字符串匹配:查找指定的屬性列值與匹配串相匹配的元組。[NOT]LIKE

‘<匹配串>’[ESCAPE

‘<換碼字符>’]通配符：%(百分號)代表任意長度（長度可以為0）的字符串例：a%b表示以a開頭，以b結尾的任意長度的字符串。如acb，addgb，ab等都滿足該匹配串_(下橫線)代表任意單個字符例：a_b表示以a開頭，以b結尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串第39頁/共46頁1)匹配串為固定字符串[例14]查詢學號為9500