計(jì)算機(jī)基礎(chǔ)vfp第7章結(jié)構(gòu)化查詢(xún)語(yǔ)言sql

1、第七章 結(jié)構(gòu)化查詢(xún)語(yǔ)言SQLSQL是結(jié)構(gòu)化查詢(xún)語(yǔ)言（Structured Query Language）的縮寫(xiě)，按照ANSI（美國(guó)國(guó)家標(biāo)準(zhǔn)協(xié)會(huì)）的規(guī)定，SQL被確定為關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)的標(biāo)準(zhǔn)語(yǔ)言。SQL語(yǔ)言簡(jiǎn)潔明了、方便實(shí)用、功能齊全，使它倍受廣大用戶(hù)的歡迎。目前，絕大多數(shù)流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，如：Sybase，SQL Server，Oracle，F(xiàn)oxPro，Access等都采用了SQL語(yǔ)言標(biāo)準(zhǔn)。 SQL語(yǔ)言功能包括數(shù)據(jù)查詢(xún)、數(shù)據(jù)操縱、數(shù)據(jù)定義和數(shù)據(jù)控制四個(gè)方面，總共有個(gè)命令，如表7-1所示。 SQL功能命 令數(shù)據(jù)定義語(yǔ)言CREATE、ALTER、 DROP數(shù)據(jù)查詢(xún)語(yǔ)言SELECT數(shù)

2、據(jù)操縱語(yǔ)言INSERT、UPDATE、DELETE數(shù)據(jù)控制語(yǔ)言GRANT、REVOKE其中：數(shù)據(jù)定義語(yǔ)言用于定義數(shù)據(jù)表的結(jié)構(gòu)，如創(chuàng)建、修改或刪除數(shù)據(jù)表；數(shù)據(jù)操縱語(yǔ)言用于修改數(shù)據(jù)，如插入、修改、刪除數(shù)據(jù)；數(shù)據(jù)查詢(xún)語(yǔ)言用于查詢(xún)數(shù)據(jù)；數(shù)據(jù)控制語(yǔ)言用于控制用戶(hù)對(duì)數(shù)據(jù)表的訪問(wèn)權(quán)限等； 表7-1 SQL的基本功能和命令1 SQL的數(shù)據(jù)定義功能 SQL語(yǔ)言使用數(shù)據(jù)定義語(yǔ)言（Date Definition Language，簡(jiǎn)稱(chēng)DDL）實(shí)現(xiàn)其數(shù)據(jù)定義功能，可對(duì)數(shù)據(jù)庫(kù)用戶(hù)、基本表、視圖、索引進(jìn)行定義和撤消。本章介紹與數(shù)據(jù)表操作有關(guān)的命令，具體命令見(jiàn)表7-2。 命令 功 能CREATE TABLE創(chuàng)建一個(gè)新的數(shù)據(jù)

3、表（自由表）ALTER TABLE添加、修改和刪除字段，DROP TABLE刪除一個(gè)數(shù)據(jù)表（自由表）表7-2 DDL的命令及功能一、 創(chuàng)建數(shù)據(jù)表CREATE TABLE命令主要用于創(chuàng)建一個(gè)新的數(shù)據(jù)表。如果在創(chuàng)建數(shù)據(jù)表時(shí)已經(jīng)打開(kāi)了一個(gè)數(shù)據(jù)庫(kù)，則所創(chuàng)建的數(shù)據(jù)表將自動(dòng)添加到該數(shù)據(jù)庫(kù)中，否則將生成自由表?！久睢?CREATE TABLE ( ( ,小數(shù)位), )【功能】 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu)?！菊f(shuō)明】表約束選項(xiàng)（數(shù)據(jù)庫(kù)表）： NULL/NOT NULL 表示是否允許字段值為空值。 CHECK 用于指定字段的有效性規(guī)則。ERROR 指定當(dāng)輸入的字段值違反有效性規(guī)則時(shí)，顯示提示信息。 DEFAULT 用于設(shè)置

4、字段的默認(rèn)值。 PRIMARY KEY 用于設(shè)置字段為主索引，一個(gè)表中只能有一個(gè)主索引，UNIQUE 用于設(shè)置候選索引。【例7-1】 創(chuàng)建學(xué)生基本情況數(shù)據(jù)表。CREATE TABLE 學(xué)生(學(xué)號(hào) C(8) PRIMARY KEY NOT NULL,姓名 C(8) NOT NULL,性別 C(2) DEFAULT 男 CHECK 性別=“男” OR 性別=“女” ERROR “性別必須是男或女”,出生年月 D CHECK（出生年月=1970-01-01)二、修改數(shù)據(jù)表SQL語(yǔ)言使用ALTER TABLE命令來(lái)修改基本表的結(jié)構(gòu)，不僅可以增加、刪除和修改字段，還可以增加、刪除和修改如主索引、侯選索引

5、、有效性規(guī)則等結(jié)構(gòu)。1. 添加字段【命令】 ALTER TABLE ADD ( ,小數(shù)位)【功能】增加新列。2. 修改字段1.修改字段類(lèi)型和約束【命令】 ALTER TABLE ALTER ( ,小數(shù)位)【功能】修改列數(shù)據(jù)類(lèi)型和約束。2.修改字段名【命令】 ALTER TABLE RENAME TO 【功能】修改列名。3. 刪除字段【命令】 ALTER TABLE DROP 【功能】刪除列?！纠?-2】 修改基本情況數(shù)據(jù)表。&重命名“出生年月” 為“出生日期”ALTER TABLE 學(xué)生 RENAME 出生年月 TO 出生日期&刪除“出生日期”ALTER TABLE 學(xué)生 DROP 出生日期三

6、、刪除數(shù)據(jù)表當(dāng)某個(gè)數(shù)據(jù)表無(wú)用時(shí)，可將其刪除。刪除后，該表中的數(shù)據(jù)和在此表上所建的索引都被刪除。【命令】 DROP TABLE 【功能】 刪除數(shù)據(jù)表。 【命令】 REMOVE TABLE 【功能】 移除數(shù)據(jù)表?！纠?-3】 刪除表“學(xué)生”。 DROP TABLE 學(xué)生 2 SQL的數(shù)據(jù)查詢(xún)功能 SELECT命令的格式【命令】SELECT ALL/DISTINCTTOP PERCENT.AS ,AS FROM ,INNER/LEFT/RIGHT/FULL JOIN ON INTO /TO FILE /TO PRINTER/TO SCREENWHERE AND AND/ORAND/ORGROUP B

7、Y ,HAVING ORDER BY ASC/DESC,ASC/DESC;1. 投影查詢(xún)【例7-4】 查詢(xún)學(xué)生的基本情況。SELECT 學(xué)號(hào), 姓名, 性別, 出生年月 FROM 學(xué)生注：在SELECT語(yǔ)句中可以重新指定列的順序。查詢(xún)學(xué)生的所有信息SELECT * FROM 學(xué)生注：用“*”表示數(shù)據(jù)表的全部列名，而不必逐一列出。 【例7-5】 查詢(xún)?nèi)胄？偡肿畹偷奈迕麑W(xué)生基本情況，按入校總分由低到高進(jìn)行排序，同時(shí)指定表中的字段在查詢(xún)結(jié)果中的列標(biāo)題。SELECT TOP 5 學(xué)號(hào) AS 學(xué)生編號(hào), 姓名AS 學(xué)生姓名,性別 AS 學(xué)生性別, 入?？偡?AS 高考分?jǐn)?shù) FROM 學(xué)生 ORDER B

8、Y 入?？偡?注：使用TOP子句時(shí)，同時(shí)要使用ORDER BY 子句。2. 條件查詢(xún)當(dāng)要在數(shù)據(jù)表中找出滿(mǎn)足某些條件的行時(shí)，則需使用WHERE子句指定查詢(xún)條件。常用的比較運(yùn)算符如表7-3所示。 運(yùn) 算 符含 義=，=，=，!=，比較大小AND，OR，NOT多重條件BETWEEN AND確定范圍IN確定集合LINK字符匹配表7-3 常用比較運(yùn)算符1. 比較大小【例7-6】 查詢(xún)選修課程號(hào)為“C140”的學(xué)生的學(xué)號(hào)和成績(jī)。SELECT 學(xué)號(hào),成績(jī) FROM 選課 WHERE 課程號(hào)=“c140”【例7-7】 查詢(xún)成績(jī)高于90分的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)。SELECT 學(xué)號(hào),課程號(hào),成績(jī) FROM 選

9、課 WHERE 成績(jī)=90【例7-8】 查詢(xún)選修課程號(hào)為“C120”或“C140”且分?jǐn)?shù)大于等于80分學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)。SELECT 學(xué)號(hào),課程號(hào),成績(jī) FROM 選課 WHERE (課程號(hào)=“c120” OR 課程號(hào)=“c140”) AND 成績(jī)=802. 多重條件查詢(xún)當(dāng)WHERE子句需要指定一個(gè)以上的查詢(xún)條件時(shí)，則需要使用邏輯運(yùn)算符AND，OR和NOT將其連接成復(fù)合的邏輯表達(dá)式。其優(yōu)先級(jí)由高到低為：NOT，AND，OR，用戶(hù)可以使用括號(hào)改變優(yōu)先級(jí)。3. 確定范圍【例7-9】 查詢(xún)工資在1500至2000之間的教師的教師號(hào)、姓名及職稱(chēng)。SELECT 教師號(hào),姓名,職稱(chēng) FROM 教師

10、WHERE 工資 BETWEEN 1500 AND 2000*或者*SELECT 教師號(hào),姓名,職稱(chēng) FROM 教師WHERE 工資=1500 AND 工資=80*或者*SELECT 學(xué)號(hào),課程,成績(jī) FROM 選課WHERE（課程號(hào)=“c140” OR 課程號(hào)= “c160”）AND 成績(jī)=803. 統(tǒng)計(jì)查詢(xún)?cè)诤芏鄳?yīng)用中，并不是只要求將表中的記錄原樣取出就行了，而是要在原有數(shù)據(jù)的基礎(chǔ)上通過(guò)計(jì)算，輸出統(tǒng)計(jì)結(jié)果。SQL提供了許多庫(kù)函數(shù)，增強(qiáng)了檢索的功能，其主要函數(shù)如表7-4所示。說(shuō)明：在以上函數(shù)中，可以使用DISTINCT短語(yǔ)或ALL短語(yǔ)，如果指定了DISTINCT短語(yǔ)，則表示在計(jì)算時(shí)取消指定列

11、中的重復(fù)值，如果不指定DISTINCT短語(yǔ)或ALL短語(yǔ)，則取默認(rèn)值A(chǔ)LL，表示不取消重復(fù)值。表7-4常用庫(kù)函數(shù)及其功能函 數(shù) 名 稱(chēng)功 能AVG按列計(jì)算平均值SUM按列計(jì)算值的總和COUNT按列值統(tǒng)計(jì)個(gè)數(shù)MAX求一列中的最大值MIN求一列中的最小值【例7-11】求學(xué)號(hào)為“s0201109”學(xué)生的總分和平均分（顯示學(xué)號(hào)）。SELECT 學(xué)號(hào)，SUM(成績(jī)) AS 總分，AVG(成績(jī)) AS 平均分FROM 選課WHERE (學(xué)號(hào) = s0201109) 注意：函數(shù)SUM和AVG只能對(duì)數(shù)值型字段進(jìn)行計(jì)算?！纠?-12】 求選修課程號(hào)為“C140”學(xué)生的最高分、最低分及之間相差的分?jǐn)?shù)（顯示課程號(hào)）。

12、SELECT 課程號(hào), MAX(成績(jī)) AS 最高分, MIN(成績(jī)) AS 最低分, MAX(成績(jī)) - MIN(成績(jī)) AS 相差分?jǐn)?shù)FROM 選課WHERE 課程號(hào) = “c140”【例7-13】 利用特殊函數(shù)COUNT(*)求教師表中“教授”和“副教授”的人數(shù)。SELECT COUNT(*) AS 教授和副教授的人數(shù) FROM 教師WHERE 職稱(chēng) IN (“教授”, “副教授”)注意： COUNT（*）用來(lái)統(tǒng)計(jì)記錄的個(gè)數(shù)。 4. 分組查詢(xún)GROUP BY子句可以將查詢(xún)結(jié)果按屬性列或?qū)傩粤薪M合在行的方向上進(jìn)行分組，每組在屬性列或?qū)傩粤薪M合上具有相同的值。【例7-14】 查詢(xún)各位教師的教

13、師號(hào)及其任課的門(mén)數(shù)。SELECT 教師號(hào), COUNT(*) AS 任課門(mén)數(shù) FROM 授課GROUP BY 教師號(hào)GROUP BY子句按教師號(hào)的值分組，所有具有相同教師號(hào)的記錄為一組，對(duì)每一組使用函數(shù)COUNT進(jìn)行計(jì)算，統(tǒng)計(jì)出各位教師任課的門(mén)數(shù)。若在分組后還要按照一定的條件進(jìn)行篩選，則需使用HAVING子句?！纠?-15】 查詢(xún)選修兩門(mén)以上課程的學(xué)生學(xué)號(hào)和選課門(mén)數(shù)。SELECT 學(xué)號(hào) AS 選課兩門(mén)以上學(xué)生的學(xué)號(hào),COUNT(*) AS 選課門(mén)數(shù)FROM 選課GROUP BY 學(xué)號(hào)HAVING COUNT(*)=2GROUP BY子句按學(xué)號(hào)的值分組，所有具有相同學(xué)號(hào)的記錄為一組，對(duì)每一組使用

14、函數(shù)COUNT進(jìn)行計(jì)算，統(tǒng)計(jì)出每位學(xué)生選課的門(mén)數(shù)。HAVING子句去掉不滿(mǎn)足選課門(mén)數(shù)在兩門(mén)以上的組。當(dāng)在一個(gè)SQL查詢(xún)中同時(shí)使用WHERE子句、GROUP BY子句和HAVING子句時(shí)，其順序是WHERE，GROUP BY，HAVING。WHERE與HAVING子句的根本區(qū)別在于作用對(duì)象不同：WHERE子句作用于基本表或視圖，從中選擇滿(mǎn)足條件的元組；HAVING子句作用于組，選擇滿(mǎn)足條件的組，必須用于GROUP BY子句之后，但GROUP BY子句可以沒(méi)有HAVING子句。 5. 查詢(xún)的排序當(dāng)需要對(duì)查詢(xún)結(jié)果排序時(shí)，可以使用ORDER BY子句對(duì)查詢(xún)的結(jié)果按照一個(gè)或多個(gè)屬性列的升序（ASC）或降

15、序（DESC）排列，默認(rèn)值為升序。ORDER BY子句必須出現(xiàn)在其他子句之后?！纠?-16】 查詢(xún)選修了課程“C140” 的學(xué)生學(xué)號(hào)和成績(jī)，并按成績(jī)降序排列。SELECT 學(xué)號(hào), 成績(jī) FROM 選課WHERE 課程號(hào)=“c140”O(jiān)RDER BY 成績(jī) DESC6. 連接查詢(xún)連接查詢(xún)是關(guān)系數(shù)據(jù)庫(kù)中最主要的查詢(xún)，包括等值與非等值連接查詢(xún)、自身連接查詢(xún)、外連接查詢(xún)等。連接操作的目的就是通過(guò)加在連接字段上的條件將多個(gè)表連接起來(lái)，以便從多個(gè)表中查詢(xún)數(shù)據(jù)。表的連接方法有兩種：方法1：表之間滿(mǎn)足一定條件的行進(jìn)行連接，此時(shí)FROM子句中指明進(jìn)行連接的表名，WHERE子句指明連接的列名及其連接條件?！纠?-

16、17】 查詢(xún)陳靜老師所講授的課程號(hào)。SELECT 教師.教師號(hào), 姓名, 課程號(hào)FROM 教師, 授課WHERE 教師.教師號(hào)=授課.教師號(hào) AND 姓名=“陳靜”注：姓名=“陳靜”為查詢(xún)條件，而教師.教師號(hào)=授課.教師號(hào)為連接條件，教師號(hào)為連接字段。方法2：利用關(guān)鍵字JOIN進(jìn)行連接。具體分為以下幾種：INNER JOIN ：顯示符合條件的記錄，此為默認(rèn)值；LEFT JOIN：顯示符合條件的數(shù)據(jù)行以及左邊表中不符合條件的數(shù)據(jù)行，此時(shí)右邊數(shù)據(jù)行會(huì)以NULL來(lái)顯示，此稱(chēng)為左連接；RIGHT JOIN：顯示符合條件的數(shù)據(jù)行以及右邊表中不符合條件的數(shù)據(jù)行，此時(shí)左邊數(shù)據(jù)行會(huì)以NULL來(lái)顯示，此稱(chēng)為右連

17、接；FULL JOIN：顯示符合條件的數(shù)據(jù)行以及左邊表和右邊表中不符合條件的數(shù)據(jù)行，此時(shí)缺乏數(shù)據(jù)的數(shù)據(jù)行會(huì)以NULL來(lái)顯示；當(dāng)將JOIN 關(guān)鍵詞放于FROM子句中時(shí)，應(yīng)有關(guān)鍵詞ON與之相對(duì)應(yīng)，以表明連接的條件。 【例7-18】 查詢(xún)陳靜老師所講授的課程號(hào)。SELECT 教師.教師號(hào), 姓名, 課程號(hào) FROM 教師 INNER JOIN 授課ON 教師.教師號(hào)=授課.教師號(hào)WHERE 姓名=“陳靜”本方法采用的是內(nèi)部連接。 SQL語(yǔ)言的數(shù)據(jù)操縱也稱(chēng)為數(shù)據(jù)更新，主要包括插入數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)三種語(yǔ)句。一、插入數(shù)據(jù)記錄插入數(shù)據(jù)是把新的記錄插入到一個(gè)存在的表中。插入數(shù)據(jù)使用語(yǔ)句INSERT I

18、NTO，可插入一條記錄，也可插入多條記錄。1. 插入一行新記錄【命令】INSERT INTO (,)VALUES(,)【功能】將新記錄插入到指定的表中，分別用表達(dá)式1、表達(dá)式2等為字段名1、字段名2等賦值?！菊f(shuō)明】列名的排列順序不一定要和表定義時(shí)的順序一致。但當(dāng)指定字段名時(shí)，VALUES子句表達(dá)式的排列順序必須和字段名的排列順序一致，個(gè)數(shù)相等，數(shù)據(jù)類(lèi)型一一對(duì)應(yīng)。3 SQL的數(shù)據(jù)更新功能 【例7-19】 在學(xué)生表中插入一條學(xué)生記錄。INSERT INTO 學(xué)生 (學(xué)號(hào),姓名,性別,出生年月,入?？偡?三好生)VALUES (“s0201111”,“徐暢”,“女”,1984-06-25,588,.T.)注意：各列名和數(shù)據(jù)必須用逗號(hào)分開(kāi)，表達(dá)式的值必須是常量。二、修改數(shù)據(jù)記錄S