mysql sampdb数据库存储过程操作记录
1.sampdb所用到的表格sampdb关于学生考试的表格总共有四张，如下
学生信息表
MariaDB > select * from student;
+-----------+-----+------------+
| name | sex | student_id |
+-----------+-----+------------+
| Megan | F | 1 |
| Joseph | M | 2 |
| Kyle | M | 3 |
学生成绩表
MariaDB > select * from score;
+------------+----------+-------+
| student_id | event_id | score |
+------------+----------+-------+
| 1 | 1 | 20 |
| 3 | 1 | 20 |
| 4 | 1 | 18 |
测试考试统计表
MariaDB > 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 > 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 > 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 > 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 > 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 > 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 > 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 > select student.name,score from student left join score on student.student_id=score.student_id wherescore.event_id=1 andscore=(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 > 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)
页:
[1]