mysql sampdb数据库存储过程操作记录

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

<code>1.sampdb所用到的表格</code>

<code> </code><code>sampdb关于学生考试的表格总共有四张，如下</code>

<code>学生信息表</code>

<code>MariaDB [sampdb]&gt; </code><code>select</code> <code>* from student;</code>

<code>+-----------+-----+------------+</code>

<code>| name      | sex | student_id |                     </code>

<code>+-----------+-----+------------+                     </code>

<code>| Megan     | F   |          1 |</code>

<code>| Joseph    | M   |          2 |</code>

<code>| Kyle      | M   |          3 |</code>

<code>学生成绩表</code>

<code>MariaDB [sampdb]&gt; </code><code>select</code> <code>* from score;</code>

<code>+------------+----------+-------+</code>

<code>| student_id | event_id | score |</code>

<code>|          1 |        1 |    20 |</code>

<code>|          3 |        1 |    20 |</code>

<code>|          4 |        1 |    18 |</code>

<code>测试考试统计表</code>

<code>MariaDB [sampdb]&gt; </code><code>select</code> <code>* from grade_event;</code>

<code>+------------+----------+----------+</code>

<code>| </code><code>date</code>       <code>| category | event_id |</code>

<code>| 2008-09-03 | Q        |        1 |</code>

<code>| 2008-09-06 | Q        |        2 |</code>

<code>| 2008-09-09 | T        |        3 |</code>

<code>| 2008-09-16 | Q        |        4 |</code>

<code>| 2008-09-23 | Q        |        5 |</code>

<code>| 2008-10-01 | T        |        6 |</code>

<code>学生缺勤表</code>

<code>MariaDB [sampdb]&gt; </code><code>select</code> <code>* from absence;</code>

<code>+------------+------------+</code>

<code>| student_id | </code><code>date</code>       <code>|</code>

<code>|          3 | 2008-09-03 |</code>

<code>|          5 | 2008-09-03 |</code>

<code>|         10 | 2008-09-06 |</code>

<code>|         10 | 2008-09-09 |</code>

<code>|         17 | 2008-09-07 |</code>

<code>|         20 | 2008-09-07 |</code>

<code>2.表格结构索引</code>

<code>   </code><code>student</code>

<code>   </code><code>| student | CREATE TABLE `student` (</code>

<code>  </code><code>`name` varchar(20) NOT NULL,</code>

<code>  </code><code>`sex` enum(</code><code>'F'</code><code>,</code><code>'M'</code><code>) NOT NULL,</code>

<code>  </code><code>`student_id` int(10) unsigned NOT NULL AUTO_INCREMENT,</code>

<code>  </code><code>PRIMARY KEY (`student_id`)</code>

<code>) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 |</code>

<code>   </code><code>score</code>

<code>   </code> 

<code>   </code><code>| score | CREATE TABLE `score` (</code>

<code>  </code><code>`student_id` int(10) unsigned NOT NULL,</code>

<code>  </code><code>`event_id` int(10) unsigned NOT NULL,</code>

<code>  </code><code>`score` int(11) NOT NULL,</code>

<code>  </code><code>PRIMARY KEY (`event_id`,`student_id`),</code>

<code>  </code><code>KEY `student_id` (`student_id`),</code>

<code>  </code><code>CONSTRAINT `score_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `grade_event` (`event_id`),</code>

<code>  </code><code>CONSTRAINT `score_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)</code>

<code>) ENGINE=InnoDB DEFAULT CHARSET=latin1 |</code>

<code>  </code><code>grade_event</code>

<code>  </code><code>| grade_event | CREATE TABLE `grade_event` (</code>

<code>  </code><code>`</code><code>date</code><code>` </code><code>date</code> <code>NOT NULL,</code>

<code>  </code><code>`category` enum(</code><code>'T'</code><code>,</code><code>'Q'</code><code>) NOT NULL,</code>

<code>  </code><code>`event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,</code>

<code>  </code><code>PRIMARY KEY (`event_id`)</code>

<code>) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |</code>

<code>   </code><code>absence</code>

<code>   </code><code>| absence | CREATE TABLE `absence` (</code>

<code>  </code><code>PRIMARY KEY (`student_id`,`</code><code>date</code><code>`),</code>

<code>  </code><code>CONSTRAINT `absence_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)</code>

<code>3.检索数据</code>

<code>检索统计参加event_id为1考试的学生人数</code>

<code>MariaDB [sampdb]&gt; </code><code>select</code> <code>count(student_id) from score where event_id=1;</code>

<code>+-------------------+</code>

<code>| count(student_id) |</code>

<code>|                29 |</code>

<code>1 row </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>

<code> </code><code>列出参加event_id为1考试的学生名单</code>

<code>MariaDB [sampdb]&gt; </code><code>select</code> <code>group_concat(student.name) from student left </code><code>join</code> <code>score on student.student_id=score.student_id </code>

<code>where score.event_id=1;</code>

<code>+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</code>

<code>| group_concat(student.name)                                                                                                                                                             |</code>

<code>| Megan,Kyle,Katie,Abby,Nathan,Liesl,Ian,Colin,Peter,Michael,Thomas,Ben,Aubrey,Rebecca,Will,Max,Rianne,Avery,Lauren,</code>

<code>Becca,Gregory,Sarah,Robbie,Keaton,Carter,Teddy,Gabrielle,Grace,Emily |</code>

<code>+---------</code>

<code>检索统计未参加event_id为1考试的学生人数</code>

<code>MariaDB [sampdb]&gt; </code><code>select</code> <code>count(student.student_id) from student left </code><code>join</code> <code>(</code><code>select</code> <code>* from score where event_id=1) as score1 on student.student_id=score1.student_id where score1.score is null;</code>

<code>+---------------------------+</code>

<code>| count(student.student_id) |</code>

<code>|                         2 |</code>

<code>列出未参加event_id为1考试的学生名单</code>

<code>MariaDB [sampdb]&gt; </code><code>select</code> <code>group_concat(student.name) from student left </code><code>join</code> <code>(</code><code>select</code> <code>* from score where event_id=1)</code>

<code> </code><code>as score1 on student.student_id=score1.student_id where score1.score is null;</code>

<code>+----------------------------+</code>

<code>| group_concat(student.name) |</code>

<code>| Joseph,Devri               |</code>

<code>检索参加event_id为1考试的学生最高成绩，平均成绩，最低成绩</code>

<code>  </code><code>MariaDB [sampdb]&gt; </code><code>select</code> <code>max(score1.score),avg(score1.score),min(score1.score) from student left </code><code>join</code> <code>(</code><code>select</code> <code>* </code>

<code>from score where event_id=1) as score1 on student.student_id=score1.student_id;</code>

<code>+-------------------+-------------------+-------------------+</code>

<code>| max(score1.score) | avg(score1.score) | min(score1.score) |</code>

<code>|                20 |           15.1379 |                 9 |</code>

<code>检索参加event_id为1考试的最高成绩的学生名字</code>

<code>select</code> <code>student.name from student left </code><code>join</code> <code>score on student.student_id=score.student_id where  </code>

<code>score.event_id=1 and score=(</code><code>select</code> <code>max(score) from score where event_id=1);</code>

<code>+--------+</code>

<code>| name   |</code>

<code>| Megan  |</code>

<code>| Kyle   |</code>

<code>| Aubrey |</code>

<code>| Max    |</code>

<code>减速参加event_id为1考试的最低成绩的学生名字</code>

<code>MariaDB [sampdb]&gt; </code><code>select</code> <code>student.name,score from student left </code><code>join</code> <code>score on student.student_id=score.student_id where  score.event_id=1 and  score=(</code><code>select</code> <code>min(score) from score where event_id=1);</code>

<code>+--------+-------+</code>

<code>| name   | score |</code>

<code>| Will   |     9 |</code>

<code>| Rianne |     9 |</code>

<code>| Avery  |     9 |</code>

<code>3 rows </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>

<code>4.写一个存储过程，根据考试ID，列出这次考试多少人参加，列出参加学生的名字，多少人缺席，列出缺席的学生名字，</code>

<code>列出考试的最高成绩的获得者的名字和最高成绩，最低成绩的获得者名字和最低成绩，平均成绩。</code>

<code>create procedure id_in(num int)</code>

<code>begin</code>

<code>select</code> <code>concat(</code><code>'考试人数'</code><code>),count(student_id) from score where event_id=num;        </code>

<code>select</code> <code>concat(</code><code>'参加考试名单'</code><code>),group_concat(name) from student left </code><code>join</code> <code>score on student.student_id=score.student_id where event_id=num;        </code>

<code>select</code> <code>concat(</code><code>'缺席考试人数'</code><code>),count(name) from student left </code><code>join</code> <code>(</code><code>select</code> <code>* from score where event_id=num) as score1 on student.student_id=score1.student_id where score1.score is null;       </code>

<code>select</code> <code>concat(</code><code>'缺席考试名单'</code><code>),group_concat(name) from student left </code><code>join</code> <code>(</code><code>select</code> <code>* from score where event_id=num) as score1 on student.student_id=score1.student_id where score1.score is null;       </code>

<code>select</code> <code>concat(</code><code>'最高成绩学生名单'</code><code>),group_concat(student.name) from student left </code><code>join</code> <code>score on student.student_id=score.student_id where score.event_id=num and score=(</code><code>select</code> <code>max(score) from score where score.event_id=num);         </code>

<code>select</code> <code>concat(</code><code>'最 低成绩学生名单'</code><code>),group_concat(student.name) from student left </code><code>join</code> <code>score on student.student_id=score.student_id where score.event_id=num and score=(</code><code>select</code> <code>min(score) from score where score.event_id=num); </code>

<code>select</code> <code>concat(</code><code>'最高成绩'</code><code>),max(score1.score),concat(</code><code>'平均成绩'</code><code>),avg(score1.score),concat(</code><code>'最低成绩'</code><code>),min(score1.score) from student left </code><code>join</code> <code>(</code><code>select</code> <code>* from score where score.event_id=num) as score1 on student.student_id=score1.student_id; </code>

<code>end$</code>

<code>结果：</code>

<code>MariaDB [sampdb]&gt; call id_in(1);</code>

<code>+------------------------+-------------------+</code>

<code>| concat(</code><code>'考试人数'</code><code>)     | count(student_id) |</code>

<code>| 考试人数               |                29 |</code>

<code>1 row </code><code>in</code> <code>set</code> <code>(0.01 sec)</code>

<code>+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</code>

<code>| concat(</code><code>'参加考试名单'</code><code>)       | group_concat(name)                                                                                                                                                                     |</code>

<code>| 参加考试名单                 | 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 |</code>

<code>+------------------------------+-------------+</code>

<code>| concat(</code><code>'缺席考试人数'</code><code>)       | count(name) |</code>

<code>| 缺席考试人数                 |           2 |</code>

<code>+------------------------------+--------------------+</code>

<code>| concat(</code><code>'缺席考试名单'</code><code>)       | group_concat(name) |</code>

<code>| 缺席考试名单                 | Joseph,Devri       |</code>

<code>+------------------------------------+----------------------------+</code>

<code>| concat(</code><code>'最高成绩学生名单'</code><code>)         | group_concat(student.name) |</code>

<code>| 最高成绩学生名单                   | Megan,Kyle,Aubrey,Max      |</code>

<code>+-------------------------------------+----------------------------+</code>

<code>| concat(</code><code>'最 低成绩学生名单'</code><code>)         | group_concat(student.name) |</code>

<code>| 最 低成绩学生名单                   | Will,Rianne,Avery          |</code>

<code>+------------------------+-------------------+------------------------+-------------------+------------------------+-------------------+</code>

<code>| concat(</code><code>'最高成绩'</code><code>)     | max(score1.score) | concat(</code><code>'平均成绩'</code><code>)     | avg(score1.score) | concat(</code><code>'最低成绩'</code><code>)     | min(score1.score) |</code>

<code>| 最高成绩               |                20 | 平均成绩               |           15.1379 | 最低成绩               |                 9 |</code>

<code>Query OK, 0 rows affected (0.01 sec)</code>

本文转自 am2012 51CTO博客，原文链接:http://blog.51cto.com/goome/1964559