SHARE
TWEET

Untitled

a guest Jun 15th, 2017 495 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -db作成
  2.  
  3. create database line;
  4.  
  5. create table users(
  6. id int auto_increment primary key,
  7. name varchar(255),
  8. email varchar(255),
  9. password varchar(255));
  10.  
  11. create table groups(
  12. id int auto_increment primary key,
  13. name varchar(255));
  14.  
  15. create table groups_users(
  16. id int auto_increment primary key,
  17. group_id int,
  18. user_id int);
  19.  
  20. create table friends(
  21. id int auto_increment primary key,
  22. sender_id int,
  23. receiver_id int,
  24. relation int);
  25.  
  26. enum(1,友達追加 2,ブロック)
  27.    
  28. create table friend_messages (
  29. id int auto_increment primary key,
  30. sender_id int,
  31. receiver_id int,
  32. body text);
  33.  
  34. create table group_messages (
  35. id int auto_increment primary key,
  36. group_id int,
  37. user_id int,
  38. body text);
  39.  
  40. - データ挿入
  41.  
  42. insert into users values
  43. (1, 'takagi', 'aaaa@gmail.com', 'aaaa'),
  44. (2, 'satou', 'bbbb@gmail.com', 'bbbb'),
  45. (3, 'hattori', 'cccc@gmail.com', 'cccc');
  46.  
  47. insert into groups values
  48. (1, 'taka'),
  49. (2, 'koba'),
  50. (3, 'oni');
  51.  
  52. insert into groups_users values
  53. (1, 1, 1),
  54. (2, 1, 2),
  55. (3, 2, 2),
  56. (4, 2, 3);
  57.  
  58. insert into friends values
  59. (1, 1, 2, 1),
  60. (2, 2, 1, 1),
  61. (3, 1, 3, 1),
  62. (4, 3, 1, 2);
  63.  
  64. insert into friend_messages values
  65. (1, 1, 2, 'hogehoge'),
  66. (2, 2, 1, 'kkkd'),
  67. (3, 1, 3, 'sssss');
  68.  
  69. insert into group_messages values
  70. (1, 1, 1, 'hogehoge'),
  71. (2, 1, 2, 'えええ'),
  72. (3, 2, 2, 'ううう');
  73.  
  74. - ユーザ(id=1)とユーザ(id=2)の会話の履歴
  75. select sender_id,body from friend_messages
  76.   where (sender_id = 1 and receiver_id = 2)
  77.     or (sender_id = 2 and receiver_id = 1);
  78.  
  79. - グループ(id=1)に所属しているメンバー一覧
  80. select name from users
  81.   where id
  82.     in (select user_id from groups_users
  83.       where group_id = 1);
  84.  
  85. - グループ(id=1)の会話履歴
  86. select user_id,body from group_messages
  87.   where group_id = 1;
  88.  
  89. - hogeという文字を含むメッセージ一覧
  90. select sender_id,body from friend_messages
  91.   where body like '%hoge%';
  92. select user_id,body from group_messages
  93.   where body like '%hoge%';
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top