1、數(shù)據(jù)庫原理與應(yīng)用第三章SQL語言2011年3月學(xué)習(xí)目標(biāo)本章要求：拿握對(duì)教據(jù)庫的基本操作，并了解數(shù)據(jù)庫管理糸統(tǒng)的基 本功能；熟練拿握SQL查詢語句，并運(yùn)用SQL語句兜成對(duì)數(shù)據(jù)庫的 操作本章重6:簡(jiǎn)單查詢、連揍查詢、嵌套子查詢、聚合查詢SQL教據(jù)良義語言SQL教據(jù)更新語言本章難點(diǎn)、：連接查詢、嵌套子查詢、聚合查詢內(nèi)容概要3. 1SQL概述3. 2簡(jiǎn)單查詢3. 3連接查詢3. 4嵌冬孑番詢3. 5聚合查詢3. 6集合查詢3. 7SQL 杳詢一般格式3. 8SQL 數(shù)據(jù)是義語言3. 9SQL 救據(jù)更新語言3. 10視圖本章小結(jié)3. 1 SQL概述3.1.1 SQL發(fā)展3.1.2 SQL特點(diǎn) 3.1.3

2、 SQL查詢基本概念3. 1. 1 SQL發(fā)展SQL語言是關(guān)糸數(shù)據(jù)庫的標(biāo)準(zhǔn)語言，其經(jīng)坊的發(fā)展階段如下：SQL-86:第一個(gè)SQL標(biāo)準(zhǔn).也稱為SQL-1SQL-92：也稱為SQL-2SQL-99:護(hù)展了適歸、紐發(fā).而向?qū)ο蠹夹g(shù)，也稱為SQL-3SQL-2003:也稱為SQL-4ISO/IEC 9075-1 : Framework CSQL/FrameworkJ ；ISO/IEC 9075-2：Foundation fSQLVFoundation J ；ISO/IEC 9075-3：Call Level Interface fSQL/CLU ;ISO/IEC 9075-4: Persistent

3、Stored Modules (SQL/PSM 丿；ISO/IEC 9075-9: Management of External Data fSQL/MEDj ；ISO/IEC 9075-10：Object Language Bindings fSQL/OLBJ；ISO/IEC 9075-1 I : Information and Definition Schemas (SQLVSchemataJ；ISO/IEC 9075-1 3: Java Routines and Types Using the Java Programming LanguagefSQL7JRT)；ISO/IEC 9075

4、-14：XML-Related Specifications fSQL/XMLJ ；3. 1. 1 SQL發(fā)展許多數(shù)據(jù)庫廠商都支持SQL-92的絕丸多數(shù)標(biāo)準(zhǔn)，以及SQL-99和SQL2003的部分標(biāo)準(zhǔn)， 并對(duì)SQL語言進(jìn)行了 護(hù)展， 并賦予SQL不同的名字Oracle：PL/SQLSybase. Microsoft SQL Server: Transact-SQLSQL語言由4部分組成救據(jù)定義語言DDL：定義教據(jù)庫的逆輯結(jié)構(gòu)救據(jù)操縱語言DML：對(duì)數(shù)據(jù)庫的數(shù)煽進(jìn)行檢索和更新救據(jù)控制語言DCL：對(duì)教據(jù)庫的對(duì)象進(jìn)行授權(quán)、用戶維護(hù) 、完整性規(guī)則定義和事務(wù)定義其他：嵌入式SQL語言和動(dòng)態(tài)SQL語言，規(guī)定

5、了SQL語言在 豬主語言中使用的規(guī)則。3e 1 2SQL特點(diǎn)綜合統(tǒng)一SQL語言集數(shù)據(jù)定義語言DDL,數(shù)據(jù)掾縱語言DML和數(shù)據(jù)控制語TDCL的功能于一體，語言鳳格統(tǒng)一，可以獨(dú)立完成數(shù)據(jù)庫生命 周期中的全部活動(dòng)。數(shù)據(jù)結(jié)構(gòu)的單一性決龍數(shù)據(jù)操作符的單一性，無論是實(shí)體還是實(shí) 體訶的聯(lián)糸P均采用冋一種操作軒需度非過程化非關(guān)糸數(shù)據(jù)庫模型的數(shù)據(jù)操縱語言是面向過程的語言： 完成禁項(xiàng)捺作請(qǐng)求時(shí)必須指走存取路徑SQL語言進(jìn)行數(shù)據(jù)操作，用戶只需提出“做什么”,而不必描朗“念么做”面向集合的襟作方式SQL語言釆用集合操作方式，其操作對(duì)象、查找結(jié)果都是元組的 集合；非關(guān)糸數(shù)據(jù)模型采用的是面向訶錄的操作方式，操作對(duì)象是一條

6、 記錄3e 1 2SQL特點(diǎn)同一種語出結(jié)楊提供兩種彼用芳無 SQL 諸言阮是窗含式語言.又是喪入式語言，且蠱兩種不同的使用方式 下.SQLvS T 的語法結(jié)構(gòu)基本上是一玫的。作為自令式涪言，能夠歿立的用于聯(lián)機(jī)交互的使用方式.用戶可以衣終 鶏織盤上應(yīng)接線入 SQL令令對(duì)數(shù)據(jù)庫進(jìn)行捺作作為獲人丸為言，SQL 語句能夠渕入刊嘉級(jí)語言程序中，供程序員沒計(jì) 程序時(shí)使用。語言簡(jiǎn)潔，易學(xué)易用SQL 語言的動(dòng)詞SQL 功能動(dòng)詞數(shù)據(jù)定義CREATE, DROP, ALTER數(shù)據(jù)查詢SELECT數(shù)據(jù)操縱INSERT, UPDATE DELETE數(shù)據(jù)控制GRANT, REVOKE3. 1. 3 SQL查詢基本概念S

7、QL語t支持關(guān)系數(shù)據(jù)庫管理系統(tǒng)的三級(jí)模式結(jié)構(gòu)，其中外模式對(duì)應(yīng)視圖和部分基本衣，模式對(duì)應(yīng)基本表 ,內(nèi)模式對(duì)應(yīng)存儲(chǔ)丈件3. 1. 3 SQL查詢基本概念基本恚數(shù)據(jù)庫中獨(dú)立存在的表稱為基本表；在SQL中一個(gè)關(guān)糸對(duì)應(yīng)一個(gè)基本表，一個(gè)（或多個(gè)丿基本奩對(duì)應(yīng) 一個(gè)存儲(chǔ)文件；一個(gè)表可以帶若干素引，素引也存放在存儲(chǔ)文件 中。視圖視圖是指從一個(gè)或幾個(gè)基本表（或視圖丿中導(dǎo)出的表，是虛表， 只存放祝圖的走義而不存放對(duì)應(yīng)的數(shù)摒查詢表查詢表是查詢結(jié)皋對(duì)應(yīng)的表存赭文件存緖文件是指數(shù)據(jù)庫中存放關(guān)糸的楊理文件，其邏輯結(jié)構(gòu)組成了 關(guān)糸數(shù)據(jù)庫的內(nèi)棋無，其揚(yáng)理結(jié)皋對(duì)用戶是透朗的。3. 8 SQL數(shù)據(jù)定義語言數(shù)據(jù)庫中的關(guān)系集合必須由數(shù)

8、據(jù)；t義語t-DDL義 ,包括：教據(jù)庫模式、關(guān)纟模式、每個(gè)屬性的值城、完整性約束、毎個(gè)關(guān)糸的素引集合和關(guān)糸的楊理存儲(chǔ) 結(jié)構(gòu)等。 SQL數(shù)據(jù)定義語言包括數(shù)據(jù)庫的定義、修改和刪除基本表的走義、修改和刪除視圖的走義、修改和刪除素引的皮義、修改和刪除3. 8 SQL數(shù)據(jù)定義語言 SQL數(shù)據(jù)定義撫作對(duì)象創(chuàng)建修改刪除數(shù)據(jù)庫CREATE DATABASEALTER DATABASEDROP DATABASE表CREATETABLEALTERTABLEDROPTABLE視圖CREATEVIEWALTER VIEWDROPVIEW索引CREATE INDEXDROP INDEX3. 8 1數(shù)據(jù)庫的定義數(shù)據(jù)庫與其

9、對(duì)象之問的關(guān)糸3. 8. 1數(shù)據(jù)庫的定義數(shù)據(jù)庫的創(chuàng)建數(shù)據(jù)文件（data filej:存赭數(shù)據(jù)的文件可志文件flog filej:存赭可志的文件與該教據(jù)庫相關(guān)的福述信息會(huì)存入列糸統(tǒng)的數(shù)據(jù)字典中在走義數(shù)據(jù)庫的肘候，必須定義數(shù)據(jù)庫的名字，送輯設(shè)備 名和煬理文件名。定義數(shù)據(jù)庫操作的語法CREATE DATABASE ON ,. .n,.nLOG ON,.n其中： := PRIMARY (NAME=,數(shù)據(jù)邏輯設(shè)備3. 8. 1數(shù)據(jù)庫的定義FILENAME =4,SIZE = .MAXSIZE = | UNLIMITED .FILEGROWTH =),.n := Fl LEG RO UP ,.n 3. 8

10、. 1數(shù)據(jù)庫的定義其中:彼創(chuàng) 建的數(shù)據(jù)庫的名字，滿足如下要求長(zhǎng)度可以從I30,第一個(gè)字符必須是字母，或下劃線或字符衣祈字符后的字苻可以是號(hào)母、救字或者前面規(guī)則中提到的步符9右稱中不能有空格數(shù)據(jù)庫的大小可以彼護(hù)展或者收縮ON :指定數(shù)據(jù)庫中數(shù)據(jù)的該盤文件。其中it據(jù)文件，他們用逸號(hào)來隔需，用來之義主Cprimaryj送輯 役備中的數(shù)據(jù)文件；除了主送輯設(shè)務(wù)外，用戶還可以定義 用戶的送輯設(shè)備和相關(guān)用戶丈件。3 & 1數(shù)據(jù)庫的定義PRIMARY指走關(guān)聯(lián)的vfilespec 列表走義主丈件。 主丈件組包含所有救據(jù) 庫糸統(tǒng)表。還包含所有未指派給用戶丈件組的對(duì)象。主丈件組的 第一個(gè)filespec條

11、M成為主丈件，該丈件包含數(shù)據(jù)庫的邏輯起 點(diǎn)及其糸統(tǒng)表。一個(gè)數(shù)堀庫只能有一個(gè)主丈件。如黑沒冇指走PRIMARY,叩么CREATE DATABASE語旬中列出的 第一個(gè)文件檸成為主丈件。LOG ON指定救據(jù)庫目志的該盤丈件（日志丈件丿，其中vfilespec指ti志 丈件。如果沒有指癥.LOGON,糸統(tǒng)將動(dòng)創(chuàng)建單個(gè)的可志丈件3. 8. 1數(shù)據(jù)庫的定義例3.1建立學(xué)生成績(jī)救據(jù)庫ScoreDB CREATE DATABASE ScoreDB ON(NAME=ScoreDB,FILENAME二e:SQLDatabaseScoreDB.mdf,SIZE=2,MAXSIZE=IO,FILEGROWTH=I)

12、3. 8. 1數(shù)據(jù)庫的定義 LOG ON(NAM E=ScoreLog,FILENAME二e:SQLDatabaseScoreLog.ldf,SIZE=I,MAXSIZE=5,FILEGROWTH=I)3. 8. 1數(shù)據(jù)庫的定義例3.2建立一個(gè)復(fù)雜的數(shù)據(jù)庫M ylempDBCREATE DATABASE MylmpDBONPRIM AR -主股備(NAME=_empDev, FILENAME=td:empData7empDev.mdf,SIZE=5,FILEGROWTH=2),FILEGROUPTempHisDev,第一個(gè)用戶沒等，僅包含I個(gè)文件(NAME= TempHisDev I,FILE

13、NAME=4d:TempDatalempHisDev|.mdf,SIZE=IO,FILEGROWTH=5),3. 8. 1數(shù)據(jù)庫的定義 FILEGROUPTempBakDev2f第二個(gè)用戶沒備，包舍 3 個(gè)丈件 (NAME=TempBakDev 11FILENAME 三 d:TempDataTempBakDev l.mdf, SIZE=5tFILEGROWTH=2).FILEGROUPTempBakDev2.(NAME=TempBakDev2,FILENAME-d:TempDataTempBakDev2.mdfSIZE=5tFILEGROWTH=2),FILEGROUPTempBakDev3,

14、(NAME=TempBakDev3,FILENAME 二 dTempDataTempBakDev3mdf：SIZE=5tFILEGROWTH=2).3. 8. 1數(shù)據(jù)庫的定義 LOG ON(NAM E=Score Log,FILENAME二e:SQLDatabaseVfempLogDev I .Idf,SIZE=5,FILEGROWTH=I)(NAME=ScoreLog,FILENAME二e:SQLDatabaseScoreLog.ldf,SIZE=5,FILEGROWTH=2)3. 8. 1數(shù)據(jù)庫的定義 LOG ON(NAM E=ScoreLog,FILENAME二e:SQLDatabase

15、ScoreLog.ldf,SIZE=I,MAXSIZE=5,FILEGROWTH=I)3. 8. 1數(shù)據(jù)庫的定義救據(jù)庫的修改修叱數(shù)據(jù)庫捺作的語法ALTER DATABASE ADD FILEvespecnTO FILEGROUP|ADD LOG FILEv 他 spec,.n|REMOVE FILE|ADD FILEGROUP|REMOVE FILEGROUP|MODIFY FILE|MODIFY FILEGROUP其中： :指走枚修改的救據(jù)庫的名字；ADD FILE：指走添加列救據(jù)庫中的故垢丈件；3. 8. 1數(shù)據(jù)庫的定義TO FILEGROUP:指走文件潦加刊 丈件組名為 的丈件俎；3.

16、8. 1數(shù)據(jù)庫的定義 ADD LOG FILE：指走添加到數(shù)據(jù)庫中的可志文件； REMOVE FILE：從數(shù)據(jù)庫糸統(tǒng)表中刪除該丈件，并楊 理刪除該文件； ADD FILEGROUP：指査海加到數(shù)據(jù)庫的丈件組； :文件組的名稱； REMOVE FILEGROUP：從數(shù)據(jù)庫中刪除該丈件組，并 刪除在這個(gè)文件組中的丈件； MODIFYFILE：指定要修改的丈件，包含該丈件的名 稱、大小、增長(zhǎng)量和最丸家量。卜說朗：一次只可以修改其中的一個(gè)選項(xiàng)。3. 8. 1數(shù)據(jù)庫白勺定義M3.2ALTER DATABASE MyfempDBMODIFY FILE(NAME=7empHisDevI,SIZE=20MB)

17、3. & 1數(shù)據(jù)庫白勺定義數(shù)據(jù)庫的刪除刪除教據(jù)庫肘，糸統(tǒng)會(huì)同肘從纟統(tǒng)的數(shù)據(jù)字典中將該數(shù)據(jù) 庫的檔述一起刪除，有的數(shù)據(jù)庫糸統(tǒng)還會(huì)自動(dòng)刪除與教據(jù) 庫相關(guān)聯(lián)的楊理文件。 DROP DATABASE 3. & 2基本表的定義創(chuàng)建了數(shù)據(jù)庫后，就可以在數(shù)據(jù)庫中遣尤基本慕。通.過將基木 表與送輯沒務(wù)相關(guān)聯(lián)，使得一個(gè)基本表可以放在一個(gè)楊理文件 上，也可以放在多個(gè)腸理文件上。SQL中的基本數(shù)據(jù)類型是：整型：int(4B)、smallint(2B), tinyinc(lB)；實(shí)型：float. real(4B).；字 符型：char(n). varchar(n). text；二 進(jìn)制 型：bina

18、ry(n)、varbinary(n). image；邏輯型：b,只能取0和I,不允許為空；貨幣型：money(8B, 4住小數(shù)).small money(4B, 2住小數(shù))；肘間型：datetime(4B, I 753-01-01開始)smalldatetime(4B,1900-01 -01開始3. 8. 2基本表的定義創(chuàng)直基本表CREATETABLEdatabase_name. owner .| owner. table name裹七( I column_namc AS computcd_column_cxprcssion一列名AS丈義計(jì) 算列 做詢表達(dá)式| := CONSTRAINT co

19、nstraint namc | PRIMARY KEY | UNIQUE ,.n )ON filcgroup | DEFAULT桿對(duì)象放在粘定的11輯設(shè)務(wù)上，缺不主設(shè)備TEXTIMAGE_ON filegroup | DEFAULT 建儀：不檸帀戶對(duì)兔建立應(yīng)主匡轎役備上，主邏轎設(shè)備用來存放糸統(tǒng)做據(jù)。其中： := column namc data type COLLATE DEFAULT constantexpression | IDENTITY ( seed increment) NOT FOR REPUCATION ROWGUIDCOL .n 3. & 2基本表的定義v column

20、_constraint := CONSTRAINT constraint_name NULL | NOT NULL一| PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT1| FOREIGN KEY REFERENCES rentable ( ref_column ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT F

21、OR REPLICATION ( logical_express ion )3. 8. 2基本表的定義 := CONSTRAINT constraint_name PRIMARY KEY | UNIQUE一 CLUSTERED | NONCLUSTERED ( column ASC | DESC t.n ) WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT | FOREIGN KEY ( column t.n )REFERENCES rentable ( ref.column t.n ) ON DELETE CASCADE | NO ACT

22、ION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( search_conditions )3. 8. 2基本表的定義落用兜整性約柬主碼約束：PRIMARY KEY難一性約束：UNIQUE非空值約東：NOT NULL（屬于表結(jié)構(gòu)，更改肘用ALTER TABLEALTERCOLUMN更改丿參照完整性約束PRIMARY KE丫與UNIQUE的區(qū)別？3. & 2基本表的定義例3.3建立ScoreDB中的4張表 CREATE TABLE Class(char(6)varchar(3

23、0)varchar(30) smallintdefault 0 tinyintCONSTRAINT CoursePKPRIMARY3. & 2基本表的定義CREATE TABLE CoursP (courseNochar(3)NOT NULL,courseNamevarchar(30)NOT NULL,creditHournumeric(l) default 0NULL,courseHourtinyint default 0NULL,priorCoursechar(3)NULL,CONSTRAINT CoursePK PRIMARY KEY(courseNo),CONSTRAINT C

24、ourseFK FOREIGN KEY(priorCourse)REFERENCE Course(courseNo)classNoclassNamcinstitutegradeclassNumNOTNULL,NOTNULL,NOTNULL,NOTNULL,NULL,KEY(classNo)3. 8. 2基本表的定義 CREATE TABLE Score(studentNo char(7)NOT NULL,courseNo char(3)NOT NULL,score numeric(5,1)default 0NOT NULLCHECK(score BETWEEN 0.0 AND 100.0),C

25、ONSTRAINT CoursePK PRIMARYKEY(studentNo,courseNo),CONSTRAINT CourseFK FOREIGN KEY(courseNo)REFERENCE Course(courseNo),CONSTRAINT CourseFK FOREIGN KEY(studentNo)REFERENCE Student(studentNo)3. & 2基本表的定義 CREATETABLE Stiidpnt (studentNochar(7)NOT NULL,stude ntZamevarchar(20)NOT NULL,sexchar(2)NULL,b

26、irthdaydatetimeNULL,nativevarchar(20)NULL,nationvarchar(30) defaultz漢族NULL,classNochar(6)NULL,CONSTRAINT StudentPK PRIMARY KEY(studentNo),CONSTRAINT StudentFKFOREIGN KEY(classNo)REFERENCE Class(classNo)說朗：以上4張表都缺茍了 OZfilegroup_name,數(shù)據(jù)存 菽在主 邏輯役務(wù) 上。3. & 2基本表的定義基本表的修孜 ALTER TABLE table ALTER COLUMN

27、 column_name new_data_typc ( precision . scale )數(shù)毎類型必須要有COLLATE v collation_name NULL | NOT NULL| ADD | DROP ROWGUIDCOL | ADD | column_name AS computed_column_expression . n | WITH CHECK | WITH NOCHECK ADD n | DROP CONSTRAINT constraintname | COLUMN column ,.n | CHECK | NOCHECK CONSTRAINT ALL | cons

28、traintname n | ENABLE | DISABLE TRIGGER ALL | triggername n 3. & 2基本表的定義 := column name data type DEFAULT constanjexpression WITHVALUES | IDENTITY ( seed , increment) NOT FOR REPLICATION ROWGUIDCOL COLLATE v column constraint .n 3. & 2基本表的定義v column_constraint :=CONSTRAINT constraint_name NU

29、LL | NOT NULLi | PRIMARY KEY | UNIQUECLUSTERED | NONCLUSTERED WITH FILLFACTOR = fillfactor ON filcgroup | DEFAULT | FOREIGN KEYREFERENCES refutable ( ref_column )ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION (logical_expression ) 3 8. 2基本

30、表的定義 :=CONSTRAINT con$crainc_name PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED ( column ,.n ) WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT J| FOREIGN KEY(column ,.n )REFERENCES ref cable ( ref column .n ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NOACTION NOT FOR REPLICATION | DEFAUL

31、T con$canc_cxpre$ionFOR column WITH VALUES | CHECK NOT FOR REPLICATION (search_conditions )3. 8. 2基本表的定義基本表的修改增加列ALTERTABLE ADD 劇除列ALTERTABLE DROP COLUMN 增加約束ALTERTABLE ADD CONSTRAINT 刪除約束ALTERTABLE DROP 修改列的數(shù)據(jù)類型ALTERTABLE ALTER COLUMN 3. 8. 2基本表的定義例3.4在ScoreDB_i&庫中為Student裹增加一列ALTER TABLE Stude

32、ntADD address varchar(50)例3.習(xí)在ScoreDB數(shù)據(jù)庫中為Course表的CourseZame列 修改教 據(jù)類型ALTER TABLE CourseALTER COLUMN courseName varchar(40)例3.6$.ScoreDBL4庫中為Class表增加唯一約束ALTERTABLE ClassADD CONSTRAINT UcName UNIQUE(className)例3.7在ScoreQB數(shù)據(jù)庫中為Student表刪 除一列ALTER TABLE StudentDROP COLUMN address3. 8. 2基本表的定義卜基本表的刪除DROPT

33、ABLE DROPTABLE不能用于除去由FOREIGN KEY約束引用的表。 必須丸除去引用的FOREIGN KEY約束或引用的表。表所有者可以除去任何數(shù)據(jù)庫內(nèi)的表。 除去表肘， 表上的規(guī) 則或默認(rèn)值將鮮除綁定，任何與表關(guān)聯(lián)的約束或紐發(fā)彖將自 動(dòng)除去。婦果重拚創(chuàng)建表，必須重新綁走適當(dāng)?shù)囊?guī)則和默認(rèn)值,重新創(chuàng)建任何紐發(fā)器并添加必要的約束。在糸統(tǒng)表上不能使用DROPTABLE語句。3. 8. 3索弓I的定義建立秦引是加快查詢速度的有枚手段建立素引DBA或表的厲主（即建立表的人丿垠據(jù)需要建立有些DBMS如sql server2000會(huì)自動(dòng)建立以下列上的素引 PRIMARY KEYUNIQUE維護(hù)素引DBMSij動(dòng)完成使用素引DBMS自動(dòng)選擇是否使用素引以及使用哪些素引3. & 3索引的定義語句格式CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_nam