![\"\"](\"https:\/\/egonlin.com\/wp-content\/uploads\/2022\/03\/\u8bb0\u5f55\u76f8\u5173\u64cd\u4f5c1.jpg\")
<\/div><\/p>\n\u4e00\u3001\u4ecb\u7ecd<\/h2>\n
MySQL\u6570\u636e\u64cd\u4f5c\uff1a DML<\/p>\n
========================================================<\/p>\n
\u5728MySQL\u7ba1\u7406\u8f6f\u4ef6\u4e2d\uff0c\u53ef\u4ee5\u901a\u8fc7SQL\u8bed\u53e5\u4e2d\u7684DML\u8bed\u8a00\u6765\u5b9e\u73b0\u6570\u636e\u7684\u64cd\u4f5c\uff0c\u5305\u62ec\n\u4f7f\u7528INSERT\u5b9e\u73b0\u6570\u636e\u7684\u63d2\u5165\nUPDATE\u5b9e\u73b0\u6570\u636e\u7684\u66f4\u65b0\n\u4f7f\u7528DELETE\u5b9e\u73b0\u6570\u636e\u7684\u5220\u9664\n\u4f7f\u7528SELECT\u67e5\u8be2\u6570\u636e\u4ee5\u53ca\u3002<\/code><\/pre>\n========================================================<\/p>\n
\u672c\u8282\u5185\u5bb9\u5305\u62ec\uff1a<\/p>\n
\u63d2\u5165\u6570\u636e \u66f4\u65b0\u6570\u636e \u5220\u9664\u6570\u636e \u67e5\u8be2\u6570\u636e<\/p>\n
\u4e8c\u3001\u63d2\u5165\u6570\u636eINSERT<\/h2>\n1. \u63d2\u5165\u5b8c\u6574\u6570\u636e\uff08\u987a\u5e8f\u63d2\u5165\uff09\n \u8bed\u6cd5\u4e00\uff1a\n INSERT INTO \u8868\u540d(\u5b57\u6bb51,\u5b57\u6bb52,\u5b57\u6bb53\u2026\u5b57\u6bb5n) VALUES(\u503c1,\u503c2,\u503c3\u2026\u503cn);\n\n \u8bed\u6cd5\u4e8c\uff1a\n INSERT INTO \u8868\u540d VALUES (\u503c1,\u503c2,\u503c3\u2026\u503cn);\n\n2. \u6307\u5b9a\u5b57\u6bb5\u63d2\u5165\u6570\u636e\n \u8bed\u6cd5\uff1a\n INSERT INTO \u8868\u540d(\u5b57\u6bb51,\u5b57\u6bb52,\u5b57\u6bb53\u2026) VALUES (\u503c1,\u503c2,\u503c3\u2026);\n\n3. \u63d2\u5165\u591a\u6761\u8bb0\u5f55\n \u8bed\u6cd5\uff1a\n INSERT INTO \u8868\u540d VALUES\n (\u503c1,\u503c2,\u503c3\u2026\u503cn),\n (\u503c1,\u503c2,\u503c3\u2026\u503cn),\n (\u503c1,\u503c2,\u503c3\u2026\u503cn);\n\n4. \u63d2\u5165\u67e5\u8be2\u7ed3\u679c\n \u8bed\u6cd5\uff1a\n INSERT INTO \u8868\u540d(\u5b57\u6bb51,\u5b57\u6bb52,\u5b57\u6bb53\u2026\u5b57\u6bb5n) \n SELECT (\u5b57\u6bb51,\u5b57\u6bb52,\u5b57\u6bb53\u2026\u5b57\u6bb5n) FROM \u88682\n WHERE \u2026;<\/code><\/pre>\n\u4e09\u3001\u66f4\u65b0\u6570\u636eUPDATE<\/h2>\n\u8bed\u6cd5\uff1a\n UPDATE \u8868\u540d SET\n \u5b57\u6bb51=\u503c1,\n \u5b57\u6bb52=\u503c2,\n WHERE CONDITION;\n\n\u793a\u4f8b\uff1a\n UPDATE mysql.user SET password=password(\u2018123\u2019) \n where user=\u2019root\u2019 and host=\u2019localhost\u2019;<\/code><\/pre>\n\u56db\u3001\u5220\u9664\u6570\u636eDELETE<\/h2>\n\u8bed\u6cd5\uff1a\n DELETE FROM \u8868\u540d \n WHERE CONITION;\n\n\u793a\u4f8b\uff1a\n DELETE FROM mysql.user \n WHERE password=\u2019\u2019;\n\n\u7ec3\u4e60\uff1a\n \u66f4\u65b0MySQL root\u7528\u6237\u5bc6\u7801\u4e3amysql123\n \u5220\u9664\u9664\u4ece\u672c\u5730\u767b\u5f55\u7684root\u7528\u6237\u4ee5\u5916\u7684\u6240\u6709\u7528\u6237<\/code><\/pre>\n\u4e94\u3001\u67e5\u8be2\u6570\u636eSELECT-\u5355\u8868\u67e5\u8be2<\/h2>\n1\u3001\u5355\u8868\u67e5\u8be2\u7684\u8bed\u6cd5<\/h3>\nSELECT \u5b57\u6bb51,\u5b57\u6bb52... FROM \u8868\u540d\n WHERE \u6761\u4ef6\n GROUP BY field\n HAVING \u7b5b\u9009\n ORDER BY field\n LIMIT \u9650\u5236\u6761\u6570<\/code><\/pre>\n2\u3001\u5173\u952e\u5b57\u7684\u6267\u884c\u4f18\u5148\u7ea7<\/h3>\n
*\u91cd\u70b9\u4e2d\u7684\u91cd\u70b9*<\/em><\/strong><\/p>\nfrom\nwhere\ngroup by\nhaving\nselect\ndistinct\norder by\nlimit<\/code><\/pre>\n1.\u627e\u5230\u8868:from\n2.\u62ff\u7740where\u6307\u5b9a\u7684\u7ea6\u675f\u6761\u4ef6\uff0c\u53bb\u6587\u4ef6\/\u8868\u4e2d\u53d6\u51fa\u4e00\u6761\u6761\u8bb0\u5f55\n3.\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\n4.\u5c06\u5206\u7ec4\u7684\u7ed3\u679c\u8fdb\u884chaving\u8fc7\u6ee4\n5.\u6267\u884cselect\n6.\u53bb\u91cd\n7.\u5c06\u7ed3\u679c\u6309\u6761\u4ef6\u6392\u5e8f\uff1aorder by\n8.\u9650\u5236\u7ed3\u679c\u7684\u663e\u793a\u6761\u6570<\/code><\/pre>\n3\u3001\u7b80\u5355\u67e5\u8be2<\/h3>\n
\u51c6\u5907\u8868\u548c\u8bb0\u5f55<\/p>\n
company.employee\n \u5458\u5de5id id int \n \u59d3\u540d emp_name varchar\n \u6027\u522b sex enum\n \u5e74\u9f84 age int\n \u5165\u804c\u65e5\u671f hire_date date\n \u5c97\u4f4d post varchar\n \u804c\u4f4d\u63cf\u8ff0 post_comment varchar\n \u85aa\u6c34 salary double\n \u529e\u516c\u5ba4 office int\n \u90e8\u95e8\u7f16\u53f7 depart_id int\n\n#\u521b\u5efa\u8868\ncreate table employee(\nid int not null unique auto_increment,\nname varchar(20) not null,\nsex enum('male','female') not null default 'male', #\u5927\u90e8\u5206\u662f\u7537\u7684\nage int(3) unsigned not null default 28,\nhire_date date not null,\npost varchar(50),\npost_comment varchar(100),\nsalary double(15,2),\noffice int, #\u4e00\u4e2a\u90e8\u95e8\u4e00\u4e2a\u5c4b\u5b50\ndepart_id int\n);\n\n#\u67e5\u770b\u8868\u7ed3\u6784\nmysql> desc employee;\n+--------------+-----------------------+------+-----+---------+----------------+\n| Field | Type | Null | Key | Default | Extra |\n+--------------+-----------------------+------+-----+---------+----------------+\n| id | int(11) | NO | PRI | NULL | auto_increment |\n| name | varchar(20) | NO | | NULL | |\n| sex | enum('male','female') | NO | | male | |\n| age | int(3) unsigned | NO | | 28 | |\n| hire_date | date | NO | | NULL | |\n| post | varchar(50) | YES | | NULL | |\n| post_comment | varchar(100) | YES | | NULL | |\n| salary | double(15,2) | YES | | NULL | |\n| office | int(11) | YES | | NULL | |\n| depart_id | int(11) | YES | | NULL | |\n+--------------+-----------------------+------+-----+---------+----------------+\n\n#\u63d2\u5165\u8bb0\u5f55\n#\u4e09\u4e2a\u90e8\u95e8\uff1a\u6559\u5b66\uff0c\u9500\u552e\uff0c\u8fd0\u8425\ninsert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values\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\n('alex','male',78,'20150302','teacher',1000000.31,401,1),\n('wupeiqi','male',81,'20130305','teacher',8300,401,1),\n('yuanhao','male',73,'20140701','teacher',3500,401,1),\n('liwenzhou','male',28,'20121101','teacher',2100,401,1),\n('jingliyang','female',18,'20110211','teacher',9000,401,1),\n('jinxin','male',18,'19000301','teacher',30000,401,1),\n('\u6210\u9f99','male',48,'20101111','teacher',10000,401,1),\n\n('\u6b6a\u6b6a','female',48,'20150311','sale',3000.13,402,2),#\u4ee5\u4e0b\u662f\u9500\u552e\u90e8\u95e8\n('\u4e2b\u4e2b','female',38,'20101101','sale',2000.35,402,2),\n('\u4e01\u4e01','female',18,'20110312','sale',1000.37,402,2),\n('\u661f\u661f','female',18,'20160513','sale',3000.29,402,2),\n('\u683c\u683c','female',28,'20170127','sale',4000.33,402,2),\n\n('\u5f20\u91ce','male',28,'20160311','operation',10000.13,403,3), #\u4ee5\u4e0b\u662f\u8fd0\u8425\u90e8\u95e8\n('\u7a0b\u54ac\u91d1','male',18,'19970312','operation',20000,403,3),\n('\u7a0b\u54ac\u94f6','female',18,'20130311','operation',19000,403,3),\n('\u7a0b\u54ac\u94dc','male',18,'20150411','operation',18000,403,3),\n('\u7a0b\u54ac\u94c1','female',18,'20140512','operation',17000,403,3)\n;\n\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<\/code><\/pre>\n#\u7b80\u5355\u67e5\u8be2<\/p>\n
SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id \n FROM employee;\n\n SELECT * FROM employee;\n\n SELECT name,salary FROM employee;\n\n#\u907f\u514d\u91cd\u590dDISTINCT\n SELECT DISTINCT post FROM employee; \n\n#\u901a\u8fc7\u56db\u5219\u8fd0\u7b97\u67e5\u8be2\n SELECT name, salary*12 FROM employee;\n SELECT name, salary*12 AS Annual_salary FROM employee;\n SELECT name, salary*12 Annual_salary FROM employee;\n\n#\u5b9a\u4e49\u663e\u793a\u683c\u5f0f\n CONCAT() \u51fd\u6570\u7528\u4e8e\u8fde\u63a5\u5b57\u7b26\u4e32\n SELECT CONCAT('\u59d3\u540d: ',name,' \u5e74\u85aa: ', salary*12) AS Annual_salary \n FROM employee;\n\n CONCAT_WS() \u7b2c\u4e00\u4e2a\u53c2\u6570\u4e3a\u5206\u9694\u7b26\n SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary \n FROM employee;\n\n \u7ed3\u5408CASE\u8bed\u53e5\uff1a\n SELECT\n (\n CASE\n WHEN NAME = 'egon' THEN\n NAME\n WHEN NAME = 'alex' THEN\n CONCAT(name,'_BIGSB')\n ELSE\n concat(NAME, 'SB')\n END\n ) as new_name\n FROM\n emp;<\/code><\/pre>\n\u5c0f\u7ec3\u4e60\uff1a<\/p>\n
1 \u67e5\u51fa\u6240\u6709\u5458\u5de5\u7684\u540d\u5b57\uff0c\u85aa\u8d44,\u683c\u5f0f\u4e3a\n <\u540d\u5b57:egon> <\u85aa\u8d44:3000>\n2 \u67e5\u51fa\u6240\u6709\u7684\u5c97\u4f4d\uff08\u53bb\u6389\u91cd\u590d\uff09\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<\/code><\/pre>\n\u7b54\u6848:<\/p>\n
select concat(‘<\u540d\u5b57:’,name,’> ‘,'<\u85aa\u8d44:’,salary,’>’) from employee;<\/p>\n
select distinct depart_id from employee;<\/p>\n
select name,salary*12 annual_salary from employee;<\/p>\n
4\u3001WHERE\u7ea6\u675f<\/h3>\n
where\u5b57\u53e5\u4e2d\u53ef\u4ee5\u4f7f\u7528\uff1a<\/p>\n
1. \u6bd4\u8f83\u8fd0\u7b97\u7b26\uff1a> < >= <= <> !=\n2. between 80 and 100 \u503c\u572810\u523020\u4e4b\u95f4\n3. in(80,90,100) \u503c\u662f10\u621620\u621630\n4. like 'egon%'\n pattern\u53ef\u4ee5\u662f%\u6216_\uff0c\n %\u8868\u793a\u4efb\u610f\u591a\u5b57\u7b26\n _\u8868\u793a\u4e00\u4e2a\u5b57\u7b26\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\n\n1:\u5355\u6761\u4ef6\u67e5\u8be2\n SELECT name FROM employee\n WHERE post='sale';\n\n#2:\u591a\u6761\u4ef6\u67e5\u8be2\n SELECT name,salary FROM employee\n WHERE post='teacher' AND salary>10000;\n\n#3:\u5173\u952e\u5b57BETWEEN AND\n SELECT name,salary FROM employee \n WHERE salary BETWEEN 10000 AND 20000;\n\n SELECT name,salary FROM employee \n WHERE salary NOT BETWEEN 10000 AND 20000;\n\n#4:\u5173\u952e\u5b57IS NULL(\u5224\u65ad\u67d0\u4e2a\u5b57\u6bb5\u662f\u5426\u4e3aNULL\u4e0d\u80fd\u7528\u7b49\u53f7\uff0c\u9700\u8981\u7528IS)\n SELECT name,post_comment FROM employee \n WHERE post_comment IS NULL;\n\n SELECT name,post_comment FROM employee \n WHERE post_comment IS NOT NULL;\n\n SELECT name,post_comment FROM employee \n WHERE post_comment=''; \u6ce8\u610f''\u662f\u7a7a\u5b57\u7b26\u4e32\uff0c\u4e0d\u662fnull\n ps\uff1a\n \u6267\u884c\n update employee set post_comment='' where id=2;\n \u518d\u7528\u4e0a\u6761\u67e5\u770b\uff0c\u5c31\u4f1a\u6709\u7ed3\u679c\u4e86\n\n#5:\u5173\u952e\u5b57IN\u96c6\u5408\u67e5\u8be2\n SELECT name,salary FROM employee \n WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;\n\n SELECT name,salary FROM employee \n WHERE salary IN (3000,3500,4000,9000) ;\n\n SELECT name,salary FROM employee \n WHERE salary NOT IN (3000,3500,4000,9000) ;\n\n#6:\u5173\u952e\u5b57LIKE\u6a21\u7cca\u67e5\u8be2\n \u901a\u914d\u7b26\u2019%\u2019\n SELECT * FROM employee \n WHERE name LIKE 'eg%';\n\n \u901a\u914d\u7b26\u2019_\u2019\n SELECT * FROM employee \n WHERE name LIKE 'al__';<\/code><\/pre>\n\u5c0f\u7ec3\u4e60\uff1a<\/p>\n
1. \u67e5\u770b\u5c97\u4f4d\u662fteacher\u7684\u5458\u5de5\u59d3\u540d\u3001\u5e74\u9f84\n2. \u67e5\u770b\u5c97\u4f4d\u662fteacher\u4e14\u5e74\u9f84\u5927\u4e8e30\u5c81\u7684\u5458\u5de5\u59d3\u540d\u3001\u5e74\u9f84\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\n4. \u67e5\u770b\u5c97\u4f4d\u63cf\u8ff0\u4e0d\u4e3aNULL\u7684\u5458\u5de5\u4fe1\u606f\n5. \u67e5\u770b\u5c97\u4f4d\u662fteacher\u4e14\u85aa\u8d44\u662f10000\u62169000\u621630000\u7684\u5458\u5de5\u59d3\u540d\u3001\u5e74\u9f84\u3001\u85aa\u8d44\n6. \u67e5\u770b\u5c97\u4f4d\u662fteacher\u4e14\u85aa\u8d44\u4e0d\u662f10000\u62169000\u621630000\u7684\u5458\u5de5\u59d3\u540d\u3001\u5e74\u9f84\u3001\u85aa\u8d44\n7. \u67e5\u770b\u5c97\u4f4d\u662fteacher\u4e14\u540d\u5b57\u662fjin\u5f00\u5934\u7684\u5458\u5de5\u59d3\u540d\u3001\u5e74\u85aa<\/code><\/pre>\nselect name,age from employee where post = 'teacher';\nselect name,age from employee where post='teacher' and age > 30; \nselect name,age,salary from employee where post='teacher' and salary between 9000 and 10000;\nselect * from employee where post_comment is not null;\nselect name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);\nselect name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);\nselect name,salary*12 from employee where post='teacher' and name like 'jin%';<\/code><\/pre>\n5\u3001\u5206\u7ec4\u67e5\u8be2:GROUP BY<\/h3>\n
\u4ec0\u4e48\u662f\u5206\u7ec4\uff1f\u4e3a\u4ec0\u4e48\u8981\u5206\u7ec4\uff1f<\/p>\n
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<\/p>\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<\/p>\n
3\u3001\u4e3a\u4f55\u8981\u5206\u7ec4\u5462\uff1f<\/p>\n
\u53d6\u6bcf\u4e2a\u90e8\u95e8\u7684\u6700\u9ad8\u5de5\u8d44<\/p>\n
\u53d6\u6bcf\u4e2a\u90e8\u95e8\u7684\u5458\u5de5\u6570<\/p>\n
\u53d6\u7537\u4eba\u6570\u548c\u5973\u4eba\u6570<\/p>\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<\/p>\n
4\u3001\u5927\u524d\u63d0\uff1a<\/p>\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\u51fd\u6570<\/p>\n
ONLY_FULL_GROUP_BY<\/p>\n
\uff01\uff01\uff01SQL_MODE\u8bbe\u7f6e\uff01\uff01\uff01<\/p>\n
mysql> select @@global.sql_mode;\n+-------------------+\n| @@global.sql_mode |\n+-------------------+\n| |\n+-------------------+\n1 row in set (0.00 sec)\n\nmysql> select * from emp group by post; \n+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+\n| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |\n+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+\n| 14 | \u5f20\u91ce | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |\n| 9 | \u6b6a\u6b6a | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |\n| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |\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 |\n+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+\n4 rows in set (0.00 sec)\n\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\n\nmysql> set global sql_mode='ONLY_FULL_GROUP_BY';\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> quit #\u8bbe\u7f6e\u6210\u529f\u540e\uff0c\u4e00\u5b9a\u8981\u9000\u51fa\uff0c\u7136\u540e\u91cd\u65b0\u767b\u5f55\u65b9\u53ef\u751f\u6548\nBye\n\nmysql> use db1;\nDatabase changed\nmysql> select * from emp group by post; #\u62a5\u9519\nERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY\nmysql> select post,count(id) from emp group by post; #\u53ea\u80fd\u67e5\u770b\u5206\u7ec4\u4f9d\u636e\u548c\u4f7f\u7528\u805a\u5408\u51fd\u6570\n+----------------------------+-----------+\n| post | count(id) |\n+----------------------------+-----------+\n| operation | 5 |\n| sale | 5 |\n| teacher | 7 |\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f | 1 |\n+----------------------------+-----------+\n4 rows in set (0.00 sec)<\/code><\/pre>\nGROUP BY<\/p>\n
\u5355\u72ec\u4f7f\u7528GROUP BY\u5173\u952e\u5b57\u5206\u7ec4\n SELECT post FROM employee GROUP BY post;\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\n\nGROUP BY\u5173\u952e\u5b57\u548cGROUP_CONCAT()\u51fd\u6570\u4e00\u8d77\u4f7f\u7528\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\n SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;\n\nGROUP BY\u4e0e\u805a\u5408\u51fd\u6570\u4e00\u8d77\u4f7f\u7528\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<\/code><\/pre>\n\u5f3a\u8c03\uff1a<\/p>\n
\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<\/p>\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<\/p>\n
\u56db \u805a\u5408\u51fd\u6570<\/p>\n
\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<\/p>\n
\u793a\u4f8b\uff1a<\/p>\n
SELECT COUNT(*) FROM employee;\n SELECT COUNT(*) FROM employee WHERE depart_id=1;\n SELECT MAX(salary) FROM employee;\n SELECT MIN(salary) FROM employee;\n SELECT AVG(salary) FROM employee;\n SELECT SUM(salary) FROM employee;\n SELECT SUM(salary) FROM employee WHERE depart_id=3;<\/code><\/pre>\n\u5c0f\u7ec3\u4e60\uff1a<\/p>\n
\\1. \u67e5\u8be2\u5c97\u4f4d\u540d\u4ee5\u53ca\u5c97\u4f4d\u5305\u542b\u7684\u6240\u6709\u5458\u5de5\u540d\u5b57<\/p>\n
\\2. \u67e5\u8be2\u5c97\u4f4d\u540d\u4ee5\u53ca\u5404\u5c97\u4f4d\u5185\u5305\u542b\u7684\u5458\u5de5\u4e2a\u6570<\/p>\n
\\3. \u67e5\u8be2\u516c\u53f8\u5185\u7537\u5458\u5de5\u548c\u5973\u5458\u5de5\u7684\u4e2a\u6570<\/p>\n
\\4. \u67e5\u8be2\u5c97\u4f4d\u540d\u4ee5\u53ca\u5404\u5c97\u4f4d\u7684\u5e73\u5747\u85aa\u8d44<\/p>\n
\\5. \u67e5\u8be2\u5c97\u4f4d\u540d\u4ee5\u53ca\u5404\u5c97\u4f4d\u7684\u6700\u9ad8\u85aa\u8d44<\/p>\n
\\6. \u67e5\u8be2\u5c97\u4f4d\u540d\u4ee5\u53ca\u5404\u5c97\u4f4d\u7684\u6700\u4f4e\u85aa\u8d44<\/p>\n
\\7. \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<\/p>\n
#\u98981\uff1a\u5206\u7ec4\nmysql> select post,group_concat(name) from employee group by post;\n+-----------------------------------------+---------------------------------------------------------+\n| post | group_concat(name) |\n+-----------------------------------------+---------------------------------------------------------+\n| operation | \u5f20\u91ce,\u7a0b\u54ac\u91d1,\u7a0b\u54ac\u94f6,\u7a0b\u54ac\u94dc,\u7a0b\u54ac\u94c1 |\n| sale | \u6b6a\u6b6a,\u4e2b\u4e2b,\u4e01\u4e01,\u661f\u661f,\u683c\u683c |\n| teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,\u6210\u9f99 |\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f | egon |\n+-----------------------------------------+---------------------------------------------------------+\n\n#\u9898\u76ee2\uff1a\nmysql> select post,count(id) from employee group by post;\n+-----------------------------------------+-----------+\n| post | count(id) |\n+-----------------------------------------+-----------+\n| operation | 5 |\n| sale | 5 |\n| teacher | 7 |\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f | 1 |\n+-----------------------------------------+-----------+\n\n#\u9898\u76ee3\uff1a\nmysql> select sex,count(id) from employee group by sex;\n+--------+-----------+\n| sex | count(id) |\n+--------+-----------+\n| male | 10 |\n| female | 8 |\n+--------+-----------+\n\n#\u9898\u76ee4\uff1a\nmysql> select post,avg(salary) from employee group by post;\n+-----------------------------------------+---------------+\n| post | avg(salary) |\n+-----------------------------------------+---------------+\n| operation | 16800.026000 |\n| sale | 2600.294000 |\n| teacher | 151842.901429 |\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f | 7300.330000 |\n+-----------------------------------------+---------------+\n\n#\u9898\u76ee5\nmysql> select post,max(salary) from employee group by post;\n+-----------------------------------------+-------------+\n| post | max(salary) |\n+-----------------------------------------+-------------+\n| operation | 20000.00 |\n| sale | 4000.33 |\n| teacher | 1000000.31 |\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f | 7300.33 |\n+-----------------------------------------+-------------+\n\n#\u9898\u76ee6\nmysql> select post,min(salary) from employee group by post;\n+-----------------------------------------+-------------+\n| post | min(salary) |\n+-----------------------------------------+-------------+\n| operation | 10000.13 |\n| sale | 1000.37 |\n| teacher | 2100.00 |\n| \u8001\u7537\u5b69\u9a7b\u6c99\u6cb3\u529e\u4e8b\u5904\u5916\u4ea4\u5927\u4f7f | 7300.33 |\n+-----------------------------------------+-------------+\n\n#\u9898\u76ee\u4e03\nmysql> select sex,avg(salary) from employee group by sex;\n+--------+---------------+\n| sex | avg(salary) |\n+--------+---------------+\n| male | 110920.077000 |\n| female | 7250.183750 |\n+--------+---------------+<\/code><\/pre>\n\u516d\u3001\u591a\u8868\u67e5\u8be2<\/h2>\n1\u3001\u51c6\u5907\u8868<\/h3>\n#\u5efa\u8868\ncreate table department(\nid int,\nname varchar(20) \n);\n\ncreate table employee(\nid int primary key auto_increment,\nname varchar(20),\nsex enum('male','female') not null default 'male',\nage int,\ndep_id int\n);\n\n#\u63d2\u5165\u6570\u636e\ninsert into department values\n(200,'\u6280\u672f'),\n(201,'\u4eba\u529b\u8d44\u6e90'),\n(202,'\u9500\u552e'),\n(203,'\u8fd0\u8425');\n\ninsert into employee(name,sex,age,dep_id) values\n('egon','male',18,200),\n('alex','female',48,201),\n('wupeiqi','male',38,201),\n('yuanhao','female',28,202),\n('liwenzhou','male',18,200),\n('jingliyang','female',18,204)\n;\n\n#\u67e5\u770b\u8868\u7ed3\u6784\u548c\u6570\u636e\nmysql> desc department;\n+-------+-------------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+-------------+------+-----+---------+-------+\n| id | int(11) | YES | | NULL | |\n| name | varchar(20) | YES | | NULL | |\n+-------+-------------+------+-----+---------+-------+\n\nmysql> desc employee;\n+--------+-----------------------+------+-----+---------+----------------+\n| Field | Type | Null | Key | Default | Extra |\n+--------+-----------------------+------+-----+---------+----------------+\n| id | int(11) | NO | PRI | NULL | auto_increment |\n| name | varchar(20) | YES | | NULL | |\n| sex | enum('male','female') | NO | | male | |\n| age | int(11) | YES | | NULL | |\n| dep_id | int(11) | YES | | NULL | |\n+--------+-----------------------+------+-----+---------+----------------+\n\nmysql> select * from department;\n+------+--------------+\n| id | name |\n+------+--------------+\n| 200 | \u6280\u672f |\n| 201 | \u4eba\u529b\u8d44\u6e90 |\n| 202 | \u9500\u552e |\n| 203 | \u8fd0\u8425 |\n+------+--------------+\n\nmysql> select * from employee;\n+----+------------+--------+------+--------+\n| id | name | sex | age | dep_id |\n+----+------------+--------+------+--------+\n| 1 | egon | male | 18 | 200 |\n| 2 | alex | female | 48 | 201 |\n| 3 | wupeiqi | male | 38 | 201 |\n| 4 | yuanhao | female | 28 | 202 |\n| 5 | liwenzhou | male | 18 | 200 |\n| 6 | jingliyang | female | 18 | 204 |\n+----+------------+--------+------+--------+<\/code><\/pre>\n2\u3001\u591a\u8868\u8fde\u63a5\u67e5\u8be2<\/h3>\n
#\u91cd\u70b9\uff1a\u5916\u94fe\u63a5\u8bed\u6cd5<\/p>\n
SELECT \u5b57\u6bb5\u5217\u8868\n FROM \u88681 INNER|LEFT|RIGHT JOIN \u88682\n ON \u88681.\u5b57\u6bb5 = \u88682.\u5b57\u6bb5;\n1 \u4ea4\u53c9\u8fde\u63a5\uff1a\u4e0d\u9002\u7528\u4efb\u4f55\u5339\u914d\u6761\u4ef6\u3002\u751f\u6210\u7b1b\u5361\u5c14\u79ef\n\nmysql> select * from employee,department;\n+----+------------+--------+------+--------+------+--------------+\n| id | name | sex | age | dep_id | id | name |\n+----+------------+--------+------+--------+------+--------------+\n| 1 | egon | male | 18 | 200 | 200 | \u6280\u672f |\n| 1 | egon | male | 18 | 200 | 201 | \u4eba\u529b\u8d44\u6e90 |\n| 1 | egon | male | 18 | 200 | 202 | \u9500\u552e |\n| 1 | egon | male | 18 | 200 | 203 | \u8fd0\u8425 |\n| 2 | alex | female | 48 | 201 | 200 | \u6280\u672f |\n| 2 | alex | female | 48 | 201 | 201 | \u4eba\u529b\u8d44\u6e90 |\n| 2 | alex | female | 48 | 201 | 202 | \u9500\u552e |\n| 2 | alex | female | 48 | 201 | 203 | \u8fd0\u8425 |\n| 3 | wupeiqi | male | 38 | 201 | 200 | \u6280\u672f |\n| 3 | wupeiqi | male | 38 | 201 | 201 | \u4eba\u529b\u8d44\u6e90 |\n| 3 | wupeiqi | male | 38 | 201 | 202 | \u9500\u552e |\n| 3 | wupeiqi | male | 38 | 201 | 203 | \u8fd0\u8425 |\n| 4 | yuanhao | female | 28 | 202 | 200 | \u6280\u672f |\n| 4 | yuanhao | female | 28 | 202 | 201 | \u4eba\u529b\u8d44\u6e90 |\n| 4 | yuanhao | female | 28 | 202 | 202 | \u9500\u552e |\n| 4 | yuanhao | female | 28 | 202 | 203 | \u8fd0\u8425 |\n| 5 | liwenzhou | male | 18 | 200 | 200 | \u6280\u672f |\n| 5 | liwenzhou | male | 18 | 200 | 201 | \u4eba\u529b\u8d44\u6e90 |\n| 5 | liwenzhou | male | 18 | 200 | 202 | \u9500\u552e |\n| 5 | liwenzhou | male | 18 | 200 | 203 | \u8fd0\u8425 |\n| 6 | jingliyang | female | 18 | 204 | 200 | \u6280\u672f |\n| 6 | jingliyang | female | 18 | 204 | 201 | \u4eba\u529b\u8d44\u6e90 |\n| 6 | jingliyang | female | 18 | 204 | 202 | \u9500\u552e |\n| 6 | jingliyang | female | 18 | 204 | 203 | \u8fd0\u8425 |\n+----+------------+--------+------+--------+------+--------------+<\/code><\/pre>\n2 \u5185\u8fde\u63a5\uff1a\u53ea\u8fde\u63a5\u5339\u914d\u7684\u884c\n\n#\u627e\u4e24\u5f20\u8868\u5171\u6709\u7684\u90e8\u5206\uff0c\u76f8\u5f53\u4e8e\u5229\u7528\u6761\u4ef6\u4ece\u7b1b\u5361\u5c14\u79ef\u7ed3\u679c\u4e2d\u7b5b\u9009\u51fa\u4e86\u6b63\u786e\u7684\u7ed3\u679c\n#department\u6ca1\u6709204\u8fd9\u4e2a\u90e8\u95e8\uff0c\u56e0\u800cemployee\u8868\u4e2d\u5173\u4e8e204\u8fd9\u6761\u5458\u5de5\u4fe1\u606f\u6ca1\u6709\u5339\u914d\u51fa\u6765\nmysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; \n+----+-----------+------+--------+--------------+\n| id | name | age | sex | name |\n+----+-----------+------+--------+--------------+\n| 1 | egon | 18 | male | \u6280\u672f |\n| 2 | alex | 48 | female | \u4eba\u529b\u8d44\u6e90 |\n| 3 | wupeiqi | 38 | male | \u4eba\u529b\u8d44\u6e90 |\n| 4 | yuanhao | 28 | female | \u9500\u552e |\n| 5 | liwenzhou | 18 | male | \u6280\u672f |\n+----+-----------+------+--------+--------------+\n\n#\u4e0a\u8ff0sql\u7b49\u540c\u4e8e\nmysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;<\/code><\/pre>\n3 \u5916\u94fe\u63a5\u4e4b\u5de6\u8fde\u63a5\uff1a\u4f18\u5148\u663e\u793a\u5de6\u8868\u5168\u90e8\u8bb0\u5f55\n\n#\u4ee5\u5de6\u8868\u4e3a\u51c6\uff0c\u5373\u627e\u51fa\u6240\u6709\u5458\u5de5\u4fe1\u606f\uff0c\u5f53\u7136\u5305\u62ec\u6ca1\u6709\u90e8\u95e8\u7684\u5458\u5de5\n#\u672c\u8d28\u5c31\u662f\uff1a\u5728\u5185\u8fde\u63a5\u7684\u57fa\u7840\u4e0a\u589e\u52a0\u5de6\u8fb9\u6709\u53f3\u8fb9\u6ca1\u6709\u7684\u7ed3\u679c\nmysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;\n+----+------------+--------------+\n| id | name | depart_name |\n+----+------------+--------------+\n| 1 | egon | \u6280\u672f |\n| 5 | liwenzhou | \u6280\u672f |\n| 2 | alex | \u4eba\u529b\u8d44\u6e90 |\n| 3 | wupeiqi | \u4eba\u529b\u8d44\u6e90 |\n| 4 | yuanhao | \u9500\u552e |\n| 6 | jingliyang | NULL |\n+----+------------+--------------+\n\n4 \u5916\u94fe\u63a5\u4e4b\u53f3\u8fde\u63a5\uff1a\u4f18\u5148\u663e\u793a\u53f3\u8868\u5168\u90e8\u8bb0\u5f55\n\n#\u4ee5\u53f3\u8868\u4e3a\u51c6\uff0c\u5373\u627e\u51fa\u6240\u6709\u90e8\u95e8\u4fe1\u606f\uff0c\u5305\u62ec\u6ca1\u6709\u5458\u5de5\u7684\u90e8\u95e8\n#\u672c\u8d28\u5c31\u662f\uff1a\u5728\u5185\u8fde\u63a5\u7684\u57fa\u7840\u4e0a\u589e\u52a0\u53f3\u8fb9\u6709\u5de6\u8fb9\u6ca1\u6709\u7684\u7ed3\u679c\nmysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;\n+------+-----------+--------------+\n| id | name | depart_name |\n+------+-----------+--------------+\n| 1 | egon | \u6280\u672f |\n| 2 | alex | \u4eba\u529b\u8d44\u6e90 |\n| 3 | wupeiqi | \u4eba\u529b\u8d44\u6e90 |\n| 4 | yuanhao | \u9500\u552e |\n| 5 | liwenzhou | \u6280\u672f |\n| NULL | NULL | \u8fd0\u8425 |\n+------+-----------+--------------+\n\n5 \u5168\u5916\u8fde\u63a5\uff1a\u663e\u793a\u5de6\u53f3\u4e24\u4e2a\u8868\u5168\u90e8\u8bb0\u5f55\n\n\u5168\u5916\u8fde\u63a5\uff1a\u5728\u5185\u8fde\u63a5\u7684\u57fa\u7840\u4e0a\u589e\u52a0\u5de6\u8fb9\u6709\u53f3\u8fb9\u6ca1\u6709\u7684\u548c\u53f3\u8fb9\u6709\u5de6\u8fb9\u6ca1\u6709\u7684\u7ed3\u679c\n#\u6ce8\u610f\uff1amysql\u4e0d\u652f\u6301\u5168\u5916\u8fde\u63a5 full JOIN\n#\u5f3a\u8c03\uff1amysql\u53ef\u4ee5\u4f7f\u7528\u6b64\u79cd\u65b9\u5f0f\u95f4\u63a5\u5b9e\u73b0\u5168\u5916\u8fde\u63a5\nselect * from employee left join department on employee.dep_id = department.id\nunion\nselect * from employee right join department on employee.dep_id = department.id\n;\n#\u67e5\u770b\u7ed3\u679c\n+------+------------+--------+------+--------+------+--------------+\n| id | name | sex | age | dep_id | id | name |\n+------+------------+--------+------+--------+------+--------------+\n| 1 | egon | male | 18 | 200 | 200 | \u6280\u672f |\n| 5 | liwenzhou | male | 18 | 200 | 200 | \u6280\u672f |\n| 2 | alex | female | 48 | 201 | 201 | \u4eba\u529b\u8d44\u6e90 |\n| 3 | wupeiqi | male | 38 | 201 | 201 | \u4eba\u529b\u8d44\u6e90 |\n| 4 | yuanhao | female | 28 | 202 | 202 | \u9500\u552e |\n| 6 | jingliyang | female | 18 | 204 | NULL | NULL |\n| NULL | NULL | NULL | NULL | NULL | 203 | \u8fd0\u8425 |\n+------+------------+--------+------+--------+------+--------------+\n\n#\u6ce8\u610f union\u4e0eunion all\u7684\u533a\u522b\uff1aunion\u4f1a\u53bb\u6389\u76f8\u540c\u7684\u7eaa\u5f55\n<\/code><\/pre>\n3\u3001\u7b26\u5408\u6761\u4ef6\u8fde\u63a5\u67e5\u8be2<\/h3>\n#\u793a\u4f8b1\uff1a\u4ee5\u5185\u8fde\u63a5\u7684\u65b9\u5f0f\u67e5\u8be2employee\u548cdepartment\u8868\uff0c\u5e76\u4e14employee\u8868\u4e2d\u7684age\u5b57\u6bb5\u503c\u5fc5\u987b\u5927\u4e8e25,\u5373\u627e\u51fa\u5e74\u9f84\u5927\u4e8e25\u5c81\u7684\u5458\u5de5\u4ee5\u53ca\u5458\u5de5\u6240\u5728\u7684\u90e8\u95e8\nselect employee.name,department.name from employee inner join department\n on employee.dep_id = department.id\n where age > 25;\n\n#\u793a\u4f8b2\uff1a\u4ee5\u5185\u8fde\u63a5\u7684\u65b9\u5f0f\u67e5\u8be2employee\u548cdepartment\u8868\uff0c\u5e76\u4e14\u4ee5age\u5b57\u6bb5\u7684\u5347\u5e8f\u65b9\u5f0f\u663e\u793a\nselect employee.id,employee.name,employee.age,department.name from employee,department\n where employee.dep_id = department.id\n and age > 25\n order by age asc;<\/code><\/pre>\n4\u3001\u5b50\u67e5\u8be2<\/h3>\n#1\uff1a\u5b50\u67e5\u8be2\u662f\u5c06\u4e00\u4e2a\u67e5\u8be2\u8bed\u53e5\u5d4c\u5957\u5728\u53e6\u4e00\u4e2a\u67e5\u8be2\u8bed\u53e5\u4e2d\u3002\n#2\uff1a\u5185\u5c42\u67e5\u8be2\u8bed\u53e5\u7684\u67e5\u8be2\u7ed3\u679c\uff0c\u53ef\u4ee5\u4e3a\u5916\u5c42\u67e5\u8be2\u8bed\u53e5\u63d0\u4f9b\u67e5\u8be2\u6761\u4ef6\u3002\n#3\uff1a\u5b50\u67e5\u8be2\u4e2d\u53ef\u4ee5\u5305\u542b\uff1aIN\u3001NOT IN\u3001ANY\u3001ALL\u3001EXISTS \u548c NOT EXISTS\u7b49\u5173\u952e\u5b57\n#4\uff1a\u8fd8\u53ef\u4ee5\u5305\u542b\u6bd4\u8f83\u8fd0\u7b97\u7b26\uff1a= \u3001 !=\u3001> \u3001<\u7b49\n# \u5e26IN\u5173\u952e\u5b57\u7684\u5b50\u67e5\u8be2\n\u00a0#\u67e5\u8be2\u5e73\u5747\u5e74\u9f84\u572825\u5c81\u4ee5\u4e0a\u7684\u90e8\u95e8\u540d\nselect id,name from department\n where id in \n (select dep_id from employee group by dep_id having avg(age) > 25);\n\n#\u67e5\u770b\u6280\u672f\u90e8\u5458\u5de5\u59d3\u540d\nselect name from employee\n where dep_id in \n (select id from department where name='\u6280\u672f');\n\n#\u67e5\u770b\u4e0d\u8db31\u4eba\u7684\u90e8\u95e8\u540d(\u5b50\u67e5\u8be2\u5f97\u5230\u7684\u662f\u6709\u4eba\u7684\u90e8\u95e8id)\nselect name from department where id not in (select distinct dep_id from employee);<\/code><\/pre>\n![\"\"](\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\")
<\/div><\/p>\n","protected":false},"excerpt":{"rendered":"\u8bb0\u5f55\u76f8\u5173\u64cd\u4f5c \u4e00\u3001\u4ecb\u7ecd MySQL\u6570\u636e\u64cd\u4f5c\uff1a DML ============================ […]<\/p>\n","protected":false},"author":3,"featured_media":6746,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[3,403],"tags":[],"_links":{"self":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/4893"}],"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=4893"}],"version-history":[{"count":0,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/4893\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/media\/6746"}],"wp:attachment":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4893"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4893"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4893"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/egonlin.com\/wp-content\/uploads\/2022\/03\/wps7.jpg\")
<\/div><\/p>\n","protected":false},"excerpt":{"rendered":"