{"id":206,"date":"2025-05-16T17:13:09","date_gmt":"2025-05-16T09:13:09","guid":{"rendered":"https:\/\/zd1269878.noblogs.org\/?p=206"},"modified":"2025-10-21T10:31:55","modified_gmt":"2025-10-21T02:31:55","slug":"2025-05-16-%e6%95%b0%e6%8d%ae%e5%ba%93-%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\/05\/16\/2025-05-16-%e6%95%b0%e6%8d%ae%e5%ba%93-%e7%9f%a5%e8%af%86%e7%82%b9%e6%95%b4%e7%90%86\/","title":{"rendered":"2025-05-16-\u6570\u636e\u5e93-\u77e5\u8bc6\u70b9\u6574\u7406"},"content":{"rendered":"\n

\u9898\u76ee\u7b54\u6848:<\/p>\n\n\n\n

11-12\u9898
11
(1)ALTER TABLE STUDENT ADD NATION VARCHAR2(20);
(2)ALTER TABLE STUDENT DROP COLUMN NATION;
(3)INSERT INTO GRADE VALUES ('2021110','3','80');
(4)UPDATE GRADE SET GMARK='70' WHERE SNO='2021110';
(5)DELETE FROM GRADE WHERE SNO='2021110';
(6)CREATE INDEX IX_CLASS ON GRADE(CNO ASC);
(7)DROP INDEX IX_CLASS;






12
(1)SELECT DISTINCT CNO FROM GRADE;
(2)SELECT * FROM STUDENT T WHERE T.SsEX='\u5973' AND T.CLNO='20311';
(3)SELECT T.SNAME,T.SSEX,T.SBIRTH FROM STUDENT T WHERE T.CLNO='20311' OR T.CLNO='20312';
(4)SELECT * FROM STUDENT T WHERE T.SNAME LIKE '\u674e%';
(5)SELECT COUNT(*)
FROM STUDENT S
WHERE S.CLNO=(SELECT CLNO FROM STUDENT WHERE SNAME=\"\u674e\u52c7\")
(6)SELECT
MAX(GMARK) AS \u6700\u9ad8\u5206,
MIN(GMARK) AS \u6700\u4f4e\u5206,
AVG(GMARK) AS \u5e73\u5747\u5206
FROM GRADE
WHERE CNO = (SELECT CNO FROM COURSE WHERE CNAME = '\u64cd\u4f5c\u7cfb\u7edf');
(7)SELECT COUNT(DISTINCT SNO)
FROM GRADE;
(8)SELECT COUNT(DISTINCT SNO)
FROM GRADE WHERE CNO=(SELECT CNO FROM COURSE WHERE CNAME='\u64cd\u4f5c\u7cfb\u7edf');
(9)
SELECT S.SNAME
FROM STUDENT S LEFT JOIN CLASS C ON S.CLNO=C.CLNO LEFT JOIN GRADE G ON S.SNO=G.SNO
WHERE C.INYEAR='2020' AND C.SPECIAL='\u8ba1\u7b97\u673a\u79d1\u5b66\u4e0e\u6280\u672f' AND G.GMARK IS NULL;<\/p>\n\n\n\n


(10)
SELECT C1.CNAME, C2.CNAME
\u00a0\u00a0\u00a0 FROM COURSE C1,COURSE C2
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE C2.CNO=C1.CPNO OR C1.CPNO IS NULL;
13
(1)
SELECT *
\u00a0\u00a0\u00a0 FROM STUDENT S
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE S.CLNO=(SELECT T.CLNO
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM STUDENT T
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE T.SNAME='\u674e\u52c7'
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 );
(2)
SELECT S.*
\u00a0\u00a0\u00a0 FROM STUDENT S,GRADE G
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE S.SNO=G.SNO
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND G.CNO IN (SELECT G2.CNO
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM GRADE G2,STUDENT S2
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE G2.SNO=S2.SNO
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND S2.SNAME='\u674e\u52c7');
(3)
SELECT *
\u00a0\u00a0\u00a0 FROM STUDENT S
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE S.SBIRTH BETWEEN (SELECT S.SBIRTH
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM STUDENT S2
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE S2.SNAME='\u674e\u52c7')
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND to_date('2005-01-01','yyyy-mm-dd');
(4)
SELECT S.SNO,S.SNAME
\u00a0\u00a0\u00a0 FROM STUDENT S,COURSE C,GRADE G
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE C.CNAME='\u64cd\u4f5c\u7cfb\u7edf'
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND C.CNO=G.CNO
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND S.SNO=G.SNO;
(5)
SELECT S.SNAME
FROM STUDENT S
WHERE NOT EXISTS (
\u00a0\u00a0\u00a0 SELECT 1
\u00a0\u00a0\u00a0 FROM GRADE G
\u00a0\u00a0\u00a0 WHERE G.SNO = S.SNO AND G.CNO = '1'
);
(6)
SELECT G1.SNO, G1.CNO
FROM GRADE G1
WHERE G1.GMARK > (
\u00a0\u00a0\u00a0 SELECT AVG(G2.GMARK)
\u00a0\u00a0\u00a0 FROM GRADE G2
\u00a0\u00a0\u00a0 WHERE G2.SNO = G1.SNO
);
(7)
SELECT S.SNAME
FROM STUDENT S
WHERE NOT EXISTS (
\u00a0\u00a0\u00a0 SELECT C.CNO
\u00a0\u00a0\u00a0 FROM COURSE C
\u00a0\u00a0\u00a0 WHERE NOT EXISTS (
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT G.CNO
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM GRADE G
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE G.SNO = S.SNO AND G.CNO = C.CNO
\u00a0\u00a0\u00a0 )
);
14
(1)

15
(1)

16
(1)<\/p>\n\n\n\n

\u6570\u636e\u5e93\u77e5\u8bc6\u6574\u7406<\/a><\/h1>\n\n\n\n
\n\n\n\n

\u4e00\u3001SQL <\/strong><\/a>\u67e5\u8be2\u57fa\u7840<\/strong><\/h2>\n\n\n\n

1. <\/a>\u5d4c\u5957\u5b50\u67e5\u8be2<\/h3>\n\n\n\n