{"id":1982,"date":"2024-12-10T09:05:39","date_gmt":"2024-12-10T01:05:39","guid":{"rendered":"http:\/\/114.55.108.251\/?p=1982"},"modified":"2024-12-11T13:04:24","modified_gmt":"2024-12-11T05:04:24","slug":"mysql%e7%ac%94%e8%ae%b0","status":"publish","type":"post","link":"https:\/\/guapicoding.com\/?p=1982","title":{"rendered":"MySQL\u7b14\u8bb0"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">\u547d\u4ee4<\/h2>\n\n\n\n<h4 class=\"wp-block-heading\">DDL-\u5b9a\u4e49\u6570\u636e\u5e93\u3001\u8868\u7ed3\u6784<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u6570\u636e\u5e93\u64cd\u4f5c<\/strong>\n<ul class=\"wp-block-list\">\n<li>\u67e5\u8be2\u6240\u6709\u6570\u636e\u5e93\uff1a<code>SHOW DATABASES;<\/code><\/li>\n\n\n\n<li>\u67e5\u8be2\u5f53\u524d\u6570\u636e\u5e93\uff1a<code>SELECT DATABASE();<\/code><\/li>\n\n\n\n<li>\u521b\u5efa\u6570\u636e\u5e93\uff1a<code>CREATE DATABASE \u6570\u636e\u5e93\u540d;<\/code><\/li>\n\n\n\n<li>\u4f7f\u7528\u6570\u636e\u5e93\uff1a<code>USE \u6570\u636e\u5e93\u540d;<\/code><\/li>\n\n\n\n<li>\u5220\u9664\u6570\u636e\u5e93\uff1a<code>DROP DATABASES \u6570\u636e\u5e93\u540d;<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>\u8868\u64cd\u4f5c<\/strong>\n<ul class=\"wp-block-list\">\n<li>\u67e5\u8be2\n<ul class=\"wp-block-list\">\n<li>\u67e5\u8be2\u5f53\u524d\u6570\u636e\u5e93\u5185\u6240\u6709\u8868\uff1a<code>SHOW TABLES;<\/code><\/li>\n\n\n\n<li>\u67e5\u8be2\u8868\u7ed3\u6784\uff1a<code>DESC \u8868\u540d;<\/code><\/li>\n\n\n\n<li>\u67e5\u8be2\u6307\u5b9a\u8868\u7684\u5efa\u8868\u8bed\u53e5\uff1a<code>SHOW CREATE TABLE \u8868\u540d;<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>\u521b\u5efa\n<ul class=\"wp-block-list\">\n<li>\u521b\u5efa\u8868\uff1a<code>CREATE TABLE \u8868\u540d(\u5b57\u6bb51 \u5b57\u6bb51\u7c7b\u578b \u5b57\u6bb51\u7ea6\u675f, \u5b57\u6bb52 \u5b57\u6bb52\u7c7b\u578b \u5b57\u6bb52\u7ea6\u675f...);<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>\u4fee\u6539\n<ul class=\"wp-block-list\">\n<li>\u5220\u9664\u8868\uff1a\n<ul class=\"wp-block-list\">\n<li><code>DROP TABLE \u8868\u540d;<\/code> \/\/\u5220\u9664\u8be5\u8868\u4ee5\u53ca\u8868\u5185\u6570\u636e<\/li>\n\n\n\n<li><code>TRUNCATE TABLE \u8868\u540d;<\/code> \/\/\u53ea\u5220\u9664\u8868\u5185\u6570\u636e<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>\u4fee\u6539\u5b57\u6bb5\uff1a<code>ALTER TABLE \u8868\u540d ADD\/MODIFY\/CHANGE\/DROP\/RENAME TO ...;<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">DML-\u6570\u636e\u589e\u5220\u6539<\/h4>\n\n\n\n<p><strong>\u6dfb\u52a0\u6570\u636e\uff1a<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># \u7ed9\u6307\u5b9a\u5b57\u6bb5\u6dfb\u52a0\u6570\u636e\nINSERT INTO \u8868\u540d(\u5b57\u6bb5\u540d1, \u5b57\u6bb5\u540d2, ...) VALUES(\u503c1, \u503c2, ...);\n# \u7ed9\u5168\u90e8\u5b57\u6bb5\u6dfb\u52a0\u6570\u636e\nINSERT INTO \u8868\u540d VALUES(\u503c1, \u503c2, ...);\n# \u6279\u91cf\u6dfb\u52a0\u6570\u636e\nINSERT INTO \u8868\u540d(\u5b57\u6bb5\u540d1, \u5b57\u6bb5\u540d2, ...) VALUES (\u503c1, \u503c2, ...),(\u503c1, \u503c2, ...),(\u503c1, \u503c2, ...);\nINSERT INTO \u8868\u540d VALUES(\u503c1, \u503c2, ...),(\u503c1, \u503c2, ...),(\u503c1, \u503c2, ...);<\/code><\/pre>\n\n\n\n<p><strong>\u4fee\u6539\u6570\u636e\uff1a<\/strong><code>UPDATE \u8868\u540d SET \u5b57\u6bb5\u540d1 = \u503c1 , \u5b57\u6bb5\u540d2 = \u503c2 , .... [ WHERE \u6761\u4ef6 ];<\/code><\/p>\n\n\n\n<p><strong>\u5220\u9664\u6570\u636e\uff1a<\/strong><code>DELETE FROM \u8868\u540d [ WHERE \u6761\u4ef6 ];<\/code><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">DQL-\u67e5\u8be2\u8bed\u53e5-select<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u57fa\u672c\u67e5\u8be2\uff1a\n<ul class=\"wp-block-list\">\n<li><code>SELECT \u5b57\u6bb51, \u5b57\u6bb52, \u5b57\u6bb53 \u2026 FROM \u8868\u540d;<\/code> # \u67e5\u8be2\u591a\u4e2a\u5b57\u6bb5<\/li>\n\n\n\n<li><code>SELECT * FROM \u8868\u540d;<\/code> # \u8fd4\u56de\u6240\u6709\u5b57\u6bb5<\/li>\n\n\n\n<li><code>SELECT \u5b57\u6bb51 [ AS \u522b\u540d1 ] , \u5b57\u6bb52 [ AS \u522b\u540d2 ] \u2026 FROM \u8868\u540d;<\/code> # \u8bbe\u7f6e\u522b\u540d\uff0cAS\u53ef\u7701\u7565\uff0c\u589e\u5f3a\u5b57\u6bb5\u53ef\u8bfb\u6027<\/li>\n\n\n\n<li><code>SELECT DISTINCT \u5b57\u6bb5\u5217\u8868 FROM \u8868\u540d; <\/code># \u67e5\u8be2\u65f6\u4f1a\u53bb\u9664\u91cd\u590d\u8bb0\u5f55<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>\u6761\u4ef6\u67e5\u8be2(where)\uff1a<code>SELECT \u5b57\u6bb5\u5217\u8868 FROM \u8868\u540d WHERE \u6761\u4ef6\u5217\u8868;<\/code><\/li>\n\n\n\n<li>\u805a\u5408\u51fd\u6570(count\u3001max\u3001min\u3001avg\u3001sum)\uff1a<code>SELECT \u805a\u5408\u51fd\u6570(\u5b57\u6bb5\u5217\u8868) FROM \u8868\u540d;<\/code><\/li>\n\n\n\n<li>\u5206\u7ec4\u67e5\u8be2(group by)\uff1a<code>SELECT \u5b57\u6bb5\u5217\u8868 FROM \u8868\u540d [ WHERE \u6761\u4ef6 ] GROUP BY \u5206\u7ec4\u5b57\u6bb5\u540d [ HAVING \u5206\u7ec4\u540e\u8fc7\u6ee4\u6761\u4ef6];<\/code><\/li>\n\n\n\n<li>\u6392\u5e8f\u67e5\u8be2(order by)\uff1a<code>SELECT \u5b57\u6bb5\u5217\u8868 FROM \u8868\u540d ORDER BY \u5b57\u6bb51 \u6392\u5e8f\u65b9\u5f0f1 , \u5b57\u6bb52 \u6392\u5e8f\u65b9\u5f0f2 ;<\/code><\/li>\n\n\n\n<li>\u5206\u9875\u67e5\u8be2(limit)\uff1a<code>SELECT \u5b57\u6bb5\u5217\u8868 FROM \u8868\u540d LIMIT \u8d77\u59cb\u7d22\u5f15, \u67e5\u8be2\u8bb0\u5f55\u6570;<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u5b89\u88c5\u4e0e\u914d\u7f6e<\/h2>\n\n\n\n<h4 class=\"wp-block-heading\">\u5b89\u88c5<\/h4>\n\n\n\n<p>\u5b89\u88c5\u547d\u4ee4\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo apt update \/\/\u83b7\u53d6\u8f6f\u4ef6\u66f4\u65b0\u5305\nsudo apt upgrade \/\/\u6267\u884c\u8f6f\u4ef6\u66f4\u65b0(\u4e0emysql\u5b89\u88c5\u65e0\u5173)\nsudo apt-get install mysql-server \/\/Ubuntu20.04\u4ee5\u4e0a\u7248\u672c\u9ed8\u8ba4\u5b89\u88c5mysql8<\/code><\/pre>\n\n\n\n<p>\u67e5\u770b\u8fd0\u884c\u72b6\u6001\uff1a<code>systemctl status mysql<\/code><\/p>\n\n\n\n<p>\u67e5\u770b\u5b89\u88c5\u8fc7\u7a0b\u4e2d\u7cfb\u7edf\u9ed8\u8ba4\u521b\u5efa\u7684\u7528\u6237\uff1a<code>sudo cat \/etc\/mysql\/debian.cnf<\/code><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u9996\u6b21\u767b\u5f55<\/h4>\n\n\n\n<p>\u9ed8\u8ba4\u7528\u6237\u767b\u5f55\uff1a<code>mysql -u debian-sys-maint -p<\/code> \/\/\u4f7f\u7528\u4e0a\u9762\u67e5\u627e\u5230\u7684\u9ed8\u8ba4\u7528\u6237\u540d\uff0c\u6839\u636e\u63d0\u793a\u8f93\u5165\u9ed8\u8ba4\u7528\u6237\u5bc6\u7801<\/p>\n\n\n\n<p>\u4fee\u6539\u5bc6\u7801\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u67e5\u770b<code>user<\/code>\u8868\uff1a\n<ul class=\"wp-block-list\">\n<li><code>use mysql;<\/code><\/li>\n\n\n\n<li><code>select user,plugin from user;<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>\u4fee\u6539<code>root<\/code>\u5bc6\u7801\uff1a\n<ul class=\"wp-block-list\">\n<li>\u4fee\u6539<code>root<\/code>\u5bc6\u7801\u683c\u5f0f\uff1a<code>update user set plugin='mysql_native_password' where user='root'; <\/code><\/li>\n\n\n\n<li>\u5237\u65b0\u6743\u9650\uff1a<code>flush privileges;<\/code> <\/li>\n\n\n\n<li>\u4fee\u6539\u5bc6\u7801\uff1a<code>alter user 'root'@'localhost' identified by 'mysql0217';<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">\u5378\u8f7d<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u5378\u8f7dMySQL\u7684\u6240\u6709\u76f8\u5173\u8f6f\u4ef6\u5305\uff1a<code>sudo apt purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*<\/code><\/li>\n\n\n\n<li>\u5220\u9664\u76f8\u5173\u6587\u4ef6\uff1a\n<ul class=\"wp-block-list\">\n<li>\u914d\u7f6e\u6587\u4ef6\uff1a<code>sudo rm -rf \/etc\/mysql<\/code><\/li>\n\n\n\n<li>\u6570\u636e\u6587\u4ef6\uff1a<code>sudo rm -rf \/var\/lib\/mysql<\/code><\/li>\n\n\n\n<li>\u65e5\u5fd7\u6587\u4ef6\uff1a\n<ul class=\"wp-block-list\">\n<li><code>sudo rm -rf \/var\/log\/mysql<\/code><\/li>\n\n\n\n<li><code>sudo rm -rf \/var\/log\/mysql.*<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>\u5220\u9664\u6b8b\u4f59\u4f9d\u8d56\u5305\uff1a<code>sudo apt autoremove --purge<\/code><\/li>\n\n\n\n<li>\u9a8c\u8bc1\u7cfb\u7edf\u4e2d\u662f\u5426\u4ecd\u6709\u4e0eMySQL\u76f8\u5173\u7684\u6587\u4ef6\u6ca1\u88ab\u6e05\u7406\uff1a<code>dpkg -l | grep mysql<\/code><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>\u547d\u4ee4 DDL-\u5b9a\u4e49\u6570\u636e\u5e93\u3001\u8868\u7ed3\u6784 DML-\u6570\u636e\u589e\u5220\u6539 \u6dfb\u52a0\u6570\u636e\uff1a \u4fee\u6539\u6570\u636e\uff1aUPDATE \u8868\u540d SET \u5b57\u6bb5\u540d [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[21,66],"class_list":["post-1982","post","type-post","status-publish","format-standard","hentry","category-9","tag-linux","tag-mysql"],"_links":{"self":[{"href":"https:\/\/guapicoding.com\/index.php?rest_route=\/wp\/v2\/posts\/1982","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/guapicoding.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/guapicoding.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/guapicoding.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/guapicoding.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1982"}],"version-history":[{"count":19,"href":"https:\/\/guapicoding.com\/index.php?rest_route=\/wp\/v2\/posts\/1982\/revisions"}],"predecessor-version":[{"id":2013,"href":"https:\/\/guapicoding.com\/index.php?rest_route=\/wp\/v2\/posts\/1982\/revisions\/2013"}],"wp:attachment":[{"href":"https:\/\/guapicoding.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1982"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/guapicoding.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1982"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/guapicoding.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1982"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}