mysql练习题1

一、准备数据

#创建表及插入记录
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、查询“c001”课程比“c002”课程成绩高的所有学生的学号

select sno from student
where (select score from sc where cno = 'c001' and sc.sno=student.sno) >
(select score from sc where cno = 'c002' and sc.sno = student.sno);

2、查询平均成绩大于60 分的同学的学号和平均成绩；

select sno,avg(score) as average from sc group by sno having average > 60;

3、查询所有同学的学号、姓名、选课数、总成绩；

select student.sno,sname,count(*),sum(score) from student
left join sc on sc.sno = student.sno
group by student.sno;

4、查询姓“刘”的老师的个数；

select count(*) from teacher where tname like '刘%';

5、查询没学过“谌燕”老师课的同学的学号、姓名；

(1)查询字段（学号，姓名）--student表
select sno,sname from student;

(2)条件：teacher表姓名=‘谌燕’-->tno->(course-->cno)-->(sc->查找没有记录的)
查询出谌燕的tno
select tno from teacher where tname = '谌燕';
根据谌燕的tno 查询出她所教的课程的cno
select cno from course where tno = (select tno from teacher where tname = '谌燕');
根据tno查询sc表中没有记录的sno
select sno from sc where (select cno from course where tno = (select tno from teacher where tname = '谌燕') and sc.cno = course.cno) not in 
		(select cno from sc group by sno);
第二种做法
SELECT student.sno as "学号",student.sname as "姓名"
FROM sc JOIN student ON sc.sno = student.sno
WHERE sc.sno NOT IN (
	SELECT sc.sno
	FROM sc
	WHERE sc.cno IN(
		SELECT course.cno
		FROM teacher JOIN course ON teacher.tno = course.tno
		WHERE teacher.tname = '谌燕'
	)
);

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

解题思路：找出每门课程学习的学生集合，如果一个学生在一个集合里，就说明这个学生学了这门课

select student.sno,student.sname from student
join sc on sc.sno = student.sno
where sc.sno in (select sno from sc where cno = 'c001')
and sc.sno in (select sno from sc where cno = 'c002')
group by student.sno;

7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；

条件：teacher(tname=’谌燕’)-->course(所有的cno)==》cno的集合
select cno from course where tno = (select tno from teacher where tname = '谌燕');

==>分组统计（某个学生选了多少门‘谌燕’老师的课程）
select count(cno) from sc where cno in (select cno from course where tno = (select tno from teacher where tname = '谌燕')) group by sno;

==>判断学生选课数量等于‘谌燕’老师所教课程的数量
select sno,sname from student where 
(select count(*) from course where tno = (select tno from teacher where tname = '谌燕'))
= 
(select count(*) from sc where cno in (select cno from course where tno = (select tno from teacher where tname = '谌燕')) and sc.sno = student.sno group by sno);

8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；

(1)查询出学生课程'c001'的成绩
select sno,score from sc where cno = 'c002';

(2)将两门成绩进行对比并查询出符合条件的学生
select sno,sname from student where 
(select score from sc where cno = 'c001' and sc.sno = student.sno) > 
(select score from sc where cno = 'c002' and sc.sno = student.sno);

9、查询所有课程成绩小于60 分的同学的学号、姓名

理解：查找课程成绩有小于60分的同学，而不是学生学的所有课程都不及格的学生
SELECT DISTINCT student.sno,student.sname FROM student
INNER JOIN sc ON sc.sno = student.sno
WHERE score < 60;

10、查询没有学全所有课的同学的学号、姓名；

通过查询所有课程数量与学生学习的课程数量对比
select stu.sno,stu.sname from student as stu
where (select count(*) from course) > (select count(*) as sum from sc where stu.sno = sc.sno group by sc.sno);

11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；

SELECT DISTINCT student.sno,sname FROM student
INNER JOIN sc ON sc.sno = student.sno
WHERE cno IN
(SELECT cno FROM sc WHERE sno = 's001')
AND sc.sno <> 'c001';

12、查询至少学过学号为“s001”同学所有课的其他同学学号和姓名；

查询时判断课程是否在s001同学学过的课程里，并且不在s001同学没学过的课程,
最后再判断所学课程数量是否等于s001同学的课程数量
SELECT student.sno,sname FROM student
INNER JOIN sc ON student.sno = sc.sno
WHERE cno IN (SELECT sno FROM sc WHERE sno = 's001')
AND cno NOT IN (SELECT sno FROM sc WHERE sno <> 's001')
GROUP BY sno HAVING COUNT(*) >=
(SELECT COUNT(*) FROM sc WHERE sno = 's001');

13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；

UPDATE sc AS s
SET score = 
(SELECT	AVG( s.score ) FROM course  AS c 
INNER JOIN teacher AS t
ON ( t.tno = c.tno ) WHERE t.tname = '谌燕' AND c.cno = s.cno	GROUP BY s.cno) 
WHERE cno IN (SELECT cno FROM course JOIN teacher
ON ( teacher.tno = course.tno) WHERE teacher.tname = '谌燕' GROUP BY cno);

14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；

与12题类似
SELECT student.sno,sname FROM student
INNER JOIN sc ON student.sno = sc.sno
WHERE cno IN (SELECT sno FROM sc WHERE sno = 's001')
AND cno NOT IN (SELECT sno FROM sc WHERE sno <> 's001')
GROUP BY sno HAVING COUNT(*) =
(SELECT COUNT(*) FROM sc WHERE sno = 's001');

15、删除学习“谌燕”老师课的SC 表记录；

DELETE FROM sc WHERE cno IN 
(SELECT cno FROM course
INNER JOIN teacher ON course.tno = teacher.tno AND tname = '谌燕');

16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；

INSERT INTO sc(sno, cno, score) SELECT sno,'c002',(SELECT AVG(score) FROM sc WHERE cno = 'c002')
FROM student WHERE sno NOT IN 
(SELECT sno FROM sc WHERE cno = 'c002');

17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT cno,max(score),min(score) FROM sc GROUP BY cno;

18、按各科平均成绩从低到高和及格率的百分数从高到低顺序

求平均成绩
SELECT AVG(score) AS avgs, FROM sc GROUP BY sno ORDER BY avgs DESC;

SELECT cname,
AVG(sc.score) AS savg
,CONCAT(LEFT((SELECT COUNT(*) FROM sc AS s2 WHERE s2.cno = sc.cno AND s2.score>=60)/
(SELECT COUNT(*) FROM sc AS s3 WHERE s3.cno = sc.cno)*100,4),'%')
AS passrate
FROM sc INNER JOIN course ON course.cno = sc.cno
GROUP BY sc.cno,course.cname ORDER BY savg,passrate DESC;

19、查询不同老师所教不同课程平均分从高到低显示

SELECT c.cname,tname,AVG(score) AS avgs FROM sc s
INNER JOIN
course c ON s.cno = c.cno
INNER JOIN teacher on c.tno = teacher.tno
GROUP BY c.cno,c.tno ORDER BY avgs desc;

20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

SELECT course.cno,course.cname,
sum(CASE WHEN score > 85 AND score <= 100 THEN 1 ELSE 0 END) AS '[100-85]',
sum(CASE WHEN score > 70 AND score <= 85 THEN 1 ELSE 0 END) AS '[85-70]',
sum(CASE WHEN score > 60 AND score <= 70 THEN 1 ELSE 0 END) AS '[70-60]',
sum(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS '[<60]'
FROM course
INNER JOIN sc ON course.cno = sc.cno
GROUP BY sc.cno,cname;

21、查询各科成绩前三名的记录:(不考虑成绩并列情况)

SELECT score,sc.cno,course.cno,course.cname
FROM sc 
LEFT JOIN course ON (sc.cno = course.cno)
GROUP BY sc.cno,sc.score,course.cname
ORDER BY course.cname,sc.score DESC;

22、查询每门课程被选修的学生数

SELECT cname,COUNT(*) FROM sc
INNER JOIN course ON sc.cno = course.cno
GROUP BY course.cname;

23、查询出只选修了一门课程的全部学生的学号和姓名

学生选修的课程数量
SELECT sno,COUNT(*) FROM sc GROUP BY sno;


SELECT s.sno,sname FROM student s
INNER JOIN sc ON sc.sno = s.sno
GROUP BY sc.sno HAVING COUNT(*) = 1;

24、查询男生、女生人数

SELECT ssex,COUNT(*) FROM student GROUP BY ssex;

25、查询姓“张”的学生名单

SELECT * FROM student WHERE sname LIKE '张%';

26、查询同名同性学生名单，并统计同名人数

SELECT  COUNT(*) AS '人数',A.sname AS '姓名'
FROM student AS A,student AS B
WHERE A.sname = B.sname AND A.sno <> B.sno
GROUP BY A.sname,B.sname;

27、1981 年出生的学生名单(注：Student 表中Sage 列的类型是int)

计算当前时间到1981年应该几岁
SELECT YEAR(NOW()) - 1981;
--
SELECT * FROM student WHERE YEAR(NOW()) - 1981 = sage;

28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

SELECT AVG(score) AS savg FROM sc GROUP BY cno ORDER BY savg,cno DESC;

29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩

SELECT sname,stu.sno,avg(score) AS savg FROM sc
INNER JOIN student AS stu ON sc.sno = stu.sno
GROUP BY stu.sno HAVING savg>85;

30、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数

SELECT sname,score FROM student
INNER JOIN sc ON student.sno = sc.sno
INNER JOIN course ON course.cno = sc.cno
WHERE cname = '数据库' AND score < 60;

31、查询所有学生的选课情况；

SELECT C.cno,C.cname,S.sno,S.sname
FROM student S
JOIN sc ON (S.sno = sc.sno)
JOIN course C ON (sc.cno = C.cno)
GROUP BY sc.sno,sc.cno,C.cname;

32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；

其实就是查询大于70的纪录对应的信息
SELECT sname,cname,score FROM student
INNER JOIN sc ON sc.sno = student.sno
INNER JOIN course ON course.cno = sc.cno
WHERE score > 70;

33、查询不及格的课程，并按课程号从大到小排列

SELECT DISTINCT cno FROM sc WHERE score < 60 ORDER BY cno DESC;

34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；

SELECT sc.sno,sname from student
INNER JOIN sc ON sc.sno = student.sno
WHERE cno = 'c001' AND score > 80;

35、求选了课程的学生人数

SELECT COUNT(*) FROM student WHERE
(SELECT COUNT(*) FROM sc WHERE student.sno = sc.sno) > 0;

36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩

SELECT sname,score FROM sc
INNER JOIN course ON sc.cno = course.cno
INNER JOIN student ON sc.sno = student.sno
WHERE course.cno IN (SELECT cno FROM course WHERE tno = 
	(SELECT tno FROM teacher WHERE tname = '谌燕')) ORDER BY score DESC LIMIT 1;

37、查询各个课程及相应的选修人数

SELECT cname,COUNT(*) FROM sc
INNER JOIN course ON course.cno = sc.cno
GROUP BY course.cname;

38、查询不同课程成绩相同的学生的学号、课程号、学生成绩

SELECT s1.sno,s1.cno,s1.score FROM sc AS s1
INNER JOIN sc AS s2 ON s1.sno = s2.sno
WHERE s1.cno <> s2.cno AND s1.score = s2.score;

39、查询每门功课成绩最好的前两名

SELECT S.score,S.cno
FROM sc S
WHERE S.score IN (
		SELECT G.score
		FROM  S AS T,
		(SELECT score 
		FROM sc 
		WHERE cno = T.cno
		ORDER BY score DESC
		LIMIT 2) AS G)
GROUP BY S.cno,S.score;

40、统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT cno,COUNT(*) AS scount FROM sc
GROUP BY cno HAVING scount > 10 ORDER BY scount DESC,cno;

41、检索至少选修两门课程的学生学号

SELECT sno FROM sc
GROUP BY sno HAVING COUNT(*) >=2;

42、查询全部学生都选修的课程的课程号和课程名

思路：查询没门课程学生学生选修的人数，与学生表学生人数对比

SELECT course.cno,cname FROM course
INNER JOIN sc ON sc.cno = course.cno
GROUP BY course.cno,cname HAVING COUNT(*) = 
(SELECT COUNT(*) FROM student);

43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名

思路：查询学生选修的谌燕老师的课程的数量，数量为0则没学过

SELECT sname FROM student
INNER JOIN sc on student.sno = sc.sno
WHERE cno IN
(SELECT cno FROM course WHERE tno = 
(SELECT tno FROM teacher WHERE tname = '谌燕'))
GROUP BY sc.sno HAVING COUNT(*) = 0;

44、查询两门以上不及格课程的同学的学号及其平均成绩

SELECT sno,avg(score) FROM sc WHERE score < 60 GROUP BY sno HAVING COUNT(*) >2;

45、检索“c004”课程分数小于60，按分数降序排列的同学学号

SELECT sno FROM sc WHERE cno = 'c004' AND score < 60 ORDER BY score DESC;

46、删除“s002”同学的“c001”课程的成绩

DELETE FROM sc WHERE sno = 's002' AND cno = 'c001';