{"id":96,"date":"2025-09-06T21:02:07","date_gmt":"2025-09-06T13:02:07","guid":{"rendered":"http:\/\/101.42.104.90\/?p=96"},"modified":"2025-09-06T21:27:25","modified_gmt":"2025-09-06T13:27:25","slug":"sql-%e5%85%a5%e9%97%a8%e5%ad%a6%e4%b9%a0%e7%ac%94%e8%ae%b0%ef%bc%9a%e4%bb%8e%e6%9f%a5%e8%af%a2%e5%88%b0%e5%ae%9e%e6%88%98%e7%9a%84%e5%9f%ba%e7%a1%80%e8%af%ad%e6%b3%95","status":"publish","type":"post","link":"http:\/\/yuemustory.fun\/index.php\/2025\/09\/06\/sql-%e5%85%a5%e9%97%a8%e5%ad%a6%e4%b9%a0%e7%ac%94%e8%ae%b0%ef%bc%9a%e4%bb%8e%e6%9f%a5%e8%af%a2%e5%88%b0%e5%ae%9e%e6%88%98%e7%9a%84%e5%9f%ba%e7%a1%80%e8%af%ad%e6%b3%95\/","title":{"rendered":"SQL \u5165\u95e8\u5b66\u4e60\u7b14\u8bb0\uff1a\u4ece\u67e5\u8be2\u5230\u5b9e\u6218\u7684\u57fa\u7840\u8bed\u6cd5"},"content":{"rendered":"<h3>\u65b9\u6848\u4e00\uff1aSQL \u6838\u5fc3\u8bed\u6cd5\u793a\u610f\u56fe\uff08\u6587\u5b57\u63cf\u8ff0\u7248\uff09<\/h3>\n<p>\u9002\u5408\u7528 PPT\u3001Figma\u3001Canva \u7b49\u5de5\u5177\u7ed8\u5236\uff0c\u7ed3\u6784\u6e05\u6670\uff0c\u9002\u914d\u65b0\u624b\u5b66\u4e60\u573a\u666f\uff1a<\/p>\n<h4>\u6574\u4f53\u5e03\u5c40<\/h4>\n<ul>\n<li>\n<p><strong>\u6807\u9898\u533a<\/strong>\uff08\u9876\u90e8\uff0c\u6a59\u8272\u80cc\u666f\uff09\uff1a<code>SQL\u57fa\u7840\u8bed\u6cd5\u6838\u5fc3\u6846\u67b6<\/code> + \u526f\u6807\u9898<code>\uff08\u57fa\u4e8eMySQL\uff0c\u9002\u914d\u5165\u95e8\u5b66\u4e60\uff09<\/code><\/p>\n<\/li>\n<li>\n<p><strong>\u5de6\u4fa7\u6a21\u5757<\/strong>\uff08\u5360\u6bd4 60%\uff0c\u767d\u8272\u80cc\u666f\uff0c\u9ed1\u8272\u8fb9\u6846\uff09\uff1aSQL \u67e5\u8be2\u8bed\u6cd5\u6d41\u7a0b\uff08\u542b\u4ee3\u7801\u793a\u4f8b\uff09<\/p>\n<\/li>\n<li>\n<p><strong>\u53f3\u4fa7\u6a21\u5757<\/strong>\uff08\u5360\u6bd4 40%\uff0c\u6d45\u7070\u8272\u80cc\u666f\uff0c\u9ed1\u8272\u8fb9\u6846\uff09\uff1a\u5173\u952e\u8bed\u6cd5\u8bf4\u660e\uff08\u8868\u683c \/ \u56fe\u6807\uff09<\/p>\n<\/li>\n<\/ul>\n<h4>\u5de6\u4fa7\uff1aSQL \u67e5\u8be2\u8bed\u6cd5\u6d41\u7a0b\uff08\u4ece\u4e0a\u5230\u4e0b\u6392\u5e8f\uff09<\/h4>\n<ol>\n<li><strong>\u8bed\u6cd5\u6b65\u9aa4\u5361\u7247<\/strong>\uff08\u6bcf\u4e2a\u6b65\u9aa4\u7528\u6d45\u84dd\u8272\u5c0f\u5361\u7247\u5305\u88f9\uff0c\u95f4\u8ddd 10px\uff09\uff1a<\/li>\n<\/ol>\n<ul>\n<li>\n<p>\u6b65\u9aa4 1\uff1a<code>SELECT \u5b57\u6bb51, \u5b57\u6bb52 AS \u522b\u540d<\/code>\uff08\u6807\u6ce8\uff1a\u6307\u5b9a\u67e5\u8be2\u5b57\u6bb5\uff0cAS \u8bbe\u522b\u540d\uff09<\/p>\n<\/li>\n<li>\n<p>\u6b65\u9aa4 2\uff1a<code>FROM \u8868\u540d<\/code>\uff08\u6807\u6ce8\uff1a\u6307\u5b9a\u6570\u636e\u6765\u6e90\u8868\uff0c\u4f8b\uff1astudents\uff09<\/p>\n<\/li>\n<li>\n<p>\u6b65\u9aa4 3\uff1a<code>WHERE \u6761\u4ef6<\/code>\uff08\u6807\u6ce8\uff1a\u7b5b\u9009\u6570\u636e\uff0c\u4f8b\uff1a\u6210\u7ee9 &gt; 80 AND \u73ed\u7ea7 =&#8217;1 \u5e74 1 \u73ed &#8216;\uff09<\/p>\n<\/li>\n<li>\n<p>\u6b65\u9aa4 4\uff1a<code>GROUP BY \u5206\u7ec4\u5b57\u6bb5<\/code>\uff08\u6807\u6ce8\uff1a\u6309\u5b57\u6bb5\u5206\u7ec4\uff0c\u4f8b\uff1a\u73ed\u7ea7\uff09<\/p>\n<\/li>\n<li>\n<p>\u6b65\u9aa4 5\uff1a<code>HAVING \u5206\u7ec4\u6761\u4ef6<\/code>\uff08\u6807\u6ce8\uff1a\u7b5b\u9009\u5206\u7ec4\u7ed3\u679c\uff0c\u4f8b\uff1aAVG (\u6210\u7ee9)&gt;=80\uff09<\/p>\n<\/li>\n<li>\n<p>\u6b65\u9aa4 6\uff1a<code>ORDER BY \u5b57\u6bb5 DESC<\/code>\uff08\u6807\u6ce8\uff1a\u6392\u5e8f\uff0cDESC \u964d\u5e8f\uff0cASC \u5347\u5e8f\uff09<\/p>\n<\/li>\n<li>\n<p>\u6b65\u9aa4 7\uff1a<code>LIMIT 5 OFFSET 10<\/code>\uff08\u6807\u6ce8\uff1a\u5206\u9875\uff0c\u53d6 5 \u6761\uff0c\u8df3\u8fc7\u524d 10 \u6761\uff09<\/p>\n<\/li>\n<\/ul>\n<ol>\n<li><strong>\u5b8c\u6574\u4ee3\u7801\u793a\u4f8b<\/strong>\uff08\u6b65\u9aa4\u4e0b\u65b9\uff0c\u6df1\u8272\u80cc\u666f\uff0c\u767d\u8272\u6587\u5b57\uff0c\u4ee3\u7801\u9ad8\u4eae\uff09\uff1a<\/li>\n<\/ol>\n<pre><code>\\-- \u793a\u4f8b\uff1a\u67e5\u8be21\u5e741\u73ed\u6210\u7ee980-90\u5206\u7684\u5b66\u751f\uff0c\u6309\u6210\u7ee9\u964d\u5e8f\u53d6\u524d3\u6761\n\nSELECT \u59d3\u540d, \u6210\u7ee9&amp;#x20;\n\nFROM students&amp;#x20;\n\nWHERE \u73ed\u7ea7='1\u5e741\u73ed' AND \u6210\u7ee9 BETWEEN 80 AND 90&amp;#x20;\n\nORDER BY \u6210\u7ee9 DESC&amp;#x20;\n\nLIMIT 3;<\/code><\/pre>\n<h4>\u53f3\u4fa7\uff1a\u5173\u952e\u8bed\u6cd5\u8bf4\u660e<\/h4>\n<ol>\n<li><strong>\u5e38\u7528\u51fd\u6570\u8868<\/strong>\uff08\u767d\u8272\u80cc\u666f\uff0c\u8fb9\u6846\u8868\u683c\uff0c3 \u5217\uff1a\u51fd\u6570\u540d\u3001\u529f\u80fd\u3001\u793a\u4f8b\uff09\uff1a<\/li>\n<\/ol>\n<table>\n<thead>\n<tr>\n<th>\u51fd\u6570<\/th>\n<th>\u529f\u80fd<\/th>\n<th>\u793a\u4f8b<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>AVG (\u5b57\u6bb5)<\/td>\n<td>\u8ba1\u7b97\u5e73\u5747\u503c<\/td>\n<td>AVG (\u6210\u7ee9) \u2192 82.5<\/td>\n<\/tr>\n<tr>\n<td>COUNT(*)<\/td>\n<td>\u7edf\u8ba1\u603b\u884c\u6570<\/td>\n<td>COUNT(*) \u2192 30<\/td>\n<\/tr>\n<tr>\n<td>MAX (\u5b57\u6bb5)<\/td>\n<td>\u53d6\u6700\u5927\u503c<\/td>\n<td>MAX (\u6210\u7ee9) \u2192 98<\/td>\n<\/tr>\n<tr>\n<td>ROUND (\u503c\uff0c1)<\/td>\n<td>\u56db\u820d\u4e94\u5165<\/td>\n<td>ROUND(82.56,1) \u2192 82.6<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<ol>\n<li><strong>\u901a\u914d\u7b26\u8bf4\u660e<\/strong>\uff08\u56fe\u6807 + \u6587\u5b57\uff0c2 \u884c\uff09\uff1a<\/li>\n<\/ol>\n<ul>\n<li>\n<p><code>%<\/code>\uff1a\u4ee3\u8868 0 \u4e2a \/ \u591a\u4e2a\u4efb\u610f\u5b57\u7b26\uff08\u4f8b\uff1a<code>\u59d3\u540d LIKE '\u5f20%'<\/code> \u2192 \u6240\u6709\u5f20\u59d3\u5b66\u751f\uff09<\/p>\n<\/li>\n<li>\n<p><code>_<\/code>\uff1a\u4ee3\u8868 1 \u4e2a\u4efb\u610f\u5b57\u7b26\uff08\u4f8b\uff1a<code>\u59d3\u540d LIKE '\u5f20_'<\/code> \u2192 \u4e24\u4e2a\u5b57\u7684\u5f20\u59d3\u5b66\u751f\uff09<\/p>\n<\/li>\n<\/ul>\n<h3>\u65b9\u6848\u4e8c\uff1aSQL \u4ee3\u7801\u7247\u6bb5\u56fe\u7247\uff08\u5de5\u5177 + \u4ee3\u7801\uff09<\/h3>\n<p>\u9002\u5408\u751f\u6210 \u201c\u4ee3\u7801\u5c55\u793a\u7c7b\u56fe\u7247\u201d\uff0c\u7528\u4e8e\u7b14\u8bb0\u63d2\u56fe\u3001\u793e\u4ea4\u5206\u4eab\uff0c\u63a8\u8350\u5de5\u5177\uff1a<a href=\"https:\/\/carbon.now.sh\/\">Carb<\/a><a href=\"https:\/\/carbon.now.sh\/\">on<\/a>\uff08\u5728\u7ebf\u751f\u6210\uff0c\u514d\u8d39\uff09\u3001<a href=\"https:\/\/marketplace.visualstudio.com\/items?itemName=adpyke.codesnap\">Co<\/a><a href=\"https:\/\/marketplace.visualstudio.com\/items?itemName=adpyke.codesnap\">deSna<\/a><a href=\"https:\/\/marketplace.visualstudio.com\/items?itemName=adpyke.codesnap\">p<\/a>\uff08VSCode \u63d2\u4ef6\uff09\u3002<\/p>\n<h4>\u63a8\u8350\u751f\u6210\u7684 SQL \u4ee3\u7801\u7247\u6bb5\uff08\u590d\u5236\u5230\u5de5\u5177\u4e2d\uff09<\/h4>\n<pre><code>\\-- 1. \u57fa\u7840\u67e5\u8be2\uff1a\u6307\u5b9a\u5b57\u6bb5+\u5206\u9875\n\nSELECT \u59d3\u540d, \u73ed\u7ea7, \u6210\u7ee9&amp;#x20;\n\nFROM students&amp;#x20;\n\nLIMIT 5 OFFSET 5; -- \u7b2c6-10\u6761\u6570\u636e\n\n\\-- 2. \u591a\u8868\u8054\u5408\u67e5\u8be2\uff08LEFT JOIN\uff09\n\nSELECT s.\u59d3\u540d, s.\u73ed\u7ea7, c.\u793e\u56e2\u540d\u79f0&amp;#x20;\n\nFROM students s -- \u8868\u522b\u540d\uff1as=students\n\nLEFT JOIN clubs c -- \u8868\u522b\u540d\uff1ac=clubs\n\nON s.\u793e\u56e2 = c.\u793e\u56e2\u7f16\u53f7; -- \u5173\u8054\u6761\u4ef6\n\n\\-- 3. \u5206\u7ec4\u7edf\u8ba1+\u7b5b\u9009\n\nSELECT \u73ed\u7ea7,&amp;#x20;\n\n&amp;#x20;      ROUND(AVG(\u6210\u7ee9),1) AS \u73ed\u7ea7\u5e73\u5747\u5206&amp;#x20;\n\nFROM students&amp;#x20;\n\nGROUP BY \u73ed\u7ea7&amp;#x20;\n\nHAVING \u73ed\u7ea7\u5e73\u5747\u5206 &gt;= 80; -- \u7b5b\u9009\u5e73\u5747\u5206&ge;80\u7684\u73ed\u7ea7<\/code><\/pre>\n<h4>\u5de5\u5177\u8bbe\u7f6e\u5efa\u8bae\uff08\u4ee5 Carbon \u4e3a\u4f8b\uff09<\/h4>\n<ul>\n<li>\n<p><strong>\u4e3b\u9898<\/strong>\uff1a\u9009\u62e9 \u201cMonokai\u201d \u6216 \u201cGitHub Dark\u201d\uff08\u6df1\u8272\u80cc\u666f\uff0c\u4ee3\u7801\u9ad8\u4eae\u6e05\u6670\uff09<\/p>\n<\/li>\n<li>\n<p><strong>\u5b57\u4f53<\/strong>\uff1aFira Code\uff08\u7b49\u5bbd\u5b57\u4f53\uff0c\u4ee3\u7801\u5bf9\u9f50\u7f8e\u89c2\uff09<\/p>\n<\/li>\n<li>\n<p><strong>\u5c3a\u5bf8<\/strong>\uff1a\u5bbd\u5ea6 800px\uff0c\u9ad8\u5ea6 600px\uff08\u9002\u914d\u624b\u673a \/ \u7535\u8111\u67e5\u770b\uff09<\/p>\n<\/li>\n<li>\n<p><strong>\u80cc\u666f<\/strong>\uff1a\u8f7b\u5fae\u6e10\u53d8\uff08\u6d45\u6a59 \/ \u6d45\u84dd\uff0c\u907f\u514d\u5355\u8c03\uff09<\/p>\n<\/li>\n<\/ul>\n<h3>\u65b9\u6848\u4e09\uff1aSQL \u903b\u8f91\u6d41\u7a0b\u56fe\uff08\u9002\u5408\u7406\u89e3\u5173\u8054\u5173\u7cfb\uff09<\/h3>\n<p>\u7528\u6d41\u7a0b\u56fe\u5de5\u5177\uff08\u5982 Draw.io\u3001ProcessOn\uff09\u7ed8\u5236\uff0c\u4e3b\u9898\uff1a\u201c\u5b66\u751f\u8868 + \u793e\u56e2\u8868\u201d \u8054\u5408\u67e5\u8be2\u903b\u8f91\uff1a<\/p>\n<ol>\n<li>\n<p><strong>\u8d77\u59cb\u8282\u70b9<\/strong>\uff08\u5706\u5f62\uff0c\u84dd\u8272\uff09\uff1a<code>\u9700\u6c42\uff1a\u67e5\u8be2\u5b66\u751f\u53ca\u5bf9\u5e94\u793e\u56e2\u540d\u79f0<\/code><\/p>\n<\/li>\n<li>\n<p><strong>\u6570\u636e\u6765\u6e90<\/strong>\uff08\u77e9\u5f62\uff0c\u767d\u8272\uff09\uff1a<code>\u5b66\u751f\u8868\uff08students\uff09\uff1a\u59d3\u540d\u3001\u73ed\u7ea7\u3001\u793e\u56e2\uff08\u7f16\u53f7\uff09<\/code> \u2192 <code>\u793e\u56e2\u8868\uff08clubs\uff09\uff1a\u793e\u56e2\u7f16\u53f7\u3001\u793e\u56e2\u540d\u79f0<\/code><\/p>\n<\/li>\n<li>\n<p><strong>\u5173\u8054\u6761\u4ef6<\/strong>\uff08\u83f1\u5f62\uff0c\u6d45\u84dd\u8272\uff09\uff1a<code>students.\u793e\u56e2 = clubs.\u793e\u56e2\u7f16\u53f7<\/code><\/p>\n<\/li>\n<li>\n<p><strong>\u67e5\u8be2\u7ed3\u679c<\/strong>\uff08\u77e9\u5f62\uff0c\u767d\u8272\uff09\uff1a<\/p>\n<\/li>\n<\/ol>\n<ul>\n<li>\n<p>\u5206\u652f 1\uff08LEFT JOIN\uff09\uff1a<code>\u663e\u793a\u6240\u6709\u5b66\u751f\uff0c\u65e0\u793e\u56e2\u5219\u4e3aNULL<\/code><\/p>\n<\/li>\n<li>\n<p>\u5206\u652f 2\uff08INNER JOIN\uff09\uff1a<code>\u4ec5\u663e\u793a\u6709\u5339\u914d\u793e\u56e2\u7684\u5b66\u751f<\/code><\/p>\n<\/li>\n<\/ul>\n<ol>\n<li><strong>\u7ed3\u675f\u8282\u70b9<\/strong>\uff08\u5706\u5f62\uff0c\u84dd\u8272\uff09\uff1a<code>\u8f93\u51fa\u7ed3\u679c\uff1a\u59d3\u540d\u3001\u73ed\u7ea7\u3001\u793e\u56e2\u540d\u79f0<\/code><\/li>\n<\/ol>","protected":false},"excerpt":{"rendered":"<p>\u65b9\u6848\u4e00\uff1aSQL \u6838\u5fc3\u8bed\u6cd5\u793a\u610f\u56fe\uff08\u6587\u5b57\u63cf\u8ff0\u7248\uff09 \u9002\u5408\u7528 PPT\u3001Figma\u3001Canva \u7b49\u5de5\u5177\u7ed8\u5236\uff0c\u7ed3\u6784\u6e05\u6670\uff0c\u9002 [&hellip;]<\/p>","protected":false},"author":1,"featured_media":98,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-96","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"http:\/\/yuemustory.fun\/index.php\/wp-json\/wp\/v2\/posts\/96","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/yuemustory.fun\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/yuemustory.fun\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/yuemustory.fun\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/yuemustory.fun\/index.php\/wp-json\/wp\/v2\/comments?post=96"}],"version-history":[{"count":3,"href":"http:\/\/yuemustory.fun\/index.php\/wp-json\/wp\/v2\/posts\/96\/revisions"}],"predecessor-version":[{"id":117,"href":"http:\/\/yuemustory.fun\/index.php\/wp-json\/wp\/v2\/posts\/96\/revisions\/117"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/yuemustory.fun\/index.php\/wp-json\/wp\/v2\/media\/98"}],"wp:attachment":[{"href":"http:\/\/yuemustory.fun\/index.php\/wp-json\/wp\/v2\/media?parent=96"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/yuemustory.fun\/index.php\/wp-json\/wp\/v2\/categories?post=96"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/yuemustory.fun\/index.php\/wp-json\/wp\/v2\/tags?post=96"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}