{"id":296,"date":"2022-06-13T00:09:33","date_gmt":"2022-06-12T16:09:33","guid":{"rendered":"https:\/\/frogvps.com\/?p=296"},"modified":"2022-06-13T00:11:28","modified_gmt":"2022-06-12T16:11:28","slug":"mysql-ddl%e8%af%ad%e5%8f%a5%e6%80%8e%e4%b9%88%e5%86%99","status":"publish","type":"post","link":"https:\/\/frogvps.com\/?p=296","title":{"rendered":"MYSQL DDL\u8bed\u53e5"},"content":{"rendered":"\n<p>DDL \u8bed\u53e5\u8be6\u89e3<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>\u521b\u5efa\u8868<\/strong><\/h2>\n\n\n\n<p>1.\u76f4\u63a5\u521b\u5efa\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE &#91;IF NOT EXISTS] 'tbl_name' (col1 type1 \u4fee\u9970\u7b26, col2 type2 \u4fee\u9970\u7b26, ...)\n#\u5b57\u6bb5\u4fe1\u606f\ncol type1\nPRIMARY KEY(col1,...)\nINDEX(col1, ...)\nUNIQUE KEY(col1, ...)\n#\u8868\u9009\u9879\uff1a\nENGINE &#91;=] engine_name\nROW_FORMAT &#91;=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}\n#\u540c\u4e00\u4e2a\u6570\u636e\u5e93\u4e2d\u7684\u8868\u5efa\u8bae\u4f7f\u7528\u540c\u4e00\u79cd\u5b58\u50a8\u5f15\u64ce\u7c7b\u578b\u3002<\/code><\/pre>\n\n\n\n<p>\u8303\u4f8b\uff0c\u521b\u5efastudent\u4fe1\u606f\u8868\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE student (\nid int UNSIGNED AUTO_INCREMENT PRIMARY KEY,\nname VARCHAR(20) NOT NULL,\nage tinyint UNSIGNED,\ngender ENUM('M','F') default 'M'\n)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;<\/code><\/pre>\n\n\n\n<p>\u8303\u4f8b\uff0c\u521b\u5efa\u65f6\u95f4\u7c7b\u578b\u8868\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE testdate (\nid int AUTO_INCREMENT PRIMARY KEY,\ndate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);<\/code><\/pre>\n\n\n\n<p>2.\u901a\u8fc7\u67e5\u8be2\u73b0\u5b58\u8868\u521b\u5efa<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE user SELECT user,host FROM mysql.user;<\/code><\/pre>\n\n\n\n<p>3.\u901a\u8fc7\u590d\u5236\u73b0\u5b58\u7684\u8868\u7684\u8868\u7ed3\u6784\u521b\u5efa\uff0c\u4f46\u4e0d\u590d\u5236\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE &#91;TEMPORARY] TABLE &#91;IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE\nold_tbl_name) }<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">\u67e5\u770b\u8868<\/h2>\n\n\n\n<p>\u67e5\u770b\u8868\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SHOW TABLES &#91;FROM db_name]<\/code><\/pre>\n\n\n\n<p>\u67e5\u770b\u8868\u521b\u5efa\u547d\u4ee4\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SHOW CREATE TABLE tbl_name<\/code><\/pre>\n\n\n\n<p>\u67e5\u770b\u8868\u7ed3\u6784\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DESC &#91;db_name.]tb_name\nSHOW COLUMNS FROM &#91;db_name.]tb_name<\/code><\/pre>\n\n\n\n<p>\u67e5\u770b\u8868\u72b6\u6001\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SHOW TABLE STATUS LIKE 'tbl_name'\\G\nSHOW TABLE STATUS FROM db_name<\/code><\/pre>\n\n\n\n<p>\u67e5\u770b\u652f\u6301\u7684engine\u7c7b\u578b\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SHOW ENGINES<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">\u4fee\u6539\u8868<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE 'tbl_name'\n#\u5b57\u6bb5\uff1a\n#\u6dfb\u52a0\u5b57\u6bb5\uff1aadd\nADD col1 data_type &#91;FIRST|AFTER col_name]\n#\u5220\u9664\u5b57\u6bb5\uff1adrop\n#\u4fee\u6539\u5b57\u6bb5\uff1a\nalter\uff08\u9ed8\u8ba4\u503c\uff09, change\uff08\u5b57\u6bb5\u540d\uff09, modify\uff08\u5b57\u6bb5\u5c5e\u6027\uff09<\/code><\/pre>\n\n\n\n<p>\u4fee\u6539\u8868\u540d\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE students RENAME stu;<\/code><\/pre>\n\n\n\n<p>\u5728\u67d0\u4e2a\u5b57\u6bb5\u540e\u6dfb\u52a0\u5b57\u6bb5\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE stu ADD phone varchar(11) AFTER name;<\/code><\/pre>\n\n\n\n<p>\u4fee\u6539\u5b57\u6bb5\u7684\u6570\u636e\u7c7b\u578b\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE stu MODIFY phone int;<\/code><\/pre>\n\n\n\n<p>\u4fee\u6539\u5b57\u6bb5\u540d\u79f0\u548c\u7c7b\u578b\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE stu CHANGE COLUMN phone mobile char(11);<\/code><\/pre>\n\n\n\n<p>\u5220\u9664\u5b57\u6bb5\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE stu DROP COLUMN mobile;<\/code><\/pre>\n\n\n\n<p>\u4fee\u6539\u5b57\u7b26\u96c6\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE stu CHARACTER SET utf8mb4;<\/code><\/pre>\n\n\n\n<p>\u4fee\u6539\u5b57\u6bb5\u540d\u548c\u7c7b\u578b\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE stu CHANGE stuid id int UNSIGNED NOT NULL;<\/code><\/pre>\n\n\n\n<p>\u6dfb\u52a0\u4e3b\u952e\uff08\u4e00\u4e2a\u8868\u53ea\u80fd\u6709\u4e00\u4e2a\u4e3b\u952e\uff09\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE stu add primary key (id);<\/code><\/pre>\n\n\n\n<p>\u5220\u9664\u4e3b\u952e\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE stu DROP primary key;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">\u5220\u9664\u8868<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP TABLE &#91;IF EXISTS] 'tbl_name';<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>DDL \u8bed\u53e5\u8be6\u89e3 \u521b\u5efa\u8868 1.\u76f4\u63a5\u521b\u5efa\uff1a \u8303\u4f8b\uff0c\u521b\u5efastudent\u4fe1\u606f\u8868\uff1a \u8303\u4f8b\uff0c\u521b\u5efa\u65f6\u95f4\u7c7b\u578b\u8868\uff1a 2.\u901a\u8fc7\u67e5\u8be2\u73b0\u5b58\u8868\u521b\u5efa 3.\u901a\u8fc7\u590d\u5236\u73b0\u5b58\u7684\u8868\u7684\u8868\u7ed3\u6784\u521b\u5efa\uff0c\u4f46\u4e0d\u590d\u5236\u6570\u636e \u67e5\u770b\u8868 \u67e5\u770b\u8868\uff1a \u67e5\u770b\u8868\u521b\u5efa\u547d\u4ee4\uff1a \u67e5\u770b\u8868\u7ed3\u6784\uff1a \u67e5\u770b\u8868\u72b6\u6001\uff1a \u67e5\u770b&#8230;<\/p>\n","protected":false},"author":1,"featured_media":283,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35],"tags":[36],"topic":[],"class_list":["post-296","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-35","tag-mysql"],"_links":{"self":[{"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/posts\/296","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=296"}],"version-history":[{"count":3,"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/posts\/296\/revisions"}],"predecessor-version":[{"id":299,"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/posts\/296\/revisions\/299"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/media\/283"}],"wp:attachment":[{"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=296"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=296"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=296"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftopic&post=296"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}