數(shù)據(jù)庫原理及運用第5講SQL.ppt

1,第5講 SQL語言,教學(xué)目標(biāo)：使學(xué)生掌握SQL結(jié)構(gòu)化查詢語言的使用方法 教學(xué)內(nèi)容：SQL的基本知識 建立刪除數(shù)據(jù)表和索引 教學(xué)重點： SQL的基本概念 數(shù)據(jù)庫的基本操作 教學(xué)難點：完整性約束 教學(xué)方法：講授法 舉例法 教學(xué)時間：2學(xué)時 教學(xué)過程：,2,5.1 SQL語言的基本概念與特點,5.1.1 SQL語言的發(fā)展 SQL語言是當(dāng)前最為成功、應(yīng)用最為廣泛的關(guān)系數(shù)據(jù)庫語言，其發(fā)展主要經(jīng)歷了以下幾個階段： 1974年由CHAMBERLIN和BOYEE提出，當(dāng)時稱為SEQUEL(STUCTURED ENGLISH QUERY LANGUAGE)； IBM公司對其進行了修改，并用于其SYSTEM R關(guān)系數(shù)據(jù)庫系統(tǒng)中； 1981年 IBM推出其商用關(guān)系關(guān)系數(shù)據(jù)庫SQL/DS，并將其名字改為SQL，由于SQL語言功能強大，簡潔易用，因此得到了廣泛的使用； 今天廣泛應(yīng)用于各種大型數(shù)據(jù)庫，如SYBASE、INFORMIX、 ORACLE、DB2、INGRES等，也用于各種小型數(shù)據(jù)庫，如FOXPRO、ACCESS。,3,SQL概述及特點,字面看SQL只是一個查詢語言，而實際上SQL作為一種標(biāo)準(zhǔn)數(shù)據(jù)庫語言，從對數(shù)據(jù)庫的隨機查詢到數(shù)據(jù)庫的管理和程序設(shè)計，SQL幾乎無所不能，功能十分豐富. SQL語言是一種關(guān)系數(shù)據(jù)庫語言，提供數(shù)據(jù)的定義、查詢、更新和控制等功能。 SQL語言不是一個應(yīng)用程序開發(fā)語言，只提供對數(shù)據(jù)庫的操作能力，不能完成屏幕控制、菜單管理、報表生成等功能，可成為應(yīng)用開發(fā)語言的一部分。 SQL語言不是一個DBMS，它屬于DBMS語言處理程序。 大部分DBMS產(chǎn)品都支持SQL，成為操作數(shù)據(jù)庫的標(biāo)準(zhǔn)語言,4,SQL具有交互式與嵌入式兩種形式 交互式SQL：一般DBMS都提供聯(lián)機交互工具，用戶可直接鍵入SQL命令對數(shù)據(jù)庫進行操作由DBMS來進行解釋 嵌入式SQL：能將SQL語句嵌入到高級語言（宿主語言），使應(yīng)用程序充分利用SQL訪問數(shù)據(jù)庫的能力、宿主語言的過程處理能力,一般需要預(yù)編譯，將嵌入的SQL語句轉(zhuǎn)化為宿主語言編譯器能處理的語句 SQL的語法結(jié)構(gòu)基本一致 SQL具有語言簡潔、易學(xué)易用的特點,5,SQL的特點,SQL支持三級模式結(jié)構(gòu) 一個SQL數(shù)據(jù)庫的總體邏輯結(jié)構(gòu)是基本表（Table）的集合，對應(yīng)于概念模式 SQL數(shù)據(jù)庫的底層存儲結(jié)構(gòu)采用文件，一個或幾個表對應(yīng)一個存儲文件,以及索引文件。對應(yīng)內(nèi)模式 用戶所見的數(shù)據(jù)結(jié)構(gòu)是視圖（View），用戶可直接操作的表，可為視圖或部分基本表。對應(yīng)外模式 注：支持sql語言的數(shù)據(jù)庫稱為sql數(shù)據(jù)庫,6,5.1.2 SQL語言的基本概念 首先介紹兩個基本概念：基本表和視圖。 基本表（BASE TABLE）：是獨立存在的表，不是由其它的表導(dǎo)出的表。一個關(guān)系對應(yīng)一個基本表，一個或多個基本表對應(yīng)一個存儲文件。 視圖（VIEW）：是一個虛擬的表，是從一個或幾個基本表導(dǎo)出的表。它本身不獨立存在于數(shù)據(jù)庫中，數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中。當(dāng)基本表中的數(shù)據(jù)發(fā)生變化時，從視圖中查詢出來的數(shù)據(jù)也隨之改變。,7,圖5.1 SQL語言支持的關(guān)系數(shù)據(jù)庫的三級邏輯結(jié)構(gòu),SQL語言支持?jǐn)?shù)據(jù)庫的三級模式結(jié)構(gòu)，如圖6.1所示。其中外模式對應(yīng)于視圖和部分基本表，模式對應(yīng)于基本表，內(nèi)模式對應(yīng)于存儲文件。,8,例如：學(xué)生數(shù)據(jù)庫中有學(xué)生基本情況表STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT)，此表為基本表，對應(yīng)一個存儲文件?？梢栽谄浠A(chǔ)上定義一個男生基本情況表STUDENT_MALE(SNO,SNAME,SAGE,SDEPT)， 它是從STUDENT中選擇SSEX=男的各個行，然后在SNO,SNAME,SAGE,SDEPT上投影得到的。 在數(shù)據(jù)庫中只存有STUDENT_MALE的定義，而STUDENT_MALE的記錄不重復(fù)存儲。 在用戶看來，視圖是通過不同路徑去看一個實際表，就象一個窗口一樣，透過視圖可以看到數(shù)據(jù)庫中自己感興趣的內(nèi)容。,9,SQL的特點,10,5.2 SQL數(shù)據(jù)定義,SQL語言使用數(shù)據(jù)定義語言（DATA DEFINITION LANGUAGE，簡稱DDL）實現(xiàn)其數(shù)據(jù)定義功能。,11,SQL語句格式的約定符號,語句格式中, 中的內(nèi)容是必須的，是用戶自定義語義; 為任選項 或分隔符|表示必選項,即必選其中之一項 ,N表示前面得項可以重復(fù)多次,12,5.2. 1 創(chuàng)建數(shù)據(jù)表 數(shù)據(jù)表是關(guān)系數(shù)據(jù)庫的基本組成單位，它物理地存儲于數(shù)據(jù)庫的存儲文件中。 CREATE TABLE （ 列級完整性約束條件 , 列級完整性約束條件 , n , n ） （1）是合法標(biāo)識符，最多可有128個字符，如S,SC,C，不允許重名。 （2）列名(字母開頭，可含字母、數(shù)字、$、_ =128字符) 同一表中不許有重名列； （2）數(shù)據(jù)類型：見后； （3）字段的長度、精度和小數(shù)位數(shù)；,13,例5.1 建立一學(xué)生表 Use student Create table s ( sno char(8) , sname varchar(20), sge int, sex char(2), dept varchar(20) ); 執(zhí)行該語句后，便產(chǎn)生了學(xué)生基本表的表框架，此表為一個空表。,14,字段數(shù)據(jù)類型,當(dāng)用SQL語句定義表時，需要為表中的每一個字段設(shè)置一個數(shù)據(jù)類型，用來指定字段所存放的數(shù)據(jù)是整數(shù)、字符串、貨幣或是其它類型的數(shù)據(jù)。 SQL Server 的數(shù)據(jù)類型有很多種，分為以下9類： 1. 整數(shù)數(shù)據(jù)類型：依整數(shù)數(shù)值的范圍大小，有bit, int , smallint, tinyint(微整型)四種。 2. 精確數(shù)值類型：用來定義可帶小數(shù)部分的數(shù)字，有numeric和decimal兩種。十進制數(shù)，共P位，其中小數(shù)點后S位。0=S=P，S=0時可省略。如：123.0、8000.56,15,3. 近似浮點數(shù)值數(shù)據(jù)類型：當(dāng)數(shù)值的位數(shù)太多時，可用此數(shù)據(jù)類型來取其近似值，用float和real兩種。如：1.23E+10 4. 日期時間數(shù)據(jù)類型：用來表示日期與時間，依時間范圍與精確程度可分為datetime與smalldatetime兩種。如：1998-06-08 15:30:00 5. 字符串?dāng)?shù)據(jù)類型：用來表示字符串的字段。包括：char, varchar, text三種，如：“數(shù)據(jù)庫” 6.標(biāo)記數(shù)據(jù)類型：有uniqueidentifier，timestamp兩種，此數(shù)據(jù)類型通常系統(tǒng)自動產(chǎn)生，而不是用戶輸入的， timestamp記錄數(shù)據(jù)更新的時間戳印，而 uniqueidentifier用來識別每一筆數(shù)據(jù)的唯一性。,16,各種數(shù)據(jù)類型的有關(guān)規(guī)定如下表：,17,18,19,字段的長度：指字段所能容納的最大數(shù)據(jù)量，但對不同的數(shù)據(jù)類型來說，長度對字段的意義可能有些不同。 對字符串?dāng)?shù)據(jù)類型而言，長度代表字段所能容納的字符的數(shù)目，因此它會限制用戶所能輸入的文本長度。 對數(shù)值類的數(shù)據(jù)類型而言，長度則代表字段使用多少個字節(jié)來存放數(shù)字。 精度和小數(shù)位數(shù) 精度是指數(shù)中數(shù)字的位數(shù)，包括小數(shù)點左側(cè)的整數(shù)部分和小數(shù)點右側(cè)的小數(shù)部分； 小數(shù)位數(shù)則是指數(shù)字小數(shù)點右側(cè)的位數(shù)。 例如：數(shù)字12345.678，其精度為8，小數(shù)位數(shù)為3； 所以只有數(shù)值類的數(shù)據(jù)類型才有必要指定精度和小數(shù)位數(shù)。,字段的長度、精度和小數(shù)位數(shù),20,經(jīng)常以如下所示的格式來表示數(shù)據(jù)類型以及它所采用的長度、精度和小數(shù)位數(shù)，其中的N代表長度，P代表精度，S表示小數(shù)位數(shù)。 BINARY(10) CHAR(20) NUMERIC(8,3) 但有的數(shù)據(jù)類型的精度與小數(shù)位數(shù)是固定的，對采用此類數(shù)據(jù)類型的字段而言，不需設(shè)置精度與小數(shù)位數(shù)， 如：如果某字段采用int數(shù)據(jù)類型，其長度固定是4，精度固定是10，小數(shù)位數(shù)則固定是0，這表示字段將能存放10位數(shù)沒有小數(shù)點的整數(shù)。存儲大小則是4個字節(jié)。,21,3. 定義完整性約束 上列為創(chuàng)建基本表的最簡單形式，還可以對表進一步定義，如主鍵、空值的設(shè)定，使數(shù)據(jù)庫用戶能夠根據(jù)應(yīng)用的需要對基本表的定義做出更為精確和詳盡的規(guī)定。 在SQL Server中，對于基本表的約束分為列約束和表約束。 列約束是對某一個特定列的約束，包含在列定義中，直接跟在該列的其他定義之后，用空格分隔，不必指定列名； 表約束與列定義相互獨立，不包括在列定義中，通常用于對多個列一起進行約束，定義表約束時必須指出要約束的那些列的名稱。完整性約束的基本語法格式為： constarint 約束名：約束不指定名稱時，系統(tǒng)會給定一個名稱。,22,約束類型：在定義完整性約束時必須指定的類型。 在SQL Server中可以定義五種類型的完整性約束. （1）NULL/NOT NULL 是否允許該字段的值為NULL。 NULL值不是0也不是空白，更不是填入字符串“NULL”，而是表示“不知道”、“不確定”或“沒有數(shù)據(jù)”的意思。 當(dāng)某一字段的值一定要輸入才有意義的時候，則可以設(shè)置為NOT NULL。 如主鍵列就不允許出現(xiàn)空值，否則就失去了唯一標(biāo)識一條記錄的作用 只能用于定義列約束， 其語法格式如下： constarint NULL|NOT NULL,23,例 建立一個S表，對SNO字段進行NOT NULL約束。 USE STUDENT CREATE TABLE S (SNO CHAR(10) NOT NULL， SN VARCHAR(20), AGE INT, SEX CHAR(2) DEFAULT 男 , DEPT VARCHAR(20); 當(dāng)SNO為空上時，系統(tǒng)給出錯誤信息，無NOT NULL約束時，系統(tǒng)缺省為NULL。,24,（2）UNIQUE約束 UNIQUE約束用于指明基本表在某一列或多個列的組合上的取值必須唯一。 定義了UNIQUE約束的那些列稱為唯一鍵，系統(tǒng)自動為唯一鍵建立唯一索引，從而保證了唯一鍵的唯一性。 唯一鍵允許為空，但系統(tǒng)為保證其唯一性，最多只可以出現(xiàn)一個NULL值。 UNIQUE既可用于列約束，也可用于表約束。 例 建立一個S表，定義SN為唯一鍵。 USE STUDENT CREATE TABLE S (SNO CHAR(6), SN CHAR(8) UNIQUE, SEX CHAR(2), AGE NUMERIC(2);,25,UNIQUE用于定義表約束時，其語法格式如下： CONSTRAINT UNIQUE（,） 例 建立一個S表，定義SN+SEX為唯一鍵。 USE STUDENT CREATE TABLE S ( SNO CHAR(5), SN CHAR(8), SEX CHAR(2), UNIQUE(SN,SEX); 系統(tǒng)為SN+SEX建立唯一索引，確保同一性別的學(xué)生沒有重名。,26,（3）PRIMARY KEY約束 PRIMARY KEY約束用于定義基本表的主鍵，起唯一標(biāo)識作用，其值不能為NULL，也不能重復(fù)，以此來保證實體的完整性。 PRIMARY KEY與UNIQUE約束類似，通過建立唯一索引來保證基本表在主鍵列取值的唯一性，但它們之間存在著很大的區(qū)別： 在一個基本表中只能定義一個PRIMARY KEY約束，但可定義多個UNIQUE約束； 對于指定為PRIMARY KEY的一個列或多個列的組合，其中任何一個列都不能出現(xiàn)空值，而對于UNIQUE所約束的唯一鍵，則允許為空。,27,注意：不能為同一個列或一組列既定義UNIQUE約束，又定義PRIMARY KEY約束。 PRIMARY KEY既可用于列約束，也可用于表約束。 例 建立一個S表，定義SNO為S的主鍵 USE STUDENT CREATE TABLE S (SNO CHAR(5) NOT NULL PRIMARY KEY, SN CHAR(8), AGE NUMERIC(2);,28,PRIMARY KEY用于定義表約束時，即將某些列的組合定義為主鍵，其語法格式如下： CONSTRAINT PRIMARY KEY () 例 建立一個SC表，定義SNO+CNO為SC的主鍵 USE STUDENT CREATE TABLE SC (SNO CHAR(5) NOT NULL, CNO CHAR(5) NOT NULL, SCORE NUMERIC(3), PRIMARY KEY(SNO,CNO);,29,（4）FOREIGN KEY約束 FOREIGN KEY約束指定某一個列或一組列作為外碼，其中，包含外碼的表稱為從表，包含外部鍵所引用的主鍵或唯一鍵的表稱主表。 系統(tǒng)保證從表在外碼上的取值要么是主表中某一個主碼值，要么取空值。以此保證兩個表之間的連接，確保了實體的參照完整性。 FOREIGN KEY既可用于列約束，也可用于表約束， 其語法格式為： CONSTRAINT FOREIGN KEY REFERENCES (),30,例 建立一個SC表，定義SNO,CNO為SC的外碼。 USE STUDENT CREATE TABLE SC (SNO CHAR(5) NOT NULL FOREIGN KEY REFERENCES S(SNO), CNO CHAR(5) NOT NULL FOREIGN KEY REFERENCES C(CNO), SCORE NUMERIC(3), PRIMARY KEY (SNO,CNO);,31,（5）CHECK約束 CHECK約束用來檢查字段值所允許的范圍，如，一個字段只能輸入整數(shù)，而且限定在0-100的整數(shù)，以此來保證域的完整性。 CHECK既可用于列約束，也可用于表約束， 其語法格式為： CONSTRAINT CHECK () 例 建立一個SC表，定義SCORE 的取值范圍為0到100之間。 USE STUDENT CREATE TABLE SC (SNO CHAR(5), CNO CHAR(5), SCORE NUMERIC(5,1) CHECK(SCORE=0 AND SCORE =100);,32,例 建立包含完整性定義的學(xué)生表 USE STUDENT CREATE TABLE S (SNO CHAR(6) PRIMARY KEY, SN CHAR(8) NOT NULL, AGE NUMERIC(2) NOT NULL CHECK (AGE BETWEEN 15 AND 50), SEX CHAR(2) DEFAULT 男, DEPT CHAR(10) NOT NULL);,33,5.2.3.2 修改基本表 由于應(yīng)用環(huán)境和應(yīng)用需求的變化，經(jīng)常需要修改基本表的結(jié)構(gòu)，比如，增加新列和完整性約束、修改原有的列定義和完整性約束等。 SQL語言使用ALTER TABLE命令來完成這一功能，有如下三種修改方式： 1. ADD方式 用于增加新列和完整性約束，定義方式同CREATE TABLE語句中的定義方式相同，其語法格式為： ALTER TABLE ADD | 例 在S表中增加一個班號列和住址列。 USE STUDENT ALTER TABLE S ADD CLASS_NO CHAR(6), ADDRESS CHAR(40),34,注意：使用此方式增加的新列自動填充NULL值，所以不能為增加的新列指定NOT NULL約束 。 例 在SC表中增加完整性約束定義，使SCORE在0-100之間。 USE STUDENT ALTER TABLE SC ADD CONSTRAINT SCORE_CHK CHECK(SCORE BETWEEN 0 AND 100),35,2. ALTER 方式 用于修改某些列，其語法格式為： ALTER TABLE ALTER COLUMN NULL|NOT NULL 例 把S表中的SNO列加寬到8位字符寬度 USE STUDENT ALTER TABLE S ALTER COLUMN SNO CHAR(8),36,注意：使用此方式有如下一些限制： 不能將含有空值的列的定義修改為NOT NULL約束； 若列中已有數(shù)據(jù)，則不能減少該列的寬度，也不能改變其數(shù)據(jù)類型； 只能修改NULL|NOT NULL約束，其它類型的約束在修改之前必須先刪除，然后再重新添加修改過的約束定義。,37,3.DROP方式 刪除完整性約束定義，其語法格式為： ALTER TABLE DROP CONSTRAINT 例 刪除S表中的AGE_CHK約束 USE STUDENT ALTER TABLE S DROP CONSTRAINT AGE_CHK,38,5.2.3.4 刪除基本表 當(dāng)某個基本表無用時，可將其刪除。 刪除后，該表中的數(shù)據(jù)和在此表上所建的索引都被刪除，而建立在該表上的視圖不會隨之刪除，系統(tǒng)將繼續(xù)保留其定義，但已無法使用。 如果重新恢復(fù)該表，這些視圖可重新使用。 刪除表的語法格式： DROP TABLE 例 刪除表STUDENT USE STUDENT DROP TABLE STUDENT 注意：只能刪除自己建立的表，不能刪除其他用戶所建的表。,39,5.2.5 設(shè)計、創(chuàng)建和維護索引 5.2.5.1 索引的作用 在日常生活中我們會經(jīng)常遇到索引，例如圖書目錄、詞典索引等。 借助索引，人們會很快地找到需要的東西。 索引是數(shù)據(jù)庫隨機檢索的常用手段，它實際上就是記錄的關(guān)鍵字與其相應(yīng)地址的對應(yīng)表。 例如，當(dāng)我們要在本書中查找有關(guān)“SQL查詢”的內(nèi)容時，應(yīng)該先通過目錄找到“SQL查詢”所對應(yīng)的頁碼，然后從該頁碼中找出所要的信息。這種方法比直接翻閱書的內(nèi)容要快。 如果把數(shù)據(jù)庫表比作一本書，則表的索引就如書的目錄一樣，通過索引可大大提高查詢速度。 此外，在SQL SERVER中，行的唯一性也是通過建立唯一索引來維護的。 索引的作用可歸納為： 1. 加快查詢速度； 2. 保證行的唯一性。,40,5.2.5.2 索引的分類 1. 按照索引記錄的存放位置可分為聚集索引與非聚集索引 聚集索引：按照索引的字段排列記錄，并且依照排好的順序?qū)⒂涗洿鎯υ诒碇小?非聚集索引：按照索引的字段排列記錄，但是排列的結(jié)果并不會存儲在表中，而是另外存儲。 2. 唯一索引的概念 唯一索引表示表中每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄， 這與表的PRIMARY KEY的特性類似，因此唯一性索引常用于PRIMARY KEY的字段上，以區(qū)別每一筆記錄。 當(dāng)表中有被設(shè)置為UNIQUE的字段時，SQL SERVER會自動建立一個非聚集的唯一性索引。 而當(dāng)表中有PRIMARY KEY的字段