SQL インデックス

基本はB-tree(B+tree)インデックスが利用されることが多い。

特徴としては、ノードにキーを持ち、リーフノードにポインタがある。作成時はキーをソートする。
リーフノードまでの距離が均一でバランスがいいのが特徴。

インデックスが必要な箇所

大規模(数万以上)なテーブル。規模が小さいとフルスキャンと大差ない。

カーディナリティ(入る値の種類数のこと)が高い列。さらに入る値が集中していないこと。

検索条件や結合条件の列以外は無意味。(検索条件内でもインデックスの列で演算などするとダメ)注意点として、否定形(<>)やORやLIKE演算子はダメで、暗黙の型変換もダメ。

主キーは既にインデックスされているので不要。

統計基礎 標準偏差、相関係数、線形回帰

標準偏差、相関係数、線形回帰の求め方。
手動計算とエクセル関数を使った場合。

分析基礎

・基本

結論は金額換算して分かりやすく。継続観察していくならKPIを設定する。

・サンプルの中で変数に属性を追加。

例)
来店率が下がっている顧客、上がっている顧客、特定の時期に上がる顧客等。
購入金額が高い顧客(Aランク)、低い顧客(Bランク)など。

これにより、顧客のニーズの仮説や、分析から排除の必要性等を検討する。

・次に他のサンプルと比較。

サンプル1(会員等)での特徴(標準偏差や中央値等)がサンプル2(非会員等)と比較してどう違うか。そうすることで、サンプル1はサンプル2と比べてはどのような特徴があるのか分かる。

・複数のサンプルでの比較

会員と商品の関係での特徴は、非会員と商品の関係と比較してどう違うか。
会員は特定商品のリピート率が高い等。

SQLで相関係数(傾き)

標準偏差や分散などは標準の関数であるが、相関係数はない。
集計用に傾きだけ欲しかったので、とりあえずSQLで求めた

テーブル名:t
結果:17.2482570475901

傾きで利用したストアド

SQl Server Management Studioでの結果をそのままエクセルに貼り付けたかったので、
以下のような書き方で解決した。

SQL DB正規化

直ぐ忘れてしまう。

先ず、一方の値が決まると他方も決まるものを、関数従属性という。

第一正規化

一つのセルに複数のデータを持たない。

第二正規化

部分関数従属を取り除く。
部分関数従属とは、主キーの一部だけで一意に決まる列のこと。

主キーが複数あり、その内の一つだけで値が決まるものを別のテーブルにする。

第三正規化

推移的関数従属を取り除く。

推移的関数従属とは、テーブル内の段階的な関数従属のこと。
つまり、主キー以外と関数従属があること。

主キー以外の項目で、他の列の値を一意に決めるものを別テーブルにする。

例えば、(主)社員ID → 部署コード → 部署名

部署コードが決まれば部署名が決まる。当然主である社員IDと部署コードは関数従属の関係なので、
段階的な関数従属があるといえる。

部分関数従属、推移的関数従属を取り除くことの必要性。

あるテーブルの中で推移的関数従属が残っていると、間違った登録が可能。(会社コードと会社名)
また、主キーが不明の段階で登録できない。

キー| メンバー名 | 会社コード  | 会社名
1  | 太郎     | 0000   | AAA
2  | 次郎     | 0001   | AAA

テーブルの関連

1対1、1対多、多対多があるが、通常1対多のみ使う。

多対多となる場合、関連実体というテーブルを作る。ただ、通常の業務要件では、
マスタとマスタを直接関連付けることはないので、普通に考えると多対多とはならない。

ストアドプロシージャ

SQLServer

・定義

create procedure ストアドプロシージャ名
@パラメータ名 型
as
begin
処理
end

・変数

宣言
declare @変数名 型

データ型
varchar:文字列
int:数値
decimal:小数
date:日付

代入
set 変数 = 値

・カーソル
1行ずつループして処理する方法。

カーソルの宣言
declare カーソル名 cursor for (select 文)

カーソルを開く
open カーソル名

データの取得
fetch next from カーソル名 into 変数リスト

ループ処理の実行

while 条件式
begin
処理内容 where current of カーソル名
end

※ループの定番
while @@fetch_status = 0

カーソルを閉じる
close カーソル名

カーソルの開放
deallocate カーソル名

MySQL

CREATE ROUTINEの権限が必要。

・基本
create procedure sample01()
select now();
mysql>call sample01;

・削除
mysql>drop procedure sample01;

・一覧
show procedure status;

・中身
show create procedure sample01;

・引数をとる
create procedure sample01(in a int, in b int)
select a + b;
mysql>call sample01(10,5);

・返り値
create procedure sample01(out x int)
set x = 3;
mysql> call sample01(@var);
mysql> select @var;

・複数のクエリを発行
delimiter //
create procedure sample01()
begin
select 1;
select 2;
end//
delimiter ;
mysql> call sample01;

・IF文
delimiter //
create procedure sample02(in x int)
begin
if x = 1 then
select “inp 1”;
elseif x = 2 then
select “int 2”;
else
select “inp else”;
end if
end //
delimiter ;
call sample02(1);

・select~into文で変数へ
delimiter //
create procedure p(in a int, out sum int)
begin
select a * 2 into @tmp;
set sum = @tmp;
end //
delimiter ;
call p (4,@sum);
select @sum;

・カーソル単位で処理する

declare xxx cursorという形で宣言する。
for select でselectの結果を定義する。
fetch cursor for カラムで代入。
処理前にopenして最後にcloseする。

 

MySQL 基本SQL

MySQL関連基礎(主にLinux)

外部から接続したい場合、
hostの変更とconfファイルにbind-addressを追加する。

保存場所確認
show variables like ‘%dir%’;

my.cnfの位置
mysql –help | grep /my.cnf
左から順番。開いていくと/etc/mysql/my.cnf以外は空
my.cnfを開くと
/etc/mysq/conf.d/
/etc/mysql/mysql.conf.d/
の2つのディレクトリをインクルードしている。
ディレクトリの中に適当にファイルを作っていれておけばOK

ユーザー

ユーザーの作成
CREATE USER ‘test_usr’@’192.168.102.%’ IDENTIFIED BY  ‘passw0rd’;

ホスト名の変更
RENAME USER ‘root’@’localhost’ to ‘root’@’192.168.102.%’;

ユーザー一覧
select User,Host from mysql.user;

パスワード変更(ユーザー指定)
SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘56789’);

パスワード変更(ログインユーザー)
SET PASSWORD = PASSWORD(‘5678’);

データベース

現状の状況
STATUS;

データベース一覧
SHOW DATABASES;

データベースの作成
CREATE DATABASE test_db2 CHARACTER SET sjis;

データベース選択
USE test_db2

データベース削除
DROP DATABASE test_db;

テーブル

テーブル一覧
SHOW TABLES;

エンジンの確認
SHOW TABLE STATUS;

テーブルの作成
CREATE TABLE tbl (
id int PRIMARY KEY AUTO_INCREMENT NOT NULL
) DEFAULT CHARACTER SET sjis;

テーブルのリネーム
ALTER TABLE tbl RENAME tbl;

テーブルの複製
CREATE TABLE to_tbl LIKE from_tbl

カラム

列の追加
ALTER TABLE tbl ADD col VARCHAR

列の情報
SHOW COLUMNS FROM tbl;

列の変更
ALTER TABLE tbl CHANGE col col2 int;

列の削除
ALTER TABLE tbl DROP col2;

SELECT式の評価順

FROM
ON – JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP(LIMIT)

文字コード

有効な文字コード
SHOW CHARACTER SET;

データベースの文字コード
SHOW CREATE DATABASE test_db2;

文字コード(接続中のデータベース)
SHOW VARIABLES LIKE ‘char%’;

作成済みデータベースの文字コード変更
ALTER DATABASE db_name CHARACTER SET utf8;

或いは以下のようなコードで確認

文字コードの修正方法

例えば以下のような状態

コマンド、PHPから見るとエンコが合っていないのが分かる。

[mysqld]
character-set-server = utf8
を記述して一旦再起動。

sudo /etc/mysql/init.d/mysql restart

これで問題ないはず。

以前はmy.cnfには以下のように書いていた。

[mysqld]
character-set-server=utf8 //バージョンよって違う(以前はdefault-character-set)

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8
//全クライアントで共通の設定と書かれていたが、ここは書かなくても大丈夫のようだけで書いておいた。

[client]
default-character-set=utf8

character_set_client : クライアント側で発行したsql文はこの文字コードになる
character_set_connection : クライアントから受け取った文字をこの文字コードへ変換する
character_set_database : 現在参照しているDBの文字コード
character_set_results : クライアントへ送信する検索結果はこの文字コードになる
character_set_server : DB作成時のデフォルトの文字コード
character_set_system : システムの使用する文字セットで常にutf8が使用されている

ちなみに、
PDO(‘mysql:host=localhost;dbname=db;charset=utf8’, ‘root’, ‘5678’);
として、DSNでcharset=utf8と指定して、逆にmy.cnfに何も書かないと、

となる。

つまり、データベース作成、テーブル作成、DSNでも字コードを指定しても
結局serverは変わらないので、my.cnfは必要。
逆にmy.cnfを1行入れれば、PDOのDSNはなくてもOK。

Windowsの文字コード

create database時に、sjis指定すると、
character_set_client :utf8
character_set_connection:utf8
character_set_database:sjis
character_set_filesystem:binary
character_set_results:utf8
character_set_server:utf8
character_set_system:utf8

sjis指定なしでデータベースを作成すると、
character_set_client :utf8
character_set_connection:utf8
character_set_database:utf8
character_set_filesystem:binary
character_set_results:utf8
character_set_server:utf8
character_set_system:utf8
となる。

my.iniは
C:\ProgramData\MySQL\MySQL Server 5.7
にある。

ちなみに、インストール時は

[client]
指定なし

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8

[mysqldump]
指定なし

となっていた。

ちなみに、㈱など文字化けする場合、
[mysqld]
character-set-server=sjis
とすると直る。

Winでの再起動は
sevices.msc
から実行

制約の種類

NOT NULL
CHECK
UNIQUE KEY
PRIMARY KEY
FOREIGN KEY

追加方法

ALTER TABLE tbl ADD UNIQUE (key);

ちなみに、primaryとuniqueは
複数指定すると、複合になるのでuniqueをそれぞれ複数の列の場合、
create table tbl (id int ,nm varchar(5) , unique(id) , unique(nm));
のように指定。