软件开发培训班 >> 编程开发 >> MySQL


mysql 数据插入和更新及删除详情

1銆佹彃鍏?/h2> 
INSERT INTO customers(
customers.cust_address,
customers.cust_city,
customers.cust_state,
customers.cust_zip,
customers.cust_country,
customers.cust_contact,
customers.cust_email
)
VALUES('zhangsan','good','111','ca','dasdsa','usa',NULL)

杩欐牱灏辨彃鍏ユ垚鍔熶簡銆?/p>

鍦ㄦ彃鍏ョ殑鏃跺�欎负浜嗗畨鍏ㄨ捣瑙侊紝閭ｄ箞姣忔鎻掑叆鐨勬椂鍊欏簲璇ュ啓濂藉垪鍚嶃�備笉绠′娇鐢ㄥ摢绉?code>INSERT璇硶锛岄兘蹇呴』缁欏嚭VALUES鐨勬纭暟鐩�傚鏋滀笉鎻愪緵鍒楀悕锛屽垯蹇呴』缁欐瘡涓〃鍒楁彁渚涗竴涓�笺�傚鏋滄彁渚涘垪鍚嶏紝鍒欏繀椤诲姣忎釜鍒楀嚭鐨勫垪缁欏嚭涓�涓�笺�傚鏋滀笉杩欐牱锛屽皢浜х敓涓�鏉￠敊璇秷鎭紝鐩稿簲鐨勮鎻掑叆涓嶆垚鍔熴�?/p>

鐪佺暐鍒?濡傛灉琛ㄧ殑瀹氫箟鍏佽锛屽垯鍙互鍦↖NSERT鎿嶄綔涓渷鐣ユ煇浜涘垪銆?/p>

鐪佺暐鐨勫垪蹇呴』婊¤冻浠ヤ笅鏌愪釜鏉′欢

  • 璇ュ垪瀹氫箟涓哄厑璁窷ULL鍊硷紙鏃犲�兼垨绌哄�硷級銆?/li>
  • 鍦ㄨ〃瀹氫箟涓粰鍑洪粯璁ゅ�笺�傝繖琛ㄧず濡傛灉涓嶇粰鍑哄�硷紝灏嗕娇鐢ㄩ粯璁ゅ�?/li>

鎻愰珮鏁翠綋鎬ц兘 鏁版嵁搴撶粡甯歌澶氫釜瀹㈡埛璁块棶锛屽澶勭悊浠�涔堣姹備互鍙婄敤浠�涔堟搴忓鐞嗚繘琛岀鐞嗘槸MySQL鐨勪换鍔°�?/p>

INSERT鎿嶄綔鍙兘寰堣�楁椂锛堢壒鍒槸鏈夊緢澶氱储寮曢渶瑕佹洿鏂版椂锛夛紝鑰屼笖瀹冨彲鑳介檷浣庣瓑寰呭鐞嗙殑SELECT璇彞鐨勬�ц兘銆?/p>

濡傛灉鏁版嵁妫�绱㈡槸鏈�閲嶈鐨勶紙閫氬父鏄繖鏍凤級锛屽垯浣犲彲浠ラ�氳繃鍦?code>INSERT鍜?code>INTO涔嬮棿娣诲姞鍏抽敭瀛?code>LOW_PRIORITY锛屾寚绀?code>MySQL闄嶄綆INSERT璇彞鐨勪紭鍏堢骇銆?/p> 

INSERT INTO customers(
customers.cust_address,
customers.cust_city,
customers.cust_state,
customers.cust_zip,
customers.cust_country,
customers.cust_contact,
customers.cust_email
)
VALUES('zhangsan','good','111','ca','dasdsa','usa',NULL)
锛?'zhangsan','good','111','ca','dasdsa','usa',NULL)
锛?'zhangsan','good','111','ca','dasdsa','usa',NULL)
锛?'zhangsan','good','111','ca','dasdsa','usa',NULL)

濡傛灉鎯虫彃鍏ュ琛岋紝鍙渶瑕佸姞鍏ュ涓?code>values鍗冲彲銆?/p>

INSERT涓�鑸敤鏉ョ粰琛ㄦ彃鍏ヤ竴涓寚瀹氬垪鍊肩殑琛屻�備絾鏄紝INSERT杩樺瓨鍦ㄥ彟涓�绉嶅舰寮忥紝鍙互鍒╃敤瀹冨皢涓�鏉?code>SELECT璇彞鐨勭粨鏋滄彃鍏ヨ〃涓�?/p>

杩欏氨鏄墍璋撶殑INSERT SELECT锛岄【鍚嶆�濅箟锛屽畠鏄敱涓�鏉?code>INSERT璇彞鍜屼竴鏉?code>SELECT璇彞缁勬垚鐨勩�?/p>

鍋囧浣犳兂浠庡彟涓�琛ㄤ腑鍚堝苟瀹㈡埛鍒楄〃鍒颁綘鐨?code>customers琛ㄣ�備笉闇�瑕佹瘡娆¤鍙栦竴琛岋紝鐒跺悗鍐嶅皢瀹冪敤INSERT鎻掑叆锛?strong>鍙互濡備笅杩涜锛?/strong>

insert into customers(xx,xx,xx)
select xx,xx,xx
from newcustomers

宸笉澶氬氨鏄繖鏍风殑浜嗐�?/p>

INSERT SELECT涓殑鍒楀悕 涓虹畝鍗曡捣瑙侊紝杩欎釜渚嬪瓙鍦?code>INSERT鍜?code>SELECT璇彞涓娇鐢ㄤ簡鐩稿悓鐨勫垪鍚嶃�?/p>

浣嗘槸锛屼笉涓�瀹氳姹傚垪鍚嶅尮閰嶃�備簨瀹炰笂锛?code>MySQL鐢氳嚦涓嶅叧蹇?code>SELECT杩斿洖鐨勫垪鍚嶃�?/p>

瀹冧娇鐢ㄧ殑鏄垪鐨勪綅缃紝鍥犳SELECT涓殑绗竴鍒楋紙涓嶇鍏跺垪鍚嶏級灏嗙敤鏉ュ～鍏?/p>

琛ㄥ垪涓寚瀹氱殑绗竴涓垪锛岀浜屽垪灏嗙敤鏉ュ～鍏呰〃鍒椾腑鎸囧畾鐨勭浜屼釜鍒楋紝濡傛绛夌瓑銆?/p>

杩欏浜庝粠浣跨敤涓嶅悓鍒楀悕鐨勮〃涓鍏ユ暟鎹槸闈炲父鏈夌敤鐨勩�?/p>

2銆佹洿鏂?/h2>

涓轰簡鏇存柊锛堜慨鏀癸級琛ㄤ腑鐨勬暟鎹紝鍙娇鐢?code>UPDATE璇彞銆?/p>

鍙噰鐢ㄤ袱绉嶆柟寮忎娇鐢?code>UPDATE锛?/strong>

  • 鏇存柊琛ㄤ腑鐗瑰畾琛岋紱
  • 鏇存柊琛ㄤ腑鎵�鏈夎銆?/li> 
UPDATE customers
SET cust_email = 'email@qq.com'
where cust_id = 10005;

涓婇潰杩欏氨鏄洿鏂拌鍙ヤ簡銆?/p>

濡傛灉鏇存柊澶氫釜鍒楃殑璇?

UPDATE customers
SET cust_email = 'email@qq.com',
cust_name = 'game'
where cust_id = 10005;

IGNORE鍏抽敭瀛?濡傛灉鐢?code>UPDATE璇彞鏇存柊澶氳锛屽苟涓斿湪鏇存柊杩欎簺琛屼腑鐨勪竴琛屾垨澶氳鏃跺嚭涓�涓幇閿欒锛屽垯鏁翠釜UPDATE鎿嶄綔琚彇娑堬紙閿欒鍙戠敓鍓嶆洿鏂扮殑鎵�鏈夎琚仮澶嶅埌瀹冧滑鍘熸潵鐨勫�硷級銆?/p>

涓哄嵆浣挎槸鍙戠敓閿欒锛屼篃缁х画杩涜鏇存柊锛屽彲浣跨敤IGNORE鍏抽敭瀛楋紝濡備笅鎵�绀猴細 UPDATE IGNORE customers鈥?/code>

杩欎箞璇存槑涓�涓嬶紝濡傛灉纭畾鍙洿鏂颁竴琛岀殑璇濓紝閭ｄ箞寤鸿鏄洿鏂版槸鐢?code>update 1杩欑妯″紡锛屾寚鏄庡彧鏇存柊涓�琛屻�?/p>

涓轰簡浠庝竴涓〃涓垹闄わ紙鍘绘帀锛夋暟鎹紝浣跨敤DELETE璇彞銆傚彲浠ヤ袱绉嶆柟寮忎娇鐢?code>DELETE锛?/p>

3銆佸垹闄?/h2>
  • 浠庤〃涓垹闄ょ壒瀹氱殑琛岋紱
  • 浠庤〃涓垹闄ゆ墍鏈夎銆?/li>

鍒犻櫎琛ㄧ殑鍐呭鑰屼笉鏄〃 DELETE璇彞浠庤〃涓垹闄よ锛岀敋鑷虫槸
鍒犻櫎琛ㄤ腑鎵�鏈夎銆備絾鏄紝DELETE涓嶅垹闄よ〃鏈韩銆?/p>

鏇村揩鐨勫垹闄?濡傛灉鎯充粠琛ㄤ腑鍒犻櫎鎵�鏈夎锛屼笉瑕佷娇鐢?code>DELETE銆?/p>

鍙娇鐢?code>TRUNCATE TABLE璇彞锛屽畠瀹屾垚鐩稿悓鐨勫伐浣滐紝浣嗛�熷害鏇村揩锛?code>TRUNCATE瀹為檯鏄垹闄ゅ師鏉ョ殑琛ㄥ苟閲嶆柊鍒涘缓涓�涓〃锛岃�屼笉鏄�愯鍒犻櫎琛ㄤ腑鐨勬暟鎹級

鍒版杩欑瘒鍏充簬mysql 鏁版嵁鎻掑叆鍜屾洿鏂板強鍒犻櫎璇︽儏鐨勬枃绔犲氨浠嬬粛鍒拌繖浜?鏇村鐩稿叧mysql 鏁版嵁鎻掑叆鍜屾洿鏂板強鍒犻櫎鍐呭璇锋悳绱㈣剼鏈箣瀹朵互鍓嶇殑鏂囩珷鎴栫户缁祻瑙堜笅闈㈢殑鐩稿叧鏂囩珷甯屾湜澶у浠ュ悗澶氬鏀寔鑴氭湰涔嬪锛?/p>