數(shù)據(jù)庫(kù)原理與應(yīng)用 課件 張千帆 第5-7章 SQL定義語(yǔ)句、SQL數(shù)據(jù)操縱語(yǔ)句、SQL數(shù)據(jù)查詢語(yǔ)句

第5章SQL數(shù)據(jù)定義語(yǔ)句本章介紹數(shù)據(jù)庫(kù)、基本表、索引的定義方法數(shù)據(jù)庫(kù)系統(tǒng)原理及應(yīng)用5.1定義數(shù)據(jù)庫(kù)5.2定義基本表5.3定義索引主要內(nèi)容定義數(shù)據(jù)庫(kù)5.11）創(chuàng)建數(shù)據(jù)庫(kù)在SQL中，一個(gè)模式(SHEMA)定義為基本表的集合。模式由模式名和模式擁有者的用戶名或賬號(hào)來(lái)確定，并包含模式中每一個(gè)元素（基本表、視圖、索引等）的定義。創(chuàng)建一個(gè)模式，可以理解為建立一個(gè)數(shù)據(jù)庫(kù)，或定義一個(gè)存儲(chǔ)空間。CREATESCHEMA與CREATEDATABASE同義。語(yǔ)法格式為：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]db_name;參數(shù)：（1）IFNOTEXISTS：防止數(shù)據(jù)庫(kù)已經(jīng)存在時(shí)系統(tǒng)報(bào)錯(cuò)。（2）db_name：擬創(chuàng)建的數(shù)據(jù)庫(kù)的名字。1）創(chuàng)建數(shù)據(jù)庫(kù)[例5-1]:創(chuàng)建名稱為飯卡管理

的數(shù)據(jù)庫(kù)。

[例5-2]:創(chuàng)建名稱為

飯卡管理

的數(shù)據(jù)庫(kù)（該數(shù)據(jù)庫(kù)已存在）。CREATEDATABASE飯卡管理;CREATEDATABASE飯卡管理;CREATEDATABASEIFNOTEXISTS飯卡管理;第一個(gè)語(yǔ)句執(zhí)行時(shí)系統(tǒng)提示錯(cuò)誤，因?yàn)閿?shù)據(jù)庫(kù)已存在。第二個(gè)語(yǔ)句執(zhí)行成功。2）刪除數(shù)據(jù)庫(kù)DROPDATABASE刪除數(shù)據(jù)庫(kù)中的所有表并刪除數(shù)據(jù)庫(kù)。DROPSCHEMA是DROPDATABASE的同義詞。語(yǔ)法格式為：DROP{DATABASE|SCHEMA}[IFEXISTS]db_name;參數(shù)：IFEXISTS：防止數(shù)據(jù)庫(kù)不存在時(shí)發(fā)生錯(cuò)誤。2）刪除數(shù)據(jù)庫(kù)[例5-3]:刪除數(shù)據(jù)庫(kù)

飯卡管理

及其下屬所有的對(duì)象。

[例5-4]:刪除數(shù)據(jù)庫(kù)

飯卡管理（不存在該數(shù)據(jù)庫(kù)）。。DROPSCHEMA飯卡管理;或DROPDATABASE飯卡管理;DROPDATABASE飯卡管理;DROPDATABASEIFEXISTS飯卡管理;第一個(gè)語(yǔ)句執(zhí)行時(shí)系統(tǒng)提示錯(cuò)誤，因?yàn)閿?shù)據(jù)庫(kù)不存在。第二個(gè)語(yǔ)句執(zhí)行成功。定義基本表5.21）創(chuàng)建基本表創(chuàng)建基本表時(shí)，只需要定義型，定義后得到具有表結(jié)構(gòu)的一張空表。SQL使用CREATETABLE語(yǔ)句定義基本表語(yǔ)法格式為：CREATETABLE[IFNOTEXISTS]tbl_name(col_namecolumn_definition,...)參數(shù)：（1）tbl_name：基本表的表名。（2）col_namecolumn_definition：列定義子句，其中col_name為列名，column_definition定義列的數(shù)據(jù)類型和列的完整性約束。一個(gè)基本表由多個(gè)列組成，多個(gè)列定義語(yǔ)句之間用逗號(hào)分開(kāi)，最后一個(gè)列定義語(yǔ)句除外。表級(jí)的完整性約束需要單獨(dú)的子句進(jìn)行定義。列級(jí)的完整性約束可以在列定義子句中進(jìn)行說(shuō)明，也可以用單獨(dú)的子句定義在表級(jí)。1）創(chuàng)建基本表[例5-5]：定義基本表card，改表的表結(jié)構(gòu)如表5-1所示。

CREATETABLEcard(CIDCHAR(6)PRIMARYKEY,passwordVARCHAR(6)NOTNULL,balanceDECIMAL(10,2)NOTNULLCHECK(balance>=0)，stateCHAR(1)CONSTRAINTstate_1CHECK(statein('0','1','2'))));1）創(chuàng)建基本表[例5-6]：定義基本表student，表結(jié)構(gòu)如表5-2所示。CREATETABLEstudent(SIDCHAR(12),CIDCHAR(6),snameVARCHAR(20),genderCHAR(1),collegeVARCHAR(20)NOTNULL,CONSTRAINTSID_pkPRIMARYKEYNONCLUSTERED(SIDDESC),FOREIGNKEY(CID)REFERENCEScard(CID)ONDELETECASCADE);2）修改基本表在基本表建立使用一段時(shí)間后，可能由于分析設(shè)計(jì)不到位或應(yīng)用需求的不斷變化等原因，需要對(duì)基本表結(jié)構(gòu)進(jìn)行修改，比如新增列和完整性約束、修改原有的列定義和完整性約束定義等。SQL語(yǔ)言使用ALTERTABLE命令來(lái)完成這一功能。其基本格式為：ALTERTABLEtbl_name[alter_option[,alter_option]...]參數(shù)：（1）tbl_name：需要修改表結(jié)構(gòu)的基本表。（2）alter_option：修改項(xiàng)。2）修改基本表修改項(xiàng)包含兩大類，一類是對(duì)列的修改，一類是對(duì)完整性約束的修改。如下所示：alter_option:{|RENAMECOLUMNold_col_nameTOnew_col_name

//改列名|RENAME[TO|AS]new_tbl_name

//改表名

|ADD[COLUMN](col_namecolumn_definition,...)

//增加列

|ALTER[COLUMN]col_name{SETDEFAULT{literal|(expr)}|DROPDEFAULT

//設(shè)置或刪除列的默認(rèn)值|DROP[COLUMN]col_name

//刪除列|MODIFY[COLUMN]col_namecolumn_definition

//修改列|CHANGE[COLUMN]old_col_namenew_col_namecolumn_definition

//修改列|ADD[CONSTRAINT[symbol]]PRIMARYKEY[index_type].(key_part,...)

//增加主鍵約束|ADD[CONSTRAINT[symbol]]FOREIGNKEY(col_name,...)reference_definition//增加外鍵約束|ADD[CONSTRAINT[symbol]]CHECK(expr)

//增加用戶定義約束|DROPPRIMARYKEY

//刪除主鍵約束|DROPFOREIGNKEYfk_symbol

//刪除外鍵約束|DROP{CHECK|CONSTRAINT}symbol

//刪除用戶定義約束}2）修改基本表[例5-9]:在student表中添加“專業(yè)(major)”列。ALTERTABLEstudentADDmajorCHAR(8);[例5-11]:使用MODIFY將student表中的SID列的數(shù)據(jù)類型改為CHAR(20)。ALTERTABLEstudentMODIFYcolumnSIDCHAR(20);例5-10:刪除列。刪除student中的major列。ALTERTABLEstudentDROPmajor;3）刪除基本表

刪除基本表命令的基本格式為DROPTABLE[IFEXISTS]tbl_name[,tbl_name]...[RESTRICT|CASCADE]參數(shù)：（1）IFEXISTS：不使用IFEXISTS子句，刪除的表不存在時(shí)系統(tǒng)給出錯(cuò)誤提示；使用IFEXISTS子句時(shí)，刪除的表不存在時(shí)系統(tǒng)不報(bào)錯(cuò)。（2）RESTRICT：拒絕刪除主表。該值是默認(rèn)值。（3）CASCADE：刪除主表時(shí)，自動(dòng)刪除從表中的匹配行。[例5-12]:刪除card表。DROPTABLEcard;定義索引5.35.3.1索引的概念5.3.1索引的概念索引為數(shù)據(jù)快速檢索提供支持，并可作為其他數(shù)據(jù)庫(kù)對(duì)象的數(shù)據(jù)源。索引定義的是關(guān)鍵字與其存儲(chǔ)地址的對(duì)應(yīng)關(guān)系。索引是依賴于表的，它提供了數(shù)據(jù)庫(kù)中編排表中數(shù)據(jù)的內(nèi)部方法。當(dāng)刪除基本表時(shí)，表上建立的索引一并被刪除。當(dāng)基本表中插入、刪除、修改數(shù)據(jù)時(shí)，行的存儲(chǔ)地址可能發(fā)生變化，索引需要同步進(jìn)行更新，即數(shù)據(jù)庫(kù)不僅要更新基本表，還要更新基本表上的索引。索引可以提高基本表的查詢速度，但會(huì)降低基本表的更新速度。適合建立索引的列

經(jīng)常被查詢、排序、分組的列

值域很大的列

經(jīng)常用在連接的列上

經(jīng)常需要使用WHERE子句的列

經(jīng)常被組合在一起查詢的列上適合建立組合索引，且把使用最頻繁的列作為

組合索引的前導(dǎo)列。5.3.2索引類型1）單列索引與組合索引單列索引是在單個(gè)列上創(chuàng)建索引。組合索引是由多個(gè)列組合構(gòu)建的索引，多個(gè)列中的值都不允許有空值。使用組合索引時(shí)遵循“最左前綴規(guī)則”，只有當(dāng)查詢條件使用了組合索引的第一個(gè)列時(shí)索引才會(huì)被使用。2）主鍵索引主鍵索引是建立在主鍵上的索引，列的值不允許重復(fù)，也不允許有空值。主鍵索引一般是在創(chuàng)建表、定義主鍵時(shí)由系統(tǒng)自動(dòng)創(chuàng)建的。3）唯一索引唯一索引是指列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。4）普通索引用表中的普通列構(gòu)建的索引，對(duì)列值沒(méi)有任何限制。5）全文索引全文索引是大文本適用的一種索引類型。全文索引為文本生成一份單詞的清單，并根據(jù)清單來(lái)索引。生成全文索引非常消耗時(shí)間和空間，但是對(duì)于大文本，或者較大的字符型數(shù)據(jù)的查詢速度比普通索引快。5.3.2索引類型聚集索引是指索引的邏輯順序與表中相應(yīng)行的物理順序一致，因此一個(gè)表只能包含一個(gè)聚集索引。非聚集索引是指索引的邏輯順序與磁盤(pán)上行的物理存儲(chǔ)順序不同，一個(gè)表可以包含多個(gè)非聚集索引。5.3.3創(chuàng)建索引1）CREATETABLE語(yǔ)句創(chuàng)建索引CREATETABLE語(yǔ)句在基本表上定義PRIMARYKEY、FOREIGNKEY或者UNIQUE約束時(shí)，數(shù)據(jù)庫(kù)管理系統(tǒng)自動(dòng)在主鍵上創(chuàng)建主鍵索引、在外鍵上創(chuàng)建普通索引、在唯一值約束的列上創(chuàng)建唯一索引。CREATETABLE語(yǔ)句中使用INDEX子句在其它列上創(chuàng)建索引。INDEX子句的語(yǔ)法格式為：[UNIQUE|FULLTEXT]INDEXindex_name(key_part,...)key_part:col_name[(length)][ASC|DESC]參數(shù)：（1）index_name：索引的名稱。（2）UNIQUE|FULLTEXT：索引類型。UNIQUE是唯一索引，F(xiàn)ULLTEXT是全文索引，缺省值為普通索引，即NORMAL類型。（3）key_part：可以是單列索引，也可以是多列組成的組合索引。組合索引中多個(gè)列用逗號(hào)分開(kāi)。（4）length：用作索引的列名長(zhǎng)度過(guò)長(zhǎng)會(huì)造成索引的關(guān)鍵字太大，導(dǎo)致效率降低，在允許的情況下，可以只取索引列的前幾個(gè)字符作為索引。（5）ASC|DESC：ASC為升序排列，DESC為降序排列。默認(rèn)值是ASC。5.3.3創(chuàng)建索引[例5-13]:創(chuàng)建student表時(shí)在CID列建立名為normal_index的單列、普通索引，在college列建立名為single_index的單列、普通索引，索引長(zhǎng)度為8。CREATETABLEstudent(SIDCHAR(12)PRIMARYKEY,CIDCHAR(6)，snameVARCHAR(20)，genderCHAR(1)DEFAULT'M'CHECK(genderin('M','F')),collegeVARCHAR(20)NOTNULL,FOREIGNKEY(CID)REFERENCEScard(CID)ONDELETECASCADE,INDEXnormal_index(sname)， INDEXsingle_index(college(8)));5.3.3創(chuàng)建索引2）ALTERTABLE語(yǔ)句創(chuàng)建索引INDEX子句的語(yǔ)法格式為：ADD[UNIQUE|FULLTEXT]INDEXindex_name(key_part,...)...key_part:col_name[(length)][ASC|DESC][例5-14]:設(shè)創(chuàng)建student表時(shí)，為了提高查詢效率，需要在sname列建立名為normal_index的普通索引,在college列和sname列上建立名為multi_index的組合索引。ALTERTABLEstudentADDINDEXnormal_index(sname)，ADDINDEXmulti_index(college(8)，sname(6));5.3.3創(chuàng)建索引3）CREATEINDEX語(yǔ)句創(chuàng)建索引CREATEINDEX語(yǔ)句創(chuàng)建索引的基本語(yǔ)法格式為：CREATE[UNIQUE|FULLTEXT]INDEXindex_nameONtbl_name(key_part,...)key_part:col_name[(length)][ASC|DESC][例5-14]:在student表上在并在CID列建立名為unique_index的唯一索引CREATE

UNIQUEINDEXunique_index(CID);5.3.4刪除索引1）直接刪除索引DROPINDEX語(yǔ)句的基本語(yǔ)法格式為：DROPINDEXindex_nameONtbl_name;2）修改表結(jié)構(gòu)刪除索引ALTERTABLE語(yǔ)句刪除索引的基本語(yǔ)法格式為：ALTERTABLEtbl_nameDROPINDEXindex_name;[例5-15]:刪除student上的multi_index索引。DROPINDEXmulti_indexONstudent;或者ALTERTABLEstudentDROPINDEXmulti_index;5.3.4刪除索引3）刪除主鍵索引其基本的語(yǔ)法格式為：ALTERTABLEtab_nameDROPPRIMARYKEY;當(dāng)有其它表參照該主鍵時(shí)，即該主鍵是其他表的外鍵，則不能刪除該主鍵索引。例如，刪除card表的主鍵索引會(huì)出錯(cuò)，因?yàn)閟alebill表中的外鍵CID是參照該表的主鍵CID的。此外，如果主鍵是自增型變量，不能直接刪除該列的主鍵索引，修改變量類型后才能刪除主鍵索引。第6章SQL數(shù)據(jù)操縱語(yǔ)句本章介紹數(shù)據(jù)操縱語(yǔ)言的基本概念及相關(guān)數(shù)據(jù)操作。數(shù)據(jù)庫(kù)系統(tǒng)原理及應(yīng)用6.1插入數(shù)據(jù)6.2更新數(shù)據(jù)6.3刪除數(shù)據(jù)主要內(nèi)容6.1.1VALUES或VALUE賦值INSERT[INTO]tbl_name[(col_name[,col_name]...)]{VALUES|VALUE}(value_list)[,(value_list)]...value:{expr|DEFAULT}value_list:value[,value]...基本格式：參數(shù)：[(col_name[,col_name]...)]：如果給表中所有列賦值，表名后面不需要指明列名，并且值列表中值的順序必須與表中列的順序完全一致，不可跳過(guò)或顛倒，空數(shù)據(jù)用NULL表示。否則按照指定的列名依次賦值。有NOTNULL約束的字段必須全部列出，否則操作不能成功。6.1.1VALUES或VALUE賦值具體語(yǔ)法分成3種情形：1)完整型INSERTINTOtabnameVALUES(val1,val2,......)2）列舉型INSERTINTOtabname(col1,col2,......)VALUES(val1,val2,......)3）復(fù)制型INSERTINTOtabname1(col1,col2,......)SELECT(col1,col2,......)FROMtabname26.1.1VALUES或VALUE賦值注意：1）字符、日期等數(shù)據(jù)類型用單引號(hào)或雙引號(hào)界定，數(shù)值型不用引號(hào)。2）每個(gè)數(shù)據(jù)之間用逗號(hào)隔開(kāi)。3）運(yùn)用完整型語(yǔ)法時(shí)，數(shù)據(jù)順序要與定義表時(shí)的字段順序完全一致，不可跳過(guò)或顛倒，空數(shù)據(jù)用NULL表示。4）運(yùn)用列舉型語(yǔ)法時(shí)，數(shù)據(jù)順序要與列舉的字段順序完全一致，空數(shù)據(jù)用NULL表示，也可不列出；屬性為NOTNULL約束的字段必須全部列出，否則，操作不能成功。5）運(yùn)用復(fù)制型語(yǔ)法時(shí)，要求兩個(gè)表具有大致相同的表結(jié)構(gòu)。6.1.2SET賦值如果使用SET子句插入行，則必須至少為一列賦值。如果某一個(gè)字段使用了默認(rèn)值或自增值，SET子句中可以省略這些字段語(yǔ)法格式為：INSERT[INTO]tbl_name

SETcol_name=value[,col_name=value]...[例6-1]:向student中插入一條新記錄，學(xué)號(hào)為20200301001，姓名為黃磊，院系為管理學(xué)院，其它信息暫缺。INSERTINTOstudent(SID,sname,college)VALUES('20200301001','黃磊','經(jīng)濟(jì)學(xué)院');或INSERTINTOstudentSETSID='20200301001',sname='黃磊',college='經(jīng)濟(jì)學(xué)院';6.1.3SELECT賦值SELECT賦值可以用一個(gè)INSERT語(yǔ)句向基本表中插入多行。SELECT賦值基本語(yǔ)法格式為：INSERT[INTO]tbl_name[(col_name[,col_name]...)]{SELECT...|TABLEtable_name}[例6-2]:求每個(gè)院系的學(xué)生人數(shù)，并把結(jié)果存入數(shù)據(jù)庫(kù)中。首先在建立一個(gè)存儲(chǔ)院系名稱和學(xué)生人數(shù)的表table_sum。CREATETABLEtable_sum(collegeVARCHAR(20)PRIMARYKEY,total_numberINT);然后向table_sum表中插入行。INSERTINTOtable_sumSELECTcollege,COUNT(*)學(xué)生人數(shù)FROMstudentGROUPBYcollege;6.2更新數(shù)據(jù)UPDATE語(yǔ)句用于更新數(shù)據(jù)其基本語(yǔ)法格式為：

UPDATEtbl_name

SETcol_name={value|DEFAULT}[,...n]

[WHEREwhere_condition]參數(shù)：（1）SET子句：指明要修改的列以及應(yīng)提供的數(shù)據(jù)值，數(shù)據(jù)值可以是表達(dá)式或DEFAULT。（2）WHERE子句：指定修改數(shù)據(jù)的條件，如果不提供WHERE子句，表中的所有行都將被更新。6.2更新數(shù)據(jù)1）單字段更新UPDATEtabnameSETCOL1=VAL1 [WHEREcondition]2）多字段更新型UPDATEtabnameSETCOL1=VAL1,COL2=VAL2, ...... [WHEREcondition]當(dāng)不包含條件語(yǔ)句WHERE時(shí)，該命令對(duì)所有記錄進(jìn)行操作。例6-3:將C00004校園卡的狀態(tài)由掛失改為正常使用，并充值500元。修改前該卡的信息如表所示。

UPDATEcardSETstate='0',balance=balance+500WHERECID='C00004';6.3刪除數(shù)據(jù)SQL中，DELETE語(yǔ)句用于刪除數(shù)據(jù)，但不會(huì)改變表結(jié)構(gòu)。其基本語(yǔ)法格式為：DELETE[FROM]tbl_name[WHEREcondition]參數(shù)：WHERE子句：定義刪除條件，符合條件的數(shù)據(jù)將被刪除。如果不使用WHERE子句，將刪除表中所有行。[例6-4]:刪除C00004校園卡的消費(fèi)記錄。DELETEFROMsalebillWHERECID='C00004';[例6-5]:刪除管理學(xué)院“周萍”的消費(fèi)記錄。DELETEFROMsalebillWHERECIDIN(SELECTCIDFROMstudentWHEREcollege='管理學(xué)院'ANDsname='周萍');第7章SQL數(shù)據(jù)查詢語(yǔ)句本章介紹SQL中數(shù)據(jù)查詢語(yǔ)言的基本概念及數(shù)據(jù)操作。數(shù)據(jù)庫(kù)系統(tǒng)原理及應(yīng)用7.1數(shù)據(jù)查詢語(yǔ)句基本結(jié)構(gòu)7.2簡(jiǎn)單查詢7.3聯(lián)合查詢7.4連接查詢主要內(nèi)容7.5嵌套查詢數(shù)據(jù)查詢語(yǔ)句基本結(jié)構(gòu)7.17.1數(shù)據(jù)查詢語(yǔ)句基本結(jié)構(gòu)SELECT[ALL|DISTINCT]col_name1(,...n)FROMtable_references[WHEREwhere_condition][GROUPBYcol_name,...][HAVINGwhere_condition][ORDERBYcol_name[ASC|DESC],...]參數(shù)：（1）SELECT：指定查詢結(jié)果的列或列表達(dá)式。ALL|DISTINCT：使用ALL選項(xiàng)時(shí)，顯示符合條件的所有行。使用DISTINCT選項(xiàng)時(shí)，對(duì)于所有重復(fù)的數(shù)據(jù)行在結(jié)果集合中只保留一行。默認(rèn)為ALL。col_name1(,...n)：查詢結(jié)果中顯示的列（4）

FROM：指定查詢范圍。WHERE：指定查詢條件。不使用WHERE子句時(shí)返回所有行。GROUPBY：指定分組依據(jù)。HAVING：指定對(duì)分組結(jié)果的篩選條件。不使用HAVING子句時(shí)返回所有分組結(jié)果。ORDERBY：對(duì)查詢結(jié)果按照指定的列進(jìn)行排序，ASC是升序，DESC是降序，默認(rèn)值是升序。SELECT語(yǔ)句的基本語(yǔ)法格式簡(jiǎn)單查詢7.27.2.1SELECT子句指定查詢列1）“*”表示顯示表中所有列[例7-1]:顯示student中所有數(shù)據(jù)。SELECT*FROMstudent;3）顯示/刪除重復(fù)行2）僅顯示部分列[例7-2]：查詢student表中的學(xué)號(hào)和學(xué)生名。SELECTSID,snameFROMstudent;[例7-3]:查詢student中學(xué)生所在的院系，有多少人就顯示多少個(gè)學(xué)院名稱。SELECTALLcollegeFROMstudent;或者SELECTcollegeFROMstudent;7.2.1SELECT子句指定查詢列4）限制返回的行數(shù)

LIMITn：返回前n行；

LIMITn,m：返回第n行之后的m行。[例7-4]:查詢student中前5名學(xué)生的校園卡號(hào)和姓名。SELECT*FROMstudentLIMIT5;5）重命名

oldname[AS]newname[例7-5]:查詢student中的sname和college，把表名命名為學(xué)生表，把各列命名為對(duì)應(yīng)的中文。SELECTSIDAS學(xué)號(hào),CID卡號(hào),sname姓名,gender性別,collegeAS院系FROMstudent;7.2.2WHERE子句設(shè)置查詢條件7.2.2WHERE子句設(shè)置查詢條件1）比較運(yùn)算符（大小比較）>、>=、=、<、<=、<>、!=[例7-6]:查詢消費(fèi)金額大于等于50元的消費(fèi)信息。SELECT*FROMsalebillWHERE

payamount>=50;2）范圍運(yùn)算符（表達(dá)式值是否在指定的范圍）BETWEEN…AND…和NOTBETWEEN…AND…[例7-7]:查詢消費(fèi)金額在20元至30元之間的消費(fèi)信息。SELECT*FROMsalebillWHEREpayamountBETWEEN20AND30;3）列表運(yùn)算符（判斷表達(dá)式是否為列表中的指定項(xiàng)）IN(項(xiàng)1,項(xiàng)2……)或者NOTIN(項(xiàng)1,項(xiàng)2……)[例7-8]:查詢管理學(xué)院和機(jī)械學(xué)院的學(xué)生信息。SELECT*FROMstudentWHEREcollegeIN('管理學(xué)院','機(jī)械學(xué)院');7.2.2WHERE子句設(shè)置查詢條件4）空值判斷符(判斷表達(dá)式是否為空)ISNULL和ISNOTNULL[例7-9]:查詢尚未辦理校園卡的學(xué)生姓名和院系。SELECTsname,collegeFROMstudentWHERECIDISNULL;5）邏輯運(yùn)算符(用于多條件的邏輯連接)AND、NOT、OR其優(yōu)先級(jí)從高到低依次是NOT、AND、OR。[例7-10]:查詢2020年5月28日B003商戶的銷售情況。SELECT*FROMsalebillWHEREBID='B003'ANDsaledate='2020-5-28';7.2.2WHERE子句設(shè)置查詢條件[例7-13]:查詢密碼為“12_”開(kāi)頭的校園卡卡號(hào)。6）模式匹配符（用于字符串的模糊匹配）LIKE和NOTLIKE[例7-11]:查詢姓“張”的學(xué)生的姓名。SELECTsnameFROMstudentWHEREsnameLIKE'張__';SELECTsnameFROMstudentWHEREsnameLIKE'張%';百分號(hào)%:可匹配任意類型和長(zhǎng)度的字符。下劃線_:匹配單個(gè)任意字符，它常用來(lái)限制表達(dá)式的字符長(zhǎng)度。[例7-12]:查詢姓“張”且姓名一共三個(gè)字的人員的信息。轉(zhuǎn)義符:如果要查詢的字符串中包含%或_，需要使用轉(zhuǎn)義符。\為默認(rèn)的轉(zhuǎn)義符，也可以使用ESCAPE子句指定其他轉(zhuǎn)義符SELECTCID,passwordFROMcardWHERE`password`LIKE'12_%';7.2.3聚合函數(shù)1）計(jì)數(shù)函數(shù)COUNT2）求和函數(shù)SUM3）求平均值函數(shù)AVG7.2.3聚合函數(shù)4）求最大值函數(shù)MAX5）求最小值值函數(shù)MIN7.2.3聚合函數(shù)[例7-14]:統(tǒng)計(jì)學(xué)生表中的學(xué)生總?cè)藬?shù)。SELECTCOUNT(*)FROMstudent;[例7-15]:統(tǒng)計(jì)消費(fèi)清單中單比消費(fèi)的最大值和最小值，以及總金額和平均消費(fèi)水平。SELECTMAX(payamount)，MIN(payamount)，SUM(payamount)，AVG(payamount)FROMsalebill;7.2.4GROUPBY子句進(jìn)行分組計(jì)算分組的目的是細(xì)化聚合函數(shù)的統(tǒng)計(jì)范圍。分組語(yǔ)句跟在WHERE子句的后面，它的基本形式為

GROUPBYcol_name[例7-16]:統(tǒng)計(jì)每個(gè)院系的學(xué)生人數(shù)。SELECTcollegeAS院系,COUNT(*)AS學(xué)生人數(shù)FROMstudentGROUPBYcollege;7.2.5HAVING子句對(duì)分組結(jié)果進(jìn)行篩選子句HAVING用來(lái)對(duì)GROUPBY后的數(shù)據(jù)進(jìn)行條件篩選。HAVING子句的基本語(yǔ)法格式為：

HAVINGwhere_condition

參數(shù)：

where_condition：HAVING的條件表達(dá)式中一般都包含聚合函數(shù)，

以此來(lái)篩選符合條件的分組。子句HAVING必須放在GROUPBY之后，ORDERBY之前。[例7-17]:統(tǒng)計(jì)每個(gè)學(xué)院的學(xué)生數(shù)，只列出人數(shù)大于2人的學(xué)院名稱和學(xué)生總數(shù)。SELECTSchoolAS學(xué)院名稱,COUNT(*)AS學(xué)生數(shù)FROMTable_Student GROUPBYSchool HAVINGCOUNT(*)>27.2.6ORDERBY子句對(duì)查詢結(jié)果排序ORDERBY子句可以對(duì)查詢結(jié)果排序。ORDERBY子句的語(yǔ)法格式為：

ORDERBYcol_name[ASC|DESC][,…n]

參數(shù)：

ASC表示升序，為默認(rèn)值，DESC表示降序；

對(duì)多個(gè)列排序，列名之間用逗號(hào)分開(kāi)，每個(gè)列都可以指定按升序或降序排序。[例7-18]:

查詢校園卡的卡號(hào)和消費(fèi)總額，并將查詢結(jié)果按消費(fèi)總額的升序排序。SELECTCID,SUM(payamount)消費(fèi)總額FROMsalebillGROUPBYCIDORDERBYSUM(payamount);聯(lián)合查詢7.3集合查詢種類聯(lián)合查詢語(yǔ)法格式select_statementUNION[ALL|DISTINCT]selectstatementUNION[ALL|DISTINCT]selectstatement][…n]

參數(shù)：select_statement：待聯(lián)合的SELECT查詢語(yǔ)句。

ALL：不會(huì)消除重復(fù)行直接返回聯(lián)合運(yùn)算的結(jié)果。

DISTINCT：消除重復(fù)行。MySQL僅支持UNION集合運(yùn)算INTERSECT和EXCEPT和UNION語(yǔ)法格式類似聯(lián)合查詢[例7-19]:

查詢消費(fèi)總額低于100元或高于300元的校園卡信息。SELECTCID校園卡號(hào),SUM(payamount)消費(fèi)總額FROMsalebillGROUPBYCIDHAVINGSUM(payamount)<100UNIONSELECTCID校園卡號(hào),SUM(payamount)消費(fèi)總額FROMsalebillGROUPBYCIDHAVINGSUM(payamount)>300;聯(lián)合查詢聯(lián)合查詢時(shí)，查詢結(jié)果的列標(biāo)題為第一個(gè)查詢語(yǔ)句的列標(biāo)題。因此，要定義列標(biāo)題必須在第一個(gè)查詢語(yǔ)句中定義。要對(duì)聯(lián)合查詢結(jié)果排序時(shí)，也必須使用第一查詢語(yǔ)句中的列名、列標(biāo)題或者列序號(hào)。在使用聯(lián)合查詢時(shí)，應(yīng)保證每個(gè)聯(lián)合查詢語(yǔ)句的選擇列表中列數(shù)相同，對(duì)應(yīng)列的數(shù)據(jù)類型相同，或是可以自動(dòng)將它們轉(zhuǎn)換為相同的數(shù)據(jù)類型。在自動(dòng)轉(zhuǎn)換時(shí)，對(duì)于數(shù)值類型，系統(tǒng)將低精度的數(shù)據(jù)類型轉(zhuǎn)換為高精度的數(shù)據(jù)類型。連接查詢7.4為了從多個(gè)表中獲取數(shù)據(jù)，需要進(jìn)行多表連接。連接查詢?cè)赟ELECT

語(yǔ)句的FROM子句中實(shí)現(xiàn)語(yǔ)法格式為FROM

join_table

join_type

join_table[ON

(join_condition)]其中，join_table指出參與連接操作的表名，連接可以對(duì)同一個(gè)表操作，也可以對(duì)多表操作，對(duì)同一個(gè)表操作的連接又稱做自連接。join_type指的是連接類型，可分為內(nèi)連接、外連接和交叉連接三種類型。ON

(join_condition)

子句指出連接條件，它由被連接表中的列和比較運(yùn)算符、邏輯運(yùn)算符等構(gòu)成。連接查詢7.4.1內(nèi)連接（Inner

Join或Join）使用比較運(yùn)算符進(jìn)行表間某(些)列數(shù)據(jù)的比較操作，并列出這些表中與連接條件相匹配的數(shù)據(jù)行。（1）等值連接:在連接條件中使用等于（=）運(yùn)算符比較被連接列的列值，其查詢結(jié)果中列出被連接表中的所有列，包括其中的重復(fù)列。（2）自然連接：在連接條件中使用等于（=）運(yùn)算符比較被連接列的列值，但它使用選擇列表指出查詢結(jié)果集合中所包括的列，并刪除連接表中的重復(fù)列。（3）不等連接

：在連接條件使用除等于運(yùn)算符以外的其它比較運(yùn)算符比較被連接的列的列值。這些運(yùn)算符包括>、>=、<=、<、!>、!<和<>。7.4.1內(nèi)連接[例7-20]:查詢?cè)?019年6月29號(hào)，消費(fèi)金額累計(jì)超過(guò)20元的學(xué)生學(xué)號(hào)和其消費(fèi)總額。SELECTStudentID,SUM(PayAmount)AS消費(fèi)總額FROMstudentAJOINsalebillBONA.CardID=B.CardIDWHERESaleDate='2019-06-29'GROUPBYStudentIDHAVINGSUM(PayAmount)>20多表的連接查詢SELECTa.*,b.*,c.*,d.*FROMTable_StudentaJOINTable_Cardb ONa.CardID=b.CardIDJOINTable_SaleBillc ONb.CardID=c.CardIDJOINTable_Machined ONc.MachineID=d.MachineID7.4.2外連接外連接分為三種左外連接（LeftOuterJoin或LeftJoin）右外連接（Right

Outer

Join或RightJoin）全外連接（Full

Outer

Join或Full

Join）MySQL中沒(méi)有全外連接7.4.2外連接外連接分為三種左外連接（LeftOuterJoin或LeftJoin）右外連接（Right

Outer

Join或RightJoin）全外連接（Full

Outer

Join或Full

Join）MySQL中沒(méi)有全外連接7.4.2外連接7.4.2外連接7.4.2外連接7.4.2外連接左外連接左連接的含義是不管表1的元組是否滿足連接條件，均輸出表1的內(nèi)容。其語(yǔ)法格式為FROM表1LEFTJOIN表2ON<連接條件>[例7-21]:查詢所有飯卡的消費(fèi)情況，包括沒(méi)有消費(fèi)的飯卡和消費(fèi)的飯卡。SELECTcard.CardID,salebill.CardID,PayAmount,SaleDateFROMcardLEFTJOINsalebillONcard.CardID=salebill.CardID7.4.2外連接右外連接右外連接的含義是不管表2的元組是否滿足連接條件，均輸出表2的內(nèi)容。其語(yǔ)法格式為FROM表1RIGHTJOIN表2ON<連接條件>[例7-22]:查詢所有飯卡的消費(fèi)情況，包括沒(méi)有消費(fèi)的飯卡和消費(fèi)的飯卡。SELECTcard.CardID,salebill.CardID,PayAmount,SaleDateFROMsalebillRIGHTJOINcardONcard.CardID=salebill.CardID;7.4.2外連接全外連接全外連接沒(méi)有WHERE子句，它返回連接表中所有數(shù)據(jù)行的笛卡爾積，其結(jié)果集合中的數(shù)據(jù)行數(shù)等于第一個(gè)表中符合查詢條件的數(shù)據(jù)行數(shù)乘以第二個(gè)表中符合查詢條件的數(shù)據(jù)行數(shù)。嵌套查詢7.5嵌套查詢嵌套查詢嵌套查詢1）子查詢必須用括號(hào)包含。2）子查詢中不能使用ORDERBY子句，ORDERBY子句永遠(yuǎn)只能對(duì)外層查詢結(jié)果排序。3）返回多行的子查詢可以和多值操作符如IN一起使用。4）BETWEEN操作符不能和子