大數(shù)據(jù)分析計(jì)算機(jī)基礎(chǔ)-數(shù)據(jù)庫(kù)部分SQL命令素材

大數(shù)據(jù)分析計(jì)算機(jī)基礎(chǔ)——數(shù)據(jù)庫(kù)基礎(chǔ)SQL命令教學(xué)素材12.2例1CREATESCHEMASSBCREATETABLEpart(p_partkeyint,p_namevarchar(22),p_categoryvarchar(7))CREATEVIEWpart_viewASSELECTp_name,p_categoryFROMpartWHEREp_partkey<200;CREATESCHEMASSB;CREATETABLESSB.part(p_partkeyint,p_namevarchar(22),p_categoryvarchar(7));CREATEVIEWSSB.part_viewASSELECTp_name,p_categoryFROMpartWHEREp_partkey<200;DROPTABLESSB.part;DROPVIEWSSB.part_view;DROPSCHEMASSB;例2先創(chuàng)建兩個(gè)臨時(shí)表part,supplier作為被參照表，然后創(chuàng)建帶有參照完整性關(guān)系的表partsuppCREATETABLEpart(p_partkeyintprimarykey,p_namevarchar(22));CREATETABLEsupplier(S_SUPPKEYintprimarykey,s_namevarchar(22));CREATETABLEPARTSUPP(PS_PARTKEYinteger,PS_SUPPKEYinteger,PS_AVAILQTYinteger,PS_SUPPLYCOSTDecimal,PS_COMMENTvarchar(199),PRIMARYKEY(PS_PARTKEY,PS_SUPPKEY),/*使用表級(jí)約束定義復(fù)合主鍵*/FOREIGNKEY(PS_PARTKEY)REFERENCESPART(p_partkey),/*使用表級(jí)約束定義參照完整性約束*/FOREIGNKEY(PS_SUPPKEY)REFERENCESSUPPLIER(S_SUPPKEY));例3--1.創(chuàng)建part表CREATETABLEpart(p_partkey integer NOTNULL,p_name varchar(22) NOTNULL,p_mfgr varchar(6)NOTNULL,p_category varchar(7)NOTNULL,p_brand1 varchar(9)NOTNULL,p_color varchar(11) NOTNULL,p_type varchar(25) NOTNULL,p_size integer NOTNULL,p_container varchar(10)NOTNULL,PRIMARYKEY(p_partkey));--2.創(chuàng)建supplier表CREATETABLEsupplier(s_suppkey integer NOTNULL,s_name varchar(25)NOTNULL,s_address varchar(25) NOTNULL,s_city varchar(10)NOTNULL,s_nation varchar(15)NOTNULL,s_region varchar(12)NOTNULL,s_phone varchar(15)NOTNULL,PRIMARYKEY(s_suppkey));--3.創(chuàng)建customer表CREATETABLEcustomer(c_custkey integer NOTNULL,c_name varchar(25) NOTNULL,c_address varchar(25) NOTNULL,c_city varchar(10)NOTNULL,c_nation varchar(15)NOTNULL,c_region varchar(12)NOTNULL,c_phone varchar(15)NOTNULL,c_mktsegmentvarchar(10) NOTNULL,PRIMARYKEY(c_custkey));--4.創(chuàng)建date表CREATETABLEdate(d_datekey integer NOTNULL,d_date varchar(19)NOTNULL,d_dayofweek varchar(10)NOTNULL,d_month varchar(10)NOTNULL,d_year integer NOTNULL,d_yearmonthnum integer NOTNULL,d_yearmonthvarchar(8)NOTNULL,d_daynuminweekinteger NOTNULL,d_daynuminmonth integer NOTNULL,d_daynuminyearinteger NOTNULL,d_monthnuminyear integer NOTNULL,d_weeknuminyear integer NOTNULL,d_sellingseason varchar(13)NOTNULL,d_lastdayinweekfl varchar(1)NOTNULL,d_lastdayinmonthfl varchar(1)NOTNULL,d_holidayflvarchar(1)NOTNULL,d_weekdayflvarchar(1)NOTNULL,PRIMARYKEY(d_datekey));--5.創(chuàng)建lineorder表CREATETABLElineorder(lo_orderkey integer NOTNULL,lo_linenumber integer NOTNULL,lo_custkeyinteger NOTNULL,lo_partkeyinteger NOTNULL,lo_suppkeyinteger NOTNULL,lo_orderdate integer NOTNULL,lo_orderpriority varchar(15)NOTNULL,lo_shippriority varchar(1)NOTNULL,lo_quantity integer NOTNULL,lo_extendedprice integer NOTNULL,lo_ordertotalprice integer NOTNULL,lo_discountinteger NOTNULL,lo_revenueinteger NOTNULL,lo_supplycost integer NOTNULL,lo_taxinteger NOTNULL,lo_commitdateinteger NOTNULL,lo_shipmodevarchar(10) NOTNULL,PRIMARYKEY(lo_orderkey,lo_linenumber),FOREIGNKEY(lo_custkey)REFERENCEScustomer(c_custkey),FOREIGNKEY(lo_partkey)REFERENCESpart(p_partkey),FOREIGNKEY(lo_suppkey)REFERENCESsupplier(s_suppkey),FOREIGNKEY(lo_orderdate)REFERENCESdate(d_datekey));例4ALTERTABLElineorderADDlo_surrkeyint;--SQL命令解析：增加一個(gè)int類型的列l(wèi)o_surrkey：ALTERTABLElineorderALTERCOLUMNlo_quantitySMALLINT;--SQL命令解析：將lo_quantity列的數(shù)據(jù)類型修改為SMALLINT：ALTERTABLElineorderALTERCOLUMNlo_orderpriorityvarchar(15)NULL;--SQL命令解析：將lo_orderpriority列的NOTNULL約束修改為NULL約束：ALTERTABLElineorderDROPCOLUMNlo_shippriority;--SQL命令解析：刪除表中的列l(wèi)o_shippriority：ALTERTABLElineorderADDCONSTRAINTFK_SFOREIGNKEY(lo_suppkey)REFERENCESsupplier(s_suppkey);--SQL命令解析：在lineorder表中增加一個(gè)外鍵約束。CONSTRAINT關(guān)鍵字定義約束的名稱FK_S，然后定義表級(jí)參照完整性約束條件。例5注：因?yàn)閰⒄胀暾约s束關(guān)系不能直接刪除。DROPTABLEpart;例6CREATEUNIQUEINDEXs_name_InxONsupplier(s_name);CREATEINDEXs_n_c_InxONsupplier(s_nationASC,s_cityDESC);例7DROPINDEXsupplier.s_n_c_Inx;DROPINDEXs_name_InxONsupplier;例8注：需要先按13.1節(jié)內(nèi)容導(dǎo)入數(shù)據(jù)，然后執(zhí)行查詢。SELECT*FROMpart;SELECTp_partkey,p_name,p_mfgr,p_category,p_brand1,p_color,p_type,p_size,p_containerFROMpart;例9SELECTp_name,p_brand1,p_containerFROMpart;例10SELECTlname,fname,birthdateFROMcustomer;--SQL查詢解析：輸出表中原始的列信息，其中姓名分別存儲(chǔ)為lname和fname兩列，存儲(chǔ)有出生日期信息但沒有年齡信息。SELECTfname+''+lname,YEAR(birthdate)FROMcustomer;--SQL查詢解析：將字符型的lname和fname列進(jìn)行字符串連接操作，輸出以空格間隔的姓名格式的表達(dá)式，并通過(guò)日期函數(shù)YEAR計(jì)算出用戶出生年份。由于輸出的是表達(dá)式派生列，因此輸出默認(rèn)的列名為空。SELECTfname+''+lnameASfullname,(2015-YEAR(birthdate))ASageFROMcustomer;--SQL查詢解析：在列表達(dá)式后通過(guò)AS命令增加一個(gè)列別名，標(biāo)識(shí)表達(dá)式派生列，將通過(guò)構(gòu)造日期表達(dá)式計(jì)算出用戶在2015年時(shí)的年齡。SELECTfname+''+lnameASfullname,(2015-YEAR(birthdate))ASage,2015asupdatetimeFROMcustomer;--SQL查詢解析：增加一個(gè)常量輸出列，標(biāo)識(shí)當(dāng)前年份2015。例11SELECTs_regionFROMsupplier;--SQL命令解析：輸出s_region列中全部的取值，包括了重復(fù)的取值。SELECTDISTINCTs_regionFROMsupplier;--SQL命令解析：通過(guò)DISTINCT短語(yǔ)指定列s_region只輸出不同取值的成員，列中的每個(gè)取值只輸出一次。例12SELECTtotal_childrenFROMcustomerWHEREtotal_children>2;--SQL命令解析：輸出customer表中total_children>2的記錄的total_children列。SELECTlnameFROMcustomerWHERElname>'t';--SQL命令解析：輸出customer表中l(wèi)name>'t'的記錄的lname列。Lname是字符型屬性，表達(dá)式以字典序?yàn)闃?biāo)準(zhǔn)對(duì)字符型數(shù)據(jù)進(jìn)行比較。SELECTbirthdateFROMcustomerWHEREbirthdate>'1950-01-01';--SQL命令解析：輸出customer表中birthdate>'1950-01-01'的記錄的birthdate列。Birthdate為datetime數(shù)據(jù)類型，表達(dá)式中的日期常量需要滿足數(shù)據(jù)庫(kù)日期數(shù)據(jù)類型的格式，在SQLserver中的日期常量表示為'1950-01-01'。SELECTbirthdateFROMcustomerWHERE2015-YEAR(birthdate)>40;--SQL命令解析：輸出customer表中年齡超過(guò)40歲的用戶的birthdate。用戶年齡通過(guò)表達(dá)式2015-YEAR(birthdate)計(jì)算得到。SELECTtotal_children,num_cars_ownedFROMcustomerWHEREtotal_children>num_cars_owned;--SQL命令解析：輸出customer表中孩子數(shù)量超過(guò)汽車數(shù)量的記錄。total_children>num_cars_owned是兩個(gè)列表達(dá)式之間的比較操作。例13SELECT*FROMcustomerWHEREtotal_childrenBETWEEN2AND4;--SQL命令解析：輸出customer表中total_children數(shù)量介于2和4之間的記錄。SELECT*FROMcustomerWHEREtotal_children>=2ANDtotal_children<=4;--SQL命令解析：total_children>=2ANDtotal_children<=4等價(jià)于BETWEEN2AND4。SELECT*FROMcustomerWHERElnameBETWEEN'C'AND'H';--SQL命令解析：輸出customer表中l(wèi)name介于'C'和'H'之間的記錄，支持字符型數(shù)據(jù)上的范圍操作。SELECT*FROMcustomerWHEREbirthdateBETWEEN'1970-01-01'AND'1980-01-01';--SQL命令解析：輸出customer表中birthdate介于'1970-01-01'和'1980-01-01'之間的記錄，支持日期型數(shù)據(jù)上的范圍操作。例14SELECT*FROMcustomerWHEREtotal_childrenIN(1,4);--SQL命令解析：輸出total_children數(shù)量為1和4的記錄。SELECT*FROMcustomerWHERElnameIN('Chin','Hill');--SQL命令解析：輸出lname為'Chin'和'Hill'的記錄。SELECT*FROMcustomerWHEREbirthdateIN('1944-10-25','1956-04-26','1970-09-21');--SQL命令解析：輸出birthdate為'1944-10-25'、'1956-04-26'和'1970-09-21'的記錄。例15SELECT*FROMcustomerWHERElnameLIKE'C%n';--SQL命令解析：輸出customer表中l(wèi)name中以C開頭，最后一個(gè)字母為n的記錄。SELECT*FROMcustomerWHERElnameLIKE'%i%n%';--SQL命令解析：輸出customer表中l(wèi)name中包含字母i和n的記錄且字母i在字母n之前。SELECT*FROMcustomerWHERElnameLIKE'Pa_';--SQL命令解析：輸出customer表中l(wèi)name中以Pa開頭，最后一個(gè)字母為任意字符的記錄。SELECT*FROMcustomerWHERElnameLIKE'_h%n_';--SQL命令解析：輸出customer表中l(wèi)name中第二個(gè)字母為h，其后包含倒數(shù)第二個(gè)字母為n的記錄。SELECT*FROMcustomerWHERElnameLIKE'Chow\_Wang'ESCAPE'\';--SQL命令解析：輸出customer表中l(wèi)name中取值為Chow_Wang的記錄，其中_為普通字符，不是通配符，由'\'表示其后的_為普通字符。例16SELECT*FROMcustomerWHEREmiISNULL;--SQL命令解析：輸出customer表中mi列為空值為記錄。SELECT*FROMcustomerWHEREmiISNOTNULL;--SQL命令解析：輸出customer表中mi列不是空值為記錄。例17SELECT*FROMcustomerWHEREmiISNULLANDgender='F'ORtotal_children>2;--SQL命令解析：輸出customer表中mi列為空值并且性別為女的客戶記錄或者孩子數(shù)量大于2個(gè)的客戶記錄。SELECT*FROMcustomerWHEREmiISNULLAND(gender='F'ORtotal_children>2);--SQL命令解析：輸出customer表中mi列為空值并且至少滿足性別為女或者孩子數(shù)量大于2兩個(gè)條件之一的客戶記錄。例18SELECTCOUNT(*)FROMcustomer;--SQL命令分析：統(tǒng)計(jì)customer表中記錄的總數(shù)。SELECTCOUNT(mi)FROMcustomer;--SQL命令分析：統(tǒng)計(jì)customer表中mi的元組數(shù)。當(dāng)COUNT函數(shù)指定列時(shí)，列中的空值不進(jìn)行計(jì)數(shù)，只統(tǒng)計(jì)非空值的記錄數(shù)量而不是COUNT(*)所對(duì)應(yīng)的表中記錄數(shù)量。SELECTCOUNT(DISTINCTmi)FROMcustomer;--SQL命令分析：統(tǒng)計(jì)customer表中mi列中不重復(fù)值的個(gè)數(shù)。SELECTCOUNT(DISTINCTmi)FROMcustomerWHEREmi!='';--SQL命令分析：統(tǒng)計(jì)customer表中非空和非空白字符（''，長(zhǎng)度為0的字符）的記錄數(shù)量。SELECTSUM(total_children)FROMcustomer;--SQL命令分析：統(tǒng)計(jì)customer表中total_children的總和。SELECTAVG(total_children)FROMcustomer;--SQL命令分析：統(tǒng)計(jì)customer表中total_children的平均值。SELECTMAX(total_children)FROMcustomer;--SQL命令分析：統(tǒng)計(jì)customer表中total_children的最大值SELECTMIN(total_children)FROMcustomer;--SQL命令分析：統(tǒng)計(jì)customer表中total_children的最小值。SELECTAVG(2015-YEAR(birthdate))FROMcustomer;--SQL命令分析：統(tǒng)計(jì)customer表中通過(guò)birthdate列計(jì)算出的平均年齡。例19SELECTCOUNT(*)ASamountFROMcustomer;--SQL命令解析：統(tǒng)計(jì)customer表中記錄的總數(shù)。SELECTgender,COUNT(*)ASamountFROMcustomerGROUPBYgender;--SQL命令解析：按性別gender列分組統(tǒng)計(jì)customer表中客戶記錄的數(shù)量。SELECTeducation,gender,COUNT(*)ASamountFROMcustomerGROUPBYeducation,genderORDERBYeducation,gender;--SQL命令解析：按教育程度education和性別gender列分組統(tǒng)計(jì)customer表中客戶記錄的數(shù)量。例20SELECTeducation,gender,COUNT(*)ASamountFROMcustomerGROUPBYeducation,genderHAVINGCOUNT(*)>1000ORDERBYeducation,gender;--SQL命令解析：HAVING短語(yǔ)中的COUNT(*)>1000作為分組聚集計(jì)算結(jié)果的過(guò)濾條件，對(duì)分組聚集結(jié)果進(jìn)行篩選。SELECTeducation,gender,COUNT(*)ASamount,AVG(total_children)ASavg_childrenFROMcustomerGROUPBYeducation,genderHAVINGsum(num_cars_owned)>2000ORDERBYeducation,gender;--SQL命令解析：HAVING短語(yǔ)中可以使用輸出目標(biāo)列中沒有的聚集函數(shù)表達(dá)式。如HAVINGsum(num_cars_owned)>2000短語(yǔ)中sum(num_cars_owned)>2000并不是查詢輸出的聚集函數(shù)表達(dá)式，只用于對(duì)分組聚集計(jì)算結(jié)果進(jìn)行篩選。例21SELECTeducation,gender,COUNT(*)ASamountFROMcustomerGROUPBYeducation,genderORDERBYeducationASC,genderDESC;--SQL命令解析：對(duì)查詢結(jié)果按分組屬性排序，第一排序?qū)傩詾閑ducation升序，第二排序?qū)傩詾間ender降序。SELECTeducation,gender,COUNT(*)ASamountFROMcustomerGROUPBYeducation,genderORDERBYCOUNT(*);--SQL命令解析：對(duì)分組聚集結(jié)果按聚集計(jì)算結(jié)果升序排列。SELECTeducation,gender,COUNT(*)ASamountFROMcustomerGROUPBYeducation,genderORDERBYamount;--SQL命令解析：當(dāng)聚集表達(dá)式設(shè)置別名時(shí)，可以使用別名作為排序?qū)傩悦?，指代聚集表達(dá)式。例22SELECTcustomer_id,store_salesINTOsales_simpleFROMsales_fact_1997;SELECTcustomer_id,lname,fname,gender,educationINTOcustomer_simpleFROMcustomer;SELECTF.*,C.*FROMsales_simpleF,customer_simpleCWHEREF.customer_id=C.customer_id;SELECTF.*,C.*FROMsales_simpleFINNERJOINcustomer_simpleCONF.customer_id=C.customer_id;例23SELECTF.customer_id,F.store_sales,C.lname,C.fname,C.gender,C.educationFROMsales_simpleF,customer_simpleCWHEREF.customer_id=C.customer_id;例24SELECTF.customer_id,F.store_sales,C.lname,C.fname,C.gender,C.educationFROMsales_simpleF,customer_simpleCWHEREF.customer_id=C.customer_idANDF.store_salesBETWEEN4AND8ANDC.educationin('BachelorsDegree','GraduateDegree');例25SELECTC.education,AVG(F.store_sales)FROMsales_simpleF,customer_simpleCWHEREF.customer_id=C.customer_idANDC.gender='F'GROUPBYC.education;例26SELECTCOUNT(*)FROMcustomerc1,customerc2WHEREc1.lname=c2.fname;SELECTc1.customer_id,c1.lname,c1.fname,c2.customer_id,c2.lname,c2.fnameFROMcustomerc1,customerc2WHEREc1.lname=c2.fname;例27SELECTF.*,C.*FROMsales_simpleFLEFTOUTERJOINcustomer_simpleCONF.customer_id=C.customer_id;SELECTF.*,C.*FROMsales_simpleFRIGHTOUTERJOINcustomer_simpleCONF.customer_id=C.customer_id;例28注：前兩個(gè)查詢使用SSB數(shù)據(jù)庫(kù)，后一個(gè)查詢使用Foodmart數(shù)據(jù)庫(kù)，注意sales_fact_1997表名稱與自己的數(shù)據(jù)庫(kù)名稱一致SELECTc_name,p_name,s_name,d_date,lo_quantityFROMcustomer,part,supplier,date,lineorderWHERElo_custkey=c_custkeyANDlo_partkey=p_partkeyANDlo_suppkey=s_suppkeyANDlo_orderdate=d_datekey;SELECTc_name,p_name,s_name,d_date,lo_quantityFROMlineorderINNERJOINcustomerONlo_custkey=c_custkeyINNERJOINpartONlo_partkey=p_partkeyINNERJOINsupplierONlo_suppkey=s_suppkeyINNERJOINdateONlo_orderdate=d_datekey;SELECTr.sales_region,s.store_name,t.the_date,pr.media_type,c.fname,duct_name,duct_category,sf.store_salesFROMregionr,stores,time_by_dayt,promotionpr,sales_fact_1997sf,customerc,productp,product_classpcWHEREs.region_id=r.region_idANDsf.store_id=s.store_idANDsf.time_id=t.time_idANDmotion_id=motion_idANDsf.customer_id=c.customer_idANDduct_id=duct_idANDduct_class_id=duct_class_id;例29SELECTSUM(store_sales)FROMsales_fact_1997WHEREcustomer_idIN(SELECTcustomer_idFROMcustomerWHEREgender='F');SELECTSUM(store_sales)FROMsales_fact_1997F,customerCWHEREF.customer_id=C.customer_idANDC.gender='F';例30SELECTSUM(store_sales)FROMsales_fact_1997WHEREproduct_idIN(SELECTproduct_idFROMproductWHEREproduct_class_idIN(SELECTproduct_class_idFROMproduct_classWHEREproduct_category='Seafood'));SELECTSUM(store_sales)FROMsales_fact_1997F,productP,product_classPCWHEREF.product_id=P.product_idANDP.product_class_id=PC.product_class_idANDproduct_category='Seafood';例31SELECTCOUNT(*)FROMsales_fact_1997_1997,customerWHEREsales_fact_1997_1997.customer_id=customer.customer_idANDgender='F'ANDstore_sales>(SELECT1.5*AVG(store_sales)FROMsales_fact_1997_1997WHEREsales_fact_1997_1997.customer_id=customer.customer_id);SELECTCOUNT(*)FROMsales_fact_1997,customerWHEREsales_fact_1997.customer_id=customer.customer_idANDstore_sales>(SELECT1.5*AVG(store_sales)FROMsales_fact_1997WHEREsales_fact_1997.customer_id=customer.customer_idANDgender='F');SELECTCOUNT(*)FROMsales_fact_1997WHEREstore_sales>(SELECT1.5*AVG(store_sales)FROMsales_fact_1997,customerWHEREsales_fact_1997.customer_id=customer.customer_idANDgender='F');SELECTCOUNT(*)FROMsales_fact_1997,customerWHEREsales_fact_1997.customer_id=customer.customer_idANDgender='F'ANDstore_sales>(SELECT1.5*AVG(store_sales)FROMsales_fact_1997);例32SELECTCOUNT(*)FROMsales_fact_1997WHEREstore_sales>ANY(SELECTstore_salesFROMsales_fact_1997,customerWHEREsales_fact_1997.customer_id=customer.customer_idANDfname='Jeanne');--SQL命令解析：內(nèi)層查詢返回用戶Jeanne的所有store_sales值，外層查詢判斷store_sales是否滿足大于內(nèi)層查詢store_sales值中任意一個(gè)的條件，并對(duì)滿足條件的記錄進(jìn)行計(jì)數(shù)。SELECTCOUNT(*)FROMsales_fact_1997WHEREstore_sales>(SELECTMIN(store_sales)FROMsales_fact_1997,customerWHEREsales_fact_1997.customer_id=customer.customer_idANDfname='Jeanne');--SQL命令解析：>ANY等價(jià)于大于多值結(jié)果集中的最小值，上述兩個(gè)SQL命令等價(jià)。例33SELECTCOUNT(*)FROMsales_fact_1997WHEREstore_sales>ALL(SELECTstore_salesFROMsales_fact_1997,customerWHEREsales_fact_1997.customer_id=customer.customer_idANDfname='Jeanne');--SQL命令解析：內(nèi)層查詢返回用戶Jeanne的所有store_sales值，外層查詢判斷store_sales是否滿足大于內(nèi)層查詢store_sales全部值的條件，并對(duì)滿足條件的記錄進(jìn)行計(jì)數(shù)。SELECTCOUNT(*)FROMsales_fact_1997WHEREstore_sales>(SELECTMAX(store_sales)FROMsales_fact_1997,customerWHEREsales_fact_1997.customer_id=customer.customer_idANDfname='Jeanne');--SQL命令解析：>ALL等價(jià)于大于多值結(jié)果集中的最大值，上述兩個(gè)SQL命令等價(jià)。例34SELECTCOUNT(*)FROMcustomer;--SQL命令解析：統(tǒng)計(jì)客戶總數(shù)。SELECTCOUNT(*)FROMcustomerWHEREEXISTS(SELECT*FROMsales_fact_1997WHEREsales_fact_1997.customer_id=customer.customer_id);--SQL命令解析：判斷外層查詢的每一條記錄的customer_id值是否在內(nèi)層查詢中存在滿足sales_fact_1997.customer_id=customer.customer_id條件的記錄，如果存在則外層查詢進(jìn)行計(jì)數(shù)。SELECTCOUNT(*)FROMcustomerWHERENOTEXISTS(SELECT*FROMsales_fact_1997WHEREsales_fact_1997.customer_id=customer.customer_id);--SQL命令解析：判斷外層查詢的每一條記錄的customer_id值是否在內(nèi)存查詢中不存在滿足sales_fact_1997.customer_id=customer.customer_id條件的記錄，如果不存在則外層查詢進(jìn)行計(jì)數(shù)。后兩個(gè)查詢分別統(tǒng)計(jì)了存在和不存在銷售記錄的customer數(shù)量，統(tǒng)計(jì)結(jié)果之和與customer表中記錄總數(shù)相等。例35SELECTcustomer_idFROMcustomerWHEREgender='F'ANDtotal_children>3UNIONSELECTcustomer_idFROMcustomerWHEREmember_card='Silver'ANDnum_cars_owned=1;--SQL命令解析：將UNION連接的兩個(gè)子查詢的結(jié)果集合并，兩個(gè)結(jié)果集中的重復(fù)元組自動(dòng)去掉。SELECTcustomer_idFROMcustomerWHEREgender='F'ANDtotal_children>3UNIONALLSELECTcustomer_idFROMcustomerWHEREmember_card='Silver'ANDnum_cars_owned=1;--SQL命令解析：將UNION連接的兩個(gè)子查詢的結(jié)果集合并，保留兩個(gè)結(jié)果集中重復(fù)的元組。SELECTcustomer_idFROMcustomerWHERE(gender='F'ANDtotal_children>3)OR(member_card='Silver'ANDnum_cars_owned=1);--SQL命令解析：將兩個(gè)子查詢轉(zhuǎn)換為用OR連接的復(fù)合謂詞，查詢結(jié)果集小于UNIONALL查詢的元組數(shù)量，與UNION查詢結(jié)果集相同。例36SELECTcustomer_idFROMcustomerWHEREgender='F'ANDtotal_children>3INTERSECTSELECTcustomer_idFROMcustomerWHEREmember_card='Silver'ANDnum_cars_owned=1;--SQL命令解析：3個(gè)以上孩子的女性子查詢結(jié)果集與有一輛車的銀牌會(huì)員子查詢的結(jié)果集執(zhí)行交集運(yùn)算，生成滿足兩個(gè)集合條件的查詢結(jié)果集。--交集子查詢等價(jià)于子查詢條件的與運(yùn)算：SELECTcustomer_idFROMcustomerWHERE(gender='F'ANDtotal_children>3)AND(member_card='Silver'ANDnum_cars_owned=1);例37SELECTcustomer_idFROMcustomerWHEREgender='F'ANDtotal_children>3INTERSECTSELECTcustomer_idFROMcustomerWHEREmember_card='Silver'ANDnum_cars_owned=1;--SQL命令解析：3個(gè)以上孩子的女性子查詢結(jié)果集與有一輛車的銀牌會(huì)員子查詢的結(jié)果集執(zhí)行交集運(yùn)算，生成滿足兩個(gè)集合條件的查詢結(jié)果集。--交集子查詢等價(jià)于子查詢條件的與運(yùn)算：SELECTcustomer_idFROMcustomerWHERE(gender='F'ANDtotal_children>3)AND(member_card='Silver'ANDnum_cars_owned=1);例38SELECTcustomer_idFROMcustomerWHEREmember_card='Silver'ANDnum_cars_owned=1UNIONSELECTcustomer_idFROMsales_fact_1997WHEREstore_cost<0.2;--SQL命令解析：集合操作連接的子查詢可以使用相同的表，也可以使用不同的表，需要滿足查詢結(jié)果集具有相同的結(jié)構(gòu)和語(yǔ)義。例39SELECTlname,C_sales.SUM_salesFROMcustomerC,(SELECTcustomer_id,SUM(store_sales)FROMsales_fact_1997GROUPBYcustomer_idHAVINGAVG(store_sales)>7)ASC_sales(customer_id,SUM_sales)WHEREC.customer_id=C_sales.customer_id;WITHC_sales(customer_id,SUM_sales)AS(SELECTcustomer_id,SUM(store_sales)FROMsales_fact_1997GROUPBYcustomer_idHAVINGAVG(store_sales)>7)SELECTlname,C_sales.SUM_salesFROMcustomerC,C_salesWHEREC.customer_id=C_sales.customer_id;例40INSERTINTOcustomer(customer_id,lname,fname,city)VALUES(10282,'JACK','ROSE','NewYork');例41CREATETABLEcust_test(cityVARCHAR(50),cust_countINT);INSERTINTOcust_testSELECTcity,COUNT(*)FROMcustomerGROUPBYcityORDERBYcity;例42SELECTcustomer_id,lname,cityINTOcust_test1FROMcustomer;例43SELECTcity,COUNT(*)AScust_countINTOcust_test2FROMcustomerGROUPBYcityORDERBYcity;例44UPDATEcustomerSETgender='M'WHEREcustomer_id=10282;例45UPDATEcustomerSETmember_card='Silver'WHEREyearly_income='$70K-$90K'ANDnum_children_at_home>2ANDmember_card='Bronze';例46UPDATEcustomerSETmember_card='Golden'FROM(SELECTcustomer_id,SUM(store_sales)ASsum_custFROMsales_fact_1997GROUPBYcustomer_idHAVINGSUM(store_sales)>800)AScust_sumWHEREcustomer.customer_id=cust_sum.customer_id;例47ALTERTABLEtime_by_dayADDholiday_flagchar(1);--SQL命令解析：修改日期表time_by_day，增加一個(gè)日期標(biāo)識(shí)列，由'T'或'F'標(biāo)識(shí)是否假日。UPDATEtime_by_daySETholiday_flag='F';--SQL命令解析：將holiday_flag初始化為'F'UPDATEtime_by_daySETholiday_flag='T'WHEREthe_dateIN('1997-01-01','1997-05-01','1997-06-01','1997-10-01');--SQL命令解析：將1997年的1月1日，5月1日，6月1日，10月1日記錄的holiday_flag標(biāo)識(shí)為'T'?？梢园慈掌谥鹉陿?biāo)識(shí)假日，也可以對(duì)各年假日按月-日批量修改。UPDATEtime_by_daySETholiday_flag='T'WHEREmonth_of_year=10ANDday_of_month=1;--SQL命令解析：將日期表中10月1日全部標(biāo)識(shí)為假日。UPDATEtime_by_daySETholiday_flag='T'WHEREmonth_of_year=5ANDday_of_month=1;--SQL命令解析：將日期表中5月1日全部標(biāo)識(shí)為假日。例48DELETEFROMcust_test;例49DELETEFROMcustomerWHEREcustomer_id=10282;例50DELETEFROMsales_fact_1997FROMcustomerctINNERJOINsales_fact_1997sfONct.customer_id=sf.customer_idWHEREct.state_province='CA';DELETEFROMsales_fact_1997WHEREcustomer_idIN(SELECTcustomer_idFROMcustomerWHEREstate_province='CA');例51CREATEVIEWcust_sales(customer_id,fullname,gender,education,store_sales,store_units,store_cost)ASSELECTC.customer_id,C.fullname,C.gender,C.education,F.store_sales,F.unit_sales,F.store_costFROMcustomerC,sales_fact_1997FWHEREC.customer_id=F.customer_id;例52CREATEVIEWgender_sales(gender,sum_sales,sum_units,sum_cost)ASSELECTgender,SUM(store_sales),SUM(unit_sales),SUM(store_cost)FROMcustomerC,sales_fact_1997FWHEREC.customer_id=F.customer_idGROUPBYgender;CREATEVIEWgender_salesASSELECTgender,SUM(store_sales)ASsum_sales,SUM(unit_sales)ASsum_units,SUM(store_cost)ASsum_costFROMcustomerC,sales_fact_1997FWHEREC.customer_id=F.customer_idGROUPBYgender;例53DROPVIEWgender_sales;例54CREATEVIEWSSB_tablesASSELECTcustomer.*,part.*,supplier.*,date.*,lineorder.*FROMcustomer,part,supplier,date,lineorderWHERElo_custkey=c_custkeyANDlo_partkey=p_partkeyANDlo_suppkey=s_suppkeyANDlo_orderdate=d_datekey;SELECTd_year,c_nation,SUM(CAST(lo_revenue-lo_supplycostASbigint))asprofitFROMdate,customer,supplier,part,lineorderWHERElo_custkey=c_custkeyANDlo_suppkey=s_suppkeyANDlo_partkey=p_partkeyANDlo_orderdate=d_datekeyANDc_region='AMERICA'ANDs_region='AMERICA'AND(p_mfgr='MFGR#1'ORp_mfgr='MFGR#2')GROUPBYd_year,c_nationORDERBYd_year,c_nation;--SQL命令改寫為基于視圖的單表查詢。SELECTd_year,c_nation,SUM(CAST(lo_revenue-lo_supplycostASbigint))asprofitFROMSSB_tablesWHEREc_region='AMERICA'ANDs_region='AMERICA'AND(p_mfgr='MFGR#1'ORp_mfgr='MFGR#2')GROUPBYd_year,c_nationORDERBYd_year,c_nation;例55SELECTLEN(lname)FROMcustomer;--SQL命令解析：查詢customer表lname列的字符串長(zhǎng)度。SELECTMAX(LEN(lname)),MIN(LEN(lname)),AVG(LEN(lname))FROMcustomer;--SQL命令解析：查看customer表lname列的最大長(zhǎng)度、最小長(zhǎng)度和平均長(zhǎng)度。SELECTCHARINDEX('sh','Washington');--返回3SELECTLEFT('李小明',1);--返回'李'SELECTRIGHT('李小明',2);--返回'小明'SELECTSUBSTRING('$30K-$50K',8,4);--返回'$50K'例56SELECTLEFT(address1,CHARINDEX('',address1)-1)ASaddressNo,address1FROMcustomer;例57SELECTDATEDIFF(YEAR,'10-01-1949','10-01-2015');--返回66SELECTDATEDIFF(MONTH,'10-01-1949','10-01-2015');--返回792SELECTDATEDIFF(DAY,'10-01-1949','10-01-2015');--返回24106SELECTbirthdate,YEAR(GETDATE())-YEAR(birthdate)ASAGE0,DATEDIFF(YEAR,birthdate,GETDATE())ASAGE1,DATEDIFF(DAY,birthdate,GETDATE())/365ASAGE2FROMcustomer;例58SELECTSUM(CAST(lo_revenueASbigint))FROMlineorder;例59SELECTAVG(CAST(lo_revenueASbigint))FROMlineorder;--SQL命令解析：lo_revenue為bigint類型，AVG結(jié)果也為bigint類型。SELECTCAST(AVG(CAST(lo_revenueASreal))ASdecimal(10,2))FROMlineorder;--SQL命令解析：使用CAST函數(shù)將AVG函數(shù)計(jì)算結(jié)果轉(zhuǎn)換為帶有兩位小數(shù)位的decimal(10,2)類型。例60SELECTCONVERT(numeric(15,2),AVG(CONVERT(real,lo_extendedprice*lo_quantity)))asrevenueFROMlineorder;例61SELECTlo_extendedprice,lo_quantity,lo_discount,1-CONVERT(decimal(4,2),lo_discount)/100asdiscountFROMlineorder;SELECTCONVERT(numeric(18,2),SUM(lo_extendedprice*lo_quantity*(1-CONVERT(decimal(4,2),lo_discount)/100)))FROMlineorder;例62SELECTcustomer_id,lname,ROW_NUMBER()OVER(ORDERBYlname)ASRowNumFROMcustomerORDERBYlname;SELECTcustomer_id,lname,RANK()OVER(ORDERBYlname)ASRowNumFROMcustomerORDERBYlname;SELECTcustomer_id,lname,DENSE_RANK()OVER(ORDERBYlname)ASRowNumFROMcustomerORDERBYlname;SELECTcustomer_id,SUM(store_sales)ASsalesFROMsales_fact_1997GROUPBYcustomer_idORDERBYsales;SELECTcustomer_id,SUM(store_sales)ASsales,RANK()OVER(ORDERBYsales)ASRowNumFROMsales_fact_1997GROUPBYcustomer_idORDERBYsales;CREATEVIEWrank_sales(customer_id,sales)ASSELECTcustomer_id,SUM(store_sales)ASsalesFROMsales_fact_1997GROUPBYcustomer_id;SELECTcustomer_id,sales,RANK()OVER(ORDERBYsales)ASrownumFROMrank_salesORDERBYsales;例63SELECTAVG(store_sales)ASavg_sales,STDEV(store_sales)ASstedv_sales,STDEVP(store_sales)ASstdevp_sales,VAR(store_sales)ASvar_sales,VARP(store_sales)ASvarp_salesFROMsales_fact_1997;例64SELECTgender,marital_status,houseowner,SUM(total_children)Num_childrenFROMcustomerC,sales_fact_1997FWHEREC.customer_id=F.customer_idGROUPBYgender,marital_status,houseowner;SELECTgender,marital_status,houseowner,SUM(total_children)Num_childrenFROMcustomerC,sales_fact_1997FWHEREC.customer_id=F.customer_idGROUPBYROLLUP(gender,marital_status,houseowner);SELECTgender,marital_status,houseowner,SUM(total_children)Num_childrenFROMcustomerC,sales_fact_1997FWHEREC.customer_id=F.customer_idGROUPBYCUBE(gender,marital_status,houseowner);案例實(shí)踐SSB13個(gè)測(cè)試查詢：--Q1.1selectsum(CONVERT(real,lo_extendedprice*lo_quantity))asrevenuefromlineorder,datewherelo_orderdate=d_datekeyandd_year=1993andlo_discountbetween1and3andlo_quantity<25;--Q1.2selectsum(CONVERT(real,lo_extendedprice*lo_quantity))asrevenuefromlineorder,datewherelo_orderdate=d_datekeyandd_yearmonth='Jan1994'andlo_discountbetween4and6andlo_quantitybetween26and35;--Q1.3selectsum(CONVERT(real,lo_extendedprice*lo_quantity))asrevenuefromlineorder,datewherelo_orderdate=d_datekeyandd_weeknuminyear=6andd_year=1994andlo_discountbetween5and7andlo_quantitybetween26and35;--Q2.1selectsum(lo_revenue),d_year,p_brand1fromlineorder,date,part,supplierwherelo_orderdate=d_datekeyandlo_partkey=p_partkeyandlo_suppkey=s_suppkeyandp_category='MFGR#12'ands_region='AMERICA'groupbyd_year,p_brand1orderbyd_year,p_brand1;--Q2.2selectsum(lo_revenue),d_year,p_brand1fromlineorder,date,part,supplierwherelo_orderdate=d_datekeyandlo_partkey=p_partkeyandlo_suppkey=s_suppkeyandp_brand1between'MFGR#2221'and'MFGR#2228'ands_region='ASIA'groupbyd_year,p_brand1orderbyd_year,p_brand1;--Q2.3selectsum(lo_revenue),d_year,p_brand1fromlineorder,date,part,supplierwherelo_orderdate=d_datekeyandlo_partkey=p_partkeyandlo_suppkey=s_suppkeyandp_brand1='MFGR#2239'ands_re