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

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

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

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

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

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

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

7、示任何一個日期，日期數(shù)據(jù)格式為yyyy/mm/dd或yyyy-mm-dd。為了區(qū)別于數(shù)值型數(shù)據(jù)，字符char型數(shù)據(jù)和日期date型數(shù)據(jù)在書寫時都需要用單引號括起來。如20整數(shù)表示，-3.26為實數(shù)表示，學(xué)習(xí)成績就是一個字符串表示，2013/3/27就是一個日期數(shù)據(jù)表示。3 3列級列級完整性約束完整性約束在定義一個基本表中，在每個列的列名及數(shù)據(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。一個表中被注明主碼后，數(shù)據(jù)庫管理系統(tǒng)將按主碼值的升序自動建立一個對應(yīng)的索引，以后在顯示或處理表中的內(nèi)容時，將自動按照主碼的升序。如學(xué)生號列的定義為：“學(xué)生號 char(7) PRIMARY KEY”，這就定義了學(xué)生號列為字符型，主碼約束，學(xué)生號成為學(xué)生表中的主碼。 （4）單值約束。表示為：UNIQUEUNIQUE。注明該列上的所有取值

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

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

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

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

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

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

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

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

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

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