- 追加された行はこの色です。
- 削除された行はこの色です。
[[FrontPage]]
*概要 [#v04b8c33]
-データベース操作言語
*目次 [#iea555ac]
#contents
*関連ページ [#w9a059b6]
-[[SQLite]]
*学習 [#t8f52393]
-[[これが初学習に良さそう>http://rfs.jp/sb/sql]]
-[[Leetcode>https://leetcode.com/problemset/database/]]
-Leetcode, Hacker RankにはSQL問題がある
--https://www.hackerrank.com/domains/sql/select
本章ではデータベース、RDBMS、MySQLの概要について説明しました。
まず大きな括りとしてデータベースがあり、その中にRDBMSがあり、様々なRDBMSの一つとしてMySQLが有ることを理解してください。
*順序 [#g22a4024]
-SQLが実行される順序は以下
--FROM → WHERE → GROUPBY → HAVING → SELECT → ORDERBY
SELECT build_id, project_id, string_agg(text, "\n") as logs
FROM argo.builds.today
WHERE project_id = "container-registry-147718"
GROUP BY build_id, project_id
HAVING logs LIKE "%quota%"
LIMIT 100
*用語 [#o3b07d1f]
-dropというものはSQLのテーブルを削除する操作
*勉強方法 [#c8f42b4e]
-以下のコマンドで管理画面に移れる
mysql -u root -p
*用語 [#a96af20f]
-MySQL
--データベースの集合
--RDBMSの一種。
-データベース
--インデックスが一個、ビューが複数個、テーブルが複数個で構成
--mysqlデータベースとinformation_schemaデータベースは、管理用で慎重に触る
--testデータベースがデフォルトである
-テーブル
--(列名、型、属性）のタプルのリストに対して、データが入っている。
--型はintとか
--属性は主キーとか、UNIQUEとか
-ユーザ
--rootが管理者用。
--rootはユーザを追加できる。
-列名
--fieldとも言う（が、行列の類推から列名と言ったほうがわかりやすいのでこっちで統一）
-型
--typeともいう。
-属性
--(Null, key, default, extra)のタプル。
*管理用コマンド [#h245a307]
-SET NAMES CP932; #日本語入力OKにする（Ubuntuでは効かなかった）
-show databases; #データベースの一覧を出力
-create database newdatabase; #新しいデータベースの追加
-USE newdatabase;#データベースの選択
-今のデータベースの確認
show tables; #テーブルの一覧を出力
CREATE TABLE bookinfo( #テーブルの追加。フィールドを必要な分だけ指定（[field, type, attribute]+）。
isbn VARCHAR(20) PRIMARY KEY,
title VARCHAR(100),
price INTEGER NOT NULL
);
SHOW FIELDS FROM bookinfo; #テーブルのフィールドを出力
DROP TABLE bookinfo;
-USE mysql;#管理用データベースの選択（rootユーザが必要）
-select user from user #ユーザの一覧を出力
-GRANT ALL PRIVILEGES ON mybookdb.* TO newuser@localhost IDENTIFIED BY 'newuserpassward' #新しいユーザの追加
-QUIT; #終了. Ctrl-Dでもよい
*主キー [#e246cf71]
-要するにunordered_setのキー
--例えば、同姓同名の人を入れたい場合は、姓名をキーにできない。
++重複する値を取らない（UNIQUE）
++空の状態は許されない(NOT NULL)
-他にも性質はDEFAULT, AUTO INCREMENTというものがある
-複合主キーというものがある。
--この場合、最後に以下のようにする。
CREATE TABLE bookinfo (
isbn VARCHAR(20),
title VARCHAR(100),
price INTEGER NOT NULL,
PRIMARY KEY(isbn,title)
)
;
*文法 [#ccf80910]
**分類 [#m5bfb5c4]
-データ定義言語（DDL）：データ構造定義
--CREATE TABLE
--DROP TABLE
-データ操作言語（DML）：データ検索・新規登録・更新・削除
--INSERT
--SELECT
--UPDATE
-データ制御言語（DCL）:データに対するアクセス制御
--GRANT
**文 [#q6d16222]
-WHERE
--一般に列を限定するために使う。
--conditionの書き方について特記すべきこと
---比較演算子は=
---!=は、<>
---ワイルドカード検索は、LIKE '%値%'
---&&, ||は、AND, OR
-SELECT：テーブルの取得。
# SELECT 列名 FROM テーブル名 WHERE [行抽出用condition];
SELECT * FROM bookinfo WHERE isbn='00003';
--表示順をソートしたい場合
---最後にORDER BY 列名 ASK|DESKをつける。
---単数キー：SELECT * FROM bookinfo ORDER BY price ASC;
---複数キー：SELECT * FROM bookinfo ORDER BY price ASC,isbn DESC;
-INSERT INTO：末尾追加
# INSERT INTO bookinfo(列名1,列名2,列名3) VALUES(値1,値2,値3);
INSERT INTO bookinfo(isbn,title,price) VALUES('00002','SQL test',2300);
INSERT INTO fruit (name, price) VALUES ("apple", 120), ("pineapple", 200);
INSERT INTO fruit SET name="melon", price=1500;
# auto_incrementの項がある場合は、その場所を飛ばしてタプルを書けば良い。また、強引にauto_incrementの部分に変な値を追加すると、次からはその値を基準にincrementしていく
INSERT INTO users (name, password, created_at) VALUES ("John Legendary","75D2DB8E-45E5-08D5-6669-BBA06F615472","2017-04-01 08:13:56"); #こんな感じでvaluesに対して列名を固定させることもできる
-UPDATE：テーブルの更新。
# UPDATE テーブル名 SET 列名1='', 列名2='' WHERE [行抽出用condition];
UPDATE bookinfo SET title='Android基礎テキスト',price=2000 WHERE isbn='00005';
-DELETE：テーブルの削除。
# DELETE FROM テーブル名 WHERE [行抽出用condition];
-トランザクション：必ず連続して行わなければならない処理A->Bを確実に行う
++Aを行った後、普通にBが終了すれば問題ない（このことをコミットという）
++Aを行った後、Bがトラブルで実行不能の場合、Aを取り消す（ロールバックという）
--テーブルを作るときに、ENGINE=InnoDBを指定する必要がある
BEGIN;
[処理]
COMMIT; # もしくはROLL BACK（BEGINの前の状態に戻る）
-WITH AS: 一時テーブルの代入
--https://dev.classmethod.jp/server-side/db/redshift-postgresql-with/
WITH CTE1 AS (
SELECT item_id, item_name FROM items
), CTE2 AS (
SELECT item_id, item_name FROM items
)
**複数のテーブルを使う [#gf9bc45c]
-列名は、FROM内部のテーブルの中で共有されている場合、テーブル名を指定しないと行けない。
--逆に、共有されていなければ省略できる（JOINを参照）
**JOIN [#x666c934]
-リレーション系の真骨頂 [[参考1>http://qiita.com/zaburo/items/548b3c40fee68cd1e3b7]], [[参考2>http://rfs.jp/sb/sql/s03/03_3.html]]
-2つのテーブルで共有する列名に対して、
-主に三種類のJOINがある
--INNER JOIN：対応するものがある場合のみ報告する
--OUTER JOIN：もし対応するものがなかった場合、「なかった」と報告する列名を報告する方法（どちらのテーブルを支配的にするかを指定するためにLEFT JOINとRIGHT JOINがある）
-例：
--fullnameはcustomerのみの列名なのでcustomer.fullnameとしなくてもよい
--ON内部が=なら、USING(id_c)としてもいい
--この場合、customerが支配的なので、必ず全customerがレコードに現れる。
SELECT id_p, customer.id_c, fullname
FROM customer LEFT OUTER JOIN purchase
ON purchase.id_c = customer.id_c;
-[[この説明>https://discuss.leetcode.com/topic/10964/simple-solution]]がすごく良い。
--FROM Table t1,Table t2とすると、列サイズ|t1|+|t2|、行サイズ|t1|*|t2|のテーブルができる。
--ここからWHEREで枝刈りができる
--Deleteのあとにはテーブル名を入れるが、これは要するにレコードのunorder_setになっていれば良い。直積ならば、列サイズを限定するために、select t1と、t1の全てを指定することができる。
-,とINNER JOINは基本同じだが、INNER JOINじゃないとONが使えない。,の場合は代わりにwhereでやる。
**別名 [#xf6f9104]
-別名と言っているが、省略以上の意味を持っている。
--「別のテーブルを作る」くらいの意味がある。
-例
--Employee emp
--Employee as emp
**重複除外 [#obec9f36]
-select distinctとすると、重複除外される。
**IS NULL [#rdafda20]
-NULLは他のどの値と比較しても偽となります。例えNULLをNULLと比較しても偽となります。次の例を見てください。
mysql> select * from strtest where str = NULL; # 間違い
mysql> select * from strtest where str is NULL;
*表記 [#t1c52710]
-MySQLに頑張って入れるのはめんどいので、なんかこういう方式の入力形式が流行っている。
--{"headers": {"Employee": ["Id", "Name", "Salary", "ManagerId"]}, "rows": {"Employee": [[1, "Joe", 70000, 3], [2, "Henry", 80000, 4], [3, "Sam", 60000, null], [4, "Max", 90000, null]]}}
--headersが列名、rowsの一つ一つが行に相当する。
**GROUP BY [#ic8d7653]
-Group by (同一視列)を使うと、列を同一視することができる。
--同一視した部分以外は集合のように
---select文では、Sum(非同一視列)のように、reduction関数を使って選択するしかなくなる。
---reductionには、max, sum, avg, min, countなどが存在する。
--集合に対する枝刈りをしたいならば、having count(*) > 10とか、having max(Quantity) > 100とかのようにする
http://www.atmarkit.co.jp/ait/articles/0012/22/news002.html
*インデックス [#v8c51253]
-[[MySQLはインデックスを書くと早くなる>http://blog.takanabe.tokyo/2014/11/30/49/]]
-indexとは、要するにmap<ll, S>であって、indexが[l, r)の範囲のデータ in Sを全列挙するというクエリが簡単にできるもの。
--もっと一般化すると、d次元データの矩形領域に含まれる点群を高速全列挙できれば何でも良いという話がある。
-B木が平衡二分木が使われているというのが有名
-基本的には上のスキームに乗ってさえいれば全然問題なくて、亜種がある。例としては
--r=l+1ならハッシュindex（WHERE id = 1;とかのクエリだけが早くなる）
--Sがbinaryだったらビットマップ索引
--地理情報であれば、Segment Treeやkd木などの多次元矩形領域全列挙アルゴリズムを使えば良いねということになる
--クラスタ化で、そもそも冗長にデータをディスクそのものに持っておきましょうという話になると、indexがちゃんと整合していない部分が現れる。こういうものは、実際にアクセスしたときに初めて整合するように代入し直すので、
*計算量 [#q4b706c8]
-http://nippondanji.blogspot.jp/2009/03/mysql_25.html
*設計 [#z8c227b1]
結局これどうやってシステムと組み合わせるの？
*雑多 [#t393ddb2]
select id, name from a order by 2;
とすると、1-indexedでスキーマの2番目を取得することができる。なぜかというと一時テーブルを生成する場合とかに名前をつけてられないので
SQL
safe devide
if
date_diff
concat
format
unnest
SQLのunionとunion allの差は非常に少なくて、要するに重複がないかどうかに尽きる（union allだとmultisetになる）
unionは、縦方向に連結する。スキーマは必ず同じじゃないといけない、さもなくばエラー
over句は分析関数と呼ばれる。「行をまとめないのに集合関数のように集計する」これが分析関数の基本的動作です。
https://qiita.com/tlokweng/items/fc13dc30cc1aa28231c5 この資料めっちゃよい
集合関数
SELECT COUNT(*) FROM test_orders;
COUNT(*)
----------------
6
分析関数
SELECT order_id, item, COUNT(*) OVER () FROM test_orders;
ORDER_ID ITEM COUNT(*)OVER()
---------- ---------- --------------
1001 Apple 6
1005 Banana 6
1010 Banana 6
1021 Apple 6
1025 Apple 6
1026 Apple 6
over句には行の同一視というか、集計範囲が指定できる。指定方法にはpartition, window, groupがある。partitionはgroup byと同じで同じ値のものが同一視される。ordered by itemは「itemでソートしたときの先頭行からの累積和」というもとの意味と異なるもの。windowはgroup byのオプション。ORDER BYでは先頭行から現在行までが対象になると説明しましたが、これは実はWINDOW指定を省略したときのデフォルト動作。ROWS BETWEEN If replication lag is high but steady, you can delete the failover replica and recreate it. However, if the write load continues, and the replication lag continues to increase, you must take action
レプリカの同期が遅れている場合、別にレプリカを消して作り直せば、データベースにクエリを適用するのではなく、マスターをそのままコピーできるからそれでも良い
3 preceeding 2 foillowingのようなもので、今の行プラスマイナス何行を範囲に含めるか？という話になる。
RANGEは、行数ではなくORDER BYで指定したカラムの値で範囲指定する。つまり、10「日」前までの総計などのクエリになり、すごい。
partition by ID order by SortKeyはどちらかというとpair<S, T>でのソートをしているという感覚っぽい。https://www.oracle.com/technetwork/jp/articles/otnj-sql-image3-4-323606-ja.html
ちなみに、select *とかやるとスキーマがテーブルから読み込まれるが、
「select 'hoge', 3, timestamp '2015-12-4 3:13:43'」はそれそのものが無名表となる。
無名の表は無名な部分が前からf0_, f1_…と連番でつけられていく（無名, data, 無名のようになっち得たらf0_, data, f1_と名付けられる）
SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34';
ちなみに、普通はinsert into test_orders values (1001, 'Apple', 4, date'2018-1-10');とするらしい。
timestamp_diffはタイムスタンプの引き算を行う
format_timestamp(string, timestamp)は%Xとかさまざまな表現があるっぽい。
timestamp部分の抽出を行い、以下の％から始まる文字列を利用することができる。
%Y-%m-%d %H:%M:%S %Xで時間以下だけ表示
You can redirect Dremel query output from within a client with the -> operator:
SELECT f1 FROM Table -> my_output_file.csv;
You can use the ->> operator to append client query output to the end of an existing file:
SELECT f2 FROM Table ->> my_output_file.csv;
With - as構文は便利で、複数のテーブルを前処理で取得して変数に格納することができる。サブクエリ構文って何に使うの？
https://www.postgresql.jp/document/9.4/html/queries-with.html
なんかselect, having, where, fromのどこにも入って来る可能性があるらしくかなりヤバそう
fromに入っているのはほとんどwith as句と同じ。しかも、with as句はDAGになってさえいれば順序を木にする必要ない
なおwith as句を複数のクエリに渡って使いたい場合は、create view句というものが存在
CREATE VIEW s1 AS
SELECT age, COUNT(age) as age_count
FROM students
GROUP BY age;
microsoft SQLでは、limit 10の代わりにselect top(names)を使うらしい。どれくらいの出力かとかわからないし使いみち不明だけど。
SQLではlikeで%=0個以上の文字、_で1文字に対応して検索できる
*レプリケーション [#q82f6f7f]
**遅延への対処 [#u0ed9c61]
- If replication lag is high but steady, you can delete the failover replica and recreate it. However, if the write load continues, and the replication lag continues to increase, you must take action
--レプリカの同期が遅れている場合、別にレプリカを消して作り直せば、データベースにクエリを適用するのではなく、マスターをそのままコピーできるからそれでも良い
*トラブルシューティング [#b1caee34]
-[[メモリが減るとbinlogが増える>https://screenshot.googleplex.com/dahRCf78AhO]]
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
