--create table sql
create table tb_user
(
user_id INTEGER not null,
user_name VARCHAR2(20 CHAR)
)
--add primary key for tb_user
alter table tb_user
add constraint pk_tb_user primary key (user_id);
create table tb_relation (
user_id INTEGER,
friend_id INTEGER
)
--add primary key for tb_relation
alter table tb_relation
add constraint pk_tb_relation primary key (user_id,friend_id);
--add foreign key for tb_relation,user_id is 'on delete cascade'
alter table tb_relation
add constraint fk_tb_relation_user_id foreign key (user_id)
references tb_user (user_id) on delete cascade;
--add foreign key for tb_relation,friend_id is 'on delete set cascade'
alter table tb_relation
add constraint fk_tb_relation_friend_id foreign key (friend_id)
references tb_user (user_id) on delete cascade;
create table tb_weibo (
weibo_id INTEGER not null,
weibo_content varchar2(1000 char),
user_id INTEGER
)
--add primary key for tb_weibo
alter table tb_weibo
add constraint pk_tb_weibo primary key (weibo_id);
--add foreign key for tb_weibo,friend_id is 'on delete cascade'
alter table tb_weibo
add constraint fk_tb_weibo_user_id foreign key (user_id)
references tb_user (user_id) on delete cascade;
----insert data into tb_user
insert into tb_user(user_id,user_name) values(1,'jason');
insert into tb_user(user_id,user_name) values(2,'jack');
insert into tb_user(user_id,user_name) values(3,'mike');
insert into tb_user(user_id,user_name) values(4,'lucy');
insert into tb_user(user_id,user_name) values(5,'lily);
insert into tb_user(user_id,user_name) values(6,'mary');
insert into tb_user(user_id,user_name) values(7,'max');
insert into tb_user(user_id,user_name) values(8,'jobs');
insert into tb_user(user_id,user_name) values(9,'andy');
insert into tb_user(user_id,user_name) values(10,'jim');
insert into tb_user(user_id,user_name) values(11,'lilei');
insert into tb_user(user_id,user_name) values(12,'jelly');
insert into tb_user(user_id,user_name) values(13,'hanson');
insert into tb_user(user_id,user_name) values(14,'ladygaga');
insert into tb_user(user_id,user_name) values(15,'bill');
----insert data into tb_relation
insert into tb_relation(user_id,friend_id) values(1,2);
insert into tb_relation(user_id,friend_id) values(1,3);
insert into tb_relation(user_id,friend_id) values(1,4);
insert into tb_relation(user_id,friend_id) values(1,5);
insert into tb_relation(user_id,friend_id) values(1,6);
insert into tb_relation(user_id,friend_id) values(1,7);
insert into tb_relation(user_id,friend_id) values(1,8);
insert into tb_relation(user_id,friend_id) values(1,9);
insert into tb_relation(user_id,friend_id) values(1,10);
insert into tb_relation(user_id,friend_id) values(1,11);
insert into tb_relation(user_id,friend_id) values(1,12);
insert into tb_relation(user_id,friend_id) values(1,13);
insert into tb_relation(user_id,friend_id) values(1,14);
insert into tb_relation(user_id,friend_id) values(1,15);
insert into tb_relation(user_id,friend_id) values(2,1);
insert into tb_relation(user_id,friend_id) values(2,3);
----insert data into tb_weibo
insert into tb_weibo(weibo_id,weibo_content,user_id) values(1,'jason_hi',1);
insert into tb_weibo(weibo_id,weibo_content,user_id) values(2,'jason_hello',1);
insert into tb_weibo(weibo_id,weibo_content,user_id) values(3,'jason_how are you',1);
insert into tb_weibo(weibo_id,weibo_content,user_id) values(4,'jack_hi',2);
insert into tb_weibo(weibo_id,weibo_content,user_id) values(5,'jack_hello',2);
insert into tb_weibo(weibo_id,weibo_content,user_id) values(6,'jack_how are you',2);
insert into tb_weibo(weibo_id,weibo_content,user_id) values(7,'mike_hi',3);
insert into tb_weibo(weibo_id,weibo_content,user_id) values(8,'mike_hello',3);
insert into tb_weibo(weibo_id,weibo_content,user_id) values(9,'mike_how are you',3);
--select data from tables;
select * from tb_user friend;
select * from tb_relation relation;
select * from tb_weibo weibo;
--select all friend of jason
select r.friend_id from tb_user u join tb_relation r on u.user_id=r.user_id and u.user_name='jason';
--select all friends'weibo of jason
select w.weibo_content from tb_user u join tb_weibo w on u.user_id=w.user_id and u.user_id in(select r.friend_id from tb_user u join tb_relation r on u.user_id=r.user_id and u.user_name='jack');
--you need to delete all constraint before delete tables
alter table tb_weibo drop constraints fk_tb_weibo_user_id;
alter table tb_relation drop constraints fk_tb_relation_user_id;
alter table tb_relation drop constraints fk_tb_relation_friend_id;
drop table tb_user;
drop table tb_relation;
drop table tb_weibo;