{"id":11415,"date":"2025-06-11T23:50:10","date_gmt":"2025-06-11T15:50:10","guid":{"rendered":"https:\/\/egonlin.com\/?p=11415"},"modified":"2025-06-12T16:26:12","modified_gmt":"2025-06-12T08:26:12","slug":"%e5%8d%95%e8%a1%a8%e6%9f%a5%e8%af%a2","status":"publish","type":"post","link":"https:\/\/egonlin.com\/?p=11415","title":{"rendered":"\u5355\u8868\u67e5\u8be2"},"content":{"rendered":"<h3>\u4e00 \u5355\u8868\u67e5\u8be2\u7684\u8bed\u6cd5<\/h3>\n<div class=\"cnblogs_code\">\n<pre>SELECT \u5b57\u6bb51,\u5b57\u6bb52... FROM \u8868\u540d\r\n                  WHERE \u6761\u4ef6\r\n                  GROUP BY field\r\n                  HAVING \u7b5b\u9009\r\n                  ORDER BY field\r\n                  LIMIT \u9650\u5236\u6761\u6570<\/pre>\n<\/div>\n<h3>\u4e8c \u5173\u952e\u5b57\u7684\u6267\u884c\u4f18\u5148\u7ea7(\u91cd\u70b9)<\/h3>\n<div class=\"cnblogs_code\">\n<pre>\u91cd\u70b9\u4e2d\u7684\u91cd\u70b9\uff1a\u5173\u952e\u5b57\u7684\u6267\u884c\u4f18\u5148\u7ea7\r\nfrom\r\nwhere\r\ngroup by\r\nhaving\r\nselect\r\ndistinct\r\norder by\r\nlimit<\/pre>\n<\/div>\n<p><strong>1.\u627e\u5230\u8868:from<\/strong><\/p>\n<p><strong>2.\u62ff\u7740where\u6307\u5b9a\u7684\u7ea6\u675f\u6761\u4ef6\uff0c\u53bb\u6587\u4ef6\/\u8868\u4e2d\u53d6\u51fa\u4e00\u6761\u6761\u8bb0\u5f55<\/strong><\/p>\n<p><strong>3.\u5c06\u53d6\u51fa\u7684\u4e00\u6761\u6761\u8bb0\u5f55\u8fdb\u884c\u5206\u7ec4group by\uff0c\u5982\u679c\u6ca1\u6709group by\uff0c\u5219\u6574\u4f53\u4f5c\u4e3a\u4e00\u7ec4<\/strong><\/p>\n<p><strong>4.\u5c06\u5206\u7ec4\u7684\u7ed3\u679c\u8fdb\u884chaving\u8fc7\u6ee4<\/strong><\/p>\n<p><strong>5.\u6267\u884cselect<\/strong><\/p>\n<p><strong>6.\u53bb\u91cd<\/strong><\/p>\n<p><strong>7.\u5c06\u7ed3\u679c\u6309\u6761\u4ef6\u6392\u5e8f\uff1aorder by<\/strong><\/p>\n<p><strong>8.\u9650\u5236\u7ed3\u679c\u7684\u663e\u793a\u6761\u6570<\/strong><\/p>\n<p>\u8be6\u7ec6\u89c1<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"6mZz7QQxtq\"><p><a href=\"https:\/\/egonlin.com\/?p=11448\">SQL\u903b\u8f91\u67e5\u8be2\u8bed\u53e5\u6267\u884c\u987a\u5e8f<\/a><\/p><\/blockquote>\n<p><iframe class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"\u300aSQL\u903b\u8f91\u67e5\u8be2\u8bed\u53e5\u6267\u884c\u987a\u5e8f\u300b\u2014Egon\u6797\u6d77\u5cf0\" src=\"https:\/\/egonlin.com\/?p=11448&#038;embed=true#?secret=eUuxr0R3UR#?secret=6mZz7QQxtq\" data-secret=\"6mZz7QQxtq\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<h3>\u4e09 \u7b80\u5355\u67e5\u8be2<\/h3>\n<p>\u51c6\u5907\u8868\u548c\u8bb0\u5f55<\/p>\n<pre>company.employee\r\n    \u5458\u5de5id      id                  int             \r\n    \u59d3\u540d        emp_name            varchar\r\n    \u6027\u522b        sex                 enum\r\n    \u5e74\u9f84        age                 int\r\n    \u5165\u804c\u65e5\u671f     hire_date           date\r\n    \u5c97\u4f4d        post                varchar\r\n    \u804c\u4f4d\u63cf\u8ff0     post_comment        varchar\r\n    \u85aa\u6c34        salary              double\r\n    \u529e\u516c\u5ba4       office              int\r\n    \u90e8\u95e8\u7f16\u53f7     depart_id           int\r\n\r\n\r\n\r\n#\u521b\u5efa\u8868\r\ncreate table employee(\r\nid int not null unique auto_increment,\r\nname varchar(20) not null,\r\nsex enum('male','female') not null default 'male', #\u5927\u90e8\u5206\u662f\u7537\u7684\r\nage int(3) unsigned not null default 28,\r\nhire_date date not null,\r\npost varchar(50),\r\npost_comment varchar(100),\r\nsalary double(15,2),\r\noffice int, #\u4e00\u4e2a\u90e8\u95e8\u4e00\u4e2a\u5c4b\u5b50\r\ndepart_id int\r\n);\r\n\r\n\r\n#\u67e5\u770b\u8868\u7ed3\u6784\r\nmysql&gt; desc employee;\r\n+--------------+-----------------------+------+-----+---------+----------------+\r\n| Field        | Type                  | Null | Key | Default | Extra          |\r\n+--------------+-----------------------+------+-----+---------+----------------+\r\n| id           | int(11)               | NO   | PRI | NULL    | auto_increment |\r\n| name         | varchar(20)           | NO   |     | NULL    |                |\r\n| sex          | enum('male','female') | NO   |     | male    |                |\r\n| age          | int(3) unsigned       | NO   |     | 28      |                |\r\n| hire_date    | date                  | NO   |     | NULL    |                |\r\n| post         | varchar(50)           | YES  |     | NULL    |                |\r\n| post_comment | varchar(100)          | YES  |     | NULL    |                |\r\n| salary       | double(15,2)          | YES  |     | NULL    |                |\r\n| office       | int(11)               | YES  |     | NULL    |                |\r\n| depart_id    | int(11)               | YES  |     | NULL    |                |\r\n+--------------+-----------------------+------+-----+---------+----------------+\r\n\r\n#\u63d2\u5165\u8bb0\u5f55\r\n#\u4e09\u4e2a\u90e8\u95e8\uff1a\u6559\u5b66\uff0c\u9500\u552e\uff0c\u8fd0\u8425\r\ninsert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values\r\n('egon','male',18,'20170301','\u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f',7300.33,401,1), #\u4ee5\u4e0b\u662f\u6559\u5b66\u90e8\r\n('alex','male',78,'20150302','teacher',1000000.31,401,1),\r\n('wupeiqi','male',81,'20130305','teacher',8300,401,1),\r\n('yuanhao','male',73,'20140701','teacher',3500,401,1),\r\n('liwenzhou','male',28,'20121101','teacher',2100,401,1),\r\n('jingliyang','female',18,'20110211','teacher',9000,401,1),\r\n('jinxin','male',18,'19000301','teacher',30000,401,1),\r\n('\u6210\u9f99','male',48,'20101111','teacher',10000,401,1),\r\n\r\n('\u6b6a\u6b6a','female',48,'20150311','sale',3000.13,402,2),#\u4ee5\u4e0b\u662f\u9500\u552e\u90e8\u95e8\r\n('\u4e2b\u4e2b','female',38,'20101101','sale',2000.35,402,2),\r\n('\u4e01\u4e01','female',18,'20110312','sale',1000.37,402,2),\r\n('\u661f\u661f','female',18,'20160513','sale',3000.29,402,2),\r\n('\u683c\u683c','female',28,'20170127','sale',4000.33,402,2),\r\n\r\n('\u5f20\u91ce','male',28,'20160311','operation',10000.13,403,3), #\u4ee5\u4e0b\u662f\u8fd0\u8425\u90e8\u95e8\r\n('\u7a0b\u54ac\u91d1','male',18,'19970312','operation',20000,403,3),\r\n('\u7a0b\u54ac\u94f6','female',18,'20130311','operation',19000,403,3),\r\n('\u7a0b\u54ac\u94dc','male',18,'20150411','operation',18000,403,3),\r\n('\u7a0b\u54ac\u94c1','female',18,'20140512','operation',17000,403,3)\r\n;\r\n\r\n#ps\uff1a\u5982\u679c\u5728windows\u7cfb\u7edf\u4e2d\uff0c\u63d2\u5165\u4e2d\u6587\u5b57\u7b26\uff0cselect\u7684\u7ed3\u679c\u4e3a\u7a7a\u767d\uff0c\u53ef\u4ee5\u5c06\u6240\u6709\u5b57\u7b26\u7f16\u7801\u7edf\u4e00\u8bbe\u7f6e\u6210gbk\r\n\r\n\r\n<\/pre>\n<pre>#\u7b80\u5355\u67e5\u8be2\r\n    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id \r\n    FROM employee;\r\n\r\n    SELECT * FROM employee;\r\n\r\n    SELECT name,salary FROM employee;\r\n\r\n#\u907f\u514d\u91cd\u590dDISTINCT\r\n    SELECT DISTINCT post FROM employee;    \r\n\r\n#\u901a\u8fc7\u56db\u5219\u8fd0\u7b97\u67e5\u8be2\r\n    SELECT name, salary*12 FROM employee;\r\n    SELECT name, salary*12 AS Annual_salary FROM employee;\r\n    SELECT name, salary*12 Annual_salary FROM employee;\r\n\r\n#\u5b9a\u4e49\u663e\u793a\u683c\u5f0f\r\n   CONCAT() \u51fd\u6570\u7528\u4e8e\u8fde\u63a5\u5b57\u7b26\u4e32\r\n   SELECT CONCAT('\u59d3\u540d: ',name,'  \u5e74\u85aa: ', salary*12)  AS Annual_salary \r\n   FROM employee;\r\n   \r\n   CONCAT_WS() \u7b2c\u4e00\u4e2a\u53c2\u6570\u4e3a\u5206\u9694\u7b26\r\n   SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary \r\n   FROM employee;\r\n\r\n   \u7ed3\u5408CASE\u8bed\u53e5\uff1a\r\n   SELECT\r\n       (\r\n           CASE\r\n           WHEN NAME = 'egon' THEN\r\n               NAME\r\n           WHEN NAME = 'alex' THEN\r\n               CONCAT(name,'_BIGSB')\r\n           ELSE\r\n               concat(NAME, 'SB')\r\n           END\r\n       ) as new_name\r\n   FROM\r\n       emp;\r\n\r\n<\/pre>\n<p>\u5c0f\u7ec3\u4e60\uff1a<\/p>\n<div class=\"cnblogs_code\">\n<pre>1 \u67e5\u51fa\u6240\u6709\u5458\u5de5\u7684\u540d\u5b57\uff0c\u85aa\u8d44,\u683c\u5f0f\u4e3a\r\n    &lt;\u540d\u5b57:egon&gt;    &lt;\u85aa\u8d44:3000&gt;\r\n2 \u67e5\u51fa\u6240\u6709\u7684\u5c97\u4f4d\uff08\u53bb\u6389\u91cd\u590d\uff09\r\n3 \u67e5\u51fa\u6240\u6709\u5458\u5de5\u540d\u5b57\uff0c\u4ee5\u53ca\u4ed6\u4eec\u7684\u5e74\u85aa,\u5e74\u85aa\u7684\u5b57\u6bb5\u540d\u4e3aannual_year<\/pre>\n<\/div>\n<div class=\"cnblogs_code\">\n<div id=\"cnblogs_code_open_afadf8e2-03c1-4fd8-9573-27b310228184\" class=\"cnblogs_code_hide\">\n<pre>select concat('&lt;\u540d\u5b57:',name,'&gt;    ','&lt;\u85aa\u8d44:',salary,'&gt;') from employee;\r\nselect distinct depart_id from employee;\r\nselect name,salary*12 annual_salary from employee;<\/pre>\n<\/div>\n<\/div>\n<h3>\u56db WHERE\u7ea6\u675f<\/h3>\n<p>where\u5b57\u53e5\u4e2d\u53ef\u4ee5\u4f7f\u7528\uff1a<\/p>\n<p>1. \u6bd4\u8f83\u8fd0\u7b97\u7b26\uff1a&gt; &lt; &gt;= &lt;= &lt;&gt; !=<br \/>\n2. between 80 and 100 \u503c\u572810\u523020\u4e4b\u95f4<br \/>\n3. in(80,90,100) \u503c\u662f10\u621620\u621630<br \/>\n4. like &#8216;egon%&#8217;<br \/>\npattern\u53ef\u4ee5\u662f%\u6216_\uff0c<br \/>\n%\u8868\u793a\u4efb\u610f\u591a\u5b57\u7b26<br \/>\n_\u8868\u793a\u4e00\u4e2a\u5b57\u7b26<br \/>\n5. \u903b\u8f91\u8fd0\u7b97\u7b26\uff1a\u5728\u591a\u4e2a\u6761\u4ef6\u76f4\u63a5\u53ef\u4ee5\u4f7f\u7528\u903b\u8f91\u8fd0\u7b97\u7b26 and or not<\/p>\n<div class=\"cnblogs_code\">\n<pre>#1:\u5355\u6761\u4ef6\u67e5\u8be2\r\n    SELECT name FROM employee\r\n        WHERE post='sale';\r\n        \r\n#2:\u591a\u6761\u4ef6\u67e5\u8be2\r\n    SELECT name,salary FROM employee\r\n        WHERE post='teacher' AND salary&gt;10000;\r\n\r\n#3:\u5173\u952e\u5b57BETWEEN AND\r\n    SELECT name,salary FROM employee \r\n        WHERE salary BETWEEN 10000 AND 20000;\r\n\r\n    SELECT name,salary FROM employee \r\n        WHERE salary NOT BETWEEN 10000 AND 20000;\r\n    \r\n#4:\u5173\u952e\u5b57IS NULL(\u5224\u65ad\u67d0\u4e2a\u5b57\u6bb5\u662f\u5426\u4e3aNULL\u4e0d\u80fd\u7528\u7b49\u53f7\uff0c\u9700\u8981\u7528IS)\r\n    SELECT name,post_comment FROM employee \r\n        WHERE post_comment IS NULL;\r\n\r\n    SELECT name,post_comment FROM employee \r\n        WHERE post_comment IS NOT NULL;\r\n        \r\n    SELECT name,post_comment FROM employee \r\n        WHERE post_comment=''; \u6ce8\u610f''\u662f\u7a7a\u5b57\u7b26\u4e32\uff0c\u4e0d\u662fnull\r\n    ps\uff1a\r\n        \u6267\u884c\r\n        update employee set post_comment='' where id=2;\r\n        \u518d\u7528\u4e0a\u6761\u67e5\u770b\uff0c\u5c31\u4f1a\u6709\u7ed3\u679c\u4e86\r\n\r\n#5:\u5173\u952e\u5b57IN\u96c6\u5408\u67e5\u8be2\r\n    SELECT name,salary FROM employee \r\n        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;\r\n    \r\n    SELECT name,salary FROM employee \r\n        WHERE salary IN (3000,3500,4000,9000) ;\r\n\r\n    SELECT name,salary FROM employee \r\n        WHERE salary NOT IN (3000,3500,4000,9000) ;\r\n\r\n#6:\u5173\u952e\u5b57LIKE\u6a21\u7cca\u67e5\u8be2\r\n    \u901a\u914d\u7b26\u2019%\u2019\r\n    SELECT * FROM employee \r\n            WHERE name LIKE 'eg%';\r\n\r\n    \u901a\u914d\u7b26\u2019_\u2019\r\n    SELECT * FROM employee \r\n            WHERE name LIKE 'al__';<\/pre>\n<\/div>\n<p>\u5c0f\u7ec3\u4e60\uff1a<\/p>\n<div class=\"cnblogs_code\">\n<pre>1. \u67e5\u770b\u5c97\u4f4d\u662fteacher\u7684\u5458\u5de5\u59d3\u540d\u3001\u5e74\u9f84\r\n2. \u67e5\u770b\u5c97\u4f4d\u662fteacher\u4e14\u5e74\u9f84\u5927\u4e8e30\u5c81\u7684\u5458\u5de5\u59d3\u540d\u3001\u5e74\u9f84\r\n3. \u67e5\u770b\u5c97\u4f4d\u662fteacher\u4e14\u85aa\u8d44\u57289000-1000\u8303\u56f4\u5185\u7684\u5458\u5de5\u59d3\u540d\u3001\u5e74\u9f84\u3001\u85aa\u8d44\r\n4. \u67e5\u770b\u5c97\u4f4d\u63cf\u8ff0\u4e0d\u4e3aNULL\u7684\u5458\u5de5\u4fe1\u606f\r\n5. \u67e5\u770b\u5c97\u4f4d\u662fteacher\u4e14\u85aa\u8d44\u662f10000\u62169000\u621630000\u7684\u5458\u5de5\u59d3\u540d\u3001\u5e74\u9f84\u3001\u85aa\u8d44\r\n6. \u67e5\u770b\u5c97\u4f4d\u662fteacher\u4e14\u85aa\u8d44\u4e0d\u662f10000\u62169000\u621630000\u7684\u5458\u5de5\u59d3\u540d\u3001\u5e74\u9f84\u3001\u85aa\u8d44\r\n7. \u67e5\u770b\u5c97\u4f4d\u662fteacher\u4e14\u540d\u5b57\u662fjin\u5f00\u5934\u7684\u5458\u5de5\u59d3\u540d\u3001\u5e74\u85aa<\/pre>\n<\/div>\n<pre>select name,age from employee where post = 'teacher';\r\nselect name,age from employee where post='teacher' and age &gt; 30; \r\nselect name,age,salary from employee where post='teacher' and salary between 9000 and 10000;\r\nselect * from employee where post_comment is not null;\r\nselect name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);\r\nselect name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);\r\nselect name,salary*12 from employee where post='teacher' and name like 'jin%';\r\n\r\n<\/pre>\n<h3>\u4e94 \u5206\u7ec4\u67e5\u8be2:GROUP BY<\/h3>\n<p><strong>\u4e00 \u4ec0\u4e48\u662f\u5206\u7ec4\uff1f\u4e3a\u4ec0\u4e48\u8981\u5206\u7ec4\uff1f<\/strong><\/p>\n<div class=\"cnblogs_code\">\n<pre>#1\u3001\u9996\u5148\u660e\u786e\u4e00\u70b9\uff1a\u5206\u7ec4\u53d1\u751f\u5728where\u4e4b\u540e\uff0c\u5373\u5206\u7ec4\u662f\u57fa\u4e8ewhere\u4e4b\u540e\u5f97\u5230\u7684\u8bb0\u5f55\u800c\u8fdb\u884c\u7684\r\n\r\n#2\u3001\u5206\u7ec4\u6307\u7684\u662f\uff1a\u5c06\u6240\u6709\u8bb0\u5f55\u6309\u7167\u67d0\u4e2a\u76f8\u540c\u5b57\u6bb5\u8fdb\u884c\u5f52\u7c7b\uff0c\u6bd4\u5982\u9488\u5bf9\u5458\u5de5\u4fe1\u606f\u8868\u7684\u804c\u4f4d\u5206\u7ec4\uff0c\u6216\u8005\u6309\u7167\u6027\u522b\u8fdb\u884c\u5206\u7ec4\u7b49\r\n\r\n#3\u3001\u4e3a\u4f55\u8981\u5206\u7ec4\u5462\uff1f\r\n    \u53d6\u6bcf\u4e2a\u90e8\u95e8\u7684\u6700\u9ad8\u5de5\u8d44\r\n    \u53d6\u6bcf\u4e2a\u90e8\u95e8\u7684\u5458\u5de5\u6570\r\n    \u53d6\u7537\u4eba\u6570\u548c\u5973\u4eba\u6570\r\n\r\n\u5c0f\u7a8d\u95e8\uff1a\u2018\u6bcf\u2019\u8fd9\u4e2a\u5b57\u540e\u9762\u7684\u5b57\u6bb5\uff0c\u5c31\u662f\u6211\u4eec\u5206\u7ec4\u7684\u4f9d\u636e\r\n\r\n\r\n#4\u3001\u5927\u524d\u63d0\uff1a\r\n    \u53ef\u4ee5\u6309\u7167\u4efb\u610f\u5b57\u6bb5\u5206\u7ec4\uff0c\u4f46\u662f\u5206\u7ec4\u5b8c\u6bd5\u540e\uff0c\u6bd4\u5982group by post\uff0c\u53ea\u80fd\u67e5\u770bpost\u5b57\u6bb5\uff0c\u5982\u679c\u60f3\u67e5\u770b\u7ec4\u5185\u4fe1\u606f\uff0c\u9700\u8981\u501f\u52a9\u4e8e\u805a\u5408<\/pre>\n<\/div>\n<p><strong>\u4e8c ONLY_FULL_GROUP_BY<\/strong><br \/>\n\uff01\uff01\uff01SQL_MODE\u8bbe\u7f6e\uff01\uff01\uff01<\/p>\n<pre>#\u67e5\u770bMySQL 5.7\u9ed8\u8ba4\u7684sql_mode\u5982\u4e0b\uff1a\r\nmysql&gt; select @@global.sql_mode;\r\nONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\r\n\r\n#\uff01\uff01\uff01\u6ce8\u610f\r\nONLY_FULL_GROUP_BY\u7684\u8bed\u4e49\u5c31\u662f\u786e\u5b9aselect target list\u4e2d\u7684\u6240\u6709\u5217\u7684\u503c\u90fd\u662f\u660e\u786e\u8bed\u4e49\uff0c\u7b80\u5355\u7684\u8bf4\u6765\uff0c\u5728ONLY_FULL_GROUP_BY\u6a21\u5f0f\u4e0b\uff0ctarget list\u4e2d\u7684\u503c\u8981\u4e48\u662f\u6765\u81ea\u4e8e\u805a\u96c6\u51fd\u6570\u7684\u7ed3\u679c\uff0c\u8981\u4e48\u662f\u6765\u81ea\u4e8egroup by list\u4e2d\u7684\u8868\u8fbe\u5f0f\u7684\u503c\u3002\r\n\r\n\r\n#\u8bbe\u7f6esql_mole\u5982\u4e0b\u64cd\u4f5c(\u6211\u4eec\u53ef\u4ee5\u53bb\u6389ONLY_FULL_GROUP_BY\u6a21\u5f0f)\uff1a\r\nmysql&gt; set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';\r\n\r\n<\/pre>\n<pre>mysql&gt; select @@global.sql_mode;\r\n+-------------------+\r\n| @@global.sql_mode |\r\n+-------------------+\r\n|                   |\r\n+-------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql&gt; select * from emp group by post; \r\n+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+\r\n| id | name | sex    | age | hire_date  | post                       | post_comment | salary     | office | depart_id |\r\n+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+\r\n| 14 | \u5f20\u91ce | male   |  28 | 2016-03-11 | operation                  | NULL         |   10000.13 |    403 |         3 |\r\n|  9 | \u6b6a\u6b6a | female |  48 | 2015-03-11 | sale                       | NULL         |    3000.13 |    402 |         2 |\r\n|  2 | alex | male   |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |\r\n|  1 | egon | male   |  18 | 2017-03-01 | \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f | NULL         |    7300.33 |    401 |         1 |\r\n+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+\r\n4 rows in set (0.00 sec)\r\n\r\n\r\n#\u7531\u4e8e\u6ca1\u6709\u8bbe\u7f6eONLY_FULL_GROUP_BY,\u4e8e\u662f\u4e5f\u53ef\u4ee5\u6709\u7ed3\u679c\uff0c\u9ed8\u8ba4\u90fd\u662f\u7ec4\u5185\u7684\u7b2c\u4e00\u6761\u8bb0\u5f55\uff0c\u4f46\u5176\u5b9e\u8fd9\u662f\u6ca1\u6709\u610f\u4e49\u7684\r\n\r\nmysql&gt; set global sql_mode='ONLY_FULL_GROUP_BY';\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql&gt; quit #\u8bbe\u7f6e\u6210\u529f\u540e\uff0c\u4e00\u5b9a\u8981\u9000\u51fa\uff0c\u7136\u540e\u91cd\u65b0\u767b\u5f55\u65b9\u53ef\u751f\u6548\r\nBye\r\n\r\nmysql&gt; use db1;\r\nDatabase changed\r\nmysql&gt; select * from emp group by post; #\u62a5\u9519\r\nERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY\r\nmysql&gt; select post,count(id) from emp group by post; #\u53ea\u80fd\u67e5\u770b\u5206\u7ec4\u4f9d\u636e\u548c\u4f7f\u7528\u805a\u5408\u51fd\u6570\r\n+----------------------------+-----------+\r\n| post                       | count(id) |\r\n+----------------------------+-----------+\r\n| operation                  |         5 |\r\n| sale                       |         5 |\r\n| teacher                    |         7 |\r\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f |         1 |\r\n+----------------------------+-----------+\r\n4 rows in set (0.00 sec)<\/pre>\n<p><strong>\u4e09 GROUP BY<\/strong><\/p>\n<div class=\"cnblogs_code\">\n<pre>\u5355\u72ec\u4f7f\u7528GROUP BY\u5173\u952e\u5b57\u5206\u7ec4\r\n    SELECT post FROM employee GROUP BY post;\r\n    \u6ce8\u610f\uff1a\u6211\u4eec\u6309\u7167post\u5b57\u6bb5\u5206\u7ec4\uff0c\u90a3\u4e48select\u67e5\u8be2\u7684\u5b57\u6bb5\u53ea\u80fd\u662fpost\uff0c\u60f3\u8981\u83b7\u53d6\u7ec4\u5185\u7684\u5176\u4ed6\u76f8\u5173\u4fe1\u606f\uff0c\u9700\u8981\u501f\u52a9\u51fd\u6570\r\n\r\nGROUP BY\u5173\u952e\u5b57\u548cGROUP_CONCAT()\u51fd\u6570\u4e00\u8d77\u4f7f\u7528\r\n    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#\u6309\u7167\u5c97\u4f4d\u5206\u7ec4\uff0c\u5e76\u67e5\u770b\u7ec4\u5185\u6210\u5458\u540d\r\n    SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;\r\n\r\nGROUP BY\u4e0e\u805a\u5408\u51fd\u6570\u4e00\u8d77\u4f7f\u7528\r\n    select post,count(id) as count from employee group by post;#\u6309\u7167\u5c97\u4f4d\u5206\u7ec4\uff0c\u5e76\u67e5\u770b\u6bcf\u4e2a\u7ec4\u6709\u591a\u5c11\u4eba<\/pre>\n<p><strong>\u5f3a\u8c03\uff1a<\/strong><\/p>\n<div class=\"cnblogs_code\">\n<pre>\u5982\u679c\u6211\u4eec\u7528unique\u7684\u5b57\u6bb5\u4f5c\u4e3a\u5206\u7ec4\u7684\u4f9d\u636e\uff0c\u5219\u6bcf\u4e00\u6761\u8bb0\u5f55\u81ea\u6210\u4e00\u7ec4\uff0c\u8fd9\u79cd\u5206\u7ec4\u6ca1\u6709\u610f\u4e49\r\n\u591a\u6761\u8bb0\u5f55\u4e4b\u95f4\u7684\u67d0\u4e2a\u5b57\u6bb5\u503c\u76f8\u540c\uff0c\u8be5\u5b57\u6bb5\u901a\u5e38\u7528\u6765\u4f5c\u4e3a\u5206\u7ec4\u7684\u4f9d\u636e<\/pre>\n<p><strong>\u56db \u805a\u5408\u51fd\u6570<\/strong><\/p>\n<div class=\"cnblogs_code\">\n<pre>#\u5f3a\u8c03\uff1a\u805a\u5408\u51fd\u6570\u805a\u5408\u7684\u662f\u7ec4\u7684\u5185\u5bb9\uff0c\u82e5\u662f\u6ca1\u6709\u5206\u7ec4\uff0c\u5219\u9ed8\u8ba4\u4e00\u7ec4\r\n\r\n\u793a\u4f8b\uff1a\r\n    SELECT COUNT(*) FROM employee;\r\n    SELECT COUNT(*) FROM employee WHERE depart_id=1;\r\n    SELECT MAX(salary) FROM employee;\r\n    SELECT MIN(salary) FROM employee;\r\n    SELECT AVG(salary) FROM employee;\r\n    SELECT SUM(salary) FROM employee;\r\n    SELECT SUM(salary) FROM employee WHERE depart_id=3;<\/pre>\n<\/div>\n<p><strong>\u4e94 \u5c0f\u7ec3\u4e60\uff1a<\/strong><\/p>\n<div class=\"cnblogs_code\">\n<pre>1. \u67e5\u8be2\u5c97\u4f4d\u540d\u4ee5\u53ca\u5c97\u4f4d\u5305\u542b\u7684\u6240\u6709\u5458\u5de5\u540d\u5b57\r\n2. \u67e5\u8be2\u5c97\u4f4d\u540d\u4ee5\u53ca\u5404\u5c97\u4f4d\u5185\u5305\u542b\u7684\u5458\u5de5\u4e2a\u6570\r\n3. \u67e5\u8be2\u516c\u53f8\u5185\u7537\u5458\u5de5\u548c\u5973\u5458\u5de5\u7684\u4e2a\u6570\r\n4. \u67e5\u8be2\u5c97\u4f4d\u540d\u4ee5\u53ca\u5404\u5c97\u4f4d\u7684\u5e73\u5747\u85aa\u8d44\r\n5. \u67e5\u8be2\u5c97\u4f4d\u540d\u4ee5\u53ca\u5404\u5c97\u4f4d\u7684\u6700\u9ad8\u85aa\u8d44\r\n6. \u67e5\u8be2\u5c97\u4f4d\u540d\u4ee5\u53ca\u5404\u5c97\u4f4d\u7684\u6700\u4f4e\u85aa\u8d44\r\n7.\u00a0\u67e5\u8be2\u7537\u5458\u5de5\u4e0e\u7537\u5458\u5de5\u7684\u5e73\u5747\u85aa\u8d44\uff0c\u5973\u5458\u5de5\u4e0e\u5973\u5458\u5de5\u7684\u5e73\u5747\u85aa\u8d44\r\n\r\n<\/pre>\n<pre>#\u98981\uff1a\u5206\u7ec4\r\nmysql&gt; select post,group_concat(name) from employee group by post;\r\n+-----------------------------------------+---------------------------------------------------------+\r\n| post                                    | group_concat(name)                                      |\r\n+-----------------------------------------+---------------------------------------------------------+\r\n| operation                               | \u5f20\u91ce,\u7a0b\u54ac\u91d1,\u7a0b\u54ac\u94f6,\u7a0b\u54ac\u94dc,\u7a0b\u54ac\u94c1                        |\r\n| sale                                    | \u6b6a\u6b6a,\u4e2b\u4e2b,\u4e01\u4e01,\u661f\u661f,\u683c\u683c                                |\r\n| teacher                                 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,\u6210\u9f99   |\r\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f              | egon                                                    |\r\n+-----------------------------------------+---------------------------------------------------------+\r\n\r\n\r\n#\u9898\u76ee2\uff1a\r\nmysql&gt; select post,count(id) from employee group by post;\r\n+-----------------------------------------+-----------+\r\n| post                                    | count(id) |\r\n+-----------------------------------------+-----------+\r\n| operation                               |         5 |\r\n| sale                                    |         5 |\r\n| teacher                                 |         7 |\r\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f              |         1 |\r\n+-----------------------------------------+-----------+\r\n\r\n\r\n#\u9898\u76ee3\uff1a\r\nmysql&gt; select sex,count(id) from employee group by sex;\r\n+--------+-----------+\r\n| sex    | count(id) |\r\n+--------+-----------+\r\n| male   |        10 |\r\n| female |         8 |\r\n+--------+-----------+\r\n\r\n#\u9898\u76ee4\uff1a\r\nmysql&gt; select post,avg(salary) from employee group by post;\r\n+-----------------------------------------+---------------+\r\n| post                                    | avg(salary)   |\r\n+-----------------------------------------+---------------+\r\n| operation                               |  16800.026000 |\r\n| sale                                    |   2600.294000 |\r\n| teacher                                 | 151842.901429 |\r\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f              |   7300.330000 |\r\n+-----------------------------------------+---------------+\r\n\r\n#\u9898\u76ee5\r\nmysql&gt; select post,max(salary) from employee group by post;\r\n+-----------------------------------------+-------------+\r\n| post                                    | max(salary) |\r\n+-----------------------------------------+-------------+\r\n| operation                               |    20000.00 |\r\n| sale                                    |     4000.33 |\r\n| teacher                                 |  1000000.31 |\r\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f              |     7300.33 |\r\n+-----------------------------------------+-------------+\r\n\r\n#\u9898\u76ee6\r\nmysql&gt; select post,min(salary) from employee group by post;\r\n+-----------------------------------------+-------------+\r\n| post                                    | min(salary) |\r\n+-----------------------------------------+-------------+\r\n| operation                               |    10000.13 |\r\n| sale                                    |     1000.37 |\r\n| teacher                                 |     2100.00 |\r\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f              |     7300.33 |\r\n+-----------------------------------------+-------------+\r\n\r\n#\u9898\u76ee\u4e03\r\nmysql&gt; select sex,avg(salary) from employee group by sex;\r\n+--------+---------------+\r\n| sex    | avg(salary)   |\r\n+--------+---------------+\r\n| male   | 110920.077000 |\r\n| female |   7250.183750 |\r\n+--------+---------------+<\/pre>\n<h3>\u516d HAVING\u8fc7\u6ee4<\/h3>\n<p><strong>HAVING\u4e0eWHERE\u4e0d\u4e00\u6837\u7684\u5730\u65b9\u5728\u4e8e!!!!!!<\/strong><\/p>\n<div class=\"cnblogs_code\">\n<pre>#\uff01\uff01\uff01\u6267\u884c\u4f18\u5148\u7ea7\u4ece\u9ad8\u5230\u4f4e\uff1awhere &gt; group by &gt; having \r\n#1. Where \u53d1\u751f\u5728\u5206\u7ec4group by\u4e4b\u524d\uff0c\u56e0\u800cWhere\u4e2d\u53ef\u4ee5\u6709\u4efb\u610f\u5b57\u6bb5\uff0c\u4f46\u662f\u7edd\u5bf9\u4e0d\u80fd\u4f7f\u7528\u805a\u5408\u51fd\u6570\u3002\r\n\r\n#2. Having\u53d1\u751f\u5728\u5206\u7ec4group by\u4e4b\u540e\uff0c\u56e0\u800cHaving\u4e2d\u53ef\u4ee5\u4f7f\u7528\u5206\u7ec4\u7684\u5b57\u6bb5\uff0c\u65e0\u6cd5\u76f4\u63a5\u53d6\u5230\u5176\u4ed6\u5b57\u6bb5,\u53ef\u4ee5\u4f7f\u7528\u805a\u5408\u51fd\u6570\r\n\r\n<\/pre>\n<p>\u9a8c\u8bc1<\/p>\n<pre>mysql&gt; select @@sql_mode;\r\n+--------------------+\r\n| @@sql_mode         |\r\n+--------------------+\r\n| ONLY_FULL_GROUP_BY |\r\n+--------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql&gt; select * from emp where salary &gt; 100000;\r\n+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+\r\n| id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |\r\n+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+\r\n|  2 | alex | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |\r\n+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql&gt; select * from emp having salary &gt; 100000;\r\nERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause\r\n\r\nmysql&gt; select post,group_concat(name) from emp group by post having salary &gt; 10000;#\u9519\u8bef\uff0c\u5206\u7ec4\u540e\u65e0\u6cd5\u76f4\u63a5\u53d6\u5230salary\u5b57\u6bb5\r\nERROR 1054 (42S22): Unknown column 'salary' in 'having clause'\r\nmysql&gt; select post,group_concat(name) from emp group by post having avg(salary) &gt; 10000;\r\n+-----------+-------------------------------------------------------+\r\n| post | group_concat(name) |\r\n+-----------+-------------------------------------------------------+\r\n| operation | \u7a0b\u54ac\u94c1,\u7a0b\u54ac\u94dc,\u7a0b\u54ac\u94f6,\u7a0b\u54ac\u91d1,\u5f20\u91ce |\r\n| teacher | \u6210\u9f99,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |\r\n+-----------+-------------------------------------------------------+\r\n2 rows in set (0.00 sec)<\/pre>\n<p>\u5c0f\u7ec3\u4e60\uff1a<\/p>\n<div class=\"cnblogs_code\">\n<pre>1. \u67e5\u8be2\u5404\u5c97\u4f4d\u5185\u5305\u542b\u7684\u5458\u5de5\u4e2a\u6570\u5c0f\u4e8e2\u7684\u5c97\u4f4d\u540d\u3001\u5c97\u4f4d\u5185\u5305\u542b\u5458\u5de5\u540d\u5b57\u3001\u4e2a\u6570\r\n3. \u67e5\u8be2\u5404\u5c97\u4f4d\u5e73\u5747\u85aa\u8d44\u5927\u4e8e10000\u7684\u5c97\u4f4d\u540d\u3001\u5e73\u5747\u5de5\u8d44\r\n4. \u67e5\u8be2\u5404\u5c97\u4f4d\u5e73\u5747\u85aa\u8d44\u5927\u4e8e10000\u4e14\u5c0f\u4e8e20000\u7684\u5c97\u4f4d\u540d\u3001\u5e73\u5747\u5de5\u8d44<\/pre>\n<\/div>\n<div class=\"cnblogs_code\">\n<div id=\"cnblogs_code_open_50574448-ec93-4893-81ea-f20cb93ab7f0\" class=\"cnblogs_code_hide\">\n<pre>#\u98981\uff1a\r\nmysql&gt; select post,group_concat(name),count(id) from employee group by post having count(id) &lt; 2;\r\n+-----------------------------------------+--------------------+-----------+\r\n| post                                    | group_concat(name) | count(id) |\r\n+-----------------------------------------+--------------------+-----------+\r\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f              | egon               |         1 |\r\n+-----------------------------------------+--------------------+-----------+\r\n\r\n#\u9898\u76ee2\uff1a\r\nmysql&gt; select post,avg(salary) from employee group by post having avg(salary) &gt; 10000;\r\n+-----------+---------------+\r\n| post      | avg(salary)   |\r\n+-----------+---------------+\r\n| operation |  16800.026000 |\r\n| teacher   | 151842.901429 |\r\n+-----------+---------------+\r\n\r\n#\u9898\u76ee3\uff1a\r\nmysql&gt; select post,avg(salary) from employee group by post having avg(salary) &gt; 10000 and avg(salary) &lt;20000;\r\n+-----------+--------------+\r\n| post      | avg(salary)  |\r\n+-----------+--------------+\r\n| operation | 16800.026000 |\r\n+-----------+--------------+<\/pre>\n<h3>\u4e03 \u67e5\u8be2\u6392\u5e8f:ORDER BY<\/h3>\n<div class=\"cnblogs_code\">\n<pre>\u6309\u5355\u5217\u6392\u5e8f\r\n    SELECT * FROM employee ORDER BY salary;\r\n    SELECT * FROM employee ORDER BY salary ASC;\r\n    SELECT * FROM employee ORDER BY salary DESC;\r\n\r\n\u6309\u591a\u5217\u6392\u5e8f:\u5148\u6309\u7167age\u6392\u5e8f\uff0c\u5982\u679c\u5e74\u7eaa\u76f8\u540c\uff0c\u5219\u6309\u7167\u85aa\u8d44\u6392\u5e8f\r\n    SELECT * from employee\r\n        ORDER BY age,\r\n        salary DESC;<\/pre>\n<\/div>\n<p>\u5c0f\u7ec3\u4e60\uff1a<\/p>\n<div class=\"cnblogs_code\">\n<pre>1. \u67e5\u8be2\u6240\u6709\u5458\u5de5\u4fe1\u606f\uff0c\u5148\u6309\u7167age\u5347\u5e8f\u6392\u5e8f\uff0c\u5982\u679cage\u76f8\u540c\u5219\u6309\u7167hire_date\u964d\u5e8f\u6392\u5e8f\r\n2. \u67e5\u8be2\u5404\u5c97\u4f4d\u5e73\u5747\u85aa\u8d44\u5927\u4e8e10000\u7684\u5c97\u4f4d\u540d\u3001\u5e73\u5747\u5de5\u8d44,\u7ed3\u679c\u6309\u5e73\u5747\u85aa\u8d44\u5347\u5e8f\u6392\u5217\r\n3. \u67e5\u8be2\u5404\u5c97\u4f4d\u5e73\u5747\u85aa\u8d44\u5927\u4e8e10000\u7684\u5c97\u4f4d\u540d\u3001\u5e73\u5747\u5de5\u8d44,\u7ed3\u679c\u6309\u5e73\u5747\u85aa\u8d44\u964d\u5e8f\u6392\u5217<\/pre>\n<\/div>\n<div class=\"cnblogs_code\">\n<div id=\"cnblogs_code_open_669e8618-4c68-4470-9c63-762fb34efcce\" class=\"cnblogs_code_hide\">\n<pre>#\u9898\u76ee1\r\nmysql&gt; select * from employee ORDER BY age asc,hire_date desc;\r\n\r\n#\u9898\u76ee2\r\nmysql&gt; select post,avg(salary) from employee group by post having avg(salary) &gt; 10000 order by avg(salary) asc;\r\n+-----------+---------------+\r\n| post      | avg(salary)   |\r\n+-----------+---------------+\r\n| operation |  16800.026000 |\r\n| teacher   | 151842.901429 |\r\n+-----------+---------------+\r\n\r\n#\u9898\u76ee3\r\nmysql&gt; select post,avg(salary) from employee group by post having avg(salary) &gt; 10000 order by avg(salary) desc;\r\n+-----------+---------------+\r\n| post      | avg(salary)   |\r\n+-----------+---------------+\r\n| teacher   | 151842.901429 |\r\n| operation |  16800.026000 |\r\n+-----------+---------------+<\/pre>\n<h3>\u516b \u9650\u5236\u67e5\u8be2\u7684\u8bb0\u5f55\u6570:LIMIT<\/h3>\n<div class=\"cnblogs_code\">\n<pre>\u793a\u4f8b\uff1a\r\n    SELECT * FROM employee ORDER BY salary DESC \r\n        LIMIT 3;                    #\u9ed8\u8ba4\u521d\u59cb\u4f4d\u7f6e\u4e3a0 \r\n    \r\n    SELECT * FROM employee ORDER BY salary DESC\r\n        LIMIT 0,5; #\u4ece\u7b2c0\u5f00\u59cb\uff0c\u5373\u5148\u67e5\u8be2\u51fa\u7b2c\u4e00\u6761\uff0c\u7136\u540e\u5305\u542b\u8fd9\u4e00\u6761\u5728\u5185\u5f80\u540e\u67e55\u6761\r\n\r\n    SELECT * FROM employee ORDER BY salary DESC\r\n        LIMIT 5,5; #\u4ece\u7b2c5\u5f00\u59cb\uff0c\u5373\u5148\u67e5\u8be2\u51fa\u7b2c6\u6761\uff0c\u7136\u540e\u5305\u542b\u8fd9\u4e00\u6761\u5728\u5185\u5f80\u540e\u67e55\u6761<\/pre>\n<\/div>\n<p>\u5c0f\u7ec3\u4e60\uff1a<\/p>\n<div class=\"cnblogs_code\">\n<pre>1. \u5206\u9875\u663e\u793a\uff0c\u6bcf\u98755\u6761<\/pre>\n<\/div>\n<div class=\"cnblogs_code\">\n<div id=\"cnblogs_code_open_a8e9b0d7-b88c-4a45-970d-6b435241501e\" class=\"cnblogs_code_hide\">\n<pre>mysql&gt; select * from  employee limit 0,5;\r\n+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+\r\n| id | name      | sex  | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |\r\n+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+\r\n|  1 | egon      | male |  18 | 2017-03-01 | \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f              | NULL         |    7300.33 |    401 |         1 |\r\n|  2 | alex      | male |  78 | 2015-03-02 | teacher                                 |              | 1000000.31 |    401 |         1 |\r\n|  3 | wupeiqi   | male |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |\r\n|  4 | yuanhao   | male |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |\r\n|  5 | liwenzhou | male |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |\r\n+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+\r\n5 rows in set (0.00 sec)\r\n\r\nmysql&gt; select * from  employee limit 5,5;\r\n+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+\r\n| id | name       | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |\r\n+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+\r\n|  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |\r\n|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |\r\n|  8 | \u6210\u9f99       | male   |  48 | 2010-11-11 | teacher | NULL         | 10000.00 |    401 |         1 |\r\n|  9 | \u6b6a\u6b6a       | female |  48 | 2015-03-11 | sale    | NULL         |  3000.13 |    402 |         2 |\r\n| 10 | \u4e2b\u4e2b       | female |  38 | 2010-11-01 | sale    | NULL         |  2000.35 |    402 |         2 |\r\n+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+\r\n5 rows in set (0.00 sec)\r\n\r\nmysql&gt; select * from  employee limit 10,5;\r\n+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+\r\n| id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |\r\n+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+\r\n| 11 | \u4e01\u4e01      | female |  18 | 2011-03-12 | sale      | NULL         |  1000.37 |    402 |         2 |\r\n| 12 | \u661f\u661f      | female |  18 | 2016-05-13 | sale      | NULL         |  3000.29 |    402 |         2 |\r\n| 13 | \u683c\u683c      | female |  28 | 2017-01-27 | sale      | NULL         |  4000.33 |    402 |         2 |\r\n| 14 | \u5f20\u91ce      | male   |  28 | 2016-03-11 | operation | NULL         | 10000.13 |    403 |         3 |\r\n| 15 | \u7a0b\u54ac\u91d1    | male   |  18 | 1997-03-12 | operation | NULL         | 20000.00 |    403 |         3 |\r\n+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+\r\n5 rows in set (0.00 sec)<\/pre>\n<h3>\u4e5d \u4f7f\u7528\u6b63\u5219\u8868\u8fbe\u5f0f\u67e5\u8be2<\/h3>\n<div class=\"cnblogs_code\">\n<pre>SELECT * FROM employee WHERE name REGEXP '^ale';\r\n\r\nSELECT * FROM employee WHERE name REGEXP 'on$';\r\n\r\nSELECT * FROM employee WHERE name REGEXP 'm{2}';\r\n\r\n\r\n\u5c0f\u7ed3\uff1a\u5bf9\u5b57\u7b26\u4e32\u5339\u914d\u7684\u65b9\u5f0f\r\nWHERE name = 'egon';\r\nWHERE name LIKE 'yua%';\r\nWHERE name REGEXP 'on$';<\/pre>\n<\/div>\n<p>\u5c0f\u7ec3\u4e60\uff1a<\/p>\n<div class=\"cnblogs_code\">\n<pre>\u67e5\u770b\u6240\u6709\u5458\u5de5\u4e2d\u540d\u5b57\u662fjin\u5f00\u5934\uff0cn\u6216\u8005g\u7ed3\u679c\u7684\u5458\u5de5\u4fe1\u606f<\/pre>\n<\/div>\n<div class=\"cnblogs_code\">\n<div id=\"cnblogs_code_open_3e43e4a0-8f4d-4582-8d14-28f289176834\" class=\"cnblogs_code_hide\">\n<pre>select * from employee where name regexp '^jin.*[gn]$';<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00 \u5355\u8868\u67e5\u8be2\u7684\u8bed\u6cd5 SELECT \u5b57\u6bb51,\u5b57\u6bb52&#8230; FROM \u8868\u540d WHERE \u6761\u4ef6 GROUP BY f [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/11415"}],"collection":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11415"}],"version-history":[{"count":5,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/11415\/revisions"}],"predecessor-version":[{"id":11450,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/11415\/revisions\/11450"}],"wp:attachment":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11415"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11415"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11415"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}