目錄CONTENTSSQL語句產生發展SQL語句特點SQL語句功能1231.SQL語句產生發展

SQL（StructedQueryLanguage，結構化查詢語言）是操作關系數據庫的通用語言。SQL名為查詢語言，但不只支持數據庫查詢操作，其功能還包括數據定義、數據操縱、數據控制等數據庫管理系統盡管軟件存在差異，但都有SQL軟件或與SQL的接口軟件。雖然各個數據庫廠商附帶的SQL軟件產品對SQL語言支持很相似，但也存在一定的差異1.SQL語句產生發展IBM研究人員在20世紀70年代研究出SQL原型，命名為SEQUEL現在的SQL標準已經包括了:SQL框架、SQL調用接口、SQL永久存儲模塊、SQL宿主語言綁定、SQL外部數據管理、XML相關規范等內容。2.SQL特點SQL是一個綜合、功能強大又簡單易學的語言，從數據庫定義到數據庫維護都提供了相應功能。其主要特點如下：一體化高度非過程化語言簡潔多種使用方式3.SQL語言功能概述SQL的功能主要包括：數據定義、數據查詢、數據操縱、數據控制

DatabasePrincipleandApplication數據庫原理及應用3.2.1數據庫定義及維護重慶理工大學計算機科學與工程學院目錄CONTENTS數據庫定義數據庫維護121.數據庫定義數據庫中的所有的數據、對象和事務日志均以文件的形式保存。根據作用不同，這些文件可分為數據文件與事務日志文件。

數據文件可根據數據存儲需要進行組織，除了必須的一個主數據文件，還可以包括一個或多個次數據文件。1.數據庫定義主數據文件用于存儲數據庫的系統表，其文件擴展名為mdf。次數據文件用于存儲主數據文件中未存儲的數據和數據對象。其文件擴展名為ndf。事務日志文件用于記錄對數據庫的操作情況。其文件擴展名為ldf。1.數據庫定義數據庫使用CREATEDATABASE語句實現，其一般格式如下：CREATEDATABASEdatabase_name[ON(NAME=logical_file_name,[,FILENAME={'os_file_name'|'filestream_path'}][,SIZE=size[KB|MB|GB|TB]]][,MAXSIZE={maxsize[KB|MB|GB|TB]|UNLIMITED}][,FILEGROWTH=growth_increment[KB|MB|GB|TB|%])][LOGON(NAME=logical_file_name,[,FILENAME={'os_file_name'|'filestream_path'}][,SIZE=size[KB|MB|GB|TB]]][,MAXSIZE={maxsize[KB|MB|GB|TB]|UNLIMITED}][,FILEGROWTH=growth_increment[KB|MB|GB|TB|%])]1.數據庫定義示例1：創建一個只設置名稱的數據庫，數據庫名稱為dbtest。CREATEDATABASEdbtest2.數據庫維護示例2：修改數據庫sjkDB中數據文件的初始大小，將其初始大小改為9MB,最大為120MB。ALTERDATABASEsjkDBMODIFYFILE(NAME=sjkDB_data,SIZE=9,MAXSIZE=120)2.數據庫維護示例3：為數據庫sjkDB添加新的日志文件，邏輯名稱為sjkDBlog1，存儲路徑為E:\teaching，物理文件名為sjkDBlog1.ldf，初始大小3MB，增量1MB，最大20MB。ALTERDATABASEsjkDBADDLOGFILE(NAME=sjkDBlog1,FILENAME='E:\teaching\sjkDBlog1.ldf',SIZE=3,MAXSIZE=20,FILEGROWTH=1)2.數據庫維護示例4：將數據庫test更名為test_1示例5：使用DROPDATABASE語句刪除數據庫dbtest。ALTERDATABASEtestmodifyname=test_1DROPDATABASEdbtest

DatabasePrincipleandApplication數據庫原理及應用3.2.2表定義及維護重慶理工大學計算機科學與工程學院目錄CONTENTS表定義數據類型12表維護31.表定義<表名>：所要定義的基本表的名字<列名>：組成該表的各個屬性（列）<列級完整性約束條件>：涉及相應屬性列的完整性約束條件<表級完整性約束條件>：涉及一個或多個屬性列的完整性約束條件注：如果完整性約束條件涉及到該表的多個屬性列，則必須定義在表級上，否則列級或表級都可以。CREATETABLE<表名>(<列名><數據類型>[<列級完整性約束條件>]

[,<列名><數據類型>[<列級完整性約束條件>]]

…

[,<表級完整性約束條件>]);1.表定義[例1]建立“學生”表Student。學號是主碼，姓名取值唯一。

CREATETABLEStudent

(SNOCHAR(9)PRIMARYKEY,/*列級完整性約束條件,Sno是主碼*/

SNameCHAR(20)UNIQUE,/*Sname取唯一值*/SsexCHAR(2),SageSMALLINT,SdeptCHAR(20));命名格式：

constraint<完整性約束名><完整性約束條件>constraints_pkconstraints_uk2.數據類型數據類型含義CHAR(n),CHARACTER(n)長度為n的定長字符串VARCHAR(n),CHARACTERVARYING(n)最大長度為n的變長字符串INT，INTEGER長整數（4字節）SMALLINT短整數（2字節）BIGINT大整數（8字節）NUMERIC(p，d)定點數，由p位數字（不包括符號、小數點）組成，小數后面有d位數字DECIMAL(p,d),DEC(p,d)同NUMERICDATE日期，包含年、月、日，格式為YYYY-MM-DDTIME時間，包含一日的時、分、秒，格式為HH:MM:SS3.表維護--修改表ALTERTABLE<table_name>[ADD<column_name><data_type>[constaint]]/*增加列*/[DROPCOLUMN<column_name>]/*刪除列*/[ALTERCOLUMN<column_name><data_type>[constaint]]/*修改列*/3.表維護--刪除表【例】將例3-9的“商品種類”表添加一列，用以存放描述商品大類的數據，比如牙刷屬于日用品類。ALTERTABLEcategoryADDCat_CategoryNOvarchar(20)【例】將例3-8的goods表的barcode列刪除。ALTERTABLEgoodsDROPCOLUMNbarcode【例】將例3-10的Supplier表Suppliername列的數據類型修改為nvarchar(200)，且不允許為空。ALTERTABLESupplierALTERCOLUMNSuppliernamenvarchar(200)notnull

3.表維護--刪除表DROPTABLE<表名>［RESTRICT|CASCADE］;RESTRICT：刪除表是有限制的。欲刪除的基本表不能被其他表的約束所引用如果存在依賴該表的對象，則此表不能被刪除CASCADE：刪除該表沒有限制。

在刪除基本表的同時，相關的依賴對象一起刪除3.表維護--刪除表[例2]刪除Student表

DROPTABLEStudentCASCADE;基本表定義被刪除，數據被刪除表上建立的索引、視圖、觸發器等一般也將被刪除

DatabasePrincipleandApplication數據庫原理及應用3.2.3完整性定義及維護重慶理工大學計算機科學與工程學院目錄CONTENTS完整性約束定義完整性約束維護121.完整性約束定義數據的完整性是指數據的正確性與相容性的要求。完整性約束的作用范圍可以分為：列級約束：某列的約束；比如，年齡>0元組約束：元組中各字段之間聯系的約束；比如：結束日期>=開始日期關系約束：關系之間聯系的約束;比如：供貨商表里沒有的商家不能提供商品；SQL代碼格式：ALTERTABLE<table_name>[ADD[<constraint><constraint_name>]<constraint>]/*增加約束*/一、完整性約束

示例4：為員工表添加主碼

示例5：為薪資表的薪級名稱列添加UNIQUE約束SQL代碼：ALTERTABLE員工表ADDCONSTRAINTpk_ygPRIMARYKEY(員工編號)/*pk_yg約束名*/SQL代碼：ALTERTABLE薪資表ADDCONSTRAINTU_xinznameUNIQUE(薪級名稱)/*U_xinzname約束名*/1.完整性約束定義二、自定義完整性約束

示例6：薪資表的基礎薪資列定義DEFAULT約束

示例7：薪資表的實發薪資列添加CHECK約束，使其值小于應發薪資列SQL代碼：ALTERTABLE薪資表ADDCONSTRAINTDF_jichuDEFAULT2000FOR基礎薪資SQL代碼：ALTERTABLE薪資表WITHNOCHECK/*表示該約束對舊數據不作用，對新數據約束*/ADDCONSTRAINTCK_shifaCHECK(實發薪資<應發薪資)1.完整性約束定義三、參照完整性約束參照完整性屬于表間規則，是指一個表中的主碼與另一個表外碼之間的關系，保證兩個表的相容性。若主碼與外碼來自同一個表，則稱自參照完整性。只要外碼值存在，主碼表中的數據就不能任意修改與刪除，除非設置了級聯刪除與修改。

SQL語句使用：FOREIGNKEY

實現參照完整性

示例8：員工表的薪級編碼列添加外碼約束，引用薪資表的薪級編號SQL代碼：ALTERTABLE員工表ADDCONSTRAINTFK_xinjiFOREIGNKEY(薪級編號)REFERENCES薪資表(薪級編號)1.完整性約束定義2.約束條件維護三、參照完整性約束示例8：員工表的薪級編碼列添加外碼約束，引用薪資表的薪級編號對參照表(外碼表)添加元組或修改外碼屬性列時，都有可能破壞參考完整性約束；對被參照表(主碼表)刪除元組或修改主碼屬性列時，也會破壞參考完整性約束；處理這些破壞完整性約束的操作，三種方式：拒絕、級聯刪除（修改）和設置為空值；SQL代碼：ALTERTABLE員工表ADDCONSTRAINTFK_xinjiFOREIGNKEY(薪級編號)REFERENCES薪資表(薪級編號)/*員工表：外碼表；薪資表：主碼表*/三、參照完整性約束

如何能讓參照約束實現聯級刪除(修改)？示例9：員工表的薪級編碼列添加外碼約束，引用薪資表的薪級編號。定義該完整性約束可以級聯刪除或修改。SQL代碼：ALTERTABLE員工表ADDCONSTRAINTFK_xinjiFOREIGNKEY(薪級編號)REFERENCES薪資表(薪級編號)ONDELETECASCADE/*級聯刪除*/ONUPDATECASCADE/*級聯修改*/1.完整性約束定義四、多種約束合并添加

上述的三種的約束，可以在表定義的時候一并進行定義添加。

SQL代碼：CREATETABLE員工表(員工編號char(7)primarykey,姓名nchar(5)notnull,入職日期smalldatetimenotnull,轉正日期smalldatetime,手機號碼char(11)uniqueCHECK(手機號碼LIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),薪級編號char(3),FOREIGNKEY(薪級編號)REFERENCES薪資表(薪級編號)ONDELETECASCADEONUPDATECASCADE)1.完整性約束定義五、完整性約束刪除對完整性約束的維護主要是修改和刪除約束。修改約束可以先刪除約束，再添加同名約束。刪除約束的語句如下：

SQL代碼：ALTERTABLE<table_name>[DROP<constraint_name>]/*刪除約束*/2.完整性約束維護

DatabasePrincipleandApplication數據庫原理及應用3.2.4索引定義及維護重慶理工大學計算機科學與工程學院目錄CONTENTS索引的定義索引的維護121.索引的定義索引的由來：在很多時候，由于表中的數據量比較大時，查詢操作會變得非常耗時，索引就是用來提高查詢速度的重要手段。索引與圖書目錄類似，查找書本內容，可以在目錄中直接查看該內容在書本中的頁數，而不需要查閱整本書。目錄索引索引表頁碼可以幫助快速找到對應的內容1.索引的定義索引可以加快查詢速度，但索引表本身會占用用戶數據庫空間，在對數據進行插入、更新、刪除時，維護索引也會增加時間成本。

因此，我們在建立索引時，需要綜合考慮；索引可分為：聚集索引，非聚集索引，唯一索引等種類

聚集索引：是指數據表中的數據按照索引關鍵字順序存儲，表設置主碼后，就會建立一個主碼上的聚集索引。因為一個表的數據只能按照一種物理順序存儲，所以一個表上只能有一個聚集索引1.索引的定義

非聚集索引：則不要求數據表的數據按照索引關鍵字順序排序，表的物理順序與索引關鍵字順序不同。一個表上可以有多個非聚集索引。

唯一索引：索引關鍵字不允許重復。如果在Student表“SName”字段上建立了唯一索引，則SName的值不允許重復

聚集索引與非聚集索引都可以是唯一索引。1.索引的定義索引的SQL定義格式：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]/*定義索引類型*/INDEXindex_name/*定義索引名稱*/ONtable_name(column[ASC|DESC][,..n])/*定義索引屬性列及次序,默認為升序*/1.索引的定義索引創建實例

示例1：為“薪資表”中的“薪級名稱”建立唯一非聚集升序索引

示例2：按應發薪資升序和實發薪資降序建立唯一索引CREATEUNIQUENONCLUSTEREDINDEXindex_name1ON薪資表(薪級名稱ASC)CREATEUNIQUEINDEXindex_name2ON薪資表(應發薪資ASC,實發薪資DESC)1.索引的定義查看索引

如果我們要查看某張表上的索引，可以使用系統存儲過程Sp_helpindex查看所建立索引，比如查看薪資表索引語句為通過上圖我們可以發現，主碼約束其實是一種索引，在數據庫系統中描述為“clustered,unique,primarykey…”；UNIQUE約束默認為非聚集唯一索引，描述為“nonclustered,unique,uniquekey…”Sp_helpindex薪資表2.索引的維護索引維護索引一經建立，就有數據庫系統維護，無需用戶參與。常見準則避免在經常更新的表上建立過多索引，如果建立聚集索引，應設置較短索引建長度。對經常用于查詢中的謂詞和連接條件的列建立非聚集索引。在經常用作查詢過濾的列上建立索引。在查詢中經常進行GROUPBY、ORDERBY的列上建立索引。在不同值較少的列上不必要建立索引，如性別字段。對于經常存取的列避免建立索引。在經常存取的多個列上建立復合索引，但要注意復合索引的建立順序要按照使用的頻度來確定。考慮對計算列建立索引。2.索引的維護刪除索引

SQL基本格式如下

示例3：刪除薪資表上所有索引DROPINDEXtable_name.index_name或者DROPINDEXindex_nameONtable_nameDROPINDEX薪資表.index_name1,薪資表.index_name2或者DROPINDEXindex_name1

on薪資表,index_name2on薪資表

DatabasePrincipleandApplication數據庫原理及應用3.3.1單表查詢重慶理工大學計算機科學與工程學院目錄CONTENTSSELECT語句格式

列操作12

行操作

排序34

聚合函數

分組統計561.SELECT語句格式SQL使用SELECT語句一般格式：SELECT[ALL|DISTINCT]<TargetColumn|Expression>[,<TargetColumn|Expression>]..FROM<TABLE_name|VIEW_name>[,<TABLE_name|VIEW_name>...][WHERE<ConditionalExpression>][GROUPBY<COLUMN_name>[,COLUMN_name...]HAVING<ConditionalExpression>]][ORDERBY<COLUMN_name>[ASC|DESC]];2.列操作單表查詢是From子句后面的數據表只有一張的查詢。選擇列即關系代數中的投影運算。SELECT子句可以查詢指定列、表達式。

SELECT目標列|表達式………FROMtable1）查詢指定列查詢指定列可以是部分列，也可以是全部列，列的顯示順序由SELECT子句目標列順序決定。

示例1：查詢全體學生姓名、學號、專業SELECTSName,SNO,MajorFROMStudent2.列操作1）查詢指定列單表查詢所有數據

示例2：查詢全體學生的詳細信息

如果列的顯示順序與表中的列順序一致，可以將目標列用*代替SELECTSNO,SName,BirthYear,Ssex,college,Major,WeiXinFROMStudentSELECT*FROMStudent2.列操作2）查詢表達式的值SELECT子句中的表達式（Expression）可以是包含列的計算表達式，也可以是常量或函數。

示例3：查詢全體學生的學號、姓名、年齡

其中2019-BirthYear是一個計算表達式，我們也可以使用系統函數GETDATE()，讀取當前時間，在用函數YEAR()讀取年份。SELECTSNO,SName,2019-BirthYearFROMStudentSELECTSNO,SName,YEAR(GETDATE())-BirthYearFROMStudent2.列操作2）查詢表達式的值表達式的計算值被記錄在結果集中，但沒有列名，顯示為“無列名”，可以使用AS子句為其添加別名記錄其語義，AS也可以省略。

示例4：查詢全體學生的學號、姓名、年齡（年齡使用別名：Age）

SELECTSNO,SName,YEAR(GETDATE())-BirthYearAS

AgeFROMStudentSELECTSNO,SName,‘Age’,YEAR(GETDATE())-BirthYear‘Age’FROMStudent2.列操作3）去掉重復列

示例5：查詢購買了商品的學生學號

上述SQL語句查詢出來的學生學號是有重復的。我們可以使用DISTINCT關鍵字，去掉重復數據。

SELECTSNOFROMSaleBillSELECTDISTINCTSNOFROMSaleBill2.列操作3.行操作--WHERE子句在前面選擇列的例子中，都是查詢表的全部元組。SQL可以使用WHERE子句對元組進行篩選。WHERE子句的格式如下：

其中ConditionalExpression表示條件表達式或條件運算符

常用的條件運算符如表所示：[WHERE<ConditionalExpression>]

比較大小

在數據庫中的數據中，存在大量的數據是可以用進行比較大小的。比如：成績、金額、交易日期、時間等等，當然如果是等于或不等于比較的話，使用的數據類型就更加廣泛了；

SQL語句提供了

=（等于）>

（大于）

<（小于）>=（大于等于）

<=（小于等于）!=（不等于）

<>（不等于）

!>（不大于）

!<（不小于）3.行操作--比較運算符

比較大小

示例1：查詢管理信息系統專業學生名單

示例2：查詢年齡不大于18的學生名單

SELECT*FROMStudentWHEREMajor=‘MIS‘

/*MIS代表：管理信息系統*/SELECT*FROMStudentWHEREYEAR(GETDATE())-BirthYear!>20/*<=*/3.行操作--比較運算符

確定范圍謂詞BETWEENAND可以確定取值范圍，BETWEEN

后跟范圍下限，AND

后跟上限。NOTBETWEENAND確定取值范圍以外的值。

示例3：查詢現貨存量在3到10之間的商品信息

示例4：查詢2019年生產的商品信息

SELECT*FROMGOODSWHEREProductTimeBETWEEN'2019-1-1'AND'2019-12-31'SELECT*FROMGoodsWHERENumberBETWEEN3AND103.行操作--范圍運算符

確定范圍

示例5：查詢年齡在18至20歲以外的學生信息

等價于

SELECT*FROMStudentWHEREYEAR(GETDATE())-BirthYear<18AND

YEAR(GETDATE())-BirthYear>20SELECT*FROMStudentWHEREYEAR(GETDATE())-BirthYear

NOTBETWEEN18AND203.行操作--范圍運算符確定范圍

示例6：查詢姓名在'李明'和'閔紅'之間的學生信息中文字符串按字符拼音字母先后排序，如果拼音第一字母相同，則比較第二字母，以此類推

SELECT*FROMStudentWHERESNameBETWEEN'李明‘AND'閔紅'3.行操作--范圍運算符

確定集合謂詞IN用來查找屬性值屬于指定集合的元組。NOTIN運算符的含義相反，查找屬性值不屬于指定集合的元組。

示例7：查詢商品編號為GN0001、GN0002的銷售信息

SELECT*FROMSaleBillWHEREGoodsNOIN('GN0001','GN0002')3.行操作--集合運算符

確定集合

示例8：查詢不是MIS專業的學生信息

等價于：

SELECT*FROMStudentWHEREMajorNOTIN('MIS')SELECT*FROMStudentWHEREMajor!='MIS'3.行操作--集合運算符字符匹配在字符查詢條件不確定時，可以使用LIKE運算符進行模糊查詢。LIKE運算符通過匹配部分字符達到查詢目的一般格式如下：

[NOT]LIKE‘<匹配串>’[ESCAPE‘<轉義字符>’]3.行操作--字符匹配字符匹配匹配串可以是完整的字符串，也可以是含有通配符的字符串。通配符包括如下四種：

%（百分號）：匹配任一長度字符串，可以是0個，也開始多個。

_（下劃線）：匹配任一字符。

[]：數據表列值匹配[]中任一字符成功，該LIKE運算符結果均為TRUE。如果[]中的字符連續的，可以使用“-”代表中間部分。比如a、b、c、d，記為[a-d]。

[^]：表示不匹配[]中的任意字符。比如不匹配a-d之間的字符，記為[^a-d]3.行操作--字符匹配字符匹配

示例1：查詢商品名稱中包含“咖啡”的商品信息

上面的SQL語句表示：不管字符串“咖啡”在元組GoodsName列的開頭、結尾還是中間，該元組就會被篩選出來。SELECT*FROMGoodsWHEREGoodsNameLIKE'%咖啡%'3.行操作--字符匹配字符匹配

示例2：查詢學生姓名第二個字為“民”的學生信息

上面的SQL語句表示：如果去掉后面的%，那就是表示查詢姓名為兩個字，第二個字為“民”的學生信息，該題是單字符和多字符模糊匹配的組合應用。SELECT*FROMStudentWHERESNameLIKE'_民%'3.行操作--字符匹配字符匹配

示例3：商品編號最后一位不是1、4、7的商品信息

上面的SQL語句表示：括號內所列字符中的一個（類似正則表達式）。指定一個字符、字符串或范圍，要求所匹配對象為它們中的任一個。[]中的內容可以是‘123456’、‘abcdef’也可以是‘1-6’、‘a-f’SELECT*FROMGoodsWHEREGoodsNONOTLIKE'%[147]'等同于SELECT*FROMGoodsWHEREGoodsNOLIKE'%[^147]'3.行操作--字符匹配字符匹配如果查詢的字符串含有通配符，為了與通配符區分開，需要使用ESCAPE關鍵字對通配符進行轉義，告訴數據庫系統該字符不是通配符，而是字符本身。ESCAPE關鍵字后所跟的一個字符為轉義字符，轉義字符后所跟字符不再為通配符，而是代表其本來含義。

示例4：要查找包含有5%的元組：其中字符“a”即為轉義字符，表明其后的“%”不是通配符，而是百分號WHEREcolumn_nameLIKE'%5a%%'ESCAPE'a'3.行操作--字符匹配字符匹配

示例5：查詢包含“[]”元組，WHERE子句如圖：其中字符“!”即為轉義字符，表明其后的“[]”不是通配符，而是方括號本身的含義。WHEREcolumn_nameLIKE'%![%!]%'ESCAPE'!'3.行操作--字符匹配空值查詢空值（NULL）在數據庫中表示不確定值，即在字符集中沒有確定值與之對應。未對某元組的某個列輸入值，就會形成空值（NULL）。

涉及空值的判斷，不能用前述運算符，只能使用IS或ISNOT來判斷

示例6：查詢還沒有輸入供應商編號的商品信息

【注：SQL中的NULL值還是導致許多錯誤的罪魁禍首】SELECT*FROMGoodsWHERESupplierNOISNULL3.行操作--空值處理多重條件查詢使用運算符AND和OR可以連接多個查詢條件。多個運算符的執行順序是從左至右，AND的運算級別高于OR，用戶可以使用小括號改變優先級。AND連接的條件只有所有子表達式為TRUE時，整個表達式的結果才為TRUE。OR連接的條件只有所有的子表達式為FALSE，整個表達式的結果才為FALSE。

示例7：查詢AC專業的學生和MIS專業男生的信息SELECT*FROMStudentWHEREMajor='AC'ORMajor='MIS'ANDSsex='男'3.行操作--多重條件多重條件查詢

如果示例7的SQL，如圖進行修改。則其語義變為：查詢AC專業和MIS專業的男生信息，即查詢兩個專業的男生信息。

()小括號改變了優先級別;SELECT*FROMStudentWHERE(Major='AC'ORMajor='MIS')ANDSsex='男'3.行操作--多重條件4.排序如果要對查詢結果進行排序，可以按照ORDERBY子句指定升序（ASC）或降序（DESC）排列。

示例1：查詢學生信息，按出生年升序排列BirthYear沒有指定是升序還是降序，那默認是升序

SELECT*FROMStudentORDERBYBirthYear

ORDERBY子句后也可以跟多個字段。先按第一個字段的順序排列，如果第一字段的排序結果相同，則按第二個字段順序排列，以此類推。

示例2：查詢商品名含“咖啡”的商品的商品編號、商品名、現貨存量和生產時間。按現貨存量升序、生產日期降序排列

查詢結果先按照貨存量升序、然后生產日期降序排列結果。SELECTGoodsNO,GoodsName,Number,ProductTimeFROMGoodsWHEREGoodsNameLIKE'%咖啡%'ORDERBYNUMBERASC,ProductTimeDESC4.排序

ODERBY子句后也可以跟表達式、函數等。

示例3：查詢商品表的商品編號、商品名稱、現貨存量、生產日期、

保質期剩余天數，按保質期剩余天數升序排列。

上述語句就可以滿足，對過期商品的篩選業務需求。SELECTGoodsNO,GoodsName,Number,ProductTime,QGPeriod*30-DATEDIFF(day,ProductTime,GETDATE())保質期剩余天數FROMGoodsORDERBYQGPeriod*30-DATEDIFF(day,ProductTime,GETDATE())4.排序5.聚合函數

SQL使用聚合函數提供了一些統計功能，常見聚合函數及功能，如表所示：

上述函數除了COUNT(*)外，其余函數均忽略NULL值。

1).COUNT

示例4：查詢商品個數聚合函數計算時如果要忽略重復值，則要指定統計列為DISTINCT。

示例5：查詢售出商品種類數

SELECTCOUNT(*)FROMGoodsSELECTCOUNT(DISTINCTGoodsNO)FROMSaleBill5.聚合函數

2).SUM

示例6：查詢商品總的銷售量。

3).AVG、MAX、MIN

示例7：統計銷售表中單次售出量最多、最少和平均值

SELECTSUM(Number)總銷售量FROMSaleBillSELECTMAX(Number)最大銷售量,MIN(Number)最小銷售量,

AVG(Number)平均銷售量FROMSaleBill5.聚合函數6.分組統計分組就是將查詢結果按照某一列或多列的值進行分組，值相同的分為一組。

對查詢結果分組的目的是為了細化聚合函數的作用范圍，沒有分組聚合函數作用于所有數據，分組后，聚合函數將作用于每組數據。

示例1：統計每個學生購買的商品種類數

SELECTSNO,COUNT(DISTINCTGoodsNO)AS商品種類數FROMSaleBillGROUPBYSNO

SQL使用GROUPBY子句對元組分組。數據表中的列只有出現在GROUPBY子句后的列才能放在SELECT后面的目標列中，否則SQLServer會提示出錯信息，“因為該列沒有包含在聚合函數或GROUPBY子句中”

錯誤示例1：統計每個學生購買的商品種類

SELECTSname,SNO,COUNT(DISTINCTGoodsNO)AS商品種類FROMSaleBillGROUPBYSNO上述語句要報錯：Sname沒有包含在聚合函數或GROUPBY子句中6.分組統計分組查詢可以先對數據使用WHERE進行選擇，再使用GROUPBY分組查詢，一般情況下，可以提高查詢效率。

示例2：統計2019年學生購買的商品種類數

SELECTSNO,COUNT(DISTINCTGoodsNO)AS商品種類數FROMSaleBillWHEREYEAR(HappenTime)=2019GROUPBYSNO6.分組統計分組條件

對查詢結果除了可以分組之外，SQL同時還可以再分組的基礎上，進行條件篩選。使用的關鍵字是HAVING

示例3：統計每個學生購買的商品種類數，列出購買3種或三種以上商品學生的學號，購買商品種類數

SELECTSNO,COUNT(DISTINCTGoodsNO)AS商品種類數FROMSaleBillGROUPBYSNOHAVINGCOUNT(DISTINCTGoodsNO)>=36.分組統計

HAVING對組進行選擇，后面可以跟列名、聚合函數作為條件表達式。

WHERE對元組進行選擇，因此聚合函數不能出現在WHERE子句里作為條件表達式。

示例4：統計學生表中每年出生的男、女生人數，按出生年降序、人

數升序排列SELECTBirthYear,Ssex,COUNT(*)FROMStudentGROUPBYBirthYear,SsexORDERBYBirthYearDESC,COUNT(*)6.分組統計

DatabasePrincipleandApplication數據庫原理及應用3.3.2多表連接查詢重慶理工大學計算機科學與工程學院目錄CONTENTS

內連接

自連接12

外連接31.內連接--多表查詢前面的單表查詢只涉及一個表的數據，更多的時候需要從多個表中查詢數據。涉及到兩個或兩個以上表的查詢，需要連接后查詢。連接包括內連接和外連接。

多表查詢，最常見的連接方式是：內連接，內連接是一種常見的查詢方式。內連接包括非等值連接、等值連接。等值連接的連接字段如果一樣，去掉重復的列，就是自然連接。

在SQL中，實現內連接有兩種方式：一種采用WHERE子句將連接字段的條件表達式表達出來。

示例1：將商品表與商品種類表連接起來的語句商品表的字段CategoryNO與商品類別表的字段CategoryNO語義相同、數據類型相同（相容），被用作連接字段SELECT*FROMGoods,CategorywhereGoods.CategoryNO=Category.CategoryNO1.內連接--多表查詢另一種連接方式采用JOIN…ON子句連接，格式如圖所示：INNER關鍵字表示是內連接，可以省略，即JOIN連接默認為內連接。關鍵字ON后的連接字段COLUMN_name如果在各表中是唯一的，則表名前綴（表1.或表2.）可以省略，否則必須加表名予以區分。連接字段在語法上必須是可以比較的數據類型。在語義上必須符合邏輯，否則比較毫無意義。FROM<TABLE1_name>[INNER]JOIN<TABLE2_name>ON[<TABLE1_name>.]<COLUMN_name><comparisonoperator>[<TABLE2_name>.]<COLUMN_name>[JOIN…]1.內連接--多表查詢比較運算符為等號的連接稱為等值連接，不為等號時為非等值連接。連接查詢中常用等值連接查詢

示例2：查詢學生購物情況

上面的SQL語句的查詢結果，會有兩個Sno，如果去掉重復字段，則為自然連接SELECT*FROMStudentJOINSaleBillON

Student.SNO=SaleBill.SNOSELECTStudent.SNO,SName,BirthYear,Ssex,college,Major,WeiXin,GoodsNO,HappenTime,NumberFROMStudentJOINSaleBillONStudent.SNO=SaleBill.SNO1.內連接--等值查詢在查詢的時候可以把多表連接的結果集看成一個單表來操作，在其后添加WHERE子句、GROUPBY子句等。

為簡化代碼，可以為連接表指定別名，一旦指定別名后，查詢語句中相應的表都要用該別名替代。

示例3：查詢“CS”學院各學生的消費金額SELECTcollege,SNAME,SUM(SA.Number*SalePrice)消費金額FROMStudentSJOINSaleBillSAONS.SNO=SA.SNOJOINGoodsGONG.GoodsNO=SA.GoodsNOWHEREcollege='CS'GROUPBYcollege,SNAME1.內連接--等值查詢

示例3：查詢“CS”學院各學生的消費金額

還有一種等價的SQL語句：SELECTcollege,SNAME,SUM(SA.Number*SalePrice)消費金額FROMStudentSJOINSaleBillSAONS.SNO=SA.SNOJOINGoodsGONG.GoodsNO=SA.GoodsNOGROUPBYcollege,SNAMEHAVINGcollege='CS'1.內連接--等值查詢當連接的運算符為=時，稱為等值連接，使用其他運算符稱為非等值連接；比較運算符：>、<、>=、<=、<>、!=(<>)

[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>]連接謂詞：[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>]AND[<表名2>.]<列名3>]1.內連接--非等值查詢2.自連接自連接將同一張表進行連接。

同一張表之所以會自身連接，是因為該表存在不同屬性列上的參照完整性約束，或者要查詢同一張表中不同數據之間的部分共同屬性值的情況。

首先，自連接也是內連接。

其次，自連接需要在邏輯上復制出一張和數據表A一模一樣的表B。

示例1：查詢與商品“麥氏威爾冰咖啡”同一類別的商品的商品編號、商品名SELECTG2.GoodsNO,G2.GoodsNameFROMGoodsJOINGoodsG2ONGoods.CategoryNO=G2.CategoryNOWHEREGoods.GoodsName='麥氏威爾冰咖啡'ANDG2.GoodsName!='麥氏威爾冰咖啡'等價于：SELECTG2.GoodsNO,G2.GoodsNameFROMGoods,GoodsG2WHEREGoods.CategoryNO=G2.CategoryNOANDGoods.GoodsName='麥氏威爾冰咖啡'ANDG2.GoodsName!='麥氏威爾冰咖啡'2.自連接

問題1：查詢與小明同一學院的學生學號和姓名SELECTS1.Sno,S1.SnameFROMStudentS1JOINStudentS2ONS1.College=S2.CollegeWHERES1.Sname='小明'ANDS2.Sname!='小明'2.自連接

3.外連接內連接是將滿足連接條件的元組連接起來形成結果集元組，但有時用戶需要將不滿足連接條件的元組也顯示在結果集中。

比如查看哪些商品沒有人買，這時就需要使用外連接來完成此類查詢。

外連接包括：

全外連接

左外連接

右外連接1）全外連接全外連接是將參與連接的表中不滿足連接條件的元組均顯示出來，無對應連接元組值使用NULL填充。

SELECT*FROMAFULLJOINA2ONA.SNO=A2.SNO

3.外連接2）左外連接左外連接使用LEFT[OUTER]JOIN…ON語句連接。左邊表的元組不管滿不滿足連接條件均顯示，右邊表不滿足連接條件的不顯示。

SELECT*FROMALEFTJOINA2ONA.SNO=A2.SNO

3.外連接2）右外連接左外連接使用RIGHT[OUTER]JOIN…ON語句連接。右邊表的元組不管滿不滿足連接條件均顯示，左邊表不滿足連接條件的不顯示。右外連接只要改變表在語句中的位置，右外連接與左外連接的查詢結果就會一樣。SELECT*FROMARIGHTJOINA2ONA.SNO=A2.SNO

3.外連接

DatabasePrincipleandApplication數據庫原理及應用3.3.3子查詢重慶理工大學計算機科學與工程學院目錄CONTENTS

嵌套查詢IN謂詞子查詢12

比較子查詢3

相關子查詢4

不相關子查詢51.嵌套查詢在SQL語句中，一個SELECT-FROM-WHERE是一個查詢塊。如果在一個查詢塊WHERE中還有一個SELECT-FROM-WHERE，則這樣的查詢稱為嵌套查詢。SELECTSname/*外層查詢*/FROMStudentWhereSnoIN( SELECTSno/*內層查詢*/ FROMSaleBill WHEREGoodsNO=‘GN001’)1.嵌套查詢在SQL語句中，是允許多層嵌套查詢，也就是說一個子查詢中，還可以再嵌套一個子查詢。

需要注意的是：子查詢的SELECT語句中不能有ORDERBY排序子句，ORDERBY排序只能在最外層查詢中。

嵌套查詢是用戶可以用多個簡單的查詢語句構成一個復雜的查詢，從而增強SQL語句的查詢靈活性和效率。這也正是SQL語句結構化的特征之一。

2.IN謂詞子查詢

示例1：查詢與商品“麥氏威爾冰咖啡”同一類別的商品的商品編

號、商品名。1）查詢商品“麥氏威爾冰咖啡”的商品類別編號SELECTCategoryNOfromGoodsWHEREGoodsName='麥氏威爾冰咖啡‘結果：CN0012）查詢種類編號為”CN001”的商品名字SELECTGoodsNameFROMGoodsWHERECategoryNO='CN001'3）排除“麥氏威爾冰咖啡”商品SELECTGoodsNameFROMGoodsWHERECategoryNO='CN001'ANDGoodsName!='麥氏威爾冰咖啡'2.IN謂詞子查詢第二步需要的結果,“CN001”是第一步查詢的結果，可以用第一步的查詢語句替代，并用小括號將該查詢語句括起來。

SELECTGoodsNameFROMGoodsWHERECategoryNOIN(

SELECTCategoryNOfromGoods

WHEREGoodsName='麥氏威爾冰咖啡')ANDGoodsName!='麥氏威爾冰咖啡'2.IN謂詞子查詢

示例2：查詢購買了“某某食品貿易部”經銷的商品的學

生學號，姓名1）查詢“某某食品貿易部”的供貨商編號SELECTSupplierNOFROMSupplierWHERESupplierName='某某食品貿易部‘查詢結果：Sup0022）查詢供貨商編號為“Sup002”的供貨商經銷的商品編號SELECTGoodsNOFROMGoodsWHERESupplierNO='Sup002‘結果：為“GN0002”和“GN0003”3)查詢購買了商品編號為“GN0002”或“GN0003”的商品的學生學號SELECTDISTINCTSNOFROMSaleBillWHEREGoodsNOIN('GN0002','GN0003')結果：“S01”、“S02”、“S05”和“S06”2.IN謂詞子查詢

4）根據學號查詢學生姓名SELECTSNO,SNameFROMSTUDENTWHERESNOIN('S01','S02','S03','S04')SELECTSNO,SNameFROMSTUDENTWHERESNOIN(SELECTDISTINCTSNOFROMSaleBillWHEREGoodsNOIN(SELECTGoodsNOFROMGoodsWHERESupplierNO

IN(SELECTSupplierNOFROMSupplierWHERESupplierName=‘某某食品貿易部')))3.比較子查詢比較運算符作為子查詢

示例1：查詢進價大于平均進價的商品名稱，進價

1）查詢商品平均進價。 SELECTAVG(InPrice)FROMGoods查詢結果為“20.617000”。2）查詢進價大于20.617的商品名稱，進價。 SELECTGoodsName,InPriceFROMGoodsWHEREInPrice>20.617第二步的20.617用第一步的子查詢代碼替代：SELECTGoodsName,InPriceFROMGoodsWHEREInPrice>(SELECTAVG(InPrice)FROMGoods)

示例2：查詢和“小明”在同一個學院的學生信息

SELECT*FROMSTUDENTWHERECollege=

（ SELECTCollege

FROMSTUDENT

WHERESname=‘小明’）3.比較子查詢

子查詢返回單個值時可以使用比較運算符，但返回多個值時，就不能直接使用比較運算法，可以采用ANYALL謂詞修飾符。

ANYALL運算符是一個邏輯運算符，它將值與子查詢返回的一組值進行比較。ANY運算符必須以比較運算符：>，>=，<，<=，=，<>開頭，后跟子查詢。

示例3：查詢非計算機學院，比計算機學院任意一個學生都年齡大的學生信息SELECT*FROMStudentWHEREBirthYear<ANY( SELECTBirthYearfromStudent

WHERECollege=‘計算機學院’)ANDCollege<>‘計算機學院’3.比較子查詢--ANY,ALL

示例3：查詢非計算機學院，比計算機學院任意一個學生都年齡大的學生信息SELECT*FROMStudentWHEREBirthYear<( SELECTMAX(BirthYear)fromStudent

WHERECollege=‘計算機學院’)ANDCollege<>‘計算機學院’3.比較子查詢--ANY,ALL

示例4：查詢非計算機學院，比計算機學院任何一個學生都年齡大的

學生信息

SELECT*FROMStudentWHEREBirthYear<

ALL( SELECTBirthYearfromStudent

WHERECollege=‘計算機學院’)ANDCollege<>‘計算機學院’3.比較子查詢--ANY,ALL

示例5：查詢非計算機學院，比計算機學院任何一個學生都年齡大的

學生信息

SELECT*FROMStudentWHEREBirthYear<( SELECTMIN(BirthYear)fromStudent

WHERECollege=‘計算機學院’)ANDCollege<>‘計算機學院’3.比較子查詢--ANY,ALL4.不相關子查詢不相關子查詢不相關子查詢是指內層查詢條件不依賴于外層查詢。即單獨執行內層語句也會得到明確結果集。

示例1：查詢與商品“麥氏威爾冰咖啡”同一類別的商品的商品編

號、商品名SELECTGoodsNameFROMGoodsWHERECategoryNOIN(SELECTCategoryNOfromGoodsWHEREGoodsName='麥氏威爾冰咖啡')ANDGoodsName!='麥氏威爾冰咖啡'不相關子查詢

示例2：查詢進價大于平均進價的商品名稱，進價SELECTGoodsName,InPriceFROMGoodsWHEREInPrice>(

SELECTAVG(InPrice)

FROMGoods)4.不相關子查詢5.相關子查詢相關子查詢如果子查詢內層查詢的查詢條件依賴于外層查詢，則被稱為相關子查詢。

示例3：查詢超過同種類商品平均進價的商品信息SELECT*FROMGoodsWHEREInPrice>(

SELECTAVG(InPrice)

FROMGoodsG

WHEREG.CategoryNO=Goods.CategoryNO)相關子查詢可見相關子查詢不能像無相關子查詢一樣，一次性將內層查詢結果獲得，而是要根據外層查詢，一個一個元組進行到內層查詢中去求解。1）外層查詢獲取一個元組GN0006類別是CN001，傳送給內層查詢SELECTAVG(InPrice)FROMGoodsWHERECategoryNO=‘CN001’2)假設1)的結果是102.3，該值會代替內層查詢，作用于外層查詢上。SELECT*FROMGoodsWHEREInPrice>102.35.相關子查詢EXISTS謂詞帶有EXISTS謂詞的子查詢不返回任何數據，如果子查詢結果不為空，則返回真值“TRUE”，否則返回假值“FALSE”。NOTEXISTS則相反。因為只關心返回真值或假值，不關心具體數據，所以帶EXISTS謂詞的子查詢往往用“*”代替目標列。

示例4：查詢購買了商品的學生信息SELECT*FROMStudentWHEREEXISTS(

SELECT*FROMSaleBillWHERESNO=Student.SNO)5.相關子查詢本例中，外層查詢先定位Student表中第一條元組，此時取出的SNO列值為“S01”，內層查詢根據“S01”在SaleBill查詢，如果不為空，則返回真值，則Student表中第一條元組被放入結果集，如果為空，返回假值，則第一條元組不被放入結果集。然后外層定位Student表中第二條元組，重復這一過程，直到Student表查詢完畢。5.相關子查詢

DatabasePrincipleandApplication數據庫原理及應用3.3.4集合查詢重慶理工大學計算機科學與工程學院1.集合查詢SQL還提供了集合查詢操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。

參加集合操作的列數需相等，對應列的數據類型需相同。

1）并操作

示例1：查詢MIS專業或出生年晚于1991年的學生信息

SELECT*FROMStudentWHEREMajor='MIS'UNIONSELECT*FROMSTUDENTWHEREBirthYear>19911.集合查詢2）交操作

示例2：查詢MIS專業，出生年晚于1991年的學生信息

SELECT*FROMStudentWHEREMajor='MIS'INTERSECTSELECT*FROMSTUDENTWHEREBirthYear>1991SELECT*FROMStudentWHEREMajor='MIS‘ANDBirthYear>19911.集合查詢3）差操作

示例3：查詢MIS專業，出生年晚于1991年的學生信息差集SELECT*FROMStudentWHEREMajor='MIS‘ANDBirthYear<=1991SELECT*FROMStudentWHEREMajor='MIS'EXCEPTSELECT*FROMSTUDENTWHEREBirthYear>1991'

DatabasePrincipleandApplication數據庫原理及應用3.3.5基于派生表查詢重慶理工大學計算機科學與工程學院1.派生表查詢當子查詢出現在FROM子句中時，子查詢的查詢結果形成一個臨時派生表，這個表也可以作為查詢對象。

示例4：查詢各類別商品商品種類名，平均售價本例中，子查詢SELECTCategoryNO,AVG(SalePrice)FROMGoodsGROUPBYCategoryNO查詢結果為生產派生表AVG_CA,子句ASAVG_CA(CategoryNO,AVGSALEPRICE)為派生表指定別名。如果沒有聚合函數，派生表別名后可以不跟列名。AS關鍵字可以省略。SELECTC.CategoryName,AVG_CA.AVGSALEPRICEFROMCategoryCJOIN(SELECTCategoryNO,AVG(SalePrice)

FROMGoodsGROUPBYCategoryNO)ASAVG_CA(CategoryNOAVGSALEPRICE)ONC.CategoryNO=AVG_CA.CategoryNO1.派生表查詢

示例5：查詢購買了GN0002商品的學生信息SELECT*FROMStudentSJOIN(

SELECTSNO,GoodsNO

FROMSaleBill

WHEREGoodsNO='GN0002‘)SA_SNOONS.