數(shù)據(jù)表的管理31表的創(chuàng)建及修改.pptVIP

第3章 數(shù)據(jù)表的管理,3.1表的創(chuàng)建及修改,3.3 表中數(shù)據(jù)的檢索,3.2表中數(shù)據(jù)的修改,主要內(nèi)容,了解SQL Server 2005的數(shù)據(jù)類型 掌握表的創(chuàng)建和表結(jié)構(gòu)的修改方法 掌握表中數(shù)據(jù)的插入、刪除和修改操作 掌握表中數(shù)據(jù)的檢索方法,3.1表的創(chuàng)建及修改,3.1.1 數(shù)據(jù)類型 數(shù)據(jù)類型是數(shù)據(jù)庫的重要特性之一，SQL Server提供了多種系統(tǒng)數(shù)據(jù)類型，也可以由用戶自定義數(shù)據(jù)類型。 邏輯類型 整數(shù)類型 浮點類型 字符類型 二進制類型 時間數(shù)據(jù)類型 貨幣數(shù)據(jù)類型,3.1.2 創(chuàng)建表 在SQL Server 2005中提供兩種創(chuàng)建表的方式：一種是在SQL Server Management Studio中創(chuàng)建表，另一種方式是通過執(zhí)行T-SQL語句創(chuàng)建表。 1在SQL Server Management Studio中創(chuàng)建表 2用T-SQL語句創(chuàng)建表 例 3-1創(chuàng)建一個教師表 CREAT TABLE 教師表 (教師編號 INT IDENTITY(1,1) NOT NULL, /*設(shè)置標識規(guī)范*/ 教師姓名 CHAR(8) NOT NULL, 職稱 CHAR(6) DEFAULT 講師, /*設(shè)置默認值約束*/ 專業(yè)方向 CHAR(20), 系 CHAR(10), 聯(lián)系方式 CHAR(30) GO,3.1.3 修改表結(jié)構(gòu) 1在SQL Server Management Studio中修改表 2用T-SQL語句修改表:ALTER TABLE 例 3-2 要修改教師表的教師編號為6位字符，刪除職稱列： ALTER TABLE 教師表 ALTER COLUMN 教師編號 char（6） GO ALTER TABLE 教師表 DROP COLUMN 職稱 GO,3.1.4 刪除表,將表從數(shù)據(jù)庫中刪除，不僅刪除了表的結(jié)構(gòu)，還包括表中的所有對象，該表的結(jié)構(gòu)定義、數(shù)據(jù)、全文索引、約束和索引都從數(shù)據(jù)庫中永久刪除。 如果要刪除通過 FOREIGN KEY 和 UNIQUE 或 PRIMARY KEY 約束相關(guān)聯(lián)的表，則必須先刪除具有 FOREIGN KEY 約束的表。如果要刪除 FOREIGN KEY 約束中引用的表但不能刪除整個外鍵表，則必須刪除 FOREIGN KEY 約束。,3.1.4 刪除表,1在SQL Server Management Studio中刪除表 2用T-SQL語句刪除表 刪除表的T-SQL語句是：DROP TABLE table_name 例 3-3 刪除數(shù)據(jù)庫“教務(wù)管理”中的“成績”表 DROP TABLE 成績,3.1.5 臨時表,臨時表存儲在系統(tǒng)數(shù)據(jù)庫 tempdb 中，當不再使用時會自動刪除。 臨時表有兩種類型：本地的和全局的。 本地臨時表的名稱以單個數(shù)字符號 “#”打頭，它們僅對當前的用戶連接是可見的，當用戶從 SQL Server 實例斷開連接時被刪除。 全局臨時表的名稱以兩個數(shù)字符號“#”打頭，創(chuàng)建后對任何用戶都是可見的，當所有引用該表的用戶從 SQL Server 斷開連接時被刪除。,3.2表中數(shù)據(jù)的修改 3.2.1 表中數(shù)據(jù)的插入 INSERT 語句可向表中添加一個或多個新行。其語法格式如下： INSERT INTO table_or_view (column_list) VALUES(data_values,n) 使用INSERTSELECT語句還可以將一個表中的數(shù)據(jù)插入到另一個表中，例如： INSERT 課程 SELECT * FROM 基礎(chǔ)課程表,3.2.2 表中數(shù)據(jù)的刪除,在對象資源管理器中通過菜單命令刪除表； 使用刪除語句DELETE也可實現(xiàn)對記錄的刪除。 語法格式： DELETE table_or_view FROM table_sources WHERE search_condition,3.2.3 表中數(shù)據(jù)的字段值的修改,UPDATE 語句可以更改表或視圖中單行、行組或所有行的數(shù)據(jù)值。其語法格式如下： UPDATE table_name SET column_name=expression|DEFAULT|NULL ,n FROM tablelist WHERE condition 例 3-5 設(shè)置課程號小于“199”的課學分為4分 UPDATE 課程 SET 學分=4 WHERE 課程號199,3.2.4 數(shù)據(jù)關(guān)系圖的使用,數(shù)據(jù)庫關(guān)系圖設(shè)計器,3.3.1 SELECT語句概述 數(shù)據(jù)的查詢是通過SELECT語句完成的。的主要子句可歸納如下： SELECT select_list INTO new_table_name FROM table_list WHERE search_conditions GROUP BY group_by_list HAVING search_conditions ORDER BY order_list ASC | DESC ,3.3 表中數(shù)據(jù)的檢索,3.3.2 列的檢索 選擇列是限定返回某些列組成結(jié)果集。 1選擇所有列 選擇所有的列，既可以在查詢中逐一地列示出所有的列名，也可以使用符號“*”。 例 3-6 查詢本校教師信息 SELECT * FROM 本校教師 2選擇指定的列 若要選擇表中的特定列，應在選擇列表中明確地列出每一列，在列與列之間用逗號隔開。 例：SELECT 教師編號，教師姓名，系，專業(yè)方向，職稱 FROM 本校教師,行的檢索可以將通過限定條件返回結(jié)果的行組成結(jié)果集。 1使用DISTINCT消除重復行 使用DISTINCT消除重復行的語法格式如下： DISTINCT column_name,column_name DISTINCT關(guān)鍵字對其后面所指定的列消除重復行。一個SELECT語句中只能有一個DISTINCT，而且必須放在所有的列名之前。,3.3.3 行的檢索,2使用 TOP 子句限制結(jié)果集中返回的行數(shù) TOP 子句指定返回的行數(shù)，語法格式為： TOP ( expression ) PERCENT WITH TIES 其中，expression 是指定返回行數(shù)的數(shù)值表達式，如果指定了 PERCENT，則是指返回的結(jié)果集行的百分比（由 expression 指定）。,3.3.3 行的檢索,3使用WHERE子句限定返回行的條件 在SELECT語句中使用WHERE子句指定查詢的條件，使系統(tǒng)將符合條件的行作為結(jié)果。 WHERE子句的語法格式： WHERE AND AND |OR 1）比較搜索條件：在WHERE子句中對表達式進行比較，可使用比較運算符和邏輯運算符。,3.3.3 行的檢索,3使用WHERE子句限定返回行的條件 WHERE子句的語法格式： WHERE AND AND |OR 2）范圍搜索條件：范圍搜索返回介于兩個指定值之間的所有值。在SELECT語句中用BETWEEN 關(guān)鍵字指定要搜索的包括范圍。 例 3-13 查詢成績在60分到70分之間的學號、課程和成績： use 教務(wù)管理 SELECT 學號,課程號,成績 FROM 成績 WHERE 成績 BETWEEN 60 AND 70,3.3.3 行的檢索,3使用WHERE子句限定返回行的條件 WHERE子句的語法格式： WHERE AND AND |OR 3）列表搜索條件：在WHERE子句中使用關(guān)鍵字IN或OR運算符指定可選的取值。 例 3-14 查詢成績表中“高等數(shù)學”和“大學語文”課程的成績（課程號分別為101和102）。 use 教務(wù)管理 SELECT 學號,課程號,成績 FROM 成績 WHERE 課程號=101 OR 課程號=102 或 use 教務(wù)管理 SELECT 學號,課程號,成績 FROM 成績 WHERE 課程號 IN (101, 102),3.3.3 行的檢索,3使用WHERE子句限定返回行的條件 WHERE子句的語法格式： WHERE AND AND |OR 4）搜索條件中的模式匹配：使用LIKE關(guān)鍵字來限定模式匹配查詢。 LIKE子句中可使用的通配符,3.3.3 行的檢索,3使用WHERE子句限定返回行的條件 WHERE子句的語法格式： WHERE AND AND |OR 5）NULL值的處理：空值表示值未知?？罩挡煌诳瞻谆蛄阒?。沒有兩個相等的空值。若要在查詢中測試空值，可在 WHERE 子句中使用 IS NULL 或 IS NOT NULL。 例 3-17 查詢成績表中無成績的學號和課程。 USE 教務(wù)管理 SELECT 學號,課程號 FROM 成績 WHERE 成績 IS NULL GO,3.3.3 行的檢索,3.3.4 數(shù)據(jù)算術(shù)運算 在對表進行查詢時，有時需要對所查詢的某些列使用表達式進行計算，SELECT語句支持表達式的使用。 通過在帶有算術(shù)運算符、函數(shù)、轉(zhuǎn)換或嵌套查詢的選擇列表中使用數(shù)值列或數(shù)值常量，可以對數(shù)據(jù)進行計算和運算。算術(shù)運算符允許對數(shù)值數(shù)據(jù)進行加、減、乘、除運算。 查詢語句中支持的算術(shù)運算符包括：+，-，*，/，%（模運算）。 例：以計算結(jié)果作為派生列輸出,3.3.5 數(shù)據(jù)轉(zhuǎn)換 有時在查詢中需要將結(jié)果以另外一種形式顯示出來，例如成績的顯示，有時需要分數(shù)有時需要等級，如“優(yōu)秀”、“及格”等。可以使用CASE子句指定數(shù)據(jù)的轉(zhuǎn)換。,例 3-19 查詢學生成績，以“優(yōu)秀”、“良好”、“合格”和“不及格”的等級顯示結(jié)果。 USE 教務(wù)管理 SELECT 學號,課程號,成績,等級= CASE WHEN 成績=85 THEN 優(yōu)秀 WHEN 成績=70 AND 成績=60 AND 成績70 THEN 合格 WHEN 成績60 THEN 不及格 END FROM 成績 GO,3.3.6 函數(shù)的應用 SELECT查詢也可以放在用戶自定義的函數(shù)中，通過調(diào)用函數(shù)執(zhí)行數(shù)據(jù)庫查詢。 例 3-20 定義一個函數(shù)來計算全體學生某門課程的平均分數(shù)。通過調(diào)用這個函數(shù)來查詢“101”、“201”和“301”號的課程的平均分。 CREATE FUNCTION averagescore(course char(3) RETURNS INT AS BEGIN DECLARE avg_score INT SELECT avg_score= (SELECT AVG(成績) FROM 成績 WHERE 課程號=course) RETURN avg_score END GO 例：調(diào)用函數(shù)： USE 教務(wù)管理 SELECT dbo.averagescore(101),3.3.7 數(shù)據(jù)匯總 在對表數(shù)據(jù)進行查詢時，經(jīng)常需要對結(jié)果進行匯總計算。可以使用聚集函數(shù)對數(shù)據(jù)進行計算。 例 3-20 統(tǒng)計選修“301”號課程的總分，平均分和最高分。 USE 教務(wù)管理 SELECT SUM(成績) AS 總成績,AVG(成績) AS 平均成績,MAX(成績) AS 最高成績 FROM 成績 WHERE 課程號=301 GO,常用聚集函數(shù),3.3.7 數(shù)據(jù)匯總 除使用聚集函數(shù)進行匯總外，還可以使用COMPUTE關(guān)鍵字對結(jié)果進行匯總計算。 COMPUTE可以生成合計作為附加的匯總列，出現(xiàn)在結(jié)果集的最后。 例 3-21 查詢家住江蘇省的學生，輸出學號、姓名和家庭住址，并統(tǒng)計學生人數(shù)。 USE 教務(wù)管理 SELECT 學號,姓名,家庭住址 FROM 學生 WHERE 家庭住址 LIKE 江蘇% COMPUTE COUNT(學號) GO,3.3.8 連接查詢 在實際查詢中，有時所需要的信息來自于不同的表，需要在查詢中將這些表進行連接方能得到完整的信息。 連接條件可通過以下方式定義兩個表在查詢中的關(guān)聯(lián)方式： 指定每個表中要用于聯(lián)接的列。典型的聯(lián)接條件在一個表中指定一個外鍵，而在另一個表中指定與其關(guān)聯(lián)的鍵。 指定用于比較各列的值的邏輯運算符（例如 = 或 ）。,3.3.8 連接查詢 1內(nèi)部連接 內(nèi)部聯(lián)接是使用比較運算符比較要聯(lián)接列中的值的聯(lián)接。使用FROMWHERE子句能夠?qū)⒈磉M行連接，語法格式為： SELECT column_name ,coluumn_name, /*指定輸出的列*/ FROM table_name ,table_name, /*指定連接的表*/ WHERE condition /*指定連接條件或選擇條件*/ 例 3-22 查詢學生姓名、所選課程和各科成績。 表及表結(jié)構(gòu)如下： 學生(學號,姓名,性別,身份證號,出生年月,聯(lián)系電話,民族,家庭住址,郵編) 課程(課程號,課程名,學時,學分) 成績(學號,課程號,成績),3.3.8 連接查詢 2外部連接 外部連接會返回 FROM 子句中提到的至少一個表或視圖中的所有行，只要這些行符合任何 WHERE 或 HAVING 搜索條件。 Microsoft SQL Server 2005 對 FROM 子句中指定的外部連接使用下列關(guān)鍵字： LEFT OUTER JOIN 或 LEFT JOIN RIGHT OUTER JOIN 或 RIGHT JOIN FULL OUTER JOIN 或 FULL JOIN 其中LEFT表示左外連接，RIGHT表示右外連接，F(xiàn)ULL表示完全外連接。,3.3.8 連接查詢 2外部連接 例：左外連接、右外連接、完全外連接。,左外連接查詢,右外連接查詢,完全外連接,3.3.8 連接查詢 3交叉連接 在FROM子句中關(guān)鍵字CROSS表示交叉連接，又稱為自然連接，即生成一個笛卡爾積。沒有 WHERE 子句的交叉連接將產(chǎn)生連接所涉及的表的笛卡爾積。第一個表的行數(shù)乘以第二個表的行數(shù)等于笛卡爾積結(jié)果集的大小。,交叉連接查詢,3.3.9 聯(lián)合查詢 UNION操作符將兩個或兩個以上的查詢結(jié)果全并為一個結(jié)果集。UNION語句的語法格式為： | () UNION ALL | () UNION ALL | () n 其中，指明了查詢的詳細說明或查詢表達式。UNION為合并操作符。ALL表示合并所有數(shù)據(jù)行到結(jié)果集中，包括值重復的數(shù)據(jù)行。如果不指定此選項，則重復的數(shù)據(jù)行只顯示一行。,3.3.9 聯(lián)合查詢 例 3-26查詢所有的教師姓名，包括本校教師和外聘教師。 表及表結(jié)構(gòu)為： 本校教師(教師編號,教師姓名,職稱,專業(yè)方向,系) 外聘教師(教師編號,教師姓名,職稱,專業(yè)方向,工作單位,聯(lián)系方式)查詢語句為：,3.3.10 嵌套查詢 嵌套查詢也稱“子查詢”。外部的SELECT語句稱為外圍查詢（也稱為父查詢），內(nèi)部的SELECT語句稱為子查詢。子查詢的結(jié)果將作為外圍查詢的參數(shù)使用。 1無關(guān)子查詢 無關(guān)子查詢，子查詢在外圍查詢之前進行，然后返回結(jié)果集供外圍查詢使用。對應于SQL語句來說，如果被嵌套的查詢中不包含對于外圍查詢的引用，則成為無關(guān)子查詢。最常用的無關(guān)子查詢方式是IN或NOT IN子句，其語法格式如下： SELECT select_list FROM t