2024/1/71四關系數據庫語言SQL

4.1查詢語句

4.2數據更新

4.3數據定義學習目標：能夠熟練掌握SQL語言并能夠在實際工作中進行靈活的應用。2024/1/72SQL是結構化查詢語言〔StructuredQueryLanguage〕的縮寫，美國國家標準局ANSI在1986年將SQL作為關系數據庫系統的標準語言，后被國際標準化組織〔ISO〕采納為國際標準。現在很多大型數據庫都實現了SQL語言。SQL概述2024/1/73SQL和SQLSERVER的區別SQL〔structuredquerylanguage)結構化查詢語言。它是一種標準，不是一種軟件SQLSERVER是數據庫管理系統的一種它是一種軟件，這種軟件在遵循SQL這種標準，很多數據庫管理軟件及開發工具都支持SQL這種標準。2024/1/74SQL語言的特點SQL語言的特點1.綜合統一2.高度非過程化3.面向集合的操作方式4.以同一種語法結構提供兩種使用方法5.語言簡潔，易學易用2024/1/752024/1/76SQL語句的書寫準那么對大小寫不敏感一條語句可以寫成一行或多行關鍵字不能在行與行之間分開數據項例如屬性項、表、視圖項等同時列出時，分隔符用逗號；字符或字符串常量要用單引號定界。2024/1/77根本操作對象根本表：本身獨立存在的表，SQL中一個關系就對應一個表；一個表對應一個存儲文件；一個表可以帶假設干索引。視圖：從一個或幾個根本表中導出的表，本身不獨立存放在數據庫中，數據庫只存儲視圖的定義，視圖相當于一個虛表，用戶可以在視圖上在定義視圖。索引：為快速訪問數據，而在包含數據的表中增加的一種組織，分為聚簇索引和非聚簇索引。

指索引項的順序與表中記錄的物理順序一致的索引組織例如：人事檔案〔員工姓名、地址、電話號碼、社會平安號、工資〕其中只有姓名、地址、電話號碼是公開信息。可以生成不包含社會平安號和工資的視圖，對表和視圖設置不同的權限2024/1/784.1查詢語句

4.1.1根本查詢4.1.2使用列表達式4.1.3WHERE從句的進一步使用4.1.4數據匯總4.1.5連接查詢4.1.6嵌套查詢4.1.7聯合查詢4.1.8使用系統內置函數的查詢2024/1/79SQL的核心是數據查詢。對于數據庫的查詢操作是通過SELECT查詢命令實現的，它的根本形式由SELECT－FROM－WHERE查詢塊組成，多個查詢塊可以嵌套執行。2024/1/710語句格式SELECT[ALL|DISTINCT]<目標列表達式>[，<目標列表達式>]…FROM

<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]]；

指定要顯示的屬性列指定查詢對象指定查詢條件查詢結果按指定列的值分組篩選出只有滿足指定條件的組對查詢結果表按指列值的升序或降序排序

2024/1/7114.1.1根本查詢1、簡單的無條件查詢例：Select*fromstu_info

把stu_info表中的所有記錄的所有屬性查詢出來2024/1/712SELECT*FROMSTU_INFOWHEREXSH=’06’4.1.1根本查詢2、簡單的條件查詢例4.2：從edu_d的數據庫表stu_info中查找包含所有列的土建學院〔學院代號xsh為06〕的學生名單。“*”表示查詢所有列字符或字符串用單引號引起來相當于選擇運算2024/1/713例4.3：從數據庫EDU_INFO中查詢土建學院〔XSH=’06’〕的學生的學號、姓名、性別、學院代號信息。SELECTXH,XM,XBM,XSHFROMSTU_INFOWHEREXSH=’06’屬性名用逗號隔開

3、查詢語句中投影運算的實現2024/1/7144.1.2使用列表達式查詢指定的列，除了可以使用*或者屬性列表以外，還可以使用列表達式。1、計算列值例4.4：在成績表XK中按滿分150分計算學生成績并顯示學號、課程號、教師號

SELECTXH,KCH,JSH,’150成績’=KSCJ*1.50FROMXK利用列表達式實現不同列的連接例4.5SELECT'學號',XH,XM+XSH,BHFROMSTU_INFOWHEREXBM=‘男’ORDERBYXHDESC在SQLServer2000系統的企業管理器中運行后系統自動將語句的寫法改成了：SELECT'學號'ASEXPR1,XH,XM+XSHASEXPR2,BHFROMSTU_INFOWHERE(XBM=‘男’)ORDERBYXHDESC2024/1/716注意：更改的是查詢結果顯示的列標題，這是列的別名，而不是更改了數據庫表或視圖的列標題。2、修改查詢結果的列標題例如：改寫例4.5SELECTXHAS學號,XM+XSHAS姓名和學院代碼,BHAS班級FROMSTU_INFOWHEREXBM=‘男’ORDERBYXHDESC2024/1/717另一種形式：SELECT學號=XH,姓名和學院代碼=XM+XSH,班級=BHFROMSTU_INFOWHEREXBM=‘男’

ORDERBYXHDESC

注意：當自定義的列標題中含有空格時，必須用引號將標題括起來。

2024/1/718采用別名的目的：

數據庫表中的列名用英文字母表示更方便，但是為了在查詢時，讓用戶看到的結果更直觀，所以可以采用中文別名。2024/1/719查詢工作的關鍵就在通過分析怎樣把實際問題中的自然語言描述轉化為1〕從哪個表中查詢即：from后的一項2〕要查詢哪些列即：select后的一項3)要查詢的條件即：where后的一項完成查詢工作的三個關鍵步驟

第3步最復雜4.1.3WHERE從句的進一步使用WHERE從句中，可以使用一個或者多個邏輯表達式限制查詢數據的范圍。表4-1常用查詢條件一覽表查詢條件謂詞比較=,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比較運算符確定范圍Betweenand,notbetweenand確定集合In,notin字符匹配Like,notlike空值Isnull,isnotnull多重條件And,or2024/1/7211、表達式比較例如：SELECT*FROMXKWHEREKSCJ>602、確定范圍例4.6：查詢數據庫表XK中考試成績在60-70之間的學號、課程號、成績

SELECTXH學號,KCH課程號，KSCJ成績FROMXKWHEREKSCJBETWEEN60AND70

2024/1/7223、確定集合例4.7：查詢應電0601、機專0601班的學生的信息

SELECT學號，姓名，班級FROMmingdanWHERE班級

IN(’應電0601’,’機專0601’)當與指定的值表中的任何一個匹配時，返回TRUE，否那么返回FALSE2024/1/7234、字符匹配用NOTLIKE

和LIKE與通配符%和_搭配，其運算對象可以是CHAR,VARCHAR,TEXT,NTEXT,DATETIME和SMALLDATETIME類型的數據。

表示任意長度的字符串表示人一單個字符例如：1、SELECT*FROMmingdanWHERE班級LIKE‘應%’2、SELECT*FROMmingdanWHERE姓名LIKE‘__紅’

2024/1/7245、涉及空值的查詢例如：SELECT*FROMmingdanWHERE班級ISNULL6、多重條件查詢例如：SELECT*FROMmingdanWHERE班級LIKE‘應%’

AND

姓名LIKE‘%紅’

2024/1/725一般格式：SELECT列名1，列名2，…FROM表名WHERE條件表達式ORDERBY

列名1[ASC|DESC][，列名2[ASC|DESC]…]升序〔默認〕降序4.1.4、設定排序條件2024/1/726例4.4:從數據庫EDU_D的表STU_INFO中查詢建9809班學生情況并按學號排序。SELECT*FROMSTU_INFOWHEREBH=‘建9809’ORDERBYXHSELECT*FROMSTU_INFOWHEREBH=‘建9809’ORDERBYZYH,XHDESC按多列排序：先按專業升序排列，然后同一專業的記錄按學號進行降序排列

注意：〔1〕ORDERBY子句不改變根本表中行或者列的順序，只改變查詢結果的排列順序。〔2〕ORDERBY子句制定排序的列必須出現在SELECT子句的列表達式中。〔3〕排序是查詢語句的最后一步工作，所以要把ORDERBY子句一般放在查詢語句的最后。2024/1/728在要查詢的屬性名前加DISTINCT，以去掉結果中重復的值。例：SELECTDISTINCT

班級

FROMmingdan注意：在一個SELECT語句中，DISTINCT只能出現一次，并且DISTINCT必須寫在所有列名之前。5、限定重復記錄2024/1/7294.1.4數據匯總1、聚合函數表4-2聚合函數一覽表

聚合函數含義Count([distinct|all]*)統計元組（記錄）個數Count([distinct|all]<列名>)統計一列中不為NULL值的個數Sum([distinct|all]<列名>)求一列值的總合（必須為數值型）Avg([distinct|all]<列名>)求一列值的平均數（必須為數值型）Max([distinct|all]<列名>)求一列值中的最大值Min([distinct|all]<列名>)求一列值中的最小值表示去掉結果中的重復行

指定ALL選項或不指定那么表示保存重復行2024/1/730例4.8：查詢學生總數SELECTCOUNT(*)AS學生數FROMmingdan或者SELECTCOUNT(XH)FROMmingdan例4.9：XK中高等數學的代號是090101,查詢學生選修高等數學的平均成績SELECTAVG(KSCJ)AS‘高等數學平均成績’FROMXKWHEREKCH=‘090101’因為XH是該表的主鍵，不允許有空值

查詢每個班的學生人數？2024/1/731例4.10：查詢學生選修高等數學的最高分和最低分SELECTMAX(KSCJ)AS‘高等數學最高分’,MIN(KSCJ)AS‘高等數學最低分’

FROMXKWHEREKCH=‘090101’

注意：在查詢中，除COUNT〔*〕外，所有的聚合函數都不包括取值為空的行。2024/1/7322、GROUPBY子句GROUPBY子句用于對表或視圖中數據的查詢結果按某一列或多列值分組，值相等的分為一組

2、GROUPBY子句例4.11：在表mingdan中查詢各班的學生人數

SELECT班級,COUNT(XH)AS人數FROMmingdanGROUPBY班級

注意：SELECT子句的列表中只能包含在GROUPBY中指出的列或在聚合函數中指定的列。GROUPBY子句用于對表或視圖中數據的查詢結果按某一列或多列值分組，值相等的分為一組

GROUPBY子句常與聚合函數聯合使用，用于針對分組的統計匯總

2024/1/7332、GROUPBY子句舉例：從表stu中統計xsh=‘0015’中男生和女生的人數Selectcount(*)fromstuwherexsh=‘0015’groupbyxb還可以按多個字段分組思考:統計每個學院的男生和女生的人數?2024/1/734例4.12：在表STU_INFO上產生一個結果集，包括每個專業的男生、女生人數。SELECTZYH,XBM,COUNT(*)AS人數FROMSTU_INFOGROUPBYZYH,XBM

2024/1/7353、HAVING短語HAVING短語指定組或聚合的條件。只有滿足條件的組才被選出來，HAVING通常與GROUPBY子句一起使用。〔注意與WHERE子句區分〕SELECTXH,COUNT(*)；FROMXK；GROUPBYXH；HAVINGCOUNT(*)>3例4.15查詢選修了3門以上課程的學生及選課數：2024/1/736例4.16：只查詢選修了3門以上課程的學號前四位是20011的學生學號及選課數：

SELECTXH,COUNT(*)FROMXKWHEREXHLIKE‘2001%’GROUPBYXHHAVINGCOUNT(*)>32024/1/737Having短語指定選擇組的條件，只有滿足條件的組才被選出來。Where子句從根本表中選擇滿足條件的元組，having選擇滿足條件的組2024/1/738假設一個查詢同時涉及兩個或兩個以上的表，那么稱為連接查詢。連接查詢中用來連接兩個關系的條件稱為連接條件或連接謂詞，一般格式為：[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>連接謂詞中的列名稱為連接字段

4.2.5連接查詢例如：xk.xh=stu_info.xh連接查詢的from后面跟多個表2024/1/739連接查詢中用來連接兩個關系的條件稱為連接條件或連接謂詞，格式：[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>其中比較運算符主要有：=、>、<、>=、<=、!=當連接運算符為=時，叫等值連接。使用其它運算符稱為非等值連接。連接謂詞中的列名稱為連接字段。在SELECT中連接條件放在where后面2024/1/740例：查詢每個學生及其選課情況Selectstu_info.*,xk.*Fromstu_info,xkWherestu_info.xh=xk.xh2024/1/741例：查詢物理012班每個學生及其選修課程的情況SELECTstu_info.*,xk.*FROMstu_info,xkWHEREstu_info.xh=xk.xhandbh=‘物理012’/*復合條件連接*/2024/1/742例4.18：查詢選修了有機化學這門課程的學生的姓名

SELECTXMFROMSTU_INFO,XK,GCOURSEWHEREGCOURSE.KM=‘有機化學’

ANDGCOURSE.KCH=XK.KCHANDXK.XH=STU_INFO.XH說明：〔1〕連接查詢涉及的所有表名都放在FROM子句后面。〔2〕連接條件放在WHERE子句中。〔3〕如果屬性列名在參加連接的各表中是唯一的，可以省略表名前綴；如果屬性列名是兩個表共同的屬性，那么一定要加表名前綴。2024/1/743〔4〕在書寫連接查詢時，為了簡化，可以為表名取別名，別名應該簡單。別名只在本次查詢有效。例：〔4.18〕SELECTXMFROMSTU_INFOS,XKX,GCOURSECWHEREC.KM=‘有機化學’ANDC.KCH=X.KCHANDX.XH=S.XH2024/1/7444.1.6嵌套查詢例4.19：在STU_info表中查詢選修了課程編號為090201的學生姓名分析：查詢所要求的結果出自一個關系〔STU_info〕，但相關的條件卻涉及多個關系〔STU_info和XK〕。SELECTXMFROMSTU_INFOWHERE(XHIN(SELECT

XH

FROMXK

WHEREKCH=‘090201’))一個SELECT-FROM-WHERE語句稱為一個查詢塊，將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。

子查詢2024/1/745說明：〔1〕這個查詢首先執行小括號里的內部查詢，得到一個結果集，再判斷外部查詢的列是否屬于這個集合；〔2〕關鍵字IN的前面只能有一個列，這個列必須與內部查詢結果集里的各個值含義相同，數據類型兼容。SELECTXMFROMSTU_INFOWHERE(XHIN(SELECT

XH

FROMXK

WHEREKCH=‘090201’))結果集2024/1/746例4.20查詢與劉玉濤在同一個班學習的學生SELECTXH,XM,BHFROMSTU_INFOWHERE(BHIN())1、帶有in謂詞的子查詢同一個學校的劉玉濤可能重名，而且可能不同班SELECTBHFROMSTU_INFOWHEREXM=‘劉玉濤’2024/1/7472、帶有比較運算符的子查詢例4.21查詢與學號2000029001的學生在同一個班學習的學生，按學號排序。SELECTXH,XM,BHFROMSTU_INFOWHERE(BH=())ORDERBYXH明確內查詢結果是唯一值時可以使用=SELECTBHFROMSTU_INFOWHEREXH=‘2000029001’2024/1/748帶有比較運算符的子查詢舉例：從mingdan表中查詢成績最高的同學的學號，姓名和成績Select學號,姓名,成績frommingdanWhere成績=(selectmax(成績)frommingdan)Select學號,max(成績)frommingdanSelect學號,max(成績)frommingdangroupby學號2024/1/7493、帶有ANY、SOME、ALL的比較子查詢例4.22查詢其他班級中比材物2k01班某個學生年齡小的學生的姓名和年齡。SELECTXM,NLFROMSTU_INFOWHERENL<ANY(

)ANDBH<>’材物2k01’同義字SELECTNLFROMSTU_INFOWHEREBH=‘材物2k01’2024/1/7504、帶有EXISTS謂詞的子查詢EXISTS是測試子查詢是否有數據行返回，如果有那么返回TRUE，否那么返回FALSE。NOTEXISTS那么相反，當結果表為空時，才返回TRUE。例4.23查詢選修了高等數學〔KCH=090101〕的學生的姓名等信息SELECTXM,XBM,BHFROMSTU_INFOWHEREEXISTS(SELECT*FROMXKWHEREXH=STU_INFO.XHANDKCH=‘090101’不需要明確指定列名，這個子查詢不一定會執行完，只要發現匹配條件成立，就退出子查詢2024/1/751例4.24查詢沒有選修高等數學的學生的姓名等信息

SELECTXM,XBM,BHFROMSTU_INFOWHERENOTEXISTS(SELECT*FROMK2001WHEREXH=STU_INFO.XHANDKM='高等數學')2024/1/752例：在stu_info表中查詢選修了090101號課程的學生姓名SELECTxmFROMstu_infoWHERE(XHIN(SELECTXHFROMxkWHERE(kch=‘090101')))當查詢的信息涉及一個關系時，嵌套查詢和連接查詢可互換Selectxmfromstu_info,xkWherestu_info.xh=xk.xhandkch=‘090101’2024/1/7534.1.7聯合查詢多個SELECT語句的結果可進行集合操作，SQLServer的Transact-SQL語言只提供UNION〔并〕運算符實現并操作。語法格式：SELECT_1UNION[ALL]SELECT_2{[UNION[ALL]SELECT_3]}…進行并運算的子查詢2024/1/754例4.25：有兩個數據庫表STUFR和STUIS，其結構完全一樣，分別存放外語學院和信息學院的學生根本信息，現在要查詢兩個學院女生的學號、姓名。SELECTXH,XMFROMSTUFRWHEREXB=＇女＇UNIONSELECTXH,XMFROMSTUISWHEREXB=＇女＇

2024/1/755說明：

〔1〕使用UNION運算符進行聯合查詢時，要保證各個SELECT語句的目標列表達式數量相等、排列順序相互一一對應、數據類型必須兼容。〔2〕UNION之后如果使用了ALL選項，那么顯示所有的包括重復的行，如果沒有使用ALL選項，那么重復行只顯示一行。〔3〕UNION操作常用于歸檔數據，例如歸檔各個基層部門的數據等，運行時將查詢的數據合并到第一個表中。〔4〕也可以對結果進行排序或者分組匯總，這是必須把ORDERBY子句或GROUPBY子句放在最后一個SELECT語句的后面，并且必須是針對第一個SELECT語句的列進行的排序或分組。〔5〕使用UNION連接的所有SELECT語句也可以使用同一張表，此時UNION運算符可以用OR運算符來代替。2024/1/756例4.26：將上個例子中的兩個數據庫表STUFR和STUIS的數據合并到結構相同的全校的學生數據表STU中去。SELECT*FROMSTUUNIONALLSELECT*FROMSTUFRUNIONALLSELECT*FROMSTUIS如何將查詢的結果保存在當前數據庫新建的表NEWSTU中？SELECT*INTONEWSTUFROMSTUFRUNIONALLSELECT*FROMSTUIS2024/1/757例4.27：查詢01學院的學生及所有的本科生

SELECT*FROMSTU_INFOWHEREXSH=‘01’

UNIONSELECT*FROMSTU_INFOWHEREPYCCM=‘本科’

改寫

SELECT*FROMSTU_INFOWHEREXSH=’01’

ORPYCCM=‘本科’

2024/1/7584.1.8使用系統內置函數的查詢T-SQL提供了三種系統內置函數：行集函數、聚合函數和標量函數。其中聚合函數在4.1.4節進行了介紹。

1、數學函數：對SQLServer提供的數字數據〔decimal,integer,float,real,money,smallmoney,smallint和tinyint〕進行數學運算并返回運算結果。2024/1/759〔2〕RAND函數語法格式：RAND([seed])功能：返回0～1之間的一個隨機值。〔1〕ABS函數語法格式：ABS(數值型表達式)功能：返回給定數值表達式的絕對值參數seed〔種子〕為整型表達式2024/1/760〔1〕ASCⅡ函數語法格式：ASCⅡ(字符型表達式)功能：返回字符表達式最左端字符的ASCⅡ值。〔2〕CHAR函數語法格式：CHAR(0～255之間的整數)功能：將ASCⅡ轉換為字符。2024/1/761〔3〕LEFT函數語法格式：LEFT(字符型表達式,整型表達式)功能：返回從字符串左邊開始指定個數的字符例4.28：查詢學號最左邊的4個字符。SELECTLEFT(XH,4)FROMSTU_INFO2、字符串處理函數2024/1/762〔4〕LTRIM函數語法格式：LTRIM(字符型表達式)功能：刪除字符型表達式字符串最左邊的空格，并返回字符串。〔5〕REPLACE函數語法格式：REPLACE(‘字符表達式1’,’字符表達式2’,’字符表達式3’)功能：用字符表達式3替換字符表達式1中包含的字符表達式2，并返回替換后的表達式。2024/1/763例4.29某學校學號〔XH，char〕的前4位是入學年，第5位是性別代碼，后5位是流水號，如某女生的學號：2024030101。在學生根本情況數據庫表STU_INFO中查詢所有學生的學號、姓名、入學年、性別信息。〔6〕SUBSTRING函數語法格式：SUBSTRING(expression,整型,整型)功能：返回expression中指定的局部數據。可以是字符串、二進制串、text、image字段或表達式

指定從expression的第幾個字節開始

length指定要返回的字節數

SELECTXH,XM,LEFT(XH,4)AS入學年,

SUBSTRING(XH,5,1)AS性別碼FROMSTU_INFOORDERBYXH2024/1/764〔7〕STR函數語法格式：STR〔FLOATL類型的表達式[，length[,decimal]]〕功能：將數字數據轉換為字符數據。返回值的總長度〔包括小數點〕制定小數點右邊的位數整數2024/1/7653、系統函數系統函數用于對SQLServer中的值、對象和設置進行操作并返回有關信息。〔1〕CASE函數①簡單的CASE函數語法格式：CASE輸入表達式WHEN比較表達式THEN結果表達式……ELSE表達式END功能：計算輸入表達式的值，與每一個WHEN的比較表達式的值比較，如果相等，那么返回對應的結果表達式的值；否那么，返回ELSE之后的表達式的值，如果省略了ELSE，那么返回NULL值。2024/1/766例4.30：在STU_INFO中查詢03學院學生的學號、姓名、性別，并將性別分別轉換成“男生〞、“女生〞。SELECTXH,XM,XBM,XBM=

CASE＇男＇

WHENXBMTHEN＇男生＇

ELSE＇女生＇

ENDFROMSTU_INFOWHEREXSH=＇03＇2024/1/767②CASE搜索函數語法格式：CASEWHEN條件1THEN表達式1WHEN條件2THEN表達式2……ELSE表達式END功能：系統將查詢出的結果進行判斷，當滿足WHEN的某個條件時，那么將該結果顯示為THEN之后的表達式的值，如果沒有滿足的條件，那么顯示ELSE之后的表達式的值，如果沒有指定ELSE子句時，返回NULL值。2024/1/768例4.31：查詢成績表XK中選修了“090101〞課程的學生的學號、5分制成績SELECTXH,＇5分制成績＇=

CASEWHENKSCJ<60THEN＇不及格＇

WHENKSCJ>=60ANDKSCJ<70THEN＇及格＇

WHENKSCJ>=70ANDKSCJ<80THEN＇中等＇

WHENKSCJ>=80ANDKSCJ<90THEN＇良好＇

WHENKSCJ>=90THEN＇優秀＇

ENDFROMXKWHEREKCH=＇090101＇注意：這個查詢改變的僅僅是查詢出的結果，而根底表中的數據并沒有發生轉換。2024/1/769〔2〕CAST函數語法格式：CAST(表達式AS數據類型)功能：將表達式的值轉換為數據類型參數所指定的類型。例4.32：查詢成績表K2024,其中考試成績〔KSCJ〕、XH、XM等數據類型都是CHAR，現要查詢考試成績在50分到60之間的學生及成績，并將成績加10分顯示。SELECTXH,XM,KSCJ,CAST(KSCJASFLOAT)+10AS加分后成績FROMK2024WHEREKSCJLIKE＇5_＇2024/1/770〔3〕日期時間函數GETDATE語法格式：GETDATE〔〕功能：按照SQLServer標準內部格式返回當前的系統日期和時間。返回值數據類型為datetime型。〔4〕年、月、日函數語法格式：YEAR(date)MONTH(date)DAY(date)其中參數date是數據類型為datetime或smalldatetime的表達式。返回值的數據類型為int型。

2024/1/7714.2數據更新主要包括對數據庫表的數據進行插入、修改、刪除操作。INSERTUPDATEDELETE2024/1/7724.2.1插入數據

插入數據的操作有兩種形式：〔1〕使用VALUES子句向數據庫的根本表表一次插入一個元組；〔2〕插入SELECT子查詢的結果，一次插入一批數據記錄。2024/1/7731、插入單個元組語法格式：INSERTINTO<表名>[(<屬性列1>[,<屬性列2>..)]VALUES(<常量1>[，<常量2>]…)

說明：〔1〕屬性列與常量必須一一對應，數據類型要一致；〔2〕在根本表結構定義中未說明為NOTNULL的屬性列，如果沒有出現在INTO子句后，這些列將取空值。已經說明為NOTNULL的屬性列，那么必須出現在INTO子句后面；〔3〕如果INTO子句后面沒有指定任何列，那么VALUES子句后面的常量個數必須與根本表中列的個數相等，且類型、順序一致。2024/1/774例〔在STU_COU數據庫中〕INSERTINTOSTU(XH,XM,XB,XSH,NL)VALUES(‘20240902090’,’王東方’，‘男’，‘0019’，22)等價于：INSERTINTOSTUVALUES(‘20240902090’,’王東方’，‘男’，‘0019’，22)(XH,XM,XB,XSH,NL)表中所有的列2024/1/7752、插入子查詢結果語法格式：INSERT[INTO]表名[(列名1，……，列名N)]SELECT

兼容列名1，……，兼容列名NFROM

兼容表名WHERE

邏輯表達式一一對應結構一致2024/1/776例：4.33在STU表中查詢出女生的學生信息存放在STUIS表中。INSERT

INTOSTUISSELECTXH,XM,XB,XSH,NLFROMSTUWHEREXB=‘女’INSERTintoSTUISSELECT*FROMSTUWHEREXB=‘女’或者2024/1/7774.2.2修改數據修改數據主要是對數據庫表中一個或多個元組某個或某些屬性的值進行更改。語法格式：UPDATE<表名>SET<列名>=<表達式>[，<列名>=<表達式>,…][WHERE<條件>]2024/1/7781、修改一個元組的值

例如：UPDATESTUSETXSH=‘機械’，NL=NL+1WHEREXH=＇20000501022＇

2、修改多個元組的值例：將所有課程的學分改為2分

UPDATECOURSESETCCREDIT=2例：將女生年齡增加1歲UPDATESTUSETNL=NL+1WHEREXB=‘女’2024/1/7793、帶子查詢的修改語句4.34：將選修了高等數學的90分以下的學生的成績加10分〔從edu_d庫中的xk數據表中修改〕UPDATEXKSETKSCJ=KSCJ+10WHEREKCH=(SELECTKCHFROMGCOURSEWHEREKM=＇高等數學＇)ANDKSCJ<=90建議：使用UPDATE對批量數據進行修改前，先用SELECT語句將要修改的記錄查詢出來，仔細檢查無誤后，再進行修改。2024/1/7804.2.3刪除數據語法格式：DELETEFROM<表名>[WHERE<條件>]注意：DELETE命令刪除的是一個或多個元組〔記錄、行〕，而不是某個行中個別列的值。不需要的某個行的個別列的值，只能用UPDATE命令修改成NULL值或空格符，不能被刪除。2024/1/7811、刪除一個元組例如：DELETEFROMSTU_INFOWHEREXH=＇2024050601＇2、刪除多個元組例如：DELETEFROMSTU_INFOWHEREBH=＇應0203＇

2024/1/7823、帶子查詢的刪除語句

例4.35：將信息學院學生的選課信息全部刪除

注意：為防止誤操作，在日常工作中，通常操作刪除語句時，要先查詢要刪除的數據，確認無誤后，再將SELECT*局部改成DELETE進行刪除操作。DELETEFROMXK,STU_INFOWHERESTU_INFO.XSH=’12’ANDSTU_INFO.XH=XK.XHSELECTXK.*FROMXK,STU_INFOWHERESTU_INFO.XSH=’12’ANDSTU_INFO.XH=XK.XHSELECT*FROMXKWHERE‘12＇=(SELECTXSHFROMSTU_INFOWHERESTU_INFO.XH=XK.XH)DELETEFROMXKWHERE‘12＇=(SELECTXSHFROMSTU_INFOWHERESTU_INFO.XH=XK.XH)2024/1/7834.3數據定義SQL的數據定義語句是對數據庫表、視圖、索引等的結構和屬性進行定義。常見的操作方式如下表：表4_3數據操作方式一覽表DROPINDEXCREATEINDEX索引DROPVIEWCREATEVIEW視圖ALTERTABLEDROPTABLECREATETABLE表修改刪除創建操作方法操作對象2024/1/7844.3.1定義根本表一、語法格式：CREATETABLE[數據庫名]<表名>〔<列名><數據類型>[<列級完整性約束條件>][，<列名><數據類型>[<列級完整性約束條件>]]…[，<表級完整性約束條件>]〕默認為當前數據庫

要定義的根本表的名字組成該表的各個屬性〔列〕涉及相應屬性列的完整性約束條件涉及一個或多個屬性列的完整性約束條件2024/1/785常用完整性約束主碼約束：PRIMARYKEY唯一性約束：UNIQUE非空值約束：NOTNULL參照完整性約束FOREIGNKEY……REFERENCESCHECK約束CHECK默認值

DEFAULT標識列

IDENTITY(seed,inctement)公式

AS2024/1/786二、數據類型類型標識符取值范圍空間注意邏輯類型BIT0,11位不允許定義為NULL整數類型INT-231~231-14Smallint-215~215-12TINYINT0~28-11正整數2024/1/787浮點類型標識符類別取值空間REAL-3.4×1038~3.4×10384FLOAT(n)n省略或[8，15]-1.7×10308~1.7×103088n∈[1,7]-3.4×1038~3.4×10384Decimal(p,s)或Numeric(p,s)P數據總位數S小數位數-1038~1038-1Numeric可用于帶identify關鍵字的列2-172024/1/788字符類型定長CHAR(n)最多為8000個ANSI字符NCHAR(n)最多為4000個Unicode字符變長VARCHAR(n)最多為8000個ANSI字符NVARCHAR(n)最多為4000個Unicode字符超長TEXT1~231-1NTEXT230-1個Unicode字符2024/1/789二進制數據類型類型標識符空間說明定長BINARY(n)8000個字節如允許為NULL，成變長變長VARBINARY(n)8000個字節超長IMAGE231-1個字節2024/1/790時間類型DATETIME8字節1753-1-1~9999-12-31的時間和日期精確到1/300秒SMALLDATETIME4字節1900-1-1~2079-6-6的時間和日期精確到1秒2024/1/791貨幣類型MONEY用2個4字節整數存儲SMALLMONEY用4字節存儲2024/1/792[例1]建立一個“學生〞表Student，它由學號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成。其中學號不能為空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent

(SnoCHAR(5)NOTNULL

UNIQUE，

SnameCHAR(20)UNIQUE，

SsexCHAR(1)，

SageINT，

SdeptCHAR(15))2024/1/793[例2]建立一個“學生選課〞表SC，它由學號Sno、課程號Cno，修課成績Grade組成，其中(Sno,Cno)為主碼。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,

Primarykey(Sno,Cno))2024/1/794例4.36：定義一個學生的成績表k2001CREATETABLEk2001/*表名為K2001*/(XHvarchar(12)NOTNULL,/*XH〔學號〕列，不為空*/XMvarchar(8)NULL,/*姓名*/KCHvarchar(8)NOTNULL,/*課程代碼*/KSCJvarchar(5)NULL,/*考試成績8/KKNYvarchar(5)NULL, /*開課時間*/KCXFvarchar(5)NULL, /*課程學分*/KMvarchar(30)NULL, /*課程名稱*/KCFZvarchar(1)NULL, /*課程分組*/JSMvarchar(8)NULL, /*任課教師*/BZvarchar(18)NULL) /*備注*/2024/1/795例4.37：創立表n_jobs。CREATETABLEn_jobs(job_idsmallintIDENTITY(1,1)

PRIMARYKEYjob_descvarchar(50)NOTNULLDEFAULT

＇新部門，暫無職位＇,min_lvltinyintNOTNULLCHECK(min_lvl>=12),max_lvltinyintNOTNULLCHECK(max_lvl<=250))是表示為PRIMARYKEY約束創立聚集關鍵字。表示新列是標識列。必須同時指定種子seed和增量increment，或者二者都不指定。如果二者都未指定，那么取默認值〔1,1〕。設置默認值，默認值可以是常量、NULL或系統函數。

例4.38：創立表new_employees。CREATETABLEnew_employee(emp_idchar(9)CONSTRAINT

PK_emp_id

PRIMARYKEYNONCLUSTERED

CONSTRAINT

CK_emp_id

CHECK(emp_idLIKE

＇[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]＇oremp_idLIKE＇[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]＇),/*每個員工編號由三個字符打頭，然后是10000-99999的某個數字和代表性別的字母F或M*/fnamevarchar(20)NOTNULL,minitchar(1)NULL,lnamevarchar(30)NOTNULL,job_idsmallintNOTNULLDEFAULT1REFERENCESjobs(job_id),job_lvltinyintDEFAULT10,pub_idchar(4)NOTNULLDEFAULT(＇9952＇)REFERENCESpublishers(pub_id),hire_datedatetimeNOTNULLDEFAULT(getdate())/*使用了獲取當前日期的函數作為默認值)表示PRIMARYKEY、NOTNULL、UNIQUE、FOREIGNKEY或CHECK約束定義的開始

約束的名稱

外鍵約束

2024/1/7974.3.2修改根本表語法結構：ALTERTABLE<表名>[ADD<新列名><數據類型>[完整性約束]][DROP<完整性約束名>|COLUMN<列名>][ALTERCOLUMN<列名><數據類型>]增加新列和新的完整性約束條件刪除指定的完整性約束條件用于修改列名和數據類型2024/1/798例4.43向Stu_info表增加“入學時間〞列，其數據類型為日期型

ALTERTABLESTU_INFOADDRXSJDATETIME不管根本表中原來是否已有數據，新增加的列一律為空值。1、添加列2024/1/799例4.44:將教師表的XB列改為SMALLINT數據類型

注意：修改原有的列定義有可能會破壞已有數據2、修改列ALTERTABLEGTECHALTERCOLUMNXBSMALLINT2024/1/71003、刪除列例4.45:刪除教師表中的性別一列。ALTERTABLEGTECHDROP

COLUMNXB

下面的列不能除去：被復制列；用在索引中的列；用在CHECK、FOREIGNKEY、UNIQUE或PRIMARYKEY約束中的列；有相關聯的默認值〔由DEFAULT關鍵字定義〕的列或綁定到默認對象的列；綁定到規那么的列。2024/1/7101[例]刪除數據庫表new_employee中的emp_id的檢查約束。ALTERTABLEnew_employeeDROP

CK_emp_id

4、刪除約束2024/1/71024.3.3刪除根本表語法格式：DROPTABLE<表名>

2024/1/71034.3.4視圖視圖是關系數據庫系統提供給用戶以多種角度觀察數據庫中數據的重要機制。視圖是從一個或多個根本表〔或視圖〕導出的表，是個虛表。數據庫只存放視圖的定義，不存放視圖對應的數據，這些數據仍存放在原來的根本表中。所以，視圖的數據跟隨根本表的數據而變化。視圖一旦被定義，就可以被查詢、刪除，修改。再定義一個新的視圖。2024/1/7104補充說明：視圖是一個虛擬表，其內容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行數據。但是，視圖并不在數據庫中以存儲的數據值集形式存在。行和列數據來自由定義視圖的查詢所引用的表，并且在引用視圖時動態生成。視圖允許我們把表結構的細節封裝起來，這些表可能因你的應用的進化而變化，而我們卻可以通過視圖給用戶一個一致的接口．視圖幾乎可以在一個真正的表可以使用的任何地方使用．在其它視圖上面再建造視圖也是很常見的．2024/1/71051、定義視圖〔1〕用企業管理器建立視圖〔自學〕〔2〕用SQL數據定義語句createview命令建立視圖語法格式：CREATEVIEW

視圖名AS<子查詢>[WITHCHECKOPTION]

對視圖進行UPDATE,INSERT和DELETE操作時要保證更新、插入或刪除的行滿足視圖定義中的條件表達式

2024/1/7106注意問題：〔1〕只能在當前的數據庫中創立視圖；〔2〕一個視圖最多可以引用1024個列；〔3〕視圖的命名必須符合SQLServer中的標識符的定義規那么；〔4〕可以將視圖建立在其他視圖或者引用視圖的過程之上，SQLServer2000中允許最多32層的視圖嵌套；〔5〕不能將規那么、默認值定義綁定在視圖上；〔6〕定義視圖的查詢語句中不能包括COMPUTE、COMPUTEBY、ORDERBY子句或是INTO等關鍵詞；〔7〕在視圖中不能定義全文索引，但可以定義索引；〔8〕不能創立臨時視圖，而且也不能在臨時表上創立視圖；2024/1/7107〔9〕默認狀態下，視圖中的列繼承他們在基表中的名稱。對于以下情況，在創立視圖時需要明確給出每一列的名稱。①視圖中的某些列來自表達式、函數或常量；②視圖中兩個或多個列在不同表中具有相同的名稱；③希望在視圖中的列使用不同于基表中的列名時。2024/1/7108A．選擇列定義視圖例：

CREATEVIEWMYVIEW1AS

SELECTEMP_ID,LNAME,FNAMEFROMEMPLOYEE定義了視圖后，可以和表一樣，使用SELECT語句訪問它。

2024/1/7109B．基于列的表達式定義視圖

例：CREATEVIEWMYVIEW2ASSELECTTITLE，ADVANCE，

PRICE*ROYALTY*YTD_SALESASNEWPRICEFROMTITLESWHEREPRICE>$52024/1/7110C．選擇行定義視圖例：CREATEVIEWMYVIEW3