绗?叓澶?鎿嶄綔鏁版嵁)
insert璇?彞涓?暟瀛楀拰绌哄?间笉闇?瑕佸紩鍙凤紝鑰屽瓧绗﹀瀷鏁版嵁鍒欓渶瑕佸紩鍙枫??
insert value璇?彞鍦ㄥ悜琛ㄤ腑鎻掑叆鍑犱釜鏁版嵁鐨勬椂鍊欓潪甯告湁鐢?紝浣嗘樉鐒惰繖鏄?笉澶熺殑銆傚?鏋滀綘鎯冲悜琛ㄤ腑鎻掑叆25000琛屾暟鎹?椂鎬庝箞鍔烇紵鍦ㄨ繖绉嶆儏鍐典笅insert select璇?彞灏遍潪甯告湁鏁堛?傚畠鍏佽?绋嬪簭鍛樻嫹璐濅竴涓?垨涓?缁勮〃鐨勪俊鎭?埌鍙︿竴涓?〃涓??備綘鍙?互鍦ㄤ笅杈硅繖鍑犵?鎯呭喌涓嬩娇鐢ㄨ?璇?彞銆傞渶瑕佹煡璇㈢殑琛ㄧ粡甯镐骇鐢熷埄娑︾殑澧炲姞銆侀渶瑕佹煡璇㈢殑琛ㄥ彲浠ヤ粠澶氫釜鏁版嵁搴撴垨琛ㄤ腑鑾峰緱澶栭儴鏁版嵁锛岀敱浜庡?涓?〃鐨勬煡璇㈣?姣斿崟涓?琛ㄧ殑鏌ヨ?閫熷害鎱㈠緱澶氬洜姝ゅ?鍗曚釜琛ㄧ殑鏌ヨ?閫熷害瑕佽繙杩滈珮浜庡?鏉傝?岀紦鎱㈢殑澶氫釜琛ㄦ煡璇??傚湪鏈嶅姟鍣?瀹㈡埛鏈虹郴缁熶笂闇?瑕佹煡璇㈢殑琛ㄧ殑鏁版嵁缁忓父瀛樺偍鍦ㄥ?鎴锋満涓婁互鍑忓皯缃戠粶涓?殑鏁版嵁浼犺緭閫熷害銆?
骞朵笉鏄?墍鏈夌殑鏁版嵁搴撶?鐞嗙郴缁熼兘鏀?寔涓存椂琛ㄣ?傝?妫?鏌ヤ綘鐨勬暟鎹?簱绯荤粺鐨勬枃妗ｏ紝鐪嬬湅瀹冩槸鍚︽敮鎸佷复鏃惰〃
insert select璇?彞鐨勮?娉曟牸寮忓?涓嬶細
insert into table_name (col1,col2...) select col1,col2... from tablename where search_condition
鏈?川涓婃潵璇村畠鏄?皢涓?涓猻elect璇?彞鐨勮緭鍑虹粨鏋滃湪杈撳叆鍒板彟涓?涓?〃鏍间腑鍘伙紝鍦╥nsert value涓?殑瑙勫垯涔熼?傜敤浜巌nsert select璇?彞銆?
insert select璇?彞瑕佹眰浣犻伒寰??涓嬭?鍒欙細
1銆乻elect璇?彞涓嶈兘浠庤?鎻掑叆鏁版嵁鐨勮〃涓??夋嫨琛屻??
2銆乮nsert into涓?殑鍒楁暟蹇呴』涓巗elect璇?彞杩斿洖鐨勫垪鏁扮浉绛夈??
3銆乮nsert into涓?殑鏁版嵁绫诲瀷瑕佷笌select璇?彞杩斿洖鐨勬暟鎹?被鍨嬬浉鍚?
insert select璇?彞鐨勫彟澶栦竴涓?敤澶勬槸褰撲綘闇?瑕佸?琛ㄨ繘琛岄噸鏂板畾涔夋椂瀵硅〃杩涜?澶囦唤銆傝繖鏃堕渶瑕佷綘閫氳繃閫夋嫨鍘熷?琛ㄤ腑鐨勬墍鏈夋暟鎹?苟灏嗗叾鎻掑叆鍒颁竴涓?复鏃惰〃涓?潵瀹屾垚銆?
渚嬪?锛歩nsert into copy_table select * from origina_table;鐒跺悗浣犲氨鍙?互鏀惧績鍦板?鍘熷?琛ㄨ繘琛屽彉鏇翠簡銆?
鍑犱箮鎵?鏈夌殑鏁版嵁搴撶郴缁熼兘鍙?互瀵煎叆鎴栧?鍑篈SCLL鐮佺殑鏂囨湰鏂囦欢锛屽敖绠?QL璇?█娌℃湁杩欎釜鍔熻兘锛孲QL涓嶄細鍋氬緱姣斾粠涓?涓?┖鏁版嵁搴撳紑濮嬫洿濂姐??
娉?細浠庡?閮ㄦ暟鎹?簮涓??鍏ュ拰瀵煎嚭鏁版嵁鐪嬩笉鎳傜湅涓嶆噦銆?
绗?節澶?鍒涘缓鍜屾搷浣滆〃)
褰撲竴涓?紑鍙戜汉鍛樺湪寤虹珛鏁版嵁搴撴椂蹇呴』瑕佽?冭檻涓?浜涗簨鎯呫?傜?涓?浠朵簨鎯呮槸浣犵殑鏉冮檺绾у埆锛屽?鏋滀綘浣跨敤鐨勫叧绯诲瀷鏁版嵁搴撶?鐞嗙郴缁熸敮鎸佹潈闄愶紝浣犲繀椤荤‘璁や綘鏄?惁鍏锋湁绯荤粺绠＄悊璁剧疆鏉冮檺鎴栫郴缁熺?鐞嗗憳宸茬粡鍏佽?浣犳湁鍒涘缓鏁版嵁搴撶殑鏉冮檺銆?
璁稿?鍥犵礌浼氬?浣犺?璁℃暟鎹?簱閫犳垚褰卞搷锛屽?涓嬭竟鐨勶細
1銆佸畨鍏ㄩ棶棰橈紱2銆佺?鐩樼殑鍙?敤绌洪棿锛?銆佹暟鎹??绱㈠強鍝嶅簲鐨勯?熷害锛?銆佹暟鎹?洿鏂扮殑鏁板害锛?銆佸?琛ㄥ綊骞惰繑鍥炴暟鎹?殑閫熷害锛?銆丷DBMS瀵逛复鏃惰〃鐨勬敮鎸併??
褰撲綘鍦ㄨ繘琛屼唬鐮佸拰鏁版嵁搴撶殑缁撴瀯璁捐?鏃跺皢浣犵殑鏁版嵁缁撴瀯涓庝綘褰撴椂鐨勬剰鍥捐繘琛岃?缁嗙殑璁板綍鏄?潪甯搁噸瑕佺殑锛岃繖琚?О涓烘暟鎹?瓧鍏搞??
澶у?鏁癛DBMS涓?兘鎻愪緵浜嗙敓鎴愭暟鎹?瓧鍏哥殑宸ュ叿鍖呮垨鏈夎?鏄庢潵鍛婅瘔浣犲?浣曞畨瑁呭畠銆?
涓诲叧閿?瓧鍜屽?鍏抽敭瀛楋紝涓婚敭鍞?竴鎬э紝澶栭敭鍏宠仈鍏跺畠琛ㄥ瓧娈点??
寤鸿〃鍩烘湰璇?硶锛?
create table table_name (field1 datatype[NOT NULL],
field2 datatype[NOT NULL],
field3 datatype[NOT NULL]...);
寤鸿〃鏃秓racle鎵?鏀?寔鐨勬暟鎹?被鍨嬶細
char锛氬彲浠ュ瓨鍌ㄩ暱搴︿负1~255涓?瓧绗︾殑瀛楃?涓诧紝绌烘牸浼氳?濉?厖鍒板瓧绗︿覆鐨勫彸杈逛互淇濊瘉鍏跺唴瀹规弧瓒冲畾涔夌殑闀垮害銆?
date锛氬寘鎷?棩鏈熺殑涓栫邯銆佸勾銆佹湀銆佹棩銆佹椂銆佸垎銆佺?銆?
long锛氬彲浠ユ敮鎸侀暱杈?G鐨勫瓧绗︿覆銆?
long raw锛氬彲浠ュ瓨鍌ㄩ暱杈?G鐨勪簩杩涘埗鍐呭?銆?
number锛氶浂銆佹?鍊兼垨璐熷?肩殑瀹氱偣鎴栨诞鐐规暟銆?
raw锛氬彲浠ュ瓨鍌ㄩ暱涓嶈繃255涓?瓧鑺傜殑浜岃繘鍒朵唬鐮併??
rowid锛氱敤涓?涓?崄鍏?繘鍒剁殑鏁版潵鏍囨槑褰撳墠琛屽湪琛ㄥ唴鐨勫敮涓?鍦板潃銆?
varchar2锛氬彉闀跨殑瀛楁瘝鎴栨暟瀛楋紝闀垮害鍙?互浠?鍒?000銆?
娉?細long鏁版嵁绫诲瀷鍦ㄥ叾瀹冪殑鏁版嵁搴撶郴缁熶腑甯歌?绉颁负澶囨敞绫诲瀷锛屽畠涓昏?鐢ㄤ簬瀛樺偍澶ч噺鐨勫彲浠ュ湪绋嶅悗杩斿洖鐨勬枃鏈?唴瀹广??
long raw绫诲瀷鍦ㄥ叾瀹冩暟鎹?簱绯荤粺涓?父琚?О涓哄ぇ浜岃繘鍒剁被鍨?blob)锛屽畠鍙?互鐢ㄦ潵瀛樺偍鍥惧舰锛屽０闊炽?佽?棰戞暟鎹?紝灏界?鍏崇郴鍨嬫暟鎹?簱绠＄悊绯荤粺鏈?鍒濅笉鏄?负瀹冧滑鑰岃?璁＄殑锛屼絾鏄??濯掍綋鏁版嵁鍙?互瀛樺偍鍦╞lob绫诲瀷鐨勫瓧娈靛唴銆?
rowid甯哥敤鍦ㄥ彲浠ュ皢浣犵殑琛ㄤ腑鐨勬瘡涓?鏉¤?褰曞姞浠ュ敮涓?鐨勬爣璇嗙殑鍦哄悎锛岃?澶氬叧绯诲瀷鏁版嵁搴撶?鐞嗙郴缁熺敤counter(濡俛ccess)鎴杋dentity(sql server)鏉ヨ〃杈捐繖涓??蹇点??
null鍙?槸涓?涓?慨楗帮紝鍥犱负濡傛灉涓?涓?瓧娈电殑鍐呭?涓簄ull鐨勮瘽瀹為檯涓婃槸璇磋繖涓?瓧娈典腑浠?涔堜笢瑗夸篃娌℃湁銆傚湪寤虹珛琛ㄧ殑鏃跺?欙紝澶у?鏁版暟鎹?簱绠＄悊绯荤粺鍏佽?浣犵敤not null鏉ユ寚鏄庡瓧娈垫槸鍚︿负椋炵┖灞炴?э紝not null鐨勬剰鎬濆氨鏄?湪褰撳墠琛ㄧ殑璇ュ瓧娈典腑涓嶈兘鏈変换浣曡?褰曞瓨鍦ㄧ┖鍊笺??
鍦ㄩ?夋嫨涓诲叧閿?瓧娈垫椂鏈夊嚑涓?渶瑕佹敞鎰忕殑闂??锛屾垜浠?浘杩戣?杩囷紝oracle鎻愪緵浜嗕竴涓猺owid瀛楁?锛屽畠瀵逛簬姣忎竴琛屽潎浼氳嚜鍔ㄩ?掑?銆傚洜姝ら粯璁ゆ儏鍐典笅瀹冩?绘槸涓?涓?敮涓?鐨勬暟鍊硷紝灏唕owid瀛楁?浣滀负涓诲叧閿?瓧鏈夊緢澶氱悊鐢便??
鐢ㄤ竴涓?凡缁忓瓨鍦ㄧ殑琛ㄦ潵寤鸿〃锛?
浣跨敤鐨勬儏鍐碉細
1銆佸綋浣犲?琛ㄨ繘琛屼复鏃舵敼鍔ㄩ渶瑕佸皢鏁版嵁閫夊嚭鏃惰繖绉嶆柟娉曟槸寰堟湁鐢ㄧ殑銆?
2銆佸綋浣犺?鍒涘缓鐨勮〃涓庡凡鏈夌殑琛ㄧ被浼煎苟涓斿叾鍐呭?椤电被浼兼椂銆?
璇?硶锛?
create table new_table(field1,field2,field3)
as(select field1,field2,field3
from old_table <where ...>);
alert table璇?彞鍙?互甯?姪浣犲仛涓や欢浜嬶細
1銆佸姞鍏ヤ竴鍒楀埌宸茬粡瀛樺湪鐨勮〃涓???
2銆佷慨鏀瑰凡缁忓瓨鍦ㄧ殑琛ㄤ腑鐨勬煇涓?鍒椼??
璇?硶鏍煎紡濡備笅锛?
alter table table_name <add column_name data_type;|
modify column_name data_type;>
鎯虫妸鏌愪竴鍒楃敱not null鏀瑰彉涓簄ull鍙?互浣跨敤涓嬭竟鐨勮?娉曪細
alter table table_name modify(column_name data_type null);
濡傛灉鎯虫妸涓?鍒楃敱null鏀瑰彉涓簄ot null锛屼綘蹇呴』缁忚繃涓?涓嬫?楠わ細
1銆佺‘璁よ?鏀瑰彉鐨勫垪涓?湁娌℃湁null鍊笺??
2銆佸垹鎺変綘鎵?鍙戠幇鐨勪换浣昻ull鍊?鍒犻櫎銆佹洿鏂拌繖涓?绾?綍绛夌瓑)銆?
3銆佷娇鐢╝lter table鍛戒护銆?
DROP TABLE鍙?互浠庢暟鎹?簱涓?垹闄や竴涓?寚瀹氱殑琛ㄤ互鍙婁笌涔嬬浉鍏宠仈鐨勭储寮曞拰瑙嗗浘銆?
鎺ㄨ崘浣跨敤drop table owner.new_bills;
绗?崄澶?鍒涘缓瑙嗗浘鍜岀储寮?锛?
瑙嗗浘甯稿父琚?О涓鸿櫄琛?紝瀹冩槸鐢╟reate view璇?彞寤虹珛鐨勶紝鍦ㄤ娇鐢ㄨ?鍥句互鍚庝綘鍙?互瀵硅?鍥鹃噰鐢ㄥ?涓嬪懡浠わ細select,insert,input,update,delete
绱㈠紩鏄?笌纾佺洏涓婃暟鎹?殑瀛樺偍鏂瑰紡涓嶅悓鐨勫彟澶栦竴绉嶇粍缁囨暟鎹?殑鏂规硶锛岀储寮曠殑鐗逛緥鏄?〃涓??褰曚緷鎹?叾鍦ㄧ?鐩樹笂鐨勫瓨鍌ㄤ綅缃?樉绀猴紝绱㈠紩鍙?互鍦ㄨ〃鍐呭垱寤轰竴涓?垪鎴栧垪鐨勭粍鍚堬紝褰撳簲鐢ㄧ储寮曚互鍚庯紝鏁版嵁浼氭寜鐓т綘浣跨敤create index璇?彞鎵?瀹氫箟鐨勬帓搴忔柟寮忚繑鍥炵粰鐢ㄦ埛锛岄?氳繃瀵规?纭?殑銆佺壒瀹氱殑涓や釜琛ㄧ殑褰掑苟瀛楁?杩涜?绱㈠紩鍙?互鑾峰緱鏄庢樉鐨勫ソ澶勩??
褰撹〃涓?殑鏁版嵁鏀瑰彉浠ュ悗锛屼綘灏嗕細鍦ㄦ煡璇㈣?鍥炬椂鍙戠幇鐩稿簲鐨勬敼鍙橈紝瑙嗗浘骞朵笉鍗犵敤鏁版嵁搴撴垨琛ㄧ殑鐗╃悊绌洪棿銆?
璇?硶鏍煎紡濡備笅锛?
create view <view_name> [(column1,column2...)] as select <table_name column_names> from <table_name>
褰撳垱寤鸿?鍥惧悗锛屽畠瀹為檯涓婃槸涓?涓?櫄琛ㄣ??
渚嬪?锛歝reate view creditcard_debts as select * from debts where account_id=4;鍒涘缓浜嗙储寮?
鐒跺悗鏌ヨ?璇?彞select * from creditcard_debts;绛夊悓浜巗elect * from debts where account_id=4;
create view涔熷厑璁镐綘浠庤〃涓??夋嫨鐗瑰畾鐨勫垪鍒拌?鍥句腑锛屼緥濡俢reate view company_info (name,state) as select * from company;鐒跺悗鏌ヨ?鐨勬椂鍊欏氨鍙?樉绀簄ame鍜宻tate涓?
鍒椼??
濡傛灉鏁版嵁閲忚緝澶э紝鍒涘缓绱㈠紩浠ュ悗鏌ヨ?鏁版嵁鐨勯?熷害浼氭瘮鐩存帴鏌ヨ?蹇?緢澶氥??
鍙?互鐢?鍙峰皢鍑犵粍鏁版嵁缁勫悎璧锋潵锛岀劧鍚庤嚜宸卞懡鍚嶈?鍥惧悕绉帮紝渚嬪?锛歝reate view envelope (company,mailing_address) as select name,address+" "+city+","+state
from company;
sql鍦ㄦ墽琛岃繃绋嬩腑锛岄?鍏堜細鏌ユ壘鎵?鏌ヨ?鐨勬槸涓嶆槸琛?紝濡傛灉鎵句笉鍒拌〃锛岀劧鍚庡畠灏变細鍘昏?鍥鹃噷闈㈠幓鎵剧湅鏈夋病鏈夈??
姣旇緝澶嶆潅鐐圭殑瑙嗗浘鍒涘缓锛?
create view example(state,total_bills,total_amount) as select distinct company.state,count(bills.name),sum(bills.amount) from bills,company group by company.state having bills.name = company.name;
鍦ㄨ?鍥剧殑select璇?彞涓?娇鐢ㄧ害鏉熸槸蹇呯劧鐨勶紝鍦ㄤ娇鐢╯elect璇?彞涓?彲浠ュ簲鐢ㄤ笅杈硅繖涓や釜瑙勫垯锛?
1銆佷綘涓嶈兘鐢╱nion鎿嶄綔銆?
2銆佷綘涓嶈兘浣跨敤order by瀛愬彞锛屼絾鏄?湪瑙嗗浘涓?娇鐢╣roup by瀛愬彞鍙?互鏈塷rder by瀛愬彞鐩稿悓鐨勫姛鑳姐??
浣犲彲浠ュ湪鏁版嵁搴撶殑涓?涓?垨澶氫釜琛ㄤ腑浣跨敤瑙嗗浘锛屼篃鍙?互鍦⊿QL鍜屾暟鎹?簱搴旂敤绋嬪簭涓?娇鐢ㄨ櫄琛ㄣ?傚垱寤鸿?鍥句互鍚庯紝浣犲彲浠ョ敤insert,update,delete璇?彞鏉ユ洿鏂般?佹彃鍏ャ?佸垹闄よ?鍥句腑鐨勬暟鎹???
涓嬭竟渚嬪瓙鏄剧ず浜嗗?浣曞湪瑙嗗浘涓?搷浣滄暟鎹?細
create view late_payment as select * from bills;鍒涘缓瑙嗗浘
update late_payment set amount = amount*1.10;鏇存柊瑙嗗浘
delete from late_payment where account_id = 4;浠庤?鍥句腑鍒犻櫎涓?琛?
update late_payment set new_total = new_total + 10 where new_total > 100;鏇存柊瑙嗗浘
濡傛灉浣犲湪鍒涘缓瑙嗗浘鏃朵娇鐢ㄤ簡dintinct瀛愬彞锛岄偅涔堜綘灏变笉鑳芥彃鍏ユ垨鏇存柊杩欎釜瑙嗗浘涓?殑璁板綍銆?
瑙嗗浘瀵逛簬瀹炵幇鏁版嵁搴撶殑瀹夊叏鏈夌浉褰撳ぇ鐨勭敤澶勩??
鍦ㄥ崟浣嶆崲绠椾腑浣跨敤瑙嗗浘锛氫緥濡傚姞鎷垮ぇ鐨勭敤鎴蜂笉鎯抽?绻佺殑杩涜?缇庡厓涓庡姞鎷垮ぇ鍏冧箣闂寸殑杞?崲宸ヤ綔锛?
create view canadian_bills(name,can_amount) as select name,amount/1.10 from bills;
褰撴妸涓?涓?煡璇㈠垎瑙ｆ垚鍑犱釜瑙嗗浘浠ュ悗锛屾渶鍚庣殑鏌ヨ?灏遍潪甯哥畝鍗曚簡锛屽綋鐒讹紝浣跨敤涓?涓??鍥句篃缁忓父鏄?繀椤荤殑銆?
鍒犻櫎瑙嗗浘锛歞rop view vie_name;
娉?細drop view鍛戒护浼氫娇鎵?鏈変笌drop瑙嗗浘鐩稿叧鑱旂殑瑙嗗浘涓嶈兘姝ｅ父杩愯?锛屼竴浜涙暟鏁版嵁搴撶郴缁熺敋鑷冲皢鎵?鏈変笌瑕乨rop鐨勮?鍥剧浉鍏宠仈鐨勮?鍥句篃鍒犻櫎鎺夈??
浣犲彲浠ュ垹闄や竴涓??鍥捐?屼笉褰卞搷浠讳綍涓?涓?湡瀹炵殑琛?紝杩欎篃灏辨槸涓轰粈涔堟垜浠?皢瑙嗗浘绉颁负铏氳〃鐨勫師鍥犮??
鍦ㄦ暟鎹?簱涓?娇鐢ㄧ储寮曞彲浠ヨ?SQL浣跨敤鐩存帴璁块棶鏂瑰紡銆係QL閲囩敤鏍戝舰缁撴瀯鏉ュ瓨鍌ㄥ拰杩斿洖绱㈠紩鏁版嵁锛岀敤浠ユ寚绀虹殑鏁版嵁瀛樺偍鍦ㄦ爲鐨勬渶鏈??(涔熷氨鏄?彾瀛?锛屽畠浠??绉颁负缁撶偣(涔熷彲浠ュ彨鍙跺瓙)銆傛瘡涓?涓?粨鐐逛腑鏈変竴涓?寚鍚戝叾瀹冪粨鐐圭殑鎸囬拡锛岀粨鐐瑰乏杈圭殑鍊煎彧鏄?畠鐨勫弻浜茬粨鐐癸紝缁撶偣鍙宠竟鐨勫?煎垯鏄??瀛愮粨鐐规垨鍙跺瓙銆?
绱㈠紩璇?硶锛?
create index index_name on table_name(column_name1[column_name2],...);
涓嶅悓鐨勬暟鎹?簱鍒涘缓绱㈠紩璇?彞鍩烘湰閮戒笉鍚岋紝浣嗘槸鎵?鏈夎В閲婂櫒鏈夊嚑鐐规槸鐩稿悓鐨勶紝瀹冧滑鐨勫熀鏈?紑濮嬭?鍙ラ兘鏄?細
create index index_name on table_name(column_name,...);
渚嬪?鍦ㄤ竴涓?〃閲岄潰鐨勬煇涓?瓧娈靛垱寤轰簡绱㈠紩锛岄偅涔堟煡璇㈠嚭鏉ョ殑鏁版嵁閮芥槸鎸夋?瀛楁?鎺掑簭鐨勩??
鏈変簡绱㈠紩鎺掑簭涓轰粈涔堣繕闇?瑕乷rder by鎺掑簭鍛?紵涓嶅悓涔嬪?鍦ㄤ簬褰撲綘浣跨敤order by瀛愬彞鏃舵瘡娆¤繍琛屽畠閮介渶瑕侀噸鏂拌繘琛屾帓搴忥紝鑰屽綋浣犱娇鐢ㄧ储寮曠殑鏃跺?欙紝鏁版嵁搴撲細寤虹珛涓?涓?墿鐞嗙储寮曞?璞?灏辨槸鍓嶈竟鎻愬埌鐨勬爲缁撴瀯)锛岃?屽湪浣犳瘡娆¤繍琛屾煡璇㈡椂閮借?闂?悓涓?涓?储寮曘??
1銆佸?浜庡皬琛ㄦ潵璇达紝浣跨敤绱㈠紩瀵逛簬鎬ц兘涓嶄細鏈変换浣曟彁楂樸??
2銆佸綋浣犵殑绱㈠紩鍒椾腑鏈夋瀬澶氱殑涓嶅悓鐨勬暟鎹?拰绌哄?兼椂绱㈠紩浼氫娇鎬ц兘鏈夋瀬澶х殑鎻愰珮銆?
3銆佷笉瑕佸湪缁忓父闇?瑕佹洿鏂版垨淇?敼鐨勫瓧娈靛垱寤虹储寮曪紝鏇存柊绱㈠紩鐨勫紑閿?浼氶檷浣庝綘鎵?鏈熸湜鑾峰緱鐨勬?ц兘銆?
sql涔熷厑璁镐綘瀵瑰?涓?瓧娈佃繘琛岀储寮曪紝杩欑?绱㈠紩琚?О涓哄?鍚堢储寮曪紝渚嬪?锛歝reate index id_cmpd_index on bills(account_id,amount);
褰撲娇鐢ㄥ?鍚堢储寮曟椂锛岃?鎶婃渶鍙?兘閫夋嫨鐨勫瓧娈垫斁鍦ㄥ墠杈癸紝涔熷氨鏄??锛屾妸浣犳渶缁忓父鍦ㄦ煡璇?腑浣跨敤鐨勫瓧娈垫斁鍦ㄦ渶鍓嶈竟銆?
鍦╞ills琛ㄤ腑鍒涘缓涓?涓?储寮曚互瀵筧mount瀛楁?杩涜?闄嶅簭鎺掑垪锛歝reate index desc_amount on bills(amount desc);閫氬父鎯呭喌涓嬪?鏋滀笉鎸囨槑锛岄粯璁や細鎸夊崌搴忔帓鍒椼??
瑙嗗浘鏄?竴绉嶈櫄琛?紝瑙嗗浘鏄?彁渚涚粰鐢ㄦ埛鐨勬暟鎹?笌鍏跺湪鏁版嵁搴撶殑鐪熷疄闈㈣矊涓嶇浉鍚岀殑涓?绉嶆柟娉曘?備綘鍙?互灏嗚?鍥捐?涓轰竴涓?父瑙勭殑琛ㄦ潵鎵ц?鎻掑叆銆佸垹闄ゃ?佹洿鏂板拰閫夋嫨鎿嶄綔銆?
绗?崄涓?澶?浜嬬墿澶勭悊鎺у埗)
浜嬪姟鏄?寚鍦ㄩ?昏緫涓婂繀椤诲畬鎴愮殑涓?鍛戒护搴忓垪鐨勫崟浣嶏紝鍗曚綅宸ヤ綔鏈熸槸鎸囦簨鍔＄殑寮?濮嬪拰缁撴潫鏃舵湡銆傚?鏋滃湪浜嬪姟涓?骇鐢熺殑閿欒?锛岄偅涔堟暣涓?繃绋嬪彲浠ユ牴鎹?渶瑕佽?缁堟?锛屽?鏋滄瘡涓?浠朵簨閮芥槸姝ｇ‘鐨勶紝閭ｄ箞缁撴灉灏嗕細琚?繚瀛樺埌鏁版嵁搴撲腑銆?
鎵?鏈夋敮鎸佷簨鍔″?鐞嗙殑绯荤粺閮藉繀椤讳互涓?绉嶅噯纭?殑璇?硶鏉ュ憡璇夌郴缁熶竴椤逛簨鍔℃槸濡備綍寮?濮嬬殑(涓嶈?蹇樿?浜嬪姟澶勭悊鍙?槸宸ヤ綔鐨勯?昏緫鍒嗙粍锛屽畠鏈夎嚜宸辩殑寮?濮嬪拰缁撴潫)锛屽湪浣跨敤oracle7鏃讹紝瀹冪殑璇?硶褰㈠紡濡備笅锛?
set transaction {read only | use rollback segment segment}
鍦ㄨ?澶氭儏鍐典笅杞戒竴涓?伐浣滆繃绋嬩腑蹇呴』瑕佹眰鏁版嵁淇濇寔鏈夋晥锛岃?屼笉浠呬粎鏄??鍗曚釜璇?彞銆俹racle鍏佽?鐢ㄦ埛鐢╯et transaction鏉ユ寚瀹氫簨鍔＄殑寮?濮嬶紝濡傛灉浣犳兂妫?鏌?ill tuner鐨勪俊鎭?苟涓旇?淇濊瘉鏁版嵁鍦ㄨ繖涔嬩腑鏄?笉鑳芥敼鍙樼殑锛岄偅涔堜綘鍙?互浣跨敤濡備笅璇?彞锛?
set transaction tead only;
select * from customers where name='Bill Turner';
commit;
鍦╫racle璇?硶涓?粨鏉熶簨鍔″?鐞嗚?鍙ョ殑璇?硶濡備笅锛?
commit[work] [comment 'text' | force 'text'[,integer]];
commit鍛戒护灏嗕繚瀛樺湪涓?椤逛簨鍔′腑鎵?杩涜?鐨勬墍鏈夌殑鏀瑰彉锛屽湪寮?濮嬩竴椤逛簨鍔′箣鍓嶈?鍏堣繍琛宑ommit鍛戒护浠ョ‘淇濆湪涔嬪墠娌℃湁浜嬪姟琚?‘璁ゃ??
渚嬪?锛?
commit;
set transaction read only;
select * from customers where name='Bill Turner';
commit;
鍦ㄤ竴涓?簨鍔″?鐞嗙殑杩囩▼涓?紝甯稿父浼氳繍琛屼竴浜涢敊璇??鏌ヤ互纭??鍦ㄨ繃绋嬩腑鏄?惁璇?彞鏄?繍琛屾垚鍔熴?備綘鍙?互浣跨敤rollback璇?彞鏉ユ挙閿?浜嬪姟涓?墍鍋氱殑姣忎竴椤瑰伐浣滐紝鍗充究宸ヤ綔鏄?垚鍔熺殑浣犱篃鍙?互鎾ら攢銆備絾鏄?紝杩欏繀椤绘槸鍦╟ommit涔嬪墠锛宺ollback璇?彞蹇呴』鍦ㄤ竴涓?簨鍔′箣涓?繍琛岋紝瀹冨彲浠ヤ竴鐩存挙閿?鍒颁簨鍔＄殑寮?濮嬶紝涔熷氨鏄??锛屾暟鎹?簱浼氫竴鐩磋繑鍥炲埌浜嬪姟澶勭悊鍒氬紑濮嬬殑鐘舵?併?俹racke7涓?殑璇?硶濡備笅锛?
rollback[work] [to[savepoint] savepoint | force 'text']
渚嬪?锛?
set transaction;
insert into customers values ('Bubba MacDowell','222 Blue Lake Way','Austin','TX',39874,8);
rollback;
select * from customers;
rollback璇?彞灏嗕細缁堟?鏁翠釜浜嬪姟锛屽綋瀛樺湪宓屽?浜嬪姟鏃讹紝rollback灏嗕細缁堟?鎺夊叏閮ㄤ簨鍔★紝绯荤粺灏嗕細杩斿洖鍒颁簨鍔″紑濮嬬殑鏈?鍒濈姸鎬併?傚?鏋滃綋鍓嶆病鏈夋椿鍔ㄧ殑浜嬪姟鏃讹紝rollback鎴朿ommit璇?彞灏嗕笉浼氬?鏁版嵁搴撲骇鐢熶换浣曚綔鐢?浣犲彲浠ヨ?涓鸿繖鏄?竴涓?棤鏁堢殑鍛戒护)銆?
鍦ㄤ簨鍔′娇鐢ㄤ腑rollback鍙?互鍙栨秷鏁翠釜鏄?簨鍔★紝浣嗘槸浣犱篃鍙?互鍦ㄤ綘鐨勪簨鍔″綋涓?娇鐢ㄨ?鍙ヨ繘琛屸?滈儴鍒嗗湴纭??鈥濄?傚湪Sybase鍜孫racke涓?兘鍏佽?浣犲湪褰撳墠浜嬪姟涓??涓?涓?繚瀛樼偣锛屼粠杩欎竴鐐瑰紑濮嬶紝濡傛灉浣犱娇鐢ㄤ簡rollback鍛戒护锛岄偅涔堢郴缁熷皢浼氬洖鍒颁繚瀛樼偣鏃剁殑鐘舵?侊紝鑰屽湪淇濆瓨鐐逛箣鍓嶇殑璇?彞灏嗕細寰楀埌纭??锛屽湪oracle涓?垱寤轰竴涓?繚瀛樼偣鐨勮?娉曟牸寮忓?涓嬶細
savepoint savepoint_name;
渚嬪?锛?
set transaction;
update balances set curr_bal = 25000 where account_id=5;
savepoint save_it;
delete from balances where account_id=5;
rollback to savepoint save_it;
commit;
select * from balances;
鍦ㄤ笂杈圭殑渚嬪瓙涓?垱寤轰簡涓?涓?彨save_it鐨勪繚瀛樼偣锛寀pdate璇?彞鏇存柊浜嗙粨绠楀钩琛¤〃涓?殑curr_bal鍒楋紝浣犲湪鍏跺悗璁剧疆浜嗕竴涓?繚瀛樼偣锛屽湪淇濆瓨涔嬪悗锛屼綘鍙堣繍琛屼簡delete鍛戒护锛岀郴缁熼??鍥炲埌浜嗕繚瀛樼偣澶勶紝涔嬪悗浣犲?浜嬪姟鐢╟ommit鍛戒护杩涜?浜嗙‘璁わ紝缁撴灉鎵?鏈夌殑淇濆瓨鐐逛箣鍓嶇殑鍛戒护寰楀埌浜嗙‘璁ゃ?傚?鏋滀綘鍦ㄥ叾鍚庡張浣跨敤浜唕ollback鍛戒护锛岄偅涔堝皢浼氬彇娑堝綋鍓嶇殑浜嬪姟鑰屼笉浼氭湁浠讳綍鐨勬敼鍙樸??