many to many mysql_mysql may to many

-- register three user

insert into user(username,password,address,register_time) values('james','123','shanghai',current_timestamp);

insert into user(username,password,address,register_time) values('dudu','123','beijing',current_timestamp);

insert into user(username,password,address,register_time) values('didi','123','shengzheng',current_timestamp);

-- user didi publish three project;

insert into project(project_name,price_estimation,techs_required,project_note,publish_time,publish_user,status)

values('didiproject1','5000-10000','java,spring','this is a balabala...',current_timestamp,'didi','p');

insert into project(project_name,price_estimation,techs_required,project_note,publish_time,publish_user,status)

values('didiproject2','3000-8000','Python','this is a balabala...',current_timestamp,'didi','p');

insert into project(project_name,price_estimation,techs_required,project_note,publish_time,publish_user,status)

values('didiproject3','10000-20000','PHP','this is a balabala...',current_timestamp,'didi','p');

-- user james request three project

insert into user_project(project_id,username,accepted) values(1,'james',false);

insert into user_project(project_id,username,accepted) values(2,'james',false);

insert into user_project(project_id,username,accepted) values(3,'james',false);

-- user dudu request three project

insert into user_project(project_id,username,accepted) values(1,'dudu',false);

insert into user_project(project_id,username,accepted) values(2,'dudu',false);

insert into user_project(project_id,username,accepted) values(3,'dudu',false);

-- user didi deal project

update user_project set accepted=true where project_id=1 and username='james';

update project set status='d' and deal_time=current_timestamp where project_id=1;

-- james get latest pulished project

select * from project where status='p' order by publish_time desc;

-- james get latest deal project

select * from project where status='d' order by deal_time desc;

-- didi get my published project

select * from project where publish_user='didi' order by publish_time;

-- james get my request project

select * from project p left join user_project up on p.project_id=up.project_id

where up.username='james';

drop table if exists user_project;

drop table if exists project;

drop table if exists user;

create table user(

username varchar(45) not null primary key,

password varchar(100) not null,

register_email varchar(30) ,

system_receive_email varchar(30) ,

nick_name varchar(30),

real_name varchar(30) ,

city varchar(20) ,

address varchar(50) not null,

area_code varchar(10) ,

mobile_phone varchar(20) ,

land_line varchar(20),

company_full_name varchar(50),

msn varchar(30),

qq varchar(30),

register_time timestamp not null

);

create table project(

project_id bigint not null primary key auto_increment,

project_name varchar(30) not null,

price_estimation varchar(20) not null,

competitor_count int,

techs_required varchar(100) not null,

project_note text not null,

project_show_days int,

publish_time timestamp not null,

deal_time timestamp,

publish_user varchar(45) not null,

status char(1) ,

constraint fk_publish_user_id_1 foreign key(publish_user) references user(username)

);

create table user_project(

project_id bigint not null ,

username varchar(45) not null,

accepted boolean not null default false,

primary key (project_id,username),

constraint fk_project_id_1 foreign key(project_id) references project(project_id),

constraint fk_user_id_1 foreign key(username) references user(username)

);

下载次数: 0

分享到：

2015-08-13 18:01

浏览 221

分类:数据库

评论