1、,SQL Server 2000管理及 應用開發(fā)教程,授課教師： 職務：,第8章 常用數(shù)據(jù)操縱語言（DML）,課程描述 數(shù)據(jù)操縱語言（DML）包含了用來查詢、添加、修改和刪除數(shù)據(jù)庫中數(shù)據(jù)的語句，這些語句包括SELECT、INSERT、UPDATE和DELETE等。本章將介紹使用這些SQL語句操縱數(shù)據(jù)庫中數(shù)據(jù)的方法。,本章知識點,數(shù)據(jù)查詢語句 插入數(shù)據(jù)語句 修改數(shù)據(jù)語句 刪除數(shù)據(jù)語句,8.1 數(shù)據(jù)查詢語句,SELECT語句語法簡介 準備演示數(shù)據(jù) 簡單SELECT語句 使用分組統(tǒng)計 生成匯總行 連接查詢 子查詢 合并查詢 保存查詢結果,8.1.1 SELECT語句語法簡介,SELECT子句 INT

2、O 子句 FROM 子句 WHERE 子句 GROUP BY 子句 HAVING 子句 ORDER BY 子句 例如： SELECT * FROM EmpInfo,SELECT語句中各子句的說明,8.1.2 準備演示數(shù)據(jù),本節(jié)使用數(shù)據(jù)庫HrSystem中表DepInfo和表EmpInfo中的數(shù)據(jù)來演示表的查詢操作。,表DepInfo中的演示數(shù)據(jù),表EmpInfo中的演示數(shù)據(jù),8.1.3 簡單SELECT語句,SELECT子句是SELECT語句的關鍵部分。它的作用是指定由查詢返回的列。 SELECT子句的語法結構如下： SELECT ALL | DISTINCT TOP n PERCENT WI

3、TH TIES ,參數(shù)說明,1使用DISTINCT關鍵字,DISTINCT關鍵字的作用是指定結果集中返回指定列存在不重復數(shù)據(jù)的記錄。 【例8.1】查詢表EmpInfo中所有的職務數(shù)據(jù)，可以使用以下命令： USE HrSystem GO SELECT Title FROM EmpInfo GO,如果使用DISTINCT,USE HrSystem GO SELECT DISTINCT Title FROM EmpInfo GO,例8.1的運行結果,2使用TOP n PERCENT關鍵字,如果只需要顯示前n行，可以在SELECT語句中使用TOP n PERCENT關鍵字。 【例8.2】查詢表EmpI

4、nfo中的前3個員工記錄，具體語句如下： USE HrSystem GO SELECT TOP 3 Emp_Name FROM EmpInfo GO,例8.2的運行結果,3改變顯示的列標題,USE HrSystem GO SELECT TOP 3 Emp_Name as 姓名, Title as 職務 FROM EmpInfo GO,4設置查詢條件,WHERE子句指定用于限制返回的行的搜索條件。它的語法結構如下： WHERE | := column_name * = | = * column_name 參數(shù) 是通過使用謂詞限制結果集內返回的行。對搜索條件中可以包含的謂詞數(shù)量沒有限制。,【例8.

5、3】,表EmpInfo中查詢所有工資大于3000而且小于4000的記錄，具體語句如下： USE HrSystem GO SELECT Emp_Name, Wage FROM EmpInfo WHERE Wage 3000 AND Wage 4000 GO,SQL Server的通配符及其含義,【例8.4】,要查詢所有身份證號以110開頭的員工記錄，可以使用以下語句： USE HrSystem GO SELECT * FROM EmpInfo WHERE IDCard LIKE 110% GO,【例8.5】,要查詢所有身份證號以110123開頭、中間包含任意一個字符、其后為adx、最后包含任意一

6、個字符的員工記錄（即身份證格式為110123?adx?，?表示任意一個字符），可以使用以下語句： USE HrSystem GO SELECT * FROM EmpInfo WHERE IDCard LIKE 110123_adx_ GO,5排序結果集,在SELECT語句中使用ORDER BY子句可以指定結果集的排序。它的語法結構如下： ORDER BY order_by_expression ASC | DESC ,.n 參數(shù)order_by_expression指定要排序的列。 ASC表示按照遞增的順序排列，DESC表示按照遞減的順序排列。 ASC為默認值。,【例8.6】,如果按照員工姓名

7、排序，可以使用以下命令： USE HrSystem GO SELECT * FROM EmpInfo ORDER BY Emp_Name GO,8.1.4 使用分組統(tǒng)計,在統(tǒng)計查詢中，可以使用GROUP BY子句指定查詢結果的分組條件。它的語法結構如下： GROUP BY ALL group_by_expression ,.n WITH CUBE | ROLLUP ,【例8.7】,要查詢各職位的平均工資，可以使用以下語句： USE HrSystem GO SELECT Title AS 職位, AVG(Wage) AS 平均公資 FROM EmpInfo GROUP BY Title GO,【

8、例8.8】,在下面的SELECT語句中，GROUP BY子句中不包含Sex列，而Sex列出現(xiàn)在SELECT子句中。因此，在執(zhí)行SELECT語句時會出現(xiàn)錯誤。 USE HrSystem GO SELECT Sex, Title, AVG(Wage) FROM EmpInfo GROUP BY Title GO,【例8.9】,查詢平均工資大于4400.00元的部門，并顯示部門編號和平均工資，具體語句如下： USE HrSystem GO SELECT Dep_Id, AVG(Wage) FROM EmpInfo GROUP BY Dep_Id HAVING AVG(Wage) 4400 GO,8.

9、1.5 生成匯總行,COMPUTE子句的語法結構如下： COMPUTE AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM ( expression ) ,.n BY expression ,.n ,【例8.10】,要查詢所有員工的工資，并對工資總額進行匯總，可以使用以下命令： USE HrSystem GO SELECT Emp_Name, Wage FROM EmpInfo COMPUTE SUM(Wage) GO,8.1.6 連接查詢,內連接 外連接 交叉連接,1內連接,內連接使用比較運算符（最常使用的是等號，即等值連接

10、）根據(jù)每個表共有列的值匹配兩個表中的行。 只有每個表中都存在相匹配列值的記錄才出現(xiàn)在結果集中。 在內連接中，所有表是平等的，沒有主次之分。,【例8.11】,使用內連接從表EmpInfo和表DepInfo中同時獲取數(shù)據(jù)，查詢所有員工的姓名和所在部門名稱，具體語句如下： USE HrSystem GO SELECT t1.Dep_name, t2.Emp_name FROM DepInfo t1, EmpInfo t2 WHERE t1.Dep_id=t2.Dep_id,例8.11的運行結果,使用INNER JOIN關鍵字,USE HrSystem GO SELECT t1.Dep_name, t

11、2.Emp_name FROM DepInfo t1 INNER JOIN EmpInfo t2 ON t1.Dep_id=t2.Dep_id,2外連接,左向外連接以連接（JOIN）子句左側的表為主表，主表中所有記錄都將出現(xiàn)在結果集中。如果主表中的記錄在右表中沒有匹配的數(shù)據(jù)，則結果集中右表的列值為NULL?？梢允褂?=符號定義左連接。 右向外連接以連接（JOIN）子句右側的表為主表，主表中所有記錄都將出現(xiàn)在結果集中。如果主表中的記錄在左表中沒有匹配的數(shù)據(jù)，則結果集中右表的列值為NULL?？梢允褂?*符號定義左連接。 完整外部連接包括連接表中的所有行，無論它們是否匹配。在SQL Server 2

12、000中，還可以使用FULL OUTER JOIN或FULL JOIN關鍵字定義完整外部連接。,【例8.12】,使用左向外連接從表DepInfo和表EmpInfo中同時獲取數(shù)據(jù)，查詢所有員工的姓名和所在部門名稱，其中表DepInfo為主表，具體語句如下： USE HrSystem GO SELECT t1.Dep_name, t2.Emp_name FROM DepInfo t1, EmpInfo t2 WHERE t1.Dep_id *= t2.Dep_id GO,LEFT OUTER JOIN或LEFT JOIN關鍵字,USE HrSystem GO SELECT t1.Dep_name,

13、 t2.Emp_name FROM DepInfo t1 LEFT JOIN EmpInfo t2 ON t1.Dep_id=t2.Dep_id GO,【例8.13】,使用右向外連接從表DepInfo和表EmpInfo中同時獲取數(shù)據(jù)，查詢所有員工的姓名和所在部門名稱，其中表EmpInfo為主表，具體語句如下： USE HrSystem GO SELECT t1.Dep_name, t2.Emp_name FROM DepInfo t1, EmpInfo t2 WHERE t2.Dep_id =* t1.Dep_id GO,RIGHT OUTER JOIN或RIGHT JOIN關鍵字,USE H

14、rSystem GO SELECT t1.Dep_name, t2.Emp_name FROM EmpInfo t2 RIGHT JOIN DepInfo t1 ON t1.Dep_id=t2.Dep_id GO,【例8.14】,使用完整外部連接從表DepInfo和表EmpInfo中同時獲取數(shù)據(jù)，查詢所有員工的姓名和所在部門名稱，具體語句如下： USE HrSystem GO SELECT t1.Dep_name, t2.Emp_name FROM EmpInfo t2 FULL JOIN DepInfo t1 ON t1.Dep_id=t2.Dep_id GO,3交叉連接,在交叉連接查詢中，

15、兩個表中的每兩行都可能互相組合成為結果集中的一行。交叉連接并不常用，除非需要窮舉兩個表的所有可能的記錄組合。,【例8.15】,使用交叉連接從表DepInfo和表EmpInfo中同時獲取數(shù)據(jù)，查詢所有員工的姓名和所在部門名稱，具體語句如下： USE HrSystem GO SELECT t1.Dep_name, t2.Emp_name FROM EmpInfo t2 CROSS JOIN DepInfo t1 GO,8.1.7 子查詢,【例8.16】查詢辦公室的所有員工，可以使用以下命令： USE HrSystem GO SELECT Emp_Name FROM EmpInfo WHERE De

16、p_Id = (SELECT Dep_Id FROM DepInfo WHERE Dep_name = 辦公室) GO,8.1.8 合并查詢,使用UNION關鍵字可以組合兩個查詢的結果集，結合的基本規(guī)則是： （1）所有查詢中的列數(shù)和列的順序必須相同。 （2）數(shù)據(jù)類型必須兼容。,【例8.17】,從表DepInfo中查詢各部門信息，然后在從表EmpInfo中查詢各個部門的部門經(jīng)理，具體語句如下： USE HrSystem GO SELECT Dep_Id, Dep_Name FROM DepInfo UNION SELECT Dep_Id, Emp_Name FROM EmpInfo WHERE

17、Title = 部門經(jīng)理 GO,8.1.9 保存查詢結果, INTO ,【例8.18】,將辦公室的所有員工的姓名和職務信息保存到表Office中，可以使用以下命令： USE HrSystem GO SELECT e.Emp_Name, e.Title INTO Office FROM EmpInfo e, DepInfo d WHERE e.Dep_id = d.Dep_Id AND d.Dep_Name = 辦公室 GO SELECT * FROM Office GO,例8.18的運行結果,8.2 數(shù)據(jù)更新語句,插入數(shù)據(jù)語句INSERT 修改數(shù)據(jù)語句UPDATE 刪除數(shù)據(jù)語句DELETE,8

18、.2.1 插入數(shù)據(jù)語句,INSERT語句的基本語法結構如下： INSERT INTO (列名1, 列名2, , 列名n) VALUES (值1, 值2, , 值n); 列名1, 列名2, , 列名n必須是指定表名中定義的列，而且必須和VALUES子句中的 值1, 值2, , 值n一一對應，數(shù)據(jù)類型相同。,【例8.19】,向表DepInfo中插入記錄“公關部”，可以使用以下命令： USE HrSystem GO INSERT INTO DepInfo VALUES(公關部) GO SELECT * FROM DepInfo GO,【例8.20】,向表EmpInfo中插入新員工記錄，初始值包括員工

19、姓名、性別、職務和部門編號等，具體語句如下： USE HrSystem GO INSERT INTO EmpInfo (Emp_Name, Sex, Title, Dep_Id) VALUES (小明, 男, 職員, 2) GO,8.2.2 修改數(shù)據(jù)語句,可以使用UPDATE命令修改表中的現(xiàn)有數(shù)據(jù)。UPDATE語句的基本語法結構如下： UPDATE 表名 SET 列名1=值1 , 列名2=值2, ., 列名n=值n WHERE 更新條件,【例8.21】,使用UPDATE語句將表EmpInfo中所有記錄的工資增加10%，具體語句如下： USE HrSystem GO UPDATE EmpInfo

20、 SET Wage=Wage*1.1 GO,【例8.22】,使用UPDATE語句將表EmpInfo所有部門為“辦公室”的員工工資增加10%。 具體語句如下： USE HrSystem GO UPDATE EmpInfo SET Wage=Wage*1.1 WHERE Dep_id = (SELECT Dep_id FROM DepInfo WHERE Dep_name = 辦公室) GO,1不允許設置標識列的值,【例8.23】在表DepInfo中，列Dep_Id被設置為標識列，其編號由系統(tǒng)自動生成。在UPDATE語句中設置該列的值，具體語句如下： USE HrSystem GO UPDATE

21、DepInfo SET Dep_id = 1 WHERE Dep_id = 2 GO 執(zhí)行結果如下： 服務器: 消息 8102，級別 16，狀態(tài) 1，行 1 無法更新標識列 Dep_id。,2不允許向惟一性約束列中插入相同的數(shù)據(jù),【例8.24】在表EmpInfo中，假定列Emp_name被設置為唯一性約束。表EmpInfo中存在姓名為“張三”和“李四”的兩條記錄。使用UPDATE語句將姓名為“李四”的記錄“姓名”值修改為“張三”，具體語句如下： USE HrSystem GO UPDATE EmpInfo SET Emp_name=張三 WHERE Emp_name=李四 GO 執(zhí)行結果如下： 服務器: 消息 2627，級別 14，狀態(tài) 2，行 1 違反了 UNIQUE KEY 約束 IX_EmpInfo。不能在對象 EmpInfo 中插入重復鍵。 語句已終止。,3不能違反檢查約束,【例8.25】在表EmpInfo中，假定列Wage被設置為檢查約束，檢查條件為“Wage=0”。試使用UPDATE語句將張三的工資修改為-1，具體語句如下： USE HrSystem GO UPDATE EmpInfo SET Wage=-1 WHERE Emp_Name=張三 GO 執(zhí)行結果如下： 服務器: