{"id":107,"date":"2023-06-09T19:48:47","date_gmt":"2023-06-09T11:48:47","guid":{"rendered":"https:\/\/jiachen.de.cool\/blog\/?p=107"},"modified":"2023-06-09T19:48:47","modified_gmt":"2023-06-09T11:48:47","slug":"%e5%85%b6%e4%bb%96%e7%b1%bb%e5%9e%8b%e7%9a%84%e6%9f%a5%e8%af%a2","status":"publish","type":"post","link":"https:\/\/jiachen.de.cool\/blog\/2023\/06\/09\/%e5%85%b6%e4%bb%96%e7%b1%bb%e5%9e%8b%e7%9a%84%e6%9f%a5%e8%af%a2\/","title":{"rendered":"\u5176\u4ed6\u7c7b\u578b\u7684\u67e5\u8be2"},"content":{"rendered":"\n

1\u3001\u5b50\u67e5\u8be2\uff08\u5d4c\u5957\u67e5\u8be2\uff09<\/p>\n\n\n\n

mysql> select * from people;\r\n+------+-----+\r\n| name | age |\r\n+------+-----+\r\n| \u7532   |  29 |\r\n| \u4e59   |  35 |\r\n+------+-----+\r\n2 rows in set (0.00 sec)\r\n\r\r\nmysql> select * from houses where name in (select name from people where age<30);\r\n+------+----------------+-----------------+\r\n| name | house_location | purchasing_year |\r\n+------+----------------+-----------------+\r\n| \u7532   | \u5929\u6cb3           | 1997            |\r\n| \u7532   | \u5929\u6cb3           | 1998            |\r\n+------+----------------+-----------------+\r\n2 rows in set (0.10 sec)\r\n\r\nmysql> select * from houses;\r\n+------+----------------+-----------------+\r\n| name | house_location | purchasing_year |\r\n+------+----------------+-----------------+\r\n| \u7532   | \u5929\u6cb3           | 1997            |\r\n| \u7532   | \u5929\u6cb3           | 1998            |\r\n+------+----------------+-----------------+\r\n2 rows in set (0.00 sec)\r\n\r\nmysql> select * from houses where name='\u7532';\r\n+------+----------------+-----------------+\r\n| name | house_location | purchasing_year |\r\n+------+----------------+-----------------+\r\n| \u7532   | \u5929\u6cb3           | 1997            |\r\n| \u7532   | \u5929\u6cb3           | 1998            |\r\n+------+----------------+-----------------+\r\n2 rows in set (0.00 sec)\r\n\r\nmysql> select * from houses where name in (select name from people where age >100);\r\nEmpty set (0.00 sec)<\/code><\/pre>\n\n\n\n
2\u3001\u5206\u7ec4\u67e5\u8be2\uff08group by\uff09<\/p>\n\n\n\n
mysql> select house_location from houses group by house_location;\r\n+----------------+\r\n| house_location |\r\n+----------------+\r\n| \u5929\u6cb3           |\r\n+----------------+\r\n1 row in set (0.00 sec)<\/code><\/pre>\n\n\n\n
3\u3001\u805a\u5408\u67e5\u8be2\uff08count(), sum(), max(), min()\uff09<\/p>\n\n\n\n
mysql> select house_location, count(*) as total from houses group by house_location;\r\n+----------------+-------+\r\n| house_location | total |\r\n+----------------+-------+\r\n| \u5929\u6cb3           |     2 |\r\n+----------------+-------+\r\n1 row in set (0.06 sec)\r\n\r\r\nmysql> select sum(age) from people;\r\n+----------+\r\n| sum(age) |\r\n+----------+\r\n|       64 |\r\n+----------+\r\n1 row in set (0.04 sec)\r\n\r\nmysql> select * from people;\r\n+------+-----+\r\n| name | age |\r\n+------+-----+\r\n| \u7532   |  29 |\r\n| \u4e59   |  35 |\r\n+------+-----+\r\n2 rows in set (0.00 sec)\r\n\r\nmysql> select max(age) from people;\r\n+----------+\r\n| max(age) |\r\n+----------+\r\n|       35 |\r\n+----------+\r\n1 row in set (0.04 sec)\r\n\r\nmysql> select min(age) from people;\r\n+----------+\r\n| min(age) |\r\n+----------+\r\n|       29 |\r\n+----------+\r\n1 row in set (0.00 sec)<\/code><\/pre>\n\n\n\n
4\u3001\u7ec4\u5408\u67e5\u8be2\uff08union\uff09<\/p>\n\n\n\n
mysql> select name from houses;\n+------+\n| name |\n+------+\n| \u7532   |\n| \u7532   |\n+------+\n2 rows in set (0.00 sec)\n\nmysql> select name from people;\n+------+\n| name |\n+------+\n| \u7532   |\n| \u4e59   |\n+------+\n2 rows in set (0.00 sec)\n\nmysql> select name from houses union distinct select name from people;\n+------+\n| name |\n+------+\n| \u7532   |\n| \u4e59   |\n+------+\n2 rows in set (0.03 sec)\n\nmysql> select name from houses union all select name from people;\n+------+\n| name |\n+------+\n| \u7532   |\n| \u7532   |\n| \u7532   |\n| \u4e59   |\n+------+\n4 rows in set (0.00 sec)\n\nmysql> select name from houses union select name from people;\/*\u7b49\u540c\u4e8e union distinct*\/\n+------+\n| name |\n+------+\n| \u7532   |\n| \u4e59   |\n+------+\n2 rows in set (0.00 sec)<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
1\u3001\u5b50\u67e5\u8be2\uff08\u5d4c\u5957\u67e5\u8be2\uff09 2\u3001\u5206\u7ec4\u67e5\u8be2\uff08group by\uff09 3\u3001\u805a\u5408\u67e5\u8be2\uff08count(), sum(), max… <\/p>\n
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":[18,9],"class_list":["post-107","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-18","tag-9"],"_links":{"self":[{"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/posts\/107","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=107"}],"version-history":[{"count":1,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/posts\/107\/revisions"}],"predecessor-version":[{"id":108,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/posts\/107\/revisions\/108"}],"wp:attachment":[{"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/media?parent=107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/categories?post=107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/tags?post=107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}