数据库中不同的表，怎样设置两个id关联 sql server如何关联两个表
\u4e24\u4e2a\u6570\u636e\u5e93\u4e4b\u95f4\u8868\u80fd\u4e0d\u80fd\u5173\u8054\u8d77\u6765ALTER TABLE order ADD CONSTRAINT address1 FOREIGN KEY (address_id) REFERENCES address;
ALTER TABLE order ADD CONSTRAINT address2 FOREIGN KEY (address_id) REFERENCES address;
\u4f60\u8981\u5220\u9664\u7684\u8bdd\uff1a
delete from order where address1 = '1001' or address2 = '1001';
\u50cf\u8fd9\u4e9b\u6570\u636e\uff0c\u4e0d\u5efa\u8bae\u505a\u7269\u7406\u5220\u9664\uff0c\u4e5f\u5c31\u662fdelete\u3002\u4f60\u53ef\u4ee5\u52a0\u4e00\u4e2a\u72b6\u6001\u5b57\u6bb5\uff0c\u5c06\u5b83\u6807\u8bc6\u4e3a\u65e0\u6548\u5c31\u53ef\u4ee5\u4e86\u3002\u56e0\u4e3a\u8fd9\u4e9b\u6570\u636e\uff0c\u5bf9\u4e1a\u52a1\u8fdb\u884c\u4e00\u4e9b\u7edf\u8ba1\u548c\u6570\u636e\u5206\u6790\u90fd\u662f\u6709\u7528\u7684\u3002\u518d\u6bd4\u5982\uff0c\u6211\u4f7f\u7528\u5730\u57401\u4e0b\u8fc7\u4e00\u4e9b\u8ba2\u5355\uff0c\u73b0\u5728\u6211\u642c\u5bb6\u4e86\uff0c\u5730\u57401\u5c31\u6ca1\u7528\u4e86\uff0c\u6211\u8981\u6362\u53e6\u5916\u4e00\u4e2a\u5730\u5740\u3002\u90a3\u6211\u4f1a\u5220\u9664\u5730\u57401\uff0c\u518d\u6dfb\u52a0\u4e00\u4e2a\u5730\u57402\uff0c\u4f46\u6211\u5730\u57401\u7684\u8ba2\u5355\u8fd8\u662f\u8981\u770b\u7684\u5427\uff0c\u4f60\u603b\u4e0d\u80fd\u5220\u9664\u554a\u3002\u800c\u4e14\u5728\u5730\u57401\u7684\u8ba2\u5355\u4e2d\uff0c\u6211\u8981\u80fd\u770b\u5230\u5730\u57401\u7684\u5730\u5740\u4fe1\u606f\u3002\u6240\u4ee5\uff0c\u8fd9\u91cc\u5bf9\u4e8e\u5730\u5740\u5220\u9664\uff0c\u90a3\u5c31\u4e0d\u80fd\u505adelete\u4e86\uff0c\u53ea\u80fd\u505a\u6807\u8bc6\u3002\u6807\u8bc6\u65e0\u6548\u540e\uff0c\u53ea\u80fd\u4ee3\u8868\u5730\u5740\u5728\u4e0b\u5355\u9009\u62e9\u5730\u5740\u6216\u4e00\u4e9b\u5176\u5b83\u64cd\u4f5c\u7684\u65f6\u5019\uff0c\u8fd9\u4e2a\u65e0\u6548\u7684\u5730\u5740\u4e0d\u663e\u793a\uff0c\u4f46\u5728\u67e5\u770b\u8ba2\u5355\u7684\u65f6\u5019\uff0c\u8fd9\u4e2a\u5730\u5740\u4fe1\u606f\u8fd8\u662f\u80fd\u663e\u793a\u51fa\u6765\u7684\u3002
\u8fd9\u4e2a\u5177\u4f53\u54ea\u53ef\u4ee5\u663e\u793a\uff0c\u54ea\u4e0d\u80fd\u663e\u793a\uff0c\u5c31\u8981\u6839\u636e\u4f60\u7684\u4e1a\u52a1\u548c\u5b9e\u9645\u60c5\u51b5\u6765\u5b9a\u4e86
\u4f60\u662f\u60f3\u5f97\u5230 table2 \u7684\u6570\u636e\u5bf9\u5427\uff1f
\u4f60\u53ef\u4ee5\u7528\u89c6\u56fe\u6765\u505a
create view table1 as select id as ttid ,text1,text2 from table2
\u6559\u7a0b\uff1a
CREATE VIEW
\u540d\u79f0
CREATE VIEW \u2014 \u6784\u5efa\u4e00\u4e2a\u865a\u62df\u8868\uff08\u89c6\u56fe\uff09
\u8bed\u6cd5
CREATE VIEW view AS SELECT query
\u8f93\u5165
view
\u6240\u8981\u521b\u5efa\u7684\u89c6\u56fe\u540d\u79f0\uff0e
query
\u4e00\u4e2a\u5c06\u4e3a\u89c6\u56fe\u63d0\u4f9b\u884c\u548c\u5217\u7684 SQL \u67e5\u8be2\uff0e
\u8bf7\u53c2\u9605 SELECT \u8bed\u53e5\u83b7\u53d6\u6709\u6548\u53c2\u6570\u7684\u66f4\u591a\u4fe1\u606f\uff0e
\u8f93\u51fa
CREATE
\u5982\u679c\u89c6\u56fe\u521b\u5efa\u6210\u529f\uff0c\u8fd4\u56de\u6b64\u4fe1\u606f\uff0e
ERROR: Relation 'view' already exists
\u5982\u679c\u5728\u6570\u636e\u5e93\u4e2d\u5df2\u7ecf\u5b58\u5728\u6240\u58f0\u660e\u7684\u89c6\u56fe\uff0e
NOTICE create: attribute named "column" has an unknown type
\u5982\u679c\u4e0d\u58f0\u660e\uff0c\u6240\u521b\u5efa\u7684\u89c6\u56fe\u5c06\u6709\u4e00\u4e2a\u672a\u77e5\u7c7b\u578b\u7684\u5b57\u6bb5\uff0e\u4f8b\u5982\uff0c\u4e0b\u9762\u547d\u4ee4\u8fd4\u56de\u4e00\u4e2a\u8b66\u544a\uff1a
CREATE VIEW vista AS SELECT 'Hello World'
\u7136\u800c\u4e0b\u9762\u547d\u4ee4\u5c06\u4e0d\u51fa\u73b0\u8b66\u544a\uff1a
CREATE VIEW vista AS SELECT text 'Hello World'
\u63cf\u8ff0
CREATE VIEW \u5c06\u5b9a\u4e49\u4e00\u4e2a\u8868\u7684\u89c6\u56fe\uff0e\u8fd9\u4e2a\u89c6\u56fe\u4e0d\u662f\u7269\u7406\u4e0a\u5b9e\u9645\u5b58\u5728\uff08\u4e8e\u78c1\u76d8\uff09\u7684\uff0e\u5177\u4f53\u7684\u8bf4\uff0c\u81ea\u52a8\u751f\u6210\u4e00\u4e2a\u6539\u5199\u7d22\u5f15\u89c4\u5219\u7684\u67e5\u8be2\u7528\u4ee5\u652f\u6301\u5728\u89c6\u56fe\u4e0a\u7684\u68c0\u7d22\uff0e
\u6ce8\u610f
\u76ee\u524d\uff0c\u89c6\u56fe\u662f\u53ea\u8bfb\u7684\uff0e
\u4f7f\u7528 DROP VIEW \u8bed\u53e5\u5220\u9664\u89c6\u56fe\uff0e
\u7528\u6cd5
\u521b\u5efa\u4e00\u4e2a\u7531\u6240\u6709 Comedy \uff08\u559c\u5267\uff09\u7535\u5f71\u7ec4\u6210\u7684\u89c6\u56fe\uff1a
CREATE VIEW kinds AS
SELECT *
FROM films
WHERE kind = 'Comedy';
SELECT * FROM kinds;
code | title | did | date_prod | kind | len
-------+---------------------------+-----+------------+--------+-------
UA502 | Bananas | 105 | 1971-07-13 | Comedy | 01:22
C_701 | There's a Girl in my Soup | 107 | 1970-06-11 | Comedy | 01:36
(2 rows)
\u517c\u5bb9\u6027
SQL92
SQL92 \u4e3a CREATE VIEW \u58f0\u660e\u4e86\u4e00\u4e9b\u9644\u52a0\u7684\u529f\u80fd\uff1a
CREATE VIEW view [ column [, ...] ]
AS SELECT expression [ AS colname ] [, ...]
FROM table [ WHERE condition ]
[ WITH [ CASCADE | LOCAL ] CHECK OPTION ]
\u5b8c\u6574\u7684SQL92\u547d\u4ee4\u53ef\u9009\u7684\u5b50\u53e5\u662f\uff1a
CHECK OPTION
\u8fd9\u4e2a\u9009\u9879\u7528\u4e8e\u53ef\u66f4\u65b0\u89c6\u56fe\uff0e\u6240\u6709\u5bf9\u89c6\u56fe\u7684 INSERT \u548c UPDATE \u90fd\u8981\u7ecf\u8fc7\u89c6\u56fe\u5b9a\u4e49\u6761\u4ef6\u7684\u6821\u9a8c\uff0e\u5982\u679c\u6ca1\u6709\u901a\u8fc7\u6821\u9a8c\uff0c\u66f4\u65b0\u5c06\u88ab\u62d2\u7edd\uff0e
LOCAL
\u5bf9\u8fd9\u4e2a\u89c6\u56fe\u8fdb\u884c\u5b8c\u6574\u6027\u68c0\u67e5\uff0e
CASCADE
\u5bf9\u6b64\u89c6\u56fe\u548c\u4efb\u4f55\u76f8\u5173\u89c6\u56fe\u8fdb\u884c\u5b8c\u6574\u6027\u68c0\u67e5\uff0e\u5728\u65e2\u6ca1\u6709\u58f0\u660e CASCADE \u4e5f\u6ca1\u6709\u58f0\u540d LOCAL \u65f6\uff0c\u5047\u8bbe\u4e3a CASCADE\uff0e
方法二：自然连接select * from member natural join xinxi;
方法三：直接连接select x.id,m.name,m.class,m.xid,x.phone,x.address from member m join xinxi x on m.id=x.id;
数据库——数据库关系图(右击）——数据库关系图——添加member和xinxi表---点击member的id拖动到xinxi的id即可。
绛旓細鍏崇郴鐨勫缓绔嬮渶瑕佷綘鍏堝仛濂借繖涓や釜琛ㄤ箣闂寸殑涓汇佷粠閿鐨勮缃锛堝嵆妗ｆ琛ㄤ腑鐨瀛﹀彿涓轰富閿紝鎴愮哗琛ㄤ腑鐨勫鍙蜂负浠庨敭锛夛紝鍐嶅湪宸ュ叿鑿滃崟鏍忎腑鎵惧埌鈥滃叧绯烩濋夐」锛屽皢杩欎袱涓〃杩涜娣诲姞锛屽苟鐢ㄩ紶鏍囧乏閿湪浠讳竴琛ㄤ腑鍗曞嚮鈥滃鍙封濆瓧娈碉紝鎵樿嚦鍙︿竴琛ㄤ腑锛姝ゆ椂绯荤粺浼氬脊鍑哄叧绯婚夐」锛屽啀鏍规嵁鍏舵彁绀哄缓绔嬪叧绯诲嵆鍙傝嚦浜庣獥浣撶殑鍒涘缓锛...
绛旓細1锛庢墦寮鏁版嵁搴撶鐞嗗伐鍏峰苟鍦鏁版嵁搴撲腑鍒涘缓涓や釜鐢ㄤ簬娴嬭瘯鐨勬柊琛紝鍦ㄨ繖閲岋紝杩欎袱涓〃鐨勮〃缁撴瀯搴旇鏄浉鍚岀殑銆2锛庢墦寮涓涓柊鐨凷QL鏌ヨ绐楀彛鏉ュ垱寤轰竴涓瓨鍌ㄨ繃绋嬨傝繖鎰忓懗鐫灏嗘彃鍏ュ埌TEST涓殑鏁版嵁鎻掑叆鍒癟EST1涓3锛庡崟鍑烩滄墽琛屸濓紝灏嗗垱寤轰竴涓悕涓衡淭鈥濈殑鏂拌Е鍙戝櫒銆傝緭鍏ヤ唬鐮侊細USE锛籔HCTIAOMA锛紾O 锛忥紛锛婏紛object...
绛旓細濡傛灉瑕佸幓鎺夐噸澶嶄环鏍煎彲浠ュ鍔燿istinct锛屽涓嬶細select a.name , count(distinct price) from a,b where a.mediid = b.mediid and a.forgid = b.forgid and a.forgid = 'E' group by a.name;闂2锛氭煡璇鏈烘瀯涓嬮潰锛屼袱涓琛ㄤ腑缂栧彿鐩鍚岀殑椤圭洰 select count(name) from a,b where a.mediid ...
绛旓細1.濡傛灉鏄悓涓鏈嶅姟鍣細鍋囪 鍙︿竴涓暟鎹簱鍚嶄负'鏁版嵁搴揃',骞朵笖褰撶劧鐢ㄦ埛瀵涓や釜鏁版嵁搴閮芥湁瀵瑰簲鏉冮檺 select into [table] from [鏁版嵁搴揃].[鎵鏈夎匽.[琛ㄥ悕]2.濡傛灉涓嶅湪鍚屼竴鏈嶅姟鍣 select into [table] from opendatasource('sqloledb','data source=鏈嶅姟鍣ㄥ悕鎴朓P;user id=鐧婚檰鍚;password=鍙ｄ护')....
绛旓細2銆佹彃鍏ヤ骇鍝佽〃娴嬭瘯鏁版嵁 insert into test_product values(1001,'鐜╁叿');insert into test_product values(1002,'涔︽湰');insert into test_product values(1003,'鏀堕泦');3銆佹彃鍏ヨ鍗曡〃娴嬭瘯鏁版嵁 insert into test_order values(1,1001);insert into test_order values(2,1002);insert into test_order...
绛旓細璧勬枡搴 涓婚敭鍞竴鏄竴寮琛ㄩ噷鍞竴杩樻槸鏁翠釜璧勬枡搴撻噷鍞竴 杩欏紶琛ㄤ腑銆備竴涓笓妗堝綋涓彲浠ユ湁涓や釜涓婚敭鐩稿悓鐨勮祫鏂搴撹〃鍚 涓嶅彲浠锛屼笉鍚琛ㄧ殑鏍忎綅鍚嶅彲浠ヤ竴鏍凤紝浣嗘槸涓婚敭鍚嶄笉鑳戒竴鏍凤紝灏卞儚涓嶈兘鏈2涓鍚屽悕鐨勮〃涓鏍 涓涓猺ac 涓涢泦涓彲鍚︽湁涓や釜浠ヤ笂鐨勮祫鏂欏簱 涓涓鏁版嵁搴鍙互鐢卞涓緥椤瑰紑鍚紝浣嗕换浣曟椂鍒讳竴涓緥椤...
绛旓細琛ㄥ悕鍜岀敤鎴锋病鏈夊叧绯伙紝鍏抽敭鐨勪竴涓疄渚嬩笅锛屼笉鑳芥湁鐩稿悓鐨勮〃鍚嶅瓨鍦ㄣ備篃灏辨槸璇碠racle鏁版嵁搴撶郴缁熶腑锛屽彲浠ユ湁澶氫釜鏁版嵁搴擄紝姣忎竴涓暟鎹簱鍙互鏈夊涓鏁版嵁琛銆傚悓涓涓鏁版嵁搴撲腑涓鑳芥湁鐩稿悓鍚嶇О鐨勬暟鎹琛紝浣嗗湪涓嶅悓鐨鏁版嵁搴撲腑鍙互鏈夌浉鍚岀殑鏁版嵁琛ㄥ悕銆
绛旓細鍙互锛岀粷瀵瑰彲浠 1.鏈夐噸澶嶇殑鍐呭 ---> 杩欎釜寰堟甯革紝鏁版嵁搴鏈夐噸澶嶆暟鎹緢姝ｅ父锛屽嵆浣挎槸鍦ㄤ竴涓〃閲岄潰锛屽鏋滃湪琛ㄩ噷闈㈡病鏈璁剧疆涓婚敭鐨勮瘽锛岄噸澶嶅緢姝ｅ父锛屽湪鐜板湪鐨勫伐浣滀腑锛屼笓闂ㄥ氨鏈変竴涓簨鎯咃紝灏辨槸鍘婚噸锛岃屼綘璇存槸鍦涓や釜涓嶅悓鐨勮〃閲岄潰锛屾湁閲嶅鐨勮瘽锛岄偅灏辨洿鍔犳洿姝ｅ父涓嶈繃浜 2.鏈夊悓鏍风殑瀛楁---> 杩欎釜澶甯镐簡...
绛旓細涓や釜琛ㄦ牸鏁版嵁鍙互閫氳繃鍚勭鏂瑰紡鍏宠仈鍦ㄤ竴璧凤紝鏈甯歌鐨勬柟娉曟槸浣跨敤鍏崇郴鍨鏁版嵁搴撲腑鐨“杩炴帴”锛圝OIN锛夋搷浣溿傛牴鎹袱涓〃鏍间箣闂寸殑鍏卞悓瀛楁锛堥氬父鏄富閿拰澶栭敭锛夛紝鍙互閫夋嫨涓嶅悓绫诲瀷鐨勮繛鎺ワ紙濡傚唴杩炴帴銆佸乏杩炴帴銆佸彸杩炴帴鎴栧叏杩炴帴锛夋潵鍚堝苟鏁版嵁銆傚湪鏁版嵁澶勭悊鍜屽垎鏋愪腑锛岀粡甯搁渶瑕佸皢鏉ヨ嚜涓嶅悓琛ㄦ牸鐨勬暟鎹叧鑱旇捣鏉ワ紝...
绛旓細鐢╠blink鍚,姣旇緝鏂逛究,濡傛灉璺鏁版嵁搴闇瑕佽冭檻鍐欏簲鐢ㄧ▼搴忓疄鐜 dblink鐨勫垱寤轰娇鐢:1.鍒涘缓dblink璇硶锛欳REATE [PUBLIC] DATABASE LINK link CONNECT TO username IDENTIFIED BY password USING 鈥榗onnectstring鈥欎緥瀛:create database link link_tgwms_histdb connect to usernameidentified by password using '(...
