第4章結(jié)構(gòu)化查詢語(yǔ)言SQL

1、第第4 4章章 結(jié)構(gòu)化查詢語(yǔ)言結(jié)構(gòu)化查詢語(yǔ)言-SQL-SQL一、一、SQLSQL簡(jiǎn)介和庫(kù)簡(jiǎn)介和庫(kù)操作操作1 1SQLSQL語(yǔ)言概述語(yǔ)言概述SQLSQL是是英文Structured Query Language的縮寫，中文含義是結(jié)構(gòu)化查詢語(yǔ)言。它是美國(guó)國(guó)家標(biāo)準(zhǔn)化組織ANSI批準(zhǔn)使用的一種關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言標(biāo)準(zhǔn)。如微軟公司推出的Access和SQL Server數(shù)據(jù)庫(kù)管理系統(tǒng)，其中所采用的SQL，都與標(biāo)準(zhǔn)SQL有所不同。SQL具有數(shù)據(jù)定義DDL、數(shù)據(jù)操縱DML和數(shù)據(jù)控制DCL等豐富功能。它定義有一組操作命令，用戶通過(guò)命令交互方式，或者程序執(zhí)行方式，使用它們來(lái)實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的相應(yīng)操作功能。在SQL中，外模

2、式又叫做視圖視圖(view)，全局模式簡(jiǎn)稱模式(schema)或數(shù)據(jù)庫(kù)(database)，內(nèi)模式通常不需要專門定義，可由DBMS自動(dòng)完成。在SQL語(yǔ)言中，每個(gè)關(guān)系又叫做基本表基本表或表表(table)，每個(gè)關(guān)系中的屬性又叫做字段字段(field)或列列(column)，元組又叫做行行(row)。每個(gè)視圖視圖也是一個(gè)關(guān)系，它由基本表產(chǎn)生出來(lái)，有自己獨(dú)立的結(jié)構(gòu)定義結(jié)構(gòu)定義，但沒(méi)有獨(dú)立的數(shù)據(jù)存在數(shù)據(jù)存在，它的數(shù)據(jù)來(lái)自基本表。把視圖稱為虛表虛表，把基本表稱為實(shí)表。SQL的數(shù)據(jù)操縱功能包括對(duì)基本表和視圖的數(shù)據(jù)查詢、插入、刪除和修改。SQL是一種高度非過(guò)程化的、面向集合操作的語(yǔ)言。2 2數(shù)據(jù)庫(kù)的建立數(shù)據(jù)

3、庫(kù)的建立建立數(shù)據(jù)庫(kù)的命令格式命令格式為： CREATE SCHEMA | DATABASE AUTHORIZATION 命令功能：命令功能：將在數(shù)據(jù)庫(kù)管理系統(tǒng)中建立一個(gè)名稱為所給的一個(gè)空數(shù)據(jù)庫(kù)，它的所有者，也就是創(chuàng)建者屬于所給的。命令格式說(shuō)明：命令格式說(shuō)明：大寫英文單詞是命令關(guān)鍵字；用尖括號(hào)括起來(lái)的語(yǔ)法成分是用戶定義的標(biāo)識(shí)符，它可以是由漢字、英文字母、數(shù)字組成的字符串，作為一個(gè)名稱使用；一對(duì)花括號(hào)中用一個(gè)或若干個(gè)豎線分開的每個(gè)語(yǔ)法成分，只能并且必須選用其一；中括號(hào)中的語(yǔ)法成分可以被選用，也可以被省略。使用英文字母時(shí)，默認(rèn)為大小寫是等效的，如大寫B(tài)A和 小寫ba相同。命令格式舉例：命令格式舉例：

4、 (1) create schema xuesh authorization xxk (2) create database 教學(xué)庫(kù)當(dāng)執(zhí)行了此create命令建立數(shù)據(jù)庫(kù)后，此數(shù)據(jù)庫(kù)將立即成為當(dāng)前數(shù)當(dāng)前數(shù)據(jù)庫(kù)據(jù)庫(kù)，若要改變另一個(gè)數(shù)據(jù)庫(kù)為當(dāng)前數(shù)據(jù)庫(kù)，則可以使用“USE ”命令；如使用“USE xuesh”命令后，xuesh數(shù)據(jù)庫(kù)就成為了當(dāng)前數(shù)據(jù)庫(kù)。3 3數(shù)據(jù)庫(kù)的刪除數(shù)據(jù)庫(kù)的刪除刪除數(shù)據(jù)庫(kù)的命令格式如下： DROP SCHEMA | DATABASE 命令功能：命令功能：刪除掉由命令中所指定的一個(gè)數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)。當(dāng)然會(huì)同時(shí)把該庫(kù)中已經(jīng)存在的所有內(nèi)容和信息一并刪除掉。 命令格式舉例：命令格式舉例： d

5、rop database xuesh 該命令把剛建立的名稱為xuesh的空數(shù)據(jù)庫(kù)從數(shù)據(jù)庫(kù)管理系統(tǒng)中刪除掉。二、表結(jié)構(gòu)二、表結(jié)構(gòu)操作操作1 1建立表結(jié)構(gòu)命令的定義建立表結(jié)構(gòu)命令的定義 命令定義格式如下：命令定義格式如下： CREATE TABLE . (,.,.) 命令功能：命令功能：在當(dāng)前或給定的數(shù)據(jù)庫(kù)中定義一個(gè)基本表的結(jié)構(gòu)。2 2列的數(shù)據(jù)類型列的數(shù)據(jù)類型在表結(jié)構(gòu)的定義體內(nèi)，對(duì)每個(gè)字段（列）的定義，都需要給出字段名稱、字段的數(shù)據(jù)類型、字段的完整性約束等信息?？砂裇QL語(yǔ)言中的數(shù)據(jù)類型主要分為以下四種： char(n) 為定長(zhǎng)字符型，或者稱為字符串型，其長(zhǎng)度定義為n，能夠用來(lái)保存具有n個(gè)字符的字

6、符串，如可以將姓名定義為定長(zhǎng)字符型，姓名的數(shù)據(jù)類型被定義為char(6)?，F(xiàn)在所有字符都采用unicode國(guó)際標(biāo)準(zhǔn)編碼，每個(gè)字符（無(wú)論是西文字符或漢字）都統(tǒng)一采用兩個(gè)字節(jié)編碼。 int為整數(shù)型，簡(jiǎn)稱整型。該類型占用4個(gè)字節(jié)，能夠用來(lái)表示-2147483648到+2147483647之間的所有整數(shù)，如可以將年齡、工齡等字段定義為整型。 float為浮點(diǎn)型，又稱實(shí)數(shù)型。該類型占4個(gè)或8個(gè)字節(jié)，能夠表示相當(dāng)大范圍內(nèi)的任何浮點(diǎn)數(shù)或?qū)崝?shù)，包括該范圍內(nèi)的所有整數(shù)和小數(shù)。如職工工資、產(chǎn)品重量等字段都可以采用float類型。 date或datetime為日期型，表示日期和時(shí)間。該類型占用4個(gè)或8個(gè)字節(jié)，能夠表

7、示任何一個(gè)日期，日期數(shù)據(jù)格式為yyyy/mm/dd或yyyy-mm-dd。為了區(qū)別于數(shù)值型數(shù)據(jù)，字符char型數(shù)據(jù)和日期date型數(shù)據(jù)在書寫時(shí)都需要用單引號(hào)括起來(lái)。如20整數(shù)表示，-3.26為實(shí)數(shù)表示，學(xué)習(xí)成績(jī)就是一個(gè)字符串表示，2013/3/27就是一個(gè)日期數(shù)據(jù)表示。3 3列級(jí)列級(jí)完整性約束完整性約束在定義一個(gè)基本表中，在每個(gè)列的列名及數(shù)據(jù)類型定義的后面，還有時(shí)需要給出該列的完整性約束的定義，稱此為列級(jí)完整性約束。列級(jí)完整性約束被分為以下6種情況。 （1）默認(rèn)值約束。表示為：DEFAULT DEFAULT 。如DEFAULT 0 可作為職工工資列的默認(rèn)值，DEFAULT 18 可作為職工年齡

8、列的默認(rèn)值。 （2）空值/非空值約束。表示為：NULL/NOT NULL/NOT NULLNULL。如姓名列的定義為：“姓名 char(6) NOT NULL”，就定義了姓名列為字符型，非空約束。3）主碼約束。表示為：PRIMARY KEYPRIMARY KEY。一個(gè)表中被注明主碼后，數(shù)據(jù)庫(kù)管理系統(tǒng)將按主碼值的升序自動(dòng)建立一個(gè)對(duì)應(yīng)的索引，以后在顯示或處理表中的內(nèi)容時(shí)，將自動(dòng)按照主碼的升序。如學(xué)生號(hào)列的定義為：“學(xué)生號(hào) char(7) PRIMARY KEY”，這就定義了學(xué)生號(hào)列為字符型，主碼約束，學(xué)生號(hào)成為學(xué)生表中的主碼。 （4）單值約束。表示為：UNIQUEUNIQUE。注明該列上的所有取值

9、必須互不相同。如身份證號(hào)列可定義為：“身份證號(hào) char(18) UNIQUE”。 （5）外碼約束。表示為：REFERENCES REFERENCES ()。注明該列為外碼，并給出對(duì)應(yīng)的父表及父表中被參照的主碼。學(xué)生號(hào)列可定義為：“學(xué)生號(hào) char(7) REFERENCES 學(xué)生表(學(xué)生號(hào))”。 （6）檢查約束。表示為：CHECK(CHECK()。注明該列的取值條件，或稱取值限制。如性別列的定義可以為：“性別 char(2) not null check(性別=男 OR 性別=女)”。4 4表級(jí)表級(jí)完整性約束完整性約束列級(jí)完整性約束的定義只針對(duì)所在的列，而表級(jí)完整性約束的定義可以針對(duì)該表定義

10、中的任何一個(gè)列或多個(gè)列，當(dāng)需要涉及到多個(gè)列的完整性約束的定義時(shí)，則必須使用表級(jí)完整性約束的定義。 的定義被分為以下4種情況。 （1）主碼約束。表示為：PRIMARY KEY(PRIMARY KEY(,.),.)。注明一個(gè)或同時(shí)多個(gè)列為該表中的主碼。如在選課表的定義中，在所有列的定義之后，可以使用表級(jí)主碼約束的定義：primary key(學(xué)生號(hào),課程號(hào))。 （2）單值約束。表示為：UNIQUE(UNIQUE(,.),.)。注明一個(gè)或同時(shí)若干個(gè)列為單值。如unique(長(zhǎng)途區(qū)號(hào)，電話號(hào)碼)。3）外碼約束。表示為：FOREIGN KEY(FOREIGN KEY(,.) ,.) REFERENCES

11、REFERENCES(,.),.)。它注明一個(gè)或同時(shí)多個(gè)列為外碼，并給出對(duì)應(yīng)的父表及父表中被參照的主碼。如在一個(gè)選課表的定義中，使用的表級(jí)完整性外碼約束的定義為：foreign key (學(xué)生號(hào)) references 學(xué)生(學(xué)生號(hào))。 （4）檢查約束。表示為：CHECK(CHECK()。注明表中一個(gè)或一些列上的取值必須滿足的約束條件。如假定在職工表中采用的表級(jí)完整性檢查約束的定義為：check(工齡年齡)。表級(jí)完整性約束共有4種，列級(jí)完整性約束共有6種。在表級(jí)完整性約束中缺少列級(jí)的默認(rèn)值約束和空值/非空值約束，其他4個(gè)約束是共有的，包括主碼約束、外碼約束、單值約束和檢查約束。5 5基本表定義

12、格式基本表定義格式舉例舉例（1 1）定義學(xué)生表）定義學(xué)生表 create table 學(xué)生 ( 學(xué)生號(hào) char(7) primary key, 姓名 char(6) not null unique, 性別 char(2) not null check(性別=男 or 性別=女), 出生日期 datetime not null check(出生日期=1 and 年級(jí)=2 and 課程學(xué)分=0 and 成績(jī)=100), primary key(學(xué)生號(hào),課程號(hào)), foreign key(學(xué)生號(hào)) references 學(xué)生(學(xué)生號(hào)), foreign key(課程號(hào)) references 課程

13、(課程號(hào)) )6 6修改和刪除表結(jié)構(gòu)修改和刪除表結(jié)構(gòu)建立表結(jié)構(gòu)使用的是CREATE TABLE命令。當(dāng)建立一個(gè)表之后，就可以向其輸入數(shù)據(jù)。SQL語(yǔ)言提供了修改表結(jié)構(gòu)的命令，這就是教材中給出的ALTER TABLE命令。SQL語(yǔ)言也提供了刪除表結(jié)構(gòu)的命令，這就是教材中給出的DROP TABLE命令。三、表、視圖和索引三、表、視圖和索引操作操作1.1.向表中插入記錄的語(yǔ)句向表中插入記錄的語(yǔ)句向表中插入記錄有兩種語(yǔ)句格式，一種是單行插入語(yǔ)句格式，另一種是多行插入語(yǔ)句格式，它們的語(yǔ)句關(guān)鍵字都是INSERT。 單行插入語(yǔ)句格式如下：?jiǎn)涡胁迦胝Z(yǔ)句格式如下： INSERT INTO . (,.) VALUE

14、S(,.) 此單行插入語(yǔ)句的功能就是向一個(gè)給定的表中插入一行記錄數(shù)據(jù)。例如：例如：在當(dāng)前數(shù)據(jù)庫(kù)中進(jìn)行了如下一個(gè)職工表的定義： create table 職工 ( 職工號(hào) char(6) primary key, 姓名 char(8) not null, 性別 char(2) not null, 年齡 int, 基本工資 float );針對(duì)這個(gè)職工表，使用下面一條插入語(yǔ)句： insert into 職工(職工號(hào),姓名,性別,年齡,基本工資) values(010405,李羽,女,28,3560); 職工號(hào) 姓名 性別 年齡 基本工資 010405 李羽 女 28 3560 多行插入語(yǔ)句格式如下

15、：多行插入語(yǔ)句格式如下： INSERT INTO . (,.) 例如：例如：假定在當(dāng)前數(shù)據(jù)庫(kù)中已建立了一個(gè)職工1表，它的當(dāng)前內(nèi)容如下： 職工號(hào) 姓名 性別 年齡 職務(wù) 基本工資 職務(wù)津貼 010203 李英 女 32 副處 3750 2450 010408 劉秀 男 25 科員 3250 1550 010506 王強(qiáng) 男 52 處長(zhǎng) 4400 2800 010526 趙紅 女 38 科長(zhǎng) 3600 2000 010715 朱通 男 46 科長(zhǎng) 3700 2000 假定要執(zhí)行下面的插入語(yǔ)句： insert 職工(職工號(hào),姓名,性別,年齡,基本工資) select 職工號(hào),姓名,性別,年齡,基本工

16、資 from 職工1 where 性別=男 職工號(hào) 姓名 性別 年齡 基本工資 010405 李羽 女 28 3560 010408 劉秀 男 25 3250 010506 王強(qiáng) 男 52 4400 010715 朱通 男 46 37002 2修改表中記錄的修改表中記錄的語(yǔ)句語(yǔ)句 修改表中記錄的語(yǔ)句為UPDATE語(yǔ)句，具體語(yǔ)句格式如下： UPDATE . SET=,. FROM,. WHERE 例如：有如下一條修改記錄語(yǔ)句：例如：有如下一條修改記錄語(yǔ)句： update 職工 set 年齡=年齡+1 該語(yǔ)句把職工表中所有職工的年齡都增加1，執(zhí)行后職工表中的記錄如下： 職工號(hào) 姓名 性別 年齡 基

17、本工資 010405 李羽 女 29 3560 010408 劉秀 男 26 3250 010506 王強(qiáng) 男 53 4400 010715 朱通 男 47 3700又例：有如下的又例：有如下的update修改記錄的語(yǔ)句： update 職工 set 職工.基本工資=職工1.基本工資+職工1.職務(wù)津貼 from 職工1 where 職工.職工號(hào)=職工1.職工號(hào)為了在語(yǔ)句中區(qū)分所使用的列名屬于哪個(gè)表，需要在列名前加上表名和圓點(diǎn)分隔符，如職工.職工號(hào)，職工1.職工號(hào)，職工.基本工資，職工1.基本工資。 職工號(hào) 姓名 性別 年齡 基本工資 010405 李羽 女 29 3560 010408 劉秀

18、男 26 4800 010506 王強(qiáng) 男 53 7200 010715 朱通 男 47 57003 3刪除記錄語(yǔ)句刪除記錄語(yǔ)句 刪除表中記錄的語(yǔ)句為DELETE語(yǔ)句，具體語(yǔ)句格式如下： DELETE FROM . FROM,. WHERE4.4.視圖的建立、修改和視圖的建立、修改和刪除刪除（1 1）視圖概念）視圖概念視圖視圖(view)是在基本表之上建立的表，它的結(jié)構(gòu)，即所有列定義取自基本表，它的內(nèi)容，即所有數(shù)據(jù)行也取自基本表?；颈碓跀?shù)據(jù)庫(kù)存儲(chǔ)空間中有對(duì)應(yīng)的存儲(chǔ)文件，而視圖則沒(méi)有對(duì)應(yīng)的存儲(chǔ)文件，它只與基本表相對(duì)應(yīng)。（2 2）建立視圖語(yǔ)句）建立視圖語(yǔ)句建立視圖的語(yǔ)句格式如下： CREATE VIEW (,.) AS 假定在職工庫(kù)中已經(jīng)建立有如下結(jié)構(gòu)和內(nèi)容的職工表。 職工號(hào) 姓名 性別 年齡 基本工資 010405 李羽 女 29 3