SAS統(tǒng)計(jì)分析軟件SQL從多個(gè)表中檢索數(shù)據(jù).ppt

1、第21章 SQL從多個(gè)表中檢索數(shù)據(jù),清華大學(xué)經(jīng)管學(xué)院 朱世武,本章內(nèi)容： 使用連接從多個(gè)表中選取數(shù)據(jù)； 使用子查詢(xún)通過(guò)表與表之間的聯(lián)系選取數(shù)據(jù)； 使用SET算符合并查詢(xún)結(jié)果。 如果沒(méi)有另外說(shuō)明，本章的PROC SQL語(yǔ)對(duì)表和視圖都適用。,使用連接語(yǔ)句JOIN從多個(gè)表中檢索數(shù)據(jù),最基本的連接就是兩個(gè)表的簡(jiǎn)單連接。 例21.1 表china與表 usa的簡(jiǎn)單連接程序： proc sql; select * from resdat.china, resdat.usa; quit;,JOIN方式分類(lèi),通常連接Join方式分為兩種類(lèi)型： 內(nèi)部join，只返回連接表中匹配連接條件的行。 外部join，是內(nèi)

2、部連接的補(bǔ)充，還包括除內(nèi)部連接部分以外不符合連接條件的行。 外部連接分三種：left 左連接,right右連接 and full完全連接。,內(nèi)部連接,數(shù)據(jù)格式 Proc sql; Select object-item from table-name alias, table-name alias Where sql-expression;,例21.2 只對(duì)相同水平的運(yùn)動(dòng)員進(jìn)行連接。 proc sql; select * from resdat.china, resdat.usa where china.level=usa.level; quit;,使用表的別名,通常的查詢(xún)時(shí)會(huì)遇到兩個(gè)表有相同名

3、字的列，為了在引用時(shí)不產(chǎn)生混淆，需要在列名前加上表名或者表的別名。 語(yǔ)句格式: From table-name table-alias,proc sql; select * from resdat.china as a, resdat.usa as b where a.level=b.level; quit;,使用關(guān)鍵詞INNER JOIN的內(nèi)部連接,語(yǔ)句格式： 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;,關(guān)鍵詞INNER JOIN用于連接多個(gè)表的數(shù)據(jù)。關(guān)鍵詞inner是可選的，語(yǔ)句中用ON代替了原來(lái)設(shè)定匹配條件的where語(yǔ)句。,從多于兩個(gè)表的數(shù)據(jù)集中查詢(xún)數(shù)據(jù),例21.6 簡(jiǎn)單的多表連接。 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;,不同形式的外部連接,語(yǔ)句格式 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;,結(jié)果比以前的內(nèi)部連接多了一行，該行就是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;,結(jié)果比以前的內(nèi)部連接多了一行，該行就是Table usa與Table china不匹配的行，不匹配行中Table china的列都是缺失值。,完全外部連接,proc sql; select * from resdat.china a full join resdat.usa b on a.level=b.level; quit;,結(jié)果顯示，兩個(gè)表中所有不匹配的行都出現(xiàn)在完全連接的輸出結(jié)果中。,MERGE語(yǔ)句和JOIN連接比較,所有行匹配無(wú)重復(fù)值情況,兩個(gè)表中的by變量的值都相等且沒(méi)有重復(fù)值的時(shí)候，可以使用一個(gè)內(nèi)部連連接來(lái)產(chǎn)生

7、同樣的效果。 例21.7 BY變量值相等且沒(méi)有重復(fù)值。 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在合并前的兩個(gè)數(shù)據(jù)集已經(jīng)按code排過(guò)序，而PROC SQL則不需要排序，下面程序給出和上面同樣的結(jié)果。 proc sql; title Tab

8、le MERGE1; select a.code, a.manager, b.Assitant from a, b where a.code=b.code; quit;,有重復(fù)值情況,當(dāng)用來(lái)連接兩個(gè)表的列變量或者BY組中有重復(fù)值時(shí)，Merge和Proc sql的處理方式有所區(qū)別。 例21.9 BY組中有重復(fù)值。 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，則會(huì)出現(xiàn)下面的結(jié)果： Proc sql; Title Table Merge3; Select a.code, a.manager, b.assistant From a full join b On a.code=b.code; quit;,使用子查詢(xún)語(yǔ)句選擇數(shù)據(jù),語(yǔ)言格式： (select .from.);,產(chǎn)生單個(gè)值的子查詢(xún),例21.10 考慮例21.9的兩個(gè)表。 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；,產(chǎn)生多個(gè)值的子查詢(xún),例21.11 根據(jù)表sampstk中給定股票的股票代碼，從表lstkinfo中選出相應(yīng)的股票信息。 Proc sql; select stkcd,lstknm,lstdt from resdat.lstkinfo where stkcd in (select stkcd from resdat.sampstk); quit;,混合子查詢(xún),例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;,合并兩個(gè)或多個(gè)查詢(xún)結(jié)果,以下的SET算符例子都基于下面兩個(gè)表。 Table A x y - 1 one 2 two 2 two Three 與JOIN的橫向連接不同，SET連接是豎直的連接。,Table B x z - 1 one 2 two

12、 4 four,由多個(gè)查詢(xún)產(chǎn)生非重復(fù)觀測(cè) (UNION算符),proc sql; title A UNION B; select * from A union select * from B; quit;,產(chǎn)生只屬于第一個(gè)查詢(xún)的觀測(cè)(EXCEPT算符),proc sql; title A EXCEPT B; select * from A except select * from B; quit;,從多個(gè)查詢(xún)中產(chǎn)生公共部分 (INTERSECT算符),proc sql; title A INTERSECT B; select * from A intersect select * from B

13、;,直接連接查詢(xún)結(jié)果 (OUTER UNION算符),proc sql; title A OUTER UNION B; select * from A outer union select * from B;,第22章 SQL創(chuàng)建與更新表和視圖,清華大學(xué)經(jīng)管學(xué)院 朱世武 Z Resdat樣本數(shù)據(jù)： SAS論壇： ,本章內(nèi)容 創(chuàng)建表 更新表 修改已存在的表 刪除表 建立索引 創(chuàng)建表時(shí)使用完整性約束 創(chuàng)建視圖,創(chuàng)建表,Create Table語(yǔ)句創(chuàng)建一個(gè)表。 常用語(yǔ)句如下： 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;,用列定義方式創(chuàng)建表,語(yǔ)句格式： CREATE TABLE table-name (column-specification) ;,例22.1 創(chuàng)建一個(gè)新的股票信息表。 proc sql; create table Newstkinfo ( stkcd char(6), lstknm char(20), lstdt num informat=date9. /* 規(guī)定輸入和輸出 */ format= date9. /* 格式為 DATE9

15、.*/ ); quit; 新的表newstkinfo有三列和零行。 使用describe Table 語(yǔ)句來(lái)檢查這個(gè)新表，并從日志中查看列的信息： proc sql; describe table newstkinfo;,日志信息：,從查詢(xún)結(jié)果創(chuàng)建表,語(yǔ)句格式： CREATE TABLE table-name AS query-expression,例22.2 用Create Table語(yǔ)句從股票信息表創(chuàng)建的查詢(xún)結(jié)果表。 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 語(yǔ)句查看默認(rèn)列的信息。 proc sql; describe table stkinfo; quit;,stkinfo在DESCRIBE TABLE 語(yǔ)句中的SAS日志信息如下：,使用DATA SET 選項(xiàng)語(yǔ)句,在CREATE TALBE語(yǔ)句中使用DATA SET選項(xiàng)語(yǔ)句。 實(shí)例如下： Proc sql; CREATE TABLE stkinfo4 as select * from stkinfo2 (drop=stkcd); 即在from語(yǔ)句中的table name 和vie

17、w name后面可以使用DATA SET選項(xiàng)語(yǔ)句。,在表中插入行,PROC SQL中使用INSERT語(yǔ)句將觀測(cè)行插入表中。 可以使用SET子句或者VALUES子句來(lái)設(shè)定行的信息 。,用SET子句插入觀測(cè),語(yǔ)句格式： INSERT INTO table-name|sas/access-view|proc-sql-view ) SET column=sql-expression ;,例22.5 用2個(gè)SET語(yǔ)句往空表stkinfo3中插入2個(gè)觀測(cè)行。 proc sql; insert into stkinfo3 set stkcd=000002,lstknm=萬(wàn)科A,lstdt=29jan1991

18、d set stkcd=000004,lstknm=*ST國(guó)農(nóng),lstdt=14jun1991d; select stkcd,lstknm,lstdt from stkinfo3; quit; 輸出窗口顯示：,用VALUES子句插入行,語(yǔ)句格式： INSERT INTO table-name|sas/access-view|proc-sql-view ) VALUES (value ) );,例22.6 用VALUES語(yǔ)句插入兩個(gè)觀測(cè)行。 proc sql; insert into stkinfo3 values (000001,S深發(fā)展A,.) values (000005, ,10dec1

19、990d); select stkcd,lstknm,lstdt from stkinfo3; quit;,在表中插入查詢(xún)結(jié)果,INSERT INTO table-name|sas/access-view|proc-sql-view ) ,例22.7建立一個(gè)新表stkinfo4，然后把stkinfo3中日期不為缺失的觀測(cè)插入到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;,更新表中數(shù)據(jù),語(yǔ)句格式： PORC SQL; UPDATE table-name|sas/access-view|proc-sql-view SET column=sql-expression ;,用同一方式更新表中所有觀測(cè),例22.8 更新投資者的股票持倉(cāng)情況。 先創(chuàng)建一個(gè)基本表，記錄一個(gè)投資者的股票持倉(cāng)情況。 data invest; input stkcd $ stknm $ inv; cards; 000001 S深發(fā)展 100000 000002 萬(wàn)科A 50000 600601 方

21、正科技 40000 ; run;,把該投資者對(duì)三支股票的投資都提高10%。 proc sql; update invest set inv= inv*1.1; title Updated Investment ; select stkcd label=股票代碼, stknm label=股票名稱(chēng), inv label=投資額度 from invest; quit;,用不同方式更新表中觀測(cè),例22.9 更新例22.8中的數(shù)據(jù)：深發(fā)展再增加50%的投資，萬(wàn)科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;,刪除觀測(cè)行,DELETE語(yǔ)句用于刪除表（視圖）中的一個(gè)或多個(gè)觀測(cè)。 proc sql; delete from invest where stkcd=600601; quit;,列修改,ALTER TABLE語(yǔ)句可以增加、修改、和刪除表中的列。注意，只能在表中使用該語(yǔ)句，而不能在視圖中使用。,增

23、加列,語(yǔ)句格式： 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=股票名稱(chēng),inv label=投資額度, total,ratio from invest; quit;,目前增加的列中還沒(méi)有信息，需要用UPDATE語(yǔ)句來(lái)補(bǔ)充這些缺失值。 proc sq

24、l; update invest set total=400000; update invest set ratio=inv/total; select * from invest; quit;,修改列,語(yǔ)句格式： 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子句用來(lái)刪除表中的列。 proc sql; alter table invest drop stknm;,創(chuàng)建索引,索引是建立在表中一個(gè)列或多個(gè)列上的一個(gè)對(duì)象，目的是加快訪(fǎng)問(wèn)表中的數(shù)據(jù),使用PROC SQL來(lái)創(chuàng)建索引,語(yǔ)句格式： CREATE INDEX index-name ON table-name ( column ); 可以創(chuàng)建一個(gè)單列的簡(jiǎn)單索引。這時(shí)索引的名字必須和列名一致。 下面的程序在invest表中創(chuàng)建一個(gè)stkcd的索引。 proc sql; create index stkcd on invest(stkcd);,刪除索引,DROP INDEX

26、 語(yǔ)句用來(lái)刪除一個(gè)索引。 proc sql; drop index A from Invest;,創(chuàng)建和使用完整性約束,完整性約束是指創(chuàng)建一些規(guī)則來(lái)保證數(shù)據(jù)的完整性、準(zhǔn)確性和連貫性。,完整性約束語(yǔ)句格式,語(yǔ)句格式： ALTER TABLE table-name 選項(xiàng)說(shuō)明： ,一般完整性約束,一般完整性約束用來(lái)保證列中的數(shù)據(jù) 非缺失 唯一 非缺失并且唯一處于一個(gè)特定范圍中。 以下有四種一般完整性約束，格式參照前面一節(jié)中的constraint的定義： CHECK (WHERE-clause) not null unique primary key,參考完整性約束,將一個(gè)表中的主鍵與另外一個(gè)表中的外鍵聯(lián)系起來(lái)時(shí)，就建立了一個(gè)參考完整性約束(referential integrity constraint) 。 建立參考完整性約束的基本條件： 主鍵和外鍵對(duì)應(yīng)的變量列的數(shù)量以及順序必須相同。 變量列的數(shù)據(jù)類(lèi)型(字符或數(shù)值)和長(zhǎng)度必須相同。 如果在一個(gè)已經(jīng)存在數(shù)據(jù)的表中加入外鍵，則外鍵中每一個(gè)觀測(cè)的外鍵值必須和主鍵中的值匹配，或者為缺失。,建立完整性約束實(shí)例,例22.11 建立兩個(gè)表，一個(gè)表(Invest)記錄投資情況，另一個(gè)是股票價(jià)格信息表(prinfo)。 加入如下約束： 兩個(gè)表中的股票代碼stk