MySQL删除表数据

MySQL删除表数据

在本教程中,您将学习如何使用MySQL DELETE语句从单个表中删除数据。

1. MySQL DELETE语句介绍

要从表中删除数据,请使用MySQL DELETE语句。下面说明了DELETE语句的语法:

DELETE FROM table_name
WHERE condition;

在上面查询语句中 –

  • 首先,指定删除数据的表(table_name)。
  • 其次,使用条件来指定要在WHERE子句中删除的行记录。如果行匹配条件,这些行记录将被删除。

请注意,WHERE子句是可选的。如果省略WHERE子句,DELETE语句将删除表中的所有行。

除了从表中删除数据外,DELETE语句返回删除的行数。

要使用单个DELETE语句从多个表中删除数据,请阅读下一个教程中将介绍的DELETE JOIN语句

要删除表中的所有行,而不需要知道删除了多少行,那么应该使用TRUNCATE TABLE语句来获得更好的执行性能。

对于具有外键约束的表,当从父表中删除行记录时,子表中的行记录将通过使用ON DELETE CASCADE选项自动删除。

2. MySQL DELETE的例子

我们将使用示例数据库(yiibaidb)中的employees表进行演示。

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| emp_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| emp_name    | varchar(255) | NO   |     | NULL    |                |
| performance | int(11)      | YES  | MUL | NULL    |                |
| salary      | float        | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set

请注意,一旦删除数据,它就会永远消失。 因此,在执行DELETE语句之前,应该先备份数据库,以防万一要找回删除过的数据。

假设要删除officeNumber4的员工,则使用DELETE语句与WHERE子句作为以下查询:

DELETE FROM employees 
WHERE
    officeCode = 4;

要删除employees表中的所有行,请使用不带WHERE子句的DELETE语句,如下所示:

DELETE FROM employees;

在执行上面查询语句后,employees表中的所有行都被删除。

MySQL DELETE和LIMIT子句

如果要限制要删除的行数,则使用LIMIT子句,如下所示:

DELETE FROM table
LIMIT row_count;

请注意,表中的行顺序未指定,因此,当您使用LIMIT子句时,应始终使用ORDER BY子句,不然删除的记录可能不是你所预期的那样。

DELETE FROM table_name
ORDER BY c1, c2, ...
LIMIT row_count;

考虑在示例数据库(yiibaidb)中的customers表,其表结构如下:

mysql> desc customers;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| customerNumber         | int(11)       | NO   | PRI | NULL    |       |
| customerName           | varchar(50)   | NO   |     | NULL    |       |
| contactLastName        | varchar(50)   | NO   |     | NULL    |       |
| contactFirstName       | varchar(50)   | NO   |     | NULL    |       |
| phone                  | varchar(50)   | NO   |     | NULL    |       |
| addressLine1           | varchar(50)   | NO   |     | NULL    |       |
| addressLine2           | varchar(50)   | YES  |     | NULL    |       |
| city                   | varchar(50)   | NO   |     | NULL    |       |
| state                  | varchar(50)   | YES  |     | NULL    |       |
| postalCode             | varchar(15)   | YES  |     | NULL    |       |
| country                | varchar(50)   | NO   |     | NULL    |       |
| salesRepEmployeeNumber | int(11)       | YES  | MUL | NULL    |       |
| creditLimit            | decimal(10,2) | YES  |     | NULL    |       |
+------------------------+---------------+------+-----+---------+-------+
13 rows in set

例如,以下语句按客户名称按字母排序客户,并删除前10个客户:

DELETE FROM customers
ORDER BY customerName
LIMIT 10;

类似地,以下DELETE语句选择法国(France)的客户,按升序按信用额度(creditLimit)进行排序,并删除前5个客户:

DELETE FROM customers
WHERE country = 'France'
ORDER BY creditLimit
LIMIT 5;

类似地,以下DELETE语句选择法国(France)的客户,按升序按信用额度(creditLimit)进行排序,并删除前5个客户:

DELETE FROM customers
WHERE country = 'France'
ORDER BY creditLimit
LIMIT 5;

在本教程中,您已经学会了如何使用MySQL DELETE语句从表中删除数据。

 

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

95 Responses

  1. Zucscl说道:

    order augmentin 375mg pill clomid 50mg cost order clomid 50mg

  2. Cqokny说道:

    escitalopram 20mg price order revia 50mg without prescription purchase naltrexone online cheap

  3. Siijuv说道:

    furosemide 40mg pills order doxycycline 200mg pills generic albuterol

  4. Mgxmuq说道:

    buy generic strattera strattera 25mg oral sertraline 50mg price

  5. Beeyqk说道:

    purchase urso online cheap buy ursodiol generic order cetirizine sale

  6. Eivxnr说道:

    zithromax 500mg drug azithromycin 250mg sale buy gabapentin 100mg without prescription

  7. Jupuux说道:

    ppi stronger than prilosec anti sickness over the counter scented fart pills

  8. Doosyt说道:

    buy deltasone online buy amoxil for sale order amoxicillin 1000mg generic

  9. Vepqqx说道:

    walgreens birth control pills price ejackulation pills best remedy for premature ejaculation

  10. Gpwbvb说道:

    duodenal ulcer diagnosis and treatment types of bacterial bladder infections order cystitis treatment online

  11. Kezmlv说道:

    order phenergan for sale purchase phenergan online ivermectin uk buy

  12. Ehodvz说道:

    strongest antifungal over the counter how to treat vaginal herpes high blood pressure medication names

  13. Estgsx说道:

    duloxetine 40mg for sale buy glipizide generic buy modafinil 100mg generic

  14. Erdwds说道:

    pills for herpes outbreak how much does inhaler cost diabetes pills names

  15. Ctbxgw说道:

    cost periactin 4mg buy ketoconazole 200 mg generic nizoral 200mg sale

  16. Mdvptq说道:

    quit smoking pills zyban buy strong painkillers online pain killers well known

  17. Oiwzhz说道:

    provera 5mg generic oral provera buy cheap microzide

  18. Dbvvpt说道:

    sleeping pills over the counter buy sleep aids online top weight loss pills

  19. Oupjte说道:

    femara canada femara oral order generic aripiprazole

  20. Kexihl说道:

    buy generic alfuzosin for sale buy alfuzosin cheap acid reducing medications

  21. Jthkpw说道:

    buy minocycline generic buy ropinirole 1mg purchase ropinirole

  22. Cxlyvt说道:

    oral medication for severe acne buy oxcarbazepine generic buy oxcarbazepine 600mg generic

  23. Icqykq说道:

    clonidine 0.1 mg usa catapres 0.1 mg sale tiotropium bromide without prescription

  24. Cydvap说道:

    buy rocaltrol 0.25 mg generic tricor medication buy generic fenofibrate 160mg

  25. Abfrka说道:

    purchase trimox online cheap brand clarithromycin order biaxin 500mg

  26. Weyqpc说道:

    help with thesis online slot machines free slot

  27. Gtigzv说道:

    purchase essay online pay to do my statistics homework cefixime 200mg pills

  28. Szkolx说道:

    aspirin 75mg usa best casino free casino games no registration no download

  29. Vjnydn说道:

    desyrel 50mg canada purchase clindac a gel buy generic clindamycin

  30. Vmdhpe说道:

    buy terbinafine 250mg sale lamisil 250mg drug online casino real money no deposit

  31. Nrdbzh说道:

    buy generic ceftin 500mg careprost nasal spray methocarbamol 500mg cheap

  32. Nywwjy说道:

    order tadalafil 10mg pills order generic tadacip 20mg buy indomethacin pill

  33. Negeiu说道:

    retin cream oral order tadalafil online order avana online cheap

  34. Qmjfqv说道:

    purchase cleocin online erythromycin 250mg drug sildenafil price

  35. Igvdri说道:

    flagyl 200mg ca bactrim 960mg price cephalexin generic

  36. Pwfpge说道:

    order lamictal pill buy generic vermox over the counter generic mebendazole

  37. Mmcgct说道:

    fluconazole 100mg drug ciprofloxacin 500mg usa order ciprofloxacin 1000mg for sale

  38. Xsnesz说道:

    order sildenafil 100mg pill buy estrace 1mg generic where can i buy estradiol

  39. Gckbda说道:

    order aldactone online cheap finasteride 5mg price cost finasteride 5mg

  40. Gbuvyz说道:

    cheap thesis binding assignment writing services academic writing terms

  41. Vwblqq说道:

    flomax 0.4mg cost order zofran 8mg online cheap brand simvastatin

  42. Cwdwbk说道:

    oral domperidone 10mg sumycin buy online buy tetracycline online cheap

  43. Ngksib说道:

    buy cheap ranitidine ranitidine where to buy celebrex cheap

  44. Whkwvn说道:

    buspar 10mg uk buy ezetimibe without a prescription order cordarone 100mg without prescription

  45. Rgqymr说道:

    buy imitrex pills levaquin 500mg brand buy dutasteride online

  46. Shwyvz说道:

    nexium online cost mirtazapine how to buy topamax

  47. Wkzzqk说道:

    where to buy pepcid without a prescription order losartan 50mg without prescription buy generic prograf

  48. Afbery说道:

    purchase astelin generic acyclovir 800mg ca avalide price

  49. Jwbbgo说道:

    medex pills buy paroxetine tablets order reglan online

  50. Yykqjh说道:

    purchase orlistat online cheap order mesalamine without prescription buy diltiazem 180mg pill

  51. Yrcwmx说道:

    buy nortriptyline 25 mg without prescription acetaminophen 500 mg sale buy anacin 500mg pills

  52. Lmtnnr说道:

    purchase inderal online cheap buy clopidogrel 150mg clopidogrel pills

  53. Ejpyir说道:

    fosamax 35mg uk fosamax uk purchase macrodantin for sale

  54. Pauexu说道:

    buy amaryl 1mg generic cheap amaryl 1mg buy etoricoxib generic

  55. Cqawhk说道:

    buy generic lioresal order elavil 10mg without prescription ketorolac drug

  56. Mummsw说道:

    ozobax pill buy generic endep buy toradol 10mg sale

  57. Ywadhe说道:

    claritin ca buy loratadine without prescription order dapoxetine 90mg pills

  58. Rpkzqa说道:

    buy phenytoin 100mg pill oral dilantin 100 mg oxybutynin pills

  59. В поисках азарта я вбил в Яндекс “казино на деньги” и первым делом обнаружил сайт caso-slots.com. Тут есть все: много казино с игровыми автоматами, бонусы на депозит и статьи с советами, как играть, чтобы выиграть. Теперь я готов к большой игре!

  60. Jqmjrw说道:

    generic coversum order generic fexofenadine allegra 180mg sale

  61. Jkncfy说道:

    order vardenafil 10mg pills vardenafil tablet tizanidine 2mg pill

  62. Xxqvcc说道:

    order clomid pills imdur oral order azathioprine sale

  63. Zasxlp说道:

    buy generic methylprednisolone methylprednisolone 4mg over counter generic triamcinolone 4mg

  64. Ojwrpr说道:

    online casinos for real money free slot machine games order synthroid 150mcg for sale

  65. Ioblmw说道:

    casinos online online casino gambling ivermectin 6mg for humans for sale

  66. Pwnuzh说道:

    purchase symmetrel generic order symmetrel 100mg online avlosulfon 100mg price

  67. Dzbkbu说道:

    slot machines monodox us buy albuterol generic

  68. Sxdlmw说道:

    play casino slots free spins no deposit canada lasix where to buy

  69. Ostknp说道:

    protonix 40mg sale zestril 5mg us order generic phenazopyridine 200mg

  70. Hfdrbx说道:

    azipro 250mg price order prednisolone 40mg for sale buy gabapentin 600mg online cheap

  71. Vohnge说道:

    order lipitor lipitor 40mg brand buy amlodipine generic

  72. Jooyli说道:

    cheap accutane 10mg where can i buy azithromycin order azithromycin pill

  73. Uorjgv说道:

    modafinil without prescription buy deltasone order deltasone online

  74. Hycqjj说道:

    buy cefdinir 300mg pill buy omnicef online lansoprazole for sale online

  75. Ilifan说道:

    cenforce 50mg us naproxen pill buy aralen online

  76. Kcffsa说道:

    buy tadalafil 5mg oral tadalafil 5mg viagra sildenafil

  77. Wehhjx说道:

    order telmisartan without prescription movfor canada molnupiravir sale

  78. Bhvevq说道:

    premarin 600 mg cheap sildenafil 50mg oral order sildenafil without prescription

  79. Lkwbkl说道:

    omeprazole uk generic omeprazole lopressor 50mg over the counter

  80. Sgdyxv说道:

    how to buy betahistine oral betahistine 16mg buy benemid without a prescription

  81. Gmfazb说道:

    order xalatan online buy zovirax without prescription cheap rivastigmine 6mg

  82. Anljhq说道:

    order vasotec pills buy duphalac for sale purchase lactulose without prescription

  83. Akggpf说道:

    purchase mestinon online oral rizatriptan 10mg rizatriptan 5mg pills

  84. Oglvuy说道:

    order ferrous generic buy ascorbic acid 500mg generic betapace usa

  85. Smmqdy说道:

    prasugrel cost order chlorpromazine 50 mg online order detrol 1mg sale

  86. Esqvpx说道:

    order generic monograph 600mg buy monograph 600 mg online cheap pletal drug

  87. Vrxdzr说道:

    buy dydrogesterone 10 mg generic buy sitagliptin online cheap order empagliflozin online

  88. Fyvqpm说道:

    purchase fludrocortisone online buy generic florinef purchase imodium for sale

  89. Kdhmqu说道:

    buy meloset sale brand melatonin buy danocrine 100mg online

  90. Injwqo说道:

    generic dipyridamole 25mg order pravastatin online cost pravachol 20mg

  91. Tosnej说道:

    aspirin 75mg pills where can i buy imiquad zovirax creams

  92. Rvupsm说道:

    order mintop for sale erection problems buy ed pills medication

  93. Dcbdzc说道:

    acarbose generic micronase for sale online fulvicin 250 mg generic

  94. Qiswgs说道:

    order ketotifen 1 mg generic generic ketotifen imipramine 75mg uk

  95. Kkmiyp说道:

    buy tricor 200mg for sale tricor medication tricor 160mg for sale

发表评论

您的电子邮箱地址不会被公开。

CAPTCHAis initialing...