{"id":1816,"date":"2020-07-01T05:51:36","date_gmt":"2020-06-30T21:51:36","guid":{"rendered":"http:\/\/39.97.231.10\/?p=1816"},"modified":"2020-07-01T05:51:36","modified_gmt":"2020-06-30T21:51:36","slug":"mysql-%e5%b8%b8%e7%94%a8%e5%91%bd%e4%bb%a4","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=1816","title":{"rendered":"MySQL \u5e38\u7528\u547d\u4ee4"},"content":{"rendered":"
\n
\n
\n
\u67e5\u770b\u6570\u636e\u5e93<\/span><\/div>\n<\/div>\n<\/div>\n<\/div>\n
SHOW DATABASES;<\/p>\n
\u521b\u5efa\u6570\u636e\u5e93<\/h5>\n

CREATE DATABASE IF NOT EXISTS \u6570\u636e\u5e93\u540d;<\/p>\n

\u9009\u62e9\u6570\u636e\u5e93<\/h5>\n

USE \u6570\u636e\u5e93\u540d;<\/p>\n

\u67e5\u770b\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\u8868<\/h5>\n

SHOW TABLES;<\/p>\n

\u5220\u9664\u6570\u636e\u5e93<\/h5>\n

DROP DATABASE IF EXISTS \u6570\u636e\u5e93\u540d;<\/p>\n

\u521b\u5efa\u4e00\u4e2a\u7b80\u5355\u7684\u6570\u636e\u5e93\u8868<\/h5>\n

CREATE TABLE IF NOT EXISTS \u8868\u540d(
\nid INT UNSTGND AUTO_INCREMENT PRIMARY KEY,
\nname VARCHAR(255) NOT NULL,
\nsex TINYINT NOT NULL DEFAULT 1,
\nage TINYINT NOT NULL DEFAULT 0
\n)ENGINE = MyISAM DEFAULT CHARSET=utf8;<\/p>\n

\u6dfb\u52a0\u6570\u636e<\/h5>\n

INSERT INTO \u8868\u540d VALUES(NULL,\\’cendxia\\’,1,22);<\/p>\n

\u67e5\u8be2\u6570\u636e<\/h5>\n

SELECT * FROM \u8868\u540d;<\/p>\n

\u4fee\u6539\u6570\u636e<\/h5>\n

UPDATE \u8868\u540d SET \u5b57\u6bb51 = \\’\u503c1\\’,\u5b57\u6bb51=\\’\u503c2\\’ WHERE \u6761\u4ef6;<\/p>\n

\u5220\u9664\u6570\u636e<\/h5>\n

DELETE FROM \u8868\u540d WHERE \u6761\u4ef6;<\/p>\n

\u521b\u5efa\u65b0\u666e\u901a\u7528\u6237<\/h5>\n

GRANT \u6743\u9650 ON \u5e93\u540d.\u8868\u540d TO \\’\u7528\u6237\u540d\\’@\\’\u4e3b\u673a\u540d\\’ IDENTIFIED BY \\’\u5bc6\u7801\\’<\/p>\n

\u67e5\u8be2\u6240\u6709\u7528\u6237<\/h5>\n

SELECT user,host FROM mysql.user<\/p>\n

\u5220\u9664\u666e\u901a\u7528\u6237<\/h5>\n

DROP USER \\’\u7528\u6237\u540d\\’@\\’\u4e3b\u673a\u540d\\’;<\/p>\n

\u4fee\u6539root\u7528\u6237\u5bc6\u7801<\/h5>\n

SET PASSWORD = PASSWORD(\\’\u65b0\u5bc6\u7801\\’);<\/p>\n

root\u7528\u6237\u4fee\u6539\u666e\u901a\u7528\u6237\u5bc6\u7801<\/h5>\n

SET PASSWORD FOR \\’\u7528\u6237\u540d\\’@\\’\u4e3b\u673a\u540d\\’=PASSWORD(\\’\u65b0\u5bc6\u7801\\’);<\/p>\n

\u6388\u6743<\/h5>\n

GRANT \u6743\u9650 ON \u5e93\u540d.\u8868\u540d TO \\’\u7528\u6237\u540d\\’@\\’\u4e3b\u673a\u540d\\’ IDENTIFIED BY \\’\u5bc6\u7801\\’;
\nGRANT SELECT,INSERT,UPDATE,DELETE ON cendxia.user TO \\’\u7528\u6237\u540d\\’@\\’\u4e3b\u673a\u540d\\’ IDENTIFIED BY \\’\u5bc6\u7801\\’;<\/p>\n

\u67e5\u770b\u6743\u9650<\/h5>\n

SHOW GRANTS FOR \\’\u7528\u6237\u540d\\’@\\’\u4e3b\u673a\u540d\\’;<\/p>\n

\u6536\u56de\u6743\u9650<\/h5>\n

REVOKE \u6743\u9650 ON \u5e93\u540d.\u8868\u540d FROM \\’\u7528\u6237\u540d\\’@\\’\u4e3b\u673a\u540d\\’;<\/p>\n

\u5907\u4efd<\/h5>\n

mysqldump -uroot -p \u6570\u636e\u5e93\u540d > \u8981\u4fdd\u5b58\u7684\u4f4d\u7f6e<\/p>\n

\u8fd8\u539f\u6570\u636e<\/h5>\n

mysql -uroot -p \u6570\u636e\u5e93\u540d<\/p>\n

\u521b\u5efa\u6570\u636e\u8868<\/h5>\n

CREATE TABLE IF NOT EXISTS \u8868\u540d(
\n\u5b57\u6bb51 \u7c7b\u578b(\u957f\u5ea6) \u5c5e\u6027 \u7d22\u5f15,
\n\u5b57\u6bb52 \u7c7b\u578b(\u957f\u5ea6) \u5c5e\u6027 \u7d22\u5f15,
\n\u5b57\u6bb53 \u7c7b\u578b(\u957f\u5ea6) \u5c5e\u6027 \u7d22\u5f15,
\n\u5b57\u6bb54 \u7c7b\u578b(\u957f\u5ea6) \u5c5e\u6027 \u7d22\u5f15,
\n\u5b57\u6bb5n…… –\u6700\u540e\u4e00\u4e2a\u5b57\u6bb5\u540e\u9762\u4e0d\u8981\u52a0\u9017\u53f7
\n)ENGINE=MyISAM DEFAULT CHARSET=UTF8;<\/p>\n

\u5efa\u8868\u5f15\u64ce<\/h5>\n

MyISAM –\u8bfb\u53d6\u901f\u5ea6\u5feb\uff0c\u4e0d\u652f\u6301\u4e8b\u52a1
\nInnoDB –\u8bfb\u53d6\u901f\u5ea6\u7a0d\u6162 \u652f\u6301\u4e8b\u52a1 \u4e8b\u52a1\u56de\u6eda<\/p>\n

\u4e00\u4e9b\u5e38\u7528\u5c5e\u6027<\/h5>\n

UNSTGND \u65e0\u7b26\u53f7\u5c5e\u6027
\nAUTO_INCREMENT \u81ea\u589e\u5c5e\u6027(\u4e00\u822c\u7528\u5728id\u5b57\u6bb5\u4e0a)
\nZEROFILL \u96f6\u586b\u5145<\/p>\n

\u5b57\u7b26\u4e32\u7c7b\u578b<\/h5>\n

CHAR \u5b9a\u957f\u7684\u5b57\u7b26\u4e32\u7c7b\u578b (0-255)\u4e2a\u5b57\u7b26
\nVARCHAR \u53d8\u957f\u7684\u5b57\u7b26\u4e32\u7c7b\u578b\uff0c5.0\u4ee5\u524d(0-255)\u4e2a\u5b57\u7b26\uff0c5.0\u7248\u672c\u4ee5\u540e(0-65535)\u4e2a\u5b57\u7b26<\/p>\n

\u67e5\u770b\u8868\u7ed3\u6784<\/h5>\n

DESC \u8868\u540d; (\u7f29\u5199\u7248)
\nDESCRIBE \u8868\u540d;<\/p>\n

\u67e5\u770b\u5efa\u8868\u8bed\u53e5<\/h5>\n

SHOW CREATE TABLE \u8868\u540d;<\/p>\n

\u4fee\u6539\u8868\u540d<\/h5>\n

ALTER TABLE \u539f\u8868\u540d RENAME TO \u65b0\u8868\u540d;<\/p>\n

\u4fee\u6539\u5b57\u6bb5\u7684\u6570\u636e\u7c7b\u578b<\/h5>\n

ALTER TABLE \u8868\u540d MODIFY \u5b57\u6bb5\u540d \u6570\u636e\u7c7b\u578b \u5c5e\u6027 \u7d22\u5f15;<\/p>\n

\u4fee\u6539\u5b57\u6bb5\u540d<\/h5>\n

ALTER TABLE \u8868\u540d CHANGE \u539f\u5b57\u6bb5\u540d \u65b0\u5b57\u6bb5\u540d \u6570\u636e\u7c7b\u578b \u5c5e\u6027 \u7d22\u5f15;<\/p>\n

\u589e\u52a0\u5b57\u6bb5<\/h5>\n

ALTER TABLE \u8868\u540d ADD \u5b57\u6bb5\u540d \u6570\u636e\u7c7b\u578b \u5c5e\u6027 \u7d22\u5f15;
\n— [FIRST|AFIER \u5b57\u6bb5\u540d]
\n— (FIRST \u5728\u6700\u524d\u9762\u6dfb\u52a0\u5b57\u6bb5\u3002AFIER \u5b57\u6bb5\u540d \u5728\u67d0\u5b57\u6bb5\u540e\u9762\u6dfb\u52a0)<\/p>\n

\u5220\u9664\u5b57\u6bb5<\/h5>\n

ALTER TABLE \u8868\u540d DROP \u5b57\u6bb5\u540d;<\/p>\n

\u4fee\u6539\u5b57\u6bb5\u7684\u6392\u5217\u4f4d\u7f6e<\/h5>\n

ALTER TABLE \u8868\u540d MODIFY \u5b57\u6bb5\u540d \u6570\u636e\u7c7b\u578b \u5c5e\u6027 \u7d22\u5f15 AFIER \u5b57\u6bb5\u540d;<\/p>\n

\u4fee\u6539\u8868\u5f15\u64ce<\/h5>\n

ALTER TABLE \u8868\u540d ENGINE=\u5f15\u64ce\u540d; –MyISAM \u6216 InnoDB
\n<\/article>\n","protected":false},"excerpt":{"rendered":"

\u67e5\u770b\u6570\u636e\u5e93 SHOW DATABASES; \u521b\u5efa\u6570\u636e\u5e93 CREATE DATABASE IF NOT EXIS …<\/p>\n

MySQL \u5e38\u7528\u547d\u4ee4<\/span> \u67e5\u770b\u5168\u6587 »<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[90,88],"tags":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"mailpoet_newsletter_max":false,"woocommerce_thumbnail":false,"woocommerce_single":false,"woocommerce_gallery_thumbnail":false,"shop_catalog":false,"shop_single":false,"shop_thumbnail":false},"uagb_author_info":{"display_name":"weijing","author_link":"http:\/\/www.weijing.co\/?author=4"},"uagb_comment_info":0,"uagb_excerpt":"\u67e5\u770b\u6570\u636e\u5e93 SHOW DATABASES; \u521b\u5efa\u6570\u636e\u5e93 CREATE DATABASE IF NOT EXIS…","_links":{"self":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1816"}],"collection":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1816"}],"version-history":[{"count":0,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1816\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1816"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1816"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1816"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}