第五章查詢和視圖

1、綜合例題綜合例題5.1 5.1 查詢的創(chuàng)建和使用查詢的創(chuàng)建和使用1. 使用界面方法使用界面方法 1）查詢向?qū)В┎樵兿驅(qū)?2）查詢?cè)O(shè)計(jì)器）查詢?cè)O(shè)計(jì)器 打開查詢?cè)O(shè)計(jì)器的方法：打開查詢?cè)O(shè)計(jì)器的方法： 用用 create query 命令打開查詢?cè)O(shè)計(jì)器窗口。命令打開查詢?cè)O(shè)計(jì)器窗口。 選擇選擇“文件文件”菜單中的菜單中的“新建新建”或常用工具欄中的或常用工具欄中的“新建新建”按鈕。按鈕。 在在“項(xiàng)目管理器項(xiàng)目管理器”窗口中的窗口中的“數(shù)據(jù)數(shù)據(jù)”選項(xiàng)卡中選擇選項(xiàng)卡中選擇“查查詢?cè)儭?，并選擇，并選擇“新建新建”按鈕。按鈕。2. 命令方法命令方法 用用select-sql語句。語句。學(xué)號(hào)學(xué)號(hào)課程課程1課程課程

2、2課程課程3課程課程4 課程課程5 課程課程6總分總分950201856591838777488950102767884659267462表中的坐標(biāo)。表中的坐標(biāo)。 SELECTFROM已知學(xué)生表中含有字符型字段（已知學(xué)生表中含有字符型字段（bjbh）和日期）和日期型字段出生日期型字段出生日期(csrq)。在項(xiàng)目中創(chuàng)建查詢：基于學(xué)生。在項(xiàng)目中創(chuàng)建查詢：基于學(xué)生(xs)表統(tǒng)計(jì)各班各年份出生的人數(shù)，要求輸出字段為：表統(tǒng)計(jì)各班各年份出生的人數(shù)，要求輸出字段為：bjbh，出生年份，人數(shù)，查詢結(jié)果按班級(jí)編號(hào)升序排，出生年份，人數(shù)，查詢結(jié)果按班級(jí)編號(hào)升序排序，同一班級(jí)再按人數(shù)降序排序。序，同一班級(jí)再按人數(shù)降

3、序排序。SELECT Xs.bjbh, YEAR(csrq) AS 出生年份出生年份, COUNT(*) AS 人數(shù)人數(shù); FROM sjk!xs; GROUP BY Xs.bjbh, 2; ORDER BY Xs.bjbh, 3 DESC基于基于xscjview視圖和視圖和kc表統(tǒng)計(jì)學(xué)生所學(xué)必修課表統(tǒng)計(jì)學(xué)生所學(xué)必修課的門數(shù)和總學(xué)分。其中學(xué)生的成績(jī)?cè)诘拈T數(shù)和總學(xué)分。其中學(xué)生的成績(jī)?cè)?0或或60以上才能取以上才能取得學(xué)分。要求輸出：得學(xué)分。要求輸出：xh,xm,必修課門數(shù)，總學(xué)分，查詢必修課門數(shù)，總學(xué)分，查詢結(jié)果按學(xué)號(hào)排序。結(jié)果按學(xué)號(hào)排序。SELECT Xscjview.xh, Xscjview

4、.xm, ; sum(iif(cj=60,xf,0) as 總學(xué)分總學(xué)分,; sum(iif(bxk,1,0) as 必修課門數(shù)必修課門數(shù); FROM sjk!xscjview INNER JOIN sjk!kc ; ON Xscjview.kcdh = Kc.kcdh; GROUP BY Xscjview.xh; ORDER BY Xscjview.xh查詢綜合例題查詢綜合例題 已知在已知在SJK數(shù)據(jù)庫的學(xué)生（數(shù)據(jù)庫的學(xué)生（XS）表中含有學(xué)）表中含有學(xué)號(hào)（號(hào)（xh）、姓名（）、姓名（xm）等字段，成績(jī)（）等字段，成績(jī)（CJ）表中）表中含有學(xué)號(hào)（含有學(xué)號(hào)（xh）、成績(jī)（）、成績(jī)（cj）等字段。

5、按如下要求）等字段。按如下要求修改查詢修改查詢CHAXUN： 基于基于XS表和表和CJ表查詢兩門或兩門以上課程成表查詢兩門或兩門以上課程成績(jī)不及格（即低于績(jī)不及格（即低于60）的學(xué)生，要求輸出）的學(xué)生，要求輸出xh、xm和不及格課程門數(shù)，查詢結(jié)果按不及格門數(shù)降序和不及格課程門數(shù)，查詢結(jié)果按不及格門數(shù)降序排序，相同時(shí)按學(xué)號(hào)排序。排序，相同時(shí)按學(xué)號(hào)排序。例一例一SELECT Xs.xh, Xs.xm, COUNT(*) as 不及格課程門數(shù)不及格課程門數(shù); FROM sjk!xs INNER JOIN sjk!cj ; ON Xs.xh = Cj.xh; WHERE Cj.cj = 2; ORDE

6、R BY 3 DESC, Xs.xhSELECT Xs.xh, Xs.xm, SUM(IIF(cj2; ORDER BY 3 DESC, Xs.xh 已知在已知在SJK數(shù)據(jù)庫的圖書（數(shù)據(jù)庫的圖書（TS）表中含有圖書）表中含有圖書分類號(hào)分類號(hào)(flh)、書名（、書名（sm）等字段，借閱）等字段，借閱(JY)表中含有表中含有圖書分類號(hào)圖書分類號(hào)(flh)、借閱人員類型（、借閱人員類型（lx）等字段。按如）等字段。按如下要求修改查詢下要求修改查詢CHAXUN： 基于基于TS表和表和JY表查詢各類圖書被教師（表查詢各類圖書被教師（lx為為“J”）和學(xué)生（）和學(xué)生（lx為為“X”）借閱的次數(shù)情況。要求輸

7、）借閱的次數(shù)情況。要求輸出字段為：出字段為：flh、sm、教師借閱次數(shù)、學(xué)生借閱次數(shù)，、教師借閱次數(shù)、學(xué)生借閱次數(shù)，教師借閱次數(shù)和學(xué)生借閱次數(shù)均為教師借閱次數(shù)和學(xué)生借閱次數(shù)均為0的記錄不顯示，的記錄不顯示，查詢結(jié)果按教師借閱次數(shù)降序排序。查詢結(jié)果按教師借閱次數(shù)降序排序。例二例二SELECT Jy.flh, Ts.sm, sum(iif(lx=“J”,1,0) AS 教師；教師；借閱次數(shù)借閱次數(shù),; sum(iif(lx=X,1,0) AS 學(xué)生借閱次數(shù)學(xué)生借閱次數(shù); FROM sjk!jy INNER JOIN sjk!ts ; ON Jy.flh = Ts.flh; GROUP BY Jy.

8、flh; HAVING 教師借閱次數(shù)教師借閱次數(shù) 0; OR 學(xué)生借閱次數(shù)學(xué)生借閱次數(shù) 0; ORDER BY 3 DESC 已知在已知在SJK數(shù)據(jù)庫的教師（數(shù)據(jù)庫的教師（JS）表中含有工號(hào)）表中含有工號(hào)（gh）、姓名（）、姓名（xm）、職稱代號(hào)（）、職稱代號(hào)（zcdh）等字段；）等字段；工資（工資（GZ）表中含有工號(hào)（）表中含有工號(hào)（gh）、工資（）、工資（jbgz）等）等字段。按如下要求修改查詢字段。按如下要求修改查詢CHAXUN： 基于基于JS表和表和GZ表查詢職稱為表查詢職稱為“教授教授”（職稱代（職稱代號(hào)為號(hào)為“01”）和）和“副教授副教授”（職稱代號(hào)為（職稱代號(hào)為“02”）的教）的

9、教師人數(shù)和工資情況。要求輸出字段為職稱（字段值顯師人數(shù)和工資情況。要求輸出字段為職稱（字段值顯示為示為“教教 授授”和和“副教授副教授”）、人數(shù)、最高工資、）、人數(shù)、最高工資、最低工資和平均工資，結(jié)果按平均工資降序排序，且最低工資和平均工資，結(jié)果按平均工資降序排序，且將結(jié)果輸出至表將結(jié)果輸出至表TEMP。例三例三SELECT iif(zcdh=01,教授教授,副教授副教授) as 職職稱稱, ；count(*) as 人數(shù)人數(shù),; MAX(jbgz) AS 最高工資最高工資, MIN(jbgz) AS 最高工資最高工資, avg(jbgz) as 平均工資平均工資; FROM sjk!js I

10、NNER JOIN sjk!gz ; ON Js.gh = Gz.gh; WHERE Js.zcdh = 02; GROUP BY 1; ORDER BY 5 DESC; INTO TABLE temp 已知在已知在SJK數(shù)據(jù)庫的系名（數(shù)據(jù)庫的系名（XIM）表中含有）表中含有系代號(hào)（系代號(hào)（xdh）、系名（）、系名（ximing）等字段，教師）等字段，教師（JS）表中含有工號(hào)（）表中含有工號(hào)（gh）、姓名（）、姓名（xm）、系代）、系代號(hào)（號(hào)（xdh）、）、email地址（地址（email）等字段。按如下）等字段。按如下要求修改查詢要求修改查詢CHAXUN： 基于基于XIM表和表和JS表查詢各系在表查詢各系在yahoo網(wǎng)站上網(wǎng)站上注冊(cè)的信箱個(gè)數(shù)。要求輸出字段為注冊(cè)的信箱個(gè)數(shù)。要求輸出字段為xdh、ximing和和注冊(cè)人數(shù)，查詢結(jié)果按注冊(cè)人數(shù)降序排序，且輸注冊(cè)人數(shù)，查詢結(jié)果按注冊(cè)人數(shù)降序排序，且輸出至表出至表TEMP。提示：提示：yahoo網(wǎng)站上注冊(cè)的信箱是指在網(wǎng)站上注冊(cè)的信箱是指在email地址地址中含有中含有“”子字符串。子字符串。例四例四SE