表的結構如下：表結構：
CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5))
go
CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL)
go
CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL)
go
CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL)
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾華'
,'男' ,1977-09-01,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明'
,'男' ,1975-10-02,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王麗'
,'女' ,1976-01-23,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李軍'
,'男' ,1976-02-20,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳'
,'女' ,1975-02-10,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陸君'
,'男' ,1974-06-03,95031);
GO
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'計算機導論',825)
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'作業系統' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'資料電路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等數學' ,100);
GO
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
GO
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (804,'李誠','男','1958-12-02','副教授','計算機系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (856,'張旭','男','1969-03-12','講師','電子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'王萍','女','1972-05-05','助教','計算機系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (831,'劉冰','女','1977-08-14','助教','電子工程系');
題目如下：查詢“95033”班所選課程的平均分。
自己寫出來差不多四個寫法。其中使用多表查詢與聯接查詢的結果不符。
第一種：
SELECT AVG(degree)FROM score
LEFT JOIN student ON student.SNO=score.SNO
GROUP BY class
HAVING class='95033'
第二種：
SELECT AVG(degree)FROM score
JOIN student ON student.SNO=score.SNO
WHERE classd='95033';
以上兩種查詢結果皆為：avg(degree)
79.66667
第三種：
SELECT AVG(degree) FROM score,student
GROUP BY class
HAVING class='95033'
第四種：
SELECT AVG(degree) FROM score,student
WHERE class='95033'
后兩邊兒的查詢結果皆為80.25000
哪個結果是錯的？為什么錯。
uj5u.com熱心網友回復：
第二種 SELECT * FROM score,student 沒有SCORE.SNO = STUDENT.SNO 的話其實是做了笛卡爾積。SELECT * FROM score,studentWHERE class='95033'出來的記錄有36行。
做聯結查詢的話只有6行。
沒有注意聯接查詢到與多表查詢的區別。基礎不夠扎實。
uj5u.com熱心網友回復：
第2個是對的uj5u.com熱心網友回復：
select avg(DEGREE) from score where sno in ( SELECT sno from student where class = 95033)轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/93777.html
標籤：MySQL
上一篇：Sybase Central連接遠程資料庫需要驅動嗎
下一篇：mongo樹結構操作