人大數(shù)據(jù)計算機(jī)基礎(chǔ)v2第3篇12章sql語言

大數(shù)據(jù)計算機(jī)基礎(chǔ)張延松中國人民大學(xué)信息學(xué)院1目錄2第1章Linux基礎(chǔ)第2章用戶界面和文件管理第3章編輯器及shell編程第4章用戶權(quán)限及磁盤管理第5章系統(tǒng)管理及Linux基本網(wǎng)絡(luò)配置第1篇Linux基礎(chǔ)第4章第4章第4章第4章第4章第5章第5章第5章第5章第2篇Python程序設(shè)計基礎(chǔ)Python基礎(chǔ)知識Python語言的模塊Python語言的類利用Python獲取數(shù)據(jù)利用Python進(jìn)行數(shù)據(jù)處理第3篇數(shù)據(jù)庫基礎(chǔ)數(shù)據(jù)庫基礎(chǔ)知識數(shù)據(jù)庫查詢語言SQL數(shù)據(jù)庫查詢處理與查詢優(yōu)化技術(shù)SQLServer2017數(shù)據(jù)庫分析處理案例SQL概述SQL是結(jié)構(gòu)化查詢語言（StructuredQueryLanguage）的簡稱，是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言。SQL是一種通用的、功能強(qiáng)大的數(shù)據(jù)庫查詢和程序設(shè)計語言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng)，幾乎所有關(guān)系數(shù)據(jù)庫系統(tǒng)都支持SQL，而且一些非關(guān)系數(shù)據(jù)庫也支持類似SQL或與SQL部分兼容的查詢語言，如HiveSQL、SciDBAQL、SparkSQL等。SQL同樣得到其他領(lǐng)域的重視和采用，如人工智能領(lǐng)域的數(shù)據(jù)檢索。同時，SQL語言也在不斷發(fā)展，SQL標(biāo)準(zhǔn)中增加了對JSON的支持，SQLServer2017增加了對圖數(shù)據(jù)處理的支持。本章學(xué)習(xí)的目標(biāo)是掌握SQL語言的基本語法與使用技術(shù)，能夠面向企業(yè)級數(shù)據(jù)庫進(jìn)行管理和數(shù)據(jù)處理，實現(xiàn)基于SQL的數(shù)據(jù)分析處理。本章要點/學(xué)習(xí)目標(biāo)數(shù)據(jù)定義SQL視圖的定義和使用面向大數(shù)據(jù)管理的SQL擴(kuò)展語法本章內(nèi)容1234356數(shù)據(jù)查詢SQL數(shù)據(jù)更新SQL7TPC-H案例數(shù)據(jù)庫簡介第1節(jié)SQL概述SQL語言是一種數(shù)據(jù)庫查詢和程序設(shè)計語言，允許用戶在高層數(shù)據(jù)結(jié)構(gòu)上工作，是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言，也是一個通用的、功能強(qiáng)大的關(guān)系數(shù)據(jù)庫語言。SQL語言是高級的非過程化編程語言，不要求用戶指定對數(shù)據(jù)的存放方法，也不需要用戶了解具體的數(shù)據(jù)存放方式，這種特性保證了具有完全不同底層結(jié)構(gòu)的數(shù)據(jù)庫系統(tǒng)可以使用相同的SQL查詢語言作為數(shù)據(jù)輸入與管理的接口。SQL語言具有獨立性，基本上獨立于數(shù)據(jù)庫本身、所使用的計算機(jī)系統(tǒng)、網(wǎng)絡(luò)、操作系統(tǒng)等，基于SQL的DBMS產(chǎn)品可以運行在從個人機(jī)、工作站到基于局域網(wǎng)、小型機(jī)和大型機(jī)的各種計算機(jī)系統(tǒng)上，具有良好的可移植性。SQL語言具有共享性，數(shù)據(jù)庫和各種產(chǎn)品都使用SQL作為共同的數(shù)據(jù)存取語言和標(biāo)準(zhǔn)的接口，使不同數(shù)據(jù)庫系統(tǒng)之間的互操作有了共同的查詢操作語言基礎(chǔ)，能夠?qū)崿F(xiàn)異構(gòu)系統(tǒng)、異構(gòu)操作系統(tǒng)之間的共享與移植。SQL語言具有豐富的語義，其功能不僅僅是交互式數(shù)據(jù)操縱語言，還包括數(shù)據(jù)定義、數(shù)據(jù)庫的插入/刪除/修改等更新操作、數(shù)據(jù)庫安全性完整性定義與控制、事務(wù)控制等功能，SQL語句可以嵌套，具有極大的靈活性并且能夠表述復(fù)雜的語義。本節(jié)所使用的示例數(shù)據(jù)庫是TPC-H，SQL命令執(zhí)行平臺為SQLServer2017。4第1節(jié)SQL概述SQL語言起源于1974年IBM公司圣約瑟研究實驗室研制的大型關(guān)系數(shù)據(jù)庫管理系統(tǒng)SYSTEMR中使用的SEQUEL語言（由BOYCE和CHAMBERLIN提出），后來在SEQUEL的基礎(chǔ)上發(fā)展了SQL語言。80年代初，美國國家標(biāo)準(zhǔn)局（ANSI）開始著手制定SQL標(biāo)準(zhǔn)，最早的ANSI標(biāo)準(zhǔn)于1986年完成，稱為SQL86。標(biāo)準(zhǔn)的出臺使SQL作為標(biāo)準(zhǔn)的關(guān)系數(shù)據(jù)庫語言的地位得到加強(qiáng)。SQL標(biāo)準(zhǔn)幾經(jīng)修改和完善1992年制定了SQL92標(biāo)準(zhǔn)，全名是“InternationalStandardISO/IEC9075:1992,DatabaseLanguageSQL”。SQL99則進(jìn)一步擴(kuò)展為框架、SQL基礎(chǔ)部分、SQL調(diào)用接口、SQL永久存儲模塊、SQL宿主語言綁定、SQL外部數(shù)據(jù)的管理和SQL對象語言綁定等多個部分。SQL2003包含了XML相關(guān)內(nèi)容，自動生成列值（columnvalues）。SQL2006定義了結(jié)構(gòu)化查詢語言與XML（包含XQuery）的關(guān)聯(lián)應(yīng)用，2006年Sun公司將以結(jié)構(gòu)化查詢語言基礎(chǔ)的數(shù)據(jù)庫管理系統(tǒng)嵌入JavaV6。SQL2008、SQL2011、SQL2016分別增加了一些新的語法、時序數(shù)據(jù)類型支持及對JSON等多樣化數(shù)據(jù)類型的支持。5一、SQL的產(chǎn)生與發(fā)展第1節(jié)SQL概述結(jié)構(gòu)化查詢語言包含6個部分：（1）數(shù)據(jù)定義語言（DDL，DataDefinitionLanguage）DDL語句包括動詞CREATE和DROP。在數(shù)據(jù)庫中創(chuàng)建新表或刪除表（CREATTABLE或DROPTABLE）；表創(chuàng)建或刪除索引（CREATEINDEX或DROPINDEX）等。（2）數(shù)據(jù)操作語言（DML，DataManipulationLanguage）DML語句包括動詞INSERT、UPDATE和DELETE。分別用于插入、修改和刪除表中的元組。（3）事務(wù)處理語言（TPL，TransactionControlLanguage）TPL語句能確保被DML語句影響的表的所有行能夠得到可靠的更新。TPL語句包括BEGINTRANSACTION、COMMIT和ROLLBACK。（4）數(shù)據(jù)控制語言（DCL，DataControlLanguage）DCL語句通過GRANT或REVOKE獲得授權(quán)，分配或取消單個用戶和用戶組對數(shù)據(jù)庫對象的訪問權(quán)限。（5）數(shù)據(jù)查詢語言（DQL，DataQueryLanguage）DQL用于在表中查詢數(shù)據(jù)。保留字SELECT是DQL（也是所有SQL）用得最多的動詞，其他DQL常用的保留字有WHERE、ORDERBY、GROUPBY和HAVING等。（6）指針控制語言（CCL，CursorControlLanguage）CCL語句用于對一個或多個表單獨行的操作。例如DECLARECURSOR、FETCHINTO和UPDATEWHERECURRENT。6二、SQL語言結(jié)構(gòu)第1節(jié)SQL概述1．統(tǒng)一的數(shù)據(jù)操作語言：SQL語言集數(shù)據(jù)定義DDL、數(shù)據(jù)操縱DML和數(shù)據(jù)控制DCL語言于一體，可以完成數(shù)據(jù)庫中的全部工作。2．高度非過程化：與“面向過程”的語言不同，SQL進(jìn)行數(shù)據(jù)操作時只提出要“做什么”，不必描述“怎么做”，也不需要了解存儲路徑。數(shù)據(jù)存儲路徑的選擇及數(shù)據(jù)操作的過程由數(shù)據(jù)庫系統(tǒng)的查詢優(yōu)化引擎自動完成，既減輕了用戶的負(fù)擔(dān)，又提高了數(shù)據(jù)的獨立性。3．面向集合的操作：SQL采用集合操作方式，即數(shù)據(jù)操作的對象是元組的集合，即關(guān)系操作的對象是關(guān)系，關(guān)系操作的輸出也是關(guān)系。4．使用方式靈活：SQL既可以以交互語言方式獨立地使用，也可以作為嵌入式語言嵌入到C、C++、FORTRAN、JAVA、Python、R等主語言中使用。兩種語言的使用方式相同，為用戶提供了方便和靈活。4．語法簡潔，表達(dá)能力強(qiáng)，易于學(xué)習(xí)：在ANSI標(biāo)準(zhǔn)中，只包含了94個英文單詞，核心功能只用9個動詞，語法接近英語口語，易于學(xué)習(xí)。數(shù)據(jù)查詢：SELECT數(shù)據(jù)定義：CREATE、DROP、ALTER數(shù)據(jù)操縱：INSERT、DELETE、UPDATE數(shù)據(jù)控制：GRANT、REVOKE7三、SQL語言特點第1節(jié)SQL概述SQL語言中的五種主要的數(shù)據(jù)類型包括：字符型、文本型、數(shù)值型、邏輯型和日期型。1.字符型字符型用于字符串存儲，根據(jù)字符串長度與存儲長度的關(guān)系可以分為兩大類：CHAR(n)和VARCHAR(n)，表示最大長度為n的字符串。CHAR(n)采用固定長度存儲，當(dāng)字符串長度小于寬度時尾部自動增加空格。VARCHAR(n)按照字符串實際長度存儲，字符串需要加上表示字節(jié)長度值的前綴，當(dāng)n不超過255時使用一個字節(jié)前綴數(shù)據(jù)，當(dāng)n超過255時使用兩個字節(jié)前綴數(shù)據(jù)。當(dāng)字符串長度超過CHAR(n)或VARCHAR(n)的最大長度時，按n對字符串截斷填充。8四、SQL數(shù)據(jù)類型第1節(jié)SQL概述圖4-1給出了字符串’’（空串，長度為0）、’Hello’、’HelloWorld’、’HelloWorldCup’在CHAR(12)或VARCHAR(12)中的存儲空間分配。CHAR(12)無論存儲多長的字符串其長度都為12，VARCHAR(12)長度比實際字符串長度增加1個前綴數(shù)據(jù)字節(jié)，不同長度的字符串存儲時實際使用的空間為n+1個字節(jié)。CHAR(n)會浪費一定的存儲空間，但對于數(shù)據(jù)長度變化范圍輕小的數(shù)據(jù)來說存儲和訪問簡單，在列存儲數(shù)據(jù)庫中定長列易于實現(xiàn)根據(jù)邏輯位置訪問數(shù)據(jù)物理地址；VARCHAR(n)在字符串長度變化范圍較大時存儲效率較高，但在存儲管理和訪問上較為復(fù)雜。9圖4?1CHAR(12)和VARCHAR(12)存儲長度NCHAR(n)和NVARCHAR(n)數(shù)據(jù)類型采用Unicode標(biāo)準(zhǔn)字符集，Unicode標(biāo)準(zhǔn)用兩個字節(jié)為一個存儲單位。第1節(jié)SQL概述3.數(shù)值型數(shù)值型主要包括：整數(shù)、小數(shù)、浮點數(shù)、貨幣型。整型包括：BIGINT、INT、SMALLINT和TINYINT。為了節(jié)省數(shù)據(jù)庫的存儲空間，在表設(shè)計時需要為列設(shè)置適合的數(shù)據(jù)類型，以免存儲空間浪費。值域和存儲空間如表2-2所示。10表4-1BIGINT、INT、SMALLINT和TINYINT值域及存儲空間數(shù)據(jù)類型范圍存儲bigint-263(-9,223,372,036,854,775,808)到263-1(9,223,372,036,854,775,807)8字節(jié)

int-231(-2,147,483,648)到231-1(2,147,483,647)4字節(jié)

smallint-215(-32,768)到215-1(32,767)2字節(jié)

tinyint0到2551字節(jié)

第1節(jié)SQL概述NUMERIC型數(shù)據(jù)用于表示一個數(shù)的整數(shù)部分和小數(shù)部分。NUMERIC[(p[,s])]中，p表示不包含符號、小數(shù)點的總位數(shù)，范圍是1~38，默認(rèn)18；s表示小數(shù)位數(shù)，默認(rèn)為0，滿足0≤s≤p。NUMERIC型數(shù)據(jù)使用最大精度時可以存儲從-1038+1到1038-1范圍內(nèi)的數(shù)。Decimal與NUMERIC用法相同。浮點型數(shù)據(jù)類型包括：REAL、FLOAT[(n)]、DOUBLE。REAL、FLOAT與DOUBLE分別對應(yīng)單精度（4字節(jié)）與以精度浮點數(shù)（8字節(jié)），REAL的SQL-92同義詞為FLOAT(24)，數(shù)值范圍-3.40E+38至-1.18E-38、0以及1.18E-38至3.40E+38。FLOAT(n)類型n為用于存儲FLOAT數(shù)值尾數(shù)，存儲大小為4字節(jié)時n取值范圍為1-24，精度是7位；DOUBLEPRECISION的同義詞為FLOAT(53)，存儲大小為8字節(jié)，n取值范圍為25-53，精度是15位。FLOAT數(shù)值范圍（取決于n值大?。?1.79E+308至-2.23E-308、0以及2.23E-308至1.79E+308。浮點型數(shù)據(jù)屬于近似數(shù)字?jǐn)?shù)據(jù)類型，存儲值的最近似值，并不存儲指定的精確值。當(dāng)要求精確的數(shù)字狀態(tài)時，如銀行、財務(wù)系統(tǒng)等應(yīng)用中，不適合使用這類類型而是使用INTEGER、DECIMAL、MONEY或SMALLMONEY等數(shù)據(jù)類型。11第1節(jié)SQL概述貨幣型數(shù)據(jù)包括：MONEY和SMALLMONEY。MONEY和SMALLMONEY數(shù)據(jù)類型精確到它們所代表的貨幣單位的萬分之一，各自的值域及存儲空間如表4-2所示。12表4-2MONEY和SMALLMONEY值域及存儲空間數(shù)據(jù)類型范圍存儲MONEY-922,337,203,685,477.5808到922,337,203,685,477.58078字節(jié)SMALLMONEY-214,748.3648到214,748.36474字節(jié)4.邏輯型邏輯型BOOLEAN類型只能有兩個取值：真（True）或假（False），用于表示邏輯結(jié)果。5.日期型日期型數(shù)據(jù)包含多種數(shù)據(jù)類型，如date、time、timestamp等，以DATETIME和SMALLDATETIME為例說明日期型數(shù)據(jù)的存儲大小與取值范圍。一個DATETIME型的字段可以存儲的日期范圍是從1753年1月1日第一毫秒到9999年12月31日最后一毫秒，存儲長度為8字節(jié)。SMALLDATETIME與DATETIME型數(shù)據(jù)同樣使用，只不過它能表示的日期和時間范圍比DATETIME型數(shù)據(jù)小，而且不如DATETIME型數(shù)據(jù)精確。一個SMALLDATETIME型的字段能夠存儲從1900年1月1日到2079年6月6日的日期，它只能精確到秒，存儲長度為4字節(jié)。SQL概述SQL是結(jié)構(gòu)化查詢語言（StructuredQueryLanguage）的簡稱，是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言。SQL是一種通用的、功能強(qiáng)大的數(shù)據(jù)庫查詢和程序設(shè)計語言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng)，幾乎所有關(guān)系數(shù)據(jù)庫系統(tǒng)都支持SQL，而且一些非關(guān)系數(shù)據(jù)庫也支持類似SQL或與SQL部分兼容的查詢語言，如HiveSQL、SciDBAQL、SparkSQL等。SQL同樣得到其他領(lǐng)域的重視和采用，如人工智能領(lǐng)域的數(shù)據(jù)檢索。同時，SQL語言也在不斷發(fā)展，SQL標(biāo)準(zhǔn)中增加了對JSON的支持，SQLServer2017增加了對圖數(shù)據(jù)處理的支持。本章學(xué)習(xí)的目標(biāo)是掌握SQL語言的基本語法與使用技術(shù)，能夠面向企業(yè)級數(shù)據(jù)庫進(jìn)行管理和數(shù)據(jù)處理，實現(xiàn)基于SQL的數(shù)據(jù)分析處理。本章要點/學(xué)習(xí)目標(biāo)數(shù)據(jù)定義SQL視圖的定義和使用面向大數(shù)據(jù)管理的SQL擴(kuò)展語法本章內(nèi)容12341356數(shù)據(jù)查詢SQL數(shù)據(jù)更新SQL7TPC-H案例數(shù)據(jù)庫簡介TPC-HTPC-H是TPC（TransactionProcessingPerformanceCouncil）組織于1999年在TPC-D基準(zhǔn)的基礎(chǔ)上發(fā)展而來的面向決策支持的性能測試基準(zhǔn)。TPC-H面向商業(yè)模式的即席查詢（ad-hocquery）和復(fù)雜的分析查詢，TPC-H檢測在標(biāo)準(zhǔn)數(shù)據(jù)集和指定規(guī)模的數(shù)據(jù)量下，通過執(zhí)行一系列指定條件下的查詢時決策支持系統(tǒng)的性能。14

1.TPC-H模式特點NATION-REGION為共享層次表；PARTSUPP為跨維度屬性表；CUSTOMER、PART、SUPPLIER為三個維度表；ORDERS和LINEITEM為主-從式事實表，其中包含退化維度屬性，ORDERS為訂單事實，LINEITEM為訂單明細(xì)項事實，每一個訂單記錄包含若干個訂單明細(xì)項記錄，訂單表的O_ORDERKEY為主鍵，訂單明細(xì)表的L_ORDERKEY為復(fù)合主鍵第一關(guān)鍵字，訂單記錄與訂單明細(xì)項記錄之間保持偏序關(guān)系，即訂單表的O_ORDERKEY順序與訂單明細(xì)表中的L_ORDERKEY順序保持一致。15約束Primarykeyconstraint:P_PARTKEY;S_SUPPKEY;PS_PARTKEY,PS_SUPPKEY;C_CUSTKEY;O_ORDERKEY;L_ORDERKEY,L_LINENUMBER;N_NATIONKEY;R_REGIONKEY.16約束Foreignkeyconstraint:PS_PARTKEY(referencingP_PARTKEY);PS_SUPPKEY(referencingS_SUPPKEY);C_NATIONKEY(referencingN_NATIONKEY);O_CUSTKEY(referencingC_CUSTKEY);L_ORDERKEY(referencingO_ORDERKEY);L_PARTKEY(referencingP_PARTKEY);L_SUPPKEY(referencingS_SUPPKEY);L_PARTKEY,L_SUPPKEY(referencingPS_PARTKEY,PS_SUPPKEY);N_REGIONKEY(referencingR_REGIONKEY);172.TPC-H數(shù)據(jù)量特點TPC-H提供了數(shù)據(jù)生成器dbgen，可發(fā)生成指定的數(shù)據(jù)集大小。數(shù)據(jù)集大小用SF（ScaleFactor）代表，SF=1時，事實表LINEITEM包含6000000行記錄。各表的記錄數(shù)量為：LINEITEM[SF×6000000]，ORDERS[SF×1500000]，PARTSUPP[SF×800000]，SUPPLIER[SF×10000]，PART[SF×200000]，CUSTOMER[SF×150000]，NATION[25]，REGION[5]。SF=100時各個表的記錄行數(shù)、表大小及各表所占比重，其中三個維表PART、SUPPLIER、CUSTOMER占總數(shù)據(jù)量的5.37%，跨維度屬性表PARTSUPP所占比重較大，達(dá)到13.79%，兩個事實表ORDER和LINEITEM所占比重為80.85%18表名總寬度行數(shù)Size(GB)%ORDERS13315000000018.5815.70%LINEITEM13860000000077.1165.15%PARTSUPP2198000000016.3213.79%SUPPLIER19710000000.180.16%PART164200000003.052.58%CUSTOMER223150000003.122.63%NATION185250.0000040.000004%REGION18150.0000010.000001%數(shù)據(jù)生成器Dbgen：運行dbgen程序，可以按指定的SF大小生成相應(yīng)的數(shù)據(jù)文件。19生成SF=1的TPC-H各表數(shù)據(jù)文件的命令為：dbgen-s1-Tcdbgen-s1-TPdbgen-s1-Tsdbgen-s1-TSdbgen-s1-Tndbgen-s1-Trdbgen-s1-TOdbgen-s1-TL3.TPC-H查詢特點TPC-H是一種雪花形模式，采用雙事實表結(jié)構(gòu)，事實表orderS和lineitem是一種主從式事實結(jié)構(gòu)，即orderS表存儲的是訂單事實的匯總信息，而Lineitem表存儲的是訂單的細(xì)節(jié)信息，查詢需要在orderS表和lineitem表連接的基礎(chǔ)上才能給出完整的事實數(shù)據(jù)信息。星形連接（lineite?part?supplier，partsupp?part?supplier）雪花形連接（lineitme?supplier?nation?region，orderS?customer?nation?region）多級連接（lineitem?orders?customer）等是TPC-H查詢優(yōu)化的關(guān)鍵問題20查詢類別TPC-H的22個查詢?nèi)缦拢篞01統(tǒng)計查詢Q02WHERE條件中，使用子查詢(=)Q03多表關(guān)聯(lián)統(tǒng)計查詢，并統(tǒng)計(SUM)Q04WHERE條件中，使用子查詢(EXISTS)，并統(tǒng)計(COUNT)Q05多表關(guān)聯(lián)查詢(=)，并統(tǒng)計(SUM)Q06條件(BETWEENAND)查詢，并統(tǒng)計(SUM)Q07帶有FROM子查詢，從結(jié)果集中統(tǒng)計(SUM)Q08帶有FROM多表子查詢，從結(jié)果集中的查詢列上帶有邏輯判斷(WHENTHENELSE)的統(tǒng)計(SUM)Q09帶有FROM多表子查詢，查詢表中使用函數(shù)(EXTRACT)，從結(jié)果集中統(tǒng)計(SUM)Q10多表條件查詢(>=,<)，并統(tǒng)計(SUM)Q11在GROUPBY中使用比較條件(HAVING>)，比較值從子查詢中查出Q12帶有邏輯判斷(WHENAND/WHENOR)的查詢，并統(tǒng)計(SUM)Q13帶有FROM子查詢，子查詢中使用外聯(lián)結(jié)Q14使用邏輯判斷(WHENELSE)的查詢Q15使用視圖和表關(guān)聯(lián)查詢Q16在WHERE子句中使用子查詢，使用IN/NOTIN判斷條件，并統(tǒng)計(COUNT)Q17在WHERE子句中使用子查詢，使用<比較，使用了AVG函數(shù)Q18在WHERE子句中使用IN條件從子查詢結(jié)果中比較Q19多條件比較查詢Q20WHERE條件子查詢(三層)Q21在WHERE條件中使用子查詢，使用EXISTS和NOTEXISTS判斷Q22在WHERE條件中使用判斷子查詢、IN、NOTEXISTS，并統(tǒng)計(SUM、COUNT)查詢結(jié)果21（1）價格摘要報告查詢（Q1）Q1查詢相當(dāng)于摘要報表功能，報告已經(jīng)付款的、已運送的和返回的生意的數(shù)量。商業(yè)問題描述：價格摘要報告查詢提供了給定日期的運送的所有行的價格摘要報告查詢功能定義：Q1為單表查詢，主要應(yīng)用聚集函數(shù)進(jìn)行分析計算selectl_returnflag,l_linestatus,sum(l_quantity)assum_qty,sum(l_extendedprice)assum_base_price,sum(l_extendedprice*(1-l_discount))assum_disc_price,sum(l_extendedprice*(1-l_discount)*(1+l_tax))assum_charge,avg(l_quantity)asavg_qty,avg(l_extendedprice)asavg_price,avg(l_discount)asavg_disc,count(*)ascount_orderfromlineitemwherel_shipdate<=date'1998-12-01'-interval'[DELTA]'day(3)group

byl_returnflag,l_linestatusorder

byl_returnflag,l_linestatus;注：DELTA在區(qū)間[60,120]內(nèi)隨機(jī)選擇22（2）訂單優(yōu)先權(quán)檢查查詢（Q4）Q4用戶了解訂單優(yōu)先權(quán)系統(tǒng)工作得如何，并給出顧客滿意度的一個估計值。商業(yè)問題描述：訂單優(yōu)先權(quán)檢查查詢計算給定的某一年的某一季度的訂單的數(shù)量，在每個訂單中至少有一行由顧客在它的提交日期之后收到。查詢按照優(yōu)先權(quán)的升序列出每一優(yōu)先權(quán)的訂單的數(shù)量。查詢功能定義：Q4包含子查詢，由EXISTS子查詢提供嵌套的謂詞判斷selecto_orderpriority,count(*)asorder_countfromorderswhereo_orderdate>=date'[DATE]'ando_orderdate<date'[DATE]'+interval'3'monthandexists(select*fromlineitemwherel_orderkey=o_orderkeyandmitdate<l_receiptdate)groupbyo_orderpriorityorderbyo_orderpriority;注：DATE是在1993年1月和1997年10月之間隨機(jī)選擇的一個月的第一天23（3）預(yù)測收入變化查詢（Q6）Q6查詢確定收入增加的數(shù)量，這些增加的收入是在給定的一年中在指定的百分比范圍內(nèi)消除了折扣產(chǎn)生的。商業(yè)問題描述：預(yù)測收入變化查詢考慮了指定的一年中折扣在DISCOUNT-0.01和DISCOUNT＋0.01之間的已運送的所有訂單。查詢功能定義：主要應(yīng)用RANGE謂詞構(gòu)造查詢條件selectsum(l_extendedprice*l_discount)asrevenuefromlineitemwherel_shipdate>=date'[DATE]'andl_shipdate<date'[DATE]'+interval'1'yearandl_discountbetween[DISCOUNT]-0.01and[DISCOUNT]+0.01andl_quantity<[QUANTITY];

注：下面的替換參數(shù)的值在查詢時產(chǎn)生，以形成可執(zhí)行查詢文本：DATE是從[1993,1997]中隨機(jī)選擇的一年的1月1日；DISCOUNT在區(qū)間[0.02,0.09]中隨機(jī)選擇；QUANTITY在區(qū)間[24,25]中隨機(jī)選擇。24（4）返回項目報告查詢（Q10）Q10查詢標(biāo)記那些可能對貨運給他們的零件有問題的顧客。商業(yè)問題描述：Q10查詢根據(jù)在一個季度中那些有返回零件的顧客中對收入產(chǎn)生影響，造成損失的前20名。查詢功能定義：查詢主要體現(xiàn)為多表連接操作Selectc_custkey,c_name,sum(l_extendedprice*(1-l_discount))asrevenue,c_acctbal,n_name,c_address,c_phone,mentFromcustomer,orders,lineitem,nationwherec_custkey=o_custkeyandl_orderkey=o_orderkeyando_orderdate>=date'[DATE]'ando_orderdate<date'[DATE]'+interval'3'monthandl_returnflag='R'andc_nationkey=n_nationkeygroupbyc_custkey,c_name,c_acctbal,c_phone,n_name,c_address,mentorderbyrevenuedesc;注：DATE是位于1993年一月到1994年十二月中任一月的一個日期。25（5）貨運模式和命令優(yōu)先查詢（Q12）Q12查詢決定選擇現(xiàn)對便宜的貨運模式是否會因為使消費者更多的在合同日期之后收到貨物而對緊急優(yōu)先命令產(chǎn)生負(fù)面影響。商業(yè)問題描述：此查詢得到顧客在某一年通過船運模式收到的項目，項目的數(shù)目屬于在兩個特別的船運模式下l_receiptdate超過mitdate的訂單。查詢功能定義：查詢中包含case命令應(yīng)用，通過case命令將查詢結(jié)果分類Selectl_shipmode,sum(casewheno_orderpriority='1-URGENT'oro_orderpriority='2-HIGH‘then1else0end)ashigh_line_count,sum(casewheno_orderpriority<>'1-URGENT'ando_orderpriority<>'2-HIGH‘then1else0end)aslow_line_countFromorders,lineitemWhereo_orderkey=l_orderkeyandl_shipmodein('[SHIPMODE1]','[SHIPMODE2]')andmitdate<l_receiptdateandl_shipdate<mitdateandl_receiptdate>=date'[DATE]'andl_receiptdate<date'[DATE]'+interval'1'yeargroupbyl_shipmodeorderbyl_shipmode;注：下面的替代參數(shù)的值必須被產(chǎn)生和用來建立可執(zhí)行查詢文本：SHIPMODE1是在表定義Modes值的列表中的任意值；SHIPMODE2是在表定義Modes值的列表中的任意值，且必須有別于SHIPMODE1；DATE是從1993年到1997年中任一年的一月一號，用于表示一個完整的年度。26（6）小量訂單收入查詢（Q17）Q17查詢計算出如果沒有沒有小量訂單，平均年收入將損失多少。商業(yè)問題描述：此查詢考慮零件給定品牌和給定包裝類型，決定在一個七年數(shù)據(jù)庫的所有訂單中這些訂單零件的平均項目數(shù)量。查詢功能定義：查詢主要應(yīng)用子查詢完成對指定范圍零件平均值的計算作為查詢條件Selectsum(l_extendedprice)/7.0asavg_yearlyFromlineitem,partwherep_partkey=l_partkeyandp_brand='[BRAND]'andp_container='[CONTAINER]'andl_quantity<(Select0.2*avg(l_quantity)FromlineitemWherel_partkey=p_partkey);

注：以下替代參數(shù)的值在查詢時產(chǎn)生，用來建立可執(zhí)行的查詢文本：BRAND＝’Brand#MN’，M和N是兩個字母，代表兩個數(shù)值，相互獨立，取值在1到5之間。CONTAINER是在表定義中Containers字符串列表中的任意取值。27TPC-H的應(yīng)用TPC-H的性能是分析型數(shù)據(jù)庫性能的重要風(fēng)向標(biāo)。當(dāng)前TPC-H成績中位居前列的是近年來主流的列存儲和內(nèi)存優(yōu)化數(shù)據(jù)庫系統(tǒng)。但TPC-H并不是專用的數(shù)據(jù)倉庫性能測試基準(zhǔn)，其數(shù)據(jù)庫模式與數(shù)據(jù)倉庫還有很大的不同表中包含一些與多維分析不相關(guān)的屬性維、層次的定義并不明確3NF的設(shè)計與數(shù)據(jù)倉庫最常的星形模式也有很大的區(qū)別。TPC-H中很多復(fù)雜的查詢不能轉(zhuǎn)換為OLAP中基礎(chǔ)的切片、切塊等多維操作其SQL命令也不能與BI系統(tǒng)常用的MDX語言直接進(jìn)行轉(zhuǎn)換。28SQL概述SQL是結(jié)構(gòu)化查詢語言（StructuredQueryLanguage）的簡稱，是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言。SQL是一種通用的、功能強(qiáng)大的數(shù)據(jù)庫查詢和程序設(shè)計語言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng)，幾乎所有關(guān)系數(shù)據(jù)庫系統(tǒng)都支持SQL，而且一些非關(guān)系數(shù)據(jù)庫也支持類似SQL或與SQL部分兼容的查詢語言，如HiveSQL、SciDBAQL、SparkSQL等。SQL同樣得到其他領(lǐng)域的重視和采用，如人工智能領(lǐng)域的數(shù)據(jù)檢索。同時，SQL語言也在不斷發(fā)展，SQL標(biāo)準(zhǔn)中增加了對JSON的支持，SQLServer2017增加了對圖數(shù)據(jù)處理的支持。本章學(xué)習(xí)的目標(biāo)是掌握SQL語言的基本語法與使用技術(shù)，能夠面向企業(yè)級數(shù)據(jù)庫進(jìn)行管理和數(shù)據(jù)處理，實現(xiàn)基于SQL的數(shù)據(jù)分析處理。本章要點/學(xué)習(xí)目標(biāo)數(shù)據(jù)定義SQL視圖的定義和使用面向大數(shù)據(jù)管理的SQL擴(kuò)展語法本章內(nèi)容12342956數(shù)據(jù)查詢SQL數(shù)據(jù)更新SQL7TPC-H案例數(shù)據(jù)庫簡介第3節(jié)數(shù)據(jù)定義SQL數(shù)據(jù)庫中的關(guān)系必須由數(shù)據(jù)定義語言（DDL）指定給系統(tǒng)，SQL的DDL用于定義關(guān)系及關(guān)系的一系列信息，包括：關(guān)系模式屬性的值域完整性約束索引安全與權(quán)限存儲結(jié)構(gòu)SQL的數(shù)據(jù)定義功能包括模式、表、視圖和索引。SQL標(biāo)準(zhǔn)通常不提供修改模式、修改視圖和修改索引定義的操作，用戶可以通過先刪除原對象再重新建立的方式修改這些對象。30表4-3數(shù)據(jù)定義SQL命令操作對象操作方式創(chuàng)建刪除修改模式CREATESCHEMADROPSCHEMA

表CREATETABLEDROPTABLEALTERTABLE索引CREATEINDEXDROPINDEX

視圖CREATEVIEWDROPVIEW

現(xiàn)代關(guān)系數(shù)據(jù)庫管理系統(tǒng)提供層次化的數(shù)據(jù)庫對角命名機(jī)制，最頂層是數(shù)據(jù)庫（也稱為目錄），數(shù)據(jù)庫中可以創(chuàng)建多個模式，模式中包括多個表、視圖、索引等數(shù)據(jù)庫對象。第3節(jié)數(shù)據(jù)定義SQL1.模式的定義命令：CREATESCHEMA功能：創(chuàng)建一個新模式。模式是形成單個命名空間的數(shù)據(jù)庫實體的集合，模式中包含表、視圖、索引、權(quán)限定義等對象。該命令需要獲得數(shù)據(jù)庫管理員權(quán)限，或者用戶被授予CREATESCHEMA權(quán)限。語法：CREATESCHEMAschema_name[AUTHORIZATIONusername][schema_element[...]]CREATESCHEMAAUTHORIZATIONusername[schema_element[...]]SQL命令描述：模式名schema_name省略時使用用戶名作為模式名，用戶名username缺省時使用執(zhí)行命令的用戶名，只有超級用戶才能創(chuàng)建不屬于自己的模式。模式成員schema_element定義了要在模式中創(chuàng)建的對象，包含CREATETABLE、CREATEVIEW和GRANT命令創(chuàng)建的對象，其他對象可以在創(chuàng)建模式后獨立創(chuàng)建。模式是數(shù)據(jù)庫的命名空間，模式內(nèi)的對象命名唯一，但可以與其他模式內(nèi)的對象重名。當(dāng)創(chuàng)建模式的用戶需要被刪除時，可以通過轉(zhuǎn)讓模式的所有權(quán)實現(xiàn)用戶與模式的分離，避免因刪除用戶而導(dǎo)致的數(shù)據(jù)丟失問題。31一、模式的定義與刪除第3節(jié)數(shù)據(jù)定義SQLSQL命令示例：【例12-1】創(chuàng)建一個模式TPCHDEMO，授權(quán)給用戶TPCH_user，并且在模式里面創(chuàng)建表和視圖。CREATESCHEMATPCHDEMOAUTHORIZATIONTPCH_userCREATETABLEpart(p_partkeyint,p_namevarchar(22),p_categoryvarchar(7))CREATEVIEWpart_viewASSELECTp_name,p_categoryFROMpartWHEREp_partkey<200;上面的SQL命令與以下三個SQL命令等價：CREATESCHEMATPCHDEMO;CREATETABLETPCHDEMO.part(p_partkeyint,p_namevarchar(22),p_categoryvarchar(7));CREATEVIEWTPCHDEMO.part_viewASSELECTp_name,p_categoryFROMpartWHEREp_partkey<200;首先創(chuàng)建模式TPCHDEMO，然后創(chuàng)建以TPCHDEMO為前綴的表part和視圖part_view。也就是說用戶在創(chuàng)建模式的同時可以在模式中進(jìn)一步創(chuàng)建表、視圖，定義授權(quán)等。當(dāng)沒有指定模式名時，模式名隱含為用戶名TPCH_user，如：CREATESCHEMAAUTHORIZATIONTPCH_user;32第3節(jié)數(shù)據(jù)定義SQL2.刪除模式命令：DROPSCHEMA功能：刪除指定模式。語法：DROPSCHEMAschema_name;SQL命令描述：當(dāng)刪除模式時，如果模式中已經(jīng)定義了下屬的數(shù)據(jù)庫對象，則中止該刪除模式語句的執(zhí)行，需要首先將模式內(nèi)的對象刪除，然后才能將模式刪除?！纠?2-2】刪除模式TPCHDEMO.刪除模式TPCHDEMO時需要首先刪除表part和視圖part_view，然再刪除模式TPCHDEMO：DROPTABLETPCHDEMO.part;DROPVIEWTPCHDEMO.part_view;DROPSCHEMATPCHDEMO;33第3節(jié)數(shù)據(jù)定義SQL3.模式轉(zhuǎn)移模式轉(zhuǎn)移命令用于將一個模式中的數(shù)據(jù)庫對象轉(zhuǎn)換給另一個模式。【例12-3】創(chuàng)建一個模式temp并在模式中創(chuàng)建表users，然后將users轉(zhuǎn)移給模式TPCHDEMO。CREATESCHEMAtempCREATETABLEusers(idINT,usernameVARCHAR(30));ALTERSCHEMATPCHDEMOTRANSFEROBJECT::temp.users;34首先創(chuàng)建模式temp和模式中的表users，在SQLServer2017管理器中查看數(shù)據(jù)庫中的表對象存在名稱為temp.users的表。通過ALTERSCHEMA命令將模式temp中的表users轉(zhuǎn)移給模式dbo，命令執(zhí)行完后查詢管理器確認(rèn)表名稱改為dbo.users，實現(xiàn)了模式中對象的轉(zhuǎn)移。圖4?2模式轉(zhuǎn)移第3節(jié)數(shù)據(jù)定義SQL1.定義表命令：CREATETABLE功能：創(chuàng)建一個基本表。語法：CREATETABLE[database_name.[schema_name].|schema_name.]table_name(<column_name><type_name>[constraint_name][,<column_name><type_name>[constraint_name]]……[,<table_constraint>]);35二、表的定義、刪除與修改第3節(jié)數(shù)據(jù)定義SQLSQL命令描述：基本表是關(guān)系的物理實現(xiàn)。表名table_name定義了關(guān)系的名稱，相同的模式中表名不能重復(fù)，不同的模式或數(shù)據(jù)庫之間表名可以相同。列名column_name是屬性的標(biāo)識，表中的列名不能相同，不同表的列名可以相同。當(dāng)查詢中所使用不同表的列名相同時，需要使用“表名.列名”來標(biāo)識相同名稱的列，當(dāng)列名不同時，不同表的列可以直接通過列名訪問，因此在標(biāo)準(zhǔn)化的設(shè)計中通常采用表名縮寫通過下劃線與列名組成復(fù)合列名的命名方式來唯一標(biāo)識不同的列，如part表的name列命名為“p_name”，supplier表的name列命名為“s_name”，通過表名縮寫前綴來區(qū)分不同表中的列。數(shù)據(jù)類型type_name規(guī)定了列的取值范圍，需要根據(jù)列的數(shù)據(jù)特點定義適合的數(shù)據(jù)類型，既要避免因數(shù)據(jù)類型值域過小引起的數(shù)據(jù)溢出問題，也需要避免因數(shù)據(jù)類型值域過大導(dǎo)致的存儲空間浪費問題。在大數(shù)據(jù)存儲時，數(shù)據(jù)類型的寬度決定了數(shù)據(jù)存儲空間，需要合理地根據(jù)應(yīng)用的特征選擇適當(dāng)?shù)臄?shù)據(jù)類型。36第3節(jié)數(shù)據(jù)定義SQL列級完整性約束constraint_name包括：列是否可以取空值：[NULL|NOTNULL]如P_SIZEintNULL表示列P_SIZE可以取空值。注意，表中設(shè)置為主碼的列不可為空，需要設(shè)置NOTNULL約束條件。列是否為主鍵/唯一鍵：{PRIMARYKEY|UNIQUE}[CLUSTERED|NONCLUSTERED]如S_SUPPKEYintPRIMARYKEYCLUSTERED表示列S_SUPPKEY設(shè)置為主碼并創(chuàng)建聚集索引。聚集索引是指表中行數(shù)據(jù)的物理順序與鍵值的邏輯（索引）順序相同，一個表只能有一個聚集索引，一些數(shù)據(jù)庫系統(tǒng)默認(rèn)為主碼建立聚集索引。列是否是外碼：REFERENCES[schema_name.]referenced_table_name[(ref_column)]如N_REGIONKEintREFERENCESREGION(R_REGIONKEY)表示列N_REGIONKE是外碼，參照表REGION中的列R_REGIONKEY。約束條件定義在列之后的方式稱為列級約束。表級約束table_constraint是為表中的列所定義的約束。當(dāng)表中使用多個屬性的復(fù)合主碼時，主碼的定義需要使用表級約束。列級參照完整性約束也可以表示為表級參照完整性約束。37第3節(jié)數(shù)據(jù)定義SQL【例12-4】參照圖3-13（A）模式，寫出TPC-H數(shù)據(jù)庫中各表的定義命令。CREATETABLEREGION( R_REGIONKEY integer PRIMARYKEY, R_NAME char(25), R_COMMENT varchar(152));38CREATETABLENATION( N_NATIONKEY integer PRIMARYKEY, N_NAME char(25), N_REGIONKEY integer REFERENCESREGION(R_REGIONKEY), N_COMMENT varchar(152));第3節(jié)數(shù)據(jù)定義SQLCREATETABLEPART( P_PARTKEY integer PRIMARYKEY, P_NAME varchar(55), P_MFGR char(25), P_BRAND char(10), P_TYPE varchar(25), P_SIZE integer, P_CONTAINER char(10), P_RETAILPRICE decimal, P_COMMENT varchar(23));39CREATETABLESUPPLIER( S_SUPPKEY integer PRIMARYKEY, S_NAME char(25), S_ADDRESS varchar(40), S_NATIONKEY integer REFERENCESNATION(N_NATIONKEY), S_PHONE char(15), S_ACCTBAL decimal, S_COMMENT varchar(101));第3節(jié)數(shù)據(jù)定義SQLCREATETABLEPARTSUPP( PS_PARTKEY integer REFERENCESPART(P_PARTKEY), PS_SUPPKEY integer REFERENCESSUPPLIER(S_SUPPKEY), PS_AVAILQTY integer, PS_SUPPLYCOST decimal, PS_COMMENT varchar(199), PRIMARYKEY(PS_PARTKEY,PS_SUPPKEY));40CREATETABLECUSTOMER( C_CUSTKEY integer PRIMARYKEY, C_NAME varchar(25), C_ADDRESS varchar(40), C_NATIONKEY integer REFERENCESNATION(N_NATIONKEY), C_PHONE char(15), C_ACCTBAL Decimal, C_MKTSEGMENT char(10), C_COMMENT varchar(117));第3節(jié)數(shù)據(jù)定義SQLCREATETABLEORDERS( O_ORDERKEY integer PRIMARYKEY, O_CUSTKEY integerREFERENCESCUSTOMER(C_CUSTKEY), O_ORDERSTATUS char(1), O_TOTALPRICE decimal, O_ORDERDATE date, O_ORDERPRIORITY char(15), O_CLERK char(15), O_SHIPPRIORITY integer, O_COMMENT varchar(79));41第3節(jié)數(shù)據(jù)定義SQLCREATETABLELINEITEM( L_ORDERKEY integer REFERENCESORDERS(O_ORDERKEY), L_PARTKEY integer, L_SUPPKEY integer, L_LINENUMBER integer, L_QUANTITY decimal, L_EXTENDEDPRICE decimal, L_DISCOUNT decimal, L_TAX decimal, L_RETURNFLAG char(1), L_LINESTATUS char(1), L_SHIPDATE date, L_COMMITDATE date, L_RECEIPTDATE date, L_SHIPINSTRUCT char(25), L_SHIPMODE char(10), L_COMMENT varchar(44), PRIMARYKEY(L_ORDERKEY,L_LINENUMBER), FOREIGNKEY(L_PARTKEY,L_SUPPKEY)REFERENCESPARTSUPP(PS_PARTKEY, PS_SUPPKEY));42第3節(jié)數(shù)據(jù)定義SQL在表定義命令中，單屬性上的主碼及參照完整性約束定義可以使用列級約束定義，直接寫在列定義語句中，復(fù)合屬性主、外碼定義則需要使用表級定義，通過獨立的語句寫在表定義命令中。在參照完整性約束定義中，使用列級約束定義完整性約束時只需要定義參照的表及列，而使用表級約束定義完整性約束時則需要定義外碼及參照的表和列。43第3節(jié)數(shù)據(jù)定義SQL2.修改表命令：ALTERTABLE功能：修改基本表。語法：ALTERTABLE<table_name>[ADD<column_name><type_name>[constraint_name]][ADD<table_constraint>][DROP<column_name>][DROP<constraint_name>][ALTERCOLUMN<column_name>[type_name]|[NULL|NOTNULL]];SQL命令描述：修改基本表，其中ADD子句用于增加新的列（列名、數(shù)據(jù)類型、列級約束）和新的表級約束條件；DROP子句用于刪除指定的列或者指定的完整性約束條件；ALTERTABLE子句用于修改原有的列定義，包括列名、數(shù)據(jù)類型等。44第3節(jié)數(shù)據(jù)定義SQLSQL命令示例：【例12-5】完成下面的表修改操作。ALTERTABLELINEITEMADDL_SURRKEYint;--SQL命令解析：增加一個int類型的列L_SURRKEY;ALTERTABLELINEITEMALTERCOLUMNL_QUANTITYSMALLINT;--SQL命令解析：將L_QUANTITY列的數(shù)據(jù)類型修改為SMALLINT：ALTERTABLEORDERSALTERCOLUMNO_ORDERPRIORITYvarchar(15)NOTNULL;--SQL命令解析：將O_ORDERPRIORITY列的約束修改為NOTNULL約束：ALTERTABLELINEITEMADDCONSTRAINTFK_SFOREIGNKEY(L_SURRKEY)REFERENCESSUPPLIER(S_SUPPKEY);--SQL命令解析：在LINEITEM表中增加一個外鍵約束。CONSTRAINT關(guān)鍵字定義約束的名稱FK_S，然后定義表級參照完整性約束條件。ALTERTABLELINEITEMDROPCONSTRAINTFK_S;--SQL命令解析：在LINEITEM表中刪除外鍵約束FK_S。ALTERTABLELINEITEMDROPCOLUMNL_SURRKEY;--SQL命令解析：刪除表中的列L_SURRKEY：45第3節(jié)數(shù)據(jù)定義SQL命令執(zhí)行約束：在列的修改操作中，如果將數(shù)據(jù)寬度由小變大時可以直接在原始數(shù)據(jù)上修改，如果數(shù)據(jù)寬度由大變小或者改變數(shù)據(jù)類型時，通常需要先清除掉數(shù)據(jù)庫中該列的內(nèi)容然后才能修改。在這種情況下，可以通過臨時列完成列數(shù)據(jù)類型修改時的數(shù)據(jù)交換，如修改列數(shù)據(jù)類型時先增加一個與被修改的列類型一樣的列作為臨時列，然后將要修改列的數(shù)據(jù)復(fù)制到臨時列并置空要修改的列，然后修改該列的數(shù)據(jù)類型，再從臨時列將數(shù)據(jù)經(jīng)過數(shù)據(jù)類型轉(zhuǎn)換后復(fù)制回被修改的列，最后刪除臨時列。當(dāng)數(shù)據(jù)庫中存儲了大量數(shù)據(jù)時，表結(jié)構(gòu)的修改會產(chǎn)生較高的列數(shù)據(jù)更新代價，因此需要在基本表的設(shè)計階段全面考慮列的數(shù)量、數(shù)據(jù)類型和數(shù)據(jù)寬度，盡量避免對列的修改。46第3節(jié)數(shù)據(jù)定義SQL3.刪除表命令：DROPTABLE功能：刪除一個基本表。語法：DROPTABLE<table_name>[RESTRICT|CASCADE];SQL命令描述：RESTRICT：缺省選項，表的刪除有限制條件。刪除的基本表不能被其他表的約束所引用，如FOREIGNKEY，不能有視圖、觸發(fā)器、存儲過程及函數(shù)等依賴于該表的對象，如果存在，需要首先刪除這些對象或者解除與該表的依賴后才能刪除該表。CASCADE：無限制條件，表刪除時相關(guān)對象一起刪除。47第3節(jié)數(shù)據(jù)定義SQLSQL命令示例：【例12-6】刪除表part。DROPTABLEpart;不同的數(shù)據(jù)庫對DROPTABLE命令有不同的規(guī)定，有的數(shù)據(jù)庫不支持RESTRICT|CASCADE選項，在刪除表時需要手工刪除與表相關(guān)的對象或解除刪除表與其他表的依賴關(guān)系。對于依賴于基本表的對象，如索引、視圖、存儲過程和函數(shù)、觸發(fā)器等對象，不同的數(shù)據(jù)庫在刪除基本表時采取的策略有所不同，通常來說，刪除基本表后索引會自動刪除，視圖、存儲過程和函數(shù)在不刪除時也會失效，觸發(fā)器和約束引用在不同數(shù)據(jù)庫中有不同的策略。48第3節(jié)數(shù)據(jù)定義SQL當(dāng)表的數(shù)據(jù)量比較大時，查詢操作需要掃描大量的數(shù)據(jù)而產(chǎn)生較大的耗時。索引是數(shù)據(jù)庫中重要的性能優(yōu)化技術(shù)，通過創(chuàng)建索引，在表上創(chuàng)建一個或多個索引，提供多種存儲路徑，數(shù)據(jù)庫能夠自動地執(zhí)行索引查找，提高數(shù)據(jù)庫的查詢性能。關(guān)系數(shù)據(jù)庫中常用的索引包括B+樹索引、哈希索引、位圖索引、位圖連接索引、存儲索引和列存儲索引等。49三、代表性的索引技術(shù)1.索引類型（1）聚集索引數(shù)據(jù)庫通常會為關(guān)系中定義的主碼自動創(chuàng)建聚集索引（clusteredindex），即記錄按主碼的順序物理存儲，保持?jǐn)?shù)據(jù)在邏輯上和物理上都能按主碼成順序訪問，這種聚簇存儲機(jī)制能夠有效地提高查詢性能，但一個關(guān)系上只能創(chuàng)建一個聚集索引。第3節(jié)數(shù)據(jù)定義SQL（2）B+樹索引B+樹索引是磁盤數(shù)據(jù)庫的一般經(jīng)典索引結(jié)構(gòu)，它的基本思想是以page為單位組織表記錄鍵值-地址（PageId）對的分層存儲。在創(chuàng)建索引時，原始表中較長的記錄按索引的結(jié)構(gòu)抽取出鍵值-地址對排序后以page為單位存儲在B+樹索引葉節(jié)點層，各葉節(jié)點形成鏈表結(jié)構(gòu)，記錄了索引鍵值的排序序列。每個葉節(jié)點page中的最小值抽取出構(gòu)建上級非葉索引節(jié)點，以page為單位依次存儲每個葉節(jié)點最小值-地址對數(shù)據(jù)，即葉節(jié)點為記錄建立索引，非葉節(jié)點為下一級索引節(jié)點建立索引。非葉節(jié)點依次向上構(gòu)建，直到只產(chǎn)生唯一的非葉節(jié)點作為B+樹索引的根節(jié)點。假設(shè)表記錄寬度為80字節(jié)，記錄行數(shù)為10億條，索引列數(shù)據(jù)類型為int（4字節(jié)），page大小為4KB，則記錄需要存儲在80*1,000,000,000/(4*1024)≈19,531,250個page中，即，在沒有索引的情況下如果查找某個鍵值對應(yīng)的記錄需要順序掃描19,531,250個page。在建立B+樹索引時，10億條記錄鍵值-地址對寬度為8字節(jié)（假設(shè)鍵值4字節(jié)，地址4字節(jié)），則每個4KB大小的page中可以存儲512個索引項，B+樹索引的葉節(jié)點需要38147個page，葉節(jié)點中的每一個page中的最小值和PageId構(gòu)成第二級非葉節(jié)點的索引項，需要74個二級非葉節(jié)點索引page，然后74個索引page中的最小值-地址對繼續(xù)構(gòu)造1個第三級根節(jié)點page。50第3節(jié)數(shù)據(jù)定義SQL在執(zhí)行索引查找時，首先訪問B+樹索引的根節(jié)點，根據(jù)鍵值大小訪問下一級非葉索引節(jié)點，再依次訪問葉節(jié)點，獲得鍵值匹配記錄的地址（PageId），最后訪問數(shù)據(jù)page，在page頁面內(nèi)順序掃描，讀取相應(yīng)的記錄。查找過程如圖4-3所示鍵值為61記錄的查找過程。51B+樹索引在底層存儲順序的鍵值數(shù)據(jù)，比較適合進(jìn)行點查找和范圍查找，在范圍查找時只需要查找范圍表達(dá)式的最小值后在葉結(jié)點上順序訪問，直到葉結(jié)點對應(yīng)的數(shù)據(jù)超過最大值為止。圖4-3B+樹索引第3節(jié)數(shù)據(jù)定義SQL（3）哈希索引B+樹索引是一種分層的、鍵值有序的索引結(jié)構(gòu)，可以支持點查詢和范圍查詢。哈希索引主要支持點查詢，通過哈希函數(shù)建立鍵值與地址之間的直接映射關(guān)系，如圖4-6所示的哈希索引結(jié)構(gòu)。哈希索引對應(yīng)隨機(jī)查找過程，索引的效率受隨機(jī)訪問性能的影響較大，如通過哈希索引訪問較多的鍵值時，每個索引訪問都可能導(dǎo)致一個磁盤page的隨機(jī)訪問，產(chǎn)生大量隨機(jī)磁盤I/O，索引訪問性能在選擇率較高時可能低于不使用索引的順序掃描方法。提高哈希索引性能的方法是將哈希表建立在高性能隨訪問存儲設(shè)備中，如通過數(shù)據(jù)分區(qū)技術(shù)將表劃分為較小的分區(qū)，在較小的分區(qū)上建立哈希表，使哈希表位于內(nèi)存或容量更小、性能更高的cache中，從而提高哈希索引訪問性能。52哈希查詢適合于進(jìn)行等值查找，通過哈希函數(shù)計算直接得到數(shù)據(jù)存儲位置，在等值查找效率上比B+樹索引要高，但不支持范圍查找。第3節(jié)數(shù)據(jù)定義SQL（4）位圖索引位圖索引是一種通過位圖記錄屬性列在每個成員在行中位置的技術(shù)，位圖索引適用于屬性列中不同成員的數(shù)量與行數(shù)之比小于1%的低勢集屬性。如圖4-7所示，關(guān)系中的屬性Gender和Country分別有2個和3個成員，為屬性Gender創(chuàng)建的位圖索引包含兩個位圖，分別表示Gender值為M或F的記錄在表中的位置。圖右所示的位圖索引結(jié)構(gòu)可以看作是為屬性Gender按屬性成員的數(shù)量創(chuàng)建一個位圖矩陣，Gender屬性的每一行的取值在Gender位圖矩陣的每一行中只有一個對應(yīng)位置取值為1，其余位置取值為0。通過位圖索引機(jī)制，Gender屬性存儲為2個位圖，壓縮了屬性存儲空間，而且通過位圖索引能夠直接獲得Gender取指定值時所有滿足條件記錄的位置。53圖4-7位圖索引第3節(jié)數(shù)據(jù)定義SQL圖4-8對應(yīng)了在為Gender和Country屬性創(chuàng)建位圖索引后執(zhí)行查詢時位圖索引的計算過程：SELECTcount(*)FROMcustomerWHEREGender=’M’ANDCountryin(’Mexico’,’USA’);查詢中的謂詞條件對應(yīng)已建立位圖索引的屬性，將謂詞條件Gender=’M’轉(zhuǎn)換為訪問Gender值為’M’的位圖，查詢條件Countryin(’Mexico’,’USA’)轉(zhuǎn)換為訪問Country值為’Mexico’和’USA’的位圖，并對兩個位圖執(zhí)行OR操作。查詢中的復(fù)合謂詞條件轉(zhuǎn)換為圖4-8所示的多個位圖之間的邏輯運算，位圖運算結(jié)果對應(yīng)的位圖則指標(biāo)了滿足查詢謂詞條件的記錄在表中的位置。54圖4?8位圖索引的使用第3節(jié)數(shù)據(jù)定義SQL屬性中的成員數(shù)量越多，位圖索引中的位圖數(shù)量越多，位圖存儲空間代價越大。但屬性中的成員數(shù)量越多，每個位圖中1的數(shù)量更加稀疏，位圖運算對應(yīng)的選擇率越低，查詢性能提升越大。當(dāng)表中記錄數(shù)量非常大時，稀疏的位圖可以通過壓縮技術(shù)縮減位圖索引存儲空間，同時，當(dāng)位圖很大時，位圖運算也消耗大量的CPU計算資源，可以通過SIMD并行計算技術(shù)提高位圖計算性能，也可以通過協(xié)處理器，如GPGPU、Phi協(xié)處理器所支持的512位SIMD計算能力來提高位圖索引的計算性能。55第3節(jié)數(shù)據(jù)定義SQL圖4-9中維表Customer的屬性Gender和Country為低勢集屬性，事實表Sales中包含Customer表的外鍵屬性customer_id。我們可以使用如下的SQL命令為Customer表的Gender和Country屬性創(chuàng)建與Sales表的位圖連接索引。CREATEBITMAPINDEXsales_c_gender_countryONsales(customer.gender,customer.country)FROMsales,customerWHEREsales.customer_id=customer.customer_id;56圖4?9位圖連接索引的使用第3節(jié)數(shù)據(jù)定義SQL位圖索引表示的是屬性成員在當(dāng)前表中的位置信息，而位圖連接索引則表示屬性成員在連接表中的位置信息。位圖連接索引相當(dāng)于為物化連接表屬性創(chuàng)建的位圖索引，在實際應(yīng)用中能夠有效地減少連接操作代價。位圖索引和位圖連接索引都是在選定屬性上為所有成員創(chuàng)建位圖，低勢集的屬性上創(chuàng)建的位圖數(shù)量較少，索引空間開銷較小，但由于屬性成員數(shù)量少，每一個位圖對應(yīng)的選擇率較高，對連接操作的加速能力較低，而高勢集屬性的成員數(shù)量較多，需要創(chuàng)建較多的位圖，索引存儲空間開銷較大，但位圖的選擇率低，對連接操作的加速能力強(qiáng)，因此位圖連接索引的創(chuàng)建和使用需要權(quán)衡位圖連接索引的存儲開銷和查詢性能優(yōu)化收益而綜合評估。57第3節(jié)數(shù)據(jù)定義SQL（5）存儲索引StorageIndex存儲索引是一種根據(jù)數(shù)據(jù)塊元信息過濾查詢訪問數(shù)據(jù)塊的索引技術(shù)。如圖4-10所示，數(shù)據(jù)庫為每設(shè)定大小的數(shù)據(jù)塊在內(nèi)存中建立匯總元信息，如最小值、最大值、數(shù)據(jù)塊中記錄數(shù)、數(shù)據(jù)型列累加和等信息，可以對常用列自動收集這些匯總信息并在內(nèi)存建立存儲索引。在查詢執(zhí)行時，首先掃描內(nèi)存中存儲索引各數(shù)據(jù)塊的元信息，根據(jù)查詢條件與元信息過濾掉不符合條件的數(shù)據(jù)塊，如查詢條件“PROD_CODEBETWEEN75000AND90000”超出第一個數(shù)據(jù)塊中PROD_CODE最大值，因此查詢時可以完全跳過對該數(shù)據(jù)塊的訪問，查詢條件與第二個數(shù)據(jù)塊的最小值（39023）最大值（87431）范圍有交集，因此需要掃描第二個數(shù)據(jù)塊。存儲索引只記錄數(shù)據(jù)塊中匯總的元信息，數(shù)據(jù)量極小，可以常駐內(nèi)存。通過存儲索引過濾掉與查詢條件不相關(guān)的磁盤數(shù)據(jù)塊，提高查詢的I/O效率。當(dāng)數(shù)據(jù)分布比較偏斜時，塊中最小值與最大值分布也比較偏斜，在查詢中存儲索引的過濾效果較好。與其他索引不同，存儲索引不是一種精確的索引，而是基于元數(shù)據(jù)統(tǒng)計的粗糙過濾索引，索引訪問的粒度是數(shù)據(jù)塊，數(shù)據(jù)塊內(nèi)還需要通過掃描操作完成查詢。58第3節(jié)數(shù)據(jù)定義SQL59圖4?10存儲索引第3節(jié)數(shù)據(jù)定義SQL（6）列存儲索引Columnstoreindex列存儲索引是一種基于列存儲模型的索引結(jié)構(gòu)。SQLServer2012/2016/2017中采用了列存儲索引技術(shù)，如圖4-11所示，行記錄以1M行為單位劃分為rowgroup，每個rowgroup中的屬性按列存儲并進(jìn)行壓縮，采用字典表壓縮技術(shù)的列需要在rowgroup中存儲字典表。在查詢處理時，索引涉及的列在列存儲索引上按列處理，提高查詢處理性能。60圖4?11列存儲索引第3節(jié)數(shù)據(jù)定義SQL在傳統(tǒng)的磁盤行存儲數(shù)據(jù)庫中，當(dāng)按指定鍵值查找時需要掃描全部的記錄才能找到滿足條件的記錄，在查找過程中只有指定屬性值被查找所使用，數(shù)據(jù)訪問效率低。索引相當(dāng)于將查找屬性單獨存儲并通過索引優(yōu)化查找操作，無論是在數(shù)據(jù)訪問效率還是查找效率都有較好的性能。索引是數(shù)據(jù)