{"id":204,"date":"2024-12-17T21:46:23","date_gmt":"2024-12-17T21:46:23","guid":{"rendered":"https:\/\/www.isvmspace.com\/?p=204"},"modified":"2024-12-17T21:46:23","modified_gmt":"2024-12-17T21:46:23","slug":"mysql-%e6%95%b0%e6%8d%ae%e6%93%8d%e4%bd%9c%e8%af%a6%e8%a7%a3-%e6%9f%a5%e8%af%a2","status":"publish","type":"post","link":"https:\/\/www.isvmspace.com\/?p=204","title":{"rendered":"Mysql-\u6570\u636e\u64cd\u4f5c\u8be6\u89e3-\u67e5\u8be2"},"content":{"rendered":"

SELECT\u8bed\u6cd5<\/h1>\n

SELECT\u57fa\u672c\u8bed\u6cd5<\/h2>\n

SELECT \u7684\u8bed\u6cd5\u683c\u5f0f\u4e3a\uff1a<\/p>\n

SELECT {* | <filed1>, ..., <filedN>}\nFROM <table 1>, \u2026 , <tableN>\nWHERE <expression>\nGROUP BY <filed>  HAVING <expression>\nORDER BY <filed>\nLIMIT [<offset>,] <row count>\n<\/code><\/pre>\n
\u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
<table>\uff1a\u6307\u5b9a\u88ab\u64cd\u4f5c\u7684\u8868\u540d\u3002\n<field>\uff1a\u661f\u53f7(*)\u67e5\u8be2\u8868\u7684\u6240\u6709\u5b57\u6bb5\u5217\u8868\uff0c\u8868\u793a\u6240\u8981\u67e5\u8be2\u5b57\u6bb5\u7684\u540d\u79f0\u3002\nWHERE <expression>\uff1a\u662f\u53ef\u9009\u9879\uff0c\u5982\u679c\u9009\u62e9\u8be5\u9879\uff0c\u5c06\u9650\u5b9a\u67e5\u8be2\u6570\u636e\u5fc5\u987b\u6ee1\u8db3\u8be5\u67e5\u8be2\u6761\u4ef6\nGROUP BY < filed>: \u8be5\u5b50\u53e5\u544a\u8bc9 MySQL \u5982\u4f55\u663e\u793a\u67e5\u8be2\u51fa\u6765\u7684\u6570\u636e\uff0c\u5e76\u6309\u7167\u6307\u5b9a\u7684\u5b57\u6bb5\u5206\u7ec4\nORDER BY< filed >: \u8be5\u5b50\u53e5\u544a\u8bc9 MySQL \u6309\u4ec0\u4e48\u6837\u7684\u987a\u5e8f\u663e\u793a\u67e5\u8be2\u51fa\u6765\u7684\u6570\u636e\uff0c\u53ef\u4ee5\u8fdb\u884c\u7684\u6392\u5e8f\u6709\u5347\u5e8f\uff08ASC\uff09\u548c\u964d\u5e8f\uff08DESC\uff09\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u662f\u5347\u5e8f\nLIMIT[<offset>\uff0c]<row count>:\u8be5\u5b50\u53e5\u544a\u8bc9 MySQL \u6bcf\u6b21\u663e\u793a\u67e5\u8be2\u51fa\u6765\u7684\u6570\u636e\u6761\u6570\n<\/code><\/pre>\n
\u8bbe\u7f6e\u522b\u540d<\/h2>\n
\u4e3a\u8868\u8bbe\u7f6e\u522b\u540d\uff1a<\/p>\n
<\u8868\u540d> [AS] <\u522b\u540d>\nselect id, name, age, gender from employee as emp;\n<\/code><\/pre>\n
\u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
<\u8868\u540d>\uff1a\u6570\u636e\u5e93\u4e2d\u5b58\u50a8\u7684\u6570\u636e\u8868\u7684\u540d\u79f0\n<\u522b\u540d>\uff1a\u67e5\u8be2\u65f6\u6307\u5b9a\u7684\u8868\u7684\u65b0\u540d\u79f0\nAS\u5173\u952e\u5b57\u53ef\u4ee5\u7701\u7565\uff0c\u7701\u7565\u540e\u9700\u8981\u5c06\u8868\u540d\u548c\u522b\u540d\u7528\u7a7a\u683c\u9694\u5f00\n<\/code><\/pre>\n
\u4e3a\u5b57\u6bb5\u8bbe\u7f6e\u522b\u540d\uff1a<\/p>\n
<\u5b57\u6bb5\u540d> [AS] <\u522b\u540d>\nselect emp.id, name '\u59d3\u540d', age AS '\u5e74\u9f84', gender '\u6027\u522b' from employee as emp;\n<\/code><\/pre>\n
\u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
<\u5b57\u6bb5\u540d>\uff1a\u4e3a\u6570\u636e\u8868\u4e2d\u5b57\u6bb5\u5b9a\u4e49\u7684\u540d\u79f0\n<\u5b57\u6bb5\u522b\u540d>\uff1a\u5b57\u6bb5\u65b0\u7684\u540d\u79f0\nAS\u5173\u952e\u5b57\u53ef\u4ee5\u7701\u7565\uff0c\u7701\u7565\u540e\u9700\u8981\u5c06\u5b57\u6bb5\u540d\u548c\u522b\u540d\u7528\u7a7a\u683c\u9694\u5f00\n<\/code><\/pre>\n
\u6761\u4ef6\u67e5\u8be2<\/h2>\n
WHERE\u5173\u952e\u5b57\u7684\u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n
WHERE <expression>\n<\/code><\/pre>\n
\u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
<expression>\u53ef\u4ee5\u662f\uff1a\n\u5e26\u6bd4\u8f83\u8fd0\u7b97\u7b26\u548c\u903b\u8f91\u8fd0\u7b97\u7b26\u7684\u67e5\u8be2\u6761\u4ef6\n\u5e26 BETWEEN AND \u5173\u952e\u5b57\u7684\u67e5\u8be2\u6761\u4ef6\n\u5e26 IS NULL \u5173\u952e\u5b57\u7684\u67e5\u8be2\u6761\u4ef6\n\u5e26 IN \u5173\u952e\u5b57\u7684\u67e5\u8be2\u6761\u4ef6\n\u5e26 LIKE \u5173\u952e\u5b57\u7684\u67e5\u8be2\u6761\u4ef6\n...\n<\/code><\/pre>\n
MySql\u652f\u63014\u4e2d\u8fd0\u7b97\u7b26\uff1a<\/strong><\/p>\n
    \n
  • \u7b97\u6570\u8fd0\u7b97\u7b26
    \n\u6267\u884c\u7b97\u6570\u8fd0\u7b97\uff0c\u5982\u52a0\u3001\u51cf\u3001\u4e58\u3001\u9664\u7b49<\/p>\n<\/li>\n
  • \n
    \u6bd4\u8f83\u8fd0\u7b97\u7b26
    \n\u4e3b\u8981\u7528\u4e8e\u6570\u503c\u7684\u6bd4\u8f83\u3001\u5b57\u7b26\u4e32\u5339\u914d\u7b49\uff0c\u5305\u62ec\u5927\u4e8e\u3001\u5c0f\u4e8e\u3001\u7b49\u4e8e\u3001...<\/p>\n<\/li>\n
  • \n
    \u903b\u8f91\u8fd0\u7b97\u7b26
    \n\u903b\u8f91\u5224\u65ad\uff0c\u8fd4\u56de\u503c\u4e3a\u5e03\u5c14\u578b\uff0c\u5305\u62ec\u4e0e\u3001\u6216\u3001\u975e\u7b49<\/p>\n<\/li>\n
  • \n
    \u4f4d\u8fd0\u7b97\u7b26
    \n\u4f4d\u8fd0\u7b97\u7b26\u662f\u5728\u4e8c\u8fdb\u5236\u6570\u4e0a\u8fdb\u884c\u8ba1\u7b97\u7684\u8fd0\u7b97\u7b26\u3002\u4f4d\u8fd0\u7b97\u4f1a\u5148\u5c06\u64cd\u4f5c\u6570\u53d8\u6210\u4e8c\u8fdb\u5236\u6570\uff0c\u8fdb\u884c\u4f4d\u8fd0\u7b97\u3002\u7136\u540e\u518d\u5c06\u8ba1\u7b97\u7ed3\u679c\u4ece\u4e8c\u8fdb\u5236\u6570\u53d8\u56de\u5341\u8fdb\u5236\u6570\u3002\u5305\u62ec\u6309\u4f4d\u4e0e\u3001\u6309\u4f4d\u6216\u3001\u53d6\u53cd\u3001\u5de6\u79fb\u3001\u53f3\u79fb\u7b49<\/p>\n<\/li>\n<\/ul>\n
    \u5206\u7ec4\u67e5\u8be2<\/h2>\n
    GROUP BY\u5173\u952e\u5b57\u7684\u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n
    GROUP BY  <field>\n<\/code><\/pre>\n
    \u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
    <field>\uff1a\u8868\u793a\u9700\u8981\u5206\u7ec4\u7684\u5b57\u6bb5\u540d\u79f0\uff0c\u591a\u4e2a\u5b57\u6bb5\u65f6\u7528\u9017\u53f7\u9694\u5f00\n<\/code><\/pre>\n
    GROUP BY\u5355\u72ec\u4f7f\u7528<\/p>\n
    SELECT age FROM employee GROUP BY age;\n<\/code><\/pre>\n
    GROUP BY\u4e0eGROUP_CONCAT()\u51fd\u6570\u642d\u914d\u4f7f\u7528<\/p>\n
    SELECT age, GROUP_CONCAT(name) FROM employee GROUP BY age;\n<\/code><\/pre>\n
    GROUP BY\u4e0e\u805a\u5408\u51fd\u6570\u642d\u914d\u4f7f\u7528\uff0c\u5982 COUNT()\uff0cSUM()\uff0cAVG()\uff0cMAX() \u548c MIN()\u7b49<\/p>\n
    SELECT age, COUNT(id) FROM employee GROUP BY age;\n<\/code><\/pre>\n
    WITH ROLLUP\uff1a\u5728group\u5206\u7ec4\u5b57\u6bb5\u7684\u57fa\u7840\u4e0a\u518d\u8fdb\u884c\u7edf\u8ba1\u6570\u636e<\/p>\n
    SELECT age, count(id) FROM employee GROUP BY age WITH ROLLUP;\n<\/code><\/pre>\n
    \u8fc7\u6ee4\u5206\u7ec4<\/strong><\/p>\n
    HAVING \u5173\u952e\u5b57\u7684\u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n
    HAVING <\u67e5\u8be2\u6761\u4ef6>\n\nHAVING \u5173\u952e\u5b57\u548c WHERE \u5173\u952e\u5b57\u90fd\u53ef\u4ee5\u7528\u6765\u8fc7\u6ee4\u6570\u636e\uff0c\u4e14 HAVING \u652f\u6301 WHERE \u5173\u952e\u5b57\u4e2d\u6240\u6709\u7684\u64cd\u4f5c\u7b26\u548c\u8bed\u6cd5\u3002\n WHERE \u548c HAVING \u5173\u952e\u5b57\u4e5f\u5b58\u5728\u4ee5\u4e0b\u51e0\u70b9\u5dee\u5f02\uff1a\n\u4e00\u822c\u60c5\u51b5\u4e0b\uff0cWHERE \u7528\u4e8e\u8fc7\u6ee4\u6570\u636e\u884c\uff0c\u800c HAVING \u7528\u4e8e\u8fc7\u6ee4\u5206\u7ec4\nWHERE \u67e5\u8be2\u6761\u4ef6\u4e2d\u4e0d\u53ef\u4ee5\u4f7f\u7528\u805a\u5408\u51fd\u6570\uff0c\u800c HAVING \u67e5\u8be2\u6761\u4ef6\u4e2d\u53ef\u4ee5\u4f7f\u7528\u805a\u5408\u51fd\u6570\nWHERE \u5728\u6570\u636e\u5206\u7ec4\u524d\u8fdb\u884c\u8fc7\u6ee4\uff0c\u800c HAVING \u5728\u6570\u636e\u5206\u7ec4\u540e\u8fdb\u884c\u8fc7\u6ee4\nWHERE \u6839\u636e\u6570\u636e\u8868\u4e2d\u7684\u5b57\u6bb5\u8fdb\u884c\u8fc7\u6ee4\uff0c\u800c HAVING \u662f\u6839\u636e\u524d\u9762\u5df2\u7ecf\u67e5\u8be2\u51fa\u7684\u5b57\u6bb5\u8fdb\u884c\u8fc7\u6ee4\nWHERE \u67e5\u8be2\u6761\u4ef6\u4e2d\u4e0d\u53ef\u4ee5\u4f7f\u7528\u5b57\u6bb5\u522b\u540d\uff0c\u800c HAVING \u67e5\u8be2\u6761\u4ef6\u4e2d\u53ef\u4ee5\u4f7f\u7528\u5b57\u6bb5\u522b\u540d\n\n<\/code><\/pre>\n
    mysql> select * from employee having age > 30;\n+----+-------------+------+--------+-----+--------+----------+---------+\n| id | mobile      | name | gender | age | dept   | post     | salary  |\n+----+-------------+------+--------+-----+--------+----------+---------+\n|  3 | 13101010002 | \u674e\u56db | \u5973     |  45 | NULL   | \u9500\u552e\u987e\u95ee | 0       |\n|  4 | 13101010003 | \u738b\u4e8c | \u5973     |  50 | \u9500\u552e\u90e8 | \u9500\u552e\u603b\u76d1 | 8888.88 |\n|  5 | 14101010004 | \u9ebb\u5b50 | \u7537     |  36 | NULL   | \u603b\u7ecf\u7406   | NULL    |\n|  6 | 14101010005 | TOM  | \u672a\u77e5   |  36 | \u9500\u552e\u90e8 | \u9500\u552e\u987e\u95ee | 6055.22 |\n|  7 | 14101010006 | JACK | \u7537     |  50 | NULL   | NULL     | 2222.01 |\n+----+-------------+------+--------+-----+--------+----------+---------+\n5 rows in set\n\nmysql> select id, mobile,name\n from employee having age > 30;\n1054 - Unknown column 'age' in 'having clause'\n\n<\/code><\/pre>\n
    \u5bf9\u67e5\u8be2\u7ed3\u679c\u6392\u5e8f<\/h2>\n
    ORDER BY\u5173\u952e\u5b57\u7684\u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n
    ORDER BY <field> [ASC|DESC]\n<\/code><\/pre>\n
    \u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
    <field>\uff1a\u8868\u793a\u9700\u8981\u6392\u5e8f\u7684\u5b57\u6bb5\u540d\u79f0\uff0c\u591a\u4e2a\u5b57\u6bb5\u65f6\u7528\u9017\u53f7\u9694\u5f00\nASC|DESC\uff1aASC\u8868\u793a\u5b57\u6bb5\u6309\u5347\u5e8f\u6392\u5e8f\uff1bDESC\u8868\u793a\u5b57\u6bb5\u6309\u964d\u5e8f\u6392\u5e8f\u3002\u5176\u4e2dASC\u4e3a\u9ed8\u8ba4\u503c\n\n\u6ce8\u610f\uff1a\nORDER BY \u5173\u952e\u5b57\u540e\u53ef\u4ee5\u8ddf\u5b50\u67e5\u8be2\n\u5f53\u6392\u5e8f\u7684\u5b57\u6bb5\u4e2d\u5b58\u5728\u7a7a\u503c\u65f6\uff0cORDER BY \u4f1a\u5c06\u8be5\u7a7a\u503c\u4f5c\u4e3a\u6700\u5c0f\u503c\u6765\u5bf9\u5f85\nORDER BY \u6307\u5b9a\u591a\u4e2a\u5b57\u6bb5\u8fdb\u884c\u6392\u5e8f\u65f6\uff0cMySQL \u4f1a\u6309\u7167\u5b57\u6bb5\u7684\u987a\u5e8f\u4ece\u5de6\u5230\u53f3\u4f9d\u6b21\u8fdb\u884c\u6392\u5e8f\n<\/code><\/pre>\n
    \u5355\u5b57\u6bb5\u6392\u5e8f<\/p>\n
    select * from employee order by age desc;\n<\/code><\/pre>\n
    \u591a\u5b57\u6bb5\u6392\u5e8f<\/p>\n
    select * from employee order by age , mobile desc;\n<\/code><\/pre>\n
    \u6ce8\u610f\uff1a\u5728\u5bf9\u591a\u4e2a\u5b57\u6bb5\u8fdb\u884c\u6392\u5e8f\u65f6\uff0c\u6392\u5e8f\u7684\u7b2c\u4e00\u4e2a\u5b57\u6bb5\u5fc5\u987b\u6709\u76f8\u540c\u7684\u503c\uff0c\u624d\u4f1a\u5bf9\u7b2c\u4e8c\u4e2a\u5b57\u6bb5\u8fdb\u884c\u6392\u5e8f<\/p>\n
    mysql> select * from employee order by age desc;\n+----+-------------+------+--------+-----+--------+----------+---------+\n| id | mobile      | name | gender | age | dept   | post     | salary  |\n+----+-------------+------+--------+-----+--------+----------+---------+\n|  4 | 13101010003 | \u738b\u4e8c | \u5973     |  50 | \u9500\u552e\u90e8 | \u9500\u552e\u603b\u76d1 | 8888.88 |\n|  7 | 14101010006 | JACK | \u7537     |  50 | NULL   | NULL     | 2222.01 |\n|  3 | 13101010002 | \u674e\u56db | \u5973     |  45 | NULL   | \u9500\u552e\u987e\u95ee | 0       |\n|  5 | 14101010004 | \u9ebb\u5b50 | \u7537     |  36 | NULL   | \u603b\u7ecf\u7406   | NULL    |\n|  6 | 14101010005 | TOM  | \u672a\u77e5   |  36 | \u9500\u552e\u90e8 | \u9500\u552e\u987e\u95ee | 6055.22 |\n|  1 | 13101010001 | \u5f20\u4e09 | \u7537     |  24 | \u5e02\u573a\u90e8 | \u5e02\u573a\u603b\u76d1 | 8888.88 |\n+----+-------------+------+--------+-----+--------+----------+---------+\n6 rows in set\nmysql> select * from employee order by age , mobile desc;\n+----+-------------+------+--------+-----+--------+----------+---------+\n| id | mobile      | name | gender | age | dept   | post     | salary  |\n+----+-------------+------+--------+-----+--------+----------+---------+\n|  1 | 13101010001 | \u5f20\u4e09 | \u7537     |  24 | \u5e02\u573a\u90e8 | \u5e02\u573a\u603b\u76d1 | 8888.88 |\n|  6 | 14101010005 | TOM  | \u672a\u77e5   |  36 | \u9500\u552e\u90e8 | \u9500\u552e\u987e\u95ee | 6055.22 |\n|  5 | 14101010004 | \u9ebb\u5b50 | \u7537     |  36 | NULL   | \u603b\u7ecf\u7406   | NULL    |\n|  3 | 13101010002 | \u674e\u56db | \u5973     |  45 | NULL   | \u9500\u552e\u987e\u95ee | 0       |\n|  7 | 14101010006 | JACK | \u7537     |  50 | NULL   | NULL     | 2222.01 |\n|  4 | 13101010003 | \u738b\u4e8c | \u5973     |  50 | \u9500\u552e\u90e8 | \u9500\u552e\u603b\u76d1 | 8888.88 |\n+----+-------------+------+--------+-----+--------+----------+---------+\n6 rows in set\n\nmysql> select * from employee order by mobile desc, age desc ;\n+----+-------------+------+--------+-----+--------+----------+---------+\n| id | mobile      | name | gender | age | dept   | post     | salary  |\n+----+-------------+------+--------+-----+--------+----------+---------+\n|  7 | 14101010006 | JACK | \u7537     |  50 | NULL   | NULL     | 2222.01 |\n|  6 | 14101010005 | TOM  | \u672a\u77e5   |  36 | \u9500\u552e\u90e8 | \u9500\u552e\u987e\u95ee | 6055.22 |\n|  5 | 14101010004 | \u9ebb\u5b50 | \u7537     |  36 | NULL   | \u603b\u7ecf\u7406   | NULL    |\n|  4 | 13101010003 | \u738b\u4e8c | \u5973     |  50 | \u9500\u552e\u90e8 | \u9500\u552e\u603b\u76d1 | 8888.88 |\n|  3 | 13101010002 | \u674e\u56db | \u5973     |  45 | NULL   | \u9500\u552e\u987e\u95ee | 0       |\n|  1 | 13101010001 | \u5f20\u4e09 | \u7537     |  24 | \u5e02\u573a\u90e8 | \u5e02\u573a\u603b\u76d1 | 8888.88 |\n+----+-------------+------+--------+-----+--------+----------+---------+\n6 rows in set\n\n<\/code><\/pre>\n
    \u9650\u5236\u67e5\u8be2\u7ed3\u679c\u7684\u6761\u6570<\/h2>\n
    LIMIT\u5173\u952e\u5b57\u7684\u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n
    LIMIT [<offset>,] <row count>\n<\/code><\/pre>\n
    \u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
    <offset>\uff1a\u8868\u793a\u4ece\u54ea\u884c\u8bb0\u5f55\u5f00\u59cb\u663e\u793a\uff0c\u53ef\u7701\u7565\uff0c\u9ed8\u8ba4\u4e3a0\n<row count>\uff1a \u8868\u793a\u663e\u793a\u8bb0\u5f55\u7684\u6761\u6570\n\u6ce8\u610f\uff1aLIMIT \u540e\u7684\u4e24\u4e2a\u53c2\u6570\u5fc5\u987b\u90fd\u662f\u6b63\u6574\u6570\n<\/code><\/pre>\n
    \u6307\u5b9a\u521d\u59cb\u4f4d\u7f6e<\/p>\n
    select * from employee limit 2,4;\n\nselect * from employee limit 4 offset 2;\n<\/code><\/pre>\n
    \u4e0d\u6307\u5b9a\u521d\u59cb\u4f4d\u7f6e<\/p>\n
    select * from employee limit 3;\n<\/code><\/pre>\n
    mysql> select * from employee limit 3;\n+----+-------------+------+--------+-----+--------+----------+---------+\n| id | mobile      | name | gender | age | dept   | post     | salary  |\n+----+-------------+------+--------+-----+--------+----------+---------+\n|  1 | 13101010001 | \u5f20\u4e09 | \u7537     |  24 | \u5e02\u573a\u90e8 | \u5e02\u573a\u603b\u76d1 | 8888.88 |\n|  3 | 13101010002 | \u674e\u56db | \u5973     |  45 | NULL   | \u9500\u552e\u987e\u95ee | 0       |\n|  4 | 13101010003 | \u738b\u4e8c | \u5973     |  50 | \u9500\u552e\u90e8 | \u9500\u552e\u603b\u76d1 | 8888.88 |\n+----+-------------+------+--------+-----+--------+----------+---------+\n3 rows in set\n\nmysql> select * from employee limit 2,4;\n+----+-------------+------+--------+-----+--------+----------+---------+\n| id | mobile      | name | gender | age | dept   | post     | salary  |\n+----+-------------+------+--------+-----+--------+----------+---------+\n|  4 | 13101010003 | \u738b\u4e8c | \u5973     |  50 | \u9500\u552e\u90e8 | \u9500\u552e\u603b\u76d1 | 8888.88 |\n|  5 | 14101010004 | \u9ebb\u5b50 | \u7537     |  36 | NULL   | \u603b\u7ecf\u7406   | NULL    |\n|  6 | 14101010005 | TOM  | \u672a\u77e5   |  36 | \u9500\u552e\u90e8 | \u9500\u552e\u987e\u95ee | 6055.22 |\n|  7 | 14101010006 | JACK | \u7537     |  50 | NULL   | NULL     | 2222.01 |\n+----+-------------+------+--------+-----+--------+----------+---------+\n4 rows in set\n\nmysql> select * from employee limit 4 offset 2;\n+----+-------------+------+--------+-----+--------+----------+---------+\n| id | mobile      | name | gender | age | dept   | post     | salary  |\n+----+-------------+------+--------+-----+--------+----------+---------+\n|  4 | 13101010003 | \u738b\u4e8c | \u5973     |  50 | \u9500\u552e\u90e8 | \u9500\u552e\u603b\u76d1 | 8888.88 |\n|  5 | 14101010004 | \u9ebb\u5b50 | \u7537     |  36 | NULL   | \u603b\u7ecf\u7406   | NULL    |\n|  6 | 14101010005 | TOM  | \u672a\u77e5   |  36 | \u9500\u552e\u90e8 | \u9500\u552e\u987e\u95ee | 6055.22 |\n|  7 | 14101010006 | JACK | \u7537     |  50 | NULL   | NULL     | 2222.01 |\n+----+-------------+------+--------+-----+--------+----------+---------+\n4 rows in set\n\n<\/code><\/pre>\n
    \u5bf9\u67e5\u8be2\u7ed3\u679c\u53bb\u91cd<\/h2>\n
    DISTINCT\u5173\u952e\u5b57\u7684\u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n
    SELECT DISTINCT <field> FROM <table>;\n<\/code><\/pre>\n
    \u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
    <field>\uff1a\u4e3a\u9700\u8981\u6d88\u9664\u91cd\u590d\u8bb0\u5f55\u7684\u5b57\u6bb5\u540d\u79f0\uff0c\u591a\u4e2a\u5b57\u6bb5\u65f6\u7528\u9017\u53f7\u9694\u5f00\n\n\u6ce8\u610f\uff1a\nDISTINCT \u5173\u952e\u5b57\u53ea\u80fd\u5728 SELECT \u8bed\u53e5\u4e2d\u4f7f\u7528\n\u5728\u5bf9\u4e00\u4e2a\u6216\u591a\u4e2a\u5b57\u6bb5\u53bb\u91cd\u65f6\uff0cDISTINCT \u5173\u952e\u5b57\u5fc5\u987b\u5728\u6240\u6709\u5b57\u6bb5\u7684\u6700\u524d\u9762\n\u5982\u679c DISTINCT \u5173\u952e\u5b57\u540e\u6709\u591a\u4e2a\u5b57\u6bb5\uff0c\u5219\u4f1a\u5bf9\u591a\u4e2a\u5b57\u6bb5\u8fdb\u884c\u7ec4\u5408\u53bb\u91cd\n\n<\/code><\/pre>\n
    \u5b50\u67e5\u8be2<\/h2>\n
    \u5b50\u67e5\u8be2\u5728 WHERE \u4e2d\u7684\u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n
    WHERE <\u8868\u8fbe\u5f0f> <\u64cd\u4f5c\u7b26> (\u5b50\u67e5\u8be2)\n<\/code><\/pre>\n
    \u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
    <\u64cd\u4f5c\u7b26>: \u53ef\u4ee5\u662f\u6bd4\u8f83\u8fd0\u7b97\u7b26\u548c IN\u3001NOT IN\u3001EXISTS\u3001NOT EXISTS \u7b49\u5173\u952e\u5b57\n<\/code><\/pre>\n
    IN | NOT IN<\/strong><\/p>\n
    IN: \u5f53\u8868\u8fbe\u5f0f\u4e0e\u5b50\u67e5\u8be2\u8fd4\u56de\u7684\u7ed3\u679c\u96c6\u4e2d\u7684\u67d0\u4e2a\u503c\u76f8\u7b49\u65f6\uff0c\u8fd4\u56de TRUE\uff0c\u5426\u5219\u8fd4\u56de FALSE<\/p>\n
    SELECT * from emp e where e.dept_id IN (SELECT id from dept where `name` = '\u5e02\u573a\u90e8');\n\nSELECT * from emp e where e.dept_id NOT IN (SELECT id from dept where `name` = '\u5e02\u573a\u90e8');\n<\/code><\/pre>\n
    EXISTS | NOT EXISTS<\/strong><\/p>\n
    EXISTS: \u7528\u4e8e\u5224\u65ad\u5b50\u67e5\u8be2\u7684\u7ed3\u679c\u96c6\u662f\u5426\u4e3a\u7a7a\uff0c\u82e5\u5b50\u67e5\u8be2\u7684\u7ed3\u679c\u96c6\u4e0d\u4e3a\u7a7a\uff0c\u8fd4\u56de TRUE\uff0c\u5426\u5219\u8fd4\u56de FALSE<\/p>\n
    SELECT * from emp e where EXISTS (SELECT id from dept where `name` = '\u5e02\u573a\u90e8' and dept.id = e.dept_id);\n\nSELECT * from emp e where NOT EXISTS (SELECT id from dept where `name` = '\u5e02\u573a\u90e8' and dept.id = e.dept_id);\n\nSELECT (\u5b50\u67e5\u8be2) FROM \u8868\u540d\nSELECT id, `name` , dept_id, (SELECT name from dept where dept.id = emp.dept_id) 'dept_name' from emp;\n\nSELECT * FROM (\u5b50\u67e5\u8be2) AS \u8868\u7684\u522b\u540d\nselect emp_id, emp_name from (SELECT e.id emp_id, e.`name` emp_name, e.age from emp e) tmp;\n<\/code><\/pre>\n
    \u4ea4\u53c9\u8fde\u63a5\uff08CROSS JOIN\uff09<\/h2>\n
    \u4ea4\u53c9\u8fde\u63a5\u7684\u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n
    SELECT <field> FROM <table1> CROSS JOIN <table2> [WHERE <expression>]\n\u6216 SELECT <field> FROM <table1>, <table2> [WHERE <expression>]\n<\/code><\/pre>\n
    \u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
    <field>: \u9700\u8981\u67e5\u8be2\u7684\u5b57\u6bb5\u540d\u79f0,\u591a\u4e2a\u5b57\u6bb5\u65f6\u7528\u9017\u53f7\u9694\u5f00\n<table1>, <table2>\uff1a\u9700\u8981\u4ea4\u53c9\u8fde\u63a5\u7684\u8868\u540d\nWHERE <expression>\uff1a\u7528\u6765\u8bbe\u7f6e\u4ea4\u53c9\u8fde\u63a5\u7684\u67e5\u8be2\u6761\u4ef6\n\n<\/code><\/pre>\n
    SELECT * FROM emp CROSS JOIN dept WHERE emp.dept_id = dept.id;\n\nSELECT * FROM emp, dept WHERE emp.dept_id = dept.id;\n\n<\/code><\/pre>\n
    \u5185\u8fde\u63a5\uff08INNER JOIN\uff09<\/h2>\n
    \u5185\u8fde\u63a5\u7684\u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n
    SELECT <field> FROM <table1> INNER JOIN <table2> [ON\u5b50\u53e5]\n<\/code><\/pre>\n
    \u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
    <field>: \u9700\u8981\u67e5\u8be2\u7684\u5b57\u6bb5\u540d\u79f0,\u591a\u4e2a\u5b57\u6bb5\u65f6\u7528\u9017\u53f7\u9694\u5f00\n<table1>, <table2>\uff1a\u9700\u8981\u5185\u8fde\u63a5\u7684\u8868\u540d\nINNER JOIN \uff1a\u5185\u8fde\u63a5\u4e2d\u53ef\u4ee5\u7701\u7565 INNER \u5173\u952e\u5b57\uff0c\u53ea\u7528\u5173\u952e\u5b57 JOIN\nON \u5b50\u53e5\uff1a\u7528\u6765\u8bbe\u7f6e\u5185\u8fde\u63a5\u7684\u8fde\u63a5\u6761\u4ef6\nWHERE <expression>\uff1a\u7528\u6765\u8bbe\u7f6e\u4ea4\u53c9\u8fde\u63a5\u7684\u67e5\u8be2\u6761\u4ef6\n\n<\/code><\/pre>\n
    SELECT emp.id, emp.`name`, emp.gender, dept.`name` dept_name, role.`name` role_name from emp\nJOIN dept on emp.dept_id = dept.id\nJOIN emp_role_ref erf on erf.emp_id = emp.id\nJOIN role on role.id = erf.role_id;\n<\/code><\/pre>\n
    \u5916\u8fde\u63a5\uff08OUTER JOIN\uff09<\/h2>\n
    \u5de6\u8fde\u63a5\u7684\u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n
    SELECT <field> FROM <table1> LEFT OUTER JOIN <table2> [ON\u5b50\u53e5]\n<\/code><\/pre>\n
    \u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
    LEFT OUTER JOIN \uff1a\u5de6\u8fde\u63a5\u4e2d\u53ef\u4ee5\u7701\u7565 OUTER \u5173\u952e\u5b57\uff0c\u53ea\u4f7f\u7528\u5173\u952e\u5b57 LEFT JOIN\n<table1><table2>\uff1a\u9700\u8981\u5de6\u8fde\u63a5\u7684\u8868\u540d\uff0c\u201dtable1\u201d\u4e3a\u57fa\u8868\uff0c\u201ctable2\u201d\u4e3a\u53c2\u8003\u8868\u3002\u5de6\u8fde\u63a5\u67e5\u8be2\u65f6\uff0c\u53ef\u4ee5\u67e5\u8be2\u51fa\u201ctable1\u201d\u4e2d\u7684\u6240\u6709\u8bb0\u5f55\u548c\u201ctable2\u201d\u4e2d\u5339\u914d\u8fde\u63a5\u6761\u4ef6\u7684\u8bb0\u5f55\u3002\u5982\u679c\u201ctable1\u201d\u7684\u67d0\u884c\u5728\u201ctable2\u201d\u4e2d\u6ca1\u6709\u5339\u914d\u884c\uff0c\u90a3\u4e48\u5728\u8fd4\u56de\u7ed3\u679c\u4e2d\uff0c\u201ctable2\u201d\u7684\u5b57\u6bb5\u503c\u5747\u4e3a\u7a7a\u503c\uff08NULL\uff09\n<\/code><\/pre>\n
    \u53f3\u8fde\u63a5\u7684\u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n
    SELECT <field> FROM <table1> RIGHT OUTER JOIN <table2> [ON\u5b50\u53e5]\n<\/code><\/pre>\n
    \u8bed\u6cd5\u8bf4\u660e\uff1a<\/p>\n
    RIGHT OUTER JOIN \uff1a\u53f3\u8fde\u63a5\u4e2d\u53ef\u4ee5\u7701\u7565 OUTER \u5173\u952e\u5b57\uff0c\u53ea\u4f7f\u7528\u5173\u952e\u5b57RIGHT JOIN\n<table1><table2>\uff1a\u9700\u8981\u5de6\u8fde\u63a5\u7684\u8868\u540d\uff0c\u201dtable2\u201d\u4e3a\u57fa\u8868\uff0c\u201ctable1\u201d\u4e3a\u53c2\u8003\u8868\u3002\u5de6\u8fde\u63a5\u67e5\u8be2\u65f6\uff0c\u53ef\u4ee5\u67e5\u8be2\u51fa\u201ctable2\u201d\u4e2d\u7684\u6240\u6709\u8bb0\u5f55\u548c\u201ctable1\u201d\u4e2d\u5339\u914d\u8fde\u63a5\u6761\u4ef6\u7684\u8bb0\u5f55\u3002\u5982\u679c\u201ctable2\u201d\u7684\u67d0\u884c\u5728\u201ctable1\u201d\u4e2d\u6ca1\u6709\u5339\u914d\u884c\uff0c\u90a3\u4e48\u5728\u8fd4\u56de\u7ed3\u679c\u4e2d\uff0c\u201ctable1\u201d\u7684\u5b57\u6bb5\u503c\u5747\u4e3a\u7a7a\u503c\uff08NULL\uff09\n<\/code><\/pre>\n
    mysql> SELECT emp.id, emp.`name`, emp.gender, dept.`name` dept_name from emp\nLEFT JOIN dept on emp.dept_id = dept.id\nORDER BY emp.id LIMIT 10;\n+----+--------+--------+-----------+\n| id | name   | gender | dept_name |\n+----+--------+--------+-----------+\n|  1 | \u5f20\u4e0901 | \u7537     | NULL      |\n|  2 | \u5f20\u4e0902 | \u5973     | NULL      |\n|  3 | \u5f20\u4e0903 | \u7537     | \u5ba2\u6237\u90e8    |\n|  4 | \u5f20\u4e0904 | \u7537     | \u9500\u552e\u90e8    |\n|  5 | \u5f20\u4e0905 | \u7537     | \u670d\u52a1\u90e8    |\n|  6 | \u5f20\u4e0906 | \u672a\u77e5   | \u5e02\u573a\u90e8    |\n|  7 | \u5f20\u4e0907 | \u5973     | \u670d\u52a1\u90e8    |\n|  8 | \u5f20\u4e0908 | \u7537     | \u5e02\u573a\u90e8    |\n|  9 | \u5f20\u4e0909 | \u7537     | \u5ba2\u6237\u90e8    |\n| 10 | \u5f20\u4e0910 | \u5973     | \u5ba2\u6237\u90e8    |\n+----+--------+--------+-----------+\n10 rows in set\n\nmysql> SELECT emp.id, emp.`name`, emp.gender, dept.`name` dept_name from dept\nRIGHT JOIN emp on emp.dept_id = dept.id\nORDER BY emp.id LIMIT 10;\n+----+--------+--------+-----------+\n| id | name   | gender | dept_name |\n+----+--------+--------+-----------+\n|  1 | \u5f20\u4e0901 | \u7537     | NULL      |\n|  2 | \u5f20\u4e0902 | \u5973     | NULL      |\n|  3 | \u5f20\u4e0903 | \u7537     | \u5ba2\u6237\u90e8    |\n|  4 | \u5f20\u4e0904 | \u7537     | \u9500\u552e\u90e8    |\n|  5 | \u5f20\u4e0905 | \u7537     | \u670d\u52a1\u90e8    |\n|  6 | \u5f20\u4e0906 | \u672a\u77e5   | \u5e02\u573a\u90e8    |\n|  7 | \u5f20\u4e0907 | \u5973     | \u670d\u52a1\u90e8    |\n|  8 | \u5f20\u4e0908 | \u7537     | \u5e02\u573a\u90e8    |\n|  9 | \u5f20\u4e0909 | \u7537     | \u5ba2\u6237\u90e8    |\n| 10 | \u5f20\u4e0910 | \u5973     | \u5ba2\u6237\u90e8    |\n+----+--------+--------+-----------+\n10 rows in set\n\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
    SELECT\u8bed\u6cd5 SELECT\u57fa\u672c\u8bed\u6cd5 SELECT \u7684\u8bed\u6cd5\u683c\u5f0f\u4e3a\uff1a SELECT {* | <file […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"_links":{"self":[{"href":"https:\/\/www.isvmspace.com\/index.php?rest_route=\/wp\/v2\/posts\/204"}],"collection":[{"href":"https:\/\/www.isvmspace.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.isvmspace.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.isvmspace.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.isvmspace.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=204"}],"version-history":[{"count":6,"href":"https:\/\/www.isvmspace.com\/index.php?rest_route=\/wp\/v2\/posts\/204\/revisions"}],"predecessor-version":[{"id":210,"href":"https:\/\/www.isvmspace.com\/index.php?rest_route=\/wp\/v2\/posts\/204\/revisions\/210"}],"wp:attachment":[{"href":"https:\/\/www.isvmspace.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=204"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.isvmspace.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=204"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.isvmspace.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=204"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}