SAS统计分析软件SQL从多个表中检索数据.ppt

1、第21章 SQL从多个表中检索数据,清华大学经管学院 朱世武,本章内容： 使用连接从多个表中选取数据； 使用子查询通过表与表之间的联系选取数据； 使用SET算符合并查询结果。 如果没有另外说明，本章的PROC SQL语对表和视图都适用。,使用连接语句JOIN从多个表中检索数据,最基本的连接就是两个表的简单连接。 例21.1 表china与表 usa的简单连接程序： proc sql; select * from resdat.china, resdat.usa; quit;,JOIN方式分类,通常连接Join方式分为两种类型： 内部join，只返回连接表中匹配连接条件的行。 外部join，是内

2、部连接的补充，还包括除内部连接部分以外不符合连接条件的行。 外部连接分三种：left 左连接,right右连接 and full完全连接。,内部连接,数据格式 Proc sql; Select object-item from table-name alias, table-name alias Where sql-expression;,例21.2 只对相同水平的运动员进行连接。 proc sql; select * from resdat.china, resdat.usa where china.level=usa.level; quit;,使用表的别名,通常的查询时会遇到两个表有相同名

3、字的列，为了在引用时不产生混淆，需要在列名前加上表名或者表的别名。 语句格式: From table-name table-alias,proc sql; select * from resdat.china as a, resdat.usa as b where a.level=b.level; quit;,使用关键词INNER JOIN的内部连接,语句格式： From table-name JOIN table-name ON sql-expression,proc sql; select * from resdat.china a inner join resdat.china b on

4、 a.level=b.level; order by level; quit;,关键词INNER JOIN用于连接多个表的数据。关键词inner是可选的，语句中用ON代替了原来设定匹配条件的where语句。,从多于两个表的数据集中查询数据,例21.6 简单的多表连接。 proc sql outobs=3; select a.stkcd,b.lstknm,c.clpr from resdat.sampstk a,resdat.lstkinfo b,resdat.qttndist c where a.stkcd=b.stkcd and b.stkcd=c.stkcd and a.stkcd=c.s

5、tkcd; quit;,不同形式的外部连接,语句格式 From table-name LEFT JOIN | RIGHT JOIN | FULL JOIN,左外部连接,proc sql; select * from resdat.china a left join resdat.usa b on a.level=b.level; quit;,结果比以前的内部连接多了一行，该行就是Table china与Table usa不匹配的行，不匹配行中Table usa的列为缺失值。,右外部连接,proc sql; select * from resdat.china a right join resd

6、at.usa b on a.level=b.level; quit;,结果比以前的内部连接多了一行，该行就是Table usa与Table china不匹配的行，不匹配行中Table china的列都是缺失值。,完全外部连接,proc sql; select * from resdat.china a full join resdat.usa b on a.level=b.level; quit;,结果显示，两个表中所有不匹配的行都出现在完全连接的输出结果中。,MERGE语句和JOIN连接比较,所有行匹配无重复值情况,两个表中的by变量的值都相等且没有重复值的时候，可以使用一个内部连连接来产生

7、同样的效果。 例21.7 BY变量值相等且没有重复值。 Table a Table b code manager code Assitant 145 Max 145 Tracy 150 Jack 150 Yao 155 Paul 155 Chen 程序如下： data merge1; merge a b; by code; run;,proc print data=merge1 noobs; title Table MERGE1; run;,Merge在合并前的两个数据集已经按code排过序，而PROC SQL则不需要排序，下面程序给出和上面同样的结果。 proc sql; title Tab

8、le MERGE1; select a.code, a.manager, b.Assitant from a, b where a.code=b.code; quit;,有重复值情况,当用来连接两个表的列变量或者BY组中有重复值时，Merge和Proc sql的处理方式有所区别。 例21.9 BY组中有重复值。 Table newone Table newtwo code Manager code Assistant 145 Max 145 Jerry 145 Xam 145 Tracy 155 Paul 155 Chen Data步 data merge3; merge a b; by co

9、de; run; proc print data=merge3 noobs; title Table MERGE3; run;,若用SQL，则会出现下面的结果： Proc sql; Title Table Merge3; Select a.code, a.manager, b.assistant From a full join b On a.code=b.code; quit;,使用子查询语句选择数据,语言格式： (select .from.);,产生单个值的子查询,例21.10 考虑例21.9的两个表。 Proc sql; Title Which Manager has the same

10、code as Assistant Chen; Select * From a Where code eq (select code from b where assistant=Chen); Quit；,产生多个值的子查询,例21.11 根据表sampstk中给定股票的股票代码，从表lstkinfo中选出相应的股票信息。 Proc sql; select stkcd,lstknm,lstdt from resdat.lstkinfo where stkcd in (select stkcd from resdat.sampstk); quit;,混合子查询,例21.12 选出表resdat.

11、yrret中所有A股2005年的年收益率。 proc sql; select stkcd, yrret from resdat.yrret a where (select stktype from resdat.lstkinfo b where a.stkcd=b.stkcd)=A and 1jan2005d=date=31dec2005d; quit;,合并两个或多个查询结果,以下的SET算符例子都基于下面两个表。 Table A x y - 1 one 2 two 2 two Three 与JOIN的横向连接不同，SET连接是竖直的连接。,Table B x z - 1 one 2 two

12、 4 four,由多个查询产生非重复观测 (UNION算符),proc sql; title A UNION B; select * from A union select * from B; quit;,产生只属于第一个查询的观测(EXCEPT算符),proc sql; title A EXCEPT B; select * from A except select * from B; quit;,从多个查询中产生公共部分 (INTERSECT算符),proc sql; title A INTERSECT B; select * from A intersect select * from B

13、;,直接连接查询结果 (OUTER UNION算符),proc sql; title A OUTER UNION B; select * from A outer union select * from B;,第22章 SQL创建与更新表和视图,清华大学经管学院 朱世武 Z Resdat样本数据： SAS论坛： ,本章内容 创建表 更新表 修改已存在的表 删除表 建立索引 创建表时使用完整性约束 创建视图,创建表,Create Table语句创建一个表。 常用语句如下： 1）CREATE TABLE table-name (column-specification) ; 2）CREATE TA

14、BLE table-name AS query-expression ; 3）CREATE TABLE table-name LIKE table-name2;,用列定义方式创建表,语句格式： CREATE TABLE table-name (column-specification) ;,例22.1 创建一个新的股票信息表。 proc sql; create table Newstkinfo ( stkcd char(6), lstknm char(20), lstdt num informat=date9. /* 规定输入和输出 */ format= date9. /* 格式为 DATE9

15、.*/ ); quit; 新的表newstkinfo有三列和零行。 使用describe Table 语句来检查这个新表，并从日志中查看列的信息： proc sql; describe table newstkinfo;,日志信息：,从查询结果创建表,语句格式： CREATE TABLE table-name AS query-expression,例22.2 用Create Table语句从股票信息表创建的查询结果表。 proc sql outobs=3; title Stock Information; create table stkinfo as select stkcd, lstkn

16、m, lstdt from ResDat.lstkinfo; quit; 例22.3 用describe Table 语句查看默认列的信息。 proc sql; describe table stkinfo; quit;,stkinfo在DESCRIBE TABLE 语句中的SAS日志信息如下：,使用DATA SET 选项语句,在CREATE TALBE语句中使用DATA SET选项语句。 实例如下： Proc sql; CREATE TABLE stkinfo4 as select * from stkinfo2 (drop=stkcd); 即在from语句中的table name 和vie

17、w name后面可以使用DATA SET选项语句。,在表中插入行,PROC SQL中使用INSERT语句将观测行插入表中。 可以使用SET子句或者VALUES子句来设定行的信息 。,用SET子句插入观测,语句格式： INSERT INTO table-name|sas/access-view|proc-sql-view ) SET column=sql-expression ;,例22.5 用2个SET语句往空表stkinfo3中插入2个观测行。 proc sql; insert into stkinfo3 set stkcd=000002,lstknm=万科A,lstdt=29jan1991

18、d set stkcd=000004,lstknm=*ST国农,lstdt=14jun1991d; select stkcd,lstknm,lstdt from stkinfo3; quit; 输出窗口显示：,用VALUES子句插入行,语句格式： INSERT INTO table-name|sas/access-view|proc-sql-view ) VALUES (value ) );,例22.6 用VALUES语句插入两个观测行。 proc sql; insert into stkinfo3 values (000001,S深发展A,.) values (000005, ,10dec1

19、990d); select stkcd,lstknm,lstdt from stkinfo3; quit;,在表中插入查询结果,INSERT INTO table-name|sas/access-view|proc-sql-view ) ,例22.7建立一个新表stkinfo4，然后把stkinfo3中日期不为缺失的观测插入到stkinfo4中。 proc sql; create table stkinfo4 like stkinfo3; proc sql; insert into stkinfo4 select * from stkinfo3 where lstdt is not missi

20、ng; select stkcd,lstknm,lstdt from stkinfo4; quit;,更新表中数据,语句格式： PORC SQL; UPDATE table-name|sas/access-view|proc-sql-view SET column=sql-expression ;,用同一方式更新表中所有观测,例22.8 更新投资者的股票持仓情况。 先创建一个基本表，记录一个投资者的股票持仓情况。 data invest; input stkcd $ stknm $ inv; cards; 000001 S深发展 100000 000002 万科A 50000 600601 方

21、正科技 40000 ; run;,把该投资者对三支股票的投资都提高10%。 proc sql; update invest set inv= inv*1.1; title Updated Investment ; select stkcd label=股票代码, stknm label=股票名称, inv label=投资额度 from invest; quit;,用不同方式更新表中观测,例22.9 更新例22.8中的数据：深发展再增加50%的投资，万科A增加40%，而方正科技则减少10000元的投资。 proc sql; update Invest set inv= inv *1.5 whe

22、re stkcd=000001; update Invest set inv = inv *1.4 where stkcd=000002; update Invest set inv= inv -10000 where stkcd=600601; select * from invest; quit;,删除观测行,DELETE语句用于删除表（视图）中的一个或多个观测。 proc sql; delete from invest where stkcd=600601; quit;,列修改,ALTER TABLE语句可以增加、修改、和删除表中的列。注意，只能在表中使用该语句，而不能在视图中使用。,增

23、加列,语句格式： PROC SQL ; ALTER TABLE table-name constraint-clause ,例22.10 在invest表中加入该投资者的总股本和投资比例。 proc sql; alter table invest add total num label=总股本,ratio num label=投资比例; select stkcd label=股票代码, stknm label=股票名称,inv label=投资额度, total,ratio from invest; quit;,目前增加的列中还没有信息，需要用UPDATE语句来补充这些缺失值。 proc sq

24、l; update invest set total=400000; update invest set ratio=inv/total; select * from invest; quit;,修改列,语句格式： ALTER TABLE table-name ;,proc sql; alter table invest modify stkcd char(10) format=$10.; update invest set stkcd=res|stkcd; select * from invest(rename=(stkcd=resstkcd); quit;,删除列,ALTER TABLE

25、table-name DROP子句用来删除表中的列。 proc sql; alter table invest drop stknm;,创建索引,索引是建立在表中一个列或多个列上的一个对象，目的是加快访问表中的数据,使用PROC SQL来创建索引,语句格式： CREATE INDEX index-name ON table-name ( column ); 可以创建一个单列的简单索引。这时索引的名字必须和列名一致。 下面的程序在invest表中创建一个stkcd的索引。 proc sql; create index stkcd on invest(stkcd);,删除索引,DROP INDEX

26、 语句用来删除一个索引。 proc sql; drop index A from Invest;,创建和使用完整性约束,完整性约束是指创建一些规则来保证数据的完整性、准确性和连贯性。,完整性约束语句格式,语句格式： ALTER TABLE table-name 选项说明： ,一般完整性约束,一般完整性约束用来保证列中的数据 非缺失 唯一 非缺失并且唯一处于一个特定范围中。 以下有四种一般完整性约束，格式参照前面一节中的constraint的定义： CHECK (WHERE-clause) not null unique primary key,参考完整性约束,将一个表中的主键与另外一个表中的外键联系起来时，就建立了一个参考完整性约束(referential integrity constraint) 。 建立参考完整性约束的基本条件： 主键和外键对应的变量列的数量以及顺序必须相同。 变量列的数据类型(字符或数值)和长度必须相同。 如果在一个已经存在数据的表中加入外键，则外键中每一个观测的外键值必须和主键中的值匹配，或者为缺失。,建立完整性约束实例,例22.11 建立两个表，一个表(Invest)记录投资情况，另一个是股票价格信息表(prinfo)。 加入如下约束： 两个表中的股票代码stk