{"id":1613,"date":"2020-11-24T21:03:44","date_gmt":"2020-11-24T13:03:44","guid":{"rendered":"https:\/\/www.yusian.com\/blog\/?p=1613"},"modified":"2020-11-24T21:03:44","modified_gmt":"2020-11-24T13:03:44","slug":"mysql%e4%b8%ad%e5%a4%9a%e8%a1%a8%e6%9f%a5%e8%af%a2%e7%9a%84%e4%b8%80%e8%88%ac%e6%93%8d%e4%bd%9c%e6%b1%87%e6%80%bb","status":"publish","type":"post","link":"https:\/\/www.yusian.com\/blog\/database\/2020\/11\/24\/2103441613.html","title":{"rendered":"MySQL\u4e2d\u591a\u8868\u67e5\u8be2\u7684\u4e00\u822c\u64cd\u4f5c\u6c47\u603b"},"content":{"rendered":"<pre><code class=\"language-sql line-numbers\">\/****************************\u521b\u5efa\u8868\u3001\u63d2\u5165\u6570\u636e**********************************\/\n\nCREATE TABLE department (\n    id INT PRIMARY KEY auto_increment,\n    dept_name VARCHAR ( 32 ) \n) DEFAULT charset = utf8mb4;\n\nCREATE TABLE employee (\n    id INT PRIMARY KEY auto_increment,\n    username VARCHAR ( 32 ),\n    age INT,\n    gender VARCHAR ( 2 ),\n    dept_id INT,\nFOREIGN KEY ( dept_id ) REFERENCES department ( id ) \n) DEFAULT charset = utf8mb4;\n\nINSERT INTO department VALUES (1,'\u7814\u53d1\u90e8'),(2,'\u9500\u552e\u90e8'),(3,'\u8d22\u52a1\u90e8');\n\nSELECT*FROM department;\n\nINSERT INTO employee VALUES (1,'\u5f20\u4e09',18,'\u7537',1),(2,'\u674e\u56db',18,'\u7537',1),(3,'\u738b\u4e94',18,'\u7537',2),(4,'\u8d75\u516d',18,'\u5973',3),(5,'\u7530\u4e03',18,'\u7537',2);\n\n\/****************************\u591a\u8868\u67e5\u8be2**********************************\/\n\nSELECT*FROM employee,department;-- \u76f4\u63a5\u67e5\u8be2\u591a\u5f20\u8868\u5f97\u5230\u7684\u7ed3\u679c\u662f\u591a\u8868\u7684\u7b1b\u5361\u5c14\u79ef\uff0c\u5373 M x N\n\n\/*\n    \u591a\u8868\u67e5\u8be2\u7684\u5206\u7c7b\uff1a\n        1\u3001\u5185\u8fde\u63a5\u67e5\u8be2\n        2\u3001\u5916\u8fde\u63a5\u67e5\u8be2\n        3\u3001\u5b50\u67e5\u8be2\n*\/\n# 1\u3001\u5185\u8fde\u63a5\u67e5\u8be2\n-- \u9690\u5f0f\u5185\u8fde\u63a5\u67e5\u8be2\uff0c\u4f7f\u7528where\u6761\u4ef6\u6765\u8fc7\u6ee4\u4e0d\u5fc5\u8981\u7684\u6570\u636e\nSELECT*FROM employee,department WHERE employee.dept_id=department.id;-- \u901a\u8fc7where\u8bed\u53e5\u6d88\u9664\u91cd\u590d\u6570\u636e\n\n-- \u591a\u8868\u67e5\u8be2\u4e00\u822c\u4f1a\u6307\u5b9a\u5b57\u6bb5\nSELECT employee.id,employee.username,employee.gender,department.dept_name FROM employee,department WHERE employee.dept_id=department.id;\n-- \u663e\u5f0f\u5185\u8fde\u63a5\nSELECT*FROM employee INNER JOIN department WHERE employee.dept_id=department.id;\n\n-- \u53ef\u4ee5\u4e3a\u8868\u5b9a\u4e49\u522b\u540d\u66f4\u65b9\u4fbf\u4e66\u5199\nSELECT t1.id,-- \u5458\u5de5\u8868id\nt1.username,-- \u5458\u5de5\u8868\u59d3\u540d\nt1.gender,-- \u5458\u5de5\u8868\u6027\u522b\nt2.dept_name-- \u90e8\u95e8\u8868\u540d\u79f0\nFROM employee t1,department t2 WHERE t1.dept_id=t2.id;\n\n-- \u5916\u8fde\u63a5\uff1a\u5de6\u5916\u53f3\u5916\nSELECT t1.*,t2.dept_name,t2.dept_loc FROM employee t1 LEFT OUTER JOIN department t2 ON t1.dept_id=t2.id;\n\n-- \u5185\u8fde\u63a5\n# \u5355\u884c\u5355\u5217\nSELECT*FROM employee WHERE mgr=(SELECT id FROM employee WHERE ename='\u5218\u660e');\nSELECT*FROM employee WHERE salary&gt; (SELECT avg(salary) FROM employee);\n# \u591a\u884c\u5355\u5217\nSELECT*FROM employee WHERE mgr IN (SELECT id FROM employee WHERE mgr=12);\n# \u591a\u884c\u591a\u5217\uff0c\u591a\u884c\u591a\u5217\u53ef\u7b49\u6548\u4e8e\u5b50\u67e5\u8be2\u7ed3\u679c\u4e3a\u865a\u62df\u8868\nSELECT t1.*,t2.dept_name,t2.dept_loc FROM (SELECT*FROM employee WHERE salary&gt;=10000) t1 JOIN department t2 WHERE t1.dept_id=t2.id;\n\n\/*** \u591a\u8868\u67e5\u8be2\u7ec3\u4e60 ***\/\n# \u67e5\u8be2\u6240\u6709\u5458\u5de5\u4fe1\u606f\uff0c\u7f16\u53f7\u3001\u59d3\u540d\u3001\u5de5\u8d44\u3001\u804c\u52a1\u540d\u79f0\u3001\u804c\u52a1\u63cf\u8ff0\nSELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description FROM employee t1,job t2 WHERE t1.job_id=t2.id;\nSELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description FROM employee t1 INNER JOIN job t2 WHERE t1.job_id=t2.id; \nSELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description FROM employee t1 LEFT OUTER JOIN job t2 ON t1.job_id=t2.id;\n# \u67e5\u8be2\u5458\u5de5\u7f16\u53f7\u3001\u59d3\u540d\u3001\u5de5\u8d44\u3001\u804c\u52a1\u540d\u79f0\u3001\u804c\u52a1\u63cf\u8ff0\u3001\u90e8\u95e8\u540d\u79f0\u3001\u90e8\u95e8\u4f4d\u7f6e\n-- \u5185\u8fde\u63a5\u9690\u5f0f\u8fde\u63a5\u67e5\u8be2\nSELECT\n    t1.id,-- \u5458\u5de5\u7f16\u53f7\n    t1.ename,-- \u59d3\u540d\n    t1.salary,-- \u5de5\u8d44\n    t2.jname,-- \u804c\u52a1\u540d\u79f0\n    t2.description,-- \u804c\u52a1\u63cf\u8ff0\n    t3.dept_name,-- \u90e8\u95e8\u540d\u79f0\n    t3.dept_loc -- \u90e8\u95e8\u4f4d\u7f6e\n\nFROM\n    employee t1,\n    job t2,\n    department t3 \nWHERE\n    t1.job_id = t2.id \n    AND t1.dept_id = t3.id;\n-- \u5de6\u5916\u8fde\u63a5\u67e5\u8be2\nSELECT\n    t1.id,-- \u5458\u5de5\u7f16\u53f7\n    t1.ename,-- \u59d3\u540d\n    t1.salary,-- \u5de5\u8d44\n    t2.jname,-- \u804c\u52a1\u540d\u79f0\n    t2.description,-- \u804c\u52a1\u63cf\u8ff0\n    t3.dept_name,-- \u90e8\u95e8\u540d\u79f0\n    t3.dept_loc -- \u90e8\u95e8\u4f4d\u7f6e\n\nFROM\n    employee t1\n    LEFT JOIN job t2 ON t1.job_id = t2.id\n    LEFT JOIN department t3 ON t1.dept_id = t3.id \nORDER BY\n    t1.id;\n# \u67e5\u8be2\u5458\u5de5\u59d3\u540d\u3001\u5de5\u8d44\u3001\u5de5\u8d44\u7b49\u7ea7\n-- \u9690\u5f0f\u5185\u8fde\u63a5\u67e5\u8be2\nSELECT\n    t1.id,\n    t1.ename,\n    t1.salary,\n    t2.grade \nFROM\n    employee t1,\n    salary_grade t2 \nWHERE\n    t1.salary BETWEEN t2.losalary \n    AND t2.hisalary;\n-- \u5de6\u5916\u8fde\u63a5\u67e5\u8be2\nSELECT\n    t1.id,\n    t1.ename,\n    t1.salary,\n    t2.grade \nFROM\n    employee t1\n    LEFT JOIN salary_grade t2 ON t1.salary BETWEEN t2.losalary \n    AND t2.hisalary;\n\n# \u67e5\u8be2\u5458\u5de5\u59d3\u540d\u3001\u5de5\u8d44\u3001\u804c\u52a1\u540d\u79f0\u3001\u804c\u52a1\u63cf\u8ff0\u3001\u90e8\u95e8\u540d\u79f0\u3001\u90e8\u95e8\u4f4d\u7f6e\u3001\u5de5\u8d44\u7b49\u7ea7\n-- \u5185\u8fde\u63a5\u9690\u5f0f\u67e5\u8be2\nSELECT\n    t1.ename \u59d3\u540d,\n    t1.salary \u5de5\u8d44,\n    t2.jname \u804c\u52a1\u540d\u79f0,\n    t2.description \u804c\u52a1\u63cf\u8ff0,\n    t3.dept_name \u90e8\u95e8\u540d\u79f0,\n    t3.dept_loc \u90e8\u95e8\u4f4d\u7f6e,\n    t4.grade \u5de5\u8d44\u7b49\u7ea7\nFROM\n    employee t1,\n    job t2,\n    department t3,\n    salary_grade t4 \nWHERE\n    t1.job_id = t2.id and\n    t1.dept_id = t3.id and\n    t1.salary BETWEEN t4.losalary \n    AND t4.hisalary;\n\n-- \u5de6\u5916\u8fde\u63a5\u67e5\u8be2\nSELECT\n    t1.ename \u59d3\u540d,\n    t1.salary \u5de5\u8d44,\n    t2.jname \u804c\u52a1\u540d\u79f0,\n    t2.description \u804c\u52a1\u63cf\u8ff0,\n    t3.dept_name \u90e8\u95e8\u540d\u79f0,\n    t3.dept_loc \u90e8\u95e8\u4f4d\u7f6e,\n    t4.grade \u5de5\u8d44\u7b49\u7ea7 \nFROM\n    employee t1\n    LEFT JOIN job t2 ON t1.job_id = t2.id\n    LEFT JOIN department t3 ON t1.dept_id = t3.id\n    LEFT JOIN salary_grade t4 ON t1.salary BETWEEN t4.losalary \n    AND t4.hisalary;\n\n# \u67e5\u8be2\u90e8\u95e8\u7f16\u53f7\u3001\u90e8\u95e8\u540d\u79f0\u3001\u90e8\u95e8\u4f4d\u7f6e\u3001\u90e8\u95e8\u4eba\u6570\n-- \u5b50\u67e5\u8be2\nSELECT*,(SELECT COUNT(id) FROM employee WHERE employee.dept_id=t1.id) '\u90e8\u95e8\u4eba\u6570' FROM department t1;\n\nSELECT t1.*,t2.ecount '\u90e8\u95e8\u4eba\u6570' FROM department t1,(SELECT dept_id,COUNT(id) ecount FROM employee GROUP BY dept_id) t2 WHERE t1.id=t2.dept_id;\n\n# \u67e5\u8be2\u6240\u6709\u5458\u5de5\u53ca\u5176\u76f4\u63a5\u4e0a\u7ea7\u7684\u59d3\u540d\uff0c\u6ca1\u6709\u9886\u5bfc\u7684\u5458\u5de5\u4e5f\u9700\u8981\u67e5\u8be2\u51fa\u6765\n-- \u9690\u5f0f\u5185\u67e5\u8be2\nSELECT t1.ename \u59d3\u540d,t2.ename \u4e0a\u7ea7 FROM employee t1,employee t2 WHERE t1.mgr=t2.id;\n-- \u5de6\u5916\u8fde\u63a5\u67e5\u8be2\nSELECT t1.ename \u59d3\u540d,t2.ename \u4e0a\u7ea7 FROM employee t1 LEFT OUTER JOIN employee t2 ON t1.mgr=t2.id;\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\/****************************\u521b\u5efa\u8868\u3001\u63d2\u5165\u6570\u636e**********************************\/ CREATE TABLE department ( id INT PRIMARY KEY auto_increment, dept_name VARCHAR ( 32 ) ) DEFAULT charset = utf8mb4; CREATE TABLE employee ( id INT PRIMARY KEY auto_increment, username VARCHAR ( 32 ), age INT, gender VARCHAR ( 2 ), dept_id INT, FOREIGN KEY ( dept_id ) REFERENCES department ( id ) ) DEFAULT [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[282],"tags":[287,22,286],"class_list":["post-1613","post","type-post","status-publish","format-standard","hentry","category-database","tag-join","tag-mysql","tag-286"],"_links":{"self":[{"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/posts\/1613","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/comments?post=1613"}],"version-history":[{"count":0,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/posts\/1613\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/media?parent=1613"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/categories?post=1613"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/tags?post=1613"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}