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

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