MySQL更新表数据

MySQL更新表数据

更新数据是使用数据库时最重要的任务之一。 在本教程中,您将学习如何使用MySQL UPDATE语句来更新表中的数据。

1. MySQL UPDATE语句简介

我们使用UPDATE语句来更新表中的现有数据。也可以使用UPDATE语句来更改表中单个行,一组行或所有行的列值。

下面说明了MySQL UPDATE语句的语法:

UPDATE [LOW_PRIORITY] [IGNORE] table_name 
SET 
    column_name1 = expr1,
    column_name2 = expr2,
    ...
WHERE
    condition;

在上面UPDATE语句中:

  • 首先,在UPDATE关键字后面指定要更新数据的表名。
  • 其次,SET子句指定要修改的列和新值。要更新多个列,请使用以逗号分隔的列表。以字面值,表达式或子查询的形式在每列的赋值中来提供要设置的值。
  • 第三,使用WHERE子句中的条件指定要更新的行。WHERE子句是可选的。 如果省略WHERE子句,则UPDATE语句将更新表中的所有行。

请注意,WHERE子句非常重要,所以不应该忘记指定更新的条件。 有时,您可能只想改变一行; 但是,可能会忘记写上WHERE子句,导致意外更新表中的所有行。

MySQL在UPDATE语句中支持两个修饰符。

  • LOW_PRIORITY修饰符指示UPDATE语句延迟更新,直到没有从表中读取数据的连接。 LOW_PRIORITY对仅使用表级定的存储引擎(例如MyISAMMERGEMEMORY)生效。
  • 即使发生错误,IGNORE修饰符也可以使UPDATE语句继续更新行。导致错误(如重复键冲突)的行不会更新。

2. MySQL UPDATE示例

我们使用MySQL示例数据库(yiibaidb)中的一些表来练习使用UPDATE语句。

2.1 MySQL UPDATE一个单列示例

在这个例子中,我们将把 Mary Patterson 的电子邮件更新为新的电子邮件mary.patterso@yiibai.com

首先,为了确保更新电子邮件成功,使用以下SELECT语句employees表查询Mary的电子邮件:

SELECT 
    firstname, lastname, email
FROM
    employees
WHERE
    employeeNumber = 1056;

执行上面的查询语句,得到以下结果 –

+-----------+-----------+----------------------+
| firstname | lastname  | email                |
+-----------+-----------+----------------------+
| Mary      | Patterson | mpatterso@yiibai.com |
+-----------+-----------+----------------------+
1 row in set

第二步,使用UPDATE语句将Mary的电子邮件更新为新的电子邮件:mary.new@yiibai.com,如下查询所示:

UPDATE employees 
SET 
    email = 'mary.new@yiibai.com'
WHERE
    employeeNumber = 1056;

因为上面语句中,只想更新一行,所以使用WHERE子句来指定更新的是员工编号1056的行。SET子句将电子邮件列的值设置为新的电子邮件。

第三,再次执行SELECT语句来验证更改。

SELECT 
    firstname, lastname, email
FROM
    employees
WHERE
    employeeNumber = 1056;

再次执行上面的查询语句,得到以下结果 –

+-----------+-----------+---------------------+
| firstname | lastname  | email               |
+-----------+-----------+---------------------+
| Mary      | Patterson | mary.new@yiibai.com |
+-----------+-----------+---------------------+
1 row in set

2.2 MySQL UPDATE多列

要更新多列中的值,需要在SET子句中指定分配。例如,以下语句更新了员工编号1056的姓氏和电子邮件列:

UPDATE employees 
SET 
    lastname = 'Hill',
    email = 'mary.hill@yiibai.com'
WHERE
    employeeNumber = 1056;

在执行上面语句之后,查询员工编号为:1056的记录,如下所示 –

+-----------+----------+----------------------+
| firstname | lastname | email                |
+-----------+----------+----------------------+
| Mary      | Hill     | mary.hill@yiibai.com |
+-----------+----------+----------------------+
1 row in set

2.3 使用SELECT语句的MySQL UPDATE示例

可以使用SELECT语句查询来自其他表的数据来提供给SET子句的值。

例如,在customers表中,有些客户没有任何销售代表。 salesRepEmployeeNumber列的值为NULL,如下所示:

mysql> SELECT 
    customername, salesRepEmployeeNumber
FROM
    customers
WHERE
    salesRepEmployeeNumber IS NULL;
+--------------------------------+------------------------+
| customername                   | salesRepEmployeeNumber |
+--------------------------------+------------------------+
| Havel & Zbyszek Co             | NULL                   |
| Porto Imports Co.              | NULL                   |
| Asian Shopping Network, Co     | NULL                   |
| Natrlich Autos                 | NULL                   |
| ANG Resellers                  | NULL                   |
| Messner Shopping Network       | NULL                   |
| Franken Gifts, Co              | NULL                   |
| BG&E Collectables              | NULL                   |
| Schuyler Imports               | NULL                   |
| Der Hund Imports               | NULL                   |
| Cramer Spezialitten, Ltd       | NULL                   |
| Asian Treasures, Inc.          | NULL                   |
| SAR Distributors, Co           | NULL                   |
| Kommission Auto                | NULL                   |
| Lisboa Souveniers, Inc         | NULL                   |
| Stuttgart Collectable Exchange | NULL                   |
| Feuer Online Stores, Inc       | NULL                   |
| Warburg Exchange               | NULL                   |
| Anton Designs, Ltd.            | NULL                   |
| Mit Vergngen & Co.             | NULL                   |
| Kremlin Collectables, Co.      | NULL                   |
| Raanan Stores, Inc             | NULL                   |
+--------------------------------+------------------------+
22 rows in set

我们可以为这些客户提供销售代表和更新。

为此,需要从employees表中随机选择一个职位为Sales Rep的雇员,并将其更新到employees表中。
下面的查询语句是从employees表中随机选择一个其职位是Sales Rep的员工。

SELECT 
    employeeNumber
FROM
    employees
WHERE
    jobtitle = 'Sales Rep'
ORDER BY RAND()
LIMIT 1;

要更新customers表中的销售代表员工编号(employeeNumber)列,我们将上面的查询放在UPDATE语句的SET子句中,如下所示:

UPDATE customers 
SET 
    salesRepEmployeeNumber = (SELECT 
            employeeNumber
        FROM
            employees
        WHERE
            jobtitle = 'Sales Rep'
        LIMIT 1)
WHERE
    salesRepEmployeeNumber IS NULL;

如果在执行上面更新语句后,查询customers表中的数据,将看到每个客户都有一个销售代表。 换句话说,以下查询不返回任何行数据。

SELECT 
     salesRepEmployeeNumber
FROM
    customers
WHERE
    salesRepEmployeeNumber IS NULL;
`

在本教程中,您已经学会了如何使用MySQL UPDATE语句来更新数据库表中的数据。

 

移动端:请扫描本页面底部(右侧)二维码并关注微信公众号,回复:”教程” 选择相关教程阅读

98 Responses

  1. Hggfsn说道:

    where can i buy lexapro revia online naltrexone cost

  2. Kcxyvk说道:

    order furosemide generic acticlate drug purchase albuterol inhalator for sale

  3. Txbatq说道:

    buy strattera online buy strattera generic zoloft 50mg without prescription

  4. Lycncp说道:

    urso pills buy urso online cheap purchase zyrtec sale

  5. Xdsysp说道:

    buy zithromax 250mg pills order zithromax online buy neurontin 800mg for sale

  6. Bllmwi说道:

    best natural supplements for gerd medications for vomiting in adults the best gas reducing relief

  7. Seremz说道:

    buy deltasone online cheap buy amoxicillin 250mg pills oral amoxicillin

  8. Mvjnmj说道:

    buy emergency contraception pill training to last longer does viagra make you last longer in bed

  9. Ozktev说道:

    buy phenergan pills for sale ed pills for sale buy ivermectin 12mg for humans

  10. Jpkzfl说道:

    peptic duodenitis diet drugs for ventricular tachycardia antibiotics for gram positive uti

  11. Qwfncu说道:

    cymbalta 40mg canada provigil price buy modafinil online cheap

  12. Xmricp说道:

    strongest antifungal herbs fungus clear fix reviews high blood pressure medications list

  13. BVZ_Moskva_P说道:

    Моментально возводимые здания: финансовая польза в каждом блоке!
    В современной сфере, где время – деньги, экспресс-конструкции стали реальным спасением для коммерции. Эти прогрессивные сооружения сочетают в себе устойчивость, финансовую эффективность и мгновенную сборку, что придает им способность отличным выбором для разных коммерческих начинаний.
    Быстровозводимые здания
    1. Срочное строительство: Секунды – самое ценное в коммерческой деятельности, и скоростроительные конструкции способствуют значительному сокращению сроков возведения. Это высоко оценивается в вариантах, когда актуально быстро начать вести дело и начать получать прибыль.
    2. Финансовая эффективность: За счет оптимизации производства и установки элементов на месте, финансовые издержки на быстровозводимые объекты часто бывает менее, по отношению к обычным строительным проектам. Это позволяет сократить затраты и обеспечить более высокую рентабельность вложений.
    Подробнее на http://scholding.ru/
    В заключение, объекты быстрого возвода – это оптимальное решение для коммерческих проектов. Они сочетают в себе молниеносную установку, экономичность и повышенную надежность, что позволяет им первоклассным вариантом для деловых лиц, имеющих целью быстрый бизнес-старт и извлекать прибыль. Не упустите возможность сократить затраты и время, наилучшие объекты быстрого возвода для ваших будущих инициатив!

  14. Nrpgbo说道:

    cyproheptadine buy online luvox 50mg canada brand ketoconazole 200 mg

  15. BVZ_Moskva_P说道:

    Экспресс-строения здания: экономический доход в каждой детали!
    В современном обществе, где секунды – доллары, сооружения с быстрым монтажем стали реальным спасением для коммерческой деятельности. Эти современные сооружения комбинируют в себе устойчивость, финансовую выгоду и быстрое строительство, что придает им способность первоклассным вариантом для разнообразных предпринимательских инициатив.
    Быстровозводимые здания
    1. Скорость строительства: Минуты – важнейший фактор в коммерции, и экспресс-сооружения обеспечивают существенное уменьшение сроков стройки. Это преимущественно важно в моменты, когда требуется быстрый старт бизнеса и начать монетизацию.
    2. Экономия: За счет оптимизации производства и установки элементов на месте, стоимость быстровозводимых зданий часто снижается, чем у традиционных строительных проектов. Это предоставляет шанс сократить издержки и достичь большей доходности инвестиций.
    Подробнее на scholding.ru
    В заключение, скоро возводимые строения – это первоклассное решение для проектов любого масштаба. Они объединяют в себе скорость строительства, экономическую эффективность и надежность, что придает им способность оптимальным решением для компаний, активно нацеленных на скорый старт бизнеса и получать деньги. Не упустите возможность сократить затраты и время, наилучшие объекты быстрого возвода для вашего следующего начинания!

  16. Cyiejn说道:

    drugs used for smoking cessation biological dmards list of medications pain medication online order

  17. Neuhhp说道:

    buy provera sale praziquantel 600 mg brand microzide 25mg cheap

  18. Hapalg说道:

    order sleep meds online prescription meds for hair loss online prescriptions for weight loss

  19. Edfbva说道:

    femara 2.5mg cost order albenza pill buy abilify 20mg generic

  20. Imdoch说道:

    buy cheap alfuzosin alfuzosin 10mg uk strongest prescription acid reflux medication

  21. Xhtnep说道:

    minocycline where to buy requip without prescription buy ropinirole 2mg

  22. Vbibhx说道:

    how to get rid of body acne fast sudden adult acne female oxcarbazepine buy online

  23. Ciozwx说道:

    buy clonidine 0.1mg for sale antivert 25mg oral brand tiotropium bromide

  24. Rdgyxb说道:

    buy calcitriol sale order tricor 200mg fenofibrate 160mg for sale

  25. Cimoqo说道:

    order amoxicillin 250mg arimidex 1mg cheap biaxin 500mg generic

  26. Jzoloe说道:

    buy term papers online real casino slot machine games best online blackjack real money

  27. Sjblbo说道:

    assignment website university essay help suprax 100mg canada

  28. Tfbzrr说道:

    purchase aspirin generic all real money online casinos texas poker online

  29. Dbjaxz说道:

    buy lamisil paypal online casino real money us world series of poker online

  30. Otlpod说道:

    order desyrel 100mg online sildenafil price order clindamycin online cheap

  31. Hgutjt说道:

    order cefuroxime 500mg online purchase axetil pills brand methocarbamol 500mg

  32. Kwfhyq说道:

    tadacip 10mg us buy tadacip generic purchase indomethacin pill

  33. Zkoaou说道:

    buy generic tamoxifen over the counter buy rhinocort paypal symbicort order

  34. Sljuyz说道:

    cleocin 300mg pills buy cleocin 300mg online cheapest ed pills online

  35. Fpoivx说道:

    order lamictal pill order mebendazole 100mg without prescription purchase nemazole pills

  36. Rnuozb说道:

    purchase flagyl online cheap order septra online cephalexin pill

  37. Osfhlg说道:

    forcan cost buy acillin medication ciprofloxacin 1000mg cost

  38. Iubsga说道:

    sildenafil for sale sildalis sale buy estrace without a prescription

  39. Duenjh说道:

    spironolactone over the counter buy cheap aldactone finasteride oral

  40. Geczgq说道:

    write essay for money do my research paper help with research paper

  41. Wmapga说道:

    purchase tamsulosin buy ondansetron 4mg without prescription buy simvastatin tablets

  42. Ofpyaw说道:

    buy domperidone sale motilium 10mg uk order sumycin generic

  43. Rvujya说道:

    brand ranitidine 150mg order mobic online cheap purchase celebrex sale

  44. Iszwsd说道:

    order buspar 10mg generic order buspirone 10mg pills buy amiodarone without a prescription

  45. Seash说道:

    A dating app specifically for bears, cubs and all men who fancy those aforementioned dad bods. Whether you enjoy the categorisation of gay men’s bodies or not, these apps exist and people use them. If you yourself feel a little bit more body-conscious and want to be in a space where being both sexual and thicc is praised rather than risky, Growlr might be an excellent starting point even if you move on elsewhere. With Happn, users can meet and interact with other people. This free dating mobile application is available for both Android and iOS devices. Developed by HAPPN SAS, the app was fully created on a principle of reciprocity. The League is an application-based app that requires users to enter their job title, their alma mater and LinkedIn profile in order to be accepted. It can take some time to get approved in larger cities and yeah, it’s a little elitist, but it might be worth it to find your perfect person.
    https://wiki-neon.win/index.php?title=Dating_apps_for_14_and_older
    You don’t have to smile in every one of your dating profile photos, but you need to smile in a few. #4 does a good job of smiling, but his non-smiling pics need to go. They make him look like he’s trying to look tough, not like he actually is. He’s also got to lose the Spider-Man meme and the bathroom selfie. We’ve already talked about why bathroom selfies suck, but you need to be in every photo you post on your dating profile. This isn’t your Instagram feed. Posting this photo actually violates POF’s terms of service and he can get in trouble for posting copyrighted material. Zavala, who lives in Washington, D.C., notes that the bots have gotten better over time. “In the past, what has tipped me are pictures and a lack of a bio. However, recently some bot profiles are a bit harder to tell due to them including more than one picture or having a somewhat relatable bio,” he says.

  46. Gvfdxd说道:

    sumatriptan 25mg sale order avodart 0.5mg sale avodart 0.5mg drug

  47. Qsnvno说道:

    zyloprim us zyloprim 300mg cheap buy rosuvastatin online

  48. Nbhhpd说道:

    where can i buy nexium buy mirtazapine 15mg topamax 100mg tablet

  49. Lngknv说道:

    buy azelastine medication zovirax cost oral avalide

  50. Fsgpnk说道:

    where can i buy pepcid buy famotidine online tacrolimus 5mg pills

  51. Gmstco说道:

    buy warfarin coumadin over the counter reglan 20mg pills

  52. Liwasq说道:

    order xenical 60mg generic buy xenical 120mg without prescription order generic diltiazem

  53. Nxnfpr说道:

    order pamelor 25mg without prescription purchase nortriptyline generic buy acetaminophen generic

  54. Zipjey说道:

    inderal 20mg uk plavix 150mg price clopidogrel online buy

  55. Yqtncq说道:

    purchase alendronate without prescription colchicine 0.5mg pills nitrofurantoin 100 mg us

  56. Xdmjse说道:

    glimepiride 1mg drug order arcoxia generic cheap arcoxia 60mg

  57. eroms说道:

    Even though the real estate market is slowing, prices are still high, and competition is still tough. For instance, 100,000 people moved to Dallas from 2020 to 2021, Smith says. Some can’t compete with all-cash buyers or get a loan because of a lack of down payment funds or a low credit score. Paying a higher interest rate is not a deterrent for people who want to buy a home but haven’t been able to either because of the state of the market or because they don’t qualify for a mortgage in a traditional setting. Information from the new block is transformed into a cryptographic code. For some bitcoin backers, that’s just fine. Saylor, one of bitcoin’s most vocal evangelists, declined to comment for this story. But elsewhere, he has said that he views the digital token as an asset akin to gold, meant to be bought and held, not used as an alternative to the dollar.
    http://xn--2y1by25awlasel42e.com/bbs/board.php?bo_table=free&wr_id=11985
    Of course! If you prefer to buy your crypto with cash, you can visit any of our BTMs across North America. You can also load cash to your wallet at any of our participating BDCheckout retail partner checkout counters.Download the app or head to our Locations page to find a BTM near you. Although users may wonder if Bitcoin stocks are available or wonder how to buy Bitcoin stocks, this classification does not technically exist. You can, however, buy stock in companies involved with Bitcoin, such as companies focused on BTC mining, or buy shares of companies that have invested in Bitcoin. Select crypto at checkout on eligible purchases when checking out at millions of online stores worldwide. Over the last four years, saving $ 50 per week in Bitcoin turned $ 10,450 into $ 17,805. As the first-ever cryptocurrency, bitcoin has seen its share of drastic ups and downs. In July 2010, one bitcoin would cost you $0.0008 — compare that to the all-time high of $64,863 per coin in April 2021.

  58. Qoitdo说道:

    buy baclofen no prescription endep tablet order toradol without prescription

  59. Kuniwh说道:

    baclofen tablet baclofen order online order toradol 10mg generic

  60. Pombsl说道:

    claritin over the counter ramipril 10mg oral priligy 30mg cheap

  61. Gairwr说道:

    dilantin 100 mg without prescription order oxytrol without prescription oxybutynin online order

  62. Vqdwfk说道:

    buy aceon medication buy allegra without a prescription buy fexofenadine

  63. Gnnqlb说道:

    buy levitra 10mg sale levitra buy online brand tizanidine

  64. Imjxub说道:

    order clomid 100mg generic imdur 40mg canada buy imuran

  65. Qcnbxo说道:

    buy oral medrol buy methylprednisolone generic triamcinolone 4mg pills

  66. Tlbehw说道:

    poker online for fun slots games levothyroxine pill

  67. Sucfrd说道:

    order symmetrel 100mg buy amantadine 100mg pill aczone tablet

  68. Bbvpyi说道:

    real online blackjack play roulette for free stromectol liquid

  69. Ioixgc说道:

    best online gambling sites free blackjack purchase ventolin inhalator without prescription

  70. Bvzgyi说道:

    buy pantoprazole without prescription pantoprazole ca pyridium without prescription

  71. Vwdker说道:

    slot machine games order lasix 100mg for sale buy lasix 40mg for sale

  72. Unpipm说道:

    azipro 250mg cost azithromycin 250mg generic oral neurontin

  73. Lkrdul说道:

    order lipitor 20mg sale albuterol 100 mcg ca order norvasc 5mg for sale

  74. Obmtpu说道:

    isotretinoin brand purchase isotretinoin online azithromycin price

  75. Xoevip说道:

    cheap provigil 100mg buy deltasone 40mg online cheap prednisone 10mg canada

  76. Vkgnpt说道:

    buy cefdinir 300 mg online cheap order metformin 500mg online cheap order lansoprazole 30mg generic

  77. Zjkgkg说道:

    buy cenforce 50mg generic naproxen 250mg sale buy cheap aralen

  78. Juluci说道:

    real cialis order generic sildenafil 100mg order generic sildenafil

  79. Vsbbth说道:

    purchase telmisartan online order movfor generic purchase molnupiravir

  80. Ikvxxn说道:

    premarin price order sildenafil sale order viagra 50mg online

  81. Ulvrne说道:

    omeprazole to treat heartburn montelukast online buy metoprolol pills

  82. Ruyxpj说道:

    betahistine 16 mg for sale buy benemid cheap buy probalan pills for sale

  83. Pwkeao说道:

    zovirax generic buy generic rivastigmine for sale buy exelon online cheap

  84. Dwosfq说道:

    vasotec buy online vasotec 10mg ca buy duphalac medication

  85. Dwzvdo说道:

    order mestinon pills maxalt order rizatriptan canada

  86. Szhmxv说道:

    ferrous sulfate 100 mg sale sotalol price purchase betapace generic

  87. Madkxw说道:

    etodolac 600mg over the counter oral mebeverine 135 mg cilostazol 100 mg oral

  88. Iywvet说道:

    cheap prasugrel where to buy prasugrel without a prescription order detrol 2mg generic

  89. Kvzixz说道:

    buy fludrocortisone 100mcg generic imodium 2 mg generic buy loperamide 2mg without prescription

  90. Prfjwb说道:

    cheap duphaston 10 mg purchase januvia generic empagliflozin 10mg ca

  91. Egmlty说道:

    meloset sale desogestrel 0.075mg cost buy danazol 100 mg for sale

  92. Tzxooh说道:

    dipyridamole 100mg pills plendil cost how to buy pravastatin

  93. Ggeusx说道:

    buy aspirin 75mg purchase aspirin for sale zovirax where to buy

  94. Ujrchr说道:

    acarbose 50mg brand purchase glyburide order griseofulvin 250 mg for sale

  95. Rbbdmq说道:

    buy generic minoxidil order cialis 10mg generic erection pills viagra online

  96. Dqrpdh说道:

    tadalafil 5mg price cheap sildenafil generic order sildenafil 50mg without prescription

  97. Wwrfmr说道:

    zaditor uk buy imipramine 25mg order tofranil 25mg sale

  98. Ajbvmd说道:

    tricor 160mg sale purchase tricor pill buy generic fenofibrate

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

CAPTCHAis initialing...