mysql练习题1

drop table if exists student;
drop table if exists teacher;
drop table if exists course;
drop table if exists sc;

create table student(
sno varchar(10) primary key,
sname varchar(20),
sage int,
ssex varchar(5)
);

create table teacher(
tno varchar(10) primary key,
tname varchar(20)
);

create table course(
cno varchar(10),
cname varchar(20),
tno varchar(20),
constraint pk_course primary key (cno,tno)
);

create table sc(
sno varchar(10),
cno varchar(10),
score decimal(8,2),
constraint pk_sc primary key (sno,cno)
);

/*初始化学生表的数据/
insert into student values (‘s001’,‘张三’,23,‘男’);
insert into student values (‘s002’,‘李四’,23,‘男’);
insert into student values (‘s003’,‘吴鹏’,25,‘男’);
insert into student values (‘s004’,‘琴沁’,20,‘女’);
insert into student values (‘s005’,‘王丽’,20,‘女’);
insert into student values (‘s006’,‘李波’,21,‘男’);
insert into student values (‘s007’,‘刘玉’,21,‘男’);
insert into student values (‘s008’,‘萧蓉’,21,‘女’);
insert into student values (‘s009’,‘陈萧晓’,23,‘女’);
insert into student values (‘s010’,‘陈美’,22,‘女’);

/初始化教师表*****/
insert into teacher values (‘t001’, ‘刘阳’);
insert into teacher values (‘t002’, ‘谌燕’);
insert into teacher values (‘t003’, ‘胡明星’);

/初始化课程表*************/
insert into course values (‘c001’,‘J2SE’,‘t002’);
insert into course values (‘c002’,‘Java Web’,‘t002’);
insert into course values (‘c003’,‘SSH’,‘t001’);
insert into course values (‘c004’,‘Oracle’,‘t001’);
insert into course values (‘c005’,‘SQL SERVER 2005’,‘t003’);
insert into course values (‘c006’,‘C#’,‘t003’);
insert into course values (‘c007’,‘JavaScript’,‘t002’);
insert into course values (‘c008’,‘DIV+CSS’,‘t001’);
insert into course values (‘c009’,‘PHP’,‘t003’);
insert into course values (‘c010’,‘EJB3.0’,‘t002’);

/初始化成绩表********/
insert into sc values (‘s001’,‘c001’,78.9);
insert into sc values (‘s002’,‘c001’,80.9);
insert into sc values (‘s003’,‘c001’,81.9);
insert into sc values (‘s004’,‘c001’,60.9);
insert into sc values (‘s001’,‘c002’,82.9);
insert into sc values (‘s002’,‘c002’,72.9);
insert into sc values (‘s003’,‘c002’,81.9);
insert into sc values (‘s001’,‘c003’,59);
insert into sc values (‘s001’,‘c004’,78.9);
insert into sc values (‘s002’,‘c004’,80.9);
insert into sc values (‘s003’,‘c005’,81.9);
insert into sc values (‘s004’,‘c005’,60.9);
insert into sc values (‘s001’,‘c006’,82.9);
insert into sc values (‘s002’,‘c006’,72.9);
insert into sc values (‘s003’,‘c007’,81.9);
insert into sc values (‘s001’,‘c007’,59);
insert into sc values (‘s005’,‘c001’,80.9);
insert into sc values (‘s006’,‘c001’,81.9);
insert into sc values (‘s007’,‘c001’,90.9);
insert into sc values (‘s004’,‘c002’,75);
insert into sc values (‘s005’,‘c002’,71.2);
insert into sc values (‘s006’,‘c002’,85.9);
insert into sc values (‘s007’,‘c003’,59);
insert into sc values (‘s004’,‘c004’,77.9);
insert into sc values (‘s005’,‘c004’,84.9);
insert into sc values (‘s006’,‘c005’,82.9);
insert into sc values (‘s007’,‘c005’,67.9);
insert into sc values (‘s004’,‘c006’,62.9);
insert into sc values (‘s006’,‘c006’,92.9);
insert into sc values (‘s007’,‘c007’,35.9);
insert into sc values (‘s008’,‘c007’,69);
1.查询“c001”课程比“c002”课程成绩高的所有学生的学号；

select a.sno
FROM
(SELECT * from sc a where a.cno='c001' ) a,
(SELECT * from sc b where b.cno='c002' ) b
where a.sno=b.sno and a.score>b.score;

select sno
FROM sc a
where cno='c001' and EXISTS(SELECT sno from sc b where b.sno=a.sno and b.cno='c002' and a.score>b.score);

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

select sno,avg(score)
from sc
GROUP BY sno
HAVING AVG(score)>60;

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

-- 注意必须是student.sno sno是student绝对全有，sc不一定
select student.sno,sname,count(cno),sum(score)
from student left join sc on (sc.sno=student.sno)
GROUP BY student.sno;

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

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

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

select sno,sname 
from student
where sno not in (
select sno
from sc
where cno in(
select cno
from course
where tno in(
select tno
from teacher
where tname = '谌燕')));

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

select sno,sname
from student
where sno in(
select sno
from sc
where cno='c001' and sno in(
select sno
from sc
where cno='c002'));

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

select DISTINCT(student.sno),sname 
from sc,student,teacher,course
where sc.sno = student.sno and sc.cno = course.cno and course.tno = teacher.tno AND tname = '谌燕';

select sno,sname 
from student
where sno in (
select sno
from sc
where cno in(
select cno
from course
where tno in(
select tno
from teacher
where tname = '谌燕')));

学过所有谌燕老师课程的学生

select student.sno,sname 
from sc,student,teacher,course
where sc.sno = student.sno and sc.cno = course.cno and course.tno = teacher.tno AND tname = '谌燕'

GROUP BY student.sno
having count(course.cno)>=(
select count(course.cno)
from course where tno in (
select tno from teacher
where tname='谌燕'))

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

select sno,sname 
from student
where sno in(
select a.sno
from sc a,sc b
where a.sno=b.sno and a.cno='c001' and b.cno='c002' and a.score>b.score);

9.查询所有课程成绩小于60 分的同学的学号、姓名；
（所有课程成绩大于60分反转）

select sno,sname
from student
where sno IN(
SELECT DISTINCT(sno)
from sc
where score<60);

select sno,sname
from student
where sno not IN(
SELECT DISTINCT(sno)
from sc
where score>60);

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

select sno,sname
from student
where sno IN(
select sno
from sc 
GROUP BY sno
HAVING COUNT(cno) != (
SELECT count(cno)
from course
));

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

select sno,sname
from student
where sno in(
select sno
from sc
where cno in (
select cno
from sc
where sno='s008')) and sno!='s008';

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

select student.sno,sname
from student left join sc on student.sno=sc.sno
-- 查找cno属于s002的集合
WHERE cno in(
select cno
from sc
where sno='s002')
GROUP BY student.sno
-- 统计个数
HAVING COUNT(cno)>=(
select count(cno)
from sc
where sno='s002') and sno!='s002';

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

update sc s1 join (select sc.cno,avg(sc.score) avgscore from course,teacher,sc
where course.tno=teacher.tno
and course.cno=sc.cno
and teacher.tname='谌燕'
group by sc.cno) s2 ON s1.cno = s2.cno
SET s1.score=s2.avgscore 

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

select sno,sname
from student 
where sno in(
select sno
from sc
where cno in (
select cno
from sc
where sno='s001')
GROUP BY sno
HAVING COUNT(cno) = (select count(cno) from sc where sno='s001')) and sno!='s001';

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

DELETE from sc where cno
IN (SELECT cno from course where tno in (select tno from teacher where 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 cno,avg(score),100*(sum(case when score>=60 then 1 else 0 END)/COUNT(*)) as passLine
from sc
GROUP BY cno
ORDER BY AVG(score), passLine desc;

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

select teacher.tno,course.cno,avg(score)
from teacher,course,sc
where teacher.tno = course.tno and sc.cno = course.cno 
GROUP BY teacher.tno,course.cno
ORDER BY AVG(score) desc;

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

select cno,sum(case when score BETWEEN 85 and 100 then 1 else 0 end) as "[100-85]",
sum(case when score BETWEEN 70 and 85 then 1 else 0 end) as "[85-70]",
sum(case when score BETWEEN 60 and 70 then 1 else 0 end) as "[70-60]",
sum(case when score BETWEEN 0 and 60 then 1 else 0 end) as "[60-0]"
from sc
GROUP BY cno;

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

  select a.cno,a.first_score,b.second_score from (select cno,max(score) first_score from sc group by cno) a,
  (select s.cno,max(s.score) second_score from sc s,
  (select cno,max(score) first_score from sc group by cno) a
  where s.cno = a.cno
  and s.score < a.first_score
  group by s.cno) b
  where a.cno = b.cno

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

select course.cno,count(sno)
from course left join sc on course.cno=sc.cno
GROUP BY course.cno

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

select student.sno,sname
from student left join sc on student.sno=sc.sno
GROUP BY sno
having COUNT(cno)=1;

24.查询男生、女生人数

select sum(case when ssex='男' then 1 else 0 end) as '男total',
sum(case when ssex='女' then 1 ELSE 0 end) as '女total'
from student;

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

select *
from student 
where sname like '张%';

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

select sname
from student
GROUP BY sname
HAVING COUNT(sname)>1;

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

select *
from student
where sage=2022-2000;

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

select cno,avg(score)
from sc
GROUP BY cno
ORDER BY avg(score) asc,
cno desc;

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

select student.sno,sname,avg(score)
from student left join sc on student.sno=sc.sno
GROUP BY student.sno
having AVG(score) > 50;

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

select sname,score,sc.cno
from student left join sc on student.sno=sc.sno
left join course on sc.cno=course.cno
WHERE cname='Oracle' AND score<80;

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

-- 可能有学生没有选课
SELECT student.sno,cno
from student left join sc on student.sno=sc.sno

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

select sname,cname,score
from student left join sc on student.sno=sc.sno
left join course ON course.cno=sc.cno
where score>70 

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

select sno,cno,score
from sc
where score<60;
ORDER BY cno desc;

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

select student.sno,sname,score
from student left join sc on student.sno=sc.sno
where cno='c006' and score>80;

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

select count(sno)
from student WHERE sno in (
select distinct(sno) from sc
);

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

-- 不分课程
select sname,score,sc.cno
from student left join sc on student.sno=sc.sno
left join course on course.cno=sc.cno
left join teacher on teacher.tno=course.tno
where tname = '谌燕' and score >= all(
select score
from student left join sc on student.sno=sc.sno
left join course on course.cno=sc.cno
left join teacher on teacher.tno=course.tno
where tname = '谌燕')

-- 分课程
select sname,score,sc.cno
from student left join sc on student.sno=sc.sno
left join course on course.cno=sc.cno
left join teacher on teacher.tno=course.tno
where tname = '谌燕' and score in (
-- 寻找谌燕所教每个课程的最高分
select max(score)
from student left join sc on student.sno=sc.sno
left join course on course.cno=sc.cno
left join teacher on teacher.tno=course.tno
where tname = '谌燕'
GROUP BY sc.cno)

最终版分课程最高的学生，注意：先求各科最高分与表相连，使max=score

-- 查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select a.sno,sname,score,a.cno
from
(select sc.sno,sname,score,sc.cno
from student left join sc on student.sno=sc.sno
left join course on course.cno=sc.cno
left join teacher on teacher.tno=course.tno) a join
(select sc.cno,max(score) max_score
from student left join sc on student.sno=sc.sno
left join course on course.cno=sc.cno
left join teacher on teacher.tno=course.tno
where tname = '谌燕'
GROUP BY sc.cno) b on a.cno=b.cno and score=max_score

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

select course.cno,count(sno)
from course left join sc on sc.cno=course.cno
GROUP BY course.cno

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

select a.sno,a.cno,a.score
from sc as a,sc as b
where a.score=b.score and a.cno!=b.cno;

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

  select a.cno,a.first_score,b.second_score from (select cno,max(score) first_score from sc group by cno) a,
  (select s.cno,max(s.score) second_score from sc s,
  (select cno,max(score) first_score from sc group by cno) a
  where s.cno = a.cno
  and s.score < a.first_score
  group by s.cno) b
  where a.cno = b.cno

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

select cno,count(sno)
from sc
GROUP BY cno
having count(sno)>=10
order by count(sno) DESC,cno asc;

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

SELECT sno 
from sc
GROUP BY sno
HAVING count(cno)>=2;

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

select cno,cname
from course 
where cno in(
SELECT cno
from sc
GROUP BY cno
HAVING count(sno) = (
SELECT count(sno)
from student
))

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

select sname
from student
where sno not in (
select sno
from sc
where cno in(
select cno
from course
where tno in(
select tno 
from teacher
where tname='谌燕')));

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

select sno,avg(score)
from sc
GROUP BY sno
HAVING sum(case when score<60 then 1 else 0 end)>=2;

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

select sno,score
from sc
where score<60 and cno='c004';

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

delete from sc where sno='s002' and cno = 'c002';

最后

以上就是害怕柚子为你收集整理的mysql练习题1的全部内容，希望文章能够帮你解决mysql练习题1所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。