大数据分析计算机基础-数据库部分SQL命令素材

大数据分析计算机基础——数据库基础SQL命令教学素材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先创建两个临时表part,supplier作为被参照表，然后创建带有参照完整性关系的表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),/*使用表级约束定义复合主键*/FOREIGNKEY(PS_PARTKEY)REFERENCESPART(p_partkey),/*使用表级约束定义参照完整性约束*/FOREIGNKEY(PS_SUPPKEY)REFERENCESSUPPLIER(S_SUPPKEY));例3--1.创建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.创建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.创建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.创建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.创建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命令解析：增加一个int类型的列lo_surrkey：ALTERTABLElineorderALTERCOLUMNlo_quantitySMALLINT;--SQL命令解析：将lo_quantity列的数据类型修改为SMALLINT：ALTERTABLElineorderALTERCOLUMNlo_orderpriorityvarchar(15)NULL;--SQL命令解析：将lo_orderpriority列的NOTNULL约束修改为NULL约束：ALTERTABLElineorderDROPCOLUMNlo_shippriority;--SQL命令解析：删除表中的列lo_shippriority：ALTERTABLElineorderADDCONSTRAINTFK_SFOREIGNKEY(lo_suppkey)REFERENCESsupplier(s_suppkey);--SQL命令解析：在lineorder表中增加一个外键约束。CONSTRAINT关键字定义约束的名称FK_S，然后定义表级参照完整性约束条件。例5注：因为参照完整性约束关系不能直接删除。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节内容导入数据，然后执行查询。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查询解析：输出表中原始的列信息，其中姓名分别存储为lname和fname两列，存储有出生日期信息但没有年龄信息。SELECTfname+''+lname,YEAR(birthdate)FROMcustomer;--SQL查询解析：将字符型的lname和fname列进行字符串连接操作，输出以空格间隔的姓名格式的表达式，并通过日期函数YEAR计算出用户出生年份。由于输出的是表达式派生列，因此输出默认的列名为空。SELECTfname+''+lnameASfullname,(2015-YEAR(birthdate))ASageFROMcustomer;--SQL查询解析：在列表达式后通过AS命令增加一个列别名，标识表达式派生列，将通过构造日期表达式计算出用户在2015年时的年龄。SELECTfname+''+lnameASfullname,(2015-YEAR(birthdate))ASage,2015asupdatetimeFROMcustomer;--SQL查询解析：增加一个常量输出列，标识当前年份2015。例11SELECTs_regionFROMsupplier;--SQL命令解析：输出s_region列中全部的取值，包括了重复的取值。SELECTDISTINCTs_regionFROMsupplier;--SQL命令解析：通过DISTINCT短语指定列s_region只输出不同取值的成员，列中的每个取值只输出一次。例12SELECTtotal_childrenFROMcustomerWHEREtotal_children>2;--SQL命令解析：输出customer表中total_children>2的记录的total_children列。SELECTlnameFROMcustomerWHERElname>'t';--SQL命令解析：输出customer表中lname>'t'的记录的lname列。Lname是字符型属性，表达式以字典序为标准对字符型数据进行比较。SELECTbirthdateFROMcustomerWHEREbirthdate>'1950-01-01';--SQL命令解析：输出customer表中birthdate>'1950-01-01'的记录的birthdate列。Birthdate为datetime数据类型，表达式中的日期常量需要满足数据库日期数据类型的格式，在SQLserver中的日期常量表示为'1950-01-01'。SELECTbirthdateFROMcustomerWHERE2015-YEAR(birthdate)>40;--SQL命令解析：输出customer表中年龄超过40岁的用户的birthdate。用户年龄通过表达式2015-YEAR(birthdate)计算得到。SELECTtotal_children,num_cars_ownedFROMcustomerWHEREtotal_children>num_cars_owned;--SQL命令解析：输出customer表中孩子数量超过汽车数量的记录。total_children>num_cars_owned是两个列表达式之间的比较操作。例13SELECT*FROMcustomerWHEREtotal_childrenBETWEEN2AND4;--SQL命令解析：输出customer表中total_children数量介于2和4之间的记录。SELECT*FROMcustomerWHEREtotal_children>=2ANDtotal_children<=4;--SQL命令解析：total_children>=2ANDtotal_children<=4等价于BETWEEN2AND4。SELECT*FROMcustomerWHERElnameBETWEEN'C'AND'H';--SQL命令解析：输出customer表中lname介于'C'和'H'之间的记录，支持字符型数据上的范围操作。SELECT*FROMcustomerWHEREbirthdateBETWEEN'1970-01-01'AND'1980-01-01';--SQL命令解析：输出customer表中birthdate介于'1970-01-01'和'1980-01-01'之间的记录，支持日期型数据上的范围操作。例14SELECT*FROMcustomerWHEREtotal_childrenIN(1,4);--SQL命令解析：输出total_children数量为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表中lname中以C开头，最后一个字母为n的记录。SELECT*FROMcustomerWHERElnameLIKE'%i%n%';--SQL命令解析：输出customer表中lname中包含字母i和n的记录且字母i在字母n之前。SELECT*FROMcustomerWHERElnameLIKE'Pa_';--SQL命令解析：输出customer表中lname中以Pa开头，最后一个字母为任意字符的记录。SELECT*FROMcustomerWHERElnameLIKE'_h%n_';--SQL命令解析：输出customer表中lname中第二个字母为h，其后包含倒数第二个字母为n的记录。SELECT*FROMcustomerWHERElnameLIKE'Chow\_Wang'ESCAPE'\';--SQL命令解析：输出customer表中lname中取值为Chow_Wang的记录，其中_为普通字符，不是通配符，由'\'表示其后的_为普通字符。例16SELECT*FROMcustomerWHEREmiISNULL;--SQL命令解析：输出customer表中mi列为空值为记录。SELECT*FROMcustomerWHEREmiISNOTNULL;--SQL命令解析：输出customer表中mi列不是空值为记录。例17SELECT*FROMcustomerWHEREmiISNULLANDgender='F'ORtotal_children>2;--SQL命令解析：输出customer表中mi列为空值并且性别为女的客户记录或者孩子数量大于2个的客户记录。SELECT*FROMcustomerWHEREmiISNULLAND(gender='F'ORtotal_children>2);--SQL命令解析：输出customer表中mi列为空值并且至少满足性别为女或者孩子数量大于2两个条件之一的客户记录。例18SELECTCOUNT(*)FROMcustomer;--SQL命令分析：统计customer表中记录的总数。SELECTCOUNT(mi)FROMcustomer;--SQL命令分析：统计customer表中mi的元组数。当COUNT函数指定列时，列中的空值不进行计数，只统计非空值的记录数量而不是COUNT(*)所对应的表中记录数量。SELECTCOUNT(DISTINCTmi)FROMcustomer;--SQL命令分析：统计customer表中mi列中不重复值的个数。SELECTCOUNT(DISTINCTmi)FROMcustomerWHEREmi!='';--SQL命令分析：统计customer表中非空和非空白字符（''，长度为0的字符）的记录数量。SELECTSUM(total_children)FROMcustomer;--SQL命令分析：统计customer表中total_children的总和。SELECTAVG(total_children)FROMcustomer;--SQL命令分析：统计customer表中total_children的平均值。SELECTMAX(total_children)FROMcustomer;--SQL命令分析：统计customer表中total_children的最大值SELECTMIN(total_children)FROMcustomer;--SQL命令分析：统计customer表中total_children的最小值。SELECTAVG(2015-YEAR(birthdate))FROMcustomer;--SQL命令分析：统计customer表中通过birthdate列计算出的平均年龄。例19SELECTCOUNT(*)ASamountFROMcustomer;--SQL命令解析：统计customer表中记录的总数。SELECTgender,COUNT(*)ASamountFROMcustomerGROUPBYgender;--SQL命令解析：按性别gender列分组统计customer表中客户记录的数量。SELECTeducation,gender,COUNT(*)ASamountFROMcustomerGROUPBYeducation,genderORDERBYeducation,gender;--SQL命令解析：按教育程度education和性别gender列分组统计customer表中客户记录的数量。例20SELECTeducation,gender,COUNT(*)ASamountFROMcustomerGROUPBYeducation,genderHAVINGCOUNT(*)>1000ORDERBYeducation,gender;--SQL命令解析：HAVING短语中的COUNT(*)>1000作为分组聚集计算结果的过滤条件，对分组聚集结果进行筛选。SELECTeducation,gender,COUNT(*)ASamount,AVG(total_children)ASavg_childrenFROMcustomerGROUPBYeducation,genderHAVINGsum(num_cars_owned)>2000ORDERBYeducation,gender;--SQL命令解析：HAVING短语中可以使用输出目标列中没有的聚集函数表达式。如HAVINGsum(num_cars_owned)>2000短语中sum(num_cars_owned)>2000并不是查询输出的聚集函数表达式，只用于对分组聚集计算结果进行筛选。例21SELECTeducation,gender,COUNT(*)ASamountFROMcustomerGROUPBYeducation,genderORDERBYeducationASC,genderDESC;--SQL命令解析：对查询结果按分组属性排序，第一排序属性为education升序，第二排序属性为gender降序。SELECTeducation,gender,COUNT(*)ASamountFROMcustomerGROUPBYeducation,genderORDERBYCOUNT(*);--SQL命令解析：对分组聚集结果按聚集计算结果升序排列。SELECTeducation,gender,COUNT(*)ASamountFROMcustomerGROUPBYeducation,genderORDERBYamount;--SQL命令解析：当聚集表达式设置别名时，可以使用别名作为排序属性名，指代聚集表达式。例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注：前两个查询使用SSB数据库，后一个查询使用Foodmart数据库，注意sales_fact_1997表名称与自己的数据库名称一致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命令解析：内层查询返回用户Jeanne的所有store_sales值，外层查询判断store_sales是否满足大于内层查询store_sales值中任意一个的条件，并对满足条件的记录进行计数。SELECTCOUNT(*)FROMsales_fact_1997WHEREstore_sales>(SELECTMIN(store_sales)FROMsales_fact_1997,customerWHEREsales_fact_1997.customer_id=customer.customer_idANDfname='Jeanne');--SQL命令解析：>ANY等价于大于多值结果集中的最小值，上述两个SQL命令等价。例33SELECTCOUNT(*)FROMsales_fact_1997WHEREstore_sales>ALL(SELECTstore_salesFROMsales_fact_1997,customerWHEREsales_fact_1997.customer_id=customer.customer_idANDfname='Jeanne');--SQL命令解析：内层查询返回用户Jeanne的所有store_sales值，外层查询判断store_sales是否满足大于内层查询store_sales全部值的条件，并对满足条件的记录进行计数。SELECTCOUNT(*)FROMsales_fact_1997WHEREstore_sales>(SELECTMAX(store_sales)FROMsales_fact_1997,customerWHEREsales_fact_1997.customer_id=customer.customer_idANDfname='Jeanne');--SQL命令解析：>ALL等价于大于多值结果集中的最大值，上述两个SQL命令等价。例34SELECTCOUNT(*)FROMcustomer;--SQL命令解析：统计客户总数。SELECTCOUNT(*)FROMcustomerWHEREEXISTS(SELECT*FROMsales_fact_1997WHEREsales_fact_1997.customer_id=customer.customer_id);--SQL命令解析：判断外层查询的每一条记录的customer_id值是否在内层查询中存在满足sales_fact_1997.customer_id=customer.customer_id条件的记录，如果存在则外层查询进行计数。SELECTCOUNT(*)FROMcustomerWHERENOTEXISTS(SELECT*FROMsales_fact_1997WHEREsales_fact_1997.customer_id=customer.customer_id);--SQL命令解析：判断外层查询的每一条记录的customer_id值是否在内存查询中不存在满足sales_fact_1997.customer_id=customer.customer_id条件的记录，如果不存在则外层查询进行计数。后两个查询分别统计了存在和不存在销售记录的customer数量，统计结果之和与customer表中记录总数相等。例35SELECTcustomer_idFROMcustomerWHEREgender='F'ANDtotal_children>3UNIONSELECTcustomer_idFROMcustomerWHEREmember_card='Silver'ANDnum_cars_owned=1;--SQL命令解析：将UNION连接的两个子查询的结果集合并，两个结果集中的重复元组自动去掉。SELECTcustomer_idFROMcustomerWHEREgender='F'ANDtotal_children>3UNIONALLSELECTcustomer_idFROMcustomerWHEREmember_card='Silver'ANDnum_cars_owned=1;--SQL命令解析：将UNION连接的两个子查询的结果集合并，保留两个结果集中重复的元组。SELECTcustomer_idFROMcustomerWHERE(gender='F'ANDtotal_children>3)OR(member_card='Silver'ANDnum_cars_owned=1);--SQL命令解析：将两个子查询转换为用OR连接的复合谓词，查询结果集小于UNIONALL查询的元组数量，与UNION查询结果集相同。例36SELECTcustomer_idFROMcustomerWHEREgender='F'ANDtotal_children>3INTERSECTSELECTcustomer_idFROMcustomerWHEREmember_card='Silver'ANDnum_cars_owned=1;--SQL命令解析：3个以上孩子的女性子查询结果集与有一辆车的银牌会员子查询的结果集执行交集运算，生成满足两个集合条件的查询结果集。--交集子查询等价于子查询条件的与运算：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个以上孩子的女性子查询结果集与有一辆车的银牌会员子查询的结果集执行交集运算，生成满足两个集合条件的查询结果集。--交集子查询等价于子查询条件的与运算：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命令解析：集合操作连接的子查询可以使用相同的表，也可以使用不同的表，需要满足查询结果集具有相同的结构和语义。例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，增加一个日期标识列，由'T'或'F'标识是否假日。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标识为'T'。可以按日期逐年标识假日，也可以对各年假日按月-日批量修改。UPDATEtime_by_daySETholiday_flag='T'WHEREmonth_of_year=10ANDday_of_month=1;--SQL命令解析：将日期表中10月1日全部标识为假日。UPDATEtime_by_daySETholiday_flag='T'WHEREmonth_of_year=5ANDday_of_month=1;--SQL命令解析：将日期表中5月1日全部标识为假日。例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列的字符串长度。SELECTMAX(LEN(lname)),MIN(LEN(lname)),AVG(LEN(lname))FROMcustomer;--SQL命令解析：查看customer表lname列的最大长度、最小长度和平均长度。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结果也为bigint类型。SELECTCAST(AVG(CAST(lo_revenueASreal))ASdecimal(10,2))FROMlineorder;--SQL命令解析：使用CAST函数将AVG函数计算结果转换为带有两位小数位的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);案例实践SSB13个测试查询：--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