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

本文详细介绍了用于微博社交应用的数据库设计方案,包括用户表、关系表及微博内容表的创建语句,并设置主键和外键约束以确保数据一致性。此外,还提供了插入示例数据及查询特定用户好友和其好友微博内容的SQL语句。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值