\u67e5\u8be2\u8bed\u53e5\u7684\u8bed\u6cd5<\/p>\n\n\n\n
select\n \u5b57\u6bb5\u5217\u8868\nfrom\n \u8868\u540d\u5217\u8868\nwhere\n \u6761\u4ef6\u5217\u8868\ngroup by\n \u5206\u7ec4\u5b57\u6bb5\nhaving\n \u5206\u7ec4\u4e4b\u540e\u7684\u6761\u4ef6\norder by\n \u6392\u5e8f\nlimit\n \u5206\u9875\u9650\u5b9a<\/code><\/pre>\n\n\n\n\u7ec3\u4e60\u4f7f\u7528\u7684sql<\/p>\n\n\n\n
# \u521b\u5efa\u90e8\u95e8\u8868\nCREATE TABLE dept(\n\tid INT PRIMARY KEY AUTO_INCREMENT,\n\tNAME VARCHAR(20)\n);\nINSERT INTO dept (NAME) VALUES ('\u5f00\u53d1\u90e8'),('\u5e02\u573a\u90e8'),('\u8d22\u52a1\u90e8');\n# \u521b\u5efa\u5458\u5de5\u8868\nCREATE TABLE emp (\n\tid INT PRIMARY KEY AUTO_INCREMENT,\n\tNAME VARCHAR(10),\n\tgender CHAR(1), -- \u6027\u522b\n\tsalary DOUBLE, -- \u5de5\u8d44\n\tjoin_date DATE, -- \u5165\u804c\u65e5\u671f\n\tdept_id INT,\n\tFOREIGN KEY (dept_id) REFERENCES dept(id) -- \u5916\u952e\uff0c\u5173\u8054\u90e8\u95e8\u8868(\u90e8\u95e8\u8868\u7684\u4e3b\u952e)\n);\nINSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('\u5b59\u609f\u7a7a','\u7537',7200,'2013-02-24',1);\nINSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('\u732a\u516b\u6212','\u7537',3600,'2010-12-02',2);\nINSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('\u5510\u50e7','\u7537',9000,'2008-08-08',2);\nINSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('\u767d\u9aa8\u7cbe','\u5973',5000,'2015-10-07',3);\nINSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('\u8718\u86db\u7cbe','\u5973',4500,'2011-03-14',1);\n<\/code><\/pre>\n\n\n\n\u591a\u8868\u67e5\u8be2\u7684\u4f5c\u7528<\/p>\n\n\n\n
\u9ed8\u8ba4\u591a\u8868\u67e5\u8be2\u662f\u7b1b\u5361\u5c14\u79ef\u7684\u7ed3\u679c<\/p>\n\n\n\n
\u6bd4\u5982\uff0c\u76f4\u63a5\u67e5\u8be2 emp, dept \u8fd9\u4e24\u5f20\u8868\u7684\u6570\u636e<\/p>\n\n\n\n
select * from emp, dept;<\/code><\/pre>\n\n\n\nmysql> select * from emp, dept;\n+----+--------+--------+--------+------------+---------+----+--------+\n| id | NAME | gender | salary | join_date | dept_id | id | NAME |\n+----+--------+--------+--------+------------+---------+----+--------+\n| 1 | \u5b59\u609f\u7a7a | \u7537 | 7200 | 2013-02-24 | 1 | 1 | \u5f00\u53d1\u90e8 |\n| 1 | \u5b59\u609f\u7a7a | \u7537 | 7200 | 2013-02-24 | 1 | 2 | \u5e02\u573a\u90e8 |\n| 1 | \u5b59\u609f\u7a7a | \u7537 | 7200 | 2013-02-24 | 1 | 3 | \u8d22\u52a1\u90e8 |\n| 2 | \u732a\u516b\u6212 | \u7537 | 3600 | 2010-12-02 | 2 | 1 | \u5f00\u53d1\u90e8 |\n| 2 | \u732a\u516b\u6212 | \u7537 | 3600 | 2010-12-02 | 2 | 2 | \u5e02\u573a\u90e8 |\n| 2 | \u732a\u516b\u6212 | \u7537 | 3600 | 2010-12-02 | 2 | 3 | \u8d22\u52a1\u90e8 |\n| 3 | \u5510\u50e7 | \u7537 | 9000 | 2008-08-08 | 2 | 1 | \u5f00\u53d1\u90e8 |\n| 3 | \u5510\u50e7 | \u7537 | 9000 | 2008-08-08 | 2 | 2 | \u5e02\u573a\u90e8 |\n| 3 | \u5510\u50e7 | \u7537 | 9000 | 2008-08-08 | 2 | 3 | \u8d22\u52a1\u90e8 |\n| 4 | \u767d\u9aa8\u7cbe | \u5973 | 5000 | 2015-10-07 | 3 | 1 | \u5f00\u53d1\u90e8 |\n| 4 | \u767d\u9aa8\u7cbe | \u5973 | 5000 | 2015-10-07 | 3 | 2 | \u5e02\u573a\u90e8 |\n| 4 | \u767d\u9aa8\u7cbe | \u5973 | 5000 | 2015-10-07 | 3 | 3 | \u8d22\u52a1\u90e8 |\n| 5 | \u8718\u86db\u7cbe | \u5973 | 4500 | 2011-03-14 | 1 | 1 | \u5f00\u53d1\u90e8 |\n| 5 | \u8718\u86db\u7cbe | \u5973 | 4500 | 2011-03-14 | 1 | 2 | \u5e02\u573a\u90e8 |\n| 5 | \u8718\u86db\u7cbe | \u5973 | 4500 | 2011-03-14 | 1 | 3 | \u8d22\u52a1\u90e8 |\n+----+--------+--------+--------+------------+---------+----+--------+\n15 rows in set (0.10 sec)<\/code><\/pre>\n\n\n\n\u53ef\u4ee5\u770b\u5230\uff0c\u7b1b\u5361\u5c14\u79ef\u7684\u7ed3\u679c\u5b9e\u9645\u4e0a\u5e76\u4e0d\u662f\u6b63\u786e\u7684\u7ed3\u679c<\/p>\n\n\n\n
\u800c\u5b66\u4e60\u591a\u8868\u67e5\u8be2\uff0c\u5c31\u662f\u4e3a\u4e86\u6d88\u9664\u4e00\u4e9b\u65e0\u7528\u7684\u6570\u636e\uff0c\u5f97\u5230\u60f3\u8981\u7684\u6570\u636e<\/p>\n\n\n\n
\u591a\u8868\u67e5\u8be2\u7684\u5206\u7c7b<\/p>\n\n\n\n
- \u5185\u8fde\u63a5<\/li>
- \u5916\u8fde\u63a5<\/li>
- \u5b50\u67e5\u8be2<\/li><\/ol>\n\n\n\n
1.\u5185\u8fde\u63a5<\/h4>\n\n\n\n
\u5185\u8fde\u63a5\u5c31\u662f\u9650\u5b9a\u4e86\u5fc5\u987b\u5bf9\u5e94\u7684\u5b57\u6bb5\u4fe1\u606f<\/p>\n\n\n\n
1.\u9690\u5f0f\u5185\u8fde\u63a5<\/h5>\n\n\n\n
\u67e5\u8be2 emp, dept \u8868\u6240\u6709\u7684\u6570\u636e<\/p>\n\n\n\n
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;<\/code><\/pre>\n\n\n\n\u67e5\u8be2 emp, dept \u8868\u5176\u4e2d\u7684\u4e00\u4e9b\u5c5e\u6027\uff0c\u53ef\u4ee5\u4f7f\u7528 \u201c\u8868\u540d.\u5c5e\u6027\u201d \u6765\u8868\u793a \u201c\u5c5e\u6027\u201d<\/p>\n\n\n\n
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;<\/code><\/pre>\n\n\n\n\u53ef\u4ee5\u4f7f\u7528\u8d77\u522b\u540d\u7684\u65b9\u5f0f\uff0c\u7b80\u5316\u67e5\u8be2<\/p>\n\n\n\n
SELECT \n\tt1.name, -- \u5458\u5de5\u8868\u7684\u59d3\u540d\n\tt1.gender,-- \u5458\u5de5\u8868\u7684\u6027\u522b\n\tt2.name -- \u90e8\u95e8\u8868\u7684\u540d\u79f0\nFROM\n\temp t1,\n\tdept t2\nWHERE \n\tt1.`dept_id` = t2.`id`;<\/code><\/pre>\n\n\n\n2.\u663e\u5f0f\u5185\u8fde\u63a5<\/h5>\n\n\n\n
\u663e\u5f0f\u5185\u8fde\u63a5\u6548\u679c\u4e00\u6837\uff0c\u53ea\u662f\u8bed\u6cd5\u4e0d\u540c\uff0c\u8bed\u6cd5\u5982\u4e0b<\/p>\n\n\n\n
select \u5b57\u6bb5\u5217\u8868 from \u8868\u540d1 [inner] join \u8868\u540d2 on \u6761\u4ef6;<\/code><\/pre>\n\n\n\n\uff08\u5176\u4e2d \u88681 \u662f\u4e3b\u8868\uff0c\u540e\u9762\u7684\u662f\u4ece\u8868\uff0c\u4e14 inner \u5173\u952e\u5b57\u53ef\u4ee5\u7701\u7565\uff09<\/p>\n\n\n\n
\u4f8b\u5982\uff0c\u4ecd\u7136\u67e5\u8be2 emp, dept \u8868\u7684\u6240\u6709\u6570\u636e<\/p>\n\n\n\n
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;<\/code><\/pre>\n\n\n\nSELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;<\/code><\/pre>\n\n\n\n3.\u5185\u8fde\u63a5\u67e5\u8be2\u6ce8\u610f\u4e8b\u9879<\/h5>\n\n\n\n- \u67e5\u8be2\u54ea\u4e9b\u5b57\u6bb5<\/li>
- \u4ece\u54ea\u4e9b\u8868\u67e5\u8be2\u6570\u636e<\/li>
- \u67e5\u8be2\u6761\u4ef6\u662f\u4ec0\u4e48<\/li><\/ol>\n\n\n\n
2.\u5916\u8fde\u63a5<\/h4>\n\n\n\n1.\u5de6\u5916\u8fde\u63a5\uff1a\u67e5\u8be2\u201c\u5de6\u8868\u201d\u6240\u6709\u6570\u636e\uff0c\u4ee5\u53ca\u5176\u4ed6\u8868\u7684\u201c\u4ea4\u96c6\u201d\u90e8\u5206<\/h5>\n\n\n\n
\u5de6\u5916\u8fde\u63a5\u8bed\u6cd5\u5982\u4e0b<\/p>\n\n\n\n
select \u5b57\u6bb5\u5217\u8868 from \u88681 left [outer] join \u88682 on \u6761\u4ef6;<\/code><\/pre>\n\n\n\n\u4f8b\u5982\uff0c\u67e5\u8be2\u6240\u6709\u5458\u5de5\u4fe1\u606f\uff0c\u5982\u679c\u5458\u5de5\u6709\u90e8\u95e8\uff0c\u5219\u67e5\u8be2\u90e8\u95e8\u540d\u79f0\uff0c\u6ca1\u6709\u90e8\u95e8\uff0c\u5219\u4e0d\u663e\u793a\u90e8\u95e8\u540d\u79f0<\/p>\n\n\n\n
\u9996\u5148\u52a0\u5165\u4e00\u4e2a\u65b0\u5165\u804c\u5458\u5de5\u5c0f\u767d\u9f99\uff0c\u8fd8\u672a\u5206\u914d\u90e8\u95e8<\/p>\n\n\n\n
insert into emp(name,gender,salary) values(\"\u5c0f\u767d\u9f99\",\"\u7537\",3000);<\/code><\/pre>\n\n\n\nSELECT \tt1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;<\/code><\/pre>\n\n\n\n2.\u53f3\u5916\u8fde\u63a5\uff1a\u67e5\u8be2\u201c\u53f3\u8868\u201d\u6240\u6709\u6570\u636e\uff0c\u4ee5\u53ca\u5176\u4ed6\u8868\u7684\u201c\u4ea4\u96c6\u201d\u90e8\u5206<\/h5>\n\n\n\nselect \u5b57\u6bb5\u5217\u8868 from \u88681 right [outer] join \u88682 on \u6761\u4ef6;<\/code><\/pre>\n\n\n\n\u4f8b\u5982\uff0c\u67e5\u8be2\u6240\u6709\u5458\u5de5\u4fe1\u606f\uff0c\u5982\u679c\u5458\u5de5\u6709\u90e8\u95e8\uff0c\u5219\u67e5\u8be2\u90e8\u95e8\u540d\u79f0\uff0c\u6ca1\u6709\u90e8\u95e8\uff0c\u5219\u4e0d\u663e\u793a\u90e8\u95e8\u540d\u79f0<\/p>\n\n\n\n
SELECT \tt1.*,t2.`name` FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;<\/code><\/pre>\n\n\n\n3.\u5b50\u67e5\u8be2<\/h4>\n\n\n\n
\u67e5\u8be2\u4e2d\u201c\u5d4c\u5957\u201d\u67e5\u8be2\uff0c\u5219\u79f0\u4e3a\u5b50\u67e5\u8be2<\/p>\n\n\n\n
\u5b50\u67e5\u8be2\u8bed\u6cd5\uff1a\u7528\u4e00\u4e2a\u5c0f\u62ec\u53f7()\uff0c\u628a\u5b50\u67e5\u8be2\u8bed\u53e5\u5d4c\u5957\u5728where\u4e2d<\/p>\n\n\n\n
select \u5217\u540d\u79f0 from \u8868\u540d\u79f0 where \u503c1=(\u5b50\u67e5\u8be2\u8bed\u53e5),...;<\/code><\/pre>\n\n\n\n\u67e5\u8be2\u5de5\u8d44\u6700\u9ad8\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n\n\n\n
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);<\/code><\/pre>\n\n\n\n1.\u5b50\u67e5\u8be2\u7684\u7ed3\u679c\u662f\u5355\u884c\u5355\u5217\u7684\uff1a\u7ed3\u679c\u53ef\u4ee5\u4f7f\u7528\u8fd0\u7b97\u7b26\u201c> >= < <= =\u201d\u53bb\u5224\u65ad<\/h5>\n\n\n\n
\u67e5\u8be2\u5458\u5de5\u5de5\u8d44\u5c0f\u4e8e\u5e73\u5747\u5de5\u8d44\u7684\u4eba<\/p>\n\n\n\n
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);<\/code><\/pre>\n\n\n\n2.\u5b50\u67e5\u8be2\u7684\u7ed3\u679c\u662f\u591a\u884c\u5355\u5217\u7684\uff1a\u901a\u5e38\u4f7f\u7528\u8fd0\u7b97\u7b26 in \u6765\u5224\u65ad<\/h5>\n\n\n\n
\u67e5\u8be2’\u8d22\u52a1\u90e8’\u548c’\u5e02\u573a\u90e8’\u6240\u6709\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n\n\n\n
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '\u8d22\u52a1\u90e8' OR NAME = '\u5e02\u573a\u90e8');<\/code><\/pre>\n\n\n\n3.\u5b50\u67e5\u8be2\u7684\u7ed3\u679c\u662f\u591a\u884c\u591a\u5217\u7684\uff1a\u53ef\u4ee5\u4f5c\u4e3a\u4e00\u5f20\u865a\u62df\u8868\u53c2\u4e0e\u67e5\u8be2<\/h5>\n\n\n\n
\u67e5\u8be2\u5458\u5de5\u5165\u804c\u65e5\u671f\u662f2011-11-11\u65e5\u4e4b\u540e\u7684\u5458\u5de5\u4fe1\u606f\u548c\u90e8\u95e8\u4fe1\u606f<\/p>\n\n\n\n
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2\n WHERE t1.id = t2.dept_id;\t\t\t<\/code><\/pre>\n\n\n\n\u8be5\u67e5\u8be2\u901a\u5e38\u662f\u76f4\u63a5\u4f7f\u7528\u5185\u8fde\u63a5\u67e5\u8be2<\/p>\n\n\n\n
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11';<\/code><\/pre>\n\n\n\n\u5de9\u56fa\u7ec3\u4e60<\/h4>\n\n\n\n
\u7ec3\u4e60\u4f7f\u7528\u7684 sql \u8bed\u53e5<\/p>\n\n\n\n
-- \u90e8\u95e8\u8868\nCREATE TABLE dept (\n id INT PRIMARY KEY PRIMARY KEY, -- \u90e8\u95e8id\n dname VARCHAR(50), -- \u90e8\u95e8\u540d\u79f0\n loc VARCHAR(50) -- \u90e8\u95e8\u6240\u5728\u5730\n);\n-- \u6dfb\u52a04\u4e2a\u90e8\u95e8\nINSERT INTO dept(id,dname,loc) VALUES \n(10,'\u6559\u7814\u90e8','\u5317\u4eac'),\n(20,'\u5b66\u5de5\u90e8','\u4e0a\u6d77'),\n(30,'\u9500\u552e\u90e8','\u5e7f\u5dde'),\n(40,'\u8d22\u52a1\u90e8','\u6df1\u5733');\n-- \u804c\u52a1\u8868\uff0c\u804c\u52a1\u540d\u79f0\uff0c\u804c\u52a1\u63cf\u8ff0\nCREATE TABLE job (\n id INT PRIMARY KEY,\n jname VARCHAR(20),\n description VARCHAR(50)\n);\n-- \u6dfb\u52a04\u4e2a\u804c\u52a1\nINSERT INTO job (id, jname, description) VALUES\n(1, '\u8463\u4e8b\u957f', '\u7ba1\u7406\u6574\u4e2a\u516c\u53f8\uff0c\u63a5\u5355'),\n(2, '\u7ecf\u7406', '\u7ba1\u7406\u90e8\u95e8\u5458\u5de5'),\n(3, '\u9500\u552e\u5458', '\u5411\u5ba2\u4eba\u63a8\u9500\u4ea7\u54c1'),\n(4, '\u6587\u5458', '\u4f7f\u7528\u529e\u516c\u8f6f\u4ef6');\n-- \u5458\u5de5\u8868\nCREATE TABLE emp (\n id INT PRIMARY KEY, -- \u5458\u5de5id\n ename VARCHAR(50), -- \u5458\u5de5\u59d3\u540d\n job_id INT, -- \u804c\u52a1id\n mgr INT , -- \u4e0a\u7ea7\u9886\u5bfc\n joindate DATE, -- \u5165\u804c\u65e5\u671f\n salary DECIMAL(7,2), -- \u5de5\u8d44\n bonus DECIMAL(7,2), -- \u5956\u91d1\n dept_id INT, -- \u6240\u5728\u90e8\u95e8\u7f16\u53f7\n CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),\n CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)\n);\n-- \u6dfb\u52a0\u5458\u5de5\nINSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES \n(1001,'\u5b59\u609f\u7a7a',4,1004,'2000-12-17','8000.00',NULL,20),\n(1002,'\u5362\u4fca\u4e49',3,1006,'2001-02-20','16000.00','3000.00',30),\n(1003,'\u6797\u51b2',3,1006,'2001-02-22','12500.00','5000.00',30),\n(1004,'\u5510\u50e7',2,1009,'2001-04-02','29750.00',NULL,20),\n(1005,'\u674e\u9035',4,1006,'2001-09-28','12500.00','14000.00',30),\n(1006,'\u5b8b\u6c5f',2,1009,'2001-05-01','28500.00',NULL,30),\n(1007,'\u5218\u5907',2,1009,'2001-09-01','24500.00',NULL,10),\n(1008,'\u732a\u516b\u6212',4,1004,'2007-04-19','30000.00',NULL,20),\n(1009,'\u7f57\u8d2f\u4e2d',1,NULL,'2001-11-17','50000.00',NULL,10),\n(1010,'\u5434\u7528',3,1006,'2001-09-08','15000.00','0.00',30),\n(1011,'\u6c99\u50e7',4,1004,'2007-05-23','11000.00',NULL,20),\n(1012,'\u674e\u9035',4,1006,'2001-12-03','9500.00',NULL,30),\n(1013,'\u5c0f\u767d\u9f99',4,1004,'2001-12-03','30000.00',NULL,20),\n(1014,'\u5173\u7fbd',4,1007,'2002-01-23','13000.00',NULL,10);\n-- \u5de5\u8d44\u7b49\u7ea7\u8868\nCREATE TABLE salarygrade (\n grade INT PRIMARY KEY, -- \u7ea7\u522b\n losalary INT, -- \u6700\u4f4e\u5de5\u8d44\n hisalary INT -- \u6700\u9ad8\u5de5\u8d44\n);\n-- \u6dfb\u52a05\u4e2a\u5de5\u8d44\u7b49\u7ea7\nINSERT INTO salarygrade(grade,losalary,hisalary) VALUES \n(1,7000,12000),\n(2,12010,14000),\n(3,14010,20000),\n(4,20010,30000),\n(5,30010,99990);<\/code><\/pre>\n\n\n\n1.\u67e5\u8be2\u6240\u6709\u5458\u5de5\u4fe1\u606f\u3002\u67e5\u8be2\u5458\u5de5\u7f16\u53f7\uff0c\u5458\u5de5\u59d3\u540d\uff0c\u5de5\u8d44\uff0c\u804c\u52a1\u540d\u79f0\uff0c\u804c\u52a1\u63cf\u8ff0<\/p>\n\n\n\n
SELECT \n\tt1.`id`, -- \u5458\u5de5\u7f16\u53f7\n\tt1.`ename`, -- \u5458\u5de5\u59d3\u540d\n\tt1.`salary`,-- \u5de5\u8d44\n\tt2.`jname`, -- \u804c\u52a1\u540d\u79f0\n\tt2.`description` -- \u804c\u52a1\u63cf\u8ff0\nFROM \n\temp t1, \n job t2 \nWHERE \n\tt1.`job_id` = t2.`id`; <\/code><\/pre>\n\n\n\n2.\u67e5\u8be2\u5458\u5de5\u7f16\u53f7\uff0c\u5458\u5de5\u59d3\u540d\uff0c\u5de5\u8d44\uff0c\u804c\u52a1\u540d\u79f0\uff0c\u804c\u52a1\u63cf\u8ff0\uff0c\u90e8\u95e8\u540d\u79f0\uff0c\u90e8\u95e8\u4f4d\u7f6e<\/p>\n\n\n\n
SELECT \n\tt1.`id`, -- \u5458\u5de5\u7f16\u53f7\n\tt1.`ename`, -- \u5458\u5de5\u59d3\u540d\n\tt1.`salary`,-- \u5de5\u8d44\n\tt2.`jname`, -- \u804c\u52a1\u540d\u79f0\n\tt2.`description`, -- \u804c\u52a1\u63cf\u8ff0\n\tt3.`dname`, -- \u90e8\u95e8\u540d\u79f0\n\tt3.`loc` -- \u90e8\u95e8\u4f4d\u7f6e\nFROM \n\temp t1, \n job t2,\n dept t3\nWHERE \n\tt1.`job_id` = t2.`id` \n AND t1.`dept_id` = t3.`id`;<\/code><\/pre>\n\n\n\n3.\u67e5\u8be2\u5458\u5de5\u59d3\u540d\uff0c\u5de5\u8d44\uff0c\u5de5\u8d44\u7b49\u7ea7<\/p>\n\n\n\n
SELECT \n\tt1.ename , -- \u5458\u5de5\u59d3\u540d\n\tt1.`salary`, -- \u5de5\u8d44\n\tt2.`grade` -- \u5de5\u8d44\u7b49\u7ea7\nFROM \n emp t1, \n salarygrade t2\nWHERE \n t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;<\/code><\/pre>\n\n\n\n4.\u67e5\u8be2\u5458\u5de5\u59d3\u540d\uff0c\u5de5\u8d44\uff0c\u804c\u52a1\u540d\u79f0\uff0c\u804c\u52a1\u63cf\u8ff0\uff0c\u90e8\u95e8\u540d\u79f0\uff0c\u90e8\u95e8\u4f4d\u7f6e\uff0c\u5de5\u8d44\u7b49\u7ea7<\/p>\n\n\n\n
SELECT \n\tt1.`ename`, -- \u5458\u5de5\u59d3\u540d\n\tt1.`salary`, -- \u5de5\u8d44\n\tt2.`jname`, -- \u804c\u52a1\u540d\u79f0\n\tt2.`description`, -- \u804c\u52a1\u63cf\u8ff0\n\tt3.`dname`, -- \u90e8\u95e8\u540d\u79f0\n\tt3.`loc`, -- \u90e8\u95e8\u4f4d\u7f6e\n\tt4.`grade` -- \u5de5\u8d44\u7b49\u7ea7\nFROM \n\temp t1,\n job t2,\n dept t3,\n salarygrade t4\nWHERE \n\tt1.`job_id` = t2.`id` \n\tAND t1.`dept_id` = t3.`id`\n\tAND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;<\/code><\/pre>\n\n\n\n5.\u67e5\u8be2\u51fa\u90e8\u95e8\u7f16\u53f7\u3001\u90e8\u95e8\u540d\u79f0\u3001\u90e8\u95e8\u4f4d\u7f6e\u3001\u90e8\u95e8\u4eba\u6570<\/p>\n\n\n\n
SELECT \n\tt1.`id`, -- \u90e8\u95e8\u7f16\u53f7\n t1.`dname`, -- \u90e8\u95e8\u540d\u79f0\n t1.`loc` , -- \u90e8\u95e8\u4f4d\u7f6e\n t2.total -- \u90e8\u95e8\u4eba\u6570\nFROM \n\tdept t1, \n\t(SELECT\n\t\tdept_id,COUNT(id) total\n\tFROM \n\t\temp\n\tGROUP BY dept_id) t2 -- \u5b50\u67e5\u8be2\u5f97\u5230\u7684\u8868\u522b\u540dt2\nWHERE \n t1.`id` = t2.dept_id;\n<\/code><\/pre>\n\n\n\n6.\u67e5\u8be2\u6240\u6709\u5458\u5de5\u7684\u59d3\u540d\u53ca\u5176\u76f4\u63a5\u4e0a\u7ea7\u7684\u59d3\u540d,\u6ca1\u6709\u9886\u5bfc\u7684\u5458\u5de5\u4e5f\u9700\u8981\u67e5\u8be2<\/p>\n\n\n\n
SELECT \n\tt1.ename, -- \u5458\u5de5\u59d3\u540d\n\tt2.`ename` -- \u4e0a\u7ea7\u9886\u5bfc\u7684\u59d3\u540d\nFROM \n emp t1\nLEFT JOIN \n emp t2\nON \n t1.`mgr` = t2.`id`;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"\u67e5\u8be2\u8bed\u53e5\u7684\u8bed\u6cd5 \u7ec3\u4e60\u4f7f\u7528\u7684sql \u591a\u8868\u67e5\u8be2\u7684\u4f5c\u7528 \u9ed8\u8ba4\u591a\u8868\u67e5\u8be2\u662f\u7b1b\u5361\u5c14\u79ef\u7684\u7ed3\u679c \u6bd4\u5982\uff0c\u76f4\u63a5\u67e5\u8be2 emp, de… \u7ee7\u7eed\u9605\u8bfb SQL \u591a\u8868\u67e5\u8be2<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4],"tags":[],"_links":{"self":[{"href":"https:\/\/www.52dixiaowo.com\/db\/wp-json\/wp\/v2\/posts\/88"}],"collection":[{"href":"https:\/\/www.52dixiaowo.com\/db\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.52dixiaowo.com\/db\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.52dixiaowo.com\/db\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.52dixiaowo.com\/db\/wp-json\/wp\/v2\/comments?post=88"}],"version-history":[{"count":9,"href":"https:\/\/www.52dixiaowo.com\/db\/wp-json\/wp\/v2\/posts\/88\/revisions"}],"predecessor-version":[{"id":103,"href":"https:\/\/www.52dixiaowo.com\/db\/wp-json\/wp\/v2\/posts\/88\/revisions\/103"}],"wp:attachment":[{"href":"https:\/\/www.52dixiaowo.com\/db\/wp-json\/wp\/v2\/media?parent=88"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.52dixiaowo.com\/db\/wp-json\/wp\/v2\/categories?post=88"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.52dixiaowo.com\/db\/wp-json\/wp\/v2\/tags?post=88"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}