データベース|SQL

データベースの講義スライドです。

Transcript

1. データベース SQL（CREATE、INSERT、DELETE、UPDATEなど） 奥 健太
2. データベースの作成から削除まで データベースの作成 テーブルの作成 データの登録 データの更新 PostgreSQLにログイン データベースの削除 テーブルの削除 データの削除 PostgreSQLからログアウト 2
3. PostgreSQLにログイン $ sudo -u postgres psql [sudo] xxx のパスワード: Password for user postgres: postgres=# 端末から下記コマンドを実行し、PostgreSQLにログイン -u: PostgreSQLにログインするユーザ名を指定するオプション <- sudoコマンドを実行するためのパスワード <- PostgreSQLにログインするためのパスワード <- ログインすると、PostgreSQLのプロンプトが表示される 3
4. データベースの一覧表示 postgres=# ¥l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows) 4
5. データベースの作成 postgres=# CREATE DATABASE ml_latest_small ENCODING ‘UTF8’; postgres=# CREATE DATABASE name ENCODING encoding; name: 作成するデータベース名 encoding: 文字セット符号化方式 PostgreSQL 14.5文書 CREATE DATABASE: https://www.postgresql.jp/document/14/html/sql-createdatabase.html 5
6. データベースへの接続 postgres=# ¥c ml_latest_small You are now connected to database "ml_latest_small" as user "postgres". ml_latest_small=# <- プロンプトが接続データベース名に切り替わる postgres=# ¥c name name: 接続先データベース名 ※以降のスライドではプロンプトは省略 6
7. テーブルの作成 CREATE TABLE table_name ( column_name data_type column_constraint, column_name data_type column_constraint, ..., column_name data_type column_constraint ); table_name: 作成するテーブル名 column_name: カラム名 data_type: データ型 column_constraint: 制約 PostgreSQL 14.5文書 CREATE TABLE: https://www.postgresql.jp/document/14/html/sql-createtable.html CREATE TABLE users ( user_id INT, user_name TEXT NOT NULL, PRIMARY KEY(user_id)); 7
8. テーブルの一覧表示 ml_latest_small=# ¥d List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | users | table | postgres (1 rows) 8
9. データの登録 INSERT INTO table_name (column_name, column_name, ..., column_name) VALUES (expression, expression, ..., expression); table_name: テーブル名 column_name: カラム名 expression: 式または値 PostgreSQL 14.5文書 INSERT: https://www.postgresql.jp/document/14/html/sql-insert.html INSERT INTO users (user_id, user_name) VALUES (1, 'Alice'); INSERT INTO users (user_id, user_name) VALUES (2, 'Bruno'); INSERT INTO users (user_id, user_name) VALUES (3, 'Chiara'); 9
10. 登録されている全データの確認 SELECT * FROM table_name; table_name: テーブル名 PostgreSQL 14.5文書 SELECT: https://www.postgresql.jp/document/14/html/sql-select.html SELECT * FROM users; ※SELECT文は次回以降の講義で詳細に学ぶ 10
11. データの更新 UPDATE table_name SET column_name = expression, column_name = expression, ..., column_name = expression WHERE condition; table_name: テーブル名 column_name: カラム名 expression: 式または値 condition: 問合せ条件 PostgreSQL 14.5文書 UPDATE: https://www.postgresql.jp/document/14/html/sql-update.html UPDATE users SET user_name = 'Dhruv' WHERE user_id = 1; <- WHERE句を忘れると、すべてのデータが更新されるので注意 11
12. データの削除 DELETE FROM table_name WHERE condition; table_name: テーブル名 condition: 問合せ条件 PostgreSQL 14.5文書 DELETE: https://www.postgresql.jp/document/14/html/sql-delete.html DELETE FROM users WHERE user_id = 1; <- WHERE句を忘れると、すべてのデータが削除されるので注意 12
13. テーブルの削除 DROP TABLE table_name; table_name: テーブル名 PostgreSQL 14.5文書 DROP TABLE: https://www.postgresql.jp/document/14/html/sql-droptable.html DROP TABLE users; ※警告や確認メッセージなしに削除されるため、実行するときは慎重に 13
14. データベースの削除 DROP DATABASE name; name: データベース名 PostgreSQL 14.5文書 DROP DATABASE: https://www.postgresql.jp/document/14/html/sql-dropdatabase.html ml_latest_small=# ¥c postgres postgres=# DROP DATABASE ml_latest_small; ※接続中のデータベースは削除できないため、他のデータベースに接続してから実行 ※警告や確認メッセージなしに削除されるため、実行するときは慎重に 14
15. PostgreSQLからのログアウト postgres=# ¥q $ <- ログアウトすると、端末のプロンプトに戻る 15
16. データベース SQL（デモ用データベースの作成） 奥 健太
17. データベースの作成 postgres=# CREATE DATABASE ml_latest_small ENCODING ‘UTF8’; postgres=# ¥c ml_latest_small ml_latest_small=# 17
18. テーブルの作成 | users CREATE TABLE users ( user_id INT, user_name TEXT NOT NULL, PRIMARY KEY(user_id) ); 属性名 カラム名 データ型 制約 ユーザID user_id INT PRIMARY KEY ユーザ名 user_name TEXT NOT NULL users ユーザ (ユーザID, ユーザ名) 18
19. テーブルの作成 | movies CREATE TABLE movies ( movie_id INT, title TEXT NOT NULL, year INT, PRIMARY KEY(movie_id) ); 属性名 カラム名 データ型 制約 映画ID movie_id INT PRIMARY KEY タイトル title TEXT NOT NULL 公開年 year INT movies 映画 (映画ID, タイトル, 公開年) 19
20. テーブルの作成 | genres CREATE TABLE genres ( genre_id INT, genre_name TEXT NOT NULL, PRIMARY KEY(genre_id) ); 属性名 カラム名 データ型 制約 ジャンルID genre_id INT PRIMARY KEY ジャンル名 genre_name TEXT NOT NULL genres ジャンル (ジャンルID, ジャンル名) 20
21. テーブルの作成 | links CREATE TABLE links ( movie_id INT, imdb_id INT, tmdb_id INT, PRIMARY KEY(movie_id), FOREIGN KEY(movie_id) REFERENCES movies(movie_id)); 属性名 カラム名 データ型 制約 映画ID movie_id INT PRIMARY KEY, REFERENCES movies(movie_id) IMDb ID imdb_id INT TMDb ID tmdb_id INT links リンク (映画ID, IMDb ID, TMDb ID) 21
22. テーブルの作成 | movies_genres CREATE TABLE movies_genres ( movie_id INT, genre_id INT, PRIMARY KEY(movie_id, genre_id), FOREIGN KEY(movie_id) REFERENCES movies(movie_id), FOREIGN KEY(genre_id) REFERENCES genres(genre_id)); 属性名 カラム名 データ型 制約 映画ID movie_id INT PRIMARY KEY, REFERENCES movies(movie_id) ジャンルID genre_id INT PRIMARY KEY, REFERENCES genres(genre_id) movies_genres 映画-ジャンル (映画ID, ジャンルID) 22
23. テーブルの作成 | ratings CREATE TABLE ratings ( user_id INT, movie_id INT, rating NUMERIC, rated_at TIMESTAMP, PRIMARY KEY(user_id, movie_id), FOREIGN KEY(user_id) REFERENCES users(user_id), FOREIGN KEY(movie_id) REFERENCES movies(movie_id)); 属性名 カラム名 データ型 制約 ユーザID user_id INT PRIMARY KEY, REFERENCES users(user_id) 映画ID movie_id INT PRIMARY KEY, REFERENCES movies(movie_id) 評価値 rating NUMERIC 評価日時 rated_at TIMESTAMP ratings 評価値 (ユーザID, 映画ID, 評価値, 評価日時) 23
24. テーブルの作成 | tags CREATE TABLE tags ( user_id INT, movie_id INT, tag TEXT, tagged_at TIMESTAMP, PRIMARY KEY(user_id, movie_id, tag), FOREIGN KEY(user_id) REFERENCES users(user_id), FOREIGN KEY(movie_id) REFERENCES movies(movie_id)); 属性名 カラム名 データ型 制約 ユーザID user_id INT PRIMARY KEY, REFERENCES users(user_id) 映画ID movie_id INT PRIMARY KEY, REFERENCES movies(movie_id) タグ tag TEXT PRIMARY KEY タグ付け日時 tagged_at TIMESTAMP tags タグ (ユーザID, 映画ID, タグ, タグ付け日時) 24
25. テーブルの一覧表示 ml_latest_small=# ¥d List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | genres | table | postgres public | links | table | postgres public | movies | table | postgres public | movies_genres | table | postgres public | ratings | table | postgres public | tags | table | postgres public | users | table | postgres (7 rows) 25
26. カラムの一覧表示 ml_latest_small=# ¥d tags Table "public.tags" Column | Type | Collation | Nullable | Default -----------+-----------------------------+-----------+----------+--------- user_id | integer | | not null | movie_id | integer | | not null | tag | text | | not null | tagged_at | timestamp without time zone | | | Indexes: "tags_pkey" PRIMARY KEY, btree (user_id, movie_id, tag) Foreign-key constraints: "tags_movie_id_fkey" FOREIGN KEY (movie_id) REFERENCES movies(movie_id) "tags_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(user_id) 26
27. 参照整合性制約とは movie_id title year 1 Toy Story 1995 2 Jumanji 1995 3 Grumpier Old Men 1995 4 Waiting to Exhale 1995 5 Father of the Bride Part II 1995 6 Heat 1995 movies user_id movie_id rating rated_at 1 1 4 2000-07-31 3:45:03 1 3 4 2000-07-31 3:20:47 1 6 4 2000-07-31 3:37:04 5 1 4 1996-11-08 15:36:02 6 2 4 1996-10-17 20:58:42 6 3 5 1996-10-17 21:11:36 user_id user_name 1 user001 2 user002 3 user003 4 user004 5 user005 6 user006 ratings users 参照 参照 FOREIGN KEY(user_id) REFERENCES users(user_id) FOREIGN KEY(movie_id) REFERENCES movies(movie_id)) ❏ 参照先のカラムにない値を設定しようとするとエラーになる 外部キー 27
28. MovieLens Datasetsの取込み 28
29. MovieLens Datasets F. Maxwell Harper and Joseph A. Konstan. 2015. The MovieLens Datasets: History and Context. ACM Transactions on Interactive Intelligent Systems (TiiS) 5, 4: 19:1–19:19. https://doi.org/10.1145/2827872 GroupLens Researchが映画推薦システムであるMovieLens*上で収集し たデータを研究・教育用データセットとして公開 *MovieLens: https://movielens.org/ MovieLens | GroupLens <https://grouplens.org/datasets/movielens/> ※運営者に許諾を得たうえで教材として利用 29
30. MovieLens Datasetsの取込み（1/4） MovieLens Datasetsサイト*から ml-latest-small.zip をダウンロード 30 *MovieLens: https://movielens.org/
31. MovieLens Datasetsの取込み（2/4） $ cd ~/Downloads/ $ ls ml-latest-small.zip $ unzip ml-latest-small.zip Archive: ml-latest-small.zip creating: ml-latest-small/ inflating: ml-latest-small/links.csv inflating: ml-latest-small/tags.csv inflating: ml-latest-small/ratings.csv inflating: ml-latest-small/README.txt inflating: ml-latest-small/movies.csv $ rm -f ml-latest-small.zip $ ls ml-latest-small/ README.txt links.csv movies.csv ratings.csv tags.csv ダウンロードした ml-latest-small.zip を下記のように展開 31 ※このスライドは受講生向けの内容です。
32. MovieLens Datasetsの取込み（3/4） $ ls ml-latest-small ml2db.py 授業で配布した ml2db.py を ml-latest-small/ と同じディレクトリに置く $ source ~/venv/rsl-base/bin/activate (rsl-base) $ python ml2db.py (rsl-base) $ deactivate $ cd ml-latest-small/out/ $ ls genres.csv movies.csv ratings.csv users.csv links.csv movies_genres.csv tags.csv rsl-base 仮想環境をアクティベートし、ml2db.py を実行する 32 ※このスライドは受講生向けの内容です。
33. MovieLens Datasetsの取込み（4/4） $ psql ml_latest_small -U postgres -c "¥copy users from 'users.csv' with delimiter E'¥t' csv header encoding 'UTF8'" COPY 610 $ psql ml_latest_small -U postgres -c "¥copy movies from 'movies.csv' with delimiter E'¥t' csv header encoding 'UTF8'" COPY 9742 $ psql ml_latest_small -U postgres -c "¥copy genres from 'genres.csv' with delimiter E'¥t' csv header encoding 'UTF8'" COPY 20 $ psql ml_latest_small -U postgres -c "¥copy links from 'links.csv' with delimiter E'¥t' csv header encoding 'UTF8'" COPY 9742 $ psql ml_latest_small -U postgres -c "¥copy movies_genres from 'movies_genres.csv' with delimiter E'¥t' csv header encoding 'UTF8'" COPY 22084 $ psql ml_latest_small -U postgres -c "¥copy ratings from 'ratings.csv' with delimiter E'¥t' csv header encoding 'UTF8'" COPY 100836 $ psql ml_latest_small -U postgres -c "¥copy tags from 'tags.csv' with delimiter E'¥t' csv header encoding 'UTF8'" COPY 3683 psql コマンドで各データを ml_latest_small データベースに取り込む ※件数はデータセットをダウンロードするタイミングで異なる可能性がある 33 ※このスライドは受講生向けの内容です。
34. データベース SQL (SELECT：基本) 奥 健太
35. 基本 35
36. データの検索 SELECT column_name, column_name, ..., column_name FROM table_name WHERE condition options; column_name: カラム名 table_name: テーブル名 condition: 問合せ条件 options: 出力指定オプション PostgreSQL 14.5文書 SELECT: https://www.postgresql.jp/document/14/html/sql-select.html 指定したテーブルから、問合せ条件に合致したデータを検索し、指定したカラム の値を、指定した出力方法で出力 省略可 36
37. 全データの出力 SELECT * FROM movies; すべてのカラムを意味する 37
38. 複数のカラムの指定 SELECT movie_id, title FROM movies; 38
39. 問合せ条件 39
40. 条件の指定 SELECT * FROM movies WHERE movie_id = 1; 40
41. 範囲検索 SELECT * FROM movies WHERE year >= 2015; 比較演算子 カラムが数値型の場合 カラムが文字列型の場合 column = x x と等しい x の文字列と一致 column < x (column > x) x よりも小さい（大きい） 列の文字列のコードが x の文字列 のコードよりも小さい（大きい） column <= x (column >= x) x 以下（以上） 列の文字列のコードが x の文字列 のコードと一致する、もしくは、 それよりも小さい（大きい） column <> x x と等しくない x の文字列と一致しない 41
42. 部分一致検索 SELECT title FROM movies WHERE title LIKE 'Star Wars%'; 文字列の部分一致（前方一致や後方一致等）を条件で指定することが可能 ワイルドカード 説明 % 0個以上の文字 _ 任意の1個の文字 %a% どこかに 'a' を含む文字列 _%a%_ 中間に 'a' を含む文字列 ‘%’や’_’を文字列の要素として指定したいときは、直前にエスケープ文字’¥’を置く 42
43. LIKE句による条件の指定 SELECT title FROM movies WHERE title LIKE 'Star%'; SELECT title FROM movies WHERE title LIKE '%Star'; SELECT title FROM movies WHERE title LIKE '%Star%'; SELECT title FROM movies WHERE title LIKE '_%Star%_'; 43
44. 空値（NULL）の指定 SELECT * FROM movies WHERE year IS NULL; SELECT * FROM movies WHERE year IS NOT NULL; 空値（データが未登録）を意味する 44
45. 条件の組合せ SELECT * FROM movies WHERE title LIKE 'Star Wars%' AND year >= 2015; SELECT * FROM movies WHERE title LIKE 'Star Wars%' OR year >= 2015; 45
46. BETWEEN SELECT * FROM movies WHERE year BETWEEN 2014 AND 2016; SELECT * FROM movies WHERE year >= 2014 AND year <= 2016; 等価 46
47. IN SELECT * FROM movies WHERE year IN (2014, 2016); SELECT * FROM movies WHERE year = 2014 OR year = 2016; 等価 47
48. 出力指定 48
49. 並べ替え SELECT * FROM movies ORDER BY year; SELECT * FROM movies ORDER BY year ASC; 昇順（ascending-order）を指定 SELECT * FROM movies ORDER BY year DESC; 降順（descending-order）を指定 49
50. 出力件数の指定（LIMIT） SELECT * FROM movies ORDER BY movie_id ASC LIMIT 20; SELECT * FROM movies ORDER BY movie_id ASC OFFSET 10 LIMIT 20; 50
51. 複数条件での並べ替え SELECT * FROM movies ORDER BY year DESC, title ASC; 1) yearの降順に出力する 2) yearが同じ場合には、titleの昇順に並べ替えて出力する 51
52. 重複の除去を指定 SELECT DISTINCT year FROM movies; 明示的に重複の除去を指定 （distinct = 別の、異なった） 52
53. 集約関数 53
54. 集約関数（1/2） SELECT count(*), avg(rating), sum(rating), max(rating), min(rating) FROM ratings; 集約関数 集約関数 説明 count() 指定条件によって得られたテーブルのレコード数を出力 avg() 指定条件によって得られたカラムの値の平均値を出力 sum() 指定条件によって得られたカラムの値の合計を出力 max() 指定条件によって得られたカラムの値の中の最大値を出力 min() 指定条件によって得られたカラムの値の中の最小値を出力 54
55. 集約関数（2/2） SELECT count(DISTINCT movie_id) FROM ratings; 相違なる movie_id の件数を出力する 55
56. グループ化 56
57. グループ化（1/3） SELECT movie_id, count(rating), avg(rating) FROM ratings GROUP BY movie_id; 映画ごとの評価値数と評価値の平均を出力する 57
58. グループ化（2/3） SELECT movie_id, count(rating), avg(rating) FROM ratings WHERE rated_at >= '2015-01-01' GROUP BY movie_id; 評価日時が2015年1月1日以降のデータのみを対象に、 映画ごとの評価値数と評価値の平均を出力する 58
59. グループ化（3/3） SELECT movie_id, count(rating), avg(rating) FROM ratings GROUP BY movie_id HAVING count(rating) >= 20; 映画ごとの評価値数と評価値の平均を出力する ただし、評価値数が20件以上のデータのみを出力する ※WHERE句は集計対象の問合せ条件 HAVING句はあくまでも出力対象の条件 59
60. データベース SQL (SELECT：結合、副問合せ) 奥 健太
61. 結合 61
62. 結合 交差結合 二つのテーブルの直積（全組合せ）を求める 自然結合 二つのテーブルで同名のカラムで結合 等結合（内部結合） 二つのテーブルで条件に合致するもののみを結合 左（右、完全）外部結合 左（右）側のテーブルに対応するデータがない場合はNULL値をセットして結合 自己結合 自分自身のテーブルで結合 62
63. 自然結合 SELECT * FROM movies NATURAL JOIN tags; 二つのテーブルで同名のカラムで結合 movies movie_id title year 3 Grumpier Old Men 1995 4 Waiting to Exhale 1995 5 Father of the Bride Part II 1995 user_id movie_id tag tagged_at 289 3 moldy 2006-03-27 11:01:00 289 3 old 2006-03-27 11:01:00 474 5 pregnancy 2006-01-16 10:11:43 474 5 remake 2006-01-16 10:11:43 movie_id title year user_id tag tagged_at 3 Grumpier Old Men 1995 289 moldy 2006-03-27 11:01:00 3 Grumpier Old Men 1995 289 old 2006-03-27 11:01:00 5 Father of the Bride Part II 1995 474 pregnancy 2006-01-16 10:11:43 5 Father of the Bride Part II 1995 474 remake 2006-01-16 10:11:43 tags 63
64. 等結合 SELECT * FROM ratings JOIN tags ON ratings.rated_at = tags.tagged_at; 二つのテーブルで条件に合致するもののみを結合 user_id movie_id rating rated_at user_id movie_id tag tagged_at 380 182639 4 2018-09-14 6:38:26 62 49530 Africa 2018-09-14 6:38:26 user_id movie_id rating rated_at 62 49530 4.5 2018-09-14 6:38:16 380 182639 4 2018-09-14 6:38:26 380 2048 4 2018-09-14 6:39:15 user_id movie_id tag tagged_at 62 49530 corruption 2018-09-14 6:38:24 62 49530 Africa 2018-09-14 6:38:26 tags ratings 64
65. 左外部結合 SELECT * FROM ratings LEFT OUTER JOIN tags ON ratings.rated_at = tags.tagged_at; 左側のテーブルに対応するデータがない場合はNULL値をセットして結合 user_id movie_id rating rated_at user_id movie_id tag tagged_at 62 49530 4.5 2018-09-14 6:38:16 NULL NULL NULL NULL 380 182639 4 2018-09-14 6:38:26 62 49530 Africa 2018-09-14 6:38:26 380 2048 4 2018-09-14 6:39:15 NULL NULL NULL NULL user_id movie_id rating rated_at 62 49530 4.5 2018-09-14 6:38:16 380 182639 4 2018-09-14 6:38:26 380 2048 4 2018-09-14 6:39:15 user_id movie_id tag tagged_at 62 49530 corruption 2018-09-14 6:38:24 62 49530 Africa 2018-09-14 6:38:26 tags ratings 65
66. 右外部結合 SELECT * FROM ratings RIGHT OUTER JOIN tags ON ratings.rated_at = tags.tagged_at; 右側のテーブルに対応するデータがない場合はNULL値をセットして結合 user_id movie_id rating rated_at user_id movie_id tag tagged_at NULL NULL NULL NULL 62 49530 corruption 2018-09-14 6:38:24 380 182639 4 2018-09-14 6:38:26 62 49530 Africa 2018-09-14 6:38:26 user_id movie_id rating rated_at 62 49530 4.5 2018-09-14 6:38:16 380 182639 4 2018-09-14 6:38:26 380 2048 4 2018-09-14 6:39:15 user_id movie_id tag tagged_at 62 49530 corruption 2018-09-14 6:38:24 62 49530 Africa 2018-09-14 6:38:26 tags ratings 66
67. 完全外部結合 SELECT * FROM ratings FULL OUTER JOIN tags ON ratings.rated_at = tags.tagged_at; 左側のテーブルを基準として結合、次に右側のテーブルを基準として結合 user_id movie_id rating rated_at 62 49530 4.5 2018-09-14 6:38:16 380 182639 4 2018-09-14 6:38:26 380 2048 4 2018-09-14 6:39:15 user_id movie_id tag tagged_at 62 49530 corruption 2018-09-14 6:38:24 62 49530 Africa 2018-09-14 6:38:26 user_id movie_id rating rated_at user_id movie_id tag tagged_at 62 49530 4.5 2018-09-14 6:38:16 NULL NULL NULL NULL 380 182639 4 2018-09-14 6:38:26 62 49530 Africa 2018-09-14 6:38:26 380 2048 4 2018-09-14 6:39:15 NULL NULL NULL NULL NULL NULL NULL NULL 62 49530 corruption 2018-09-14 6:38:24 tags ratings 67
68. 交差結合 SELECT * FROM ratings CROSS JOIN tags; 二つのテーブルの直積（全組合せ）を求める user_id movie_id rating rated_at 62 49530 4.5 2018-09-14 6:38:16 380 182639 4 2018-09-14 6:38:26 380 2048 4 2018-09-14 6:39:15 user_id movie_id tag tagged_at 62 49530 corruption 2018-09-14 6:38:24 62 49530 Africa 2018-09-14 6:38:26 SELECT * FROM ratings, tags; user_id movie_id rating rated_at user_id movie_id tag tagged_at 62 49530 4.5 2018-09-14 6:38:16 62 49530 corruption 2018-09-14 6:38:24 62 49530 4.5 2018-09-14 6:38:16 62 49530 Africa 2018-09-14 6:38:26 380 182639 4 2018-09-14 6:38:26 62 49530 corruption 2018-09-14 6:38:24 380 182639 4 2018-09-14 6:38:26 62 49530 Africa 2018-09-14 6:38:26 380 2048 4 2018-09-14 6:39:15 62 49530 corruption 2018-09-14 6:38:24 380 2048 4 2018-09-14 6:39:15 62 49530 Africa 2018-09-14 6:38:26 tags ratings 68
69. 内部結合と外部結合（1/2） SELECT * FROM t1 CROSS JOIN t2; SELECT * FROM t1 JOIN t2 ON t1.c2 = t2.c3; SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.c2 = t2.c3; SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.c2 = t2.c3; t1 t2 交差結合 c1 c2 c3 c4 a 1 1 x a 1 2 y a 1 3 z b 1 1 x b 1 2 y b 1 3 z c 2 1 x c 2 2 y c 2 3 z d 4 1 x d 4 2 y d 4 3 z c1 c2 a 1 b 1 c 2 d 4 c3 c4 1 x 2 y 3 z c1 c2 c3 c4 a 1 1 x b 1 1 x c 2 2 y c1 c2 c3 c4 a 1 1 x b 1 1 x c 2 2 y d 4 NULL NULL c1 c2 c3 c4 a 1 1 x b 1 1 x c 2 2 y NULL NULL 3 z 等結合＝内部結合 左外部結合 右外部結合 69
70. 内部結合と外部結合（2/2） 内部結合 結合結果が、交差結合結果の部分集合（内部）になる (a,1,1,x) (a,1,2,y) (a,1,3,z) (b,1,1,x) (b,1,2,y) (b,1,3,z) (c,2,1,x) (c,2,2,y) (c,2,3,z) (d,4,1,x) (d,4,2,y) (d,4,NULL,NULL) (NULL,NULL,3,z) 外部結合 結合結果が、交差結合結果の部分集合に納まらない（外部にはみ出す） (d,4,3,z) 内部結合 外部結合 交差結合 70
71. 等結合と内部結合 SELECT * FROM ratings JOIN tags ON ratings.rated_at = tags.tagged_at; SELECT * FROM ratings INNER JOIN tags ON ratings.rated_at = tags.tagged_at; SELECT * FROM ratings, tags WHERE ratings.rated_at = tags.tagged_at; 等価 二つのテーブルの交差結合から条件に合致するデータを選択していることになる 等価 71
72. テーブルの追加とデータの登録 | genres2 ml_latest_small=# CREATE TABLE genres2 ( genre_id INT, genre_name TEXT NOT NULL, parent_genre_id INT, PRIMARY KEY(genre_id), FOREIGN KEY(genre_id) REFERENCES genres2(genre_id)); 属性名 カラム名 データ型 制約 ジャンルID genre_id INT PRIMARY KEY ジャンル名 genre_name TEXT NOT NULL 親ジャンルID parent_genre_id INT REFERENCES genres2(genre_id) genres2 ジャンル (ジャンルID, ジャンル名, 親ジャンルID) $ psql ml_latest_small -U postgres -c "¥copy genres2 from 'genres2.csv' with delimiter E'¥t' csv header encoding 'UTF8'" COPY 27 72
73. 自己結合 SELECT child.genre_id, child.genre_name, child.parent_genre_id, parent.genre_id AS parent__genre_id, parent.genre_name AS parent__genre_name FROM genres2 child JOIN genres2 parent ON child.parent_genre_id = parent.genre_id; 自分自身のテーブルで結合 genre_id genre_name parent_genre_id 1 Action 15 Sci-Fi 20 Car Action 1 22 Spy Action 1 23 Space Opera 15 genre_i genre_name parent_genre_id parente__genre_id parent__genre_name 20 Car Action 1 1 Action 22 Spy Action 1 1 Action 23 Space Opera 15 15 Sci-Fi genre_id genre_name parent_genre_id 1 Action 15 Sci-Fi 20 Car Action 1 22 Spy Action 1 23 Space Opera 15 カラムの別名指定 genres2 -> child genres2 -> parent 73
74. 副問合せ 74
75. 副問合せ SELECT文内に複数の基本的なSELECT文を階層的に（入れ子型に）組み 合わせた問合せ テーブル副問合せ 副問合せの結果がテーブルの形になる スカラ副問合せ 副問合せの結果がスカラ（1行1列）になる 75
76. テーブル副問合せ（IN句、ANY(SOME)句、ALL句） SELECT * FROM movies NATURAL JOIN movies_genres NATURAL JOIN genres WHERE genre_id IN ( SELECT genre_id FROM movies_genres WHERE movie_id = 260 ); genre_id 1 2 15 =ANY、=SOMEでも等価 <=ALL、<=ANYとすると...？ 76
77. テーブル副問合せ（FROM句の副問合せ） SELECT avg(c) FROM ( SELECT count(rating) AS c FROM ratings GROUP BY movie_id ) AS t1; c 28 1 1 : 77
78. テーブル副問合せ（EXISTS句） SELECT movie_id, title FROM movies WHERE EXISTS ( SELECT * FROM tags WHERE movie_id = movies.movie_id ); SELECT * FROM tags WHERE movie_id = 1; SELECT * FROM tags WHERE movie_id = 2; SELECT * FROM tags WHERE movie_id = 3; SELECT * FROM tags WHERE movie_id = 4; SELECT * FROM tags WHERE movie_id = 5; EXISTS EXISTS EXISTS NOT EXISTS EXISTS movie_id title 1 Toy Story 2 Jumanji 3 Grumpier Old Men 5 Father of the Bride Part II : : SELECT * FROM tags WHERE movie_id = 6; NOT EXISTS 78
79. テーブル副問合せ（NOT EXISTS句） SELECT movie_id, title FROM movies WHERE NOT EXISTS ( SELECT * FROM tags WHERE movie_id = movies.movie_id ); SELECT * FROM tags WHERE movie_id = 1; SELECT * FROM tags WHERE movie_id = 2; SELECT * FROM tags WHERE movie_id = 3; SELECT * FROM tags WHERE movie_id = 4; SELECT * FROM tags WHERE movie_id = 5; EXISTS EXISTS EXISTS NOT EXISTS EXISTS SELECT * FROM tags WHERE movie_id = 6; NOT EXISTS movie_id title 4 Waiting to Exhale 6 Heat 8 Tom and Huck 9 Sudden Death : : 79
80. スカラ副問合せ（WHERE句の副問合せ） SELECT movie_id, rating FROM ratings WHERE rating >= ( SELECT avg(rating) FROM ratings ); avg 3.501556984 80
81. SELECT movie_id, count(rating), avg(rating) FROM ratings GROUP BY movie_id HAVING count(rating) >= ( SELECT avg(c) FROM ( SELECT count(rating) AS c FROM ratings GROUP BY movie_id ) AS t1 ); スカラ副問合せ（HAVING句の副問合せ） c 28 1 1 : avg 10.36980666 81
82. データベース SQL（VIEW、ALTER、GRANTなど） 奥 健太
83. ビュー 83
84. ビュー（仮想テーブル）の作成 CREATE VIEW name AS query; name: ビュー名 query: ビューの列と行を生成するSELECT文 PostgreSQL 14.5文書 CREATE VIEW: https://www.postgresql.jp/document/14/html/sql-createview.html CREATE VIEW v_movies AS SELECT movie_id, title, year, genre_name FROM movies NATURAL JOIN movies_genres NATURAL JOIN genres; 実際に存在するテーブルから抽出したデータを保持 アプリケーションで必要な項目のみを抽出 84
85. ビューへの問合せ 等価 SELECT * FROM ( SELECT movie_id, title, year, genre_name FROM movies NATURAL JOIN movies_genres NATURAL JOIN genres ) AS v_movies; SELECT * FROM v_movies; CREATE VIEW v_movies AS SELECT movie_id, title, year, genre_name FROM movies NATURAL JOIN movies_genres NATURAL JOIN genres; ビューのメリット ❏ 複雑な問合せを単純な問合せとして実行できる ❏ ビューへのアクセス権限設定によるセキュリティ強化 85
86. ビューの削除 DROP VIEW name; name: ビュー名 PostgreSQL 14.5文書 DROP VIEW: https://www.postgresql.jp/document/14/html/sql-dropview.html DROP VIEW v_movies; 86
87. アクセス権限 87
88. データベースロールの作成 CREATE ROLE name WITH LOGIN PASSWORD 'password'; name: ロール名 password: ロールのパスワード PostgreSQL 14.5文書 CREATE ROLE: https://www.postgresql.jp/document/14/html/sql-createrole.html CREATE ROLE rsl WITH LOGIN PASSWORD 'ryukoku'; $ psql -U rsl -d ml_latest_small Password for user rsl: ml_latest_small=> ml_latest_small=> SELECT * FROM v_movies; ERROR: permission denied for view v_movies <- ロールrslのパスワード <- プロンプトが => と表示される <- アクセスが拒否される 作成したロールでのログインとデータベースへのアクセス 88
89. ロールへのアクセス権限の付与 GRANT 権限 ON table_name TO role_specification; 権限: 設定する権限（SELECT、INSERT、UPDATE、DELETE、ALLなど） table_name: テーブル名 role_specification: 権限設定対象のロール PostgreSQL 14.5文書 GRANT: https://www.postgresql.jp/document/14/html/sql-grant.html ml_latest_small=# GRANT SELECT ON v_movies TO rsl; GRANT ml_latest_small=> SELECT * FROM v_movies; 89
90. ロールからのアクセス権限の取消 REVOKE 権限 ON table_name FROM role_specification; 権限: 設定する権限（SELECT、INSERT、UPDATE、DELETE、ALLなど） table_name: テーブル名 role_specification: 権限設定対象のロール PostgreSQL 14.5文書 REVOKE: https://www.postgresql.jp/document/14/html/sql-revoke.html ml_latest_small=# REVOKE SELECT ON v_movies FROM rsl; REVOKE ml_latest_small=> SELECT * FROM v_movies; ERROR: permission denied for view v_movies 90
91. バックアップと復元 91
92. データベースのダンプ $ sudo -u postgres pg_dump dbname > filename dbname: ダンプするデータベース名 filename: 出力先のダンプファイル名 $ sudo -u postgres pg_dump ml_latest_small > ml_latest_small.sql PostgreSQL 14.5文書 pg_dump: https://www.postgresql.jp/document/14/html/app-pgdump.html 92
93. ダンプファイル $ less ml_latest_small.sql データベースを復元するための一連のSQL文で構成されている -- -- PostgreSQL database dump -- ...（略）... CREATE TABLE public.movies ( movie_id integer NOT NULL, title text NOT NULL, year integer ); ...（略）... COPY public.movies (movie_id, title, year) FROM stdin; 1 Toy Story 1995 2 Jumanji 1995 3 Grumpier Old Men 1995 4 Waiting to Exhale 1995 5 Father of the Bride Part II 1995 ...（略）... 93
94. データベースの復元 $ sudo -u postgres psql dbname < filename dbname: 復元先のデータベース名 filename: 復元元のダンプファイル名 $ sudo -u postgres psql ml_latest_small_copy < ml_latest_small.sql PostgreSQL 14.5文書 pg_dump: https://www.postgresql.jp/document/14/html/app-pgdump.html ※事前にデータベースは用意しておく必要がある postgres=# CREATE DATABASE ml_latest_small_copy ENCODING ‘UTF8’; postgres=# ¥c ml_latest_small_copy ml_latest_small_copy=# ¥d 94
95. 参考 ❏ 増永良文, データベース入門[第2版], サイエンス社, 2021. ❏ 吉川正俊, IT Text データベースの基礎, オーム社, 2019. ❏ 植村俊亮, 入門 データベース, オーム社, 2018. ❏ 奥野幹也, 理論から学ぶデータベース実践入門, 技術評論社, 2015. ❏ 川越恭二, 楽しく学べるデータベース, 共立出版, 2014. ❏ 木村明治, プロになるためのデータベース技術入門, 技術評論社, 2012. ❏ 松信嘉範, データベース技術実践入門, 技術評論社, 2012. ❏ 永田武, データベースの基礎, コロナ社, 2011. ❏ 上島紳一ら, データベース, 昭晃堂, 2009. ❏ 真野正, 独習データベース設計, 翔泳社, 2009. ❏ 石川博, データベース, 森北出版, 2008. ❏ 島田達巳ら, データベース, 日科技連, 2008. ❏ 大木幹雄, データベース技術, 日本理工出版会, 2006. ❏ 山本森樹, 体系的に学ぶデータベースのしくみ, 日経BPソフトプレス, 2005. ❏ 上向井照彦ら, リレーショナルデータベース, 日刊工業新聞社, 2004. ❏ 速水治夫ら, データベース, オーム社, 2002. ❏ 高橋栄司ら, 基礎からのデータベース設計, ソフトバンクパブリッシング, 2002. ❏ 鶴保征城ら, 情報データベース技術, 電気通信協会, 2000. ❏ 西尾章治朗, データベース, オーム社, 2000. ❏ T.J.ティオリー, データベースの設計, 勁草書房, 2000. ❏ 北川博之, データベースシステム, 昭晃堂, 1996. ❏ 増永良文, リレーショナルデータベース入門, サイエンス社, 1991. ❏ PostgreSQL 14.5文書, https://www.postgresql.jp/document/14/html/index.html 95
96. Acknowledgement F. Maxwell Harper and Joseph A. Konstan. 2015. The MovieLens Datasets: History and Context. ACM Transactions on Interactive Intelligent Systems (TiiS) 5, 4: 19:1–19:19. https://doi.org/10.1145/2827872 MovieLens | GroupLens <https://grouplens.org/datasets/movielens/> 96