查看微薄好友的微薄内容，表设计和查询sql

--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;