金鑫

吳靖

主編

唐小毅

馬燕林

參編數據庫原理及應用（Access2021版）4.3.1創建表

4.3.2刪除表

4.3.3修改表4.1數據查詢語言4.4習題4.2數據操縱語言4.3數據定義語言4.1.1簡單查詢

4.1.2多表查詢

4.1.3排序

4.1.4子查詢

4.1.5分組查詢

4.1.6連接查詢

4.1.7聯合查詢4.2.1追加

4.2.2更新

4.2.3刪除

第4章

結構化查詢語言SQL第4章

SQL（StructuredQueryLanguage）是DBMS提供的對數據庫進行操作的語言，稱為結構化查詢語言。SQL已經成為關系數據庫語言的國際標準。1986年美國國家標準協會（ANSI）公布了第一個SQL標準SQL-86，將SQL解釋為StructuredQueryLanguage。國際標準化組織通過了SQL并于1989年公布了經過增補的SQL-89，1992年公布了SQL-92，即SQL2。SQL支持數據操作，用于描述數據的動態特性。SQL包括4個主要功能：數據定義語言（DataDefinitionLanguage）、數據查詢語言（DataQueryLanguage）、數據操縱語言（DataManipulationLanguage）、數據控制語言（DataControlLanguage）。本章所用數據庫為學生管理數據庫，如下圖所示。

圖

學生管理數據庫第4章

SQL的核心是從一個或多個表中返回指定記錄集合的SELECT語句。SELECT命令的基本形式為：SELECT…FROM…WHERE。【命令格式】SELECT[predicate]{*|table.*|[table.]field1[ASalias1][,[table.]field2[ASalias2][,...]]}FROMtable_names[WHEREsearch_criteria][GROUPBYgroupfieldlist [HAVINGaggregate_criteria]][ORDERBYcolumn_criteria[ASC|DESC]]

4.1數據查詢語言第4章

值得強調的是，上述命令結構中包含了SQL子句，每一個子句執行一個SQL語句的功能，有些子句在SELECT語句中是必須出現的，如表4-1所示。SQL子句執行的操作是否必需SELECT列出查詢的字段是FROM列出包含查詢字段或查詢條件字段的表是WHERE指出查詢條件否ORDERBY對結果排序，指出排序的依據否GROUPBY在包含聚合函數的SQL語句中，列出未在SELECT子句中匯總的字段僅在存在這類字段時才是必需的HAVING在包含聚合函數的SQL語句中，指定應用于在SELECT語句中匯總的字段的條件否表4-1SQL命令子句第4章

4.1.1簡單查詢

1．選擇字段使用SELECT命令，可以選擇表中的部分字段，建立一個新表。相當于關系運算中的投影運算。SELECT語句的最短的語法是：SELECTfieldsFROMtable。第4章【例4-1】

查詢學生姓名、性別和所屬院系。SELECT姓名,性別,所屬院系FROMstudent;【例4-2】

查詢student中所有字段和記錄。SELECT*FROMstudent;

【例4-3】

查詢已經選課的學生的學號。SELECTDISTINCT學號FROMgrade;

4.1.1簡單查詢

第4章【例4-4】

查詢學生的姓名和年齡。SELECT姓名,year(now())-year(出生日期)AS年齡FROMstudent;【例4-5】

所有課程的學分數增加50%，重新計算各門課程的學分數并列出清單。SELECT課程名稱,INT(學分*(1+0.5))AS新學分FROMcourse;【例4-6】

查詢學生的姓名，所屬院系，出生日期和性別。SELECT姓名,所屬院系,出生日期,性別FROMstudent;

4.1.1簡單查詢

2．選擇記錄在SELECT命令中設定查詢條件，查找滿足條件的記錄，這就是關系運行中的選擇運算。SELECT命令中用于完成選擇記錄（查詢條件）的命令子句是：[WHEREsearch_criteria]第4章【例4-7】

查詢會計學院學生的學號、姓名和所屬院系屬性。SELECT學號,姓名,所屬院系FROMstudentWHERE所屬院系="會計學院";【例4-8】

查詢學分少于3（不含3學分）的課程名稱、課程性質和學分。SELECT課程名稱,課程性質，學分FROMcourseWHERE學分<3;【例4-9】

查詢會計學院的非黨員學生。SELECT學號,姓名,所屬院系,政治面貌FROMstudentWHERE所屬院系="會計學院"and政治面貌<>"黨員";

4.1.1簡單查詢

第4章【例4-10】

查詢成績在60分以下（不包括60分）、90分以上（含90分）學生的學號和成績。SELECT學號,成績FROMgradeWHERE成績<60or成績>=90;【例4-11】

查詢教師中所有黨員記錄。SELECT*FROMteacherWHERE是否黨員=true;【例4-12】

查詢所有信息學院和會計學院的學生信息。SELECT*FROMstudentWHERE所屬院系IN('信息學院','會計學院');【例4-12】

查詢所有信息學院和會計學院的學生信息。SELECT*FROMstudentWHERE所屬院系IN('信息學院','會計學院');【例4-13】

查詢所有選修了課程，但沒有參加考試（成績為“空”）的學生學號。SELECT學號FROMgradeWHERE成績ISNULL;4.1.2多表查詢【例

4-14】

查詢會計學院學生選修的課程及成績，要求查詢結果中含學號、姓名、課程名稱和成績。SELECTstudent.學號,姓名,課程名稱,成績FROMstudent,grade,course,scheduleWHEREstudent.學號=grade.學號ANDgrade.課程ID=schedule.課程IDANDcourse.課程編號=schedule.課程編號AND所屬院系='會計學院'AND成績ISNOTNULL;第4章關系不是孤立的，所以表也不是孤立的，表之間是有聯系的。多表查詢是指SELECT命令的查詢內容或查詢條件同時涉及數據庫中相關的多個表。第4章4.1.2多表查詢【例4-15】

查詢有不及格成績的學生的學號和姓名。SELECTDISTINCTstudent.學號,姓名FROMstudent,gradeWHEREstudent.學號=grade.學號

AND成績<60AND成績ISNOTNULL;注意：DISTINCT在這條命令中所起到的作用【例4-16】

查詢所有選修了的課程，但沒有參加考試的學生的所屬院系、姓名、課程名稱和成績。SELECT所屬院系,姓名,課程名稱,成績

FROMstudent,course,grade,schedule

WHEREstudent.學號=grade.學號

ANDgrade.課程ID=schedule.課程ID

ANDschedule.課程編號=course.課程編號

AND成績ISNULL;第4章4.1.2多表查詢【例4-17】

查詢成績在60分以上（含60分）、90分以下（含90分）學生的學號、姓名、課程名稱和成績。SELECTstudent.學號,姓名,課程名稱,成績FROMstudent,course,grade,scheduleWHEREstudent.學號=grade.學號ANDgrade.課程ID=schedule.課程IDANDschedule.課程編號=course.課程編號AND成績BETWEEN60AND90;【例4-18】

查詢經濟學院開設的學分為2～4（含2和4）的所有課程的課程名稱、學分和所屬院系。SELECTDISTINCT課程名稱,學分,所屬院系FROMteacher,course,scheduleWHEREteacher.教師編號=schedule.教師編號ANDschedule.課程編號=course.課程編號AND所屬院系="經濟學院"AND學分BETWEEN2AND4;第4章4.1.2多表查詢【例4-19】查詢課程名中最后兩個字是“管理”的課程信息。SELECT*FROMcourseWHERE課程名稱LIKE'*管理';意

義樣

例返回True返回False字符范圍LIKE[a-z]F,p,j2,&范圍之外LIKE[!a-z]9,&,%b,a非數字LIKE[!0-9]A,a,&,～0,1,9組合字LIKEa[!b-m]#An9,az0,a99abc,aj0第4章4.1.2多表查詢【例4-21】

查詢成績在60分以下（不含60）、90分以上（不含90分）學生的學號和成績。SELECT學號,成績FROMgradeWHERE成績NOTBETWEEN60AND90;可以用SELECT…INTO語句將查詢結果保存到一個新的表中，語句格式如下：SELECTfield1[,field2[,

…]]INTOnewtable

FROMsource_table;【例4-20】

查詢課程名稱中含有“理”字的所有課程信息。SELECT*FROMcourseWHERE課程名稱LIKE'*理*';如果對上述例子中所有邏輯表達式進行非運算，就可以獲得否定（不是、不等于、不包含）的查詢結果。

4.1.3排序

第4章【例4-22】

查詢所有會計學院學生的信息，查詢結果按學號排序。SELECT*FROMstudentWHERE所屬院系='會計學院'ORDERBY學號;SELECT命令中用于對查詢結果排序的命令子句為：[ORDERBY<fieldname1>[ASC|DESC][,<fieldname2>[ASC|DESC]...]]【例4-23】

查詢所有學生的信息，查詢結果按所屬院系排序，同一院系按性別降序排序。SELECT*FROMstudentORDERBY所屬院系,性別DESC;比較下面的命令：SELECT*FROMstudentORDERBY7,3DESC;第4章

4.1.3排序

【例4-24】

查詢學生所屬院系、學號、姓名、課程名稱和成績等信息，且按所屬院系升序排序，同一院系按學號升序排序，同一學生按課程名稱升序排序，相同課程名稱按成績降序排列結果。SELECT所屬院系,student.學號,姓名,課程名稱,成績FROMstudent,course,grade,scheduleWHEREstudent.學號=grade.學號ANDgrade.課程ID=schedule.課程IDANDschedule.課程編號=course.課程編號AND成績ISNOTNULLORDERBY所屬院系,student.學號,課程名稱,成績DESC;第4章

4.1.3排序

【例4-26】

查詢成績從高到低排序后，前20%學生的課程和成績信息。SELECTTOP20PERCENT*FROMgrade,schedule,courseWHEREgrade.課程ID=schedule.課程IDANDschedule.課程編號=course.課程編號ANDNOT(ISNULL(成績))ORDERBY成績DESC;【例4-25】

查詢成績最低的3名學生的課程和成績信息。SELECTTOP3student.學號,姓名,課程名稱,成績FROMstudent,course,grade,scheduleWHEREstudent.學號=grade.學號ANDgrade.課程ID=schedule.課程IDANDschedule.課程編號=course.課程編號ANDNOT(ISNULL(成績))ORDERBY成績;4.1.4子查詢

第4章【命令格式】comparison[ANY|ALL|SOME](sqlstatement)expression[NOT]IN(sqlstatement)【命令說明】1）comparison：一個表達式及一個比較運算符，將表達式與子查詢的結果進行比較。2）expression：用以搜尋子查詢結果集的表達式。3）sqlstatement：SELECT語句，遵從與其他SELECT語句相同的格式及規則。它必須放在括號之中。

在SQL查詢語言中，一個SELECT…FROM…WHERE語句稱為一個查詢塊，把一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING子句的條件中的查詢，就構成子查詢。

子查詢的一般求解方法是由里向外處理，即每一個子查詢在上一級查詢處理之前求解，子查詢的結果用于建立其父查詢的查詢條件。外層查詢依賴于內層查詢的結果，內層查詢與外層查詢無關。通常的情況，當查詢的結果出自一個表，條件涉及多個表時，使用子查詢。第4章4.1.4子查詢

【例4-29】

查詢有不及格成績的學生信息。SELECT*FROMstudentWHERE學號IN (SELECT學號FROMgradeWHERE成績<60);【例4-28】

查詢沒有選修課程的學生，查詢結果中包含學號、姓名和所屬院系。結果按學號降序排列。SELECT學號,姓名,所屬院系FROMstudentWHERE學號NOTIN(SELECT學號FROMgrade)ORDERBY學號DESC;【例4-27】

查詢所有學生選修的課程，查詢結果包含課程名稱和學分。SELECT課程名稱,學分FROMcourseWHERE課程編號IN(SELECT課程編號FROMschedule,gradeWHEREschedule.課程ID=grade.課程ID);第4章4.1.4子查詢

【例4-33】

查詢選修了“西方經濟學”課程的學生信息。SELECT*FROMstudentWHERE學號IN(SELECT學號FROMgrade,schedule,courseWHEREgrade.課程ID=schedule.課程IDANDschedule.課程編號=course.課程編號AND課程名稱='西方經濟學')【例4-32】

查詢所有選修課程的成績都及格了的學生信息。SELECT*FROMstudentWHERE學號NOTIN(SELECT學號FROMgradeWHERE成績<60)AND學號IN(SELECT學號FROMgrade)【例4-31】

查詢與“楊楊”同年出生的學生信息。SELECT*FROMstudentWHEREYEAR(出生日期)=

(SELECTYEAR(出生日期)FROMstudentWHERE姓名='楊楊');【例4-30】

查詢與“楊楊”在同一個院系的學生信息。

SELEOCT*FROMstudentWHERE所屬院系= (SELECT所屬院系FROMstudentWHERE姓名='楊楊');4.1.5分組查詢

第4章利用SELECT命令還可以進行分組查詢，分組查詢是一種分類統計。【命令格式】SELECT[ALL|DISTINCT|DISTINCTROW] Aggregate_function(field_name)ASalias_name [,select_list] FROMtable_names [WHEREsearch_criteria] GROUPBYgroupfieldlist [HAVINGaggregate_criteria] [ORDERBYcolumn_criteria[ASC|DESC]]第4章4.1.5分組查詢

【例4-34】

查詢各門課程的平均分。SELECT課程名稱,AVG(成績)AS平均分

FROMgrade,schedule,course

WHEREgrade.課程ID=schedule.課程ID

ANDschedule.課程編號=course.課程編號

AND成績ISNOTNULL

GROUPBY課程名稱;【例4-33】

查詢選修了“西方經濟學”課程的學生信息。SELECT*FROMstudentWHERE學號IN(SELECT學號FROMgrade,schedule,courseWHEREgrade.課程ID=schedule.課程IDANDschedule.課程編號=course.課程編號AND課程名稱='西方經濟學')第4章4.1.5分組查詢

【例4-37】

查詢“西方經濟學”課程成績高于該門課程平均成績的學生信息。SELECTstudent.*

FROMstudent,grade,schedule,course

WHEREstudent.學號=grade.學號

ANDgrade.課程ID=schedule.課程ID

ANDschedule.課程編號=course.課程編號

AND課程名稱='西方經濟學'AND成績

>=(SELECTAVG(成績)AS平均分

FROMgrade,schedule,course

WHEREgrade.課程ID=schedule.課程ID

ANDschedule.課程編號=course.課程編號

AND課程名稱='西方經濟學');【例4-36】

查詢至少有兩門不及格課程的學生信息。SELECT*FROMstudentWHERE學號IN(SELECT學號FROMgradeWHERE成績<60GROUPBY學號HAVINGCOUNT(*)>=2);【例4-35】

查詢選修“西方經濟學”課程的學生人數。SELECT課程名稱,COUNT(*)AS選修人數

FROMgrade,schedule,course

WHEREgrade.課程ID=schedule.課程ID

ANDschedule.課程編號=course.課程編號

AND課程名稱='西方經濟學'

GROUPBY課程名稱;第4章

4.1.6連接查詢

【命令格式】SELECT[predicate]select_listFROMtable1{INNER|LEFT|RIGHT}JOINtable2ONjoin_criteria[WHEREsearch_criteria] [ORDERBYcolumn_criteria[ASC|DESC]]注意：在這個命令子句中，所要連接的表和其連接類型是由FROM給出的，連接的條件是由ON給出的，ON條件指出當兩個表在公共字段上的值相匹配時，進行連接。第4章

4.1.6連接查詢

【例4-39】

完成student表和grade表的普通連接。SELECT*FROMteacherINNERJOINsalaryONteacher.教師編號=salary.教師編號;【例4-38】

完成student表和grade表的普通連接。SELECT*FROMstudentINNERJOINgradeONstudent.學號=grade.學號;1．普通連接命令選項INNERJOIN為普通連接，也稱為內部連接。普通連接的結果是只有滿足連接條件的記錄才會出現在查詢結果中。第4章

4.1.6連接查詢

【例4-40】

完成student表和grade表的左連接。結果包括所有的學生記錄（student表中全部記錄），也包括沒有選擇課程的學生信息。SELECT*FROMstudentLEFTJOINgradeONstudent.學號=grade.學號;2．左連接左連接的查詢結果是返回第一個表全部記錄和第二個表中滿足連接條件的記錄。第一個表中所有沒在第二個表中找到相應連接記錄的記錄，其對應第二個表的字段值為NULL。第4章

4.1.6連接查詢

【例4-42】

完成grade表和course表的右連接。結果中包括了所有的課程（course中的全部記錄），也包括沒有學生選擇的課程。SELECT*FROMgradeRIGHTJOINcourseONcourse.課程編號=grade.課程編號;【例4-41】

完成student表和grade表的右連接。SELECT*FROMstudentRIGHTJOINgradeONstudent.學號=grade.學號;比較上述3種連接的結果。3．右連接右連接的查詢結果是返回第二個表全部記錄和第一個表中滿足連接條件的記錄。同樣，第二個表不滿足連接條件的記錄，其對應第一個表的字段值為NULL。第4章

4.1.7聯合查詢

【例4-43】

查詢學生和教師中的女性記錄。SELECT姓名,性別,出生日期FROMstudentWHERE性別="女"UNIONSELECT姓名,性別,出生日期FROMteacherWHERE性別="女";【命令格式】SELECTselect_statementUNIONSELECTselect_statement[UNIONSELECTselect_statement[UNION……]聯合查詢的要求：聯合查詢中合并的選擇查詢必須具有相同的輸出字段數、采用相同的順序并包含相同或兼容的數據類型。在運行聯合查詢時，來自每組相應字段中的數據將合并到一個輸出字段中，這樣查詢輸出所包含的字段數將與每個Select語句相同。4.2數據操縱語言第4章數據操作語言（DML）命令實現的功能包括追加、更新和刪除。第4章

4.2.1追加

追加就是添加一個或多個記錄至一個表。1．多重記錄追加查詢【命令格式】INSERTINTOtarget[(field1[,field2[,...]])]

SELECTfield1[,field2[,...]]

FROMtableexpression2．單一記錄追加查詢【命令格式】INSERTINTOtarget[(field1[,field2[,...]])]

VALUES(value1[,value2[,...]])【例4-44】

插入一條完整的記錄。INSERTINTOstudentVALUES('053500001','齊心','女',#1988-01-01#,"漢族",'團員',"信息學院","電商05-1","北京市","排球","");

4.2.2更新

第4章更新命令改變指定表中滿足條件記錄的字段值。【命令格式】UPDATEtable_name SETcolumn_name=value[,column_name=value[,…]] WHEREupdatecriteria【例4-45】

對崗位津貼低于800元的教師，在其原有崗位津貼的基礎上增加20%，重新計算崗位津貼（此例在操作之前，先將salary表復制為salary1表）。UPDATEsalary1SET崗位津貼=崗位津貼*(1+0.2)WHERE崗位津貼<=800;

4.2.3刪除

第4章刪除命令刪除指定表中滿足條件的記錄。【命令格式】 DELETEFROMtable_name [WHEREdelete_criteria]【例4-46】

刪除salary1表（見【例4-45】）中教師編號首位是“0”的記錄。DELETEFROMsalary1WHERE教師編號LIKE"0*";4.3數據定義語言第4章通過在SQL視圖中編寫數據定義語言可以創建和修改表、限制、索引和關系。本節介紹數據定義語言，以及如何使用這類語言創建表、限制、索引及關系。第4章【例4-48】

用CREATE命令建立mytable2表，其中ID字段為自動編號主鍵。CREATETABLEmytable2(IDCOUNTERPRIMARYKEY,MyTextTEXT(10));【例4-47】

用CREATE命令建立mytable1表。CREATETABLEmytable1(FirstNameTEXT,LastNameTEXT,DateOfBirthDATETIME);4.3.1創建表第4章可以使用CREATETABLE命令創建表。

創建一個新表。在創建表的同時可以定義表的字段名、字段類型、小數位數、是否支持“空”值、參照完整性規則等。【命令格式】CREATE[TEMPORARY]TABLEtable_name(field1type[(size)][NOTNULL][index1][,field2type[(size)][NOTNULL][index2][,…]][CONSTRAINTmultifieldindex[,…]])

建立TEMPORARY表時，只能在建立表的會話期間看見它。會話期終止時，它就被自動刪除。Temporary表能被不止一個用戶訪問。第4章【例4-50】

用CREATE命令建立grade表，其中“學號”和“課程編號”為雙字段主鍵，并與student表建立聯系。CREATETABLEgrade(學號TEXT(8)REFERENCESstudent(學號),課程編號TEXT(6),

成績NUMBER,PRIMARYKEY(學號,課程編號));【例4-49】

用CREATE命令建立student表，學號為主鍵，且與grade表以學號為連接字段建立聯系。CREATETABLEstudent(學號TEXT(8)PRIMARYKEY,姓名TEXT(10),性別TEXT(1),出生日期DATETIME)referencesgrade(學號);

4.3.2刪除表

第4章表中刪除已存在的索引。【命令格式】DROP{TABLEtable_name|INDEXindex_nameON表}【例4-51】

刪除mytable表。DROPTABLEmytable

這種刪除是不可恢復的。4.3.3修改表第1章ALTER命令用于修改已創建