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

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