SHARE
TWEET

Untitled

a guest Jun 15th, 2017 496 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -db作成
  2.  
  3. create database facebook;
  4.  
  5. create table users(
  6. id int auto_increment primary key,
  7. name varchar(255),
  8. gender varchar(10),
  9. email varchar(255),
  10. password varchar(255));
  11.  
  12. create table tweets(
  13. id int auto_increment primary key,
  14. body varchar(255),
  15. image varchar(255),
  16. user_id int);
  17.  
  18. create table comments(
  19. id int auto_increment primary key,
  20. body varchar(255),
  21. user_id int,
  22. tweet_id int);
  23.  
  24. create table friends(
  25. id int auto_increment primary key,
  26. sender_id int,
  27. receiver_id int,
  28. relation int);
  29.  
  30. enum(1,友達追加 2,ブロック)
  31.    
  32. - データ挿入
  33.  
  34. insert into users values
  35. (1, 'takagi', man, 'aaaa@gmail.com', 'aaaa'),
  36. (2, 'satou', man, 'bbbb@gmail.com', 'bbbb'),
  37. (3, 'hattori', woman, 'cccc@gmail.com', 'cccc');
  38.  
  39. insert into tweets values
  40. (1, 'taka', 'jsjs.png', 1),
  41. (2, 'koba', 'asis.png', 1),
  42. (3, 'oni', 'idj.png', 2);
  43.  
  44. insert into comments values
  45. (1, 'はは', 1, 3),
  46. (2, 'jdjd', 2, 1),
  47. (3, 'きskし', 3, 1),
  48. (4, 'jしし', 3, 2);
  49.  
  50. insert into friends values
  51. (1, 1, 2, 1),
  52. (2, 2, 1, 1),
  53. (3, 1, 3, 1),
  54. (4, 3, 1, 2);
  55.  
  56. - inner joinを用いてtweetとcommentが対応し結合したテーブルを抽出する
  57. select * from tweets
  58.   inner join comments on tweets.id = comments.tweet_id;
  59.  
  60. - group byを用いてuserのうち、男何人、女何人かをグループに分けて表示する
  61. select gender,count(*) from users
  62.   group by gender;
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