1\uff09\u5355\u5217\u7d22\u5f15
\u666e\u901a\u7d22\u5f15\uff1aMySQL\u4e2d\u57fa\u672c\u7d22\u5f15\u7c7b\u578b\uff0c\u6ca1\u6709\u4ec0\u4e48\u9650\u5236\uff0c\u5141\u8bb8\u5728\u5b9a\u4e49\u7d22\u5f15\u7684\u5217\u4e2d\u63d2\u5165\u91cd\u590d\u503c\u548c\u7a7a\u503c\uff0c\u7eaf\u7cb9\u4e3a\u4e86\u67e5\u8be2\u6570\u636e\u66f4\u5feb\u4e00\u70b9\u3002<\/p>\n\n\n\n
\u552f\u4e00\u7d22\u5f15\uff1a\u7d22\u5f15\u5217\u4e2d\u7684\u503c\u5fc5\u987b\u662f\u552f\u4e00\u7684\uff0c\u4f46\u662f\u5141\u8bb8\u4e3a\u7a7a\u503c\u3002<\/p>\n\n\n\n
\u4e3b\u952e\u7d22\u5f15\uff1a\u662f\u4e00\u79cd\u7279\u6b8a\u7684\u552f\u4e00\u7d22\u5f15\uff0c\u4e0d\u5141\u8bb8\u6709\u7a7a\u503c\u3002<\/p>\n\n\n\n
2\uff09\u7ec4\u5408\u7d22\u5f15
\u5728\u8868\u4e2d\u7684\u591a\u4e2a\u5b57\u6bb5\u7ec4\u5408\u4e0a\u521b\u5efa\u7684\u7d22\u5f15<\/p>\n\n\n\n
\u7ec4\u5408\u7d22\u5f15\u7684\u4f7f\u7528\uff0c\u9700\u8981\u9075\u5faa\u6700\u5de6\u524d\u7f00\u539f\u5219\uff08\u6700\u5de6\u5339\u914d\u539f\u5219\uff0c\u540e\u9762\u9ad8\u7ea7\u7bc7\u8bb2\u89e3\uff09\u3002<\/p>\n\n\n\n
\u4e00\u822c\u60c5\u51b5\u4e0b\uff0c\u5efa\u8bae\u4f7f\u7528\u7ec4\u5408\u7d22\u5f15\u4ee3\u66ff\u5355\u5217\u7d22\u5f15\uff08\u4e3b\u952e\u7d22\u5f15\u9664\u5916\uff0c\u5177\u4f53\u539f\u56e0\u540e\u9762\u77e5\u8bc6\u70b9\u8bb2\u89e3\uff09\u3002<\/p>\n\n\n\n
3\uff09\u5168\u6587\u7d22\u5f15
\u53ea\u6709\u5728MyISAM\u5f15\u64ce\u4e0a\u624d\u80fd\u4f7f\u7528\uff0c\u800c\u4e14\u53ea\u80fd\u5728CHAR,VARCHAR,TEXT\u7c7b\u578b\u5b57\u6bb5\u4e0a\u4f7f\u7528\u5168\u6587\u7d22\u5f15\u3002<\/p>\n\n\n\n
4\uff09\u7a7a\u95f4\u7d22\u5f15
\u4e0d\u505a\u4ecb\u7ecd\uff0c\u4e00\u822c\u4f7f\u7528\u4e0d\u5230\u3002<\/p>\n\n\n\n
\u5b50\u67e5\u8be2\uff08\u5d4c\u5957\u67e5\u8be2\uff09 <\/p>\n\n\n\n
\u4e00\u4e2a select \u8bed\u53e5\u4e2d,\u5d4c\u5165\u53e6\u5916\u4e00\u4e2a select \u8bed\u53e5, \u88ab\u5d4c\u5165\u7684 select \u8bed\u53e5\u79f0\u4e3a\u5b50\u67e5\u8be2\u8bed\u53e5\uff0c\u5916\u90e8select\u8bed\u53e5\u5219\u79f0\u4e3a\u4e3b\u67e5\u8be2\u3002 \u4e3b\u67e5\u8be2\u548c\u5b50\u67e5\u8be2\u7684\u5173\u7cfb \u5b50\u67e5\u8be2\u662f\u5d4c\u5165\u5230\u4e3b\u67e5\u8be2\u4e2d \u5b50\u67e5\u8be2\u662f\u8f85\u52a9\u4e3b\u67e5\u8be2\u7684\uff0c\u8981\u4e48\u5145\u5f53\u6761\u4ef6,\u8981\u4e48\u5145\u5f53\u6570\u636e\u6e90 \u5b50\u67e5\u8be2\u662f\u53ef\u4ee5\u72ec\u7acb\u5b58\u5728\u7684\u8bed\u53e5\uff0c\u662f\u4e00\u6761\u5b8c\u6574\u7684 select \u8bed\u53e5 <\/p>\n\n\n\n
\u8bed\u6cd5\uff1a\u8bed\u6cd5\uff1a\u5d4c\u5957\u67e5\u8be2\u4e5f\u5c31\u662f\u5728\u67e5\u8be2\u8bed\u53e5\u4e2d\u5305\u542b\u6709\u5b50\u67e5\u8be2\u8bed\u53e5\uff0c\u6240\u4ee5\u53eb\u5d4c\u5957\u67e5\u8be2\uff0c\u6ca1\u6709\u5355\u72ec\u7684\u8bed\u6cd5\uff0c\u5d4c\u5957\u5b50\u67e5\u8be2\u901a\u5e38\u4f4d\u4e8e\u67e5\u8be2\u8bed\u53e5\u7684\u6761\u4ef6\u4e4b\u540e;\n \n-- \u5148\u67e5\u8be2\u5b66\u751f\u5e73\u5747\u5e74\u9f84\uff0c\u518d\u67e5\u8be2\u5927\u4e8e\u5e73\u5747\u5e74\u9f84\u7684\u5b66\u751f\nselect * from student where age > (select avg(age) from student);<\/code><\/pre>\n\n\n\n\u8fde\u63a5\u67e5\u8be2<\/h5>\n\n\n\n
MySQL\u67e5\u8be2\u8fde\u63a5\u4e3b\u8981\u5206\u4e3a\u4e09\u7c7b\uff1a\u5185\u8fde\u63a5\u3001\u5916\u8fde\u63a5\u548c\u4ea4\u53c9\u8fde\u63a5\u3002<\/p>\n\n\n\n
\u5185\u8fde\u63a5<\/h6>\n\n\n\n
INNER JOIN<\/p>\n\n\n\n
-- \u5185\u8fde\u63a5\n\u8bed\u6cd5\uff1aselect \u5b57\u6bb5 from \u88681 inner join \u88682 on \u88681.\u5b57\u6bb5 = \u88682.\u5b57\u6bb5;\n\t\u6839\u636e\u4e24\u4e2a\u8868\u4e2d\u5171\u6709\u7684\u5b57\u6bb5\u8fdb\u884c\u5339\u914d\uff0c\u7136\u540e\u5c06\u7b26\u5408\u6761\u4ef6\u7684\u5408\u96c6\u8fdb\u884c\u62fc\u63a5\n\ton\u540e\u9762\u662f\u8fde\u63a5\u6761\u4ef6\uff0c\u4e5f\u5c31\u662f\u5171\u6709\u5b57\u6bb5\n \nselect * from student inner join engScore on student.name = engScore.name;\n-- \u5c06 student \u8868\u4e0e engScore \u8868\u901a\u8fc7\u76f8\u540c\u7684 name \u62fc\u63a5\u8d77\u6765\uff0c\u7b80\u5355\u7684\u6765\u8bf4\u5c31\u662f\u4e24\u4e2a excel \u5408\u5e76<\/code><\/pre>\n\n\n\n\u5916\u8fde\u63a5<\/h6>\n\n\n\n
OUTER JOIN<\/p>\n\n\n\n
\u5bf9\u4e8e\u5916\u8054\u7ed3 outer\u5173\u952e\u5b57\u53ef\u4ee5\u7701\u7565<\/p>\n\n\n\n
\u5916\u8fde\u63a5\u5206\u4e3a\u5de6\u8fde\u63a5\u548c\u53f3\u8fde\u63a5\uff0c\u8fd9\u79cd\u8fde\u63a5\u662f\u6307\u5728\u8fde\u63a5\u4e24\u5f20\u6216\u591a\u5f20\u8868\u65f6\uff0c\u5305\u542b\u4e86\u6240\u6709\u7684\u8bb0\u5f55\u3002<\/p>\n\n\n\n
\u5de6\uff08\u5916\uff09\u8fde\u63a5<\/strong><\/p>\n\n\n\n-- \u5de6\u8fde\u63a5\n\u8bed\u6cd5\uff1aselect \u5b57\u6bb5 from \u88681 left join \u88682 on \u8fde\u63a5\u6761\u4ef6;\n \nselect * from student left join engScore on student.name = engScore.name;\n-- \u4e0e\u5185\u8fde\u63a5\u5f62\u5f0f\u76f8\u540c\uff0c\u4f46\u5de6\u8868\u4e3a\u4e3b\u8868\uff0c\u6307\u5b9a\u5b57\u6bb5\u90fd\u4f1a\u663e\u793a\uff0c\u53f3\u8868\u4e3a\u4ece\u8868\uff0c\u65e0\u5185\u5bb9\u4f1a\u663e\u793a null<\/code><\/pre>\n\n\n\n\u53f3\uff08\u5916\uff09\u8fde\u63a5<\/strong><\/p>\n\n\n\n-- \u53f3\u8fde\u63a5\n\u8bed\u6cd5\uff1aselect \u5b57\u6bb5 from \u88681 right join \u88682 on \u8fde\u63a5\u6761\u4ef6;\n \nselect * from student right join engScore on student.name = engScore.name;\n-- \u4e0e\u5185\u8fde\u63a5\u5f62\u5f0f\u76f8\u540c\uff0c\u4f46\u53f3\u8868\u4e3a\u4e3b\u8868\uff0c\u6307\u5b9a\u5b57\u6bb5\u90fd\u4f1a\u663e\u793a\uff0c\u5de6\u8868\u4e3a\u4ece\u8868\uff0c\u65e0\u5185\u5bb9\u4f1a\u663e\u793a null<\/code><\/pre>\n\n\n\n\u4e09.\u4e3b\u4ece\u590d\u5236<\/h2>\n\n\n\n
1 \uff09\u5728 slave \u670d\u52a1\u5668\u4e0a\u6267\u884c start slave \u547d\u4ee4\uff0c\u4e3b\u4ece\u590d\u5236\u5f00\u59cb\u8fdb\u884c<\/p>\n\n\n\n
2\uff09\u6b64\u65f6\uff0cslave \u670d\u52a1\u5668\u7684 I\/O \u7ebf\u7a0b \u4f1a\u901a\u8fc7\u5728 Master \u4e0a\u5df2\u7ecf\u6388\u6743\u7684\u7528\u6237\u8bf7\u6c42\u8fde\u63a5 Master \u670d\u52a1\u5668 \uff0c\u5e76\u8bf7\u6c42\u4ece\u6307\u5b9a binlog \u65e5\u5fd7\u6587\u4ef6\u7684\u6307\u5b9a\u4f4d\u7f6e\uff08\u65e5\u5fd7\u6587\u4ef6\u540d\u548c\u4f4d\u7f6e\u5c31\u662f\u5728\u914d\u7f6e\u4e3b\u4ece\u590d\u5236\u670d\u52a1\u65f6\u6267\u884c change master \u547d\u4ee4\u6307\u5b9a\u7684\uff09\u4e4b\u540e\u5f00\u59cb\u53d1\u9001 binlog \u65e5\u5fd7\u5185\u5bb9
3) Master \u670d\u52a1\u5668\u63a5\u6536\u5230\u6765\u81ea slave \u670d\u52a1\u5668\u7684 I\/O \u7ebf\u7a0b\u7684\u8bf7\u6c42\u4e4b\u540e\uff0c\u5176\u4e0a\u8d1f\u8d23\u590d\u5236\u7684 binlog dump \u7ebf\u7a0b\u4f1a\u6839\u636e\u8bf7\u6c42\u7684\u4fe1\u606f\uff0c\u5206\u6279\u8bfb\u53d6\u6307\u5b9a binlog \u65e5\u5fd7\u6587\u4ef6\u6240\u6307\u5b9a\u4f4d\u7f6e\u4e4b\u540e\u7684 binlog \u65e5\u5fd7\u4fe1\u606f\uff0c\u7136\u540e\u8fd4\u56de\u7ed9 Slave \u7aef\u7684 I\/O \u7ebf\u7a0b\u3002 \u8fd4\u56de\u7684\u4fe1\u606f\u4e2d\u9664\u4e86 binlog \u65e5\u5fd7\u5185\u5bb9\u4e4b\u5916\uff0c\u8fd8\u5305\u62ec\u5728 Master \u670d\u52a1\u5668\u7aef\u8bb0\u5f55\u7684\u65b0\u7684 binlog \u6587\u4ef6\u540d\u79f0\uff0c \u4ee5\u53ca\u5728\u65b0\u7684 binlog \u4e2d\u7684\u4e0b\u4e00\u4e2a\u6307\u5b9a\u7684\u66f4\u65b0\u4f4d\u7f6e<\/p>\n\n\n\n
4\uff09\u5f53 slave \u670d\u52a1\u5668\u7684 I\/O \u7ebf\u7a0b\u83b7\u53d6\u5230 Master \u670d\u52a1\u5668\u4e0a I\/O \u7ebf\u7a0b\u53d1\u9001\u7684\u65e5\u5fd7\u5185\u5bb9\u53ca \u65e5\u5fd7\u6587\u4ef6\u548c\u4f4d\u7f6e\u70b9\u4e4b\u540e \uff0c\u4f1a\u5c06 binlog \u65e5\u5fd7\u5185\u5bb9\u4f9d\u6b21\u5199\u5165\u5230 Slave \u7aef\u81ea\u8eab\u7684 Relay Log \uff08\u5373\u4e2d\u7ee7\u65e5\u5fd7\uff09\u6587\u4ef6\uff08 MySQL-relay-bin.xxxxxx \uff09\u7684\u6700\u672b\u7aef\uff0c\u5e76\u5c06\u65b0\u7684 binlog \u6587\u4ef6\u540d\u548c\u4f4d\u7f6e\u8bb0\u5f55\u5230 master info \u6587\u4ef6\u4e2d\uff0c\u4ee5\u4fbf\u4e0b\u6b21\u8bfb\u53d6 Master \u7aef\u65b0 binlog \u65e5\u5fd7\u65f6\uff0c\u80fd\u591f\u544a\u8bc9 Master \u670d\u52a1\u5668\u9700\u8981\u4ece\u65b0 binlog \u65e5\u5fd7\u7684\u6307\u5b9a\u6587\u4ef6\u53ca\u4f4d\u7f6e\u5f00\u59cb\u8bf7\u6c42\u65b0\u7684 binlog \u65e5\u5fd7\u5185\u5bb9<\/p>\n\n\n\n
5 ) Slave \u670d\u52a1\u5668\u7aef\u7684 SQL \u7ebf\u7a0b\u4f1a\u5b9e\u65f6\u5730\u68c0\u6d4b\u672c\u5730 Relay Log \u4e2d I\/O \u7ebf\u7a0b\u65b0\u589e\u52a0\u7684\u65e5\u5fd7\u5185\u5bb9\uff0c\u7136\u540e\u53ca\u65f6\u5730\u628a Relay Log \u6587\u4ef6\u4e2d\u7684\u5185\u5bb9\u89e3\u6790\u6210 SQL \u8bed\u53e5\uff0c \u5e76\u5728\u81ea\u8eab slave \u670d\u52a1\u5668\u4e0a\u6309\u89e3\u6790 SQL \u8bed\u53e5\u7684\u4f4d\u7f6e\u987a\u5e8f\u6267\u884c\uff0c\u5e76\u5c06\u5f53\u524d\u5e94\u7528\u4e2d\u7ee7\u65e5\u5fd7\u7684\u6587\u4ef6\u540d\u53ca\u4f4d\u7f6e\u70b9\u8bb0\u5f55\u5728 relay- log.info
<\/p>\n","protected":false},"excerpt":{"rendered":"
\u4e00.\u7d22\u5f15 1\uff09\u5355\u5217\u7d22\u5f15\u666e\u901a\u7d22\u5f15\uff1aMySQL\u4e2d […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-377","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.aqrboyblog.top\/index.php?rest_route=\/wp\/v2\/posts\/377","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.aqrboyblog.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.aqrboyblog.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.aqrboyblog.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.aqrboyblog.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=377"}],"version-history":[{"count":2,"href":"https:\/\/www.aqrboyblog.top\/index.php?rest_route=\/wp\/v2\/posts\/377\/revisions"}],"predecessor-version":[{"id":379,"href":"https:\/\/www.aqrboyblog.top\/index.php?rest_route=\/wp\/v2\/posts\/377\/revisions\/379"}],"wp:attachment":[{"href":"https:\/\/www.aqrboyblog.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=377"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.aqrboyblog.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=377"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.aqrboyblog.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=377"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}