|
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
|
1.sampdb所用到的表格 sampdb关于学生考试的表格总共有四张，如下
学生信息表MariaDB [sampdb]> select * from student;
+-----------+-----+------------+| name | sex | student_id | +-----------+-----+------------+ | Megan | F | 1 || Joseph | M | 2 || Kyle | M | 3 |学生成绩表MariaDB [sampdb]> select * from score;
+------------+----------+-------+| student_id | event_id | score |+------------+----------+-------+| 1 | 1 | 20 || 3 | 1 | 20 || 4 | 1 | 18 |测试考试统计表MariaDB [sampdb]> select * from grade_event;
+------------+----------+----------+| date | category | event_id |
+------------+----------+----------+| 2008-09-03 | Q | 1 || 2008-09-06 | Q | 2 || 2008-09-09 | T | 3 || 2008-09-16 | Q | 4 || 2008-09-23 | Q | 5 || 2008-10-01 | T | 6 |+------------+----------+----------+学生缺勤表MariaDB [sampdb]> select * from absence;
+------------+------------+| student_id | date |
+------------+------------+| 3 | 2008-09-03 || 5 | 2008-09-03 || 10 | 2008-09-06 || 10 | 2008-09-09 || 17 | 2008-09-07 || 20 | 2008-09-07 |2.表格结构索引 student
| student | CREATE TABLE `student` (
`name` varchar(20) NOT NULL,
`sex` enum('F','M') NOT NULL,
`student_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 | score
| score | CREATE TABLE `score` (
`student_id` int(10) unsigned NOT NULL,
`event_id` int(10) unsigned NOT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`event_id`,`student_id`),
KEY `student_id` (`student_id`),
CONSTRAINT `score_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `grade_event` (`event_id`),
CONSTRAINT `score_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | grade_event
| grade_event | CREATE TABLE `grade_event` (
`date` date NOT NULL,
`category` enum('T','Q') NOT NULL,
`event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`event_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 | absence
| absence | CREATE TABLE `absence` (
`student_id` int(10) unsigned NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`student_id`,`date`),
CONSTRAINT `absence_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |3.检索数据检索统计参加event_id为1考试的学生人数MariaDB [sampdb]> select count(student_id) from score where event_id=1;
+-------------------+| count(student_id) |+-------------------+| 29 |+-------------------+1 row in set (0.00 sec)
列出参加event_id为1考试的学生名单
MariaDB [sampdb]> select group_concat(student.name) from student left join score on student.student_id=score.student_id
where score.event_id=1;+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| group_concat(student.name) |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Megan,Kyle,Katie,Abby,Nathan,Liesl,Ian,Colin,Peter,Michael,Thomas,Ben,Aubrey,Rebecca,Will,Max,Rianne,Avery,Lauren,Becca,Gregory,Sarah,Robbie,Keaton,Carter,Teddy,Gabrielle,Grace,Emily |+---------检索统计未参加event_id为1考试的学生人数MariaDB [sampdb]> select count(student.student_id) from student left join (select * from score where event_id=1) as score1 on student.student_id=score1.student_id where score1.score is null;
+---------------------------+| count(student.student_id) |+---------------------------+| 2 |+---------------------------+1 row in set (0.00 sec)
列出未参加event_id为1考试的学生名单MariaDB [sampdb]> select group_concat(student.name) from student left join (select * from score where event_id=1)
as score1 on student.student_id=score1.student_id where score1.score is null;
+----------------------------+| group_concat(student.name) |+----------------------------+| Joseph,Devri |+----------------------------+检索参加event_id为1考试的学生最高成绩，平均成绩，最低成绩 MariaDB [sampdb]> select max(score1.score),avg(score1.score),min(score1.score) from student left join (select *
from score where event_id=1) as score1 on student.student_id=score1.student_id;+-------------------+-------------------+-------------------+| max(score1.score) | avg(score1.score) | min(score1.score) |+-------------------+-------------------+-------------------+| 20 | 15.1379 | 9 |+-------------------+-------------------+-------------------+检索参加event_id为1考试的最高成绩的学生名字select student.name from student left join score on student.student_id=score.student_id where
score.event_id=1 and score=(select max(score) from score where event_id=1);
+--------+| name |+--------+| Megan || Kyle || Aubrey || Max |+--------+减速参加event_id为1考试的最低成绩的学生名字MariaDB [sampdb]> select student.name,score from student left join score on student.student_id=score.student_id where score.event_id=1 and score=(select min(score) from score where event_id=1);
+--------+-------+| name | score |+--------+-------+| Will | 9 || Rianne | 9 || Avery | 9 |+--------+-------+3 rows in set (0.00 sec)
4.写一个存储过程，根据考试ID，列出这次考试多少人参加，列出参加学生的名字，多少人缺席，列出缺席的学生名字，列出考试的最高成绩的获得者的名字和最高成绩，最低成绩的获得者名字和最低成绩，平均成绩。create procedure id_in(num int)beginselect concat('考试人数'),count(student_id) from score where event_id=num;
select concat('参加考试名单'),group_concat(name) from student left join score on student.student_id=score.student_id where event_id=num;
select concat('缺席考试人数'),count(name) from student left join (select * from score where event_id=num) as score1 on student.student_id=score1.student_id where score1.score is null;
select concat('缺席考试名单'),group_concat(name) from student left join (select * from score where event_id=num) as score1 on student.student_id=score1.student_id where score1.score is null;
select concat('最高成绩学生名单'),group_concat(student.name) from student left join score on student.student_id=score.student_id where score.event_id=num and score=(select max(score) from score where score.event_id=num);
select concat('最 低成绩学生名单'),group_concat(student.name) from student left join score on student.student_id=score.student_id where score.event_id=num and score=(select min(score) from score where score.event_id=num);
select concat('最高成绩'),max(score1.score),concat('平均成绩'),avg(score1.score),concat('最低成绩'),min(score1.score) from student left join (select * from score where score.event_id=num) as score1 on student.student_id=score1.student_id;
end$结果：MariaDB [sampdb]> call id_in(1);+------------------------+-------------------+| concat('考试人数') | count(student_id) |
+------------------------+-------------------+| 考试人数 | 29 |+------------------------+-------------------+1 row in set (0.01 sec)
+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| concat('参加考试名单') | group_concat(name) |
+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 参加考试名单 | Megan,Kyle,Katie,Abby,Nathan,Liesl,Ian,Colin,Peter,Michael,Thomas,Ben,Aubrey,Rebecca,Will,Max,Rianne,Avery,Lauren,Becca,Gregory,Sarah,Robbie,Keaton,Carter,Teddy,Gabrielle,Grace,Emily |+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)
+------------------------------+-------------+| concat('缺席考试人数') | count(name) |
+------------------------------+-------------+| 缺席考试人数 | 2 |+------------------------------+-------------+1 row in set (0.01 sec)
+------------------------------+--------------------+| concat('缺席考试名单') | group_concat(name) |
+------------------------------+--------------------+| 缺席考试名单 | Joseph,Devri |+------------------------------+--------------------+1 row in set (0.01 sec)
+------------------------------------+----------------------------+| concat('最高成绩学生名单') | group_concat(student.name) |
+------------------------------------+----------------------------+| 最高成绩学生名单 | Megan,Kyle,Aubrey,Max |+------------------------------------+----------------------------+1 row in set (0.01 sec)
+-------------------------------------+----------------------------+| concat('最 低成绩学生名单') | group_concat(student.name) |
+-------------------------------------+----------------------------+| 最 低成绩学生名单 | Will,Rianne,Avery |+-------------------------------------+----------------------------+1 row in set (0.01 sec)
+------------------------+-------------------+------------------------+-------------------+------------------------+-------------------+| concat('最高成绩') | max(score1.score) | concat('平均成绩') | avg(score1.score) | concat('最低成绩') | min(score1.score) |
+------------------------+-------------------+------------------------+-------------------+------------------------+-------------------+| 最高成绩 | 20 | 平均成绩 | 15.1379 | 最低成绩 | 9 |+------------------------+-------------------+------------------------+-------------------+------------------------+-------------------+1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec) |
本文转自 am2012 51CTO博客，原文链接:http://blog.51cto.com/goome/1964559