版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
创建表语句
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
这里是数据库字段的结构图
1.查询“某1”课程比“某2”课程成绩高的所有学生的学号;
select a.sid from (select sid,score from SC where cid=1) a,(select sid,score from SC where cid=2) b where a.score>b.score and a.sid=b.sid;
2.查询平均成绩大于60分的同学的学号和平均成绩;
select sid,AVG(score) from SC group by sid having avg(score) > 60;
3.查询所有同学的学号、姓名、选课数、总成绩
select Student.sid,Student.sname,count(SC.cid),sum(SC.score) from Student left join SC on Student.sid=SC.sid group by sid,sname;
4.查询姓“李”的老师的个数;
select count(Teacher.tid) from Teacher where Teacher.tname like '李%';
5.查询没学过“张三”老师课的同学的学号、姓名;
select s.sid,s.sname from Student as s where sid not in(select DISTINCT(s.sid) from Course as c,SC as s,Teacher as t where c.cid=s.cid AND c.tid=t.tid AND t.tname='张三');
6.查询学过“```”并且也学过编号“```”课程的同学的学号、姓名;
select a.sid,a.sname from
(select s.sid,s.sname from Student as s,Course as c,SC as sc where c.cname='数学' AND s.sid=sc.sid AND c.cid=sc.cid) a,
(select s.sid,s.sname from Student as s,Course as c,SC as sc where c.cname='语文' AND s.sid=sc.sid AND c.cid=sc.cid) b
WHERE a.sid=b.sid;
7.查询学过“张三”老师所教的所有课的同学的学号、姓名;
SELECT a.sid,a.sname from (select s.sid,s.sname from Student as s,Course as c,SC as sc ,Teacher as t where t.tname='王五' AND s.sid=sc.sid AND c.cid=sc.cid AND c.tid=t.tid) a;
8.查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名;
SELECT DISTINCT(a.sid),a.sname from (select sc.score,s.sid,s.sname from Student as s,SC as sc where sc.cid=01) a, (select sc.score,s.sid,s.sname from Student as s,SC as sc where sc.cid=02) b where a.score<b.score AND a.sid=b.sid;
9.查询所有课程成绩小于60分的同学的学号、姓名;
select s.sid,s.sname,sc.cid from Student as s,SC as sc where sc.score<60 AND sc.sid=s.sid;
10.查询没有学全所有课的同学的学号、姓名;
select s.sid,s.sname from Student as s,SC as sc where s.sid=sc.sid GROUP BY s.sid,s.sname HAVING count(sc.cid)<(select count(cid) from Course);
11.查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名;
SELECT Student.sid,Student.sname FROM SC,Student WHERE EXISTS (
SELECT cid FROM SC WHERE sid=01 AND SC.cid=cid
) AND Student.sid=SC.sid GROUP BY Student.sid,Student.sname;
12.查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
select * from Student where sid in (
SELECT sid FROM SC WHERE
cid in (SELECT cid FROM SC WHERE sid=01) AND sid!=01 GROUP BY sid HAVING COUNT(*)=(SELECT COUNT(*) FROM SC WHERE sid=01)
)
13.把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩;
update SC,(
SELECT t.tid,avg(score) as ascore
from Course as c,SC as sc,Teacher as t
WHERE t.tname='张三' AND c.tid=t.tid AND c.cid=sc.cid
)a set score=a.ascore
;
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT * FROM Student WHERE sid in(
SELECT sid FROM SC WHERE cid NOT in(
SELECT cid
FROM Teacher,Course WHERE Teacher.tname='张三' AND Teacher.tid=Course.tid
)
)
15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT s.sid,s.sname,AVG(score)
FROM SC sc JOIN Student s on sc.sid=s.sid WHERE
sc.score<60 GROUP BY s.sid,s.sname HAVING COUNT(sc.score)>=2
16、检索"01"课程分数小于60,按分数降序排列的学生信息
SELECT Student.*,SC.score from Student,SC WHERE EXISTS (
SELECT sid,score FROM SC WHERE cid=01 AND score<60 AND sid=Student.sid
) AND Student.sid=SC.sid AND SC.cid=01 ORDER BY SC.score DESC;
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT a.*
,SUM(CASE WHEN b.cid='01' THEN b.score ELSE 0 END) AS s01
,SUM(CASE WHEN b.cid='02' THEN b.score ELSE 0 END) AS s02
,SUM(CASE WHEN b.cid='03' THEN b.score ELSE 0 END) AS s03
,AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) avs
FROM Student a
LEFT JOIN SC b
ON a.sid=b.sid
GROUP BY 1,2,3,4
ORDER BY avs DESC;
18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
SELECT a.cid
,a.cname
,MAX(b.score)
,MIN(b.score)
,AVG(b.score)
,SUM(CASE WHEN b.score>=60 THEN 1 ELSE 0 END)/COUNT(1)
,SUM(CASE WHEN b.score>=70 AND b.score<80 THEN 1 ELSE 0 END)/COUNT(1)
,SUM(CASE WHEN b.score>=80 AND b.score<90 THEN 1 ELSE 0 END)/COUNT(1)
,SUM(CASE WHEN b.score>=90 THEN 1 ELSE 0 END)/COUNT(1)
FROM Course a
INNER JOIN SC b
ON a.cid=b.cid
GROUP BY 1,2;
19.按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT cid,avg_score,CONCAT(de,'%') 及格率
FROM(
SELECT sc0.cid,
AVG(sc0.score) avg_score,
CAST((
SELECT COUNT(1) FROM SC WHERE cid=sc0.cid AND score>=60
)*1.0/(
SELECT COUNT(1) FROM SC WHERE cid=sc0.cid
)*100 AS DECIMAL(10,2)) de
FROM SC sc0
GROUP BY sc0.cid
ORDER BY avg_score,de DESC)a;
20、查询学生的总成绩并进行排名
select a.sid,a.sname,a.sage,a.sum,count(b.sum) rank
from
(select a.sid,
a.sname,
a.sage,
a.ssex,
sum(b.score) sum
from student a inner join sc b
on a.sid=b.sid
group by a.sid,
a.sname,
a.sage,
a.ssex) a left join
(select a.sid,
a.sname,
a.sage,
a.ssex,
sum(b.score) sum
from student a inner join sc b
on a.sid=b.sid
group by a.sid,
a.sname,
a.sage,
a.ssex) b
on 1=1
where b.sum>a.sum
group by a.sid,a.sname,a.sage,a.sum order by rank;
21、查询不同老师所教不同课程平均分从高到低显示
SELECT Course.tid,Teacher.tname,a.cid,a.avg_score
FROM (SELECT cid,avg(score) avg_score FROM SC GROUP BY cid ) a,Course,Teacher
WHERE a.cid=Course.cid AND Teacher.tid=Course.tid ORDER BY a.avg_score DESC;
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT s.*,sc0.*
FROM SC sc0 INNER JOIN Student s ON s.sid=sc0.sid
WHERE (SELECT COUNT(DISTINCT score) FROM SC WHERE sc0.cid=SC.cid AND sc0.score>SC.score) BETWEEN 2 AND 3
ORDER BY sc0.cid;
23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
这道题 太复杂了懒的写
SELECT cid,count(SELECT score FROM SC WHERE score BETWEEN 85 AND 100) a,count(SELECT score FROM SC WHERE score BETWEEN 85 AND 100)/count(score),count(score) FROM SC GROUP BY cid;
SELECT count(1) FROM (SELECT score FROM SC WHERE score BETWEEN 85 AND 100)a;
24、查询学生平均成绩及其名次
SELECT a.sid,
a.avg_score,
sum(case when a.avg_score<b.avg_score then 1 else 0 end)+1 scc
FROM (SELECT sid,avg(score) avg_score FROM SC GROUP BY sid) a INNER JOIN
(SELECT sid,avg(score) avg_score FROM SC GROUP BY sid) b
GROUP BY a.sid,
a.avg_score
ORDER BY scc;
25、查询各科成绩前三名的记录
SELECT sc0.*,
(SELECT count(1) FROM SC WHERE SC.cid=sc0.cid AND SC.score>sc0.score)+1 rank
FROM SC sc0
GROUP BY 2,1,3
HAVING rank<=3
ORDER BY sc0.cid,rank;
26.查询每门课程被选修的学生数
SELECT sc.cid,count(sc.sid) from Course c,SC sc WHERE c.cid=sc.cid GROUP BY sc.cid;
27.查询出只选修了一门课程的全部学生的学号和姓名
SELECT s.sid,s.sname from SC as sc,Student AS s WHERE sc.sid=s.sid GROUP BY sc.sid,s.sname HAVING COUNT(cid)=2;
28、查询男生、女生人数
SELECT count(ssex='女'),count(ssex='男') FROM Student;
29、查询名字中含有"风"字的学生信息
SELECT Student.* FROM Student WHERE sname like '%风%';
30、查询同名同性学生名单,并统计同名人数
SELECT sname,ssex,count(1)
FROM Student GROUP BY 1,2
HAVING count(1)>1;
31、查询1990年出生的学生名单(注:Student表中Sage列的类型是datetime)
SELECT Student.* FROM Student WHERE YEAR(sage)=1990;
32.查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
SELECT cid,AVG(score) from SC GROUP BY cid order by avg(score),cid DESC;
37.查询不及格的课程,并按课程号从大到小排列
SELECT cid,score FROM SC WHERE score<60 order by cid DESC;
38.查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名;
SELECT s.sid,s.sname,score,sc.cid
FROM SC as sc,Student as s
WHERE sc.sid=s.sid AND sc.cid=01 AND score>60;
40.查询选修“张三”老师所授课程的学生中,成绩最高的学生姓名及其成绩
这道题写的时候想出了两种方达,都能实现
SELECT *
FROM (SELECT s.sid as aid,s.sname as aname,sc.score as ascore,t.tname FROM SC as sc,Student s,Course as c,Teacher as t
WHERE sc.sid=s.sid AND c.cid=sc.cid AND c.tid=t.tid AND t.tname="张三" ORDER BY score) a LIMIT 0,1;
SELECT s.sid,s.sname,a.ascore
FROM SC as sc,Student s,Course as c,Teacher as t ,
(SELECT MAX(score) ascore FROM SC as sc,Student s,Course as c,Teacher as t
WHERE sc.sid=s.sid AND c.cid=sc.cid AND c.tid=t.tid AND t.tname="张三" ) a
WHERE sc.sid=s.sid AND c.cid=sc.cid AND c.tid=t.tid AND t.tname="张三" AND sc.score=a.ascore;
42、查询每门功课成绩最好的前两名
SELECT sc.* FROM SC sc WHERE (SELECT COUNT(1) FROM SC WHERE SC.cid=sc.cid AND score>sc.score)<2 ORDER BY sc.cid,sc.score;
43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT cid,count(1) count
FROM SC GROUP BY cid HAVING count(1)>=5 ORDER BY count desc,cid ;
44、检索至少选修两门课程的学生学号
SELECT a.*,b.count
FROM Student a INNER JOIN (SELECT sid,count(cid) count FROM SC GROUP BY sid HAVING count>1)b
ON a.sid=b.sid GROUP BY 1,2,3,4;
45、查询选修了全部课程的学生信息
SELECT sid,count(cid) count_c FROM SC
GROUP BY sid
HAVING count_c=(SELECT COUNT(1) FROM Course);
46、查询各学生的年龄
SELECT Student.*,YEAR(CURDATE())-YEAR(Student.sage) FROM Student;
47、查询本周过生日的学生
SELECT * FROM Student WHERE WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)=0;
48、查询下周过生日的学生
SELECT * FROM Student WHERE WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)=-1;
49、查询本月过生日的学生
SELECT * FROM Student WHERE MONTH(NOW())-MONTH(sage)=0;
50、查询下月过生日的学生
SELECT * FROM Student WHERE MONTH(NOW())-MONTH(sage)=-1;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
<link href="https://csdnimg.cn/release/phoenix/mdeditor/markdown_views-e44c3c0e64.css" rel="stylesheet">
</div>