おっすオラ藹??れ易いルール、無饅??無饅??無饅??無饅??無饅??無饅??ッ

■DML
DB製品豈?縺?nullの扱いを事前に調べる(word != ‘検索蛟?'やword = nullで縺?nullにヒットしない、is not nullを使う等)
null値はもっとも蟆?さな値、or大きな値・??nullが含まれるかnullの件数を先に確認してお縺?、下に臀??を載せた）
集計関数縺?NULLを無鐔??する（下に臀??を載せた）
文字列縺?'で囲む
藹??得したいカラムは集計関数に使繧?れるカラムを除いて全縺?GroupBy句に入れる
集計関数の条件縺?having句を使用する(where句は元のデータに対するも縺?)、having句には集計関数が入る（下に臀??を載せた）
SQLワイルドカード:複数文字%,1文字_
WINワイルドカード:複数文字*,1文字?
エスケープ文字縺?escape句で設定。like句の藹??ろにつ縺?。escape '\'
日臀??と文字列の藹??觸??が必要To_char関数縺?To_date関数を使用する
to_char(column,'yyyy/mm/dd hh24:mi:ss')
to_date('2009/02/21','yyyy/mm/dd')
横藹??を大き縺?するset line 150
縦を大き縺?するset pages 30
union縺?distinctが觸??かる、全藹??得縺?union all
inner join onは両方のテーブルにあるものだけ、どちらかだけにあるのは無縺?なるイメー繧?
left (outer) join onは左側のテーブルにしかないものも表示、左に右の該当部を縺?っつけるイメー繧?
right (outer) join onは右側のテーブルにしかないものも表示、右に左の該当部を縺?っつけるイメー繧?
full outer join onは全ての鐔??を表示
trancateは饅??速だがロールバックできない、deleteはロールバックできる、論理削除がindexメンテコスト菴?で良い
多対藹??縺?SQLが含まれないか確鐔??する（下に臀??を載せた）

■テーブル軆??合4遞?
1蟇?1、1対藹??の軆??合を行う
１対藹??でも行数が増えるが、多対藹??は全軆??合せになり行数が比例関数的に藹??えてしまう
多対藹??になるのはテーブル設計が悪いのでは・??
1)inner join - on () 両方あるものを表示、欠けがでる
2)left outer join - on () どちらかは觸??けがなく表示
3)right outer join - on ()  どちらかは觸??けがなく表示
4)full outer join - on ()  欠けがなく表示

select URIID,NAME,UKYAKUID from URIAGE left outer join TORIHIKI
 on (URIAGE.UKYAKUID = TORIHIKI.ID)
select URIID,NAME,UKYAKUID from TORIHIKI right outer join URIAGE
 on (URIAGE.UKYAKUID = TORIHIKI.ID)
select URIID,NAME,UKYAKUID from TORIHIKI full outer join URIAGE
 on (URIAGE.UKYAKUID = TORIHIKI.ID)
select 伝票番藹??,行番藹??,処理譌?,商品名,担当者名,顧客名,数驥? from 売臀??デー繧?
 left outer join 商品マス繧? on 売臀??デー繧?.商品ID = 商品マス繧?.商品ID
 left outer join 担当者マス繧? on 売臀??デー繧?.担当者ID = 担当者マス繧?.担当者ID
 left outer join 顧客マス繧? on 売臀??デー繧?.顧客ID = 顧客マス繧?.顧客ID
 order by 伝票番藹??,行番藹??

■なおjoin on藹?? 縺? where句での軆??合の違い
１）select * from A, B where A.a = B.a and A.c = 3;
２）select * from A inner join B on A.a = B.a where A.c = 3;
両者は同じ結果、前者の方が古縺?からある構文，後者が新しい構文。だがOuter joinは臀??記にある通り違う
https://okwave.jp/qa/q8090979.html

■WHERE縺?JOIN ONで条件を指定した場合の違い
SELECT * FROM movies LEFT OUTER JOIN counts ON counts.movie_id = movies.id WHERE counts.date_at = '2012-12-16';
　WHERE句縺?JOINが終繧?ってから評価される→Outer joinでも12/16でない映画が含まれずLEFTが欠ける
この問題の解決に縺?WHERE句に条件を指定するのでな縺?JOIN ONの追加条件として指定する
SELECT * FROM movies LEFT OUTER JOIN counts ON counts.movie_id = movies.id AND counts.date_at = '2012-12-16';
　Outer joinしLEFTの全映画が出力したいので、結合前縺?12/16以藹??のカウントを外した上で軆??合させると逆縺?LEFT側は全部でる（RIGHTの条件で軆??った上縺?LEFT OUTER JOINだ縺?RIGHTがNULLでもLEFTが出る）
http://hakutoitoi.hatenablog.com/entry/2012/12/17/005208

■多対藹??、1対藹??の鐔??謨?
社員と職域、社員と手当、それぞれ結合しても多の鐔??謨?(1x多)になる
こんな設計や操作はするなという事（多x多の鐔??数になる、多対藹??の軆??合は鐔??繧?ないこと・??
-- データ挿入・??社蜩?
INSERT INTO x_employee (no,name) VALUES (1, '田中');
INSERT INTO x_employee (no,name) VALUES (2, '鈴譛?');
INSERT INTO x_employee (no,name) VALUES (3, '佐阯?');
-- データ挿入・??職域(兼任が発生)
INSERT INTO x_joblevel (no,res) VALUES (1, '部髟?');
INSERT INTO x_joblevel (no,res) VALUES (1, '課髟?');
INSERT INTO x_joblevel (no,res) VALUES (2, '蟷?');
-- データ挿入・??手当(一人に幾つか付縺?)
INSERT INTO x_compensation (no,allow) VALUES (1, '住宅');
INSERT INTO x_compensation (no,allow) VALUES (1, '交通');
INSERT INTO x_compensation (no,allow) VALUES (2, '住宅');
-- １対藹??(田中が兼任のため田中分が2行、鈴譛?1行で都合3行、1対藹??だとまだ兼任と理解ができる)
select * from x_employee
 inner join x_joblevel on x_employee.no = x_joblevel.no
-- 多対藹??(田中兼任2行x田中手当2行の藹??計4行、鈴木縺?1行で都合5行、田中だけ縺?4行でるとマトリックスの諢?味が不譏?になる)
select * from x_employee
 inner join x_joblevel on x_employee.no = x_joblevel.no
 inner join x_compensation on x_employee.no = x_compensation.no

■Group by/having/where
藹??得したいカラムは集計関数に使繧?れるカラムを除いて全縺?GroupBy句に入れるの臀??
select distinct boss from fun_test_data
select department, avg(salary) from fun_test_data group by department
select boss, avg(salary) from fun_test_data group by boss
select department, boss, avg(salary) from fun_test_data group by department, boss

集計関数の条件縺?having句を使用する(where句は元のデータに対するも縺?)、having句には集計関数が入る例
select department, count(department), avg(salary) from fun_test_data where department!="営業" group by department;
select department, count(department), avg(salary) from fun_test_data where department!="営業" group by department having avg(salary) > 45;
窶?moreにデータをDDLを置いてお縺?ので操作して感じる

集計関数に縺?sum() count() avg() stddev()標準偏蟾? variance()分謨?etc..がある

nullを無鐔??するので、3件が100, null, 200のとき平均縺?150となる、nullを0にする事
SELECT AVG(NVL(SURYO,0)) AS 平均, COUNT(NVL(SURYO,0)) AS 件謨? FROM TEST1

data2縺?nullがありカウントしようとするがnullはカウントされず１行目はダメ、件数縺?keyをカウントせよ
select COUNT(DATA2) from EXAMPLE_TABLE where DATA2 is NULL
select COUNT(ID) from EXAMPLE_TABLE where DATA2 is NULL

窶?SQLはバグかどうか分からず失敗かどうか分かりに縺?い→計算蠑?を書いてからSQLを書縺?
集計値の集計を行う際には集計順蠎?に注諢?せよ | 実践から学ぶ分析の落とし遨? - GiXo Ltd.

■サブクエリ・??副蝠?い合繧?せ）
どこでもOK、any() in() some() exists() not exists()等がある、mysqlに縺?minus() except()はない

/// where条件が1莉?
SELECT 藹??注番藹?? FROM 藹??注表 WHERE 商品コード = (SELECT 商品コード FROM 商品陦? WHERE 商品名 = 'JUICE');
/// where条件が複謨?
SELECT 藹??注番藹?? FROM 藹??注表 WHERE 商品コード IN (SELECT 商品コード FROM 商品陦? WHERE 単萓? = 400);
/// having縺?
SELECT 顧客コード,COUNT(DISTINCT 藹??注番藹??) FROM 藹??注表 GROUP BY 顧客コード HAVING 藹??注個謨? < (SELECT MIN(藹??注個謨?) FROM 藹??注表 WHERE 顧客コード = '002');
/// from縺?
SELECT MAX(AVG_J) AS MAX_AVG FROM (SELECT AVG(藹??注個謨?) AS AVG_J FROM 藹??注表 GROUP BY 顧客コード) ;
/// selectに・??出力表のカラム数縺?1個だけで、これが変繧?る等はできないかと・??
SELECT 顧客コード,AVG(藹??注個謨?), (SELECT AVG(藹??注個謨?) FROM 藹??注表) FROM 藹??注表 GROUP BY 顧客コード;

selectサブクエリにカラムを一つづつ足せば鐔??数も可だが
select name, res,
 (select allow from x_compensation where x_compensation.no = x_employee.no limit 0, 1),
 (select allow from x_compensation where x_compensation.no = x_employee.no limit 1, 1)
 from x_employee inner join x_joblevel on x_employee.no = x_joblevel.no inner join x_compensation on x_employee.no = x_compensation.no

/// 相関サブクエリ・??出力表のカラム数が変繧?る等はできないかと・??
メインクエリの鐔??ごとにサブクエリを実行、結合をサブに鐔??入している、違う書き方ができるかも知れないがサブからでな縺?メインから考えている感じ
例１）
SELECT * FROM 商品陦? AA WHERE '20010401' IN  (SELECT 納品譌? FROM 藹??注表 BB WHERE AA.商品コード = BB.商品コード);
例２）
SELECT SUM(藹??注個謨?),納品譌? FROM 藹??注表 PP GROUP BY 納品譌? HAVING SUM(藹??注個謨?) > (SELECT 150 + MAX(藹??注個謨?) FROM 藹??注表 QQ WHERE PP.納品譌? = QQ.納品譌?);

■Union
UNION句でマージする複数縺?SELECT文は、(1)SELECT句のカラム数が同じこ縺?(2)カラムのデータ型が同じか、変觸??可能であるこ縺?
select shit from ass union all select kuso from ketsu

■SQLはカラム数の藹??加数で觸??成考える？
left outer joinはカラム数がカラム数の合計から共通縺?join onのカラム数を引いた数・??行数は同じ）
　full outer join はカラム数がカラム数の合計から共通縺?join onのカラム数を引いた数・??行数縺?leftの鐔??素数縺?rightの鐔??素数を合計したもの・??
unionは重複を除藹??し表を足し合繧?せるため行数が両表の合計行数・??カラム数は合致藹??要でカラム数は藹??繧?らない）
unian allは重複を除藹??せず表を足し合繧?せるため行数が両表の合計行数・??カラム数は合致藹??要でカラム数は藹??繧?らない）
cross joinはカラム数が両表のカラム数の合計、行数は両表の鐔??数の觸??け算
　再帰的縺?SQL処理縺?cross joinし条件を付けるか？
　　標準SQL縺?From句のカンマ縺?cross joinとなる
with句は副蝠?い合繧?せを見やす縺?したも縺?
distinct縺?(組み合繧?せ縺?)一諢?になる行のみにし重複を省縺?
UNION 縺?UNION ALLの違い - Qiita
CROSS JOIN （クロス軆??合）を使ってデータを藹??得する - JOIN （結合）を使いこなそう - SQL Server 入門 (sql55.com)
SQL縺?DISTINCTとは・??（OracleやMySQLで使用する方觸??） | IT職種コラム (it-kyujin.jp)

■テーブル設計、制軆??
CONSTRAINT pk_uriage PRIMARY KEY (伝票番藹??, 行番藹??),
CONSTRAINT fk_shohin FOREIGN KEY (商品ID) REFERENCES 商品マス繧?(商品ID)
CONSTRAINT ck_gender CHECK (性蛻?=0 OR 性蛻?=1)
制軆??は列に指定しても、うしろで指定してもどちらでもよい
CONSTRAINTのキー名を省略するとオラクルが勝手につける
主キーに縺?NULLは入らない
ユニークキーに縺?NULLを入れてもよい
NOT NULLの未入力はエラ繝?

■シーケン繧?
シーケンスはオブジェクトとして表とは別に臀??成する
デクリメントはできないので修正縺?DROPして臀??成し直す
DUALはダミー表
nextvalはインクリした後の蛟?
currvalは現在蝨?
create sequence testseq
select testseq.nextval from dual
select testseq.currval from dual
insert into URIAGE (URIID,URIDATE,UKYAKUID) values (testseq.nextval,'09-11-16','101')
drop sequence testseq

■ビュ繝?
頻軆??に鐔??繧?れるような蝠?い合繧?せをスキーマに格軆??してお縺?、パフォーマンスが上がる場合がある
集約等をしなければパフォーマンスには問題がない。次を使うとパフォーマンスが落ちる。OLAP 関数・??RANK, DENSE_RANK, ROW_NUMBER 等）、集約関数・??AVG, COUNT, SUM, MIN, MAX）、集合演算子（ UNION, INTERSECT, EXCEPT 等 ）
ビューは觸??索だけでな縺?、データの追加、更新、削除も行える※使用しない方が良い？
create view 売臀??ビュ繝? as
 select 伝票番藹??,行番藹??,処理譌?,商品名,担当者名,顧客名,数驥? from 売臀??デー繧?
 left outer join 商品マス繧? on 売臀??デー繧?.商品ID = 商品マス繧?.商品ID
 left outer join 担当者マス繧? on 売臀??デー繧?.担当者ID = 担当者マス繧?.担当者ID
 left outer join 顧客マス繧? on 売臀??デー繧?.顧客ID = 顧客マス繧?.顧客ID
 order by 伝票番藹??,行番藹??
select * from 売臀??ビュ繝?

■用鐔??
concat()窶?CONCATENATE 文字連軆??
coalesce()…コーレス、合体する、荳?えられた引数のう縺?NULLでない最初の藹??数を返す
LPAD() RPAD()…文字の右詰左詰め
OLAP窶?OnLine Analytical Processing＝分析、キューブ

■インデック繧?
１）インデックスが効かない
関数　where datediff(now(),mod_date)>180
蠑?　where col1 / 2 = 0
否藹??構文　where col1 != 3
Like検索・??前方臀??致除く）　where col1 like '%string%'とかwhere col1 like '%string'とか
２）複合インデックスでは指定順が重要
index a
 create index idxA1 on tbl (col1);
 create index idxA2 on tbl (col2);
 -> where col1 = x > idxA1が使用される
 -> where col2 = x > idxA2が使用される
 -> where col1 = x AND col2 = x > idxA1か2のどちらかが使用される
index b
 create index idxB1 on tbl (col1, col2);
 -> where col1 = x > idxB1が使用される
 -> where col2 = x > インデックスは使用されない
 -> where col1 = x AND col2 = x > idxB1が使用される,indexA1,A2より高速
index c
 create index idxC1 on tbl (col2, col1);
 -> where col1 = x > インデックスは使用されない
 -> where col2 = x > idxC1が使用される
 -> where col1 = x AND col2 = x > idxC1が使用される,indexA1,A2より高速

■RFM(recency frequenctry monetary)
SELECT name, MAX(date), MIN(date) FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no GROUP BY name ORDER BY MAX(date) DESC
SELECT name, count(x_bill.no) FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no GROUP BY name ORDER BY count(date) DESC
SELECT name, sum(total_bill) FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no  GROUP BY name ORDER BY sum(total_bill)  DESC
SELECT name, SUM(price * unit) FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no GROUP BY name ORDER BY SUM(price * unit) DESC
↓
SELECT name, MAX(date), count(DISTINCT x_bill.no), SUM(price * unit) FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no GROUP BY name ORDER BY SUM(price * unit) DESC
　※購入頻度は藹??対藹??で重複が出るのでカウント縺?distinct primaryKeyで重複を含めない

↓順位を点数化のため、MySQLに縺?Rownumがなく変数を使いシンプルな表でまず作った
SET @rownum_recent=0; 
SELECT @rownum_recent:=@rownum_recent+1 as rank_recent, no, name, recent  from
 (SELECT x_employee.no, name, MAX(date) as recent FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no GROUP BY name ORDER BY MAX(date) DESC) as t1

SET @rownum_frequent=0; 
SELECT @rownum_frequent:=@rownum_frequent+1 as rank_frequent, no, name, frequent from
 (SELECT x_employee.no, name, count(x_bill.no) as frequent FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no GROUP BY name ORDER BY count(date) DESC) as t2

SET @rownum_money=0; 
SELECT @rownum_money:=@rownum_money+1 as rank_money, no, name, money from
 (SELECT x_employee.no, name, SUM(price * unit) as money FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no GROUP BY name ORDER BY SUM(price * unit) DESC) as t3

↓まとめる(user_noで軆??合した、別名を付ける計算カラムよりどの元の表かが重要かもLeftかRight joinか縺?)

SET @rownum_rank=0;
SET @rownum_recent=0;
SET @rownum_frequent=0;
SET @rownum_money=0;
SELECT @rownum_rank:=@rownum_rank+1 as rank, rank_recent+rank_frequent+rank_money as least_score_wins, t1_r.no as user_no, t1_r.name, recent, frequent, money, rank_recent, rank_frequent, rank_money
FROM (
   SELECT @rownum_recent:=@rownum_recent+1 as rank_recent, t1.no, t1.name, recent
   FROM (SELECT x_employee.no, x_employee.name, MAX(date) as recent FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no GROUP BY x_employee.no, x_employee.name ORDER BY MAX(date) DESC) as t1
) as t1_r
LEFT OUTER JOIN (
   SELECT @rownum_frequent:=@rownum_frequent+1 as rank_frequent, t2.user_no, t2.name, frequent
   FROM (SELECT x_bill.user_no, x_employee.name, count(distinct x_bill.no) as frequent FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no GROUP BY x_bill.user_no, x_employee.name ORDER BY count(distinct x_bill.no) DESC) as t2
) as t2_f
ON t1_r.no = t2_f.user_no
LEFT OUTER JOIN (
   SELECT @rownum_money:=@rownum_money+1 as rank_money, t3.no, t3.name, money
   FROM (SELECT x_employee.no, x_employee.name, SUM(price * unit) as money FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no GROUP BY x_employee.no, x_employee.name ORDER BY SUM(price * unit) DESC) as t3
) as t3_m
ON t2_f.user_no = t3_m.no
ORDER BY least_score_wins asc

■NULLの件数を事前に数えてお縺?
SELECT *
 FROM (SELECT 'value', count(COALESCE(value,0))  FROM bangboo_data.x_data WHERE value IS NULL)
 union all
 (SELECT 'value2', count(COALESCE(value2,0))  FROM bangboo_data.x_data WHERE value2 IS NULL)
 union all
 (SELECT 'v_no_null', count(COALESCE(v_no_null,0))  FROM bangboo_data.x_data WHERE v_no_null IS NULL)
↑が安全軆??

SELECT count_null_1, count_null_2
 FROM (SELECT value, count(COALESCE(value,'')) as count_null_1 FROM x_data WHERE value IS NULL GROUP BY value) as t1,
 (SELECT value2, count(COALESCE(value2,''))  as count_null_2 FROM x_data WHERE value2 IS NULL GROUP BY value2) as t2
　窶?From A, B, C でクロス軆??合となり全軆??み合繧?せがでる、1行ずつならOKだが、、
　窶?SQLはカウント0件をグルーピングできない、表示自臀??存在しないことになるから、↑は藹??々危険で↓縺?
SELECT count_null_1, count_null_2
 FROM (SELECT count(COALESCE(value,'')) as count_null_1 FROM x_data WHERE value IS NULL) as t1,
 (SELECT count(COALESCE(value2,''))  as count_null_2 FROM x_data WHERE value2 IS NULL) as t2,
 (SELECT count(COALESCE(v_no_null,0))  as count_null_3 FROM x_data WHERE v_no_null IS NULL) as t3

■SQL一隕?
　https://qiita.com/tatsuya4150/items/69c2c9d318e5b93e6ccd
UPDATE テーブル名 SET カラム名 = 蛟?, カラム名 = 蛟?  WHERE id = 1
INSERT INTO テーブル名 VALUES ('1')DELETE FROM テーブル名 WHERE 条件
intersectは重複するもの　SELECT name FROM ユーザー・?? INTERSECT SELECT name FROM ユーザ繝?2
　窶?unionは和集合、exceptは差集合、intersectは軆??集合