1. 创建一个存储过程proc1，将10万行如下格式的测试数据插入到students表中，数据中只有sid是递增的，其余字段值都是固定的 （1，’mike’,1,1), (2,’mike’,1,1), (3,’mike’,1,1), ……. (100000,’mike’,1,1) Delimiter // Create procedure proc1() Begin Declare n int default 1; while n<=100000 do Insert into students values(n, 'mike' , 1,1); Set n=n+1; End while; End; // Delimiter // Create procedure proc1_2() Begin Declare n int default 100000; while n>0 do Insert into students values(n, 'mike' , 1,1); Set n=n-1; End while; End; // delimiter // Create procedure proc1_3() begin Declare n int default 1; start_label: loop if n>100000 then leave start_label; End if; insert into students values(n, 'mike' , 1,1); set n=n+1; end loop; End; // Delimiter ; 2. 在第1题的基础上，创建另一个存储过程proc2，插入10万行数据到students表中，但要求gender字段在0和1之间随机，dept_id在1~3这三个整数之间取随机，sname字段固定是’mike’ Delimiter // Create procedure proc2() Begin Declare n int default 1; Declare v_gender_id int; Declare v_dept_id int; while n<=100000 do Set v_gender_id=round(rand()); Set v_dept_id=floor(rand()*3+1); Insert into students values(n, 'mike' , v_gender_id,v_dept_id); Set n=n+1; End while; End; // delimiter ; mysql> select gender,dept_id,count(*) from students group by gender,dept_id order by gen-der,dept_id; +--------+---------+----------+ | gender | dept_id | count(*) | +--------+---------+----------+ | 0 | 1 | 16678 | | 0 | 2 | 16696 | | 0 | 3 | 16660 | | 1 | 1 | 16561 | | 1 | 2 | 16679 | | 1 | 3 | 16726 | +--------+---------+----------+ 6 rows in set (0.07 sec) 3. 创建一个函数，输入参数为学生学号sid，函数返回对应学生的平均成绩 Delimiter // Create function func1(v_sid int) Returns int Begin Select avg(score) into @x from score where sid=v_sid; Return @x; End; // Delimiter ; mysql> select func1(1),func1(2); +----------+----------+ | func1(1) | func1(2) | +----------+----------+ | 74 | 82 | +----------+----------+ 1 row in set (0.00 sec) 4. 创建一个函数，输入参数是老师的id，函数返回该老师所教授的课程数量，并将这些学习这些课程的每个学生如果成绩不及格，把学生的sid和对应课程名字、成绩insert到表A中，如果成绩及格，把学生的sid和对应的课程名字、成绩insert到表B中 Delimiter // Create function func2(v_teacher_id int) Returns int Begin Declare n_course int; Select count(*) into n_course from course where teacher_id=v_teacher_id; Insert into A select a.sid,b.course_name,a.score From score a inner join course b on a.course_id=b.id Where b.teacher_id=v_teacher_id and a.score<60; Insert into B select a.sid,b.course_name,a.score From score a inner join course b on a.course_id=b.id Where b.teacher_id=v_teacher_id and a.score>=60; Return n_course; End; // Delimiter ; mysql> select func2(1); +----------+ | func2(1) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) mysql> select * from A; +------+-------------+-------+ | sid | course_name | score | +------+-------------+-------+ | 3 | english | 54 | +------+-------------+-------+ 1 row in set (0.00 sec) mysql> select * from B; +------+-------------+-------+ | sid | course_name | score | +------+-------------+-------+ | 1 | english | 78 | | 2 | english | 84 | +------+-------------+-------+ 2 rows in set (0.00 sec) 5.基于第4题，用游标的方法实现相同功能 delimiter // Create function func3(v_teacher_id int) Returns int Begin Declare n_course int; Declare v_sid int default null; Declare v_course_name varchar(60); Declare v_score int; Declare cur1 cursor for select a.sid,b.course_name,a.score From score a inner join course b on a.coure_id=b.id Where b.teacher_id=v_teacher_id; declare continue handler for not found set v_sid=null; Select count(*) into n_course from course where teacher_id=v_teacher_id; Open cur1; Fetch cur1 into v_sid,v_course_name,v_score; While v_sid is not null do If v_score<60 then insert into A select v_sid,v_course_name,v_score; Else insert into B select v_sid,v_course_name,v_score; End if; Fetch cur1 into v_sid,v_course_name,v_score; End while; Close cur1; Return n_course; End; // Delimiter ; 6. 在score表上创建一个触发器，当有新的数据插入时，在score_bak表里记录新插入的数据的所有字段信息，并用tstamp字段标注数据的插入时间 Delimiter // Create trigger trig1 after insert on score For each row Begin Insert into score_bak(Sid,course_id,score,tstamp) val-ues(new.sid,new.course_id,new.score,now()); End; // Delimiter ; 7. 在score表上创建一个触发器，当有新的数据插入时，在score_avg表里记录对应学生的所有课程的平均成绩（注意，如果在score_avg表里已经有了学生的记录，需要update） Delimiter // Create trigger trig2 After insert on score For each row Begin Declare n int; Select count(*) into n from score_avg where sid=new.sid; If n=1 then update score_avg set avg_score=(select avg(score) from score where sid=new.sid) where sid=new.sid; Else insert into score_avg select sid,avg(score) from score where sid=new.sid group by sid; End if; End; // Delimiter ; ######################################################## 1. Proc1执行到一半失败了，数据会怎样？ 2. MyISAM存储引擎下的表修改之后，触发器修改失败，数据会回滚？ Mysql> create table score1(sid int,course_id int,score int) engine=myisam; mysql> create table score_bak1(sid int,course_id int,score int,tstamp datetime,primary key(sid,course_id)); Delimiter // Create trigger trig1 after insert on score1 For each row Begin Insert into score_bak1(Sid,course_id,score,tstamp) values(new.sid,new.course_id,new.score,now()); End; // Delimiter ; mysql> insert into score1 values(1,1,90); Query OK, 1 row affected (0.00 sec) mysql> select * from score_bak1; +-----+-----------+-------+---------------------+ | sid | course_id | score | tstamp | +-----+-----------+-------+---------------------+ | 1 | 1 | 90 | 2017-05-12 21:17:35 | +-----+-----------+-------+---------------------+ 1 row in set (0.00 sec) mysql> insert into score1 values(1,1,78); ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY' mysql> select * from score1; +------+-----------+-------+ | sid | course_id | score | +------+-----------+-------+ | 1 | 1 | 90 | | 1 | 1 | 78 | +------+-----------+-------+ Delimiter // Create trigger trig1 before insert on score1 For each row Begin Insert into score_bak1(Sid,course_id,score,tstamp) values(new.sid,new.course_id,new.score,now()); End; // Delimiter ; mysql> insert into score1 values(1,1,80); ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY' mysql> mysql> mysql> mysql> select * from score1; +------+-----------+-------+ | sid | course_id | score | +------+-----------+-------+ | 1 | 1 | 90 | | 1 | 1 | 78 | +------+-----------+-------+