第21章SQL从多个表中检索数据清华大学经管学院朱世武本章内容：使用连接从多个表中选取数据；使用子查询通过表与表之间的联系选取数据；使用SET算符合并查询结果。如果没有另外说明，本章的PROCSQL语对表和视图都适用。使用连接语句JOIN从多个表中检索数据最基本的连接就是两个表的简单连接。例21.1表china与表usa的简单连接程序：procsql;select*fromresdat.china,resdat.usa;quit;levelchinalevelusa---------------------------------------------0c011u000c012u010c012u020c013u030c014u041c021u001c022u011c022u021c023u031c024u042c031u002c032u012c032u022c033u032c034u043c041u003c042u013c042u023c043u033c044u04JOIN方式分类通常连接Join方式分为两种类型：内部join，只返回连接表中匹配连接条件的行。外部join，是内部连接的补充，还包括除内部连接部分以外不符合连接条件的行。外部连接分三种：left左连接,right右连接andfull完全连接。内部连接数据格式Procsql;Selectobject-itemfromtable-name<
alias>,table-name<alias>Wheresql-expression;例21.2只对相同水平的运动员进行连接。procsql;select*fromresdat.china,resdat.usawherechina.level=usa.level;quit;levelchinalevelusa-----------------------------------------------1c021u002c032u012c032u023c043u03使用表的别名通常的查询时会遇到两个表有相同名字的列，为了在引用时不产生混淆，需要在列名前加上表名或者表的别名。语句格式:Fromtable-nametable-alias,procsql;select*fromresdat.chinaasa,resdat.usaasbwherea.level=b.level;quit;使用关键词INNERJOIN的内部连接语句格式：Fromtable-nameJOINtable-nameONsql-expressionprocsql;select*fromresdat.chinaainnerjoinresdat.chinabona.level=b.level;orderbylevel;quit;关键词INNERJOIN用于连接多个表的数据。关键词inner是可选的，语句中用ON代替了原来设定匹配条件的where语句。从多于两个表的数据集中查询数据例21.6简单的多表连接。procsqloutobs=3;selecta.stkcd,b.lstknm,c.clprfromresdat.sampstka,resdat.lstkinfob,resdat.qttndistcwherea.stkcd=b.stkcdandb.stkcd=c.stkcdanda.stkcd=c.stkcd;quit;股票代码|Sto最新股票名称|收盘价|CloseckCodeLatestStockNamePrice-----------------------------------------------------------------------000002万科A15.52000002万科A15.57000002万科A15.48不同形式的外部连接语句格式Fromtable-nameLEFTJOIN|RIGHTJOIN|FULLJOIN左外部连接procsql;select*fromresdat.chinaaleftjoinresdat.usabona.level=b.level;quit;levelchinalevelusa--------------------------------------------------0c01.1c021u002c032u022c032u013c043u03结果比以前的内部连接多了一行，该行就是Tablechina与Tableusa不匹配的行，不匹配行中Tableusa的列为缺失值。右外部连接procsql;select*fromresdat.chinaarightjoinresdat.usabona.level=b.level;quit;levelchinalevelusa-------------------------------------------1c021u002c032u022c032u013c043u03.4u04结果比以前的内部连接多了一行，该行就是Tableusa与Tablechina不匹配的行，不匹配行中Tablechina的列都是缺失值。完全外部连接procsql;select*fromresdat.chinaafulljoinresdat.usabona.level=b.level;quit;levelchinalevelusa--------------------------------------------0c01.1c021u002c032u022c032u013c043u03.4u04结果显示，两个表中所有不匹配的行都出现在完全连接的输出结果中。MERGE语句和JOIN连接比较所有行匹配无重复值情况两个表中的by变量的值都相等且没有重复值的时候，可以使用一个内部连连接来产生同样的效果。例21.7BY变量值相等且没有重复值。TableaTablebcodemanagercodeAssitant145Max145Tracy150Jack150Yao155Paul155Chen程序如下：datamerge1;mergeab;bycode;run;procprintdata=merge1noobs;title'TableMERGE1';run;TableMERGE1codemanagerAssitant145MaxTracy150JackYao155PaulChenMerge在合并前的两个数据集已经按code排过序，而PROCSQL则不需要排序，下面程序给出和上面同样的结果。procsql;title'TableMERGE1';selecta.code,a.manager,b.Assitantfroma,bwherea.code=b.code;quit;有重复值情况当用来连接两个表的列变量或者BY组中有重复值时，Merge和Procsql的处理方式有所区别。例21.9BY组中有重复值。TablenewoneTablenewtwocodeManagercodeAssistant145Max145Jerry145Xam145Tracy155Paul155ChenData步datamerge3;mergeab;bycode;run;procprintdata=merge3noobs;title'TableMERGE3';run;TableMerge3codeManagerAssitant145MaxJerry145XamTracy155PaulChen若用SQL，则会出现下面的结果：Procsql;Title'TableMerge3';Selecta.code,a.manager,b.assistantFromafulljoinbOna.code=b.code;quit;TableMerge3codemanagerassistant------------------------------------145MaxJerry145MaxTracy145XamJerry145XamTracy155PaulChen使用子查询语句选择数据语言格式：(select..from..<(where/having).>);产生单个值的子查询例21.10考虑例21.9的两个表。Procsql;Title'WhichManagerhasthesamecodeasAssistantChen';Select*FromaWherecodeeq(selectcodefrombwhereassistant='Chen');Quit；WhichManagerhasthesamecodeasAssistantChencodemanager------------------155Paul产生多个值的子查询例21.11根据表sampstk中给定股票的股票代码，从表lstkinfo中选出相应的股票信息。Procsql;selectstkcd,lstknm,lstdtfromresdat.lstkinfowherestkcdin(selectstkcdfromresdat.sampstk);quit;最新股票名称|股票代码|StoLatestStock股票上市日|LckCodeNameistDate---------------------------------------------------------------000002万科A1991-01-29000007深达声A1992-04-13000011S深物业A1992-03-30混合子查询例21.12选出表resdat.yrret中所有A股2005年的年收益率。procsql;selectstkcd,yrretfromresdat.yrretawhere(selectstktypefromresdat.lstkinfobwherea.stkcd=b.stkcd)='A'and'1jan2005'd<=date<='31dec2005'd;quit;股票代码|StockCode年收益率|YearlyReturn-------------------------------------------------------------------------0000020.2576000007-0.04730000110.3737000016-0.3481600601-0.3711600604-0.1297600651-0.2040600653-0.4478合并两个或多个查询结果以下的SET算符例子都基于下面两个表。TableAxy------------------1one2two2twoThree与JOIN的横向连接不同，SET连接是竖直的连接。TableBxz------------------1one2two4four由多个查询产生非重复观测(UNION算符)procsql;title'AUNIONB';select*fromAunionselect*fromB;quit;AUNIONBxy------------------1one2two3three4four产生只属于第一个查询的观测(EXCEPT算符)procsql;title'AEXCEPTB';select*fromAexceptselect*fromB;quit;AEXCEPTBxy------------------3three从多个查询中产生公共部分(INTERSECT算符)procsql;title'AINTERSECTB';select*fromAintersectselect*fromB;AINTERSECTBxy------------------1one2two直接连接查询结果(OUTERUNION算符)procsql;title'AOUTERUNIONB';select*fromAouterunionselect*fromB;AOUTERUNIONBxyxz-------------------------------1one.2two.2two.3three..1one.2two.4four第22章SQL创建与更新表和视图清华大学经管学院朱世武Zhushw@sem.tsinghua.edu.cnResdat样本数据：www.resset.cnSAS论坛：www.resset.cn本章内容创建表更新表修改已存在的表删除表建立索引创建表时使用完整性约束创建视图创建表CreateTable语句创建一个表。常用语句如下：1）CREATETABLEtable-name(column-specification<,...column-specification|constraint-specification>);2）CREATETABLEtable-nameASquery-expression>;3）CREATETABLEtable-nameLIKEtable-name2;用列定义方式创建表语句格式：CREATETABLEtable-name(column-specification<,...column-specification|constraint-specification>);例22.1创建一个新的股票信息表。procsql;createtableNewstkinfo(stkcdchar(6),lstknmchar(20),lstdtnuminformat=date9./*规定输入和输出*/format=date9./*格式为DATE9.*/);quit;新的表newstkinfo有三列和零行。使用describeTable语句来检查这个新表，并从日志中查看列的信息：procsql;describetablenewstkinfo;日志信息：10procsql;11describetablenewstkinfo;NOTE:SQL表WORK.NEWSTKINFO已创建为类似:createtableWORK.NEWSTKINFO(bufsize=4096)(stkcdchar(6),lstknmchar(20),lstdtnumformat=DATE9.informat=DATE9.);12quit;NOTE:“PROCEDURESQL”所用时间（总处理时间）:实际时间0.06秒CPU时间0.00秒从查询结果创建表语句格式：CREATETABLEtable-nameASquery-expression例22.2用CreateTable语句从股票信息表创建的查询结果表。procsqloutobs=3;title'StockInformation';createtablestkinfoasselectstkcd,lstknm,lstdtfromResDat.lstkinfo;quit;例22.3用describeTable语句查看默认列的信息。procsql;describetablestkinfo;quit;stkinfo在DESCRIBETABLE语句中的SAS日志信息如下：35procsql;36describetablestkinfo;NOTE:SQL表WORK.STKINFO已创建为类似:createtableWORK.STKINFO(bufsize=4096)(Stkcdchar(6)format=$6.informat=$10.label='股票代码|StockCode',Lstknmchar(12)format=$12.informat=$12.label='最新股票名称|LatestStockName',Lstdtnumformat=YYMMDD10.label='股票上市日|ListDate');37quit;NOTE:“PROCEDURESQL”所用时间（总处理时间）:实际时间0.01秒CPU时间0.01秒使用DATASET选项语句在CREATETALBE语句中使用DATASET选项语句。实例如下：Procsql;CREATETABLEstkinfo4asselect*fromstkinfo2(drop=stkcd);即在from语句中的tablename和viewname后面可以使用DATASET选项语句。在表中插入行PROCSQL中使用INSERT语句将观测行插入表中。可以使用SET子句或者VALUES子句来设定行的信息。用SET子句插入观测语句格式：INSERTINTOtable-name|sas/access-view|proc-sql-view<(column<,...column>)>SETcolumn=sql-expression<,...column=sql-expression>>;例22.5用2个SET语句往空表stkinfo3中插入2个观测行。procsql;insertintostkinfo3setstkcd='000002',lstknm='万科A',lstdt='29jan1991'dsetstkcd='000004',lstknm='*ST国农',lstdt='14jun1991'd;selectstkcd,lstknm,lstdtfromstkinfo3;quit;输出窗口显示：最新股票名称|股票代码|StoLatestStock股票上市日|LckCodeNameistDate------------------------------------------------------------000002万科A1991-01-29000004*ST国农1991-06-14用VALUES子句插入行语句格式：INSERTINTOtable-name|sas/access-view|proc-sql-view<(column<,...column>)>VALUES(value<,...value>)<...VALUES(value<,...value>)>;例22.6用VALUES语句插入两个观测行。procsql;insertintostkinfo3values('000001','S深发展A',.)values('000005','','10dec1990'd);selectstkcd,lstknm,lstdtfromstkinfo3;quit;最新股票名称|股票代码|StoLatestStock股票上市日|LckCodeNameistDate------------------------------------------------------------000002万科A1991-01-29000004*ST国农1991-06-14000001S深发展A.0000051990-12-10在表中插入查询结果INSERTINTOtable-name|sas/access-view|proc-sql-view<(column<,...column>)>例22.7建立一个新表stkinfo4，然后把stkinfo3中日期不为缺失的观测插入到stkinfo4中。procsql;createtablestkinfo4likestkinfo3;procsql;insertintostkinfo4select*fromstkinfo3wherelstdtisnotmissing;selectstkcd,lstknm,lstdtfromstkinfo4;quit;最新股票名称股票代码|StoLatestStock股票上市日|LckCodeNameistDate------------------------------------------------------------000002万科A1991-01-29000004*ST国农1991-06-14000005990-12-10更新表中数据语句格式：PORCSQL;UPDATEtable-name|sas/access-view|proc-sql-viewSETcolumn=sql-expression<...column=sql-expression>>;用同一方式更新表中所有观测例22.8更新投资者的股票持仓情况。先创建一个基本表，记录一个投资者的股票持仓情况。datainvest;inputstkcd$stknm$inv;cards;000001S深发展100000000002万科A50000600601方正科技40000;run;把该投资者对三支股票的投资都提高10%。procsql;updateinvestsetinv=inv*1.1;title"UpdatedInvestment";selectstkcdlabel=’股票代码’,stknmlabel=’股票名称’,invlabel=’投资额度’frominvest;quit;UpdatedInvestment股票代码股票名称投资额度---------------------------------------------000001S深发展110000000002万科A55000600601方正科技44000用不同方式更新表中观测例22.9更新例22.8中的数据：深发展再增加50%的投资，万科A增加40%，而方正科技则减少10000元的投资。procsql;updateInvestsetinv=inv*1.5wherestkcd='000001’;updateInvestsetinv=inv*1.4wherestkcd='000002’;updateInvestsetinv=inv-10000wherestkcd='600601’;select*frominvest;quit;stkcdstknminv---------------------------------------------000001S深发展165000000002万科A77000600601方正科技34000删除观测行DELETE语句用于删除表（视图）中的一个或多个观测。procsql;deletefrominvestwherestkcd='600601';quit;列修改ALTERTABLE语句可以增加、修改、和删除表中的列。注意，只能在表中使用该语句，而不能在视图中使用。增加列语句格式：PROCSQL;ALTERTABLEtable-nameconstraint-clause<,...constraint-clause>>>例22.10在invest表中加入该投资者的总股本和投资比例。procsql;altertableinvestaddtotalnumlabel='总股本',rationumlabel='投资比例';selectstkcdlabel='股票代码',stknmlabel='股票名称',invlabel='投资额度',total,ratiofrominvest;quit;股票代码股票名称投资额度总股本投资比例--------------------------------------------------------------------000001S深发展165000..000002万科A77000..目前增加的列中还没有信息，需要用UPDATE语句来补充这些缺失值。procsql;updateinvestsettotal=400000;updateinvestsetratio=inv/total;select*frominvest;quit;股票代码股票名称投资额度总股本投资比例----------------------------------------------------------------------000001S深发展1650004000000.4125000002万科A770004000000.1925修改列语句格式：ALTERTABLEtable-name>;procsql;altertableinvestmodifystkcdchar(10)format=$10.;updateinvestsetstkcd='res'||stkcd;select*frominvest(rename=(stkcd=resstkcd));quit;resstkcdstknminvtotalratio---------------------------------------------------------------res000001S深发展1650004000000.4125res000002万科A770004000000.1925删除列ALTERTABLEtable-name>>DROP子句用来删除表中的列。procsql;altertableinvestdropstknm;创建索引索引是建立在表中一个列或多个列上的一个对象，目的是加快访问表中的数据使用PROCSQL来创建索引语句格式：CREATEINDEXindex-nameONtable-name(column<,...column>);可以创建一个单列的简单索引。这时索引的名字必须和列名一致。下面的程序在invest表中创建一个stkcd的索引。procsql;createindexstkcdoninvest(stkcd);删除索引DROPINDEX语句用来删除一个索引。procsql;dropindexAfromInvest;创建和使用完整性约束完整性约束是指创建一些规则来保证数据的完整性、准确性和连贯性。完整性约束语句格式语句格式：ALTERTABLEtable-name>>选项说明：>一般完整性约束一般完整性约束用来保证列中的数据·非缺失·唯一·非缺失并且唯一处于一个特定范围中。以下有四种一般完整性约束，格式参照前面一节中的constraint的定义：CHECK(WHERE-clause)notnulluniqueprimarykey参考完整性约束将一个表中的主键与另外一个表中的外键联系起来时，就建立了一个参考完整性约束(referentialintegrityconstraint)。建立参考完整性约束的基本条件：·主键和外键对应的变量列的数量以及顺序必须相同。·变量列的数据类型(字符或数值)和长度必须相同。·如果在一个已经存在数据的表中加入外键，则外键中每一个观测的外键值必须和主键中的值匹配，或者为缺失。建立完整性约束实例例22.11建立两个表，一个表(Invest)记录投资情况，另一个是股票价格信息表(prinfo)。加入如下约束：两个表中的股票代码stkcd必须是唯一且不缺失的。每支股票的投资数额Inv不能为负。股票价格（price）不能为负。procsql;createtableinvest(stkcdchar(6),invnum,constraintprim_keyprimarykey(stkcd),constraintinvcheck(invge0));createtableprinfo(stkcdchar(6),pricenum,constraintfor_keyforeignkey(stkcd)referencesinvestondeleterestrictonupdatesetnull,constraintpricecheck(pricege0));可以用DESCRIBETABLE语句来从日志中查看表中的完整性约束信息。procsql;describetableconstraintsinvest;describetableconstraintsprinfo;quit;SAS日志中的信息：15procsql;16describetableconstraintsinvest;NOTE:SQL表WORK.INVEST(bufsize=4096)具有下列完整性约束:-----按字母排序的完整性约束列表-----Where#完整性约束类型变量子句参考删除更新-------------------------------------------------------------------------------------------------------------------1invCheckinv>=02prim_keyPrimaryKeystkcdfor_keyReferentialstkcdWORK.PRINFORestrictSetNull17describetableconstraintsprinfo;NOTE:SQL表WORK.PRINFO(bufsize=4096)具有下列完整性约束:-----按字母排序的完整性约束列表-----Where#完整性约束类型变量子句参考删除更新------------------------------------------------------------------------------------------------------------------1for_keyForeignKeystkcdWORK.INVESTRestrictSetNull2priceCheckprice>=0建立和使用PROCSQL视图PROCSQL视图包含一个储存的查询语句，该查询在使用SAS过程步的时候运行。建立视图语句格式：CREATEVIEWproc-sql-view<(column-name-list)>ASquery-expression>>股票代码|最新股票名称|StockCodeLatestStockNameavgret-------------------------------------------------------000002万科A0.338818000002万科A0.338818000002万科A0.338818程序实例：procsqlobs=3;createviewavgretasselectstkcd,lstknm,avg(yrret)asavgretfromresdat.yrretgroupbystkcd;select*fromavgret;quit;描述视图DESCRIBEVIEW语句将PROCSQL视图的内容写入SAS日志。procsql;describeviewavret;quit;日志信息如下：319procsql;320describeviewavret;NOTE:SQL视图WORK.AVRET定义为:selectstkcd,lstknm,AVG(yrret)asavretfromRESDAT.YRRETgroupbystkcd;321quit;NOTE:“PROCEDURESQL”所用时间（总处理时间）:实际时间0.00秒CPU时间0.00秒删除视图DROPVIEW语句用来删除一个视图。procsql;dropviewstkinfo2;
浙公网安备 33021202002483