MySQL修改表结构

MySQL修改表结构

在本教程中,您将了解并使用MySQL ALTER TABLE语句来更改现有表结构(如添加或删除列,更改列属性等)。

MySQL ALTER TABLE语句简介

可以使用ALTER TABLE语句来更改现有表的结构。 ALTER TABLE语句可用来添加列删除列,更改列的数据类型添加主键重命名表等等。 以下说明了ALTER TABLE语句语法:

ALTER TABLE table_name action1[,action2,…]

要更改现有表的结构:

  • 首先,在ALTER TABLE子句之后指定要更改的表名称。
  • 其次,列出一组要应用于该表的操作。操作可以是添加新列,添加主键,重命名表等任何操作。ALTER TABLE语句允许在单个ALTER TABLE语句中应用多个操作,每个操作由逗号()分隔。

让我们创建一个用于练习ALTER TABLE语句的新表。

我们将在示例数据库(yiibaidb)中创建一个名为tasks的新表。 以下是创建tasks表的脚本。

DROP TABLE IF EXISTS tasks;

CREATE TABLE tasks (
    task_id INT NOT NULL,
    subject VARCHAR(45) NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    description VARCHAR(200) NULL,
    PRIMARY KEY (task_id),
    UNIQUE INDEX task_id_unique (task_id ASC)
);

使用MySQL ALTER TABLE语句更改列

使用MySQL ALTER TABLE语句来设置列的自动递增属性

假设您希望在任务表中插入新行时,task_id列的值会自动增加1。那么可以使用ALTER TABLE语句将task_id列的属性设置为AUTO_INCREMENT,如下所示:

ALTER TABLE tasks
CHANGE COLUMN task_id task_id INT(11) NOT NULL AUTO_INCREMENT;

可以通过在tasks表中插入一些行数据来验证更改。

INSERT INTO tasks(subject,
                  start_date,
                  end_date,
   description)
VALUES('Learn MySQL ALTER TABLE',
       Now(),
       Now(),
      'Practicing MySQL ALTER TABLE statement');

INSERT INTO tasks(subject,
                  start_date,
                  end_date,
           description)
VALUES('Learn MySQL CREATE TABLE',
       Now(),
       Now(),
      'Practicing MySQL CREATE TABLE statement');

您可以查询数据以查看每次插入新行时task_id列的值是否增加1

SELECT 
    task_id, description
FROM
    tasks;

使用MySQL ALTER TABLE语句将新的列添加到表中

由于新的业务需求,需要添加一个名为complete的新列,以便在任务表中存储每个任务的完成百分比。 在这种情况下,您可以使用ALTER TABLE将新列添加到tasks表中,如下所示:

ALTER TABLE tasks 
ADD COLUMN complete DECIMAL(2,1) NULL
AFTER description;

使用MySQL ALTER TABLE从表中删除列

假设您不想将任务的描述存储在tasks表中了,并且必须将其删除。 以下语句允许您删除tasks表的description列:

ALTER TABLE tasks
DROP COLUMN description;

使用MySQL ALTER TABLE语句重命名表

可以使用ALTER TABLE语句重命名表。请注意,在重命名表之前,应该认真考虑以了解更改是否影响数据库和应用程序层,不要因为重命名表之后,应用程序因未找到数据库表而出错。

以下语句将tasks表重命名为work_items表:

ALTER TABLE tasks
RENAME TO work_items;

在本教程中,您学习了如何使用MySQL ALTER TABLE语句来更改现有的表结构并重命名表。

 

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

98 Responses

  1. Rfwedr说道:

    augmentin 625mg cheap order clomid order serophene sale

  2. Zseyrv说道:

    lexapro 20mg sale order prozac purchase naltrexone online

  3. Vqgnhp说道:

    lasix 100mg uk buy furosemide generic diuretic buy albuterol pills

  4. Xmszuo说道:

    atomoxetine usa buy quetiapine online cheap order sertraline 100mg pill

  5. Aagbln说道:

    urso 300mg drug buy cetirizine tablets cheap zyrtec 10mg

  6. Fofavv说道:

    buy azithromycin no prescription order gabapentin 600mg online buy gabapentin 100mg pill

  7. Zsycfa说道:

    good acid reducer instant supplements that help acid reflux pills that stop farting

  8. Wovtzn说道:

    prednisone generic accutane usa amoxil over the counter

  9. Wtjmuk说道:

    online birth control consultation who should not take cialis pills for larger ejaculation

  10. Hfhiiv说道:

    order promethazine 25mg online cheap erectile dysfunction drug stromectol for human

  11. Icnhdm说道:

    drugs for peptic ulcer types of vasodilator drugs otc pain relief for uti

  12. Swxvon说道:

    purchase cymbalta order cymbalta 20mg sale order modafinil without prescription

  13. Svzinf说道:

    best supplements for fungus best antiviral drug for herpes list of low sodium snacks

  14. Czudkm说道:

    purchase cyproheptadine cost ketoconazole nizoral sale

  15. Lrtoql说道:

    ordering valtrex overnight delivery diabetic pill medicine best medicine for diabetes reduction

  16. Wvxkan说道:

    cost medroxyprogesterone cost biltricide buy microzide

  17. Jubtpt说道:

    medicine to stop smoking cigarettes nicotine medication for quitting tobacco strongest pain relief medication

  18. Uqctyd说道:

    letrozole 2.5 mg oral femara 2.5 mg cost how to get abilify without a prescription

  19. Nrtbql说道:

    diphenhydramine hcl nighttime sleep aid xeljanz fda approval for alopecia wares mexico pharmacy diet pills

  20. Bonvup说道:

    buy uroxatral 10mg generic alfuzosin 10mg pills best painkiller for stomach pain

  21. Cruofb说道:

    order minocin generic buy requip 1mg generic requip 1mg canada

  22. Koydfa说道:

    acne ointment by prescription only adult acne medication oxcarbazepine 600mg without prescription

  23. Qindxl说道:

    purchase catapres for sale order antivert 25mg without prescription buy tiotropium bromide pill

  24. Okmnwe说道:

    rocaltrol 0.25mg price tricor 200mg pills tricor uk

  25. Uhyygt说道:

    trimox brand clarithromycin oral clarithromycin 500mg sale

  26. Swfbri说道:

    buy essay online uk casino online real money roulette online

  27. Eaueyh说道:

    academicwriters buy suprax 100mg without prescription buy suprax 200mg

  28. Icphrg说道:

    aspirin 75mg uk casino blackjack online slot games

  29. Bmwhbd说道:

    terbinafine 250mg usa best online casino games pala casino online

  30. Xmerbr说道:

    buy trazodone online order sildenafil 100mg pill buy cheap generic clindac a

  31. Xyacnh说道:

    buy generic cefuroxime for sale cefuroxime price robaxin 500mg uk

  32. Ycmnwp说道:

    buy tadacip tablets diclofenac 100mg generic cheap indocin 50mg

  33. Fwjzdo说道:

    nolvadex 10mg oral buy betahistine 16mg sale order generic rhinocort

  34. Krpqfe说道:

    purchase tretinoin online brand retin cream purchase avana

  35. Lkitrd说道:

    lamictal order online brand lamotrigine 200mg cheap mebendazole

  36. Uzlljg说道:

    generic metronidazole keflex sale order cephalexin 250mg online

  37. Gongyb说道:

    aurogra oral estradiol 2mg tablet order estradiol 1mg generic

  38. Lyusqi说道:

    buy forcan without a prescription buy diflucan 100mg generic buy baycip paypal

  39. Ujsonj说道:

    teach me how to write an essay write research paper buy an essay cheap

  40. Fdfmfj说道:

    brand spironolactone proscar online buy propecia generic

  41. Hcyihl说道:

    order motilium generic motilium 10mg for sale sumycin pill

  42. Nlodhm说道:

    order flomax 0.4mg generic cost zofran 8mg cheap simvastatin 10mg

  43. Kpvdfw说道:

    buy buspar 10mg pills order amiodarone 200mg without prescription order cordarone pills

  44. Digwfz说道:

    purchase ranitidine without prescription order zantac 150mg generic celebrex 200mg us

  45. Fqesid说道:

    buy cheap generic zyloprim rosuvastatin 20mg usa buy rosuvastatin 20mg generic

  46. Rlkbda说道:

    generic sumatriptan 25mg buy cheap levaquin buy generic dutasteride

  47. Bwattm说道:

    esomeprazole without prescription mirtazapine generic topamax 200mg for sale

  48. Nkucxm说道:

    astelin sprayers buy astelin 10ml online purchase avalide for sale

  49. Scinhj说道:

    famotidine 40mg drug tacrolimus 5mg pill prograf 5mg without prescription

  50. Qeifuj说道:

    xenical tablet purchase orlistat sale diltiazem 180mg usa

  51. Hjrqnn说道:

    coumadin price cost paroxetine 20mg metoclopramide 20mg drug

  52. Hhdwtl说道:

    pamelor 25 mg canada order panadol 500mg online cheap acetaminophen 500mg generic

  53. Aveqmq说道:

    buy inderal 20mg pills plavix buy online buy plavix 150mg sale

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

  55. Jcyyzo说道:

    fosamax 70mg us where can i buy colchicine nitrofurantoin 100mg without prescription

  56. Uvyuky说道:

    buy amaryl 4mg sale misoprostol pill oral etoricoxib 60mg

  57. Qivtlf说道:

    buy cheap generic lioresal cost endep toradol over the counter

  58. Uihsma说道:

    loratadine us order generic loratadine 10mg order dapoxetine pills

  59. Qmlvdo说道:

    baclofen oral buy toradol without a prescription buy ketorolac pill

  60. Qrhcrn说道:

    cheap phenytoin buy generic cyclobenzaprine for sale buy ditropan 2.5mg generic

  61. Rhxayi说道:

    buy generic perindopril 8mg fexofenadine 180mg ca allegra 120mg ca

  62. Nkixwp说道:

    buy vardenafil no prescription how to buy digoxin buy cheap tizanidine

  63. Eubned说道:

    methylprednisolone 4mg without a doctor prescription fda methylprednisolone triamcinolone buy online

  64. Pgccqa说道:

    buy clomid without prescription clomiphene over the counter buy imuran 50mg online

  65. Mtfsnb说道:

    ocean casino online levoxyl pills levoxyl online order

  66. Недавно мне понадобилось 4 000 рублей на неотложные нужды. По совету друга, я посетил yelbox.ru. Там я нашел много полезных статей о том, как правильно взять займы на карту онлайн и список проверенных МФО. И узнал, что некоторые из них предоставляют займы без процентов!

  67. Blhpxm说道:

    order symmetrel 100 mg generic symmetrel over the counter purchase aczone

  68. Vpautd说道:

    free slot play recommended you read stromectol lotion

  69. Nrlfml说道:

    roulette free buy acticlate for sale purchase ventolin inhalator generic

  70. Upwaat说道:

    order pantoprazole generic buy generic pyridium for sale order phenazopyridine without prescription

  71. Gpcoiy说道:

    poker online sites roulette casino furosemide online buy

  72. Qdqumo说道:

    buy azithromycin 500mg omnacortil uk gabapentin order

  73. Djjbbw说道:

    generic atorvastatin 80mg purchase amlodipine brand amlodipine 10mg

  74. Xnzium说道:

    accutane cheap order generic azithromycin 250mg azithromycin 500mg pill

  75. Qrwfbk说道:

    order cefdinir pills order glucophage 1000mg generic cheap prevacid 15mg

  76. Kfgvih说道:

    order provigil 100mg generic order deltasone 20mg sale buy prednisone 5mg without prescription

  77. Parnit说道:

    cenforce for sale online order naproxen 250mg generic chloroquine 250mg uk

  78. Qmuxar说道:

    buy telmisartan generic buy micardis 80mg online cheap movfor over the counter

  79. Rpnpdg说道:

    order cialis 10mg without prescription buy cialis 20mg pills viagra 100mg tablet

  80. Tviijf说道:

    buy prilosec 20mg montelukast online order metoprolol price

  81. Lbobvu说道:

    premarin 600 mg without prescription cheap sildenafil 50mg viagra 100mg pills

  82. Grxphr说道:

    order generic betahistine 16 mg order haldol sale order probenecid 500 mg sale

  83. Qdflrz说道:

    buy zovirax sale exelon 6mg for sale rivastigmine 3mg uk

  84. Gcvrde说道:

    purchase enalapril for sale purchase lactulose bottless duphalac where to buy

  85. Xcuwzj说道:

    order ferrous 100 mg pills purchase sotalol online cheap buy betapace no prescription

  86. Oamuaw说道:

    pill prasugrel 10 mg chlorpromazine 50 mg over the counter how to get detrol without a prescription

  87. Yrbmnk说道:

    order generic monograph cilostazol sale buy pletal generic

  88. Ejoczz说道:

    fludrocortisone 100 mcg canada florinef where to buy loperamide 2 mg without prescription

  89. Jznyoy说道:

    order dydrogesterone 10 mg jardiance 25mg over the counter jardiance 10mg sale

  90. Gbfxsa说道:

    meloset 3 mg for sale buy meloset 3mg sale order danocrine 100 mg generic

  91. Jvjkjg说道:

    dipyridamole pill order dipyridamole for sale pravastatin order

  92. Jtgcom说道:

    aspirin 75mg tablet order hydroquinone online cheap zovirax online

  93. Rlarye说道:

    order acarbose 50mg order fulvicin 250mg without prescription fulvicin 250 mg cost

  94. Tndjpa说道:

    buy mintop generic mintop solution can you buy ed pills online

  95. Rmsyxm说道:

    buy ketotifen 1mg for sale geodon 40mg brand buy imipramine 25mg generic

  96. Kdlfve说道:

    us cialis order sildenafil for sale generic sildenafil 50mg

  97. Yjobfv说道:

    order fenofibrate 200mg generic tricor cost tricor 200mg canada

发表回复

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

CAPTCHAis initialing...