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

2017-09-13 10:26:48来源:http://goome.blog.51cto.com/4045241/1964559作者:人点击

分享

1.sampdb所用到的表格
sampdb关于学生考试的表格总共有四张,如下
学生信息表
MariaDB[sampdb]>select*fromstudent;
+-----------+-----+------------+
|name|sex|student_id|
+-----------+-----+------------+
|Megan|F|1|
|Joseph|M|2|
|Kyle|M|3|

学生成绩表
MariaDB[sampdb]>select*fromscore;
+------------+----------+-------+
|student_id|event_id|score|
+------------+----------+-------+
|1|1|20|
|3|1|20|
|4|1|18|

测试考试统计表
MariaDB[sampdb]>select*fromgrade_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*fromabsence;
+------------+------------+
|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|CREATETABLE`student`(
`name`varchar(20)NOTNULL,
`sex`enum('F','M')NOTNULL,
`student_id`int(10)unsignedNOTNULLAUTO_INCREMENT,
PRIMARYKEY(`student_id`)
)ENGINE=InnoDBAUTO_INCREMENT=32DEFAULTCHARSET=latin1|

score

|score|CREATETABLE`score`(
`student_id`int(10)unsignedNOTNULL,
`event_id`int(10)unsignedNOTNULL,
`score`int(11)NOTNULL,
PRIMARYKEY(`event_id`,`student_id`),
KEY`student_id`(`student_id`),
CONSTRAINT`score_ibfk_1`FOREIGNKEY(`event_id`)REFERENCES`grade_event`(`event_id`),
CONSTRAINT`score_ibfk_2`FOREIGNKEY(`student_id`)REFERENCES`student`(`student_id`)
)ENGINE=InnoDBDEFAULTCHARSET=latin1|

grade_event

|grade_event|CREATETABLE`grade_event`(
`date`dateNOTNULL,
`category`enum('T','Q')NOTNULL,
`event_id`int(10)unsignedNOTNULLAUTO_INCREMENT,
PRIMARYKEY(`event_id`)
)ENGINE=InnoDBAUTO_INCREMENT=7DEFAULTCHARSET=latin1|

absence

|absence|CREATETABLE`absence`(
`student_id`int(10)unsignedNOTNULL,
`date`dateNOTNULL,
PRIMARYKEY(`student_id`,`date`),
CONSTRAINT`absence_ibfk_1`FOREIGNKEY(`student_id`)REFERENCES`student`(`student_id`)
)ENGINE=InnoDBDEFAULTCHARSET=latin1|
3.检索数据

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

MariaDB[sampdb]>selectcount(student_id)fromscorewhereevent_id=1;
+-------------------+
|count(student_id)|
+-------------------+
|29|
+-------------------+
1rowinset(0.00sec)

列出参加event_id为1考试的学生名单

MariaDB[sampdb]>selectgroup_concat(student.name)fromstudentleftjoinscoreonstudent.student_id=score.student_id
wherescore.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]>selectcount(student.student_id)fromstudentleftjoin(select*fromscorewhereevent_id=1)asscore1onstudent.student_id=score1.student_idwherescore1.scoreisnull;
+---------------------------+
|count(student.student_id)|
+---------------------------+
|2|
+---------------------------+
1rowinset(0.00sec)
列出未参加event_id为1考试的学生名单

MariaDB[sampdb]>selectgroup_concat(student.name)fromstudentleftjoin(select*fromscorewhereevent_id=1)
asscore1onstudent.student_id=score1.student_idwherescore1.scoreisnull;
+----------------------------+
|group_concat(student.name)|
+----------------------------+
|Joseph,Devri|
+----------------------------+

检索参加event_id为1考试的学生最高成绩,平均成绩,最低成绩

MariaDB[sampdb]>selectmax(score1.score),avg(score1.score),min(score1.score)fromstudentleftjoin(select*
fromscorewhereevent_id=1)asscore1onstudent.student_id=score1.student_id;
+-------------------+-------------------+-------------------+
|max(score1.score)|avg(score1.score)|min(score1.score)|
+-------------------+-------------------+-------------------+
|20|15.1379|9|
+-------------------+-------------------+-------------------+

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

selectstudent.namefromstudentleftjoinscoreonstudent.student_id=score.student_idwhere
score.event_id=1andscore=(selectmax(score)fromscorewhereevent_id=1);
+--------+
|name|
+--------+
|Megan|
|Kyle|
|Aubrey|
|Max|
+--------+

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

MariaDB[sampdb]>selectstudent.name,scorefromstudentleftjoinscoreonstudent.student_id=score.student_idwherescore.event_id=1andscore=(selectmin(score)fromscorewhereevent_id=1);
+--------+-------+
|name|score|
+--------+-------+
|Will|9|
|Rianne|9|
|Avery|9|
+--------+-------+
3rowsinset(0.00sec)

4.写一个存储过程,根据考试ID,列出这次考试多少人参加,列出参加学生的名字,多少人缺席,列出缺席的学生名字,
列出考试的最高成绩的获得者的名字和最高成绩,最低成绩的获得者名字和最低成绩,平均成绩。

createprocedureid_in(numint)
begin
selectconcat('考试人数'),count(student_id)fromscorewhereevent_id=num;
selectconcat('参加考试名单'),group_concat(name)fromstudentleftjoinscoreonstudent.student_id=score.student_idwhereevent_id=num;
selectconcat('缺席考试人数'),count(name)fromstudentleftjoin(select*fromscorewhereevent_id=num)asscore1onstudent.student_id=score1.student_idwherescore1.scoreisnull;
selectconcat('缺席考试名单'),group_concat(name)fromstudentleftjoin(select*fromscorewhereevent_id=num)asscore1onstudent.student_id=score1.student_idwherescore1.scoreisnull;
selectconcat('最高成绩学生名单'),group_concat(student.name)fromstudentleftjoinscoreonstudent.student_id=score.student_idwherescore.event_id=numandscore=(selectmax(score)fromscorewherescore.event_id=num);
selectconcat('最低成绩学生名单'),group_concat(student.name)fromstudentleftjoinscoreonstudent.student_id=score.student_idwherescore.event_id=numandscore=(selectmin(score)fromscorewherescore.event_id=num);
selectconcat('最高成绩'),max(score1.score),concat('平均成绩'),avg(score1.score),concat('最低成绩'),min(score1.score)fromstudentleftjoin(select*fromscorewherescore.event_id=num)asscore1onstudent.student_id=score1.student_id;
end$
结果:
MariaDB[sampdb]>callid_in(1);
+------------------------+-------------------+
|concat('考试人数')|count(student_id)|
+------------------------+-------------------+
|考试人数|29|
+------------------------+-------------------+
1rowinset(0.01sec)

+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|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|
+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1rowinset(0.01sec)

+------------------------------+-------------+
|concat('缺席考试人数')|count(name)|
+------------------------------+-------------+
|缺席考试人数|2|
+------------------------------+-------------+
1rowinset(0.01sec)

+------------------------------+--------------------+
|concat('缺席考试名单')|group_concat(name)|
+------------------------------+--------------------+
|缺席考试名单|Joseph,Devri|
+------------------------------+--------------------+
1rowinset(0.01sec)

+------------------------------------+----------------------------+
|concat('最高成绩学生名单')|group_concat(student.name)|
+------------------------------------+----------------------------+
|最高成绩学生名单|Megan,Kyle,Aubrey,Max|
+------------------------------------+----------------------------+
1rowinset(0.01sec)

+-------------------------------------+----------------------------+
|concat('最低成绩学生名单')|group_concat(student.name)|
+-------------------------------------+----------------------------+
|最低成绩学生名单|Will,Rianne,Avery|
+-------------------------------------+----------------------------+
1rowinset(0.01sec)

+------------------------+-------------------+------------------------+-------------------+------------------------+-------------------+
|concat('最高成绩')|max(score1.score)|concat('平均成绩')|avg(score1.score)|concat('最低成绩')|min(score1.score)|
+------------------------+-------------------+------------------------+-------------------+------------------------+-------------------+
|最高成绩|20|平均成绩|15.1379|最低成绩|9|
+------------------------+-------------------+------------------------+-------------------+------------------------+-------------------+
1rowinset(0.01sec)

QueryOK,0rowsaffected(0.01sec)

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台