リレーショナルデータベース ひでシス＝めぐみ・西岡 (@hidesys) 1 

第4課の内容 • リレーショナルデータベースの操作を学ぶ – リレーショナルデータベースとはなにか – テーブルの作り方、データの挿入の仕方 2 

リレーショナルデータベースとは 3 

リレーショナルデータベース概観 ユーザーID 名前 メールアドレス 1 ひでシス [email protected] 2 西岡めぐみ [email protected] つぶやきID ユーザーID つぶやき 投稿時間 1 1 ちんぽ 2016/11/02 17:13 2 2 ああああああ！！！！ 2016/11/02 17:15 3 1 ちんちん 2016/11/02 17:20 4 1 ちん 2016/11/02 17:21 5 2 ああああああああ！！！！！！ 2016/11/02 17:25 6 1 ぽ 2016/11/02 17:34 1 0-多 ユーザーテーブル つぶやきテーブル 4 

それぞれの名前 つぶやきID ユーザーID つぶやき 投稿時間 1 1 ちんぽ 2016/11/02 17:13 2 2 ああああああ！！！！ 2016/11/02 17:15 3 1 ちんちん 2016/11/02 17:20 4 1 ちん 2016/11/02 17:21 5 2 ああああああああ！！！！！！ 2016/11/02 17:25 6 1 ぽ 2016/11/02 17:34 つぶやきテーブル テーブル名 属性（列） 順不同 組（行） 順不同 複数のテーブルをまとめて、「データベース」という 5 

リレーショナルデータベースとは • もっとも広く用いられているデータベースモデ ル • SQL（など）の問い合わせ言語を用いて、制 限・射影・結合・和・差・交わりなどの関係代 数演算（集合演算を含む）ないし関係論理演 算を行うことで結果を取り出す。 6 

リレーショナルデータベースの製品 • SQLite3 – 軽量で小規模向け – FirefoxのURL履歴やmantropy.netに使われている – ワシの修論もこれを使った • MySQL – 最も普及している • PostgreSQL • Microsoft SQL Server 7 

リレーショナルデータベースを使う 8 

今日はSQLiteを使います • データベースが1ファイルで便利 • ユーザー制御がなくて便利 • 簡単に試せる • Windowsでも使える 9 

教材をダウンロードしてください `git clone https://github.com/onemonthweb/sql-teaching.git` 10 

型について データ型 例 Ruby SQLite3 MySQL 整数 21341 Fixnum INTEGER, NUMERIC Int 浮動小数点数 値 1.3423 Float REAL Float 文字列 "あああああ" String TEXT Char, Text 日付時刻 2016/11/02 17:35:00 Date, DateTime TEXT DateTime 配列 [1, 34, 3.23, "あ ああ"] Array ない ない ハッシュ {075 => "Kyoto", 03 => "Tokyo"} Hash ない ない 真偽値 true TrueClass, FalseClass NUMERIC Boolean 11 

SQLite3の実行 • `sqlite3 twitter.sqlite3` – `sqlite3 データベースファイル名`でデータベース を開く – 教材ディレクトリの中でデータベースを作ってくだ さい 12 

SQLite3の初期設定 • `.headers on` – これでデータ表示時にヘッダが出力される – . から始まるものはSQLite特有のコマンドです • `.echo on` – これで実行されるコマンドが表示されるようになり ます 13 

SQLite3で出来合いSQL文を読み込む • `.read 1-create.sql` – SQLite3のコマンドを利用してファイルを読み込ん で実行します – `.echo on`されていないと何が実行されているの かわかりません – [TAB]キーで補完ができます 14 

1. 表を作る(CREATE) CREATE TABLE tweets ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, tweet TEXT, posted_at TEXT ); CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT ); 15 

2. データの挿入(INSERT) INSERT INTO users (name, email) VALUES ("hidesys", "[email protected]"); INSERT INTO tweets (user_id, tweet, posted_at) VALUES (1, "aaaaaa", "2016/11/02 18:00:00"); 16 

3. データを見る(SELECT) SELECT * FROM users; 17 

4. データの選択(WHERE) SELECT * FROM users WHERE id = 1; 18 

5. データの射影(SELECT) SELECT name, email FROM users; 19 

6. 結合(JOIN) SELECT users.name, tweets.tweet, tweets.posted_at FROM tweets INNER JOIN users ON users.id = tweets.user_id; 20 

7. グループ化(GROUP BY) SELECT user_id, COUNT(*) FROM tweets GROUP BY user_id; 21 

8. グループ後に条件を加える (HAVING) SELECT user_id, COUNT(*) FROM tweets GROUP BY user_id HAVING COUNT(*) > 3; 22 

9. 別名(AS) SELECT user_id, COUNT(*) AS tweet_count FROM tweets GROUP BY user_id HAVING tweet_count > 3; 23 

10. 並び替え(ORDER BY) SELECT user_id, COUNT(*) AS tweet_count FROM tweets GROUP BY user_id HAVING tweet_count > 3 ORDER BY tweet_count; SELECT * FROM tweets ORDER BY user_id; SELECT * FROM tweets ORDER BY user_id DESC; 24 

11. リストの中にある(IN) SELECT * FROM tweets WHERE user_id = 2 OR user_id = 4 OR user_id = 5; SELECT * FROM tweets WHERE user_id IN (2, 4, 5); 25 

12. 存在(EXISTS) SELECT name FROM users WHERE EXISTS ( SELECT * FROM tweets WHERE users.id = tweets.user_id ); 26 

なにやっているかわかりますか？ SELECT u.name, t2.tweet_count FROM users AS u INNER JOIN ( SELECT user_id, COUNT(*) AS tweet_count FROM tweets AS t GROUP BY user_id HAVING tweet_count >= 2 ) AS t2 ON u.id = t2.user_id ORDER BY t2.tweet_count DESC; 27