Database topic Collation and explanation (IV.)

Objective

How many times sweat, pain has been filled with memories, just because always believe that to fight to win. Always inspire yourself, to succeed will have to work hard. The hot blood is boiling in the race, the giant rises in the field.

Believe in yourself, you will win, create miracles, believe in yourself, dream in your hands, this is your world. When all is past, you will be the first.

Believe in yourself, you will go beyond the limits, beyond your own!
Believe in yourself, come on, athletes, believe in yourself.

Sitting in the middle of the body, listening to this exciting cheering cheer, so you can feel the school sports will be the magnificent, although I still hit the code ...

Let's have this one, special memorial, Wolf Mercy:

Description

Cliché! Then the previous SQL statements continue to tidy up, the content of the Internet to refer to a lot of content, the end of the source, thanks to share.

This part of the main content is: MySQL database stored procedure related SQL statements, in the writing format, specifications and environment configuration, see: Database title Collation and detailed (a), description part.

Topic details

Setting up a Student information system database contains the following relationships:

STUDENT(ID, NAME, CLASSNO, BIRTH)

Complete the following functions, as described below, based on the knowledge of the stored procedures that you have learned:

(1) Create a database and a student table, and insert at least 3 data;
(2) Create and invoke a stored procedure named SP to query all the students ' information;
(3) Create and invoke a stored procedure named SP1, inserting a student's information into the table;
(4) Create and invoke a stored procedure named SP2 to query the maximum class number in the student table;
(5) Create and invoke a stored procedure named SP3, modify the student number, if the study number is 1, the revision changes to
10; otherwise, the revision number is 20, and view the information table of the university number value;
(6) Create and invoke a stored procedure named SP4, so that the student's class number is both input parameters and output parameters;
(7) Create and invoke a stored procedure named SP5, judging the class name according to the class number entered;
(8) Create and invoke a stored procedure named SP6, and update the class name according to the class number entered;

Topic answer

(1) Create a database and a student table, and insert at least 3 data;

 Create database procedure_test;# # Create a database use Procedure_test; # # Switch to the database drop table if exists student;# # Good Habits Create  table Student (# # Build ID int(one) primary key, name 
     
      char(
      ten)
       not 
      null, Classno 
      int(
      one), birth datetime);
     DESC student; # # Look at the table structure # # Insert Data Insert  into student values(1, ' Xiaohua ',  ' 1992-02-01 10:20:31 '); Insert  into student values(2, ' xiaohua1 ', in.  1993-05-11 20:34:35 '); Insert  into student values(3, ' xiaohua2 ',  ' 1989-011-11 11:11:11 ');# # Check it out. Select *  from student;

(2) Create and invoke a stored procedure named SP to query all the students ' information;

procedure if exists sp;    ## 好习惯createprocedure sp() select * from student;  ## 建立存储过程call sp();      procedure status \G;       ## 参看一哈

(3) Create and invoke a stored procedure named SP1, inserting a student's information into the table;

Delimiter//# # Self-made separators to prevent ";" Accidents# # Next is the same as the top, don't write itCreate  procedure SP1( in ID int.,  in Name Char (TEN), inClassno  int(one),  in birth datetime ) comment '  Insert into  a value  to student'begin  Set @id = ID;        Set@name = name;Set@classno = Classno;Set@birth = birth; Insert intoStudent values (ID, name, Classno, birth);End//Call SP1 (4,' Xiaohua4 ',4,' 2010-10-05 13:14:27 ')//Select* fromStudent//Show procedure status \G 

(4) Create and invoke a stored procedure named SP2 to query the maximum class number in the student table;

create procedure sp2(out p int)deterministicbegin select max(classno) into p from student;end//call sp2(@pv)//select @pv//show procedure status \G

(5) Create and invoke a stored procedure named SP3, modify the student number, if the study number is 1, the revision changes to
10; otherwise, the revision number is 20, and view the information table of the university number value;

Create  procedure SP3( in P1 int,  out p2 int)beginif p1 = 1 thenset @v = ten;                Else        Set@v = -;End if;SelectMax (ID) intoP2 fromStudentEnd//Call SP3 (1, @ret)//Select@ret; show procedure status \G 

(6) Create and invoke a stored procedure named SP4, so that the student's class number is both input parameters and
Out parameters;

 Drop procedure if exists sp4//create procedure SP4 (InOut P4 int) begin if P4 = 3 thenset @Value = ;            Else Set @Value = +;     End if;     Select @value; End// call SP4 (@result)//set @result = 4/ /Call SP4 (@result) //

(7) Create and invoke a stored procedure named SP5, judging the class name according to the class number entered;

drop  procedure  Span class= "Hljs-keyword" >if  exists  sp5//## sharp-eyed children's shoes may be seen, this adds one step, To compensate for the mistake made in the first question, the table structure forgot to establish the attribute alter  table  Student Span class= "Hljs-keyword" >add  classname char  ( One )//create  procedure  SP5 (in  classno int , out classname     Char  (11 )) begin  if  classno = 1  then  set  classname =  ' FirstClass ' ;      End if;If Classno = 2 Then set classname = ' Secondclass ';     End if;If Classno = 3 Then set classname = ' Thirdclass ';     End if;If Classno = 4 Then set classname = ' Fourthclass ';     End if;     Select *  from student; End// call SP5 (2, @ret)//select @ret;

(8) Create and invoke a stored procedure named SP5, and update the class name according to the class number entered;

# # Errors are always coincidentally, obviously! This step is to compensate for the unfinished task of the previous step. Create procedure SP6 ( in classno int)beginif classno = 1  thenupdate student set classname = ' FirstClass ' where id= 1;                 End if;If Classno = 2 thenupdate Student set classname = ' secondclass ' where id=2;     End if;If Classno = 3 Then Update student set classname = ' Thirdclass ' where id=3;     End if;If Classno = 4 Then Update student set classname = ' Fourthclass ' where id=4;     End if;     Select *  from student; End// Call SP6 (1)/ /Call SP6 (2) //Call SP6 ( 3) Call SP6 (4)//  

Resources

[1]. http://www.cnblogs.com/zhuawang/p/4185302.html
[2]. http://blog.csdn.net/woshixuye/article/details/8348180
[3]. http://www.2cto.com/database/201408/327315.html

Database topic Collation and explanation (IV.)