第五章查詢和視圖

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é)生的成績?cè)诘拈T數(shù)和總學(xué)分。其中學(xué)生的成績?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）等字段，成績（）等字段，成績（CJ）表中）表中含有學(xué)號(hào)（含有學(xué)號(hào)（xh）、成績（）、成績（cj）等字段。

5、按如下要求）等字段。按如下要求修改查詢修改查詢CHAXUN： 基于基于XS表和表和CJ表查詢兩門或兩門以上課程成表查詢兩門或兩門以上課程成績不及格（即低于績不及格（即低于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