Skip to content

Instantly share code, notes, and snippets.

@tnamao
Created July 14, 2015 18:11
Embed
What would you like to do?
リレーション確認用のSQL
create table groups (
id integer not null auto_increment,
name varchar(32) not null,
created timestamp,
updated timestamp,
primary key (id)
);
create table sample_users (
id integer not null auto_increment,
group_id integer not null,
name varchar(32) not null,
created timestamp,
updated timestamp,
primary key (id),
foreign key (group_id) references groups (id)
);
create table mobile_phones (
id integer not null auto_increment,
sample_user_id integer not null,
phone_number varchar(11) not null,
created timestamp,
updated timestamp,
primary key (id),
foreign key (sample_user_id) references sample_users (id)
);
create table job_skills (
id integer not null auto_increment,
name varchar(32) not null,
created timestamp,
updated timestamp,
primary key (id)
);
create table user_job_skills (
id integer not null auto_increment,
sample_user_id integer not null,
job_skill_id integer not null,
created timestamp,
updated timestamp,
primary key (id),
foreign key (sample_user_id) references sample_users (id),
foreign key (job_skill_id) references job_skills (id)
);
insert into groups (name, created, updated) values ('groupA', current_timestamp, current_timestamp);
insert into groups (name, created, updated) values ('groupB', current_timestamp, current_timestamp);
insert into sample_users (group_id, name, created, updated) values
((select id from groups where name = 'groupA'), 'user-a', current_timestamp, current_timestamp)
,((select id from groups where name = 'groupA'), 'user-b', current_timestamp, current_timestamp)
,((select id from groups where name = 'groupA'), 'user-c', current_timestamp, current_timestamp)
,((select id from groups where name = 'groupB'), 'user-d', current_timestamp, current_timestamp)
,((select id from groups where name = 'groupB'), 'user-e', current_timestamp, current_timestamp)
,((select id from groups where name = 'groupB'), 'user-f', current_timestamp, current_timestamp)
;
insert into mobile_phones (sample_user_id, phone_number, created, updated) values
((select id from sample_users where name = 'user-a'), 'number-a', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-a'), 'number-a1', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-b'), 'number-b', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-b'), 'number-b2', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-c'), 'number-c', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-c'), 'number-c2', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-d'), 'number-d', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-d'), 'number-d2', current_timestamp, current_timestamp)
;
insert into job_skills (name, created, updated) values
('skill-1', current_timestamp, current_timestamp)
,('skill-2', current_timestamp, current_timestamp)
,('skill-3', current_timestamp, current_timestamp)
,('skill-4', current_timestamp, current_timestamp)
,('skill-5', current_timestamp, current_timestamp)
,('skill-6', current_timestamp, current_timestamp)
;
insert into user_job_skills (sample_user_id, job_skill_id, created, updated) values
((select id from sample_users where name = 'user-a'), (select id from job_skills where name = 'skill-1'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-a'), (select id from job_skills where name = 'skill-2'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-a'), (select id from job_skills where name = 'skill-3'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-b'), (select id from job_skills where name = 'skill-4'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-b'), (select id from job_skills where name = 'skill-5'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-b'), (select id from job_skills where name = 'skill-6'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-c'), (select id from job_skills where name = 'skill-1'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-c'), (select id from job_skills where name = 'skill-5'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-c'), (select id from job_skills where name = 'skill-6'), current_timestamp, current_timestamp)
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment