MySQL sampdb database stored procedure operation record

Tag:sampdb   mysql    stored procedure    

Table used in 1.sampdb  sampdb there are a total of four forms for students ' exams, as shown in the Student information table mariadb [sampdb]> select * from  student;+-----------+-----+------------+| name      | sex |  student_id |                      +-----------+-----+------------+                      | Megan      | f   |          1  | |  Joseph    | M   |           2 | |  Kyle      | M   |           3 | Student score Table Mariadb [sampdb]> select * from score; +------------+----------+-------+| student_id | event_id | score |+------------ +----------+-------+|          1 |         1 |    20 | |           3 |         1 |    20 | |           4 |         1 |    18 | Test Statistics 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 |+------------+----------+----------+ student absence form 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. Table Structure Index     student   | student | CREATE TABLE  ' student '   (   ' Name '  varchar '  NOT NULL,   ' sex '  enum (' F ', ' M ')  NOT NULL,   ' student_id '  int (Ten)  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 (Ten)  unsigned NOT NULL,   ' event_id '   Int (Ten)  unsigned NOT NULL,   ' score '  int (one)  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 (Ten)  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 (Ten)  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. Retrieving data retrieval statistics number of students enrolled in the EVENT_ID 1 exam mariadb [sampdb]> select count (student_id)  from score where event_id=1;+-------------------+| count (student_id)  |+-------- -----------+|                 29 |+-------------------+1 row in set  (0.00 sec)   List participating Event_ List of students with ID 1 exam 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 |+---------Search Statistics not attended Event_ Number of students with ID 1 exam 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) lists the list of students who did not take the EVENT_ID exam 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                |+----------------------------+ Search for the highest score, average score and minimum score of the students who participated in EVENT_ID 1 exams   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 |+-------------------+-------------------+-------------------+ search Join Event_ ID 1 The highest grade student name for the exam select student.name from student left join score on  student.student_id=score.student_id&Nbsp;where  score.event_id=1 and score= (Select max (score)  from score  where event_id=1); +--------+| name   |+--------+| megan  | |  kyle   | |  aubrey | |  max    |+--------+ Slow down take the student name Mariadb [sampdb]> select event_id for the lowest score of 1 exams  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. Write a stored procedure, according to the exam ID, list How many people participate in this exam,List the student's name, how many people are absent, list the name of the absent student, list the highest score of the test winner's name and highest score, the lowest score of the winner's name and the lowest score, the average score. Create procedure id_in (num int) Beginselect concat (' Number of exams '), COUNT (student_id)  from  score where event_id=num;        select concat (' Take the exam list '), Group_concat (name)  from student left join score on student.student _id=score.student_id where event_id=num;        select  Concat (' Number of missed exams '), 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 (' Absent Test List '), 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 (' top student list '), 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 (' top   low student list '), 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 (' highest score '), Max (Score1.score), concat (' average score '), AVG (Score1.score), Concat (' lowest score '), 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$ results: MariaDB  [sampdb]> call id_in (1); +------------------------+-------------------+| concat (' Number of exams ')      | count (student_id)  |+------------------------+-------------------+|   Test Number                |                 29 |+---------- --------------+-------------------+1 row in set  (0.01 sec) +-------------------------- ----+---------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------+| concat (' Take the Exam list ')         | group_concat (name)                                                                                                                                                                               |+------------------------------+----------------- ------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------+|  to take the test list                   | 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 (' Number of absentee exams ')        | count (name)  |+-------------------------- ----+-------------+|  number of missed exams                    |           2 |+--------------------- ---------+-------------+1 row in set  (0.01 sec) +------------------------------+------ --------------+| concat (' Absent Test List ')        | group_concat (name)  |+------------------------------+--------------------+|  absent test list                   | Joseph,Devri        |+------------------------------+--------------------+1 row in set  (0.01  SEC) +------------------------------------+----------------------------+| concat (' top student list ')           | group_concat (student.name)  |+--------------------- ---------------+----------------------------+|  top Student list                     | megan,kyle,aubrey,max      | +------------------------------------+----------------------------+1 row in set  (0.01  SEC) +-------------------------------------+----------------------------+| concat (' best   lowest student list ')          | group_concat (student.name)  |+--------------- ----------------------+----------------------------+|    Low grade students list                     | Will,Rianne,Avery           |+-------------------------------------+---------------- ------------+1 row in set  (0.01 sec) +------------------------+-------------------+--- ---------------------+-------------------+------------------------+-------------------+| concat (' highest score ')    &nbSp; | max (Score1.score)  | concat (' average score ')      | avg ( Score1.score)  | concat (' lowest score ')      | min (score1.score)  |+-------- ----------------+-------------------+------------------------+-------------------+------------------------+---- ---------------+|  Top Results                 |                 20 |  Average score                |            15.1379 |  Minimum score                 |                  9 |+------------------------+---------- ---------+------------------------+-------------------+------------------------+-------------------+1 row in set  ( 0.01 SEC) query ok, 0 rows affected  (0.01 sec)

This article is from the "Gome Learning" blog, so be sure to keep this source http://goome.blog.51cto.com/4045241/1964559

MySQL sampdb database stored procedure operation record