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

5645re

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)
begin
select 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)