TransactSQL數(shù)據(jù)查詢與更新.ppt

1,第五章 Transact-SQL數(shù)據(jù)查詢與更新,假如有人提出這樣的一些需求,我們?nèi)绾翁幚砟?1、我希望將學(xué)生基本信息中的姓名,家庭住址,聯(lián)系電話單獨(dú)放入一個(gè)新表中保存 2、我想統(tǒng)計(jì)不同政治面貌的人數(shù) 3、后勤處想知道表中有幾種少數(shù)民族，方便安排伙食. 4、我有個(gè)熟人叫馬某某，是男同學(xué)，名字是兩個(gè)字的，幫我查查,2,第五章 Transact-SQL數(shù)據(jù)查詢與更新,5.1 簡(jiǎn)單數(shù)據(jù)查詢 5.2 分組查詢 5.3 聯(lián)接查詢 5.4 子查詢 5.5 聯(lián)合查詢 5.8 排名函數(shù) 5.9 數(shù)據(jù)更新 5.10 事務(wù),3,學(xué)生基本信息表,第五章 Transact-SQL數(shù)據(jù)查詢與更新,學(xué)生基本信息表,4,成績(jī)表,一個(gè)學(xué)生,各門課程,各門課程的成績(jī),5,系部表,課程信息表,6,5.1 簡(jiǎn)單數(shù)據(jù)查詢,SELECT select_list INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC COMPUTE 子句,7,5.1 簡(jiǎn)單數(shù)據(jù)查詢,SELECT各子句執(zhí)行順序及功能簡(jiǎn)介： （1） SELECT子句：用于指定輸出列（字段），也可求值輸出。 （2） INTO子句：將檢索結(jié)果存儲(chǔ)到新表或視圖中。 （3） FROM子句：用于指定檢索數(shù)據(jù)的源表或視圖。 （4） WHERE子句：指定選擇行（記錄）的過濾條件。 （5） GROUP BY：子句對(duì)檢索到記錄進(jìn)行分組。 （6） HAVING子句：系指定記錄輔助過濾條件，從分組的結(jié)果中篩選行，即選取滿足條件的那些組。 （7） ORDER BY子句：是對(duì)檢索到數(shù)據(jù)進(jìn)行排序；ASC 和 DESC 關(guān)鍵字用于指定行是按升序還是按降序排序，默認(rèn)升序。,8,5.1 簡(jiǎn)單數(shù)據(jù)查詢,例：使用 SELECT 語句查找學(xué)生基本信息表中回族同學(xué)的姓名和家庭住址，按出生日期降序排列。 SELECT 姓名,家庭住址 FROM 學(xué)生基本信息表 WHERE 族別=回族 ORDER BY 出生日期 DESC,Select - from - where -,9,5.1 簡(jiǎn)單數(shù)據(jù)查詢,5-1 SELECT子句和FROM子句 1、語法 SELECT ALL | DISTINCT TOP n PERCENT WITH TIES l ALL關(guān)鍵字:為默認(rèn)設(shè)置，用于指定查詢結(jié)果集的所有行，包括重復(fù)行。 l DISTINCT: 用于刪除結(jié)果集中重復(fù)的行。 l TOP n PERCENT : 指定只返回查詢結(jié)果集中的前n行。如果加了PERCENT，則表示只返回查詢結(jié)果集中的前n%行。 WITH TIES 用于指定從基本結(jié)果集中返回附加的行。,10,5.1 簡(jiǎn)單數(shù)據(jù)查詢,2、選擇列 （1）選擇所有列* （2）選擇指定列，各列之間用逗號(hào)分隔。 3、在查詢結(jié)果集中加入常量,字符“-”將名稱的兩個(gè)部分分開。 Select 課程編號(hào)+-+課程名稱 from 課程信息表 (說明:字段間用加號(hào)表示將字段值合并為一列,-也可改為其他） 4、為選擇列指定別名 列表達(dá)式 as 列別名 或 列表達(dá)式 列別名 或 列別名=列表達(dá)式 Select avg(成績(jī)) as 平均成績(jī) from 成績(jī)表,11,4、選擇列表中的計(jì)算表達(dá)式 （1）對(duì)數(shù)字列或常量使用算術(shù)運(yùn)算或函數(shù)進(jìn)行的運(yùn)算。 Select MAX(成績(jī)） as 最高分 from 成績(jī)表 Select sum(獎(jiǎng)金) as 獎(jiǎng)金總和 from 職工工資表 ( 2 ) Case 語句 USE TEST SELECT 學(xué)號(hào), 等級(jí)= CASE WHEN 成績(jī)=90 THEN 優(yōu) WHEN 成績(jī)=80 THEN 良 WHEN 成績(jī)=70 THEN 中 END FROM 成績(jī),5.1 簡(jiǎn)單數(shù)據(jù)查詢,12,5.1 簡(jiǎn)單數(shù)據(jù)查詢,（3）數(shù)據(jù)類型函數(shù) Select 學(xué)號(hào), 課程編號(hào)+cast (成績(jī) as char(8) from 成績(jī)表 6、使用distinct消除重復(fù)行 Select distinct 族別 from 學(xué)生基本信息表 7、使用top和percent限制結(jié)果集 Select top 3 學(xué)號(hào)，姓名 from 學(xué)生 8、 從學(xué)生基本信息表中只顯示10%的信息 select top 10 percent * from 學(xué)生基本信息表 9、顯示成績(jī)表1%行信息，要求附加行,按學(xué)號(hào)降序排列 Select top 1 percent with ties * from 成績(jī)表 order by 學(xué)號(hào) desc,13,關(guān)于select和from語句的課堂作業(yè),1. 顯示學(xué)生基本信息表中學(xué)生的姓名，家庭住址 2. 顯示學(xué)生基本信息表的后10個(gè)學(xué)號(hào)的信息。 將學(xué)生的成績(jī)上漲10%顯示，該字段設(shè)為“期望成績(jī)” 將學(xué)生基本信息表中的學(xué)生年齡降序排列,SELECT 姓名，家庭住址 FROM 學(xué)生基本信息表 SELECT TOP 10 * FROM 學(xué)生基本信息表 ORDER BY 學(xué)號(hào) DESC SELECT 學(xué)號(hào),成績(jī)*1.1 AS 期望成績(jī) FROM 成績(jī)表 SELECT 姓名,DATEDIFF(YEAR,出生日期,GETDATE() AS 年齡 FROM 學(xué)生基本信息表 ORDER BY 年齡 DESC,14,5.1 簡(jiǎn)單數(shù)據(jù)查詢,2 WHERE子句 1、比較運(yùn)算符(,，=等等) Select * from 課程信息表 where 學(xué)分2 2、范圍 (between 和not between) Select 學(xué)號(hào) from 成績(jī)表 where 成績(jī) not between 60 and 80 3、關(guān)鍵字in 與not in Select * from 學(xué)生 where 學(xué)分 not in(2,8,12) 4、模式匹配（like和not like) _表示任何單個(gè)字符，%表示任意多個(gè)字符 在指定范圍內(nèi)的任何單個(gè)字符。 不在指定范圍內(nèi)的任何單個(gè)字符,15,5.1 簡(jiǎn)單數(shù)據(jù)查詢,Like通配符的運(yùn)用 搜索以張開頭的姓名 搜索有個(gè)“麗”字的姓名 搜索姓名最后一個(gè)字是勇 搜索以9結(jié)尾的兩位數(shù)學(xué)號(hào) 搜索姓張,王,李,趙的姓名 搜索除了張,王,李,趙的姓名 搜索以m開頭,第二字母不是c的所有名稱,like 張% like %麗% like %勇 like _9 like 張王李趙% like 張王李趙% like mc%,16,5.1 簡(jiǎn)單數(shù)據(jù)查詢,5、空值(is null和is not null) 例：查詢testdb庫的成績(jī)表中補(bǔ)考成績(jī)空值情況 Select * from 成績(jī) where 補(bǔ)考成績(jī) is null 6、所有記錄（=all,all,300) or (區(qū)域=西北 and 學(xué)號(hào) like03%),17,select * from 學(xué)生基本信息表 where 姓名 like 趙錢孫李% select * from 學(xué)生基本信息表 where 性別=男 and 姓名 like 馬_,關(guān)于where語句的課堂作業(yè),1、 顯示學(xué)生中姓名趙錢孫李的學(xué)生信息 2、 查詢馬(二字組成的姓名)的男同學(xué)信息。,18,5.2 分組查詢,9-3-1 常用統(tǒng)計(jì)函數(shù),在SELECT 語句中，可以使用統(tǒng)計(jì)函數(shù)、GROUP BY 子句和COMPUTE BY 子句對(duì)查詢結(jié)果進(jìn)行分類匯總,19,5.2 分組查詢,1、顯示學(xué)生基本信息表最小出生日期 Select min(出生日期) from 學(xué)生基本信息表 2、查詢工資表中最高的基本工資信息 Select max(基本工資）from 工資表 3、顯示工資表中平均基本工資信息 Select avg(基本工資） from 工資表 4、統(tǒng)計(jì)學(xué)生基本信息表中“漢族”學(xué)生人數(shù) Select count(*) from 學(xué)生基本信息表 where 族別=漢族 5、查詢工資表中獎(jiǎng)金的總和 Select sum（獎(jiǎng)金) from 工資表,20,5.2 分組查詢,2、 GROUP BY 子句的語法格式為： GROUP BY ALL group_by_expression ,.n HAVING search_condition 例：顯示不同政治面貌的人數(shù) SELECT 政治面貌, COUNT(*) AS 人數(shù) FROM 學(xué)生表 GROUP BY 政治面貌 思考：顯示不同族別的人數(shù) 例：顯示成績(jī)表每個(gè)學(xué)生的成績(jī)總分 SELECT 學(xué)號(hào),SUM(成績(jī)) AS 總分 FROM 成績(jī)表 GROUP BY 學(xué)號(hào) 思考：統(tǒng)計(jì)職工檔案表不同職稱的工資平均情況,21,HAVING子句和WHERE子句很相似，均用于設(shè)置數(shù)據(jù)篩選條件。 WHERE子句對(duì)分組前的數(shù)據(jù)進(jìn)行篩選，條件中不能包含聚合函數(shù)； HAVING子句對(duì)分組過后的數(shù)據(jù)進(jìn)行篩選，條件中經(jīng)常包含聚合函數(shù)。 HAVING子句必須和GROUP BY子句聯(lián)合使用 例：查詢學(xué)生基本信息表中“回族”，“蒙古”兩個(gè)民族的學(xué)生人數(shù) Select 族別,count(*) as 人數(shù) from 學(xué)生基本信息表 group by 族別 having 族別 in(回族,蒙古),5.2 分組查詢,22,5.2 分組查詢,提高： 統(tǒng)計(jì)成績(jī)表中每門課的及格人數(shù) SELECT 課程編號(hào), COUNT(*) AS 人數(shù) FROM 成績(jī)表 WHERE (成績(jī) 60) GROUP BY 課程編號(hào) 提高：顯示總成績(jī)大于等于520分以上的學(xué)生情況。 SELECT 學(xué)號(hào),SUM(成績(jī)) FROM 成績(jī)表 GROUP BY學(xué)號(hào) HAVING SUM(成績(jī))=520,23,5.2 分組查詢,3、 使用COMPUTE BY匯總 1、而COMPUTE子句使用戶得以用同一SELECT 語句既查看明細(xì)行，又查看總計(jì)行。 2、COMPUTE BY 子句使用戶得以用同一SELECT 語句既查看明細(xì)行，又查看分類總行； 3、COMPUTE 子句需要下列信息： 可選的 BY 關(guān)鍵字，該關(guān)鍵字可對(duì)一列計(jì)算指定的行統(tǒng)計(jì)； 行統(tǒng)計(jì)函數(shù)名稱：例如，SUM、AVG、MIN、MAX 或 COUNT； 要對(duì)其執(zhí)行行統(tǒng)計(jì)函數(shù)的列。,24,5.2 分組查詢,例：查詢所有職工的工資總和，并顯示明細(xì)記錄。 不顯示明細(xì)記錄： SELECT SUM (工資) FROM 職工檔案表 SELECT * FROM 職工檔案表 COMPUTE SUM(工資) 例：按學(xué)號(hào)顯示學(xué)生成績(jī)，并計(jì)算每人的平均分.并顯示每個(gè)分組的明細(xì)記錄內(nèi)容。 不顯示明細(xì)記錄： SELECT 學(xué)號(hào),AVG(成績(jī)) FROM 成績(jī)表 GROUP BY 學(xué)號(hào) SELECT * FROM 成績(jī)表 ORDER BY 學(xué)號(hào) COMPUTE AVG(成績(jī)) BY 學(xué)號(hào),25, 5.3 聯(lián)接查詢,* 聯(lián)接，可以根據(jù)各個(gè)表之間的邏輯關(guān)系從兩個(gè)或多個(gè)表中檢索數(shù)據(jù)。聯(lián)接表示如何使用一個(gè)表中的數(shù)據(jù)來選擇另一個(gè)表中的行。 * 聯(lián)接條件通過以下方法定義兩個(gè)表在查詢中的關(guān)聯(lián)方式： （1） 指定每個(gè)表中要用于聯(lián)接的列。典型的聯(lián)接條件在一個(gè)表中指定外鍵，在另一個(gè)表中指定與其關(guān)聯(lián)的鍵。 （2） 指定比較各列的值時(shí)要使用的邏輯運(yùn)算符（=、 等）。 說明: 1. 聯(lián)接條件可在 FROM 或 WHERE 子句中指定，建議在 FROM 子句中指定聯(lián)接條件，有助于將聯(lián)接條件與 WHERE 子句中可能指定的其它搜索條件分開。 2. 引用多表字段時(shí),任何重復(fù)的列名都必須用表名限定,26, 5.3 聯(lián)接查詢,1、內(nèi)聯(lián)接（inner join） 使用比較運(yùn)算符根據(jù)每個(gè)表共有的列的值匹配兩個(gè)表的行。包括：相等聯(lián)接和自然聯(lián)接 2、外聯(lián)接 左外聯(lián)接（left outer join）：通過左向外聯(lián)接引用左表的所有行。如果左表的某行在右表中沒有匹配行，則將為右表返回空值。 右向外聯(lián)接（ right outer join）：通過右向外聯(lián)接引用右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。 完整外部聯(lián)接（ full outer join）：返回兩個(gè)表的所有行。凡對(duì)應(yīng)表中沒有匹配行，則返回付回空值，則整個(gè)結(jié)果集行包含基表的數(shù)據(jù)值。 3.交叉聯(lián)接：左表每一行與右表中所有行組合。,27,交叉聯(lián)接,28, 5.3 聯(lián)接查詢,2、 內(nèi)聯(lián)接 (1)相等聯(lián)接 例1: 查詢學(xué)生基本信息表和成績(jī)表的所有信息,按學(xué)號(hào)聯(lián)接. select * from 學(xué)生基本信息表 AS XS inner join 成績(jī)表 on XS.學(xué)號(hào)=成績(jī)表.學(xué)號(hào) 注:兩個(gè)表中都有學(xué)號(hào)字段,因此顯示結(jié)果中有兩個(gè)學(xué)號(hào)字段. (2)自然聯(lián)接 例2:更改選擇列表消除兩個(gè)相同列中的一個(gè)(學(xué)號(hào)),即自然聯(lián)接 select XS.* ,課程編號(hào),成績(jī) from 學(xué)生基本信息表 AS XS inner join 成績(jī)表 AS CJ on XS.學(xué)號(hào)=CJ.學(xué)號(hào),29, 5.3 聯(lián)接查詢,普通例題:利用“系部表”和“課程信息表”,查詢?nèi)握n教師所在系 select 任課教師,系部名稱 from 系部表 inner join 課程信息表 on 系部表.系部編號(hào)=課程信息表.系部編號(hào) 增強(qiáng)：利用“系部表”和“課程信息表”,查詢“基礎(chǔ)科學(xué)部”和“信息與計(jì)算機(jī)科學(xué)”的課程名稱,輸出系部名稱和課程名稱，按系部 名稱升序排列。 select 系部名稱,課程名稱 from 系部表 inner join 課程信息表 on 系部表.系部編號(hào)=課程信息表.系部編號(hào) where 系部名稱 in(基礎(chǔ)科學(xué)部,信息與計(jì)算機(jī)科學(xué)) order by 系部名稱,30, 5.3 聯(lián)接查詢,例: 利用成績(jī)表，學(xué)生基本信息表顯示學(xué)號(hào)，姓名，總分（用派生表的方法）,select 學(xué)生基本信息表.學(xué)號(hào),姓名,總分 from 學(xué)生基本信息表 inner join on 學(xué)生基本信息表.學(xué)號(hào)=成績(jī)二.學(xué)號(hào),(select 學(xué)號(hào),sum(成績(jī)) as 總分 from 成績(jī)表 group by 學(xué)號(hào)) as 成績(jī)二,31, 5.3 聯(lián)接查詢,提高： 利用“系部表”和“課程信息表”,統(tǒng)計(jì)不同系的課程安排數(shù)目,顯示輸出系部名稱和課程數(shù)目（參照課件中from中派生表的用法） (select 系部編號(hào),count(*) as 課程數(shù) from 課程信息表 group by 系部編號(hào)) as 課程表,select 系部名稱,課程數(shù) from 系部表 inner join on 系部表.系部編號(hào)=課程表.系部編號(hào),32, 5.3 聯(lián)接查詢,提高：利用“成績(jī)表”和“學(xué)生基本信息表”，統(tǒng)計(jì)平均分小于80的學(xué)生名單和平均分（參照課件中from中派生表的用法） (select 學(xué)號(hào),avg(成績(jī)) as 平均分 from 成績(jī)表 group by 學(xué)號(hào) having(avg(成績(jī))80) as 成績(jī)二,select 姓名,成績(jī)二.平均分 from 學(xué)生表 inner join on 成績(jī)二.學(xué)號(hào)=學(xué)生表.學(xué)號(hào),select 姓名,成績(jī)二.平均分 from 學(xué)生表 inner join (select 學(xué)號(hào),avg(成績(jī)) as 平均分 from 成績(jī)表 group by 學(xué)號(hào)) as 成績(jī)二 on 成績(jī)二.學(xué)號(hào)=學(xué)生表.學(xué)號(hào) WHERE 平均分80,33, 5.3 聯(lián)接查詢,(3)使用等號(hào)以外的運(yùn)算符的聯(lián)接 select * from 課程信息表,成績(jī)表 where 課程信息表.課程編號(hào)成績(jī)表.課程編號(hào) 交叉聯(lián)接 例：交叉聯(lián)接系部表和課程信息表 Select * from 系部表,課程信息表 Select * from 系部表 cross join 課程信息表 注:如果在交叉聯(lián)接后面添加一個(gè)where子句,它的作用就跟內(nèi)聯(lián)接一樣了.,34, 5.3 聯(lián)接查詢,自聯(lián)接: 例 查學(xué)生基本信息表中,同名同姓的情況 Select a1.* from 學(xué)生基本信息表 as a1,學(xué)生基本信息表 as a2 Where a1.姓名=a2.姓名 and a1.學(xué)號(hào)a2.學(xué)號(hào) 思考：查詢課程信息表中同一門課程任課教師情況 Select a1.* from 課程信息表 as a1 inner join 課程信息表 as a2 on a1.課程名稱=a2.課程名稱 and a1.課程編號(hào)a2.課程編號(hào),35, 5.3 外聯(lián)接例題,成績(jī)表,檔案表,查詢學(xué)生的學(xué)號(hào)，姓名，成績(jī)，通過三種聯(lián)接方式，請(qǐng)說出左聯(lián)接，右聯(lián)接，完整外部聯(lián)接的結(jié)果,36, 5.3 外聯(lián)接例題,例：用DAN表和CJ表分別左聯(lián)接、右聯(lián)接、完整聯(lián)接。 左聯(lián)接 SELECT DAN.學(xué)號(hào),姓名,成績(jī) FROM DAN LEFT OUTER JOIN CJ ON DAN.學(xué)號(hào)=CJ.學(xué)號(hào),右聯(lián)接 SELECT CJ.學(xué)號(hào),姓名,成績(jī) FROM DAN RIGHT OUTER JOIN CJ ON DAN.學(xué)號(hào)=CJ.學(xué)號(hào),37, 5.3 外聯(lián)接例題,完整外部聯(lián)接 SELECT DAN.學(xué)號(hào),姓名,成績(jī) FROM DAN FULL OUTER JOIN CJ ON DAN.學(xué)號(hào)=CJ.學(xué)號(hào),38, 5.3 聯(lián)接查詢,5、 多表聯(lián)接 雖然每個(gè)聯(lián)接規(guī)范只聯(lián)接兩個(gè)表，但 FROM 子句可包含多個(gè)聯(lián)接規(guī)范。這樣一個(gè)查詢可以聯(lián)接若干個(gè)表。 例:利用課程信息表,成績(jī)表, 學(xué)生基本信息表顯示學(xué)生的學(xué)號(hào),姓名,課程名稱,成績(jī) select 成績(jī)表.學(xué)號(hào),姓名, 課程名稱,成績(jī) from 課程信息表 inner join 成績(jī)表 on 課程信息表.課程編號(hào) = 成績(jī)表.課程編號(hào) inner join 學(xué)生基本信息表 on 成績(jī)表.學(xué)號(hào)=學(xué)生基本信息表.學(xué)號(hào),39, 5.4 子查詢,4、 子查詢基礎(chǔ) 子查詢是一個(gè) SELECT 查詢，它返回單個(gè)值且嵌套在 SELECT、INSERT、UPDATE、DELETE 語句或其它子查詢中。任何允許使用表達(dá)式的地方都可以使用子查詢 注意事項(xiàng): 1.通過比較運(yùn)算符引入的子查詢的選擇列表只能包括一個(gè)表達(dá)式或列名稱. 2.使用外部查詢的WHERE子句包括某個(gè)列名,則該子句必須與子查詢選擇列表中的該列在聯(lián)接上兼容 3.由于必須返回單個(gè)值,所以由于修改的比較運(yùn)算符引入的子查詢不能包括GROUP BY 和HAVING子句 4.包括GROUP BY 的子查詢不能使用DISTINCT關(guān)鍵字 5.只有同時(shí)指定了TOP,才可以指定ORDER BY 6.約定通過EXISTS引入的子查詢的選擇列由*組成,不使用單個(gè)列名,40, 5.4 子查詢,例：用子查詢的形式，顯示學(xué)生的姓名和平均分。 Select 學(xué)號(hào),姓名,(select avg(成績(jī)) from 成績(jī)表 where 成績(jī)表.學(xué)號(hào)=學(xué)生基本信息表.學(xué)號(hào)) as 平均成績(jī) from 學(xué)生基本信息表,41, 5.4 子查詢,2 EXIST與IN子查詢 例：使用子查詢從學(xué)生基本信息表中顯示年齡比所有回族學(xué)生都大的學(xué)生姓名,( select 學(xué)號(hào) from 成績(jī)表 where 成績(jī)60),例:顯示成績(jī)不及格的學(xué)生姓名. Select distinct 姓名 from 學(xué)生基本信息表 where 學(xué)號(hào) in,( select 出生日期 from 學(xué)生基本信息表 where 族別=回族),select