access-第3章-數(shù)據(jù)查詢與SQL命令

第3章數(shù)據(jù)查詢與SQL命令1本章主要內(nèi)容3.1查詢對象概述3.2通過向?qū)Щ蛟O(shè)計視圖創(chuàng)立查詢3.3使用SQL命令查詢3.4操作查詢總結(jié)和作業(yè)、實驗任務(wù)2思考問題：哪位導(dǎo)師帶了哪些研究生？導(dǎo)師年齡是否能自動更新？將“考古學(xué)”專業(yè)的研究生入學(xué)分?jǐn)?shù)增加15%以降低入學(xué)難度？研究生信息自動轉(zhuǎn)存為畢業(yè)生信息？33.1查詢對象概述查詢對象的實質(zhì)是SQL命令，本身不包含數(shù)據(jù)。屏幕所顯示的數(shù)據(jù)是查詢對象在執(zhí)行時從表中“抽取”的。生成查詢對象的途徑：查詢向?qū)?、查詢設(shè)計視圖和SQL視圖43.1查詢對象概述查詢能實現(xiàn)的功能：根據(jù)某種規(guī)那么查找局部數(shù)據(jù)同時顯示分布在假設(shè)干個表或查詢中的數(shù)據(jù)對表中的數(shù)據(jù)進行計算、分類、匯總將顯示結(jié)果轉(zhuǎn)換成數(shù)據(jù)表成批更新、刪除表中的數(shù)據(jù)將篩選出的數(shù)據(jù)追加到另一個表53.2通過向?qū)Щ蛟O(shè)計視圖創(chuàng)立查詢3.2.1用簡單查詢向?qū)刹樵?.2.2通過設(shè)計視圖編輯或創(chuàng)立查詢3.2.3條件查詢3.2.4查詢的有序輸出3.2.5使用通配符查詢3.2.6使用計算字段3.2.7交叉表查詢63.2.1用簡單查詢向?qū)刹樵?3.2.1用簡單查詢向?qū)刹樵僛例3-1]基于“導(dǎo)師”表生成“導(dǎo)師情況表”查詢,只顯示姓名、職稱和系編號字段。83.2.1用簡單查詢向?qū)刹樵冋f明：〔1〕作為查詢對象，“導(dǎo)師情況表”查詢只是一條SQL命令，并不包含數(shù)據(jù)。數(shù)據(jù)來自“導(dǎo)師”表?！?〕如果翻開查詢對象時對數(shù)據(jù)進行更新、添加，那么數(shù)據(jù)的變化實際發(fā)生在數(shù)據(jù)源(“導(dǎo)師”表)中?！?〕數(shù)據(jù)源如果被刪除，那么查詢無法翻開，并顯示出錯信息93.2.1用簡單查詢向?qū)刹樵僛例3-2]基于“導(dǎo)師”和“研究生”兩個表生成“導(dǎo)師-研究生”查詢。前提：“導(dǎo)師”表和“研究生”表已建立1:M關(guān)系顯示導(dǎo)師的編號、姓名、職稱及所帶研究生的學(xué)號、姓名和入學(xué)分?jǐn)?shù)103.2.1用簡單查詢向?qū)刹樵冋f明：查詢對象的字段名一般沿用數(shù)據(jù)表中的字段名，如果出現(xiàn)相同的字段名，系統(tǒng)自動在字段名前加上表的名字以示區(qū)別113.2.2通過設(shè)計視圖編輯或創(chuàng)立查詢翻開已有查詢的設(shè)計視圖：單擊“設(shè)計”按鈕123.2.2通過設(shè)計視圖編輯或創(chuàng)立查詢[例3-3]：為例3-2查詢添加“博導(dǎo)”列〔位于“職稱”的右側(cè)〕選中“學(xué)號”列，選擇“插入/列”菜單，在學(xué)號前插入一空白列133.2.2通過設(shè)計視圖編輯或創(chuàng)立查詢在設(shè)計視圖中創(chuàng)立查詢143.2.2通過設(shè)計視圖編輯或創(chuàng)立查詢[例3-4]查詢各系的系名導(dǎo)師姓名和研究生姓名

思考：如果“系”與“導(dǎo)師”表之間、“導(dǎo)師”與“研究生”表之間沒有建立關(guān)系，將得到什么結(jié)果？153.2.2通過設(shè)計視圖編輯或創(chuàng)立查詢[例3-5]按系名顯示各研究生的姓名“導(dǎo)師”表是“系”表與“研究生”表之間的聯(lián)系“橋梁”

163.2.3條件查詢

條件查詢的作用是對所選擇的記錄作進一步的限定[例3-6]顯示女研究生的全部信息

“=”號可以省略不顯示以避免數(shù)據(jù)重復(fù)173.2.3條件查詢[例3-7]列出入學(xué)分?jǐn)?shù)在340-360之間的所有研究生的姓名、性別和入學(xué)分?jǐn)?shù)And關(guān)系：兩邊的條件同時成立，整個表達(dá)式才成立。183.2.3條件查詢[例3-8]選出所有入學(xué)分?jǐn)?shù)在340-360之間的男研究生的姓名、性別、入學(xué)分?jǐn)?shù)幾個條件左右并列，說明是And關(guān)系(并且)。193.2.3條件查詢[例3-9]選出研究生方向為考古學(xué)或會計學(xué)的所有研究生的姓名、性別、入學(xué)分?jǐn)?shù)和研究方向'考古學(xué)'or'會計學(xué)'203.2.3條件查詢

[例3-10]顯示導(dǎo)師“馬騰躍”所帶的女研究生或者入學(xué)分?jǐn)?shù)超過320分的男研究生的信息上下并列的兩個條件，表示or關(guān)系213.2.3條件查詢[例3-11]找出所有沒有導(dǎo)師編號的研究生姓名沒有導(dǎo)師編號

223.2.4查詢的有序輸出

[例3-12]按性別的升序和導(dǎo)師編號的降序顯示記錄如果有假設(shè)干個列是排序關(guān)鍵字段，Access按從左到右的順序依次排序233.2.4查詢的有序輸出

[例3-13]按性別的升序和導(dǎo)師編號的降序顯示記錄，輸出字段順序為：姓名、導(dǎo)師編號、性別和入學(xué)分?jǐn)?shù)

輸出字段順序排序順序243.2.5使用通配符查詢?nèi)绻氩檎夷硞€姓“馬”的導(dǎo)師的信息，該如何給出查詢條件呢？通配符可以用作其它字符的占位符，用以實現(xiàn)在僅知局部內(nèi)容的情況下完成對文本、數(shù)值等類型字段的查詢253.2.5使用通配符查詢〔P62表3.1〕263.2.5使用通配符查詢

[例3-14]找出研究方向的第3個字為“醫(yī)”的所有姓“馬”的研究生的信息。

所有姓“馬”的第3個字為“醫(yī)”

注意：應(yīng)用通配符時不能用“=”，要用“l(fā)ike”273.2.5使用通配符查詢

[例3-15]找出年齡為50余歲的導(dǎo)師，要求其所帶的研究生中沒有姓馬或趙的學(xué)生。年齡為50余歲

排除姓“馬”、“趙”的

283.2.6使用計算字段

計算字段的值由表達(dá)式計算而得，不保存在表中假設(shè)表達(dá)式中引用的字段或值發(fā)生了變化，必須重新執(zhí)行查詢[例3-16]輸出導(dǎo)師的編號、姓名、出生年份和性別。計算字段，“出生年份”為別名2009-[年齡]改成：Year(Now())-[年齡]

293.2.7交叉表查詢交叉表用于顯示表中某個字段的匯總值，包括總和、計數(shù)和平均等，如查詢研究生表中不同性別的不同研究方向的學(xué)生人數(shù)。1.用向?qū)山徊姹?/p>

303.2.7交叉表查詢[例3-17]生成一個交叉表，顯示不同性別不同研究方向各導(dǎo)師所帶研究生的平均分

缺點：(1)標(biāo)題“總計入學(xué)分?jǐn)?shù)”含義不清(2)平均數(shù)的小數(shù)點位數(shù)太多

分類匯總的依據(jù)可以有多個313.2.7交叉表查詢2.在設(shè)計視圖中完善【3-17】交叉表

原為：總計入學(xué)分?jǐn)?shù)修改這兩欄的屬性323.2.7交叉表查詢3.引用兩個表的字段生成交叉表[例3-18]顯示不同系不同性別的導(dǎo)師人數(shù)先建立一個查詢，包含兩個表中的所需字段根據(jù)所建查詢用向?qū)?chuàng)立交叉表333.3使用SQL命令查詢

查詢的實質(zhì)是SQL命令343.3使用SQL命令查詢

3.3.1SQL語言概述3.3.2基于單一記錄源的查詢3.3.3基于多個記錄源的查詢3.3.4合計、匯總與計算3.3.5嵌套查詢353.3.1SQL語言概述

SQL(StructuredQueryLanguage)意為結(jié)構(gòu)化查詢語言，可用于定義、查詢、更新、管理關(guān)系型數(shù)據(jù)庫系統(tǒng)。

SQL語言易學(xué)易用，語句由近似自然語言的英語單詞組成.例如，Select學(xué)號,姓名,入學(xué)分?jǐn)?shù),研究方向From研究生Where性別='男'

顯示“研究生”表中所有男生的學(xué)號、姓名、入學(xué)分?jǐn)?shù)和研究方向363.3.1SQL語言概述

SQL語言不涉及數(shù)據(jù)庫內(nèi)部細(xì)節(jié)，通用性好。不能設(shè)計出與用戶交互的圖形界面，需用VB、VC等程序語言作為客戶端開發(fā)工具，同時在程序中嵌入SQL命令。

SQL命令的所有子句既可以寫在同一行上，也可以分行書寫,大小寫字母的含義相同；命令用分號“;”結(jié)束(也可以不寫)。

373.3.1SQL語言概述

SQL的查詢語句格式〔SELECT查詢又稱選擇查詢〕SELECT字段列表[INTO新表]FROM記錄源[WHERE<條件表達(dá)式>][GROUPBY<分組表達(dá)式>][HAVING<條件表達(dá)式>][ORDERBY字段列表[ASC|DESC]][]中的內(nèi)容為可選項383.3.2基于單一記錄源的查詢

單一記錄源：被查詢的對象是一個表或查詢進入SQL視圖：先進入空白查詢設(shè)計視圖(不選擇表)393.3.2基于單一記錄源的查詢

1.選取記錄源的全部字段或局部字段[例3-19]輸出“導(dǎo)師”表的全部字段

*表示記錄源的全部字段403.3.2基于單一記錄源的查詢

[例3-20]以例[3-19]所建立的“導(dǎo)師SQL”查詢?yōu)橛涗浽?，顯示其中的“導(dǎo)師編號”、“姓名”和“職稱”字段SELECT導(dǎo)師編號,姓名,職稱FROM導(dǎo)師SQL413.3.2基于單一記錄源的查詢

說明：〔1〕字段名之間的逗號“,”必須是英文字符〔2〕如果重命名查詢“導(dǎo)師SQL”，那么本查詢中引用的記錄源名字將自動更新。〔3〕作為記錄源的“導(dǎo)師SQL”查詢不能刪除〔4〕當(dāng)真正的記錄源“導(dǎo)師”表中的數(shù)據(jù)更新時，查詢的執(zhí)行結(jié)果也自動更新。423.3.2基于單一記錄源的查詢

2.用DISTINCT消除重復(fù)記錄[例3-21]顯示“導(dǎo)師”表中所有“職稱”名稱

SELECTDISTINCT職稱FROM導(dǎo)師

SELECT職稱FROM導(dǎo)師433.3.2基于單一記錄源的查詢

3.用TOP顯示前面的假設(shè)干條記錄語法格式：TOPn〔n為指定記錄數(shù)〕[例3-22]顯示“導(dǎo)師”表中的前4條記錄SELECTTOP4*FROM導(dǎo)師

443.3.2基于單一記錄源的查詢

4.用WHERE子句對記錄進行選擇語法格式：WHERE<表達(dá)式><關(guān)系運算符><表達(dá)式>〔關(guān)系運算符的名稱和含義見表3.2〕453.3.2基于單一記錄源的查詢

4.用WHERE子句對記錄進行選擇[例3-23]找年齡不低于60歲的導(dǎo)師，顯示局部字段SELECT姓名,性別,年齡FROM導(dǎo)師WHERE年齡>=60

463.3.2基于單一記錄源的查詢

可用邏輯運算符形成邏輯表達(dá)式，見表3.3。邏輯運算符的優(yōu)先級依次為：NOT>AND>OR

運算符名稱含義NOT非取反，若表達(dá)式成立，則取反后不成立，反之則成立AND與若干個關(guān)系表達(dá)式同時成立才成立，否則不成立OR或若干個關(guān)系表達(dá)式中，只要一個成立，邏輯表達(dá)式就成立473.3.2基于單一記錄源的查詢

[例3-24]找出所有年齡不低于60歲的女導(dǎo)師，顯示其姓名、性別和年齡。SELECT姓名,性別,年齡FROM導(dǎo)師WHERE導(dǎo)師.年齡>=60AND性別='女'

483.3.2基于單一記錄源的查詢

[例3-25]輸出所有入學(xué)分?jǐn)?shù)在350以上的女研究生，以及所有考古學(xué)研究方向的研究生姓名、性別、入學(xué)分?jǐn)?shù)、研究方向。

SELECT姓名,性別,入學(xué)分?jǐn)?shù),研究方向FROM研究生WHERE入學(xué)分?jǐn)?shù)>350AND性別='女'OR研究方向='考古學(xué)'493.3.2基于單一記錄源的查詢

思考與練習(xí)：[例3-26]請寫出SQL語句：輸出例[3-25]規(guī)那么以外的全部研究生的姓名、性別、入學(xué)分?jǐn)?shù)、研究方向。503.3.2基于單一記錄源的查詢

5.用特殊運算符過濾記錄

例如:“入學(xué)分?jǐn)?shù)BETWEEN320AND360”指入學(xué)分?jǐn)?shù)在320~360分之間，包括320分和360分者；而“入學(xué)分?jǐn)?shù)IN(320,360)”指入學(xué)分?jǐn)?shù)等于320或者360。

513.3.2基于單一記錄源的查詢

[例3-27]找出所有入學(xué)分?jǐn)?shù)在320-360之間的“考古學(xué)”和“會計學(xué)”方向的研究生姓名、性別、入學(xué)分?jǐn)?shù)、研究方向。SELECT姓名,性別,入學(xué)分?jǐn)?shù),研究方向FROM研究生WHERE研究方向IN('考古學(xué)','會計學(xué)')AND入學(xué)分?jǐn)?shù)BETWEEN320AND360523.3.2基于單一記錄源的查詢

[例3-28]找出所有沒有導(dǎo)師的研究生，顯示他們的所有信息。

SELECT*FROM研究生WHERE導(dǎo)師編號ISNULL導(dǎo)師編號為空

533.3.2基于單一記錄源的查詢

[例3-29]找出所有姓馬或者姓名中含有“國”字的研究生，顯示他們的所有信息。SELECT*FROM研究生WHERE姓名LIKE'馬*'OR姓名LIKE'*國*'

姓“馬”或姓名中含有“國”

543.3.2基于單一記錄源的查詢

[例3-30]顯示學(xué)號尾數(shù)不在0-4范圍中的研究生。

SELECT*FROM研究生WHERE學(xué)號LIKE'*[!0-4]'學(xué)號尾數(shù)不在0~4

條件也可寫成：WHERE學(xué)號LIKE'*[5-9]'553.3.2基于單一記錄源的查詢

6.用ORDERBY子句將記錄排序輸出排序的方式：升序、降序[例3-31]按性別的升序和入學(xué)分?jǐn)?shù)的降序，輸出入學(xué)分?jǐn)?shù)在340分以上的研究生的全部信息。563.3.2基于單一記錄源的查詢

SELECT*FROM研究生WHERE入學(xué)分?jǐn)?shù)>340ORDERBY性別,入學(xué)分?jǐn)?shù)DESC性別升序、入學(xué)分?jǐn)?shù)降序

573.3.3基于多個記錄源的查詢

數(shù)據(jù)源－多表建立表之間的關(guān)系－公共屬性連接數(shù)據(jù)表的方式：WHERE子句JOIN子句：內(nèi)連接、左外連接和右外連接583.3.3基于多個記錄源的查詢

1.用WHERE實現(xiàn)表間關(guān)系[例3-32]輸出全體導(dǎo)師的姓名及所帶的研究生姓名

SELECT導(dǎo)師.姓名,研究生.姓名FROM導(dǎo)師,研究生WHERE導(dǎo)師.導(dǎo)師編號=研究生.導(dǎo)師編號查詢結(jié)果排除：

(1)沒帶研究生的導(dǎo)師

(2)無導(dǎo)師編號的研究生

593.3.3基于多個記錄源的查詢說明：不同表的同名字段前要冠以表名以示區(qū)別ACCESS允許字段名中保存空格，字段名中假設(shè)有空格，引用時用[]括起來。例如字段名為“姓名”，那么引用是寫成：“導(dǎo)師.[姓名]”603.3.3基于多個記錄源的查詢

2.用內(nèi)連接INNERJOIN實現(xiàn)表與表的連接INNERJOIN出現(xiàn)在FROM子句中，格式為：FROM<表1>INNERJOIN<表2>ON<條件表達(dá)式>[例3-33]輸出全體導(dǎo)師的姓名及所帶的研究生姓名

SELECT導(dǎo)師.姓名,研究生.姓名FROM導(dǎo)師INNERJOIN研究生ON導(dǎo)師.導(dǎo)師編號=研究生.導(dǎo)師編號613.3.3基于多個記錄源的查詢

思考問題：請觀察[例3-32]與[例3-33]的區(qū)別？

623.3.3基于多個記錄源的查詢使用WHERE子句與使用INNERJOIN子句的區(qū)別在于：使用INNERJOIN子句可以實現(xiàn)在數(shù)據(jù)表“一”方添加新記錄，而WHERE子句不可以。633.3.3基于多個記錄源的查詢

注意：在較長的查詢命令中可用別名代替表名。

[例3-34]輸出導(dǎo)師馬騰躍的姓名、性別及其所帶研究生的姓名、性別、入學(xué)分?jǐn)?shù)。SELECTt.姓名,t.性別,s.姓名,s.性別,入學(xué)分?jǐn)?shù)FROM導(dǎo)師AStINNERJOIN研究生ASsONt.導(dǎo)師編號=s.導(dǎo)師編號WHEREt.姓名='馬騰躍't作為“導(dǎo)師”表的別名，s作為“研究生”表的別名入學(xué)分?jǐn)?shù)只有“研究生”表才有，可以不使用表名643.3.3基于多個記錄源的查詢

[例3-35]請寫出SQL語句：顯示所有系的系名、系中每位導(dǎo)師的姓名和每位導(dǎo)師所帶研究生的姓名。Select系.系名,導(dǎo)師.姓名,研究生.姓名from(系innerjoin導(dǎo)師on系.系編號=導(dǎo)師.系編號)innerjoin研究生on導(dǎo)師.導(dǎo)師編號=研究生.導(dǎo)師編號Select系.系名,導(dǎo)師.姓名,研究生.姓名from系,導(dǎo)師,

研究生where

系.系編號=導(dǎo)師.系編號and導(dǎo)師.導(dǎo)師編號=研究生.導(dǎo)師編號653.3.3基于多個記錄源的查詢

3.用左外連接實現(xiàn)表與表的連接FROM<表1>LEFTJOIN<表2>ON<條件條件式>將左側(cè)表(<表1>)中連接字段的值全部顯示4.用右外連接實現(xiàn)表與表的連接FROM<表1>RIGHTJOIN<表2>ON<條件表達(dá)式>將右側(cè)表(<表2>)中連接字段的值全部顯示663.3.3基于多個記錄源的查詢

[例3-36]顯示每位導(dǎo)師的姓名及其研究生姓名[例3-37]顯示全部研究生姓名及其帶教導(dǎo)師的姓名

SELECT導(dǎo)師.姓名,研究生.姓名FROM導(dǎo)師LEFTJOIN研究生ON導(dǎo)師.導(dǎo)師編號=研究生.導(dǎo)師編號SELECT導(dǎo)師.姓名,研究生.姓名FROM導(dǎo)師RIGHTJOIN研究生ON導(dǎo)師.導(dǎo)師編號=研究生.導(dǎo)師編號左側(cè)表：導(dǎo)師右側(cè)表：研究生

673.3.3基于多個記錄源的查詢

[例3-36](16條記錄)[例3-37](18條記錄)

683.3.4合計、匯總與計算

1.合計函數(shù)〔1〕COUNT函數(shù)〔2〕SUM函數(shù)〔3〕MAX和MIN函數(shù)〔4〕AVG函數(shù)693.3.4合計、匯總與計算

1.合計函數(shù)〔1〕COUNT函數(shù)：統(tǒng)計符合條件的記錄條數(shù)[例3-38]統(tǒng)計分?jǐn)?shù)高于340分的男研究生人數(shù)

SELECTCOUNT(姓名)FROM研究生WHERE入學(xué)分?jǐn)?shù)>=340AND性別='男'

SELECTCOUNT(姓名)AS合格人數(shù)

FROM研究生WHERE入學(xué)分?jǐn)?shù)>=340AND性別='男'也可以用“*”,“學(xué)號”,“性別”作為計數(shù)的對象703.3.4合計、匯總與計算

〔2〕SUM函數(shù)：求和。

參與的字段必須為數(shù)值類型[例3-39]求導(dǎo)師陳平林所帶的研究生入學(xué)分?jǐn)?shù)總和SELECTSUM(入學(xué)分?jǐn)?shù))AS入學(xué)分?jǐn)?shù)總計FROM導(dǎo)師,研究生WHERE導(dǎo)師.導(dǎo)師編號=研究生.導(dǎo)師編號AND導(dǎo)師.姓名='陳平林'713.3.4合計、匯總與計算

〔3〕MAX和MIN函數(shù)：分別用于在指定的記

錄范圍內(nèi)找出具有最大值和最小值的字段。[例3-40]找出男生中的最高分和最低分

SELECTMAX(入學(xué)分?jǐn)?shù))AS最高分,MIN(入學(xué)分?jǐn)?shù))AS最低分

FROM研究生WHERE性別='男'723.3.4合計、匯總與計算

〔4〕AVG函數(shù)：求平均，參數(shù)為數(shù)值類型。[例3-41]輸出計算機系所屬研究生的平均入學(xué)分?jǐn)?shù)“系”表與“研究生”表沒有直接聯(lián)系，必須以“導(dǎo)師”表為橋梁進行組合查詢SELECTAVG(入學(xué)分?jǐn)?shù))AS入學(xué)平均分FROM系,導(dǎo)師,研究生WHERE系.系編號=導(dǎo)師.系編號AND導(dǎo)師.導(dǎo)師編號=研究生.導(dǎo)師編號AND系名='計算機系'

733.3.4合計、匯總與計算

2.分組查詢將輸出記錄分成假設(shè)干組，以字段值相同的記錄為一組，配合合計函數(shù)進行統(tǒng)計匯總操作。格式：GROUPBY分組表達(dá)式1[,分組表達(dá)式2[,...]]743.3.4合計、匯總與計算

[例3-42]按性別統(tǒng)計導(dǎo)師的平均年齡

SELECT性別,AVG(年齡)AS平均年齡FROM導(dǎo)師GROUPBY性別SELECT性別,ROUND(AVG(年齡),1)AS平均年齡FROM導(dǎo)師GROUPBY性別注：ROUND(X,N):使輸出值X保存N位小數(shù)753.3.4合計、匯總與計算

使用GROUPBY子句進行分組時，顯示的字段只能是參與分組的字段以及基于分組字段的合計函數(shù)計算結(jié)果。763.3.4合計、匯總與計算

ORDEYBY子句總是出現(xiàn)在SELECT語句的最后

[例3-43]統(tǒng)計各導(dǎo)師所帶不同性別研究生的入學(xué)最高分，按導(dǎo)師編號升序，最高分的值降序排序。SELECT導(dǎo)師編號,性別,MAX(入學(xué)分?jǐn)?shù))AS最高分FROM研究生GROUPBY導(dǎo)師編號,性別ORDERBY導(dǎo)師編號,MAX(入學(xué)分?jǐn)?shù))DESC思考：分組子句可否寫為“GROUPBY導(dǎo)師編號”？773.3.4合計、匯總與計算

3.HAVING函數(shù)：該函數(shù)與GROUPBY子句聯(lián)合使用，可對分組后的結(jié)果進行限制。[例3-44]計算每一位導(dǎo)師所帶研究生的平均入學(xué)分?jǐn)?shù)、人數(shù)和他們的導(dǎo)師編號，沒有導(dǎo)師的3位研究生自成一組，對人數(shù)超過2人的組輸出統(tǒng)計結(jié)果。783.3.4合計、匯總與計算

SELECTROUND(AVG(入學(xué)分?jǐn)?shù)),1),COUNT(姓名)AS本組人數(shù),導(dǎo)師編號FROM研究生GROUPBY導(dǎo)師編號HAVINGCOUNT(姓名)>2對分組人數(shù)超過2人的組輸出統(tǒng)計結(jié)果注意：HAVING不能用WHERE代替793.3.4合計、匯總與計算

4.計算列

實質(zhì)是一個表達(dá)式，使用方式同一個字段[例3-45]顯示入校時間已達(dá)2年的研究生信息及年數(shù)SELECT研究生.*,YEAR(NOW())-YEAR(入學(xué)日期)

AS在校年數(shù)FROM研究生WHEREYEAR(NOW())-YEAR(入學(xué)日期)=2表達(dá)式(計算列)給表達(dá)式賦列名

入校時間已達(dá)兩年

803.3.5嵌套查詢

嵌套：將第一次查詢的結(jié)果作為第二次查詢的條件[例3-46]顯示所有入學(xué)分?jǐn)?shù)高于平均分的研究生數(shù)據(jù)

錯誤：

正確：SELECT姓名,入學(xué)分?jǐn)?shù)FROM研究生WHERE入學(xué)分?jǐn)?shù)>AVG(入學(xué)分?jǐn)?shù))SELECT姓名,入學(xué)分?jǐn)?shù)FROM研究生WHERE入學(xué)分?jǐn)?shù)>(SELECTAVG(入學(xué)分?jǐn)?shù))FROM研究生)內(nèi)嵌套查詢先被執(zhí)行813.3.5嵌套查詢

將上例修改為：顯示字段同時輸出平均分

SELECT姓名,入學(xué)分?jǐn)?shù),ROUND((SELECTAVG(入學(xué)分?jǐn)?shù))FROM研究生),2)AS平均分FROM研究生WHERE入學(xué)分?jǐn)?shù)>(SELECTAVG(入學(xué)分?jǐn)?shù))FROM研究生)第1行可否為:SELECT姓名,入學(xué)分?jǐn)?shù),AVG(入學(xué)分?jǐn)?shù))？？？823.3.5嵌套查詢

[例3-47]找出年齡最高的導(dǎo)師信息及其所在系的系名SELECT系名,導(dǎo)師.*FROM系,導(dǎo)師WHERE系.系編號=導(dǎo)師.系編號AND年齡=(SELECTMAX(年齡)FROM導(dǎo)師)嵌套查詢：先找出所有導(dǎo)師中的最高年齡

833.3.5嵌套查詢

[例3-48]找出社科系最年輕導(dǎo)師的全部信息SELECT系名,導(dǎo)師.*FROM系,導(dǎo)師WHERE系.系名='社科系'AND系.系編號=導(dǎo)師.系編號AND年齡=(SELECTMIN(年齡)FROM系,導(dǎo)師WHERE系.系名='社科系'AND系.系編號=導(dǎo)師.系編號)嵌套查詢：找出社科系導(dǎo)師的最小年齡

843.4操作查詢

操作查詢的特點：執(zhí)行后并非顯示結(jié)果〔與SELECT查詢的區(qū)別〕可以按某種規(guī)那么更新字段值可以刪除表中記錄可以將SELECT查詢的結(jié)果生成一個新的數(shù)據(jù)表可以將SELECT查詢的執(zhí)行結(jié)果追加到另外一個數(shù)據(jù)表中853.4操作查詢

3.4.1生成表查詢3.4.2追加查詢3.4.3更新查詢3.4.4刪除查詢

863.4.1生成表查詢

作用：將SELECT命令執(zhí)行的結(jié)果形成一個表保存在數(shù)據(jù)庫中。操作方法：SQL代碼：在SELECT命令的字段名列表后加上子句INTO<新表名>在查詢設(shè)計視圖中完成：翻開查詢設(shè)計視圖→執(zhí)行“查詢”/“生成表查詢”菜單命令873.4.1生成表查詢

[例3-49]將所有女研究生的全部信息按導(dǎo)師編號降序、入學(xué)分?jǐn)?shù)升序生成Female表。SQL代碼：SELECT研究生.*INTOFemaleFROM研究生WHERE性別='女'ORDERBY導(dǎo)師編號DESC,入學(xué)分?jǐn)?shù)練習(xí)：通過設(shè)計視圖完成883.4.2追加查詢

作用：將一個表中符合條件的局部或全部記錄添加到另一個表中操作方法：翻開查詢設(shè)計視圖，選擇表“查詢”菜單項選擇擇“追加查詢”.893.4.2追加查詢

[例3-50]將研究生表中的全部男研究生信息追加到表Female中，要求不包含記錄中“性別”信息只追加男研究生的信息

但不追加“性別”字段903.4.2追加查詢

例3-50對應(yīng)的SQL代碼〔了解〕：INSERTINTOFemale(學(xué)號,姓名,入學(xué)日期,分?jǐn)?shù),研究方向,導(dǎo)師編號)SELECT學(xué)號,姓名,入學(xué)日期,入學(xué)分?jǐn)?shù),研究方向,導(dǎo)師編號FROM研究生WHERE性別='男'913.4.2追加查詢幾點說明：待追加的字段與追加到的字段的名稱可以不一致，但類型相同或兼容。追加操作不應(yīng)該破壞數(shù)據(jù)的完整性約束。待追加的字段數(shù)可以少于目的表的字段數(shù)，但追加到目的表主鍵的字段不能省略，追加到外鍵字段的值也必須是有效的值。923.4.3更新查詢

作用：根據(jù)某種規(guī)那么批量修改表中的數(shù)據(jù)操作：翻開設(shè)計視圖，“查詢”/“更新查詢”[例3-51]將“導(dǎo)師”表每位導(dǎo)師的年齡增加1歲

UPDATE導(dǎo)師SET導(dǎo)師.年齡=[年齡]+1SQL代碼：更新查詢對表中記錄的修改不得違反所有的數(shù)據(jù)完整性約束

字段名必須用[]括起來933.4.3更新查詢

[例3-52]計算機系所有男同學(xué)的分?jǐn)?shù)增加10%

不能寫成110%943.4.4刪除查詢

作用：按規(guī)那么一次刪除表中所有符合條件的記錄操作：翻開設(shè)計視圖，“查詢”/“刪除查詢”執(zhí)行刪除查詢前先復(fù)制一個副本以備恢復(fù)數(shù)據(jù)953.4.4刪除查詢

[例3-53]用刪除查詢刪除所有入學(xué)分?jǐn)?shù)在340分或以上的男研究生的記錄。刪除查詢在刪除記錄時自動遵守“參照完整性規(guī)則”

DELETE入學(xué)分?jǐn)?shù),性別FROM研究生WHERE((入學(xué)分?jǐn)?shù)>=340)AND(性別='男'))SQL代碼：963.4.4刪除查詢

[例3-54]刪除陳平林導(dǎo)師所帶研究生中分?jǐn)?shù)最低者本例首先要找出陳平林所帶研究生的入學(xué)分?jǐn)?shù)最低分，因此在條件中需嵌套查詢。(SELECTMIN(入學(xué)分?jǐn)?shù))FROM導(dǎo)師,研究生WHERE導(dǎo)師.導(dǎo)師編號=研究生.導(dǎo)師編號AND導(dǎo)師.姓名='陳平林')97第3章主要內(nèi)容總結(jié)查詢對象概述查詢的實質(zhì)、生成查詢的方法通過向?qū)Щ蛟O(shè)計視圖創(chuàng)立查詢條件查詢、有序輸出、通配符、計算字段、交叉表查詢使用SQL命令查詢SQL及其查詢語句的格式基于單一記錄源〔*，distinct，top，where，特殊運算符，orderby〕多個記錄源的查詢〔where、join〕合計〔count、sum、max、min、avg〕、匯總〔groupby、having〕與計算嵌套查詢操作查詢生成表〔into〕、追加、更新、刪除98本章作業(yè)P851.〔1〕~〔4〕99實驗〔二〕——數(shù)據(jù)庫查詢與SQL命令第6周4個學(xué)時，第3章P86實驗題：要求〔1〕~〔13〕寫預(yù)習(xí)報告，給出相應(yīng)的SQL命令所有的題目都要完成，以“數(shù)據(jù)庫查詢與SQL命令–班級姓名.mdb”提交實驗成果實驗素材見公共郵箱10006年4月全國計算機二級考試10106年4月全國計算機二級考試10206年4月全國計算機二級考試10306年9月全國計算機二級考試1.SQL的含義是A〕結(jié)構(gòu)化查詢語言 B〕數(shù)據(jù)定義語言C〕數(shù)據(jù)庫查詢語言 D〕數(shù)據(jù)庫操縱與控制語言2.在SQL的SELECT語句中，用于實現(xiàn)選擇運算的是A〕FOR B〕WHILE C〕IF D〕WHERE10406年9月全國計算機二級考試3.以下關(guān)于空值的表達(dá)中，錯誤的選項是A〕空值表示字段還沒有確定值 B〕Access使用NULL來表示空值C〕空值等同于空字符串 D〕空值不等于數(shù)值04.假設(shè)要查詢某字段的值為“JSJ”的記錄，在查詢設(shè)計視圖對應(yīng)字段的準(zhǔn)那么中，錯誤的表達(dá)式是A〕JSJB〕”JSJ”C〕”*JSJ” D〕Like“JSJ”10506年9月全國計算機二級考試5.下面顯示的是查詢設(shè)計視圖的“設(shè)計網(wǎng)格”局部：從所顯示的內(nèi)容中可以判斷出該查詢要查找的是A〕性別為“女”并且1980以前參加工作的記錄B〕性別為“女”并且1980以后參加工作的記錄C〕性別為“女”或者1980以前參加工作的記錄D〕性別為“女”或者1980以后參加工作的記錄10607年4月全國計算機二級考試1.Access中已建立了“工資”表，表中包括“職工”、“所在單位”、“根本工資”和“應(yīng)發(fā)工資”等字段，如果要按單位統(tǒng)計應(yīng)發(fā)工資總數(shù)，那么在查詢設(shè)計視圖的“所在單位”的“總計”行和“應(yīng)發(fā)工資”的“總計”行中分別選擇的是A)sum，groupbyB)count，groupbyC)groupby，sumD)groupby，count2.在創(chuàng)立交叉表查詢時，列標(biāo)題字段的值顯示在交叉表的位置是()A)第一行B)第一列C)上面假設(shè)干行D)左面假設(shè)干列10707年4月全國計算機二級考試3.在Access中已建立了“學(xué)生”表，表中有“學(xué)號”、“姓名”、“性別”和“入學(xué)成績”等字段。執(zhí)行如下SQL命令：Select性別，avg〔入學(xué)成績〕from學(xué)生groupby性別其結(jié)果是A)計算并顯示所有學(xué)生的性別和入學(xué)成績的平均值B)按性