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

大数据分析计算机基础——数据库基础SQL命令教学素材

12.2例1CREATE SCHEMASSB CREATE TABLE part p_partkey int,p_name varchar22,p_category varchar7CREATE VIEWpart_view AS SELECT pname,pcategory FROM part WHEREp partkey200;CREATE SCHEMASSB;CREATE TABLESSB.partp_partkey int,p_name varchar22,p_category varchar7;CREATE VIEWSSB.part_view ASSELECT pname,pcategory FROM part WHEREp partkey200;DROP TABLESSB.part;DROP VIEWSSB.part_view;DROP SCHEMASSB;例2先创建两个临时表part,supplier作为被参照表，然后创建带有参照完整性关系的表partsupp CREATE TABLE partp_partkey intprimary key,p_name varchar22;CREATE TABLEsupplier SSUPPKEY intprimary key,s name varchar22;CREATE TABLEPARTSUPP PS_PARTKEY integer,PS_SUPPKEY integer,PS_AVAILQTY integer,PS_SUPPLYCOST Decimal,PS_COMMENT varchar199,PRIMARY KEYPS_PARTKEY,PS_SUPPKEY,/*使用表级约束定义复合主键*/FOREIGN KEYPS_PARTKEY REFERENCESPARTp_partkey,/*使用表级约束定义参照完整性约束*/FOREIGN KEYPS_SUPPKEY REFERENCESSUPPLIER S_SUPPKEY;例3一一

1.仓II建part表CREATE TABLEpartp_partkey integer NOT NULL,pname varchar22NOT NULL,1pmfgr varchar6NOT NULL,p categoryvarchar7NOT NULL,1p brand1varchar9NOT NULL,:pcolor varchar11NOT NULL,P_type varchar25NOT NULL,FROM sales_fact_1997F,product P,product_class PCWHERE F.product_id=P.product_id ANDP.product_class_id=PC.product_class_id ANDproduct category=Seafood;例31SELECT COUNT*FROM sales_fact_1997_1997,customer WHERE sales fact

19971997.customer id=customer.customer id AND gender=F ANDstore_salesSELECT

1.5MVG store sales FROM sales_fact_1997_1997WHERE sales_fact_1997_

1997.customer id=customer,customer id;SELECT COUNT*FROM salesfact1997,customer WHERE sales_fact_

1997.customer_id=customer.customer_id ANDstore_salesSELECT

1.5*AVGstoresales FROM sales_fact_1997WHERE sales_fact_

1997.customer!d=customer.customer_id AND gender=F;SELECT COUNT*FROM sales_fact_1997WHERE store_salesSELECT

1.5*AVGstore_sales FROM sales_fact_1997,customer WHERE sales_fact_

1997.customer_id=customer.customer_id AND gender=F;SELECT COUNT*FROM sales_fact_1997,customer WHERE sales_fact_

1997.customer_id=customer.customer_id，AND gender=F ANDstore_salesSELECT

1.5MVG store_sales FROM sales_fact_1997;例32SELECT COUNT*FROM sales_fact_1997WHERE store_salesANY SELECT store_sales FROM sales_fact_1997,customer=，，WHERE sales_fact_

1997.customer_id=customer.customer_id AND fname Jeanne;一SQL命令解析内层查询返回用户Jeanne的所有store_sales值，外层查询判断store_sales是否满足大于内层查询store_sales值中任意一个的条件，并对满足条件的记录进行计数SELECT COUNT*FROM sales_fact_1997WHERE store_salesSELECT MINstore_sales FROM sales_fact_1997,customer=，，WHERE sales_fact_

1997.customer_id=customer.customer_id ANDfname Jeanne;一SQL命令解析ANY等价于大于多值结果集中的最小值，上述两个SQL命令等价例33SELECT COUNT*FROM sales_fact_1997WHERE store_salcsALL SELECTstore_sales FROM sales_fact_1997,customer=，，WHERE sales_fact_

1997.customer_id=customer.customer id ANDfname Jeanne;--SQL命令解析内层查询返回用户Jeanne的所有store_sales值，外层查询判断store_sales是否满足大于内层查询store_sales全部值的条件，并对满足条件的记录进行计数SELECT COUNT*FROM sales_fact_1997WHERE store_salesSELECT MAXstore sales FROM sales_fact_1997,customer=，，WHEREsales_fact_

1997.customer_id=customer.customer_id ANDfnameJeanne;一SQL命令解析〉ALL等价于大于多值结果集中的最大值，上述两个SQL命令等价例34SELECT COUNT*FROM customer;一SQL命令解析统计客户总数SELECT COUNT*FROM customer WHERE EXISTS SELECT*FROM sales_fact_1997WHEREsales_fact_

1997.customer!d=customer.customer id;--SQL命令解析判断外层查询的每一条记录的customejid值是否在内层查询中存在满足sales_fact_

1997.customer_id=customer.customejid条件的记录，如果存在则外层查询进行计数SELECT COUNT*FROM customer WHERE NOTEXISTSSELECT*FROMsales_fact_1997WHEREsales_fact_

1997.customer_id=customer.customer id;--SQL命令解析判断外层查询的每一条记录的customer」d值是否在内存查询中不存在满足sales_fact_

1997.customer_id=customer.customejid条件的记录，如果不存在则外层查询进行计数后两个查询分别统计了存在和不存在销售记录的customer数量，统计结果之和与customer表中记录总数相等例35=，，SELECT customer_id FROM customer WHERE gender F AND total_children3UNION SELECT customer_id FROM customer WHEREmember_card=Silver AND num cars_owned=1;一SQL命令解析将UNION连接的两个子查询的结果集合并，两个结果集中的重复元组自动去掉SELECT customer_id FROM customer WHEREgender=F AND total children3UNION ALLSELECT customer_id FROM customer WHEREmember_card=Silver ANDnum_cars_owned=1;一SQL命令解析将UNION连接的两个子查询的结果集合并，保留两个结果集中重复的元组SELECT customer_id FROM customer WHEREgender=F AND total_children3OR member_card=SiIver ANDnum_cars_owned=1;一SQL命令解析将两个子查询转换为用OR连接的复合谓词，查询结果集小于UNION ALL查询的元组数量，与UNION查询结果集相同例36SELECT customer_id FROM customer WHEREgender=F AMtotal_children3INTERSECT=，，SELECT customcr_id FROM customer WHEREmembcr_card Silver ANDnum_cars_owncd=1;一SQL命令解析3个以上孩子的女性子查询结果集与有一辆车的银牌会员子查询的结果集执行交集运算，生成满足两个集合条件的查询结果集一交集子查询等价于子查询条件的与运算=，，SELECT customerid FROM customer WHEREgender F AND total children3AND member card=Silver ANDnum cars owned=1;例37SELECT customer_id FROM customer WHEREgender=FANDtotal children3INTERSECT SELECT customer_id FROM customer WHEREmember_card=Silver ANDnum_cars_owned=1;一SQL命令解析•3个以上孩子的女性子查询结果集与有一辆车的银牌会员子查询的结果集执行交集运算，生成满足两个集合条件的查询结果集一交集子查询等价于子查询条件的与运算=，，SELECT customer_id FROM customer WHEREgenderFANDtotal_children3AND membercard=Silver ANDnum carsowned=1;例38SELECT customer id FROM customer WHEREmembercard=SilverANDnum carsowned=1UNION SELECT customer_id FROMsales_fact_1997WHERE store_cost

0.2;一SQL命令解析集合操作连接的子查询可以使用相同的表，也可以使用不同的表，需要满足查询结果集具有相同的结构和语义例39SELECT Iname,C_sales.SUM_sales FROM customer C,SELECT customerid,SUMstore_sales FROMsales_fact_1997GROUP BYcustomer_id HAVINGAVGstore_sales7AS Casalescustomer_id,SUM_sales WHEREC.customer_id=C_sales.customer_id;WITH C_salescustomer_id,SUM_sales ASSELECT customer_id,SUMstore_sales FROMsales_fact_1997GROUP BYcustomer_id HAVINGAVGstore_sales7SELECT Iname,C_sales.SUM_sales FROM customer C,C_sales WHEREC.customer_id=C_sales.customer_id;例40INSERT INTOcustomercustomer_id,Inamc,fname,city，VALUES10282,JACK,ROSE NewYork;例41CREATE TABLEcust_test cityVARCHAR50,cust countINT;INSERT INTOcust_test SELECT city,COUNT*FROM customerGROUP BY city ORDER BY city;例42SELECT customer_id,Iname,city INTOcusttestl FROM customer;例43SELECT city,COUNT*AS cust_count INTOcust_test2FROM customerGROUP BYcity ORDER BYcity;例44UPDATE customerSET gender=M WHERE customer_id=10282;例45UPDATE customerSET membercard=Silver=，，=，，WHERE yearly_income$70K-$90K ANDnum_children_at_home2AND member_card Bronze;例46UPDATE customerSET member_card=Golden FROMSELECT customer_id,SUMstore_sales ASsum custFROMsales_fact_1997GROUP BYcustomerid HAVINGSUMstore_sales800AS cust_sum WHERE customer.customer_id=cust_sum.customer_id;例47ALTER TABLEtime_by_day ADDholiday_flag char1;一SQL命令解析修改日期表time_by_day,增加一个日期标识列，由‘T或F标识是否假日=，，UPDATE time_by_day SETholiday_flag F;一SQL命令解析将holiday_flag初始化为F UPDATE time_by_day SETholiday_flag=T WHEREthe_date TN1997-01-01,1997-05-01,1997-06-01,1997T0-01;一SQL命令解析:将1997年的1月1日,5月1日，6月1日，10月1日记录的holiday_flag标识为T可以按日期逐年标识假日，也可以对各年假日按月一日批量修改=，UPDATE time_by_day SETholiday_flag TWHERE month_of_^ear=10AND day_of_month=l;一SQL命令解析将日期表中10月1日全部标识为假日=，UPDATEtime_by_day SETholiday_flag WHEREmonth of_year=5AND day_of_month=l;一SQL命令解析将日期表中5月1日全部标识为假日例48DELETE FROMcust_test;例49DELETE FROM customer WHEREcustomer id=10282;例50DELETE FROMsales_fact_1997FROM customer ct INNER JOIN sales_fact_1997sf ONct.customer_id=sf.customer_id WHERE=，ct.state_province CA;DELETE FROMsales_fact_1997WHEREcustomer id INSELECT customerid FROM customer=，WHERE state_province CA;例51CREATE VIEWcust_sales customer_id,fullname,gender,education,store_sales,store_units,store_cost ASSELECT C.customer_id,C.fullname,C.gender,C.education,F.store_sales,F.unit_sales,F.store_cost FROM customer C,sales_fact_1997F WHEREC.customer_id=F.customer_id;例52CREATE VIEWgender_salesgender,sum_sales,sum units,sum cost ASSELECT gender,SUMstore_sales,SUMunit_sales,SUMstore_cost FROM customer C,sales_fact_1997F WHEREC.customer_id=F.customer_id GROUP BY gender;CREATE VIEWgender_sales ASSELECT gender,SUMstore_sales ASsumsales,SUMunit_sales ASsum units,SUMstore_costASsum costFROM customer C,sales_fact_1997F WHEREC.customer_id=F.customer_id GROUP BY gender;例53DROP VIEWgcndcr_sales;例54CREATE VIEWSSB_tables ASSELECT customer.*,part.*,supplier.*,date.*,lineorder.*FROM customer,part,supplier,date,lineorder WHERElo_custkey=c_custkey ANDlo_partkcy=p_partkey ANDlosuppkey=ssuppkey ANDlo_orderdate=d_datekey;SELECT d_year,c_nation,SUM CAST lo_revenue-lo_supplycost ASbigint as profit FROMdate,customer,supplier,part,lineorder WHERElocustkey=ccustkey ANDlosuppkey=ssuppkey ANDlopartkey=ppartkey ANDlo_orderdate=ddatekey ANDc region=AMERICA ANDs_region=AMERICA ANDp_mfgr=MFGR#1OR p_mfgr=MFGR#2GROUP BY dyear,c_nation ORDER BYd_year,c_nation;一SQL命令改写为基于视图的单表查询SELECT d year,c_nation,SUMCASTlo_revenue-lo_supplycost ASbigint as profit FROMSSB_tables WHEREc_region=AMERICA ANDs_region=AMERICA ANDp_mfgr=MFGR#1OR p_mfgr=MFGR#2GROUP BY dyear,conation ORDER BYdyear,conation;例55SELECT LENInameFROM customer;一SQL命令解析查询customer表Iname列的字符串长度SELECT MAXLENIname,MINLENIname,AVGLENIname FROM customer;一SQL命令解析查看customer表Iname列的最大长度、最小长度和平均长度，SELECT CHARINDEX sh,Washington;一返回3，SELECT LEFT李小明，1；一返回‘李’SELECT RIGHT李小明，2;一返回‘小明’，SELECT SUBSTRINGC$30K-$50K,8,4;一返回$50K例56，SELECT LEFTaddress1,CIIARINDEX address-1AS addressNo,addressl FROM customer;例57SELECT DATEDIFFYEAR,10-01-1949,10-01-2015;一返回66SELECT DATEDIFFMONTH,10-01-1949,10-01-2015;一返回792J,SELECT DATEDIFF DAY,10-01-1949,10-01-2015;一返回24106SELECT birthdate,YEARGETDATE-YEARbirthdate AS AGEO,DATEDIFFYEAR,birthdate,GETDATEAS AGE1,DATEDIFFDAY,birthdate,GETDATE/365ASAGE2FROM customer;例58SELECT SUMCASTlo_revenue ASbigint FROM lineorder;例59SELECT AVGCASTlo revenue ASbigint FROM lineorder;一SQL命令解析lojevenue为bigint类型，AVG结果也为bigint类型SELECT CASTAVGCASTlo_revenue ASreal ASdecimal10,2FROM lineorder;一SQL命令解析使用CAST函数将AVG函数计算结果转换为带有两位小数位的decimal10,2类型例60SELECT CONVERTnumeric15,2,AVGCONVERTreal,lo_extendedprice*lo_quantity as revenue FROM lineorder;例61SELECT lo extendedprice,lo quantity,lo discount,1-CONVERTdecimal4,2,lo discount/100as discountFROMlineorder;SELECT CONVERTnumeric18,2,SUMlo_extendedprice*lo_quantity^1-CONVERTdecimal4,2,lo_discount/100FROMlineorder;例62SELECT customer_id,Iname,ROW_NUMBEROVER ORDER BY Iname AS RowNum FROM customer ORDER BY Iname;SELECT customer_id,Iname,RANK OVER ORDER BY Iname AS RowNum FROM customer ORDERBYIname;SELECT customer id,Iname，DENSE_RANK OVERORDERBYIname ASRowNumFROM customerORDERBYIname;SELECT customer id,SUMstoresales ASsales FROMsales_fact_1997GROUP BYcustomerid ORDERBY sales;SELECT customer_id,SUMstoresales ASsales,RANKOVERORDERBY sales ASRowNumFROMsales_fact_1997GROUP BYcustomerid ORDERBY sales;CREATE VIEWrank salescustomer id,sales ASSELECT customer_id,SUMstore_sales ASsales FROMsales_fact_1997GROUP BYcustomer_id;SELECTcustomer_id,sales,RANKO OVERORDERBYsales ASrownum FROMrank salesORDERBYsales;例63SELECT AVGstore_sales ASavg_sales,STDEVstore_sales ASstedv sales,STDEVPstore_sales ASstdevpsales,VARstore_sales ASvar_sales,VARPstore_salesASvarp sales FROMsales_fact_1997;例64SELECT gender,marital status,houseowner,SUMtotal_chiIdren Numchildren FROM customerC,sales_fact_1997F WHEREC.customer_id=F.customer_id GROUP BY gender,marital status,houseowner;SELECT gender,marital status,houseowner,SUMtotalchiIdren NumchiIdren FROM customerC,sales_fact_1997F WHEREC.customer!d=F.customerid GROUP BYROLLUP gender,marital_status,houseowner;SELECT gender,marital status,houseowner,SUMtotal_chiIdren NumchiIdren FROM customerC,sales_fact_1997F WHEREC.customer_id=F.customer_idGROUP BY CUBEgender,marital_status,houseowner;案例实践SSB13个测试查询—QI.1select sumCONVERTreal,1o_extendedprice*lo_quantity as revenue from lineorder,date whereloorderdate=ddatekey and dyear=1993and lo„discount between1and3and loquantity25;—QI.2select sumCONVERTreal,lo_extendedprice*lo_quantity as revenue from lineorder,date whereloorderdate=ddatekey，and d_yearmonth=Janl994and lodiscountbetween4and6and IO—quantity between26and35;—QI.3select sumCONVERTreal,lo extendedprice*lo quantityasrevenue from lineorder,date wherelo orderdate=d datekeyand dweeknuminyear=6and dyear=1994and lodiscountbetween5and7and loquantitybetween26and35;—Q

2.1select sumlo_revenue,d year,p brandlfrom lineorder,date,part,supplier whereloorderdate=ddatekey and lopartkey=ppartkey and losuppkey=ssuppkey and p category=MFGR#12and sregion=AMERICA group by dyear,pbrandl order by dyear,pbrandl;—-Q

2.2select sumlo revenue,d year,p brandlfromlineorder,date,part,supplier whereloorderdate=ddatekey and lopartkey=p_partkey andlo_suppkey=s_suppkey and pbrandl betweenMFGR#2221and MFGR#2228and s_region=ASIA group by dyear,pbrandl orderby dyear,pbrandl;一Q

2.3select sumlo_revenue,d year,p brandlfromlineorder,date,part,supplier wherelo_orderdate=ddatekey andIo.partkey=p_partkey andlo_suppkey=s_suppkey andp_brandl=MFGR#2239and s_region，=EUROPE group by dyear,pbrandl orderby d_year,p brandl;—Q

3.1select cnation,s_nation,dyear,sumCONVERTbigint,lo revenue asrevenue from customer,1ineorder,supplier,date wherelo custkey=c custkey and losuppkey=ssuppkey andloorderdate=ddatekey p_size integer NOT NULL,p_container varchar10NOT NULL,PRIMARY KEYp_partkey；—

2.创建supplier表CREATE TABLEsupplierssuppkey integer NOT NULL,s_name varchar25NOT NULL,s_address varchar25NOT NULL,s_city varchar10NOT NULL,s nation varchar15NOT NULL,sregion varchar12NOT NULL,sphone varchar15NOT NULL,PRIMARY KEYs_suppkey；一一

3.仓U建customer表CREATE TABLEcustomerccustkey integer NOT NULL,c_namevarchar25NOT NULL,c_address varchar25NOT NULL,c_city varchar10NOT NULL,c_nationvarchar15NOT NULL,c_region varchar12NOT NULL,cphone varchar15NOT NULL,c_mktsegment varchar10NOT NULL,PRIMARY KEYc_custkey；—

4.创建date表CREATETABLEdated datekeyinteger NOT NULL,d datevarchar19NOT NULL,1ddayofweek varchar10NOT NULL,d_month varchar10NOT NULL,dyear integer NOT NULL,d yearmonthnuminteger NOT NULL,d yearmonthvarchar8NOT NULL,d daynuminweekintegerNOT NULL,ddaynuminmonth integerNOT NULL,ddaynuminyear integerNOT NULL,dmonthnuminyear integerNOT NULL,d weeknuminyearintegerNOT NULL,d sellingseasonvarchar13NOT NULL,d lastdayinweekflvarchar1NOT NULL,dlastdayinmonthfl varchar1NOT NULL,，and c_region=ASIA and s_region=ASIA and dyear=1992and dyear=1997group byc_nation,s_nation,dyear orderby d_year asc,revenue desc;一Q

3.2select ccity,s city,d year,sumCONVERTbigint,lo revenue asrevenuefrom customer,lineorder,supplier,date wherelo custkey=c custkey andlo_suppkey=s_suppkey andloorderdate=ddatekey and，，c_nation=UNITED STATES and s_nation二J UNITED STATESand d year=1992and dyear=1997group byc_city,s_city,dyear orderby dyear asc,revenue desc;—-Q

3.3select c_city,s_city,d_year,sumCONVERTbigint,lo_revenueasrevenuefromcustomer,lineorder,supplier,date wherelocustkey=c_custkey andlo_suppkey=s_suppkey andlo_orderdate=d datekeyand,,c_city=UNITED Klfor c_city=UNITED KI5and s_city=UNITED KITor s_city=UNITED KI5and dyear=1992andd_year=1997group byc_city,s_city,dyear orderby d_yearasc,revenue desc;—Q

3.4select c_city,s_city,d_year,sumCONVERTbigint,lo_revenueasrevenuefromcustomer,lineorder,supplier,date wherelocustkeyc_custkey andlosuppkey=ssuppkey andlo_orderdate=d datekeyand c_city=UNITED KHor c_city=UNITED KI5and s_city=UNITED KH,or s_city=UNITED KI5andd_yearmonth=Decl997group byccity,s_city,d_year orderby dyearasc,revenue desc;—Q

4.1select dyear,c nation,sumCONVERTbigint,lorevenue-lo supplycost asprofit from date,customer,supplier,part,lineorder wherelo_custkey=c_custkeyandlo suppkey=s suppkey andlo_partkey=p_partkey andloorderdate=ddatekey and c_region=AMERICA ands_region=AMERICA andp mfgr=MFGR#1or p_mfgr=MFGR#2groupby dyear,conation orderby dyear,conation;—Q

4.2select d_year,sanation,p_category,sumCONVERTbigint,lo_revenue-lo_supplycostasprofitfrom date,customer,supplier,part,lineorder wherelo_custkey=c_custkeyandlo_suppkey=s_suppkeyandlo partkey=p_partkey andloorderdate=d datekeyandc_region=AMERICA ands_region=AMERICA andd_year=1997or d_year=1998andp mfgr=MFGR#1or p_mfgr=MFGR#2groupbydyear,s_nation,p_category orderbydyear,s_nation,p_category;—Q

4.3select dyear,s_city,pbrandl,sumCONVERTbigint,lorevenue-lo supplycostasprofitfromdate,customer,supplier,part,lineorder wherelo custkey=c custkeyandlosuppkey=ssuppkey andlopartkey=ppartkey andloorderdate二d datekey，ands_nation=UNITEDSTATESandd_year=1997or dyear=1998andp_category=MFGR#14groupbydyear,s_city,p brandlorderbydyear,s_city,p brandl;d holidayflvarchar1NOT NULL,dweckdayf1varchar1NOT NULL,PRIMARY KEYd_datekey；一一

5.仓1J建lineorder表CREATETABLElineorderPRIMARY KEY lo orderkey,lo linenumber,FOREIGN KEYlo_custkey REFERENCEScustomerc_custkey,lo FOREIGNKEY partkeyREFERENCES partppartkey,lo_suppkey FOREIGNKEY REFERENCESsuppliers_suppkey,loorderkey integerNOT NULL,lolinenumber integerNOT NULL,lo_custkey integerNOT NULL,lo_part keyintegerNOT NULL,lo suppkeyintegerNOT NULL,lo_orderdate integerNOT NULL,loorderpriority varchar15NOT NULL,lo_shippriority varchar1NOT NULL,lo_quantity integerNOT NULL,loextendedpriceintegerNOT NULL,lo_ordertotalprice integerNOT NULL,lodiscount integerNOTNULL,lorevenue integerNOTNULL,lo_supplycost integerNOTNULL,lo_tax integerNOTNULL,lo_commitdate integerNOTNULL,loshipmode varchar10NOTNULL,FOREIGN KEYlo_orderdate REFERENCESdate d_datekey；例4ALTER TABLElineorder ADDlo_surrkey int;一SQL命令解析增加一个int类型的列lo_surrkeyALTER TABLElineorder ALTERCOLUMN lo_quantity SMALLINT;一SQL命令解析将lo_quantity列的数据类型修改为SMALLINTALTER TABLElineorder ALTERCOLUMN lo_orderpriority varchar15NULL;一SQL命令解析将lo_orderpriority列的NOTNULL约束修改为NULL约束ALTER TABLElincordcr DROPCOLUMN lo_shippriority;一SQL命令解析删除表中的列1o_sh ippr ior ityALTER TABLElineorder ADDCONSTRAINT FK_S FOREIGNKEYlo_suppkey REFERENCESsupp tiers_suppkey;一SQL命令解析在lineorder表中增加一个外键约束CONSTRAINT关键字定义约束的名称FK_S,然后定义表级参照完整性约束条件例5注因为参照完整性约束关系不能直接删除DROP TABLEpart;例6CREATE UNIQUEINDEXs_name_Tnx ON suppliers_name;CREATE INDEXs nc Inx ONsuppliers nation ASC,s cityDESC;例7DROP INDEXsupplier,snc lnx;DROP INDEXs_name_InxONsupplier;例8注需要先按

13.1节内容导入数据，然后执行查询SELECT*FROMpart;SELECT ppartkey,p name,pmfgr,p_category,p brand1,pcolor,p_type,p_size,pcontainer FROMpart;例9SELECT p name,p brand1,p containerFROMpart;例10SELECT Iname,fname,birthdate FROM customer;一SQL查询解析输出表中原始的列信息，其中姓名分别存储为Iname和fname两列，存储有出生日期信息但没有年龄信息，SELECT fname++Iname,YEAR birthdateFROM customer;—SQL查询解析将字符型的Iname和fname列进行字符串连接操作，输出以空格间隔的姓名格式的表达式，并通过日期函数YEAR计算出用户出生年份由于输出的是表达式派生列，因此输出默认的列名为空，SELECT fname++Iname ASfullname,2015-YEARbirthdate ASage FROM customer;一SQL查询解析在列表达式后通过AS命令增加一个列别名，标识表达式派生列，将通过构造日期表达式计算出用户在2015年时的年龄，J SELECTfname++InameASfullname,2015-YEARbirthdate ASage,2015as updatetimeFROM customer;一SQL查询解析增加一个常量输出列，标识当前年份2015例11SELECTs_region FROMsupplier;一SQL命令解析输出s_region列中全部的取值，包括了重复的取值SELECT DISTINCTs_region FROMsupplier;一SQL命令解析通过DISTINCT短语指定列s_region只输出不同取值的成员，列中的每个取值只输出一次例12SELECT total_childrenFROM customerWHERE total_children2;一SQL命令解析输出customer表中｛0101_±11亚©11〉2的记录的101;81_±11€1「©列，SELECT InameFROM customerWHERE lnameV;--SQL命令解析输出customer表中lnamet的记录的Iname列Lname是字符型属性，表达式以字典序为标准对字符型数据进行比较SELECT birthdateFROM customerWHERE birthdate1950-01-01;一SQL命令解析输出customer表中birthdate1950-

01.-01的记录的birthdate列Birthdate为datetime数据类型，表达式中的日期常量需要满足数据库日期数据类型的格式，在SQL server中的日期常量表示为‘1950-01-01SELECT birthdateFROM customerWHERE2015-YEARbirthdate40;一SQL命令解析输出customer表中年龄超过40岁的用户的birthdate用户年龄通过表达式2015-YEAR birthdate计算得到SELECT total_chiIdren,num_cars_owned FROM customerWHERE total_childrennum_cars_owned;一SQL命令解析输出customer表中孩子数量超过汽车数量的记录total_children num_car s_own ed是两个列表达式之间的比较操作例13SELECT*FROM customerWHERE total_children BETWEEN2AND4;一SQL命令解析输出customer表中total_children数量介于2和4之间的记录SELECT*FROM customerWHEREtotal_children=2ANDtotal_children=4;一SQL命令解析total_children=2ANDtotal_children=4等价于BETWEEN2AND40SELECT*FROM customerWHERE InameBETWEENC ANDH;一SQL命令解析输出customer表中Iname介于‘C‘和H之间的记录，支持字符型数据上的范围操作，SELECT*FROMcustomerWHERE birthdateBETWEEN1970-01-0T AND1980-01-01;--SQL命令解析:输出customer表中birthdate介于1970-01-01和1980-01-01之间的记录，支持日期型数据上的范围操作例14SELECT*FROMcustomerWHEREtotal_children IN1,4;一SQL命令解析输出total_children数量为1和4的记录SELECT*FROMcustomerWHERE InameIN ChinHili;--SQL命令解析:输出Iname为，Chin和HilT的记录SELECT*FROMcustomerWHERE birthdateIN1944-10-25,1956-04-26,1970-09-21;一SQL命令解析:输出birthdate为1944-10-

25、1956-04-26和1970-09-21的记录例15SELECT*FROMcustomerWHERE InameLIKEC%n;一SQL命令解析输出customer表中Iname中以C开头，最后一个字母为n的记录SELECT*FROMcustomerWHERE InameLIKE%i%n%;--SQL命令解析输出customer表中Iname中包含字母i和n的记录且字母i在字母n之前SELECT*FROMcustomerWHERE InameLIKEPa_’;一SQL命令解析输出customer表中Iname中以Pa开头，最后一个字母为任意字符的记录SELECT*FROMcustomerWHERE InameLIKE_h%n_;一SQL命令解析输出customer表中Iname中第二个字母为h,其后包含倒数第二个字母为n的记录J，SELECT*FROMcustomerWHERE InameLIKE Chow\_Wang ESCAPE;--SQL命令解析输出customer表中Iname中取值为ChowJVang的记录，其中—为普通字符，不是通配符，由‘\表示其后的―为普通字符例16SELECT*FROMcustomerWHERE miIS NULL;--SQL命令解析输出customer表中mi列为空值为记录SELECT*FROMcustomerWHERE miIS NOTNULL;一SQL命令解析输出customer表中mi列不是空值为记录例17SELECT*FROMcustomerWHERE miTS NULLANDgender=F ORtotal_children2;一SQL命令解析输出customer表中mi列为空值并且性别为女的客户记录或者孩子数量大于2个的客户记录，SELECT*FROMcustomerWHERE miIS NULLANDgender^F ORtotal_children2;一SQL命令解析输出customer表中mi列为空值并且至少满足性别为女或者孩子数量大于2两个条件之一的客户记录例18SELECT COUNT*FROMcustomer;--SQL命令分析统计customer表中记录的总数SELECT COUNTmi FROMcustomer;一SQL命令分析统计customer表中mi的元组数当COUNT函数指定列时，列中的空值不进行计数，只统计非空值的记录数量而不是COUNT*所对应的表中记录数量SELECT COUNTDISTINCT mi FROMcustomer;--SQL命令分析统计customer表中mi列中不重复值的个数SELECT COUNTDISTINCTmiFROMcustomerWHERE;一SQL命令分析统计customer表中非空和非空白字符’’，长度为0的字符的记录数量SELECT SUMtotal_chiIdren FROMcustomer;--SQL命令分析统计customer表中total chiIdren的总和SELECT AVGtotal_chiIdren FROMcustomer;--SQL命令分析统计customer表中tota]_chi Idren的平均值SELECT MAXtotal_chiIdrenFROMcustomer;一SQL命令分析统计customer表中total_chiIdren的最大值SELECT MINtotal_chiIdrenFROMcustomer;一SQL命令分析统计customer表中total_chiIdren的最小值SELECT AVG2015-YEARbirthdate FROMcustomer;一SQL命令分析统计customer表中通过birthdate列计算出的平均年龄SELECT COUNT*AS amountFROMcustomer;一SQL命令解析统计customer表中记录的总数SELECTgender,COUNT*AS amountFROMcustomerGROUP BYgender;一SQL命令解析按性别gender列分组统计customer表中客户记录的数量SELECT education,gender,COUNT*AS amountFROMcustomerGROUP BY education,gender ORDERBY education,gender;一SQL命令解析按教育程度educat ion和性别gender列分组统计customer表中客户记录的数量例20SELECT education,gender,COUNT*AS amountFROMcustomerGROUP BYeducation,gender HAVINGCOUNTW1000ORDERBYeducation,gender;一SQL命令解析HAVING短语中的COUNT*1000作为分组聚集计算结果的过滤条件，对分组聚集结果进行筛选SELECT education,gender,COUNT*AS amount,AVGtotal_children ASavg childrenFROMcustomerGROUPBYeducation,gender HAVING sum num carsowned2000ORDERBYeducation,gender;一SQL命令解析HAVING短语中可以使用输出目标列中没有的聚集函数表达式如HAVINGsum numcars_owned2000短语中sumnumcars owncd2000并不是查询输出的聚集函数表达式,只用于对分组聚集计算结果进行筛选例21SELECT education,gender,COUNT*AS amountFROMcustomerGROUPBYeducation,gender ORDERBYeducationASC,gender DESC;一SQL命令解析对查询结果按分组属性排序，第一排序属性为education升序，第二排序属性为gender降序SELECT education,gender,COUNT*AS amountFROMcustomerGROUPBYeducation,gender ORDERBY COUNT*;一SQL命令解析对分组聚集结果按聚集计算结果升序排列SELECT education,gender,COUNT*AS amountFROMcustomerGROUPBYeducation,gender ORDERBY amount;一SQL命令解析当聚集表达式设置别名时，可以使用别名作为排序属性名，指代聚集表达式SELECTcustomer_id,store_sales INTOsales_simple FROMsales_fact_1997;SELECT customcr_id,Iname,fnamc,gender,education INTOcustomer_simple FROMcustomer;SELECT F.*,C.*FROMsales_simple F,customer_simple CWHERE F.customerid=C.customerid;SELECT F.*,C.*FROMsales_simple FINNER JOIN customer_simple CON F.customer_id=C.customer]d;例23SELECT F・customer_id,F.store_sales,C.Iname,C.fname,C.gender,C.education FROMsales_simple F,customer_simple CWHERE F.customer_id=C.customer_id;例24SELECT F.customerid,F.store_sales,C.Iname,C.fname,C.gender,C.education FROMsales_simple F,customer_simple CWHERE F.customer」d二C.customer_id ANDF.store_sales BETWEEN4AND8，AND C.education inC BachelorsDegree,Graduate Degree;例25SELECT C.education,AVGF.store_sales FROMsales_simple F,customer_simple CWHERE F.customer_id=C.customer_id，AND C.gender』F GROUPBY C.education;例26SELECT COUNT*FROMcustomer cl,customer c2WHERE cl.Iname=c

2.fname;SELECT cl.customer_id,cl.Iname,cl.fname,c

2.customer_id,c

2.Iname,c

2.fname FROMcustomer cl,customerc2WHERE cl.Iname=c

2.fname;例27SELECT F.*,C.*FROMsales_simple FLEFT OUTERJOIN customer_simple CON F.customer_id=C.customerid;SELECT F.*,C.*FROMsalessimple FRIGHT OUTERJOINcustomersimple CON F.customer_id=C.customer_id;例28注前两个查询使用SSB数据库，后一个查询使用Foodmart数据库，注意sales_fact」997表名称与自己的数据库名称一致SELECT name,p name,s^name,d date,lo quantityFROMcustomer,part,supplier,date,lineorder WHERElo_custkey=c_custkey ANDlo_partkey=p_partkey ANDlo_suppkey=s_suppkey ANDlo orderdate=d datekey;SELECTc_name,pname,s_name,d date,lo quantityFROMlineorderINNERJOINcustomer ON lo_custkey=c_custkey INNERJOIN partON lo_partkey=p_partkey INNERJOIN supplierON lo_suppkey=s_suppkey INNERJOIN dateONloorderdate=d datekey;SELECT r.sales_region,s.store_name,t.the date,pr.media type,c.fname,p.product_name,pc.product_category,sf.store_sales FROMregion r,stores,time_by_day t,promotion pr,sales_fact_1997sf,customerc,product p,product_class pcWHEREs.region_id=r.region_id ANDsf.store_id=s.store_id ANDsf.time_id=t.time_id ANDsf.promotion_id=pr.promotion_id ANDsf.customer_id=c.customer_id ANDsf.product_id=p.product_idANDp.product_class_id=pc.product_class_id;例29SELECT SUMstore_sales FROMsales_fact_1997WHEREcustomer_id INSELECTcustomer_id FROMcustomerWHEREgender=F;SELECT SUMstore_sales FROMsales_fact_1997F,customerCWHERE F.customer_id=C.customer_idANDC.gender=F;例30SELECT SUMstore_salesFROMsales_fact_1997WHERE product_id INSELECT product]dFROMproduct WHERE product_class_id INSELECT product_class_idFROMproduct/lass=，，WHEREproduct_category Seafood;SELECT SUMstoresales。