整合營銷服務商
電腦端+手機端+微信端=數據同步管理
免費咨詢熱線：
電腦端+手機端+微信端=數據同步管理
免費咨詢熱線：
一：準備數據
#創建表及插入記錄
CREATE TABLE class (
cid int(11) NOT NULL AUTO_INCREMENT,
caption varchar(32) NOT NULL,
PRIMARY KEY (cid)
) ENGINE=InnoDB CHARSET=utf8;
INSERT INTO class VALUES
(1, '三年二班'),
(2, '三年三班'),
(3, '一年二班'),
(4, '二年九班');
CREATE TABLE course(
cid int(11) NOT NULL AUTO_INCREMENT,
cname varchar(32) NOT NULL,
teacher_id int(11) NOT NULL,
PRIMARY KEY (cid),
KEY fk_course_teacher (teacher_id),
CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO course VALUES
(1, '生物', 1),
(2, '物理', 2),
(3, '體育', 3),
(4, '美術', 2);
CREATE TABLE score (
sid int(11) NOT NULL AUTO_INCREMENT,
student_id int(11) NOT NULL,
course_id int(11) NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (sid),
KEY fk_score_student (student_id),
KEY fk_score_course (course_id),
CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),
CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO score VALUES
(1, 1, 1, 10),
(2, 1, 2, 9),
(5, 1, 4, 66),
(6, 2, 1, 8),
(8, 2, 3, 68),
(9, 2, 4, 99),
(10, 3, 1, 77),
(11, 3, 2, 66),
(12, 3, 3, 87),
(13, 3, 4, 99),
(14, 4, 1, 79),
(15, 4, 2, 11),
(16, 4, 3, 67),
(17, 4, 4, 100),
(18, 5, 1, 79),
(19, 5, 2, 11),
(20, 5, 3, 67),
(21, 5, 4, 100),
(22, 6, 1, 9),
(23, 6, 2, 100),
(24, 6, 3, 67),
(25, 6, 4, 100),
(26, 7, 1, 9),
(27, 7, 2, 100),
(28, 7, 3, 67),
(29, 7, 4, 88),
(30, 8, 1, 9),
(31, 8, 2, 100),
(32, 8, 3, 67),
(33, 8, 4, 88),
(34, 9, 1, 91),
(35, 9, 2, 88),
(36, 9, 3, 67),
(37, 9, 4, 22),
(38, 10, 1, 90),
(39, 10, 2, 77),
(40, 10, 3, 43),
(41, 10, 4, 87),
(42, 11, 1, 90),
(43, 11, 2, 77),
(44, 11, 3, 43),
(45, 11, 4, 87),
(46, 12, 1, 90),
(47, 12, 2, 77),
(48, 12, 3, 43),
(49, 12, 4, 87),
(52, 13, 3, 87);
CREATE TABLE student(
sid int(11) NOT NULL AUTO_INCREMENT,
gender char(1) NOT NULL,
class_id int(11) NOT NULL,
sname varchar(32) NOT NULL,
PRIMARY KEY (sid),
KEY fk_class (class_id),
CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO student VALUES
(1, '男', 1, '理解'),
(2, '女', 1, '鋼蛋'),
(3, '男', 1, '張三'),
(4, '男', 1, '張一'),
(5, '女', 1, '張二'),
(6, '男', 1, '張四'),
(7, '女', 2, '鐵錘'),
(8, '男', 2, '李三'),
(9, '男', 2, '李一'),
(10, '女', 2, '李二'),
(11, '男', 2, '李四'),
(12, '女', 3, '如花'),
(13, '男', 3, '劉三'),
(14, '男', 3, '劉一'),
(15, '女', 3, '劉二'),
(16, '男', 3, '劉四');
CREATE TABLE teacher(
tid int(11) NOT NULL AUTO_INCREMENT,
tname varchar(32) NOT NULL,
PRIMARY KEY (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO teacher VALUES
(1, '張磊老師'),
(2, '李平老師'),
(3, '劉海燕老師'),
(4, '朱云海老師'),
(5, '李杰老師');
二、題目一
1、查詢所有的課程的名稱以及對應的任課老師姓名
2、查詢學生表中男女生各有多少人
3、查詢物理成績等于100的學生的姓名
4、查詢平均成績大于八十分的同學的姓名和平均成績
5、查詢所有學生的學號，姓名，選課數，總成績
6、 查詢姓李老師的個數
7、 查詢沒有報李平老師課的學生姓名
8、 查詢物理課程比生物課程高的學生的學號
9、 查詢沒有同時選修物理課程和體育課程的學生姓名
10、查詢掛科超過兩門(包括兩門)的學生姓名和班級
、查詢選修了所有課程的學生姓名
12、查詢李平老師教的課程的所有成績記錄
13、查詢全部學生都選修了的課程號和課程名
14、查詢每門課程被選修的次數
15、查詢之選修了一門課程的學生姓名和學號
16、查詢所有學生考出的成績并按從高到低排序（成績去重）
17、查詢平均成績大于85的學生姓名和平均成績
18、查詢生物成績不及格的學生姓名和對應生物分數
19、查詢在所有選修了李平老師課程的學生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學生姓名
20、查詢每門課程成績最好的前兩名學生姓名
21、查詢不同課程但成績相同的學號，課程號，成績
22、查詢沒學過“葉平”老師課程的學生姓名以及選修的課程名稱；
23、查詢所有選修了學號為1的同學選修過的一門或者多門課程的同學學號和姓名；
24、任課最多的老師中學生單科成績最高的學生姓名
三、答案
#1、查詢所有的課程的名稱以及對應的任課老師姓名
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
#2、查詢學生表中男女生各有多少人
SELECT
gender 性別,
count(1) 人數
FROM
student
GROUP BY
gender;
#3、查詢物理成績等于100的學生的姓名
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
INNER JOIN course ON score.course_id = course.cid
WHERE
course.cname = '物理'
AND score.num = 100
);
#4、查詢平均成績大于八十分的同學的姓名和平均成績
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
student_id,
avg(num) AS avg_num
FROM
score
GROUP BY
student_id
HAVING
avg(num) > 80
) AS t1 ON student.sid = t1.student_id;
#5、查詢所有學生的學號，姓名，選課數，總成績(注意：對于那些沒有選修任何課程的學生也算在內)
SELECT
student.sid,
student.sname,
t1.course_num,
t1.total_num
FROM
student
LEFT JOIN (
SELECT
student_id,
COUNT(course_id) course_num,
sum(num) total_num
FROM
score
GROUP BY
student_id
) AS t1 ON student.sid = t1.student_id;
#6、 查詢姓李老師的個數
SELECT
count(tid)
FROM
teacher
WHERE
tname LIKE '李%';
#7、 查詢沒有報李平老師課的學生姓名(找出報名李平老師課程的學生，然后取反就可以)
SELECT
student.sname
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course.cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = '李平老師'
)
);
#8、 查詢物理課程比生物課程高的學生的學號(分別得到物理成績表與生物成績表，然后連表即可)
SELECT
t1.student_id
FROM
(
SELECT
student_id,
num
FROM
score
WHERE
course_id = (
SELECT
cid
FROM
course
WHERE
cname = '物理'
)
) AS t1
INNER JOIN (
SELECT
student_id,
num
FROM
score
WHERE
course_id = (
SELECT
cid
FROM
course
WHERE
cname = '生物'
)
) AS t2 ON t1.student_id = t2.student_id
WHERE
t1.num > t2.num;
#9、 查詢沒有同時選修物理課程和體育課程的學生姓名(沒有同時選修指的是選修了一門的，思路是得到物理+體育課程的學生信息表，然后基于學生分組，統計count(課程)=1)
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
cname = '物理'
OR cname = '體育'
)
GROUP BY
student_id
HAVING
COUNT(course_id) = 1
);
#10、查詢掛科超過兩門(包括兩門)的學生姓名和班級(求出=2)
SELECT
student.sname,
class.caption
FROM
student
INNER JOIN (
SELECT
student_id
FROM
score
WHERE
num < 60
GROUP BY
student_id
HAVING
count(course_id) >= 2
) AS t1
INNER JOIN class ON student.sid = t1.student_id
AND student.class_id = class.cid;
#11、查詢選修了所有課程的學生姓名(先從course表統計課程的總數，然后基于score表按照分組，統計課程數據等于課程總數即可)
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
COUNT(course_id) = (SELECT count(cid) FROM course)
);
#12、查詢李平老師教的課程的所有成績記錄
SELECT
*
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = '李平老師'
);
#13、查詢全部學生都選修了的課程號和課程名(取所有學生數，然后基于score表的課程分組，找出count()等于學生數即可)
SELECT
cid,
cname
FROM
course
WHERE
cid IN (
SELECT
course_id
FROM
score
GROUP BY
course_id
HAVING
COUNT(student_id) = (
SELECT
COUNT(sid)
FROM
student
)
);
#14、查詢每門課程被選修的次數
SELECT
course_id,
COUNT(student_id)
FROM
score
GROUP BY
course_id;
#15、查詢之選修了一門課程的學生姓名和學號
SELECT
sid,
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
COUNT(course_id) = 1
);
#16、查詢所有學生考出的成績并按從高到低排序（成績去重）
SELECT DISTINCT
num
FROM
score
ORDER BY
num DESC;
#17、查詢平均成績大于85的學生姓名和平均成績
SELECT
sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
student_id,
avg(num) avg_num
FROM
score
GROUP BY
student_id
HAVING
AVG(num) > 85
) t1 ON student.sid = t1.student_id;
#18、查詢生物成績不及格的學生姓名和對應生物分數
SELECT
sname 姓名,
num 生物成績
FROM
score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.sid
WHERE
course.cname = '生物'
AND score.num < 60;
#19、查詢在所有選修了李平老師課程的學生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學生姓名
SELECT
sname
FROM
student
WHERE
sid = (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course.cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = '李平老師'
)
GROUP BY
student_id
ORDER BY
AVG(num) DESC
LIMIT 1
);
#20、查詢每門課程成績最好的前兩名學生姓名
#查看每門課程按照分數排序的信息，為下列查找正確與否提供依據
SELECT
*
FROM
score
ORDER BY
course_id,
num DESC;
#表1：求出每門課程的課程,與最高分數
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id;
#表2：去掉最高分，再按照課程分組，取得的最高分，就是第二高的分數
second_num
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id;
#將表1和表2聯合到一起，得到一張表t3，包含課程與該們課程的
first_num與second_num
SELECT
t1.course_id,
t1.first_num,
t2.second_num
FROM
(
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t1
INNER JOIN (
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id
) AS t2 ON t1.course_id = t2.course_id;
#查詢前兩名的學生（有可能出現并列第一或者并列第二的情況）
SELECT
score.student_id,
t3.course_id,
t3.first_num,
t3.second_num
FROM
score
INNER JOIN (
SELECT
t1.course_id,
t1.first_num,
t2.second_num
FROM
(
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t1
INNER JOIN (
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id
) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHERE
score.num >= t3.second_num
AND score.num <= t3.first_num;
#排序后可以看的明顯點
SELECT
score.student_id,
t3.course_id,
t3.first_num,
t3.second_num
FROM
score
INNER JOIN (
SELECT
t1.course_id,
t1.first_num,
t2.second_num
FROM
(
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t1
INNER JOIN (
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id
) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHERE
score.num >= t3.second_num
AND score.num <= t3.first_num
ORDER BY
course_id;
#可以用以下命令驗證上述查詢的正確性
SELECT
*
FROM
score
ORDER BY
course_id,
num DESC;
– 21、查詢不同課程但成績相同的學號，課程號，成績
– 22、查詢沒學過“葉平”老師課程的學生姓名以及選修的課程名稱；
– 23、查詢所有選修了學號為1的同學選修過的一門或者多門課程的同學學號和姓名；
– 24、任課最多的老師中學生單科成績最高的學生姓名
*請認真填寫需求信息，我們會在24小時內與您取得聯系。
