![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
概要 †
リンク †
目次 †関連ページ †環境構築 †
$ docker exec -it Docker-mysql_mysql_1 bash -p $ mysql -u root -p -h 127.0.0.1
学習 †
勉強方法 †
用語 †
storage engine †
フラグ †sql_mode †
innodb_flush_log_at_trx_commit †
スキーマ †主キー †
CREATE TABLE bookinfo ( isbn VARCHAR(20), title VARCHAR(100), price INTEGER NOT NULL, PRIMARY KEY(isbn,title) ) ; 文法 †分類 †
selectクエリ †
型 †
SELECT [カラム | スカラ] (, [カラム | スカラ], ...) FROM [テーブル | テーブル名](, [テーブル | テーブル名], ...) WHERE CONDITION(テーブル, スカラ) GROUP BY [カラム], [カラム]... HAVING CONDITION(テーブル, スカラ) ORDER BY [カラム] LIMIT 100 OFFSET 3;
便利関数 †
WITH CTE1 AS ( SELECT item_id, item_name FROM items ), CTE2 AS ( SELECT item_id, item_name FROM items )
関数的なもの †
if †
別名 †
重複除外 †
IS NULL †
表記 †
更新操作 †
# UPDATE テーブル名 SET 列名1='', 列名2='' WHERE [行抽出用condition]; UPDATE bookinfo SET title='Android基礎テキスト',price=2000 WHERE isbn='00005';
# DELETE FROM テーブル名 WHERE [行抽出用condition];
BEGIN; [処理] COMMIT; # もしくはROLL BACK（BEGINの前の状態に戻る） 関数 †
条件分岐 †
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N-1;
RETURN (
case when N > (select count(distinct e.salary) from employee e)
then
null
else (
select a.optional_salary
from
(
select distinct e.salary as optional_salary
from employee e
order by e.salary desc
) a limit 1 offset M)
end
);
END
管理用コマンド †
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;
インデックス †
計算量 †設計 †結局これどうやってシステムと組み合わせるの？ 雑多 †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文字に対応して検索できる binlog †
レプリケーション [#q82f6f7f] 遅延への対処 †
InnoDB †バッファプール †InnoDBでは、全ての変更はバッファプール上で行われ、後から遅れてバッファプールの内容がテーブルスペースへ書き込まれる。そして、永続性を保証するためにログに更新した内容を記録するようになっている。ログへの更新はシーケンシャルな書き込みになるためテーブルスペースへの書き込みに比べると高速だからだ。 Dirty pages †https://qiita.com/shinyashikis@github/items/638accdd74bb6972b0ce ダーティページはfsyncされていないバッファプール内部のデータのこと チェックポイントはダーティページ→テーブルスペース(SSD)のflush processで、ファジーとシャープがある。シャープはどうしてもダーティページの閾値が越えた時に発生するやつ dirty=badではなく、パフォーマンスへの影響はsharp checkpoint プロセスが走らない限りは大丈夫。https://stackoverflow.com/questions/10107845/how-to-fix-innodb-dirty-pages べつに直すべきものではなく、ダーティが多いと何が起きるかというと、突然再起動した時にデータが損失する可能性があるというだけ トラブルシューティング †未整理 †SQL92でCASE句が新しくできた。 Oracleではdecode, MySql?ではIfというものができたが、互換性がある文CASEのほうがつよい elseを書かずにそのまま出た場合にはNULLが買えるので注意！ EXISTS(テーブル) テーブルを引数にとって。行が存在していればTrue, そうでないならFalseを返す関数 WHENの代わりに使うことも出来る ビューって何 SELECTとかWHEREは句 !=は、<>で表される NULLはNULL以外の何物でもない NLULが入った演算はSQLでは全部unknownになる。NULLは値ではない NULLを対象とした演算で唯一演算がunknownにならないのは、NULL IS NULL NULLしかない 三値論理の一つでtrue, unknown, falseの三つに分かれている true=2, unknown=1, false = 0とした時、論理演算ANDはmin, 論理演算ORはMAXに相当することになる。 よくやられる高速化として、CASEよりEXISTSのほうが早いみたいなのがある。これはEXISTSはインデックスをハルことが出来るから CASEとEXISTSの交換は必ず同値となるが、 NOT CASE とNOT EXISTは同値とならない可能性がある。具体的にはNOT INの後にunknownが入っていると、必ずNOT INは何にもヒットしないというほぼバグみたいな仕様があるので注意 限定述語と呼ばれる、ANY, ALLという量化演算子が使える。これによってWHEREの中にfor allの術後を入れる子おtが出来る 限定述語の評価は、普通にandが展開されると思って問題ない 極値関数=min, maxのこと max関数に空テーブルが入力されると、NULLがかえる。ちなみにAVGとかSUMも同様の挙動をする ORDER BYは表示をするには便利だが、それ自体がリレーショナル演算子であるわけではない！これはカーソル定義の一部。 GROUP BYがない場合のHAVING句は、group by 1でテーブル全体が選択された場外になっている。なので、テーブル全体がある条件を満たしていたら何かの行を出力みたいな使い方ができる。 COUNT(*)はNULLを数えるが、COUNT(column)はNULLを数えないという差がある。なので、テーブルや グループテーブルのcount(*)とcount(column)が一致していればNULLがないという判定が出来る SELECT countries.name FROM countries as c; だと Error: near line 1: no such column: countries.name となりクエリが失敗しますが、 SELECT countries.name FROM countries; だと普通に結果が取得できます。 SQL Standard に準拠した環境では、as で alias したオリジナルのテーブル名は使えなくなります。実際のところ AS は「alias = 別名をつける」というよりは、SQL Standard では「rename = 名前変更」に近い動作を行います [0]。そのため、テーブルに別名をつけた時点で、そのクエリの中で元の名前は使用することはできなくなります [1, 2]。SQL Standard は有料の資料なので具体的にリファレンスを示すことはできませんが、AS で別名をつけたテーブル名はそのクエリ内では使えないとご理解ください。 [0] https://modern-sql.com/feature/table-column-aliases#footnote-0 Although “alias” is the commonly used term, “rename” is more appropriate (and also used by the standard). Alias typically implies that an object can be referred to by its alias as well as by its original name. The feature described on this page actually renames the objects: you cannot use the original names to refer to these objects anymore. [1] https://stackoverflow.com/questions/15318199/why-cant-i-use-a-tables-alias-and-its-original-name-in-the-same-query-when-two [2] https://stackoverflow.com/questions/21381642/mysql-alias-and-table-name Once you assign an alias to a table, that's its new name during the query - the original name is then not available. The "why" is due to the SQL standard. selectにfor update指定をすることで、select されたそれぞれの行で行ロックが発生します。行ロックされた行を、行ロックしたり update しようとする場合、行ロックが解除されるまで待ち状態になります。行ロックしたトランザクションが commit or rollback されると、行ロックが解除されます。 ロックは、V$LOCKED_OBJECTテーブルで確認します。 select OBJECT_ID
from v$locked_object; ロック状態がある場合はデータが表示されます。 systemユーザで実行しました。権限がないと実行できません。 データのクエリーを実行してから、同じトランザクション内で関連データを挿入または更新する場合は、通常の SELECT ステートメントで十分な保護が提供されません。ほかのトランザクションは、クエリーが実行されたばかりの同じ行を更新または削除できます。InnoDB では、追加の安全性が提供される 2 つのタイプのロック読み取りがサポートされています。 |
