文章目录
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数(偏难)
SELECT
st.*,
sc1.s_score 01_score,
sc2.s_score 02_score
FROM
student st,
score sc1,
score sc2
WHERE
st.s_id = sc1.s_id
AND sc1.s_id = sc2.s_id
AND sc1.c_id ='01'AND sc2.c_id ='02'AND sc1.s_score > sc2.s_score
通过score表自连接后,过滤出每位学生 “01”课程分数和 “02”课程分数的一条记录,最后将01”课程分数大于 “02”课程分数记录筛选出来
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
与第1题思路一样
3、查询平均成绩大于等于60分的每个同学的学生编号和学生姓名和平均成绩
SELECT
st.s_id,
s_name,avg(sc.s_score)FROM
student st,
score sc
WHERE
st.s_id = sc.s_id
GROUPBY
st.s_id,
st.s_name
HAVINGavg(sc.s_score)>=60
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)
SELECT
st.s_id,
s_name,ROUND(avg(sc.s_score),2) avgsc
FROM
student st LEFTJOIN
score sc
on
st.s_id = sc.s_id
GROUPBY
st.s_id,
st.s_name
HAVINGavg(sc.s_score)<60or avgsc isnull
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT
st.s_id,
st.s_name,count(sc.c_id)AS sum_course,sum(sc.s_score)AS sum_score
FROM
student st
LEFTJOIN score sc ON st.s_id = sc.s_id
GROUPBY
st.s_id,
st.s_name
此题比较简单,使用分组即可。需要注意下在mysql中使用group by后,select后出现的字段要么是
分组的字段要么是聚集函数不然会报错
6、查询"李"姓老师的数量
SELECTcount(*)FROM
teacher
WHERE
t_name LIKE'李%'
这一题就是考察 mysql中like关键字的使用
7、查询学过"张三"老师授课的同学的信息
SELECT
st.*FROM
student st
WHERE
st.s_id IN(SELECT
s_id
FROM
score
WHERE
c_id =(SELECT
c_id
FROM
course
WHERE
t_id =(SELECT
t_id
FROM
teacher
WHERE
t_name ='张三')))
这题我使用的是where型子查询需要使用到in关键字,使用连接查询也可以.根据表之间的连接关系:
- 首先在teacher表中通过t_name拿到t_id
- 在course表中通过t_id拿到c_id
- 在course表中通过c_id拿到s_id
- 最后在student表中通过s_id拿到学过‘张三’老师授课的学生信息
类似这种子查询都可以用连接查询替代
8、查询没学过"张三"老师授课的同学的信息
此题只需要把上一题中的in关键改成not in关键字即可
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT*FROM
student
WHERE
s_id IN(SELECT
sc1.s_id
FROM
score sc1
JOIN score sc2 ON sc1.s_id = sc2.s_id
WHERE
sc1.c_id ='01'AND sc2.c_id ='02')
这一题的方法与第一题类似,第一题搞懂了,这一题就很简单了。直接通过score表进行自连结运算然后筛选出即选修了’01’号课程又选修了’02’号课程的s_id
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息(偏难)
SELECT
st.*
FROM
student st
WHERE
st.s_id IN ( SELECT s_id FROM score WHERE c_id ='01')
AND st.s_id NOT IN ( SELECT s_id FROM score WHERE c_id ='02')
11、查询没有学全所有课程的同学的信息
SELECT*FROM
student
WHERE
s_id NOTIN(SELECT
s_id
FROM
score
GROUPBY
s_id
HAVINGcount(c_id)=(SELECTcount(c_id)FROM
course
))
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECTDISTINCT
st.*FROM
student st
LEFTJOIN score sc ON st.s_id = sc.s_id
GROUPBY
s_id,
c_id
HAVING
c_id IN(SELECT
c_id
FROM
score
WHERE
s_id ='01')
SELECTDISTINCT st.*FROM student st LEFTJOIN score sc ON st.s_id=sc.s_id WHERE c_id IN(SELECT c_id FROM score WHERE s_id='01')
select*from Student where s_id in(SELECTDISTINCT s_id from Score where c_id in(select c_id from Score where s_id='01'))
这题关键在于用其他每个学生所学课程号与"01"学生所学课程的课程号作对比
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT
st.*FROM
student st
JOIN score sc ON st.s_id = sc.s_id
WHERE
sc.c_id IN(SELECT
c_id
FROM
score
WHERE
s_id ='01')AND st.s_id !='01'GROUPBY
st.s_id
HAVINGcount(st.s_id)=(SELECTcount(*)FROM
score
WHERE
s_id ='01')
select Student.*from Student leftJOIN Score on Student.s_id = Score.s_id where Score.c_id in(select c_id from Score where s_id='01')and Score.s_id <>'01'GROUPBY Student.s_id HAVINGCOUNT(*)=(selectcount(*)from Score where s_id='01')
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT*FROM
student
WHERE
s_id NOTIN(SELECT
st.s_id
FROM
student st,
score sc
WHERE
st.s_id = sc.s_id
AND c_id IN(SELECT
c_id
FROM
teacher te
LEFTJOIN course c ON te.t_id = c.t_id
WHERE
t_name ='张三'))
15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT
st.s_id,
st.s_name,round(avg(sc.s_score))FROM
student st,
score sc
WHERE
st.s_id = sc.s_id
AND s_score <60GROUPBY
s_id
HAVINGcount(*)>=2
16、检索"01"课程分数小于60,按分数降序排列的学生信息
SELECT
st.*,
sc.c_id,
sc.s_score
FROM
student st,
score sc
WHERE
st.s_id = sc.s_id
AND s_score <60AND c_id ='01'ORDERBY
s_score DESC
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select st.s_id,(select s_score from score where c_id='01'and s_id=st.s_id)as'语文',(select s_score from score where c_id='02'and s_id=st.s_id)as'数学',(select s_score from score where c_id='03'and s_id=st.s_id)as'英语',round((selectavg(s_score)from score where s_id=st.s_id groupby s_id),2)as'平均分'from student st orderby 平均分 desc
这一题用到了子查询的另一种方式即创建计算字段,该子查询对检索出的每个student执行一次,在此例中,该查询共执行了8次,因为检索出8名学生,另外当在group by 或 order by后面使用中文时,不要加单引号,否则不生效
18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
explainselect sc.c_id,c_name,max(s_score),min(s_score),ROUND(avg(s_score),2)'平均分',ROUND(100*(SUM(casewhen sc.s_score>=60then1else0end)/SUM(casewhen sc.s_score then1else0end)),2)as'及格率',ROUND(100*(SUM(casewhen sc.s_score>=70and sc.s_score<80then1else0end)/SUM(casewhen sc.s_score then1else0end)),2)as'中等率',ROUND(100*(SUM(casewhen sc.s_score>=80and sc.s_score<90then1else0end)/SUM(casewhen sc.s_score then1else0end)),2)as'优良率',ROUND(100*(SUM(casewhen sc.s_score>=90then1else0end)/SUM(casewhen sc.s_score then1else0end)),2)as'优秀率'from course c,score sc where c.c_id=sc.c_id groupby sc.c_id
19、按各科成绩进行排序,并显示排名
20、查询学生的总成绩并进行排名
21、查询不同老师所教不同课程平均分从高到低显示
SELECT c.t_id,t.t_name,s.c_id,ROUND(AVG(s_score),2) avgsc FROM teacher t,course c,score s WHERE t.t_id=c.t_id AND c.c_id=s.c_id GROUPBY s.c_id,c.t_id,t.t_name ORDERBY avgsc DESC
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
24、查询学生平均成绩及其名次
25、查询各科成绩前三名的记录
26、查询每门课程被选修的学生数
select c_id ,count(*)from score groupby c_id
27、查询出只有两门课程的全部学生的学号和姓名
select st.s_id,st.s_name,count(*)'课程数'from student st leftjoin score sc on st.s_id=sc.s_id groupby s_id havingcount(*)=2
select s_id,s_name from Student WHERE s_id in(select s_id FROM Score GROUPBY s_id HAVINGcount(*)=2)
28、查询男生、女生人数
select s_sex,(selectcount(s_sex)from student st2 where s_sex = st1.s_sex)'人数'from student st1 groupby s_sex
selectDISTINCT s_sex,(selectcount(s_sex)from Student st2 where s_sex = st1.s_sex)'人数'from Student st1
29、查询名字中含有"风"字的学生信息
select*from student where s_name like'%风%'
30、查询同名同性学生名单,并统计同名人数
select s_name,count(*)-1'同名人数'from student groupby s_name havingcount(*)>=2
31、查询1990年出生的学生名单
select s_name from student where s_birth like'1990%'
32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select c_id,ROUND(avg(s_score),2)'平均成绩'from score groupby c_id orderby 平均成绩 desc,c_id
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select st.s_id,s_name,ROUND(avg(s_score),2)'平均成绩'from student st,score sc where st.s_id=sc.s_id groupby st.s_id , s_name havingavg(s_score)>=85
select s.s_id,s.s_name,ROUND(AVG(sc.s_score))'平均分'from Student s LEFTJOIN Score sc ON s.s_id=sc.s_id GROUPBY s.s_id,s.s_name HAVINGAVG(sc.s_score)>=85
34、查询课程名称为"数学",且分数低于60的学生姓名和分数
select s_name,s_score from student st,course c,score sc where st.s_id=sc.s_id and sc.c_id=c.c_id and c_name='数学'and s_score<60
selectdistinct s_name,s_score from Student s LEFTJOIN Score sc on s.s_id=sc.s_id LEFTJOIN Course c on c.c_id=sc.c_id where c_name='数学'and s_score<60
35、查询所有学生的课程及分数情况
select st.s_id,s_name,c_name,s_score from student st,course c,score sc where st.s_id=sc.s_id and sc.c_id=c.c_id
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select s_name,c_name,s_score from student st,course c,score sc where st.s_id = sc.s_id and c.c_id =sc.c_id and s_score>70
37、查询不及格的课程
select st.*,c_name,s_score from student st,score sc,course c where st.s_id=sc.s_id and sc.c_id=c.c_id and s_score<60
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select st.s_id,st.s_name from student st,score sc where st.s_id=sc.s_id and c_id='01'and s_score>=80
39、求每门课程的学生人数
select c.c_name '课程名',count(*)'人数'from Course c,Score s where c.c_id=s.c_id GROUPBY s.c_id,c.c_name
select c_name '课程名',(selectcount(*)from Score s1 where c_id=c.c_id)'人数'from Course c
40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
select st.*,s_score from student st,score sc where st.s_id=sc.s_id and c_id=(select c_id from teacher te leftjoin course c on te.t_id=c.t_id where t_name='张三')orderby s_score desclimit1
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
selectdistinct s1.*FROM score s1 ,score s2 where s1.c_id !=s2.c_id and s1.s_score=s2.s_score
42、查询每门功成绩最好的前两名(偏难)
select sc1.s_id,sc1.c_id,sc1.s_score from score sc1
where(selectCOUNT(1)from score sc2 where sc2.c_id=sc1.c_id and sc2.s_score>=sc1.s_score)<=2ORDERBY sc1.c_id
首先,select count(1)表示查询出表中符合条件的行数;
sc2.c_id=sc1.c_id and sc2.s_score>=sc1.s_score表示查询条件;
select COUNT(1) from score sc2 where sc2.c_id=sc1.c_id and sc2.s_score>=sc1.s_score总体的意思就是从表sc2中查询出满足sc2.c_id=sc1.c_id and sc2.s_score>=sc1.s_score条件的行数;
结合完整的sql语句来看,这个查询出的行数要<=2,所以"行数<=2"是作为前一个查询语句的查询条件的。
这样这个语句简单点理解就是:从sc1表查询sc1.s_id,sc1.c_id,sc1.s_score这三列,查询条件是"行数<=2"。
43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select c_id,count(*)'选修人数'from score groupby c_id havingcount(*)>5orderby 选修人数 desc,c_id
44、检索至少选修两门课程的学生学号
select s_id from score groupby s_id havingcount(*)>=2
45、查询选修了全部课程的学生信息
select st.*from student st,score sc where st.s_id=sc.s_id groupby sc.s_id havingcount(*)=(selectcount(*)from course)
版权归原作者 大蒜儿ʕ๑•㉨•๑ʔ❀ 所有, 如有侵权,请联系我们删除。