select \u8bed\u53e5<\/p>
group by \u5b50\u53e5\uff0c\u5206\u7ec4\u67e5\u8be2<\/p>
\u5bf9\u67e5\u8be2\u7ed3\u679c\uff08\u5df2\u7ecf\u901a\u8fc7where\u5b50\u53e5\u8fc7\u6ee4\u4e4b\u540e\u7684\u6570\u636e,\u4e5f\u5c31\u662f\u5148select\u518dgroup by\uff09\uff0c\u6309\u7167\u67d0\u4e2a\u5b57\u6bb5\uff0c\u8fdb\u884c\u5206\u7ec4\uff01<\/p>
<\/p>
group by \u5b57\u6bb5\uff01<\/p>
\u5728\u5206\u7ec4\u7684\u7ed3\u679c\u4e2d\uff0c\u53ea\u4f1a\u663e\u793a\u7ec4\u5185\u7684\u5934\u4e00\u6761\u8bb0\u5f55\uff01 \u5982\u679c\u9700\u8981\u6392\u5e8f\uff0c\u6700\u597d\u5148\u6392\u5e8f\u540e\u5206\u7ec4
\u5206\u7ec4\u7684\u4f5c\u7528\uff0c\u4e0d\u5728\u67e5\u8be2\u6bcf\u4e2a\u7ec4\u5185\u7684\u5177\u4f53\u6570\u636e\u3002\u800c\u5176\u4f5c\u7528\u4e3b\u8981\u662f\u5728\u5206\u7ec4\u7edf\u8ba1\u4e0a\uff1a<\/p>
\u4f8b\uff1aselect * from student where age>20 group by classid;
<\/p>
\u5408\u8ba1\u51fd\u6570<\/p>
\u6b64\u65f6\u9700\u8981\u4f7f\u7528\u7edf\u8ba1\u51fd\u6570\uff08\u5408\u8ba1\u51fd\u6570\uff09\u52a0\u4ee5\u914d\u5408\uff01<\/p>
\u5408\u8ba1\u51fd\u6570\u4f8b\u5982\uff1acount() \u53ef\u4ee5\u7edf\u8ba1\u7ed3\u679c\u4e2d\u7684\u8bb0\u5f55\u6570\uff0c\u4f46\u662f\u4e00\u65e6\u4f7f\u7528\u4e86\u5206\u7ec4\u67e5\u8be2\uff0c\u5219\u53ea\u4f1a\u7edf\u8ba1\u7ec4\u5185\u7684\u6570\u636e\uff01<\/p>
\u4f8b\uff1a\u7edf\u8ba1\u6bcf\u4e2a\u73ed\u6709\u591a\u5c11\u4e2a\u4eba\uff1b<\/p>
select count(*) as ct from student group by classid;
<\/p>
count()\uff0c\u7edf\u8ba1\u8bb0\u5f55\u6570\u3002\u5178\u578b\u7684\u4f7f\u7528\u662f count(*)\uff0c\u4f46\u662f\u9664\u4e86*\u4e4b\u5916\uff0c\u662f\u53ef\u4ee5\u4f7f\u7528\u5b57\u6bb5\u540d\u7684\uff01<\/p>
\u5176\u4e2d\uff0c\u53ea\u8981\u8bb0\u5f55\u5b58\u5728\uff0c\u5219count(*)\u5c31\u4f1a\u7edf\u8ba1\u5230\u6570\u636e\uff0c\u800c\u5982\u679c\u76f8\u5e94\u7684\u5b57\u6bb5\u4e3anull\uff0c\u5219count(\u5b57\u6bb5)\u4e0d\u4f1a\u7edf\u8ba1\u4e0a\u6570\u636e\uff1a<\/p>
sum(\u5b57\u6bb5\u8868\u8fbe\u5f0f)\uff0c\u7edf\u8ba1\u548c\uff0c\u5bf9\u67d0\u4e2a\u5b57\u6bb5\u6c42\u548c\uff01<\/p>
avg()\uff0c\u5e73\u5747\u503c<\/p>
max(\u5b57\u6bb5\u8868\u8fbe\u5f0f)\uff0c\u6700\u5927\u503c<\/p>
min(\u5b57\u6bb5\u8868\u8fbe\u5f0f)\uff0c\u6700\u5c0f\u503c<\/p>
group_concat(\u5b57\u6bb5\u8868\u8fbe\u5f0f)\uff0c\u7ec4\u5185\u8fde\u63a5\u5b57\u7b26\u4e32<\/p>
\u5206\u7ec4\u6392\u5e8f<\/p>
\u9ed8\u8ba4\u7684\u5206\u7ec4\u540e\u4f1a\u6309\u7167\u5206\u7ec4\u5b57\u6bb5\u5bf9\u7ed3\u679c\u8fdb\u884c\u6392\u5e8f\u3002\u53ef\u4ee5group by\u5b50\u53e5\u6307\u5b9a\u6392\u5e8f\u7684\u65b9\u5f0f\uff08\u5347\u5e8fASC\u548c\u964d\u5e8fDESC\uff09<\/p>
<\/p>
\u591a\u5b57\u6bb5\u5206\u7ec4<\/p>
\u4f7f\u7528\u9017\u53f7\u5206\u9694\u5f00\u591a\u4e2a\u5206\u7ec4\u5b57\u6bb5\u5373\u53ef\uff01\u7edf\u8ba1\u65f6\uff0c\u4f1a\u6309\u7167\u591a\u4e2a\u5b57\u6bb5\u7684\u7ec4\u5408\u5206\u7ec4\u751f\u6210\u7ed3\u679c\uff01<\/p>
\u4f8b\u5982\uff1a\u7edf\u8ba1\u6bcf\u4e2a\u73ed\u7ea7\u5185\u7684\u7537\u751f\u548c\u5973\u751f\u7684\u6570\u91cf\uff01<\/p>
select count(*),classid,sex from student group by classid,sex;
<\/p>
\u5982\u679c\u662f\u591a\u5b57\u6bb5\u5206\u7ec4\uff0c\u9700\u8981\u67e5\u770b\u6bcf\u4e2a\u5206\u7ec4\u7684\u7684\u8be6\u7ec6\u60c5\u51b5\uff1a<\/p>
\u53ef\u4ee5\u4f7f\u7528\u5173\u952e\u5b57with rollup\u5173\u952e\u5b57\u6765\u56de\u6eda\u7edf\u8ba1\uff1a<\/p>
<\/p>
having\u5b50\u53e5\uff0c\u6761\u4ef6\u5b50\u53e5<\/p>
\u529f\u80fd\u4e0a\u4e0ewhere\u7c7b\u4f3c\uff0c\u90fd\u662f\u6761\u4ef6\u5b50\u53e5\uff01
\u4e3b\u8981\u7684\u533a\u522b\uff0c\u5728\u4e8e\u6267\u884c\u65f6\u673a\uff1a<\/p>
\u6267\u884c\u65f6\u673a\uff1a<\/p>
where\uff0c\u662f\u5f00\u59cb\u65f6\uff0c\u4ece\u6570\u636e\u6e90\u4e2d\u68c0\u7d22\u6570\u636e\u7684\u6761\u4ef6\u3002<\/p>
\u800c having\uff0c\u662f\u5728\u7b5b\u9009\uff0c\u5206\u7ec4\u4e4b\u540e\uff0c\u5728\u5f97\u5230\u7684\u7ed3\u679c\u4e2d\uff0c\u518d\u6b21\u8fdb\u884c\u7b5b\u9009\u7684\u8bed\u6cd5\uff01\uff08\u6240\u4ee5\u4ed6\u4eec\u7684\u987a\u5e8f\u7c7b\u4f3c\u4e8e\u8fd9\u6837where->group by ->having)
\u56e0\u6b64 having\u7684\u7ed3\u679c\u4e00\u5b9a\u662f where \u5df2\u7ecf\u8fc7\u6ee4\u4e4b\u540e\u7684\u7ed3\u679c!
having\u7684\u4f5c\u7528\u5728\u4e8e\uff0c\u5bf9\u7ed3\u679c\u8fdb\u884c\u4e8c\u6b21\u5904\u7406\uff01<\/p>
\u4e3a\u5565\u8981\u6709 having\uff1awhere\u6ca1\u6709\u529e\u6cd5\u4e0e\u5408\u8ba1\u51fd\u6570\u4e00\u8d77\u4f7f\u7528\uff01\u539f\u56e0\u5728\u4e8e\u6267\u884c\u987a\u5e8f\u95ee\u9898\uff01<\/p>
\u5178\u578b\u7684\u5e94\u8be5\u4f7f\u7528\u522b\u7684\u5f62\u5f0f\u4e3a having\u5b8c\u6210\u6761\u4ef6\u8868\u8fbe\u5f0f\uff1a<\/p>
order by \u6392\u5e8f\u5b50\u53e5<\/p>
\u5bf9\u7ed3\u679c\u8fdb\u884c\u6392\u5e8f\u7684\u8bed\u53e5\uff01<\/p>
<\/p>
order by \u5b57\u6bb5\u540d [asc|desc], [\u5b57\u6bb5\u540d[asc|desc],]<\/p>
\u53ef\u89c1\u53ef\u6309\u7167\u591a\u4e2a\u5b57\u6bb5\u8fdb\u884c\u6392\u5e8f<\/p>
\u539f\u5219\u662f\uff0c\u5148\u6309\u7167\u7b2c\u4e00\u4e2a\u5b57\u6bb5\u8fdb\u884c\u6392\u5e8f\uff0c\u5982\u679c\u5b57\u6bb5\u503c\u76f8\u540c\uff0c\u5219\u91c7\u7528\u7b2c\u4e8c\u4e2a\uff0c\u4ee5\u6b64\u7c7b\u63a8\uff01<\/p>
<\/p>
limit \u5b50\u53e5<\/p>
\u9650\u5236\u7ed3\u679c\u8bb0\u5f55\u6570\u7684\u5b50\u53e5\uff01<\/p>
\u4ece\u6240\u6709\u7684\u7ed3\u679c\u4e2d\uff0c\u9009\u62e9\u90e8\u5206\u7ed3\u679c\u7684\u5b50\u53e5\uff01<\/p>
select * from tablename limit start,size;
\u4e0a\u9762\u7684\u662f\u8bb0\u5f55\u7684\u4f4d\u7f6e\uff1a<\/p>
\u53ef\u4ee5\u4ece\u67d0\u4e2a\u4f4d\u7f6e\u5f00\u59cb\uff0c\u53d6\u5f97\u591a\u5c11\u6761\uff01<\/p>
limit start, size;<\/p>
start:\u8d77\u59cb\u4f4d\u7f6e<\/p>
size\uff0c\u53d6\u5f97\u7684\u8bb0\u5f55\u6570<\/p>
\u6ce8\u610f\uff0c\u7b2c\u4e8c\u4e2a\u53c2\u6570size\u662f\u957f\u5ea6\uff0c\u800c\u4e0d\u662f\u7ec8\u6b62\u4f4d\u7f6e\uff01<\/p>
select * from tablename limit size;
\u8fd8\u6709\u4e00\u4e2a\u7b80\u5199\uff0c\u7701\u7565start\u8d77\u59cb\u4f4d\u7f6e\uff0c\u8868\u793a\u4ece\u7b2c\u4e00\u6761\u8bb0\u5f55\u5f00\u59cb\uff1a<\/p>
<\/p>
<\/p>
select\u7684\u5b50\u53e5\u7684\u603b\u7ed3<\/p>
select\u5b50\u53e5\u7684\u5168\u90e8\u5b50\u53e5<\/p>
\u4e66\u5199\u987a\u5e8f<\/p>
\u5b57\u6bb5\u8868\u8fbe\u5f0f\uff0cfrom\u5b50\u53e5\uff0cwhere\u5b50\u53e5\uff0cgroup by\u5b50\u53e5\uff0chaving\u5b50\u53e5\uff0corder by\u5b50\u53e5\uff0climit\u5b50\u53e5\u3002<\/p>
\u4e66\u5199\u987a\u5e8f\uff0c\u4e0e\u6267\u884c\u987a\u5e8f\uff01\u51e0\u4e4e\u662f\u4e00\u6837\u7684\uff01<\/p>
\u6267\u884c\u987a\u5e8f<\/p>
from<\/p>
where<\/p>
group by<\/p>
\u5b57\u6bb5\u8868\u8fbe\u5f0f\uff0c\u5408\u8ba1\u51fd\u6570\u8868\u8fbe\u5f0f<\/p>
having<\/p>
order by<\/p>
limit<\/p>
<\/p>
\u4e66\u5199\u987a\u5e8f\u4e0d\u80fd\u9519\uff0c\u4f46\u662f\u5b50\u53e5\u51e0\u4e4e\u90fd\u53ef\u4ee5\u7701\u7565\uff01\u7701\u7565\u8868\u793a\u4e0d\u53d1\u751f\u64cd\u4f5c\uff01<\/p>
<\/p>
<\/p>
\u5b50\u67e5\u8be2\uff0csub-query<\/p>
\u51fa\u73b0\u5728\u5176\u4ed6\u8bed\u53e5\u5185\u90e8\u7684\u67e5\u8be2\u8bed\u53e5\uff0c\u79f0\u4e4b\u4e3a\u5b50\u67e5\u8be2<\/p>
<\/p>
<\/p>
\u5b50\u67e5\u8be2\u7684\u5206\u7c7b<\/p>
\u5206\u7c7b\u7684\u4f9d\u636e\uff01<\/p>
\u4e24\u79cd\u5206\u7c7b\u4f9d\u636e\uff1a<\/p>
1\uff0c \u4f9d\u636e\u5b50\u67e5\u8be2\u51fa\u73b0\u7684\u4f4d\u7f6e\uff01<\/p>
where\u578b\u5b50\u67e5\u8be2\uff0c\u51fa\u73b0\u5728where\u5b50\u53e5\u5185\uff01<\/p>
from \u578b\u5b50\u67e5\u8be2\uff0c\u51fa\u73b0\u5728from\u5b50\u53e5\u5185\uff01<\/p>
<\/p>
2\uff0c \u4f9d\u636e\u5b50\u67e5\u8be2\u7684\u8fd4\u56de\u6570\u636e\u7684\u683c\u5f0f\uff01<\/p>
\u6807\u91cf\u5b50\u67e5\u8be2\uff0c\u8fd4\u56de\u503c\u662f\u4e00\u4e2a\u6570\u636e\uff0c\u79f0\u4e4b\u4e3a\u6807\u91cf\u5b50\u67e5\u8be2\uff01<\/p>
\u5217\u5b50\u67e5\u8be2\uff0c\u8fd4\u56de\u4e00\u4e2a\u5217\uff0c<\/p>
\u884c\u5b50\u67e5\u8be2\uff0c\u8fd4\u56de\u4e00\u4e2a\u884c\uff0c<\/p>
\u8868\u5b50\u67e5\u8be2\uff0c\u8fd4\u56de\u7684\u662f\u4e00\u4e2a\u4e8c\u7ef4\u8868<\/p>
<\/p>
<\/p>
from\u578b<\/p>
\u4f8b\u5b50\uff1a\u627e\u51fa\u6bcf\u4e2a\u73ed\u91cc\u9762\uff0c\u8eab\u9ad8\u6700\u9ad8\u7684\u5b66\u751f\u4fe1\u606f<\/p>
select * from (select * from student order by height desc) as tmp group by classid;<\/p>
\u4f46\u662f\u8fd9\u6837\u67e5\u6709\u4e00\u4e2a\u95ee\u9898\uff0c\u5982\u679c\u540c\u73ed\u91cc\u9762\u6709\u51e0\u4e2a\u76f8\u540c\u8eab\u9ad8\u4e14\u5747\u4e3a\u6700\u9ad8\u7684\u5b66\u751f\u65f6\uff0c\u53ea\u80fd\u67e5\u51fa\u4e00\u4e2a\uff1b\u56e0\u4e3agroup by \u9ed8\u8ba4\u53ea\u663e\u793a\u7b2c\u4e00\u4e2a\uff1b\u53ef\u4ee5\u7528in\u6761\u4ef6\uff0c\u540e\u9762\u4f1a\u8bf4\u5230
\u7559\u610f\uff1afrom \u9700\u8981\u4e00\u4e2a\u6570\u636e\u8fd8\u662f\u4e00\u4e2a\u8868\uff0c\u9700\u8981\u5c06\u5b50\u67e5\u8be2\u8fd4\u56de\u7684\u6570\u636e\uff0c\u5bfc\u51fa\u6210\u8868\u5373\u53ef\uff01<\/p>
\u4e3a\u5b50\u67e5\u8be2\u8d77\u4e2a\u522b\u540d\u5373\u53ef\uff01<\/p>
<\/p>
\u5217\u5b50\u67e5\u8be2<\/p>
\u8fd4\u56de\u503c\u5e94\u8be5\u662f\u4e00\u5217\uff01<\/p>
\u7531\u4e8e\u8fd4\u56de\u7684\u662f\u4e00\u5217\uff0c\u662f\u4e00\u7c7b\u6570\u636e\uff0c\u770b\u6210\u662f\u4e00\u4e2a\u96c6\u5408\uff01<\/p>
\u4f8b\u5b50\uff1a\u627e\u51fa\u6bcf\u4e2a\u73ed\u91cc\u9762\uff0c\u8eab\u9ad8\u6700\u9ad8\u7684\u5b66\u751f\u4fe1\u606f\uff1b\u90a3\u4e48\u7528\u5b50\u67e5\u8be2\u89e3\u51b3\u4e0d\u4e86\u7684\u7528in\u5c31\u53ef\u4ee5\u89e3\u51b3\u4e86<\/p>
select * from student where height in(select max(height) as height from student group by classid);<\/p>
<\/p>
\u5178\u578b\u7684\u5217\u5b50\u67e5\u8be2\u4f7f\u7528 in\uff0c not in\u4f5c\u4e3a\u5b50\u67e5\u8be2\u7684\u6761\u4ef6\uff01
<\/p>
\u5217\u5b50\u67e5\u8be2\uff0c\u8fd8\u53ef\u4ee5\u4f7f\u7528<\/p>
= some<\/p>
!= all<\/p>
\u6216\u8005\u5176\u4ed6\u7684\u8fd0\u7b97\u7b26\u914d\u5408 some() \u548c all\uff08\uff09\u8bed\u6cd5\u5b8c\u6210\uff01
some(), \u8868\u793a\u96c6\u5408\u4e2d\u7684\u4e00\u90e8\u5206\uff01<\/p>
all(),\u96c6\u4e2d\u7684\u5168\u90e8
\u6d4b\u8bd5\uff1a<\/p>
=some()\u76f8\u5f53\u4e8ein\u4e48\uff1f<\/p>
!=some()\u76f8\u5f53\u4e8e\u4ec0\u4e48\uff1f\u4e0d\u76f8\u5f53\u4e8enot in\uff01<\/p>
\u54ea\u4e2a\u76f8\u5f53\u4e8enot in \uff0c\u4e0e\u96c6\u5408\u5185\u7684\u4efb\u4f55\u4e00\u4e2a\u503c\u90fd\u4e0d\u76f8\u7b49\uff01<\/p>
!=all() \u662fnot in!
any\u5c31\u662fsome\uff0c\u4e00\u4e2a\u529f\u80fd\uff01<\/p>
<\/p>
\u884c\u5b50\u67e5\u8be2<\/p>
\u573a\u666f\uff1a\u627e\u5230\u6700\u9ad8\u5e76\u4e14\u6700\u6709\u94b1\uff01
\u4f7f\u7528\u884c\u5b50\u67e5\u8be2\u53ef\u4ee5\uff0c\u4e00\u6b21\u6027\u67e5\u51fa\u6765\u4e00\u4e2a\u884c\uff08\u591a\u4e2a\u884c\uff09\u4f7f\u7528\u884c\u8fdb\u884c\u5339\u914d\uff1a<\/p>
\u4e0a\u9762\u4f7f\u7528\u4e86 ()\uff0c\u6784\u5efa\u4e86\u4e00\u884c\uff01\u4e0e\u5b50\u67e5\u8be2\u7684\u884c\u4f5c\u6bd4\u8f83\uff01<\/p>
\u4f8b\u5b50\uff1a\u627e\u51fa\u6700\u9ad8\u4e5f\u6700\u6709\u94b1\u7684\u5b66\u751f<\/p>
select * from student where (height,money)=(select max(height),max(money) from student);
<\/p>
<\/p>
(not) exists\u578b\u5b50\u67e5\u8be2<\/p>
\u5224\u65ad\u4f9d\u636e\u4e0d\u662f\u6839\u636e\u5b50\u67e5\u8be2\u6240\u8fd4\u56de\u7684\u6570\u636e\uff01\u53ea\u662f\u6839\u636e\u5b50\u67e5\u8be2\u662f\u5426\u5b58\u5728\u8fd4\u56de\u6570\u636e\u6765\u770b\uff1b
\u5982\u679c\u5b50\u67e5\u8be2\u5b58\u5728\u8fd4\u56de\u6570\u636e\uff0c\u5219exists\u8fd4\u56de\u771f\u3002\u53cd\u4e4b\uff0c\u8fd4\u56de\u5047\uff01
\u51fa\u73b0\u5728where\u6761\u4ef6\u5185\uff1a<\/p>
\u4f8b\u5b50\uff1a\u73ed\u7ea7\u5df2\u7ecf\u4e0d\u5b58\u5728\u7684\u5b66\u751f\uff01 (\u5047\u8bbe\u73ed\u7ea7id\u6700\u5927\u4e3a3);<\/p>
select * from student where exists(select * from class where id>3);<\/p>
\u7ed3\u679c\u4e3a\u7a7a\uff1b
<\/p>
<\/p>
\u8fde\u63a5\u67e5\u8be2\uff0cjoin<\/p>
\u8fde\u63a5\uff0c\u591a\u4e2a\u8868\u8bb0\u5f55\u4e4b\u95f4\u7684\u8fde\u63a5\uff01<\/p>
<\/p>
\u573a\u666f\uff1a<\/p>
\u9700\u8981\u5f97\u5230\u4e00\u4e2a\u5b66\u751f\u5217\u8868\uff0c\u8981\u6c42\u662f\uff0c\u5c55\u793a\uff1a<\/p>
\u5b66\u751f\uff0c\u6027\u522b\uff0c\u73ed\u7ea7\u540d\u5b57<\/p>
<\/p>
\u6b64\u65f6\u9700\u8981\u4e0d\u5355\u4ece\u5b66\u751f\u8868\u83b7\u53d6\u6570\u636e\uff0c\u8fd8\u9700\u8981\u4ece\u73ed\u7ea7\u8868\u83b7\u5f97\u6570\u636e\uff01<\/p>
<\/p>
\u8bed\u6cd5\uff1a<\/p>
from \u8868\u540d1 join \u8868\u540d2 on \u8fde\u63a5\u6761\u4ef6<\/p>
\u8fc7\u7a0b\u662f\uff0c\u5148\u6267\u884c from\u5b50\u53e5\uff0c\u9700\u8981\u8fde\u63a5join\u3002<\/p>
<\/p>
<\/p>
\u4e24\u4e2a\u7279\u6b8a\u7684\u5730\u65b9\uff1a<\/p>
join \uff0c\u8fde\u63a5<\/p>
on \uff0c\u8fde\u63a5\u6761\u4ef6<\/p>
<\/p>
join \u8fde\u63a5\u8bed\u6cd5<\/p>
\u9664\u4e86\u9ed8\u8ba4\u7684\u8fde\u63a5\u4e4b\u5916\uff0c\u6709\u5176\u4ed6\u5f62\u5f0f\u7684\u8fde\u63a5\u65b9\u5f0f<\/p>
<\/p>
\u5185\u8fde\u63a5<\/p>
\u5916\u8fde\u63a5\uff0c\u5de6\u5916\u8fde\u63a5\uff0c\u53f3\u5916\u8fde\u63a5\uff0c[\u5168\u5916\u8fde\u63a5\uff0c\u4e5f\u662f\u5916\u8fde\u63a5\uff0c\u4f46\u662f\u4e0d\u662fmysql\u6240\u652f\u6301\u7684]<\/p>
\u4ea4\u53c9\u8fde\u63a5<\/p>
\u81ea\u7136\u8fde\u63a5<\/p>
<\/p>
<\/p>
\u5185\u8fde\u63a5\uff0cinner join<\/p>
\u8bb0\u5f55\u4e0e\u771f\u5b9e\u7684\u8bb0\u5f55\u8fde\u63a5\uff0c\u79f0\u4e4b\u4e3a\u5185\u8fde\u63a5\uff01\uff08\u4e24\u6761\u771f\u5b9e\u5b58\u5728\u7684\u8bb0\u5f55\u7684\u8fde\u63a5\uff09<\/p>
mysql\u9ed8\u8ba4\u7684\u8fde\u63a5\u5c31\u662f inner join<\/p>
<\/p>
\u5185\u8fde\u63a5\uff0c\u53ef\u4ee5\u8fde\u63a5\u7701\u7565\u6761\u4ef6\uff01<\/p>
on\u53ef\u4ee5\u7701\u7565\uff1a\u76f8\u5f53\u4e8e\u8fde\u63a5\u6761\u4ef6\u6c38\u8fdc\u6210\u7acb\uff01<\/p>
\u8fd4\u56de\u503c\u662f\u4e00\u4e2a\u7b1b\u5361\u5c14\u79ef\uff01<\/p>
\u5916\u8fde\u63a5\uff0cleft join\uff0cright join<\/p>
\u5206\u6210\uff1a\u5de6\u5916\u8fde\u63a5left join\uff0c\u53f3\u5916\u8fde\u63a5right join\uff01<\/p>
<\/p>
\u8fde\u63a5\u7684\u8bb0\u5f55\uff0c\u53ef\u80fd\u662f\u4e00\u65b9\u4e0d\u5b58\u5728\u7684\uff01\uff08\u4e24\u6761\u8bb0\u5f55\u4e2d\uff0c\u53ef\u80fd\u67d0\u6761\u4e0d\u5b58\u5728\uff09<\/p>
<\/p>
\u603b\u7ed3\uff1a\u5185\u8fde\u63a5\uff0c\u5916\u8fde\u63a5\u5dee\u522b\u4e0d\u5927\uff0c\u53ea\u662f\u5916\u8fde\u63a5\u4f1a\u5c06\u6ca1\u6709\u8fde\u63a5\u6210\u529f\u7684\u8bb0\u5f55\uff0c\u4e5f\u51fa\u73b0\u6700\u7ec8\u7684\u8fde\u63a5\u7684\u7ed3\u679c\u5185\uff0c\u800c\u5185\u8fde\u63a5\uff0c\u8fde\u63a5\u7684\u7ed3\u679c\u53ea\u6709\u8fde\u63a5\u6210\u529f\u7684\uff08\u4e24\u6761\u8bb0\u5f55\u90fd\u5b58\u5728\u7684\uff09<\/p>
<\/p>
<\/p>
\u6ce8\u610f\u597d\u5de6\u5916\u4e0e\u53f3\u5916\u7684\u533a\u522b\uff1a<\/p>
\u533a\u522b\u5728\u4e8e\uff0c\u90a3\u4e2a\u8868\u7684\u8bb0\u5f55\uff08\u6307\u7684\u662f\u8fde\u63a5\u5931\u8d25\u7684\u8bb0\u5f55\uff09\uff0c\u4f1a\u6700\u7ec8\u51fa\u73b0\u5728\u8fde\u63a5\u7ed3\u679c\u5185\uff1f<\/p>
<\/p>
\u4ec0\u4e48\u662f\u5de6\u8868\u548c\u53f3\u8868\uff1f<\/p>
join\u5173\u952e\u5b57\u524d\u9762\u7684\uff08\u5de6\u8fb9\u7684\uff09\u5de6\u8868\uff0cjoin\u5173\u952e\u5b57\u540e\u8fb9\u7684\uff08\u53f3\u8fb9\u7684\uff09\u53f3\u8868\uff01<\/p>
<\/p>
\u5de6\u5916\uff1a\u5982\u679c\u51fa\u73b0\u5de6\u8868\u8bb0\u5f55\u8fde\u63a5\u4e0d\u4e0a\u53f3\u8868\u8bb0\u5f55\u7684\uff0c\u5de6\u8868\u8bb0\u5f55\u4f1a\u51fa\u73b0\u6b63\u5728\u6700\u7ec8\u7684\u8fde\u63a5\u7ed3\u679c\u5185\uff01\u800c\u53f3\u8868\u8bb0\u5f55\u76f8\u5e94\u8bbe\u7f6e\u6210NULL\u3002<\/p>
\u53f3\u5916\uff1a\u5982\u679c\u51fa\u73b0\u53f3\u8868\u8bb0\u5f55\u8fde\u63a5\u4e0d\u4e0a\u5de6\u8868\u8bb0\u5f55\u7684\uff0c\u53f3\u8868\u8bb0\u5f55\u4f1a\u51fa\u73b0\u6b63\u5728\u6700\u7ec8\u7684\u8fde\u63a5\u7ed3\u679c\u5185\uff01\u800c\u5de6\u8868\u8bb0\u5f55\u76f8\u5e94\u8bbe\u7f6e\u6210NULL\u3002<\/p>
\u56e0\u6b64\uff0c\u53ef\u4ee5\u4ea4\u6362\u8868\u7684\u4f4d\u7f6e\uff0c\u8fbe\u5230\u4f7f\u7528left\u4e0eright join \u6df7\u7528\u7684\u7684\u76ee\u7684\uff01<\/p>
<\/p>
<\/p>
<\/p>
\u4ea4\u53c9\u8fde\u63a5\uff0ccross join<\/p>
<\/p>
\u7ed3\u679c\u4e0e\u5185\u8fde\u63a5\u4e00\u81f4\uff01<\/p>
<\/p>
\u6709\u65f6\uff0c\u5728\u83b7\u5f97\u7b1b\u5361\u5c14\u79ef\u65f6\uff0c\u663e\u5f0f\u7684\u4f7f\u7528\u4ea4\u53c9\u8fde\u63a5\uff01<\/p>
<\/p>
\u4ea4\u53c9\u8fde\u63a5\u76f8\u5f53\u4e8e\u662f\u6ca1\u6709\u6761\u4ef6\u7684\u5185\u8fde\u63a5\uff01<\/p>
<\/p>
<\/p>
\u81ea\u7136\u8fde\u63a5\uff0cnatural join<\/p>
<\/p>
mysql\uff0c\u81ea\u52a8\u5224\u65ad\u8fde\u63a5\u6761\u4ef6\uff0c\u5e2e\u52a9\u6211\u4eec\u5b8c\u6210\u8fde\u63a5\uff01<\/p>
\u5178\u578b\u7684\u6761\u4ef6\u5c31\u662f\uff0c\u8868\u4e2d\u7684\u540c\u540d\u5b57\u6bb5\uff01<\/p>
<\/p>
\u800c\u81ea\u7136\u8fde\u63a5\u4e5f\u5206\u5185\u8fde\u63a5\u4e0e\u5916\u8fde\u63a5\uff01<\/p>
\u81ea\u7136\u5185\u8fde\u63a5\uff1anatural join<\/p>
\u81ea\u7136\u5de6\u5916\uff1anatural left join<\/p>
\u81ea\u7136\u53f3\u5916\uff1anatual right join<\/p>
<\/p>
<\/p>
\u603b\u7ed3\uff1a<\/p>
\u6700\u7ec8\u7684\u6548\u679c\u53ea\u6709\uff1a\u5185\uff0c\u5de6\u5916\uff0c\u53f3\u5916\uff01<\/p>
\u4ea4\u53c9\uff0c\u7279\u6b8a\u7684\u5185!<\/p>
\u81ea\u7136\uff0c\u76f8\u5f53\u4e8e\u81ea\u52a8\u5224\u65ad\u8fde\u63a5\u6761\u4ef6\uff0c\u5b8c\u6210\u5185\uff0c\u5de6\u5916\uff0c\u53f3\u5916\uff01<\/p>
<\/p>
\u8fde\u63a5\u6761\u4ef6\uff0con\uff0cusing<\/p>
on\uff0c\u540e\u9762\u4f7f\u7528\u4e00\u4e2a\u8fde\u63a5\u6761\u4ef6\u8868\u8fbe\u5f0f\uff01<\/p>
using(\u8fde\u63a5\u5b57\u6bb5)\uff0c\u8981\u6c42\u4f7f\u7528\u540c\u540d\u5b57\u6bb5\u8fdb\u884c\u8fde\u63a5\uff01<\/p>
<\/p>
using \u7684\u7279\u522b\u5730\u65b9\uff1a<\/p>
\u4f1a\u5bf9\u5b57\u6bb5\u5217\u8868\u505a\u4e00\u6b21\u6574\u7406\uff01\u5c06\u8fde\u63a5\u5b57\u6bb5\u4f5c\u4e3a\u4e00\u6b21\u663e\u793a\uff01<\/p>
<\/p>
<\/p>
union\u67e5\u8be2\uff0c\u8054\u5408\u67e5\u8be2<\/p>
\u5c06\u591a\u4e2a\u67e5\u8be2\u7684\u7ed3\u679c\uff0c\u5e76\u5217\u5230\u4e00\u4e2a\u7ed3\u679c\u96c6\u5408\u5185\uff01<\/p>
<\/p>
\u6b64\u65f6\uff0c\u83b7\u5f97\u6240\u6709\u7537\u751f\uff0c\u6309\u7167\u8eab\u9ad8\u5347\u5e8f\u6392\u5e8f\u3002<\/p>
\u83b7\u5f97\u6240\u6709\u7684\u5973\u751f\uff0c\u6309\u7167\u8eab\u9ad8\u964d\u5e8f\u6392\u5e8f\u3002<\/p>
\u6b64\u65f6\uff1a<\/p>
<\/p>
\u6b64\u65f6\uff0c\u5c06\u4e24\u4e2a\u7ed3\u679c\u8054\u5408\u8d77\u6765\uff1a<\/p>
\u6ce8\u610f\uff0c\u5728union\u662f\uff0c\u5982\u679c\u5b50\u53e5\u4e2d\u51fa\u73b0\u4e86 order by\uff0c\u5219\u9700\u8981\u5b50\u53e5\u51fa\u73b0\u5728\u5c0f\u62ec\u53f7\u5185\uff01<\/p>
\u6b64\u65f6\uff0c\u5b50\u53e5\u7684order by \u4e5f\u4f1a\u5728union\u7684\u65f6\u5019\uff0c\u4f1a\u5ffd\u89c6\u6389\uff01\u9700\u8981\u5b50\u53e5\u914d\u5408limit\u4e00\u8d77\u4f7f\u7528order by<\/p>
<\/p>
union \u7684\u8fde\u63a5\u7684\u4e24\u4e2a\u5b50\u53e5\uff0c\u4e0d\u8981\u6c42\u5b9e\u540c\u8868\uff0c\u53ea\u8981\u6c42\uff0c\u5217\u7684\u6570\u91cf\u76f8\u540c\uff01
union\u4f1a\u5728\u8054\u5408\u65f6\uff1a\u4e3b\u52a8\u53bb\u6389\u76f8\u540c\u7684\u8bb0\u5f55\uff1a\u6b64\u65f6\uff0c\u53ef\u4ee5\u4f7f\u7528 all\u5173\u952e\u5b57\u52a0\u4ee5\u4fee\u6b63\uff1a<\/p>
<\/p>
select\u8bed\u53e5\u7684\u9009\u9879<\/p>
distinct\uff0c\u53d6\u6d88\u76f8\u540c\u7684\u8bb0\u5f55
\u9ed8\u8ba4\u662f all\uff0c\u53ef\u4ee5\u4e0d\u5199\uff0c\u8868\u793a\u6240\u6709\u7684\u8bb0\u5f55\u90fd\u51fa\u73b0\uff01<\/p>"}