第十章結構化查詢語言 SQL- 資料操作語言 (DML) 資料庫系統設計理論李紹綸著
本章內容 SQL 的資料異動部份 新增資料 修改資料 刪除資料 SQL 的資料查詢部份 查詢所有的欄位 :* 指定查詢欄位名稱 去除重複列錄 :DISTINCT 聯集運算 :UNION 差集運算 :EXCEPT 交叉聯結 :CROSS JOIN 使用查詢結果建立新資料表 :SELECT INTO 2
內部聯結 :INNER JOIN 別名運算 :AS 完全外部聯結 :FULL OUTER JOIN 左外部聯結 :LEFT OUTER JOIN 右外部聯結 :RIGHT OUTER JOIN 交集運算 :INTERSECT 除法運算 聚合函數 GROUP BY 子句 HAVING 子句 子查詢 虛值 NULL 的處理 字串的處理 介於之間運算 :BETWEEN AND ORDER BY 子句 COMPUTE 子句 本章內容 3
新增資料 INSERT INTO 指令敘述格式如下 : INSERT [ TOP ( expression ) [ PERCENT ] ] [ INTO ] [ server_name. database_name. schema_name. database_name.[ schema_name ]. schema_name. ] table_or_view_name [ ( column_list ) ] { VALUES ( { DEFAULT NULL expression } [,...n ] ) SELECT_statement } [ ; ] 4
範例 10.1 請在 公司 資料庫的 員工 資料表中新增一筆員工資料, 假設此新進員工郭信義的身分證號碼是 K111111111, 而且他是出生於民國 64 年 7 月 30 號, 且居住在台北市忠孝路 186 號 由於郭信義是新進員工, 所以他的薪水是 25000 同時因為是剛進公司, 所以他也沒有直屬上司和沒有被派至任何一個部門工作 5
將查詢的結果新增到一個資料表中 員工 和 部門 資料表內容 6
範例 10.2 請在 公司 資料庫中利用 CREATE TABLE 來建立一個名為部門資訊的資料表, 用來儲存關於每一個部門名稱 該部門的員工人數和該部門的薪水總合, 然後再利用 INSERT/SELECT 陳述式將相關資料新增至該資料表中 7
修改資料 UPDATE 指令敘述格式如下 : UPDATE [ TOP ( expression ) [ PERCENT ] ] [ server_name. database_name. schema_name. database_name.[ schema_name ]. schema_name. ] table_or_view_name SET { column_name = { expression DEFAULT NULL } } [,...n ] [ WHERE search_condition ] [ ; ] 8
範例 10.3 請在 公司 資料庫的 員工 資料表中將 身分證號碼 為 K111111111 的員工 地址 由 台北市忠孝路 186 號 改為 台北市忠孝路 286 號 9
範例 10.4 請將在資訊部門的每一位員工薪水都增加 1000 元 10
範例 10.5 請將在研發部門的每一位員工薪水都調薪增加 10% 11
刪除資料 DELETE 指令敘述格式如下 : DELETE [ TOP ( expression ) [ PERCENT ] ] [ FROM ] [ server_name. database_name. schema_name. database_name.[ schema_name ]. schema_name. ] table_or_view_name [ FROM table_source [,...n ] ] [ WHERE search_condition ] [ ; ] 12
範例 10.6 請在 公司 資料庫的 員工 資料表中將 身分證號碼 為 K111111111 的員工資料予以刪除 13
範例 10.7 請將在資訊部門的每一位非主管員工資料予以刪除 14
範例 10.8 請將在研發部門的每一位非主管員工資料予以刪除 15
範例 10.9 請在 公司 資料庫中刪除 員工 資料表中的所有資料 16
驗證何謂 參考完整性限制 外鍵 經理身分證號碼 自動被設定為虛值畫面 17
使用 TRUNCATE TABLE 截斷資料表 TRUNCATE TABLE 指令敘述格式如下 : TRUNCATE TABLE [ { database_name. [ schema_name ]. schema_name. } ] table_name [ ; ] 18
範例 10.10 請在 公司 資料庫中截斷一個名為 部門 的資料表 19
利用 SQL 建立 公司 資料庫各個資料表的資料 20
公司 資料庫各個資料表內容 21
SQL 的資料查詢部份 SELECT 陳述式的基本語法如下 : [ WITH <common_table_expression>] 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 expression ] <common_table_expression>::= expression_name [ ( column_name [,...n ] ) ] AS ( CTE_query_definition ) 22
SELECT 指令敘述格式如下 : SQL 的資料查詢部份 SELECT statement ::= [WITH <common_table_expression> [,...n]] <query_expression> [ ORDER BY { order_by_expression column_position [ ASC DESC ] } [,...n ] ] [ COMPUTE { { AVG COUNT MAX MIN SUM } ( expression ) } [,...n ] [ BY expression [,...n ] ] ] [ <FOR Clause>] [ OPTION ( <query_hint> [,...n ] ) ] <query expression> ::= { <query specification> ( <query expression> ) } [ { UNION [ ALL ] EXCEPT INTERSECT } <query specification> ( <query expression> ) [...n ] ] <query specification> ::= SELECT [ ALL DISTINCT ] [TOP expression [PERCENT] [ WITH TIES ] ] < select_list > [ INTO new_table ] [ FROM { <table_source> } [,...n ] ] [ WHERE <search_condition> ] [ GROUP BY [ ALL ] group_by_expression [,...n ] [ WITH { CUBE ROLLUP } ] ] [ HAVING < search_condition > ] 23
SELECT 子句 SELECT 指令敘述格式 SELECT [ ALL DISTINCT ] [ TOP expression [ PERCENT ] [ WITH TIES ] ] <select_list> <select_list> ::= { * { table_name view_name table_alias }.* { column_name [ ] expression $IDENTITY $ROWGUID } udt_column_name [ {. :: } { { property_name field_name } method_name(argument [,...n] ) } ] [ [ AS ] column_alias ] column_alias = expression } [,...n ] 24
SELECT 指令敘述格式 FROM [ FROM { <table_source> } [,...n ] ] <table_source> ::= { table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] [ WITH ( < table_hint > [ [, ]...n ] ) ] rowset_function [ [ AS ] table_alias ] [ ( bulk_column_alias [,...n ] ) ] user_defined_function [ [ AS ] table_alias ] OPENXML <openxml_clause> derived_table [ AS ] table_alias [ ( column_alias [,...n ] ) ] <joined_table> <pivoted_table> <unpivoted_table> } <tablesample_clause> ::= TABLESAMPLE [SYSTEM] ( sample_number [ PERCENT ROWS ] ) [ REPEATABLE ( repeat_seed ) ] <joined_table> ::= { <table_source> <join_type> <table_source> ON <search_condition> <table_source> CROSS JOIN <table_source> left_table_source { CROSS OUTER } APPLY right_table_source [ ( ] <joined_table> [ ) ] } 25
SELECT 指令敘述格式 <join_type> ::= [ { INNER { { LEFT RIGHT FULL } [ OUTER ] } } [ <join_hint> ] ] JOIN <pivoted_table> ::= table_source PIVOT <pivot_clause> table_alias <pivot_clause> ::= ( aggregate_function ( value_column ) FOR pivot_column IN ( <column_list> ) ) <unpivoted_table> ::= table_source UNPIVOT <unpivot_clause> table_alias <unpivot_clause> ::= ( value_column FOR pivot_column IN ( <column_list> ) ) <column_list> ::= column_name [,... ] 26
SELECT 指令敘述格式 WHERE [ WHERE <search_condition> ] 27
SELECT 指令敘述格式 GROUP BY [ GROUP BY [ ALL ] group_by_expression [,...n ] [ WITH { CUBE ROLLUP } ] ] 28
SELECT 指令敘述格式 HAVING [ HAVING <search condition> ] < search_condition > ::= { [ NOT ] <predicate> ( <search_condition> ) } [ { AND OR } [ NOT ] { <predicate> ( <search_condition> ) } ] [,...n ] <predicate> ::= { expression { = < >! = > > =! > < < =! < } expression string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_character' ] expression [ NOT ] BETWEEN expression AND expression expression IS [ NOT ] NULL CONTAINS ( { column * }, '< contains_search_condition >' ) FREETEXT ( { column * }, 'freetext_string' ) expression [ NOT ] IN ( subquery expression [,...n ] ) expression { = < >! = > > =! > < < =! < } { ALL SOME ANY} ( subquery ) EXISTS ( subquery ) } 29
SELECT 指令敘述格式 UNION { <query specification> ( <query expression> ) } UNION [ ALL ] <query specification ( <query expression> ) [ UNION [ ALL ] <query specification> ( <query expression> ) [...n ] ] 30
EXCEPT 和 INTERSECT { <query_specification> ( <query_expression> ) } { EXCEPT INTERSECT } { <query_specification> ( <query_expression> ) } SELECT 指令敘述格式 31
ORDER BY 子句 SELECT 指令敘述格式 [ ORDER BY { order_by_expression [ COLLATE collation_name ] [ ASC DESC ] } [,...n ] ] 32
SELECT 指令敘述格式 COMPUTE [ COMPUTE { { AVG COUNT MAX MIN STDEV STDEVP VAR VARP SUM } ( expression ) } [,...n ] [ BY expression [,...n ] ] ] 33
SELECT 指令敘述格式 FOR 子句 [ FOR { BROWSE <XML> } ] <XML> ::= XML { { RAW [ ( 'ElementName' ) ] AUTO } [ <CommonDirectives> [, { XMLDATA XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] } ] [, ELEMENTS [ XSINIL ABSENT ] ] EXPLICIT [ <CommonDirectives> [, XMLDATA ] ] PATH [ ( 'ElementName' ) ] [ <CommonDirectives> [, ELEMENTS [ XSINIL ABSENT ] ] ] } <CommonDirectives> ::= [, BINARY BASE64 ] [, TYPE ] [, ROOT [ ( 'RootName' ) ] ] 34
SELECT 指令敘述格式 OPTION 子句 [ OPTION ( <query_hint> [,...n ] ) ] <query_hint > ::= { { HASH ORDER } GROUP { CONCAT HASH MERGE } UNION { LOOP MERGE HASH } JOIN FAST number_rows FORCE ORDER MAXDOP number_of_processors OPTIMIZE FOR ( @variable_name = literal_constant [, n ] ) PARAMETERIZATION { SIMPLE FORCED } RECOMPILE ROBUST PLAN KEEP PLAN KEEPFIXED PLAN EXPAND VIEWS MAXRECURSION number USE PLAN N'xml_plan' } 35
範例 10.11 查詢有哪些員工的薪水是高於 40000 元 36
範例 10.12 查詢有哪些員工的薪水是高於 40000 元且生日是在民國 60 年以前出生 37
查詢每一位員工的姓名和其薪水所得 範例 10.13 38
查詢每一位員工的薪水所得 範例 10.14 39
範例 10.15 查詢出有參與 1 號計劃或 2 號計劃的員工身分證號碼 40
範例 10.16 查詢出有參與 1 號計劃但沒有參與 2 號計劃的員工身分證號碼 41
範例 10.17 查詢出有參與 2 號計劃但沒有參與 1 號計劃的員工身分證號碼 42
範例 10.18 查詢出所有員工與部門的資訊, 並將其結果儲存於員工部門的資料表 43
範例 10.19 查詢出所有員工與部門的資訊, 並將其結果儲存於員工部門 1 的資料表 44
範例 10.20 查詢出所有員工與部門的資訊, 並將其結果儲存於員工部門 2 的資料表 45
範例 10.21 查詢出所有擁有眷屬的員工並列出其員工與員工眷屬的相關資料, 並將其結果儲存於員工眷屬 1 的資料表 46
範例 10.22 查詢出所有員工的姓名以及每位員工的直屬上司姓名, 並將其結果儲存於員工直屬上司的資料表 47
範例 10.23 查詢出所有員工的姓名以及每位員工擁有的眷屬姓名, 如果該名員工沒有任何眷屬, 則該名員工之眷屬姓名屬性需以虛值 (Null) 呈現, 並將其結果儲存於員工眷屬 2 的資料表 48
範例 10.24 查詢出所有員工的姓名以及每位員工的直屬上司姓名, 如果該名員工沒有任何直屬上司, 則該名員工之直屬上司姓名屬性需以虛值 (Null) 呈現, 並將其結果儲存於員工直屬上司 1 的資料表 49
範例 10.25 查詢出所有員工的姓名以及每位員工的直屬下屬姓名, 如果該名員工沒有任何直屬下屬, 則該名員工之直屬下屬姓名屬性需以虛值 (Null) 呈現, 並將其結果儲存於員工直屬下屬的資料表 50
範例 10.26 查詢出有參與 1 號和計劃 2 號計劃的員工身分證號碼 51
範例 10.27 查詢哪些員工有全部參與每一個執行地點在台中的計劃, 請列出這些員工的姓名, 並將其結果儲存於參與所有台中計劃員工的資料表 52
範例 10.28 查詢員工的總人數 最高薪水 最低薪水和平均薪水 53
範例 10.29 查詢每一位員工的身分證號碼和該員工參加計劃個數與總工作時數 54
範例 10.30 查詢至少參加兩個計劃以上的每一位員工的身分證號碼和該員工參加計劃個數與總工作時數 55
範例 10.31 查詢每一位員工的姓名和該員工的總工作時數 56
範例 10.32 查詢出有參與 1 號計劃或 2 號計劃的員工姓名 57
範例 10.33 查詢出有參與 1 號計劃但沒有參與 2 號計劃的員工姓名 58
範例 10.34 查詢出有參與 2 號計劃但沒有參與 1 號計劃的員工姓名 59
範例 10.35 查詢出有參與 1 號計劃和 2 號計劃的員工姓名 60
範例 10.36 查詢出有參與 1 號計劃和 2 號計劃的員工姓名 61
查詢出在資訊部門工作的的員工姓名 範例 10.37 62
查詢出沒有直屬上司的員工姓名 範例 10.38 63
查詢出沒有直屬上司的員工姓名 範例 10.39 64
以指定的值來取代 NULL 值 :ISNULL 函數 ISNULL 指令敘述格式如下 : ISNULL ( check_expression, replacement_value ) [ ; ] 65
範例 10.40 查詢出所有員工的姓名以及每位員工擁有的眷屬姓名, 如果該名員工沒有任何眷屬, 則該名員工之眷屬姓名屬性需列出無 66
字串的處理 CAST 和 CONVERT 這兩個指令敘述格式如下 : CAST ( expression AS data_type [ (length ) ]) CONVERT ( data_type [ ( length ) ], expression [, style ] ) [ ; ] 67
範例 10.41 查詢出每位員工所支領的月薪, 並以 某某員工的薪水是 XXXXX 元 的格式輸出 68
字串比較運算 :LIKE 字串間的比對包含下列四種特殊的字元 : % ( 百分比 ): 每一個 % 皆代表一個任意長度的字串 _ ( 底線 ): 每一個 _ 皆代表一個任意字元 [ ] ( 中括弧 ): 用來指定可以選用的字元項目或指定字元的範圍 [^]: 與 [] 的作用相反, 凡是被列入中括弧內的字元都被排除 69
查詢出住在台北市的員工姓名和地址 範例 10.42 70
查詢出所有姓李員工的相關基本資料 範例 10.43 71
範例 10.44 查詢出所有身分證號碼第一碼為 A 或 F 員工的相關基本資料 72
介於之間運算 :BETWEEN AND BETWEEN 指令敘述格式如下 : test_expression [ NOT ] BETWEEN begin_expression AND end_expression [ ; ] 73
範例 10.45 查詢有哪些員工的薪水是介於 25000 元到 45000 元之間 74
範例 10.46 查詢每一位員工的姓名 該員工參加計劃的計劃名稱和與該員工參加計劃的工作時數, 並且將查詢的結果依照該員工的姓名由小到大和計劃名稱由大到小排序方式排列出來 75
範例 10.47 查詢每一位員工的姓名 該員工參加計劃的計劃名稱和與該員工參加計劃的工作時數, 並且將查詢的結果依照該員工的姓名由小到大和計劃名稱由大到小排序方式排列出來, 並計算出所有員工參加所有計劃的工作時數總和 76
範例 10.48 查詢每一位員工的姓名 該員工參加計劃的計劃名稱和與該員工參加計劃的工作時數, 並且將查詢的結果依照該員工的姓名由小到大和計劃名稱由大到小排序方式排列出來, 並分別計算出每一個員工參加所有計劃的工作時數總和 77