mySql常用实例

drop table if exists link_tb;
commit;
create table if not exists link_tb(
id int unsigned not null auto_increment,
name varchar(20),
link varchar(255),
detail varchar(150),
primary key(id)
);
commit;
drop table if exists admin_tb;
commit;
create table if not exists admin_tb(
id int unsigned not null auto_increment,
loginId varchar(20) not null,
name varchar(20) not null,
password varchar(20) not null,
sex varchar(2) default '男',
age int default 0,
education varchar(12) default '暂未填写',
graduate_college varchar(100)  default '暂未填写',
phone varchar(11)  default '暂未填写',
email varchar(50)  default '暂未填写',
img varchar(255)  default '暂未填写',
job varchar(20)  default '暂未填写',
native_place varchar(255)  default '暂未填写',
living_place varchar(255)  default '暂未填写',
hobbies varchar(100)  default '暂未填写',
mood varchar(100)  default '暂未填写',
detail varchar(250)  default '暂未填写',
primary key(id)
);
commit;
delete from link_tb;
commit;
insert into link_tb(name,link,detail)values('mainpage','./mainpage.jsp','主页');
insert into link_tb(name,link,detail)values('sourcepage','./sourcepage.jsp','个人资料');
insert into link_tb(name,link,detail)values('growwaypage','./growwaypage.jsp','成长历程');
insert into link_tb(name,link,detail)values('workpage','./workpage.jsp','个人作品');
insert into link_tb(name,link,detail)values('homepage','./homepage.jsp','家庭状况');
insert into link_tb(name,link,detail)values('hometownpage','./hometownpage.jsp','家乡展况');
insert into link_tb(name,link,detail)values('person_showPic1','./img/person_pic/person_showPic1.jpg','个人作品1');
insert into link_tb(name,link,detail)values('person_showPic2','./img/person_pic/person_showPic2.jpg','个人作品2');
insert into link_tb(name,link,detail)values('person_showPic3','./img/person_pic/person_showPic3.jpg','个人作品3');
insert into link_tb(name,link,detail)values('person_showPic4','./img/person_pic/person_showPic4.jpg','个人作品4');
insert into link_tb(name,link,detail)values('person_showPic5','./img/person_pic/person_showPic5.jpg','个人作品5');
commit;
delete from admin_tb;
commit;
insert into admin_tb(loginId,name,password,phone,detail)values('yaoweinan','姚伟男','yaoweiwei','13659236754','大家好，我是姚伟男');
commit;
drop table if exists log_tb;
commit;
create table if not exists log_tb(
id int unsigned not null auto_increment,
note varchar(100),
dte datetime,
f_id int unsigned,
isPress int not null,
primary key(id)
);
commit;
insert into log_tb(note,dte,isPress)value('主人上传了一个作品','2010-9-8',0);
insert into log_tb(note,dte,isPress)value('主人上传了一个作品','2010-2-5',0);
insert into log_tb(note,dte,isPress)value('主人上传了一个作品','2010-3-9',0);
insert into log_tb(note,dte,isPress)value('主人上传了一个作品','2010-5-2',0);
insert into log_tb(note,dte,isPress,f_id)value('主人上传了一个作品','2010-9-8',1,1);
commit;
drop table if exists works_tb;
commit;
create table works_tb(
id int unsigned not null auto_increment,
link_id int unsigned not null,
caption varchar(50) not null,
detail varchar(500),
primary key(id),
foreign key(link_id) references  mysite.link_tb(id)
);
insert into works_tb values(1,2,'测试','测试中..');
commit;
drop table  if exists notes_tb;
commit;
create table notes_tb(
id int unsigned not null auto_increment,
user_id varchar(20) not null,
caption varchar(50) not null,
words text not null,
dte datetime,
primary key(id),
foreign key(user_id) references mysite.admin_tb(loginId)
);
commit;
drop procedure if exists lstworks;
commit;
create procedure lstworks()
--列出作品存储过程
begin
select name,link,w.detail,caption,w.id from link_tb as l,works_tb as w where w.link_id=l.id order by w.id desc limit 0,15;
end;
commit;

drop procedure if exists findworks;
commit;

create procedure findworks(in_id int)
--查找作品存储过程
begin
select name,link,w.detail,caption,w.id from link_tb as l,works_tb as w where w.link_id=l.id and w.id=in_id;
end;
commit;
drop procedure if exists addNote;
commit;
--添加日志存储过程
CREATE  PROCEDURE addNote(in_note varchar(100))
insert into log_tb(note,dte,isPress)value(in_note,now(),0);

drop procedure if exists addworks;
commit;
create procedure addworks(in_name varchar(20),in_link varchar(255),in_detail varchar(150),in_caption varchar(50),in_detail1 varchar(500))
--添加作品存储过程
begin
call addNote('主人上传了一个新作品');
insert into link_tb(name,link,detail)values(in_name,in_link,in_detail);
insert into works_tb(link_id,caption,detail)values((select id from link_tb where name=in_name),in_caption,in_detail1);

end;
commit;
drop procedure if exists delworks;
commit;

create procedure delworks(in_idd int)
--删除作品存储过程
begin
select link_id into in_idd from works_tb where id=in_idd;
delete from works_tb where link_id=in_idd;
delete from link_tb  where id=in_idd;
call addNote('主人删除了一个作品');
end;
commit;
drop procedure if exists updateMood;
commit;

create procedure updateMood(in_idd varchar(20),in_mood varchar(100))
--更新心情存储过程
begin
update admin_tb set mood=in_mood where loginId=in_idd;
call addNote('主人更新了心情');
end;
commit;
drop procedure if exists getPersonalInf;
commit;

create procedure getPersonalInf(in_idd varchar(20))
--查看主人信息存储过程
begin
select * from admin_tb where loginId=in_idd;
end;
commit;