第4課 リレーショナルデータベース

http://onemonthweb.hatenablog.com/

Transcript

1. リレーショナルデータベース ひでシス＝めぐみ・西岡 (@hidesys) 1
2. 第4課の内容 • リレーショナルデータベースの操作を学ぶ – リレーショナルデータベースとはなにか – テーブルの作り方、データの挿入の仕方 2
3. リレーショナルデータベースとは 3
4. リレーショナルデータベース概観 ユーザーID 名前 メールアドレス 1 ひでシス info@hidesys.net 2 西岡めぐみ info@megumi.online つぶやき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
5. それぞれの名前 つぶやき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
6. リレーショナルデータベースとは • もっとも広く用いられているデータベースモデ ル • SQL（など）の問い合わせ言語を用いて、制 限・射影・結合・和・差・交わりなどの関係代 数演算（集合演算を含む）ないし関係論理演 算を行うことで結果を取り出す。 6
7. リレーショナルデータベースの製品 • SQLite3 – 軽量で小規模向け – FirefoxのURL履歴やmantropy.netに使われている – ワシの修論もこれを使った • MySQL – 最も普及している • PostgreSQL • Microsoft SQL Server 7
8. リレーショナルデータベースを使う 8
9. 今日はSQLiteを使います • データベースが1ファイルで便利 • ユーザー制御がなくて便利 • 簡単に試せる • Windowsでも使える 9
10. 教材をダウンロードしてください `git clone https://github.com/onemonthweb/sql-teaching.git` 10
11. 型について データ型 例 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
12. SQLite3の実行 • `sqlite3 twitter.sqlite3` – `sqlite3 データベースファイル名`でデータベース を開く – 教材ディレクトリの中でデータベースを作ってくだ さい 12
13. SQLite3の初期設定 • `.headers on` – これでデータ表示時にヘッダが出力される – . から始まるものはSQLite特有のコマンドです • `.echo on` – これで実行されるコマンドが表示されるようになり ます 13
14. SQLite3で出来合いSQL文を読み込む • `.read 1-create.sql` – SQLite3のコマンドを利用してファイルを読み込ん で実行します – `.echo on`されていないと何が実行されているの かわかりません – [TAB]キーで補完ができます 14
15. 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
16. 2. データの挿入(INSERT) INSERT INTO users (name, email) VALUES ("hidesys", "info@hidesys.net"); INSERT INTO tweets (user_id, tweet, posted_at) VALUES (1, "aaaaaa", "2016/11/02 18:00:00"); 16
17. 3. データを見る(SELECT) SELECT * FROM users; 17
18. 4. データの選択(WHERE) SELECT * FROM users WHERE id = 1; 18
19. 5. データの射影(SELECT) SELECT name, email FROM users; 19
20. 6. 結合(JOIN) SELECT users.name, tweets.tweet, tweets.posted_at FROM tweets INNER JOIN users ON users.id = tweets.user_id; 20
21. 7. グループ化(GROUP BY) SELECT user_id, COUNT(*) FROM tweets GROUP BY user_id; 21
22. 8. グループ後に条件を加える (HAVING) SELECT user_id, COUNT(*) FROM tweets GROUP BY user_id HAVING COUNT(*) > 3; 22
23. 9. 別名(AS) SELECT user_id, COUNT(*) AS tweet_count FROM tweets GROUP BY user_id HAVING tweet_count > 3; 23
24. 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
25. 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
26. 12. 存在(EXISTS) SELECT name FROM users WHERE EXISTS ( SELECT * FROM tweets WHERE users.id = tweets.user_id ); 26
27. なにやっているかわかりますか？ 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