chapter03關(guān)系數(shù)據(jù)庫標準語言.ppt

普通高等教育十五規(guī)劃教材 數(shù)據(jù)庫系統(tǒng)概論,主講：張中軍 25 用戶名：s_zzj 密碼：空,第3章 關(guān)系數(shù)據(jù)庫標準語言SQL,第3章 關(guān)系數(shù)據(jù)庫標準語言SQL,3.1 引言 3.2 數(shù)據(jù)定義 3.3 數(shù)據(jù)查詢 3.4 數(shù)據(jù)更新 3.5 視圖,3.1 引言,引言,SQL（Structured Query Language）結(jié)構(gòu)化查詢語言，是關(guān)系數(shù)據(jù)庫的標準語言 SQL是一個通用的、功能極強的關(guān)系數(shù)據(jù)庫語言,背景,SQL語言最早稱為Sequel，是Boyce和Chamberlin1974年提出的。 Sequel不斷發(fā)展，并更名為SQL由于SQL 1974年在IBM公司的System R上實現(xiàn)。 1986年被美國國家標準局（ANSI）批準為關(guān)系數(shù)據(jù)庫語言的美國標準。 1987年國際標準化組織（ISO）通過這一標準 自SQL-92以來，SQL標準的規(guī)模開始變大（SQL-89標準大約120頁，SQL-92標準超過620頁，而SQL-99標準多達1700頁）。 目前，大多數(shù)商品化DBMS支持SQL-92主要部分（初級標準和部分中、高級的標準），并在其他方面有一些擴展。 SQL-99擴充太快，過于龐大，DBMS開發(fā)商對實現(xiàn)SQL-99似乎不太積極。,背景,標準 大致頁數(shù) 發(fā)布日期 SQL/86 1986.10 SQL/89(FIPS 127-1) 120頁 1989年 SQL/92 622頁 1992年 SQL99 1700頁 1999年 SQL2003 3600頁 2003年,SQL概述,SQL是一種完整的數(shù)據(jù)庫語言，它提供了豐富的功能 SQL的使用方式 SQL的表的類型,SQL概述(續(xù)),SQL的功能 SQL的數(shù)據(jù)定義語言（DDL）提供了模式定義、修改和刪除，基本表定義、修改和刪除、域定義修改和刪除。 SQL的數(shù)據(jù)操縱語言（DML）提供了數(shù)據(jù)查詢子語言。SQL的數(shù)據(jù)查詢子語言具有關(guān)系代數(shù)和關(guān)系演算的雙重特征。 SQL DML不僅包括數(shù)據(jù)查詢，而且包括數(shù)據(jù)更新（數(shù)據(jù)插入、刪除和修改）語句，允許用戶更新數(shù)據(jù)庫。 SQL DDL還允許用戶定義視圖，并且SQL DML允許用戶對視圖進行查詢和受限的更新操作。,SQL概述(續(xù)),SQL的功能(續(xù)) SQL DDL允許用戶定義各種完整性約束條件，并在數(shù)據(jù)庫訪問時自動檢查，確保數(shù)據(jù)庫操作不會破壞完整性約束條件。 SQL DDL還包括授權(quán)定義，用來定義用戶對數(shù)據(jù)庫對象（基本表、視圖等）的訪問權(quán)限，防止非法訪問，確保數(shù)據(jù)庫的安全性。 SQL還支持事務(wù)，提供了定義事務(wù)開始和結(jié)束的語句 SQL的使用方式 獨立使用 嵌入到通用程序設(shè)計語言中,SQL概述(續(xù)),SQL的表的類型 基本表（base table） 持久基本表（persistent base table） 全局臨時表（global temporary table） 局部臨時表 （local temporary table） 導(dǎo)出表,SQL的特點,SQL的特點 集多種數(shù)據(jù)庫語言于一體 高度非過程化 面向集合的操作方式 一種語法兩種使用方式 功能強大，語言簡潔,SQL的特點(續(xù)),1. 集多種數(shù)據(jù)庫語言于一體（綜合統(tǒng)一） 非關(guān)系模型的數(shù)據(jù)語言一般分為： 模式數(shù)據(jù)定義語言（模式DDL） 外模式數(shù)據(jù)定義語言（外模式DDL，子模式DDL） 數(shù)據(jù)存儲有關(guān)的描述語言（DSDL） 數(shù)據(jù)操縱語言（DML） 當(dāng)用戶數(shù)據(jù)庫投入運行后，如果要修改模式，必須停止運行，轉(zhuǎn)儲數(shù)據(jù)，修改模式并編譯后再重裝數(shù)據(jù)庫,SQL的特點(續(xù)),1. 集多種數(shù)據(jù)庫語言于一體(續(xù)) SQL語言集數(shù)據(jù)定義語言（DDL），數(shù)據(jù)操縱語言（DML），數(shù)據(jù)控制語言（DCL）功能于一體。 可以獨立完成數(shù)據(jù)庫生命周期中的全部活動 定義關(guān)系模式，插入數(shù)據(jù)，建立數(shù)據(jù)庫； 對數(shù)據(jù)庫中的數(shù)據(jù)進行查詢和更新； 數(shù)據(jù)庫重構(gòu)和維護 數(shù)據(jù)庫安全性、完整性控制等 用戶數(shù)據(jù)庫投入運行后，可根據(jù)需要隨時逐步修改模式，不影響數(shù)據(jù)的運行。 數(shù)據(jù)操作符統(tǒng)一，查找、插入、刪除、更新等只需一種操作符,SQL的特點(續(xù)),2.高度非過程化 非關(guān)系數(shù)據(jù)模型的數(shù)據(jù)操縱語言“面向過程”，必須使用類似于指針的機制，指定存取路徑 SQL只要提出“做什么”，無須了解存取路徑。 例如，使用SQL語言表達查詢時，用戶只需要正確地表達需要哪些信息，這些信息在哪些關(guān)系中，結(jié)果元組應(yīng)當(dāng)滿足什么條件 系統(tǒng)將考察多種執(zhí)行方案，選擇并運行一個最優(yōu)的執(zhí)行方案得到結(jié)果。 大大減輕了用戶負擔(dān) 有利于提高數(shù)據(jù)的獨立性,SQL的特點(續(xù)),3.面向集合的操作方式 非關(guān)系數(shù)據(jù)模型采用面向記錄的操作方式，操作對象是一條記錄 SQL采用集合操作方式 操作對象、查找結(jié)果可以是元組的集合 一次插入、刪除、更新操作的對象可以是元組的集合,SQL的特點(續(xù)),4. 一種語法兩種使用方式 SQL是獨立的語言 能夠獨立地用于聯(lián)機交互的使用方式 SQL又是嵌入式語言 SQL能夠嵌入到高級語言（例如C，C+，Java）程序中，供程序員設(shè)計程序時使用,SQL的特點(續(xù)),5 .功能強大, 語言簡潔 SQL是一種完整地數(shù)據(jù)庫語言，其功能涵蓋數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)控制等數(shù)據(jù)管理的主要需求。 但SQL語言相對比較簡潔，其核心動詞只有9個（p81）。 SQL 語言的語法簡單，與英語口語的風(fēng)格類似，易學(xué)易用。,3.2 數(shù)據(jù)定義,3.2 數(shù)據(jù)定義,SQL的數(shù)據(jù)定義語言DDL包括定義模式、關(guān)系（SQL稱之為基本表）、視圖、索引、授權(quán)等。 本節(jié)介紹如何定義基本表、索引和模式， 3.6節(jié)將介紹如何定義和使用視圖， 授權(quán)定義將在后面章節(jié)討論,SQL的數(shù)據(jù)類型,SQL支持許多內(nèi)置的數(shù)據(jù)類型 允許用戶定義新的域（數(shù)據(jù)）類型。 SQL支持的數(shù)據(jù)類型包括 CHARACTER CHARACTER VARYING BIT BIT VARYING INTEGER SMALLINT,SQL的數(shù)據(jù)類型(續(xù)),SQL支持的數(shù)據(jù)類型 NUMERIC FLOAT REAL DOUBLE PRECISION DATETIME TIMESTAMP INTERVAL,SQL的數(shù)據(jù)類型(續(xù)),CHAR(n)：定長字符串，長度n由用戶指定。省略(n)時，長度為1。CHAR的全稱是CHARACTER。 VARCHAR(n)：變長字符串，最大長度n由用戶指定。VARCHAR的全稱是CHARACTER VARYING 定長和變長字符串的差別主要表現(xiàn)在前者需要固定長度的空間，而后者占用的空間在最大長度范圍內(nèi)是可改變的。 BIT(n)：定長二進位串，長度n由用戶指定。省略(n)時，長度為1。 BIT VARYING(n)：變長二進位串，最大長度n由用戶指定。,SQL的數(shù)據(jù)類型(續(xù)),INT：整數(shù)，其值域依賴于具體實現(xiàn)。INT的全稱是INTEGER。 SMALLINT：小整數(shù)，其值域依賴于具體實現(xiàn)，但小于INT的值域 NUMERIC(p, d)：p位有效數(shù)字的定點數(shù)，其中小數(shù)點右邊占d位。 FLOAT(n)：精度至少為n位數(shù)字的浮點數(shù)，其值域依賴于實現(xiàn)。 REAL：實數(shù)，精度依賴于實現(xiàn)。 DOUBLE PRECISION：雙精度實數(shù)，精度依賴于實現(xiàn)，但精度比REAL高。,SQL的數(shù)據(jù)類型(續(xù)),DATETIME ：日期時間，包括年、月、日、時、分、秒，格式為YYYY-MM-DD hh-mm-ss。 TIMESTAMP：時間戳，是DATE和TIME的結(jié)合 INTERVAL：時間間隔。SQL允許對DATE、TIME和INTERVAL類型的值進行計算。,SQL的數(shù)據(jù)類型(續(xù)),SQL提供DATEPART(field , Var)，從DATETIME類型變量Var中提取字段field。 對于DATETIME類型的變量，field可以是YEAR、MONTH、DAY、HOUR、MINUTE 、SECOND； 例如，如果d是DATETIME類型，則DATEPART (YEAR , d)返回d中的年份。,定義、修改和刪除基本表,說明： 本章，我們將“表”和“關(guān)系”視為同義詞（盡管它們實際上有差別） 將“屬性”、“屬性列”和“列”視為同義詞 使用“元組”或“記錄”表示表的行。 術(shù)語“基本表”主要用于表示持久基本表，而“表”泛指基本表和導(dǎo)出表（包括視圖）,定義、修改和刪除基本表(續(xù)),符號約定 表示X是需要進一步定義或說明語言成分。 X表示X可以缺省或出現(xiàn)一次。 X表示X可以出現(xiàn)一次。 X | Y表示或者X出現(xiàn)，或者Y出現(xiàn)，但二者不能同時出現(xiàn)。 SQL語言的保留字（如CREATE）不區(qū)分大小寫。為醒目起見，對于SQL語句中的SQL的保留字，我們使用大寫。 SQL語句用分號結(jié)束。一個SQL語句可以寫在一行或多行中，各種空白符號用于分隔不同的詞。良好的語句的書寫風(fēng)格使得程序賞心悅目、易于閱讀。,定義基本表,創(chuàng)建一個基本表要對基本表命名，定義表的每個列，并定義表的完整性約束條件。 SQL語言使用CREATE TABLE語句創(chuàng)建基本表，其基本格式如下： CREATE TABLE (, , , , )； 是標識符，對定義的基本表命名； 圓括號中包括一個或多個，零個或多個，中間用逗號隔開,定義基本表(續(xù)),定義每個屬性（列）的名稱、類型、缺省值和列上的約束條件，格式如下： DEFAULT , , 其中，是標識符，對定義的列命名； 定義列的取值類型，它可以是3.2.1節(jié)介紹的任意類型，也可以是用戶定義的域類型（見5.3節(jié)）； 可選短語“DEFAULT ”定義列上的缺省值，是中的一個特定值或NULL ; 每個列上可以定義零個或多個約束條件，約束列的取值,定義基本表(續(xù)),列約束定義格式如下 CONSTRAINT 其中可選短語“CONSTRAINT ”為列約束命名 常用的列約束包括： NOT NULL：不允許該列取空值 不加NOT NULL限制時，該列可以取空值。 PRIMARY KEY：指明該列是主碼，值非空、惟一。 UNIQUE：該列上的值必須惟一 相當(dāng)于說明該列為候選碼。 CHECK ()：指明該列的值必須滿足的條件，其中是一個涉及該列的布爾表達式,定義基本表(續(xù)),一個表可以包含零個或多個，用于定義主碼、其他候選碼、外碼和表上的其它約束。表約束定義定義形式如下： CONSTRAINT 其中可選短語“CONSTRAINT ”為表約束命名 PRIMARY KEY (A1, , Ak)：說明屬性列A1, , Ak構(gòu)成該關(guān)系的主碼。當(dāng)主碼只包含一個屬性時，也可以用列約束定義主碼。 UNIQUE (A1, , Ak)：說明屬性A1, , Ak上值必須惟一 相當(dāng)于說明A1, , Ak構(gòu)成該關(guān)系的候選碼 當(dāng)候選碼只包含一個屬性時，也可以用列約束定義候選碼。,定義基本表(續(xù)),CHECK ()：說明該表上的一個完整性約束條件。通常，是一個涉及該表一個或多個列的布爾表達式 外碼比較復(fù)雜，它具有如下形式： FOREIGN KEY (A1, , Ak) REFERENCES () 屬性A1, , Ak是關(guān)系（表）的外碼 給出被參照關(guān)系的表名 給出被參照關(guān)系的主碼 說明違反參照完整性時需要采取的措施,定義基本表(續(xù)),例3.1 下面的語句創(chuàng)建教師表Teachers CREATE TABLE Teachers (Tno CHAR (7) PRIMARY KEY, Tname CHAR (10) NOT NULL, Sex CHAR (2) CHECK (Sex= 男 OR Sex= 女), Birthday DATETIME, Title CHAR (6), Dno CHAR (4), FOREIGN KEY (Dno) REFERENCES Departments (Dno);,主碼,列約束,表級約束,定義基本表(續(xù)),創(chuàng)建選課表SC用如下語句： CREATE TABLE SC (Sno CHAR (9), Cno CHAR (5), Grade SMALLINT CHECK (Grade=0 AND Grade=100), PRIMARY KEY (Sno,Cno), FOREIGN KEY (Sno) REFERENCES Students (Sno), FOREIGN KEY (Cno) REFERENCES Courses (Cno);,定義基本表(續(xù)),CREATE TABLE Departments (Dno CHAR(4) PRIMARY KEY, Dname CHAR (10), Dheadno CHAR (7), FOREIGN KEY (Dheadno) REFERENCES Teachers(Tno);,定義基本表(續(xù)),CREATE TABLE Students (Sno CHAR(9) PRIMARY KEY, Sname CHAR(10) NOT NULL, Sex CHAR (2) CHECK (Sex=男 OR Sex=女), Birthday DATETIME, Enrollyear CHAR(4), Speciality CHAR(20), Dno CHAR (3), FOREIGN KEY (Dno) REFERENCES Department (Dno);,定義基本表(續(xù)),CREATE TABLE Courses (Cno CHAR(5) PRIMARY KEY, Cname CHAR(20) NOT NULL, Period SMALLINT, Credit SMALLINT);,定義基本表(續(xù)),CREATE TABLE Teaches (Tno CHAR (7), Cno CHAR (5), TCscore SMALLINT, PRIMARY KEY (Tno,Cno), FOREIGN KEY (Tno) REFERENCES Teachers (Tno), FOREIGN KEY (Cno) REFERENCES Courses (Cno);,修改基本表,基本表創(chuàng)建好以后，在某些情況下需要修改它的結(jié)構(gòu)。 SQL允許 添加列定義 刪除列 添加表約束 刪除表約束。 使用ALTER TABLE語句修改基本表,修改基本表(續(xù)),1. 向基本表添加新的列 ALTER TABLE ADD COLUMN COLUMN可以省略（下同） 和創(chuàng)建基本表相同，但是新添加的列一般不允許用NOT NULL說明。,修改基本表(續(xù)),3. 刪除已存在的列 ALTER TABLE DROP COLUMN CASCADE | RESTRICT 有些版本的SQL支持CASCADE | RESTRICT CASCADE表示級聯(lián)，刪除將成功，并且依賴于該列的數(shù)據(jù)庫對象（如涉及該列的視圖）也一并刪除 RESTRICT表示受限，僅當(dāng)沒有依賴于該列的數(shù)據(jù)庫對象時刪除才能成功 4. 添加表約束 ALTER TABLE ADD 其中與創(chuàng)建基本表相同。,修改基本表(續(xù)),5. 刪除表約束 ALTER TABLE DROP CONSTRAINT CASCADE | RESTRICT 其中被刪除的約束一定是命名的約束，給出約束名 CASCADE | RESTRICT只在有些版本的SQL支持 CASCADE導(dǎo)致刪除約束并且同時刪除依賴于該約束的數(shù)據(jù)庫對象 RESTRICT僅當(dāng)不存在依賴于該約束的數(shù)據(jù)庫對象時才刪除該約束。,修改基本表(續(xù)),例3.2 在Courses中增加一個新列Pno，表示課程的先行課的課程號，可以用 ALTER TABLE Courses ADD Pno CHAR (5); 刪除Courses中的Pno列可以用 ALTER TABLE Courses DROP COLUMN Pno;,刪除基本表,當(dāng)不需要某個基本表時，可以使用DROP TABLE語句將它刪除。語句格式為： DROP TABLE CASCADE RESTRICT SQL以前版本可帶CASCADE RESTRICT可選項 CASCADE表示及聯(lián)刪除，依賴于表的數(shù)據(jù)對象（最常見的是視圖）也將一同被刪除 RESTRICT表示受限刪除，如果基于該表定義有視圖，或者有其他表引用該表（如CHECK、FOREIGN KEY等約束），或者該表有觸發(fā)器、存儲過程或函數(shù)等，則不能刪除。 刪除基本表導(dǎo)致存放在表中的數(shù)據(jù)和表定義都將被徹底刪除。,刪除基本表(續(xù)),例3.3 如果用如下語句刪除SC表 DROP TABLE Course RESTRICT; 則僅當(dāng)沒有依賴于Course的任何數(shù)據(jù)庫對象才能刪除。如果用 DROP TABLE Course CASCADE; 則表Course和依賴它的數(shù)據(jù)庫對象都被徹底刪除 注意：SQL2005好像不支持CASCADE RESTRICT， 基本表的刪除是永久的，不可恢復(fù)的?；颈碇械臄?shù)據(jù)及索引將全部被刪除掉。在該表上建立的視圖依然存在但不可使用。因此執(zhí)行刪除基本表的操作時要格外小心。,創(chuàng)建和刪除索引,索引類似于書的目錄。 索引可以加快表之間的連接速度，加快表的排序和分組工作。 索引屬于物理存儲的路徑概念，而不是邏輯的概念。 索引由DBA或表的屬主負責(zé)建立和刪除，其他用戶不能隨意建立和刪除索引。 索引由DBMS自動選擇和維護。索引通常分為惟一性索引（每一個索引值對應(yīng)一個數(shù)據(jù)行）和非惟一性索引；也可以分為聚族索引和非聚族索引。,創(chuàng)建索引,通常，DBMS自動為主碼建立索引 其他索引需要用CREATE INDEX語句創(chuàng)建。創(chuàng)建索引的語句格式為： CREATE UNIQUE CLUSTERED INDEX ON ( , ) 為建立的索引命名 是要建立索引的基本表的名字 索引可以建在該表的一列或多列上，各列名間用逗號分隔；每個后可以用指定索引值的排列次序 次序可以是ASC（升序）和DESC（降序），缺省值為ASC。,創(chuàng)建索引(續(xù)),UNIQUE表示該索引為惟一性索引。UNIQUE缺省時，創(chuàng)建的索引為非唯一性索引。 CLUSTER表示建立的索引是聚簇索引，缺省時為非聚簇索引。 創(chuàng)建索引不僅創(chuàng)建索引結(jié)構(gòu)，而且將索引的定義存儲在數(shù)據(jù)字典中。,創(chuàng)建索引(續(xù)),例3.4 在Students的Dno上創(chuàng)建一個名為Student_Dept的索引 可以用： CREATE INDEX Student_Dept ON Students (Dno); 而在Teachers上的Dno創(chuàng)建一個名為Teacher-Dept的聚簇索引可以用： CREATE CLUSTERED INDEX Teacher_Dept ON Teachers (Dno); 注意：學(xué)生流動性比較大，Students更新頻繁，不適合創(chuàng)建聚簇索引；而教師相對穩(wěn)定，可以考慮按所在院系在Teachers上創(chuàng)建聚簇索引。,刪除索引,索引旦建立，就由系統(tǒng)來選擇和維護，無需用戶干預(yù)，但當(dāng)刪除一些不必要的索引時，可用下列語句來實現(xiàn)： DROP INDEX ON 刪除索引時，系統(tǒng)將刪除索引結(jié)構(gòu)，并同時從數(shù)據(jù)字典中刪去有關(guān)該索引的定義。 例3.5 刪除索引Student_Dept。 DROP INDEX Student_Dept ON Students；,模式的定義和刪除,支持SQL的DBMS提供了一個SQL環(huán)境（SQL-environment) SQL環(huán)境包括 零個或多個數(shù)據(jù)庫 零個或多個用戶標識符（稱作授權(quán)標識符） 零個或多個模塊和目錄中的模式描述的SQL數(shù)據(jù)。 DBMS為關(guān)系的命名提供了一個三級層次結(jié)構(gòu) 頂層由數(shù)據(jù)庫組成 每個數(shù)據(jù)庫中包含一些模式（schema） 而SQL對象（關(guān)系、視圖等）都包含在模式內(nèi)。,模式的定義和刪除(續(xù)),注意： SQL環(huán)境中的數(shù)據(jù)庫不能重名 同一數(shù)據(jù)庫下的模式不能重名 同一模式下的關(guān)系不能重名 一個關(guān)系由數(shù)據(jù)庫名、模式名和關(guān)系名惟一確定，例如 Database2.Supply-schema.Suppliers 確定Database2目錄下Supply-schema模式中的Suppliers關(guān)系。如果關(guān)系在默認數(shù)據(jù)庫的默認模式中，則可以省略目錄名和模式名前綴,模式的定義和刪除(續(xù)),為了進行數(shù)據(jù)庫操作，首先必須連接到數(shù)據(jù)庫 當(dāng)用戶（程序）連接到數(shù)據(jù)庫時，系統(tǒng)為該連接建立一個默認的模式。 模式的創(chuàng)建、設(shè)置和刪除依賴于具體實現(xiàn)，不包含在SQL標準中 可以用CREATE SCHEMA和DROP SCHEMA創(chuàng)建和刪除模式,創(chuàng)建模式,誰有權(quán)創(chuàng)建模式依賴于實現(xiàn) 通常，DBMS規(guī)定只有DBA和或經(jīng)DBA授權(quán)創(chuàng)建模式的用戶才能創(chuàng)建模式 創(chuàng)建模式的語句格式有兩種 第一種格式 CREATE SCHEMA 創(chuàng)建一個以命名的模式，并可以在創(chuàng)建模式的同時為該模式創(chuàng)建或不創(chuàng)建模式元素。 可以是表定義、視圖定義、斷言定義、授權(quán)定義等。 這種格式?jīng)]有授權(quán)其他用戶訪問創(chuàng)建的模式，以后可以用授權(quán)語句授權(quán)。,創(chuàng)建模式(續(xù)),第二種格式 CREATE SCHEMA AUTHORIZATION 與第一種的區(qū)別在于它將創(chuàng)建的模式授權(quán)予指定的用戶 當(dāng)缺省時，用作為模式名 實際上，創(chuàng)建一個模式就相當(dāng)于創(chuàng)建一個數(shù)據(jù)庫（微軟的前幾個版本SQL Server只用CREATE DATBASE，2005中才真正體現(xiàn)出模式的概念）。,創(chuàng)建模式(續(xù)),例3.6 為WangQiang創(chuàng)建一個名為Supply_schema的模式，可以用： CREATE SCHEMA Supply_schema AUTHORIZATION WangQiang; 所創(chuàng)建的模式在當(dāng)前目錄下，并為WangQiang所擁有 如果用 CREATE SCHEMA Supply_schema; 則創(chuàng)建一個名為Supply_schema的模式，但未向任何用戶授權(quán) 如果用 CREATE SCHEMA AUTHORIZATION WangQiang; 則為WangQiang創(chuàng)建一個模式，并用WangQiang命名,創(chuàng)建模式(續(xù)),還可以在創(chuàng)建模式的同時創(chuàng)建該模式中的對象 例如 CREATE SCHEMA Supply_schema CREATE TABLE Suppliers (Sno CHAR(5) PRIMERY KEY, Sname CHAR(20) NOT NULL, Status SMALLINT, Address CHAR(30), Phone CHAR(10); 在創(chuàng)建模式Supply_schema的同時還在該模式中定義了一個基本表Suppliers,刪除模式,DBA和模式的擁有者可以用DROP SCHEMA刪除模式。刪除模式的語句格式為： DROP SCHEMA CASCADERESTRICT 其中CASCADE和RESTRICT兩者必須選擇其一。選擇CASCADE，則刪除指定模式得同時并刪除該模式中的所有數(shù)據(jù)庫對象（基本表、視圖、斷言等）；選擇RESTRICT，則僅當(dāng)指定的模式不包含任何數(shù)據(jù)庫對象時才刪除指定的模式，否則拒絕刪除。,刪除模式(續(xù)),例3.7 語句 DROP SCHEMA Supply_schema RESTRICT 僅當(dāng)模式Supply_schema中不包含任何數(shù)據(jù)庫對象時，才刪除模式Supply_schema，否則什么也不做。而 DROP SCHEMA Supply_schema CASCADE 將直接刪除模式Supply_schema，并同時刪除該模式中所有的數(shù)據(jù)庫對象,3.3 數(shù)據(jù)查詢,數(shù)據(jù)查詢,查詢是數(shù)據(jù)庫的最重要的操作 在SQL中，所有查詢都用SELECT語句實現(xiàn) 查詢在一個或多個關(guān)系（基本表或視圖）上進行，其結(jié)果是一個關(guān)系,數(shù)據(jù)查詢(續(xù)),3.3.1 SELECT語句的一般形式 3.3.2 不帶WHERE的簡單查詢 3.3.3 帶WHERE子句的查詢 3.3.4 排序和分組 3.3.5 連接查詢 3.3.6 嵌套查詢 3.3.7 子查詢導(dǎo)出的表 3.3.8 集合運算,SELECT語句的一般形式,SELECT語句的一般形式如下： SELECT ALLDISTINCT FROM , , WHERE GROUP BY , HAVING ORDER BY ASCDESC , ASCDESC 其中最基本的結(jié)構(gòu)是SELECT-FROM-WHERE，并且SELECT子句和FROM子句是必須的，其他子句都是可選的,我們先介紹SELECT語句的基本結(jié)構(gòu)。,SELECT語句,1. SELECT子句 相當(dāng)于關(guān)系代數(shù)的投影運算（更準確地說，相當(dāng)于廣義投影），用來列出查詢結(jié)果表的諸列 SELECT后可以使用集合量詞ALL或DISTINCT，缺省時為ALL ALL不刪除結(jié)果的重復(fù)行，而DISTINCT將刪除結(jié)果中的重復(fù)行 ALL或DISTINCT作用于所有列而不是一個列 有兩種形式 列舉查詢結(jié)果的每個列 *,SELECT語句(續(xù)),1. SELECT子句 列舉查詢結(jié)果的每個列 結(jié)果列的次序可以任意指定，列之間用逗號隔開。每個結(jié)果列具有如下形式： AS 最常見形式為 . 必須出現(xiàn)在FROM子句指定的表中 在不會引起混淆（即只出現(xiàn)在一個表中）時，前的可選前綴“.”可以缺省。是所在表的名字或別名（見FROM子句） 可以是“*”,SELECT語句(續(xù)),2. FROM子句 相當(dāng)于關(guān)系代數(shù)的笛卡爾積運算，用來列出查詢需要掃描的基本表或?qū)С霰?FROM子句中可以有一個或多個，中間用逗號隔開。的最常見形式是： AS 可選短語“AS ”（可省略AS）用來對表起別名（在SELECT-FROM-WHERE結(jié)構(gòu)中有效） 當(dāng)同一個表在SELECT-FROM-WHERE結(jié)構(gòu)中重復(fù)出現(xiàn)時，使用別名可以很好地區(qū)分它們的不同出現(xiàn)。當(dāng)列名前需要帶表名前綴時，使用較短的別名也可以簡化語句書寫,SELECT語句(續(xù)),3. WHERE子句 WHERE子句相當(dāng)于關(guān)系代數(shù)中的選擇運算，是作用于FROM子句中的表和視圖的選擇謂詞 WHERE子句缺省時等價于WHERE TRUE（即查詢條件為恒真條件）?？梢苑浅?fù)雜 基本SELECT語句的執(zhí)行相當(dāng)于 首先求FROM子句指定的基本表或?qū)С霰淼牡芽柗e 然后根據(jù)WHERE子句的查詢條件從中選擇滿足查詢條件的元組 最后投影到SELECT子句的結(jié)果列上，得到查詢的回答.,不帶WHERE的簡單查詢,最簡單的SELECT語句是只包括SELECT和FROM子句。這種語句只能完成對單個表的投影運算。 也是就是教材上所說的“選擇表中的若干列” 例3.8 查詢所有課程的信息可以用： SELECT Cno, Cname, Period, Credit FROM Courses; 或簡單地用： SELECT * FROM Courses;,不帶WHERE的簡單查詢(續(xù)),用“*”來表示所有的屬性列時，得到的查詢表的屬性次序?qū)⒑捅矶x的屬性次序一致 如果用第一種形式，屬性的次序不必與表屬性的定義次序一致，并且允許只顯示我們感興趣的某些屬性 例如，下面的語句將顯示每門課程的課程號和學(xué)分： SELECT Cno, Credit FROM Courses;,不帶WHERE的簡單查詢(續(xù)),SELECT子句中的列可以是表達式，如下面的例子所示。 例3.9 假定當(dāng)前年份為2010，下面的語句將顯示每位學(xué)生的年齡： SELECT 2010DATEPART(YEAR,Birthday) AS Age FROM Students; AS Age（可以省略AS）用Age對表達式2010DATEPART(YEAR,Birthday)重新命名，導(dǎo)致結(jié)果為單個屬性Age的表.,不帶WHERE的簡單查詢(續(xù)),如果去掉AS Age，則查詢結(jié)果的列名為“無列名” 。該查詢將顯示每位學(xué)生的年齡 由于學(xué)生的人數(shù)眾多，這可能是一個很長的年齡列表，其中幾乎每個值都重復(fù)出現(xiàn)很多次 SQL允許我們用DISTINCT短語強制刪除重復(fù)元組。下面的語句將所有顯示所有學(xué)生的不同年齡： SELECT DISTINCT 2010DATEPART(YEAR,Birthday) Age FROM Students;,帶WHERE的簡單查詢,稍微復(fù)雜一點的查詢都需要WHERE子句，用來說明一個查詢條件 一般地，查詢條件是一個布爾表達式。 布爾表達式是由基本布爾表達式用圓括號和邏輯運算符（NOT、AND和OR）構(gòu)成的表達式。 基本布爾表達式可以是邏輯常量（TRUE和FALSE）、比較表達式、BETWEEN表達式、IN表達式、LIKE表達式、NULL表達式、量化比較表達式、存在表達式、唯一表達式或匹配表達式,帶WHERE的簡單查詢(續(xù)),1. 比較表達式 比較表達式的常見形式如下： 其中是比較運算符（、=、=、或!=） 和都是可求值的表達式，并且它們的值可以進行比較。通常，這些值表達式是常量、屬性和函數(shù)。比較表達式根據(jù)比較關(guān)系是否成立產(chǎn)生真假值,帶WHERE的簡單查詢(續(xù)),例3.10 (1) 查詢職稱（Title）為講師的全體教師的姓名和性別。 SELECT Tname, Tsex FROM Teachers WHERE Title = 講師; (2) 查詢考試成績不及格的學(xué)生的學(xué)號。 SELECT DISTIINCT Sno FROM SC WHERE Grade60;,帶WHERE的簡單查詢(續(xù)),2. BETWEEN表達式 判定一個給定的值是否在給定的閉區(qū)間，其最常見形式是： NOT BETWEEN AND 其中、和都是可求值的表達式，其值是序數(shù)類型 的值小于或等于 當(dāng)且僅當(dāng)?shù)闹翟诤痛_定的閉區(qū)間時， BETWEEN AND 為真，而 NOT BETWEEN AND 為假,帶WHERE的簡單查詢(續(xù)),例3.11 (1) 查詢查詢出生年份在19871990年之間的學(xué)生的姓名和所在院系。 SELECT Sname, Sdept FROM Students WHERE DATEPART(YEAR,Birthday) BETWEEN 1987 AND 1990; (2) 查詢查詢出生年份不在19871990年之間的學(xué)生的姓名和所在院系。 SELECT Sname, Sdept FROM Students WHERE DATEPART(YEAR,Birthday) NOT BETWEEN 1987 AND 1990;,帶WHERE的簡單查詢(續(xù)),BETWEEN-AND和NOT BETWEEN-AND表示的條件一般都可以用AND或OR連接的多重比較表示 例如，查詢(1)可以用： SELECT Sname, Sdept FROM Students WHERE DATEPART(YEAR,Birthday)=1987 AND DATEPART(YEAR,Birthday)1990;,帶WHERE的簡單查詢(續(xù)),3. IN表達式 判定一個給定的元素是否在給定的集合中 IN表達式有兩種形式： NOT IN () | NOT IN 在第一種形式中，是可求值的，（通常是屬性），而包括一個或多個可求值的表達式（通常是字面值，如45，教授等），中間用逗號隔開 當(dāng)且僅當(dāng)?shù)闹党霈F(xiàn)在中, IN ()為真, 而 NOT IN ()為假。,帶WHERE的簡單查詢(續(xù)),例3.12 (1) 查詢計算機科學(xué)與技術(shù)和軟件工程專業(yè)的學(xué)生的學(xué)號和姓名。 SELECT Sno, Sname FROM Students WHERE Speciality IN (計算機科學(xué)與技術(shù), 軟件工程) (2) 查詢既不是計算機科學(xué)與技術(shù)，也不是軟件工程專業(yè)的學(xué)生的學(xué)號和姓名。 SELECT Sno, Sname FROM Students WHERE Speciality NOT IN (計算機科學(xué)與技術(shù), 軟件工程); 當(dāng)很小時，IN和NOT IN表示的查詢條件都容易用多重比較表示。然而，當(dāng)較大時，使用IN表達式更簡潔,帶WHERE的簡單查詢(續(xù)),4. LIKE表達式 使用比較運算符，兩個字符串可以在字典序下進行比較。但是這種比較是精確比較，不能解決諸如“查找課程名的前兩個漢字是數(shù)據(jù)的課程”這類模糊查詢。 LIKE表達式允許我們表示這類查詢。LIKE表達式的一般形式為： NOT LIKE ESCAPE 其中和都是字符串表達式，它們的值是可比較的。 通常，是屬性，是給定的字符串常量。中允許使用通配符,帶WHERE的簡單查詢(續(xù)),有兩種通配符 “_”（下橫線）可以與任意單個字符匹配 而“%”可以與零個或多個任意字符匹配 ESCAPE 通常的形式是ESCAPE 。它定義“”為轉(zhuǎn)義字符，將緊隨其后的一個字符轉(zhuǎn)義。如果中的_或%緊跟在之后，則這個_或%就失去了通配符的意義，而取其字面意義 當(dāng)且僅當(dāng)與匹配時， LIKE 為真，而 NOT LIKE 為假,帶WHERE的簡單查詢(續(xù)),例3.13(1) 查詢所有以“數(shù)據(jù)”開頭的課程名。 SELECT Cname FROM Courses WHERE Cname LIKE 數(shù)據(jù)%; (2) 查詢姓李并且姓名只有兩個漢字的學(xué)生的學(xué)號和姓名。 SELECT Sno, Sname FROM Students WHERE Sname LIKE 李 _ _;,帶WHERE的簡單查詢(續(xù)),(3) 查詢以C_打頭的課程的詳細信息。 由于通配符“_”出現(xiàn)在模式中，我們需要使用轉(zhuǎn)義字符將它轉(zhuǎn)義。該查詢可以用如下語句實現(xiàn)： SELECT * FROM Courses WHERE Cname LIKE C_% ESCAPE ; 其中，ESCAPE 短語定義“”為轉(zhuǎn)義字符，模式C_%中的“_”被轉(zhuǎn)義，不再取通配符含義，而是取字面意義。 注意：C_%中的“%”仍然是通配符，因為轉(zhuǎn)義字符只對緊隨其后的一個字符轉(zhuǎn)義,帶WHERE的簡單查詢(續(xù)),5. NULL表達式 SQL允許元組的某些屬性上取空值（NULL）?？罩荡砦粗闹?，不能與其他值進行比較 NULL表達式允許我們判定給定的值是否為空值。NULL表達式的常見形式如下 | IS NOT NULL NULL表達式可以判定一個特定的值或子查詢結(jié)果是否為空值。通常，是屬性 例如, 如果A是屬性,則A IS NULL 為真當(dāng)且僅當(dāng)屬性A上取空值,帶WHERE的簡單查詢(續(xù)),例3.14 查詢成績?yōu)榭盏膶W(xué)生的學(xué)號和課程號。 SELECT Sno, Cno FROM SC WHERE Grade IS NULL;,排序和分組,1. 將查詢結(jié)果排序 通常，查詢結(jié)果的顯示次序是任意的 查詢的結(jié)果按一定的次序顯示更便于觀察 ORDER BY子句可以將查詢的結(jié)果按一定次序顯示。并可以按多個結(jié)果列將查詢結(jié)果排序，其一般形式如： ORDER BY ASCDESC , ASCDESC 其中，是屬性名或?qū)傩缘膭e名，必須出現(xiàn)在SELECT子句中,排序和分組(續(xù)),ORDER BY后可以有一個或多個，中間用逗號隔開。每個都可以獨立指定按升序（ASC）還是按降序（DESC）排序，缺省時為升序 如果指定多個，則查詢結(jié)果按指定的次序，首先按第一個的值排序，第一個值相同的結(jié)果元組按第二個的值排序，如此下去,排序和分組(續(xù)),例3.15 (1) 查詢每位學(xué)生3號課程的成績，并將查詢結(jié)果按成績降序排序。 SELECT * FROM SC WHERE Cno=3 ORDER BY Grade DESC; (2) 查詢每位學(xué)生的每門課程的成績，并將查詢結(jié)果按課程號升序、成績降序排序。 SELECT * FROM SC ORDER BY Cno, Grade DESC;,排序和分組(續(xù)),2. 聚集函數(shù) 在實際應(yīng)用中，常常需要計算一些統(tǒng)計量 例如，統(tǒng)計學(xué)生的總?cè)藬?shù)、女生的人數(shù)、平均成績等 SQL語言提供了一些聚集函數(shù)，使用這些聚集函數(shù)可以方便的進行各種統(tǒng)計查詢。 SQL的聚集函數(shù)可以單獨使用 聚集函數(shù)作用于整個查詢結(jié)果 SQL的聚集函數(shù)也可以配合GROUP BY（分組）子句使用 聚集函數(shù)作用于查詢結(jié)果的每個分組 聚集函數(shù)單獨使用時，可以認為整個查詢結(jié)果形成一個分組,排序和分組(續(xù)),SQL的聚集函數(shù)具有如下形式： COUNT (ALL | DISTINCT *) 或 (ALL | DISTINCT ) 第一種情況比較簡單 COUNT (*)或COUNT (ALL *)返回每個分組中的元組個數(shù) COUNT (DISTINCT *)返回每個分組中不同元組的個數(shù)。,排序和分組(續(xù)),對于第二種情況，可以是 COUNT（計數(shù)） SUM（和） AVG（平均值） MAX（最大值） MIN（最小值） 是可求值的表達式，通常是屬性。短語ALL或DISTINCT是可選的，缺省時為ALL,排序和分組(續(xù)),設(shè)f是聚集函數(shù)，e是值表達式 f(ALL e)或f(e) 對每個分組，首先對該分組中每個元組計算e，得到e值的多重集；然后，將f作用于該多重集得到聚集函數(shù)值 而f(DISTINCT e)與f (e)的唯一不同是， f (DISTINCT e)在得到函數(shù)值之前要刪除多重集中的重復(fù)元素。 例如，設(shè)A是屬性 SUM(DISTINCT A)將對每個分組中的元組，在屬性A的不同值上求和 SUM(A)將簡單地對每個分組中的元組，在屬性A上求和,排序和分組(續(xù)),例3.16 (1) 查詢選修了3號課程的學(xué)生的人數(shù)。 SELECT COUNT (*) FROM SC WHERE Cno = 3; (2) 查詢3號課程成績最低分、平均分和最高分。 SELECT MIN (Grade), AVG (Grade), MAX (Grade) FROM SC WHERE Cno = 3;,排序和分組(續(xù)),3. 分組 SQL語言提供了GROUP BY子句用于分組，其一般形式：GROUP BY , HAVING 其中，是屬性（可以帶表名前綴），它所在的表出現(xiàn)在FROM子句中 可選的HAVING子句用來過濾掉不滿足的分組，缺省時等價于HAVING TRUE 類似于WHERE子句的查詢條件，但其中允許出現(xiàn)聚集函數(shù)。 對于帶GROUP BY子句的SELECT語句，SELECT子句中的結(jié)果列必須是GROUP BY子句中的或聚集函數(shù),排序和分組(續(xù)),帶GROUP BY子句的SELECT語句的執(zhí)行效果相當(dāng)于： 首先對FROM子句中的表計算笛卡爾積 再根據(jù)WHERE子句的查詢條件從中選擇滿足查詢條件的元組，得到查詢的中間結(jié)果 然后，按照GROUP BY子句指定的一個或多個列對中間結(jié)果分組，在這些列上的值相等的元組分為一組 計算聚集函數(shù)（如果SELECT子句或HAVING短語包含聚集函數(shù)的話），并按照HAVING短語中的分組選擇條件過濾掉不滿足條件的分組 最后，投影到SELECT子句的結(jié)果列上，得到查詢的回答,排序和分組(續(xù)),例3.17 查詢每個學(xué)生的平均成績，輸出學(xué)生的學(xué)號和平均成績。 SELECT Sno, AVG (Grade) FROM SC GROUP BY Sno; 例3.18 查詢每個學(xué)生的平均成績，并輸出平均成績大于85的學(xué)生學(xué)號和平均成績。 SELECT Sno, AVG (Grade) FROM SC GROUP BY Sno HAVING AVG (Grade)85;,連接查詢,SQL支持多表查詢，允許FROM子句中包括多個表 當(dāng)FROM子句中包含多個表時，相當(dāng)于求這些表的笛卡爾積 一般地，將來自不同表的任意元組串接在一起所形成的元組并沒有實際意義,我們需要的是自然連接和其他連接 SQL允許FROM子句中包含各種連接的表 例如，T1 NATURAL JOIN T2產(chǎn)生表T1和T2的自然連接，可以作為一個表引用出現(xiàn)在FROM子句中 許多商品化的DBMS并不能很好地支持這些功能 可以在WHERE子句中說明連接條件，并通過SELECT子句選取所需要的屬性來實現(xiàn)各種連接 在這種意義下，涉及多個表的查詢通常稱為連接查詢。,連接查詢(續(xù)),例3.19 查詢學(xué)號為2010006的學(xué)生的各科成績，對每門課程顯示課程名和成績。 SELECT Cname, Grade FROM SC, Courses WHERE SC.Cno=Courses.Cno AND Sno = 2010006; 其中SC.Cno=Courses.Cno是連接條件，相當(dāng)于求SC和Courses的自然連接 而Sno= 2010006是該查詢的選擇條件 注意：Cno既是表SC的屬性，也是Courses的屬性。為了避免二義性，我們必須在Cno前加前綴“SC.”或“Courses.” 其實，任何屬性前都可以加前綴。但是，當(dāng)A只是FROM子句中一個表的屬性時，前綴可以省略,連接查詢(續(xù)),例3.20 查詢選修2號課程，并且成績在89分以上的所有學(xué)生的學(xué)號、姓名和成績。 SELECT Students.Sno, Sname, Grade FROM Students, SC WHERE Students.Sno = SC.Sno AND Cno= 2 AND Grade89； 例3.21 查詢每個學(xué)生選修的每門課程的成績，要求列出的學(xué)號、姓名、課程名和成績。 SELECT Student.Sno, Sname, Cname, Grade FROM Students, SC, Courses WHERE Students.Sno = SC. Sno AND SC.Cno = Course. Cno;,連接查詢(續(xù)),例3.22 查詢每個學(xué)生的平均成績，并輸出平均成績大于85的學(xué)生學(xué)號、姓名和平均成績。 SELECT Student.Sno, Sname, AVG (Grade) FROM SC, Students WHERE Students.Sno = SC. Sno GROUP BY Students.Sno, Sname HAVING AVG (Grade)85; 注意：對于帶GROUP BY子句的查詢，SELECT子句中的結(jié)果列只能是分組屬性和聚集函數(shù)。由于查詢要求顯示學(xué)生的姓名，因此在該查詢中，我們按學(xué)生的學(xué)號和姓名分組。按學(xué)生的學(xué)號和姓名分組與按學(xué)號分組的效果是一樣的,連接查詢(續(xù)),自身連接是一個表和它自己進行連接，通常情況下使用的不多，對于一些特定的查詢，自身連接查詢非常有效。 例3.23 查詢和劉亦菲所在院系相同的學(xué)生的姓名。 SELECT S2.Sname FROM Students S1, Students S2 WHERE S1.Sdept=S2. Sdept AND S1.Sname=劉亦菲 AND S2.Sname劉亦菲; 表Students在FROM子句中出現(xiàn)兩次，分別對它們使用別名S1和S2，可以看作Students的兩個副本。,連接查詢(續(xù)),該查詢的執(zhí)行相當(dāng)于：對于S1的每個元組tS1，考察S2