-db作成

create database line;

create table users(
id int auto_increment primary key,
name varchar(255),
email varchar(255),
password varchar(255));

create table groups(
id int auto_increment primary key,
name varchar(255));

create table groups_users(
id int auto_increment primary key,
group_id int,
user_id int);

create table friends(
id int auto_increment primary key,
sender_id int,
receiver_id int,
relation int);

enum(1,友達追加 2,ブロック)

create table friend_messages (
id int auto_increment primary key,
sender_id int,
receiver_id int,
body text);

create table group_messages (
id int auto_increment primary key,
group_id int,
user_id int,
body text);

- データ挿入

insert into users values
(1, 'takagi', 'aaaa@gmail.com', 'aaaa'),
(2, 'satou', 'bbbb@gmail.com', 'bbbb'),
(3, 'hattori', 'cccc@gmail.com', 'cccc');

insert into groups values
(1, 'taka'),
(2, 'koba'),
(3, 'oni');

insert into groups_users values
(1, 1, 1),
(2, 1, 2),
(3, 2, 2),
(4, 2, 3);

insert into friends values
(1, 1, 2, 1),
(2, 2, 1, 1),
(3, 1, 3, 1),
(4, 3, 1, 2);

insert into friend_messages values
(1, 1, 2, 'hogehoge'),
(2, 2, 1, 'kkkd'),
(3, 1, 3, 'sssss');

insert into group_messages values
(1, 1, 1, 'hogehoge'),
(2, 1, 2, 'えええ'),
(3, 2, 2, 'ううう');

- ユーザ(id=1)とユーザ(id=2)の会話の履歴
select sender_id,body from friend_messages
  where (sender_id = 1 and receiver_id = 2)
    or (sender_id = 2 and receiver_id = 1);

- グループ(id=1)に所属しているメンバー一覧
select name from users
  where id
    in (select user_id from groups_users
      where group_id = 1);

- グループ(id=1)の会話履歴
select user_id,body from group_messages
  where group_id = 1;

- hogeという文字を含むメッセージ一覧
select sender_id,body from friend_messages
  where body like '%hoge%';
select user_id,body from group_messages
  where body like '%hoge%';