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