1 drop database if exists sunwuhao_过程与函数;
2 create database sunwuhao_过程与函数 default character set=utf8;
3 use sunwuhao_过程与函数;
4
5 drop table if exists sunwuhao_student;
6 create table sunwuhao_student(
7 sno int primary key AUTO_INCREMENT,
8 sname varchar(10),
9 class varchar(5),
10 sex char(4) check(sex='男' or sex='女'),
11 grade int check(grade<=100 and grade>=0)
12 )AUTO_INCREMENT=1001;
13 insert into sunwuhao_student values (null,'张三','C1','男',88);
14 insert into sunwuhao_student values (null,'李四','C1','男',99);
15 insert into sunwuhao_student values (null,'王五','C2','男',88);
16 insert into sunwuhao_student values (null,'赵柳','C3','女',56);
17 insert into sunwuhao_student values (null,'小时','C4','男',77);
18 insert into sunwuhao_student values (null,'孙武','C5','女',98);
19 insert into sunwuhao_student values (null,'孙铁','C4','男',90);
20 insert into sunwuhao_student values (null,'孙哥','C3','女',88);
21 insert into sunwuhao_student values (null,'大圣','C3','女',75);
22 insert into sunwuhao_student values (null,'师兄','C1','男',100);
23 insert into sunwuhao_student values (null,'猴子','C3','女',0);
24 insert into sunwuhao_student values (null,'猴哥','C2','男',89);
25 insert into sunwuhao_student values (null,'鸭鸭','C1','女',37);
26 select * from sunwuhao_student;
27
28
29
30
31
32 -- 1、自行创建一个数据库，
33 --
34 -- 2、创建一个表，表为学生表，例如，liuzhijun_student，表中含五个字段
35 --
36 -- 如Liuzhijun_student(sno,sname.class,sex,grade) //(学号，姓名，班级，性别，分数)
37 --
38 -- 3、头自行设计录入记录10条以上
39
40
41
42 -- 三、实操细目（在MySQL 命令窗口中完成）
43 --
44 -- 1、创建一个存储过程，实现查询表student中的记录信息，并执行存储过程。
45 Delimiter $$
46 drop PROCEDURE if exists select_stu$$
47 CREATE PROCEDURE select_stu()
48 BEGIN
49 select * from sunwuhao_student;
50 END$$
51 Delimiter ;
52 CALL select_stu();
53
54 -- 2、创建一个存储过程，根据给定的学生学号返回该学生的姓名 。
55 Delimiter $$
56 drop PROCEDURE if exists select_stu_where$$
57 CREATE PROCEDURE select_stu_where(in sno int)
58 BEGIN
59 SELECT s.sname from sunwuhao_student as s WHERE s.sno=sno;
60 END$$
61 Delimiter ;
62 CALL select_stu_where(1001);
63
64 -- 3、创建一个存储过程，根据所给的班级的编号，统计该班人数，并将人数以输出变量返回给用户。
65 Delimiter $$
66 drop PROCEDURE if exists select_stu_count$$
67 CREATE PROCEDURE select_stu_count(in Stu_class varchar(20))
68 BEGIN
69 declare sum_mumber int DEFAULT 0;
70 set @sum_mumber=(SELECT COUNT(sunwuhao_student.sno) from sunwuhao_student WHERE sunwuhao_student.class=Stu_class);
71 SELECT @sum_mumber as 该班人数; -- 输出
72 END$$
73 Delimiter ;
74 CALL select_stu_count('C1');
75
76
77
78 -- 4、创建一个存储过程，根据所给的学号查询该学生的考试分数是否超过了85分， 若超过则输出“ 考得好”， 否则输出 “考得不好”。
79 Delimiter $$
80 drop PROCEDURE if exists select_stu_gradeGood;
81 CREATE PROCEDURE select_stu_gradeGood(in sno int)
82 BEGIN
83 IF (SELECT grade from sunwuhao_student as s WHERE s.sno=sno)>85 THEN
84 SELECT '考得好';
85 ELSE
86 SELECT '考得不好';
87 END IF;
88 END$$
89 Delimiter ;
90 CALL select_stu_gradeGood(1001);
91
92
93
94 -- 5、撰写一个存储过程，输出各班学生的平均成绩。
95 Delimiter $$
96 drop PROCEDURE if exists select_stu_avgGrade$$
97 CREATE PROCEDURE select_stu_avgGrade()
98 BEGIN
99 -- SELECT s.class as 班级,AVG(s.grade) as 平均分 from sunwuhao_student as s group by s.class;
100 SELECT (s.class)as 班级 ,(AVG(s.grade)) as 平均分 from sunwuhao_student as s group by s.class;
101 END$$
102 Delimiter ;
103 CALL select_stu_avgGrade();
104
105 -- 6、写一个存储过程，求1+2+3+…x的值。
106 Delimiter $$
107 drop PROCEDURE if exists select_stu_1_100_sum$$
108 CREATE PROCEDURE select_stu_1_100_sum(in x int)
109 BEGIN
110 declare i int default 1;
111 declare num int default 0;
112 WHILE i<=x DO
113 set num=num+i;
114 set i=i+1;
115 END WHILE;
116 select num;
117 END$$
118 Delimiter ;
119 CALL select_stu_1_100_sum(100);
120
121
122
123 -- 例如：创建一个存储过程，根据所给的学号查询该学生的考试分数是否超过了85分， 若超过则输出“ 考得好”， 否则输出 “考得不好”。
124 --
125 -- 注意在MySQL 命令窗口中对代码的截图和对运行结果的截图，要清晰。
126 Delimiter ;;
127 CREATE PROCEDURE proc4(in sno int)
128 BEGIN
129 IF (SELECT grade from liuzhijun_student s WHERE s.sno=sno)>85 THEN
130 SELECT '考得好';
131 ELSE
132 SELECT '考得不好';
133 END IF;
134 END;;
135 Delimiter ;;
136 CALL proc4(sno);
137 //sno 可换具体的值，不同的值，结果显示的好坏，取决于你数据录入的好坏
浙公网安备 33010602011771号