é¦å
ï¼å表çUPDATEè¯å¥ï¼
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
å
¶æ¬¡ï¼å¤è¡¨çUPDATEè¯å¥ï¼
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
UPDATEè¯æ³å¯ä»¥ç¨æ°å¼æ´æ°åæ表è¡ä¸çååã
SETåå¥æ示è¦ä¿®æ¹åªäºååè¦ç»äºåªäºå¼ãWHEREåå¥æå®åºæ´æ°åªäºè¡ã
å¦æ没æWHEREåå¥ï¼åæ´æ°ææçè¡ãå¦ææå®äºORDER BYåå¥ï¼åæç
§è¢«æå®ç顺åºå¯¹è¡è¿è¡æ´æ°ã
LIMITåå¥ç¨äºç»å®ä¸ä¸ªéå¼ï¼éå¶å¯ä»¥è¢«æ´æ°çè¡çæ°ç®ã
UPDATEè¯å¥æ¯æ以ä¸ä¿®é¥°ç¬¦ï¼
1ï¼å¦ææ¨ä½¿ç¨LOW_PRIORITYå
³é®è¯ï¼åUPDATEçæ§è¡è¢«å»¶è¿äºï¼ç´å°æ²¡æå
¶å®ç客æ·ç«¯ä»è¡¨ä¸è¯»å为æ¢ã
2ï¼å¦ææ¨ä½¿ç¨IGNOREå
³é®è¯ï¼åå³ä½¿å¨æ´æ°è¿ç¨ä¸åºç°é误ï¼æ´æ°è¯å¥ä¹ä¸ä¼ä¸æã
å¦æåºç°äºéå¤å
³é®åå²çªï¼åè¿äºè¡ä¸ä¼è¢«æ´æ°ãå¦æå被æ´æ°åï¼æ°å¼ä¼å¯¼è´æ°æ®è½¬åé误ï¼åè¿äºè¡è¢«æ´æ°ä¸ºææ¥è¿çåæ³çå¼ã
å¦ææ¨å¨ä¸ä¸ªè¡¨è¾¾å¼ä¸éè¿tbl_name访é®ä¸åï¼åUPDATE使ç¨åä¸çå½åå¼ã
ä¾å¦ï¼æå¹´é¾å设置为æ¯å½åå¼å¤ä¸ï¼
代ç å¦ä¸:
mysql> UPDATE persondata SET age=age+1;
UPDATEèµå¼è¢«ä»å·¦å°å³è¯ä¼°ã
ä¾å¦ï¼å¯¹å¹´é¾åå åï¼ç¶ååè¿è¡å¢å ï¼
代ç å¦ä¸:
mysql> UPDATE persondata SET age=age*2, age=age+1;
å¦ææ¨æä¸å设置为å
¶å½åå«æçå¼ï¼åMySQLä¼æ³¨æå°è¿ä¸ç¹ï¼ä½ä¸ä¼æ´æ°ã
å¦ææ¨æ被已å®ä¹ä¸ºNOT NULLçåæ´æ°ä¸ºNULLï¼å该å被设置å°ä¸åç±»å对åºçé»è®¤å¼ï¼å¹¶ä¸ç´¯å è¦åæ°ã
对äºæ°åç±»åï¼é»è®¤å¼ä¸º0ï¼å¯¹äºå符串类åï¼é»è®¤å¼ä¸ºç©ºå符串('')ï¼å¯¹äºæ¥æåæ¶é´ç±»åï¼é»è®¤å¼ä¸ºâzeroâå¼ã
UPDATEä¼è¿åå®é
被æ¹åçè¡çæ°ç®ãMysql_info() C APIå½æ°å¯ä»¥è¿å被å¹é
å被æ´æ°çè¡çæ°ç®ï¼ä»¥åå¨UPDATEè¿ç¨ä¸äº§ççè¦åçæ°éã
æ¨å¯ä»¥ä½¿ç¨LIMIT row_countæ¥éå®UPDATEçèå´ãLIMITåå¥æ¯ä¸ä¸ªä¸è¡å¹é
çéå®ã
åªè¦åç°å¯ä»¥æ»¡è¶³WHEREåå¥çrow_countè¡ï¼å该è¯å¥ä¸æ¢ï¼ä¸è®ºè¿äºè¡æ¯å¦è¢«æ¹åã
å¦æä¸ä¸ªUPDATEè¯å¥å
æ¬ä¸ä¸ªORDER BYåå¥ï¼åæç
§ç±åå¥æå®ç顺åºæ´æ°è¡ã
æ¨ä¹å¯ä»¥æ§è¡å
æ¬å¤ä¸ªè¡¨çUPDATEæä½ãtable_referencesåå¥ååºäºå¨èåä¸å
å«ç表ã
ä¾åï¼
代ç å¦ä¸:
SQL>UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
说æï¼ä»¥ä¸ä»£ç æ¾ç¤ºåºäºä½¿ç¨éå·æä½ç¬¦çå
é¨èåï¼ä½æ¯multiple-table UPDATEè¯å¥å¯ä»¥ä½¿ç¨å¨SELECTè¯å¥ä¸å
许çä»»ä½ç±»åçèåï¼æ¯å¦LEFT JOINã
注éï¼ä¸è½æORDER BYæLIMITä¸multiple-table UPDATEåæ¶ä½¿ç¨ã
å¨ä¸ä¸ªè¢«æ´æ¹çmultiple-table UPDATEä¸ï¼æäºå被å¼ç¨ãæ¨åªéè¦è¿äºåçUPDATEæéãæäºå被读åäºï¼ä½æ¯æ²¡è¢«ä¿®æ¹ãæ¨åªéè¦è¿äºåçSELECTæéã
å¦ææ¨ä½¿ç¨çmultiple-table UPDATEè¯å¥ä¸å
å«å¸¦æå¤é®éå¶çInnoDB表ï¼åMySQLä¼å符å¤ç表ç顺åºå¯è½ä¸ä¸ä¸å±çº§å
³ç³»ç顺åºä¸åã
å¨æ¤æ
åµä¸ï¼è¯å¥æ æ并被 åæ»ãåæ¶ï¼æ´æ°ä¸ä¸ªåä¸è¡¨ï¼å¹¶ä¸ä¾é ON UPDATEåè½ã
该åè½ç±InnoDBæä¾ï¼ç¨äºå¯¹å
¶å®è¡¨è¿è¡ç¸åºçä¿®æ¹ã
ç®åï¼ä¸è½å¨ä¸ä¸ªåæ¥è¯¢ä¸æ´æ°ä¸ä¸ªè¡¨ï¼åæ¶ä»åä¸ä¸ªè¡¨ä¸éæ©ã
updateè¯å¥çå ç§åºæ¬ç¨æ³
A. 使ç¨ç®åç UPDATE
ä¸å示ä¾è¯´æå¦æä» UPDATE è¯å¥ä¸å»é¤ WHERE åå¥ï¼ææçè¡ä¼åå°ä»ä¹å½±åã
ä¸é¢è¿ä¸ªä¾å说æï¼å¦æ表 publishers ä¸çææåºç社å°æ»é¨æ¬è¿å°ä½æ²»äºå·çäºç¹å
°å¤§å¸ï¼è¡¨ publishers å¦ä½æ´æ°ã
代ç å¦ä¸:
UPDATE publishers
SET city = 'Atlanta', state = 'GA'
æ¬ç¤ºä¾å°ææåºçåçååå为 NULLã
å¤å¶ä»£ç 代ç å¦ä¸:
UPDATE publishers
SET pub_name = NULL
ä¹å¯ä»¥å¨æ´æ°ä¸ä½¿ç¨è®¡ç®å¼ãæ¬ç¤ºä¾å°è¡¨ titles ä¸çææä»·æ ¼å åã
代ç å¦ä¸:
UPDATE titles
SET price = price * 2
B.æ WHERE åå¥å UPDATE è¯å¥ä¸èµ·ä½¿ç¨
WHERE åå¥æå®è¦æ´æ°çè¡ä¾å¦ï¼å¨ä¸é¢è¿ä¸ªèæçäºä»¶ä¸ï¼åå å©ç¦å°¼äºæ´å为 Pacificaï¼ç¼©å为 PCï¼ï¼è奥å
å
°çå¸æ°æ票å³å®å°å
¶åå¸çååæ¹ä¸º Bay Cityãè¿ä¸ªä¾å说æå¦ä½ä¸ºå¥¥å
å
°å¸ä»¥åçææå±
æ°ï¼ä»ä»¬çå°åå·²ç»è¿æ¶ï¼æ´æ°è¡¨ authorsã
代ç å¦ä¸:
UPDATE authors
SET state = 'PC', city = 'Bay City'
WHERE state = 'CA' AND city = 'Oakland'
å¿
é¡»ç¼åå¦ä¸ä¸ªè¯å¥æ¥æ´æ¹åå å©ç¦å°¼äºå
¶å®åå¸çå±
æ°æå¨çå·åã
C.éè¿ UPDATE è¯å¥ä½¿ç¨æ¥èªå¦ä¸ä¸ªè¡¨çä¿¡æ¯
æ¬ç¤ºä¾ä¿®æ¹è¡¨ titles ä¸ç ytd_sales åï¼ä»¥åæ 表 sales ä¸çææ°éå®è®°å½ã
å¤å¶ä»£ç 代ç å¦ä¸:
UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
è¿ä¸ªä¾ååå®ï¼ä¸ç§ç¹å®çååå¨ç¹å®çæ¥æåªè®°å½ä¸æ¹éå®éï¼èä¸æ´æ°æ¯ææ°çãå¦æä¸æ¯è¿æ ·ï¼å³å¦æä¸ç§ç¹å®çååå¨åä¸å¤©å¯ä»¥è®°å½ä¸æ¢ä¸æ¹éå®éï¼ï¼è¿éæ示çä¾åå°åºéãä¾åå¯æ£ç¡®æ§è¡ï¼ä½æ¯æ¯ç§åååªç¨ä¸æ¹éå®éè¿è¡æ´æ°ï¼èä¸ç®¡é£ä¸å¤©å®é
éå®äºå¤å°æ¹ãè¿æ¯å 为ä¸ä¸ª UPDATE è¯å¥ä»ä¸ä¼å¯¹åä¸è¡æ´æ°ä¸¤æ¬¡ã
对äºç¹å®çååå¨åä¸å¤©å¯éå®ä¸æ¢ä¸æ¹çæ
åµï¼æ¯ç§ååçææéå®éå¿
é¡»å¨ UPDATE è¯å¥ä¸å计å¨ä¸èµ·ï¼å¦ä¸ä¾æ示ï¼
代ç å¦ä¸:
UPDATE titles
SET ytd_sales =
(SELECT SUM(qty)
FROM sales
WHERE sales.title_id = titles.title_id
AND sales.ord_date IN (SELECT MAX(ord_date) FROM sales))
FROM titles, sales
D. å° UPDATE è¯å¥ä¸ SELECT è¯å¥ä¸ç TOP åå¥ä¸èµ·ä½¿ç¨
è¿ä¸ªä¾å对æ¥èªè¡¨ authors çåå个ä½è
ç state åè¿è¡æ´æ°ã
代ç å¦ä¸:
UPDATE authors
SET state = 'ZZ'
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id
以ä¸å°±æ¯mysql updateè¯å¥ç¨æ³çå
¨é¨å
容
温馨提示:答案为网友推荐,仅供参考