{"id":55,"date":"2021-03-13T12:17:24","date_gmt":"2021-03-13T04:17:24","guid":{"rendered":"http:\/\/daishen.ltd\/?p=55"},"modified":"2021-03-13T12:17:24","modified_gmt":"2021-03-13T04:17:24","slug":"select%e8%af%ad%e5%8f%a5%e7%9a%84%e5%ba%94%e7%94%a8","status":"publish","type":"post","link":"https:\/\/www.daishen.ltd\/?p=55","title":{"rendered":"select\u8bed\u53e5\u7684\u5e94\u7528"},"content":{"rendered":"<p><meta charset=\"UTF-8\"><meta name=\"viewport\" content=\"width=device-width initial-scale=1\"><br \/>\n<title>select\u8bed\u53e5\u7684\u5e94\u7528<\/title><\/p>\n<h1>select\u8bed\u53e5\u7684\u5e94\u7528<\/h1>\n<h2>select\u5355\u72ec\u4f7f\u7528\u7684\u60c5\u51b5<\/h2>\n<pre><code class=\"language-mysql\" lang=\"mysql\">-- select @@xxx \u67e5\u770b\u7cfb\u7edf\u53c2\u6570\nSELECT @@port;\nSELECT @@basedir;\nSELECT @@datadir;\nSELECT @@socket;\nSELECT @@server_id;\nSELECT @@innodb_flush_log_at_trx_commit;\n<\/code><\/pre>\n<pre><code class=\"language-mysql\" lang=\"mysql\">-- select \u51fd\u6570();\nSELECT NOW();\nSELECT DATABASE();\nSELECT USER();\nSELECT CONCAT(\"hello world\");\nSELECT CONCAT(USER,\"@\",HOST) FROM mysql.user;\nSELECT GROUP_CONCAT(USER,\"@\",HOST) FROM mysql.user;\n<\/code><\/pre>\n<h2>select\u901a\u7528\u8bed\u6cd5\u3001\u6267\u884c\u987a\u5e8f\uff08\u5355\u8868\uff09<\/h2>\n<pre><code class=\"language-mysql\" lang=\"mysql\">select \u5217\nfrom \u8868\nwhere  \u6761\u4ef6\ngroup by \u6761\u4ef6\nhaving  \u6761\u4ef6\norder by \u6761\u4ef6\nlimit\n<\/code><\/pre>\n<h2>\u5355\u8868\u5b50\u53e5-from<\/h2>\n<pre><code class=\"language-undefined\" lang=\"undefined\">SELECT \u52171,\u52172 FROM \u8868\nSELECT  *  FROM \u8868\n<\/code><\/pre>\n<p>\u4f8b\u5b50:<br \/>\n&#8212; \u67e5\u8be2stu\u4e2d\u6240\u6709\u7684\u6570\u636e(\u4e0d\u8981\u5bf9\u5927\u8868\u8fdb\u884c\u64cd\u4f5c)<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">SELECT * FROM stu ;\n<\/code><\/pre>\n<p>&#8212; \u67e5\u8be2stu\u8868\u4e2d,\u5b66\u751f\u59d3\u540d\u548c\u5165\u5b66\u65f6\u95f4<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">SELECT sname , intime FROM stu;\n<\/code><\/pre>\n<h3>\u5355\u8868\u5b50\u53e5-where<\/h3>\n<pre><code class=\"language-undefined\" lang=\"undefined\">SELECT \u5217,\u5217 FROM \u8868 WHERE \u8fc7\u6ee4\u6761\u4ef6;\n<\/code><\/pre>\n<h5>where\u914d\u5408\u7b49\u503c\u67e5\u8be2<\/h5>\n<p>\u4f8b\u5b50:<br \/>\n&#8212; \u67e5\u8be2\u4e2d\u56fd(CHN)\u6240\u6709\u57ce\u5e02\u4fe1\u606f<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">SELECT * FROM city WHERE countrycode='CHN';\n<\/code><\/pre>\n<p>&#8212; \u67e5\u8be2\u5317\u4eac\u5e02\u7684\u4fe1\u606f<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">SELECT * FROM city WHERE NAME='peking';\n<\/code><\/pre>\n<p>&#8212; \u67e5\u8be2\u7518\u8083\u7701\u6240\u6709\u57ce\u5e02\u4fe1\u606f<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">SELECT * FROM city WHERE district='gansu';\n<\/code><\/pre>\n<h5>where\u914d\u5408\u6bd4\u8f83\u64cd\u4f5c\u7b26(&gt; &lt; &gt;= &lt;= &lt;&gt;)<\/h5>\n<p>\u4f8b\u5b50:<br \/>\n&#8212; \u67e5\u8be2\u4e16\u754c\u4e0a\u5c11\u4e8e100\u4eba\u7684\u57ce\u5e02<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">SELECT * FROM city WHERE population&lt;100;\n<\/code><\/pre>\n<h5>where\u914d\u5408\u903b\u8f91\u8fd0\u7b97\u7b26(and  or )<\/h5>\n<p>\u4f8b\u5b50:<br \/>\n&#8212; \u4e2d\u56fd\u4eba\u53e3\u6570\u91cf\u5927\u4e8e500w<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">SELECT * FROM city WHERE countrycode='CHN' AND population&gt;5000000;\n<\/code><\/pre>\n<p>&#8212; \u4e2d\u56fd\u6216\u7f8e\u56fd\u57ce\u5e02\u4fe1\u606f<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';\n<\/code><\/pre>\n<h5>where\u914d\u5408like\u8bed\u53e5\u6a21\u7cca\u67e5\u8be2<\/h5>\n<p>\u4f8b\u5b50:<br \/>\n&#8212; \u67e5\u8be2\u7701\u7684\u540d\u5b57\u524d\u9762\u5e26guang\u5f00\u5934\u7684<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">SELECT * FROM city WHERE district LIKE 'guang%';    \n\u6ce8\u610f:\u4e0d\u8981\u51fa\u73b0\u7c7b\u4f3c\u4e8e%CH%\uff0c\u524d\u540e\u90fd\u6709\u767e\u5206\u53f7\u7684\u8bed\u53e5\uff0c\u56e0\u4e3a\u4e0d\u8d70\u7d22\u5f15\uff0c\u6027\u80fd\u6781\u5dee\n\u5982\u679c\u4e1a\u52a1\u4e2d\u6709\u5927\u91cf\u9700\u6c42,\u6211\u4eec\u7528\"ES\"\"\u6765\u66ff\u4ee3\n<\/code><\/pre>\n<h5>where\u914d\u5408in\u8bed\u53e5<\/h5>\n<p>&#8212; \u4e2d\u56fd\u6216\u7f8e\u56fd\u57ce\u5e02\u4fe1\u606f<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA');\n<\/code><\/pre>\n<h5>where\u914d\u5408between and<\/h5>\n<p>\u4f8b\u5b50:<br \/>\n&#8212; \u67e5\u8be2\u4e16\u754c\u4e0a\u4eba\u53e3\u6570\u91cf\u5927\u4e8e100w\u5c0f\u4e8e200w\u7684\u57ce\u5e02\u4fe1\u606f<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">SELECT * FROM city  WHERE population &gt;1000000 AND population &lt;2000000;\nSELECT * FROM city  WHERE population BETWEEN 1000000 AND 2000000;\n<\/code><\/pre>\n<h3>group by + \u5e38\u7528\u805a\u5408\u51fd\u6570<\/h3>\n<h5>\u4f5c\u7528<\/h5>\n<pre><code class=\"language-csharp\" lang=\"csharp\">\u6839\u636e by\u540e\u9762\u7684\u6761\u4ef6\u8fdb\u884c\u5206\u7ec4\uff0c\u65b9\u4fbf\u7edf\u8ba1\uff0cby\u540e\u9762\u8ddf\u4e00\u4e2a\u5217\u6216\u591a\u4e2a\u5217\n<\/code><\/pre>\n<h5>\u5e38\u7528\u805a\u5408\u51fd\u6570<\/h5>\n<pre><code class=\"language-swift\" lang=\"swift\">**max()**      \uff1a\u6700\u5927\u503c\n**min()**      \uff1a\u6700\u5c0f\u503c\n**avg()**      \uff1a\u5e73\u5747\u503c\n**sum()**      \uff1a\u603b\u548c\n**count()**    \uff1a\u4e2a\u6570\ngroup_concat() : \u5217\u8f6c\u884c\n<\/code><\/pre>\n<h5>\u4f8b\u5b50\uff1a<\/h5>\n<p>\u4f8b\u5b501\uff1a\u7edf\u8ba1\u4e16\u754c\u4e0a\u6bcf\u4e2a\u56fd\u5bb6\u7684\u603b\u4eba\u53e3\u6570.<\/p>\n<pre><code class=\"language-php\" lang=\"php\">USE world\nSELECT countrycode ,SUM(population)    FROM  city  GROUP BY countrycode;\n<\/code><\/pre>\n<p>\u4f8b\u5b502\uff1a \u7edf\u8ba1\u4e2d\u56fd\u5404\u4e2a\u7701\u7684\u603b\u4eba\u53e3\u6570\u91cf(\u7ec3\u4e60)<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">SELECT district,SUM(Population) FROM city  WHERE countrycode='chn' GROUP BY district;\n<\/code><\/pre>\n<p>\u4f8b\u5b503\uff1a\u7edf\u8ba1\u4e16\u754c\u4e0a\u6bcf\u4e2a\u56fd\u5bb6\u7684\u57ce\u5e02\u6570\u91cf(\u7ec3\u4e60)<\/p>\n<pre><code class=\"language-objectivec\" lang=\"objectivec\">SELECT countrycode,COUNT(id)  FROM city GROUP BY countrycode;\n<\/code><\/pre>\n<h3>having<\/h3>\n<pre><code class=\"language-csharp\" lang=\"csharp\">where|group|having\n<\/code><\/pre>\n<p>\u4f8b\u5b504\uff1a\u7edf\u8ba1\u4e2d\u56fd\u6bcf\u4e2a\u7701\u7684\u603b\u4eba\u53e3\u6570\uff0c\u53ea\u6253\u5370\u603b\u4eba\u53e3\u6570\u5c0f\u4e8e100<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">SELECT district,SUM(Population)\nFROM city\nWHERE countrycode='chn'\nGROUP BY district\nHAVING SUM(Population) &lt; 1000000 ;\n<\/code><\/pre>\n<h3>order by + limit<\/h3>\n<h5>\u4f5c\u7528<\/h5>\n<pre><code class=\"language-csharp\" lang=\"csharp\">\u5b9e\u73b0\u6392\u5e8f\uff0cby\u540e\u6dfb\u52a0\u6761\u4ef6\u5217\n<\/code><\/pre>\n<h5>\u5e94\u7528\u6848\u4f8b<\/h5>\n<ol start=\"\">\n<li>\u67e5\u770b\u4e2d\u56fd\u6240\u6709\u7684\u57ce\u5e02\uff0c\u5e76\u6309\u4eba\u53e3\u6570\u8fdb\u884c\u6392\u5e8f(\u4ece\u5927\u5230\u5c0f)<\/li>\n<\/ol>\n<pre><code class=\"language-shell\" lang=\"shell\">SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;\n<\/code><\/pre>\n<ol start=\"\">\n<li>\u7edf\u8ba1\u4e2d\u56fd\u5404\u4e2a\u7701\u7684\u603b\u4eba\u53e3\u6570\u91cf\uff0c\u6309\u7167\u603b\u4eba\u53e3\u4ece\u5927\u5230\u5c0f\u6392\u5e8f<\/li>\n<\/ol>\n<pre><code class=\"language-php\" lang=\"php\">SELECT district AS \u7701 ,SUM(Population) AS \u603b\u4eba\u53e3\nFROM city\nWHERE countrycode='chn'\nGROUP BY district\nORDER BY \u603b\u4eba\u53e3 DESC ;\n<\/code><\/pre>\n<ol start=\"\">\n<li>\u7edf\u8ba1\u4e2d\u56fd,\u6bcf\u4e2a\u7701\u7684\u603b\u4eba\u53e3,\u627e\u51fa\u603b\u4eba\u53e3\u5927\u4e8e500w\u7684,\u5e76\u6309\u603b\u4eba\u53e3\u4ece\u5927\u5230\u5c0f\u6392\u5e8f,\u53ea\u663e\u793a\u524d\u4e09\u540d<\/li>\n<\/ol>\n<pre><code class=\"language-csharp\" lang=\"csharp\">SELECT  district, SUM(population)  FROM  city \nWHERE countrycode='CHN'\nGROUP BY district \nHAVING SUM(population)&gt;5000000\nORDER BY SUM(population) DESC\nLIMIT 3 ;\n\nLIMIT N ,M ---&gt;\u8df3\u8fc7N,\u663e\u793a\u4e00\u5171M\u884c\nLIMIT 5,5\nLIMIT M,N:\u8df3\u8fc7M\u884c,\u663e\u793a\u4e00\u5171N\u884c\nLIMIT Y OFFSET X:\u8df3\u8fc7x\u884c,\u663e\u793a\u4e00\u5171Y\u884c\n\n\nSELECT  district, SUM(population)  FROM  city \nWHERE countrycode='CHN'\nGROUP BY district \nHAVING SUM(population)&gt;5000000\nORDER BY SUM(population) DESC\nLIMIT 5,5;\n<\/code><\/pre>\n<h3>distinct\uff1a\u53bb\u91cd\u590d<\/h3>\n<pre><code class=\"language-cpp\" lang=\"cpp\">SELECT countrycode FROM city ;\nSELECT DISTINCT(countrycode) FROM city  ;\n<\/code><\/pre>\n<h3>\u8054\u5408\u67e5\u8be2- union all<\/h3>\n<pre><code class=\"language-csharp\" lang=\"csharp\">-- \u4e2d\u56fd\u6216\u7f8e\u56fd\u57ce\u5e02\u4fe1\u606f\n\nSELECT * FROM city \nWHERE countrycode IN ('CHN' ,'USA');\n\nSELECT * FROM city WHERE countrycode='CHN'\nUNION ALL\nSELECT * FROM city WHERE countrycode='USA'\n\n\u8bf4\u660e:\u4e00\u822c\u60c5\u51b5\u4e0b,\u6211\u4eec\u4f1a\u5c06 IN \u6216\u8005 OR \u8bed\u53e5 \u6539\u5199\u6210 UNION ALL,\u6765\u63d0\u9ad8\u6027\u80fd\nUNION     \u53bb\u91cd\u590d\nUNION ALL \u4e0d\u53bb\u91cd\u590d\n<\/code><\/pre>\n<h3>\u591a\u8868\u8fde\u63a5\u67e5\u8be2\uff08\u5185\u8fde\u63a5\uff09<\/h3>\n<h4>\u591a\u8868\u8fde\u63a5\u57fa\u672c\u8bed\u6cd5<\/h4>\n<p>\u6700\u6838\u5fc3\u7684\u662f\uff0c\u627e\u5230\u591a\u5f20\u8868\u4e4b\u95f4\u7684\u5173\u8054\u6761\u4ef6\u5217<\/p>\n<p>\u5217\u4e66\u5199\u65f6\uff0c\u5fc5\u987b\u662f\uff1a\u8868\u540d.\u5217<\/p>\n<p>\u5c06\u6240\u6709\u7684\u8fc7\u6ee4,\u5206\u7ec4,\u6392\u5e8f\u7b49\u6761\u4ef6\u6309\u987a\u5e8f\u5199\u5728on\u7684\u540e\u9762<\/p>\n<pre><code class=\"language-mysql\" lang=\"mysql\">SELECT\ncountry.name,\ncountry.SurfaceArea,\ncity.name,\ncity.Population\nFROM city\nJOIN country\nON city.CountryCode = country.code\nWHERE city.population&lt;100;\n<\/code><\/pre>\n<p>\u591a\u5f20\u8868<\/p>\n<pre><code class=\"language-mysql\" lang=\"mysql\">A\nJOIN B\nON A.x=B.y\nJOIN C\nONB.m=C.n.\n<\/code><\/pre>\n<p><img decoding=\"async\" src=\"C:\\Users\\HAIRUI_H\\AppData\\Roaming\\Typora\\typora-user-images\\image-20201120151602216.png\" referrerpolicy=\"no-referrer\" alt=\"image-20201120151602216\"><\/p>\n<h3>\u522b\u540d\u5e94\u7528<\/h3>\n<h4>\u8868\u522b\u540d<\/h4>\n<p>\u8868\u522b\u540d\u662f\u53ef\u4ee5\u5168\u5c40\u8c03\u7528\u7684<\/p>\n<pre><code class=\"language-mysql\" lang=\"mysql\">SELECT t. tname , GROUP_ CONCAT (CONCAT (st.sname,\":\",sc. score) )\nEROM teacher as t\nJOIN\ncourse as C\nON t.tno =C. tno\nJOIN sc\nON c.cno=sc.cno\nJOIN student as st\nON sc.sno=st.sno\nWHERE sc.score&lt;60\nGROUP BY t.tno\n<\/code><\/pre>\n<h4>\u5217\u522b\u540d<\/h4>\n<p>\u5217\u522b\u540d\u53ef\u4ee5\u88abhaving \u548c order \u8c03\u7528<\/p>\n<pre><code class=\"language-mysql\" lang=\"mysql\">SELECT t.tname as\u8bb2\u5e08\u540d, GROUP_ CONCAT (CONCAT (st. sname, \":\",sc. score))\nas\u4e0d\u53ca\u683c\u7684\nFROM teacher as t\nJOIN course as C\nON t.tno=c. tno\nJOIN sc\nON c.cno=sc.cno\nJOIN student as st\nON sc. sno=st.sno \nWHERE sc.score&lt;60\nGROUP BY t.tno\n<\/code><\/pre>\n<h3>group_ concat<\/h3>\n<p>\u5217\u8f6c\u884c\u805a\u5408\u51fd\u6570<\/p>\n<pre><code class=\"language-mysql\" lang=\"mysql\">select user,group_concat(host) from mysql.user group by user;\n<\/code><\/pre>\n<h3>concat<\/h3>\n<p>\u505a\u5217\u503c\u62fc\u63a5<\/p>\n<pre><code class=\"language-mysql\" lang=\"mysql\">select concat(user,\"@\",host) from mysql.user;\n<\/code><\/pre>\n<h3>\u5173\u4e8e\u591a\u8868\u8fde\u63a5\u8bed\u6cd5\u89c4\u5219<\/h3>\n<p>1.\u9996\u5148\u627e\u6d89\u53ca\u5230\u7684\u6240\u6709\u8868<br \/>\n2.\u627e\u5230\u8868\u548c\u8868\u4e4b;\u95f4\u7684\u5173\u8054\u5217<br \/>\n3.\u5173\u8054\u6761\u4ef6\u5199\u5728on\u540e\u9762<br \/>\nA join   B   on  \u5173\u8054\u5217<br \/>\n4.\u6240\u6709\u9700\u8981\u67e5\u8be2\u7684\u4fe1\u606f\u653e\u5728select\u540e<br \/>\n5.\u5176\u4ed6\u7684\u8fc7\u6ee4\u6761\u4ef6where   group by   having   order by   limit\u5f80\u6700\u540e\u653e<\/p>\n<p>\u6ce8\u610f:\u5bf9\u591a\u8868\u8fde\u63a5\u4e2d\uff0c\u9a71\u52a8\u8868\u9009\u62e9\u6570\u636e\u884c\u5c11\u7684\u8868\u3002\u540e\u7eed\u6240\u6709\u8868\u7684\u5173\u8054\u5217\u5c3d\u91cf\u662f\u4e3b\u952e\u6216\u552f\u4e00\u952e\uff08\u8868\u8bbe\u8ba1\uff09\uff0c\u81f3\u5c11\u5efa\u7acb\u4e00\u4e2a\u7d22\u5f15\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>select\u8bed\u53e5\u7684\u5e94\u7528 select\u8bed\u53e5\u7684\u5e94\u7528 select\u5355\u72ec\u4f7f\u7528\u7684\u60c5\u51b5 &#8212; select @@xxx \u67e5 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-55","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts\/55","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=55"}],"version-history":[{"count":1,"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts\/55\/revisions"}],"predecessor-version":[{"id":56,"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts\/55\/revisions\/56"}],"wp:attachment":[{"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=55"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=55"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=55"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}