{"id":104,"date":"2023-06-01T18:57:59","date_gmt":"2023-06-01T10:57:59","guid":{"rendered":"https:\/\/jiachen.de.cool\/blog\/?p=104"},"modified":"2023-06-01T18:57:59","modified_gmt":"2023-06-01T10:57:59","slug":"%e8%bf%9e%e6%8e%a5%e6%9f%a5%e8%af%a2","status":"publish","type":"post","link":"https:\/\/jiachen.de.cool\/blog\/2023\/06\/01\/%e8%bf%9e%e6%8e%a5%e6%9f%a5%e8%af%a2\/","title":{"rendered":"\u8fde\u63a5\u67e5\u8be2"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">MySQL\u7684\u8fde\u63a5\u67e5\u8be2\u5206\u4e3a\u5185\u8fde\u63a5\u67e5\u8be2\uff08INNER JOIN\uff09\u3001\u5916\u8fde\u63a5\u67e5\u8be2\uff08OUTER JOIN\uff09\u548c\u590d\u5408\u8fde\u63a5\u67e5\u8be2\uff0c\u5176\u4e2d\u5916\u8fde\u63a5\u67e5\u8be2\u53c8\u5305\u542b\u5de6\u8fde\u63a5\u67e5\u8be2\uff08LEFT JOIN\uff09\u548c\u53f3\u8fde\u63a5\u67e5\u8be2\uff08RIGHT JOIN\uff09\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> use learning;\nDatabase changed\nmysql> show tables;\n+--------------------+\n| Tables_in_learning |\n+--------------------+\n| houses             |\n| housesview         |\n| people             |\n| people2            |\n+--------------------+\n4 rows in set (0.01 sec)\n\nmysql> select * from houses;\n+------+----------------+-----------------+\n| name | house_location | purchasing_year |\n+------+----------------+-----------------+\n| \u7532   | \u5929\u6cb3           | 1997            |\n| \u7532   | \u5929\u6cb3           | 1998            |\n| \u4e59   | \u756a\u79ba           | 1998            |\n| \u4e19   | \u756a\u79ba           | 1998            |\n| \u4e19   | \u5929\u6cb3           | 1999            |\n| \u4e01   | \u5929\u6cb3           | 2000            |\n+------+----------------+-----------------+\n6 rows in set (0.00 sec)\n\nmysql> select * from people;\n+------+------+\n| name | age  |\n+------+------+\n| \u7532   |   29 |\n| \u4e59   |   35 |\n+------+------+\n2 rows in set (0.00 sec)\n\nmysql> select * from people2;\n+------+------+\n| name | age  |\n+------+------+\n| jia  |   29 |\n| yi   |   35 |\n+------+------+\n2 rows in set (0.00 sec)\n\n\nmysql> select a.name name1,a.house_location house_location3, a.purchasing_year\n\u8d2d\u4e70\u5e74\u4efd,b.name name45,b.age from houses a\n    -> right join people2 b\n    -> on a.name=b.name;\n+-------+-----------------+--------------+--------+------+\n| name1 | house_location3 | \u8d2d\u4e70\u5e74\u4efd     | name45 | age  |\n+-------+-----------------+--------------+--------+------+\n| NULL  | NULL            | NULL         | jia    |   29 |\n| NULL  | NULL            | NULL         | yi     |   35 |\n+-------+-----------------+--------------+--------+------+\n2 rows in set (0.00 sec)\n\nmysql> create table housing_price (house_location varchar(3) not null, price int\n(3) not null);\nQuery OK, 0 rows affected (0.07 sec)\n\nmysql> desc housing_price;\n+----------------+------------+------+-----+---------+-------+\n| Field          | Type       | Null | Key | Default | Extra |\n+----------------+------------+------+-----+---------+-------+\n| house_location | varchar(3) | NO   |     | NULL    |       |\n| price          | int(3)     | NO   |     | NULL    |       |\n+----------------+------------+------+-----+---------+-------+\n2 rows in set (0.01 sec)\n\n\nmysql> insert into housing_price values('\u5929\u6cb3','500');\nQuery OK, 1 row affected (0.01 sec)\n\nmysql> insert into housing_price values('\u756a\u79ba','400');\nQuery OK, 1 row affected (0.00 sec)\n\nmysql> select * from housing_price;\n+----------------+-------+\n| house_location | price |\n+----------------+-------+\n| \u5929\u6cb3           |   500 |\n| \u756a\u79ba           |   400 |\n+----------------+-------+\n2 rows in set (0.00 sec)\n\nmysql> show tables;\n+--------------------+\n| Tables_in_learning |\n+--------------------+\n| houses             |\n| housesview         |\n| housing_price      |\n| people             |\n| people2            |\n+--------------------+\n5 rows in set (0.00 sec)\n\n\nmysql> alter table housing_price rename to housingprice;\nQuery OK, 0 rows affected (0.04 sec)\n\nmysql> show tables;\n+--------------------+\n| Tables_in_learning |\n+--------------------+\n| houses             |\n| housesview         |\n| housingprice       |\n| people             |\n| people2            |\n+--------------------+\n5 rows in set (0.00 sec)\n\nmysql> select * from houses;\n+------+----------------+-----------------+\n| name | house_location | purchasing_year |\n+------+----------------+-----------------+\n| \u7532   | \u5929\u6cb3           | 1997            |\n| \u7532   | \u5929\u6cb3           | 1998            |\n| \u4e59   | \u756a\u79ba           | 1998            |\n| \u4e19   | \u756a\u79ba           | 1998            |\n| \u4e19   | \u5929\u6cb3           | 1999            |\n| \u4e01   | \u5929\u6cb3           | 2000            |\n+------+----------------+-----------------+\n6 rows in set (0.00 sec)\n\nmysql> select * from people;\n+------+------+\n| name | age  |\n+------+------+\n| \u7532   |   29 |\n| \u4e59   |   35 |\n+------+------+\n2 rows in set (0.00 sec)\n\nmysql> select * from people2;\n+------+------+\n| name | age  |\n+------+------+\n| jia  |   29 |\n| yi   |   35 |\n+------+------+\n2 rows in set (0.00 sec)\n\nmysql> select * from housingprice;\n+----------------+-------+\n| house_location | price |\n+----------------+-------+\n| \u5929\u6cb3           |   500 |\n| \u756a\u79ba           |   400 |\n+----------------+-------+\n2 rows in set (0.00 sec)\n\nmysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a\n    -> inner join people b\n    -> on a.name=b.name;\n+------+----------------+-----------------+------+\n| name | house_location | purchasing_year | age  |\n+------+----------------+-----------------+------+\n| \u7532   | \u5929\u6cb3           | 1997            |   29 |\n| \u7532   | \u5929\u6cb3           | 1998            |   29 |\n| \u4e59   | \u756a\u79ba           | 1998            |   35 |\n+------+----------------+-----------------+------+\n3 rows in set (0.00 sec)\n\nmysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a\n    -> join people2 b\n    -> on a.name=b.name;\nEmpty set (0.00 sec)\n\nmysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a\n    -> left join people b\n    -> on a.name=b.name;\n+------+----------------+-----------------+------+\n| name | house_location | purchasing_year | age  |\n+------+----------------+-----------------+------+\n| \u7532   | \u5929\u6cb3           | 1997            |   29 |\n| \u7532   | \u5929\u6cb3           | 1998            |   29 |\n| \u4e59   | \u756a\u79ba           | 1998            |   35 |\n| \u4e19   | \u756a\u79ba           | 1998            | NULL |\n| \u4e19   | \u5929\u6cb3           | 1999            | NULL |\n| \u4e01   | \u5929\u6cb3           | 2000            | NULL |\n+------+----------------+-----------------+------+\n6 rows in set (0.00 sec)\n\n\nmysql> select a.name,a.house_location,a.purchasing_year,b.age from people b\n    -> left join houses a\n    -> on a.name=b.name;\n+------+----------------+-----------------+------+\n| name | house_location | purchasing_year | age  |\n+------+----------------+-----------------+------+\n| \u7532   | \u5929\u6cb3           | 1997            |   29 |\n| \u7532   | \u5929\u6cb3           | 1998            |   29 |\n| \u4e59   | \u756a\u79ba           | 1998            |   35 |\n+------+----------------+-----------------+------+\n3 rows in set (0.00 sec)\n\nmysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a\n    -> left join people b\n    -> on a.name=b.name and b.name='\u4e59';\n+------+----------------+-----------------+------+\n| name | house_location | purchasing_year | age  |\n+------+----------------+-----------------+------+\n| \u4e59   | \u756a\u79ba           | 1998            |   35 |\n| \u7532   | \u5929\u6cb3           | 1997            | NULL |\n| \u7532   | \u5929\u6cb3           | 1998            | NULL |\n| \u4e19   | \u756a\u79ba           | 1998            | NULL |\n| \u4e19   | \u5929\u6cb3           | 1999            | NULL |\n| \u4e01   | \u5929\u6cb3           | 2000            | NULL |\n+------+----------------+-----------------+------+\n6 rows in set (0.00 sec)\n\nmysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a\n    -> left join people b\n    -> on a.name=b.name where b.name='\u4e59';\n+------+----------------+-----------------+------+\n| name | house_location | purchasing_year | age  |\n+------+----------------+-----------------+------+\n| \u4e59   | \u756a\u79ba           | 1998            |   35 |\n+------+----------------+-----------------+------+\n1 row in set (0.00 sec)\n\nmysql> insert into people values('\u620a','45');\nQuery OK, 1 row affected (0.00 sec)\n\nmysql> select * from people;\n+------+------+\n| name | age  |\n+------+------+\n| \u7532   |   29 |\n| \u4e59   |   35 |\n| \u620a   |   45 |\n+------+------+\n3 rows in set (0.00 sec)\n\nmysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a\n    -> right join people b\n    -> on a.name=b.name;\n+------+----------------+-----------------+------+\n| name | house_location | purchasing_year | age  |\n+------+----------------+-----------------+------+\n| \u7532   | \u5929\u6cb3           | 1997            |   29 |\n| \u7532   | \u5929\u6cb3           | 1998            |   29 |\n| \u4e59   | \u756a\u79ba           | 1998            |   35 |\n| NULL | NULL           | NULL            |   45 |\n+------+----------------+-----------------+------+\n4 rows in set (0.00 sec)\n\nmysql> select a.name \u59d3\u540d,a.house_location,a.purchasing_year \u8d2d\u4e70\u5e74\u4efd,b.age \u5e74\u9f84\n from houses a\n    -> right join people2 b\n    -> on a.name=b.name;\n+--------+----------------+--------------+--------+\n| \u59d3\u540d   | house_location | \u8d2d\u4e70\u5e74\u4efd     | \u5e74\u9f84   |\n+--------+----------------+--------------+--------+\n| NULL   | NULL           | NULL         |     29 |\n| NULL   | NULL           | NULL         |     35 |\n+--------+----------------+--------------+--------+\n2 rows in set (0.00 sec)\n\nmysql> select a.name,a.house_location,a.purchasing_year,b.age,c.price from house\ns a\n    -> inner join people b\n    -> right join housingprice c\n    -> on a.name=b.name\n    -> and a.house_location=c.house_location;\n+------+----------------+-----------------+------+-------+\n| name | house_location | purchasing_year | age  | price |\n+------+----------------+-----------------+------+-------+\n| \u7532   | \u5929\u6cb3           | 1997            |   29 |   500 |\n| \u7532   | \u5929\u6cb3           | 1998            |   29 |   500 |\n| \u4e59   | \u756a\u79ba           | 1998            |   35 |   400 |\n+------+----------------+-----------------+------+-------+\n3 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>MySQL\u7684\u8fde\u63a5\u67e5\u8be2\u5206\u4e3a\u5185\u8fde\u63a5\u67e5\u8be2\uff08INNER JOIN\uff09\u3001\u5916\u8fde\u63a5\u67e5\u8be2\uff08OUTER JOIN\uff09\u548c\u590d\u5408\u8fde\u63a5\u67e5\u8be2\uff0c&hellip; <\/p>\n<p><a class=\"moretag\" href=\"https:\/\/jiachen.de.cool\/blog\/2023\/06\/01\/%e8%bf%9e%e6%8e%a5%e6%9f%a5%e8%af%a2\/\">Read the full article<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[9,17],"class_list":["post-104","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-9","tag-17"],"_links":{"self":[{"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/posts\/104","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/comments?post=104"}],"version-history":[{"count":1,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/posts\/104\/revisions"}],"predecessor-version":[{"id":105,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/posts\/104\/revisions\/105"}],"wp:attachment":[{"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/media?parent=104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/categories?post=104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/tags?post=104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}