SQL」タグアーカイブ

SQL インデックス

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

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

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

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

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

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

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

SQL EXCEPT, INTERSECTについて

Accessでは使えないためSQL Serverも利用した。

久しぶりに動かしたら
your sql server installation is either corrupt or has been tampered
というエラー。

ほとんど使わないので関連サービスを止めていた。

管理ツールのサービスから
SQL Server (SQLEXPRESS)
手動起動したら繋がった。

SQL DB正規化

直ぐ忘れてしまう。

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

第一正規化

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

第二正規化

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

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

第三正規化

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

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

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

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

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

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

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

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

テーブルの関連

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

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

VBA SQL実験用

SQLの挙動を調べるのにエクセルから手軽にSQLを発行

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));
のように指定。