-- 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)
);
分享到:
相关推荐
other users put MySQL to work in their environments. We wanted a book that wasn’t just a SQL primer. We wanted a book with a title that didn’t start or end in some arbitrary time frame (“...in ...
And many thanks to the artists who provided artwork for the art gallery example. They are Elliott Easterling, Laura Blair, Stuart Sheldon, and Todd Brown. Errata and solutions to the labs can be ...
cation developer but also on the rigorous demands of the MySQL administrator, who needs to keep the system up and running no matter what the programmers or users may throw at the server. Having said ...
DCDIAG existed prior to Windows Server 2003 SP1, but many of the commands that are available today were first introduced in Windows Server 2003 SP1. You can access the Domain Controller Diagnostic ...
There may be many reasons that brought you here, it could be because you heard all about Hadoop and what it can do to crunch petabytes of data in a reasonable amount of time. While reading into Hadoop...
JavaScript, MySQL, and jQuery to implement Web 2.0 applications, are looking for a step-by-step, example-driven AJAX tutorial, want to learn advanced AJAX coding patterns and techniques, and want to...
Many operating systems already include a phpMyAdmin package and will automatically keep it updated, however these versions are sometimes slightly outdated and therefore may be missing the latest ...
files sub-divided into sections to support both SQLite and MySQL database implementations. The "speedy" folder relates to Chapter 27 and contains code relevant to the binding of fast functions ...
The examples use core methods provided in the PHP language. PHP includes many additional methods to accomplish similar tasks. The reader may, and should, research additional ways of improving ...
SQL Assistant equips database developers and DBAs with the productivity tools they need to speed up the database development process, improve code quality and accuracy. SQL Assistant can boost your ...
As many may want to put it, the Google chromecast is a tiny but magical device offering so much. Am sure it is in your interest to find out what it is it does exactly and I should assure you will get ...
Windows 10 becomes a gateway by which many can experience hardware and Internet of Things development who may never have had the opportunity otherwise. However, even savvy Windows programmers ...
You will need to know the basics of PHP and MySQL programming, but no experience of programming Drupal is required, although you will be expected to be familiar with the basic operation of Drupal.
You may be able to send headers and cookies after you've already sent output ; through print or echo. You also may see performance benefits if your server is ; emitting less packets due to buffered ...
It is not necessary to modify this path, but you may wish to change it if: * your site runs multiple Drupal installations from a single codebase (modify the file system path of each installation ...
When you use the sqlite3 module, the database connection details are slightly different from the ones you would use to connect to other database systems like MySQL, Post‐ greSQL, and Oracle....
The benefit of a column maker is that it can help you to format your text/code, or in some cases to make it easier to read in complex nested logic. Quick Open UltraEdit and UEStudio provide multiple ...
Introduction to MySQL 243 Working with MySQL 246 Accessing a Database from a Python Script 254 Identify the Elements of the Table That Stores Registration Details 256 Identify the Steps for ...
The number of topics to grasp and challenges to overcome may seem daunting. However, that is where the usefulness and purpose of this book comes in. This volume of the Best of TIM Review book series ...