數(shù)據(jù)庫(kù)概論數(shù)據(jù)庫(kù)的創(chuàng)建、修改以及刪除

1、數(shù)據(jù)庫(kù)概論數(shù)據(jù)庫(kù)的創(chuàng)建、修改以及刪除 結(jié)構(gòu)化查詢(xún)語(yǔ)言SQL(Structured Query Language)是一個(gè)通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言，目前已成為關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言。第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言3.1 SQL概述及其特點(diǎn) 1. SQL概述 按其功能分為四大類(lèi) 名稱(chēng)動(dòng)詞功能數(shù)據(jù)查詢(xún)SELECT查詢(xún)數(shù)據(jù)數(shù)據(jù)定義CREATE，DROP，ALTER定義、撤消和修改數(shù)據(jù)模式數(shù)據(jù)操縱INSERT，UPDATE，DELETE增、刪、改數(shù)據(jù)數(shù)據(jù)控制GRANT，REVOKE數(shù)據(jù)訪問(wèn)權(quán)限的控制 其作用是建立和使用數(shù)據(jù)庫(kù)。 2. SQL數(shù)據(jù)庫(kù)的體系結(jié)構(gòu) 按支持SQL的數(shù)據(jù)庫(kù)管理系統(tǒng)可稱(chēng)其SQL數(shù)據(jù)庫(kù)

2、，其結(jié)構(gòu)基本上采用三級(jí)結(jié)構(gòu)，但所用術(shù)語(yǔ)與傳統(tǒng)關(guān)系模型的術(shù)語(yǔ)有些不同。SQL傳統(tǒng)的RDB基本表(Base Table)關(guān)系模式存儲(chǔ)文件(Base Table)內(nèi)模式（存儲(chǔ)模式）視圖(View)外模式行(Row) / 列(Column)元組/屬性用戶(hù)1用戶(hù)2用戶(hù)3用戶(hù)4視圖3視圖1視圖2基本表1基本表2基本表3基本表4存儲(chǔ)文件1存儲(chǔ)文件1SQL用戶(hù)視圖基本表存儲(chǔ)文件外模式模式內(nèi)模式表實(shí)例數(shù)據(jù)庫(kù)實(shí)例數(shù)據(jù)庫(kù)關(guān)系模式：學(xué)生(學(xué)號(hào)，姓名，專(zhuān)業(yè)名，性別，出生日期， 總學(xué)分，備注）課程（課程號(hào)，課程名，開(kāi)課學(xué)期，學(xué)時(shí)，學(xué)分）成績(jī)（學(xué)號(hào)，課程號(hào)，成績(jī)）創(chuàng)建數(shù)據(jù)庫(kù) 通過(guò)企業(yè)管理器創(chuàng)建數(shù)據(jù)庫(kù)通過(guò)DDL創(chuàng)建數(shù)據(jù)庫(kù) 3

3、.2.1 使用CREATE DATABASE創(chuàng)建數(shù)據(jù)庫(kù) 一個(gè)示例 【例】創(chuàng)建一個(gè)名為XSCJ1的數(shù)據(jù)庫(kù)，其初始大小為5MB，最大大小50MB，允許數(shù)據(jù)庫(kù)自動(dòng)增長(zhǎng)，增長(zhǎng)方式是按10%比例增長(zhǎng)；日志文件初始為2MB，最大可增長(zhǎng)到5MB，按1MB增長(zhǎng)。CREATE DATABASE XSCJ1ON(NAME=XSCJ1_Data, e:sqldataMSSQLDataXSCJ1.mdf, SIZE=5MB, MAXSIZE=50MB, )LOG ON(NAME=XSCJ1_Log, e:sqldataMSSQLDataXSCJ1_Log.ldf, SIZE=2MB, MAXSIZE=5MB, ) 1

4、. 基本表 定義基本表結(jié)構(gòu) 定義表名 定義表中各列的特征： 列名、數(shù)據(jù)類(lèi)型、 長(zhǎng)度以及能否取空值3.3.1 基本表的定義和修改 一般格式： CREATE TABLE ( 數(shù)據(jù)類(lèi)型 列級(jí)完整性約束條件 , 數(shù)據(jù)類(lèi)型 列級(jí)完整性約束條件 ) ； 說(shuō)明： 建表的同時(shí)可以定義與該表有關(guān)的完整性約束,這些約束條件被存入系統(tǒng)的數(shù)據(jù)字典中,當(dāng)用戶(hù)操作表時(shí),DBMS會(huì)自動(dòng)檢查該操作是否有違背完整約束條件. 建立約束的考慮：如果完整性約束條件涉及到該表的多個(gè)屬性列，則必須定義在表級(jí)上；否則既可以定義在列級(jí)上也可以定義在表級(jí)上。 表名、列名是不區(qū)分大小寫(xiě)的。 對(duì)一個(gè)用戶(hù)而言，表名必須唯一；一個(gè)表中，列名必須唯一。

5、 表名、列名必須以字母開(kāi)頭，長(zhǎng)度不超過(guò)30個(gè)字符。SQL Server 支持的數(shù)據(jù)類(lèi)型 設(shè)已經(jīng)創(chuàng)建了數(shù)據(jù)庫(kù)XSCJ，現(xiàn)在該數(shù)據(jù)庫(kù)中需創(chuàng)建學(xué)生情況表XS，該表的結(jié)構(gòu)見(jiàn)表。創(chuàng)建表XS的T-SQL語(yǔ)句如下： USE XSCJ CREATE TABLE XS ( 學(xué)號(hào) char(6) primary key, 姓名 char(8) NOT NULL, 專(zhuān)業(yè)名 char(10) NULL, 性別 bit NOT NULL, 出生時(shí)間 smalldatetime NOT NULL, 總學(xué)分 tinyint NULL, 備注 text NULL ) 修改基本表結(jié)構(gòu) 修改已經(jīng)定義的表的結(jié)構(gòu) 修改已經(jīng)定義的表的

6、完整性約束：一般格式：ALTER TABLE ADD 完整性約束 DROP ALTER COLUMN ; DROP COLUMN ; 說(shuō)明 ADD子句用于新增列及其完整性約束條件， 新增加的字段只能作為表的最后一個(gè)字段； DROP子句用于刪除指定的完整性約束條件; MODIFY子句用于修改原有的列定義,包括修改列的數(shù)據(jù)類(lèi)型和長(zhǎng)度； SQL沒(méi)有提供刪除屬性列的語(yǔ)句，用戶(hù)只能間接實(shí)現(xiàn)這一功能。方法是先將表中要保留的列及其內(nèi)容復(fù)制到一個(gè)新表中，然后刪除原表，再將新表重新命名為原表；【例】修改表XS中已有列的屬性：將名為“姓名”的列長(zhǎng)度由原來(lái)的8改為10；將名為“出生時(shí)間”的列的數(shù)據(jù)類(lèi)型由原來(lái)的sma

7、lldatetime改為datetime。 USE XSCJ ALTER TABLE XS ALTER COLUMN 姓名 char(10) ALTER COLUMN 出生時(shí)間 datetime GO 【例】在表XS中增加1個(gè)新列獎(jiǎng)學(xué)金等級(jí)。 USE XSCJ ALTER TABLE XS ADD 獎(jiǎng)學(xué)金等級(jí) tinyint NULL【例】在表XS中刪除名為獎(jiǎng)學(xué)金等級(jí)的列。 USE XSCJ ALTER TABLE XS DROP COLUMN 獎(jiǎng)學(xué)金等級(jí)【例】修改表XS中已有列的屬性：將名為“姓名”的列長(zhǎng)度由原來(lái)的8改為10；將名為“出生時(shí)間”的列的數(shù)據(jù)類(lèi)型由原來(lái)的smalldatetime

8、改為datetime。 USE XSCJ ALTER TABLE XS ALTER COLUMN 姓名 char(10) ALTER COLUMN 出生時(shí)間 datetime GO 刪除基本表定義一般格式：DROP TABLE ;說(shuō)明: 基本表一旦刪除，表中的數(shù)據(jù)、此表上建立的索引和視圖都將自動(dòng)被刪除。例：刪除STUDENT表。 數(shù)據(jù)操縱命令將導(dǎo)致數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變。SQL語(yǔ)言的數(shù)據(jù)操縱功能包括三部分: INSERT 向表中插入數(shù)據(jù) UPDATE 修改表中已存在行中的數(shù)據(jù) DELETE 刪除表中的數(shù)據(jù) 3.4 SQL數(shù)據(jù)操縱 - DML 要求表必須是已存在的。(手工方式略) 1. 單行插入一般

9、格式： INSERT INTO (,) VALUES (,);3.4.1 插入數(shù)據(jù)注意： 在表定義時(shí)說(shuō)明了NOT NULL 的屬性列不能取空值，否則會(huì)出錯(cuò)。 如果 INTO 子句中選擇了列名，則VALUES子句中的值表達(dá)式，必須與列一一對(duì)應(yīng)；如果沒(méi)有指明任何列名，則新插入的紀(jì)錄必須在每個(gè)屬性列上均有值。 字符型和日期型數(shù)據(jù)在插入時(shí)要加單引號(hào)。 對(duì)于在INSERT語(yǔ)句中未出現(xiàn)的列，那么這些列則為NULL；也可以顯式地在VALUES子句中用NULL來(lái)代表空值進(jìn)行插入數(shù)據(jù)。 2. 多行插入 多行插入也稱(chēng)為表間拷貝，即從一個(gè)表中抽取若干行數(shù)據(jù)插入到另一個(gè)表中。一般格式： INSERT INTO (,)

10、 SELECT 列表達(dá)式1 , 列表達(dá)式2 FROM WHERE ;子查詢(xún)例：向?qū)W生表xs插入一行新的數(shù)據(jù)。例：向?qū)W生表xs中計(jì)算機(jī)系中的學(xué)生插入到表s1中。create table s1( sno char(6) not null, sname char(8) not null, sdept char(10) null)insert into s1select 學(xué)號(hào),姓名,專(zhuān)業(yè)名 from xswhere 專(zhuān)業(yè)名=計(jì)算機(jī) 可以修改表中某些指定列的數(shù)據(jù)。 一般格式： UPDATE 別名 SET = ,= WHERE ; 其中：表達(dá)式=表達(dá)式|常量|列名3.4.2 修改數(shù)據(jù)1. 修改一個(gè)或多個(gè)元

11、組的值 如果不選WHERE子句，則表中所有的行全被更新； 如果選擇了WHERE子句，則使WHERE 中條件表達(dá)式為真的行被更新；例：把所有學(xué)生的總學(xué)分加10。例：把姓名為“羅林琳”的學(xué)生的專(zhuān)業(yè)改為“計(jì)算機(jī)”，備注改為“三好學(xué)生”。 刪除表中某些行的數(shù)據(jù)。一般格式： DELETE FROM WHERE ; 其中：表達(dá)式=表達(dá)式|常量|列名;注意： 刪除表中的全部記錄后，該表的定義仍在數(shù)據(jù)字典中。3.4.3 刪除數(shù)據(jù)例54：刪除所有學(xué)生的成績(jī)記錄。例55：刪除計(jì)算機(jī)系全體學(xué)生的成績(jī)記錄。作業(yè)：1 寫(xiě)出創(chuàng)建產(chǎn)品銷(xiāo)售數(shù)據(jù)庫(kù)CPXS和表的SQL語(yǔ)句：數(shù)據(jù)庫(kù)的初始大小為10MB，最大大小為100MB，數(shù)據(jù)

12、庫(kù)按10%比例自動(dòng)增長(zhǎng)；日志文件初始為2MB，最大可增長(zhǎng)到5MB，按1MB增長(zhǎng)。所包含的表如下：產(chǎn)品表：產(chǎn)品編號(hào)，產(chǎn)品名稱(chēng)，價(jià)格，庫(kù)存量。2 在產(chǎn)品表中插入如下數(shù)據(jù)：0001 空調(diào)3000 2000002 冰箱2500 1000003 彩電 1200 500004 洗衣機(jī) 1000 30（1）將產(chǎn)品表的每種商品的價(jià)格打8折（2）將產(chǎn)品表中打折后價(jià)格小與1000的商品刪除 格式：SELECT ALL|DISTINCT，F(xiàn)ROM ，WHERE GROUP BY HAVINGORDER BY ASC|DESC； 描述： 根據(jù)WHERE子句的條件表達(dá)式，從FROM子句指定的基本表或視圖中找出滿(mǎn)足條件的

13、元組，再按SELECT子句中的目標(biāo)列表達(dá)式，選出元組中的屬性值形成結(jié)果表。如果有GROUP BY子句則將結(jié)果按的值進(jìn)行分組，該屬性列相等的元組為一個(gè)組。通常會(huì)在每組中作用集函數(shù)。如果GROUP子句帶有HAVING短語(yǔ)，則只有滿(mǎn)足指定條件的組才予輸出。如果有ORDER BY子句，則結(jié)果表還要按的值升序或降序排列。3.2 SQL查詢(xún) 1. 選擇表中的若干列 選擇表中的指定列； 選擇表中的所有列； 結(jié)果列的顯示順序； 使用列表達(dá)式； SELECT中的可以是表中的列，也可以是表達(dá)式，包括算術(shù)表達(dá)式、字符串常數(shù)、函數(shù)等。（字符串用單引號(hào)定界）。 使用的列的別名: 列名 AS 列的別名3.2.1 單表查詢(xún)

14、 僅涉及一個(gè)表的查詢(xún)例1：求全體學(xué)生的學(xué)號(hào)、姓名。例2：求全體學(xué)生的詳細(xì)信息。例3：求學(xué)生學(xué)號(hào)和學(xué)生出生的年份 （經(jīng)過(guò)計(jì)算的值）。例4：求學(xué)生的學(xué)號(hào)和出生年份，顯示時(shí) 使用別名 Student_No 和 Birth_Day。2. 選擇表中的若干元組 消除取值重復(fù)行 查找相異的行：在SELECT語(yǔ)句中使用關(guān)鍵字 DISTINCT 原本不完全相同的元組，經(jīng)過(guò)向某些列投影操作后，可能變成相同的行了。如果想去掉結(jié)果表中的重復(fù)行，必須指定 DISTINCT 短語(yǔ)，沒(méi)有指定，則使用用缺省值 ALL，意為保留結(jié)果表中所有的行。 例5：求選修了課程的學(xué)生學(xué)號(hào)。 查詢(xún)滿(mǎn)足條件的元組 可以通過(guò)WHERE子句來(lái)實(shí)現(xiàn)

15、。WHERE常用的查詢(xún)條件如下表所示。查詢(xún)條件謂詞比較=,=,=,!=,!,!; NOT+上述比較運(yùn)算符確定范圍BETWEEN AND，NOT BETWEEN AND確定集合IN，NOT IN字符匹配LIKE，NOT LIKE空值IS NULL，IS NOT NULL多重條件AND，OR，NOT 大小比較 條件表達(dá)式的形式如下： 比較運(yùn)算符 列名|常量|表達(dá)式 其中：字符串常量和日期常量要用一對(duì) 單引號(hào)括起來(lái)。例6：求年齡大于等于20歲的學(xué)生姓名和年齡。 BETWEEN 確定范圍 謂詞BETWEEN AND與NOT BETWEEN AND的一般格式為： NOT BETWEEN AND 查詢(xún)屬性

16、值在（或不在）指定范圍內(nèi)的元組 其中： 為范圍的下限（低值）， 為范圍的上限（高值）。 選出的元組包括邊界與,要求a=b，等價(jià)于 a = 屬性列 = b 。例7：求年齡在20歲與22歲之間（包括20歲和 22歲）的學(xué)生學(xué)號(hào)和年齡。例8：求年齡不在20歲與22歲之間的學(xué)生學(xué)號(hào) 和年齡。 IN 確定集合 謂詞IN可以用來(lái)查找屬性值屬于指定集合的元組。 NOT IN 謂詞IN實(shí)際上是一系列謂詞OR的縮寫(xiě)。所起的作用就是檢查列值是否等于它后面括弧內(nèi)的一組值中某一個(gè)。如果等于其中某一個(gè)值，則其結(jié)果為真，否則其結(jié)果為假。 NOT IN 表示與IN完全相反的含義。例9： 求在下列各系的學(xué)生：通信工程系、 計(jì)

17、算機(jī)系。例10：求不是通信工程系、計(jì)算機(jī)系的學(xué)生的 信息。 LIKE 匹配查詢(xún)或模糊查詢(xún) 謂詞LIKE的一般格式為： NOT LIKE 查詢(xún)指定的屬性列與相匹配的元組。 注釋?zhuān)嚎梢允且粋€(gè)完整的字符串，也可以含有通配符的字符串。通配符包括： %（百分號(hào)）： 代表任意長(zhǎng)（長(zhǎng)度為0）字符串。 _（下劃線(xiàn)）： 代表任意單個(gè)字符 所有其它字符：只代表自己例11：求姓名是以字母馬打頭的學(xué)生信息。例12：求姓名中含有字母馬的學(xué)生信息。例13：求姓名長(zhǎng)度至少是三個(gè)字符且倒數(shù)第 三個(gè)字符必須是字母馬的學(xué)生信息。 使用換碼字符將通配符轉(zhuǎn)義為普通字符 例 查詢(xún)DB_Design課程的課程號(hào)和學(xué)分。 例 查詢(xún)以DB_

18、開(kāi)頭，且倒數(shù)第3個(gè)字符為i的課程的詳細(xì)情況。elect * from kcwhere 課程名 like db_% escape ESCAPE 表示“ ” 為換碼字符 NULL 涉及空值查詢(xún) NULL表示空值?？罩凳且环N不存在的或者不知道、不可用的數(shù)據(jù)。 列名 NOT IS NULL 這里的IS不能用=替代。 數(shù)據(jù)庫(kù)表的行中，未被賦值的字段自動(dòng)被認(rèn)為是空值。0長(zhǎng)度的字符串自動(dòng)解釋為空值。 空值的賦值： 把連續(xù)兩個(gè)單引號(hào)賦值給它； 把空值常量NULL賦值給它。例14：求缺少學(xué)習(xí)成績(jī)的學(xué)生的學(xué)號(hào)和課程號(hào)。 （查詢(xún)含有空值的行）。 SELECT SNO, CNO FROM SCORE WHERE GR

19、ADE IS NULL; 多重條件 當(dāng)查詢(xún)條件涉及到多個(gè)時(shí)，可將若干條件通過(guò)邏輯運(yùn)算符構(gòu)成一個(gè)更復(fù)雜的條件進(jìn)行查詢(xún)。可以使用三種邏輯運(yùn)算符： NOT 邏輯非； AND 邏輯與； OR 邏輯或 運(yùn)算符的優(yōu)先順序如下： = != NOT= = = NOT BETWEENAND NOT IN NOT LIKE IS NOT NULL NOT AND OR 用戶(hù)可以用括號(hào)( )改變優(yōu)先級(jí)。例15：求計(jì)算機(jī)系或通信工程系，年齡 大于20歲的學(xué)生姓名、系和年齡。例16：求選修課程(101)或課程(102)，成績(jī)?cè)?85和95之間的學(xué)生的學(xué)號(hào)、課程與成績(jī)。 3. 控制行的顯示順序 ORDER BY 子句中，

20、顯示查詢(xún)結(jié)果時(shí)，數(shù)據(jù)按指定的列排序。ASC 升序排序（默認(rèn)值）；DESC 降序排序 可以選擇多列進(jìn)行排序； ORDER BY子句中可以使用表達(dá)式； ORDER BY 子句必須是放在SELECT命令中影響選取行的所有子句的后面； 如果排序列含有空值時(shí)，DESC排序時(shí)，含有空值的行位于最前面；ASC排序時(shí)，含有空值的行位于最后面。例17：求選修課程(101)或課程(102)學(xué)生的 學(xué)號(hào)、課程號(hào)與成績(jī)，結(jié)果按學(xué)號(hào)升 序、課程號(hào)降序排序。 4. 組函數(shù) SQL提供的五種組函數(shù) COUNT( ) 計(jì)算所選數(shù)據(jù)（記錄）的個(gè)數(shù) SUM( ) 計(jì)算某一數(shù)值列的和 AVG( ) 計(jì)算某一數(shù)值列的平均值 MAX(

21、 ) 求(字符、日期、數(shù)值列)的最大值 MIN( ) 求(字符、日期、數(shù)值列)的最小值 例：設(shè)有學(xué)生關(guān)系SNOSNAMESDEPTSCLASSSAGE96001丁一CS012196002王二CS012096003張三MA012196004李四CI011996005劉五MA022096006趙六CS021896007馬芳CS022296008高興CS0122SDEPTSCLASSCS0121CS0220MA0121MA0220CI0119分組后的平均年齡按系、班級(jí)分組計(jì)算學(xué)生的平均年齡例18：求學(xué)生總?cè)藬?shù)。例19：求選修了課程的學(xué)生人數(shù)。例20：求計(jì)算機(jī)系學(xué)生的平均學(xué)分。例21：求選修了課程10

22、1的最高、最低與 平均成績(jī)。 5. 分組查詢(xún) 實(shí)現(xiàn)行的分組和分組統(tǒng)計(jì)。 在對(duì)表格進(jìn)行操作時(shí)，要求將記錄按某個(gè)或某幾個(gè)字段上相同的值分成組，然后再對(duì)組進(jìn)行相應(yīng)的操作，稱(chēng)作分組查詢(xún)。SNOSNAMESDEPTSCLASSSAGE96001丁一CS011996002王二CS012096003張三CS012196004李四CS021996005劉五CS022096006趙六CS042296007馬蓉MA012096008司馬明MA011996009張勝利MA012096010李力CI0222SSDEPTSCLASSSAGECS0120CS0219.5CS0422MA0119.3CI0222分組后的平均

23、年齡按系、班級(jí)分組計(jì)算學(xué)生的平均年齡 GROUP BY 子句 分組查詢(xún)是通過(guò) GROUP BY 子句實(shí)現(xiàn)的。 將查詢(xún)結(jié)果表按某一列或多列值分組，值相等的為一組； 當(dāng)對(duì)多列進(jìn)行分組時(shí)，所有的組函數(shù)統(tǒng)計(jì)都是對(duì)最后的分組列進(jìn)行的； 對(duì)查詢(xún)的結(jié)果分組的目的是為了細(xì)化統(tǒng)計(jì)函數(shù)的作用對(duì)象； 如果未對(duì)查詢(xún)分組，組函數(shù)將作用于整個(gè)查詢(xún)結(jié)果；如果分組后組函數(shù)將作用于每一個(gè)組，即每一個(gè)分組都有一個(gè)組函數(shù)； 在包含GROUP BY子句的查詢(xún)語(yǔ)句中，SELECT子句后面的所有字段列表（除組函數(shù)外），均應(yīng)該包含在GROUP BY 子句中，即所選項(xiàng)與分組的一致性。例22：求各門(mén)課程的平均成績(jī)與總成績(jī)。 例23：求各系中各

24、個(gè)班級(jí)的人數(shù)及平均年齡。 HAVING 子句 如果分組后還要按一定的條件對(duì)這些分組進(jìn)行篩選，只輸出滿(mǎn)足條件的組，則應(yīng)該使用HAVING短語(yǔ)指定篩選條件。 HAVING 子句用來(lái)選擇滿(mǎn)足條件的分組。WHERE與HAVING的區(qū)別：作用對(duì)象不同 WHERE 作用于基本表或視圖，從中選擇滿(mǎn)足條件的元組； HAVING短語(yǔ)作用于組，從中選擇滿(mǎn)足條件的組。 例26：求學(xué)生人數(shù)不足百人的系及其相應(yīng) 學(xué)生人數(shù)的。例27：求各系中年齡大于20歲的除1班之外的 各班級(jí)人數(shù)。 SELECT FROM WHERE GROUP BY HAVING ORDER BY 投影連接選取分組去組排序小結(jié) 連接查詢(xún)是通過(guò)各表中相

25、應(yīng)列的公共數(shù)據(jù)把一個(gè)表中的某些行與另一個(gè)表中的某些行連接起來(lái)。 表之間的聯(lián)系是通過(guò)表的字段值來(lái)體現(xiàn)的，這種字段通常稱(chēng)為連接字段。連接字段就是在兩個(gè)表中都包含的一個(gè)公共字段。連接操作的目的就是通過(guò)加在連接字段的條件將多個(gè)表連接起來(lái)。3.2.2 連接查詢(xún) 涉及兩個(gè)以上的表1. 無(wú)條件連接 在連接操作中，如果不用連接條件，則稱(chēng)為無(wú)條件連接。 無(wú)條件連接將產(chǎn)生大量的行，當(dāng)n1,n2,nn 這n個(gè)表進(jìn)行連接時(shí)，產(chǎn)生的記錄是n1*n2*nn。例：SELECT SNO, SNAME, CNO, GRADE FROM STUDENT, GRADE; 不帶WHERE子句的連接語(yǔ)句是笛卡兒乘積。2. 等值連接與非

26、等值連接 連接條件的一般格式是：. . 其中：比較運(yùn)算符主要有：=、=、=、!= .：指明是哪個(gè)表的哪個(gè)列 限定符連接查詢(xún)一定要帶上連接條件WHERE子句。注意： 連接條件中的各連接字段類(lèi)型必須是可比較的。執(zhí)行過(guò)程： 首先在表1中找到第一個(gè)元組，然后從頭開(kāi)始掃描表2，逐一查找滿(mǎn)足連接條件的元組，找到后就將表1中的第1個(gè)元組與該元組拼接形成結(jié)果表中的一個(gè)元組。表2全部找完后，再找表1中的第2個(gè)元組，然后再?gòu)念^掃描表2，逐一查找滿(mǎn)足連接條件的元組，找到后就將表1中的第2個(gè)元組與該元組拼接形成結(jié)果表中的一個(gè)元組。重復(fù)執(zhí)行，直到表1中的全部元組都處理完畢為止。自然連接查詢(xún)例28：求學(xué)生以及其選修課程的

27、情況。 SELECT XS.* , XS_KC.* FROM XS , XS_KC WHERE XS.學(xué)號(hào) = XS_KC.學(xué)號(hào)例29：查詢(xún)學(xué)生信息以及其選修課程的課程號(hào)和成績(jī)，但查詢(xún)結(jié)果中只能有一個(gè)SNO字段。SELECT XS.* , XS_KC.課程號(hào), XS_KC.成績(jī) FROM XS , XS_KC WHERE XS.學(xué)號(hào)= XS_KC.學(xué)號(hào)例30：查找選修了206課程且成績(jī)?cè)?0分以上的學(xué)生姓名及成績(jī)。SELECT 姓名 , 成績(jī)FROM XS , XS_KCWHERE XS.學(xué)號(hào) = XS_KC.學(xué)號(hào) AND 課程號(hào) = 206 AND 成績(jī) = 80 。 3. 表的別名 一般格

28、式是：表名 表的別名 表的別名有如下用途 簡(jiǎn)化輸入； 表與自身連接； 在相關(guān)嵌套查詢(xún)中。 例31：查找選修了“計(jì)算機(jī)基礎(chǔ)”課程且成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)、姓名、課程名及成績(jī)。 SELECT a.學(xué)號(hào), 姓名, 課程名, 成績(jī) FROM xs a, kc b, xs_kc c WHERE a.學(xué)號(hào) = c.學(xué)號(hào) AND b.課程號(hào) = c.課程號(hào) AND 課程名 =計(jì)算機(jī)基礎(chǔ) AND 成績(jī) = 804. 自身連接 自身連接不僅可以作用在兩個(gè)不同的表上，而且同一個(gè)表可進(jìn)行自身連接。 需要給表起別名以示區(qū)別 由于所有屬性名都是同名屬性，因此必須使用別名前綴執(zhí)行過(guò)程： 可以把一個(gè)看成兩個(gè)副本，即兩個(gè)

29、相同的表，然后再對(duì)這兩個(gè)表在連接字段上進(jìn)行連接。表與自身的連接必須使用表的別名，若在FROM子句中用兩個(gè)不同的別名對(duì)應(yīng)于同一個(gè)表是時(shí)，就可以象連接兩個(gè)分開(kāi)的表一樣來(lái)把表自身連接起來(lái)。例32：求年齡大于王燕的所有學(xué)生的姓名、 專(zhuān)業(yè)名和年齡。 from xs s1,xs s25. 外部連接 一般說(shuō)來(lái)，如果某一表中所選取的一行不能與另一個(gè)表中的任何一行相連接（即不滿(mǎn)足條件）時(shí)，則在查詢(xún)結(jié)果中此行不在出現(xiàn)。外部連接則不管條件是否滿(mǎn)足，都去相應(yīng)的數(shù)據(jù)。不僅返回兩個(gè)或兩個(gè)以上的表中能夠直接匹配的行，還返回一個(gè)表中無(wú)法從其它表中找到直接匹配的行。在FROM子句中采用如下表現(xiàn)形式：外連接（LEFT OUTER

30、 JOIN）：結(jié)果表中除了包括滿(mǎn)足連接條件的行外，還包括左表的所有行；右外連接（RIGHT OUTER JOIN）：結(jié)果表中除了包括滿(mǎn)足連接條件的行外，還包括右表的所有行；完全外連接（FULL OUTER JOIN）：結(jié)果表中除了包括滿(mǎn)足連接條件的行外，還包括兩個(gè)表的所有行。例33：求選修課程101和102的學(xué)生的學(xué)號(hào)、 課程號(hào)、課程名與成績(jī)。 select 學(xué)號(hào),kc.課程號(hào),課程名,成績(jī)from xs_kc join kc on xs_kc.課程號(hào)=kc.課程號(hào)where kc.課程號(hào)=101 or kc.課程號(hào)=102例34：查找所有學(xué)生情況，及他們選修的課程號(hào)，若學(xué)生未選修任何課，也要

31、包括其情況。SELECT XS.* , 課程號(hào)FROM XS LEFT OUTER JOIN XS_KC ON XS.學(xué)號(hào) = XS_KC.學(xué)號(hào)第三章作業(yè)：(第1次)1. 完成實(shí)驗(yàn)二中的1-33小題 概念 查詢(xún)塊：一個(gè)select-from-where語(yǔ)句稱(chēng)為一個(gè)查詢(xún)塊。 嵌套查詢(xún)：將一個(gè)查詢(xún)塊嵌套在另一個(gè)查詢(xún)塊的where子句或having子句中的查詢(xún)。子查詢(xún)的結(jié)果用于建立主查詢(xún)的查找條件。 處理步驟 一般由里向外進(jìn)行處理。3.2.3 嵌套查詢(xún)問(wèn)題為什么要用嵌套查詢(xún):查找選修離散數(shù)學(xué)的學(xué)生的學(xué)號(hào)。select 學(xué)號(hào)from kc,xs_kcwhere kc.課程號(hào)=xs_kc.課程號(hào) and

32、 課程名=離散數(shù)學(xué)select 學(xué)號(hào)from xs_kcwhere 課程號(hào)=(select 課程號(hào) from kc where 課程名=離散數(shù)學(xué)) 注意 子查詢(xún)必須用括號(hào)括住。 子查詢(xún)可以有多層，所存取的表可以是父查詢(xún)沒(méi)有存取的表。 當(dāng)子查詢(xún)所返回的不是一個(gè)值而是一個(gè)集合時(shí)，就不能使用簡(jiǎn)單的比較運(yùn)算了。必須根據(jù)語(yǔ)義在子查詢(xún)前加上ANY或者ALL；或者使用集合操作符IN及NOT; 子查詢(xún)中不能有ORDER BY子句。 子查詢(xún)選出的記錄不顯示。 注意 子查詢(xún)必須用括號(hào)括住。 子查詢(xún)可以有多層，所存取的表可以是父查詢(xún)沒(méi)有存取的表。 當(dāng)子查詢(xún)所返回的不是一個(gè)值而是一個(gè)集合時(shí)，就不能使用簡(jiǎn)單的比較運(yùn)算了

33、。必須根據(jù)語(yǔ)義在子查詢(xún)前加上ANY或者ALL；或者使用集合操作符IN及NOT; 子查詢(xún)中不能有ORDER BY子句。 子查詢(xún)選出的記錄不顯示。1. 返回單值的子查詢(xún) 子查詢(xún)返回的結(jié)果是一個(gè)值時(shí)，可以使用比較運(yùn)算符（=, , =, ANY 大于子查詢(xún)結(jié)果中的某個(gè)值 ALL 大于子查詢(xún)結(jié)果中的所有值 ANY 小于子查詢(xún)結(jié)果中的某個(gè)值 = ANY 大于等于子查詢(xún)結(jié)果中的某個(gè)值 = ALL 大于等于子查詢(xún)結(jié)果中的所有值= ANY 小于等于子查詢(xún)結(jié)果中的某個(gè)值 = ALL 小于等于子查詢(xún)結(jié)果中的所有值= ANY 等于子查詢(xún)結(jié)果中的某個(gè)值 =ALL 等于子查詢(xún)結(jié)果中的所有值（通常沒(méi)有實(shí)際意義）!=（或）A

34、NY 不等于子查詢(xún)結(jié)果中的某個(gè)值!=（或）ALL 不等于子查詢(xún)結(jié)果中的任何一個(gè)值例37：查找比所有計(jì)算機(jī)系的學(xué)生年齡都大的學(xué)生。 SELECT * FROM XS WHERE 出生時(shí)間 ALL( SELECT 出生時(shí)間 FROM XS WHERE 專(zhuān)業(yè)名 = 計(jì)算機(jī))例38：查找課程號(hào)206的成績(jī)不低于課程號(hào)101的最低成績(jī)的學(xué)生的學(xué)號(hào)。SELECT 學(xué)號(hào) FROM XS_KCWHERE 課程號(hào) = 206 AND 成績(jī) ! ANY ( SELECT 成績(jī) FROM XS_KC WHERE 課程號(hào) = 101 ) ANY（或SOME），ALL謂詞與聚集函數(shù)、IN謂詞的等價(jià)轉(zhuǎn)換關(guān)系 = 或!=

35、=ANY IN - MAXMIN= MINALL - NOT IN MINMAX= MAXSELECT 學(xué)號(hào),成績(jī) FROM XS_KCWHERE 課程號(hào) = 206 AND 成績(jī) ( SELECT min(成績(jī)) FROM XS_KC WHERE 課程號(hào) = 101 ) EXISTS 為存在量詞，用來(lái)測(cè)試子查詢(xún)是否返回結(jié)果； EXISTS表示存在量詞，帶有EXISTS的子查詢(xún)不返回任何實(shí)際數(shù)據(jù)，它只得到邏輯值“真”或“假”。其目標(biāo)列表達(dá)式通常都用*，給出列名無(wú)實(shí)際意義。 當(dāng)子查詢(xún)的的查詢(xún)結(jié)果集合為非空時(shí)，外層的WHERE子句返回真值，否則返回假值。NOT EXISTS與此相反。3. 嵌套查詢(xún)

36、可以是多層的 查詢(xún)涉及多個(gè)關(guān)系時(shí)，利用嵌套查詢(xún)逐次求解層次分明，容易理解也容易書(shū)寫(xiě)，具有結(jié)構(gòu)化程序設(shè)計(jì)的特點(diǎn)。 嵌套查詢(xún)的執(zhí)行效率比連接查詢(xún)效率高。例39：求選修了課程名為數(shù)據(jù)結(jié)構(gòu)的學(xué)生的學(xué)號(hào)和姓名。 SELECT 學(xué)號(hào),姓名FROM XSwhere 學(xué)號(hào) in( select 學(xué)號(hào) from xs_kc where 課程號(hào)=(select 課程號(hào) from kc where 課程名=離散數(shù)學(xué))4. 使用多個(gè)子查詢(xún)（多重子查詢(xún)） WHERE子句中可以包含任何數(shù)量的由AND和OR連接的含有子查詢(xún)的條件。例41：求與程明同系，或年齡大于王燕的學(xué)生的 信息。select * from xswhere

37、 專(zhuān)業(yè)名=(select 專(zhuān)業(yè)名 from xs where 姓名=程明)or 出生日期(select 出生日期 from xs where 姓名=王燕)例42：求計(jì)算機(jī)系中與數(shù)學(xué)系中同齡的學(xué)生信息select *from xs awhere 專(zhuān)業(yè)名=計(jì)算機(jī) and 出生日期 = (select 出生日期 from xs b where a.出生日期=b.出生日期 and 專(zhuān)業(yè)名=通信工程)5. 子查詢(xún)中使用表連接 有時(shí)，子查詢(xún)可能需要從每個(gè)表中檢索數(shù)據(jù)項(xiàng)，這時(shí)可在子查詢(xún)中使用表連接。例43：求計(jì)算機(jī)系中年齡相同的學(xué)生姓名和年齡。select 姓名,專(zhuān)業(yè)名,出生日期from xs a wher

38、e a.專(zhuān)業(yè)名=計(jì)算機(jī) and 出生日期=(select 出生日期 from xs b where a.出生日期=b.出生日期 and a.姓名b.姓名 and b.專(zhuān)業(yè)名=計(jì)算機(jī))select a.姓名,a.專(zhuān)業(yè)名,a.出生日期from xs a,xs b where a.專(zhuān)業(yè)名=計(jì)算機(jī) and b.專(zhuān)業(yè)名=計(jì)算機(jī) and a.出生日期=b.出生日期 and a.姓名b.姓名6. 相關(guān)子查詢(xún)例44查找選修206號(hào)課程的學(xué)生姓名。 SELECT 姓名 FROM XS WHERE EXISTS ( SELECT * FROM XS_KC WHERE 學(xué)號(hào) = XS.學(xué)號(hào) AND 課程號(hào) = 20

39、6 ) 二者的執(zhí)行方式不同普通子查詢(xún)的執(zhí)行順序是 首先執(zhí)行子查詢(xún)，然后把子查詢(xún)的結(jié)果作為父查詢(xún)的查詢(xún)條件的值。 普通子查詢(xún)只執(zhí)行一次，而父查詢(xún)所涉及的所有記錄行都與其查詢(xún)結(jié)果進(jìn)行比較以確定查詢(xún)結(jié)果集合。相關(guān)子查詢(xún)的執(zhí)行順序是 首先選取父查詢(xún)表中的第一行記錄，內(nèi)部的子查詢(xún)利用此行中相關(guān)的屬性值進(jìn)行查詢(xún)， 然后父查詢(xún)根據(jù)子查詢(xún)返回的結(jié)果判斷此行是否滿(mǎn)足查詢(xún)條件。如果滿(mǎn)足條件，則把該行放入父查詢(xún)的查詢(xún)結(jié)果集合中。重復(fù)執(zhí)行這一過(guò)程，直到處理完父查詢(xún)表中的每一行數(shù)據(jù)。 相關(guān)子查詢(xún)的執(zhí)行次數(shù)是由父查詢(xún)表的行數(shù)決定的。例45 查詢(xún)所有選修了101號(hào)課程的學(xué)生姓名。 思路分析： 本查詢(xún)涉及Student和Sc

40、ore關(guān)系； 在Student中依次取每個(gè)元組的Sno值，用此值去檢查Score關(guān)系； 若Score中存在這樣的元組，其Sno值等于此值，并且其Cno= 001，則取此送入結(jié)果關(guān)系 select 姓名from xswhere 學(xué)號(hào) in(select 學(xué)號(hào) from xs_kc where 課程號(hào)=101)select 姓名from xs,xs_kcwhere xs.學(xué)號(hào)=xs_kc.學(xué)號(hào) and 課程號(hào)=101select 姓名from xswhere exists(select * from xs_kc where 學(xué)號(hào)=xs.學(xué)號(hào) and 課程號(hào)=101)例46 查詢(xún)未選修101課程的學(xué)

41、生姓名。 SELECT SNAME FROM STUDENT S WHERE NOT EXISTS (SELECT * FROM SCORE SC WHERE CNO=001 AND SC.SNO=S.SNO ); 第三章作業(yè)：(第2次)1. 完成實(shí)驗(yàn)二中的34-55小題3.5 視圖(VIEW) 視圖可以由一個(gè)表中選取的某些列或某些行組成，也可由若干表中滿(mǎn)足一定條件的數(shù)據(jù)組成。簡(jiǎn)單地說(shuō)，視圖可以看成是一個(gè)窗口，它所反映的是一個(gè)表或若干表的局部數(shù)據(jù)。視圖一經(jīng)定義，用戶(hù)就可以把它當(dāng)作表一樣來(lái)查詢(xún)數(shù)據(jù)。 視圖和基本表不同，視圖是一個(gè)虛表，即視圖所對(duì)應(yīng)的數(shù)據(jù)不實(shí)際存在數(shù)據(jù)庫(kù)中，數(shù)據(jù)庫(kù)中只存儲(chǔ)視圖的定義

42、（存在數(shù)據(jù)字典中），因此不占用存儲(chǔ)空間。 視圖2視圖4視圖1視圖3基本表1基本表2基本表3基本表4 視圖是定義在基本表上的，也可以定義在視圖上；一個(gè)視圖可在幾個(gè)表或視圖上建立，一個(gè)表或視圖也可建立多個(gè)視圖。1. 視圖的定義與刪除 任何一個(gè)查詢(xún)結(jié)果其本身就是一個(gè)表，所以一個(gè)查詢(xún)可被用于定義一個(gè)視圖。 視圖的定義一般格式： CREATE VIEW (,) AS 子查詢(xún) WITH CHECK OPTION;說(shuō)明： 執(zhí)行CREATE VIEW語(yǔ)句時(shí)只是把視圖定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語(yǔ)句。 其中子查詢(xún)可以是任意復(fù)雜的SELECT語(yǔ)句，但通常不允許含有ORDER BY子句和DISTIN

43、CT短語(yǔ)。 WITH CHECK OPTION表示對(duì)視圖進(jìn)行UPDATE, INSERT和DELETE操作時(shí)要保證更新、插入和刪除的行滿(mǎn)足視圖定義中的謂詞條件。如果子查詢(xún)中包含有計(jì)算列，則必須指定列名（別名）。例56：建立計(jì)算機(jī)系的學(xué)生視圖。create view s_cas select * from xswhere 專(zhuān)業(yè)名=計(jì)算機(jī)例57：把學(xué)生的學(xué)號(hào)及它的平均成績(jī)定義為另一個(gè)視圖。create view s_g(學(xué)號(hào),平均成績(jī))as select 學(xué)號(hào),avg(成績(jī)) from xs_kcgroup by 學(xué)號(hào) 視圖的刪除一般格式： DROP VIEW ;drop view s_g1說(shuō)明：

44、 若導(dǎo)出視圖的基本表刪除，則由該基本表導(dǎo)出的所有視圖為不可用的視圖；視圖刪除后，與該視圖相關(guān)的基本表的數(shù)據(jù)不受任何影響。2. 視圖的查詢(xún) 視圖的查詢(xún)通常是通過(guò)轉(zhuǎn)換為基本表的查詢(xún)來(lái)實(shí)現(xiàn)的。即首先把查詢(xún)轉(zhuǎn)換成等價(jià)的對(duì)基本表的查詢(xún)，然后執(zhí)行修改了的查詢(xún)（系統(tǒng)從數(shù)據(jù)字典中取出視圖定義，然后把定義中的子查詢(xún)和視圖查詢(xún)語(yǔ)句結(jié)合起來(lái)，形成一個(gè)修正的查詢(xún)語(yǔ)句）。用戶(hù)查詢(xún)視圖基表DB用戶(hù)內(nèi)部例58：求平均成績(jī)?yōu)?0分以上的學(xué)生的學(xué)號(hào)和 成績(jī)（用例57的視圖）。 SELECT * FROM S_G WHERE 平均成績(jī)=90； 3. 視圖的更新 對(duì)視圖的更新最終要轉(zhuǎn)換成對(duì)基本表的更新，但實(shí)際上，在RDBMS中，并非所有的視圖都是可以更新的，有些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對(duì)基本表的更新。 例59：將001101學(xué)生的平均成績(jī)修改為90分。update s_gset 平均成績(jī)=90where 學(xué)號(hào)=001101視圖的更新必須遵守以下約束： 視圖的定義是從一個(gè)基本表導(dǎo)出的； 定義視圖的SELECT語(yǔ)句中不含有GROUP BY、DISTINCT和組函數(shù)； 視圖的任何要更新的列不是通過(guò)列表達(dá)式定義的； 表中的所有NOT NULL列都在視圖的定義中。 視圖的刪除操作：同時(shí)滿(mǎn)足和約束；delete from s_g where 學(xué)號(hào)=001