frankwang0909.github.io/mysql_notes_3.html at master · frankwang0909/frankwang0909.github.io · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<!doctype html><html lang=en><head><meta charset=utf-8><meta http-equiv=X-UA-Compatible content="IE=edge,chrome=1"><title>Mysql Notes 3 - Frank Wang's Coding World</title><meta name=renderer content="webkit"><meta name=viewport content="width=device-width,initial-scale=1,maximum-scale=1"><meta http-equiv=Cache-Control content="no-transform"><meta http-equiv=Cache-Control content="no-siteapp"><meta name=theme-color content="#f8f5ec"><meta name=msapplication-navbutton-color content="#f8f5ec"><meta name=apple-mobile-web-app-capable content="yes"><meta name=apple-mobile-web-app-status-bar-style content="#f8f5ec"><meta name=author content><meta name=description content="MySQL study notes: Common commands for modifying data tables, including commands to add or delete data columns, add constraints, delete constraints, modify the definition of data columns, modify the names of data columns, and rename the data table"><meta name=keywords content="MySQL study notes,Modify data table"><meta name=generator content="Hugo 0.157.0 with theme even"><link rel=canonical href=http://frankwang0909.github.io/mysql_notes_3.html><link rel=apple-touch-icon sizes=180x180 href=/apple-touch-icon.png><link rel=icon type=image/png sizes=32x32 href=/favicon-32x32.png><link rel=icon type=image/png sizes=16x16 href=/favicon-16x16.png><link rel=manifest href=/manifest.json><link rel=mask-icon href=/safari-pinned-tab.svg color=#5bbad5><link href=/sass/main.min.af7fd1da18d66c2b017df5b4cae508ef44cfcac3fb4c7c7a327fe4f4f9e28b08.css rel=stylesheet><link href=/lib/fancybox/jquery.fancybox-3.1.20.min.css rel=stylesheet><meta property="og:url" content="http://frankwang0909.github.io/mysql_notes_3.html"><meta property="og:site_name" content="Frank Wang's Coding World"><meta property="og:title" content="Mysql Notes 3"><meta property="og:description" content="MySQL study notes: Common commands for modifying data tables, including commands to add or delete data columns, add constraints, delete constraints, modify the definition of data columns, modify the names of data columns, and rename the data table"><meta property="og:locale" content="en_US"><meta property="og:type" content="article"><meta property="article:section" content="post"><meta property="article:published_time" content="2017-06-27T22:57:29+08:00"><meta property="article:modified_time" content="2017-06-27T22:57:29+08:00"><meta property="article:tag" content="MySQL"><meta itemprop=name content="Mysql Notes 3"><meta itemprop=description content="MySQL study notes: Common commands for modifying data tables, including commands to add or delete data columns, add constraints, delete constraints, modify the definition of data columns, modify the names of data columns, and rename the data table"><meta itemprop=datePublished content="2017-06-27T22:57:29+08:00"><meta itemprop=dateModified content="2017-06-27T22:57:29+08:00"><meta itemprop=wordCount content="445"><meta itemprop=keywords content="MySQL study notes,Modify data table"><meta name=twitter:card content="summary"><meta name=twitter:title content="Mysql Notes 3"><meta name=twitter:description content="MySQL study notes: Common commands for modifying data tables, including commands to add or delete data columns, add constraints, delete constraints, modify the definition of data columns, modify the names of data columns, and rename the data table"><!--[if lte IE 9]><script src=https://cdnjs.cloudflare.com/ajax/libs/classlist/1.1.20170427/classList.min.js></script><![endif]--><!--[if lt IE 9]><script src=https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js></script><script src=https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js></script><![endif]--></head><body><div id=mobile-navbar class=mobile-navbar><div class=mobile-header-logo><a href=/en/ class=logo>Frank Wang’s Coding World</a></div><div class=mobile-navbar-icon><span></span>
<span></span>
<span></span></div></div><nav id=mobile-menu class="mobile-menu slideout-menu"><ul class=mobile-menu-list><a href=/en/><li class=mobile-menu-item>Home</li></a><a href=/en/post/><li class=mobile-menu-item>Archives</li></a><a href=/en/tags/><li class=mobile-menu-item>Tags</li></a><a href=/en/categories/><li class=mobile-menu-item>Categories</li></a><a href=/><li class=mobile-menu-item>中文</li></a></ul></nav><div class=container id=mobile-panel><header id=header class=header><div class=logo-wrapper><a href=/en/ class=logo>Frank Wang’s Coding World</a></div><nav class=site-navbar><ul id=menu class=menu><li class=menu-item><a class=menu-item-link href=/en/>Home</a></li><li class=menu-item><a class=menu-item-link href=/en/post/>Archives</a></li><li class=menu-item><a class=menu-item-link href=/en/tags/>Tags</a></li><li class=menu-item><a class=menu-item-link href=/en/categories/>Categories</a></li><li class=menu-item><a class=menu-item-link href=/>中文</a></li></ul></nav></header><main id=main class=main><div class=content-wrapper><div id=content class=content><article class=post><header class=post-header><h1 class=post-title>Mysql Notes 3</h1><div class=post-meta><span class=post-time>2017-06-27</span><div class=post-category><a href=/en/categories/database/>Database</a></div><span class=more-meta>445 words </span><span class=more-meta>3 mins read</span></div></header><div class=post-toc id=post-toc><h2 class=post-toc-title>Contents</h2><div class="post-toc-content always-active"><nav id=TableOfContents><ul><li><a href=#1-add-or-delete-columns>1. Add or delete columns</a></li><li><a href=#2-add-constraints>2. Add constraints</a></li><li><a href=#3-delete-constraints>3. Delete constraints:</a></li><li><a href=#4-modify-column-definition>4. Modify column definition:</a></li><li><a href=#5-modify-the-name-of-the-column>5. Modify the name of the column:</a></li><li><a href=#6-data-table-rename>6. Data table rename:</a></li></ul></nav></div></div><div class=post-content><h2 id=1-add-or-delete-columns>1. Add or delete columns</h2><ol><li>1 Add a single column</li></ol><p>Syntax: <code>ALTER TABLE tbl_name ADD [COLUMN] col_name col_definition [FIRST | AFTER col_name];</code></p><p>Keywords that specify positional relationships:</p><ol><li><p>FIRST: Indicated in the first column of the data table</p></li><li><p>AFTER: The parameter col_name is the name of a data column, which means it is specified after the data column.</p></li></ol><p>The sample code is as follows:</p><p>ALTER TABLE users ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;</p><p>ALTER TABLE users ADD password VARCHAR(20) NOT NULL AFTER username;</p><p>ALTER TABLE users ADD truename VARCHAR(20) NOT NULL FIRST;</p><ol><li>2 Add multiple columns (positional relationship cannot be specified)</li></ol><p>ALTER TABLE tbl_name ADD [COLUMN] (col_name1 col_definition, col_name2 col_definition,&mldr;);</p><ol><li>3 Delete columns</li></ol><p>Syntax: `ALTER TABLE tbl_name DROP col_name1 [, DROP col_name2];</p><p>ALTER TABLE users DROP truename;</p><h2 id=2-add-constraints>2. Add constraints</h2><ol start=2><li>1 Add primary key constraints: (data table can only have one primary key)</li></ol><p>Syntax: <code>ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name);</code></p><p>ALTER TABLE users3 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);</p><ol start=2><li>2 Add unique constraints: There can be multiple unique constraints</li></ol><p>Syntax: <code>ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [ index_type] (index_col_name, ...);</code></p><p>ALTER TABLE users3 ADD UNIQUE (username);</p><ol start=2><li>3 Add foreign key constraints:</li></ol><p>Syntax: <code>ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_colo_name) reference_definition;</code></p><p>ALTER TABLE users3 ADD FOREIGEN KEY (pid) REFERENCE provinces (id);</p><ol start=2><li>4 Add default constraints:</li></ol><p>Syntax: <code>ALTER TABLE tbl_name ALTER [COLUMN] col_name SET DEFAULT literal;</code></p><p>Add a default constraint to the age field, with a default value of 10:</p><p>ALTER TABLE users3 ALTER age SET DEFAULT 10;</p><h2 id=3-delete-constraints>3. Delete constraints:</h2><ol start=3><li>1 Delete primary key constraints:</li></ol><p>ALTER TABLE users DROP PRIMARY KEY;</p><ol start=3><li>2 Delete the unique constraint:</li></ol><p>ALTER TABLE users DROP INDEX username;</p><ol start=3><li>3 Delete foreign key constraints: you need to specify the foreign key name</li></ol><p>ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol</p><p>fk_symbol is the name assigned to the foreign key by the system, which can be viewed through the following command</p><p>SHOW CREATE TABLE users;</p><p>ALTER TABLE users DROP FOREIGN KEY users_ibfk_1;</p><ol start=3><li>4 Delete default constraints: fields need to be specified</li></ol><p>ALTER TABLE tbl_name ALTER [COLUMN] col_name DROP DEFAULT</p><p>For example: delete the default constraint on the age field</p><p>ALTER TABLE users3 ALTER age DROP DEFAULT;</p><h2 id=4-modify-column-definition>4. Modify column definition:</h2><p>ALTER TABLE tbl_name MODIFY [COLUMN] col_name col_definition [FIRST | AFTER col_name]</p><p>ALTER TABLE users MODIFY pid SMALLINT UNSIGNED NOT NULL FIRST;</p><h2 id=5-modify-the-name-of-the-column>5. Modify the name of the column:</h2><p>ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]</p><h2 id=6-data-table-rename>6. Data table rename:</h2><p>ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;</p><p>or</p><p>RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2 ,&mldr;]</p><p>Method 2 can rename multiple data tables at the same time.</p></div><footer class=post-footer><div class=post-tags><a href=/en/tags/mysql/>MySQL</a></div><nav class=post-nav><a class=prev href=/ss.html><i class="iconfont icon-left"></i>
<span class="prev-text nav-default">Ss</span>
<span class="prev-text nav-mobile">Prev</span>
</a><a class=next href=/use_gitment_as_comment_system_for_your_blog.html><span class="next-text nav-default">Use Gitment As Comment For Your Blog</span>
<span class="next-text nav-mobile">Next</span>
<i class="iconfont icon-right"></i></a></nav></footer></article></div></div></main><footer id=footer class=footer><div class=social-links><a href=mailto:frankwang0909@gmail.com class="iconfont icon-email" title=email></a><a href=https://www.twitter.com/frankwang0909 class="iconfont icon-twitter" title=twitter></a><a href=https://www.github.com/frankwang0909 class="iconfont icon-github" title=github></a><a href=http://frankwang0909.github.io/en/index.xml type=application/rss+xml class="iconfont icon-rss" title=rss></a></div><div class=copyright><span class=power-by>Powered by <a class=hexo-link href=https://gohugo.io>Hugo</a>
</span><span class=division>|</span>
<span class=theme-info>Theme -
<a class=theme-link href=https://github.com/olOwOlo/hugo-theme-even>Even</a>
</span><span class=copyright-year>&copy;
2016 -
2026<span class=heart><i class="iconfont icon-heart"></i></span><span></span></span></div></footer><div class=back-to-top id=back-to-top><i class="iconfont icon-up"></i></div></div><script type=text/javascript src=/lib/jquery/jquery-3.2.1.min.js></script><script type=text/javascript src=/lib/slideout/slideout-1.0.1.min.js></script><script type=text/javascript src=/lib/fancybox/jquery.fancybox-3.1.20.min.js></script><script type=text/javascript src=/js/main.min.4ae89da218555efa0e7093a20b92017d2e1202b66fff9fc2edf4cb8d44b44c6e.js></script><script type=text/javascript>window.MathJax={tex:{}}</script><script async src=https://cdn.jsdelivr.net/npm/mathjax@3.0.5/es5/tex-mml-chtml.js integrity="sha256-HGLuEfFcsUJGhvB8cQ8nr0gai9EucOOaIxFw7qxmd+w=" crossorigin=anonymous></script></body></html>