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 | +------+-----------+-------+