{"id":172,"date":"2025-04-19T09:33:01","date_gmt":"2025-04-19T01:33:01","guid":{"rendered":"https:\/\/zd1269878.noblogs.org\/?p=172"},"modified":"2025-10-21T10:32:02","modified_gmt":"2025-10-21T02:32:02","slug":"2025-04-18-%e6%95%b0%e6%8d%ae%e5%ba%93-%e7%ac%ac%e4%b8%89%e7%ab%a0-%e7%9f%a5%e8%af%86%e7%82%b9%e6%95%b4%e7%90%86","status":"publish","type":"post","link":"https:\/\/zd1269878.noblogs.org\/en-US\/post\/2025\/04\/19\/2025-04-18-%e6%95%b0%e6%8d%ae%e5%ba%93-%e7%ac%ac%e4%b8%89%e7%ab%a0-%e7%9f%a5%e8%af%86%e7%82%b9%e6%95%b4%e7%90%86\/","title":{"rendered":"2025-04-18-\u6570\u636e\u5e93-\u7b2c\u4e09\u7ae0-\u77e5\u8bc6\u70b9\u6574\u7406"},"content":{"rendered":"\n

\u4e00\u3001SQL<\/strong><\/a>\u8bed\u8a00\u5206\u7c7b<\/strong>
DDL\uff08\u6570\u636e\u5b9a\u4e49\u8bed\u8a00\uff09<\/strong>
\u529f\u80fd<\/strong>\uff1a\u5b9a\u4e49\/\u4fee\u6539\u6570\u636e\u5e93\u7ed3\u6784\uff08\u8868\u3001\u7d22\u5f15\u3001\u89c6\u56fe\uff09\u3002
\u6838\u5fc3\u8bed\u53e5<\/strong>\uff1aCREATE, ALTER, DROP\u3002
DML\uff08\u6570\u636e\u64cd\u7eb5\u8bed\u8a00\uff09<\/strong>
\u529f\u80fd<\/strong>\uff1a\u64cd\u4f5c\u6570\u636e\uff08\u589e\u5220\u6539\u67e5\uff09\u3002
\u6838\u5fc3\u8bed\u53e5<\/strong>\uff1aINSERT, UPDATE, DELETE, SELECT\u3002
DCL\uff08\u6570\u636e\u63a7\u5236\u8bed\u8a00\uff09<\/strong>
\u529f\u80fd<\/strong>\uff1a\u7ba1\u7406\u6743\u9650\u3002
\u6838\u5fc3\u8bed\u53e5<\/strong>\uff1aGRANT, REVOKE\u3002

\u4e8c\u3001\u6570\u636e\u5b9a\u4e49\u8bed\u8a00\uff08DDL<\/strong><\/a>\uff09<\/strong>
1. <\/strong><\/a>\u8868\u64cd\u4f5c<\/strong>
CREATE TABLE<\/strong>
\u529f\u80fd<\/strong>\uff1a\u521b\u5efa\u65b0\u8868\uff0c\u5b9a\u4e49\u5217\u3001\u6570\u636e\u7c7b\u578b\u53ca\u7ea6\u675f\u3002
\u793a\u4f8b<\/strong>\uff1a
CREATE TABLE Employee (
\u00a0 Eno CHAR(4) PRIMARY KEY,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — \u4e3b\u952e\u7ea6\u675f
\u00a0 Ename VARCHAR(10) NOT NULL,\u00a0\u00a0\u00a0 — \u975e\u7a7a\u7ea6\u675f
\u00a0 Age INT CHECK (Age >= 18),\u00a0\u00a0\u00a0\u00a0 — \u68c0\u67e5\u7ea6\u675f\uff08\u5e74\u9f84\u226518\uff09
\u00a0 Dno CHAR(2) REFERENCES Department(Dno)\u00a0 — \u5916\u952e\u7ea6\u675f
);
ALTER TABLE<\/strong>
\u529f\u80fd<\/strong>\uff1a\u4fee\u6539\u8868\u7ed3\u6784\uff08\u589e\u5220\u5217\u3001\u7ea6\u675f\uff09\u3002
\u793a\u4f8b<\/strong>\uff1a
ALTER TABLE Employee ADD COLUMN Phone VARCHAR(11);\u00a0 — \u65b0\u589e\u5217
ALTER TABLE Employee DROP COLUMN Phone;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — \u5220\u9664\u5217
ALTER TABLE Employee ALTER COLUMN Age TINYINT;\u00a0\u00a0\u00a0\u00a0\u00a0 — \u4fee\u6539\u5217\u7c7b\u578b
DROP TABLE<\/strong>
\u529f\u80fd<\/strong>\uff1a\u5220\u9664\u8868\uff08\u7ed3\u6784+\u6570\u636e\uff09\u3002
\u5bf9\u6bd4<\/strong>\uff1a
DELETE FROM Employee\uff1a\u4ec5\u5220\u9664\u6570\u636e\uff0c\u4fdd\u7559\u8868\u7ed3\u6784\u3002
TRUNCATE TABLE Employee\uff1a\u5feb\u901f\u6e05\u7a7a\u8868\uff0c\u4e0d\u53ef\u56de\u6eda\u3002

2. <\/strong><\/a>\u7d22\u5f15\uff08Index\uff09<\/strong>
CREATE INDEX<\/strong>
\u529f\u80fd<\/strong>\uff1a\u52a0\u901f\u67e5\u8be2\uff0c\u652f\u6301\u6392\u5e8f\/\u5206\u7ec4\u3002
\u7c7b\u578b<\/strong>\uff1a
\u805a\u96c6\u7d22\u5f15<\/strong>\uff1a\u6570\u636e\u7269\u7406\u6392\u5e8f\u4e0e\u7d22\u5f15\u4e00\u81f4\uff08\u5982\u5b57\u5178\u76ee\u5f55\uff09\u3002
\u975e\u805a\u96c6\u7d22\u5f15<\/strong>\uff1a\u72ec\u7acb\u5b58\u50a8\u7d22\u5f15\u548c\u6570\u636e\u5730\u5740\uff08\u5982\u56fe\u4e66\u9986\u4e66\u540d\u7d22\u5f15\uff09\u3002
\u793a\u4f8b<\/strong>\uff1a
CREATE CLUSTERED INDEX idx_dno ON Employee(Dno);\u00a0 — \u805a\u96c6\u7d22\u5f15
CREATE NONCLUSTERED INDEX idx_ename ON Employee(Ename);\u00a0 — \u975e\u805a\u96c6\u7d22\u5f15
\u8bbe\u8ba1\u539f\u5219<\/strong>\uff1a
\u9002\u7528\u4e8e\u5927\u8868\u3001\u9ad8\u9891\u67e5\u8be2\u5217\uff08\u5982WHERE\u3001JOIN\u6761\u4ef6\u5217\uff09\u3002
\u907f\u514d\u5728\u5c0f\u8868\u6216\u4f4e\u9009\u62e9\u6027\u5217\uff08\u5982\u6027\u522b\uff09\u5efa\u7d22\u5f15\u3002

3. <\/strong><\/a>\u89c6\u56fe\uff08View\uff09<\/strong>
CREATE VIEW<\/strong>
\u529f\u80fd<\/strong>\uff1a\u57fa\u4e8e\u67e5\u8be2\u7ed3\u679c\u7684\u865a\u62df\u8868\uff0c\u7b80\u5316\u590d\u6742\u64cd\u4f5c\u3002
\u793a\u4f8b<\/strong>\uff1a
CREATE VIEW V_Emp_02 AS
SELECT * FROM Employee WHERE Dno = '02' WITH CHECK OPTION;\u00a0 — \u9650\u5236\u66f4\u65b0\u4ec5\u9650\u90e8\u95e802
\u4f5c\u7528<\/strong>\uff1a
\u7b80\u5316\u67e5\u8be2<\/strong>\uff1a\u5c06\u591a\u8868\u8fde\u63a5\u7ed3\u679c\u4fdd\u5b58\u4e3a\u89c6\u56fe\u3002
\u6570\u636e\u5b89\u5168<\/strong>\uff1a\u9690\u85cf\u654f\u611f\u5217\uff08\u5982\u4ec5\u5141\u8bb8\u67e5\u8be2\u5de5\u8d44\u8303\u56f4\uff09\u3002
\u903b\u8f91\u72ec\u7acb\u6027<\/strong>\uff1a\u57fa\u8868\u7ed3\u6784\u53d8\u5316\u65f6\uff0c\u4ec5\u9700\u8c03\u6574\u89c6\u56fe\u5b9a\u4e49\u3002
\u9650\u5236<\/strong>\uff1a
\u542b\u805a\u5408\u51fd\u6570\uff08AVG\uff09\u3001DISTINCT\u6216\u5206\u7ec4\u7684\u89c6\u56fe\u4e0d\u53ef\u66f4\u65b0\u3002

\u4e09\u3001\u6570\u636e\u64cd\u7eb5\u8bed\u8a00\uff08DML<\/strong><\/a>\uff09<\/strong>
1. <\/strong><\/a>\u6570\u636e\u63d2\u5165\uff08INSERT\uff09<\/strong>
\u529f\u80fd<\/strong>\uff1a\u5411\u8868\u4e2d\u6dfb\u52a0\u6570\u636e\u3002
\u793a\u4f8b<\/strong>\uff1a
— \u5355\u884c\u63d2\u5165
INSERT INTO Employee (Eno, Ename, Age) VALUES ('1001', '\u5f20\u4e09', 25);

— \u591a\u884c\u63d2\u5165\uff08\u4ece\u5176\u4ed6\u8868\u5bfc\u5165\uff09
INSERT INTO Engineer SELECT * FROM Employee WHERE Title = '\u5de5\u7a0b\u5e08';
2. <\/strong><\/a>\u6570\u636e\u66f4\u65b0\uff08UPDATE\uff09<\/strong>
\u529f\u80fd<\/strong>\uff1a\u4fee\u6539\u73b0\u6709\u6570\u636e\u3002
\u793a\u4f8b<\/strong>\uff1a
— \u6761\u4ef6\u66f4\u65b0
UPDATE Employee SET Age = 30 WHERE Ename = '\u5f20\u4e09';

— \u5b50\u67e5\u8be2\u66f4\u65b0\uff08\u6280\u672f\u79d1\u5458\u5de5\u5de5\u8d44\u7ffb\u500d\uff09
UPDATE Salary SET Basepay = Basepay * 2
WHERE Eno IN (SELECT Eno FROM Employee WHERE Dno = '\u6280\u672f\u79d1');
3. <\/strong><\/a>\u6570\u636e\u5220\u9664\uff08DELETE\uff09<\/strong>
\u529f\u80fd<\/strong>\uff1a\u5220\u9664\u6570\u636e\u3002
\u793a\u4f8b<\/strong>\uff1a
— \u5220\u9664\u6307\u5b9a\u884c
DELETE FROM Employee WHERE Age > 60;

— \u6e05\u7a7a\u8868\uff08\u9ad8\u6548\u4f46\u4e0d\u53ef\u56de\u6eda\uff09
TRUNCATE TABLE Employee;

4. <\/strong><\/a>\u6570\u636e\u67e5\u8be2\uff08SELECT\uff09<\/strong>
\u57fa\u7840\u67e5\u8be2<\/strong>\uff1a
SELECT Ename, Age FROM Employee WHERE Dno = '02' ORDER BY Age DESC;
\u6761\u4ef6\u67e5\u8be2<\/strong>\uff1a
\u6bd4\u8f83<\/strong>\uff1aWHERE Salary > 5000
\u8303\u56f4<\/strong>\uff1aWHERE Age BETWEEN 20 AND 30
\u6a21\u7cca\u5339\u914d<\/strong>\uff1aWHERE Ename LIKE '\u5f20%'\uff08\u5339\u914d\u201c\u5f20\u4e09\u201d\u201c\u5f20\u4f1f\u201d\uff09
\u805a\u5408\u4e0e\u5206\u7ec4<\/strong>\uff1a
SELECT Dno, AVG(Age) AS AvgAge
FROM Employee
GROUP BY Dno
HAVING AVG(Age) > 30;\u00a0 — \u7b5b\u9009\u90e8\u95e8\u5e73\u5747\u5e74\u9f84>30
\u8fde\u63a5\u67e5\u8be2<\/strong>\uff1a
— \u5185\u8fde\u63a5\uff08\u5339\u914d\u4e24\u8868\u5173\u8054\u6570\u636e\uff09
SELECT E.Ename, D.Dname
FROM Employee E JOIN Department D ON E.Dno = D.Dno;

— \u5de6\u5916\u8fde\u63a5\uff08\u4fdd\u7559\u5de6\u8868\u672a\u5339\u914d\u884c\uff09
SELECT E.Ename, D.Dname
FROM Employee E LEFT JOIN Department D ON E.Dno = D.Dno;
\u5b50\u67e5\u8be2<\/strong>\uff1a
— \u5d4c\u5957\u5b50\u67e5\u8be2\uff08\u6280\u672f\u79d1\u5458\u5de5\uff09
SELECT * FROM Employee
WHERE Dno = (SELECT Dno FROM Department WHERE Dname = '\u6280\u672f\u79d1');

— EXISTS\u5b50\u67e5\u8be2\uff08\u68c0\u67e5\u662f\u5426\u5b58\u5728\u5173\u8054\u6570\u636e\uff09
SELECT * FROM Employee E
WHERE EXISTS (SELECT 1 FROM Item_Emp WHERE Eno = E.Eno);

\u56db\u3001\u91cd\u70b9\u4e0e\u96be\u70b9<\/strong><\/a>
\u590d\u6742\u67e5\u8be2<\/strong>\uff1a
\u591a\u8868\u8fde\u63a5\uff08\u5982Employee\u4e0eDepartment\uff09\u3002
\u5b50\u67e5\u8be2\u5d4c\u5957\uff08\u5982EXISTS\u5224\u65ad\u662f\u5426\u5b58\u5728\u6570\u636e\uff09\u3002
\u6027\u80fd\u4f18\u5316<\/strong>\uff1a
\u5408\u7406\u4f7f\u7528\u7d22\u5f15\uff0c\u907f\u514d\u5168\u8868\u626b\u63cf\u3002
\u907f\u514d\u5728WHERE\u4e2d\u5bf9\u5217\u4f7f\u7528\u51fd\u6570\uff08\u5982YEAR(Date)\u5bfc\u81f4\u7d22\u5f15\u5931\u6548\uff09\u3002
\u89c6\u56fe\u66f4\u65b0<\/strong>\uff1a
\u7b80\u5355\u89c6\u56fe\uff08\u884c\u5217\u5b50\u96c6\uff09\u53ef\u66f4\u65b0\uff0c\u590d\u6742\u89c6\u56fe\uff08\u542b\u5206\u7ec4\/\u805a\u5408\uff09\u4e0d\u53ef\u66f4\u65b0\u3002

\u4e94\u3001\u603b\u7ed3<\/strong><\/a>
DDL<\/strong>\uff1a\u5b9a\u4e49\u7ed3\u6784\uff08\u8868\u3001\u7d22\u5f15\u3001\u89c6\u56fe\uff09\uff0c\u9700\u6ce8\u610f\u7ea6\u675f\u548c\u7d22\u5f15\u8bbe\u8ba1\u3002
DML<\/strong>\uff1a\u64cd\u4f5c\u6570\u636e\uff0c\u91cd\u70b9\u638c\u63e1\u590d\u6742\u67e5\u8be2\uff08\u8fde\u63a5\u3001\u5b50\u67e5\u8be2\u3001\u5206\u7ec4\uff09\u3002
\u7d22\u5f15<\/strong>\uff1a\u52a0\u901f\u67e5\u8be2\u4f46\u9700\u6743\u8861\u7ef4\u62a4\u6210\u672c\u3002
\u89c6\u56fe<\/strong>\uff1a\u7b80\u5316\u64cd\u4f5c\u3001\u589e\u5f3a\u5b89\u5168\u6027\u548c\u903b\u8f91\u72ec\u7acb\u6027\u3002
\u4ee5\u4e0b\u662f\u7ed3\u5408\u77e5\u8bc6\u5e93\u5185\u5bb9\u6574\u7406\u7684SQL\u77e5\u8bc6\u70b9\uff0c\u91cd\u70b9\u8865\u5145\u4e86\u81ea\u8eab\u8fde\u63a5\u3001\u5de6\u53f3\u8fde\u63a5\u3001\u590d\u6742\u5d4c\u5957\u5b50\u67e5\u8be2<\/strong>\u7684\u793a\u4f8b\u4e0e\u8bb2\u89e3\uff0c\u4ee5\u53caWHERE\u4e0eHAVING\u7684\u533a\u522b<\/strong>\uff1a

\u516d\u3001\u8fde\u63a5\u67e5\u8be2<\/strong><\/a>
1. <\/strong><\/a>\u81ea\u8eab\u8fde\u63a5\uff08Self-Join\uff09<\/strong>
\u5b9a\u4e49<\/strong>\uff1a\u540c\u4e00\u5f20\u8868\u7684\u4e0d\u540c\u5b9e\u4f8b\u8fdb\u884c\u8fde\u63a5\uff0c\u901a\u8fc7\u522b\u540d\u533a\u5206\u3002
\u7528\u9014<\/strong>\uff1a\u5904\u7406\u8868\u5185\u6570\u636e\u7684\u5c42\u7ea7\u5173\u7cfb\u6216\u81ea\u53cd\u5173\u7cfb\uff08\u5982\u5458\u5de5\u4e0e\u7ecf\u7406\uff09\u3002
\u793a\u4f8b<\/strong>\uff1a
— \u67e5\u8be2\u6bcf\u4e2a\u5458\u5de5\u7684\u59d3\u540d\u53ca\u5176\u5bf9\u5e94\u7684\u7ecf\u7406\u59d3\u540d
SELECT e.Ename AS Employee, m.Ename AS Manager
FROM Employee e
LEFT JOIN Employee m ON e.MgrEno = m.Eno;\u00a0 — MgrEno\u662f\u7ecf\u7406\u7f16\u53f7
\u8bf4\u660e<\/strong>\uff1a
\u901a\u8fc7\u522b\u540de\uff08\u5458\u5de5\uff09\u548cm\uff08\u7ecf\u7406\uff09\u533a\u5206\u540c\u4e00\u8868\u7684\u4e24\u4e2a\u5b9e\u4f8b\u3002
\u4f7f\u7528LEFT JOIN\u786e\u4fdd\u65e0\u7ecf\u7406\u7684\u5458\u5de5\u4e5f\u663e\u793a\uff08\u5982CEO\uff09\u3002

2. <\/strong><\/a>\u5de6\u53f3\u8fde\u63a5\uff08LEFT\/RIGHT JOIN\uff09<\/strong>
\u5de6\u5916\u8fde\u63a5\uff08LEFT JOIN\uff09<\/strong>\uff1a
\u529f\u80fd<\/strong>\uff1a\u8fd4\u56de\u5de6\u8868\u6240\u6709\u8bb0\u5f55\uff0c\u53f3\u8868\u5339\u914d\u7684\u8bb0\u5f55\uff1b\u672a\u5339\u914d\u7684\u53f3\u8868\u5b57\u6bb5\u663e\u793aNULL\u3002
\u793a\u4f8b<\/strong>\uff1a
— \u67e5\u8be2\u6240\u6709\u90e8\u95e8\u53ca\u5458\u5de5\uff08\u5373\u4f7f\u90e8\u95e8\u65e0\u5458\u5de5\uff09
SELECT D.Dname, E.Ename
FROM Department D
LEFT JOIN Employee E ON D.Dno = E.Dno;
\u53f3\u5916\u8fde\u63a5\uff08RIGHT JOIN\uff09<\/strong>\uff1a
\u529f\u80fd<\/strong>\uff1a\u8fd4\u56de\u53f3\u8868\u6240\u6709\u8bb0\u5f55\uff0c\u5de6\u8868\u5339\u914d\u7684\u8bb0\u5f55\uff1b\u672a\u5339\u914d\u7684\u5de6\u8868\u5b57\u6bb5\u663e\u793aNULL\u3002
\u793a\u4f8b<\/strong>\uff1a
— \u67e5\u8be2\u6240\u6709\u5458\u5de5\u53ca\u5176\u90e8\u95e8\uff08\u5373\u4f7f\u5458\u5de5\u672a\u5206\u914d\u90e8\u95e8\uff09
SELECT E.Ename, D.Dname
FROM Employee E
RIGHT JOIN Department D ON E.Dno = D.Dno;
\u5bf9\u6bd4<\/strong>\uff1a
LEFT JOIN\u5173\u6ce8\u5de6\u8868\u5b8c\u6574\u6027\uff0cRIGHT JOIN\u5173\u6ce8\u53f3\u8868\u5b8c\u6574\u6027\u3002
\u901a\u8fc7\u4ea4\u6362\u8868\u987a\u5e8f\uff0c\u4e8c\u8005\u53ef\u4e92\u76f8\u8f6c\u6362\u3002

\u4e03\u3001\u590d\u6742\u5d4c\u5957\u5b50\u67e5\u8be2<\/strong><\/a>
1. <\/strong><\/a>\u5d4c\u5957\u5b50\u67e5\u8be2<\/strong>
\u5b9a\u4e49<\/strong>\uff1a\u5b50\u67e5\u8be2\u72ec\u7acb\u6267\u884c\uff0c\u7ed3\u679c\u4f20\u9012\u7ed9\u5916\u90e8\u67e5\u8be2\u3002
\u793a\u4f8b<\/strong>\uff1a
— \u67e5\u8be2\u90e8\u95e8\u201c\u6280\u672f\u79d1\u201d\u7684\u5458\u5de5\u59d3\u540d
SELECT Ename
FROM Employee
WHERE Dno = (SELECT Dno FROM Department WHERE Dname = '\u6280\u672f\u79d1');
2. <\/strong><\/a>\u76f8\u5173\u5b50\u67e5\u8be2<\/strong>
\u5b9a\u4e49<\/strong>\uff1a\u5b50\u67e5\u8be2\u4f9d\u8d56\u5916\u90e8\u67e5\u8be2\u7684\u503c\uff0c\u9010\u884c\u6267\u884c\u3002
\u793a\u4f8b<\/strong>\uff1a
— \u67e5\u8be2\u6bd4\u90e8\u95e8\u5e73\u5747\u5e74\u9f84\u5927\u7684\u5458\u5de5
SELECT Ename, Age
FROM Employee E1
WHERE Age > (SELECT AVG(Age) FROM Employee E2 WHERE E2.Dno = E1.Dno);
3. EXISTS<\/strong><\/a>\u5b50\u67e5\u8be2<\/strong>
\u529f\u80fd<\/strong>\uff1a\u5224\u65ad\u5b50\u67e5\u8be2\u662f\u5426\u8fd4\u56de\u7ed3\u679c\uff0c\u5e38\u7528\u4e8e\u5b58\u5728\u6027\u68c0\u67e5\u3002
\u793a\u4f8b<\/strong>\uff1a
— \u67e5\u8be2\u53c2\u4e0e\u8fc7\u9879\u76ee\u7684\u5458\u5de5
SELECT Ename
FROM Employee E
WHERE EXISTS (SELECT 1 FROM Item_Emp WHERE Eno = E.Eno);

\u516b\u3001WHERE<\/strong><\/a>\u4e0eHAVING\u7684\u533a\u522b<\/strong>
\u5bf9\u6bd4\u9879<\/strong>
WHERE<\/strong>
HAVING<\/strong>
\u4f5c\u7528\u5bf9\u8c61<\/strong>
\u884c\uff08\u57fa\u8868\u6216\u89c6\u56fe\uff09
\u7ec4\uff08\u5206\u7ec4\u540e\u7684\u7ed3\u679c\uff09
\u6267\u884c\u65f6\u95f4<\/strong>
\u5206\u7ec4\u524d\u8fc7\u6ee4
\u5206\u7ec4\u540e\u8fc7\u6ee4
\u805a\u5408\u51fd\u6570<\/strong>
\u4e0d\u53ef\u4f7f\u7528\uff08\u5982AVG, SUM\uff09
\u53ef\u4f7f\u7528\u805a\u5408\u51fd\u6570
\u793a\u4f8b<\/strong>
WHERE Age > 30
HAVING AVG(Age) > 30
\u5178\u578b\u573a\u666f<\/strong>\uff1a
— WHERE\u8fc7\u6ee4\u884c\uff0cHAVING\u8fc7\u6ee4\u7ec4
SELECT Dno, AVG(Age) AS AvgAge
FROM Employee
WHERE Age > 20\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — \u8fc7\u6ee4\u5e74\u9f84>20\u7684\u5458\u5de5
GROUP BY Dno
HAVING AVG(Age) > 30;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — \u8fc7\u6ee4\u5e73\u5747\u5e74\u9f84>30\u7684\u90e8\u95e8

\u4e5d\u3001\u91cd\u70b9\u603b\u7ed3<\/strong><\/a>
\u81ea\u8eab\u8fde\u63a5<\/strong>\uff1a\u901a\u8fc7\u522b\u540d\u5904\u7406\u8868\u5185\u5c42\u7ea7\u5173\u7cfb\uff08\u5982\u5458\u5de5\u4e0e\u7ecf\u7406\uff09\u3002
\u5de6\u53f3\u8fde\u63a5<\/strong>\uff1a
LEFT JOIN\u4fdd\u7559\u5de6\u8868\u6240\u6709\u884c\uff0cRIGHT JOIN\u4fdd\u7559\u53f3\u8868\u6240\u6709\u884c\u3002
\u672a\u5339\u914d\u7684\u5b57\u6bb5\u663e\u793a\u4e3aNULL\u3002
\u590d\u6742\u5b50\u67e5\u8be2<\/strong>\uff1a
\u5d4c\u5957\u5b50\u67e5\u8be2\u72ec\u7acb\u6267\u884c\uff0c\u76f8\u5173\u5b50\u67e5\u8be2\u9010\u884c\u4f9d\u8d56\u5916\u90e8\u67e5\u8be2\u3002
EXISTS\u7528\u4e8e\u5b58\u5728\u6027\u5224\u65ad\u3002
WHERE vs HAVING<\/strong>\uff1a
WHERE\u5728\u5206\u7ec4\u524d\u8fc7\u6ee4\u884c\uff0c\u4e0d\u53ef\u7528\u805a\u5408\u51fd\u6570\u3002
HAVING\u5728\u5206\u7ec4\u540e\u8fc7\u6ee4\u7ec4\uff0c\u5fc5\u987b\u4e0eGROUP BY\u642d\u914d\u4f7f\u7528\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"

\u4e00\u3001SQL\u8bed\u8a00\u5206\u7c7bDDL\uff08\u6570\u636e\u5b9a\u4e49\u8bed\u8a00\uff09\u529f\u80fd\uff1a\u5b9a\u4e49\/\u4fee\u6539\u6570\u636e\u5e93\u7ed3\u6784\uff08\u8868\u3001\u7d22\u5f15\u3001\u89c6\u56fe\uff09\u3002\u6838\u5fc3\u8bed\u53e5\uff1aCREATE, ALTER, DROP\u3002DML\uff08\u6570\u636e\u64cd\u7eb5\u8bed\u8a00\uff09\u529f\u80fd\uff1a\u64cd\u4f5c\u6570\u636e\uff08\u589e\u5220\u6539\u67e5\uff09\u3002\u6838\u5fc3\u8bed\u53e5\uff1aINSERT, UPDATE, DELETE, SELECT\u3002DCL\uff08\u6570\u636e\u63a7\u5236\u8bed\u8a00\uff09\u529f\u80fd\uff1a\u7ba1\u7406\u6743\u9650\u3002\u6838\u5fc3\u8bed\u53e5\uff1aGRANT, REVOKE\u3002 \u4e8c\u3001\u6570\u636e\u5b9a\u4e49\u8bed\u8a00\uff08DDL\uff091. \u8868\u64cd\u4f5cCREATE TABLE\u529f\u80fd\uff1a\u521b\u5efa\u65b0\u8868\uff0c\u5b9a\u4e49\u5217\u3001\u6570\u636e\u7c7b\u578b\u53ca\u7ea6\u675f\u3002\u793a\u4f8b\uff1aCREATE TABLE Employee (\u00a0 Eno CHAR(4) PRIMARY KEY,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — \u4e3b\u952e\u7ea6\u675f\u00a0 Ename VARCHAR(10) NOT NULL,\u00a0\u00a0\u00a0 — \u975e\u7a7a\u7ea6\u675f\u00a0 Age INT CHECK (Age >= 18),\u00a0\u00a0\u00a0\u00a0 — \u68c0\u67e5\u7ea6\u675f\uff08\u5e74\u9f84\u226518\uff09\u00a0 Dno CHAR(2) REFERENCES Department(Dno)\u00a0 — \u5916\u952e\u7ea6\u675f);ALTER TABLE\u529f\u80fd\uff1a\u4fee\u6539\u8868\u7ed3\u6784\uff08\u589e\u5220\u5217\u3001\u7ea6\u675f\uff09\u3002\u793a\u4f8b\uff1aALTER TABLE Employee ADD COLUMN Phone VARCHAR(11);\u00a0 — \u65b0\u589e\u5217ALTER TABLE Employee DROP COLUMN Phone;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — \u5220\u9664\u5217ALTER … Continue reading 2025-04-18-\u6570\u636e\u5e93-\u7b2c\u4e09\u7ae0-\u77e5\u8bc6\u70b9\u6574\u7406<\/span><\/a><\/p>\n","protected":false},"author":20615,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_locale":"en_US","_original_post":"https:\/\/zd1269878.noblogs.org\/?p=172","activitypub_content_warning":"","activitypub_content_visibility":"","activitypub_max_image_attachments":4,"activitypub_interaction_policy_quote":"anyone","activitypub_status":"federated","footnotes":""},"categories":[15],"tags":[16],"class_list":["post-172","post","type-post","status-publish","format-standard","hentry","category-15","tag-16","en-US"],"_links":{"self":[{"href":"https:\/\/zd1269878.noblogs.org\/wp-json\/wp\/v2\/posts\/172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zd1269878.noblogs.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zd1269878.noblogs.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zd1269878.noblogs.org\/wp-json\/wp\/v2\/users\/20615"}],"replies":[{"embeddable":true,"href":"https:\/\/zd1269878.noblogs.org\/wp-json\/wp\/v2\/comments?post=172"}],"version-history":[{"count":2,"href":"https:\/\/zd1269878.noblogs.org\/wp-json\/wp\/v2\/posts\/172\/revisions"}],"predecessor-version":[{"id":289,"href":"https:\/\/zd1269878.noblogs.org\/wp-json\/wp\/v2\/posts\/172\/revisions\/289"}],"wp:attachment":[{"href":"https:\/\/zd1269878.noblogs.org\/wp-json\/wp\/v2\/media?parent=172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zd1269878.noblogs.org\/wp-json\/wp\/v2\/categories?post=172"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zd1269878.noblogs.org\/wp-json\/wp\/v2\/tags?post=172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}