Mysql实验2

南京邮电大学计算机学院
《大型数据库技术》
实验三：MySQL数据库进阶开发
姓名：王觉龙班级：B130404 学号：B13040412
2016年 4月 15日星期五
说明：斜体需要输出的部分。

1MySQL的存储过程和函数
1.1创建企业销售系统的数据库，命名为salesdb
create database salesdb charset=utf8;
1.2创建一张商品销售表，命名为salerecords，包括如下字段：商品ID，商品名称，销
售单价，销售数量。

create table salerecords(id int auto_increment primary key,name varchar(20) default 'book',price int,number int);
1.3创建一个存储过程，名称自订，通过输入商品ID，商品名称，销售单价，销售数量往
表中插入一条记录。

delimiter $$
create procedure film_1_3(IN p_id int,IN p_name varchar(20),IN p_price int,IN p_number int)
READS SQL DATA
BEGIN
insert into salerecords values(p_id,p_name,p_price,p_number);
END
$$
DELIMITER ;
call film_1_3(1,'book1',150,5000);
call film_1_3(2,'book2',250,1000);
call film_1_3(3,'book3',350,4000);
select * from salerecords;
输出截图：（包括存储过程定义，调用三次存储过程，以及表的查询结果）
1.4创建一个存储过程，名称自订，通过输入商品名称往表中插入100条记录。

其中，所
插入第一条记录的商品ID由现有表中商品ID的最大值+1构成，后续记录中商品ID 依次递增1。

所插入第一条记录的商品价格与现有表中商品价格的最小值相同，后续记录的商品价格依次递增1。

商品销售数量随机生成。

delimiter $$
create procedure film_1_4(IN p_name varchar(20))
begin
set @x=0;
set @y=0;
set @temp=1;
select max(id) into @x from salerecords;
select min(price) into @y from salerecords;
repeat
set @z=floor(rand()*900);
insert into salerecords values(@x+1,p_name,@y,@z);
set @x=@x+1;
set @y=@y+1;
set @temp=@temp+1;
until @temp>100 end repeat;
end;
$$
delimiter ;
call film_1_4('book4');
select * from salerecords limit 10;
输出截图：（包括存储过程定义，表中前10条查询结果）
1.5创建一个存储过程，名称自定，通过输入商品ID的最小值，最大值，将表中商品ID
处于(最小值，最大值)范围内，且为偶数的记录删除，包括最小值与最大值本身。

此处不考虑商品ID不存在的情况，在实验时请选择1.4中创建的商品ID范围的子集。

delimiter $$
create procedure film_1_5(in minid int,in maxid int)
begin
set @x=minid;
ins:loop
if @x=maxid then
leave ins;
end if;
if mod(@x,2)=0 then
delete from salerecords where id=@x;
end if;
set @x=@x+1;
end loop ins;
end
$$
delimiter ;
call film_1_5(1,100);
select * from salerecords limit 10;
输出截图：
1.6创建两个事件调度器，第一个每3秒往1.2的表中插入一条记录，第二个每30秒清空
1.2的表中的所有记录。

create event event_1
on schedule every 3 second
do insert into salerecords(name,price,number) values('book',20,500); create event event_2
on schedule every 30 second
do truncate table salerecords;
set global event_scheduler=1;
select count(*) from salerecords;
set global event_scheduler=0;
输出截图：（只需要截取两个事件调度器的定义，以及在三个不同时刻查询表中记录count
（*）的结果。

）
2MySQL的触发器
2.1定义一个触发器，实现如下功能，在往1.2的表中插入记录的时候，将记录同时也插
入到一张新的表sale_backup.
create table sale_backup(id int,name varchar(20),price int,number int);
delimiter $$
create trigger film_2_1
after insert on salerecords for each row begin
set @x=0;
select max(id) into @x from salerecords;
insert into sale_backup
select * from salerecords where id=@x;
end;
$$
delimiter ;
call film_1_4('book4');
select * from sale_backup limit 10;
输出截图：（包括触发器定义，以及调用了1.4的存储过程后sale_backup的前10条记录）
3MySQL的事务控制
3.1启动一个事务往1.2的表中插入任意三条记录，提交在第二条和第三条记录中定义一
个savepoint，在插入完成后回滚到定义的savepoint。

start transaction;
insert into salerecords(name,price,number) values('book5',1,1);
select * from salerecords where name='book5';
savepoint test;
insert into salerecords(name,price,number) values('book5',2,2);
insert into salerecords(name,price,number) values('book5',3,3);
select * from salerecords where name='book5';
rollback to savepoint test;
select * from salerecords where name='book5';
commit;
select * from salerecords where name='book5';
输出截图：（包括整个操作过程）
3.2 简述读锁和写锁的区别
共享锁(S锁)又称读锁,若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S 锁.这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改.
排他锁(X锁)又称写锁.若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁.这保证了其他事务在T释放A上的锁之前不能再读取和修改A.
4MySQL的分区
4.1分别使用range分区（自行决定区间）和hash分区创建对应与1.2中表的分区。

drop table salerecords;
create table salerecords(id int,name varchar(20),price int,number int)
partition by range (id)(
partition p0 values less than (100),
partition p1 values less than (200),
partition p2 values less than maxvalue
);
drop table salerecords;
create table salerecords(id int,name varchar(20),price int,number int) partition by hash (id) partitions 4;
输出截图：
4.2插入100条记录后，确认分区中的记录分布情况。

insert into salerecords values(1,'book1',24,100);
call film_1_4('book4');
explain partitions select * from salerecords where id=4
输出截图：。