{"id":154,"date":"2024-07-02T00:14:33","date_gmt":"2024-07-01T16:14:33","guid":{"rendered":"https:\/\/www.jiwenbo.top\/?p=154"},"modified":"2024-07-02T00:14:33","modified_gmt":"2024-07-01T16:14:33","slug":"mysql%e5%9f%ba%e7%a1%80%e7%af%87%ef%bc%9adql%e7%af%87","status":"publish","type":"post","link":"https:\/\/jiwenbo.top\/index.php\/2024\/07\/02\/mysql%e5%9f%ba%e7%a1%80%e7%af%87%ef%bc%9adql%e7%af%87\/","title":{"rendered":"MySql\u57fa\u7840\u7bc7\uff1aDQL\u7bc7"},"content":{"rendered":"\n

DQL:\u5373\u6570\u636e\u67e5\u8be2\u8bed\u53e5 \u67e5\u8be2\u6570\u636e\u5e93\u4e2d\u7684\u8bb0\u5f55\uff0c\u5173\u952e\u5b57 SELECT\uff0c\u8fd9\u5757\u5185\u5bb9\u975e\u5e38\u91cd\u8981\uff01<\/mark><\/strong><\/p>\n\n\n\n

\u4e00. \u6761\u4ef6\u8bed\u53e5\uff1awherer <\/p>\n\n\n\n

\u7528\u4e8e\u68c0\u7d22\u6570\u636e\u8868\u4e2d\u7b26\u5408\u6761\u4ef6\u7684\u8bb0\u5f55\u641c\u7d22\u6761\u4ef6\u53ef\u7531\u4e00\u4e2a\u6216\u591a\u4e2a\u903b\u8f91\u8868\u8fbe\u5f0f\u7ec4\u6210\uff0c\u7ed3\u679c\u4e00\u822c\u4e3a\u771f\u6216\u5047<\/p>\n\n\n\n

select \u5217\u540d from \u8868\u540d where \u5217\u540d =\u503c;<\/pre>\n\n\n\n
\u641c\u7d22\u6761\u4ef6\u76ee\u5f55\uff1a\u70b9\u51fb\u67e5\u770b<\/a><\/p>\n\n\n\n
\u4e8c. as \u53d6\u522b\u540d<\/p>\n\n\n\n
SELECT \u5b57\u6bb5\u540d as \u503c FORM \u8868\u540d;<\/pre>\n\n\n\n
\u4f7f\u7528as\u4e5f\u53ef\u4ee5\u4e3a\u8868\u53d6\u522b\u540d\uff0c\u8868\u91cc\u7684\u540d\u5b57\u6ca1\u6709\u53d8\uff0c\u53ea\u5f71\u54cd\u4e86\u67e5\u8be2\u51fa\u6765\u7684\u7ed3\u679c\u00a0\uff08\u4f5c\u7528\uff1a\u5355\u8868\u67e5\u8be2\u610f\u4e49\u4e0d\u5927\uff0c\u4f46\u662f\u5f53\u591a\u4e2a\u8868\u7684\u65f6\u5019\u53d6\u522b\u540d\u5c31\u597d\u64cd\u4f5c\uff0c\u5f53\u4e0d\u540c\u7684\u8868\u91cc\u6709\u76f8\u540c\u540d\u5b57\u7684\u5217\u7684\u65f6\u5019\u533a\u5206\u5c31\u4f1a\u597d\u533a\u5206\uff09<\/strong><\/p>\n\n\n\n
\u4e09. distinct \u53bb\u9664\u91cd\u590d\u8bb0\u5f55<\/p>\n\n\n\n
\u6ce8\u610f<\/mark>\uff1a\u5f53\u67e5\u8be2\u7ed3\u679c\u4e2d\u6240\u6709\u5b57\u6bb5\u5168\u90fd\u76f8\u540c\u65f6 \u624d\u7b97\u91cd\u590d\u7684\u8bb0\u5f55<\/p>\n\n\n\n
SELSCT DISTINCT * FROM \u8868\u540d;<\/pre>\n\n\n\n
\u6307\u5b9a\u5b57\u6bb5<\/strong>\uff1a\u661f\u53f7\u8868\u793a\u6240\u6709\u5b57\u6bb5<\/p>\n\n\n\n
\u624b\u52a8\u6307\u5b9a\u9700\u8981\u67e5\u8be2\u7684\u5b57\u6bb5<\/p>\n\n\n\n
SELECT DISTINCT s_name,s_birth FROM student;<\/pre>\n\n\n\n
\u8fd8\u53ef\u4e5f\u662f\u56db\u5219\u8fd0\u7b97\u805a\u5408\u51fd\u6570\u3002<\/p>\n\n\n\n
\u56db. \u5206\u7ec4 group by<\/p>\n\n\n\n
group by\uff1a\u662f\u6839\u636eby\u5bf9\u6570\u636e\u6309\u7167\u54ea\u4e2a\u5b57\u6bb5\u8fdb\u884c\u5206\u7ec4\uff0c\u6216\u8005\u662f\u54ea\u51e0\u4e2a\u5b57\u6bb5\u8fdb\u884c\u5206\u7ec4\u3002<\/p>\n\n\n\n
SELECT \u5b57\u6bb5\u540d FROM \u8868\u540d GROUP BY \u5b57\u6bb5\u540d\u79f0;<\/pre>\n\n\n\n
1. \u5355\u4e2a\u5b57\u6bb5\u5206\u7ec4<\/p>\n\n\n\n
SELECT COUNT(*) FROM \u8868\u540d GROUP BY \u5b57\u6bb5\u540d;<\/pre>\n\n\n\n
2. \u591a\u4e2a\u5b57\u6bb5\u5206\u7ec4<\/p>\n\n\n\n
SELECT \u5b57\u6bb51,\u5b57\u6bb52,COUNT(*) FROM \u8868\u540d GROUP BY \u5b57\u6bb51,\u5b57\u6bb52;<\/pre>\n\n\n\n
\u6ce8\u610f<\/mark>\uff1a\u591a\u4e2a\u5b57\u6bb5\u8fdb\u884c\u5206\u7ec4\u65f6\uff0c\u9700\u8981\u5c06s_name\u548cs_sex\u770b\u6210\u4e00\u4e2a\u6574\u4f53\uff0c\u53ea\u8981\u662fs_name\u548cs_sex\u76f8\u540c\u7684\u53ef\u4ee5\u5206\u6210\u4e00\u7ec4\uff1b\u5982\u679c\u53ea\u662fs_sex\u76f8\u540c\uff0cs_sex\u4e0d\u540c\u5c31\u4e0d\u662f\u4e00\u7ec4\u3002<\/p>\n\n\n\n
\u4e94. \u8fc7\u6ee4 having<\/p>\n\n\n\n
HAVING \u5b50\u53e5\u5bf9 GROUP BY \u5b50\u53e5\u8bbe\u7f6e\u6761\u4ef6\u7684\u65b9\u5f0f\u4e0e WHERE \u548c SELECT \u7684\u4ea4\u4e92\u65b9\u5f0f\u7c7b\u4f3c\u3002WHERE \u641c\u7d22\u6761\u4ef6\u5728\u8fdb\u884c\u5206\u7ec4\u64cd\u4f5c\u4e4b\u524d\u5e94\u7528\uff1b\u800c HAVING \u641c\u7d22\u6761\u4ef6\u5728\u8fdb\u884c\u5206\u7ec4\u64cd\u4f5c\u4e4b\u540e\u5e94\u7528\u3002HAVING \u8bed\u6cd5\u4e0e WHERE \u8bed\u6cd5\u7c7b\u4f3c\uff0c\u4f46 HAVING \u53ef\u4ee5\u5305\u542b\u805a\u5408\u51fd\u6570\u3002HAVING \u5b50\u53e5\u53ef\u4ee5\u5f15\u7528\u9009\u62e9\u5217\u8868\u4e2d\u663e\u793a\u7684\u4efb\u610f\u9879\u3002<\/p>\n\n\n\n
\u4f8b\u5b50\uff1a\u5982\u679c\u8981\u67e5\u8be2\u7537\u751f\u6216\u8005\u5973\u751f\uff0c\u4eba\u6570\u5927\u4e8e5\u7684\u6027\u522b<\/p>\n\n\n\n
SELECT s_sex as \u6027\u522b,COUNT(s_if) as \u4eba\u6570 FROM student GROUP BY s_sex HAVING COUNT(s_id)>5;<\/pre>\n\n\n\n
\u516d. order by \u6392\u5e8f<\/p>\n\n\n\n
\u6839\u636e\u67d0\u4e2a\u5b57\u6bb5\u6392\u5e8f\uff0c\u9ed8\u8ba4\u5347\u5e8f(\u4ece\u5c0f\u5230\u5927)<\/p>\n\n\n\n
SELECT * FROM \u8868\u540d ORDER BY \u5b57\u6bb5\u540d;<\/pre>\n\n\n\n
1. \u4e00\u4e2a\u5b57\u6bb5\uff0c\u964d\u5e8f\uff08\u4ece\u5927\u5230\u5c0f\uff09<\/p>\n\n\n\n
SELECT * FROM \u8868\u540d ORDER BY \u5b57\u6bb5\u540d DESC;<\/pre>\n\n\n\n
2. \u591a\u4e2a\u5b57\u6bb5<\/p>\n\n\n\n
SELECT * FROM \u8868\u540d ORDER BY \u5b57\u6bb5\u540d1 DESC, \u5b57\u6bb5\u540d2 ASC;<\/pre>\n\n\n\n
\u591a\u4e2a\u5b57\u6bb5\u7b2c\u4e00\u4e2a\u76f8\u540c\u5728\u6309\u7167\u7b2c\u4e8c\u4e2a<\/p>\n\n\n\n
asc:\u8868\u793a\u5347\u5e8f<\/p>\n\n\n\n
 limit:\u5206\u9875\u7528\u4e8e\u9650\u5236\u8981\u663e\u793a\u7684\u8bb0\u5f55\u6570\u91cf<\/p>\n\n\n\n
SELECT * FROM \u8868\u540d LIMIT \u4e2a\u6570;<\/pre>\n\n\n\n
SELECT * FROM \u8868\u540d LIMIT \u8d77\u59cb\u4f4d\u7f6e \u4e2a\u6570;<\/pre>\n\n\n\n
\u6ce8\u610f<\/mark>\uff1a\u8d77\u59cb\u4f4d\u7f6e \u4ece0<\/mark><\/strong>\u5f00\u59cb<\/code><\/p>\n\n\n\n
\u7ecf\u5178\u7684\u4f7f\u7528\u573a\u666f:\u5206\u9875\u663e\u793a<\/p>\n","protected":false},"excerpt":{"rendered":"
DQL:\u5373\u6570\u636e\u67e5\u8be2\u8bed\u53e5 \u67e5\u8be2\u6570\u636e\u5e93\u4e2d\u7684\u8bb0\u5f55\uff0c\u5173\u952e\u5b57 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-154","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/jiwenbo.top\/index.php\/wp-json\/wp\/v2\/posts\/154","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jiwenbo.top\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jiwenbo.top\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jiwenbo.top\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jiwenbo.top\/index.php\/wp-json\/wp\/v2\/comments?post=154"}],"version-history":[{"count":0,"href":"https:\/\/jiwenbo.top\/index.php\/wp-json\/wp\/v2\/posts\/154\/revisions"}],"wp:attachment":[{"href":"https:\/\/jiwenbo.top\/index.php\/wp-json\/wp\/v2\/media?parent=154"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jiwenbo.top\/index.php\/wp-json\/wp\/v2\/categories?post=154"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jiwenbo.top\/index.php\/wp-json\/wp\/v2\/tags?post=154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}