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

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

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

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

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

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

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

REFERENCES…用戶(hù)定義完整性：NOTNULL:列值非空UNIQE：列值唯一CHECK：檢查列值是否滿(mǎn)足一個(gè)布爾表達(dá)式第10頁(yè)/共46頁(yè)例1

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

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

ALTER

TABLEStudent

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

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

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

PRIMARYKEYUNIQUE維護(hù)索引

DBMS自動(dòng)完成

使用索引

DBMS自動(dòng)選擇是否使用索引以及使用哪些索引第18頁(yè)/共46頁(yè)語(yǔ)句格式CREATE[UNIQUE][CLUSTER]

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

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

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

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

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

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

第26頁(yè)/共46頁(yè)3.4.1單表查詢(xún)

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

第27頁(yè)/共46頁(yè)1.查詢(xún)指定列[例1]查詢(xún)?nèi)w學(xué)生的學(xué)號(hào)與姓名。

[例2]查詢(xún)?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系。SELECTSno，SnameFROMStudent；SELECTSname，Sno，SdeptFROMStudent；——考慮語(yǔ)句的執(zhí)行過(guò)程一、選擇表中的若干列第28頁(yè)/共46頁(yè)2.查詢(xún)?nèi)苛衃例3]查詢(xún)?nèi)w學(xué)生的詳細(xì)記錄。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；

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

輸出結(jié)果：

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

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

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

李勇YearofBirth:1985cs

劉晨YearofBirth:1986is

王名YearofBirth:1987ma

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

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

李勇YearofBirth:1985cs

劉晨YearofBirth:1986is

王名YearofBirth:1987ma

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

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

[例6]查詢(xún)選修了課程的學(xué)生學(xué)號(hào)。結(jié)果：Sno-------9500195001950019500295002

SELECTSnoFROMSC;第33頁(yè)/共46頁(yè)SELECTDISTINCTSnoFROMSC

結(jié)果：

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

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

‘<匹配串>’[ESCAPE

‘<換碼字符>’]通配符：%(百分號(hào))代表任意長(zhǎng)度（長(zhǎng)度可以為0）的字符串例：a%b表示以a開(kāi)頭，以b結(jié)尾的任意長(zhǎng)度的字符串。如acb，addgb，ab等都滿(mǎn)足該匹配串_(下橫線(xiàn))代表任意單個(gè)字符例：a_b表示以a開(kāi)頭，以b結(jié)尾的長(zhǎng)度為3的任意字符串。如acb，afb等都滿(mǎn)足該匹配串第39頁(yè)/共46頁(yè)1)匹配串為固定字符串[例14]查詢(xún)學(xué)號(hào)為9500