Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -db作成
- create database facebook;
- create table users(
- id int auto_increment primary key,
- name varchar(255),
- gender varchar(10),
- email varchar(255),
- password varchar(255));
- create table tweets(
- id int auto_increment primary key,
- body varchar(255),
- image varchar(255),
- user_id int);
- create table comments(
- id int auto_increment primary key,
- body varchar(255),
- user_id int,
- tweet_id int);
- create table friends(
- id int auto_increment primary key,
- sender_id int,
- receiver_id int,
- relation int);
- enum(1,友達追加 2,ブロック)
- - データ挿入
- insert into users values
- (1, 'takagi', man, 'aaaa@gmail.com', 'aaaa'),
- (2, 'satou', man, 'bbbb@gmail.com', 'bbbb'),
- (3, 'hattori', woman, 'cccc@gmail.com', 'cccc');
- insert into tweets values
- (1, 'taka', 'jsjs.png', 1),
- (2, 'koba', 'asis.png', 1),
- (3, 'oni', 'idj.png', 2);
- insert into comments values
- (1, 'はは', 1, 3),
- (2, 'jdjd', 2, 1),
- (3, 'きskし', 3, 1),
- (4, 'jしし', 3, 2);
- insert into friends values
- (1, 1, 2, 1),
- (2, 2, 1, 1),
- (3, 1, 3, 1),
- (4, 3, 1, 2);
- - inner joinを用いてtweetとcommentが対応し結合したテーブルを抽出する
- select * from tweets
- inner join comments on tweets.id = comments.tweet_id;
- - group byを用いてuserのうち、男何人、女何人かをグループに分けて表示する
- select gender,count(*) from users
- group by gender;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement