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

2、部連接的補充，還包括除內(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 只對相同水平的運動員進(jìn)行連接。 proc sql; select * from resdat.china, resdat.usa where china.level=usa.level; quit;,使用表的別名,通常的查詢時會遇到兩個表有相同名

3、字的列，為了在引用時不產(chǎn)生混淆，需要在列名前加上表名或者表的別名。 語句格式: 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)部連接,語句格式： 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用于連接多個表的數(shù)據(jù)。關(guān)鍵詞inner是可選的，語句中用ON代替了原來設(shè)定匹配條件的where語句。,從多于兩個表的數(shù)據(jù)集中查詢數(shù)據(jù),例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;,結(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é)果顯示，兩個表中所有不匹配的行都出現(xiàn)在完全連接的輸出結(jié)果中。,MERGE語句和JOIN連接比較,所有行匹配無重復(fù)值情況,兩個表中的by變量的值都相等且沒有重復(fù)值的時候，可以使用一個內(nèi)部連連接來產(chǎn)生

7、同樣的效果。 例21.7 BY變量值相等且沒有重復(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在合并前的兩個數(shù)據(jù)集已經(jīng)按code排過序，而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)用來連接兩個表的列變量或者BY組中有重復(fù)值時，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，則會出現(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;,使用子查詢語句選擇數(shù)據(jù),語言格式： (select .from.);,產(chǎn)生單個值的子查詢,例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；,產(chǎ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;,混合子查詢,例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;,合并兩個或多個查詢結(jié)果,以下的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,由多個查詢產(chǎn)生非重復(fù)觀測 (UNION算符),proc sql; title A UNION B; select * from A union select * from B; quit;,產(chǎn)生只屬于第一個查詢的觀測(EXCEPT算符),proc sql; title A EXCEPT B; select * from A except select * from B; quit;,從多個查詢中產(chǎn)生公共部分 (INTERSECT算符),proc sql; title A INTERSECT B; select * from A intersect select * from B

13、;,直接連接查詢結(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)建表時使用完整性約束 創(chuàng)建視圖,創(chuàng)建表,Create Table語句創(chuàng)建一個表。 常用語句如下： 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)建表,語句格式： CREATE TABLE table-name (column-specification) ;,例22.1 創(chuàng)建一個新的股票信息表。 proc sql; create table Newstkinfo ( stkcd char(6), lstknm char(20), lstdt num informat=date9. /* 規(guī)定輸入和輸出 */ format= date9. /* 格式為 DATE9

15、.*/ ); quit; 新的表newstkinfo有三列和零行。 使用describe Table 語句來檢查這個新表，并從日志中查看列的信息： proc sql; describe table newstkinfo;,日志信息：,從查詢結(jié)果創(chuàng)建表,語句格式： CREATE TABLE table-name AS query-expression,例22.2 用Create Table語句從股票信息表創(chuàng)建的查詢結(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 語句查看默認(rèn)列的信息。 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子句來設(shè)定行的信息 。,用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國農(nóng),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深發(fā)展A,.) values (000005, ,10dec1

19、990d); select stkcd,lstknm,lstdt from stkinfo3; quit;,在表中插入查詢結(jié)果,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;,更新表中數(shù)據(jù),語句格式： PORC SQL; UPDATE table-name|sas/access-view|proc-sql-view SET column=sql-expression ;,用同一方式更新表中所有觀測,例22.8 更新投資者的股票持倉情況。 先創(chuàng)建一個基本表，記錄一個投資者的股票持倉情況。 data invest; input stkcd $ stknm $ inv; cards; 000001 S深發(fā)展 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中的數(shù)據(jù)：深發(fā)展再增加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;,創(chuàng)建索引,索引是建立在表中一個列或多個列上的一個對象，目的是加快訪問表中的數(shù)據(jù),使用PROC SQL來創(chuàng)建索引,語句格式： CREATE INDEX index-name ON table-name ( column ); 可以創(chuàng)建一個單列的簡單索引。這時索引的名字必須和列名一致。 下面的程序在invest表中創(chuàng)建一個stkcd的索引。 proc sql; create index stkcd on invest(stkcd);,刪除索引,DROP INDEX

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